OpenCart Community

Posted: **Thu Jul 25, 2013 10:13 pm**

Hallo,
I tried to update opencart to the newest version (1.5.6) and throught updating i got this problem.

Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in /www/napasport.cz/napasport.cz/system/database/mysql.php on line 50

Can you help me please?

thank you

Posted: **Tue Jul 30, 2013 9:47 pm**

hi
i am having the same problem, hopefully a solution is coming out soon

Posted: **Tue Jul 30, 2013 10:39 pm**

Hi

Yep I am new to opencart as well. I also have the same error message when trying to update.

Posted: **Wed Jul 31, 2013 4:42 am**

Same here!

Posted: **Wed Jul 31, 2013 5:32 pm**

Heres my error:

Code: Select all

Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `oc_profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in /home/USERNAME/public_html/MYSITE.co.za/system/database/mysql.php on line 50

I'm in the exact same problem.... Isn't there a Solution???

once i click "Continue" this happens.............................
PLEASE HELP BUDDIES!

Posted: **Sun Aug 11, 2013 8:36 pm**

no solution yet but sql should be:

Code: Select all

ALTER TABLE `oc_profile` CHANGE `frequency` `frequency` ENUM ('day', 'week', 'semi_month', 'month', 'year') AFTER `price`

Posted: **Sat Aug 24, 2013 5:25 am**

Disclaimer

The following is a temporary solution until the OpenCart Team releases a fix.

Moreover, this solution has been applied to a local development environment. After having applied this solution, the upgrade script has completed the process successfully. However, no testing has been performed in order to verify that fact.

The Solution

Open the file: install/model/upgrade.php.
Go to line 55 that reads:
Code: Select all
```
preg_match_all({regular expression}, $sql, $match)
```
where the regular expression tries to read all the column definitions of a table.

Replace the regular expression with the following one:

Code: Select all

'#`(\w[\w\d]*)`\s+((tinyint|smallint|mediumint|bigint|int|tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob|varchar|char|datetime|date|float|double|decimal|timestamp|time|year|enum|set|binary|varbinary)(\(([\d\w\',]+)(,\s*(\d+))?\))?){1}\s*(collate (\w+)\s*)?(unsigned\s*)?((NOT\s*NULL\s*)|(NULL\s*))?(auto_increment\s*)?(default \'([^\']*)\'\s*)?#i'

Notice that the expression provided contains the quotation marks (') that denote the string, too.

Save the file.

And that's it.

The Problem

As you will have probably guessed by now, the problem lies within the regular expression. The replacement that takes place essentially substitutes this part:

Code: Select all

  (\d+)

with this one:

Code: Select all

  ([\d\w\',]+)

A thorough explanation is beyond the goal of this reply. Suffice it to say that this part is responsible for extracting the size of the column. For example, used upon a statement such as:

Code: Select all

  id INT(11)

the above part of the expression would match the number along with the parentheses. That is:

Code: Select all

(11)

Posted: **Sat Aug 24, 2013 10:38 pm**

Same problem for me:

Updating through Softaculous from 1.5.5.1 to 1.5.6. Tried manually also nok:

Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `***_profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in /home/***/public_html/system/database/mysql.php on line 50

Restored from backup to 1.5.5.1.

Posted: **Sun Aug 25, 2013 12:00 am**

Did you apply the above fix?

Posted: **Sat Aug 31, 2013 5:54 pm**

i2Paq wrote:Did you apply the above fix?

i have the same problem, but when i went to chage the code, in the original file it's al ready changed.

So i don't know what to do.

I'm updating 1.5.4.1 to 1.5.6

Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in /.../system/database/mysql.php on line 50

Posted: **Sat Aug 31, 2013 6:57 pm**

I wonder why someone would upgrade to 1.5.6?

Posted: **Sun Sep 01, 2013 4:23 am**

reginamor wrote:i have the same problem, but when i went to chage the code, in the original file it's al ready changed.

Hello, reginamor.

Are you sure it is changed? Because the official 1.5.6 download does not contain the fix, yet.

Anyhow, the file that worked in my case is provided below. Feel free to use it and see if it solves your problem.

Posted: **Thu Sep 05, 2013 6:23 am**

One thread offers a practical solution:
http://forum.opencart.com/viewtopic.php?f=19&t=106677

to this problem:
Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `oc_profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in

Plural threads and isolated posts accordingly bear cross-linkage, at least for the sake of noting its prevalence:
http://forum.opencart.com/viewtopic.php ... 21#p424887 (with a user's editorial comment below it)
http://forum.opencart.com/viewtopic.php?f=20&t=106706 (with perspectives)
http://forum.opencart.com/viewtopic.php?f=20&t=106973

Posted: **Tue Oct 15, 2013 10:28 am**

I am using version 1.5.6 and I am getting this error when I import my remote database into my xampp localhost. Any idea what needs to change?

Code: Select all

#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 '' at line 1

Posted: **Tue Oct 15, 2013 11:22 am**

You didn't tell us enough for any of us to say anything specific. If you give the entire error, after the "line 1", then one of us can try to pick out what seems pivotal, such as the snippet above, ALTER TABLE `oc_profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in, suggesting what is missing or malformed. Just 1064 without that doesn't help. If you swapped .sql files between OC Backup / Restore and phpMyAdmin Export / Import, then your syntax is wrong, as it says, for whatever unstated version you have, and documentation for that is at the mysql website. Just 1064 without that version doesn't help.

Posted: **Wed Oct 16, 2013 8:40 pm**

Hello ,
I have same problem

Code: Select all

Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in /www/XXX/system/database/mysql.php on line 50

I applied the change above and it is same also .

any idea ?

Posted: **Wed Oct 16, 2013 11:31 pm**

It is the upgrade/install script stumbling on the profile table -- there are two posts of a fix. Above. See do it yourself http://forum.opencart.com/viewtopic.php ... 77#p431431 and below that a download at http://forum.opencart.com/viewtopic.php ... 77#p433534 . Between YOUR tables and a script segment or a script, you may need to check particular tables affected by it, for whether you need to add or remove the oc_ prefix.

Posted: **Fri Nov 01, 2013 8:27 pm**

Thanks @weebon , it worked for me, you saved my day.

I had the same error, when upgrading for a client from 1.5.5.1 to 1.5.6.

Code: Select all

Notice: Error: 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 'AFTER `price`' at line 1
Error No: 1064
ALTER TABLE `profile` CHANGE `frequency` `frequency` ENUM AFTER `price` in /www/XXX/system/database/mysql.php on line 50

This is not my first upgrade from 1.5.5.1 to 1.5.6, but it was the first time I had these errors showing up. I assume that the client had a previous update from a lower version to 1.5.5.1 and it was not done right.

Thanks again,
Andy

OpenCart Community

[Updating] Syntax error

[Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error

Re: [Updating] Syntax error