From Zen Cart(tm) Wiki

Contents 1 Overview 1.1 Magic quotes gpc 1.2 Cannot insert configuration_key "" because it already exists 1.3 Patch tool syntax 1.4 Other issues 1.5 Conclusion

Overview

The Zen Cart™ Admin contains a tool, under the Tools menu, called Install SQL Patches. You can cut and paste SQL commands here, or browse to specify an SQL file on your hard drive to be uploaded. The tool will automatically prepend your Zen Cart™ prefix to the tables and then run the SQL on your database.

Sometimes the tool does not work well with SQL scripts containing special characters, or if the SQL statements require certain other dependencies and the scripts haven't been written with that in mind.. Below are a few of the known challenges that can occur with the Install SQL Patches tool.

Magic quotes gpc

When magic_quotes_gpc is on in your PHP configuration, it causes the tool to choke on certain input when loading a file.

You may see an error like this:

 1064 There is an error in your SQL syntax ... near 'preview','final'),' )' 

though there is no syntax error in the original file. You can fix this problem by opening the file admin/sqlpatch.php and replacing the line:

            $query_string  = zen_db_prepare_input($upload_query);

with this:

            $query_string  = $upload_query;

A better solution is to turn off magic_quotes_gpc if possible.

Cannot insert configuration_key "" because it already exists

Sometimes the patch tool will give you this error:

ERROR: Cannot insert configuration_key "" because it already exists

This may be related to particular versions of PHP, MySQL, and phpMyAdmin, or it may not. If you get this error, you can just run the SQL file directly in phpMyAdmin.

Patch tool syntax

The Install SQL Patches tool is very picky about the syntax your SQL file should use. In particular, it requires certain statements to be broken up onto two lines, and does not (yet) support all SQL keywords. Here is the documentation from the file admin/sqlpatch.php:

The SQLPATCH tool lets you install system patches by pasting SQL code 
directly into the textarea field here, or by uploading a supplied script 
(.SQL) file.

When preparing scripts to be used by this tool, DO NOT include a table prefix, 
as this tool will automatically insert the required prefix for the active 
database, based on settings in the store's admin/includes/configure.php file 
(DB_PREFIX definition).

The commands entered or uploaded may only begin with the following statements, 
and MUST be in UPPERCASE:

     DROP TABLE IF EXISTS
     CREATE TABLE
     INSERT INTO
     ALTER TABLE
     UPDATE (just a single table)
     DELETE FROM
     DROP INDEX
     CREATE INDEX
     SELECT

Advanced Methods

The following methods can be used to issue more complex statements as necessary:

To run some blocks of code together so that they are treated as one command by 
MySQL, you need the "#NEXT_X_ROWS_AS_ONE_COMMAND:xxx" value set. The parser will 
then treat X number of commands as one.

If you are running this file thru phpMyAdmin or equivalent, the "#NEXT..." comment 
is ignored, and the script will process fine.

NOTE: SELECT.... FROM... and LEFT JOIN statements need the "FROM" or "LEFT JOIN" to 
be on a line by itself in order for the parse script to add the table prefix.

Examples:
#NEXT_X_ROWS_AS_ONE_COMMAND:4
SET @t1=0;
SELECT (@t1:=configuration_value) as t1
FROM configuration
WHERE configuration_key = 'KEY_NAME_HERE';
UPDATE product_type_layout SET configuration_value = @t1 WHERE configuration_key = 'KEY_NAME_TO_CHECK_HERE';
DELETE FROM configuration WHERE configuration_key = 'KEY_NAME_HERE';

#NEXT_X_ROWS_AS_ONE_COMMAND:1
INSERT INTO tablename
(col1, col2, col3, col4)
SELECT col_a, col_b, col_3, col_4
FROM table2;

#NEXT_X_ROWS_AS_ONE_COMMAND:1
INSERT INTO table1 
(col1, col2, col3, col4 )
SELECT p.othercol_a, p.othercol_b, po.othercol_c, pm.othercol_d
FROM table2 p, table3 pm
LEFT JOIN othercol_f po
ON p.othercol_f = po.othercol_f
WHERE p.othercol_f = pm.othercol_f;

Note that the NEXT_X_ROWS_AS_ONE_COMMAND appears to refer to transactions (queries that should be carried out as an atomic unit to avoid integrity problems in the database should e.g. the power fail between two queries), not to the number of lines a single query has been broken up into.

Other issues

Another thing to note is that the SQL Patches tool determines what to do by parsing individual words in the SQL statement. It uses spaces (single space) to denote the end of one word and the beginning of another. Thus, a space is required between the table name and the following opening parenthesis.

This should work fine for example:

INSERT INTO configuration ( configuration_title, configuration_key, configuration_value)

But this will not work as expected:

INSERT INTO configuration( configuration_title, configuration_key, configuration_value)

In the second case the Zen Cart™ SQL Patches tool will fail because it cannot identify the table name successfully. (If you are not using table-prefixes, you may find that some of these commands will occasionally work anyway, but it may be advisable to write the syntax in more clear terms, both for the sake of this tool and for ease of readability.)

Conclusion

The patch tool can be a convenient tool for running SQL queries associated with contributions and add-ons; it automatically adds your Zen Cart™ database prefix so that you don't have to do that manually. It also does some system pre-checking so that commands which would cause an unexplained SQL statement failure are prevented from running, thus often saving you the headaches of errors which may appear by running the SQL statements directly in phpMyAdmin.

If you experience problems running an SQL file through the Install SQL Patches tool, you can almost always solve the problem by opening the SQL file, manually adding your Zen Cart prefix if any to all the tables in the file, and using phpMyAdmin to upload and run the file.

However, if you choose to run statements via phpMyAdmin instead, then note that if you encounter a database-error (i.e. a particular statement fails), you must carefully determine which statement failed, fix that statement, or skip it, and then resubmit only the remaining commands which have not yet executed. If you resubmit the whole script over again, you will likely get even more errors, and even run the risk of having duplicate information in your database. It is these caveats that the SQL Patches tool is intended to overcome.