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.
# ----------------------------------------------------------
# database: mysql, generated: Mon Jun 6 22:18:34 2005
# ----------------------------------------------------------
ALTER TABLE valid ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE valid ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_priority ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_priority ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_lock_type ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_lock_type ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_lock_type ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE system_user ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE system_user ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE system_user ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE user_preferences ADD FOREIGN KEY (user_id) REFERENCES `system_user`(`id`);
ALTER TABLE groups ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE groups ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE groups ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE group_user ADD FOREIGN KEY (group_id) REFERENCES `groups`(`id`);
ALTER TABLE group_user ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE group_user ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE group_user ADD FOREIGN KEY (user_id) REFERENCES `system_user`(`id`);
ALTER TABLE group_role ADD FOREIGN KEY (role_id) REFERENCES `roles`(`id`);
ALTER TABLE group_role ADD FOREIGN KEY (group_id) REFERENCES `groups`(`id`);
ALTER TABLE group_role ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE group_role ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE group_customer_user ADD FOREIGN KEY (group_id) REFERENCES `groups`(`id`);
ALTER TABLE group_customer_user ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE group_customer_user ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE roles ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE roles ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE roles ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE role_user ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE role_user ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE role_user ADD FOREIGN KEY (user_id) REFERENCES `system_user`(`id`);
ALTER TABLE personal_queues ADD FOREIGN KEY (queue_id) REFERENCES `queue`(`id`);
ALTER TABLE personal_queues ADD FOREIGN KEY (user_id) REFERENCES `system_user`(`id`);
ALTER TABLE theme ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE theme ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE theme ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE ticket_state ADD FOREIGN KEY (type_id) REFERENCES `ticket_state_type`(`id`);
ALTER TABLE ticket_state ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_state ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_state ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE ticket_state_type ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_state_type ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE salutation ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE salutation ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE salutation ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE signature ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE signature ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE signature ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE system_address ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE system_address ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE system_address ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE follow_up_possible ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE follow_up_possible ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE follow_up_possible ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (salutation_id) REFERENCES `salutation`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (signature_id) REFERENCES `signature`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (group_id) REFERENCES `groups`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (follow_up_id) REFERENCES `follow_up_possible`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (system_address_id) REFERENCES `system_address`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE queue ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (ticket_lock_id) REFERENCES `ticket_lock_type`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (ticket_state_id) REFERENCES `ticket_state`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (ticket_priority_id) REFERENCES `ticket_priority`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (queue_id) REFERENCES `queue`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (user_id) REFERENCES `system_user`(`id`);
ALTER TABLE ticket ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (priority_id) REFERENCES `ticket_priority`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (owner_id) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (state_id) REFERENCES `ticket_state`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (history_type_id) REFERENCES `ticket_history_type`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (article_id) REFERENCES `article`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (queue_id) REFERENCES `queue`(`id`);
ALTER TABLE ticket_history ADD FOREIGN KEY (ticket_id) REFERENCES `ticket`(`id`);
ALTER TABLE ticket_history_type ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_history_type ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE ticket_history_type ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE article_type ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_type ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_type ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE article_sender_type ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_sender_type ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_sender_type ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE article_flag ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE article ADD FOREIGN KEY (article_sender_type_id) REFERENCES `article_sender_type`(`id`);
ALTER TABLE article ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE article ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE article ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE article ADD FOREIGN KEY (article_type_id) REFERENCES `article_type`(`id`);
ALTER TABLE article ADD FOREIGN KEY (ticket_id) REFERENCES `ticket`(`id`);
ALTER TABLE article_plain ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_plain ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_plain ADD FOREIGN KEY (article_id) REFERENCES `article`(`id`);
ALTER TABLE article_attachment ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_attachment ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE article_attachment ADD FOREIGN KEY (article_id) REFERENCES `article`(`id`);
ALTER TABLE standard_response ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE standard_response ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE standard_response ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE queue_standard_response ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE queue_standard_response ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE queue_standard_response ADD FOREIGN KEY (standard_response_id) REFERENCES `standard_response`(`id`);
ALTER TABLE queue_standard_response ADD FOREIGN KEY (queue_id) REFERENCES `queue`(`id`);
ALTER TABLE standard_attachment ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE standard_attachment ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE standard_attachment ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE standard_response_attachment ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE standard_response_attachment ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE standard_response_attachment ADD FOREIGN KEY (standard_response_id) REFERENCES `standard_response`(`id`);
ALTER TABLE standard_response_attachment ADD FOREIGN KEY (standard_attachment_id) REFERENCES `standard_attachment`(`id`);
ALTER TABLE auto_response_type ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE auto_response_type ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE auto_response_type ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE auto_response ADD FOREIGN KEY (type_id) REFERENCES `auto_response_type`(`id`);
ALTER TABLE auto_response ADD FOREIGN KEY (system_address_id) REFERENCES `system_address`(`id`);
ALTER TABLE auto_response ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE auto_response ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE auto_response ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE queue_auto_response ADD FOREIGN KEY (auto_response_id) REFERENCES `auto_response`(`id`);
ALTER TABLE queue_auto_response ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE queue_auto_response ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE queue_auto_response ADD FOREIGN KEY (queue_id) REFERENCES `queue`(`id`);
ALTER TABLE time_accounting ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE time_accounting ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE time_accounting ADD FOREIGN KEY (article_id) REFERENCES `article`(`id`);
ALTER TABLE time_accounting ADD FOREIGN KEY (ticket_id) REFERENCES `ticket`(`id`);
ALTER TABLE customer_user ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE customer_user ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE customer_user ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE pop3_account ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE pop3_account ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE pop3_account ADD FOREIGN KEY (valid_id) REFERENCES `valid`(`id`);
ALTER TABLE notifications ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE notifications ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_item ADD FOREIGN KEY (category_id) REFERENCES `faq_category`(`id`);
ALTER TABLE faq_item ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_item ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_item ADD FOREIGN KEY (f_language_id) REFERENCES `faq_language`(`id`);
ALTER TABLE faq_item ADD FOREIGN KEY (state_id) REFERENCES `faq_state`(`id`);
ALTER TABLE faq_history ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_history ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_history ADD FOREIGN KEY (item_id) REFERENCES `faq_item`(`id`);
ALTER TABLE faq_category ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_category ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_state ADD FOREIGN KEY (type_id) REFERENCES `faq_state_type`(`id`);
ALTER TABLE faq_attachment ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_attachment ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
ALTER TABLE faq_attachment ADD FOREIGN KEY (faq_id) REFERENCES `faq_item`(`id`);
ALTER TABLE package_repository ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);
ALTER TABLE package_repository ADD FOREIGN KEY (change_by) REFERENCES `system_user`(`id`);
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support oder Consulting für Ihr OTRS System?
=> http://www.otrs.de/
