Bug #51851 Server with SBR locks mutex twice on LOAD DATA into partitioned MyISAM table
Submitted: 9 Mar 2010 5:32 Modified: 16 Nov 2010 15:48
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1, 5.6.99-m4, 5.5.3-m3 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D1 (Critical) / R2 (Low) / E2 (Low)

[9 Mar 2010 5:32] Elena Stepanova
Description:
With the provided test case, server hangs on a non-debug version and crashes on a debug version:

safe_mutex: Trying to lock mutex at storage/myisam/ha_myisam.cc, line 1712, when the mutex was already locked at sql/ha_partition.h, line 948 in thread T@5
100309  8:23:30 - mysqld got signal 6 ;
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=1048576
read_buffer_size=131072
max_used_connections=4
max_threads=151
thread_count=4
connection_count=4
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60080 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x9eff950
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 = 0xb38ff314 thread_stack 0x30000
sbin/mysqld(my_print_stacktrace+0x32)[0x8641297]
sbin/mysqld(handle_segfault+0x34c)[0x8145080]
[0x4af400]
/lib/libc.so.6(abort+0x17a)[0xaca34a]
sbin/mysqld(safe_mutex_lock+0x10e)[0x86466cc]
sbin/mysqld[0x859ff32]
sbin/mysqld(_ZN9ha_myisam4infoEj+0x13c)[0x85a0448]
sbin/mysqld(_ZN9ha_myisam6repairEP3THDR17st_mi_check_paramb+0x88a)[0x85a3f26]
sbin/mysqld(_ZN9ha_myisam14enable_indexesEj+0x214)[0x85a4346]
sbin/mysqld(_ZN9ha_myisam15end_bulk_insertEv+0x7f)[0x85a1475]
sbin/mysqld(_ZN7handler18ha_end_bulk_insertEv+0x2b)[0x81c7fcd]
sbin/mysqld(_ZN12ha_partition15end_bulk_insertEv+0xc6)[0x8335c84]
sbin/mysqld(_ZN7handler18ha_end_bulk_insertEv+0x2b)[0x81c7fcd]
sbin/mysqld(_Z10mysql_loadP3THDP12sql_exchangeP10TABLE_LISTR4ListI4ItemES8_S8_15enum_duplicatesbb+0x1225)[0x846fdc9]
sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4070)[0x81dc4b2]
sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x243)[0x81e0c15]
sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x8b1)[0x81e2011]
sbin/mysqld(_Z10do_commandP3THD+0x268)[0x81e32cc]
sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x164)[0x82b0a8e]
sbin/mysqld(handle_one_connection+0x31)[0x82b0b59]
sbin/mysqld(_Z16pfs_spawn_threadPv+0x9f)[0x8614c33]
/lib/libpthread.so.0[0xc21ab5]
/lib/libc.so.6(clone+0x5e)[0xb7883e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9f095f0 = LOAD DATA LOCAL INFILE 'init_file.txt'
INTO TABLE t_celosia_ddl_partitions (name)
thd->thread_id=3
thd->killed=NOT_KILLED

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_statement.inc

perl;
open( INIT, ">init_file.txt");
print INIT "abcd\n";
close( INIT );
EOF

USE test;

CREATE TABLE t_celosia_ddl_partitions
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name TINYBLOB NOT NULL,
modified TIMESTAMP DEFAULT '0000-00-00 00:00:00',
INDEX namelocs (name(255))) ENGINE = MyISAM
PARTITION BY HASH(id) PARTITIONS 2;

LOAD DATA LOCAL INFILE 'init_file.txt'
INTO TABLE t_celosia_ddl_partitions (name);
COMMIT;

remove_file init_file.txt;
[23 Mar 2010 8:57] 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/104055

3001 Dao-Gang.Qu@sun.com	2010-03-23
      Bug #51851  Server with SBR locks mutex twice on LOAD DATA into
      partitioned MyISAM table
      
      In a multi-row insert statement like INSERT SELECT and LOAD DATA
      where the number of candidate rows to insert is not known in 
      advance we must hold a lock/mutex for the whole statement if we
      have statement based replication. Because the statement-based 
      binary log contains only the first generated value used by the
      statement, and slaves assumes all other generated values used by
      this statement were consecutive to this first one, we must 
      exclusively lock the generator until the statement is done. It 
      will be unlocked at the end of the statement by 
      ha_partition::release_auto_increment. During the process, the
      thread tries to lock it when updating the MyISAM table share info.
      So the mutex is locked twice on LOAD DATA into partitioned MyISAM
      table.
      
      To fix the problem, updating the MyISAM table share info before
      the lock is held by a multi-row insert statement.
     @ mysql-test/extra/rpl_tests/rpl_loaddata.test
        Added test case to verify whether server with SBR locks 
        mutex twice on LOAD DATA into partitioned MyISAM table.
     @ mysql-test/suite/ndb/r/ndb_load.result
        The test result is updated by the patch of bug#51851.
     @ mysql-test/suite/ndb/t/ndb_load.test
        Update the test to adapt the patch of Bug#51851.
     @ mysql-test/suite/rpl/r/rpl_loaddata.result
        Test result for the patch of Bug#51851.
     @ sql/sql_load.cc
[26 Mar 2010 6:04] 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/104405

3001 Dao-Gang.Qu@sun.com	2010-03-26
      Bug #51851  Server with SBR locks mutex twice on LOAD DATA into partitioned MyISAM table
      
      In a multi-row insert statement like INSERT SELECT and LOAD DATA
      where the number of candidate rows to insert is not known in
      advance we must hold a lock/mutex for the whole statement if we
      have statement based replication. Because the statement-based
      binary log contains only the first generated value used by the
      statement, and slaves assumes all other generated values used by
      this statement were consecutive to this first one, we must
      exclusively lock the generator until the statement is done. It
      will be unlocked at the end of the statement by
      ha_partition::release_auto_increment. During the process, the
      thread tries to lock it when updating the MyISAM table share info.
      So the mutex is locked twice on LOAD DATA into partitioned MyISAM
      table.
      
      To fix the problem, release the auto increment after all the rows
      are inserted and before the MyISAM table share info is updated.
[26 Mar 2010 11:26] Mattias Jonsson
The bug is that ha_data is used for storage engine specific data, but since partitioning uses storage engines itself there is a conflict here.

I will probably solve this by creating a partition specific data area.
[26 Mar 2010 11:26] Daogang Qu
Discussed with Mattias and he think this is a pure
HANDLER bug. So reassigned the bug to Mattias as
Mattias recommended.
[30 Mar 2010 21:23] 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/104667

3008 Mattias Jonsson	2010-03-30
      Bug#51851: Server with SBR locks mutex twice on LOAD DATA
      into partitioned MyISAM table
      
      Problem was that the ha_data structure was introduced in 5.1
      and only used for partitioning first, but with the intention
      of be of use for others engines as well, and when used by other
      engines it would clash if it also was partitioned.
      
      Solution is to move the partitioning specific data to a separate
      structure, with its own mutex (which is used for auto_increment).
      
      Also did rename PARTITION_INFO to PARTITION_STATS since there
      already exist a class named partition_info, also cleaned up
      some related variables.
     @ mysql-test/r/partition_binlog_stmt.result
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        New result file
     @ mysql-test/t/partition_binlog_stmt.test
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        New result file
     @ sql/ha_ndbcluster.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
     @ sql/ha_ndbcluster.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
     @ sql/ha_partition.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed the partitioning engines use of ha_data in
        TABLE_SHARE and added ha_part_data instead, since
        they collide if used in the same time.
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
        
        Removed some dead code.
     @ sql/ha_partition.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed some dead code.
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
        
        Removed the partitioning engines use of ha_data in
        TABLE_SHARE and added ha_part_data instead, since
        they collide if used in the same time.
     @ sql/handler.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
     @ sql/handler.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
     @ sql/mysql_priv.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed the partitioning engines use of ha_data in
        TABLE_SHARE and added ha_part_data instead, since
        they collide if used in the same time.
        
        Added key_PARTITION_LOCK_auto_inc for instrumentation.
     @ sql/mysqld.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed the partitioning engines use of ha_data in
        TABLE_SHARE and added ha_part_data instead, since
        they collide if used in the same time.
        
        Added key_PARTITION_LOCK_auto_inc for instrumentation.
     @ sql/partition_info.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed part_state* since it was not in use.
     @ sql/sql_partition.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed part_state* since it was not in use.
     @ sql/sql_partition.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Cleaned up old commented out code.
        
        Removed part_state* since it was not in use.
     @ sql/sql_show.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Rename of PARTITION_INFO to PARTITION_STATS to better
        match the use (and there is also a class named
        partition_info...)
        
        Renamed partition_info to partition_info_str, since
        partition_info is a name of a class.
     @ sql/sql_table.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Renamed partition_info to partition_info_str, since
        partition_info is a name of a class.
     @ sql/table.cc
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed the partitioning engines use of ha_data in
        TABLE_SHARE and added ha_part_data instead, since
        they collide if used in the same time.
        
        Renamed partition_info to partition_info_str, since
        partition_info is a name of a class.
        
        removed part_state* since it was not in use.
     @ sql/table.h
        Bug#51851: Server with SBR locks mutex twice on LOAD DATA
        into partitioned MyISAM table
        
        Removed the partitioning engines use of ha_data in
        TABLE_SHARE and added ha_part_data instead, since
        they collide if used in the same time.
        
        Renamed partition_info to partition_info_str, since
        partition_info is a name of a class.
        
        removed part_state* since it was not in use.
[29 Apr 2010 10:29] Mikael Ronström
Could consider declaring as HA_DATA_PARTITION* in TABLE_SHARE instead of
as void*
[24 May 2010 12:53] 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/109057

3057 Mattias Jonsson	2010-05-24 [merge]
      Merge of bug#51851.
      
      Also moved HA_DATA_PARTITION from ha_partition.cc to table.h.
[25 May 2010 7:18] Mattias Jonsson
pushed into mysql-trunk-bugfixing, mysql-next-mr-bugfixing and mysql-6.0-codebase-bugfixing
[15 Jun 2010 8:14] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[5 Jul 2010 16:58] Jon Stephens
Documented in the 5.5.5 changelog as follows:

      Attempting to execute LOAD DATA on a partitioned MyISAM table while using
      statement-based logging mode caused the master to hang or crash.

Set to Needs Merge.
[12 Aug 2010 0:13] Chris Ferraro
related bug?  http://bugs.mysql.com/46300

If so, seeing in 5.1 also.
[2 Sep 2010 15:56] 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/117437

3498 Mattias Jonsson	2010-09-02
      Bug#51851: Server with SBR locks mutex twice on
      LOAD DATA into partitioned MyISAM table
      
      Problem was that both partitioning and myisam
      used the same table_share->mutex for different protections
      (auto inc and repair).
      
      Solved by adding a specific mutex for the partitioning
      auto_increment.
      
      This is a 5.1 ONLY patch, already fixed in 5.5+.
[1 Oct 2010 11:36] 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/119631

3498 Mattias Jonsson	2010-10-01
      Bug#51851: Server with SBR locks mutex twice on
      LOAD DATA into partitioned MyISAM table
      
      Problem was that both partitioning and myisam
      used the same table_share->mutex for different protections
      (auto inc and repair).
      
      Solved by adding a specific mutex for the partitioning
      auto_increment.
      
      Also adding destroying the ha_data structure in
      free_table_share (which is to be propagated
      into 5.5).
      
      This is a 5.1 ONLY patch, already fixed in 5.5+.
[1 Oct 2010 11:40] 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/119634

3498 Mattias Jonsson	2010-10-01
      Bug#51851: Server with SBR locks mutex twice on
      LOAD DATA into partitioned MyISAM table
      
      Problem was that both partitioning and myisam
      used the same table_share->mutex for different protections
      (auto inc and repair).
      
      Solved by adding a specific mutex for the partitioning
      auto_increment.
      
      Also adding destroying the ha_data structure in
      free_table_share (which is to be propagated
      into 5.5).
      
      This is a 5.1 ONLY patch, already fixed in 5.5+.
[1 Oct 2010 12:19] 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/119653

3213 Mattias Jonsson	2010-10-01 [merge]
      Manual merge of bug#51851 from mysql-5.1-bugteam into mysql-5.5-bugteam
[1 Oct 2010 19:42] Mattias Jonsson
pushed to mysql-5.1-bugteam, mysql-5.5-bugteam and mysql-trunk-merge
[4 Oct 2010 12:56] Jon Stephens
Set Need Merge status, as this is already documented in 5.5, and it appears that we're still waiting for a push to 5.1.
[5 Oct 2010 14:46] Mattias Jonsson
Closed bug#43477 as a duplicate of this.
[6 Oct 2010 12:31] Jon Stephens
Bugfix also documented in the 5.1.52 changelog. Closed.
[1 Nov 2010 19:01] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)
[9 Nov 2010 19:45] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[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)
[16 Nov 2010 13:46] Jon Stephens
No new changelog entries required. Returning to Closed state.