Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.15
-
FreeBSD 11.2-RELEASE-p10 amd64 GENERIC
mysql Ver 15.1 Distrib 10.3.15-MariaDB, for FreeBSD11.2 (amd64) using readline 5.1
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
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.
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.
my.cnf