Database get changed weirdly.

[heropage]

My table "video" keep update and insert quite often.(viewed_time will update when user view a video)
And today, all the viewed_time have been changed, it decreased a lot.(for example, it's supposed to 10000,now it's only 100)

And I can see it says overhead in phpMyAdmin.
Also I just notice the type of the table is innodb whereas others are myisam.

That's scary! Since the viewed_time is not so important, if it's some critical data, that's disaster!

[chris_fuel]

Well, this is one of the cases where I answer a question with another question, "Do you have a backup?"

[heropage]

Well, this is one of the cases where I answer a question with another question, "Do you have a backup?"

Yes, I do!

But after I optimized the table(because I see the overhead).
It happened again.
Not sure it's something to do with innodb or not

[chris_fuel]

Don't think that should really make a difference.. I hate to say this, but if things are randomly happening like that, you might want to get with your hosting provider and investigate a possible break-in. Not to cause more panic, but it's good to find out ahead of time.

[heropage]

Don't think that should really make a difference.. I hate to say this, but if things are randomly happening like that, you might want to get with your hosting provider and investigate a possible break-in. Not to cause more panic, but it's good to find out ahead of time.

it's a unmanaged dedicated server.

[longneck]

don't worry about overhead for innodb tables, because inno efficiently reuses deallocated pages.

but the fact that your numbers change when optimizting is scary. what version of mysql are you using? also, post the CREATE TABLE statement for the table in question.

[heropage]

don't worry about overhead for innodb tables, because inno efficiently reuses deallocated pages.

but the fact that your numbers change when optimizting is scary. what version of mysql are you using? also, post the CREATE TABLE statement for the table in question.

The data changed without any reason, it NOT because of "optimize".
I though the data changed because of "overhead"

Here is the statement:

CREATE TABLE `top_month` (
`VID` int(11) NOT NULL default '0',
`view_times` int(11) NOT NULL default '0',
`added_month` int(2) NOT NULL default '0',
PRIMARY KEY (`VID`,`added_month`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[longneck]

i think you have a bad UPDATE statement somewhere that is changing more rows that you intended.

you didn't tell me what version of mysql.

[heropage]

i think you have a bad UPDATE statement somewhere that is changing more rows that you intended.

you didn't tell me what version of mysql.

Sorry, mysql 5

[heropage]

Here is the script I update

$sql = "select count(*) as total from top_month where VID=".$rs->fields['VID']." and added_month='".date("m")."'";
$rs1 = $conn->execute($sql);

if($rs1->fields['total']>0)
$sql = "update top_month set view_times = view_times + 1 where VID=".$rs->fields['VID']." and added_month='".date("m")."'";
else
$sql = "insert top_month set VID=".$rs->fields['VID'].", view_times = 1, added_month='".date("m")."'";

$rs = $conn->execute($sql);

[longneck]

you can do that with a single statement. see http://dev.mysql.com/doc/refman/5.0/...duplicate.html

please get me the exact mysql version.

[heropage]

you can do that with a single statement. see http://dev.mysql.com/doc/refman/5.0/...duplicate.html

please get me the exact mysql version.

5.0.27