22

I have an app with Doctrine 1 and I generate update_datetime fields for objects via new Zend_Date->getIso(). It worked just fine for years, but now I got a new notebook and Doctrine tries to insert a DATETIME fields as a string "2013-07-12T03:00:00+07:00" instead of normal MySQL datetime format "2013-07-12 00:00:00" which is totally weird.

The very same code runs just fine on another computer. Everything is nearly identical – MySQL 5.6.12, PHP 5.3.15 on both. Any idea where should I look?

Fatal error: Uncaught exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2013-07-12T03:00:00+07:00' for column 'nextrun' at row 1' in library/Doctrine/Connection.php:1083

UPDATE

Ok with the help from StackOverflow community I finally solved it. The problem was with STRICT_TRANS_TABLES in sql_mode variable. But changing it in /etc/my.cnf seemed not enough, so I had to run mysql -uroot and type the following:

set sql_mode=NO_ENGINE_SUBSTITUTION; set global sql_mode=NO_ENGINE_SUBSTITUTION;

Thus removing STRICT_TRANS_TABLES

UPDATE2 How to get rid of STRICT forever? How to get rid of STRICT SQL mode in MySQL

Community
  • 1
  • 1
firedev
  • 20,898
  • 20
  • 64
  • 94
  • I'd look at your `php.ini` configuration on either machine. Something tells me you've set a default timezone on one and not the other. – Phil Jul 12 '13 at 03:38
  • Added `date.timezone` - same. Any other ideas? – firedev Jul 12 '13 at 03:44
  • When you compare the PHP and Mysql Server versions and the Mysql client library versions (and the types), which differences do you see? One way to analyze this problem is to find the difference of the configuration because if there would have been the same configuration, the application would run the same. *Nearly* identical can be different. Is one server for example using mysqlnd under the hood? – hakre Aug 02 '13 at 05:58
  • Take especially a look for the Mysql Server version and compare that mysql configuration. This looks like an issue with which values that mysql server accepts as datetime. This can be a mysql server configuration issue. – hakre Aug 02 '13 at 06:05
  • The update on the question itself did the trick for me – Dimitris Baltas Mar 12 '15 at 10:00

4 Answers4

12

If it exists, you can try removing STRICT_TRANS_TABLES from sql-mode in your my.ini.

This can cause this error with a datetime string value containing the format you have not being converted to mysql datetime. This my.ini change was reported as a fix in:

hakre
  • 193,403
  • 52
  • 435
  • 836
  • Oh my god, that was it! Thank you! But how? `STRICT_TRANS_TABLES` If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. (Implemented in MySQL 5.0.2) – firedev Aug 05 '13 at 05:11
  • 1
    Turns out this was only half a fix, is there some option inside Zend or Doctrine that might set this on runtime? I have stumbled upon the same issue in another part of the app now. – firedev Aug 05 '13 at 11:04
2

Date constants in zend are determined from sniffing out locale in this order (form zend_locale comments)

1. Given Locale
2. HTTP Client
3. Server Environment
4. Framework Standard

I'm thinking the difference between the two systems is going to be reflected in the Server Environment.

To correct and avoid this problem in the future you can specify the locale options within your application.ini using these configuration directive.

resources.locale.default = <DEFAULT_LOCALE>
resources.locale.force = false
resources.locale.registry_key = "Zend_Locale"

The locale should be set to a string like en_US

Zend_Locale specificly sniffs the locale from the environment from a call to setlocale and parsing the results.

Orangepill
  • 24,500
  • 3
  • 42
  • 63
  • I have `Zend_Locale::setDefault('en_US')` shouldn't this be enough? – firedev Jul 19 '13 at 06:09
  • 1
    If my assumptions where correct then it should have been ... i will keep digging, out of curiosity what OS are you using and if available what do you get for `LC_TIME` if you run `locale` from the command line (should be available in linux and maybe mac) – Orangepill Jul 20 '13 at 04:51
  • 1
    The issue is on Mac OS 10.8.9 here is locale output: `LC_TIME="en_GB.UTF-8"` the same is at the other computers where it does work fine. Everything is the same as far as I can see. – firedev Jul 20 '13 at 10:59
  • In every version of ZF 1.x that I have the return of Zend_Date::getIso is in the format 2013-07-12T03:00:00+07:00. I can't adjust anything in the evironment to get it to output a differently outputted string. Can you try to run `echo (new Zend_Date)->getIso();` and see if you get differently formatted dates between the working and non-working hosts. – Orangepill Jul 22 '13 at 17:42
  • Do you have the field in question explicitly defined in the tableDefinition as a datetime in your model? – Orangepill Jul 22 '13 at 18:39
  • It is actually `timestamp` as follows: `$this->hasColumn('nextRun', 'timestamp');`. And this is a working system, I can start it on another computer and it works. – firedev Jul 23 '13 at 03:30
  • what output do you get from getIso on the working environments – Orangepill Jul 31 '13 at 08:46
  • `Zend_Date->getIso()` returns exactly the same value on both systems: `2013-08-01T12:16:44+07:00`, I tried to copy php.ini over - changes nothing. – firedev Aug 01 '13 at 05:21
2

This is caused by Zend not setting your timestamp format to one that matches what MySQL is expecting. You could disable STRICT mode in MySQL, but this is a hack and not a solution (MySQL will attempt to guess what the date you're entering is).

In Zend you can set the datetime format to what MySQL is expecting to solve this:

$log = new Zend_Log ();
$log->setTimestampFormat("Y-m-d H:i:s");
Developer
  • 2,021
  • 12
  • 11
  • This sets it for the log... not everywhere – Orangepill Aug 02 '13 at 22:04
  • Sure does @Orangepill, it's an example of how to set the timestamp format. Unfortunately I'm not familiar enough with Zend to know how to set it globally. – Developer Aug 02 '13 at 22:32
  • it's in Zend_Locale... I already barked up that tree. And getIso creates the same style timestamp indepentant of locale settings. Hakre might have something there. If all things are the same except the environment then the issue must be differences in configuration. – Orangepill Aug 02 '13 at 22:36
  • harke's response definitely will 'fix' it, but it is not a real solution, just a cover up for the problem. – Developer Aug 02 '13 at 22:39
0

Ok with the help from StackOverflow community I finally solved it. The problem was with STRICT_TRANS_TABLES in sql_mode variable. But changing it in /etc/my.cnf seemed not enough, so I had to run mysql -uroot and type the following:

set sql_mode=NO_ENGINE_SUBSTITUTION; set global sql_mode=NO_ENGINE_SUBSTITUTION;

Thus removing STRICT_TRANS_TABLES

------this answer works

wangxuan
  • 1