Menu
I have a MariaDB Galera Cluster(3 nodes), I set uid to increase automatically and be the primary key of the table as
`uid | int(11) | NO | PRI | NULL | auto_increment`.
MariaDB [hello_cluster]> select uid from table order by uid limit 10;
+-----+
| uid |
+-----+
| 3 |
| 6 |
| 9 |
| 12 |
| 15 |
| 18 |
| 21 |
| 24 |
| 27 |
| 30 |
+-----+
I tried the following command, and it does not work
alter table uid AUTO_INCREMENT=1
860
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
Basically this is a bug in MySQL that causes the problem but a work around is simple. The problem happens when the Auto-Increment value of a table grows beyond the limit. Just run this SQL query in MySQL to fix the bug. Table_name is the name of the table where you found the error while inserting data into.
No. A primary key must be unique and that has to be 100% guaranteed, and NON NULL A primary key should be stable if ever possible and not change.
This is by design and is reported in MariaDB Galera Cluster - Known Limitations:
Do not rely on auto-increment values to be sequential. Galera uses a mechanism based on autoincrement increment to produce unique non-conflicting sequences, so on every single node the sequence will have gaps.
The rational is explained in Managing Auto Increments with Multi Masters, and is also why the observed auto-increment has the same step as the number of clusters.
MySQL has system variables auto_increment_increment and auto_increment_offset for managing auto increment 'sequences' in multi master environment. Using these variables, it is possible to set up a multi master replication, where auto increment sequences in each master node interleave, and no conflicts should happen in the cluster. No matter which master(s) get the INSERTs.
Even without clusters, it is rarely a "good" idea to rely on auto-increment columns to be dense sequences due to transaction rollbacks and deleted records.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
