We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Problem with saving data in DB

Hi everyone! Here is my problem: We changed DB from MySQL to MariaDB. The User model has attribute 'date' (type - timestamp). Before the changing of DB the column had default value '0000-00-00 00:00:00'. I made a migration and remove default value at all. And when I try to save the user data in DB via model ($user->save()) I get an exception 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column date'. I don't know why it happened. The user has null value for the 'date' column. I tried to set null, tried to set current date - i still get the exception. What can it be? P.S. I see the value '0000-00-00 00:00:00' in snapshot data, but I don't know what it is "snapshot data".



145.0k

Maybe mariadb just don't allow 0000-00-00 as date? It makes since imho.



1.8k

The problem is in mariadb. If you can modify my.ini/my.conf or similar find these directives and delete them: NO_ZERO_IN_DATE and NO_ZERO_DATE.

Restart mysqld service and try again.



125.7k

Just change the column type from DATE to DATETIME.



565
in reply to Dylan Anderson

It's TIMESTAMP, I wrote it

Just change the column type from DATE to DATETIME.



565
in reply to Wojciech Ślawski
edited Feb '20

I said I wrote the current date



565

I repeat my problem again. The attribute 'date' of User model is a column 'date' of users table. It has TIMESTAMP type. I try to write current date: date('Y-m-d H:i:s'), but i still get the exception 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column date'. Why?



77.7k

What are your MariaDB and Phalcon versions?

We are running a production site on MariaDB 10.1.44 and Phalcon 3.4.5 without any problems.



125.7k
in reply to Aleksst95

It's TIMESTAMP, I wrote it

Just change the column type from DATE to DATETIME.

You're right. Sorry for not reading. My answer is pretty much the same though - change the column type from TIMESTAMP to DATETIME. The docs say TIMESTAMP is for a UNIX timestamp and you're passing a MYSQL/MariaDB datetimestamp.



565
in reply to Lajos Bencz

What are your MariaDB and Phalcon versions?

We are running a production site on MariaDB 10.1.44 and Phalcon 3.4.5 without any problems. MariaDB: 10.2.31 Phalcon: 3.4.4



565
in reply to Dylan Anderson
edited Feb '20

You're right. Sorry for not reading. My answer is pretty much the same though - change the column type from TIMESTAMP to DATETIME. The docs say TIMESTAMP is for a UNIX timestamp and you're passing a MYSQL/MariaDB datetimestamp.

And what the problem is with timestamp? I don't understand. Or is it a bug? BTW I fixed the error. I just remove not null from the column in DB. It helped. But why the problem existed, i still don't know, becouse i tried to set the current day date.



125.7k

Look it up: https://mariadb.com/kb/en/timestamp/

It tells you what the expected format is, and it's different than what you were setting. It's like trying to set an INT column to foobar.