[MDEV-15925] FRM_MAX_SIZE too low for some use cases Created: 2018-04-18  Updated: 2020-08-25  Resolved: 2019-01-21

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Partitioning, Server
Affects Version/s: 10.1.36, 10.2.14, 10.3.7
Fix Version/s: 10.1.38, 10.0.38, 10.2.22, 10.3.13

Type: Bug Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: alter, create_table

Attachments: File tp.sql    
Issue Links:
Relates
relates to MDEV-6224 Incorrect information in file when *.... Closed

 Description   

As part of MDEV-6224, FRM_MAX_SIZE was increased to 512 KB. If a CREATE TABLE statement exceeds that, then the user sees an error message like the following:

Error Code: 1967. The definition for table `t` is too big

This limit is a bit low for some use cases. Especially for some Spider tables that have a lot of partitions, and which have long COMMENT strings that say on which server and which table each partition is located.

 Comments   
Comment by Valerii Kravchuk [ 2018-12-26 ]

It seems even smaller CREATE TABLE (359791 bytes in .sql form) with many (4000) partitions by LIST of long strings (content can not be shared in public) can not be successfully processed:

C:\Program Files\MariaDB 10.3\bin>mysql -uroot -proot -P3316 test <d:\big_table.sql
 
ERROR 1967 (HY000) at line 1: The definition for table `t_2277` is too big

Moreover, it seems the limit is not enforced so strict when ALTER TABLE is executed to add partitions one by one, as that way the table can be created that later can NOT be recreated from the output of SHOW CREATE TABLE.

Comment by Sergei Golubchik [ 2019-01-03 ]

Could you please provide a test case how ALTER TABLE allows to bypess the frm size limit?

Comment by Valerii Kravchuk [ 2019-01-03 ]

I was my assumption that ALTER may allow that. In fact the following test on 10.1.34 shows ALTER respects the limit:

MariaDB [test]> create table tp (id varchar(36), dt datetime, rest varchar(36)) engine=InnoDB partition by list columns(id) (partition p0 values in ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbb00'));
 
Query OK, 0 rows affected (0.37 sec)
 
 
 
openxs@ao756:~/dbs/maria10.1$ for i in `seq 1 8000`; do echo $i; bin/mysql -uroot --socket=/tmp/mariadb.sock test -e"alter table tp add partition (partition p$i values in ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb$i'))";  done
 
 
 
...
 
4105
 
ERROR 1967 (HY000) at line 1: The definition for table `tp` is too big
 
4106
 
ERROR 1967 (HY000) at line 1: The definition for table `tp` is too big
 
4107
 
...

That for loop eventually fails after creating 4084 partitions:

...
 
 PARTITION p4083 VALUES IN ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb4083') ENGINE = InnoDB,
 
 PARTITION p4084 VALUES IN ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb4084') ENGINE = InnoDB) */
 
1 row in set (0.19 sec)
 
 
 
MariaDB [test]> select 512*1024;
 
+----------+
 
| 512*1024 |
 
+----------+
 
|   524288 |
 
+----------+
 
1 row in set (0.03 sec)

The resulting .frm file is withing limits and I can dump and restore the table on 10.1.x:

openxs@ao756:~/dbs/maria10.1$ ls -l data/test/tp.frm
 
-rw-rw---- 1 openxs openxs 524235 січ  3 16:06 data/test/tp.frm
 
openxs@ao756:~/dbs/maria10.1$ bin/mysqldump -uroot --socket=/tmp/mariadb.sock test tp >/tmp/tp.sql
 
openxs@ao756:~/dbs/maria10.1$ ls -l /tmp/tp.sql
 
-rw-rw-r-- 1 openxs openxs 346870 січ  3 16:24 /tmp/tp.sql
 
openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot --socket=/tmp/mariadb.sock test2 </tmp/tp.sql
 
openxs@ao756:~/dbs/maria10.1$

But when I try to restore the same dump on 10.3.7 on Windows, it fails:

C:\Program Files\MariaDB 10.3\bin>mysql -uroot -proot -P3316 test <h:\tp.sql
 
ERROR 1967 (HY000) at line 25: The definition for table `tp` is too big

See tp.sql uploaded.

To summarize, we have two problems confirmed by my test:

1. 512K limit is too small (so we need it increased).

2. ALTER on 10.1.x allows to create partitioned table that can not be re-created from the dump on 10.3.7.

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