Joomla database issue, broken MySQL tables?

[AndyGaskell]

Hi All

I've been working on an issue all morning and am kind of stuck.

A server ran out of disk space last night and that seems to have broken some Joomla tables. I've cleared out space now, but have an error, I guess as a result.

I get a 500 error on the home page, which is an "Articles » Category List" menu type. When I switch on debug in the Joomla admin I get...

Code: Select all

Errors
    Got error -1 from storage engine SQL=SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.ch

...this is an abridged version, full error is below.

The Apcahe log give only one message re the site...

Code: Select all

[Thu Nov 27 12:30:22 2014] [error] [client 86.166.83.89] PHP Warning:  Invalid argument supplied for foreach() in /var/www/sites/site_dir/components/com_content/models/articles.php on line 548

I've tried...

• MySQL check tables
  MySQL repair tables
  MySQL analyze tables
  Joomla admin -> Extension Manager -> Database -> "Fix" button

I've done a fair bit of googling and tried some DB related things with no luck.

To me it looks like the MySQL table(s) are in some way corrupt or broken.

I'm a pretty experienced Joomla user but probably know less about the guts of MySQL generally.

Any ideas folks?

Thanks

Andy


Full error from Joomla! Debug Console...

Code: Select all

Joomla! Debug Console
Errors

    Got error -1 from storage engine SQL=SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = '0000-00-00 00:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.language, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published FROM hug6x_content AS a LEFT JOIN hug6x_content_frontpage AS fp ON fp.content_id = a.id LEFT JOIN hug6x_categories AS c ON c.id = a.catid LEFT JOIN hug6x_users AS ua ON ua.id = a.created_by LEFT JOIN hug6x_users AS uam ON uam.id = a.modified_by LEFT JOIN hug6x_categories as parent ON parent.id = c.parent_id LEFT JOIN hug6x_content_rating AS v ON a.id = v.content_id LEFT OUTER JOIN (SELECT cat.id as id FROM hug6x_categories AS cat JOIN hug6x_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND a.catid = 57 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-11-27 12:25:48') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-11-27 12:25:48') ORDER BY c.lft, CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END , a.created LIMIT 0, 50
    Call stack
    #	Function	Location
    1	JApplicationCms->execute()	JROOT/index.php:40
    2	JApplicationSite->doExecute()	JROOT/libraries/cms/application/cms.php:251
    3	JApplicationSite->dispatch()	JROOT/libraries/cms/application/site.php:237
    4	JComponentHelper::renderComponent()	JROOT/libraries/cms/application/site.php:191
    5	JComponentHelper::executeComponent()	JROOT/libraries/cms/component/helper.php:332
    6	require_once()	JROOT/libraries/cms/component/helper.php:352
    7	JControllerLegacy->execute()	JROOT/components/com_content/content.php:16
    8	ContentController->display()	JROOT/libraries/legacy/controller/legacy.php:730
    9	JControllerLegacy->display()	JROOT/components/com_content/controller.php:79
    10	ContentViewCategory->display()	JROOT/libraries/legacy/controller/legacy.php:693
    11	JViewCategory->commonCategoryDisplay()	JROOT/components/com_content/views/category/view.html.php:72
    12	JError::raiseError()	JROOT/libraries/legacy/view/category.php:117
    13	JError::raise()	JROOT/libraries/legacy/error/error.php:253

Version Info

Code: Select all

    
Database Version 	5.5.37-0ubuntu0.12.04.1
Database Collation 	utf8_general_ci
PHP Version 	5.3.10-1ubuntu3.11
Web Server 	Apache/2.2.22 (Ubuntu)
WebServer to PHP Interface 	apache2handler
Joomla! Version 	Joomla! 3.3.6 Stable [ Ember ] 01-October-2014 02:00 GMT
Joomla! Platform Version 	Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT     

[Vlad17]

As a guess, once you've run out of disc space one day and suspect MySQL, could it be that the server was ovefilled witn MySQL binary logs?

[AndyGaskell]

Hi Vlad17

Thanks for the pointers. Just to better explain, the disk was full and MySQL hung, then I cleared off some data and it mostly works ok. So it's weird, as a menu item that is a single article works, but the category view doesn't.

I noticed some other random issues on the server, on other Joomla sites.

MySQL binary logs sounded like a good thing to investigate, so I read up a little, then tried...

Code: Select all

mysqladmin -u root -p flush-logs

...but that didn't work.

Any ideas or pointers?

[Vlad17]

Hi Andi,

I am not a MySQL guru. I am running CentOS 6 for a while and I also had that problem with disc space. I've seen my tremendous MySQL logs in /var/lib/mysql. The files look like mysql-bin.000001. I removed them manually and disabled logging in my.cnf by commenting out log-bin=mysql-bin, since I do not have DB replication.

I have seen also in your info that you are running php as Apache module. Have you cleaned /cache folder in the SSH terminal? Can you please check what is there and get back.

[AndyGaskell]

Hi Vlad17

Thanks for your help and thoughts.

In "/var/lib/mysql" the files I've got are...

Code: Select all

-rw-r--r--  1 mysql mysql    0 May  2  2014 debian-5.5.flag
-rw-rw----  1 mysql mysql 1.4G Dec  2 09:44 ibdata1
-rw-rw----  1 mysql mysql 5.0M Dec  2 09:44 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Dec  2 09:22 ib_logfile1
-rw-rw----  1 mysql mysql    6 May  2  2014 mysql_upgrade_info

...plus all the database directories.

I searched for files "mysql-bin*" and didn't find any.

I guess there will be a few diferences between my Ubuntu 12.04 and your CentOS 6.

Although I'm using php as Apache module I don't have apache caching set-up, was that what you meant?

From the "/var/lib/mysql" directory listing output above, I was curious what ibdata1, ib_logfile0 and ib_logfile1 were.

I googled ibdata1 and got...
http://www.percona.com/blog/2013/08/20/ ... -in-mysql/
http://stackoverflow.com/questions/3456 ... e-in-mysql

I googled ib_logfile0 and got...
http://stackoverflow.com/questions/1638 ... ile0-files
http://dba.stackexchange.com/questions/ ... ile0-files


Then I realised I'd not actually checked the MySQL error log, which I should have done at the start, so at /var/log/mysql/error.log I saw...

Code: Select all

141202 10:09:36 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_9bd_1.MYI'; try to repair it
141202 10:09:36 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.37/storage/myisam/mi_write.c:226
141202 10:09:36 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_9bd_1.MYI'; try to repair it
141202 10:09:36 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.37/storage/myisam/mi_write.c:226

So I searched for "#sql_9bd*" and didn't find anything.

It all seemed odd, so I had a look at disk space again...

Code: Select all

df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda        47G   28G   17G  63% /
devtmpfs       1009M  4.0K 1009M   1% /dev
none            202M  172K  202M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none           1009M     0 1009M   0% /run/shm
overflow        1.0M     0  1.0M   0% /tmp

It seemed odd that /tmp/ was just a tiny partition. I check and none of my other Ubuntu servers are set-up this way.

Them I tried 2 things, in retrospect this was not such a good idea as I tried them both at the same time, so not sure which one fixed the issue.

In "/etc/mysql/my.cnf" I changed...
tmpdir = /tmp
...to...
tmpdir = /var/tmp

And I also moved...
/var/lib/mysql/ib_logfile0
...and...
/var/lib/mysql/ib_logfile1
...into a backup directory I created.

Then I restarted MySQL.

And that fixed the issues.

The two files...
/var/lib/mysql/ib_logfile0
...and...
/var/lib/mysql/ib_logfile1
...were recreated when restarted MySQL.

In practice this is not really a Joomla issue, but a MySQL thing. I though the notes might be handy for future Joomla folk who hit this though.

Any Queries or thoughts re this, please share them here.

Andy