Moodle
  1. Moodle
  2. MDL-11270

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

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.6
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Rank:
      310

      Description

      It seems that ntext is deprecated in latest MSSQL versions and the recommended way is to use nvarchar(max) instead.

      Analyse how we can change that, their impact both in dml and ddl libraries.

      Some initial references:

      http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421702&SiteID=1
      http://msdn2.microsoft.com/en-US/library/ms189087.aspx

        Issue Links

          Activity

          Hide
          Eloy Lafuente (stronk7) added a comment -

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

          Show
          Eloy Lafuente (stronk7) added a comment - Linking with another (closed) bug about the same issue. MDL-9845
          Hide
          Martin Dougiamas added a comment -

          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?

          Show
          Martin Dougiamas added a comment - 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?
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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

          Show
          Eloy Lafuente (stronk7) added a comment - 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
          Hide
          Jonathan Moore added a comment -

          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.

          Show
          Jonathan Moore added a comment - 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.
          Hide
          Luis de Vasconcelos added a comment -

          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

          Show
          Luis de Vasconcelos added a comment - 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
          Hide
          Michael Mulvihill added a comment -

          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.

          Show
          Michael Mulvihill added a comment - 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.
          Hide
          Nadine Thybusch added a comment -

          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!

          Show
          Nadine Thybusch added a comment - 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!
          Hide
          Michael Lewis added a comment -

          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...

          Show
          Michael Lewis added a comment - 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...
          Hide
          Luis de Vasconcelos added a comment -

          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.

          Show
          Luis de Vasconcelos added a comment - 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.
          Hide
          Michael Lewis added a comment -

          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()
          Show
          Michael Lewis added a comment - 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()
          Hide
          Fred Weiss added a comment -

          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.

          Show
          Fred Weiss added a comment - 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.
          Hide
          Pavel Holeček added a comment -

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

          Show
          Pavel Holeček added a comment - Here is another consequence of this issue: https://tracker.moodle.org/browse/MDL-38169
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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.

          Show
          Eloy Lafuente (stronk7) added a comment - 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.
          Hide
          Valerii Kuznetsov added a comment - - edited

          Another case of 'ntext': MDL-39068

          Show
          Valerii Kuznetsov added a comment - - edited Another case of 'ntext': MDL-39068
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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!

          Show
          Eloy Lafuente (stronk7) added a comment - 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!
          Hide
          Petr Škoda added a comment -

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

          Show
          Petr Škoda added a comment - Thanks Eloy, all 3 fixed. I think there is no need for special documentation because the change should be fully backwards compatible.
          Hide
          Sam Hemelryk added a comment -

          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.

          Show
          Sam Hemelryk added a comment - 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.
          Hide
          Petr Škoda added a comment -

          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

          Show
          Petr Škoda added a comment - 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
          Hide
          Rajesh Taneja added a comment -

          Thanks Petr,

          Works fine.

          Show
          Rajesh Taneja added a comment - Thanks Petr, Works fine.
          Hide
          Sam Hemelryk added a comment -

          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.

          Show
          Sam Hemelryk added a comment - 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.
          Hide
          Petr Škoda added a comment -

          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)
          Show
          Petr Škoda added a comment - 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)
          Hide
          Luis de Vasconcelos added a comment -

          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?

          Show
          Luis de Vasconcelos added a comment - 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?
          Hide
          Petr Škoda added a comment -

          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.

          Show
          Petr Škoda added a comment - 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.
          Hide
          Luis de Vasconcelos added a comment -

          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?

          Show
          Luis de Vasconcelos added a comment - 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?
          Hide
          Eloy Lafuente (stronk7) added a comment -

          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!

          Show
          Eloy Lafuente (stronk7) added a comment - 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!
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Fixed.

          Show
          Eloy Lafuente (stronk7) added a comment - Fixed.

            People

            • Votes:
              12 Vote for this issue
              Watchers:
              17 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: