MySQL Bugs: #62075: mtr tests should not have to save & reset innodb_file_format

Bug #62075	mtr tests should not have to save & reset innodb_file_format_check
Submitted:	3 Aug 2011 17:37	Modified:	6 Oct 2011 3:31
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Tools: MTR / mysql-test-run	Severity:	S3 (Non-critical)
Version:	5.1.52	OS:	Any
Assigned to:	Bjørn Munch	CPU Architecture:	Any
Tags:	innodb, mtr

Description:
People writing mtr tests shouldn't have to waste time saving and resetting innodb_file_format_check

here is one example -- http://bugs.mysql.com/bug.php?id=50442

Per google, this my.cnf variable has caused too many problems:
http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=site%3Abugs.mysql.com+innodb_file_...

 grep -l file_format_check /s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/*
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug36172.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug52745.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug53591.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug54679.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-index.test
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test

Yet many of them do

/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test:let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test:eval set global innodb_file_format_check=$innodb_file_format_check_orig;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test:let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test:eval set global innodb_file_format_check=$innodb_file_format_check_orig;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug36172.test:let $file_format_check=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug36172.test:EVAL SET GLOBAL innodb_file_format_check=$file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# "innodb_file_format_check" with a
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# Save the value (Antelope) in 'innodb_file_format_check' to
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# 'old_innodb_file_format_check'
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set @old_innodb_file_format_check=@@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# @old_innodb_file_format_check shall have the value of 'Antelope'
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:select @old_innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# Reset the value in 'innodb_file_format_check' to 'Barracuda'
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = Barracuda;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# Set 'innodb_file_format_check' to its default value, which
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = DEFAULT;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# Put the saved value back to 'innodb_file_format_check'
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = @old_innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:# Check whether 'innodb_file_format_check' get its original value.
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = cheetah;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = Bear;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = on;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug47167.test:set global innodb_file_format_check = off;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug52745.test:let $file_format_check=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug52745.test:EVAL SET GLOBAL innodb_file_format_check=$file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug53591.test:let $file_format_check=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug53591.test:EVAL SET GLOBAL innodb_file_format_check=$file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug54679.test:let $file_format_check=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_bug54679.test:EVAL SET GLOBAL innodb_file_format_check=$file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:set global innodb_file_format_check=antelope;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:set global innodb_file_format_check=barracuda;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:set global innodb_file_format_check=cheetah;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:set global innodb_file_format_check=default;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb_file_format.test:set global innodb_file_format_check=antelope;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-index.test:let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-index.test:eval set global innodb_file_format_check=$format;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-index.test:eval SET GLOBAL innodb_file_format_check=$innodb_file_format_check_orig;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:set global innodb_file_format_check=`Antelope`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:set global innodb_file_format_check=`Antelope`;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:select @@innodb_file_format_check;
/s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-zip.test:eval set global innodb_file_format_check=$innodb_file_format_check_orig;
[mcallaghan@dev1456 /s/bld/b62037_ignore/mysql-test] less /s/src/mysql-5.1.52/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test

How to repeat:
read mtr tests or try to write one and spend too much time dealing with this

Suggested fix:
let me write mtr tests with dealing with this

The context for this is that I am writing an mtr test in which innodb_plugin crashes and recovers, at test start and test end innodb_file_format='Barracuda', but I must do 'set innodb_file_format_check="Antelope"' at test end or I get the following.

--- /s/bld/b62037_ignore/mysql-test/var/tmp/check-mysqld_1.result	2011-08-03 20:39:41.000000000 +0300
+++ /s/bld/b62037_ignore/mysql-test/var/tmp/check-mysqld_1.reject	2011-08-03 20:40:01.000000000 +0300
@@ -105,7 +105,7 @@
 INNODB_FAST_FREE_LIST	OFF
 INNODB_FAST_SHUTDOWN	1
 INNODB_FILE_FORMAT	Barracuda
-INNODB_FILE_FORMAT_CHECK	Antelope
+INNODB_FILE_FORMAT_CHECK	Barracuda
 INNODB_FILE_PER_TABLE	ON
 INNODB_FLUSH_LOG_AT_TRX_COMMIT	1
 INNODB_FLUSH_METHOD

Thank you for the problem report.

Yes, they should in order for tests to run faster. Having the server back to its initial state allows the same server to be used to run another test case. You don't really waste time on this, mtr even tells you that you forgot to reset something back to it's original value.

Davi - I don't argue against the check. I just think the check should be correct and not require me to deal with innodb_file_format_check when my test never changed it. You assume all of those tests modify the variables. They don't. Why does mysql-test/t/innodb_plugin/innodb-autoinc.test have to reset it? The only refs to innodb_file_format_check are:

let $innodb_file_format_check_orig=`select @@innodb_file_format_check`;
eval set global innodb_file_format_check=$innodb_file_format_check_orig;

This seems to be a problem mainly for tests that crash/restart mysqld. I have many tests in the facebook patch that do that but official mysql does not.

In this case, it does not matter if your test is explicitly changing it, the check also catches side effects. The side effect probably arises from the restarts where InnoDB plugin decides to change the initial value of the variable. I would say that the something odd here is innodb_file_format_check magically changing value after a restart.

I agree

A test case:

-- source include/have_innodb_plugin.inc

SELECT 1;
-- source include/restart_mysqld.inc
-- source include/restart_mysqld.inc

"The file format tag is a “high water mark”, and as such it is increased after the server is started, if a table in a “higher” format is created or an existing table is accessed for read or write (assuming its format is supported)."

After the second restart the tag gets updated…

If a change of this variable is actually harmless and a "natural" side effect, an alternative is to have mtr explicitly ignore it when doing its check, like it currently ignores 'timestamp'.

In addition to all of the mtr tests that have wasted time on this, this variable has been a too frequent source of employment for MySQL support:
http://bugs.mysql.com/bug.php?id=47167
http://bugs.mysql.com/bug.php?id=49792
http://bugs.mysql.com/bug.php?id=55095
http://bugs.mysql.com/bug.php?id=53654
http://bugs.mysql.com/bug.php?id=50442
http://bugs.mysql.com/bug.php?id=53472

Will look into having mtr ignore this (and amending tests accordingly). Looks like this may be different in 5.5

The "innodb_file_format_check" is indeed a source of confusion, as Mark pointed out, several bugs have been filed trying to clarify it. Fortunately, this has been changed in 5.5.

One confusion came from its name, "innodb_file_format_check", it actually plays two roles 
1) Check whether to do file format check at server start up time.
2) Record the maximum file format encountered(opened) during server life time.

The 2) actually could silently change the value even though user has not changed the configure options.

To resolve this confusion (at least for naming), we splitted the "innodb_file_format_check" into "innodb_file_format_check" and "innodb_file_format_max", so to play their respective roles. So "innodb_file_format_check" will only play the role of deciding whether server will do file format check at server startup time. The max file format encountered is decided by "innodb_file_format_max".

This change has been only approved to check in 5.5, so it was not backported to 5.1, since the change could affect in market product configure options. However, I feel if the 5.1 "innodb_file_format_check" continue causing confusion/questions, we should back port the change.

Following is detail description of the reason for 5.5 splitting, and it certainly will explain the confusion points raised:

======================================================================
Before this change, "innodb_file_format_check" overloaded two sets of configure options and played some dual role of the two options in the startup and run time, and thus could cause some confusion. This change separates out the two different options, so they can be used/documented with more clarity.

Regarding file format, now we have following configure options (system variables):

1. innodb_file_format_check - this option is a start up command line option (can be set in configure file) only, during run time, it is read only. This option tells server whether it needs to check the file format in file system table, and compare it with DICT_TF_FORMAT_MAX (the hard coded highest file format supported for this server) and block the server start up.

The actual check is performed in trx_sys_file_format_max_check().

2. the newly added "innodb_file_format_max" - this system variable can be set during startup time as well as at run time. It is a mirror mapping with the file format tag in the system table space. Interestingly, it is a value can be changed in several occasions:
a) During first time start up with a newly build server installation, it is initialized as  DICT_TF_FORMAT_51 ("Antelope") by trx_sys_file_format_init().

b) By user using "set global innodb_file_format_max=..." with innodb_file_format_max_update().

c) By creating/opening a table with newer file format than the current value using trx_sys_file_format_max_upgrade():
ha_innobase::open()/ha_innobase::create()
{
        .....
        if (prebuilt->table) {
                /* We update the highest file format in the system table
                space, if this table has higher file format setting. */

                trx_sys_file_format_max_upgrade(
                        (const char**) &innobase_file_format_max,
                        dict_table_get_format(prebuilt->table));
        }
}

The "innodb_file_format_check" used to assume the role of this "innodb_file_format_max" during run time, however, it is hard to document the dual role without some confusion, even without the complexity of this "innodb_file_format_max" variable.

By looking at this variable, we notice it can be changed automatically by the server, if server discovers a newer file format than the current value. So it is a "settable" "high water marker" in the server. By allowing user to set it, it provides a means for user to access the tag in the system file space. So users can manually downgrade it if they decided to downgrade the server (boot a newer database installation with older server).

3) innodb_file_format - It is a dynamic variable used to specify file format for new InnoDB tables. We have not change any aspect of this variable. As documented, "Currently Antelope and Barracuda  are supported. This applies only for tables that have their own tablespace, so for it to have an effect innodb_file_per_table must be enabled."

In summary, by adding the innodb_file_format_max, innodb_file_format_check will really play the role of deciding whether to check the file format constraint. And innodb_file_format_max will act the role of a "high water mark" representation of the file format used in the server. Its behavior described above should be well documented.
======================================================================

Bjorn is filtering the innodb_file_format_check value change in mtr, it certainly is one solution (go around). In 5l5, he might need to screen out the innodb_file_format_max value.

Changes for test suite. No changelog entry needed.