Installation DB syntax error *SOLVED*

Joomla version 1.5 is end-of-life and are no longer supported. Please use Joomla 3.x instead.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
amko94
Joomla! Apprentice
Joomla! Apprentice
Posts: 18
Joined: Mon Jan 24, 2011 4:34 pm

Re: Installation DB syntax error *SOLVED*

Post by amko94 » Mon Jan 24, 2011 4:51 pm

fredkadet wrote:Drakkheim's attached file worked for me! Using WAMP 2.1, Thanks!
It work for me too..:D

daverazor
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Thu Nov 18, 2010 6:15 pm

Re: Installation DB syntax error *SOLVED*

Post by daverazor » Sun Jan 30, 2011 6:35 pm

Drakkheim's 'joomla.sql' in the zip file worked for me after two days of screwing around with this.

Thanks Drakkheim! (and whoever else suggested the changes to joomla.sql)

It was weird, Joomla 1.6 would install but 1.5.22 would not. It was suggested to install Zend Framework Community Edition...that didn't help at all.

amko94
Joomla! Apprentice
Joomla! Apprentice
Posts: 18
Joined: Mon Jan 24, 2011 4:34 pm

Re: Installation DB syntax error *SOLVED*

Post by amko94 » Tue Feb 01, 2011 6:43 pm

easyest way is to fix it is to install wamp 2.0i :)

dknowz
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Thu Oct 15, 2009 5:44 am

Re: Installation DB syntax error *SOLVED*

Post by dknowz » Wed Feb 02, 2011 12:32 am

Hello everyone! I tried replacing the files as mentioned above, but now I'm getting a message that says Unable to connect to the database:Could not connect to MySQL. I tried restarting mysql but I'm not having any luck. Does anyone have a suggestion for me before I actually uninstall Wampserver and install the prior version? Thanks in advance.

User avatar
dpacadmin
Joomla! Champion
Joomla! Champion
Posts: 6029
Joined: Sat Aug 16, 2008 1:46 pm
Location: the Bat Cave
Contact:

Re: Installation DB syntax error *SOLVED*

Post by dpacadmin » Wed Feb 02, 2011 1:21 am

The default MySQL user is root and the password should be left blank. If you added a password to the MySQL user that could be your problem, I haven't had much luck adding a password. Make sure the MySQL service is running, Alt/Ctrl/Delete you should see mysqld-nt.exe running. You can left click the Wamp icon, bottom right tray, and Restart or Start all services to make sure it is running.

dknowz
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Thu Oct 15, 2009 5:44 am

Re: Installation DB syntax error *SOLVED*

Post by dknowz » Wed Feb 02, 2011 8:04 am

Thanks Dpac, but the first thing I checked was to make sure mysql was running. I also left the password blank and left the user as root. I guess it must be something with the new version of wamp. Should I install the previous version of Wamp? It seems like people are saying that works.

User avatar
dpacadmin
Joomla! Champion
Joomla! Champion
Posts: 6029
Joined: Sat Aug 16, 2008 1:46 pm
Location: the Bat Cave
Contact:

Re: Installation DB syntax error *SOLVED*

Post by dpacadmin » Wed Feb 02, 2011 11:18 am

If the user name and password are correct double check the database name. The problem others are having with the latest version of Wamp is that the Joomla sql file is not updated for the latest version of MySQL that it uses. If you are using the correct connection info but getting a can not connect issue then you haven't gotten to the sql error problem yet. You might try un-installing Wamp and installing the older version WampServer2.0i and see if that helps.

irastralia
Joomla! Intern
Joomla! Intern
Posts: 53
Joined: Thu Oct 28, 2010 7:30 am

Re: Installation DB syntax error *SOLVED*

Post by irastralia » Thu Feb 03, 2011 8:49 am

ENGINE = MyISAM was great thanks

telmagz
Joomla! Apprentice
Joomla! Apprentice
Posts: 8
Joined: Sat Sep 13, 2008 3:36 pm

Re: Installation DB syntax error *SOLVED*

Post by telmagz » Fri Feb 04, 2011 12:40 pm

Hi everyone,

just wanted to witness that integradona made my day by solving the issue.

Is just funny that I used joomla1.5 before without ever getting this error.... where does it suddenly comes from?

This was a nice discussion here and it's great that the community works so well together!

peterjweller
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Fri Mar 16, 2007 9:40 am

Re: Installation DB syntax error *SOLVED*

Post by peterjweller » Sat Feb 05, 2011 3:22 pm

Just another thankyou to Drakkheim for his fix and to say that this has restored my confidence in Joomla. I was using XAMPP

cale1982
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Wed Feb 09, 2011 9:08 pm

Re: Installation DB syntax error *SOLVED*

Post by cale1982 » Wed Feb 09, 2011 9:21 pm

Drakkheim wrote:9Iceman's copy paste fix worked for me.

I've attached my 1.5.22 joomla install sql in a zip .
Yes, yes, yes! It works.

Original code:

) CHARACTER SET `utf8`;

Changed code:


) TYPE=MyISAM CHARACTER SET `utf8`;


Thank You, guys!
:D

ramjothidar
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Sat Feb 12, 2011 7:03 am

Re: Installation DB syntax error *SOLVED*

Post by ramjothidar » Sat Feb 12, 2011 7:13 am

Drakkheim wrote:9Iceman's copy paste fix worked for me.

I've attached my 1.5.22 joomla install sql in a zip .

Now its work.. Thanks.

nicmehr
I've been banned!
Posts: 36
Joined: Fri Feb 04, 2011 12:19 pm

Re: Installation DB syntax error *SOLVED*

Post by nicmehr » Sat Feb 12, 2011 7:26 am

i think if you use appserv
you will not have any problem like this

User avatar
felipe71ar
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Fri Mar 13, 2009 8:08 pm
Location: Argentina
Contact:

Re: Installation DB syntax error *SOLVED*

Post by felipe71ar » Fri Feb 18, 2011 3:54 pm

It work for me !! Thanks !!
Joomla in localhost (Win 2003) with XAMPP 1.74

teddyblood
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Feb 22, 2011 7:38 am

Re: Installation DB syntax error *SOLVED*

Post by teddyblood » Tue Feb 22, 2011 7:42 am

It works!!!!!!!!!!!!!!!!!!!!!!!!!Thank you Drakkheim

protonate
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Wed Feb 23, 2011 5:11 am

Re: Installation DB syntax error *SOLVED*

Post by protonate » Wed Feb 23, 2011 5:26 am

Drakkheim's attached file worked for me. Thank you very much.

User avatar
dpacadmin
Joomla! Champion
Joomla! Champion
Posts: 6029
Joined: Sat Aug 16, 2008 1:46 pm
Location: the Bat Cave
Contact:

Re: Installation DB syntax error *SOLVED*

Post by dpacadmin » Wed Feb 23, 2011 3:07 pm

As far as I know Joomla 1.5 is not compatible with MySQL 6.
http://www.joomla.org/technical-requirements.html

Glowysoe
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Feb 22, 2011 1:54 am

Re: Installation DB syntax error *SOLVED*

Post by Glowysoe » Sun Feb 27, 2011 6:27 am

the problem many are having is that they replaced: TYPE=MyISAM CHARACTER SET='utf-8'
with: textCHARACTER SET='utf8'

This was due to the post describing that solution missing a space between the words text and CHARACTER. the word "text" was not meant to be part of the replacement string.

the proper replacement text is just: CHARACTER SET='utf-8'

rajup
Joomla! Apprentice
Joomla! Apprentice
Posts: 31
Joined: Thu Aug 20, 2009 9:24 am

Re: Installation DB syntax error *SOLVED*

Post by rajup » Sat Mar 12, 2011 1:56 am

wow that worked for me as well....replace TYPE=MyISAM by ENGINE=MyISAM :laugh:

User avatar
sachax
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sat Dec 13, 2008 9:30 pm

Re: Installation DB syntax error *SOLVED*

Post by sachax » Sun Mar 20, 2011 12:10 am

Drakkheim wrote:9Iceman's copy paste fix worked for me.

I've attached my 1.5.22 joomla install sql in a zip .
Thanks a lot, Drakkheim. I just solved this annoying problem. I owe you a coffee ;)

soledad1905
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Sun Mar 20, 2011 11:49 am

Re: Installation DB syntax error *SOLVED*

Post by soledad1905 » Sun Mar 20, 2011 12:47 pm

Wampserver 2.0i + joomla 1.5.22 = DB syntax error solved :D

Yojy
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Mon Mar 21, 2011 10:13 pm

Re: Installation DB syntax error *SOLVED*

Post by Yojy » Mon Mar 21, 2011 10:31 pm

Hello,
still having a problem after I replaced both the files you attached.
can anyone help ?


LE: sorry , fixed it ! have a nice day :)

cyborg_b
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Mar 22, 2011 10:27 am

Re: Installation DB syntax error *SOLVED*

Post by cyborg_b » Tue Mar 22, 2011 10:36 am

Drakkheim's attached file worked for me (joomla 1.5.22.+wamp 2.1e)
Thanks a lot, Drakkheim!

pea8ch
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Thu Mar 24, 2011 4:04 pm

Re: Installation DB syntax error *SOLVED*

Post by pea8ch » Thu Mar 24, 2011 4:59 pm

Hi,

I've got the same problem while migrating from Joomla 1.0.13 to Joomla 1.5.22. When I try to upload and execute the SQL migration script I get this error:

Error at the line 35: ) TYPE=MyISAM AUTOjos_INCREMENT=1 ;

Query:CREATE TABLE IF NOT EXISTS `#jos_jos_jfjos_content` (
`id` int(10) unsigned NOT NULL autojos_increment,
`languagejos_id` int(11) NOT NULL default '0',
`referencejos_id` int(11) NOT NULL default '0',
`referencejos_table` varchar(100) NOT NULL default '',
`referencejos_field` varchar(100) NOT NULL default '',
`value` text NOT NULL,
`originaljos_value` varchar(255) default NULL,
`originaljos_text` text,
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
`modifiedjos_by` int(11) unsigned NOT NULL default '0',
`published` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTOjos_INCREMENT=1 ;

MySQL: 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 'autojos_increment, `languagejos_id` int(11) NOT NULL default '0', `reference' at line 2

I've spent hours searching for a solution on the web and nothing worked. I replaced "TYPE=MyISAM" by "ENGINE=MyISAM", and it didn't work (I've tried with several editors including Notepad++ and Wordpad). I've also tried just to remove "TYPE=MyISAM". I've tried ISO-8859-1 and UTF8 encodings (with UTF8 I get an iconv error, so I guess ISO-8859-1 is the right choice). I've tried to uncheck JoomFish! (the only 3rd party migrator I've installed) while creating the migration script. I've tried with XAMPP 1.5.4 (MySQL 5.0.24a) & 1.7.3 (with this server I get additionally a "Function ereg() is deprecated" error, so I guess it's useless to try again), WAMP 2.1 (MySQL 5.1.36), WAMP 2.0i (also the ereg error).

Nothing has worked! Always exactly the same error!

Some more information (I don't think it's useful but we never know...). The initial database has latin1_swedish_ci encoding, and the created one for Joomla 1.5 has utf8_general_ci encoding (all tables seem to have been successfully created but the installation fails).

Any idea what could be wrong? Hopefully an easy trick I've forgotten to try...

Thanks for your help

Philippe

User avatar
dpacadmin
Joomla! Champion
Joomla! Champion
Posts: 6029
Joined: Sat Aug 16, 2008 1:46 pm
Location: the Bat Cave
Contact:

Re: Installation DB syntax error *SOLVED*

Post by dpacadmin » Thu Mar 24, 2011 11:20 pm

This is messed up
Query:CREATE TABLE IF NOT EXISTS `#jos_jos_jfjos_content`
It looks like you did a search and replace on the dump file and put jos_ all through it. During the install of the migration file the only prefix you should use is jos_
http://docs.joomla.org/Common_Migrator_Errors

pea8ch
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Thu Mar 24, 2011 4:04 pm

Re: Installation DB syntax error *SOLVED*

Post by pea8ch » Fri Mar 25, 2011 2:58 pm

Thanks a lot!

As expected it was a stupid mistake... I hadn't filled in the "Old table prefix" field because it was left blank in the tutorial I'd read (probably a mistake). As I'd left this field blank the installer replaced automatically all "_" in the migration file by "jos_" when I clicked on "upload the script". The program probably considered a blank prefix as the prefix "_" and is configured to replace the blank prefix by the default prefix "_jos". But this is a bug, as replacing all "_" also has as a consequence to modify SQL keywords (e.g. "AUTO_INCREMENT" --> "AUTOjos_INCREMENT"), hence the error that occurred everytime. Now the problem is solved: the migration worked, after more than one day lost trying to find a solution...

Thanks again

Philippe

smallfishchen
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Sun Mar 27, 2011 1:39 am

Re: Installation DB syntax error *SOLVED*

Post by smallfishchen » Sun Mar 27, 2011 1:46 am

I also cannot get past this install issue :(


ERROR MESSAGE IS LIKE BELOW:
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 'textCHARACTER SET `utf8`' at line 29 SQL=CREATE TABLE `jos_banner` ( `bid` int(11) NOT NULL auto_increment, `cid` int(11) NOT NULL default '0', `type` varchar(30) NOT NULL default 'banner', `name` varchar(255) NOT NULL default '', `alias` varchar(255) NOT NULL default '', `imptotal` int(11) NOT NULL default '0', `impmade` int(11) NOT NULL default '0', `clicks` int(11) NOT NULL default '0', `imageurl` varchar(100) NOT NULL default '', `clickurl` varchar(200) NOT NULL default '', `date` datetime default NULL, `showBanner` tinyint(1) NOT NULL default '0', `checked_out` tinyint(1) NOT NULL default '0', `checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00', `editor` varchar(50) default NULL, `custombannercode` text, `catid` INTEGER UNSIGNED NOT NULL DEFAULT 0, `description` TEXT NOT NULL DEFAULT '', `sticky` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, `ordering` INTEGER NOT NULL DEFAULT 0, `publish_up` datetime NOT NULL default '0000-00-00 00:00:00', `publish_down` datetime NOT NULL default '0000-00-00 00:00:00', `tags` TEXT NOT NULL DEFAULT '', `params` TEXT NOT NULL DEFAULT '', PRIMARY KEY (`bid`), KEY `viewbanner` (`showBanner`), INDEX `idx_banner_catid`(`catid`) ) textCHARACTER SET `utf8`
Please read the forum rules regarding the use of signatures: http://forum.joomla.org/viewtopic.php?f=8&t=65

akhilpr
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Mar 29, 2011 5:57 pm

Re: Installation DB syntax error *SOLVED*

Post by akhilpr » Tue Mar 29, 2011 6:10 pm

thaxz it workz fine now :)

jflundy
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Thu Mar 31, 2011 9:10 am

Re: Installation DB syntax error *SOLVED*

Post by jflundy » Fri Apr 01, 2011 6:51 am

9Iceman wrote:Try This! It worked for me.

Open "joomla.sql" - from installation/sql/mysql
copy the this text: TYPE=MyISAM CHARACTER SET `utf8`;
with the "joomla.sql" file open got to EDIT- REPLACE and paste the text in to "FIND WHAT"
Then copy this textCHARACTER SET `utf8`; into REPLACE WITH
Hit REPLACE ALL - save the file - and your done.
Close everything and start over, it should work.
PM me if you need further help.
The above is wrong.

The replace should be: CHARACTER SET `utf8`;

samuel303
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Tue Apr 05, 2011 4:19 pm

Re: Installation DB syntax error *SOLVED*

Post by samuel303 » Wed Apr 06, 2011 3:42 pm

soledad1905 wrote:Wampserver 2.0i + joomla 1.5.22 = DB syntax error solved :D
Totally agree. Wampserver 2.0i + joomla 1.5.22 = Database syntax error solved!


Locked

Return to “Installation 1.5”