MySQL Error - Invalid date and time format: 1292 Invalid date and time value: '' for column

Question

I use mysql in Os X 10.9.2, and when I try to run my php site in local, I have this error:

Invalid date and time format: 1292 Invalid date and time value: '' for column nameOfTheColumn

The error occurs when I tried to insert an empty value in the datetime field. The site works fine in production and on some other local computers, so the problem is in the database, and I have to solve it in my local database. Reading about the problem, I read that mysql can run in strict mode, so I edit /usr/local/mysql/my.cnf and I will comment on this line:

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

I am restarting the computer and still not working, but according to the online documentation and other questions, it is, but I would like to know what exactly I need to change in order for my application to work. Repeat, the code works during the production process, windows with xampp, etc., therefore, cannot be changed, because it is long-lasting and easy to work in local. I need the correct change in the MYSQL database.

Thanks in advance!

Answer 1

It looks like the default sql_mode for your osx MySQL instance is set to reject invalid dates.

You can try to run this command:

 SET SESSION sql_mode='ALLOW_INVALID_DATES' 

at session initialization.

Answer 2

you can change your column to allow null

 ALTER TABLE table_name MODIFY datetime_column_name datetime; 

Columns are zero by default if the column is not declared NOT NULL.

Answer 3

I finally found a solution, actually I was on the way, the solution is to comment on sql_mode, but the file is in /etc/my.cnf instead of / usr / local / mysql / my.cnf. If you do not comment on this line and try to insert an empty value, an error occurs, but if you comment on this, the empty values ​​will become "0000-00-00 00:00:00"