I want to I want to upgrade a db from Maria10.1, to Maria10.2, when I run a migration I get the 'Mysql Row size too large' error when creating some tables.
One table that is throwing this error has 40 fields defined as:
varchar(250) DEFAULT NULL
The table itself is:
ENGINE=InnoDB DEFAULT CHARSET=latin1
I think I understand the core issue; innodb will try to store varchar fields with a size < 255 ‘inline’, if a table has many columns like this it results in a row size that is too large to fit within a single database page and so the 'row size' error occurs, the error does not occur when creating this table on Maria10.1.
I have 2 solutions that I have found to work:
- Change column definition to a type (or size) that will be stored ‘off page’ TEXT, BLOB, or VARCHAR size > 255, this allows each column to be stored within the row as a 20 byte reference rather than storing the column entirely within the row.
- Set innodb_page_size=64k
This is fine for new databases, however I have existing databases that need to be upgraded as they run on MariaDB 10.1 which is no longer supported. These existing databases are big and applying these solutions would be difficult; for solution 2 backing up and importing terabytes of data is ’risky’ and not easy, for solution 1 there is possibly performance to consider; maybe slower queries and increased disk space requirements?
My questions:
- Firstly; is there an alternative solution that can be used for large established databases tat will provide an easier upgrade path?
- Secondly; I may have to go with solution 1, if I do how can I mitigate the performance/storage impact/will there be a significant performance/storage impact?
Some other relevant information:
All tables are innodb
turning off innodb_strict_mode is not an option
Many thanks for looking!
CodePudding user response：
I was able to reproduce the error on MySQL 5.7.34 (I don't use MariaDB).
I was able to resolve the error by changing the table to ROW_FORMAT=COMPRESSED and changing the columns to data type TEXT instead of VARCHAR. Both changes are needed. Compressed row format with varchar columns results in the same error about row size.
You probably already know this, but you can't change the InnoDB page size for just one table. To change the page size, you would have to dump all your tables (or temporarily alter them to MyISAM or another storage engine), then shut down mysqld, remove the InnoDB tablespaces and logs, change the page size option, then restart mysqld. It will initialize new InnoDB tablespaces and logs with the new page size on startup. Then you can alter your tables back to InnoDB. Obviously, this interrupts service for a long time.
Alternatives might be to set up another server instance with the greater page size, and store just this single table in it.
But I agree with the comment above from O.Jones — you should reconsider your table design that stores 40 long string columns in the same row. That kind of design is usually the result of improper normalization.
CodePudding user response：
forty fields defined as varchar(250) DEFAULT NULL
So SELECT MAX(LENGTH(col1)), ... for some of the likely columns.
Then devise a single ALTER that shrinks a few of the smallest columns to a smaller max and change any that are close to 250 to into TEXT.
Also, take note of whether the upgrade changed the CHARACTER SET away from latin1.
And note whether any of the columns you are about to change to TEXT are in any indexes. If so, let's discuss them in more detail.
(The ALTER will take a long time, but probably not as long as adding compression and certainly not as long as changing page size.)
MariaDB 10.3 introduced "Storage-engine Independent Column Compression", which may be useful.