Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.10
-
None
Description
Hi,
MariaDB may change the primary key from 0 to 1 under following scenario.
And this problem happened in our production environment, which has caused a disaster for the business.
MariaDB [test]> select version(); |
+-----------------+ |
| version() |
|
+-----------------+ |
| 10.5.10-MariaDB |
|
+-----------------+ |
1 row in set (0.000 sec) |
|
MariaDB [test]> create table y ( a int primary key, b varchar(5)); |
Query OK, 0 rows affected (0.032 sec) |
|
MariaDB [test]> insert into y values (0,'a'),(2,'b'); |
Query OK, 2 rows affected (0.004 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select * from y; |
+---+------+ |
| a | b |
|
+---+------+ |
| 0 | a |
|
| 2 | b |
|
+---+------+ |
2 rows in set (0.000 sec) |
|
MariaDB [test]> alter table y modify column a int auto_increment; |
Query OK, 2 rows affected (0.095 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select * from y; |
+---+------+ |
| a | b |
|
+---+------+ |
| 1 | a |
|
| 2 | b |
|
+---+------+ |
2 rows in set (0.001 sec) |
As we can see, MariaDB will change primary key value from 0 to 1 if column changes to auto_increment.
I review the source code in sql/sql_table.cc, MariaDB does the logic of preserving zero value to auto_increment column.
/* |
If we are going to copy contents of one auto_increment column to
|
another auto_increment column it is sensible to preserve zeroes.
|
This condition also covers case when we are don't actually alter
|
auto_increment column.
|
*/
|
if (def->field == from->found_next_number_field) |
thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO;
|
From the source code, we can see that MariaDB will preserve zero value. But it miss the scenario when changing from 0 column value to 0 auto_increment column value.
Followings are my fix. When new table has auto_increment column, sql_mode should enable MODE_NO_AUTO_VALUE_ON_ZERO automatically.
--- sql_table2.cc 2021-05-26 15:20:41.974362582 +0800
|
+++ sql_table.cc 2021-05-26 15:01:18.592772749 +0800
|
@@ -17925,8 +17925,8 @@
|
This condition also covers case when we are don't actually alter |
auto_increment column.
|
*/
|
- if (def->field == from->found_next_number_field) |
- thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO;
|
+ //if (def->field == from->found_next_number_field) |
+ thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO;
|
}
|
(copy_end++)->set(*ptr, def->field, false); |
Now the result of alter table is right, 0 value will not change to 1.