MYSQL incorrect DATETIME format

php mysql zend-framework doctrine doctrine-1.2
firedev picture firedev · Jul 11, 2013 · Viewed 19.5k times · Source

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

Answer

hakre picture hakre · Aug 2, 2013

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:

Related questions

php, mysql - Too many connections to database error

Good day to all. I have an odd error. I have created a chat that works like this: questions/answers are inserted into a db every 2 seconds an ajax request is sent to a php script that fetch the new …

Read More
How to print exact sql query in zend framework ?

I have the following piece of code which i taken from model, ... $select = $this->_db->select() ->from($this->_name) ->where('shipping=?',$type) ->where('customer_id=?',$userid); echo $select; exit; // …

Read More
Running MySQL *.sql files in PHP

I have two *.sql files that I use when creating a new web site database. The first file creates all the tables. The second file populates some default records. I would like to execute these files from PHP. I also …

Read More