MSSQL SmallDateTime Field Can Cause PHP To Silently Crash

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!