Upgrading to Drupal 9.3.x changes users table and locks users out of the site

@devKhairul I can confirm that there is a problem with updating from Drupal 9.2 to 9.3 with the SQL server driver.

The problem is because we can't create a new identity column on a table and set the values as we want to. Therefore we have a tricky issue to solve. After updating from 9.2 to 9.3 there is no user with UID 0 anymore.

Here is a work around that fixes my local test site. Use with caution and please please please backup your database first. But this does have a chance of fixing your site if you have run 9.3 database updates on an SQL server backed site.

Here's the SQL to run against your database:

/* Create a table to hold the temporary data. */
CREATE TABLE user_temp_9301(
    [uid] [int] NOT NULL,
    [uuid] [varchar](128) NOT NULL,
    [langcode] [varchar](12) NOT NULL,
);

/* Insert the data into the temporary table. Note this assumes that prior to updating to 9.3 your user UIDs where sequential. */
insert into user_temp_9301
select uid - 1, uuid, langcode from users;

/* Before running this make sure you have a backup and that the data is present in the new user_temp_9301 table */
truncate table users;
SET IDENTITY_INSERT users ON;
insert into users (uid, uuid, langcode) select uid, uuid, langcode from user_temp_9301;

/* At this point the data should be back in the users table. Make sure this true before doing the next query. */
drop table user_temp_9301;

@devKhairul note looking at the issue summary it appears you've manually added a user with the UID of 0. You'll need to change the SQL to

insert into user_temp_9301
select uid - 1, uuid, langcode from users where uid > 0;

to cope with that.

@devKhairul well we've updated core today to not run the update that breaks stuff on SqlServer - see #3265802: user_update_9301() causes data loss and a broken site on SQL Server and the plan now is to add an update to the sqlsrv module that does the change. The current plan is to do that in this issue so you should see the patch / MR arrive here and then be committed... hopefully that'll be soon.

We have another related MySQL 8 issue to fix on Drupal 9.3.x before we might do an off-schedule Drupal 9 release to ensure no one else breaks their site due to this, if D9 release managers agree to this.

@devKhairul hopefully you didn't do the update on live and are still running on Drupal 9.2.x - that's still under security support so I'd hold on trying to upgrade until sqlsrv is updated to account for the update.

@devKhairul I spoke to @beakerboy about this - I thought he had some ideas.

Hello just wanted to toss my hat in the ring for this issue. We've attempted a Drupal 9.3 upgrade in mid-December last year and ran into the same error:

Drupal\Component\Plugin\Exception\ContextException: The 'entity:user' context is required and not present. in Drupal\Core\Plugin\Context\Context->getContextValue() (line 73 of core\lib\Drupal\Core\Plugin\Context\Context.php).

We could not figure out what the problem was and rolled back to Drupal 9.2.x as a temporary measure. After tracing multiple Drupal threads from Drupal core issue queue, I've landed here, which is promising. I've pasted in the error code in h2 tags to help with searchability for others having the same issue.

Drupal 9.2.15
Microsoft-IIS/10.0
SQLServer 13.00.4001
PHP 7.4.8

Updating issue with error message to help with searchability.

I've been working with @beakerboy in my attempt to fix the changeField method to support changing a field to serial when there is existing data in the table. This requires the following to occur:

1. Create a new table
2. Make the requested modifications to the field
3. Copy the data from the old table to the new table
4. Add the indexes back to the table with any changes as needed by the request
5. Drop the old table
6. Rename the new table to the old table's name

If anyone has any recommendations to improve this, please let me know. I am still learning MS SQL and Drupal.

One thing to note, once this is code is merged, we still need to get the core team to update user_update_9301 in the user.install file to allow it to run if the new version of the module or greater is installed.

@KenLewis - I don't think you will need the core team to do anything - the sqlsrv module can provide its own update - we already stopped running the update for sqlsrv - see #3265802: user_update_9301() causes data loss and a broken site on SQL Server . I don't think we can revert that change because we need to be sure that the latest sqlsrv module is being used.

@KenLewis I'd also be tempted to be pragmatic here and implement something like #4 in a update function in sqlsrv for now - and implement a generic fix later. It's more important to get people to 9.3.x than to have a generic fix.

Having read through this issue, it looks like the following actions are needed to resolve:

1. Test and review MR 79 to ensure sqlsrv can cope with schema changes that convert columns to serial.
2. Implement an update function to ensure the user uid column is converted to serial as per user_update_9301. This update function should ensure it only attempts to do work if the column is not already an appropriate identity column in the database (i.e. if the site admin has already fixed the schema manually to work round the problem while awaiting the fix from this issue).

Is this correct?

Hi:
I ran to this same issue when upgrading a production site from 9.2 to 9.5.
After the database update there was not posible to create users so i fixed the schema manually to work around the problem but now i had a error on the site state report:

No coinciden las definiciones de entidad y/o campo
Se detectaron los siguientes cambios en las definiciones de tipo de entidad y de campo.
Usuario
El campo ID de Usuario necesita ser actualizado.

Any idea how to solve it?

The translation of the above comment:

Entity and/or field definitions do not match
The following changes were detected in the entity type and field definitions.
User
The User ID field needs to be updated.

Updating target branch

Noting that most of the discussion has been happening on #3371473: SQL Server Identity error when updating to Drupal 10.1.0, which includes successful testing of MR87. The merge request will be completed on this issue and once this issue has been closed as fixed the other issue will be closed as duplicate.

Should this now be closed as Fixed?

I'm planning to mark all the issues resolved in 4.4.x as fixed when we do the stable release, which will hopefully be the same as rc1 if no problems arise.