Joomla! Forum - community, help and support

Joomla! Community, help and support.
https://forum.joomla.org/

Joomla 1.5.17 and earlier and MySQL 5.5.x

https://forum.joomla.org/viewtopic.php?f=199&t=513155

Page 1 of 1

Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun May 02, 2010 5:24 pm
by vsukhomlinov
Just found an issue with out of the box installation on server with MySQL 5.5. Installation fails with 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`

Also, it's easy to fix by removing 'type=MyISAM' from joomla.sql

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun May 02, 2010 8:43 pm
by RussW
As far as I can determine, MySQL 5.5.x has depreciated the table_type_option construct of TYPE and has replaced it with " ENGINE = " ;
The following constructs are deprecated and will be removed in a future
MySQL release. Where alternatives are shown, applications should be
updated to use them.

* The table_type system variable (use storage_engine).

The TYPE table option to specify the storage engine for
CREATE TABLE or ALTER TABLE (use ENGINE).

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Tue May 04, 2010 4:52 pm
by vsukhomlinov
Yes, MySQL 5.1 allowed both ENGINE & TYPE for same purpose. With 5.4 it became deprecated, and removed in 5.5. So, as earlier versions supports 'ENGINE' its either should be changed to that or completely removed, using MySQL default settings. I'm also afraid about compatibility with MariaDB branch... Were there any benchmarks demonstrating MyISAM is better than InnoDB?

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Tue May 04, 2010 8:35 pm
by RussW
innoDB is now the default engine of choice for reasons of improved performance and improved feature-set (remember MySQL now has the might of Oracle of behind it and Oracle's "way of doing" things is a slightly different mind-set)

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun Oct 24, 2010 6:32 pm
by highstall
Hi.... Brand new here to joomla. I been trying to get Joomla 15 running on freebsd with mysql 5.5.x
Reading this post.....and a bit confused I see

Quote; Also, it's easy to fix by removing 'type=MyISAM' from joomla.sql

Is this file at domain/installation/sql/mysql/joomla.sql

if so I see 'type=MyISAM all over that file

Do I need to remove every instance?

Can you show me an example?

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun Oct 24, 2010 6:53 pm
by vsukhomlinov
Hi, thats correct. You need to remove every instance of 'type=MyISAM' in this file.

lines like ") TYPE=MyISAM CHARACTER SET `utf8`;" should become:
") CHARACTER SET `utf8`;"

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun Oct 24, 2010 8:46 pm
by highstall
Thank YOU for your help

Works now

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun Oct 31, 2010 3:24 am
by cybersalt
"ENGINE=myisam" also works instead of "TYPE=myisam".

BTW, up-to and including Joomla version 1.5.21 will not install on mysql55 without the change.

Is anyone working on fixing this?

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sat Dec 25, 2010 10:26 pm
by 9Iceman
Thank you,Thank you, Thank you. It worked perfectly!
If anyone has this problem. Trust me this is the fix. I tried all the others and this 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.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun Dec 26, 2010 5:37 am
by kachan
Looks like many other modules has a similar issue. I suggest to stay away from MySQL 5.5 for now.

-=Terence=-

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sun Dec 26, 2010 6:30 am
by cybersalt
kachan wrote:Looks like many other modules has a similar issue. I suggest to stay away from MySQL 5.5 for now.

-=Terence=-
I was thinking about staying away too, but I was playing around with Joomla 1.6rc1 and found that it is compatible with mysql5.5 - having "ENGINE=" instead of "TYPE=".

So I'm going to stick with mysql5.5 and finding and replacing in the SQL files for extensions that create tables.

I've looked more into this issue and "TYPE=" has been deprecated since Mysql 4.0, was still supported for backward compatibility in MySQL 5.1, and has produced a warning since My SQL 5.1.8.

In fact, in the reference manual for MySQL 5.1.8 it says:
TYPE vs ENGINE . In order not to break legacy applications, support for TYPE = engine_name —deprecated since MySQL 4.0—has been restored, but now generates a warning.

Beginning with MySQL 5.5, TYPE = engine_name will no longer be available and will produce a syntax error.

You should not use TYPE in any new applications, and you should immediately begin conversion of existing applications to use the ENGINE = engine_name syntax instead.
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-8.html

This is an issue not going away.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Fri Jan 21, 2011 4:58 am
by amilaudana
Yes,

use
For joomla 1.5 => mysql 5.1

For joomla 1.6 => mysql 5.5

if you are going to use mysql 5.5 with joomla 1.5 you will face the sql problems. you can modify mysql installation sql files, but what about thousands of extensions. it is better to use as above.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Fri Jan 21, 2011 5:43 am
by cybersalt
MySQL55 is now stable so I've just resigned myself to editing the SQL instructions in extensions I want to install. This way I can take advantage of the incredible speed increase of 5.5.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Thu Mar 24, 2011 5:07 pm
by pea8ch
Hi,

I've got a similar 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

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Thu Mar 24, 2011 10:35 pm
by RussW
@pea8ch
I wouldn't say your problem is the same, your message is completely different, in future pleae open your own thread for a problem, not hijak someone elses.

It looks like your MySQL server versions are different and there is an unsupported statement, either the "NOT NULL" or mor elikely the "default '0'" statement.

Try exporting the initial database in "compatibility" mode

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Mon Mar 28, 2011 1:41 pm
by pea8ch
Sorry for that. I thought the problem was similar and it was better to post on an existing thread that creating a new one (as people tend to always create new threads for problems that have been already solved).

Anyway the problem is solved. It wasn't a compatibility problem as I thought. It was a stupid mistake that took me a long time to find out: I hadn't filled in the "old table prefix" field so the installation program replaced all "_" in the script by "jos_".

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Tue Jul 12, 2011 12:48 pm
by Reintje111
my demo site works now, but i get an error when i upload Easybook reloaded (extension)

Code: Select all

JInstaller::install: SQL Error. DB function failed with error number 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 'TYPE=MyISAM' at line 20 SQL=CREATE TABLE IF NOT EXISTS `jos_easybook` ( `id` int(10) NOT NULL auto_increment, `gbip` varchar(15) NOT NULL default '', `gbname` varchar(40) NOT NULL default '', `gbmail` varchar(60) default NULL, `gbmailshow` tinyint(1) NOT NULL default '0', `gbloca` varchar(50) default NULL, `gbpage` varchar(150) default NULL, `gbvote` int(10) default NULL, `gbtext` text NOT NULL, `gbdate` datetime default NULL, `gbcomment` text, `published` tinyint(1) NOT NULL default '0', `gbicq` varchar(20) default NULL, `gbaim` varchar(50) default NULL, `gbmsn` varchar(50) default NULL, `gbyah` varchar(50) default NULL, `gbskype` varchar(50) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM;
SQL = 
CREATE TABLE IF NOT EXISTS  `jos_easybook` (
          `id` int(10) NOT NULL auto_increment,
          `gbip` varchar(15) NOT NULL default '',
          `gbname` varchar(40) NOT NULL default '',
          `gbmail` varchar(60) default NULL,
          `gbmailshow` tinyint(1) NOT NULL default '0',
          `gbloca` varchar(50) default NULL,
          `gbpage` varchar(150) default NULL,
          `gbvote` int(10) default NULL,
          `gbtext` text NOT NULL,
          `gbdate` datetime default NULL,
          `gbcomment` text,
          `published` tinyint(1) NOT NULL default '0',
          `gbicq` varchar(20) default NULL,
          `gbaim` varchar(50) default NULL,
          `gbmsn` varchar(50) default NULL,
          `gbyah` varchar(50) default NULL,
          `gbskype` varchar(50) default NULL,
          PRIMARY KEY  (`id`)
        ) TYPE=MyISAM;Component Install: SQL error or missing or unreadable SQL file. DB function failed with error number 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 'TYPE=MyISAM' at line 20 SQL=CREATE TABLE IF NOT EXISTS `jos_easybook` ( `id` int(10) NOT NULL auto_increment, `gbip` varchar(15) NOT NULL default '', `gbname` varchar(40) NOT NULL default '', `gbmail` varchar(60) default NULL, `gbmailshow` tinyint(1) NOT NULL default '0', `gbloca` varchar(50) default NULL, `gbpage` varchar(150) default NULL, `gbvote` int(10) default NULL, `gbtext` text NOT NULL, `gbdate` datetime default NULL, `gbcomment` text, `published` tinyint(1) NOT NULL default '0', `gbicq` varchar(20) default NULL, `gbaim` varchar(50) default NULL, `gbmsn` varchar(50) default NULL, `gbyah` varchar(50) default NULL, `gbskype` varchar(50) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM;
SQL = 
CREATE TABLE IF NOT EXISTS  `jos_easybook` (
          `id` int(10) NOT NULL auto_increment,
          `gbip` varchar(15) NOT NULL default '',
          `gbname` varchar(40) NOT NULL default '',
          `gbmail` varchar(60) default NULL,
          `gbmailshow` tinyint(1) NOT NULL default '0',
          `gbloca` varchar(50) default NULL,
          `gbpage` varchar(150) default NULL,
          `gbvote` int(10) default NULL,
          `gbtext` text NOT NULL,
          `gbdate` datetime default NULL,
          `gbcomment` text,
          `published` tinyint(1) NOT NULL default '0',
          `gbicq` varchar(20) default NULL,
          `gbaim` varchar(50) default NULL,
          `gbmsn` varchar(50) default NULL,
          `gbyah` varchar(50) default NULL,
          `gbskype` varchar(50) default NULL,
          PRIMARY KEY  (`id`)
        ) TYPE=MyISAM;

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Tue Jul 12, 2011 7:40 pm
by cybersalt
Reintje111,

If your site is running on mysql5.5 you need to change the sql install script of any extension that is still using "TYPE=MyISAM" to "ENGINE=MyISAM"

I see that "TYPE=MyISAM" occurs a couple of times in the error message you have posted.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Fri Dec 30, 2011 5:39 am
by lucifer19700
9Iceman wrote:Thank you,Thank you, Thank you. It worked perfectly!
If anyone has this problem. Trust me this is the fix. I tried all the others and this 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.
I have been having the same problem for my joomla for the past 4 days. I have been to every forum and people have offered different solutions, none of which worked for me. Editing joomla.sql was finally what worked for me. I had thank you for your solution, I have been doing nothing but searching for solutions for the past 4 days. Thanks so much.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Sat Mar 31, 2012 9:58 pm
by slansiaux
Hi all,

I switch from TYPE to ENGINE, and I have the same result :


JInstaller::install: Erreur SQL. DB function failed with error number 1064
Erreur de syntaxe près de 'CREATE TABLE IF NOT EXISTS `jos_easybook` ( `id` int(10) NOT NULL' à la ligne 1 SQL=CREATE TABLE IF NOT EXISTS `jos_easybook` ( `id` int(10) NOT NULL auto_increment, `gbip` varchar(15) NOT NULL default '', `gbname` varchar(40) NOT NULL default '', `gbmail` varchar(60) default NULL, `gbmailshow` tinyint(1) NOT NULL default '0', `gbloca` varchar(50) default NULL, `gbpage` varchar(150) default NULL, `gbvote` int(10) default NULL, `gbtext` text NOT NULL, `gbdate` datetime default NULL, `gbcomment` text, `published` tinyint(1) NOT NULL default '0', `gbicq` varchar(20) default NULL, `gbaim` varchar(50) default NULL, `gbmsn` varchar(50) default NULL, `gbyah` varchar(50) default NULL, `gbskype` varchar(50) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;
SQL =
CREATE TABLE IF NOT EXISTS `jos_easybook` (
`id` int(10) NOT NULL auto_increment,
`gbip` varchar(15) NOT NULL default '',
`gbname` varchar(40) NOT NULL default '',
`gbmail` varchar(60) default NULL,
`gbmailshow` tinyint(1) NOT NULL default '0',
`gbloca` varchar(50) default NULL,
`gbpage` varchar(150) default NULL,
`gbvote` int(10) default NULL,
`gbtext` text NOT NULL,
`gbdate` datetime default NULL,
`gbcomment` text,
`published` tinyint(1) NOT NULL default '0',
`gbicq` varchar(20) default NULL,
`gbaim` varchar(50) default NULL,
`gbmsn` varchar(50) default NULL,
`gbyah` varchar(50) default NULL,
`gbskype` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
Composant Installation: Erreur SQL ou fichier sql absent ou illisible. DB function failed with error number 1064
Erreur de syntaxe près de 'CREATE TABLE IF NOT EXISTS `jos_easybook` ( `id` int(10) NOT NULL' à la ligne 1 SQL=CREATE TABLE IF NOT EXISTS `jos_easybook` ( `id` int(10) NOT NULL auto_increment, `gbip` varchar(15) NOT NULL default '', `gbname` varchar(40) NOT NULL default '', `gbmail` varchar(60) default NULL, `gbmailshow` tinyint(1) NOT NULL default '0', `gbloca` varchar(50) default NULL, `gbpage` varchar(150) default NULL, `gbvote` int(10) default NULL, `gbtext` text NOT NULL, `gbdate` datetime default NULL, `gbcomment` text, `published` tinyint(1) NOT NULL default '0', `gbicq` varchar(20) default NULL, `gbaim` varchar(50) default NULL, `gbmsn` varchar(50) default NULL, `gbyah` varchar(50) default NULL, `gbskype` varchar(50) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;
SQL =
CREATE TABLE IF NOT EXISTS `jos_easybook` (
`id` int(10) NOT NULL auto_increment,
`gbip` varchar(15) NOT NULL default '',
`gbname` varchar(40) NOT NULL default '',
`gbmail` varchar(60) default NULL,
`gbmailshow` tinyint(1) NOT NULL default '0',
`gbloca` varchar(50) default NULL,
`gbpage` varchar(150) default NULL,
`gbvote` int(10) default NULL,
`gbtext` text NOT NULL,
`gbdate` datetime default NULL,
`gbcomment` text,
`published` tinyint(1) NOT NULL default '0',
`gbicq` varchar(20) default NULL,
`gbaim` varchar(50) default NULL,
`gbmsn` varchar(50) default NULL,
`gbyah` varchar(50) default NULL,
`gbskype` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Can you help me solve this?
Thanks in advance.

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Tue Aug 07, 2012 8:43 pm
by crypto3
Dear All,

After upgrading from MySql 5.1 to MySql 5.5 all my Joomla! sites get down and corrupted, please once check this website: www.afghanvoip.com and all other websites going the same like www.yeget.net.

Can anyone please help me sort it out here.

I have Joomla 1.5.26 and 2.5.6 installed on the sites.

Thank you
Crypto

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Tue Aug 07, 2012 9:23 pm
by crypto3
Hello,

The issue has been resolved by turning the error reporting Off in Main >> Service Configuration >> PHP Configuration Editor

Thank you
crypto3 wrote:Dear All,

After upgrading from MySql 5.1 to MySql 5.5 all my Joomla! sites get down and corrupted, please once check this website: http://www.afghanvoip.com and all other websites going the same like http://www.yeget.net.

Can anyone please help me sort it out here.

I have Joomla 1.5.26 and 2.5.6 installed on the sites.

Thank you
Crypto

Re: Joomla 1.5.17 and earlier and MySQL 5.5.x

Posted: Thu Nov 29, 2012 6:24 pm
by zeevee
crypto3 wrote:Dear All,

After upgrading from MySql 5.1 to MySql 5.5 all my Joomla! sites get down and corrupted, please once check this website: http://www.afghanvoip.com and all other websites going the same like http://www.yeget.net.

Can anyone please help me sort it out here.

I have Joomla 1.5.26 and 2.5.6 installed on the sites.

Thank you
Crypto
I see all your sites are up now. What did you do to fix it. Did turning off the error report helped?
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/