pkgacct script not consistent in saving mysql dbs

[te2586]

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!

[/bin/bash.org]

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

[procam]

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!

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!!

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

[/bin/bash.org]

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..

[procam]

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..

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~

[areha]

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

[/bin/bash.org]

I guess not many people are interested in usable mysql backups.

[/bin/bash.org]

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

Foreign key checks prevent the database from being restored from a dump of the
database.

ERROR 1005 at line 11: Can't create table './dbdump/mydb.frm' (errno: 150)

To sucessfully restore the database add the following to the beginning of the dump:

SET FOREIGN_KEY_CHECKS=0;

This issue should only affect dumps made with MySQL < 4.1

[chirpy]

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.