MySQL Bugs: #75576: No way to set default row format

Bug #75576	No way to set default row format
Submitted:	21 Jan 2015 15:59	Modified:	22 Jan 2015 6:21
Reporter:	Phil Stracchino	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The DYNAMIC row format offers benefits over the COMPACT row format in many cases, especially when converting to InnoDB from legacy MyISAM tables in which excessive numbers of BLOB/TEXT columns have been used.  Using DYNAMIC row format requires using Barracuda file format, which in turn requires innodb_file_per_table and of course a version of the InnoDB engine which supports the Barracuda file format.

Both MySQL 5.5 and 5.6 (at least) support barracuda, and in MySQL 5.6 innodb_file_per_table is enabled by default, although the default file format even in 5.6 is inexplicably still Antelope.  This default, however, can be changed using the innodb_file_format directive in the my.cnf file.

However, there is NO WAY, as far as I can tell, to specify a default row format.  If it is desired to use DYNAMIC row format, that must be specified explicitly in every CREATE TABLE statement.  This seems a conspicuous omission.

How to repeat:
"Repeating" this is simply a matter of reading the documentation on InnoDB file formats and row formats, and then searching for any way to set a default row format.  It appears there is none.  If one exists, it is well hidden.

Suggested fix:
Could we please have a way to set the default ROW_FORMAT?

The obvious change here would be to add an innodb_row_format variable to go along with innodb_file_format.  It should be able to be dynamically set, should be used as the default in all CREATE TABLE statements where ENGINE=InnoDB if it is set AND innodb_file_format is set to Barracuda or higher, and should of course not allow itself to be set to DYNAMIC or COMPRESSED *unless* innodb_file_format is set to Barracuda or higher.

There *is* a workaround, which is to remember to explicitly specify row_format in every CREATE TABLE or do an ALTER TABLE pass after the fact, but this would realistically require code changes in a very large number of applications.

also: http://bugs.mysql.com/bug.php?id=58123

Hello Phil Stracchino,

Thank you for the report.
This is duplicate of Bug #58123

Thanks,
Umesh

Marked as duplicate of Bug #58123

It is available to change default row format for innodb since MySQL 5.7.9 via directive innodb_default_row_format. See https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_default_row_f...