Disable mysql value checking

Asked

Viewed 42 times

0

I have a system running on a VPS that was done by me on localhost (but on another computer). This system was made in php and connects to a mysql database in the VPS itself. Now, I am cloning everything on my machine to make some adjustments, however, my local database is not behaving in the same way.

In the database, there is a table with the data_f field where I specify the completion date of a certain activity. In VPS, an Insert in the database with the value for data_f = "" does not result in any error, but on my machine, returns an error.

INSERT INTO atividade (nome, data_i, data_f) VALUES ('teste', '2021-04-28 10:00:00', '');

Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column `beto`.`atividade`.`data_f`

Column accepts NULL fields.

  • I’ve already updated my mysql server
  • I don’t think it has anything to do with PHP, since, if on my local machine, I use the VPS database, everything works correctly. Either way, they both run in version 7.4
php mysql pdo
  • 1

    Have you tried to adjust the operation mode of the database to ALLOW_INVALID_DATES?

  • I will read about tonight, but I think it is valid to add that not only happens with datetime, I saw it happen also with a column int in the same situation, sending '' and returns error for not being integer.

1 answer

0

Reading in the document sent by Augusto Vasques, I checked what was configured in the global sql_mode of the VPS, and I noticed that in my place there were two different flags that were apparently doing this check.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

SET GLOBAL sql_modes = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.