Not the answer you need?
Register and ask your own question!

Implicit row_format change (dynamic => compact) on 'alter table'

tholewebgodstholewebgods EntrantInactive User Role Beginner
Background: The tables were created on a system with this setup:
| innodb_file_format         | Barracuda         |
 | innodb_file_format_check   | ON                |
 | innodb_file_format_max     | Barracuda         |

All tables were created with the option "row_format=DYNAMIC" which was neccessary for some bigger tables.


Then I've transfered the data using a SST to an XtraDB cluster with this config:
| innodb_file_format         | Antelope          |
 | innodb_file_format_check   | ON                |
 | innodb_file_format_max     | Barracuda         |


Eventually I had to alter one table (change a column type) which caused MySQL to accidently change the "row_format":
+---------+------+--------------------------------------------------------------------+
 | Level   | Code | Message                                                            |
 +---------+------+--------------------------------------------------------------------+
 | Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. |
 | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                               |
 +---------+------+--------------------------------------------------------------------+

For that table the change was not relevant (I think). But in the future I'd like to avoid implicit changes (an possible dataloss) on tables where "row_format=DYNAMIC" is important.

Thus I'd like to change "innodb_file_format" to "Barracuda" on the target system.

- First I were astounded that MySQL changed the "row_format" becuase "innodb_file_format_max=Barracuda" probably means the server is capable managing tables of this type - why does MySQL change it?
- Is my assumption correct that MySQL won't change the "row_format" on the next "alter table" after changing to "Barracuda"?
- Is there any risk of data loss when setting "innodb_file_format_max=Barracuda"?

The server is
Server version: 5.5.41-37.0-55-log Percona XtraDB Cluster (GPL), Release rel37.0, Revision 853, WSREP version 25.11, wsrep_25.11.r4027
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.