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

[22 Jan 2004 1:32] Andrew Sitnikov
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'
[22 Jan 2004 4:08] Alexander Keremidarski
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
[22 Jan 2004 4:27] Andrew Sitnikov
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.
[22 Jan 2004 7:07] Guilhem Bichot
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.
[25 Jan 2004 7:57] Guilhem Bichot
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.
[29 Jan 2004 15: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

Additional info:

Fixed in ChangeSet@1.1692, 2004-01-30 00:05:34+01:00, guilhem@mysql.com
[8 Jun 2004 17:06] Guilhem Bichot
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