Error with new install 1.3.9d 1064 error in SQL syntax

[Joseph M]

I tried the latest 1.3.9d but was not able to get it to install.
I received the following error:

1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE latin1_general_ci' at line 1
in:
[CREATE TABLE upgrade_exceptions ( upgrade_exception_id smallint(5) NOT NULL auto_increment, sql_file varchar(50) default NULL, reason varchar(200) default NULL, errordate datetime default '0001-01-01 00:00:00', sqlstatement text, PRIMARY KEY (upgrade_exception_id) ) ENGINE=MyISAM COLLATE latin1_general_ci;]

I had no trouble installing 1.3.9b and followed all of the same installer setup instructions.

Anyone know what may cause this?
Thank you

[DrByte]

What version of MySQL?

[Joseph M]

MySQL 4.0.20-standard

[DrByte]

This is on a testing area for testing your upgrade, right?
What's the DB_PREFIX setting in your configure.php files?
If you look at all your tables in your Zen Cart database using phpMyAdmin, what's the collation set to on all your tables?

[mydreams]

Dear drByte,

I've also faced problem at Prerequisites - System Inspection.

There is no action after the step. No items marked in red or with an "X" or green check-marks. I've been wait for long time but still no action...only display

"Take a moment to check whether your webserver supports the features required for Zen Cart™ to operate. Please resolve any errors or warnings before continuing. Then click on Install to continue."


No "Install" button...so I stuck here. Don't know what to do. Please help me.

Thanks.

[Joseph M]

This is on a testing area for testing your upgrade, right?
What's the DB_PREFIX setting in your configure.php files?
If you look at all your tables in your Zen Cart database using phpMyAdmin, what's the collation set to on all your tables?

Yes, it's a test site to try out the new versions.
The configure.php in admin/includes reads define('DB_PREFIX', 'zen_');
The configure.php in includes reads define('DB_PREFIX', ' ');
The database was a new one I created for the 1.3.9d version, but there are no tables found in database.

[DrByte]

What does phpMyAdmin tell you about the default collation or character-set for that empty database?

[dj_uk]

I have also experienced this error when trying a new install.

Confirmed the charset in the database is "latin1", I'm running MySQL 4.0.27, which does not have a "latin1_general_ci" charset.

Also confirmed that 1.3.9c installs fine without this error, so the problem is introduced in 1.3.9d which causes this.

The file "zc_install/includes/functions/general.php" has been changed in 1.3.9d to include a "$collateSuffix" string which is appended to the charset, this is what is causing the error.

I'm no expert, but my guess is this was changed without consideration of backwards compatibility, it probably only exists in MySQL 5.x or greater (don't have it so I can't check), so the minimum specs for ZenCart have been inadvertently changed to exclude MySQL 4.x.

Certainly there's no change in the minimum requirements posted on the ZenCart site, if you can find any.

Would be nice if someone can give an indication if this will be reversed or if the minimum specs will now officially change.

The differences between 1.3.9c and 1.3.9d :

zc_install/includes/functions/general.php
9c9
< * @version $Id: general.php 15909 2010-04-12 16:36:22Z drbyte $
---
> * @version $Id: general.php 16607 2010-06-03 12:37:20Z drbyte $
88a89
> $collateSuffix = '';
141d141
< zen_write_to_upgrade_exceptions_table($line, sprintf(REASON_TABLE_ALREADY_EXISTS,$table), $sql_file);
143c143,146
< $result=sprintf(REASON_TABLE_ALREADY_EXISTS,$table); //duplicated here for on-screen error-reporting
---
> if (strtoupper($param[2].' '.$param[3].' '.$param[4]) != 'IF NOT EXISTS') {
> zen_write_to_upgrade_exceptions_table($line, sprintf(REASON_TABLE_ALREADY_EXISTS,$table), $sql_file);
> $result=sprintf(REASON_TABLE_ALREADY_EXISTS,$table); //duplicated here for on-screen error-reporting
> }
146a150
> $collateSuffix = ' COLLATE ' . DB_CHARSET . '_general_ci';
189a194,203
> case (substr($line_upper, 0, 19) == 'ALTER IGNORE TABLE '):
> // check to see if ALTER IGNORE command may be safely executed
> if ($result=zen_check_alter_command($param)) {
> zen_write_to_upgrade_exceptions_table($line, $result, $sql_file);
> $ignore_line=true;
> break;
> } else {
> $line = 'ALTER IGNORE TABLE ' . $table_prefix . substr($line, 19);
> }
> break;
317a332,335
> if ($collateSuffix != '') {
> $newline = rtrim($newline, ';') . $collateSuffix . ';';
> $collateSuffix = '';
> }

[dj_uk]

Can confirm that by changing the line :

$collateSuffix = ' COLLATE ' . DB_CHARSET . '_general_ci';

to

$collateSuffix = ' ';

in "zc_install/includes/functions/general.php" in version 1.3.9d, Zen installs fine without issue, at least for me. However, I haven't the faintest idea of the implications of doing this, so do this at your own risk.

It's the first time I've even looked at ZenCart, I'm just evaluating shopping cart systems for a new project, so I am not at all familiar with it.

It would be nice if one of the developers could look into this, evaluate the implications and comment on them, and look into why this was included in 1.3.9d and whether it should be backed out or addressed in some other way.

Good luck!

[DrByte]

This is the better approach: http://www.zen-cart.com/forum/showpo...48&postcount=6