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

Aria engine return "The table is full" (ERROR 1114) inserting record, while MyISAM and InnoDB doesn't

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.27, 5.5.25-galera
    • 10.0.10
    • None
    • None
    • Windows server 2003 enterprise 32 bit

    Description

      Trying to convert a 9GB InnoDB table to Aria engine, I get the error 1114 when the .MAD file reaches 4GB (.MAI is 500MB). The table is DYNAMIC, with 134 fields, PK is INT, and other 3 indexes is CHAR(11),CHAR(25) and TIMESTAMP. The problem doesn't occour if I convert to MyISAM (the result table is bigger than 4GB). Same problem with another table.

      Attachments

        Activity

          elenst Elena Stepanova added a comment - - edited

          As Sergei answered in the KB ( https://kb.askmonty.org/en/aria-max-table-size/ ), the workaround is to use an explicit value of MAX_ROWS table option. I can confirm both the described problem and the workaround, see the output below. However, I'm not sure it's quite reasonable to require the option in this case. We don't create a table, but alter it. The table already contains the given number of rows, so one could expect that MAX_ROWS would be considered at least this big. I will assign it to Monty to decide if anything can and should be done about it, maybe in upcoming 10.x release, if not earlier.

          Output:

          MariaDB [test]> create table t1 (i int primary key, c1 char(255), c2 char(255), c3 char(255), index(c1(25)), index(c2(10)), index(c3(10))) engine=InnoDB row_format=dynamic;
          Query OK, 0 rows affected (0.16 sec)
           
          MariaDB [test]> load data local infile 't.load' into table t1 fields terminated by ',';
          Query OK, 10000000 rows affected (1 hour 4 min 59.47 sec)
          Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0
           
          MariaDB [test]> show table status from test like 't1' \G
          *************************** 1. row ***************************
                     Name: t1
                   Engine: InnoDB
                  Version: 10
               Row_format: Dynamic
                     Rows: 10000032
           Avg_row_length: 863
              Data_length: 8631877632
          Max_data_length: 0
             Index_length: 775585792
                Data_free: 4194304
           Auto_increment: NULL
              Create_time: 2012-11-27 04:29:52
              Update_time: NULL
               Check_time: NULL
                Collation: latin1_swedish_ci
                 Checksum: NULL
           Create_options: row_format=DYNAMIC
                  Comment:
          1 row in set (1.31 sec)
           
          MariaDB [test]> alter table t1 engine=MyISAM;
          Query OK, 10000000 rows affected (42 min 27.30 sec)
          Records: 10000000  Duplicates: 0  Warnings: 0
           
          MariaDB [test]> show table status from test like 't1' \G
          *************************** 1. row ***************************
                     Name: t1
                   Engine: MyISAM
                  Version: 10
               Row_format: Dynamic
                     Rows: 10000000
           Avg_row_length: 736
              Data_length: 7360000000
          Max_data_length: 281474976710655
             Index_length: 319958016
                Data_free: 0
           Auto_increment: NULL
              Create_time: 2012-11-27 07:28:17
              Update_time: 2012-11-27 08:10:42
               Check_time: 2012-11-27 08:10:41
                Collation: latin1_swedish_ci
                 Checksum: NULL
           Create_options: row_format=DYNAMIC
                  Comment:
          1 row in set (0.30 sec)
           
          MariaDB [test]> alter table t1 engine=Aria;
          ERROR 1114 (HY000): The table '#sql-13a8_4' is full
           
          MariaDB [test]> alter table t1 engine=Aria max_rows=10000000;
          Query OK, 10000000 rows affected (34 min 20.03 sec)
          Records: 10000000  Duplicates: 0  Warnings: 0
           
          MariaDB [test]> show table status from test like 't1' \G
          *************************** 1. row ***************************
                     Name: t1
                   Engine: Aria
                  Version: 10
               Row_format: Dynamic
                     Rows: 10000000
           Avg_row_length: 736
              Data_length: 7360000000
          Max_data_length: 1099511627775
             Index_length: 271179776
                Data_free: 0
           Auto_increment: NULL
              Create_time: 2012-11-27 16:29:27
              Update_time: 2012-11-27 17:03:46
               Check_time: 2012-11-27 17:03:45
                Collation: latin1_swedish_ci
                 Checksum: NULL
           Create_options: max_rows=10000000 row_format=DYNAMIC
                  Comment:
          1 row in set (0.36 sec)

          elenst Elena Stepanova added a comment - - edited As Sergei answered in the KB ( https://kb.askmonty.org/en/aria-max-table-size/ ), the workaround is to use an explicit value of MAX_ROWS table option. I can confirm both the described problem and the workaround, see the output below. However, I'm not sure it's quite reasonable to require the option in this case. We don't create a table, but alter it. The table already contains the given number of rows, so one could expect that MAX_ROWS would be considered at least this big. I will assign it to Monty to decide if anything can and should be done about it, maybe in upcoming 10.x release, if not earlier. Output: MariaDB [test]> create table t1 (i int primary key, c1 char(255), c2 char(255), c3 char(255), index(c1(25)), index(c2(10)), index(c3(10))) engine=InnoDB row_format=dynamic; Query OK, 0 rows affected (0.16 sec)   MariaDB [test]> load data local infile 't.load' into table t1 fields terminated by ','; Query OK, 10000000 rows affected (1 hour 4 min 59.47 sec) Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0   MariaDB [test]> show table status from test like 't1' \G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 10000032 Avg_row_length: 863 Data_length: 8631877632 Max_data_length: 0 Index_length: 775585792 Data_free: 4194304 Auto_increment: NULL Create_time: 2012-11-27 04:29:52 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: row_format=DYNAMIC Comment: 1 row in set (1.31 sec)   MariaDB [test]> alter table t1 engine=MyISAM; Query OK, 10000000 rows affected (42 min 27.30 sec) Records: 10000000 Duplicates: 0 Warnings: 0   MariaDB [test]> show table status from test like 't1' \G *************************** 1. row *************************** Name: t1 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 10000000 Avg_row_length: 736 Data_length: 7360000000 Max_data_length: 281474976710655 Index_length: 319958016 Data_free: 0 Auto_increment: NULL Create_time: 2012-11-27 07:28:17 Update_time: 2012-11-27 08:10:42 Check_time: 2012-11-27 08:10:41 Collation: latin1_swedish_ci Checksum: NULL Create_options: row_format=DYNAMIC Comment: 1 row in set (0.30 sec)   MariaDB [test]> alter table t1 engine=Aria; ERROR 1114 (HY000): The table '#sql-13a8_4' is full   MariaDB [test]> alter table t1 engine=Aria max_rows=10000000; Query OK, 10000000 rows affected (34 min 20.03 sec) Records: 10000000 Duplicates: 0 Warnings: 0   MariaDB [test]> show table status from test like 't1' \G *************************** 1. row *************************** Name: t1 Engine: Aria Version: 10 Row_format: Dynamic Rows: 10000000 Avg_row_length: 736 Data_length: 7360000000 Max_data_length: 1099511627775 Index_length: 271179776 Data_free: 0 Auto_increment: NULL Create_time: 2012-11-27 16:29:27 Update_time: 2012-11-27 17:03:46 Check_time: 2012-11-27 17:03:45 Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=10000000 row_format=DYNAMIC Comment: 1 row in set (0.36 sec)

          I confirm that the workaround is ok. However I do not understand the usefulness of the max_rows option...
          Thank you for the reply, bye.

          steris Risato Stefano added a comment - I confirm that the workaround is ok. However I do not understand the usefulness of the max_rows option... Thank you for the reply, bye.

          Hi!

          MAX_ROWS gives information to the Aria engine how many rows there will be in the table.
          This is used to calculate how big the pointers to the rows should be.
          Shorter pointers will speed up the index and use less space.

          The current assumption is to use 4 byte pointers, which for dynamic row files gives a maximum of 4G. For static size rows a 4 byte pointer would give you 4G rows.

          I will change Aria so that by default, if max_rows is not given, it will use 6 byte pointers. That should be enough for most cases.

          monty Michael Widenius added a comment - Hi! MAX_ROWS gives information to the Aria engine how many rows there will be in the table. This is used to calculate how big the pointers to the rows should be. Shorter pointers will speed up the index and use less space. The current assumption is to use 4 byte pointers, which for dynamic row files gives a maximum of 4G. For static size rows a 4 byte pointer would give you 4G rows. I will change Aria so that by default, if max_rows is not given, it will use 6 byte pointers. That should be enough for most cases.

          I have now fixed the pointers and tested this, seams to work.
          However I had to modify maria_records_in_range() to be more accurate as the longer pointers caused the binary trees in the test suite to grow and caused some unexpected test case changed.

          I am reasonable sure the changes to maria_records_in_range() are correct (spent several hours testing this), but I don't want to do this change in a stable version.

          I will first push the change to MariaDB 10.0. If we don't find any issues with this for a while and this is still found to be critical for 5.5 users I can then backport this to 5.5.

          This will be pushed to 10.0 later this week.

          monty Michael Widenius added a comment - I have now fixed the pointers and tested this, seams to work. However I had to modify maria_records_in_range() to be more accurate as the longer pointers caused the binary trees in the test suite to grow and caused some unexpected test case changed. I am reasonable sure the changes to maria_records_in_range() are correct (spent several hours testing this), but I don't want to do this change in a stable version. I will first push the change to MariaDB 10.0. If we don't find any issues with this for a while and this is still found to be critical for 5.5 users I can then backport this to 5.5. This will be pushed to 10.0 later this week.

          Fix pushed into 10.0-monty tree. Will be merged to 10.0 soon

          monty Michael Widenius added a comment - Fix pushed into 10.0-monty tree. Will be merged to 10.0 soon

          Pushed some time ago into 10.0 tree

          monty Michael Widenius added a comment - Pushed some time ago into 10.0 tree

          People

            monty Michael Widenius
            steris Risato Stefano
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.