Defaults for users.uid and node_revisions.vid missing after upgrade to D6 (PostgreSQL)

I'm not sure how it has happened but one of my site had this bug and another not.

Thank you for the description you saved time for me to fix my buggy site.

(I faced this issue when upgrade my site from 5.11 to 6.6. Database postgresql. One of my site is older - this could be the only difference between them ...)

Thank you for reporting this bug. I confirm this bug exists and will patch ASAP. Will either of you be able to test the patch?

Bumping the version to 6.9 as it is still not fixed in the latest release.

--
Sammy Spets
Drupal core maintainer (PostgreSQL support)
Synerger
http://synerger.com

After looking into it a little further it appears that ancient code is the cause of this bug for me. Drupal 4.7 and earlier didn't use serial fields for users and node_revisions tables. This wasn't a problem for Drupal 4.7 and 5. Those versions called db_next_id() to obtain the next ID from the sequence before executing the INSERT query.

Patch attached and tested thoroughly.

Adjusting title and priority. This issue is critical because it renders an upgraded site unable to accept user registrations or new content.

Subscribing. Same problem (PostgreSQL, upgrade from 5.15 to 6.9), and the fix worked fine.

This site was originally put online using Drupal 4.6, so "ancient code" is probably the culprit for me, too.

Please do not mark your own patch ready, especially if it turns out that you did not put in any conditional to the patch to only run on PostreSQL and your update is (A) pointless for MySQL users (B) breaks for MySQL users, since there is no such thing as NEXTVAL() in MySQL.

Here's a revised patch with a test added to ensure the db_type is pgsql.

Will this work?

Late nights cause problems. :) Thanks for rerolling Steve. It needed yet another reroll to increment the update hook number. The code has already been tested on PostgreSQL so we only need an ok for MySQL. Gábor?

Another patch leaked into the last patch posted.

The issue lies in system_update_6019() only changing fields to serial for MySQL! I can bet you don't have a proper default and sequence for user.uid, boxes.bid, files.fid, etc.

Let's fix system_update_6019() properly. If I'm not mistaken, this logic from this function should apply correctly on both MySQL and PostgreSQL:

  // Remove default => 0, set auto increment.
  $new_uid = 1 + db_result(db_query('SELECT MAX(uid) FROM {users}'));
  $ret[] = update_sql('UPDATE {users} SET uid = '. $new_uid .' WHERE uid = 0');
  db_drop_primary_key($ret, 'users');
  db_change_field($ret, 'users', 'uid', 'uid', array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE), array('primary key' => array('uid')));
  $ret[] = update_sql('UPDATE {users} SET uid = 0 WHERE uid = '. $new_uid);

  // Special field names.
  $map = array('node_revisions' => 'vid');
  // Make sure these tables have proper auto_increment fields.
  foreach (array('boxes', 'files', 'node', 'node_revisions') as $table) {
    $field = isset($map[$table]) ? $map[$table] : $table[0] .'id';
    db_drop_primary_key($ret, $table);
    db_change_field($ret, $table, $field, $field, array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE), array('primary key' => array($field)));
  }

Could someone test it?

Damien,

I already saw that was the original cause of the problem. I explored that and it didn't work. I'm happy to reroll the patch to add what I have in the previous patch to update 6019.

In case people want factual information about what doesn't work. The update 6019 code adds a second sequence for each table (

I'd like to get this sorted out asap since it has been hanging around for a while.

I'm having this exact problem with a sandbox site that we are waiting to upgrade to 6 until things like the above are fixed.

In the sandbox we can not post anything new as it all ends up with vid=0 - a little red box informs us that "The post could not be saved."

Keeping up with the patches on 5 is getting tedious as our other sites are already Drupal 6 - and we're hoping to upgrade this site to 6 soonish, so.. Our alternative now is to do it and then apply the sql statement (similar) to the first one posted above?

Also "ancient code" is the culprit here as well, our others sites were newer drupal babies and no problem when upgrading, this one has been around since Drupal 4.7 and upgraded step by step ever since. However I'm using Drupal on MySQL not PostgreSQL!

I believe the code referenced in comment #10 (or the code that it is meant to replace) is responsible for a bug I'm seeing when upgrading from 6.9 to 6.10. Specifically, on an install of 6.9 with a MySQL back-end, when upgrading to 6.10, the user in the users table with UID=0 has its UID reset to 1+ the maximum UID in the table, but it is *not* then subsequently reset back to UID=0 again.

So I'm left with no UID=0 and thus things that rely on the anonymous user, such as anonymous commenting, don't work. (I can of course manually reset that, which I did, and which fixed my problem)

I have NOT yet done a clean 6.9 install and then upgraded to 6.10 to confirm my hypothesis. I know that there is a bug open (not by me) against comments.module in which this same problem is described, so it appears that I'm not the only one to experience this problem. In grepping through the various 6.10 module install files, system.install was the one that contained the logic (as listed in comment #10 above) that explicitly does what I experienced. It is also *supposed* to set it back once it's done, but perhaps that part isn't working as designed for MySQL?

I am in the process of upgrading a site from 4.6 to 6.13 running postgresql 8.1 and am experiencing what appears to be this same problem with node_revisions.vid

ERROR: duplicate key violates unique constraint node_revisions_pkey
ERROR: duplicate key violates unique constraint node_vid_idx

I checked the user table and it was broken too. Fortunately, the queries at the start of this bug report along with a modified version for the user table corrected the problem for me (without having to look anything up), Thanks Andrew!

So it appears this is still a problem for those upgrading from older sites.

FWIW

I am also seeing this problem upgrading from a 5.21 to 6.16 on PostgreSQL. I checked each of the sequences in my database (displayed by doing a \ds in psql) against their tables, and here's what aren't set as defaults, based on the modules I have installed, once the 6.16 upgrade is done.

The node_revisions and users tables are mentioned above, but the privatemsg-related ones aren't:

node_revisions_vid_seq on node_revisions.vid
privatemsg_archive_id_seq on privatemsg_archive.id
privatemsg_folder_fid_seq on privatemsg_folder.fid
privatemsg_id_seq on privatemsg.id
users_uid_seq on users.uid

Further tinkering with my site I've found that my privatemsg_* tables are out of date. The 6.x privatemsg module wipes the old private messages tables out and the new pm_message and associated tables are properly linked to new sequences when upgrading from whatever I last had it installed at.

Attempting to add 2 topics to a forum caused the error described. Attempting to add 1 user caused an unable-to-save error as well. So both the user table and the node_revisions tables need updating.

Using the above query to update node_revisions.vid worked.
Using the above query to update users.uid worked. I did not have to issue a second query to populate anything in users because the sequence was at the right value for the next user (e.g. nextval was yielding max(uid) + 1)

This brings my system online, it seems, with no other orphaned tables, but the upgrading code should really be fixed.

Specifically, the queries I ran are:

ALTER TABLE node_revisions ALTER COLUMN vid SET DEFAULT nextval('node_revisions_vid_seq'); 

UPDATE node_revisions SET vid = nextval('node_revisions_vid_seq') WHERE vid = 0; UPDATE node SET vid = currval ('node_revisions_vid_seq') WHERE vid = 0; 

ALTER TABLE users ALTER COLUMN uid SET DEFAULT nextval('users_uid_seq'); 

Since this needs work I'd like some clear aims for where the implementation is to go. Here are the options:

1. Fix db_change_field() so it properly creates a serial field by creating the sequence, setting the owner of the sequence to be the serial field and setting the next value correctly (using ALTER SEQUENCE {sequence_name} RESTART [n]).
2. Add the appropriate code to system_install_update_6019().
3. Add a new update function with the appropriate code.

Which one suits the masses? My previous patch and the example code in #10 will not do the whole lot. E.g in patch #9 I'm not restarting the sequence at the right spot. Can't remember if PostgreSQL figures it out. Will have to test it before writing the desired patch.

Let's get this thing done and dusted. The issue is rotting already! :)

Only (1) seems necessary. Given that no website can be upgraded from D5 to D6 properly on PostgreSQL, I don't feel that (2) or (3) are necessary at all.

And yes, you will have to restart the sequence yourself, sequences and their column are independent one from each other in PostgreSQL.

Given that no website can be upgraded from D5 to D6 properly on PostgreSQL, I don't feel that (2) or (3) are necessary at all.

I think this bug is the main issue my site had when upgrading from 5 to 6.

I've patched various modules where I can to improve PostgreSQL support. I just hope this issue can get patched to avoid propagating a "no website can be upgraded" attitude for going from 6 to 7.

I have had the exactly same problem fixed by the exactly same procedure as described above, but - with a plain vanilla upgrade from 5.23 to 6.19 ? What went wrong here?

Has this issue been fixed in the latest D6?