ISO 8601 log datetime string not compatible with some MySQL servers

[johnolek]

We encountered an issue where Stream 3.1.1 would not log anything at all on one of our servers. We were seeing MySQL Errors like this:

MysqlError: Incorrect datetime value: '2017-01-04T16:56:56.956+0000' for column 'created' at row 1

It looks like Stream uses the wp_stream_get_iso_8601_extended_date function to generate a datetime string for this column. This type of string includes more data than MySQL is expecting. Our local vagrant boxes were able to handle the ISO 8601 datetime string fine, but some of our other servers appear to be set up to be stricter about the data they accept, triggering the error above, and preventing any data from being saved by Stream at all.

We created a workaround by filtering the data array before it's inserted into the database using the wp_stream_record_array filter, converting it to a format friendlier to the MySQL DATETIME column, specifically Y-m-d H:i:s. This resolved it for us. However, this seems like something that could be causing other people headaches as well so it seemed worth bringing up.

[greggh]

Hey @johnolek I just ran into this same problem. Any chance you have that filter code laying around still?

[johnolek]

Hey @greggh,

Sorry for not including it to begin with! Here's what we used:

function fix_log_datetime( $data_array ) {
    if ( ! isset( $data_array['created'] ) ) {
        return $data_array;
    }

    $created_datetime = new \DateTime( $data_array['created'] );

    $data_array['created'] = $created_datetime->format( 'Y-m-d H:i:s' ); // MySQL DateTime format

    return $data_array;
}

add_filter( 'wp_stream_record_array', 'fix_log_datetime' );

[greggh]

That's awesome, thanks for the fix.