Why the table gets deleted by the server in mysql? [closed]

Posted on

Question :

I have a table in mysql. I have one java application which will keep inserting the data into this table. So the size of the table grows drastically and at one point it gets deleted automatically by the mysql server. I am not sure about the reason yet. So please if anybody has come across this issue then please share the way to handle this, so that I can have my data preserved.


This is what I found in the sql log of the server.

> 2013-11-27T15:07:39.285156Z    9 Query    SELECT s.Sql_Id
>       ,s.Child_Number
>       ,s.Executions
>       ,s.Last_Active_Time
>       ,s.User_Io_Wait_Time
>       ,s.Object_Status
>       ,s.Plan_Hash_Value FROM   V$sql s WHERE  s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ T1.SYNC_TIMESTMP, T1.CUSTNO, T1.XAFID_ID_IFX,
> T1.XAFID_CLASSIFIED%' 2013-11-27T15:07:39.286132Z    9 Query  SHOW
> WARNINGS 2013-11-27T15:08:08.791015Z    9 Query   select
> @@session.tx_read_only
> 2013-11-27T15:08:08.791992Z    9 Query    drop table devicedata 2013-11-27T15:08:08.926757Z    9 Query    SHOW WARNINGS
> 2013-11-27T15:08:08.936523Z    9 Query    select @@session.tx_read_only


Answer :

Reading the log seems to make the problem fairly apparent:

2013-11-27T15:08:08.791992Z 9 Query drop table devicedata 

That’s not mysql dropping the table. That’s your application dropping the table.

There’s another line in the log that is very suspicious:

 ,s.Plan_Hash_Value FROM V$sql s WHERE s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ 

V$sql is not a valid table name, at least not without backticks. It sounds like the application may be trying to build the table name from a string and building the string incorrectly… when that query fails, it drops the table in some kind of misguided attempt at “recovery.”

Search the code for the word “drop.” MySQL has no concept of dropping a table because it’s too full.

We had similar problem where we lost all the tables of the database but the database remains as it is in the mysql. The problem and the solution is as follows,
1) Check where you have installed your MYSQL/XAMPP/WAMP
2) Check whether you have all the permissions to install the MYSQL/XAMPP at the
place where you have installed your XAMPP/MYSQL on your desktop/server.

The solutions is simple, just re-install your XAMPP/MYSQL on your desktop/any other location where you have permission to install. Your will not get this problem in future. Please do not forget to check your error logs and take necessary actions after checking logs daily.

Leave a Reply

Your email address will not be published. Required fields are marked *