Re: Question: Create table fails after switch from 4.0.18-4.1.10

[Heikki Tuuri]

Noah,

I do not have all the table definitions. Please post the full SQL statement sequence that demonstrates the problem.

Also post SHOW INNODB STAUS after that. Also post the full contents of the mysqld .err log.


------------------------
LATEST FOREIGN KEY ERROR
------------------------
050223 2:30:12 Error in foreign key constraint of table test/vir_label_set_fiel
ds:

FOREIGN KEY(index_name, label_set_name)
REFERENCES vir_label_sets(index_name, name) ON DELETE CASCADE,
CONSTRAINT vir_label_set_type_fk
FOREIGN KEY (label_type)
REFERENCES vir_valid_label_types(label_type),
CONSTRAINT vir_field_type_fk
FOREIGN KEY (field_type)
REFERENCES vir_valid_field_types(field_type),
CONSTRAINT vir_field_input_fk
FOREIGN KEY (input_method)
REFERENCES vir_valid_input_methods(input_method),
CONSTRAINT vir_lsf_check_required
CHECK( field_required IN( 'Y', 'N') ),
CONSTRAINT vir_lsf_check_hidden
CHECK( is_hidden IN( 'Y', 'N') ),
CONSTRAINT vir_lsf_check_sortable
CHECK( is_sortable IN( 'Y', 'N') )
)
TYPE=INNODB:
Cannot resolve table name close to:
(index_name, name) ON DELETE CASCADE,
CONSTRAINT vir_label_set_type_fk
FOREIGN KEY (label_type)
REFERENCES vir_valid_label_types(label_type),
CONSTRAINT vir_field_type_fk
FOREIGN KEY (field_type)
REFERENCES vir_valid_field_types(field_type),
CONSTRAINT vir_field_input_fk
FOREIGN KEY (input_method)
REFERENCES vir_valid_input_methods(input_method),
CONSTRAINT vir_lsf_check_required
CHECK( field_required IN( 'Y', 'N') ),
CONSTRAINT vir_lsf_check_hidden
CHECK( is_hidden IN( 'Y', 'N') ),
CONSTRAINT vir_lsf_check_sortable
CHECK( is_sortable IN( 'Y', 'N') )
)
TYPE=INNODB

Best regards,

Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php