Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25816

alter table may change primary key from 0 to 1

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            greenman Ian Gilfillan
            Reporter:
            qlks chengyao jiang
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration