[development] Why do we avoid auto-incrementing db columns?

[Dries Buytaert]

On 25 Apr 2007, at 18:53, Sammy Spets wrote:
> Sure there are two different use cases for insertions: those where you
> need the ID afterwards (e.g node) and those you don't (e.g watchdog).
> If you want to write code without the use of db_next_id() then do an
> INSERT and omit the id column. I don't see anyone wanting to spend
> effort patching Drupal core to gain 0.01% efficiency across the
> application.

Overall, I think it would be good if we could get rid of db_next_id 
().  Related patches:

  + http://drupal.org/node/49836
  + http://drupal.org/node/118066

* Drupal's db_next_id() was introduced in 2002 by Marco Molinari, who  
used to be a top-contributor.  Parts of the relevant discussion can  
be found at:

   http://lists.drupal.org/archives/development/2002-08/msg00222.html
   http://lists.drupal.org/archives/development/2002-09/msg00185.html

We introduced it because it was hard to reproduce the (correct) last  
ID on non MySQL databases -- we felt this was a more portable way to  
get the last ID.  As this has been a while, the original motivation  
might no longer be relevant, or it might have been incorrect all the  
way.  We had less database expertise/experts than we have now, and  
lack of experience probably made db_next_id() feel like a portable  
and safe choice ...

* I don't think it has been mentioned already but the fact we do not  
use auto_increment make it difficult/impossible to setup Drupal in a  
master-master configuration.  MySQL 5 introdeced a  
"auto_increment_increment" and "auto_increment_offset" which can't be  
used until we get rid of db_next_id().  See http://dev.mysql.com/doc/ 
refman/5.0/en/replication-auto-increment.html.

* The table lock in db_next_id() has been shown to be a problem in  
certain setups.

* table locking, as used in db_next_id(), is not always supported by  
cheap webhosts.

* http://drupal.org/node/49836#comment-74275 suggests that most/all  
database support something like auto_increment.

That's 5 reasons why we could remove db_next_id() ... ;-)


--
Dries Buytaert  ::  http://www.buytaert.net/