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 11:39	Modified:	2 Apr 16:03
Reporter:	Olav Sandstaa
Status:	Analyzing
Category:	Server: Falcon	Severity:	S2 (Serious)
Version:	6.0-falcon-team	OS:	Any
Assigned to:	Philip Stoev	Target Version:	6.0-beta
Tags:	F_ISOLATION
Triage:	Triaged: D2 (Serious)

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.