Dates entered into the database in the wrong format - insert fails

[mtudor]

ZfcUser fails to enter dates in the database because for some reason the format set in ZfcUser results in the following format:

2012-04-27T02:27:11+01:00

MySQL doesn't like this format.

(P.S. Just raised a couple of issues so to balance that, I do think this looks like a very promising module - and thanks to the devs responsible for creating it. Any pointers as to what's going wrong would be great!)

[mtudor]

Ugly fix is to change the MySQL datetime column to varchar.

[markushausammann]

Chaging all ip and date columns into varchar helps but then you get stuck at authentication when signing in again.

[mtudor]

Markus - so you're essentially seeing issue #43 that I raised. It's as if you haven't typed a correct password (actually, authentication isn't even happening in my guess but it looks like an incorrect password from the user side).

[markushausammann]

I'm now managing to authenticate most times. It's weird but yes, the message is the same as with a wrong password. Much too generic to be of any help.

[adamlundrigan]

The root of this problem can be found in ZfcBase\Mapper\DbMapperAbstract::convertObjectToScalar (Line 112):

if($obj instanceof \DateTime) {
    return $obj->format('Y-m-d\TH:i:sP');
}

However, both MySQL and SQLite expect the format to be 'YYYY-MM-DD HH:MM:SS'.

That said, this issue does not crop up for me. I cloned the HEAD of ZfcUser and ZfcBase into a fresh ZendSkeletonApplication, performed the necessary setup (Zend\Db against MySQL 5.5.21-community), ran through the registration and login processes, and in all cases my user account record was updated with the correct timestamps.

[adamlundrigan]

What version of MySQL server are you using? There was a bug (#7308, fixed in 4.1.14 and 5.0.12) in old version of MySQL which caused it to not accept ISO8601-formatted dates.

[markushausammann]

I'm using Zend Server CE which bundles MySQL Server Version: 5.1.50-community. So that would be after the fix but still I experience these problems.

[mtudor]

Ah hah! Thanks Adam, you've just solved a lot of my issues by pointing out that line of code.

For me, the "P" at the end of that date string was adding "+01:00" (the GMT offset) to the end of the string, which MySQL was rejecting. I removed the "P" and it fixed the issue. Perhaps you are in a different timezone where this isn't an issue? I'm British Summer Time.

Apologies if I'm telling you something you already know, but MySQL is pretty relaxed about delimiters between date an time values, so I could actually insert using "YYYY^MM^DD 09{22{32" if I wanted! The format "Y-m-d\TH:i:s" actually equates to (for the time now, example) "2012-05-08T13:22", which is fine for insertion into MySQL. Adding the GMT offset at the end ("P") breaks it though.

We need a word from one of the official authors to let us know whether the "P" is intended and necessary.

This fix has allowed me to keep the column type as it was, which seems to have fixed my authentication problems too for some reason (although I've also re-cloned the skeleton app, zfcbase and zfcuser, and incorporated all of the outstanding pull requests so it could have been that!).

[adamlundrigan]

I've issued a pull request for ZfcBase to remove the timezone from the dateformat string: https://github.com/ZF-Commons/ZfcBase/pull/17

[EvanDotPro]

Merged @adamlundrigan's ZfcBase PR. This should be resolved now! Thanks, Adam!