MySQL Bugs: #51851: Server with SBR locks mutex twice on LOAD DATA into partitioned MyISAM table

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

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;

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

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.

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.

Discussed with Mattias and he think this is a pure
HANDLER bug. So reassigned the bug to Mattias as
Mattias recommended.

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.

Could consider declaring as HA_DATA_PARTITION* in TABLE_SHARE instead of
as void*

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.

pushed into mysql-trunk-bugfixing, mysql-next-mr-bugfixing and mysql-6.0-codebase-bugfixing

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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.

related bug?  http://bugs.mysql.com/46300

If so, seeing in 5.1 also.

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

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

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

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

pushed to mysql-5.1-bugteam, mysql-5.5-bugteam and mysql-trunk-merge

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.

Closed bug#43477 as a duplicate of this.

Bugfix also documented in the 5.1.52 changelog. Closed.

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)

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)

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)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

No new changelog entries required. Returning to Closed state.