Menu Close

Invalid datetime value in mysql 5.7

Mysql 5.7 added a validation on datetime so blank value or ‘0000-00-00 00:00:00’ are no more valid dates and it will give you an error if you try to edit/alter a column with this value from console, phpmyadmin or from your applications.
For avoid this issue, you should set every datatime column as NULLABLE, but if you have existing databases and existing applications it’s lots of work.
A workaround for this problem is edit “sql_mode” option in your code, like prestashop:
https://github.com/PrestaShop/PrestaShop/pull/4507/files

But, if you want that phpmyadmin continues to work fine, you have to edit mysql conf file and set sql_mode persistently.

If you’re using the docker official mysql image you can fix with these steps:

1. Locate container ID of mysql:

root@ubuntu:/# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7990b9f80e73 phpmyadmin/phpmyadmin:latest "/run.sh phpmyadmin" 25 hours ago Up 25 hours 0.0.0.0:81->80/tcp r-SmartDomotik_panda-myadmin_1
6292f90ed61b mysql:latest "docker-entrypoint.sh" 25 hours ago Up 12 seconds 0.0.0.0:3306->3306/tcp 96d959f4-cd5e-40d8-95ed-7734be3e0dc1

2. Enter in the container:

root@ubuntu:/# docker exec -it 6292f90ed61b bash

3. Install nano:

root@6292f90ed61b:/# apt update
root@6292f90ed61b:/# apt install nano

4. Edit conf file:

root@6292f90ed61b:/# nano /etc/mysql/conf.d/docker.cnf

[mysqld]
skip-host-cache
skip-name-resolve
sql-mode="NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO"

5. Save and restart container:

root@6292f90ed61b:/# exit
root@ubuntu:/# docker restart 6292f90ed61b

 

Posted in Linux, News, Web Development

Leave a Reply

Your email address will not be published. Required fields are marked *