Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

by Luis de Vasconcelos -
Number of replies: 5

On my MoodleDev1 environment I have a course that contains just one quiz and a certificate for the quiz. I want to backup the course and restore it on my MoodleDev2 environment but the backup crashes with the following error:

Backup Details:
Include Quizzes without user data

Notice: The data types ntext and nvarchar are incompatible in the equal to operator. 

Followed by:

INSERT INTO mdl_backup_ids (backup_code, table_name, old_id, info) SELECT '1232364873', 'question', q.id, '' FROM mdl_question q, mdl_backup_ids bk WHERE q.category = bk.old_id AND bk.table_name = 'question_categories' AND bk.info = 'course' AND bk.backup_code = '1232364873'

line 103 of lib\dmllib.php: call to debugging()
line 461 of question\backuplib.php: call to execute_sql()
line 445 of question\backuplib.php: call to question_insert_q_ids()
line 49 of mod\quiz\backuplib.php: call to question_insert_c_and_q_ids_for_course()
line 501 of mod\quiz\backuplib.php: call to quiz_insert_category_and_question_ids()
line 179 of in C:\Moodle\lib\weblib.php on line 6923

When I created the backup I selected just the following options on the backup screen (\backup\backup.php):

Include:
- Quizzes
- Certificates

Users: None 
Logs: No
User Files: No 
Course files: Yes 
Site files used in this course: Yes 
Grade histories: No

I don't want, and am not including, any user data in the backup.

Both instances of Moodle are on the same server and are running Moodle 1.9.3+ Build 20081112 (2007101531)

Is there a solution to the above error so that I can restore the Courses with all the quiz questions?

I had a look at the moodle.xml file in the course backup file and it does not contain any of the quiz questions.

Also, I was able to export the questions from MoodleDev1 to a GIFT file and then import the GIFT file into the quiz on MoodleDev2, but that isn't the ideal solution. Is there a way to get around the above data types error?

Server config:
Windows Server 2003 SP2 Std Edition
IIS 6 (6.0.3790.3959)
PHP 5.2.6 (Non Thread Safe)
FastCGI from Microsoft
FreeTDS 5.2.9.9 (the NTS version built by Trevor Johnson, MDL-14725)
MS SQL Server 2005 SP2 Std Edition (9.00.1399.00)

Modules:
Certificate Module
Certificate - Site Wide Report (080513)

Thanks.

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

by Luis de Vasconcelos -

I made a backup of a different course on a Moodle 1.9.3 (Build: 20081015) instance that is running on a Linux box with Apache and MySQL and the backup file was created without any errors. The moodle.xml file in the course backup .zip file does contain the quiz questions. So, it certainly seems to be a problem with MSSQL.

Are there any workarounds or solutions for this? (Until we get Moodle 2 where I believe the incompatible ntext and nvarchar problem has been fixed.)

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Oh, one of those errors. Stupid crappy expensive databases seem to be incapable of comparing varchar and text columns without an explicit cast. We have a function in dmllib.php to put in the cast, but you have to remember to call it, and it looks like we forgot here. Sorry about that.

Are you able to check the very latest 1.9.3+ stable, and if it is still a problem, please can you file a bug in the tracker. (There are a number of similar issues in the tracker already, if you want to search and learn how to change the code to fix this sort of problem yourself, to get a quick fix before the fix is committed to CVS.)
Average of ratings: -
In reply to Tim Hunt

Re: Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

by Luis de Vasconcelos -

> Stupid crappy expensive databases seem to be incapable

LOL! Yes, tell that to my boss! In the meantime, I will install the latest build to see if it has been fixed.

What's the name of the function in dmllib.php that you mentioned?

Thanks Tim.

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
It is about line 460 of question/backuplib.php in function question_insert_q_ids, and the change is to replace the line
 AND bk.info = '$info'
with
 AND " . sql_compare_text('bk.info') . " = '$info'
It would be great if could test this and confirm. I have created a tracker issue MDL-17970 for this. you might like to add yourself as a watcher of that issue, so you get notified when it changes.
Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Course backup gives 'data types ntext and nvarchar are incompatible in the equal to operator' error

by Luis de Vasconcelos -

Tim, that worked!

I replaced AND bk.info = '$info' in the question/backuplib.php file with:

AND " . sql_compare_text('bk.info') . " = '$info'

and the backup was created successfully - no errors were generated.

I also had a look at the moodle.xml file in the .zip backup file and now it does include the quiz questions.

Then, after applying this patch, I backed up the course on my MoodleDev1 environment and was able to restore it on my MoodleDev2 environment. The course, including all the questions, were successfully restored.

Average of ratings: -