SQL server 2008r2, IIS7
sql trace during upgrade stops at the following:
UPDATE mdl_course_sections SET course = '8',sequence = N'61,65,66,67,71,230,280,1597,1598,1599,2668' WHERE id = '52'
DB shows both IS and IX lock on mdl_course_sections table - upgrade then just times out.
Any suggestions or questions about why running on a windows platform would not be helpful!
Thanks in advance
Hi Jamie
No help here but exactly the same issue on same setup - did you manage to resolve it yet?
That N just doesn't look like it belongs in the query does it?
Thanks
Richard
Do a query for the sequence in mdl_course_sections:
select * from `mdl_course_sections` where `sequence` like "%61,65,66,67,71,230,280,1597,1598,1599,2668%"
in something like phpmyadmin. If there is an errant 'N', one can remove the character in phpmyadmin.
The sequence column should contain only numbers separated by commas.
Might want to check all references for that course with:
select * from `mdl_course_sections` where `course` like "8"
'spirit of sharing', Ken
I'll refrain from making comment about Windows.
The N is not the issue. That is there quite correctly to tel SQL to treat the string as a unicode value.
The issue is the upgrade code is locking the table it is trying to update - so always fails.
Hi James,
I would guess that this is caused by either MDL-37939 or MDL-38173.
Do you know what is locking the course_sections table? As far as I recall, the only other step to that upgrade is a get_recordset on the same table to retrieve the data to update. This shouldn't be locking the table, though given that we're talking about MSSQL it could be that MSSQL just sucks at handling multiple open recordsets. In fact, MDL-37734 addresses this issue by changing the driver used for MSSQL from 2.5 onwards.
Andrew
Hi Jamie,
and just to be 100% sure:
A) To which Moodle version are you trying to upgrade to? From which one? (Note we need the exact long numbers 201301xxx.xx to 201303xxx.xx)
B) Which DB driver are you using "mssql" or "sqlsrv"? Version?
One simple select + update into loop should not be leading to those locking attempts at all. If SQL*Server does it one of this will be true: 1) it's configuration / versions /coding combination issue and has solution or 2) SQL*Server is doomed, change to anything else.
So I hope it's all 1), hehe. But we need the AB information above to look to it, try to reproduce and see if a configuration or code fix is possible.
Ciao
Thanks all for the information and the links to the tracker. Yes, when the same update query is executed directly in the query window it works. Having just moved our install to sql server all this is a bit new to me.
We are upgrading (on our test server, thanks be)
from $release = '2.4.1+ (Build: 20130214)';
to $release = '2.4.2 (Build: 20130311)';
using php_sqlsrv_53_nts_vc9
If I read the runes correctly one possible solution (best current?) would be to move to the FreeTDS driver and hope that the Moodle Project continues to support those who must use MS.
Thanks again
Richard
Hi All,
I am also having the exact same problem. The last query is :
UPDATE mdl_course_sections SET course = '19',sequence = N'' WHERE id = '20'
my moodle versio is: 2.3.3
I have tried to upgrade to both 2.3.5 and 2.4.2 and experiencing the same issue.
It's on IIS and SQL Server 2012. This query works alright if I run it using the Management Console.
Something that I noticed is that, if I run this query using the MS Managemanet Console and before moodle starts the upgrade process, it upgrade will pass this step without any issues.
Any help is much appreciated.
Mohsen
I finally could find a work around to upgrade from 2.3.3 to any of the higher versions when the moodle database is on MS SQL.
The issue is that the update script freezes on any query which is like:
UPDATE mdl_course_sections SET course='73', sequence=N'' WHERE id='78'
but if we run this query using the MS SQL Management Console before comitting the moodle upgrade then the moodle upgrade script passes this point and continues the upgrade unless if it needs to do another one of this update queries which will lead the update to freeze again. So, my work around was to run the following query using the MS SQL Management Console before running the moodle upgrade:
UPDATE mdl_course_sections SET sequence=N'' WHERE sequence is null;
This will do the magic and the moodle upgrade goes through after that.
Cheers,
Mohsen
Hi there, I was running my Moodle 2.2 in production server. Then wanted to upgrade Moodle 2.3. I upgraded all the custom modules written in Moodle 2.2 and database to work with Moodle 2.3; everything went fine. Now I wanted to upgrade this Moodle 2.3 to Moodle 2.4 and started upgrade. But it fails and displays the error
Debug info: Table 'mdl_course_sections_avail_fields' doesn't exist
DELETE FROM mdl_course_sections_avail_fields WHERE userfield = ?
[array (
0 => 'interests',
)]
Error code: dmlwriteexception
Stack trace:
line 429 of \lib\dml\moodle_database.php: dml_write_exception thrown
line 1310 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1696 of \lib\dml\moodle_database.php: call to mysqli_native_moodle_database->delete_records_select()
line 1605 of \lib\db\upgrade.php: call to moodle_database->delete_records()
line 1493 of \lib\upgradelib.php: call to xmldb_main_upgrade()
line 284 of \admin\index.php: call to upgrade_core()
I found in /lib/db/upgrade.php file; is this cause the db upgrade fails?
if ($oldversion < 2012120301.13) {
// Delete entries regarding invalid 'interests' option which breaks course.
$DB->delete_records('course_sections_avail_fields', array('userfield' => 'interests'));
$DB->delete_records('course_modules_avail_fields', array('userfield' => 'interests'));
// Clear course cache (will be rebuilt on first visit) in case of changes to these.
rebuild_course_cache(0, true);
upgrade_main_savepoint(true, 2012120301.13);
}
Actually, I have been using the same database since Moodle 1.9 to Moodle 2.4. I did upgrade step by step Moodle versions. For Moodle 2.2 and Moodle 2.3 versions database was upgraded fine. In Moodle 2.4 version database upgrade failed. Below are my Moodle versions used till now.
Moodle 1.9 version - 2007101591.12 - 1.9.17 (Build: 20120312)
Moodle 2.2 version - 2011120503.01 - 2.2.3+ (Build: 20120519)
Moodle 2.3 version - 2012112502.04 - 2.3.2+ (Build: 20120927)
Moodle 2.4 version - 2012120303.01 - 2.4.3+ (Build: 20130322)
Any help would be appreciated.
Thank,
Sankar.
