MySQL Bugs: #42410: falcon_compare_self fails due to row number mismatch for update statement

Bug #42410	falcon_compare_self fails due to row number mismatch for update statement
Submitted:	28 Jan 2009 10:39	Modified:	28 Jan 2013 11:46
Reporter:	Olav Sandstå	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0-falcon-team	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	F_ISOLATION

Description:
The falcon_compare_self test failed with the following written to the log file:

# 00:27:44 Started periodic reporting process...
# 00:27:44 Query: INSERT IGNORE INTO `table2_falcon` ( `bigint_key` , `bigint_unsigned` , `bigint_key` ) VALUES ( -2.29212367248949e+17 , '20011113133416' , 'pmefpgkgrfcgoqasuxylifobfwemglgfoskunhchembadfworcavkenjtmysptvm' ) failed: 1110 Column '%s' specified twice. Further errors of this kind will be suppressed.
# 00:28:45 Query: UPDATE `table0_falcon` SET `year` = 1.04253018971528e+19 WHERE `double_unsigned_key` <= 1 failed: affected_rows mismatch between servers (54 vs. 53)
# 00:28:50 Killing periodic reporting process with pid 12547...
# 00:28:55 Test completed with failure status 32.

There is two errors in this report here:

1. A query fails with the error message: 1110 Column '%s' specified twice. Further errors of this kind will be suppressed. 

   I am not sure if this is an error or expected behavior from the test?

2. A UPDATE ... WHERE ... statement fails with the error message "affected_rows mismatch between servers (54 vs. 53)". 

How to repeat:
This crash was seen with the falcon_compare_self test. I have only seen it once so I do not know how easy it is to reproduce it.

Suggested fix:
Given identical databases, a UPDATE .. WHERE statement should update the same number of rows if all statements are run sequentially.

This bug is not about a crash, but about two identical Falcon servers returning different values. The first error "column specified twice" is due to the random nature of the test and should be ignored. The issue is when the test reports that a SELECT returned different rows or that an UPDATE matched a different number of rows.

The issue has so far been observed on various queries, including ones that have a completely valid WHERE predicate (e.g. a floating-point key is compared to an integer constant). It appears that the WHERE must use a key for this bug to show up.

See previous similar bug uncovered from the same test:

http://bugs.mysql.com/bug.php?id=40112

A similar bug has been reported earlier. See Bug#40112 for some very useful comments.

I am taking over this bug. Until proven otherwise, this does not appear to be a transactional isolation issue. The test case contains LIMIT, as well as queries that assign say a datetime value to an enum column.

If I am able to reproduce the issue with integers only and no limit, then that would mean that there is an isolation bug. So far, I have been unsuccessfull.

Bug #44092 was filed to track the FLOAT issues exposed by the falcon_compare_self.

Closed since we don't support Falcon anymore.