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

Database corruption after renaming a prefix-indexed column

Details

    Description

      It seems that under certain conditions a simple "alter table" statement to rename a column can cause the database to go bad. This is what I do to reproduce the issue:

      (Basically creating a table with the primary key on two columns, after that renaming one of the columns)
      -------------------
      create database test42;
      use test42;
      create table `test` ( `test_old` varchar(255) NOT NULL,
      `other` varchar(255) NOT NULL,
      PRIMARY KEY (`test_old`,`other`) );

      alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
      select * from test;
      -------------------

      Up to here it works fine. Now after I restart the server (systemctl restart mariadb). I get this after rerunning the select statement.

      ---------------------
      select * from test;
      ERROR 1932 (42S02): Table 'test42.test' doesn't exist in engine
      ---------------------

      So something went wrong with that "test" table.

      BTW, the above is just a minimized test case, we orginally noticed this issue when trying to use 10.2.19 with openstack-neutron (which needs to run schemamigrations similar to the above).

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13671 InnoDB should use case-insensitive column name comparisons like the rest of the server

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Thanks for the report and test case, reproducible as described.
            Here is an MTR version of the same:

            --source include/have_innodb.inc
            		 
             
            		 
            create table `test` (
            		 
              `test_old` varchar(255) NOT NULL,
            		 
              `other` varchar(255) NOT NULL,
            		 
              PRIMARY KEY (`test_old`,`other`)
            		 
            ) ENGINE=InnoDB;
            		 
             
            		 
            alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
            		 
            show create table `test`;
            		 
            select * from test;
            		 
             
            		 
            --source include/restart_mysqld.inc
            		 
             
            		 
            select * from test;
            		 
             
            		 
            # Cleanup
            		 
            drop table `test`;

            Reproducible on 10.2+, not reproducible on 10.1. However, it seems to have appeared in 10.2 tree with this merge:

            commit 32062cc61cd00e4cd3b7939c8a09f9c3ac34ec76
            		 
            Merge: af9649c bae21bf
            		 
            Author: Marko Mäkelä <marko.makela@mariadb.com>
            		 
            Date:   Tue Nov 6 08:41:48 2018 +0200
            		 
             
            		 
                Merge 10.1 into 10.2

            elenst Elena Stepanova added a comment - Thanks for the report and test case, reproducible as described. Here is an MTR version of the same: --source include/have_innodb.inc   create table `test` ( `test_old` varchar (255) NOT NULL , `other` varchar (255) NOT NULL , PRIMARY KEY (`test_old`,`other`) ) ENGINE=InnoDB;   alter table `test` CHANGE COLUMN `test_old` `test_new` varchar (255) NOT NULL ; show create table `test`; select * from test;   --source include/restart_mysqld.inc   select * from test;   # Cleanup drop table `test`; Reproducible on 10.2+, not reproducible on 10.1. However, it seems to have appeared in 10.2 tree with this merge: commit 32062cc61cd00e4cd3b7939c8a09f9c3ac34ec76 Merge: af9649c bae21bf Author: Marko Mäkelä <marko.makela@mariadb.com> Date: Tue Nov 6 08:41:48 2018 +0200   Merge 10.1 into 10.2
            marko Marko Mäkelä added a comment -

            This seems to be a regression caused by the merge of the MDEV-13671 fix.

            marko Marko Mäkelä added a comment - This seems to be a regression caused by the merge of the MDEV-13671 fix.
            marko Marko Mäkelä added a comment -

            The problem seems to be that both PRIMARY KEY columns (not only test_old, but also other) will be renamed to test_new in INFORMATION_SCHEMA.INNODB_SYS_FIELDS. This causes the following message to be emitted into the error log:

            2018-12-29 11:41:17 140736989312768 [ERROR] InnoDB: No matching column for `test_new` in index `PRIMARY` of table `test`.`test`

            I do not immediately see why this would not happen in 10.0 or 10.1. (There have not been any 10.0 or 10.1 releases with this change yet.)

            marko Marko Mäkelä added a comment - The problem seems to be that both PRIMARY KEY columns (not only test_old , but also other ) will be renamed to test_new in INFORMATION_SCHEMA.INNODB_SYS_FIELDS . This causes the following message to be emitted into the error log: 2018-12-29 11:41:17 140736989312768 [ERROR] InnoDB: No matching column for `test_new` in index `PRIMARY` of table `test`.`test` I do not immediately see why this would not happen in 10.0 or 10.1. (There have not been any 10.0 or 10.1 releases with this change yet.)
            kevg Eugene Kosov (Inactive) added a comment -

            Yes it is a regression after MDEV-13671. And yes it's related to updating of INFORMATION_SCHEMA.INNODB_SYS_FIELDS

            My fix is here https://github.com/MariaDB/server/pull/1063

            kevg Eugene Kosov (Inactive) added a comment - Yes it is a regression after MDEV-13671 . And yes it's related to updating of INFORMATION_SCHEMA.INNODB_SYS_FIELDS My fix is here https://github.com/MariaDB/server/pull/1063
            nikowitt Niko Wittenbeck added a comment -

            Is there a way to directly update the INNODB_SYS_FIELDS table and to fix the columns manually (root lacks access)? Fixing that manually with table/db recreation is quite time-consuming.

            nikowitt Niko Wittenbeck added a comment - Is there a way to directly update the INNODB_SYS_FIELDS table and to fix the columns manually (root lacks access)? Fixing that manually with table/db recreation is quite time-consuming.

            People

              kevg Eugene Kosov (Inactive)
              rhafer Ralf Haferkamp
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.