MySQL Bugs: #54828: Optimise table with missing directory permissions results in crashed table

Bug #54828	Optimise table with missing directory permissions results in crashed table
Submitted:	26 Jun 2010 19:38	Modified:	23 Jul 2010 18:56
Reporter:	Steven Hartland	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.5.4-m3, 5.1.49-bzr	OS:	Any (FreeBSD, Linux)
Assigned to:		CPU Architecture:	Any

Description:
If you try to optimize a table for which the database directory has insufficient permissions for the mysql instance to create a new file then the table in question is instantly marked as crashed.

A quick repair fixes the issue, but IMO the table shouldn't be marked as crashed as there is nothing wrong the the table itself.

How to repeat:
Optimize a table for which the mysqld process doesn't have permission to write the the database directory in question so something like mysqld running as mysql:mysql but db dir is own by root:wheel e.g.
drwxr-xr-x  2 root   mysql         488 Jun 24 09:16 testdb1

Try to use the table, it will be marked as crashed.

Suggested fix:
The optimise should fail as it does with a permission denied but the table shouldn't be marked as crashed.

Thank you for the report.

This is not proper way to use mysql, so this can be considered as not a bug. Anyway I can not repeat described behavior. Please send us ouptut of OPTIMIZE TABLE command, error you get when trying to use the table and full server error log file.

Yes I understand these permissions are incorrect and hence the situation shouldnt occur but unfortunately mistakes happen and I think it would be improved behaviour if the server could better deal with this edge case.

In addition it's actually slightly stranger than I thought, which might indicate a more serious internal issue, I'm not sure.

You need to actually request the optimize twice before it crashes the table for some reason:

mysql> optimize table mytable;
+-------------+----------+----------+-----------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                  |
+-------------+----------+----------+-----------------------------------------------------------+
| test.mytable| optimize | error    | Can't create new tempfile: '/data/mysql/test/mytable.TMM' |
| test.mytable| optimize | status   | OK                                                        |
+-------------+----------+----------+-----------------------------------------------------------+
2 rows in set
mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set

mysql> optimize table mytable;
+-------------+----------+----------+-----------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                  |
+-------------+----------+----------+-----------------------------------------------------------+
| test.mytable| optimize | error    | Can't create new tempfile: '/data/mysql/test/mytable.TMM' |
| test.mytable| optimize | status   | Operation failed                                          |
+-------------+----------+----------+-----------------------------------------------------------+
2 rows in set

mysql> select count(*) from mytable;
ERROR 144 : Table './test/mytable' is marked as crashed and last (automatic?) repair failed

Hope this helps.

Thank you for the feedback.

"Can't create new tempfile: '/data/mysql/test/mytable.TMM'" looks strange. Please provide output of SHOW CREATE TABLE mytable.

Nothing strange about it, happens with all tables, the cause as mentioned is the fact the db process doesn't have permissions to write to:
/data/mysql/test/

What is strange is you need to run the optimise twice before the table is marked as crashed, is seems like the first one doesn't properly clear some internal state which then cause the second run to crash the table.

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE mytable.

-- Table "test" DDL

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `meta_name` longtext NOT NULL,
  `meta_value` longtext NOT NULL,
  `download_id` int(12) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Thank you for the feedback.

Verified as described:

mysql> CREATE TABLE `test` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `meta_name` longtext NOT NULL,   `meta_value` longtext NOT NULL,   `download_id` int(12) unsigned NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1, 'daasdadasdsad', 'dasdsad', 1);Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(2, 'daasdadasdsad', 'dasdsad', 2);Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3, 'daasdadasdsad', 'dasdsad', 3);Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(4, 'daasdadasdsad', 'dasdsad', 4);Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye

ssmirnova@blade12 ~/blade12/build/mysql-5.1
$chmod 555 data/bug54828/

ssmirnova@blade12 ~/blade12/build/mysql-5.1
$./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock  bug31724
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 203189
Server version: 5.1.49-debug-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use bug54828Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> optimize table test;
+---------------+----------+----------+----------------------------------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                                                     |
+---------------+----------+----------+----------------------------------------------------------------------------------------------+
| bug54828.test | optimize | error    | Can't create new tempfile: '/users/ssmirnova/blade12/build/mysql-5.1/data/bug54828/test.TMM' |
| bug54828.test | optimize | status   | OK                                                                                           |
+---------------+----------+----------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

mysql> optimize table test;
+---------------+----------+----------+----------------------------------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                                                     |
+---------------+----------+----------+----------------------------------------------------------------------------------------------+
| bug54828.test | optimize | error    | Can't create new tempfile: '/users/ssmirnova/blade12/build/mysql-5.1/data/bug54828/test.TMM' |
| bug54828.test | optimize | status   | Operation failed                                                                             |
+---------------+----------+----------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select count(*) from test;
ERROR 144 (HY000): Table './bug54828/test' is marked as crashed and last (automatic?) repair failed

Changing permissions on the MySQL data directory as described above is not a supported action

That as may be, but it still shouldn't make the table as crashed, as no action has been taken. The fact it takes two optimise requests to cause the table to be marked as crashed suggests there is something wrong with the tests / action taken in the initial case which could effect other cases where permissions are not an issue.

From a pure users perspective, saying that it shouldn't be done doesn't guarantee it wont happen, it did here all be it by accident.

Arguing that "XYZ is not a supported action" could be done for many sorts of issues from malicious packets on the wire, to badly formed SQL but should we ignore what shouldn't happen or should we make sure it doesn't cause undesired results?

Surely the latter makes for a better more resilient product which will benefit all, and hence fixing the cause of the undesired behaviour is the correct course of action?

Looks like a MyISAM bug, the tempfile is created inside MyISAM code.

Amazing this bug is still open. I ran into a variation of it: When executing "OPTIMIZE TABLE", my table crashed, with error message:

Can't create new tempfile: '/var/lib/mysql/zxnifltlapzlcumb/items.TMM'
Operation failed 

This was because there was already a zero-byte file at that location. Deleting the file avoids the crash. But as OP said, the table should be fine, so it should not be marked as crashed.