Moodle

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!