MySQL 1064 Error

Permalink 1 user found helpful
I get the following MySQL error on installation. I truncated it at the end because it seems like it's spitting back the entire sql file.

I can run the script fine through MySQL Workbench, but then installation complains that there are already tables installed.

mysql 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 'CREATE TABLE AreaGroups ( cID INTEGER(10) UNSIGNED NOT NUL' at line 10] in EXECUTE(" CREATE TABLE AreaGroupBlockTypes ( cID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, arHandle VARCHAR(255) NOT NULL, gID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, uID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, btID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (cID, arHandle, gID, uID, btID) ); CREATE TABLE AreaGroups ( cID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, arHandle VARCHAR(255) NOT NULL, gID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, uID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, agPermissions VARCHAR(64) NOT NULL, PRIMARY KEY (cID, arHandle, gID, uID) ); CREATE TABLE Areas ( arID INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, cID INTEGER(10) UNSIGNED NOT NULL DEFAULT 0, arHandle VARCHAR(255) NOT NULL, ... "

asmith
View Replies: View Best Answer
asmith replied on at Permalink Reply
asmith
Some specs would be helpful.

MySQL 5.0.77

C5: 5.4.1.1
Brainakazariua replied on at Permalink Reply
Brainakazariua
A basic on the error:

The error 1064 occurs due to Mysql version mismatch,because different versions have different
reserved words, which if used by a previous version causes the error to occur.

The simplest and most effective solution for it is.
-when using phpMyadmin,select the "compatibility mode" & select the target db version from the drop down box.
-when using MySql administrator,while backing up choose "compatibility mode" in advanced tab ,
and while restoring through mysql administrator ,click "auto-detect file charater set" if backup was not made using mySql administrator.
the default file characer set used by mysql admministrator is utf8.
asmith replied on at Permalink Reply
asmith
Thanks for all the information. Is compatibility mode an issue on a new installation? I did a successful installation on MySQL 5.0.91, but it's giving me a fit on another server with 5.0.77. I was thinking it was charset/collation issues, but I'm not positive yet.
Brainakazariua replied on at Permalink Reply
Brainakazariua
It can be.
asmith replied on at Permalink Reply
asmith
New piece to the puzzle. If I remove the first two line breaks on schema.sql, the create table scripts now work. It's now breaking on sample_content.sql, but fiddling with the line breaks on that files isn't yielding the same success.
dwayneparton replied on at Permalink Reply
dwayneparton
I am getting this same error while trying to install on GoDaddy Windows 4GH Hosting & MySql 5. One thing I thought was weird is that I too can take the MySql script and run it in phpmyadmin and it works perfectly installing all of the tables, but it won't work with the installer. I have searched everywhere for a solution. Is there a way to manually install? That may be a dumb question but I thought I would ask. I tried creating a config/site.php file with the DB specks after I ran the db script in phpmyadmin but then I received a Page Not Found error. Thanks in advance.
newreality replied on at Permalink Reply
I have the same problem as you dwayneparton.

I have no idea what is going on!

You have any clue?
asmith replied on at Permalink Reply
asmith
Well, I got it to work. I wish I could give an exact reason, but I had tried several things, went outside for a break, uploaded a fresh sample_content.sql on return and it installed fine.

Some things of note:

I had to remove the first two line breaks in the schema.sql file.

Created the MySQL schema in UTF8 default collation
dwayneparton replied on at Permalink Best Answer Reply
dwayneparton
asmith I think you hit the nail on the head by uploading the files as UTF-8. That is all I did and I didn't have to remove any line breaks or anything.

If you come across this problem(at least on godaddy windows hosting) you should try to save and replace the following files as UTF-8:
/concrete/config/schema.sql
/concrete/config/no_sample_content.sql
/concrete/config/sample_content.sql

CREATING A UTF-8 VERSION
1. Open the file in a text-editor such as notepad(Windows) or textedit(Mac OS).
2. Click File>Save As
3. In the "Save As" window you should see a drop down that is labeled "Encoding"(Windows) or "Plain Text Encoding"(Mac).
4. Select UTF-8
5. Save
6. Replace the files on your server with the newly created files

When you run the install you should not have any problems.