| Bug #99388 | Mysql Cluster 8 - “Jumpy” Auto Increment Values | ||
|---|---|---|---|
| Submitted: | 28 Apr 2020 12:15 | Modified: | 28 Apr 2020 19:43 |
| Reporter: | Daniel Hope | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | ndb-8.0.19 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[28 Apr 2020 12:15]
Daniel Hope
[28 Apr 2020 12:58]
Daniel Hope
So it would appear AUTO_INCREMENT values are out of sync between MySQL hosts ?
Host 1:
SHOW TABLE STATUS FROM `DB` WHERE `name` LIKE 'Material_Spreadsheet_Upload';
mysql> SELECT `AUTO_INCREMENT`
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'Trilex_Installation'
-> AND TABLE_NAME = 'Material_Spreadsheet_Upload';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 110 |
+----------------+
Host 2:
mysql> SELECT `AUTO_INCREMENT`
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'Trilex_Installation'
-> AND TABLE_NAME = 'Material_Spreadsheet_Upload';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 640 |
+----------------+
Then tried Node 1:
ANALYZE TABLE `Material_Spreadsheet_Upload`;
But that didn't fix it ?
[28 Apr 2020 18:32]
MySQL Verification Team
Hi, That is not a bug but a completely expected behavior. Each API node (mysql server is just one api node) will pre-allocate a number of auto-increment values to use in order to speed up inserts. If it would "ask" for a new number each time you insert that would create a too big of a latency. You can control how much they pre-allocate. https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-options-variables.html#sysvar_ndb_au... Thanks Bogdan
[28 Apr 2020 19:30]
Daniel Hope
There is a fair amount of documentation on the internet that suggests ordering by auto incrementing primary key for joining the latest record is reliable I.E https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql But this change breaks that behaviour because if the saves are out of order I would need to start ordering by date to ensure that im joining the last record. Maybe I dont understand the upgrade notes, but at time of writing, its not in the upgrade notes https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html I had to roll my cluster back because I wasn't easily able to identify this setting (im no DBA though) and it has caused some data loss
[28 Apr 2020 19:43]
Daniel Hope
Sorry wrong link, but here are some https://stackoverflow.com/questions/3619030/mysql-join-the-most-recent-row-only https://stackoverflow.com/questions/725153/most-recent-record-in-a-left-join
[29 Apr 2020 15:06]
MySQL Verification Team
Hi Daniel, Sorry to say this but StackOverflow is not always a reliable source of info. By the SQL standard, and by MySQL documentation, there is no promise that auto_increment values will be in order and without holes nor that they will not be reused. Ordering by the auto_increment field is bad practice. If you need to order by insert time you need to create a date-time field to store insert time. The fact auto_inc fields were behaving a certain way is just a temporary gimmick and not something to take for granted. Just like the order of the unordered query etc etc. You do not have to revert to an older version, you can set prefetch size to 1 as described in the documentation I linked. Inserts (especially bulk ones) will be a lot slower but will work the way you expect. Note that default values for different parameters do change trough time and you should never run default values for stuff you care about. all best Bogdan
