Two of my configuration options for mysql, auto_increment_increment and auto_increment_offset, are not set to 1 and it has caused problems for me. See http://drupal.org/node/222511 for one example

Here's what happens:

Drupal inserts values into the db on a new install. It assumes new autoincrement values will start at 1 and get incremented by 1 each time. This is not the case, however.

So, in this line of code:

db_query("INSERT INTO {role} (name) VALUES ('%s')", 'anonymous user');

Drupal assumes the anon user will have a rid of "1", but this is not the case if auto_increment_offset (set in mysql's my.cnf file) is not set to 1.

This bug appears to be fixed in Drupal 7 where these values get explicitly set instead of relying on autoincrement.

CommentFileSizeAuthor
#5 input_format_autoincrement.patch671 bytesSteve Dondley
#1 input_format_autoincrement.patch183.01 KBSteve Dondley

Comments

Steve Dondley’s picture

Steve Dondley commented
StatusFileSize
new input_format_autoincrement.patch183.01 KB

Here's a fix for the input formats which are affected by this problem.

Steve Dondley’s picture

Steve Dondley commented
Status: Active » Needs work
Steve Dondley’s picture

Steve Dondley commented
Status: Needs work » Needs review

Status was not set properly.

damien tournoud’s picture

damien tournoud commented
Status: Needs review » Needs work

You diffed system.install against system.module... The result is ugly :)

Please do a proper CVS diff (http://drupal.org/patch/create).

Steve Dondley’s picture

Steve Dondley commented
StatusFileSize
new input_format_autoincrement.patch671 bytes

Oops, here's the corrected patch.

I don't have this in my cvs so it's quicker to create the patch using diff. This will work just fine.

Steve Dondley’s picture

Steve Dondley commented
Status: Needs work » Needs review
scor’s picture

scor commented

I have the same issue, and indeed noticed weird problems with filters. The patch looks good, but I think the problem with auto_increment_increment is not limited to filter. see related issue Drupal install does not work with different auto increment settings.

Anonymous’s picture

Anonymous (not verified) commented
Component: install system » database system
Status: Needs review » Needs work

Clearly here the correct fix is for the install and update systems (or the database system) to

set @@auto-increment-increment = 1;
set @@auto-increment-offset = 1;

so that the expected values are given for these types of assumptions. I can see these settings being useful for a distributed database collection set where a synchronized merge needs to occur between multiple sets of data. However, I think the application needs to control that so methods would need to be built in Drupal to handle that scenario. Therefore doing these sets implicitly for each connection is probably what should be done. This however only fixes MySQL, other database vendors may have similar but different issues.

CNW - This needs a broader resolution and should be resolved in the connection routine by issuing the above two set statements.

Steve Dondley’s picture

Steve Dondley commented

@earnie, the patch above works. It solves the problem. It's not elegant but it works for all databases. We need to be practical here and get something in the code so people aren't tearing their hair out.

Anonymous’s picture

Anonymous (not verified) commented

@steve: While the patch may fix this one instance it doesn't fix the problem from existing to begin with. This effects all tables and therefore all modules. We need to fix the root of the problem and not change the effect of the problem. Fixing the root of the problem will fix it for all tables and modules.

Steve Dondley’s picture

Steve Dondley commented
Version: 6.x-dev » 7.x-dev

This bug still exists in drupal 7. A fresh install resulted in the following role table:

mysql> select * from role;
+-----+--------------------+
| rid | name               |
+-----+--------------------+
|   3 | anonymous user     | 
|   7 | authenticated user | 
+-----+--------------------+

The filter_formats table behaves the same way.

To fix, I ran these mysql commands:

update role set rid = 1 where name = 'anonymous user';
update role set rid = 2 where name = 'authenticated user';
update filter_formats set format = 1 where name = 'Filtered HTML';
update filter_formats set format = 2 where name = 'Full HTML';

bjaspan’s picture

bjaspan commented

subscribe

nnewton’s picture

nnewton commented
Status: Needs work » Closed (duplicate)

We have two issues here with two different patches that fix different things, there is this issue and then http://drupal.org/node/118066. 118066 pre-dates this one significantly, so I'm going to mark this issue a dup and post a combined patch to http://drupal.org/node/118066.