Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
-
Debian Unstable, Debian Testing and Debian Stable
Description
Hi there,
MariaDB 10.1 doesn't support `innodb_default_row_format` yet. Since InnoDB in Debian uses `utf8mb4` as default character set since version `10.0.20-2`, this leads to major pain, e.g. due to keys longer than 767 bytes.
A lot of applications silently expect the supported maximum size of VARCHAR to be 255 characters which means 1016 bytes with `utf8mb4`.
In order to support larger keys, `innodb_large_prefix` needs to be enabled and ROW_FORMAT set to `dynamic`. The former can be configured in the MariaDB server configuration in a persistent way, the latter not due to missing configuration option `innodb_default_row_format`.
Backporting `innodb_default_row_format` will at least give the option to server admins to configure MariaDB 10.1 in a way that applications don't break if they want to create keys > 767 bytes.
PS: I already discussed this issue with Marko Mäkelä (dr-m) on IRC.
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
Dagang Wei, if the application is doing CREATE INDEX or ALTER TABLE…ADD INDEX on an existing table, then the ROW_FORMAT of that table would be preserved. If the ROW_FORMAT is REDUNDANT (the old format before MySQL 5.0.3) or COMPACT (the default since MySQL 5.0.3), the limits would apply.
You could try rebuilding all tables (ALTER TABLE … ROW_FORMAT=DYNAMIC), or initialize the database from an SQL dump (after ensuring that there are no ROW_FORMAT attributes referring to old formats).
Dagang Wei, if the application is doing CREATE INDEX or ALTER TABLE…ADD INDEX on an existing table, then the ROW_FORMAT of that table would be preserved. If the ROW_FORMAT is REDUNDANT (the old format before MySQL 5.0.3) or COMPACT (the default since MySQL 5.0.3), the limits would apply.
You could try rebuilding all tables (ALTER TABLE … ROW_FORMAT=DYNAMIC), or initialize the database from an SQL dump (after ensuring that there are no ROW_FORMAT attributes referring to old formats).
I'm running a 3rd party application on Debian 9, I have upgraded MariaDB to 10.1.35 and added the properties in /etc/mysql/mariadb.conf.d/50-server.cnf:
innodb_file_format = Barracuda
innodb_file_per_table = On
innodb_large_prefix = On
innodb_default_row_format = dynamic
but it still fails for "Specified key was too long; max key length is 767 bytes". Because I don't know what the application is doing, is there a simple way for me to verify I have upgraded MariaDB and configured correctly? Thanks in advance!
mejo, the next 10.1 release should be in about 3 weeks. I would appreciate it if you can test if a package of the newest 10.1 build works for you and provide feedback. Remember to set the following:
innodb_large_prefix=on
|
innodb_default_row_format=dynamic
|
Note that any ROW_FORMAT returned by SHOW CREATE TABLE will only show what was specified at CREATE TABLE time. Based on InnoDB parameters, InnoDB could choose a different format. SHOW TABLE STATUS should display the format chosen by InnoDB.