SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database get changed weirdly.

    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!

  2. #2
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, this is one of the cases where I answer a question with another question, "Do you have a backup?"

  3. #3
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris_fuel View Post
    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

  4. #4
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris_fuel View Post
    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.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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;

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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

  10. #10
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SitePoint Addict heropage's Avatar
    Join Date
    May 2004
    Location
    canada
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •