Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

UPDATE

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

Thanks

share|improve this question

migrated from stackoverflow.com Nov 28 '13 at 9:26

This question came from our site for professional and enthusiast programmers.

2  
I don't think you have posted enough information to allow us to answer this question. There is not a DBMS on the planet that will just delete a table because it gets too big. –  Bill Gregg Nov 27 '13 at 15:49
2  
@Bill Gregg, I didn't say it deleted the table because it is too large, I just mentioned about a random observation which I am not sure. –  GuruKulki Nov 27 '13 at 15:55
    
Did MySQL delete table, or table records? –  Howard Guo Nov 27 '13 at 16:21
    
@Howard, It deleted the whole table. –  GuruKulki Nov 27 '13 at 16:24
    
@GuruKulki is there enough disk space left for MySQL journals and data? –  Howard Guo Nov 27 '13 at 16:36

1 Answer 1

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.

share|improve this answer
    
V$SQL is a valid table name (sqlfiddle.com/#!2/98730/1) and does not require any quoting (neither MySQL's dreaded backticks nor the standard double quotes). That select looks like it should have been sent to an Oracle database actually (because Oracle does have a dynamic system view that is called V$SQL containing a column named PLAN_HASH_VALUE) –  a_horse_with_no_name Mar 1 at 7:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.