Our Blog

Related Posts

Symfony's global community of developers and robust toolset made it a great choice for a recent project to build an international website.

How to achieve Pantheon's one-click-update functionality for Drupal and Wordpress while retaining your git history using git-graft.

Creative Director Maya Brueck recounts Pixo's recent project management shift away from the Waterfall model to a more collaborative workflow.

Popular Tags

Recent Comments

Brendon Carvalho
4.26.2018 - MSSQL Long Text Field Truncated In PHP
Hire Volusion Developers
4.7.2018 - Drupal 8 Roadmap

Cameron Macintosh

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!

Related Posts

Symfony's global community of developers and robust toolset made it a great choice for a recent project to build an international website.

How to achieve Pantheon's one-click-update functionality for Drupal and Wordpress while retaining your git history using git-graft.

Creative Director Maya Brueck recounts Pixo's recent project management shift away from the Waterfall model to a more collaborative workflow.

Recent Comments

Brendon Carvalho
4.26.2018 - MSSQL Long Text Field Truncated In PHP
Hire Volusion Developers
4.7.2018 - Drupal 8 Roadmap

Interested in working with us?
CONTACT US