MySQL Bugs: #70621: Suddenly "Table doesn't exist"

Bug #70621	Suddenly "Table doesn't exist"
Submitted:	15 Oct 2013 8:00	Modified:	17 Nov 2013 18:41
Reporter:	Nitsan Daniel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	mysql-5.5.2	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
I'm reporting this problem after it repeated at two of our customers.
Our database contain 6 tables, all use InnoDB engine, all partitioned (by range).
What happened is that suddenly, after everything is working for a while, one of the can't be reached from the code (can't perform select or insert) as it reported as doesn't exist.
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'my_db.my_table' doesn't exist.
I tried to login to mysql client and could see the table when running "show tables".
The only thing that solved it is dropping the DB and creating it again.
The problem is only with this table. The only difference between this table and the rest is that is use 'MEDIUMTEXT' as one of the column types. Plus I believe that this is the most loaded table.
I asked and both customer claim they didn't copied or moved MySQL files or anything like this.
Can you please assist?

How to repeat:
I don't know how to repeat this as we didn't encountered this issue in our labs.

Thank you for the bug report. Unlucky you are reporting a bug without a test case
also with a quite older server version 5.5.2 the current releae is 5.2.34. Are you able to upgrade and test?. Thanks.

I'm ok with upgrading. The question is if there was any change between the two versions that may be related to my problem.

A bug is always tested against the last release there is no a back port fix so should have a test case which we can test against the last release. Thanks.

I just find that a couple of hours before I started the problem I got the following error in the log:
java.sql.BatchUpdateException: Table '`my_db`.`my_table` /* Partition `p0000001379426400000` */' already exists

This is the same table that had the problems reported a couple of hours later.

Can you please tell me if there might be any possible connection between the two log messages?
Plus, Can you please elaborate on which cases that you are aware of the problem that I'm describing can occur? Only one table reported as not exist.

One more thing. Before getting the 'table don't exists' problem we got 'java.sql.SQLException: Got error 28 from storage engine' in the customer's logs. We asked the user to increase the disk space.

Thank you for the feedback.

Most common cause of such errors is table corruption. And last error message confirms this: "One more thing. Before getting the 'table don't exists' problem we got 'java.sql.SQLException: Got error 28 from storage engine' in the customer's logs. We asked the user to increase the disk space."

Therefore, and because you use very old version, which was *not* released as GA yet, I close the report as not a bug. If you would see same error with current version 5.5.34 and up and not caused by corruption, feel free to reopen the report.

Thanks for the response.
I can't upgrade to a newer MySQL version as this is a customer working in production with our product, paying us money and asking for support (just as we pay money to MySQL, asking for support).
I understand you don't support this version, but I would expect to get any helpful advice, what can cause this kind of behavior. Any suggestion, what should we look for\ask our customer.
I'm not sure how can you mark this a 'Not a Bug' as this is evidently some kind of a problem.
Furthermore, the client is now reporting that he gets the same error but now with a different table.
It's not reasonable that we will need to delete all their data each time this is happening.

One more question. Do you think that moving to use MyISAM will solve the problem?

Thank you for the feedback.

> (just as we pay money to MySQL, asking for support).

If you are paying money to MySQL, please open Support Request and we will help you.

This is a bug database, purpose of which is to identify and verify bugs in MySQL code, not to solve user issues. And your case looks like not MySQL bug, but rather temporarily lack of space. And if you don't have enough space, simply altering tables won't help. Symptom can differ, but no storage engine can work properly if there is no space to store data.

select 姓名,                          #table doesn't exist....... bug? no ,hide code... 
sum(case when 科目 = '语文' then 成绩 else 0  end) as "语文",
sum(case when 科目 = '数学' then 成绩 else 0  end) as "数学",
sum(case when 科目 = '英语' then 成绩 else 0  end) as "英语",
sum(case when 科目 = '语文' or 科目 = '数学' or 科目 = '英语'  then 成绩 else 0 end ) as "总成绩" # from student group by 姓名;
from student  group by 姓名;         #bug report !!!!!!!! if you use this sentence...
#from student group by 姓名;   # it's oK! is there some hiden code in this sentence? ps: it's copied from some where..

-- create table student(xingming varchar(10),kemu varchar(10),chengji tinyint);
-- insert into student values('张三','语文',20);
-- insert into student values('张三','数学',30);
-- insert into student values('张三','英语',50);
-- insert into student values('李四','语文',70);
-- insert into student values('李四','数学',60);
-- insert into student values('李四','英语',90);

-- create table student(姓名 varchar(10),科目 varchar(10),成绩 tinyint);
-- insert into student values('张三','语文',20);
-- insert into student values('张三','数学',30);
-- insert into student values('张三','英语',50);
-- insert into student values('李四','语文',70);
-- insert into student values('李四','数学',60);
-- insert into student values('李四','英语',90);