MySQL Bugs: #40499: INSERT INTO t SELECT * FROM t does not work with myisam if binlogging is on

Bug #40499	INSERT INTO t SELECT * FROM t does not work with myisam if binlogging is on
Submitted:	4 Nov 2008 13:26	Modified:	19 Nov 2008 20:10
Reporter:	Sven Sandberg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	binlog, insert select, myisam

Description:
Run the following statements:

  CREATE TABLE t1 (a INT) ENGINE = MYISAM;
  INSERT INTO t1 VALUES (1);
  INSERT INTO t1 SELECT * FROM t1;

We would expect t1 to have two elements. However, t1 has only one element if all the following conditions hold:

 - Storage engine is MyISAM (can't reproduce with other storage engine).

 - Binlogging is on (can't reproduce with binlogging off).

 - binlog_format is STATEMENT or MIXED (can't reproduce if binlog_format = ROW).

 - Server version is 5.1 (can't reproduce in 5.0 or 6.0).

How to repeat:
source include/have_log_bin.inc;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 SELECT * FROM t1;
SELECT * FROM t1;

Can't repeat it on Mac OSX 10.5 and Linux RedHat Enterprise Server 5.1
using MyQL binaries for 5.1.29 and built binaries from mysql-5.1-bugteam (5.1.30).

I can't repeat too on Linux and Windows. I will ask to co-worker to try too.

I took a fresh branch of 5.1 on the same machine, compiled it the same way (BUILD/compile-pentium-debug-max) and can't repeat any more.

Still, it's funny that I could repeat it both in 5.1 main and 5.1-rpl.

Debug traces from one good run and one bad run

Attachment: traces.zip (application/zip, text), 427.23 KiB.

In case someone feels like debugging, I created debug traces for one good run and one bad run (see previous file attachment).
The files good.all and bad.all are the full debug trace files. The files good.1, good.2, ... are filtered: good.1 contains only thread 1 from good.all, etc; all hexadecimal numbers are masked (they are usually pointers); and paths that differ in the two runs are masked. The file diff contains the diff between good.1 and bad.1, the diff between good.2 and bad.2, etc, colorized for a unix terminal.

There are some differences, but I have no idea what they mean.

The first difference that jumps out from the traces is that concurrent insert is enabled in one build but disabled on the other.

I can reproduce in the "bad" build with concurrent_insert = 1 or concurrent_insert = 2 but not with concurrent_insert = 0. I can't reproduce in a good build even if I try all values for concurrent_insert.
I'm attaching new traces (same format), produced by the following test case:

source include/have_log_bin.inc;
SELECT @@session.binlog_format;
SET @@global.concurrent_insert = 1;
CREATE TABLE t1 (a INT) ENGINE = MYISAM;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 SELECT * FROM t1;
SELECT * FROM t1;
SELECT VERSION();
DROP TABLE t1;

Debug traces from one good run and one bad run, forcing concurrent_insert = 1

Attachment: traces.zip (application/zip, text), 427.22 KiB.

Something definitely went wrong with the build of the supplied server, the values of the thr_lock_type enumeration differ between the object files. For example, on the mysys/thr_lock.o object the enumeration does not contain the TL_READ_DEFAULT value while the sql/lock.o contains it.

Furthermore, changes to the header include/thr_lock.h (declares thr_lock_type) are not causing mysys files to be re-built, which will cause problems if the source was previously compiled.