| Bug #46941 | crash with lower_case_table_names=2 and foreign key data dictionary confusion | ||
|---|---|---|---|
| Submitted: | 26 Aug 2009 16:23 | Modified: | 20 Nov 2010 17:07 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.1.30, 5.1.45 | OS: | Windows ((macosx, windows)) |
| Assigned to: | Magne Mæhre | CPU Architecture: | Any |
| Tags: | regression | ||
[26 Aug 2009 16:23]
Shane Bester
[26 Aug 2009 17:09]
Valeriy Kravchuk
Verified with a bit smaller test case (will attach later) on Mac OS X with latest 5.1.39 from bzr. In the error log I've got: Version: '5.1.39-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution 090826 20:05:46 [ERROR] Table xy/activewfset contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html 090826 20:05:46 [ERROR] Innodb could not find key n:o 2 with name FK2439AFAD7BCE1348 from dict cache for table xy/activewfset 090826 20:05:46 - mysqld got signal 10 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against 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=8384512 read_buffer_size=131072 max_used_connections=1 max_threads=151 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337716 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x1123618 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_bottom = 0xb026af64 thread_stack 0x30000 0 mysqld 0x0056a953 my_print_stacktrace + 44 1 mysqld 0x000f82c3 handle_segfault + 853 2 libSystem.B.dylib 0x940472bb _sigtramp + 43 3 ??? 0xffffffff 0x0 + 4294967295 4 mysqld 0x0041be79 _ZN11ha_innobase21store_key_val_for_rowEjPcjPKh + 1959 5 mysqld 0x0041d827 _ZN11ha_innobase10index_readEPhPKhj16ha_rkey_function + 253 6 mysqld 0x0041e2e1 _ZN11ha_innobase11index_firstEPh + 143 7 mysqld 0x0019078e _Z20init_read_record_seqP13st_join_table + 658 8 mysqld 0x0018cdff _Z10sub_selectP4JOINP13st_join_tableb + 273 9 mysqld 0x0018d23c _Z10sub_selectP4JOINP13st_join_tableb + 1358 10 mysqld 0x001a5810 _ZN4JOIN4execEv + 9078 11 mysqld 0x001a5caa _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 866 12 mysqld 0x001a602e _Z13handle_selectP3THDP6st_lexP13select_resultm + 558 13 mysqld 0x00109ee6 _Z20prepare_schema_tableP3THDP6st_lexP11Table_ident18enum_schema_tables + 2096 14 mysqld 0x00110954 _Z21mysql_execute_commandP3THD + 2896 15 mysqld 0x0011a9e4 _Z11mysql_parseP3THDPKcjPS2_ + 580 16 mysqld 0x0011b7a2 _Z16dispatch_command19enum_server_commandP3THDPcj + 3080 17 mysqld 0x0011cab6 _Z10do_commandP3THD + 654 18 mysqld 0x00107b64 handle_one_connection + 366 19 libSystem.B.dylib 0x9400c095 _pthread_start + 321 20 libSystem.B.dylib 0x9400bf52 thread_start + 34 Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x1125028 = select * from XY.ActiveWFSet limit 1 thd->thread_id=12 thd->killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 090826 20:05:46 mysqld_safe mysqld restarted 090826 20:05:47 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.1/var/ is case insensitive ...
[26 Aug 2009 20:14]
Calvin Sun
I am able to reproduce the crash on Windows XP. The call stack: 007808CA mysqld.exe!dict_index_contains_col_or_prefix()[dict0dict.c:543] 00750A91 mysqld.exe!build_template()[ha_innodb.cc:3370] 00751DA2 mysqld.exe!ha_innobase::index_read()[ha_innodb.cc:4435] 007528CC mysqld.exe!ha_innobase::index_first()[ha_innodb.cc:4738] 00627429 mysqld.exe!join_read_first()[sql_select.cc:11793] 00625A25 mysqld.exe!sub_select()[sql_select.cc:11098] 0062556F mysqld.exe!do_select()[sql_select.cc:10855] 00610905 mysqld.exe!JOIN::exec()[sql_select.cc:2199] 00610FF1 mysqld.exe!mysql_select()[sql_select.cc:2380] 0060A90D mysqld.exe!handle_select()[sql_select.cc:268] 0059869F mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4911] 00591008 mysqld.exe!mysql_execute_command()[sql_parse.cc:2204] 0059A801 mysqld.exe!mysql_parse()[sql_parse.cc:5831] 0058F05E mysqld.exe!dispatch_command()[sql_parse.cc:1216] 0058E6D4 mysqld.exe!do_command()[sql_parse.cc:857] 0049B086 mysqld.exe!handle_one_connection()[sql_connect.cc:1115] 0088DED6 mysqld.exe!pthread_start()[my_winthread.c:85] 00869EE1 mysqld.exe!_callthreadstart()[thread.c:293] 00869E87 mysqld.exe!_threadstart()[thread.c:277] 7C80B713 kernel32.dll!GetModuleFileNameA()
[10 Sep 2009 17:50]
Calvin Sun
This appears to be a server bug. Somehow the table cache is messed up when lower_case_table_names=2. I can repeat the crash when table_open_cache is 64 (default). But the crash goes away after increasing the table_open_cache to 256 (or even 100). Another indicator is the error such as: 090909 18:44:19 [ERROR] Table xy/activewfset contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? When this happens (just before the crash), table->s->keys = 4 while ib_table->indexes->count = 2. 2 (in InnoDB) is the right number. 4 is the number prior to drop database. So, the server reuses the old slot that has wrong info.
[4 Jan 2010 9:47]
Kristofer Pettersson
I suggest that it is documented behaviour for InnoDB (more or less since we say what must be done): See http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_lower_case_tabl... "If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase. " However, the default value on Windows is '2': "[..]If this variable is not set at startup and the file system on which the data directory is located does not have case-sensitive file names, MySQL automatically sets lower_case_table_names to 2." Maybe this simply should be changed since we favour InnoDB?
[19 Apr 2010 11:56]
MySQL Verification Team
problem still exists in 5.1.45..
[21 Apr 2010 19:37]
James Day
Kristofer, "should" means a recommendation, not a requirement. However it's my view that for the 5.5 server series we should change the default lower_case_table_names value to 1 to cause all names to be stored in lower case by default on all platforms. That would eliminate the potential mixed case issues and improve portability across servers for users copying databases around with OS utilities. I don't think that there are many users who really do have two tables in the same database with different case as the only difference between them, though I'm sure there are some and that some will be many thousands. There's a related background discussion whether the case should be lower or upper but that's moot for this particular issue.
[22 Apr 2010 11:52]
James Day
Workaround: set table_cache (5.0 and earlier) or table_open_cache (5.1 and later) so that Opened_tables in SHOW GLOBAL STATUS is just high enough so that it does not regularly increase after your server has been running for a few days. Don't set it much higher than required. Values up to 100,000 and higher are acceptable on Linux systems with workloads that make it necessary. Windows systems in MySQL 5.1 and earlier are limited to no more than a thousand or so due to the use of the posix compatibility layer. If you are using InnoDB then for performance reasons you should also ensure that innodb_open_files is at least as high as the number of InnoDB tablespaces that you are using. This is most likely to need adjustment if you are using innodb_file_per_table. Values in the hundred thousand range and higher are acceptable if you use that many ibd files but you should not set it much higher than required. Windows systems are not restricted in how high this can be set.
[1 Oct 2010 8:59]
Jon Olav Hauglid
Bug is repeatable on Mac OSX using 5.1, but not repeatable when using 5.5.
[7 Oct 2010 14:38]
Jon Olav Hauglid
The test case passes on Mac OSX with the committed patch.
[19 Oct 2010 10:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121099 3536 Magne Mahre 2010-10-19 Bug #46941 crash with lower_case_table_names=2 and foreign key data dictionary confusion On file systems with case insensitive file names, and lower_case_table_names set to '2', the server could crash due to a table definition cache inconsistency. This is the default setting on MacOSX, but may also be set and used on MS Windows. The bug is caused by using two different strategies for creating the hash key for the table definition cache, resulting in failure to look up an entry which is present in the cache, or failure to delete an existing entry. One strategy was to use the real table name (with case preserved), and the other to use a normalized table name (i.e a lower case version). This is manifested in two cases. One is during 'DROP DATABASE', where all known files are removed. The removal from the table definition cache is done via a generated list of TABLE_LIST with keys (wrongly) created using the case preserved name. The other is during CREATE TABLE, where the cache lookup is also (wrongly) based on the case preserved name. The fix was to use only the normalized table name when creating hash keys. @ sql/sql_db.cc Normalize table name (i.e lower case it) @ sql/sql_table.cc table_name contains the normalized name alias contains the real table name
[13 Nov 2010 16:07]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:36]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[18 Nov 2010 15:54]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[20 Nov 2010 17:07]
Paul DuBois
Noted in 5.1.53, 5.5.8 changelogs. On file systems with case insensitive file names, and lower_case_table_names=2, the server could crash due to a table definition cache inconsistency.
[16 Dec 2010 22:30]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
