MySQL Bugs: #35269: mysqlcheck behaves different depending on order of parameters

Bug #35269	mysqlcheck behaves different depending on order of parameters
Submitted:	13 Mar 2008 14:45	Modified:	16 Nov 2010 1:55
Reporter:	Rob Brucks	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.22, 5.0, 5.1 BK, 5.1.46	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	mysqlcheck

Description:
"mysqlcheck -aoc" appears to work properly, but "mysqlcheck -aco" runs very fast, appearing to skip either the optimize or check steps or both.

Also, "mysqlcheck -aoc" does not generate any error messages about views, but "mysqlcheck -aco" generates "error: <viewname> is not BASE TABLE" errors.

Same behavior observed when parameters are separated (i.e. "mysqlcheck -a -c -o").

How to repeat:
create a database of a few hundred megabytes
create a view on one of the tables
time mysqlcheck -aoc
time mysqlcheck -aco

Quick way to replicate on clean system:
rob@myplay:~$ mysql
mysql> create database foo;
mysql> use foo;
mysql> create table foo (a int);
mysql> insert into foo values (1);
mysql> insert into foo select * from foo;  (repeat this insert 22 times)
mysql> create view v_foo as select * from foo;
mysql> exit
rob@myplay:~$ time mysqlcheck -aoc foo
foo.foo                                            OK
foo.v_foo                                          OK

real    0m5.564s
user    0m0.000s
sys     0m0.080s
rob@myplay:~$ time mysqlcheck -aco foo
foo.foo                                            Table is already up to date
foo.v_foo
error    : 'foo.v_foo' is not BASE TABLE

real    0m0.042s
user    0m0.000s
sys     0m0.040s
rob@myplay:~$ time mysqlcheck -aoc foo
foo.foo                                            OK
foo.v_foo                                          OK

real    0m4.203s
user    0m0.020s
sys     0m0.010s

Hope this is more clear.

Thank you for the report.

But version 5.0.22 is old. Please upgrade to current version 5.0.51a and inform us if problem still exists.

No need to upgrade. Error still exists in current versions.

Workaround: run mysqlcheck with each of this options separately.

Customer complaint to the same behavior, so I looked into this behavior in detail with Debug version of mysqlcheck.

"mysqlcheck -aoc" appears to work properly, but "mysqlcheck -aco" runs very fast.

mysqlcheck have a variable "what_to_do" for determine what action will do.
But this variable overwrite by belows options,

enum operations { DO_CHECK, DO_REPAIR, DO_ANALYZE, DO_OPTIMIZE, DO_UPGRADE };

So only last option of above is valid.

Note that this behavior is documented in mysqlcheck --help: "The options -c,-r,-a and -o are exclusive to each other, which means that the last option will be used, if several was specified."

This behavior is pretty unintuitive and dangerous, however, so I will leave the bug open. If the options are exclusive, specifying more than one should fail.

Mysqlcheck should:

1) check the table
2) repair the table if corrupted 

And it should do this always if repair options are specified, even if other options are specified.

As it stands, 

========
C:\mysql5146\data\roelt\broke>copy * ..
test1.frm
test1.MYD
test1.MYI
        3 file(s) copied.

C:\mysql5146\data\roelt\broke>..\..\..\bin\mysqlcheck.exe -P5146 -uroot --check --auto-repair --all-databases  <-------- not using --optimize find the error immediately
[...]
roelt.test1
warning  : 1 client is using or hasn't closed the table properly
warning  : Size of datafile is: 36       Should be: 35
error    : got error: 0 when reading datafile at record: 5

--------

C:\mysql5146\data\roelt\broke>copy * ..
test1.frm
test1.MYD
test1.MYI
        3 file(s) copied.

C:\mysql5146\data\roelt\broke>..\..\..\bin\mysqlcheck.exe -P5146 -uroot --check --optimize --auto-repair --all-databases  <----- using --optimize does not find the error (same for subsequent runs - it reports the table as "Table is already up to date")
[...]
roelt.test1                                       OK

mysql> check table test1;
+-------------+-------+----------+-------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                        |
+-------------+-------+----------+-------------------------------------------------+
| roelt.test1 | check | warning  | Size of datafile is: 36       Should be: 35     |
| roelt.test1 | check | error    | got error: 0 when reading datafile at record: 5 |
| roelt.test1 | check | error    | Corrupt                                         |
+-------------+-------+----------+-------------------------------------------------+
3 rows in set (0.00 sec)

C:\mysql5146\data\roelt\broke>..\..\..\bin\mysqlcheck.exe -P5146 -uroot --check --optimize --auto-repair --all-databases  <----- same command, run again after table is established broken finds the table broken
roelt.test1
Error    : Table '.\roelt\test1' is marked as crashed and should be repaired
Error    : Table 'test1' is marked as crashed and should be repaired
error    : Corrupt
========

Also see bug #50041

Marked bug #50041 as a duplicate of this one. However, developers, please review bug #50041 in detail (and the note below) when considering a bug fix for this bug.

What is not clear to me is the following:

#1 Why the table is not always checked first before optimize (or analyze) is executed if both are specified.

#2 Why a table is not found broken when --optimize is specified (as the last option). I.e. how can a table be optimized if it's broken?

#3 Why, when mysqlcheck --check --optimize --auto-repair is executed (assuming there is a broken table as established by CHECK TABLE already), the following appears in the binary log:

OPTIMIZE TABLE `test1`
REPAIR TABLE `break`.`test1`

This order seems illogical. It should try and repair the table, then optimize it. 

Also, based on Jim's note in this bug, it seems even more inconsistent with currently documented behavior (both OPTIMIZE and TABLE are logged).

However, in correction of the notes in bug #50041, when the order is reversed (mysqlcheck --optimize --check --auto-repair) only REPAIR appears in the binary log.

Like I mentioned before, the only order to the parameters that matters is the last one specified. All of -a, -c, -C*, -m*, -o, -r, -g*, --fix-db-names*, --fix-table-names set a single variable called 'what_to_do' (and the ones with a * also set additional variables that may affect the behavior of what is done), so the only thing that is ever done is what the last option processed tells mysqlcheck to do.

"mysqlcheck -c -r -o -a" will behave no differently from "mysqlcheck -a", feeling free to substitute any of the above-listed parameters in my example. It is only the last one of those options that matters. (Excepting that the ones with a * may cause wonkiness. "mysqlcheck -c -C -c" would be equivalent to "mysqlcheck -C", for example, since the -c option doesn't un-set what -C sets. But "mysqlcheck -a -C -a" would be the same as "mysqlcheck -a" because the handling for analyze doesn't have an option for only analyzing "changed" tables.)

Note that --auto-repair is not one of the options that I listed above -- it adds an additional step of running REPAIR on any tables that fail any operation other than repair. So the last example of "mysqlcheck --check --optimize --auto-repair" means "run optimize on every table, and if there are any errors, run repair on those tables". --check is ignored because --optimize is specified after it. OPTIMIZE comes before REPAIR because that's how auto-repair works -- it tries to repair tables that fail whatever other operation you've requested.

mysqlcheck is an entirely unclever tool. As noted before, the best first fix to apply would be to simply return an error when any of the mutually-exclusive options listed above are specified, instead of silently only accepting the last one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119222

3203 Alexey Botchkov	2010-09-27
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              Issue an error if user specifies multiple commands to run.
              Also there was an unnoticed bug that DO_CHECK was actually 0 which lead
              to wrong actions in some cases.
              The mysqlcheck.test contained commands with the suspicious meaning
              for the above reason. Extra commands removed from there.
      
      per-file commands:
        client/mysqlcheck.c
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              Drop with an error if multiple commands.
        mysql-test/r/mysqlcheck.result
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              result completed.
        mysql-test/t/mysqlcheck.test
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              testcase added.
              not-working commands removed from some mysqlcheck calls.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120343

3219 Alexey Botchkov	2010-10-08
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              Issue an error if user specifies multiple commands to run.
              Also there was an unnoticed bug that DO_CHECK was actually 0 which lead
              to wrong actions in some cases.
              The mysqlcheck.test contained commands with the suspicious meaning
              for the above reason. Extra commands removed from there.
      
      per-file commands:
        client/mysqlcheck.c
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              Drop with an error if multiple commands.
        mysql-test/r/mysqlcheck.result
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              result completed.
        mysql-test/t/mysqlcheck.test
      Bug#35269      mysqlcheck behaves different depending on order of parameters
              testcase added.
              not-working commands removed from some mysqlcheck calls.

pushed into mysql-5.5-bugteam.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Noted in 5.5.7 changelog.

mysqlcheck behaved differently depending on the order in which 
options were given on the command line.