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

Incorrect information in file: ... #sql-alter ... .frm on ALTER when adding unique hash index, ERROR 1901

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
    • N/A
    • Server

    Description

      Present in InnoDB, MyISAM, Aria but not in MEMORY.

      CREATE TABLE t (b INT AUTO_INCREMENT KEY);
      CREATE UNIQUE INDEX i USING HASH ON t(b);
      

      or

      CREATE TABLE t (b INT AUTO_INCREMENT KEY);
      ALTER TABLE t ADD UNIQUE id USING HASH (b);
      

      Leads to:

      10.10.0 e1caa4bd5e8b4645944b85d4b603bf9fc9ef6ca4 (Optimized)

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

      10.10.0 e1caa4bd5e8b4645944b85d4b603bf9fc9ef6ca4 (Optimized)

      2022-07-29 18:36:43 4 [ERROR] mysqld: Incorrect information in file: './test/#sql-alter-d1832-4.frm'
      

      Bug confirmed present in:
      MariaDB: 10.4.26 (dbg), 10.4.26 (opt), 10.5.17 (dbg), 10.5.17 (opt), 10.6.9 (dbg), 10.6.9 (opt), 10.7.5 (dbg), 10.7.5 (opt), 10.8.4 (dbg), 10.8.4 (opt), 10.9.2 (dbg), 10.9.2 (opt), 10.10.0 (dbg), 10.10.0 (opt)

      Bug (or feature/syntax) confirmed not present in:
      MariaDB: 10.3.36 (dbg), 10.3.36 (opt)
      MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

      Attachments

        Issue Links

          Activity

            BB Silver Asu added a comment -

            Is there any workaround for fixing this table? Everything (SHOW CREATE TABLE, DESCRIBE, mysqldump, etc) gives this error:

            ERROR 1901 (HY000): Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `id`
            

            BB Silver Asu added a comment - Is there any workaround for fixing this table? Everything (SHOW CREATE TABLE, DESCRIBE, mysqldump, etc) gives this error: ERROR 1901 (HY000): Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `id`

            BB That is interesting. In the testcase above, only the ALTER fails, but the table itself remains healthy. Do you have a testcase which causes permanent table corruption? (Even if it is long, we can reduce it).

            Roel Roel Van de Paar added a comment - BB That is interesting. In the testcase above, only the ALTER fails, but the table itself remains healthy. Do you have a testcase which causes permanent table corruption? (Even if it is long, we can reduce it).

            BB, could it be an old table that was created in 10.4.22 or earlier?

            serg Sergei Golubchik added a comment - BB , could it be an old table that was created in 10.4.22 or earlier?
            BB Silver Asu added a comment -

            Yes, Thats possible.

            BB Silver Asu added a comment - Yes, Thats possible.

            It produces warnings on old version table and fails in new:

            myf warn= table->s->frm_version < FRM_VER_EXPRESSSIONS ? ME_WARNING : 0;
            my_error(ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(warn),
            "AUTO_INCREMENT", get_vcol_type_name(), res.name);
            if (!warn)
            DBUG_RETURN(1);

            According to MDEV-12936 it is expected limitation.

            midenok Aleksey Midenkov added a comment - It produces warnings on old version table and fails in new: myf warn= table->s->frm_version < FRM_VER_EXPRESSSIONS ? ME_WARNING : 0; my_error(ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(warn), "AUTO_INCREMENT", get_vcol_type_name(), res.name); if (!warn) DBUG_RETURN(1); According to MDEV-12936 it is expected limitation.

            People

              nikitamalyavin Nikita Malyavin
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.