By niosop on

Hello,

I've got a possible workaround for those who don't have LOCK TABLES permissions using MySQL. I figure putting it in this section would make it more likely to be noticed by those who can offer suggestions. It's at:

http://drupal.org/node/1190#comment-112437

Could someone more knowledgable than me let me know if this is MySQL 3 compatible and if there's any potential problems that need to be addressed.

Thank you,
Niosop

[fixed link - Heine]

Comments

plumbley’s picture

plumbley commented

I also wanted to get rid of LOCK TABLES in MySQL db_next_id(), even if just for aesthetic reasons :-)

This seems to have been an issue for a while (e.g. http://drupal.org/node/21439), but here's my suggestion.

function db_next_id($name) {
  $name = db_prefix_tables($name);
  db_query("INSERT IGNORE INTO {sequences} VALUES ('%s', %d)", $name, 0);
  db_query("UPDATE {sequences} SET id=LAST_INSERT_ID(id+1) WHERE name = '%s'", $name);
  return mysql_insert_id();
}

The important points are:

(1) INSERT IGNORE makes sure the entry exists but will only work the first time.

(2) Get MySQL to do the id increment, so its atomic.

(3) Use the arg version of LAST_INSERT_ID(arg), as suggested by menesis at http://drupal.org/node/21439#comment-28452. The version with arguments does not require an auto_increment column.

It works for me on my local site, but I'd appreciate any info about anything I may have missed...

Mark