I think I figured out my problem but am not sure how to solve…..

All those alter table statements that fail with error besides the first drop statement that cannot resolve table name, are because the new tables that the script created that its trying to make a foreign key restraint are of type innodb. The table that its trying to alter are of type myisam! Thus it cannot resolve it since the tables are not in concert.

http://forums.mysql.com/read.php?136,159805,214625#msg-214625

I imagine I can change all those tables that its trying to alter to be of type innodb, but am not sure if this will mess anything up(and my knowledge of innodb vs myisam is limited)…

Can anyone give me some direction here?

Thanks,

Isaac

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);