[MDEV-19715] Incompatibility on INDEX lenght after upgrade, bogus error message Created: 2019-06-07  Updated: 2019-10-11

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3.15
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: Miroslav Lachman Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: bogus_error_message, incompatibility, regression
Environment:

FreeBSD 11.2-RELEASE-p10 amd64 GENERIC
mysql Ver 15.1 Distrib 10.3.15-MariaDB, for FreeBSD11.2 (amd64) using readline 5.1

Attachments: File my.cnf     File sys_mail.mailbox_old.tgz    

 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.

 Comments   
Comment by Elena Stepanova [ 2019-06-17 ]

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.

Comment by Miroslav Lachman [ 2019-06-18 ]

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.

Comment by Miroslav Lachman [ 2019-06-18 ]

my.cnf

Generated at Thu Feb 08 08:53:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.