Some Mambo users are reporting the following error message, "DB function failed with error number 1054"
It looks something like this:
" DB function failed with error number 1054 Unknown column 'c.access' in
'on clause' SQL=SELECT c.*, g.name AS groupname, cc.name, v.name AS author
FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN
mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS v ON v.id =
c.created_by WHERE c.state = -1 AND c.catid=cc.id AND cc.section=s.id AND
s.scope='content' ORDER BY c.catid, c.ordering LIMIT 0,10"
This error is occurring after the server has been upgraded to use MySQL 5.
Mambo has been written for PHP/MySQL4. When MySQL5 was released there were changes to the way MySQL does database optimisation. (See more information on the MySQL site here.) This means that JOIN conditions must be preceded by the table name that the JOIN refers to. This was not a requirement in MySQL4 and as a result you will get "UNKNOWN COLUMN" errors.
Workaround
Locate the file called admin.content.php, open it. Move the content AS c to the end, as shown below.
On line 201 replace:
. "\n FROM , #__content AS c, #__categories AS cc, #__sections AS s"
With:
. "\n FROM #__categories AS cc, #__sections AS s, #__content AS c "
On line 312 replace:
. "\n FROM , #__content AS c, #__categories AS cc, #__sections AS s"
With:
. "\n FROM #__categories AS cc, #__sections AS s, #__content AS c "
Change MySQL Mode
1. Replace in my.ini the current sql-mode line with sql-mode="MYSQL40"
2. Start MySQL with the option --sql-mode="MYSQL40"
3. Or execute the following SQL query in phpMyAdmin (or whatever database manager you use):
SET GLOBAL sql_mode = 'NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE';
See http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for more information.
Although Mambo is fixing this in the next release there are still a great many 3rd party extensions that are not yet compatible with MySQL5. For this reason, I recommend that Mambo users change the MySQL mode for compatibility with MySQL4 until all the software has caught up.
If you enjoyed this post, make sure you subscribe to my RSS feed!
