# MySQL Problem - Tables always reset to null



## maulana23 (Nov 16, 2010)

Hi Guys,

As the title says, I have problem with MySQL that always reset table to null.

For example: I have table 1, and I put some details inside,..

Table 1


```
title | detail
------|-------
test  | test123
```

but then, after sometime (I only notice when it's already 24hours after the insertion), the table reset back to null (or whenever I do "select * from table 1" always return me empty set).

I am using:
-FreeBSD version 8
-Apache 2.2.15_9
-mysql-client 5.5.6_1
-mysql-server 5.5.6_1

I have this kind of problem since I executed this command

[cmd=]myisamchk -r table1[/cmd]

Can anyone tell me, what kind of problem that I have?

Thank you in advance.


----------



## maulana23 (Nov 16, 2010)

And also how to solve this problem?

Thank you again in advance.


----------



## SirDice (Nov 16, 2010)

Myisamchk will only repair a table when it's broken. It'll run only once so I doubt it has anything to do with it.

Are you sure there aren't any TRUNCATE commands being sent to MySQL?


----------



## maulana23 (Nov 16, 2010)

I'm not sure but I dont think there is any TRUNCATE command being sent out. Is there anyway to check whether it sent out or not?

I'm just using the CREATE, ALTER, UPDATE, and DELETE only.


----------



## SirDice (Nov 16, 2010)

You say you also have Apache running. Is this a website that uses the database? 
Are you sure that site isn't vulnerable to SQL injection attacks?


----------



## maulana23 (Nov 17, 2010)

Yes, it is a website that's using database (mysql). I'm not sure about this, if this is really an injection attacks, it should not reset the table to null every 24 hours, isn't it?


----------



## SirDice (Nov 17, 2010)

maulana23 said:
			
		

> Yes, it is a website that's using database (mysql). I'm not sure about this, if this is really an injection attacks, it should not reset the table to null every 24 hours, isn't it?



I don't know. It could be somebody has a grudge and runs a script that does this. You won't be the first to have that happen.


----------



## maulana23 (Nov 19, 2010)

Oh my,.. So basically, only TRUNCATE command and SQL Injection attack could delete all the entries on the table, right?

Thanks a lot for your input, SirDice. I will search around to find the solution on the SQL Injection Attacks.


----------



## SirDice (Nov 19, 2010)

maulana23 said:
			
		

> Oh my,.. So basically, only TRUNCATE command and SQL Injection attack could delete all the entries on the table, right?


Let's put it this way, I see no technical reason why MySQL would suddenly empty tables. I have dealt with broken tables and corrupt filesystems but I've never seen a table that is emptied on a regular basis without someone actually removing the entries.


----------



## Alt (Nov 19, 2010)

Agree to SirDice.
Tip: Turn on mysqld query log.


----------



## qsecofr (Nov 24, 2010)

How'd the record get into table1?  Was the data committed?


----------



## maulana23 (Nov 25, 2010)

Alt said:
			
		

> Agree to SirDice.
> Tip: Turn on mysqld query log.



Thanks for the tip, Alt. Is there a command I turn on the mysqld query log? or is it already available inside the partition so that I just need to find it?


----------



## maulana23 (Nov 25, 2010)

qsecofr said:
			
		

> How'd the record get into table1?  Was the data committed?



The data were inserted using the code (php) from CodeIgniter framework.

So, the scenario is like this:
- user upload file.
- file was saved to some dir in the FreeBSD.
- some information (filename, title, description, etc) was saved to the database.

problem:
- file was still available in the directory, BUT the information on the database was gone. And actually not only the specific file, but the specific table, where the file information was saved, reseted to null (empty set).


----------



## SirDice (Nov 25, 2010)

maulana23 said:
			
		

> Is there a command I turn on the mysqld query log? or is it already available inside the partition so that I just need to find it?



MySQL has excellent (online) documentation.

http://dev.mysql.com/doc/refman/5.5/en/query-log.html


----------

