Pixo is currently developing a web-based publication workflow application for American Library Association’s Choice Magazine and Choice Reviews Online utilizing the PHP framework Symfony to build a web based publication workflow application. A major component includes migrating 30+ years of legacy data out of a MSSQL database into a redesigned SQL schema in Symfony. Early in the project, I encountered this particularly nasty problem regarding smallDateTime fields as I was setting up the MSSQL side of the migration using the default MSSQL PHP driver.
The Mysterious Dying Process
I had already created 3 tables and dozens of fields without a problem. Then without warning or fanfare, my program would unceremoniously terminate without any kind of error message or stack trace. I was perplexed. What would cause my program to die without warning? Here is a generalized version of the function I wrote to interface with the MSSQL database:
function extract_data($mssql_password, $query_args) { $con = mssql_connect(MIGRATE_MSSQL_SERVER, MIGRATE_MSSQL_USERNAME, $mssql_password); if (!mssql_select_db(MIGRATE_MSSQL_DATABASE)) { print "Failed to select db '" . MIGRATE_MSSQL_DATABASE . "'\n"; return ""; } // $sql= buildQuerySQL($query_args); $query = mssql_query( $sql ); while ($row = mssql_fetch_assoc($query)) { foreach($row as $key=>$value) { if ($value==" ") { $row[$key] = ""; } } $returndata[] = $row; } $queryResults = runQuery($sql); // save_data_to_file($queryResults, $filename); mssql_free_result($query); mssql_close($con); return $queryResults; }
I began debugging with echo/var_dump statements in an effort to pin down the offending line of code. The script seemed to die either when I called mssql_free_result()
or when extract_data()
returned. Odd.
I could verify that I was getting data out of the database for the fields I just added. I could print it out to the screen and it was appearing in the file that save_data_to_file()
generates. I did notice that it was a date in a format I wasn’t expecting – “Mar 1 2008 12:00:00:000AM” rather than “2008-03-01”. I didn’t quite know where this was pointing me. I tried adding other fields with tons of content without reproducing the error. I even tried isolating the issue to a particular database record. Again, no luck.
Then I realized that this was the first date field I was attempting to pull from MSSQL, type = smalldatetime
to be precise. What if I tried adding a different field of the same type? The script died silently. Ah ha! Finally a way to reproduce the issue. This indicated to me that there was a problem centered around the release of the query object when there was a smalldatetime field involved.
Knowing this, I was finally able to locate a bug report describing a related issue: https://bugs.php.net/bug.php?id=48908. Both recommended solutions in that post worked for me: type casting inside the SQL statement and adding mssql.datetimeconvert = Off
to php.ini. I decided on the latter solution because I did not want to handle date fields differently while building the SQL query if I could avoid it… I would do that later when dealing with the individual fields.
I spent 4 hours tearing my hair out trying to pin this down. I’m just thankful I wasn’t the one who found it in the first place.
Alternatives
There is an alternative PHP driver to MSSQL: SQLSRV. However, this wasn’t an option for us because SQLSRV is only supported on windows, and we were working in a Linux (Ubuntu) environment.
Final Thoughts
Seemingly obscure bugs/behaviors like this can really kill one’s forward momentum. I hope this post will help cut down your time to discovery should you encounter these particularly nasty bugs. Cheers!
Recent Comments
4.26.2018 - MSSQL Long Text Field Truncated In PHP
4.22.2018 - MSSQL SmallDateTime Field Can Cause PHP To Silently Crash
4.12.2018 - VIN Barcode Scanner Product Innovation
4.7.2018 - Drupal 8 Roadmap
3.24.2018 - VIN Barcode Scanner Product Innovation