Note: The second solution presented in this post is a core change. Keep in mind that if you change the core of your Joomla website, you may need to re-apply your changes after a Joomla update. Also keep in mind that this particular core change may compromise the stability of your website (especially if your website has an above normal rate of write activities).
We are getting an increasing number of clients calling in and telling us that they are seeing the following error when adding/editing items (such as content, categories, menu items, etc…) to Joomla:
1044 Access denied for user ‘[user]’ to database ‘[database]’ SQL=LOCK TABLES `[table]` WRITE
For some clients, the problem is persistent (happens all the time), for some other clients, it is occasional. However, it is always annoying.
So, why does this problem happen?
In short, the problem happens because of the #__assets table: when you add an item to Joomla, Joomla adds a reference to that item in the #__assets table. Generally, adding an entry to the #__assets table results in having a large number of rows (in the #__assets table) being updated because of the lft and the rgt fields (we are not going to go into the technicalities here, but you can just blame the lft and the rgt fields). In order to maintain data integrity, Joomla acquires a lock on the #__assets table using the LOCK TABLES SQL statement (keep in mind that sometimes adding an entry to the #__assets table can take several seconds, so imagine what will happen if another entry is to be added at the same time without locking the table: a complete corruption of the table!)
But why should this be a problem?
You’re right. It shouldn’t be a problem. But it becomes a problem if the MySQL user that you’re using doesn’t have the necessary privileges to lock tables. In order to address this problem, then you will need to grant that user the LOCK TABLES privilege (you can do that either from the cPanel interface or from the MySQL client when logged in as root).
If you’re running in a restricted environment and you don’t have control over the database users’ privileges, then you will need to comment out the code responsible for locking the #__assets table (warning: this can lead to disastrous effects if you have more than one person working on the website). This can be done by opening the file nested.php located under the libraries/joomla/table folder and removing the following code:
// Lock the table for writing.
if (!$this->_lock())
{
return false;
}
You will then need to upload the file back. This will solve the problem, but, as stated in the beginning of this post, this is a core change so you will need to be extremely careful. Despite the easiness of this solution, you should always opt for the first solution.
If you’re seeing the above error when trying to add an item to your Joomla website, then try implementing the first solution first. If all else fails and you are becoming increasingly desperate then go for the second solution (keep in mind that the latter solution can corrupt your #__assets table, so it’s not recommended to apply it in a production environment). If you need help with the implementation, then please contact us. We are eager to help, we know our Joomla, we don’t charge much, and we are the friendliest programmers in our Solar System.