EllisLab text mark
Advanced Search
     
MSSQL, Blank Pages, and Smalldatetime
Posted: 10 February 2012 05:09 PM
Avatar
Joined: 2009-03-24
111 posts

Hi everyone,

I’m hoping someone else can verify this, I’m not sure if there are confounding factors or not because the query I was running it with was pretty complicated (like 30 joins… ewww)

However, In trying to figure out what was causing the issue, I finally stumbled upon one of the SELECT columns was causing it.  I did a binary search, and just kept cutting it in half until I found the troubled area.

Turns out the cause was a ‘smalldatetime’ column.  I deleted it, and the query worked and the page displayed.  Magic, right?

Well to go a step further, since I’m just on a test database, I changed the column type to datetime on SQL Server, and put it back in the query.  It worked again, so I changed it back. Broken.

I’m running Ubuntu 10.04 with Apache 2.2.14 PHP 5.3.2-1ubuntu4.13, mssql. I tried with and without eaccelerator (on a second identical ubuntu box [did a fresh install to make sure I wasn’t crazy]).  The database is MS SQL Server 2008 R2.

I’m not sure if this is a driver bug, mssql(sybase?) driver bug, or just something else.  I’m not sure where to begin with blank pages, because the actual log says the page is being output correctly, the database analyzer says the query completes successfully…  It’s just bizarre.

 Signature 

WIP - WuGen Form Library

 
Posted: 10 February 2012 05:19 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
4324 posts

There are several bugs in the current MSSQL driver.  I know a lot of fixes have been implemented into github, but they aren’t in the current 2.1 version.  You might try the version from github, although, it’s a work in progress until it’s released.

 Signature 
 
Posted: 11 February 2012 12:36 AM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2009-03-24
111 posts

I feel like I have all those changes, but I think I may have just helped out with some of the sqlsrv changes, not the mssql stuff.

I’ll grab the drivers from the mssql driver fork I saw on there and see how it performs and let you know.

 Signature 

WIP - WuGen Form Library

 
Posted: 11 February 2012 01:06 AM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2009-03-24
111 posts

I downloaded the MSSQL drivers from github, but no difference.

Logs from a page that doesn’t load (IS NOT NULL smalldatetime) - blank page

DEBUG 2012-02-10 21:59:44 --> Config Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Hooks Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Utf8 Class Initialized
DEBUG 
2012-02-10 21:59:44 --> UTF-8 Support Enabled
DEBUG 
2012-02-10 21:59:44 --> URI Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Router Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Output Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Security Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Input Class Initialized
DEBUG 
2012-02-10 21:59:44 --> XSS Filtering completed
DEBUG 
2012-02-10 21:59:44 --> XSS Filtering completed
DEBUG 
2012-02-10 21:59:44 --> XSS Filtering completed
DEBUG 
2012-02-10 21:59:44 --> XSS Filtering completed
DEBUG 
2012-02-10 21:59:44 --> XSS Filtering completed
DEBUG 
2012-02-10 21:59:44 --> XSS Filtering completed
DEBUG 
2012-02-10 21:59:44 --> Global POST and COOKIE data sanitized
DEBUG 
2012-02-10 21:59:44 --> Language Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Loader Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Helper loadedurl_helper
DEBUG 
2012-02-10 21:59:44 --> Helper loadeddate_helper
DEBUG 
2012-02-10 21:59:44 --> Helper loadedmssql_helper
DEBUG 
2012-02-10 21:59:44 --> Helper loadedlink_helper
DEBUG 
2012-02-10 21:59:44 --> Helper loadedcommon_helper
DEBUG 
2012-02-10 21:59:44 --> Helper loadedform_helper
DEBUG 
2012-02-10 21:59:44 --> Session Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Helper loadedstring_helper
DEBUG 
2012-02-10 21:59:44 --> Encrypt Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Database Driver Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Session routines successfully run
DEBUG 
2012-02-10 21:59:44 --> Controller Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Model Class Initialized
DEBUG 
2012-02-10 21:59:44 --> Model Class Initialized
ERROR 
2012-02-10 21:59:44 --> SeverityNotice  --> Undefined variabledata /var/www/test/cris/application/controllers/dashboard.php 16
ERROR 
2012-02-10 21:59:44 --> SeverityNotice  --> Undefined variabledata /var/www/test/cris/application/controllers/dashboard.php 17
DEBUG 
2012-02-10 21:59:44 --> File loadedapplication/views/dashboard/index.php
DEBUG 
2012-02-10 21:59:44 --> File loadedapplication/views/template.php
DEBUG 
2012-02-10 21:59:44 --> Language file loadedlanguage/english/profiler_lang.php
DEBUG 
2012-02-10 21:59:44 --> Helper loadedtext_helper
DEBUG 
2012-02-10 21:59:44 --> Final output sent to browser
DEBUG 
2012-02-10 21:59:44 --> Total execution time0.0488 

Logs for a page that loads (NULL smalldatetime) - page displays

DEBUG 2012-02-10 22:00:36 --> Config Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Hooks Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Utf8 Class Initialized
DEBUG 
2012-02-10 22:00:36 --> UTF-8 Support Enabled
DEBUG 
2012-02-10 22:00:36 --> URI Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Router Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Output Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Security Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Input Class Initialized
DEBUG 
2012-02-10 22:00:36 --> XSS Filtering completed
DEBUG 
2012-02-10 22:00:36 --> XSS Filtering completed
DEBUG 
2012-02-10 22:00:36 --> XSS Filtering completed
DEBUG 
2012-02-10 22:00:36 --> XSS Filtering completed
DEBUG 
2012-02-10 22:00:36 --> XSS Filtering completed
DEBUG 
2012-02-10 22:00:36 --> XSS Filtering completed
DEBUG 
2012-02-10 22:00:36 --> Global POST and COOKIE data sanitized
DEBUG 
2012-02-10 22:00:36 --> Language Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Loader Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Helper loadedurl_helper
DEBUG 
2012-02-10 22:00:36 --> Helper loadeddate_helper
DEBUG 
2012-02-10 22:00:36 --> Helper loadedmssql_helper
DEBUG 
2012-02-10 22:00:36 --> Helper loadedlink_helper
DEBUG 
2012-02-10 22:00:36 --> Helper loadedcommon_helper
DEBUG 
2012-02-10 22:00:36 --> Helper loadedform_helper
DEBUG 
2012-02-10 22:00:36 --> Session Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Helper loadedstring_helper
DEBUG 
2012-02-10 22:00:36 --> Encrypt Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Database Driver Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Session routines successfully run
DEBUG 
2012-02-10 22:00:36 --> Controller Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Model Class Initialized
DEBUG 
2012-02-10 22:00:36 --> Model Class Initialized
ERROR 
2012-02-10 22:00:36 --> SeverityNotice  --> Undefined variabledata /var/www/test/cris/application/controllers/dashboard.php 16
ERROR 
2012-02-10 22:00:36 --> SeverityNotice  --> Undefined variabledata /var/www/test/cris/application/controllers/dashboard.php 17
DEBUG 
2012-02-10 22:00:36 --> File loadedapplication/views/dashboard/index.php
DEBUG 
2012-02-10 22:00:36 --> File loadedapplication/views/template.php
DEBUG 
2012-02-10 22:00:36 --> Language file loadedlanguage/english/profiler_lang.php
DEBUG 
2012-02-10 22:00:36 --> Helper loadedtext_helper
DEBUG 
2012-02-10 22:00:36 --> Final output sent to browser
DEBUG 
2012-02-10 22:00:36 --> Total execution time0.0501 
 Signature 

WIP - WuGen Form Library

 
Posted: 11 February 2012 02:22 AM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2009-03-24
111 posts

So, I put in some logging to see what exactly is happening…  Or at least where to start looking.

The query seems to run successfully, and after it runs I can get $query->num_rows().

And actually…  I tried using mssql_connect and mssql_query to bypass the codeigniter code, and it still returns a blank page.  So it looks like the CI drivers aren’t the problem.

mssql_driver + smalldatetime field = blank page / empty response.

So I did some more digging, turns out this is specific to smalldatetime.  Easiest fix is just converting the column in the SELECT and dealing with it there.  Second option is turning off mssql.convertdatetime in php.ini (https://bugs.php.net/bug.php?id=48908)

vi /etc/php5/apache2/php.ini
After this line
:
;
mssql.datetimeconvert On
Add this line
:
mssql.datetimeconvert Off 
 Signature 

WIP - WuGen Form Library

 
Posted: 25 June 2012 09:39 AM   [ # 5 ]   [ Rating: 0 ]
Joined: 2012-06-25
1 posts

Thanks for the datetime fix, this was driving me crazy.
I had several servers which would work with older versions of SQL Server, but not with SQL Server 2008. For some reason, the first server I had set up, I had set this already, but on all the others, I was getting the aforementioned empty pages after the SQL servers were upgraded.