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.
hotwired2
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sun Jul 29, 2007 8:05 am

Installation DB syntax error *SOLVED*

Post by hotwired2 » Sun Jul 29, 2007 8:21 am

Hello,

I get this MYSQL DB syntax error during install (v5.0.18):

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 'TYPE=MyISAM' at line 29 SQL=CREATE TABLE `xxx_banner` ( `bid` int(11) NOT NULL auto_increment, `cid` int(11) NOT NULL default '0', `type` varchar(90) NOT NULL default 'banner', `name` TEXT 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(150) 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`) ) TYPE=MyISAM


I tried this also on Joomla! 1.0.13 and get this error:


SQL=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 \'TYPE=MyISAM AUTO_INCREMENT=1\' at line 23 SQL=# $Id: joomla.sql 6072 2006-12-20 02:09:09Z robs $\n\n# Table structure for table `jos_banner`\n\nCREATE TABLE `jos_banner` (\n  `bid` int(11) NOT NULL auto_increment,\n  `cid` int(11) NOT NULL default \'0\',\n  `type` varchar(10) NOT NULL default \'banner\',\n  `name` varchar(50) NOT NULL default \'\',\n  `imptotal` int(11) NOT NULL default \'0\',\n  `impmade` int(11) NOT NULL default \'0\',\n  `clicks` int(11) NOT NULL default \'0\',\n  `imageurl` varchar(100) NOT NULL default \'\',\n  `clickurl` varchar(200) NOT NULL default \'\',\n  `date` datetime default NULL,\n  `showBanner` tinyint(1) NOT NULL default \'0\',\n  `checked_out` tinyint(1) NOT NULL default \'0\',\n  `checked_out_time` datetime NOT NULL default \'0000-00-00 00:00:00\',\n  `editor` varchar(50) default NULL,\n  `custombannercode` text,\n  PRIMARY KEY  (`bid`),\n  KEY `viewbanner` (`showBanner`)\n) TYPE=MyISAM AUTO_INCREMENT=1:


TIA for your help.

...
Last edited by hotwired2 on Sun Aug 05, 2007 5:21 pm, edited 1 time in total.

 
phuc
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sat Nov 18, 2006 11:40 am

Re: Installation DB syntax error

Post by phuc » Sun Jul 29, 2007 8:49 am

I'm also get the same error with 1.5RC.
My localhost:
Apache 2.2.4
PHP 5.2.3
MySQL 6.0 (is it the main cause this problem ?)
Last edited by phuc on Sun Jul 29, 2007 8:51 am, edited 1 time in total.

phuc
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sat Nov 18, 2006 11:40 am

Re: Installation DB syntax error

Post by phuc » Mon Jul 30, 2007 9:40 am

Finally I could install Joomla 1.5 RC into my localhost, just by modify some lines in joomla_backward.sql.
But I still wonder while reading MySQL Doc, they wrote that BLOB and TEXT columns cannot have DEFAULT values. but in joomla_backward.sql has a line "`name` TEXT NOT NULL default '' (???)

hotwired2
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sun Jul 29, 2007 8:05 am

Re: Installation DB syntax error

Post by hotwired2 » Mon Jul 30, 2007 6:35 pm

Are we the only one getting this error?  Is there any way you can post your db files?  Thanks!

...

phuc
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sat Nov 18, 2006 11:40 am

Re: Installation DB syntax error

Post by phuc » Tue Jul 31, 2007 8:50 am

may be...  :)
replace installation\sql\mysql\joomla_backward.sql with my attached file
or open joomla_backward.sql and replace all TEXT NOT NULL default '' to TEXT NOT NULL
Hope this will help you !
You do not have the required permissions to view the files attached to this post.

wolfix
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 108
Joined: Tue Dec 06, 2005 5:07 pm

Re: Installation DB syntax error

Post by wolfix » Sat Aug 04, 2007 6:02 am

Wow! your file worked...

I tried to do a 'search and replace' but it didn't work so I download your file and it worked!

Thanks!
Last edited by wolfix on Sun Aug 05, 2007 10:45 pm, edited 1 time in total.

hotwired2
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sun Jul 29, 2007 8:05 am

Re: Installation DB syntax error

Post by hotwired2 » Sun Aug 05, 2007 5:20 pm

Thanks phuc.  Unfortunately that was not the issue for me.  I tried your mod and still got an error.  So I had to debug the SQL further.  Well I got it working also by changing 'TYPE=MyISAM' to 'ENGINE = MyISAM'.  What threw me off is that my server have MySQL v5.0.18 and 'TYPE=MyISAM' should have worked fine. 

Anyway the sql file that came with the download worked fine by just changing those parameters without your modifications.

stanloco
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Fri Mar 13, 2009 5:08 am

Re: Installation DB syntax error *SOLVED*

Post by stanloco » Fri Mar 13, 2009 5:21 am

That was the best fix! It finally got me past step 4. I downloaded the file from above and then had to do a find and replace on TYPE=MyISAM to ENGINE=MyISAM just like was mentioned before. I was beating my head against the computer trying to figure it out. Thanks again for the tips.

elviselvis
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Tue Mar 31, 2009 7:04 pm

Re: Installation DB syntax error

Post by elviselvis » Tue Mar 31, 2009 7:37 pm

hotwired2 wrote:Thanks phuc.  Unfortunately that was not the issue for me.  I tried your mod and still got an error.  So I had to debug the SQL further.  Well I got it working also by changing 'TYPE=MyISAM' to 'ENGINE = MyISAM'.  What threw me off is that my server have MySQL v5.0.18 and 'TYPE=MyISAM' should have worked fine. 

Anyway the sql file that came with the download worked fine by just changing those parameters without your modifications.
Thanks, also got the same problem. Changing 'TYPE=MyISAM' to 'ENGINE = MyISAM' helped :)

sistesoft
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Mon Apr 06, 2009 5:47 pm

joomla problems with mysql6

Post by sistesoft » Mon Apr 06, 2009 6:08 pm

other way without modify the sql file to run joomla, need to install mysql5, because joomla 1.5.10 have sql errors to run with mysql6

Chrystals
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Dec 21, 2010 11:57 pm

Re: Installation DB syntax error *SOLVED*

Post by Chrystals » Wed Dec 22, 2010 1:11 am

Hello there...

This is an old thread, but I'm afraid I've exhausted all remedy's here and am still getting the 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 'TYPE=MyISAM CHARACTER 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`) ) TYPE=MyISAM CHARACTER SET `utf8`

Any suggestions on what it is I'm doing wrong?

9Iceman
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Sat Dec 25, 2010 10:05 pm

Re: Installation DB syntax error *SOLVED*

Post by 9Iceman » Sat Dec 25, 2010 10:29 pm

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.
Last edited by 9Iceman on Mon Dec 27, 2010 3:47 pm, edited 1 time in total.

shoaib_G
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Sat Mar 13, 2010 5:07 pm

Re: Installation DB syntax error *SOLVED*

Post by shoaib_G » Tue Dec 28, 2010 3:39 pm

I have done all suggestion given in this page but I am till receiving this error message please some one tell me what I should do now

ERROR MESSAGE IS AS 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`

gunit
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Dec 28, 2010 3:55 pm

Re: Installation DB syntax error *SOLVED*

Post by gunit » Tue Dec 28, 2010 5:09 pm

I also cannot get past this install issue :( and getting same message as shoaib_G

first I tried replacing TYPE with ENGINE using the original joomla_backward.sql - didnt work
then tried the file attached - didnt work
then modified that replacing TYPE with ENGINE - didnt work.

Its late now so I give up! I am using mySQL v5.5.8 (wAMP server 2.1)

Cheers.

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 » Tue Dec 28, 2010 5:16 pm

Uninstall Wampserver 2.1 and install Wampserver 2.0i then try installing Joomla.

gunit
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Dec 28, 2010 3:55 pm

Re: Installation DB syntax error *SOLVED*

Post by gunit » Tue Dec 28, 2010 10:47 pm

Thanks - using v2.0 fixes this issue.

kikette
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Thu Dec 30, 2010 7:47 pm

Re: Installation DB syntax error *SOLVED*

Post by kikette » Thu Dec 30, 2010 7:54 pm

Thanks! Installing Wampserver 2.0i was an easy and fast solution. I succeeded installing Joomla! Now I can go in the FUN part.

Drakkheim
Joomla! Apprentice
Joomla! Apprentice
Posts: 7
Joined: Fri Mar 13, 2009 4:15 pm
Contact:

Re: Installation DB syntax error *SOLVED*

Post by Drakkheim » Sat Jan 01, 2011 7:16 pm

9Iceman's copy paste fix worked for me.

I've attached my 1.5.22 joomla install sql in a zip .
You do not have the required permissions to view the files attached to this post.

Aglaea
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Sun Dec 07, 2008 10:06 pm

Re: Installation DB syntax error *SOLVED*

Post by Aglaea » Sat Jan 01, 2011 11:34 pm

When will this issue be solved by the development team? It is VERY annoying and frankly shameful! I had this issue in 1.5.20 and it's still there in 1.522 which I downloaded just now!

Will you guys (and girls?) make this prio 1 to fix?

integradona
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Jan 04, 2011 6:55 pm

Re: Installation DB syntax error *SOLVED*

Post by integradona » Tue Jan 04, 2011 11:51 pm

Hi, everyone, try at both files "joomla.sql and joomla_backward.sql" in "installation/sql/mysql" folder replace all TYPE=MyISAM for ENGINE=MyISAM and then, Save it as ANSI codification instead UTF-8 codification. For anyone that does not know how to save it as ANSI, edit the files and go to save as, and "if notepad" below of "file name" and "save with type" has codification option. So, it comes codified with UTF-8 and need to be changed to ANSI.

pizookie
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Sat Jan 08, 2011 9:39 am
Location: Irving, TX

Re: Installation DB syntax error *SOLVED*

Post by pizookie » Sat Jan 08, 2011 9:44 am

Hi, I still dont know where to find files "installation/sql/mysql" ", I started today and I am clueless,,,,,,please help me :(

User avatar
Tonie
Joomla! Master
Joomla! Master
Posts: 16584
Joined: Thu Aug 18, 2005 7:13 am

Re: Installation DB syntax error *SOLVED*

Post by Tonie » Sat Jan 08, 2011 9:48 am

If you download Joomla, and unzip it on your workstation there will be an installation directory with the mentioned sql directories. Upon installation, this directory will be removed.

integradona
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Jan 04, 2011 6:55 pm

Re: Installation DB syntax error *SOLVED*

Post by integradona » Sat Jan 08, 2011 9:58 am

It's a joomla subfolder. Have you been notified with same error?

CDCReon
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Thu Sep 02, 2010 1:47 pm

Re: Installation DB syntax error *SOLVED*

Post by CDCReon » Mon Jan 10, 2011 9:28 pm

I can't view the attachments...I'm assuming it's because I haven't posted enough in this forum. I have tried to alter the text in the SQL file, saved it and I'm getting nowhere....does anyone have this file they can send to my email address? Or does anyone know of any other way to get a hold of this file so I can finish installing Joomla?

Thanks,

Reon

CDCReon
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Thu Sep 02, 2010 1:47 pm

Re: Installation DB syntax error *SOLVED*

Post by CDCReon » Mon Jan 10, 2011 10:27 pm

Got it,

Nevermind.

Thank you!

JordiK89
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Wed Jan 12, 2011 10:11 am

Re: Installation DB syntax error *SOLVED*

Post by JordiK89 » Wed Jan 12, 2011 10:32 am

I had the same issue, I didn't get past step 4 in the installation.
I solved the problem by uninstall Wampserver 2.1 and install Wampserver 2.0i, then I installed Joomla 1.5.22. Everthing is working fine!

thank you all!

shirowkhan
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Wed Jan 12, 2011 2:23 pm

Re: Installation DB syntax error *SOLVED*

Post by shirowkhan » Thu Jan 13, 2011 2:12 pm

So i did all of the above and i do mean all of the above and i still get the error 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`


My wamp server version is 2.0i (like everyone said to install) and my joomla version is 1.5. Joomla 1.6 installs with no problems, however since no working version for virtuemart exists i have to downgrade to version 1.5. Help plz! :)

sat999
I've been banned!
Posts: 5
Joined: Thu Jan 13, 2011 2:34 pm

Re: Installation DB syntax error *SOLVED*

Post by sat999 » Thu Jan 13, 2011 3:00 pm

I also cannot get past this install issue and getting same message as shoaib_G

first I tried replacing TYPE with ENGINE using the original joomla_backward.sql - didnt work
then tried the file attached - didnt work
then modified that replacing TYPE with ENGINE - didnt work.

shirowkhan
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Wed Jan 12, 2011 2:23 pm

Re: Installation DB syntax error *SOLVED*

Post by shirowkhan » Thu Jan 13, 2011 4:35 pm

So i finally got joomla 1.5.22 installed. I uninstalled the wamp server and deleted the left over folders then redeployed wamp server and joomla once again and it worked. Talk about butt load of problems before the solution slaps you in the face and makes you look stupid.

User avatar
fredkadet
Joomla! Apprentice
Joomla! Apprentice
Posts: 18
Joined: Wed Sep 06, 2006 10:40 am

Re: Installation DB syntax error *SOLVED*

Post by fredkadet » Wed Jan 19, 2011 7:05 pm

Drakkheim's attached file worked for me! Using WAMP 2.1, Thanks!

 

Locked

Return to “Installation 1.5”