[Solved] SQL Error in Control Panel in 2.0.1

[LTJ]

Hello to all.
I have exactly the same Problem in the Control Panel with 1&1 as hoster:
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` ...

I am running
Joomla 1.5.9
JoomFish 2.0.2 (Upgraded from 2.0 RC2)
PHP 5.2.8
SQL Database Version: 4.0.27-max-log
Web Server: Apache
Memory Limit 40M

When checking the SQL DB the xxx_dbcache DB table is not present at all.

I am wondering if the table was not created due to the upgrade install from the RC Version (on PHP4).
It would be great if the people having this issue could check if the DB table was created during install or if I am the only one missing this database table.
Nevertheless - JoomFish works for me in a DE/EN setup without errors so far - except the Control Panel error.

Thanks for investigation/feedback...
Joe

[lucbonnaerens]

Well indeed it is version 4.
An upgrade is due in march.
Would this then solve the jfishplus intallation error ?

kindly, niels.

[infograf768]

I got a 500 when accessing Control Panel (2.02)
JDatabaseMySQL::query: 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 'where key_name = 'jfContent'' at line 1 SQL=show index from jos_jf_content where key_name = 'jfContent'


•PHP exécuté sur•: Darwin localhost 8.11.0 Darwin Kernel Version 8.11.0: Wed Oct 10 18:26:00 PDT 2007; root:xnu-792.24.17~1/RELEASE_PPC Power Macintosh
•Version de la base de données•: 4.1.15-standard
•Collation de la base de données (dépend du jeu de caractères)•: utf8_general_ci
•Version de PHP•: 5.2.4
•Serveur Web•: Apache/1.3.41 (Darwin) PHP/5.2.4
•Du serveur Web à l'interface PHP•: apache
•Version de Joomla!•: Joomla! 1.5.9 Production/Stable [ Vatani ] 9-January-2009 23:00 GMT
•Navigateur•: Mozilla/5.0 (Macintosh; U; PPC Mac OS X 10.4; en-US; rv:1.9.0.5) Gecko/2008120121 Firefox/3.0.5

[Geraint]

MySQL 4 doesn't appear to have the helpful ability to search for the existence of specific keys

Look in administrator/components/com_joomfish/helpers/controllerHelper.php at line 109.

You'll see we are testing the existence of some keys before adding them. If you manually add these keys you can comment out this block of code.

Geraint

p.s. I'll add a workaround for the next release.
p.p.s MySQL AB stopped active support of Mysql 4.1 on 2006-12-31 - you should really get your hosting company to upgrade!

[Geraint]

change the code to read

Code: Select all

$sql = "show index from #__jf_content";// where key_name = 'jfContent'";
$db->setQuery($sql);
$data = $db->loadObjectList("Key_name");
if (!isset($data['jfContent'])){


[infograf768]

Hack works fine. Thanks.

We have now

Code: Select all

function _checkDBStructure (){

      $db = & JFactory::getDBO();
      $sql = "show index from #__jf_content"; //where key_name = 'jfContent'";
      $db->setQuery($sql);
      $data = $db->loadObjectList("Key_name");
      if (!isset($data['jfContent'])){
         $sql = "ALTER TABLE `#__jf_content` ADD INDEX `combo` ( `reference_id` , `reference_field` , `reference_table` )" ;
etc.

That version of Myql is not at any of my hosts btw but on my Mac per default. (older Mac...)

[dino]

The hack doesn't work here. I'm still getting the SQL Error

[infograf768]

The hack doesn't work here. I'm still getting the SQL Error

It is not the same error.

[dino]

Then why did you post it in this thread when your problem don't have anything to do with it?
When there are two problems, you have to open a new thread.

@Mods: Is it possible to move the appropriate posts into a new thread? Thanks in advance!

[Geraint]

Dino

Sorry - I thought you'd already fixed this - you can remove the "collate .." part of the SQL statement as you mentioned earlier and this will cause no problems to the functionality.

If you need to you can unzip the package - change the offending query - re-zip the files and install.

Geraint