Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Critical
-
Resolution: Cannot Reproduce
-
Affects Version/s: 1.9.4
-
Fix Version/s: 1.9.5
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Environment:MS Windows Server 2003
-
Database:Microsoft SQL
-
Difficulty:Difficult
-
Affected Branches:MOODLE_19_STABLE
-
Fixed Branches:MOODLE_19_STABLE
Description
When searching a forum the adapter seems to crash and return no recordset. If i run the offending SQL query directly on the MS SQL server it runs fine so i presume its a driver based problem.
The search will sometimes work and sometimes not; This seems to be rather intermittent and searches of 2 chars will often work where as larger words always fail.
I created a quick PHP page to test this obviously the list of forums will need to be changed to test this correctly.
<?PHP
require_once('../../config.php');
require_once('lib.php');
$sql = "SELECT p.*, d.forum, u.firstname, u.lastname, u.email, u.picture, u.imagealt FROM mdl_forum_posts p, mdl_forum_discussions d, mdl_user u WHERE ((p.message LIKE '%finished%') OR (p.subject LIKE '%finished%') ) AND p.discussion = d.id AND p.userid = u.id AND ((d.forum IN (89,90,91,92,93,227,294,297,79,78,87,88,254,303,288,95,96,94,97,76,98))) ORDER BY p.modified DESC";
if($records = get_records_sql($sql)){
echo "has records<br>";
}else{
echo "no records<br>";
}
$sql = "SELECT mu.id, mu.firstname FROM mdl_user mu";
if($records = get_records_sql($sql)){ echo "has records<br>"; } }else{
echo "no records 1<br>";
}
?>
i found that removeing everything after order by caused the first query to return ok but the second then fails. I then found that removeing the IN () completley made both querys return correctly.
I hope you can shed some light on this.
Activity
- All
- Comments
- History
- Activity
- Source
- Test Sessions
Hi Steven,
various things:
1) You must check what PHP driver is being used in your site. To do so, go to your moodle site, and under admin->server you'll see one PHPInfo. Select it and, look for "mssql" in that page. It should show something like "Version: FreeTDS". If you aren't running FreeTDS version, it's highly probable that you are using the OLD, really old, MS driver. That won't work at all with Moodle because it lacks support for a lot of things. You can find more information about how to install the correct FreeTDS version of the MSSQL driver here:
http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows
2) Never use "mssql" as Moodle driver, use "mssql_n" instead. It supports multibyte (unicode) fields, while the "mssql" one is not recommended for production use at all.
3) Once 1) and 2) are fulfilled, you can execute the tests available at Admin->Experiemental->XMLDB Editor (one small link in the header of that page) to see if you PHP driver (1) and Moodle driver (2) are working ok. Any error in that page will mean something is incorrect, hence you're going to run under problems.
4) Check that the queries above don't use to return thousands of records. The get_records_sql() function loads all the result in memory and, if you site has too many records, you can run out of memory. Check for errors in web server logs/php logs.
Hope this helps. As I think this is a configuration thing... I'm going to close this as "Cannot reproduce". Fell free to reopen it if, after correct configuration, you continue having problems.
Ciao 
assigning correctly. - also Steven - you don't seem to have mentioned the method of connecting to MS SQL - are you using the FreeTDS method?