[Solved] SQL Error in Control Panel in 2.0.1

[dino]

After upgrading from RC to 2.0, I get the following SQL error at the control panel:

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 'COLLATE utf8_general_ci' at line 8 SQL=CREATE TABLE IF NOT EXISTS `jos_dbcache` ( `id` varchar ( 32 ) NOT NULL default '', `groupname` varchar ( 32 ) NOT NULL default '', `expire` datetime NOT NULL default '0000-00-00 00:00:00', `value` MEDIUMBLOB NOT NULL default '', PRIMARY KEY ( `id`,`groupname` ), KEY ( `expire`,`groupname` ) ) CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE IF NOT EXISTS `jos_dbcache` ( `id` varchar ( 32 ) NOT NULL default '', `groupname` varchar ( 32 ) NOT NULL default '', `expire` datetime NOT NULL default '0000-00-00 00:00:00', `value` MEDIUMBLOB NOT NULL default '', PRIMARY KEY ( `id`,`groupname` ), KEY ( `expire`,`groupname` ) ) CHARACTER SET utf8 COLLATE utf8_general_ci

I even tried to uninstall and reinstall Joomfish, but the error persists
Any help would be greatly appreciated.

Dino

[caro84g]

Hi,

could you give us the info asked for in the pink box (forum rules)

Regards, Carolien

[dino]

PHP 5.2.8
MySQL 4.0.27
Joomla 1.5.9
JoomFish 2.0.1
Memory Limit 40M

Server OS should be Linux and Web Server Apache, but I'm not sure. Can't see this info in my Customer Menu.

[alex]

Hi,

Have you tried the 2.0.2 patch?

Alex

[dino]

Hi Alex,

I've just tried the 2.0.2 patch, but the error is still there
Suggestions?

TIA,
Dino

[dino]

Okay, I've played a little bit with PhpMyAdmin... I can execute the SQL-Command when I remove the COLLATE statement at the end.

Code: Select all

CREATE TABLE IF NOT EXISTS `jos_dbcache` ( `id` varchar ( 32 ) NOT NULL default '', `groupname` varchar ( 32 ) NOT NULL default '', `expire` datetime NOT NULL default '0000-00-00 00:00:00', `value` MEDIUMBLOB NOT NULL default '', PRIMARY KEY ( `id`,`groupname` ), KEY ( `expire`,`groupname` ) ) CHARACTER SET utf8

This SQL-Command works, so I thinks it's an issue with the MySQL Version 4.0.27 at my hosting provider. I'm no MySQL Guru, but is it possible that this version doesn't support the COLLATE statement?

[alex]

Hi,

Yes I think 4.0.27 is critical with these statements.
I might need to check if we can do something different in the installation process. Did you tried it with removing the collate?

Alex -- checking where I can find such a MySQL server still

[dino]

Hi Alex,

you can find a such a MySQL Server at 1&1, their hotline told me it's their current version and it's not possible to update by myself... and they won't do it anyway

The installation process run without any errors, but you'll get the error afterwards in your control panel.
As I wrote before, yes, the SQL-Statement works when I remove the collate. So I don't think it will help if you do something different in the installation process, because the statement is executed everytime you open the control panel.

Servus
Dino

[lucbonnaerens]

Hi Gents,

I get :
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 'COLLATE utf8_general_ci' at line 8 SQL=CREATE TABLE IF NOT EXISTS `jos_dbcache` ( `id` varchar ( 32 ) NOT NULL default '', `groupname` varchar ( 32 ) NOT NULL default '', `expire` datetime NOT NULL default '0000-00-00 00:00:00', `value` MEDIUMBLOB NOT NULL default '', PRIMARY KEY ( `id`,`groupname` ), KEY ( `expire`,`groupname` ) ) CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE IF NOT EXISTS `jos_dbcache` ( `id` varchar ( 32 ) NOT NULL default '', `groupname` varchar ( 32 ) NOT NULL default '', `expire` datetime NOT NULL default '0000-00-00 00:00:00', `value` MEDIUMBLOB NOT NULL default '', PRIMARY KEY ( `id`,`groupname` ), KEY ( `expire`,`groupname` ) ) CHARACTER SET utf8 COLLATE utf8_general_ci

is there a solution for this issue yet ?

groetjes, Niels.

[dino]

Hi Niels,

what is your MySQL Server Version?

cu,
Dino