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