query failures: LOCK TABLES sequences WRITE

I have the same problem with a pay host.

If anyone have a solution you are wellcome...

Drupal version # is 4.6.3.

Here is the way I solved the problem

in database.mysql.inc, replace

db_query("LOCK TABLES sequences WRITE");
$id = db_result(db_query("SELECT id FROM sequences WHERE name = '%s'", $name)) + 1;
db_query("REPLACE INTO sequences VALUES ('%s', '%d')", $name, $id);
db_query("UNLOCK TABLES");

return $id;

by

do {
    $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) ;
    $newid = $id + 1;
    db_query("UPDATE {sequences} SET id='%d' WHERE name='%s' AND id='%d'", $newid, name, $id);
} while (mysql_affected_rows() == 0) ;

return $newid;

As far as I am concerned, this is concurrent update safe.

Has anyone found a SAFE workaround for the table locking problem when hosting Drupal at GoDaddy?

As I tried to explain, those LOCK statements are there for good measure. On a low traffic site you might get away with this change, on a high traffic site, you might run into trouble.
--
Drupal services
My Drupal services

Hello all, I initially experienced this issue with the LOCK TABLES, and submitted it as a support ticket with my host, digitalspace.net.

They responded positively, and gave my database user the priviliges needed. I wanted to give them props, since they have proven themselves to me to be quite flexible, cost-effective and proficient in their service offerings. I didn't check GoDaddy vs Brinkster as far as being 'a bit more expensive' but I wouldn't hesitate to suggest that digitalspace is much less expensive for the results provided. EDIT: Checked Brinkster's prices, and they're cometitively priced, so go for whichever!

http://www.digitalspace.net/

I believe it is worthwhile to account for hosts which support resolving this issue, not so much as a marketing lip service, as a useful resource of supporting hosts.

Peace~mdc

There is/was a discussion among developers about this topic.

The last suggestion (September 27) involved using the DB's builting auto-increment mechanisms. If adopted, this could/would eventually do away with the sequences table completely.

Remembering, of course, that if a solution is devised within Drupal that solution may need to work on mysql 3, 4 and 5.

Paddy.

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

Sorry, but to my knowledge there's no fix.
--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.

For 4.7 beta 6 sites that are not busy and don't have multiples of users adding content you could try the following (it works for me - but then it is for a single user site..)

/includes/database.mysql.inc, replace db_next_id with

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' FOR UPDATE", $name)) + 1;
  db_query("REPLACE INTO {sequences} VALUES ('%s', %d)", $name, $id);
  //db_query('UNLOCK TABLES');

  return $id;
}

/includes/bootstrap.inc, replace variable_set with

function variable_set($name, $value) {
  global $conf;

  //db_lock_table('variable');
  //db_query("DELETE FROM {variable} WHERE name = '%s'", $name);
  db_query("SELECT name FROM {variable} WHERE name = '%s' FOR UPDATE", $name);
  db_query("REPLACE INTO {variable} (name, value) VALUES ('%s', '%s')", $name, serialize($value));
  if (!db_affected_rows()) {
    db_query("INSERT INTO {variable} (name, value) VALUES ('%s', '%s')", $name, serialize($value));
  }
  //db_unlock_tables();

  cache_clear_all('variables');

  $conf[$name] = $value;
}

and replace cache_set with

function cache_set($cid, $data, $expire = CACHE_PERMANENT, $headers = NULL) {
  //db_lock_table('cache');
  //db_query("UPDATE {cache} SET data = %b, created = %d, expire = %d, headers = '%s' WHERE cid = '%s'", $data, time(), $expire, $headers, $cid);
  db_query("SELECT cid FROM {cache} WHERE cid = '%s' FOR UPDATE", $cid);
  db_query("REPLACE INTO {cache} (cid, data, created, expire, headers) VALUES ('%s', %b, %d, %d, '%s')", $cid, $data, time(), $expire, $headers);
  if (!db_affected_rows()) {
    @db_query("INSERT INTO {cache} (cid, data, created, expire, headers) VALUES ('%s', %b, %d, %d, '%s')", $cid, $data, time(), $expire, $headers);
  }
  //db_unlock_tables();
}

Paddy.

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

im using 4.6...civicspace actually

the users above claim that the code works...i'm just not sure which code to use and if anyone confirmed that this worked...

GRANT LOCK TABLES ON database.* TO user@host IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

database is in your case richardblakehoodcom

user is the username you have in $db_url (sites/default/settings.php); it's the username for the database, not the first account.

password is obvious.

host is the server on which the database is located; usually localhost.

You can indeed run this query at the SQL tab.

If you keep getting the error; you need to contact your host and ask for global table lock privileges; if your host is godaddy, move to another host (or package?) or another CMS.

--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.

From the search I have just made on innodb, I can se that there are a lot of unanswered questions and unresolved problems.

With INNODB, the COMMIT is not really necessary. What would be needed is a sequence number that is auto-incremented and unique. The 'FOR UPDATE' clause would then lock a record (or sequence of records) for update - enforcing data integrity.

Life would be a whole lot simpler.

But on a negative note, Oracle bought Innobase (the company behind INNODB). If so, it remains to be seen how mySQL develops, and if it continues to develop with INNODB.

Paddy.

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

Contact your provider, they may have set up a cronjob to take away these permissions.

subscribed

This still seems to be a problem: (for a site using drupal 5.3 and MySQL 5)

Here is a recent posting:

This problem has been driving me and my client absolutely nuts!! We had a fine working site with lots of modules (Drupal 5.3, mySQL 5) and now the site is crashing twice a week with some variation of the error:

Warning: Table ' (pretty much any drupal table but usually 'system', 'watchdog' or 'cache')' was not locked with LOCK TABLES query: . . . . .

The site can be (temporarily) repaired by using the 'repair table' command in phpMyAdmin, but then the site inevitably crashes again a week or so later. (Very aggravating). I'd like to be spending as little time in phpMyAdmin as possible because this isn't my area of expertise, but drupal seems to be very unstable in its relationship to the database. I really just want to be worrying about updating content in the CMS.

My web host, 'Servage' tells me in their support ticket that:

Unfortunately newer versions of MySQL does not allow you to lock MyISAM tables. If you need transaction support kindly use the InnoDB table type which is transaction safe.

and sure enough, I can't grant 'LOCK TABLES' privileges through phpMyAdmin.

I have several other drupal sites of various versions and none of them have experienced these problems. I understand that I am running 5.3. Will these issues resolve if I upgrade to 5.7? I would upgrade to 6 but I am not sure that all of my modules have been ported over yet. This site is the only one which uses MySQL 5. My others all use MySQL 4. Could that be the problem? I have installed the DB maintenance module but that hasn't helped either.

I am kind of at my wits' end with this one. Can anyone give me any advice?