By 1360 on

After installing i'm getting this error on every page...

Warning: Access denied for user: '*****@%' to database '*****' query: LOCK TABLES cache WRITE in /home/content/w/e/e/*****/html/site/cms/includes/database.mysql.inc on line 108

any suggestions on a fix?

:)

Comments

1360’s picture

1360 commented

line 108 is:

trigger_error(check_plain(mysql_error($active_db) ."\nquery: ". $query), E_USER_WARNING);

Pedro Buccellato’s picture

Pedro Buccellato commented

I have this problem *only* when the mysql server and the apache server live on different machines. The exact same code runs fine on same machine set ups....

Does this make sense? Using a persistent connection doesn't seem to help.

Ciprian.Samoila’s picture

Ciprian.Samoila commented

is it working if you replace localhost with 127.0.0.1 in settings.php ?

give it a try... it usually helps with some servers...

best,
Chip

1360’s picture

1360 commented

--- Z:\database.mysql.inc\database.mysql.inc.old Thu Apr 14 18:50:24 2005 UTC
+++ Z:\database.mysql.inc\database.mysql.inc Wed Apr 27 19:06:28 2005 UTC
@@ -148,10 +148,8 @@
*/
function db_next_id($name) {
$name = db_prefix_tables($name);
- db_query('LOCK TABLES {sequences} WRITE');
- $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) + 1;
+ $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s' FOR UPDATE", $name)) + 1;
db_query("REPLACE INTO {sequences} VALUES ('%s', %d)", $name, $id);
- db_query('UNLOCK TABLES');

return $id;
}

--------------

how do i apply this patch?

tostinni’s picture

tostinni commented

I think I'm the author of this patch but I wouldn't recommend it.
As discussed in the thread where you found it, the "FOR UPDATE" command only work on InnoDB tables.
So it won't hurt if you're not using InnoDB but it won't do anything regarding concurrency (which is the problem adressed here).
So you can just comment out these two lines :

db_query('LOCK TABLES {sequences} WRITE');
db_query('UNLOCK TABLES');

And you get pretty much the same result. It would work but it won't handle concurrency.

Good luck

killes@www.drop.org’s picture

killes@www.drop.org commented

is to get your hoster to grant your database user the LOCK TABLE permission. Everything else is just a workaround.
--
Drupal services
My Drupal services

--
Drupal services
My Drupal services

Pedro Buccellato’s picture

Pedro Buccellato commented

That makes perfect sense. Thanks.

lotherius’s picture

lotherius commented

How bad is it to not have this working? My site seems to be functioning correctly, but I am getting the LOCK TABLE error at random times...

I ask because it is not likely my provider will give any special requests.

paddy_deburca’s picture

paddy_deburca commented

This LOCK TABLES issues is one of my pet peeves against Drupal. I maybe doing the Drupal developers an injustice, but I think that they see this as a hosting issue rather than a Drupal issue .... if you dont have LOCK TABLES permission go and change hosting company.

LOCK TABLES prevents two authous creating nodes of the same sequence ID. Two nodes of the same sequence ID would end up the data corruption.

By taking out LOCK TABLES you remove this safe-guard and this is highly un-recommended.

But, on the other hand, if your site is a low traffic site where the risk of two authors creating content at exactly the same time to cause Drupal to assign the exact same sequence ID is so low that the risk can be ignored - then these lines offer little or no added value.

I personally remove these lines. I prefer to remain with my hosting provider and fix the corruption issues as they arrive - after a couple of years and a half a dozen (low traffic) drupal sites I have not yet come across one corruption issue.

Paddy.

http://deburca.org, and http://amadain.net

Paddy.

http://deburca.org