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

ERROR 1032 (HY000): Can't find record with MyISAM, (too?) large key and DISABLE KEYS

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.4.19, 10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
    • N/A
    • Server, Storage Engine - MyISAM

    Description

      This runs just fine:

      CREATE TABLE `test` (
      		 
        `id` int unsigned NOT NULL,
      		 
        `foo` varchar(255) NOT NULL,
      		 
        PRIMARY KEY (`id`),
      		 
        UNIQUE KEY `foo_key` (`foo`)
      		 
      ) ENGINE=MyISAM;
      		 
      INSERT INTO `test` VALUES (1,'dummy');

      However, when first disabling keys (commonly seen when using mysqldump) the INSERT query fails with an ERROR 1032 (HY000): Can't find record in 'test':

      DROP TABLE IF EXISTS `test`;
      		 
      CREATE TABLE `test` (
      		 
        `id` int unsigned NOT NULL,
      		 
        `foo` varchar(255) NOT NULL,
      		 
        PRIMARY KEY (`id`),
      		 
        UNIQUE KEY `foo_key` (`foo`)
      		 
      ) ENGINE=MyISAM;
      		 
      ALTER TABLE `test` DISABLE KEYS;
      		 
      INSERT INTO `test` VALUES (1,'dummy');

      Character set being used is utf8mb4

      If you use the ARIA storage engine, the CREATE statement will fail with ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes (varchar(255) in utf8mb4 is 1020 bytes).
      If you use the InnoDB storage engine, there is no issue.
      If you change the column foo to a varchar(250), it works fine as well.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25102 UNIQUE USING HASH error after ALTER ... DISABLE KEYS

          • 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

          Activity

            Ascending order - Click to sort in descending order
            tgm Martijn added a comment -

            Possible the same issue as MDEV-25102

            tgm Martijn added a comment - Possible the same issue as MDEV-25102
            elenst Elena Stepanova added a comment - - edited

            Thanks for the report. Reproducible as described, yet another unique blob problem.

            DROP TABLE IF EXISTS `test`;
            		 
            CREATE TABLE `test` (
            		 
              `foo` varchar(255) NOT NULL,
            		 
              UNIQUE KEY `foo_key` (`foo`)
            		 
            ) ENGINE=MyISAM CHARACTER SET utf8mb4;
            		 
            ALTER TABLE `test` DISABLE KEYS;
            		 
            INSERT INTO `test` VALUES ('dummy');

            10.4 a48d2ec8

            		 
            mysqltest: At line 8: query 'INSERT INTO `test` VALUES ('dummy')' failed: 1032: Can't find record in 'test'

            The resulting table structure:

            SHOW CREATE TABLE `test`;
            		 
            Table	Create Table
            		 
            test	CREATE TABLE `test` (
            		 
              `foo` varchar(255) NOT NULL,
            		 
              UNIQUE KEY `foo_key` (`foo`) USING HASH
            		 
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

            or, upon DDL,

            CREATE TABLE t (f TEXT, UNIQUE(f)) ENGINE=MyISAM;
            		 
            INSERT INTO t VALUES ('x');
            		 
            ALTER TABLE t DISABLE KEYS;
            		 
            ALTER TABLE t FORCE;
            		 
             
            		 
            # Cleanup
            		 
            DROP TABLE t;

            elenst Elena Stepanova added a comment - - edited Thanks for the report. Reproducible as described, yet another unique blob problem. DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `foo` varchar (255) NOT NULL , UNIQUE KEY `foo_key` (`foo`) ) ENGINE=MyISAM CHARACTER SET utf8mb4; ALTER TABLE `test` DISABLE KEYS; INSERT INTO `test` VALUES ( 'dummy' ); 10.4 a48d2ec8 mysqltest: At line 8: query 'INSERT INTO `test` VALUES (' dummy ')' failed: 1032: Can 't find record in ' test' The resulting table structure: SHOW CREATE TABLE `test`; Table Create Table test CREATE TABLE `test` ( `foo` varchar (255) NOT NULL , UNIQUE KEY `foo_key` (`foo`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 or, upon DDL, CREATE TABLE t (f TEXT, UNIQUE (f)) ENGINE=MyISAM; INSERT INTO t VALUES ( 'x' ); ALTER TABLE t DISABLE KEYS; ALTER TABLE t FORCE ;   # Cleanup DROP TABLE t;
            midenok Aleksey Midenkov added a comment - - edited

            Does not repro on 10.5.28 7b0f59da43b:

            CREATE TABLE `test` (
            		 
            `id` int unsigned NOT NULL,
            		 
            `foo` varchar(255) NOT NULL,
            		 
            PRIMARY KEY (`id`),
            		 
            UNIQUE KEY `foo_key` (`foo`)
            		 
            ) ENGINE=MyISAM;
            		 
            ALTER TABLE `test` DISABLE KEYS;
            		 
            INSERT INTO `test` VALUES (1,'dummy');
            		 
            DROP TABLE IF EXISTS `test`;
            		 
            CREATE TABLE `test` (
            		 
            `foo` varchar(255) NOT NULL,
            		 
            UNIQUE KEY `foo_key` (`foo`)
            		 
            ) ENGINE=MyISAM CHARACTER SET utf8mb4;
            		 
            ALTER TABLE `test` DISABLE KEYS;
            		 
            INSERT INTO `test` VALUES ('dummy');
            		 
            drop table test;

            midenok Aleksey Midenkov added a comment - - edited Does not repro on 10.5.28 7b0f59da43b: CREATE TABLE `test` ( `id` int unsigned NOT NULL , `foo` varchar (255) NOT NULL , PRIMARY KEY (`id`), UNIQUE KEY `foo_key` (`foo`) ) ENGINE=MyISAM; ALTER TABLE `test` DISABLE KEYS; INSERT INTO `test` VALUES (1, 'dummy' ); DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `foo` varchar (255) NOT NULL , UNIQUE KEY `foo_key` (`foo`) ) ENGINE=MyISAM CHARACTER SET utf8mb4; ALTER TABLE `test` DISABLE KEYS; INSERT INTO `test` VALUES ( 'dummy' ); drop table test;
            serg Sergei Golubchik added a comment -

            supposedly, fixed meanwhile

            serg Sergei Golubchik added a comment - supposedly, fixed meanwhile

            People

              sanja Oleksandr Byelkin
              tgm Martijn
              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.