MySQL Backup Tool on Windows IIS Server

[chadderuski]

Hoping someone can help me get this working on IIS.

Here is the location of my executables:

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe

Here is the error that I get when I try to backup:

Error The backup failed because there was an error starting the backup program (mysqldump or mysqldump.exe).
If running on Windows 2003 server, you may need to alter permissions on cmd.exe to allow Special Access to the Internet Guest Account to read/execute.
You should talk to your webhost about why exec() commands are failing when attempting to run the mysqldump binary/program.
The command being run is: "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" "--host=localhost" "--user=samplesite" "--password=*****" --opt --complete-insert "--result-file=M:\web\samplesite.com\admin\backups\db_samplesite-20120618174239.sql" samplesite
Warning Result code: -1
Error Failure: The database has NOT been saved.

The server's admin assures me that permissions are correct for the script. The database username is the same as the site's base name (samplesite > samplesite.com). Hope that make sense!

Thanks in advance for any help!

[DrByte]

Windows Server automatically places a security restriction on cmd.exe so that the Internet Guest Account cannot access it at will. This is in the interest of security. Unfortunately, this prevents PHP from being able to run exec() commands to spawn external binaries/programs to do the tasks requested ... (such as in this case, to do backups/restores using native MySQL utilities).

To work around this, you (or the server administrator) need to alter your security configuration. Find the CMD.EXE program (usually in your windows/system32 directory) and change its properties. Under "Special Access", grant the Internet Guest Account read/execute permission.

[chadderuski]

Thanks Doc, I'll pass this on to the server's admin. I was under the impression that they did do this, but will double check.

[chadderuski]

Okay, that worked and I was able to backup. However, upon restore I received:

[19-Jun-2012 19:41:25 UTC] PHP Fatal error: 1366:Incorrect integer value: '' for column 'configuration_id' at row 1 :: insert into zen_configuration values ('', 'Last Database Restore', 'DB_LAST_RESTORE', 'db_somesite-20120619133748.sql', 'Last database restore file', '6', '', '', now(), '', '') in M:\web\somsite.com\includes\classes\db\mysql\query_factory.php on line 101

So I tried to change line 283 of backup_mysql.php:

$db->Execute("insert into " . TABLE_CONFIGURATION . " values ('', 'Last Database Restore', 'DB_LAST_RESTORE', '" . $specified_restore_file . "', 'Last database restore file', '6', '', '', now(), '', '')");

Changing '' to NULL but that didn't work. It seems the Windows implementation of MySQL doesn't like it when you assign a null value to an integer field.

Any suggestions on how to fix this?

-chadd

[DrByte]

It seems the Windows implementation of MySQL doesn't like it when you assign a null value to an integer field.

Well, it's not so much that you can't assign a NULL to an integer field. More likely the MySQL is configured in very strict mode and if the integer field doesn't have a default value assigned or the auto-increment isn't set for that field, then it doesn't know what you want it to do because '' and NULL are both invalid, and it needs to know what to put in their place. In less strict mode either would work, provided the auto-increment was properly set on the table.

I suggest you double-check that table's schema structure and fix whatever doesn't match vs the original ZC distributed SQL.

[chadderuski]

Thanks Doc, I'll look into that.

One LAST questions. The MySQL implementation I've had thrust upon me also mixes in the phpMyAdmin tables (they start with pma_).

I would think it better to not be backing up/restoring these. From reading the mysqldump manual, I don't see a wild card option so I can backup tables starting with "zen_*" ...

If this is correct, then will I have to list all the zencart table names specifically to keep things separate from the other table?

Thanks again, and I do appreciate your help!

-chadd

[DrByte]

You could list them all separately, or you could simply use phpMyAdmin to do the backups, and select the desired tables that way:
http://www.zen-cart.com/content.php?...ith-phpmyadmin