Upgrade from 1.21.0 to 1.22.3 fails, sql execution error

[todog]

Hi

The upgrade results in the following error:

Debug info: SQLState: 23000
Error Code: 515
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column 'completionpass', table 'Moodle20XX.dbo.mdl_hvp'; column does not allow nulls. UPDATE fails.
SQLState: 01000
Error Code: 3621
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated.

ALTER TABLE mdl_hvp ALTER COLUMN completionpass SMALLINT NOT NULL Error code: ddlexecuteerror Stack trace: line 492 of \lib\dml\moodle_database.php: ddl_change_structure_exception thrown line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end() line 752 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end() line 77 of \lib\ddl\database_manager.php: call to sqlsrv_native_moodle_database->change_database_structure() line 597 of \lib\ddl\database_manager.php: call to database_manager->execute_sql_arr() line 634 of \lib\ddl\database_manager.php: call to database_manager->change_field_type() line 507 of \mod\hvp\db\upgrade.php: call to database_manager->change_field_notnull() line 598 of \mod\hvp\db\upgrade.php: call to hvp_upgrade_2020080401() line 866 of \lib\upgradelib.php: call to xmldb_hvp_upgrade() line 565 of \lib\upgradelib.php: call to upgrade_plugins_modules() line 1917 of \lib\upgradelib.php: call to upgrade_plugins() line 713 of \admin\index.php: call to upgrade_noncore()

We are using SQL Server 2016 and running Moodle 3.9.3

[todog]

Not ideal, but changing the column to allow nulls solved the issue.

[leonstr]

I think #359 is the root cause. Problem also encountered here and here.

I suggest the following as a better workaround:

UPDATE mdl_hvp SET completionpass = 0 WHERE completionpass IS NULL

[leonstr]

Pull request #421 created for this.