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

some coloums‘ length of varchar is reduced from 200 to 20,run create table sql raises exception

Details

    Description

      some coloums‘ length of varchar is reduced from 200 to 20,run create table sql raises exception like 'ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.'.

      while on the same instance and the same ddl sql, the difference is some coloums‘ length of varchar is 200 not reduced ,run create table sql success.

      I except that the reduced varchar sql runs sucess as well as the not reduced.
      I am trapped with the sitiuation

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          danblack Daniel Black added a comment -

          This comes about because of https://mariadb.com/kb/en/innodb-dynamic-row-format/#overflow-pages-with-the-dynamic-row-format

          When the column was 200 (assuming to be at least utf8mb3 to exceed 255 bytes), it was in an overflow page and only consuming the 20 bytes pointer.

          The 20 character, 60/80 bytes + length, won't go in the overflow page, but attempts to go inplace on the main page that exceeds the length.

          Comes as odd that a smaller size needs more room, but that's just the way it was implemented sorry.

          So options are:

          • smaller character set, or
          • alter other columns to further reduce size by another 40/60 bytes.

          You can experiment on an empty table to determine a right structure that fits the limit:

          CREATE TABLE test LIKE origin_table;
          		 
          ALTER TABLE test .....

          danblack Daniel Black added a comment - This comes about because of https://mariadb.com/kb/en/innodb-dynamic-row-format/#overflow-pages-with-the-dynamic-row-format When the column was 200 (assuming to be at least utf8mb3 to exceed 255 bytes), it was in an overflow page and only consuming the 20 bytes pointer. The 20 character, 60/80 bytes + length, won't go in the overflow page, but attempts to go inplace on the main page that exceeds the length. Comes as odd that a smaller size needs more room, but that's just the way it was implemented sorry. So options are: smaller character set, or alter other columns to further reduce size by another 40/60 bytes. You can experiment on an empty table to determine a right structure that fits the limit: CREATE TABLE test LIKE origin_table; ALTER TABLE test .....
          danblack Daniel Black added a comment -

          Also note 10.3 is EOL - https://mariadb.org/about/#maintenance-policy

          danblack Daniel Black added a comment - Also note 10.3 is EOL - https://mariadb.org/about/#maintenance-policy

          People

            Unassigned Unassigned
            jjs Wenwen Jing
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.