Explore better options of handling auto_increment IDs

subscribing

Hmmm. How about hook_boot?

I'll try that out. I'm also reviewing our merge process starting tomorrow. The last merge was hell, doesn't scale at all. I now ALWAYS get merge errors that I have to resolve manually. Is that the case with you? How much manual conflict resolution do you normally do using your module?

Just to clarify. All we need to do for the "hack" is to run a simple query against the active database connection before anything is inserted into the database. Technically we don't even need to use the php function mysql_query, we can use drupal's own db_query.

hook_boot might do the job. To use the earlier example:

function really_nasty_module_boot() {
  global $nasty_increment, $nasty_offset;

  db_query (
    'SET @@auto_increment_increment=%d',
    $nasty_increment 
  );

  db_query (
    'SET @@auto_increment_offset=%d',
    $nasty_offset 
  );
}

It actually works! =D

Working against our development version, CCK (not heavy though), views, our custom modules, feedapi, etc.
Just refreshing the front page and looking at the logs.

No hack and Auto Increment module disabled:

/usr/local/mysql/libexec/mysqld, Version: 5.0.67-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
090309  9:12:26       1 Connect     dev_db_user@localhost on
                      1 Init DB     dev_site_drupal
                      1 Query       SET NAMES "utf8"
                      1 Query       SELECT 1 FROM pub_access WHERE type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1
                      1 Query       SELECT u.*, s.* FROM pub_users u INNER JOIN pub_sessions s ON u.uid = s.uid WHERE s.sid = '400a5d455b05d469a0d9d32737e2411c'
                      1 Query       SELECT data, created, headers, expire, serialized FROM pub_cache WHERE cid = 'variables'
                      1 Query       SELECT data, created, headers, expire, serialized FROM pub_cache_page WHERE cid = 'http://sitedev/'
                      1 Query       SELECT name, filename, throttle FROM pub_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
                      1 Query       SELECT name, filename, throttle FROM pub_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
                      1 Query       UPDATE pub_sessions SET uid = 0, cache = 0, hostname = '127.0.0.1', session = '', timestamp = 1236604346 WHERE sid = '400a5d455b05d469a0d9d32737e2411c'
090309  9:12:27       1 Quit

Core hack. Auto increment module disabled.

090309 10:52:51      32 Connect     dev_db_user@localhost on
                     32 Init DB     dev_site_drupal
                     32 Query       SET NAMES "utf8"
                     32 Query       SET @@auto_increment_increment=10                                <==============
                     32 Query       SET @@auto_increment_offset=1                                        <==============
                     32 Query       SELECT 1 FROM pub_access WHERE type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1
                     32 Query       SELECT u.*, s.* FROM pub_users u INNER JOIN pub_sessions s ON u.uid = s.uid WHERE s.sid = '1c729684459c82c522d5dd71fe106d46'
                     32 Query       SELECT r.rid, r.name FROM pub_role r INNER JOIN pub_users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1
                     32 Query       SELECT data, created, headers, expire, serialized FROM pub_cache WHERE cid = 'variables'
                     32 Query       SELECT name, filename, throttle FROM pub_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
                     32 Query       SELECT COUNT(pid) FROM pub_url_alias
                        ...<snip>

My settings.php

...
$db_url = 'mysql://...'; //removed to protect the innocent
$db_prefix = 'pub_';

// Database Scripts - Auto Increment configuration
// We need to set these as global, otherwise they will not leave the scope of the config_init function and wont be available in our module
global $auto_increment_increment, $auto_increment_offset;
$auto_increment_increment=10;
$auto_increment_offset=1;
...

Anonymous user. Auto Increment module enabled.

Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
090309 10:12:27       1 Connect     dev_db_user@localhost on
                      1 Init DB     dev_site_drupal
                      1 Query       SET NAMES "utf8"
                      1 Query       SELECT 1 FROM pub_access WHERE type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1
                      1 Query       SELECT u.*, s.* FROM pub_users u INNER JOIN pub_sessions s ON u.uid = s.uid WHERE s.sid = '3c262167fce23cbff839730a2568e37f'
                      1 Query       SELECT r.rid, r.name FROM pub_role r INNER JOIN pub_users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1
                      1 Query       SELECT data, created, headers, expire, serialized FROM pub_cache WHERE cid = 'variables'
                      1 Query       SELECT name, filename, throttle FROM pub_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
                      1 Query       SET @@auto_increment_increment=10                     <===============
                      1 Query       SET @@auto_increment_offset=1                          <===============
                      1 Query       SELECT COUNT(pid) FROM pub_url_alias
                        ....<snip>

Logged in as Administrator. Auto Increment module enabled.

090309 10:18:24       7 Connect     dev_db_user@localhost on
                      7 Init DB     dev_site_drupal
                      7 Query       SET NAMES "utf8"
                      7 Query       SELECT 1 FROM pub_access WHERE type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1
                      7 Query       SELECT u.*, s.* FROM pub_users u INNER JOIN pub_sessions s ON u.uid = s.uid WHERE s.sid = '3c262167fce23cbff839730a2568e37f'
                      7 Query       SELECT r.rid, r.name FROM pub_role r INNER JOIN pub_users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1
                      7 Query       SELECT data, created, headers, expire, serialized FROM pub_cache WHERE cid = 'variables'
                      7 Query       SELECT name, filename, throttle FROM pub_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
                      7 Query       SET @@auto_increment_increment=10                        <================
                      7 Query       SET @@auto_increment_offset=1                             <================
                      7 Query       SELECT COUNT(pid) FROM pub_url_alias
                        ...<snip>

Logged in as Administrator. Cache cleared. Auto Increment module enabled.

090309 10:44:25      20 Connect     dev_db_user@localhost on
                     20 Init DB     dev_site_drupal
                     20 Query       SET NAMES "utf8"
                     20 Query       SELECT 1 FROM pub_access WHERE type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1
                     20 Query       SELECT u.*, s.* FROM pub_users u INNER JOIN pub_sessions s ON u.uid = s.uid WHERE s.sid = 'dadf99fb075def840ed7f646456a6ab5'
                     20 Query       SELECT r.rid, r.name FROM pub_role r INNER JOIN pub_users_roles ur ON ur.rid = r.rid WHERE ur.uid = 1
                     20 Query       SELECT data, created, headers, expire, serialized FROM pub_cache WHERE cid = 'variables'
                     20 Query       SELECT name, filename, throttle FROM pub_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
                     20 Query       SET @@auto_increment_increment=10                                <================
                     20 Query       SET @@auto_increment_offset=1                                    <================
                     20 Query       SELECT COUNT(pid) FROM pub_url_alias
                        ...<snip>

I also enabled all modules and refreshed the page. It looks like I hit the cache, so UPDATE and INSERT on the cache table got in before the SET statements, but the cache table does not have any auto_increment columns.

As you can see no auto_increment insert statement hits the database before our SET queries. Hence, based on this limited testing, it looks like we're safe.

Sounds like auto increment would be in drupal modules, where as dbscripts is installed outside the drupal install usually.

if you're still interested, why not submit it as a drupal module then I'll edit the readme/etc. to mention it?