| Bug #984 | mysqld crashes when creating an innodb table with foreign key | ||
|---|---|---|---|
| Submitted: | 3 Aug 2003 12:15 | Modified: | 12 Aug 2003 13:46 |
| Reporter: | Marcus Frischherz | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 3.23.48 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[3 Aug 2003 13:03]
Heikki Tuuri
Marcus,
please upgrade to 4.0.14 and test again. Lots of bugs have been fixed since 3.23.48.
I was not able to repeat the error with mysql-max-3.23.48. Do you use the latin1_de character set?
With 4.0.15 I get:
mysql> DROP TABLE IF EXISTS laender_user2;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS laender_areas2;
Query OK, 0 rows affected (0.01 sec)
mysql> DROP TABLE IF EXISTS laender_regions2;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS laender_visited2;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE laender_user2 ( user_id smallint(6) NOT NULL
-> auto_increment,
-> user_name varchar(32) NOT NULL default '',
-> user_pass varchar(32) NOT NULL default '',
-> lang varchar(255) NOT NULL default 'de',
-> PRIMARY KEY (user_id),
-> UNIQUE KEY user_name (user_name),
-> KEY user_name_2 (user_name,user_pass)
-> ) TYPE=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE laender_areas2 (
-> area_id smallint(6) NOT NULL auto_increment,
-> area_en varchar(255) NOT NULL default '',
-> area_de varchar(255) NOT NULL default '',
-> PRIMARY KEY (area_id)
-> ) TYPE=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE laender_regions2 ( region_id int(11) NOT NULL
-> auto_increment,
-> area_ref smallint(6) NOT NULL default '0',
-> region_de varchar(255) NOT NULL default '',
-> region_en varchar(255) NOT NULL default '',
-> PRIMARY KEY (region_id),
-> KEY area_ref (area_ref),
-> foreign key (area_ref) references laender_areas2(area_id)
-> on update cascade on delete cascade) type=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE laender_visited2 (
-> region_ref smallint(6) NOT NULL default '0',
-> user_ref smallint(6) NOT NULL default '0',
-> PRIMARY KEY (region_ref,user_ref),
-> key (region_ref),
-> foreign key (region_ref) references laender_regions2(region_id)
-> on update cascade on delete cascade,
-> key (user_ref),
-> foreign key (user_ref) references laender_user2(user_id)
-> on update cascade on delete cascade) type=innodb;
ERROR 1005: Can't create table './test/laender_visited2.frm' (errno: 150)
mysql> show innodb status\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030803 22:36:23 Error in foreign key constraint of table test/laender_visited2:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint:
foreign key (region_ref) references laender_regions2(region_id)
on update cascade on delete cascade,
key (user_ref),
foreign key (user_ref) references laender_user2(user_id)
on update cascade on delete cascade) type=innodb
See http://www.innodb.com/ibman.html for correct foreign key definition.
...
The reason is that region_ref in smallint, but region_id is int.
Regards,
Heikki
[3 Aug 2003 14:41]
MySQL Verification Team
Please update for 3.23.57. The last bk tree presents the same behavior reported by Heikki (error 1005).
[3 Aug 2003 15:04]
Marcus Frischherz
Thanks for the quick response :-) with that clear error message I would have figured it out myself.... I updated to 4.0.14 as suggested and with the correct definitions everything is fine now. Marcus
[4 Aug 2003 0:08]
Heikki Tuuri
Hi!
I was able to repeat the crash if I set default-character-set=latin2 in my.cnf:
heikki@hundin:~/mysql-max-3.23.48-pc-linux-gnu-i686/bin> mysqld
InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist:
InnoDB: a new database to be created!
030804 9:37:01 InnoDB: Setting file /home/heikki/data/ibdata1 size to 20 MB
InnoDB: Database physically writes the file full: wait...
030804 9:37:04 InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new
to be created
030804 9:37:04 InnoDB: Setting file /home/heikki/data/ibdata2 size to 124 MB
InnoDB: Database physically writes the file full: wait...
030804 9:37:31 InnoDB: Log file /home/heikki/data/ib_logfile0 did not exist: n
ew to be created
InnoDB: Setting log file /home/heikki/data/ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030804 9:37:32 InnoDB: Log file /home/heikki/data/ib_logfile1 did not exist: n
ew to be created
InnoDB: Setting log file /home/heikki/data/ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030804 9:37:34 InnoDB: Started
mysqld: ready for connections.
Version: '3.23.48-max-log' socket: '' port: -1073744700
InnoDB: foreign constraint creation failed;
InnoDB: internal error number 17
InnoDB: Assertion failure in thread 11275 in file dict0crea.c line 1241
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to mysql@lists.mysql.com
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail
key_buffer_size=33550336
record_buffer=1044480
sort_buffer=1048568
max_used_connections=0
max_connections=1000
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2076756 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x807bb5f
0x82b53ba
0x817154b
0x8174b98
0x8198b38
0x80d04b3
0x80c3fed
0x80b91b6
0x80d59f0
0x8082c1f
0x8086356
0x808188d
0x8080d5c
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instr
uctions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x84a8be8 = CREATE TABLE laender_visited2 (
region_ref smallint(6) NOT NULL default '0',
user_ref smallint(6) NOT NULL default '0',
PRIMARY KEY (region_ref,user_ref),
key (region_ref),
foreign key (region_ref) references laender_regions2(region_id)
on update cascade on delete cascade,
key (user_ref),
foreign key (user_ref) references laender_user2(user_id)
on update cascade on delete cascade) type=innodb
thd->thread_id=2
Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 2 did to cause the crash. In some cases of really
bad corruption, the values shown above may be invalid
The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash
heikki@hundin:~/mysql-max-3.23.48-pc-linux-gnu-i686/bin>
If you are using a non-latin1 charset, it is this bug fixed in 3.23.49:
Fixed a bug: if one defined a non-latin1 character set as
the default character set,
then definition of foreign key constraints could fail
in an assertion failure
in dict0crea.c, reporting an internal error 17.
Regards,
Heikki
Description: I have the following table definitions: mysql> CREATE TABLE laender_user2 ( user_id smallint(6) NOT NULL auto_increment, -> user_name varchar(32) NOT NULL default '', -> user_pass varchar(32) NOT NULL default '', -> lang varchar(255) NOT NULL default 'de', -> PRIMARY KEY (user_id), -> UNIQUE KEY user_name (user_name), -> KEY user_name_2 (user_name,user_pass) -> ) TYPE=innodb; Query OK, 0 rows affected (0.40 sec) mysql> CREATE TABLE laender_areas2 ( -> area_id smallint(6) NOT NULL auto_increment, -> area_en varchar(255) NOT NULL default '', -> area_de varchar(255) NOT NULL default '', -> PRIMARY KEY (area_id) -> ) TYPE=innodb; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE laender_regions2 ( region_id int(11) NOT NULL auto_increment, -> area_ref smallint(6) NOT NULL default '0', -> region_de varchar(255) NOT NULL default '', -> region_en varchar(255) NOT NULL default '', -> PRIMARY KEY (region_id), -> KEY area_ref (area_ref), -> foreign key (area_ref) references laender_areas2(area_id) -> on update cascade on delete cascade) type=innodb; Query OK, 0 rows affected (0.20 sec) mysql> CREATE TABLE laender_visited2 ( -> region_ref smallint(6) NOT NULL default '0', -> user_ref smallint(6) NOT NULL default '0', -> PRIMARY KEY (region_ref,user_ref), -> key (region_ref), -> foreign key (region_ref) references laender_regions2(region_id) -> on update cascade on delete cascade, -> key (user_ref), -> foreign key (user_ref) references laender_user2(user_id) -> on update cascade on delete cascade) type=innodb; upon the last create table mysqld crashes. Here the content of the mysqld.log: 030803 20:55:22 InnoDB: Started /usr/sbin/mysqld-max: Warten auf Verbindungen. InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 10251 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80379 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x80bedc5 0x40032a74 0x81acb17 0x81b014d 0x81d5dc1 0x810d2bf 0x8103421 0x80f8d69 0x81117a3 0x80c5e6a 0x80c88b8 0x80c48e4 0x80c3db7 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8488f40 = CREATE TABLE laender_visited2 ( region_ref smallint(6) NOT NULL default '0', user_ref smallint(6) NOT NULL default '0', key (region_ref), foreign key (region_ref) references laender_regions2(region_id) on update cascade on delete cascade, key (user_ref), foreign key (user_ref) references laender_user2(user_id) on update cascade on delete cascade) type=innodb thd->thread_id=1 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 1 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid Then I followed the instructions mentioned, and here is the content of the stack resolved: rori:/var/lib/mysql # resolve_stack_dump -s /usr/lib/mysql/mysqld.sym -n /tmp/mysqld.stack 0x80bedc5 close_thread_tables__FP3THDb + 33 0x40032a74 _end + 937844880 0x81acb17 gbk_order + 5077 0x81b014d gbk_order + 18955 0x81d5dc1 _end + 37853 0x810d2bf _nisam_search_last + 47 0x8103421 change_columns__7analyseRt4List1Z4Item + 449 0x80f8d69 read_field__9READ_INFO + 917 0x81117a3 _nisam_rec_check + 291 0x80c5e6a mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP13select_result + 4330 0x80c88b8 get_best_combination__FP4JOIN + 212 0x80c48e4 append_unescaped__FP6StringPCc + 624 0x80c3db7 closefrm__FP8st_table + 187 Now there exists a frm file for that table, but I cannot do anything with the table, it does not really exist within the InnoDB structure, I guess. How to repeat: I repeated it (deleting the frm file, and restarting the mysqld) and the same happened again.