MySQL Bugs: #2477: Slave stop with error after master reboot if use HEAP tables

Bug #2477	Slave stop with error after master reboot if use HEAP tables
Submitted:	22 Jan 2004 1:32	Modified:	29 Jan 2004 15:07
Reporter:	Andrew Sitnikov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	4.0.17	OS:	Linux (Linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Slave stop with error after master reboot if use HEAP tables

How to repeat:
on master:

CREATE TABLE test ( id int not null default 0,  PRIMARY KEY (id)) TYPE=HEAP;
INSERT INTO test VALUES(1),(2),(3);

stop mysqld
start mysqld

on master:

DELETE FROM test;
INSERT INTO test VALUES(1),(2),(3);

slave will be stop with
Error 'Duplicate entry '1' for key 1'

Reason is obvious and I am not sure this can be considered a bug.

After reboot all HEAP tables are empty. If your application depends on specific data existing into HEAP table this data should be loaded there at startup. This is not only Replication related issue.

If empty table is Ok then simple TRUNCATE TABLE at server startup is enough. mysqld startup option 

init-file

is ideal for acvieving this goal.

Assiging to Guilhem for consideration

Yes, my app do DELETE FROM table; INSERT ... SELECT
but replication will stop, becouse binlog not have DELETE FROM table, becouse master table is empty.

Yes, the slave should notice that the HEAP table must be emptied. I am entering this into our TODO.
Until it is fixed, a workaround is, in your app:
do
TRUNCATE TABLE table; INSERT ... SELECT;
instead of
DELETE FROM table; INSERT ... SELECT;
Indeed TRUNCATE TABLE always gets into the binlog (even if the table was empty), which is unfortunately -as you pointed out- not the case for DELETE FROM (which I have put in discussion internally).
So with TRUNCATE TABLE you should be perfectly safe.

We are testing a patch to automatically write "DELETE FROM mytable" to the binlog everytime a HEAP table is used for the first time since mysqld started.

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:

Fixed in ChangeSet@1.1692, 2004-01-30 00:05:34+01:00, guilhem@mysql.com

Hi!

One last note:
> Yes, my app do DELETE FROM table; INSERT ... SELECT
> but replication will stop, becouse binlog not have DELETE FROM table, becouse
> master table is empty.

I have changed MySQL 4.1.3 (to be released soon) to write the DELETE FROM to the binlog even if the master's table was empty.

Regards,
Guilhem