Question

How (and where) to configure mysql on Ubuntu 20.04

Posted July 9, 2021 155 views
MySQL

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?

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.

×
Submit an Answer
1 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

  • Thanks, Bobby.

    I got the current sql_mode by running

     SELECT @@GLOBAL.sql_mode;
    

    in the mysql client. I removed NO ZERO DATES and NO ZERO IN DATES from that result resulting in the following line which I added to the tail end of /etc/mysql/mysql.conf.d/mysqld.cnf:

    sqlmode=“ONLYFULLGROUPBY,STRICTTRANSTABLES,ERRORFORDIVISIONBYZERO,NOENGINESUBSTITUTION”

    I restarted mysql with

     systemctl restart mysql.service
    

    and most of my legacy system started working correctly.
    Thanks again!