MySQL 5 support or 1.0.x

masterchief · **Posted:** Fri Dec 09, 2005 2:43 am

I've attached a version of database.php that allows for MySQL 5 support.

You will probably need to check out a copy of the latest 1.0 branch as I've already found some bugs that stopped things from working.

Let me know if you find any problems. I suggest you turn error reporting to maximum.

Things to watch out for are:

* SQL statements like:

"\nWHERE id = $row->id"

it should be

"\nWHERE id = " . (int) $row->id

* Field types unaccounted for - see the database::quoteField method

If there is enough interest I should be able to include this as a new connector file in the 1.0.5 release due out very soon. Also anyone with postgres experience should see if they can make a pg port of this file and get joomla to at least install, not to mention trying out MSSQL Express or Oracle Lite.

corex · **Posted:** Fri Dec 09, 2005 6:10 am

This is nice. Many thanks.

Beat · **Posted:** Fri Dec 09, 2005 8:40 am

masterchief wrote:

... If there is enough interest I should be able to include this as a new connector file in the 1.0.5 release due out very soon. ...

That's cool, Andrew. Thanks. One more vote here, even if there is still a lot of work in our 3pd components to do to catchup, but at least we can start it now

masterchief · **Posted:** Fri Dec 09, 2005 12:13 pm

Beat wrote:

One more vote here, even if there is still a lot of work in our 3pd components to do to catchup, but at least we can start it now

They shouldn't need much catching up. The only problem I'm finding so far is where a text field is designated NOT NULL but we don't define a default value.

The following sql changes will let you add content, sections and categories:

Code:

#
# This file is only to correct problems experienced with MySQL 5.x
#

ALTER TABLE `jos_content`
 MODIFY COLUMN `introtext` MEDIUMTEXT,
 MODIFY COLUMN `fulltext` MEDIUMTEXT,
 MODIFY COLUMN `images` TEXT,
 MODIFY COLUMN `urls` TEXT,
 MODIFY COLUMN `attribs` TEXT,
 MODIFY COLUMN `metakey` TEXT,
 MODIFY COLUMN `metadesc` TEXT;

ALTER TABLE ``jos_sections`
 MODIFY COLUMN `description` TEXT,
 MODIFY COLUMN `params` TEXT;

ALTER TABLE `jos_categories`
 MODIFY COLUMN `description` TEXT,
 MODIFY COLUMN `params` TEXT;

There are likely more changes required.

jljubich · **Posted:** Sun Jan 15, 2006 7:51 pm

Hello,

I have renamed database.mysql5.php as database.php in Joomla! 1.0.6 having following error message...

Code:

Fatal error: Call to undefined method mosSession::_setSchema() in D:\...\wwwroot\includes\database.php on line 757

What can I do to have Joomla! working with MySQL 5?

Thanks in advance,

Jorge.-

vbgamer45 · **Joined:** Thu Feb 02, 2006 2:59 am **Posts:** 5

Wondering the samething. I still have some issuses with MySQL 5 support for 1.0.x has anyone got it completely working? And if so do you have a complete list of database/code changes

zippytheone · **Joined:** Mon Feb 13, 2006 4:49 pm **Posts:** 13

I wondered about all that... although i got it working i ended up using mysqli extensions but still some components (3rd party) didnt work so went back to mysql 4!
Its safer!!!

vbgamer45 · **Joined:** Thu Feb 02, 2006 2:59 am **Posts:** 5

I think I have it working for MySQL 5 on windows.

Read this post
http://forum.joomla.org/index.php/topic ... #msg147625

Basiclly you configure MySQL not to run in strict mode and it seems Joomla works fine!

pangea33 · **Joined:** Sun Jan 15, 2006 1:01 am **Posts:** 38

vbgamer45 wrote:

Basiclly you configure MySQL not to run in strict mode and it seems Joomla works fine!

Seconded. I simply changed the MySQL my.ini file from this:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to this:
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

everything seems to be working properly, and I've been using this solution for about a week.

Apollo · **Joined:** Wed Jan 25, 2006 8:46 am **Posts:** 1071

Joomla 1.0.8 working so far under mysql5 with no changing to database.php or strict mode on local test server.

stingrey · **Posted:** Fri Mar 10, 2006 7:08 pm

Actually there is still a confirmed circumstance where Joomla 1.0.8 will not work MySQL 5 in strict mode.
http://forge.joomla.org/sf/go/artf3729?nav=1

I have added code to disable strict mode for 1.0.9, so hopefully we can get around this issue.

MadPax · **Posted:** Tue Mar 21, 2006 12:20 am

Ooooops,

My ISP just upgraded to MySQL 5.0.18-Debian_3.dotdeb.1

and I think I saw the site working for a couple of minutes and now I have the infamous:

This site is temporarily unavailable.
Please notify the System Administrator
2

Do I have to change something in the files of my 1.0.8 websites?

Thanx for any help.

MadPax · **Posted:** Tue Mar 21, 2006 12:29 am

Ahah!

Just made the change of database.php -> database.original.php.bak and database.mysql5.php -> database.php.

Now I have the same message but with a different number

This site is temporarily unavailable.
Please notify the System Administrator
1

What gives?

audienceone · **Posted:** Thu Apr 20, 2006 3:21 pm

Apollo wrote:

Joomla 1.0.8 working so far under mysql5 with no changing to database.php or strict mode on local test server.

same here... on PHP5 and Apache 2.x

normdouglas · **Posted:** Wed May 03, 2006 6:58 am

I can confirm that with a WAMP5 (MYSQL5 included), that I get the following error when trying to "Manage Events" in Joomla Events.
************ERROR START**************************
DB function failed with error number 1054
Unknown column 'a.checked_out' in 'on clause' SQL=SELECT a.*, cc.name AS category, u.name AS editor, g.name AS groupname FROM jos_events AS a, jos_categories AS cc LEFT JOIN jos_users AS u ON u.id = a.checked_out LEFT JOIN jos_groups AS g ON g.id = a.access WHERE a.catid=cc.id ORDER BY a.catid LIMIT 0,30
************ERROR END**************************

I don't really have many other issues, however I have had MANY issues trying to install OLDER Mambo components and put it down to the MYSQL version problem.

Is there or will there be a fix for this soon?

azuka · **Posted:** Sun May 21, 2006 4:08 am

Hi. I'm having exactly the same problem. As soon as I renamed database.mysql5.php to database.php, my site became 'Temprarily Unavailable.' I'm completely stumped.

Asphyx · **Joined:** Sun Aug 28, 2005 5:03 pm **Posts:** 2404

Error 2 if I'm not mistaken means it can't find SQL at all...
Error 1 usually means bad login I believe try resetting your account info in configuration.php and try again

mastertr · **Joined:** Wed Aug 16, 2006 9:07 am **Posts:** 4

i have same problem.
joomla 1.0.10
php5.2.0dev
mysql5.0.18
apache2.2.2

i try changing contents, joomla give me an 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 'AND state >= 0 ORDER BY ordering' at line 4 SQL=SELECT ordering AS value, title AS text FROM mos_content WHERE catid = AND state >= 0 ORDER BY ordering

what should i do? :'(

limestone · **Posted:** Fri Feb 09, 2007 8:44 pm

Just so that it is recorded somewhere, the answer to normdouglas's problem with com_events is a small change in admin.events.main.php.

This query is the one that fails, and appears around line 226. This is the fixed version and has had brackets added in line 227 in the FROM clause, like so-

Code:

   $database->setQuery( "SELECT a.*, cc.name AS category, u.name AS editor, g.name AS groupname"
      . "\nFROM (#__events AS a, #__categories AS cc)"
      . "\nLEFT JOIN #__users AS u ON u.id = a.checked_out"
      . "\nLEFT JOIN #__groups AS g ON g.id = a.access"
      . (count( $where ) ? "\nWHERE " . implode( ' AND ', $where ) : "")
      . "\nORDER BY a.catid"
      //. "\nAND a.state ASC"
      . "\nLIMIT $limitstart,$limit"
   );

Seifarth · **Joined:** Wed Dec 07, 2005 1:39 pm **Posts:** 2

Hi,

we have the same Problem with the DocMan

When i will manage documents i see the error:

DB function failed with error number 1054
Unknown column 'cc.name' in 'field list' SQL=SELECT a.*, cc.name AS category, u.name AS editor FROM intracms_docman AS cc, intracms_categories AS a LEFT JOIN intracms_users AS u ON u.id = a.checked_out WHERE a.catid=cc.id ORDER BY a.catid,a.id_subcategory,a.dmname ASC LIMIT 0,30

We also have a mysql 5.0.13 Database

Can we fix this also simple as the error in the event components?

Joomla! Discussion Forums

MySQL 5 support or 1.0.x

Quick reply

Who is online