The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

pkgacct script not consistent in saving mysql dbs

Discussion in 'General Discussion' started by te2586, Jan 3, 2006.

  1. te2586

    te2586 Member

    Joined:
    Dec 19, 2003
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    I have backed up several accounts using the pkgacct script and in about 25-50% of the cases, there's a problem with the mysql backup and restore portion. Directly tarrng and gzipping the user's databases from the /var/lib/mysql directory (and restoring those after the move) solves the issues this process creates.

    Are there any known issues with pkgacct/restorepkg and mysql. pgsql is not installed.

    I'm running:
    WHM 10.8.0 cPanel 10.8.1-R30
    RedHat Enterprise 3 i686 - WHM X v3.1.0
    MySQL v 4.0.25

    Thanks!
     
  2. MattGetWeb

    MattGetWeb Well-Known Member

    Joined:
    Aug 4, 2005
    Messages:
    49
    Likes Received:
    0
    Trophy Points:
    6
    Only jumping in here to say I've had the same thing. Site with ~1.5Gb mysql db. Used /scripts/pkgacct <user> , and the db is "truncated" (the webmaster said all his stuff wasn't there. Suspected it was an entire table missing rather than just corrupted backup). Also tried the previous night's backup, but same problem. Time pressure meant we had to do a manual copy over of the dbs. Was just before the holidays, so haven't had much chance to follow up. Makes me a bit nervous about disasters, now. :(

    WHM 10.8.0
    Cpanel 10.8.1-S31
     
  3. procam

    procam Well-Known Member

    Joined:
    Nov 24, 2003
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    I posted about this previously~ this is a common problem on multiple servers of mine now- the units run their daily and weekly backups and database data in some sites is not useable at all if the need arises to restore the website data/home dirs are fine but I have found in some instances where this is a problem the mysql databases contain special keys in them which causes the backup problem - in others I have not yet determined a cause -- I sure would be a happy camper if someone finds a fix for this!! :eek:

    Heres an expl:

    It's a little complicated, but here it is. MySQL has several engines for storing data: Berkley, MyISAM, InnoDB, etc. In earlier versions, all tables were stored in either Berkley or MyISAM, so this was not an issue. When MySQL added the InnoDB engine, it included something called "Foreign Key Constraints" which is what I suspect is an evil of the problem. In database parlance, a Foreign Key is a column whose contents are exclusively comprised of values from a Primary Key in another table. So far - no problem. The problem comes with the constraint enforcement. Given this situation:

    Table 1 - orders

    OrderNo Date SupplierCode
    -------------------------------
    1 20050829 ABC
    2 20050906 GOLD
    3 20050910 ABC
    4 20050917 WRT

    Table 2 - suppliers

    SupplierCode CompanyName Address
    ---------------------------------------------------
    ABC ABC Products 123 Elm
    GOLD Golden Bear Glassware 123 Maple
    WRT Will Robinson Tarts 123 Oak

    In table 2, SupplierCode is a Primary Key - a key that is unique for each row. In table 1, SupplierCode is a Foreign Key. In MyISAM, these table can be restored in either order, since there are no constraints. However, if the tables are InnoDB, and the FK Constraints are in effect, then any value added to the supplierCode column in Table 1 must already exist in Table 2, otherwise, the row insertion is forbidden by the database engine (This is the constraint). These 2 tables must be restored in the this order: Table 2 first, then table 1. Any attempt to reverse the order will result in rows being rejected in table 1 since there are no existing corresponding rows matching the FK constraint in table 2.

    This gets more complicated as databases get more tables. Our relatively simple model involves only about 25 tables with one or more FK constraints on pretty much all of them.

    Oracle gets around this by suspending the enforcement of FK constraints during the restore process, and re-invoking constraint enforcement once the restoration is complete.

    I'm not totally sure that this is the problem, but I suspect strongly that it might be. The folks at cpanel should be able to set up some tests that could test this hypothesis pretty easily
     
    #3 procam, Jan 6, 2006
    Last edited: Jan 6, 2006
  4. MattGetWeb

    MattGetWeb Well-Known Member

    Joined:
    Aug 4, 2005
    Messages:
    49
    Likes Received:
    0
    Trophy Points:
    6
    Thanks for the possible explanation, procom. I went and had a quick look at the db I had fail, and it does indeed have a mix of MyISAM and InnoDB tables. I'm not sure if that helps, but it fits with your scenario..
     
  5. procam

    procam Well-Known Member

    Joined:
    Nov 24, 2003
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
    It does help - now we need chirpy or someone with some "Clout" to address this issue ASAP.
    This is seriously dangerous to hosts that are trusting cpanel to backup customers data because this will in fact in the event of a drive fail cause the backup of the db to be worthless and the host could potentially lose the customer as a result. NOT GOOD~
    Chirpy help us out man ~

    I sent a pvt to Chirpy and to another host that posts frequently to see if they know what to do next~
     
    #5 procam, Jan 8, 2006
    Last edited: Jan 8, 2006
  6. areha

    areha Well-Known Member

    Joined:
    Oct 30, 2002
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    I had similar problems with my backup done this year. Only two customers databases was successfully restored. Copying over the /var/lib from the backup files/ directory worked however and saved my day and many others :)
     
  7. MattGetWeb

    MattGetWeb Well-Known Member

    Joined:
    Aug 4, 2005
    Messages:
    49
    Likes Received:
    0
    Trophy Points:
    6
    I guess not many people are interested in usable mysql backups.
     
  8. MattGetWeb

    MattGetWeb Well-Known Member

    Joined:
    Aug 4, 2005
    Messages:
    49
    Likes Received:
    0
    Trophy Points:
    6
    Following some of chirpy's advice in another thread, I went looking for a bugzilla entry. It seems CP-Ben has already added one.

    http://bugzilla.cpanel.net/show_bug.cgi?id=786

     
  9. chirpy

    chirpy Well-Known Member

    Joined:
    Jun 15, 2002
    Messages:
    13,475
    Likes Received:
    20
    Trophy Points:
    38
    Location:
    Go on, have a guess
    I'd suggest either adding comments to that bugzilla entry or creating a new one if the problem is unrelated to that. Follow it up with a ticket logged with cPanel through your license provider should bring their attention to the issue. Problems with the backups are usually dealt with quickly.
     
Loading...

Share This Page