MySQL Bugs: #59818: The return of select in db is the last version Data in the db

Bug #59818	The return of select in db is the last version Data in the db
Submitted:	30 Jan 2011 8:31	Modified:	14 Nov 2011 7:30
Reporter:	Denny mingjian	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.x	OS:	Linux (RedHat)
Assigned to:		CPU Architecture:	Any
Tags:	binary log, consistency, replication

Description:
My program extract those SQLs in mysql-binlog and get my own select SQL to get the changed rows in database. But Sometimes I only get the last version row, not the latest!

My project is simple:
I want to replicate the increace rows from my mysql database to filesystem. I need get these rows instant when database changed.

So I get MYSQL struct result from "cli_safe_read(&mysql)" and I get get the binary log data. Then I extract these data to filter the SQL 'select/delete/insert'. At last, I get the PK of the row from extract the SQL, and I can select in my database use the PK to get those rows I wanted.

There is about 1 million rows changed per day, and in most cases it run successfully. But I can get 1 row wrong or less per day.

The wrong rows are the last version rows. I think it is very serious because it can cause lost data! 

For example, I get a row which its event time is "2011-01-29 09:00:00" and I get the result by selecting in the database is "2011-01-20 09:00:00", which is the last version row changed. So the latest version row of "2011-01-29 09:00:00" is lost!

By the way, I used the following jars to create jdbc select:
mysql-connector-java-5.1.13.jar
commons-pool-1.5.4.jar
commons-dbcp-1.4.jar

How to repeat:
I don't kown how to repeat it in test demo because I don't know how it happens.

But it can repeat in my program. My database changed about 1 million per day, and it happened once or less per day.

One idea: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_t...

Peter
(not a MySQL person)

no! sorry!

yes .. maybe! :-)

mysql> show variables like "innodb_flush_log_at_trx_commit";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     | 
+--------------------------------+-------+
1 row in set (0.00 sec)

Do you ever delete data in your application? Is it possible that while you are parsing the log and preparing SELECT, data row with specific PK value is already deleted?

No. Because I can get the correct row data after I found this error happened!

Please, check if this problem still happens with recent server versions, 5.1.59 and/or 5.5.16.

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".