MDEV-14904 Backport innodb_default_row_format

InnoDB in Debian uses utf8mb4 as default character set since
version 10.0.20-2. This leads to major pain due to keys longer
than 767 bytes.

MariaDB 10.2 (and MySQL 5.7) introduced the setting
innodb_default_row_format that is DYNAMIC by default. These
versions also changed the default values of the parameters
innodb_large_prefix=ON and innodb_file_format=Barracuda.
This would allow longer column index prefixes to be created.
The original purpose of these parameters was to allow InnoDB
to be downgraded to MySQL 5.1, which is long out of support.

Every InnoDB version since MySQL 5.5 does support operation
with the relaxed limits.

We backport the parameter innodb_default_row_format to
MariaDB 10.1, but we will keep its default value at COMPACT.
This allows MariaDB 10.1 to be configured so that CREATE TABLE
is less likely to encounter a problem with the limitation:

	loose_innodb_large_prefix=ON
	loose_innodb_default_row_format=DYNAMIC

(Note that the setting innodb_large_prefix was deprecated in
MariaDB 10.2 and removed in MariaDB 10.3.)

The only observable difference in the behaviour with the default
settings should be that ROW_FORMAT=DYNAMIC tables can be created
both in the system tablespace and in .ibd files, no matter what
innodb_file_format has been assigned to. Unlike MariaDB 10.2,
we are not changing the default value of innodb_file_format,
so ROW_FORMAT=COMPRESSED tables cannot be created without
changing the parameter.

1 files changed, 85 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/default_row_format_alter.result b/mysql-test/suite/innodb/r/default_row_format_alter.result
new file mode 100644
index 00000000000..1f4f78ee670
--- /dev/null
+++ b/mysql-test/suite/innodb/r/default_row_format_alter.result
@@ -0,0 +1,85 @@
+SET @row_format = @@GLOBAL.innodb_default_row_format;
+####################################
+# Check if table rebuilding alter isn't affect if table is created
+# with explicit row_format
+CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB;
+INSERT INTO t1 VALUES (1, 'abc');
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Compact	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL	row_format=COMPACT	
+SET GLOBAL innodb_default_row_format=DYNAMIC;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
+# Here we expect COMPACT because it was explicitly specified at CREATE
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Compact	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL	row_format=COMPACT	
+DROP TABLE t1;
+####################################
+# Check if table rebuilding alter is affected when there is no
+# row_format specified at CREATE TABLE.
+SET GLOBAL innodb_default_row_format = COMPACT;
+CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1, 'abc');
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Compact	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL		
+SET GLOBAL innodb_default_row_format = DYNAMIC;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
+# Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the
+# default_row_format is changed to DYNAMIC just before ALTER
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Dynamic	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL		
+DROP TABLE t1;
+####################################
+# Check the row_format effect on ALTER, ALGORITHM=COPY
+SET GLOBAL innodb_default_row_format = REDUNDANT;
+CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Redundant	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL		
+SET GLOBAL innoDB_default_row_format = COMPACT;
+ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
+# Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't
+# created with explicit row_format, so we expect ROW_FORMAT=COMPACT
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Compact	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL		
+DROP TABLE t1;
+
+###################################
+#  Check the row_format effect on ALTER, ALGORITH=COPY on
+# create table with explicit row_format
+CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB;
+INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Redundant	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL	row_format=REDUNDANT	
+SET GLOBAL innoDB_default_row_format = COMPACT;
+ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
+# Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is
+# created with explicit row_format, so we expect original
+# ROW_FORMAT=REDUNDANT
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Redundant	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL	row_format=REDUNDANT	
+DROP TABLE t1;
+
+##################################
+# Check row_format on ALTER ALGORITHM=INPLACE
+SET GLOBAL innodb_default_row_format=COMPACT;
+CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Compact	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL		
+SET GLOBAL innodb_default_row_format=DYNAMIC;
+ALTER TABLE t1 DROP INDEX k1;
+# Because it is in-place operation, there is no rebuild, so the
+# original format has to be retained.
+SHOW TABLE STATUS LIKE 't1';
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
+t1	InnoDB	#	Compact	#	#	#	#	#	#	NULL	#	NULL	NULL	latin1_swedish_ci	NULL		
+DROP TABLE t1;
+SET GLOBAL innodb_default_row_format = @row_format;