Invalid Collation Upgrading to 4.2 to 4.3

Invalid Collation Upgrading to 4.2 to 4.3

by David Collins -
Number of replies: 2
I receive the following error when upgrading the latest 4.2 to 4.3 stable.  Environment is Windows Server 2019/IIS 10.5/SQL Server 2019/PHP 8.0.28.

Debug info: SQLState: 42000<br>
Error Code: 448<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid collation 'SQL_Latin1_General_CP1_CS_AI'.<br>

SELECT id FROM mdl_user WHERE LEN(password) = 32 AND password COLLATE SQL_Latin1_General_CP1_CS_AI NOT LIKE N'$2y$%' ESCAPE '\'
[array (
0 => '$2y$%',
)]
Error code: dmlreadexception

I managed to track it down in upgrade.php line 3504...if ($oldversion < 2023082600.02) {...etc.  It looks as if they've changed the password encryption to SHA512 and this stub finds any users with an MD5 password and sets it to something else. 

The SQL db was created with the default collation, SQL_Latin1_General_CP1_CI_AS.  I realize that isn't the recommendation but it has been working for the entirety of the 3.X lifecycle and with the upgrade to 4.x.

The upgrade code generates a sql string that takes the existing collation and "flips" the CI with CS and AS with AI, found in (sqlsrv_native_moodle_database.php -> sqllike), and the resulting collation (SQL_Latin1_General_CP1_CS_AI) does not exist.  Hence the error above.  The most similar is "Latin1_General_CS_AI".

I was able to comment out this step in the upgrade process, since none of my users besides the default admin account have hashed passwords, and finished the upgrade.  It seems like the upgrade code should not be trying to set a collation that does not exist in the database (?).

I did try to change the collation on the database and it got messy really quick.  I would like to avoid it if possible.

Thanks in advance,
David

Average of ratings: -
In reply to David Collins

Re: Invalid Collation Upgrading to 4.2 to 4.3

by David Collins -
I dedided to take my lumps, update the database collation and was able to upgrade without any mods.  Further research indicatd "SQL_" collations are deprecated (I have updated SQL Server from 2008 to 2019 over time).  Now I am using Latin1_General_CS_AS per the recommendations.
Average of ratings: -
In reply to David Collins

Re: Invalid Collation Upgrading to 4.2 to 4.3

by Whalon Herbert -
Hi forum, 

Bumping this to see if resolution is expected in a future 4.3+ build or if the manual collation change is supported.  I am in same situation as @David Collins with regard to this issue in SQL Server 15.x.  I am working with our database team to determine the correct collation, and she interpreted the same debug message the same as above....a mix up/swap of case sensitive/accent sensitive notion in the collation name. 

Just today I tried to test upgrade using the 4.3+ latest package and the same error persists.

Leaning towards requesting her to change the collation from "SQL_Latin1_General_CP1_CS_AS" to "Latin1_General_CS_AS" but am not clear if the php upgrade code in 4.3+ will interpret this as expected.

Could you advise...I see there are only a few cases of this being reported so far, and in the bug tracker I traced, this exact issue seemingly was reported as tested, verified, and fixed. At least i think I was looking at the same issue in the tracker forum.  I don't have that link in front of me, as i was searching from the server at the time, and i am back on my own machine now.

thanks,
WH
Average of ratings: -