[Updating] Syntax error

Quote

Post by wedr » 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.

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/napasport.cz/napasport.cz/system/database/mysql.php on line 50
Can you help me please?

thank you
wedr
Newbie
Posts
4
Joined
Fri Oct 26, 2012 8:54 pm
Top

Re: [Updating] Syntax error

Quote

Post by imverypoor » Tue Jul 30, 2013 9:47 pm

hi
i am having the same problem, hopefully a solution is coming out soon
imverypoor
Newbie
Posts
12
Joined
Wed Jul 17, 2013 3:54 pm
Top

Re: [Updating] Syntax error

Quote

Post by webgold » 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.
webgold
New member
Posts
24
Joined
Tue Jul 30, 2013 10:37 pm
Top

Re: [Updating] Syntax error

Quote

Post by vitalyk » Wed Jul 31, 2013 4:42 am

Same here!
vitalyk
New member
Posts
69
Joined
Sat Mar 31, 2012 10:31 am
Top

Re: [Updating] Syntax error

Quote

Post by linuxos » 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??? :choke: :choke: :choke:
once i click "Continue" this happens.............................
PLEASE HELP BUDDIES! ??? ??? ???
linuxos
Newbie
Posts
2
Joined
Wed Jul 31, 2013 5:27 pm
Top

Re: [Updating] Syntax error

Quote

Post by snakehult » 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`

Markus Edholm
ME fix IT AB
Facebook
mefixit.se

snakehult
New member
Posts
58
Joined
Tue Jan 15, 2013 7:28 pm
Top

Re: [Updating] Syntax error

Quote

Post by weebon » 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)
weebon
Newbie
Posts
2
Joined
Sat Aug 24, 2013 3:29 am
Top

Re: [Updating] Syntax error

Quote

Post by kosh » 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.
kosh
Newbie
Posts
1
Joined
Sat Aug 24, 2013 10:30 pm
Top

Re: [Updating] Syntax error

Quote

Post by i2Paq » Sun Aug 25, 2013 12:00 am

Did you apply the above fix?

Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________

Our FREE search: Find your answer FAST!.

[How to] BTW + Verzend + betaal setup.

User avatar
i2Paq
Global Moderator
Posts
12244
Joined
Mon Nov 09, 2009 7:00 pm
Location - Winkel - The Netherlands
Top

Re: [Updating] Syntax error

Quote

Post by reginamor » 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
User avatar
reginamor
Active Member
Posts
247
Joined
Wed Sep 21, 2011 4:44 am
Location - GB
Top

Re: [Updating] Syntax error

Quote

Post by i2Paq » Sat Aug 31, 2013 6:57 pm

I wonder why someone would upgrade to 1.5.6?

Norman in 't Veldt
Moderator OpenCart Forums
_________________ READ and Search BEFORE POSTING _________________

Our FREE search: Find your answer FAST!.

[How to] BTW + Verzend + betaal setup.

User avatar
i2Paq
Global Moderator
Posts
12244
Joined
Mon Nov 09, 2009 7:00 pm
Location - Winkel - The Netherlands
Top

Re: [Updating] Syntax error

Quote

Post by weebon » 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.

Attachments

MD5: 5F14DCEB35D9F1A8EEAFBDE0FDA5BFBE
SHA-1: 6F0FC59A043B78D3864C5EC1C63D1E973AEA8E42

upgrade.php Downloaded 943 times

Download - 11.76 KiB

weebon
Newbie
Posts
2
Joined
Sat Aug 24, 2013 3:29 am
Top

Re: [Updating] Syntax error

Quote

Post by butte » 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
butte
Guru Member
Posts
7063
Joined
Wed Mar 20, 2013 6:58 am
Top

Re: [Updating] Syntax error

Quote

Post by sully210 » 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

Thank you,
Sully :)

User avatar
sully210
Active Member
Posts
104
Joined
Tue Jul 13, 2010 4:46 am
Location - USA
Top

Re: [Updating] Syntax error

Quote

Post by butte » 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.
butte
Guru Member
Posts
7063
Joined
Wed Mar 20, 2013 6:58 am
Top

Re: [Updating] Syntax error

Quote

Post by MadaStore » 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 ?
MadaStore
Newbie
Posts
10
Joined
Sat Aug 31, 2013 3:44 pm
Top

Re: [Updating] Syntax error

Quote

Post by butte » 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.
butte
Guru Member
Posts
7063
Joined
Wed Mar 20, 2013 6:58 am
Top

Re: [Updating] Syntax error

Quote

Post by andrei.bogdan » 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

Andrei
andrei.opencart[at]yahoo.com
Themes and Extensions

andrei.bogdan
Active Member
Posts
303
Joined
Mon Mar 25, 2013 4:52 am
Top
Post Reply
Return to “Installation, Upgrade, & Config Support”
Who is online

Users browsing this forum: No registered users and 70 guests