A different DDL SQL error on upgrade from 2.3.3 to 2.4.1 - explicit DROP INDEX not allowed

A different DDL SQL error on upgrade from 2.3.3 to 2.4.1 - explicit DROP INDEX not allowed

by Richard Jones -
Number of replies: 2
Picture of Plugin developers Picture of Testers

I read the tracker entries for the other DDL SQL bug but this one looks different:

Debug info: SQLState: 42000<br>
Error Code: 3723<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]An explicit DROP INDEX is not allowed on index 'mdl_role.mdl_role$mdl_role_nam_uix'. It is being used for UNIQUE KEY constraint enforcement.<br>

DROP INDEX mdl_role.mdl_role$mdl_role_nam_uix 
Error code: ddlexecuteerror
Stack trace:
  • line 432 of \lib\dml\moodle_database.php: ddl_change_structure_exception thrown
  • line 258 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
  • line 679 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
  • line 88 of \lib\ddl\database_manager.php: call to sqlsrv_native_moodle_database->change_database_structure()
  • line 77 of \lib\ddl\database_manager.php: call to database_manager->execute_sql()
  • line 844 of \lib\ddl\database_manager.php: call to database_manager->execute_sql_arr()
  • line 1019 of \lib\db\upgrade.php: call to database_manager->drop_index()
  • line 1493 of \lib\upgradelib.php: call to xmldb_main_upgrade()
  • line 284 of \admin\index.php: call to upgrade_core()
As an added complication we migrated from mysql on a Debian server (Moodle 2.3.3+ (Build: 20121130) to MS SQL on IIS which might be why we are seeing this on the attempted upgrade to 2.4.1.
 
Suggestions appreciated.
 
Richard
Average of ratings: -
In reply to Richard Jones

Re: A different DDL SQL error on upgrade from 2.3.3 to 2.4.1 - explicit DROP INDEX not allowed

by Richard Jones -
Picture of Plugin developers Picture of Testers

By executing an ALTER TABLE mdl_role DROP CONSTRAINT mdl_role_nam_uix the upgrade appears to have gone smoothly.

Happy days

Richard

Average of ratings: -
In reply to Richard Jones

Re: A different DDL SQL error on upgrade from 2.3.3 to 2.4.1 - explicit DROP INDEX not allowed

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Richard,
that code has been added with the work for MDL-8249, https://github.com/moodle/moodle/commit/e1980f8bb940696518dab1da77b9844026bca4a4#L27R1018 then the version has been updated with MDL-34237, https://github.com/moodle/moodle/commit/00142ce5536d46c5eba0b344f98f8222fd6dde34#L0L1018.

I've added a short mention of your issue there: maybe there some regression around the way DROP has been implemented for MS SQL.

Matteo

Average of ratings: -