The 'ntext monster' - search block crashes on MSSQL

[luisdev]

Thanks for this block! I have installed it in on my Windows 7 Ultimate 64 laptop. Great!

Moodle 2.5.4+ (Build: 20140131) is running under:

• Windows 7 Ultimate 64
• IIS v7.5
• SQL Server 2008 R2
• Microsoft Drivers for PHP for SQL Server v2.0.1082.
• PHP v5.3
• $CFG->dbtype = 'sqlsrv';
• Moodle debug is set to Developer level.

The block installs successfully, but when I add it to the front page of my Moodle 2.5.4+ test site Moodle crashes with this error:

PHP Notice: Undefined offset: 1 in C:\Moodle\blocks\search\MoodleSearch\Model\Search.php on line 477 PHP
Stack trace: PHP 1. {main}() C:\Moodle\blocks\search\index.php:0 PHP 2. MoodleSearch\Block->search()
C:\Moodle\moodle\blocks\search\index.php:87 PHP 3.
MoodleSearch\Search->__construct() C:\Moodle\blocks\search\MoodleSearch\Block.php:86 PHP 4.
MoodleSearch\Search->runSearch() C:\Moodle\blocks\search\MoodleSearch\Model\Search.php:47 PHP 5.
MoodleSearch\Search->searchTable() C:\Moodle\blocks\search\MoodleSearch\Model\Search.php:193 PHP 6.
MoodleSearch\Search->buildWordQuery() C:\Moodle\blocks\search\MoodleSearch\Model\Search.php:225 PHP 7.
MoodleSearch\Search->getTextSubstitutions() C:\Moodle\blocks\search\MoodleSearch\Model\Search.php:378 Default
exception handler: Error reading from database Debug: SQLState: 42000
Error
Code: 8116
Message: [Microsoft][SQL Server Native Client 10.0][SQL
Server]Argument data type ntext is invalid for argument 1 of lower
function.
SELECT * FROM mdl_assign WHERE (((LOWER(name) LIKE N'%humpty%') AND
(LOWER(name) LIKE N'%dumpty%')) OR ((LOWER(intro) LIKE N'%humpty%') AND
(LOWER(intro) LIKE N'%dumpty%'))) AND course = '1' [array ( 0 => '%humpty%',
1 => '%dumpty%', 2 => '%humpty%', 3 => '%dumpty%', 4 => 1, )] Error
code: dmlreadexception * line 423 of \lib\dml\moodle_database.php:
dml_read_exception thrown * line 260 of
\lib\dml\sqlsrv_native_moodle_database.php: call to
moodle_database->query_end() * line 367 of
\lib\dml\sqlsrv_native_moodle_database.php: call to
sqlsrv_native_moodle_database->query_end() * line 788 of
\lib\dml\sqlsrv_native_moodle_database.php: call to
sqlsrv_native_moodle_database->do_query() * line 836 of
\lib\dml\sqlsrv_native_moodle_database.php: call to
sqlsrv_native_moodle_database->get_recordset_sql() * line 239 of
\blocks\search\MoodleSearch\Model\Search.php: call to
sqlsrv_native_moodle_database->get_records_sql() * line 193 of
\blocks\search\MoodleSearch\Model\Search.php: call to
MoodleSearch\Search->searchTable() * line 47 of
\blocks\search\MoodleSearch\Model\Search.php: call to
MoodleSearch\Search->runSearch() * line 86 of
\blocks\search\MoodleSearch\Block.php: call to
MoodleSearch\Search->__construct() * line 87 of \blocks\search\index.php:
call to MoodleSearch\Block->search()

The "Argument data type ntext is invalid for argument 1 of lower function" message suggests that this might be the 'ntext monster' coming back to haunt me again (MDL-11270).

How can I fix this?

[brainysmurf]

I don't have a server to test with, but basically the fix would be to adjust the query so that it casts an ntext (ntext!) to varchar. So in MoodleSearch/Models/Search.php (on line 338 if you're using latest) change the following in buildWordQuery:

    $columnName = "LOWER({$columnName})";

to

    $columnName = "LOWER(CAST({$columnName} AS VARCHAR(255)))";

Let us know if that fixes things, or, probably I guess, breaks it more?

[luisdev]

Thanks. I'll try that and report back.
FYI, Microsoft has a free version of the SQL Server database called SQL Server Express. http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx