1

I upgraded from Ubuntu 18 to Ubuntu 20. mySQL upgrade failed during the upgrade. After the upgrade, I again tried to install mysql-server. I’m getting the below error.

ERROR: Unable to start MySQL server:
2021-05-29T02:51:22.812281Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2021-05-29T02:51:22.812291Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2021-05-29T02:51:22.813552Z 0 [ERROR] [MY-010119] [Server] Aborting

Command used: sudo apt install mysql-server

I used the above command after upgrade to Ubuntu20. I even tried to edit the configuration, but that didn’t help. Below are the contents of /etc/mysql/mysql.cnf:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

I found a similar question here: https://askubuntu.com/questions/1296168/mysql-fail-after-ubuntu-20-04-upgrade

donishere
  • 98
  • 8
  • Could you try to write in `mysql.cnf` as following instead `sqlmode=STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION` . Without `"` and according to the [valid values list here?](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode) – FanoFN May 29 '21 at 04:06
  • Editing my question too. I did, but it didn't work. Below is the content: !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" – donishere May 29 '21 at 04:08
  • Maybe this https://stackoverflow.com/a/37978275/10910692 ? – FanoFN May 29 '21 at 04:21

1 Answers1

5

Thank you @FANO_FN. This was the line in /etc/mysql/mysql.conf.d/mysqld.cnf which needed to be corrected. mysql8 doesn't have the value of NO_AUTO_CREATE_USER sql_mode anymore. So, I removed that from the list of values and it started working.

The final values look like below:

sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

donishere
  • 98
  • 8
  • Wait, were you trying to use those modes? because commenting it means it's not in use.. – FanoFN May 29 '21 at 07:15
  • I just wanted to get it working. I now understood why it was erroring out. The value 'NO_AUTO_CREATE_USER ' is no longer part of myql8 – donishere May 30 '21 at 09:46
  • Super helpful - I had a MySQL update fail when upgrading Ubuntu (to 20.04) and removing NO_AUTO_CREATE_USER got things back on track – toby1kenobi Sep 15 '22 at 14:57