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
Triage: Triaged: D2 (Serious)

[13 Mar 2008 14:45] Rob Brucks
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
[13 Mar 2008 15:00] Rob Brucks
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.
[13 Mar 2008 22:34] Sveta Smirnova
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.
[13 Mar 2008 22:57] Sveta Smirnova
No need to upgrade. Error still exists in current versions.

Workaround: run mysqlcheck with each of this options separately.
[7 Sep 2009 7:22] Meiji KIMURA
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.
[7 Sep 2009 20:13] Jim Winstead
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.
[13 May 2010 5:04] Roel Van de Paar
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
========
[13 May 2010 5:04] Roel Van de Paar
Also see bug #50041
[23 May 2010 23:42] Roel Van de Paar
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.
[23 May 2010 23:42] Roel Van de Paar
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.
[24 May 2010 23:08] Jim Winstead
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.
[28 Sep 2010 7:29] Bugs System
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.
[8 Oct 2010 8:11] Bugs System
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.
[8 Oct 2010 8:35] Alexey Botchkov
pushed into mysql-5.5-bugteam.
[9 Nov 2010 19:45] Bugs System
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)
[13 Nov 2010 16:24] Bugs System
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)
[13 Nov 2010 16:33] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 1:55] Paul Dubois
Noted in 5.5.7 changelog.

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