AuthenticationKey then UserAuthenticationProviderID as Primary Key conflict with 2021.012 Upgrade

MrCaspan

So when making sure that my database is ready for the 2021.012 update I ran /utility/structure on my 3.3 install and these 2 things pop up

alter table `GDN_UserAuthenticationProvider`
drop primary key;

alter table `GDN_UserAuthenticationProvider`
add primary key (`AuthenticationKey`);

From what I can understand is that it wants to drop the Primary Key from the GDN_UserAuthenticationProvider table (Currently the primary key is on the UserAuthenticationProviderID column) then it wants to set the AuthenticationKey column to be the primary key for the table

Problem is that the UserAuthenticationProviderID column has AUTO_INCRIMENT on it. I can tell the issue so i remove the AUTO_INCRIMENT form the UserAuthenticationProviderID column and now the SQL command can run and now AuthenticationKey is the Primary key for the table.

That seems all well and good until I update to 2021.012 and then run the /utility/structure tool again and now its trying to do this:

alter table `GDN_UserAuthenticationProvider`
-- [Existing: `UserAuthenticationProviderID` int not null, New: `UserAuthenticationProviderID` int not null auto_increment]
change `UserAuthenticationProviderID` `UserAuthenticationProviderID` int not null auto_increment;

alter table `GDN_UserAuthenticationProvider`
drop primary key, algorithm=inplace, lock=none;

alter table `GDN_UserAuthenticationProvider`
add primary key (`UserAuthenticationProviderID`), algorithm=inplace, lock=none;

Now the new site wants UserAuthenticationProviderID to be auto_increment but it can't be because that column does not have the primary key. If you look at the SQL code just after it that looks like it's trying to set UserAuthenticationProviderID back to being the Primary Key and then you could set it to auto_imcriment, Maybe they just got added to the code in the wrong order? I tried swaping it so that it changed the primary key first like so

alter table `GDN_UserAuthenticationProvider`
drop primary key, algorithm=inplace, lock=none;

alter table `GDN_UserAuthenticationProvider`
add primary key (`UserAuthenticationProviderID`), algorithm=inplace, lock=none;

alter table `GDN_UserAuthenticationProvider`
-- [Existing: `UserAuthenticationProviderID` int not null, New: `UserAuthenticationProviderID` int not null auto_increment]
change `UserAuthenticationProviderID` `UserAuthenticationProviderID` int not null auto_increment;

But when I try this code, I get this SQL error

#1846 - ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.

I can manually do what it is asking to the database and change the Primary Key back to be UserAuthenticationProviderID and set it to auto_increment and then these SQL statements go away in the utility but not sure if there is an error in the code or not. Any guesses?

MrCaspan

So just FYI I had to do 2 things to get it to work

Change the order they appear and remove the algorithm and lock options form the command

alter table `GDN_UserAuthenticationProvider`
drop primary key;

alter table `GDN_UserAuthenticationProvider`
add primary key (`UserAuthenticationProviderID`);

alter table `GDN_UserAuthenticationProvider`
-- [Existing: `UserAuthenticationProviderID` int not null, New: `UserAuthenticationProviderID` int not null auto_increment]
change `UserAuthenticationProviderID` `UserAuthenticationProviderID` int not null auto_increment;

@R_J not sure if this is a bug or not.

R_J

Have you tried simply running that update scripts? The underlying code is this, I'd say:

$Construct->table('UserAuthenticationProvider');

if ($Construct->tableExists("UserAuthenticationProvider") && !$Construct->columnExists("UserAuthenticationProviderID")) {
   $userAuthenticationProvider = $SQL->prefixTable("UserAuthenticationProvider");
   $addAuthenticationProviderID = <<<SQL
alter table {$userAuthenticationProvider}
drop primary key,
add `UserAuthenticationProviderID` int not null auto_increment primary key first
SQL;

   $Construct->executeQuery($addAuthenticationProviderID);
   $Construct->reset();
}

And that looks okay.

If simply running that update script does not work flawlessly, it might be helpful if you export the table structure and post it

MrCaspan

yeah I am running /utility/structure and these keep popping back up and throwing errors in the PHP log

I am able to get mine running because I know what its trying to do and I just manually do it but if this DB update is important there is no error thrown on the update nor and error presented to the user when you run the /utility/structure

MrCaspan

Here are the Table structures

3.3 Table Structure before update

After the update this SQL codes gets stuck in the /utility/structure and throws an error when run in the PHP error log

alter table `GDN_UserAuthenticationProvider`
-- [Existing: `UserAuthenticationProviderID` int not null, New: `UserAuthenticationProviderID` int not null auto_increment]
change `UserAuthenticationProviderID` `UserAuthenticationProviderID` int not null auto_increment;

alter table `GDN_UserAuthenticationProvider`
drop primary key, algorithm=inplace, lock=none;

alter table `GDN_UserAuthenticationProvider`
add primary key (`UserAuthenticationProviderID`), algorithm=inplace, lock=none;

Obviously its in the wrong order in the /utility/structure and I can rearrange it and remove the algorithm and lock options and get it to run so that the database will look like this and then /utility/structure stops asking for this code to be run

2021.012 Structure