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

Incompatibility on INDEX lenght after upgrade, bogus error message

Details

    Description

      Previous version was MariaDB 10.1.37. Affected table was created long time ago on MariaDB 5.5. All previous upgrades was fine, but after upgrade to 10.3.15 and run of mysql_upgrade there is an inaccessible table "mailbox" with following error:

      Error : Table 'mailbox' uses an extension that doesn't exist in this MariaDB version
      error : Corrupt

      The original table:

      CREATE TABLE `mailbox` (
      `username` varchar(255) COLLATE utf8_czech_ci NOT NULL,
      `password` varchar(255) COLLATE utf8_czech_ci NOT NULL,
      `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
      `maildir` varchar(255) COLLATE utf8_czech_ci NOT NULL,
      `quota` bigint(20) NOT NULL DEFAULT '0',
      `local_part` varchar(255) COLLATE utf8_czech_ci NOT NULL,
      `domain` varchar(255) COLLATE utf8_czech_ci NOT NULL,
      `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `active` tinyint(1) NOT NULL DEFAULT '1',
      PRIMARY KEY (`username`),
      KEY `domain` (`domain`),
      KEY `active` (`active`),
      KEY `username_domain_active` (`username`,`domain`,`active`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Postfix Admin - Virtual Mailboxes'

      MariaDB 10.3.15 has problem with KEY username_domain_active. This key is too long for this version. But why? Why if this key worked for many years?
      This table cannot be fixed / altered on MariaDB 10.3. The only way to workaround this issue is to drop this table and recreate it from backup without mentioned index or with shortened index prefix length.

      Attachments

        Activity

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

          I'm not quite sure how it worked before, even the earliest existing version of MariaDB 5.5 (5.5.23) returns the error:

          MariaDB [test]> CREATE TABLE `mailbox` (
          		 
              -> `username` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
              -> `password` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
              -> `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
              -> `maildir` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
              -> `quota` bigint(20) NOT NULL DEFAULT '0',
          		 
              -> `local_part` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
              -> `domain` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
              -> `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
          		 
              -> `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
          		 
              -> `active` tinyint(1) NOT NULL DEFAULT '1',
          		 
              -> PRIMARY KEY (`username`),
          		 
              -> KEY `domain` (`domain`),
          		 
              -> KEY `active` (`active`),
          		 
              -> KEY `username_domain_active` (`username`,`domain`,`active`)
          		 
              -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Postfix Admin - Virtual Mailboxes';
          		 
          ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
          		 
          MariaDB [test]> select @@version;
          		 
          +----------------+
          		 
          | @@version      |
          		 
          +----------------+
          		 
          | 5.5.23-MariaDB |
          		 
          +----------------+
          		 
          1 row in set (0.000 sec)

          Could you please attach .frm, .MYI and MYD files of the table which work on the previous versions? Or, if the data is confidential, at least .frm.
          Please also paste or attach the cnf file(s) from a previous version where it worked.

          elenst Elena Stepanova added a comment - I'm not quite sure how it worked before, even the earliest existing version of MariaDB 5.5 (5.5.23) returns the error: MariaDB [test]> CREATE TABLE `mailbox` ( -> `username` varchar (255) COLLATE utf8_czech_ci NOT NULL , -> ` password ` varchar (255) COLLATE utf8_czech_ci NOT NULL , -> ` name ` varchar (255) COLLATE utf8_czech_ci NOT NULL , -> `maildir` varchar (255) COLLATE utf8_czech_ci NOT NULL , -> `quota` bigint (20) NOT NULL DEFAULT '0' , -> `local_part` varchar (255) COLLATE utf8_czech_ci NOT NULL , -> `domain` varchar (255) COLLATE utf8_czech_ci NOT NULL , -> `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , -> `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , -> `active` tinyint(1) NOT NULL DEFAULT '1' , -> PRIMARY KEY (`username`), -> KEY `domain` (`domain`), -> KEY `active` (`active`), -> KEY `username_domain_active` (`username`,`domain`,`active`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE =utf8_czech_ci COMMENT= 'Postfix Admin - Virtual Mailboxes' ; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes MariaDB [test]> select @@version; + ----------------+ | @@version | + ----------------+ | 5.5.23-MariaDB | + ----------------+ 1 row in set (0.000 sec) Could you please attach .frm, .MYI and MYD files of the table which work on the previous versions? Or, if the data is confidential, at least .frm. Please also paste or attach the cnf file(s) from a previous version where it worked.
          Lachman Miroslav Lachman added a comment -

          sys_mail.mailbox_old.tgz
          I dug it out of the ZFS snapshot, moved it in to an old VM with MySQL 5.6.40 where it works as expected. (I don't have any VM with an old MariaDB now)
          Then I truncated table content and inserted just one row:

          INSERT INTO mailbox SET username="some@example.com", password="somepass", name="Some", maildir="example.com/some", quota="102400000", local_part="some", domain="example.com", created="2016-07-05 00:20:34", modified="2016-07-05 00:20:34", active="1";

          frm, MYI and MYD files are in attached TAR file.

          root @ localhost [sys_mail]:mysql> SHOW CREATE TABLE mailbox \G
          		 
          *************************** 1. row ***************************
          		 
                 Table: mailbox
          		 
          Create Table: CREATE TABLE `mailbox` (
          		 
            `username` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
            `password` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
            `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
            `maildir` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
            `quota` bigint(20) NOT NULL DEFAULT '0',
          		 
            `local_part` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
            `domain` varchar(255) COLLATE utf8_czech_ci NOT NULL,
          		 
            `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
          		 
            `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
          		 
            `active` tinyint(1) NOT NULL DEFAULT '1',
          		 
            PRIMARY KEY (`username`),
          		 
            KEY `domain` (`domain`),
          		 
            KEY `active` (`active`),
          		 
            KEY `username_domain_active` (`username`,`domain`,`active`)
          		 
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Postfix Admin - Virtual Mailboxes'
          		 
          1 row in set (0.00 sec)


          root @ localhost [sys_mail]:mysql> CHECK TABLE mailbox;
          		 
          +------------------+-------+----------+----------+
          		 
          | Table            | Op    | Msg_type | Msg_text |
          		 
          +------------------+-------+----------+----------+
          		 
          | sys_mail.mailbox | check | status   | OK       |
          		 
          +------------------+-------+----------+----------+
          		 
          1 row in set (0.00 sec)

          Table in question was not created in MariaDB. It was created log time ago with MySQL 4.1 / 5.0 / 5.1 so I cannot say it can be created by MariaDB but it definitely works with MariaDB 5.5, 10.0, 10.1 as machines in question was migrated from stock MySQL 5.x to MariaDB 5.x, later to 10.0 and 10.1, now to 10.3 where the problem is fatal and error message is misleading / bogus.
          If there is problem with Index then I expect error message telling me about index problem and not about unsupported extension.
          If there is problem with Index, it would be nice to allow user to drop the problematic index instead of the whole table.

          Lachman Miroslav Lachman added a comment - sys_mail.mailbox_old.tgz I dug it out of the ZFS snapshot, moved it in to an old VM with MySQL 5.6.40 where it works as expected. (I don't have any VM with an old MariaDB now) Then I truncated table content and inserted just one row: INSERT INTO mailbox SET username= "some@example.com" , password = "somepass" , name = "Some" , maildir= "example.com/some" , quota= "102400000" , local_part= "some" , domain= "example.com" , created= "2016-07-05 00:20:34" , modified= "2016-07-05 00:20:34" , active= "1" ; frm, MYI and MYD files are in attached TAR file. root @ localhost [sys_mail]:mysql> SHOW CREATE TABLE mailbox \G *************************** 1. row *************************** Table : mailbox Create Table : CREATE TABLE `mailbox` ( `username` varchar (255) COLLATE utf8_czech_ci NOT NULL , ` password ` varchar (255) COLLATE utf8_czech_ci NOT NULL , ` name ` varchar (255) COLLATE utf8_czech_ci NOT NULL , `maildir` varchar (255) COLLATE utf8_czech_ci NOT NULL , `quota` bigint (20) NOT NULL DEFAULT '0' , `local_part` varchar (255) COLLATE utf8_czech_ci NOT NULL , `domain` varchar (255) COLLATE utf8_czech_ci NOT NULL , `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' , `active` tinyint(1) NOT NULL DEFAULT '1' , PRIMARY KEY (`username`), KEY `domain` (`domain`), KEY `active` (`active`), KEY `username_domain_active` (`username`,`domain`,`active`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE =utf8_czech_ci COMMENT= 'Postfix Admin - Virtual Mailboxes' 1 row in set (0.00 sec) root @ localhost [sys_mail]:mysql> CHECK TABLE mailbox; +------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+----------+ | sys_mail.mailbox | check | status | OK | +------------------+-------+----------+----------+ 1 row in set (0.00 sec) Table in question was not created in MariaDB. It was created log time ago with MySQL 4.1 / 5.0 / 5.1 so I cannot say it can be created by MariaDB but it definitely works with MariaDB 5.5, 10.0, 10.1 as machines in question was migrated from stock MySQL 5.x to MariaDB 5.x, later to 10.0 and 10.1, now to 10.3 where the problem is fatal and error message is misleading / bogus. If there is problem with Index then I expect error message telling me about index problem and not about unsupported extension. If there is problem with Index, it would be nice to allow user to drop the problematic index instead of the whole table.
          Lachman Miroslav Lachman added a comment -

          my.cnf

          Lachman Miroslav Lachman added a comment - my.cnf

          People

            bar Alexander Barkov
            Lachman Miroslav Lachman
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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