This has probably been discussed in depth before, and I've found a few nodes on drupal.org that captured some of the ideas, but I'm relatively new to this list, so please forgive me and enlighten me if there's more history I should know about. AUTO_INCREMENT looks like a database feature that is supported in the databases that drupal currently supports, namely mysql and pgsql. From what I gather from documentation at mysql.org and from previous comments in our forums, it seems that AUTO_INCREMENT is an improvement over the current system of creating nid's using the sequence table (and having to lock it). I'd like to look into moving drupal to use auto-increment instead of table locking, for both performance reasons (1 query vs 3) and webhost compatibility reasons. (There are some webhosts that do not grant LOCK TABLE permission.) Has anyone attempted this conversion before? Any words of advice? Is this something that's supposed to be left to the core developers? Ken
On Mon, Dec 12, 2005 at 12:54:42AM -0800, Knite wrote:
AUTO_INCREMENT looks like a database feature that is supported in the databases that drupal currently supports, namely mysql and pgsql. From what I gather from documentation at mysql.org and from previous comments in our forums, it seems that AUTO_INCREMENT is an improvement over the current system of creating nid's using the sequence table (and having to lock it).
I'd like to look into moving drupal to use auto-increment instead of table locking, for both performance reasons (1 query vs 3) and webhost compatibility reasons. (There are some webhosts that do not grant LOCK TABLE permission.)
Has anyone attempted this conversion before? Any words of advice? Is this something that's supposed to be left to the core developers?
Go ahead and make a patch. You can make it backward compatibile if you modify db_next_id() to support AUTO_INCREMENT (if possibile?), and you'll probably need to create db_userd_id() or similar, to get the id just inserted. -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
Knite wrote:
This has probably been discussed in depth before, and I've found a few nodes on drupal.org that captured some of the ideas, but I'm relatively new to this list, so please forgive me and enlighten me if there's more history I should know about.
A long time ago, we made a big effort to be database independent and introducing our own sequences feature was part of that. We wanted to welcome all RDMS, even those without sequences.
AUTO_INCREMENT looks like a database feature that is supported in the databases that drupal currently supports, namely mysql and pgsql. From what I gather from documentation at mysql.org and from previous comments in our forums, it seems that AUTO_INCREMENT is an improvement over the current system of creating nid's using the sequence table (and having to lock it).
I'd like to look into moving drupal to use auto-increment instead of table locking, for both performance reasons (1 query vs 3) and webhost compatibility reasons. (There are some webhosts that do not grant LOCK TABLE permission.)
Now that we have a few years of experience with sequences, I'll agree that this feature causes a few problems with permissions, is a small performance hit, and should be dropped. I'd support a patch that removed drupal sequences in favor of built in auto-increment and such. I just don't see anyone building support for new databases and especially not those that lack native sequences. -moshe
On Monday 12 December 2005 10:12, Moshe Weitzman wrote:
I'd like to look into moving drupal to use auto-increment instead of table locking, for both performance reasons (1 query vs 3) and webhost compatibility reasons. (There are some webhosts that do not grant LOCK TABLE permission.)
+1 for this plan. If the databases we support all have an auto-increment feature, we might as well use it. I don't see a huge priority on moving Drupal to auto-increment; it could be done on an attrition basis over time. For now, I'd be happy to see a statement from Dries or someone else in the core leadership saying, "As of now, it's okay to rely on auto-increment support in modules for Drupal 4.7 and higher." I think we should *not* back-port auto-increment to older Drupal versions, since someone might be running it on older database versions that lack auto-increment. But by definition no one other than developers and beta testers is (or should be) running 4.7 right now. So a statement like this made *before* 4.7 release does not break any production sites...unless someone is running the beta in production, in which case they went in with both eyes open and presumably with enough experience to deal with this. Scott -- ------------------------------------------------------------------------------- Scott Courtney Drupal user name: "syscrusher" http://drupal.org/user/9184 scott at 4th dot com Drupal projects: http://drupal.org/project/user/9184 Sandbox: http://cvs.drupal.org/viewcvs/drupal/contributions/sandbox/syscrusher
I just don't see anyone building support for new databases and especially not those that lack native sequences.
In fact there's an attempt to make one DB layer for Oracle, and Oracle doesn't support auto_increment column, so there's a trick to achieve it with triggers. Btw, due to the chaotic use of sequences table (sometime it's used, sometimes not), I think definitly it should be worked a little and RDMS should support this auto increment feature.
Btw, due to the chaotic use of sequences table (sometime it's used, sometimes not)
When not?
I don't have an exact list, but I remember having found some examples, so I understood that it was better handling them by DB trigger (for Oracle schema I mean) than doing nothing . Here's one (function statistics_exit in statistics module) if ((variable_get('statistics_enable_access_log', 0)) && (module_invoke('throttle', 'status') == 0)) { // Log this page access. db_query("INSERT INTO {accesslog} (title, path, url, hostname, uid, sid, timer, timestamp) values('%s', '%s', '%s', '%s', %d, '%s', %d, %d)", drupal_get_title(), $_GET['q'], referer_uri(), $_SERVER['REMOTE_ADDR'], $user->uid, session_id(), timer_read('page'), time()); } and I'm pretty sure it's not the only case. Also, I think that modules creators would rely on such function for their modules.
Nicolas Tostin wrote:
Btw, due to the chaotic use of sequences table (sometime it's used, sometimes not)
When not?
I don't have an exact list, but I remember having found some examples, so I understood that it was better handling them by DB trigger (for Oracle schema I mean) than doing nothing .
The general scheme is that we use locking if we want to know the ID in advance or if we need it afterwards for further processing (example: node iD). If we only need to make sure that we get a unique ID and don't care about its value we use auto_increment (examples: accesslog, watchdog). Using LOCK to get the IDs would be a bad idea for performance reasons. Cheers, Gerhard
On Mon, 12 Dec 2005 22:35:11 +0100, Gerhard Killesreiter <gerhard@killesreiter.de> wrote:
Nicolas Tostin wrote:
Btw, due to the chaotic use of sequences table (sometime it's used, sometimes not)
When not?
I don't have an exact list, but I remember having found some examples, so I understood that it was better handling them by DB trigger (for Oracle schema I mean) than doing nothing .
The general scheme is that we use locking if we want to know the ID in advance or if we need it afterwards for further processing (example: node iD).
There are facilities that return the last ID on a per connection basis for pgsql & mysql. As long as we do not use pconnect , we are fine. Those that use pconnect are advanced enough to introduce locking.
Karoly Negyesi wrote:
On Mon, 12 Dec 2005 22:35:11 +0100, Gerhard Killesreiter <gerhard@killesreiter.de> wrote:
Nicolas Tostin wrote:
Btw, due to the chaotic use of sequences table (sometime it's used, sometimes not)
When not?
I don't have an exact list, but I remember having found some examples, so I understood that it was better handling them by DB trigger (for Oracle schema I mean) than doing nothing .
The general scheme is that we use locking if we want to know the ID in advance or if we need it afterwards for further processing (example: node iD).
There are facilities that return the last ID on a per connection basis for pgsql & mysql.
Right. I still prefer to get the nid in advance. It just feels safer. :p Also, caring about LOCK permissions is not of much use as long as search.module uses temporary tables... Also, if we really want to make Drupal take care of simultaneous attempts to edit the same piece of stuff we probably need to introduce locking and or transactions anyway. So please, don't let poor hosting service stand in the way of Drupal's advancement. Cheers, Gerhard
As long as we do not use pconnect , we are fine. Those that use pconnect are advanced enough to introduce locking.
On Mon, Dec 12, 2005 at 11:46:11PM +0100, Gerhard Killesreiter wrote:
Right. I still prefer to get the nid in advance. It just feels safer. :p
False feeling ;) I don't see how it's safer.
Also, caring about LOCK permissions is not of much use as long as search.module uses temporary tables...
Also, if we really want to make Drupal take care of simultaneous attempts to edit the same piece of stuff we probably need to introduce locking and or transactions anyway.
LOCK vs transactions: http://drupal.org/node/39460 It says transactions are faster then LOCKing.
So please, don't let poor hosting service stand in the way of Drupal's advancement.
Right!! -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
On 12/13/05, Piotr Krukowiecki <piotr@mallorn.ii.uj.edu.pl> wrote:
On Mon, Dec 12, 2005 at 11:46:11PM +0100, Gerhard Killesreiter wrote:
Right. I still prefer to get the nid in advance. It just feels safer. :p
False feeling ;) I don't see how it's safer.
Requesting an ID in advance is also a database-dependent feature. MSSQL and others let you do it through their built-in support of sequences, but MySQL only has auto-increment as far as I know. As convenient as auto-increment is, I think it will be harder to work around that if/when Drupal is moved to other databases than it is to use sequences. -Rowan
On Tue, Dec 13, 2005 at 09:10:51AM -0500, Rowan Kerr wrote:
Requesting an ID in advance is also a database-dependent feature. MSSQL and others let you do it through their built-in support of sequences, but MySQL only has auto-increment as far as I know.
You can get a sequence just inserted. This also can be done with PostgreSQL, and probably Oracle. Don't know about MSSQL.
As convenient as auto-increment is, I think it will be harder to work around that if/when Drupal is moved to other databases than it is to use sequences.
The only other "database" that is considered (besides those 2) is sqlite, so far. It seems it has autoincrement columns (http://www.sqlite.org/autoinc.html) but I'm not sure if you can get the inserted id safely. -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
On Tuesday 13 December 2005 09:21, Piotr Krukowiecki wrote:
On Tue, Dec 13, 2005 at 09:10:51AM -0500, Rowan Kerr wrote:
Requesting an ID in advance is also a database-dependent feature. MSSQL and others let you do it through their built-in support of sequences, but MySQL only has auto-increment as far as I know.
You can get a sequence just inserted. This also can be done with PostgreSQL, and probably Oracle. Don't know about MSSQL.
If we are going to use auto_increment in Drupal, then the database abstraction layer needs to have a db_last_insert_id() function (or similar name) to abstract getting the last inserted ID number, since the functions to do this differ from one database to another, even if all of the ones we care about do support the feature. Scott -- ------------------------------------------------------------------------------- Scott Courtney Drupal user name: "syscrusher" http://drupal.org/user/9184 scott at 4th dot com Drupal projects: http://drupal.org/project/user/9184 Sandbox: http://cvs.drupal.org/viewcvs/drupal/contributions/sandbox/syscrusher
Rowan Kerr wrote:
On 12/13/05, Piotr Krukowiecki <piotr@mallorn.ii.uj.edu.pl> wrote:
On Mon, Dec 12, 2005 at 11:46:11PM +0100, Gerhard Killesreiter wrote:
Right. I still prefer to get the nid in advance. It just feels safer. :p
False feeling ;) I don't see how it's safer.
Requesting an ID in advance is also a database-dependent feature. MSSQL and others let you do it through their built-in support of sequences, but MySQL only has auto-increment as far as I know.
As convenient as auto-increment is, I think it will be harder to work around that if/when Drupal is moved to other databases than it is to use sequences.
-Rowan
Right. MySQL is the exception or odd one out, here. Most databases support sequences and transactions. MySQL has consistently been the database missing features and/or having non-standard extensions. Killes' preference for getting the nid in advance may not be safer, but it is certainly no less safe and at the same time more portable and SQL standard. I've almost never *needed* auto_increment in applications I have developed, although I have used it from time to time for convenient unique keys. Sometimes I think people who use auto_increment and mysql_insert_id() are either using the wrong algorithm to accomplish what they want, or they don't know better. ..chrisxj
participants (9)
-
Chris Johnson -
Gerhard Killesreiter -
Karoly Negyesi -
Knite -
Moshe Weitzman -
Nicolas Tostin -
piotr＠mallorn.ii.uj.edu.pl -
Rowan Kerr -
Syscrusher