Re: Table damaged? And if so how to fix?

[Matthew Fleming]

SHOW TABLE STATUS for the affected table looks like this:

| patients | MyISAM | 10 | Dynamic | 38484 | 89 | 3452652 | 281474976710655 | 3338240 | 0 | 99207 | 2013-08-03 09:40:05 | 2014-04-11 13:22:00 | 2014-04-06 16:54:09 | latin1_swedish_ci

You can see the row number above: 38484. In fact the test system has the same
data, so same row number.

The tmp directory is on the same filesystem. There is plenty of room in the
filesystem.

SHOW PROCESSLIST and SHOW OPEN TABLES (I assume that is what you meant) produce
this output, without the ALTER command (remember it is the "patients" table
that is a problem:


mysql> SHOW PROCESSLIST;
+-----+--------+-----------------+-------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+-------+---------+-------+-------+------------------+
| 128 | flemdp | localhost:38809 | dpath | Sleep | 1 | | NULL |
| 129 | flemdp | localhost:38810 | dpath | Sleep | 13146 | | NULL |
| 130 | flemdp | localhost:38811 | dpath | Sleep | 1 | | NULL |
| 131 | flemdp | localhost:38812 | dpath | Sleep | 13146 | | NULL |
| 132 | flemdp | localhost:38814 | dpath | Sleep | 243 | | NULL |
| 133 | flemdp | localhost:38815 | dpath | Sleep | 13145 | | NULL |
| 134 | flemdp | localhost:38816 | dpath | Sleep | 1 | | NULL |
| 135 | flemdp | localhost:38817 | dpath | Sleep | 13144 | | NULL |
| 169 | flemdp | localhost | dpath | Sleep | 197 | | NULL |
| 171 | flemdp | localhost | dpath | Query | 0 | NULL | SHOW PROCESSLIST |
+-----+--------+-----------------+-------+---------+-------+-------+------------------+
10 rows in set (0.00 sec)

mysql> SHOW OPEN FILES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FILES' at line 1
mysql> SHOW OPEN TABLES;
+----------+-------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------------+--------+-------------+
| dpath | ppc_auth | 0 | 0 |
| dpath | slides | 0 | 0 |
| dpath | doctors | 0 | 0 |
| dpath | sendouts | 0 | 0 |
| dpath | slips | 0 | 0 |
| dpath | pathologists | 0 | 0 |
| dpath | prohealth_charges | 0 | 0 |
| dpath | palm_auth | 0 | 0 |
| dpath | reminders | 0 | 0 |
| dpath | sources | 0 | 0 |
| dpath | patients | 0 | 0 |
| dpath | procedures | 0 | 0 |
| dpath | specimens | 0 | 0 |
| dpath | lastinvoice | 0 | 0 |
| dpath | specimens2 | 0 | 0 |
| dpath | lastnumber | 0 | 0 |
| dpath | web_auth | 0 | 0 |
| dpath | secretaries | 0 | 0 |
+----------+-------------------+--------+-------------+
18 rows in set (0.00 sec)

When I issue this command (which hangs)

mysql> alter table patients change MR MR VARCHAR(50);

they show:

mysql> SHOW PROCESSLIST;
+-----+--------+-----------------+-------+---------+-------+---------------------------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+-------+---------+-------+---------------------------------+-----------------------------------------------+
| 128 | flemdp | localhost:38809 | dpath | Sleep | 2 | | NULL |
| 129 | flemdp | localhost:38810 | dpath | Sleep | 13333 | | NULL |
| 130 | flemdp | localhost:38811 | dpath | Sleep | 2 | | NULL |
| 131 | flemdp | localhost:38812 | dpath | Sleep | 13333 | | NULL |
| 132 | flemdp | localhost:38814 | dpath | Sleep | 130 | | NULL |
| 133 | flemdp | localhost:38815 | dpath | Sleep | 13332 | | NULL |
| 134 | flemdp | localhost:38816 | dpath | Sleep | 2 | | NULL |
| 135 | flemdp | localhost:38817 | dpath | Sleep | 13331 | | NULL |
| 169 | flemdp | localhost | dpath | Query | 6 | Waiting for table metadata lock | alter table patients change MR MR VARCHAR(50) |
| 171 | flemdp | localhost | dpath | Query | 0 | NULL | SHOW PROCESSLIST |
+-----+--------+-----------------+-------+---------+-------+---------------------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql> SHOW OPEN TABLES;
+----------+-------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------------+--------+-------------+
| dpath | ppc_auth | 0 | 0 |
| dpath | slides | 0 | 0 |
| dpath | doctors | 0 | 0 |
| dpath | sendouts | 0 | 0 |
| dpath | slips | 0 | 0 |
| dpath | pathologists | 0 | 0 |
| dpath | prohealth_charges | 0 | 0 |
| dpath | palm_auth | 0 | 0 |
| dpath | reminders | 0 | 0 |
| dpath | sources | 0 | 0 |
| dpath | patients | 1 | 0 |
| dpath | procedures | 0 | 0 |
| dpath | specimens | 0 | 0 |
| dpath | lastinvoice | 0 | 0 |
| dpath | specimens2 | 0 | 0 |
| dpath | lastnumber | 0 | 0 |
| dpath | web_auth | 0 | 0 |
| dpath | secretaries | 0 | 0 |
+----------+-------------------+--------+-------------+
18 rows in set (0.00 sec)

Thank you very much: it seems you're making progress, since I assume the "Waiting for table metadata lock" explains the problem. Unfortunately, after some web searching I can't figure out what to do with this information. Somehow the ID of the process that has the lock has to be identified and it has to be killed, but how?

Thanks again,

Matthew Fleming