MySQL Bugs: #6664: "Table 'mydb.mytable' doesn't exist" errors after MySQL runs ~24 hours

Bug #6664	"Table 'mydb.mytable' doesn't exist" errors after MySQL runs ~24 hours
Submitted:	16 Nov 2004 12:44	Modified:	26 Dec 2004 11:43
Reporter:	Dmitry Gribov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.7	OS:	FreeBSD (FreeBSD 5.2, FreeBSD 5.3)
Assigned to:		CPU Architecture:	Any

Description:
After a while MySQL reports "Table 'fictionbook.mytable' doesn't exist". Then it reports the same for one more table, and finally it says "Unknown database 'fictionbook'". I restart mysql, all is fine again untill the next time, then it all goes down again.

I have a mixed-type database, but death comes to MYISAM tables first and then to database itself.

How to repeat:
When mysql dies (I'll have one more report, I hope, but I've found workaround there) it writes:
key_buffer_size=205520896
read_buffer_size=258048
max_used_connections=2
max_connections=40
threads_connected=1

Well, repeating the error is not easy :(
I'm using mod_perl with manually set up MySQL connections (which reports "disconnect invalidates 1 active statement handle" on every END section with disconnect, but I still can't find the handle, keep looking), with MaxRequestsPerChild 1000, about 20 SQL request per client request, about 50 request per minute. There are some MYISAM+InnoDB mixed queries. There is also one memory table, but it's used alone. One temporary memory table is created and deleted every 50-th request. Error happens after ~24 hours (but sometimes at the first hour or after two days) of mysql uptime. So in short it's a bit dedicated setup, carefully migrated from PostgreSQL, it's hard to find exact step before the error. While there was no real load, it all worked fine.

I've started from MaxRequestsPerChild 5000, but with 1000 it's all the same for now. I'll try to change it to 200, but I do not expect this to help. Then I'll change all tables type to InnoDB which I believe will work, while I'd prefer not to do so.

In mysql error log I see
/usr/local/libexec/mysqld: Can't open file: 'affilliated_books.MYI' (errno: 144)
(if deatch chooses simple MyISAM as a victim) or
041104 1:26:45 [ERROR] /usr/local/libexec/mysqld: Can't open file: 'downloads_all.MRG' (errno: 145)
041104 1:26:45 [ERROR] /usr/local/libexec/mysqld: Can't open file: 'downloads_fresh.MYI' (errno: 145)
(MERGE and it's child)

Merge table is used for selects only.
Once this error occurs, it's reported again and again

I can provide more detail or perform some tests, if needed. But this is a production server (!) so I'd better get a ready-to-use fix.

Looks like the reason was two tables
"BOOKS" MYISAM and "books" InnoDB

I'm not sure how could I create thouse two, but I beleave I've turned of case-sensitivity after both MYISAM table BOOKS  and InnoDB table books was created. Should be some warning or something, I think.

Nope. Removed dupicated table, still the same :(

How was mysqld built ?

Defaults, no linuxtreads or other exotics.
--enable-assembler --with-charset=utf8 --without-geometry
Default paths ad so on - all by default.
After I've deleted thouse BOOKS table, it only reports single table errors, once in ~4 hours. There was no "Unknown database" yet.

No ideas so far, sorry :(

There was a bug in concurrent access to MERGE tables, but it was fixed in 4.1.4.

There was known bug in some FreeBSD functions not being thread-safe - a solution was to link mysqld with linuxthreads - but it is supposed to be fixed in FreeBSD 5.x

What thread library do you use, by the way ?

Try to make mysqld to produce a core dump, may be it will shed some light on it (though unlikely).

You may try to build mysqld with linuxthreads - just to exclude that realpath() bug.

Did you check http://dev.mysql.com/doc/mysql/en/FreeBSD.html ?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".