How do I fix a corrupt Joomla jos_session table

I manage a few Joomla websites on a underpowered virtual servers and they occasionally crash under load. Some of the time when they crash, the jos_session table becomes corrupt, yielding this error:

jtablesession::Store Failed
DB function failed with error number 1146
Table 'dbname.jos_session' doesn't exist SQL=INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`client_id` ) VALUES ( 'rAxRWo70lP8aQOwtokou4IRsMZ','2444128272','','0','1','0' )

This is because jos_session is used to manage user sessions, so it is written to frequently. The most common cause of a corrupt table is a failed write. When the server runs out of resources, it is usually in the middle of a write, thus the corrupt jos_session table. Here’s a way to fix it in Joomla 1.5:

DROP TABLE IF EXISTS `jos_session`;
CREATE TABLE IF NOT EXISTS `jos_session` (
`username` varchar(150) default '',
`time` varchar(14) default '',
`session_id` varchar(200) NOT NULL default '0',
`guest` tinyint(4) default '1',
`userid` int(11) default '0',
`usertype` varchar(50) default '',
`gid` tinyint(3) unsigned NOT NULL default '0',
`client_id` tinyint(3) unsigned NOT NULL default '0',
`data` longtext,
PRIMARY KEY (`session_id`(64)),
KEY `whosonline` (`guest`,`usertype`),
KEY `userid` (`userid`),
KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Because jos_session is storing temporary data, dropping it and recreating the structure is a simple fix. Had the table had data that we needed, we’d need to go more in depth for recovery.

How do I do a JOIN in an UPDATE query with MySQL

As a follow up to my June 22nd post on doing a JOIN in an UPDATE with SQL Server, I just needed to do the same in MySQL. Here’s the syntax I used:

UPDATE `site_locations` 
LEFT JOIN `locations` ON `site_locations`.`location_id` = `locations`.`id`
SET 
`site_locations`.`location_phone` = `locations`.`adwizard_phone`, 
`site_locations`.`location_phone_ext` = `locations`.`adwizard_phone_ext` 
WHERE `site_locations`.`location_phone` IS NULL

As you can see, the order of things is a little different in MySQL vs SQL Server.

How Do I Recover a Lost Joomla Administrator Password

Lost passwords are the bane of my existence. Luckily, lots of places offer password recovery options. A Joomla 1.0.0 site for a long time client of mine wasn’t one of those sites. Given that the password is hashed, there’s no real way to recover it, but you can reset it; so, here’s how I reset my password:

1. Try to guess password several times, no luck
2. Look up username:

   USE `joomla`;
   SELECT `id`, `username` FROM `tbl_users`;

3. Try a few more times to guess the password, using the correct username (admin by default, not the case for me)
4. Look up the hashed password:

   USE `joomla`;
   SELECT `id`, `username`, `password` FROM `tbl_users`;

5. Look up what type of hash Joomla uses. It looked like MD5 and it was.
6. Generate a new password:

   USE `joomla`;
   UPDATE `tbl_users` SET `password` = MD5('new_password_here') WHERE `id` = 'selected_id_here';

7. You may now log in with your new_password_here

Escaping Single Quotes in MS SQL Server (T-SQL)

Escaping single quotes (‘) in Transact-SQL is done by replacing the single quote with two single quotes. So:

SELECT 'I went to Kinko''s'

Returns “I went to Kinko’s”

The only case where two consecutive single quotes don’t return a single quote is when the first one also starts the string.  In this case, if only two single quotes are present, the second one ends the string and the return value is the empty string.  SQL Blog Casts has a good example of this.

Doing the same in MySQL

Escaping a single quote in MySQL is done by adding a backslash before it (\’)

SELECT 'I went to Kinko\'s'