For mysql and mysqli, db_next_id($name) has the first line:
$name = db_prefix_tables($name);
This is both unnecessary and causes problems if the db_prefix gets changed. {sequences} is in fact the only place where the db_prefix gets stored in drupal's table data.

I'm not sending a diff, because the upgrade script might be tricky. It might be better to simply support backwards compatibility with the $id line looking like this:

$id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s' OR name = '%s' ORDER BY name DESC", $name, db_prefix_tables($name) )) + 1;

Comments

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

If you share some tables with sequences and do not share others, you need to have the table prefix in the sequences table to be able to share some sequences but not share others (by using different prefixes). Drupal supports per-table prefixing. I know this might not be the most welcome answer for you, but IMHO this is why this is implemented so.

Of course some documentation about this would be great there, so I am leaving this as active, not setting to by design.

schuyler1d’s picture

schuyler1d commented

I see the issue now, but it seems the better solution is to use the {sequences} table for the appropriate prefix than to keep the db_prefix in the {sequences} table. As an example, if my setup is like:
db_prefix = {users:'allusers_', default:'site2_'}

then db_next_id('{users}_uid') should query TABLE allusers_sequences for '{users}_uid'
instead of querying TABLE site2_sequences for 'allusers_uid'.

I see how that might require some special casing on when/where to create the sequences table, but as you describe it, whereas before, this was just kind of annoying, there's actually a bug here in that if the setup above is alongside a default:'site1_' db_prefix, then it would seem that the sequences table isn't updating everywhere it should and you'll actually get a conflict on one site after the other site increments its _uid, but the other does not.

OTOH, if this is by_design, then I'm likely missing something, and I admit that I might be more confused after writing the last paragraph than before :-)

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented

Well, I did not check the sequences code, so it should be looked at to see how it is actually done there.

tr’s picture

tr commented
Status: Active » Closed (won't fix)

Drupal 5 is no longer supported, and {sequences} is no longer used in Drupal 6 and above. #149176: Getting rid of the sequences table, using db_last_insert_id() instead of db_next_id()