mokraemer,

Are you getting something like

Table './test/t1' is crashed, skipping it. Please repair it with aria_chk -r

on server startup when automatic repair does not work?
Could you please attach the error log and cnf file(s)?
Thanks.

err-log:
150713 1:14:03 [ERROR] mysqld: Table './db/table' is marked as crashed and last (automatic?) repair failed
150713 1:14:04 [ERROR] mysqld: Table './db/table' is marked as crashed and last (automatic?) repair failed
150713 1:14:04 [ERROR] mysqld: Table './db/table' is marked as crashed and last (automatic?) repair failed
150713 1:14:05 [ERROR] mysqld: Table './db/table' is marked as crashed and last (automatic?) repair failed

1:14 is the time the cron-job tried to execute "mysqloptimize -A", before this time everything was fine.

Thanks.
This is indeed a weird issue. Below is an MTR test case which reproduces it without any side tools.
What happens there is after creating a .TMM file, three identical connections get created and execute identical pair of queries: SHOW CREATE TABLE and OPTIMIZE TABLE (just like mysqloptimize does).
First two times it works as expected: SHOW CREATE TABLE goes fine, OPTIMIZE TABLE returns "Can't create new tempfile" error.
But on the 3rd time, SHOW CREATE TABLE fails with 'Table is marked as crashed and last (automatic?) repair failed'.

MyISAM is also affected
It's an upstream issue, observed on MySQL 5.5/5.6/5.7 as well. I don't know if it makes any sense to report it to MySQL though, I think there is a zero chance it will ever be fixed.

create table t1 (pk int primary key, i int) engine=MyISAM;

insert into t1 values (1,1),(2,2);

--let $datadir=`SELECT @@datadir`

--write_file $datadir/test/t1.TMM

EOF

--connect (con1,localhost,root,,)

SHOW CREATE TABLE t1;

OPTIMIZE TABLE t1;

--disconnect con1

--connect (con1,localhost,root,,)

SHOW CREATE TABLE t1;

OPTIMIZE TABLE t1;

--disconnect con1

--connect (con1,localhost,root,,)

SHOW CREATE TABLE t1;

OPTIMIZE TABLE t1;

--disconnect con1

# Cleanup

--connection default

DROP TABLE t1;

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

create table t1 (pk int primary key, i int) engine=MyISAM;

insert into t1 values (1,1),(2,2);

SHOW CREATE TABLE t1;

Table	Create Table

t1	CREATE TABLE `t1` (

  `pk` int(11) NOT NULL,

  `i` int(11) DEFAULT NULL,

  PRIMARY KEY (`pk`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

OPTIMIZE TABLE t1;

Table	Op	Msg_type	Msg_text

test.t1	optimize	error	Can't create new tempfile: '/data/repo/git/5.5/mysql-test/var/mysqld.1/data/test/t1.TMM'

test.t1	optimize	status	OK

SHOW CREATE TABLE t1;

Table	Create Table

t1	CREATE TABLE `t1` (

  `pk` int(11) NOT NULL,

  `i` int(11) DEFAULT NULL,

  PRIMARY KEY (`pk`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

OPTIMIZE TABLE t1;

Table	Op	Msg_type	Msg_text

test.t1	optimize	error	Can't create new tempfile: '/data/repo/git/5.5/mysql-test/var/mysqld.1/data/test/t1.TMM'

test.t1	optimize	status	Operation failed

SHOW CREATE TABLE t1;

bug.t4                                   [ fail ]

        Test ended at 2015-07-16 22:13:12

CURRENT_TEST: bug.t4

mysqltest: At line 22: query 'SHOW CREATE TABLE t1' failed: 144: Table './test/t1' is marked as crashed and last (automatic?) repair failed

Issue was two fold (both in MyISAM and Aria)

• optimize and repair failed if there was an old .TMM file around. As optimized and repair are protected against multiple execution, I decided to change so that we just truncate the file if it exists.

• I had missed to check for error condition if creation of the temporary index file failed. This caused the strange behaviour that it looked as if optimized would have worked once.

Fix pushed to 10.0 tree

Fix pushed to 10.1 tree (not 10.0)

I actually experienced this recently after an upgrade to MariaDB 10 (From TokuDB 5.5)

There is an Oracle bug open here for it (from 2010!)

https://bugs.mysql.com/bug.php?id=54828

I experience randomly this problem running mysqloptimize weekly on all tables present in different MariadDB 10.0.20 servers. Sometimes after crash I have to delete original table too because was unrecovreable and crash mariadb when try to check / repair table.

As the comment above says, it was fixed in 10.1.6.

Hi Elena,
I see now "Fix pushed to 10.0 tree" was correct after in "Fix pushed to 10.1 tree (not 10.0)" from Michael Widenius
Thank you

Hi Elena,
I'm not sure if the original problem was really solved:
yesterday I encountered this problem again, as described from Fustini. The database was up and running -everything good so far. The nightly cron job executes an mysqloptimize and one of the tables was marked as crashed. During this time no .TMM file was present.
Is it possible, the lock during mysqloptimize can cause a racing condition, or is not held long enough, ...?

mokraemer,
Before we dig deep into it – are you running 10.1.6 now?

not now.
You're right. Since this is only available in 10.1 - to get it officially I will have to wait for ~1 year.

For me too, but we have the problem now with last version avialable for 10.0 and get a beta version to solve a problem in a production enviroment is not a fair otption for me. Elena how do you suggest to do ?

Alessandro,

Before I can answer that, I need to understand something. This problem has apparently been around forever, in previous releases, in MySQL, everywhere. How did it happen that it suddenly started bothering you now? Has anything in your procedure changed?

@Elena: that's what I was pointing out:
I have a cron job running every night for years the runs mysqloptimize -A
Two things changed recently updated mariadb 5.5.x -> 10.0.x
Upgraded tables from MyIsam to Aria.

Sine Mariadb 10.0 I can see is from time to time when mysqloptimize runs, one of the tables got stuck and runs into state "crashed". Since this doesn't happen every night, I assume some concurrent access to the table during optimize, which is interpreted as "crash".
The result of the next run of mysqloptomize is a stale .TMM file, so only the next run leads to exactly this bug.

I my description is clear enough to understand the problem.

PS: maybe I should open a new bug for this issue, cause this is not exactly this bug, but leads to it.

mokraemer,
My apologies, somehow I did not read from your description that you started getting corrupted tables after 5.5=>10.0 upgrade (as the matter of fact, I still don't, maybe it was in some other report?). I do see philsweeney mention it, but the comment is laconic, we'll need more details.
Anyway, getting corrupted tables is indeed a different story, especially if it looks like a regression from a previous version. So yes, please file a new bug report.

Hi Elena,
we switched from mysql 5.1 to Mariad 10.0 all the DB servers (more 100 servers) in 2 months. Now is 2 months that we are all db serves Mariadb 10.0 and we start to have randomly a stale tmm created when optimize run weekly. Thank you

@all: created a new bug for this problem.
@fustini: please comment / describe your problem there, so we get this fixed - hopefully in one of the 10.0 releases so linux distributors push this as an fix to current versions