Analyse the impact and roadmap to move, under MSSQL, from ntext to nvarchar(max)

Linking with another (closed) bug about the same issue. MDL-9845

Eloy, could you please look into this issue soon?

Jonathon Moore reported this error which seems to be related to this issue...

"The ntext data type cannot be selected as DISTINCT because it is not comparable."

From:

SELECT DISTINCT r.* FROM mdl_role r, mdl_role_assignments ra, mdl_role_allow_override rao WHERE ra.userid = 2 AND ra.contextid IN (1) AND rao.roleid = ra.roleid AND r.id = rao.allowoverride ORDER BY r.sortorder ASC

• line 686 of lib/dmllib.php: call to debugging()
• line 966 of lib/dmllib.php: call to get_recordset_sql()
• line 4128 of lib/accesslib.php: call to get_records_sql()
• line 65 of admin/roles/assign.php: call to get_overridable_roles()

Is there some quick-fix MS SQL config option that can make this work?

Hi,

if I'm not wrong, that exact problem was fixed some weeks ago (see MDL-16034) both in 19_STABLE and HEAD, so updating to latest 1.9.x weekly should solve it.

AFAIK there isn't any switch in MS-SQL to change that behaviour.

TEXT and IMAGE (formerly CLOB and BLOB) columns have serious limitations both in DISTINCT and UNION queries. And that not only affects MSSQL but also Oracle (plus causing big speed problems under MySQL and PostgreSQL).

So we should rewrite those queries always, avoiding using them in DISTINCT and UNION clauses (see again MDL-16034 for a general-purpose way to rewrite them, avoiding TEXT columns in those calculations).

We can consider switching to nvarchar(max) under MSQL in the future, yes. But that won't solve the underlying problem, so better to continue rewriting queries for now. IMO.

Ciao

Eloy are there any text searches we might be able to perform on the code base to help find the spots where this happens? I would be happy to have one our our technical staff run this down and get them documented in the issues tracker that helps.

I have Nick loading up the latest weekly to see if it resolves the error messages we have seen so far.

Based on what you are saying, I take it that its not a good idea for us to manually change the data types to nvarchar on this instance of Moodle as a form of work around?

Thanks for the follow up on this. We will report back shortly.

An interesting blog about switching from NTEXT to NVARCHAR(MAX) and the performance thereof.

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

This relates to MDL-26597 which shows one of the problems of using ntext data types in MSSQL.

admin/replace.php
Debug info: Argument data type ntext is invalid for argument 1 of replace function.
UPDATE mdl_question_numerical_options SET instructions = REPLACE(instructions, ?, ?)
[array (
0 => 'http://xxxx.medschl.cam.ac.uk',
1 => 'http://yyyy.medschl.cam.ac.uk',
)]

Clearly this will continue to cause problems in the future. It would seem like a good idea to proceed with this change ASAP.

Hello,

I just stumbled upon this bug while setting up a fresh install of moodle v221+ with our MS-SQL 2008r2 DB.

Viewing 'My profile -> Forum posts -> Post' generated the following error:
"Debug info: The ntext data type cannot be selected as DISTINCT because it is not comparable. [...]"

After a bit of reading, I learned that ...
"ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead."
(http://msdn.microsoft.com/en-us/library/ms187993.aspx)

So I changed the dbtypes in lib/ddl/mssql_sql_generator.php accordingly and reinstalled. I didn't get any errors and the SELECT DISTINCT queries are running smoothly as well.

Now I'm just wondering if my "fix" could possibly break anything else?
Some feedback would be appreciated. Thanks!

Could this issue be updated to 'affects version/s 2.0, 2.1, 2.2, 2.3'? Now running into this ntext incompatibility fairly often (SQL server 2008 r2). I have changed ntext to nvarchar(max) in a couple of tables and not seen any resulting issues, however as there is no formal support for this 'fix' I don't like to make it too pervasive.

Our institution is starting to move to SQL Server 2012. We will likely have 2008 around for quite some time but as an issue that's been known about for almost 5 years hopefully there can be some progress on a plan. Microsoft doesn't extend its support deadlines for ever...

Michael, you say that you're "running into this ntext incompatibility fairly often". Can you describe where?

Also, please describe how you're connecting PHP to MSSQL. Are you using the "Microsoft Drivers for PHP for SQL Server" to connect Moodle/PHP to MSSQL or are you using some other driver like FreeTDS? Try version 3.0 of the Microsoft Drivers for PHP for SQL Server and see if you still get the ntext data type errors.

I am currently using Microsoft Driver for Sql version 2. I will install v3 and let you know here of any changes. Here are some selected PHP error log entries:

1.
sqlsrv_query: SQLSTATE = 42000
sqlsrv_query: error code = 306
sqlsrv_query: message = [Microsoft][SQL Server Native Client 10.0][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Default exception handler: Error reading from database Debug: SQLState: 42000<br>
Error Code: 306<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.<br>

SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email,u.department,u.institution,
s.id AS submissionid, s.grade, s.submissioncomment,
s.timemodified, s.timemarked,
CASE WHEN s.timemarked > 0 AND s.timemarked >= s.timemodified THEN 1
ELSE 0 END AS status FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid
AND s.assignment = 868 WHERE u.id IN (129,133,134,148,150,172,210,264,267,268,270,280,290,295,300,306,308,309,311,317,372) ORDER BY submissioncomment ASC, lastname ASC
[array (
)]

• line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
• line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
• line 1412 of \mod\assignment\lib.php: call to sqlsrv_native_moodle_database->get_records_sql()
• line 671 of \mod\assignment\lib.php: call to assignment_base->display_submissions()
• line 57 of \mod\assignment\submissions.php: call to assignment_base->submissions()

2.
sqlsrv_query: SQLSTATE = 42000
sqlsrv_query: error code = 421
sqlsrv_query: message = [Microsoft][SQL Server Native Client 10.0][SQL Server]The ntext data type cannot be selected as DISTINCT because it is not comparable.
Default exception handler: Error reading from database Debug: SQLState: 42000<br>
Error Code: 421<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The ntext data type cannot be selected as DISTINCT because it is not comparable.<br>
SQLState: 42000<br>
Error Code: 421<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The ntext data type cannot be selected as DISTINCT because it is not comparable.<br>

SELECT DISTINCT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
FROM mdl_course c
JOIN mdl_forum_discussions fd ON fd.course = c.id
LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
WHERE fd.userid = '311'
[array (
0 => '311',
)]

• line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
• line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
• line 7824 of \mod\forum\lib.php: call to sqlsrv_native_moodle_database->get_records_sql()
• line 117 of \mod\forum\user.php: call to forum_get_courses_user_posted_in()

3.
sqlsrv_query: SQLSTATE = 42000
sqlsrv_query: error code = 402
sqlsrv_query: message = [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types ntext and varchar are incompatible in the equal to operator.
Default exception handler: Error reading from database Debug: SQLState: 42000<br>
Error Code: 402<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types ntext and varchar are incompatible in the equal to operator.<br>

SELECT COUNT('x')
FROM mdl_assignment_submissions s
LEFT JOIN mdl_assignment a ON a.id = s.assignment
INNER JOIN (SELECT DISTINCT eu1_u.id
FROM mdl_user eu1_u
JOIN mdl_role_assignments eu1_ra3 ON (eu1_ra3.userid = eu1_u.id AND eu1_ra3.roleid IN (6,5,4,3,1) AND eu1_ra3.contextid IN (1,22,25,35))
JOIN mdl_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
JOIN mdl_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = '4')
WHERE eu1_u.deleted = 0 AND eu1_u.id <> '1') u ON u.id = s.userid
WHERE s.assignment = '1' AND
s.data2 = 'submitted'
[array (
0 => '4',
1 => '1',
2 => '1',
)]

• line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
• line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
• line 1297 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_records_sql()
• line 1372 of \lib\dml\moodle_database.php: call to moodle_database->get_record_sql()
• line 1543 of \lib\dml\moodle_database.php: call to moodle_database->get_field_sql()
• line 420 of \mod\assignment\type\upload\assignment.class.php: call to moodle_database->count_records_sql()
• line 3928 of \mod\assignment\lib.php: call to assignment_upload->count_real_submissions()
• line 3501 of \lib\navigationlib.php: call to assignment_extend_settings_navigation()
• line 2910 of \lib\navigationlib.php: call to settings_navigation->load_module_settings()
• line 601 of \lib\pagelib.php: call to settings_navigation->initialise()
• line 617 of \lib\pagelib.php: call to moodle_page->magic_get_settingsnav()
• line 133 of \blocks\settings\block_settings.php: call to moodle_page->__get()
• line 280 of \blocks\moodleblock.class.php: call to block_settings->get_content()
• line 232 of \blocks\moodleblock.class.php: call to block_base->formatted_contents()
• line 926 of \lib\blocklib.php: call to block_base->get_content_for_output()
• line 978 of \lib\blocklib.php: call to block_manager->create_block_contents()
• line 349 of \lib\blocklib.php: call to block_manager->ensure_content_created()
• line 6 of \theme\afterburner\layout\default.php: call to block_manager->region_has_content()
• line 685 of \lib\outputrenderers.php: call to include()
• line 637 of \lib\outputrenderers.php: call to core_renderer->render_page_layout()
• line ? of unknownfile: call to core_renderer->header()
• line 1363 of \lib\setuplib.php: call to call_user_func_array()
• line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->__call()
• line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->header()
• line 51 of \mod\assignment\type\upload\assignment.class.php: call to assignment_base->view_header()
• line 51 of \mod\assignment\view.php: call to assignment_upload->view()

4.
sqlsrv_query: SQLSTATE = 42000
sqlsrv_query: error code = 402
sqlsrv_query: message = [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types ntext and varchar are incompatible in the not equal to operator.
Default exception handler: Error reading from database Debug: SQLState: 42000<br>
Error Code: 402<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types ntext and varchar are incompatible in the not equal to operator.<br>

SELECT * FROM mdl_feedback_value WHERE item = '292' AND value != '' AND value != '0'
[array (
0 => '292',
)]

• line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
• line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
• line 1129 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_records_sql()
• line 2412 of \mod\feedback\lib.php: call to moodle_database->get_records_select()
• line 137 of \mod\feedback\item\multichoice\lib.php: call to feedback_get_group_values()
• line 230 of \mod\feedback\item\multichoice\lib.php: call to feedback_item_multichoice->get_analysed()
• line 156 of \mod\feedback\analysis.php: call to feedback_item_multichoice->print_analysed()

This issue keeps on rearing its head in different areas. See

http://tracker.moodle.org/browse/MDL-35936
http://tracker.moodle.org/browse/MDL-34527

Are two examples but there are a few others.

Here is another consequence of this issue:
https://tracker.moodle.org/browse/MDL-38169

Thanks for the report, Pavel. I've closed MDL-38169 as dupe of MDL-38091, where the problem is going to be integrated next week.

Also, note MDL-35936 seems not replicable anymore and MDL-34527 is already fixed/closed. Those 2 from Fred Weiss, 2 comments above.

Another case of 'ntext': MDL-39068

It looks perfect, and forcing the update of storage is a very good idea, no matter how long it takes.

Just some comments:

1) This need docs, for sure (upgrade, release notes, whatever).

2) Perhaps we should also consider moving from IMAGE to VARBINARY(max). I don't think we have many uses in core, so it should be pretty fast.

3) Text @ https://github.com/skodak/moodle/compare/w31_MDL-11270_m26_nvarcharmax#L1R219 seems incorrect twice (columns and mysql).

Sending back to development about those 3 points, yay!

Thanks Eloy, all 3 fixed. I think there is no need for special documentation because the change should be fully backwards compatible.

Hi Petr - this has been integrated now.

Surely this needs at least some documentation due to the time impact this will have when upgrading right?
I'll the docs required label after this.

right, all admins using ms sql server will have to wait a bit longer during upgrades, they should make sure they set up the timeouts properly if they use IIS

Thanks Petr,

Works fine.

Against all probability we've achieved normality. You changes didn't break the tests I pretended to run and are now immortalised upstream. Good for you!

"It was a programming technique that had been reverse-engineered from the sort of psychotic mental blocks that otherwise perfectly normal people had been observed invariably to develop when elected to high political office."
Adams, D (1992) Mostly Harmless. London: William Heinemann.

slow upgrade mentioned in the release notes page:

• MDL-11270 - Significantly improved MS SQL Server compatibility (warning: upgrade step for MS SQL servers may take very long time)

I just want to confirm that I understand the meaning of this change:

During an upgrade to Moodle 2.6 all the "legacy" NTEXT columns in the MSSQL database are converted to NVARCHAR(MAX) columns (because Microsoft dropped the NTEXT data type in SQL Server 2005). So during the upgrade, Moodle goes through each of the tables in the database that contain NTEXT columns and converts those NTEXT colums to NVARCHAR(MAX) columns. During this conversion all the data stored in those NTEXT columns also has to be converted. This whole process can take a long time, especially if your Moodle site has been upgraded from several older versions over the years. By default IIS and Apache web servers are not configured to allow scripts to run for such a long time and they will timeout with "inactivity" errors. To prevent this you need to increase the timeout settings in PHP and the web server to allow for this long conversion process to run without any timeouts being triggered.

Is this correct?

Yes, it may take a very long time to upgrade, but all Moodle servers running IIS should be already configured to have very long timeout values in IIS. Alternatively try command line upgrade because it does not have these timeouts.

I just noticed that the Fix Versions for this issue are 2.0.10 and 2.6

Should 2.0.10 be there? Isn't this fix for 2.6 only?

aha, yes, it's a mistake... surely the integrator found the 2.0.10 there, and it's an already archived version, so was unable to fix it.

fixing it now... thanks!

Fixed.