Monday, February 13, 2006

Is there a bug?

I've just downloaded and installed the new Sakila Sample Database.

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?

7 comments:

Markus Popp said...

Another interesting detail - the CREATE statement is different in the original schema:

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

Markus Popp said...

However, it's a bit strange:

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

LenZ said...

If in doubt, better file a bug report about it :)

Markus Popp said...

It'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.

Either 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.

Roland Bouman said...

The syntax definition of create table (http://dev.mysql.com/doc/refman/5.0/en/create-table.html) says it should be

[DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

not DEFAULT CHARSET etc.

Markus Popp said...

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.

Anonymous said...

The 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 :-)