mysqldump, indexes, and checksetup

[Shane H. W. Travis]

So I'm running checksetup from 2.18rc3 on our 2.16.7 database, and it seems
to be balking at one point; it isn't properly clearing away the index
'product' from 'milestones' because it can't find an index of that name.

As background: I'm doing all my upgrading on a second machine to be assured
that everything is working well (customizations, etc.) before it goes live.
To facilitate this, I copied the database over... and this seems to be the
source of the problem.


On the original machine:

mysql> show index from milestones;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| milestones |          0 | product  |            1 | product     | A         |        NULL |     NULL | NULL   |         |
| milestones |          0 | product  |            2 | value       | A         |          34 |     NULL | NULL   |         |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.01 sec)


Looks good, looks like it should work. In transferring to the other machine,
however (mysqldump -c -q), the output file shows this:

CREATE TABLE milestones (
  value varchar(20) NOT NULL default '',
  product varchar(64) NOT NULL default '',
  sortkey smallint(6) NOT NULL default '0',
  PRIMARY KEY  (product,value)                  <--- here is the problem
) TYPE=MyISAM;

... which creates this:

mysql> show index from milestones;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| milestones |          0 | PRIMARY  |            1 | product     | A         |        NULL |     NULL | NULL   |         |
| milestones |          0 | PRIMARY  |            2 | value       | A         |          34 |     NULL | NULL   |         |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.00 sec)


That difference plays hob with checksetup.pl line 3333, which says

    $dbh->do("ALTER TABLE milestones DROP INDEX product");

and produces the output:

=========
...
Fixing Indexes and Uniqueness.
DBD::mysql::db do failed: Can't DROP 'product'. Check that column/key exists
at ./checksetup.pl line 3417.
Removing, renaming, and retyping old product and component fields.
Deleting unused field program from table components ...
Deleting unused field program from table versions ...
Deleting unused field product from table milestones ...
DBD::mysql::db do failed: Duplicate entry '---' for key 1 at ./checksetup.pl line 2468.
...
=========
(line numbers different in output due to local customizations)

Without addressing this, I'm left with an unwanted 'product' in milestones,
and I end up with two indexes.

Now this isn't a huge issue for me; I could figure out how to get around it
(adding a DROP PRIMARY KEY to the checksetup.pl), and it was 'only' my
upgrading test system anyway, but it seems to me that it could affect more
people than just me. Anyone who has ever had to re-create a corrupt database
from a nightly MySQLDump backup could easily run into the same problem; it
would never manifest in day-to-day operations, but would become a real
problem on upgrading.

So, what I'm wondering if this is a defect/oversight in the checksetup file,
a problem with how mysqldump works, or just something I'm missing about one
or both.

Any insights?

Shane H.W. Travis       | Anyone who is capable of getting themselves
travis at sedsystems.ca    |  made President should on no account be allowed
Saskatoon, Saskatchewan |  to do the job.  -- Douglas Adams, HHGTTG