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:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.30, 5.1.45 OS:Microsoft Windows ((macosx, windows))
Assigned to: Magne Mæhre
Tags: regression
Triage: Triaged: D1 (Critical) / R2 (Low) / E2 (Low)

[26 Aug 2009 16:23] Shane Bester
Description:
When adding/dropping foreign keys and lower_case_table_names=2, server can crash.  This happens on file-systems with case insensitive names.

Version: '5.1.39-debug'  socket: ''  port: 3306  Source distribution
090826 18:15:14 [ERROR] Table xy/activewfset contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mi

090826 18:15:14 [ERROR] Innodb could not find key n:o 2 with name FK2439AFAD7BCE1348 from dict cache for table xy/xxxxxxx

mysqld.exe!dict_index_contains_col_or_prefix()[dict0dict.c:543]
mysqld.exe!build_template()[ha_innodb.cc:3441]
mysqld.exe!ha_innobase::index_read()[ha_innodb.cc:4506]
mysqld.exe!ha_innobase::index_first()[ha_innodb.cc:4809]
mysqld.exe!join_read_first()[sql_select.cc:11855]
mysqld.exe!sub_select()[sql_select.cc:11160]
mysqld.exe!do_select()[sql_select.cc:10917]
mysqld.exe!JOIN::exec()[sql_select.cc:2213]
mysqld.exe!mysql_select()[sql_select.cc:2404]
mysqld.exe!handle_select()[sql_select.cc:268]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5011]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2206]
mysqld.exe!mysql_parse()[sql_parse.cc:5931]
mysqld.exe!dispatch_command()[sql_parse.cc:1213]
mysqld.exe!do_command()[sql_parse.cc:854]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:87]
mysqld.exe!_callthreadstart()[thread.c:293]

didn't affect 5.0 or 6.0.

How to repeat:
start server with --lower-case-table-names=2 and import the privately attached dumpfile.  Make sure lower-case-table-names is set at 2.
[26 Aug 2009 17:09] Valerii 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] Shane Bester
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)