For anyone that cared, I resolved
this by changing all of the tables needing the foreign key mapping to innodb.
All new tables are config’d for innodb storage and the references table
below were myisam(left over from old install). Thus the script created new
innodb tables and couldn’t create foreign keys to myisam tables….they
need to be the same…
Up and running with otrs 2.3.5
for a couple of days….then plan to go to 2.4.7….yay yay!!!
From:
otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Isaac
Gonzalez
Sent: Sunday, July 11, 2010 12:45 AM
To: User questions and discussions about OTRS.
Subject: Re: [otrs] upgrade issues from 2.2.x to 2.4.x
After a bit more investigation here
are the real errors from sql…
-----------------------------------------------------------------------------------------------
Upgrading from 2.2.x to
2.4.x(cent os 5, mysql 4.1.18)
I ran into some errors during
the db upgrade for mysql for 2.2 to 2.3
The first error was on line 4
for the statement
ALTER TABLE sla DROP FOREIGN KEY
FK_sla_service_id_id;
There is no foreign key in that
table in my 2.2.4 install!
Gave the “error of rename
of dbname\sla to #sql2-25000-b”
And then every alter statement
below that tried to add a constraint also gave back an error cannot create
table \dbname\#sql – 2500_b.frm
All of the below really give the
error:
100711 0:16:57 Error in
foreign key constraint of table otrsnew/#sql-19a8_39:
FOREIGN KEY (ticket_id)
REFERENCES ticket (id):
Cannot resolve table name close to:
(id)
And similar for each Reference the
foreign key is trying to make via each statement….
ALTER TABLE ticket_watcher ADD CONSTRAINT
FK_ticket_watcher_ticket_id_id FOREIGN KEY (ticket_id) REFERENCES ticket (id);
ALTER TABLE ticket_watcher ADD
CONSTRAINT FK_ticket_watcher_user_id_id FOREIGN KEY (user_id) REFERENCES users
(id);
ALTER TABLE ticket_watcher ADD
CONSTRAINT FK_ticket_watcher_create_by_id FOREIGN KEY (create_by) REFERENCES
users (id);
ALTER TABLE ticket_watcher ADD
CONSTRAINT FK_ticket_watcher_change_by_id FOREIGN KEY (change_by) REFERENCES
users (id);
ALTER TABLE queue_preferences
ADD CONSTRAINT FK_queue_preferences_queue_id_id FOREIGN KEY (queue_id)
REFERENCES queue (id);
ALTER TABLE service_sla ADD
CONSTRAINT FK_service_sla_service_id_id FOREIGN KEY (service_id) REFERENCES
service (id);
ALTER TABLE service_sla ADD
CONSTRAINT FK_service_sla_sla_id_id FOREIGN KEY (sla_id) REFERENCES sla (id);
ALTER TABLE link_type ADD
CONSTRAINT FK_link_type_create_by_id FOREIGN KEY (create_by) REFERENCES users
(id);
ALTER TABLE link_type ADD
CONSTRAINT FK_link_type_change_by_id FOREIGN KEY (change_by) REFERENCES users
(id);
ALTER TABLE link_type ADD
CONSTRAINT FK_link_type_valid_id_id FOREIGN KEY (valid_id) REFERENCES valid
(id);
ALTER TABLE link_state ADD
CONSTRAINT FK_link_state_create_by_id FOREIGN KEY (create_by) REFERENCES users
(id);
ALTER TABLE link_state ADD
CONSTRAINT FK_link_state_change_by_id FOREIGN KEY (change_by) REFERENCES users
(id);
ALTER TABLE link_state ADD
CONSTRAINT FK_link_state_valid_id_id FOREIGN KEY (valid_id) REFERENCES valid
(id);
ALTER TABLE link_relation ADD
CONSTRAINT FK_link_relation_source_object_id_id FOREIGN KEY (source_object_id)
REFERENCES link_object (id);
ALTER TABLE link_relation ADD
CONSTRAINT FK_link_relation_target_object_id_id FOREIGN KEY (target_object_id)
REFERENCES link_object (id);
ALTER TABLE link_relation ADD
CONSTRAINT FK_link_relation_state_id_id FOREIGN KEY (state_id) REFERENCES
link_state (id);
ALTER TABLE link_relation ADD
CONSTRAINT FK_link_relation_type_id_id FOREIGN KEY (type_id) REFERENCES
link_type (id);
ALTER TABLE link_relation ADD
CONSTRAINT FK_link_relation_create_by_id FOREIGN KEY (create_by) REFERENCES
users (id);
ALTER TABLE article_search ADD
CONSTRAINT FK_article_search_article_sender_type_id_id FOREIGN KEY
(article_sender_type_id) REFERENCES article_sender_type (id);
ALTER TABLE article_search ADD
CONSTRAINT FK_article_search_article_type_id_id FOREIGN KEY (article_type_id)
REFERENCES article_type (id);
ALTER TABLE article_search ADD
CONSTRAINT FK_article_search_ticket_id_id FOREIGN KEY (ticket_id) REFERENCES
ticket (id);