[MDEV-3815] Aria engine return "The table is full" (ERROR 1114) inserting record, while MyISAM and InnoDB doesn't Created: 2012-10-19  Updated: 2014-03-10  Resolved: 2014-03-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.27, 5.5.25-galera
Fix Version/s: 10.0.10

Type: Bug Priority: Major
Reporter: Risato Stefano Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None
Environment:

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.

 Comments   
Comment by Elena Stepanova [ 2012-11-27 ]

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)

Comment by Risato Stefano [ 2012-11-28 ]

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.

Comment by Michael Widenius [ 2014-01-26 ]

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.

Comment by Michael Widenius [ 2014-01-27 ]

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.

Comment by Michael Widenius [ 2014-02-11 ]

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

Comment by Michael Widenius [ 2014-03-10 ]

Pushed some time ago into 10.0 tree

Generated at Thu Feb 08 06:51:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.