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

ERROR 1901 During ALTER Leading to frm table corruption

Details

    Description

      CREATE TABLE t (a INT AUTO_INCREMENT KEY) ENGINE=InnoDB;
      		 
      ALTER TABLE t ADD UNIQUE u USING HASH (a);

      Leads to:

      10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Optimized and Debug)

      		 
      10.9.0-opt>ALTER TABLE t ADD UNIQUE u USING HASH (a);
      		 
      ERROR 1901 (HY000): Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `a`

      10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)

      		 
      2022-03-29 18:00:32 4 [ERROR] mysqld: Incorrect information in file: './test/#sql-alter-870e-4.frm'

      10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Optimized)

      		 
      2022-03-29 18:14:23 4 [ERROR] mysqld: Incorrect information in file: './test/#sql-alter-140c3c-4.frm'

      Bug confirmed present in:
      MariaDB: 10.4.25 (dbg), 10.4.25 (opt), 10.5.16 (dbg), 10.5.16 (opt), 10.6.8 (dbg), 10.6.8 (opt), 10.7.4 (dbg), 10.7.4 (opt), 10.8.3 (dbg), 10.8.3 (opt), 10.9.0 (dbg), 10.9.0 (opt)

      Bug (or feature/syntax) confirmed not present in:
      MariaDB: 10.2.44 (dbg), 10.2.44 (opt), 10.3.35 (dbg), 10.3.35 (opt)
      MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.37 (dbg), 5.7.37 (opt), 8.0.28 (dbg), 8.0.28 (opt)

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29203 Incorrect information in file: ... #sql-alter ... .frm on ALTER when adding unique hash index, ERROR 1901

          • Major - Major loss of function.
          • Closed
          is caused by

          Task - A task that needs to be done. MDEV-371 Unique indexes for blobs

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22759 Failing assertion: !cursor->index->is_committed() upon update on table with HASH index

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I think that this is due to MDEV-371, which I have nothing to do with. InnoDB has no direct control over .frm files, and I suspect that the problems are repeatable with other storage engines as well.

            marko Marko Mäkelä added a comment - I think that this is due to MDEV-371 , which I have nothing to do with. InnoDB has no direct control over .frm files, and I suspect that the problems are repeatable with other storage engines as well.
            sanja Oleksandr Byelkin added a comment - 

            worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
            		 
            CREATE TABLE t (a INT AUTO_INCREMENT KEY) ENGINE=InnoDB;
            		 
            ALTER TABLE t ADD UNIQUE u USING HASH (a);
            		 
            main.test 'innodb'                       [ fail ]
            		 
                    Test ended at 2022-06-24 17:22:47
            		 
             
            		 
            CURRENT_TEST: main.test
            		 
            mysqltest: At line 4: query 'ALTER TABLE t ADD UNIQUE u USING HASH (a)' failed: 1901: Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `a`

            sanja Oleksandr Byelkin added a comment - worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 CREATE TABLE t (a INT AUTO_INCREMENT KEY) ENGINE=InnoDB; ALTER TABLE t ADD UNIQUE u USING HASH (a); main.test 'innodb' [ fail ] Test ended at 2022-06-24 17:22:47   CURRENT_TEST: main.test mysqltest: At line 4: query 'ALTER TABLE t ADD UNIQUE u USING HASH (a)' failed: 1901: Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `a`
            Roel Roel Van de Paar added a comment -

            sanja You closed this bug as "Cannot Reproduce" yet your MTR testcase shows the expected failure?

            Roel Roel Van de Paar added a comment - sanja You closed this bug as "Cannot Reproduce" yet your MTR testcase shows the expected failure?
            Roel Roel Van de Paar added a comment -

            Marking this as duplicate of MDEV-29203, which is the same issue, better explained/reduced.

            Roel Roel Van de Paar added a comment - Marking this as duplicate of MDEV-29203 , which is the same issue, better explained/reduced.

            People

              sanja Oleksandr Byelkin
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.