MySQL Bugs: #43477: Server hangs with table general log, statement binlog format and partitions

Bug #43477	Server hangs with table general log, statement binlog format and partitions
Submitted:	7 Mar 2009 20:09	Modified:	5 Oct 2010 14:46
Reporter:	Elena Stepanova	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.29, 5.1.32, 5.1.33	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Server hangs when several connections simultaneously insert into the same table. 

dbx output from the process (Ver 5.1.33-debug for sun-solaris2.10 on sparc (Source distribution):

(dbx) where
current thread: t@1
  [1] __pollsys(0xffffffff7fff8cb0, 0x2, 0x0, 0x0, 0x0, 0x0), at 0xffffffff7ded346c
  [2] _pollsys(0xffffffff7fff8cb0, 0x2, 0x0, 0x0, 0x0, 0x0), at 0xffffffff7dec5780
  [3] _pselect(0x2, 0xffffffff7fff8cb0, 0xffffffff7dff4580, 0xffffffff7dff4580, 0x0, 0x0), at 0xffffffff7de72bd8
  [4] _select(0x9, 0xffffffff7fffaf68, 0x0, 0x0, 0x0, 0x2), at 0xffffffff7de72f7c
=>[5] handle_connections_sockets(arg = (nil)), line 4931 in "mysqld.cc"
  [6] main(argc = 34, argv = 0xffffffff7fffd188), line 4430 in "mysqld.cc"

Below are the conditions which seem to matter, as I could not reproduce the problem without having all of them:

- the table where inserts are done has partitions (at least one, tried with partition by hash);
- there is a trigger after insert on the table, which inserts into another table;
- binary log is switched on, and binlog_format is set to STATEMENT;
- logging to TABLE is switched on (log_output is TABLE or TABLE and FILE);
- general_log is switched on.

Possible workaround is to avoid using table logging if other conditions listed above are strictly necessary.

How to repeat:
 perl ./mysql-test-run.pl --mysqld=--log-output=TABLE --mysqld=--general-log --do-test=server_hangup 

The test case is attached. It works for 5.1.30 and higher; for lower version it might need some modifications, but the problem itself is reproducible on 5.1.29 as well.

Please note the variable 
let $con_number= 10;
at the start of the test. Depending on the machine, you might need to increase the value to reproduce the problem. I had a range from 4 to 30 on different machines.

Test file

Attachment: server_hangup.test (application/octet-stream, text), 1.28 KiB.

The minimal command to run the test is 

perl ./mysql-test-run.pl --mysqld=--log-output=TABLE --mysqld=--general-log --do-test=server_hangup

binlog_format is set to STATEMENT in the test itself.

Related to bug#51851. The problem is that partitioning holds the table_share->mutex to avoid giving the same auto_increment values when in statement based replication.

Solution would be to use the patch given in bug#51851 also in 5.1 (i.e break out the partitioning specific data from ha_data into ha_part_data and add a destroy function for that too). This will also resolve the problem that table_share->ha_data cannot be used by engines if it is partitioned.

Closing as duplicate of bug#51851. (tested after that was pushed to 51-bugteam).