Then I wanted to create a dump file to transfer it to my MySQL 5.1 installation on the same machine. Here's what I did:
E:\mysql_dump>mysqldump --triggers=false sakila > sakila.sql
Enter password: *******
E:\mysql_dump>mysql -P 3307 sakila < sakila.sql
Enter password: *******
ERROR 1005 (HY000) at line 392: Can't create table
'sakila.payment' (errno: 150)
E:\mysql_dump>perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
Here are my default settings for mysqldump:
all TRUE
all-databases FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
master-data 0
max_allowed_packet 25165824
net_buffer_length 1047551
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
routines FALSE
set-charset TRUE
shared-memory-base-name (No default value)
single-transaction FALSE
socket (No default value)
tab (No default value)
triggers TRUE
tz-utc TRUE
user mpopp
verbose FALSE
where (No default value)
Line 392, where the error occurs, starts the CREATE TABLE statement for the payment table:
CREATE TABLE `payment` (
`payment_id` smallint(5) unsigned NOT NULL auto_increment,
`customer_id` smallint(5) unsigned NOT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`rental_id` int(11) default NULL,
`amount` decimal(5,2) NOT NULL,
`payment_date` datetime NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
PRIMARY KEY (`payment_id`),
KEY `idx_fk_staff_id` (`staff_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `fk_payment_rental` (`rental_id`),
CONSTRAINT `fk_payment_customer`
FOREIGN KEY (`customer_id`)
REFERENCES `customer` (`customer_id`)
ON UPDATE CASCADE,
CONSTRAINT `fk_payment_rental`
FOREIGN KEY (`rental_id`)
REFERENCES `rental` (`rental_id`)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_staff`
FOREIGN KEY (`staff_id`)
REFERENCES `staff` (`staff_id`)
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This looks perfectly right to me - also the foreign key creations. FOREIGN_KEY_CHECKS is set to 0, so it shouldn't be a problem that some of the referenced tables don't exist, yet.
The complete dump file can be downloaded from here.
Anybody have a clue?
Another interesting detail - the CREATE statement is different in the original schema:
ReplyDeleteCREATE TABLE payment (
payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id SMALLINT UNSIGNED NOT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
rental_id INT DEFAULT NULL,
amount DECIMAL(5,2) NOT NULL,
payment_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (payment_id),
KEY idx_fk_staff_id (staff_id),
KEY idx_fk_customer_id (customer_id),
CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
But it seems to be different in my database:
mysql> SHOW CREATE TABLE payment\G
*************************** 1. row ***************************
Table: payment
Create Table: CREATE TABLE `payment` (
`payment_id` smallint(5) unsigned NOT NULL auto_increment,
`customer_id` smallint(5) unsigned NOT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`rental_id` int(11) default NULL,
`amount` decimal(5,2) NOT NULL,
`payment_date` datetime NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`payment_id`),
KEY `idx_fk_staff_id` (`staff_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `fk_payment_rental` (`rental_id`),
CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (
`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
However, it's a bit strange:
ReplyDeleteHere it works on my 5.0.18 server with Windows:
E:\mysql_dump>mysql sakila < sakila.sql
Enter password: *******
... but fails on my 5.1.5 server, also on Windows:
E:\mysql_dump>mysql -P 3307 sakila < sakila.sql
Enter password: *******
ERROR 1005 (HY000) at line 392: Can't create table 'sakila.payment' (errno: 150)
If I try to load it into another 5.0.18 server on Linux, it fails, too:
E:\mysql_dump>mysql -h 10.2.4.1 sakila < sakila.sql
Enter password: *******
ERROR 1005 (HY000) at line 392: Can't create table './sakila/payment.frm' (errno
: 150)
If in doubt, better file a bug report about it :)
ReplyDeleteIt's even stranger than strange. Now I tried to create a repeatable case for this "bug", but I didn't even manage to repeat it once more. No more error messages - the dump file works perfectly. Don't know whether I should be happy about it or not.
ReplyDeleteEither it's really a bug that's dug very deep inside MySQL - or it's no bug at all. Anyway, it's (it was) strange.
The syntax definition of create table (http://dev.mysql.com/doc/refman/5.0/en/create-table.html) says it should be
ReplyDelete[DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
not DEFAULT CHARSET etc.
This doesn't seem to be wrong since MySQL itself uses CHARSET - probably as valid shortcut for CHARACTER SET. Also if you use the "SHOW CREATE TABLE" command, the character set will be defined with CHARSET.
ReplyDeleteThe perror 150 geves you a misunderstanding answer. The problem is that you have to DEEPLY verify that BOTH fields for the foreign key are equal. So, if payment.rental_id is INT but rental.rental_id is UNSIGNED INT you will receive this kind of error. Remember that generally primary keys are created unisgned automatically, so you should verify this. Another problem can arise when a field can be NULL and other NOT NULL. Check, check, check :-)
ReplyDelete