MySQL Bugs: #78347: innodb_default_row_format: Undesireable new behaviour

Bug #78347	innodb_default_row_format: Undesireable new behaviour
Submitted:	6 Sep 2015 0:15	Modified:	22 Sep 2015 16:53
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I see in: http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html a reference to a new configuration option: innodb_default_row_format

Since 5.7.9 has not been released yet it's hard to know if I'm misreading the behaviour but from what I understand from the change notes 2 things change:

(1) the default setting changes from the previous implicit setting.
(2) When doing an ALTER TABLE a table built with a different row format will be rebuilt to use the new one.

Given the change any existing systems are likely to be hit by (2) when doing an ALTER TABLE. Note: there are a few types of changes to ALTER TABLE which up to now have not required rebuilding the table and thus were effectively no-ops. Changing a DEFAULT value, adding a new ENUM value to an existing ENUM column are a couple of examples.
So the change by adding this variable means that unless a huge amount of extra care is taken of explicitly mentioning the row format the table will now be rebuilt.

If you have a large number of tables or the tables are big this could be very disruptive as it may not be convenient to rebuild such tables immediately, or this may happen inadvertently.

How to repeat:
Read the docs.

Suggested fix:
I think an additional option should be created to optionally prevent this behaviour (by default disabled).

innodb_avoid_implicit_row_format_changes default 0

If set to 1 any existing ALTER TABLE which would have been a no-op type ALTER TABLE (that is one that would not have triggered a table rebuild) will behave the same and not trigger a rebuild to the new row format.

If the ALTER TABLE behaviour would have had to rebuild the table: e.g. adding a new column or index, or changing the type or size of an existing column then the previous expectation would have been to rebuild the table so this same process can happen and that would at the same time change the row_format as specified by innodb_default_row_format

Please consider adding this new variable. I think it is very important.
I can provide further details if needed.

The result of my suggestion is that the new 5.7 default can be left as it is. If people do not care then any tables using the old row format will have their tables upgraded with an ALTER TABLE and for those of us who perhaps might find this more troublesome will still be able to perform some minor changes to tables without it being changed suddenly when we do not expect it.

Question: will this behaviour also affect mysql_upgrade? That is on a minor version change are you also planning on rebuilding such tables or for mysql_upgrade is this new behaviour not going to happen. The logic of the changes in 5.7.9 would imply that you would do the same thing and upgrade the tables. Doing so would rebuild the whole server on any server being upgraded from 5.6 to 5.7. That would be extremely disruptive and undesirable.

To be clear: configuring innodb_default_row_format to the original value of COMPACT does not really solve the problem as it has has the same problem of making it hard for us to use the new format (assuming this new format is better), so we'll end up never moving over to the default value that Oracle wants us to use.

Hence my suggestion which would allow new tables to use the default format if this is not specified and existing ones to be changed when we want to but not otherwise.

Hi Mr. Simon Mudd,

I have analyzed your bug and the reasoning for the deprecation of the variable and I do agree with your findings.

I am now handing over your request to developers for the decision-making.

Hopefully, we shall hear from them soon.

Hi Simon,

I tried a few combinations of changes:
1) Add item to enum definition
2) Add index to table
3) Add column to table
4) Optimize table

Only 3 and 4 rebuilt the table internally, and this is somewhat expected since modifying the primary key requires a rebuild.

So previous no-ops appear to remain no-ops, but copies will now change the row format.  These can be avoided by using the assertion syntax for algorithm=inplace.

mysql [localhost] {msandbox} (test) > show table status\G
*************************** 1. row ***************************
           Name: a1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1029207
 Avg_row_length: 354
    Data_length: 364707840
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: 1376221
    Create_time: 2015-09-08 15:42:50
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > set global innodb_default_row_format='dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE a1 CHANGE c c enum ('a', 'b', 'c', 'd') not null, lock=none, algorithm=inplace;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > show table status\G
*************************** 1. row ***************************
           Name: a1
         Engine: InnoDB
        Version: 10
     Row_format: Compact <-- remained compact
           Rows: 1029207
 Avg_row_length: 354
    Data_length: 364707840
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: 1376221
    Create_time: 2015-09-08 15:44:18
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE a1 ADD INDEX (b);
Query OK, 0 rows affected (15.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > show table status\G
*************************** 1. row ***************************
           Name: a1
         Engine: InnoDB
        Version: 10
     Row_format: Compact <-- remained compact
           Rows: 1029207
 Avg_row_length: 354
    Data_length: 364707840
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 1376221
    Create_time: 2015-09-08 15:51:03
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE a1 ADD new_column INT NOT NULL;
Query OK, 0 rows affected (16.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > show table status\G
*************************** 1. row ***************************
           Name: a1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1028820
 Avg_row_length: 360
    Data_length: 370999296
Max_data_length: 0
   Index_length: 333447168
      Data_free: 2097152
 Auto_increment: 1376221
    Create_time: 2015-09-08 15:52:32
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > set global innodb_default_row_format='compact';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > optimize table a1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.a1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.a1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (18.79 sec)

mysql [localhost] {msandbox} (test) > show table status\G
*************************** 1. row ***************************
           Name: a1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1028820
 Avg_row_length: 360
    Data_length: 370999296
Max_data_length: 0
   Index_length: 333447168
      Data_free: 2097152
 Auto_increment: 1376221
    Create_time: 2015-09-08 15:54:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

Hi Morgan,

Thanks for doing your tests. If the behaviour is as you describe and the "table rebuild" is not triggered just because the innodb_row_format does not match the "expected" value then there is no issue. Perhaps the documentation is incorrect.

I did say I had not tested the behaviour as I was basing my comments on the release notes for 5.7.9 so this may not be an issue.

I do not remember all the cases where the table definition changes and no rebuild is required, but the typical uses are:
* add new enum values to an existing enum column
* change default settings for a column
* adding comments to a column or table

You show the first does not trigger a table rebuild and assuming the second does not either (and other "no-op" changes do not either) then I think I'm happy.

The documentation seemed to imply a behaviour which looked bad. If that behaviour does not exist I will be happy and would also suggest that the wording is adjusted to be clearer.

I will also experiment with 5.7.9 when it is released but am confident that your findings reflect a behaviour which I would be happy with.

Hi Simon,

Thiru from our team volunteered to write a testcase to show that non-rebuilding ALTERs continue to be non-rebuilding even after a defaults change.

This covered the three cases you've mentioned and also other cases.

Here is the result file of the testcase:

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1]  - 'localhost:13000' was not free
worker[1] Using MTR_BUILD_THREAD 301, with reserved ports 13010..13019
SET GLOBAL innodb_default_row_format=COMPACT;
# Table with  compact format
create table t1(f1 int not null, f2 int not null,
f3 char(200), f4 enum ('first','second'), primary
key(f1))engine=innodb;
insert into t1 values(1, 1, 'hooli', 1);
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

# Changed the default row format to dynamic

SET GLOBAL innodb_default_row_format=dynamic;

# Issue Non-rebuilding DDL query.

alter table t1 add index (f2), algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 rename index f2 to idx1, algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 drop index idx1, algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 change column f3 subject char(200), algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 change column subject subject char(200) default '0',
algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 stats_persistent = 0, algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 comment 'Test Table', algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 modify column subject char(200) comment 'Test data
column', algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single

alter table t1 modify column f4 enum ('first', 'second', 'third'),
algorithm=inplace;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size	Space Type
test/t1	test/t1	1	7	Compact	0	Single
drop table t1;
main.non-rebuilding-alter                [ pass ]     71
--------------------------------------------------------------------------

Simon,

In view of the latest clarifications on the subject, would you agree that this bug is converted into the documentation bug ????

It does indeed seem like a documentation bug.
So thanks for taking the time to confirm that.

When I get hold of a copy of 5.7.9 I'll be able to verify for myself, but I am pleased there is no change in behaviour because of the new default innodb setting.

Dear Mr. Simon,

Thank you for your feedback. This is documentation bug from now on.

I can reproduce Morgan's "8 Sep 19:57" post.

I face this behavior in upgrading 5.6.26.

1. `create table t1 (num serial);` on 5.6.26
2. shutdown 5.6.26
3. start 5.7.9 with datadir which is used by 5.6.26
4. mysql_upgrade

```
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-09-17 00:50:21
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ADD val varchar(32);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-09-17 00:51:37
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
```

mtr for "ADD COLUMN", "ADD KEY", "ADD KEY ALGORITHM= COPY"

Attachment: bug78347.test (application/octet-stream, text), 1.01 KiB.

mtr result.

```
SELECT TABLE_ID, NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE 'test/%';
TABLE_ID        NAME    ROW_FORMAT
36      test/t1 Compact
37      test/t2 Compact
38      test/t3 Compact
SET GLOBAL innodb_default_row_format= 'Dynamic';

"Add new column"
ALTER TABLE t1 ADD c3 int;
SELECT TABLE_ID, NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME= 'test/t1';
TABLE_ID        NAME    ROW_FORMAT
39      test/t1 Dynamic

"Add new index"
ALTER TABLE t2 ADD KEY(c2);
SELECT TABLE_ID, NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME= 'test/t2';
TABLE_ID        NAME    ROW_FORMAT
37      test/t2 Compact

"Add new index with Copying table"
ALTER TABLE t3 ADD KEY(c2), ALGORITHM= COPY;
SELECT TABLE_ID, NAME, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME= 'test/t3';
TABLE_ID        NAME    ROW_FORMAT
40      test/t3 Dynamic
```

The following documentation has been updated:

(1) Release note 
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html 

(2) Specifying the Row Format for an InnoDB Table
http://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html

(3) inndb_default_row_format
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_default_row_fo...

(4) Upgrading from MySQL 5.6 to 5.7 
https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

Thank you for the bug report.