[MDEV-31370] some coloums‘ length of varchar is reduced from 200 to 20,run create table sql raises exception Created: 2023-05-31  Updated: 2023-05-31  Resolved: 2023-05-31

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.3.31
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Wenwen Jing Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 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

 Comments   
Comment by Daniel Black [ 2023-05-31 ]

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

Comment by Daniel Black [ 2023-05-31 ]

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

Generated at Thu Feb 08 10:23:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.