Drupal install does not work with different auto increment settings.

how common is this setting? I didn't even know you could change that...

I'm not sure this patch will completely solve the problem of someone changing those values in their MySQL database. If the suggested patch was 100% correct and covered 100% of the cases where changed auto_increment_increment and auto_increment_offset values might cause Drupal to fail, I would say go ahead and commit the patch, because it is small and simple. But I am not confident it meets those criteria.

The chances of someone changing those values in their database for legitimate reasons is exceedingly small. Here are some quotes from the MySQL documentation page where they are described:

auto_increment_increment and auto_increment_offset are intended for use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables can be set globally or locally, and each can assume an integer value between 1 and 65,535 inclusive.

It is important to remember that it is not possible to confine the effects of these two variables to a single table, and thus they do not take the place of the sequences offered by some other database management systems; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If one of these variables is set globally, its effects persist until the global value is changed or overridden by setting them locally, or until mysqld is restarted. If set locally, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

The auto_increment_increment variable was added in MySQL 5.0.2. Its default value is 1. See Section 6.13, “Auto-Increment in Multiple-Master Replication”.

auto_increment_offset

This variable was introduced in MySQL 5.0.2. Its default value is 1. For particulars, see the description for auto_increment_increment.

Here is the reason these 2 variables exist at all: http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html

So it seems that a Drupal site would need this patch only in 2 cases:

1. Master to master replication of a Drupal database.
2. Someone came up with an unusual idea of how to use these variables for their own purposes.

It seems like it would be useful to have Drupal capable of running on MySQL master to master replication. But I think it will take some extensive testing to prove a patch correct for such a situation.

eek. killes, this is the way our new db boxes are setup (in the master-master setup that Jeremy talks about). So if this is a problem it may cause problems when moving drupal.org over. Might want to test with scratch.drupal.org on juniper. For instance, juniper is set to auto_increment in steps of 2 (as is the other master poplar, but one increments in even numbers and the other increments in odd). Rumor has it that oremj found this bug using the same cluster.

Cheers!

I think this will only affect new installs, not moved ones.

Committed to 5.x.

This has all changed in 6.x, so it might not be needed.

This problem appears in 6.x as well, because with a auto_increment_increment == 2 for example, the role IDs will be messed up in the db:

2    anonymous user
4    authenticated user

which is inconsistent with the values of DRUPAL_ANONYMOUS_RID and DRUPAL_AUTHENTICATED_RID defined in bootstrap.inc.

EDIT: marked Greyed out checkbox appears with no label when editing user #1 as duplicate.

I've just ran into the same issue; in my case, the LoginToboggan module (see this issue) which makes use of DRUPAL_AUTHENTICATED_RID failed as the latter did not point to the actual role ID. I've re-rolled the patch above which resolved the problem for me.

EDIT: I should have mentioned that the patch is against Drupal 6.10.

Subscribing from http://drupal.org/node/222511

system.install is even inconsistent:
db_query("INSERT INTO {permission} (rid, perm, tid) VALUES (%d, '%s', %d)", 1, 'access content', 0);
db_query("INSERT INTO {permission} (rid, perm, tid) VALUES (%d, '%s', %d)", 2, 'access comments, access content, post comments, post comments without approval', 0);

Using magic numbers instead of the defines from bootstrap.inc, BUT at least it doesn't rely on auto_increment here.
So i don't see any reason why not to do the same for {role}, as the patch suggests.

I haven't seen any other problems using drupal on mysql master-master replication.

subscribe

Hi, I have the same problem, users do not receive 'authenticated role' after verification email link. It's just blank in the user/user/list.

Patched my Drupal 6.x13 with #9, but still no joy. Users verify their email, but no role assigned.

Any suggestions would be greatly appreciated :)

The patch affects the setup of the Drupal data base at install time; patching an existing installation has no impact. You would have to install a Drupal instance from scratch.

subscribe

Here is a new patch for this issue, it combines the patch from http://drupal.org/node/254734, this patch and fixes a couple of the "magic numbers". It also edits the uid generation to not "waste" auto_increment values in the generation of uid 0.

Please let me know if I missed anything, as I'd like to get this issue fixed for D6.

Setting version to 6.x-dev on this.

There was some concern that this patched would not work on postgresql. I just tested that on postgres 8.3 with success. Anyone else have any issues with this? This issue in various forms has been languishing for awhile now.

Why is there no testbot results?

I think this patch is incomplete it should also take the inout formats in the next lines into account. We've had problems there when setting up l.d.o

We discussed this in Paris. I originally didn't think this was going to be a problem, but I believe Gerhard is right. The attached patch adds explicit setting of the fid to the filters inserts. In my testing, filters work correctly on auto_increment offsets.

-N

plz look at the issue http://drupal.org/node/679048

thx

plz look at the issue http://drupal.org/node/679048

thx

marked #679048: how to install drupal on a database with different server variables as duplicate.

i tried this patch .. but still having the same result..

table structures

uid name pass
0
1 user1 21a350caaf1df67c3685593d7826d2df
3 user2 21232f297a57a5a743894a0e4a801fc3

and another node table :

nid vid type
1 1 page
3 3 page
5 5 page

@meranakliid, did you reinstall Drupal after applying the patch?

My issue (http://drupal.org/node/737850) is a duplicate of this.

I applied this patch (#22) to 6.16 which seems to have solved my authorization issue on a mysql replicated, auto_increment>1 drupal backend.

Note that I started with a fresh install, applied the patch and then installed Drupal.

With these permissions, the anonymous user already can see the front page after install (Welcome...) as expected (Access Content is enabled for anonymous).

mysql> select * from permission;
| pid | rid | perm                                                                           | tid |
|  22 |   2 | access comments, post comments, post comments without approval, access content |   0 |

mysql> select * from role;
| rid | name               |
|   1 | anonymous user     |
|   2 | authenticated user |

mysql> select * from node_access;
| nid | gid | realm | grant_view | grant_update | grant_delete |
|   0 |   0 | all   |          1 |            0 |            0 |

see related #739576: Many Drupal 7 tests fail with auto_increment* > 1. Let's keep this issue for D6 since we have a somewhat mature patch, and Drupal 7 issues are pretty different.

#22: drupal_installer_6_2.patch queued for re-testing.

Another table that is effected is the filter_formats

if the 'filtered html' doesn't have id=1, then modules/filter/filter.module goes in to an infinite loop until memory or execution time is exhausted.