• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MSSQL, Blank Pages, and Smalldatetime

#1
[eluser]Kyle Johnson[/eluser]
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.

#2
[eluser]CroNiX[/eluser]
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.

#3
[eluser]Kyle Johnson[/eluser]
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.

#4
[eluser]Kyle Johnson[/eluser]
I downloaded the MSSQL drivers from github, but no difference.

Logs from a page that doesn't load (IS NOT NULL smalldatetime) - blank page
Code:
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 loaded: url_helper
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: date_helper
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: mssql_helper
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: link_helper
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: common_helper
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: form_helper
DEBUG - 2012-02-10 21:59:44 --> Session Class Initialized
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: string_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 --> Severity: Notice  --> Undefined variable: data /var/www/test/cris/application/controllers/dashboard.php 16
ERROR - 2012-02-10 21:59:44 --> Severity: Notice  --> Undefined variable: data /var/www/test/cris/application/controllers/dashboard.php 17
DEBUG - 2012-02-10 21:59:44 --> File loaded: application/views/dashboard/index.php
DEBUG - 2012-02-10 21:59:44 --> File loaded: application/views/template.php
DEBUG - 2012-02-10 21:59:44 --> Language file loaded: language/english/profiler_lang.php
DEBUG - 2012-02-10 21:59:44 --> Helper loaded: text_helper
DEBUG - 2012-02-10 21:59:44 --> Final output sent to browser
DEBUG - 2012-02-10 21:59:44 --> Total execution time: 0.0488

Logs for a page that loads (NULL smalldatetime) - page displays
Code:
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 loaded: url_helper
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: date_helper
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: mssql_helper
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: link_helper
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: common_helper
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: form_helper
DEBUG - 2012-02-10 22:00:36 --> Session Class Initialized
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: string_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 --> Severity: Notice  --> Undefined variable: data /var/www/test/cris/application/controllers/dashboard.php 16
ERROR - 2012-02-10 22:00:36 --> Severity: Notice  --> Undefined variable: data /var/www/test/cris/application/controllers/dashboard.php 17
DEBUG - 2012-02-10 22:00:36 --> File loaded: application/views/dashboard/index.php
DEBUG - 2012-02-10 22:00:36 --> File loaded: application/views/template.php
DEBUG - 2012-02-10 22:00:36 --> Language file loaded: language/english/profiler_lang.php
DEBUG - 2012-02-10 22:00:36 --> Helper loaded: text_helper
DEBUG - 2012-02-10 22:00:36 --> Final output sent to browser
DEBUG - 2012-02-10 22:00:36 --> Total execution time: 0.0501

#5
[eluser]Kyle Johnson[/eluser]
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)

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

#6
[eluser]Unknown[/eluser]
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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.