Bug #873 In transaction, INSERT to non-trans table is written too early to binlog

• Submitted: 18 Jul 2003 10:42
• Modified: 22 Aug 2003 7:07
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0
• Assigned to: Guilhem Bichot
• CPU Architecture: Any

[18 Jul 2003 10:42] Guilhem Bichot

Description:
reported by an important customer.

How to repeat:
create table t(a int);
insert into t values(1);
SET AUTOCOMMIT=0;
create temporary table u(a int) type=myisam;
insert into t select * from u;

Then in the binlog you will see only :
insert into u select * from t;

Which will break the slave ("unknown table u").

If you COMMIT, in the binlog you will have:
insert into t select * from u;
BEGIN;
create temporary table u(a int);
COMMIT;

(wrong order).
If temp table is InnoDB, no problem (as the insert is not immediately written to the binlog).
If the temp table is not temp, no problem (as the CREATE TABLE is written immediately).

Suggested fix:
After discussing this with Heikki, we agree that we should write the CREATE TEMPORARY TABLE immediately to the binlog in all cases.

[21 Jul 2003 2:02] Guilhem Bichot

Presently, CREATE TEMPORARY TABLE is written only after COMMIT.
On the opposite, DROP TEMPORARY TABLE commits the transaction and writes to the binlog.
After consulting Heikki, now consulting Monty.

[4 Aug 2003 1:20] Guilhem Bichot

Monty,
This is for you until you take a decision of what "fixing it" means.

[19 Aug 2003 1:05] Guilhem Bichot

I am changing the synopsis from
"CREATE TEMPORARY TABLE does not go immediately to binlog if AUTOCOMMIT=0"
to
"In transaction, INSERT to non-trans table is written too early to binlog"
because that's the real problem. That is, the problem has nothing to do with CREATE TEMPORARY TABLE, see this example:
create table v(a int) type=innodb;
create table u(a int) type=myisam;
begin;
insert into v values(1);
insert into u select * from v;
commit;
then in binlog you have:
insert into u select * from v;
begin;
insert into v values(1);
commit;
which is wrong and will break replication.
This is in fact case#2 of BUG#333.
So the solution is that the INSERT...SELECT to the non-transactional table (u) is written to the binlog cache, like other queries. There is no sense in writing it immediately.

[22 Aug 2003 7:07] Guilhem Bichot

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

ChangeSet@1.1576.4.1, 2003-08-22 15:39:24+02:00, [e-mail]
Back to the customer's testcase:
create table t(a int);
reset master;
SET AUTOCOMMIT=0;
create temporary table u(a int) type=myisam;
insert into t select * from u;
COMMIT or ROLLBACK;
Now in the binlog you will see nothing. Because t was not updated, so there's nothing to log.
then if you do
insert into u values(1);
insert into t select * from u;
COMMIT or ROLLBACK;
Then you will see:
BEGIN
create temporary table u(a int) type=myisam;
insert into t select * from u;
insert into u values(1);
insert into t select * from u;
COMMIT or ROLLBACK;
which is a good order and suitable for replication.