error upgrading to 3.6.3 from 3.3.12

error upgrading to 3.6.3 from 3.3.12

by Georgi Samaras -
Number of replies: 7

I'm upgrading our Moodle site to 3.6.1 and thought this would be a good time to get the latest version of the Attendance plugin (3.6.3 at the time of this post).  However, all my upgrade attempts are stopped with the following error:


Default exception handler: Error writing to database Debug: SQLState: 42000<br>
Error Code: 159<br>
Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Must specify the table name and index name for the DROP INDEX statement.<br>

DROP INDEX mdl_attewarn_idnwarwar_uix
[array (
)]
Error code: dmlwriteexception
* line 489 of \lib\dml\moodle_database.php: dml_write_exception thrown
* line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
* line 431 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
* line 840 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
* line 401 of \mod\attendance\db\upgrade.php: call to sqlsrv_native_moodle_database->execute()
* line 807 of \lib\upgradelib.php: call to xmldb_attendance_upgrade()
* line 518 of \lib\upgradelib.php: call to upgrade_plugins_modules()
* line 1852 of \lib\upgradelib.php: call to upgrade_plugins()
* line 694 of \admin\index.php: call to upgrade_noncore()

Our Moodle site is using IIS and MSSQL.  I took a look at line 401 of the \mod\attendance\db\upgrade.php page and found this:

if ($DB->get_dbfamily() === 'mysql') {
    $DB->execute("ALTER TABLE {attendance_warning} DROP INDEX ". $name);
} else {
    $DB->execute("DROP INDEX ". $name);
}

So it looks like table names are included only for mysql.  Is the fix as easy as adding "sqlsrv" along with mysql?  I'm not quite adventurous enough to try it without asking first.

Thanks in advance for any assistance!

Average of ratings: -
In reply to Georgi Samaras

correction

by Georgi Samaras -

Reading up on it a little more (and to answer my own question), proper MSSQL syntax for dropping indexes does not match what's listed for MySQL.  It would need to be something like

DROP INDEX ". $name ." ON mdl_attendance_warning

I went ahead and tried it and, while the index dropped successfully, I hit a "coding error" message, so it's not as simple a fix as I hoped it'd be.

Also, this error appears when upgrading to the version that's compatible with Moodle 3.5 as well (3.5.5).

In reply to Georgi Samaras

Re: correction

by JJ Combs -

Hi Georgi,

Did you ever manage to get the attendance mod updated?  I am in the exact same position.  I modified line 401 for MSSQL, but I am now getting the coding error with Moodle 3.5 and Attendance mod 3.5.5.  We are also using IIS and MSSQL.  Any insight would be most appreciated!

In reply to JJ Combs

Re: correction

by JJ Combs -

I was able to download the latest version on Github, which has the fix for the SQL errors!  https://github.com/danmarsden/moodle-mod_attendance

In reply to JJ Combs

回复: Re: correction

by Jason Zheng -

I get the latest version on Github, but the error still appear. sad

In reply to Jason Zheng

Re: 回复: Re: correction

by Georgi Samaras -

I dropped the ball on watching this conversation, sorry.  I guess I got auto-unenrolled from the course and stopped receiving replies by email.

Like Jason, I tried the latest 3.5 version from GitHub and still got the error when trying to upgrade.

In reply to Georgi Samaras

回复: error upgrading to 3.6.3 from 3.3.12

by Jason Zheng -

I met the same error when I upgrade from 3.3.6 to 3.6.2 .   My moodle run based CentOS 7, PHP 7.0 .The database is SQL Server 2008 R2.

In reply to Jason Zheng

回复: error upgrading to 3.6.3 from 3.3.12

by Jason Zheng -

Debug info: SQLState: 42000<br>
Error Code: 159<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]必须为 DROP INDEX 语句指定表名和索引名。<br>

DROP INDEX mdl_attewarn_idnwarwar_uix
[array (
)]
Error code: dmlwriteexception

Stack trace:
  • line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 324 of /lib/dml/sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
  • line 431 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
  • line 840 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
  • line 401 of /mod/attendance/db/upgrade.php: call to sqlsrv_native_moodle_database->execute()
  • line 807 of /lib/upgradelib.php: call to xmldb_attendance_upgrade()
  • line 518 of /lib/upgradelib.php: call to upgrade_plugins_modules()
  • line 1852 of /lib/upgradelib.php: call to upgrade_plugins()
  • line 694 of /admin/index.php: call to upgrade_noncore()