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

Please backport `innodb_default_row_format` to MariaDB 10.1

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17160 InnoDB produces wrong message or wrong row format upon attempt to create table with ROW_FORMAT=COMPRESSED and big page size

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            mejo Jonas Meurer added a comment -

            This is the related bugreport in Debian: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=886756

            mejo Jonas Meurer added a comment - This is the related bugreport in Debian: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=886756
            mejo Jonas Meurer added a comment -

            Any news on that? @dr-m, do you think that a fix will make it into the next mariadb 10.1 release?

            mejo Jonas Meurer added a comment - Any news on that? @dr-m, do you think that a fix will make it into the next mariadb 10.1 release?
            mejo Jonas Meurer added a comment -

            Any news on that? @dr-m, do you think that a fix will make it into the next mariadb 10.1 release?

            mejo Jonas Meurer added a comment - Any news on that? @dr-m, do you think that a fix will make it into the next mariadb 10.1 release?
            marko Marko Mäkelä added a comment -

            mejo, sorry, I was busy with the 10.3.5 RC and the https://m18.mariadb.com preparations.
            I just started working on this, and will submit a patch for review today.

            As part of this, the parameter innodb_file_format (which was deprecated in MySQL 5.7 and MariaDB 10.2) will lose its meaning: tables can be created in ROW_FORMAT=DYNAMIC despite what this setting says.

            For maximum compatibility with older versions, the default value of the new parameter would be innodb_default_row_format=compact. In MariaDB 10.2, the default is dynamic.

            marko Marko Mäkelä added a comment - mejo , sorry, I was busy with the 10.3.5 RC and the https://m18.mariadb.com preparations. I just started working on this, and will submit a patch for review today. As part of this, the parameter innodb_file_format (which was deprecated in MySQL 5.7 and MariaDB 10.2) will lose its meaning: tables can be created in ROW_FORMAT=DYNAMIC despite what this setting says. For maximum compatibility with older versions, the default value of the new parameter would be innodb_default_row_format=compact . In MariaDB 10.2, the default is dynamic .
            marko Marko Mäkelä added a comment -

            bb-10.1-marko

            marko Marko Mäkelä added a comment - bb-10.1-marko
            mejo Jonas Meurer added a comment -

            Thanks a lot for the backport, @marko! Is there any ETA for the release of 10.1.32?

            mejo Jonas Meurer added a comment - Thanks a lot for the backport, @marko! Is there any ETA for the release of 10.1.32?
            marko Marko Mäkelä added a comment -

            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

            marko Marko Mäkelä added a comment - 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
            Dagang Wei Dagang Wei added a comment - - edited

            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!

            Dagang Wei Dagang Wei added a comment - - edited 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!
            marko Marko Mäkelä added a comment -

            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).

            marko Marko Mäkelä added a comment - 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).
            marko Marko Mäkelä added a comment -

            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).

            marko Marko Mäkelä added a comment - 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).
            marko Marko Mäkelä added a comment -

            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.

            marko Marko Mäkelä added a comment - 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.

            People

              marko Marko Mäkelä
              mejo Jonas Meurer
              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.