SQL result is different from Aurora MySQL Read/Write

Question

Hi experts,
In last month we upgrade Aurora MySQL cluster engine version from 5.7.mysql_aurora.2.11.1 to 8.0.mysql_aurora.3.05.2. During the regression test we meet one strange issue, one for a SQL make different result in Read and Write instance (result from Write is incorrect).
According to the optimizer trace and explain analyze report, the differences between Read/Write is about temp table. And we notice the temp table storage engine is different by default in them (Write is InnoDB and Read in MyISAM).
Do you know if the different engine in Aurora will affect the SQL output? Or is there any other issues need to attention?

Thank you & best regards,

Didier Durand · Answer

Hi,

Yes, there are known differences between myIsam and InnoDB: see https://medium.com/@manish90/innodb-vs-myisam-in-mysql-d31710ca9f94

See artcile: differences in Locking, ACID compliance and Concurrency may generate different (at least transient) results on same query while the Read Instance is being synchronized (i.e. written) by the Write Instance

Best,

DIdier

SQL result is different from Aurora MySQL Read/Write

Add your answer

Relevant content