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

[Sammy Spets]

And to clarify, what i've been explaining above is different to what is
explained in this post. This post is about modifing db_next_id() to stop
using the sequences table for MySQL. My post was about PostgreSQL.

The use of db_next_id() or db_insert_id() can't be removed for obvious 
reasons. The implementation can be changed.

Thanks Dries for bringing up these posts.

-- 
Sammy Spets
Synerger Pty Ltd
http://synerger.com

On 26-Apr-07 07:53, Dries Buytaert wrote:
> 
> 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/
>