I am migrating a LAMP system from a Ubuntu 14.04 droplet to a Ubuntu 20.04 droplet. Most of my problems seem to be involved with moving from mysql 5.5.60 to 8.0.25. I’ve identified two main problems so far: I need to remove NO _ ZEROES _ IN _ DATE from the SQL mode, but can’t find where, in Digital Ocean’s LAMP setup, the mysql configuration is stored. It appears that mysql 8.0 does not have default default [sic] values for datatypes. Is there a way to define them? … to repair tables without them? … or to relax the requirement for default values, e.g. to simply use null where there is no default value? These two problems interact in absurd ways. For example, an attempt to alter a column to add a default value fails with an error complaining that existing records contain zeroes in a date: mysql> alter table meetings modify column meetMpgmKey int default 0; ERROR 1292 (22007): Incorrect date value: '2004-05-00' for column 'meetBeginDate' at row 50 More generally, is there a guide to upgrading to mysql 8 that covers issues like this?

- Community
- DigitalOcean
- Community
- DigitalOcean

How (and where) to configure mysql on Ubuntu 20.04

Posted on July 9, 2021

I am migrating a LAMP system from a Ubuntu 14.04 droplet to a Ubuntu 20.04 droplet. Most of my problems seem to be involved with moving from mysql 5.5.60 to 8.0.25. I’ve identified two main problems so far:

I need to remove NO _ ZEROES _ IN _ DATE from the SQL mode, but can’t find where, in Digital Ocean’s LAMP setup, the mysql configuration is stored.
It appears that mysql 8.0 does not have default default [sic] values for datatypes. Is there a way to define them? … to repair tables without them? … or to relax the requirement for default values, e.g. to simply use null where there is no default value?

These two problems interact in absurd ways. For example, an attempt to alter a column to add a default value fails with an error complaining that existing records contain zeroes in a date:

mysql> alter table meetings modify column meetMpgmKey int default 0;
ERROR 1292 (22007): Incorrect date value: '2004-05-00' for column 'meetBeginDate' at row 50

More generally, is there a guide to upgrading to mysql 8 that covers issues like this?

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev

July 10, 2021

Accepted Answer

Hi there,

You can find the MySQL config file here:

/etc/mysql/mysql.conf.d/mysqld.cnf

After making the changes make sure to restart MySQL so that the new changes could take effect.

Not 100% sure if there is an article out there that covers all possible problems, but here is a very good summary on the official MySQL website summarizing the new changes with MySQL 8:

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

Regards, Bobby