Database backup mySQL can't restore

I'm running ZC 1.3.7 with the Database backup mySQL mod. Haven't had any problems with this contribution in the past, but on this one particular site, it fails to restore from backups. I always get: > Result code: 1

Error Failure: The database may NOT have been restored properly. Please check it carefully.

Your host probably doesn't let you have access to the "mysql" binary in order to perform the restore.

You'll likely have to use phpMyAdmin to load the SQL file manually.

Thanks Doc! I'll check with IX Webhosting and ask if they can grant me access to the mySQL binary. > DrByte:

Your host probably doesn't let you have access to the "mysql" binary in order to perform the restore.

You'll likely have to use phpMyAdmin to load the SQL file manually.

Okay, I checked with IX Webhosting's tech support, and access to the mysql binary was not the problem.

What else would cause Result code: 1 / Error Failure: The database may NOT have been restored properly. Please check it carefully?> Dear Sean!

Both mysql and mysql_dump are present on the server and accessible for php (httpd) user. Below you may find path:

/usr/bin/mysql
/usr/bin/mysqldump

Please let me know should you need further assistance.

Anyone have any idea why Database backup MySQL doesn't restore? It doesn't Download without storing on server either -- except when Skip lock tables option is selected. As for restoring, though, it refuses to restore no matter what. :lamo: > tvadpro:

Okay, I checked with IX Webhosting's tech support, and access to the mysql binary was not the problem.

What else would cause Result code: 1 / Error Failure: The database may NOT have been restored properly. Please check it carefully?

I am having the same issue with 1.3.7 and the backup module. I do a backup and it says that it has backed it up (shows the backup file) but try and restore and get the Error 1 error. Also, with the backup file that the module created I tried importing it from MySQL Admin and it errors out, so it is not backing up correctly either. I now am using MySQLAdmin to export and import my database until this is resolved. Hope someone has an answer.

eist1:

I am having the same issue with 1.3.7 and the backup module.Well, at least we both know we're not the only one. :cry:

Did you already ask your hosting provider (as I did) whether the mysql binary is accessible on your server?

Sean

We know the problem seems unrelated to server configuration, as eist1 has more than one Zen-Cart site on the same server, but only one of those sites fails to restore from backup via Database Backup MySQL.

I'm stumped as to what causes this -- so stumped that I'm considering a second Zen-Cart installation (subdirectory) on the same server as my problem site -- just on the off chance I might make the mod work on the second Zen-Cart -- and then incrementally change the second one to mirror the first -- until the restore-from-backup breaks, thus isolating the root of this excruciating headache.

Or I might knock my own brains out with a large rock. :sleepy: > tvadpro:

Anyone have any idea why Database backup MySQL doesn't restore? It doesn't Download without storing on server either -- except when Skip lock tables option is selected. As for restoring, though, it refuses to restore no matter what. :lamo:

tvadpro:

Anyone have any idea why Database backup MySQL doesn't restore? It doesn't Download without storing on server either -- except when Skip lock tables option is selected. As for restoring, though, it refuses to restore no matter what. :lamo:

1. What's with the large font ?

2. "Skip Lock Tables" refers to a database-permissions setting. If your database userID doesn't have permissions to lock tables before doing an export, you need to check that box so that the backup doesn't attempt to do the usual lock-tables action. If your host won't give you control over that permission setting, you'll have to be sure to check the box every time you do a backup.

3. "Result code: 1 / Error Failure: The database may NOT have been restored properly. Please check it carefully?"
   This error message occurs when the EXEC() function is called and returns error code #1 from the Operating System back to PHP.
   You'll have to dig up what Error code 1 means for your operating system.

You could try running the restore in debug mode to see if it gives you any further information for diagnosis:
a) Simply select a file to restore,
b) click the "restore" button,
c) then add &debug=ON to the URL, press Enter, and click the restore button on the right-side box.
(you might even take note of the suggested EXEC() command syntax posted in the sidebox and try it via command line)
d) Then post the additional output here, masking your passwords with ****** if you care to do so.
_

Thanks DrByte!

I'll follow your instructions for restoring in debug mode, but I'll have to wait until late this evening. We're talking about a production site which is busiest 0900-1700 EST. Last time I got error #1 while restoring-from-backup, I soon discovered my address book table was corrupted and several default customer addresses were missing. I don't know if there is a causal relationship between my restore problem and my address book problem, but just in case there is, I'd hate to put the site in maintenance mode during prime-time.

I have no problem checking the skip-lock-tables box before every backup -- I should have also mentioned that 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. It's as if the skip-lock-tables box has no function other than to prevent creation of a backup when the database user lacks this permission.

As for the large font, it's just a semi-conscious attention-seeking behavior. My psychiatrist thinks this sort of thing is rooted in my fear of abandonment, like the way my dog goes bananas every time I pick up the car keys. If it annoys you, I'll stop.> DrByte:

1. What's with the large font ?

2. "Skip Lock Tables" refers to a database-permissions setting. If your database userID doesn't have permissions to lock tables before doing an export, you need to check that box so that the backup doesn't attempt to do the usual lock-tables action. If your host won't give you control over that permission setting, you'll have to be sure to check the box every time you do a backup.

3. "Result code: 1 / Error Failure: The database may NOT have been restored properly. Please check it carefully?"
   This error message occurs when the EXEC() function is called and returns error code #1 from the Operating System back to PHP.
   You'll have to dig up what Error code 1 means for your operating system.

You could try running the restore in debug mode to see if it gives you any further information for diagnosis:
a) Simply select a file to restore,
b) click the "restore" button,
c) then add &debug=ON to the URL, press Enter, and click the restore button on the right-side box.
(you might even take note of the suggested EXEC() command syntax posted in the sidebox and try it via command line)
d) Then post the additional output here, masking your passwords with ****** if you care to do so.
_

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;

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 are1. causing Restore to fail

2. dropping the address_book tableI 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

Okay, time to blow the dust off this one ...> tvadpro:

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?

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) = OFFCould this have anything to do with Database Backup putting LOCK TABLES commands into backups when the Skip Lock Tables option is checked?

tvadpro:

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

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?main_page=product_contrib_info&products_id=81

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

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.

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.

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