MySQL Bugs: #53654: after 2nd shutdown innodb_file_format

Bug #53654	after 2nd shutdown innodb_file_format_check attains strange values
Submitted:	14 May 2010 16:12	Modified:	12 Jul 2010 17:04
Reporter:	Mikhail Izioumtchenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S2 (Serious)
Version:	1.0, 1.1	OS:	Any
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
the default of innodb_fast_shutdown=1 is used below. I don't know what happens with the slow shutdown.

1. start mysqld using innodb Plugin (I tried with 1.0.7 and 1.1.0), when
no InnoDB database exists, so a brand new one is created.
2. show the value of innodb_file_format_check with
  mysqladmin variables | grep file_format. 
  The value is 'antelope'
3. shutdown mysqld normally
4. repeat steps 1,2,3 above. innodb_file_format_check is still 'antelope'
5. again repeat steps 1,2,3 above. innodb_file_format_check is now one of
'antelope', 'barracuda' or 'cheetah', depending on the circumstances.

observations:
1. whatever value you see in step 5, it will stay through subsequent shutdowns
2. the bug doesn't seem to depend on the value for innodb_file_format.
I tried antelope and barracuda.
3. The bug does depend on the value of --innodb-file-format-check that you
pass to the server, the results for mysqld started with
--innodb-file-format=antelope:

--innodb-file-format-check=antelope or 'on': stays as antelope on step 5
--innodb-file-format-check=barracuda or not supplied at all: becomes barracuda on step 5.
--innodb-file-format-check=off: becomes cheetah on step 5.

I can't say I fully understand the workings of innodb-file-format-check but
different values after the first and second shutdown with no database 
activity at all looks wrong.
  

How to repeat:
see above

a few more experiments with 5.1.46:

- same results with --innodb-fast-shutdown=0 and 2
- same results when mysqld is killed -9, instead of normal shutdown. 

My opinion is that with no database activity innodb_file_format_check
as seen at runtime should stay at antelope.

If I understand correctly, this could be how the parameter is supposed to work. This parameter was implemented by Calvin Sun while I was on leave. See also Bug #49792 and the last paragraph of what I filed originally as Mantis issue 97, copied below:

The variable innodb_file_format_check is documented as "The highest file format in the tablespace." This probably means "the file format stamped to the system tablespace".

I had to read the manual to understand how the variable is supposed to work: "You can also set innodb_file_format to a file format name. Doing so will prevent InnoDB from starting if it does not support the file format specified. It also sets the “high water mark” to the value you specify."

The variable seems to have two different purposes, depending on when it is assigned to. As a startup option, it would check the file format stamped in the transaction system header. When set as a global variable, it will overwrite the file format stamp, no matter if there exist tables in a newer format. I don't think that the dual purpose (checking the stamp, or overwriting the stamp) is a good idea.

One fix that would have little impact on users would be to make innodb_file_format_check read-only at runtime, and to introduce a settable variable, such as innodb_file_format_stamp for reading or setting the stamp in the ibdata* file. That fix would only affect those users who attempt to modify the stamp, something that should only be needed in certain downgrade operations.

I agree that the dual purpose variable is not a good idea,
and that it is hard to understand the workings of innodb_file_format_check.
I doubt that this is how it was supposed to work, however. 
Nothing can be supposed to work by acquiring different values
under what looks like the same circumstances, also how can Cheetah appear?

Sure, Calvin & Marko, we will separate out the two options.

Michael, the reason that it sets "cheetah" for "off" is following:

In innobase_file_format_check_on_off(), it will set srv_check_file_format_at_startup to 1 + max format allowed, and max format allowed for zip is Barracuda, the next is Cheetah:

innobase_file_format_check_on_off()
{
        if (!innobase_strcasecmp(format_check, "off")) {

                /* Set the value to disable checking. */
                srv_check_file_format_at_startup = DICT_TF_FORMAT_MAX + 1;

}

And subsequently, in following call stack the file_format_check is set to "Cheetah":
trx_sys_file_format_max_check
innobase_start_or_create_for_mysql
innobase_init

(gdb) p format_id
$14 = 2
(gdb) n
1193		file_format_max.name = trx_sys_file_format_id_to_name(format_id);
(gdb) 
1195		return(DB_SUCCESS);
(gdb) p file_format_max.name
$15 = 0x8764f74 "Cheetah"

ok, so the value ends up max-allowed + 1, ok with me except why it's not 
Cheetah after the first restart, only after the second one?

mysql-trunk-innodb:
 3104 Jimmy Yang        2010-06-17
      This change splits innodb_file_format_check into innodb_file_format_check
      and innodb_file_format_max two system variables. And this also fixes
      bug #53654 after 2nd shutdown innodb_file_format_check attains strange
      values.

This is intended for new 5.5.5 release.

After the change, we have following two system variables:

1) innodb_file_format_check:
Default Value: 1
Permitted Values: 0,1
Type: Boolean
Dynamic Variable: No
Command-Line Format --innodb_file_format_check=1
Config-File Format innodb_file_format_check
Option Sets Variable No, readonly

2) innodb_file_format_max:
Default Value: Antelope
Permitted Values: Antelope, Barracuda etc.(any name for future file formats)
Type: String
Dynamic Variable: Yes
Command-Line Format --innodb_file_format_max=#
Option Sets Variable Yes, innodb_file_format_max

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."

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)

Noted in 5.5.5 changelog.

Previously, the innodb_file_format_check system variable could be set 
to 1 or 0 at server startup to enable or disable whether InnoDB 
checks the file format tag in the shared tablespace (for example,
Antelope or Barracuda). If the tag is checked and is higher than that
supported by the current version of InnoDB, an error occurs and
InnoDB does not start. If the tag is not higher, InnoDB sets the
value of innodb_file_format_check to the file format tag, which is
the value seen at runtime. This behavior overloads the functions of
whether to check the tag and recording the tag onto a single
variable. 

Now, checking and recording the file format tag are handled using
separate variables. innodb_file_format_check can be set to 1 or 0 at
server startup to enable or disable whether InnoDB checks the file
format tag in the shared tablespace. If the tag is checked and is 
higher than that supported by the current version of InnoDB, an error
occurs and InnoDB does not start. If the tag is not higher, InnoDB
sets the value of innodb_file_format_max to the file format tag.