Database backup mySQL can't restore

[tvadpro]

... even though skip-lock-tables box is checked, the backups I download still contain lock (and unlock) table commands which I must remove manually before I can run the SQL in phpMyAdmin. ...

Like an iron skillet to the back of the head, it just hit me -- since skip-lock-tables has no effect on the backups I download, what if it similarly has no effect on backups stored on the server?

When trying to restore, the process would break on the first table, namely address_book, yes?

DROP TABLE IF EXISTS `zen_address_book`;
CREATE TABLE `zen_address_book` (
`address_book_id` int(11) NOT NULL auto_increment,
`customers_id` int(11) NOT NULL default '0',
`entry_gender` char(1) NOT NULL default '',
`entry_company` varchar(32) default NULL,
`entry_firstname` varchar(32) NOT NULL default '',
`entry_lastname` varchar(32) NOT NULL default '',
`entry_street_address` varchar(64) NOT NULL default '',
`entry_suburb` varchar(32) default NULL,
`entry_postcode` varchar(10) NOT NULL default '',
`entry_city` varchar(32) NOT NULL default '',
`entry_state` varchar(32) default NULL,
`entry_country_id` int(11) NOT NULL default '0',
`entry_zone_id` int(11) NOT NULL default '0',
PRIMARY KEY (`address_book_id`),
KEY `idx_address_book_customers_id_zen` (`customers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `zen_address_book`
--


/*!40000 ALTER TABLE `zen_address_book` DISABLE KEYS */;
LOCK TABLES `zen_address_book` WRITE;

First the address_book table is dropped, and then the LOCK TABLES occurs. Assuming the restore process breaks at this point (lock tables), wouldn't address_book be missing after an unsuccessful restoration attempt? If so, would ZC create a new address_book the next time someone creates a customer account or adds an address to their existing account -- with an address_book_id of 1, even though an older customer account had previously created an address with the same address_book_id?

Am I nuts, or would this explain both the restoring problem and the address book problem?

Sean

[tvadpro]

Am I nuts, or would this explain both the restoring problem and the address book problem?

It would seem I've hit the nail on the head. Since my last post, several new customers have created accounts and made purchases. In this time, I have not attempted to restore the database from any backup, and the address_book problem has yet to return. I am 99% certain the presence of LOCK TABLES commands in the backups are

1. causing Restore to fail
2. dropping the address_book table

I have not yet tried to restore with &debug=ON tacked to the end of the URL. I will do this once I have a backup which is completely free from LOCK TABLES commands. I suppose I can download a backup, remove the LOCK TABLES commands manually, then upload and restore from it (with &debug=ON tacked to the end of the URL). I hate to experiment with something known to mess up the address_book, especially on a productive store, but I don't know of a viable alternative.

Meanwhile, a question:

When I am creating a backup and I have selected the "Skip Lock option," should the resulting SQL file contain LOCK TABLES commands? Mine do -- every time. Isn't this a bug?

Sean

[tvadpro]

Okay, time to blow the dust off this one ...

When I am creating a backup and I have selected the "Skip Lock option," should the resulting SQL file contain LOCK TABLES commands? Mine do -- every time. Isn't this a bug?

[tvadpro]

I just started migrating a 1.3.7 site to the same shared web hosting server. Noticed on the initial install checker the following:

# PHP Output Buffering (gzip) = OFF

Could this have anything to do with Database Backup putting LOCK TABLES commands into backups when the Skip Lock Tables option is checked?

[DrByte]

When I am creating a backup and I have selected the "Skip Lock option," should the resulting SQL file contain LOCK TABLES commands?

edit /admin/backup_mysql.php
around line 123, you have:

          $dump_params .= ' --skip-lock-tables';     //use this if your host prevents you from locking tables for backup

Change by adding text as shown:

          $dump_params .= ' --skip-lock-tables --skip-add-locks';     //use this if your host prevents you from locking tables for backup

[DrByte]

Contribution has been updated to incorporate the above fix as well as remove the requirement to check the skip-lock-tables radio-button if your MySQL user doesn't have the required permissions. (It now traps that error and re-attempts the backup with the skip-locks parameters set.)

http://www.zen-cart.com/index.php?ma...products_id=81

[tvadpro]

Thanks, DrByte! I replaced the line around 123 ($dump_params) as you first indicated, but I got

Parse error: parse error, unexpected T_CASE in /hsphere/local/home/user/domain/admin/backup_mysql.php on line 207

when I tried running the mod. I then downloaded and unzipped the revised mod from your link, and compared revised files to the existing ones. Noticing you'd overhauled admin/backup_mysql.php in a big way, I installed the complete version 1.3.

Bravo! Works flawlessly with 1.3.7, at least in so far as it omits the LOCK_TABLES from new backups. Haven't tried restoring from backup yet; I'll install 1.3 on a non-productive site this weekend and do a restore from backup.

Again, many thanks and a round of coffee for the fix.

Sean

Contribution has been updated to incorporate the above fix as well as remove the requirement to check the skip-lock-tables radio-button if your MySQL user doesn't have the required permissions ...

[gxjenkins]

Hello,
I downloaded and installed Database Backup 1.3 today and I'm getting errors when I try to do a backup.

“Warning Result code: 2
Warning 0 => /usr/bin/mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
Warning 1 => /usr/bin/mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file
Warning 2 => /usr/bin/mysqldump: Got error: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client when trying to connect”


I'm wondering if this is not working because I'm hosted with GoDaddy, or could it be something in the local setup. Any information would be appreciated.

I'm using cart version 1.3.7

Thanks.

[DrByte]

That sounds like it's a server config problem that is most likely a result of limitations GoDaddy has set on their server.
You might be able to get resolution via their tech support team (ask them to address the charset issues in their mysql configuration).
However, since your database sits on a server that is separate from your hosting server, you may have some limitations in using the server-side tool to do the backup.
You'll likely have to use phpMyAdmin instead, or some backup option offered by GoDaddy's control panel, if any.

[gxjenkins]

Thanks, I'll check with GoDaddy. I was warned of possible limitations but hadn't run into any until now.
Thanks.