Question

How (and where) to configure mysql on Ubuntu 20.04

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?


Submit an answer


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!

Sign In or Sign Up to Answer

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
Site Moderator
Site Moderator badge
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

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel