Hi all, I currently migrating my old OTRS 1.2.4 to OTRS 2.0.x I would like to use foreign key and so I must use InnoDB table to do that. Step by Step what I do: 1 - Create in a new database tables come from otrs-schema.mysql.sql 2 - Because there is no type in the schema I change type of all tables to InnoDB: See attached file convert-OTRS-2.0-Tables-To-InnoDB.sql to know how. 3 - I import foreign key constraints from otrs-schema-post.mysql.sql At this step I got a
*requête SQL:*
# ---------------------------------------------------------- # database: mysql, generated: Mon Jun 6 22:18:34 2005 # ---------------------------------------------------------- ALTER TABLE valid ADD FOREIGN KEY ( create_by ) REFERENCES `system_user(id)` ;
*MySQL answer:*
| #1005 - Can't create table './Cri-Otrs-test/#sql-4d0b_107bfa.frm' (errno: 150) |
I think this error is produced because of bad syntax (I used MySQL 4.1.4) So I have changed the sql file to the attached file: otrs-schema-post.mysql_good.sql To convert the file otrs-schema-post.mysql.sql I have used this shell line: cat otrs-schema-post.mysql.sql |sed 's/REFERENCES `\(.*\)(\(.*\))`/REFERENCES `\1`(`\2`)/' > otrs-schema-post.mysql_good.sql But his dont work because there is some fields where we would like declare as FOREIGN KEY without have index on it before... So I have run this: ALTER TABLE `standard_response_attachment` ADD INDEX `standard_attachment_id` ( `standard_attachment_id` ); The otrs-schema-post.mysql_good.sql return error on the line:
*requête SQL:*
ALTER TABLE standard_response_attachment ADD FOREIGN KEY ( standard_attachment_id ) REFERENCES `standard_attachment` ( `id` ) ;
*MySQL a répondu:*Documentation http://dev.mysql.com/doc/mysql/en/error-returns.html
| #1005 - Can't create table './Cri-Otrs-test/#sql-4d0b_109d72.frm' (errno: 150) |
This error is because standard_attachment.id is an int(6) and standard_response_attachment.standard_attachment_id is an int(11).. I have stoped here my try to migrating with foreign key support (week end for me now :) ) and I will go drink some beer. ;) If anyone have a success story with please send me SQL file. If noone have an answer for this issue i will continue to work on it and will release SQLs files to do this.** Regards, Yann Richard.