MySQL Bugs: #45151: Binary Log mixed mode restore reports duplicates and missing fields.

Bug #45151	Binary Log mixed mode restore reports duplicates and missing fields.
Submitted:	28 May 2009 8:28	Modified:	28 Jun 2009 9:28
Reporter:	Ilias Ber	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.34	OS:	FreeBSD
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, duplicate, log, mixed, mode, restore

Description:
I tried doing a point in time restore by using first a dump that was taken before the database started using binary log. At some points I started getting errors about duplicates on primary keys and also missing fields on different tables.
I had binary log set to MIXED mode. I am using mixed myisam and innodb tables in the same database.

How to repeat:
Create a mysql dump of a database with mixed myisam and innodb tables. Start using binary log for the first time on the database after the mysql dump in MIXED mode. After collecting 5-6GB of binary logs try to do a point in time restore.
First insert the dump and then the binary logs up to a point of preference using end_datetime.

Thank you for the report.

Please provide repeatable test case showing this is a bug in MySQL code and not just wrong usage.

For example, this duplicate key error can occur if one started recovery while increment property for tables set to wrong value.

Hello,

Forgot to mention  duplicates were incurring on innodb tables that were not using any kind of auto-increment. Could you please inform me as to how statements are logged when ROW mode is automatically chosen by mysql?

Thanks,

Thank you for the feedback.

You can read about replication formats at http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html, especially at http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html When MIXED mode is using mysqld chooses ROW format in case it statement is not safe for STATEMENT format and STATEMENT otherwise.

What I need from you as repeatable test case:

1. Initial dump
2. Step-by-step description how you started recovery
3. Minimum set of binary logs problem is observable with.

Would be good if you could minimize it to minimum possible test case. For example, one table.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".