Installation DB syntax error *SOLVED*

hotwired2 · 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.

...

phuc · 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 ?)

phuc · 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 · 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 · 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 !

wolfix · 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!

hotwired2 · 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 · 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 · 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 · 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 · 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 · 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.

shoaib_G · 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 · 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.

dpacadmin · Post by **dpacadmin** » Tue Dec 28, 2010 5:16 pm

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

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

Thanks - using v2.0 fixes this issue.

kikette · 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 · 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 .

Aglaea · 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 · 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 · 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

Tonie · 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 · Post by **integradona** » Sat Jan 08, 2011 9:58 am

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

CDCReon · 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 · Post by **CDCReon** » Mon Jan 10, 2011 10:27 pm

Got it,

Nevermind.

Thank you!

JordiK89 · 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 · 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 · 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 · 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.

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

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

The Joomla! Forum™

Installation DB syntax error SOLVED

Installation DB syntax error SOLVED

Re: Installation DB syntax error

Re: Installation DB syntax error

Re: Installation DB syntax error

Re: Installation DB syntax error

Re: Installation DB syntax error

Re: Installation DB syntax error

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error

joomla problems with mysql6

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED

Re: Installation DB syntax error SOLVED