MySQL Bugs: #4971: CREATE TABLE ... TYPE=HEAP SELECT ... stops slave (wrong DELETE in binlog)

Bug #4971	CREATE TABLE ... TYPE=HEAP SELECT ... stops slave (wrong DELETE in binlog)
Submitted:	10 Aug 2004 9:44	Modified:	19 Aug 2004 0:49
Reporter:	Christain Groesswang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Other (OpenBSD 3.5)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
When I create an HASH-Table on the Master, the slave stops with an error because the create and select are in the wrong order in the binlog.

Here an example:
> CREATE TABLE test TYPE=HEAP
> SELECT AID FROM ADRESSEN;
> SELECT COUNT(AID) FROM test;
> DROP TABLE test;

on the slave it looks like this
> SELECT COUNT(AID) FROM test;
> CREATE TABLE test TYPE=HEAP
> SELECT AID FROM ADRESSEN;
> DROP TABLE test;

because it stops with:

040807 12:09:34  Slave: Error 'Table 'WEB_BZ.test' doesn't exist' on query 
'DELETE FROM `WEB_BZ`.`test`'. Default database: 'WEB_BZ', Error_code: 1146

When I create the table manually on the slave and start it again, the next error is:
040807 12:10:04  Slave: Error 'Table 'test' already exists' on query 'CREATE TABLE test TYPE=HEAPSELECT AID FROM ADRESSEN'. Default database: 'WEB_BZ', Error_code: 1050

reproduceable!
Both server are mysql 4.0.18 installed from the ports-tree on OpenBSD3.5
Hardware is P4/2.4 with 1GB on Master, Duron/1000 with 1G on slave.

 

How to repeat:
Same procedure as above

Suggested fix:
Check the order in binlog or Do not replicate HASH-Tables.

Hi,
You wrote:
> the create and select are in the wrong order in the binlog
> on the slave it looks like this
> SELECT COUNT(AID) FROM test;
> CREATE TABLE test TYPE=HEAP
> SELECT AID FROM ADRESSEN;
> DROP TABLE test;
But a SELECT is never written in the binlog. You can inspect the content of your binary log with the mysqlbinlog utility to check that; you won't find the above SELECT COUNT(*).
But! you still found a bug. I tested and in the master's binlog there is:
| gbichot2-bin.001 | 79  | Query      | 1         | 79           | use `test`; DELETE FROM `test`.`hea`                     |
| gbichot2-bin.001 | 138 | Query      | 1         | 138          | use `test`; create table hea type=heap select * from a
The query which fails is the DELETE FROM; it's automatically written to the binlog every time a HEAP table is opened for the first time since server startup (which ensures that if you restart the master, the slave will know the HEAP table is now empty). Here it's a bug that this DELETE gets written before the CREATE TABLE. It's this DELETE which your slave complains about (look in the error messages you posted).
Thank you for your bug report.

Sorry, was a wrong description. The Error log says it right.

The problem is not the select, it is the delete which comes before the Create

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

Additional info:

ChangeSet@1.1964.2.1, 2004-08-19 00:29:11+02:00, guilhem@mysql.com