MySQL won't start (error connecting)

[kunxin-chor]

Describe the bug

mysql -u -root won't work

Steps to reproduce

1. Create a new repo on Github
2. Run Gitpod
3. Choose to setup project
4. Choose 'Default+MySQL' workspace
5. Close workspace
6. Start a new workspace
7. At terminal, type in mysql -u root

Expected behavior

MySQL command line to show up

Additional information

2020-04-15T15:15:59.847625Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) initializing of server in progress as process 770
2020-04-15T15:16:02.696460Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-04-15T15:16:04.315075Z 5 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.19-0ubuntu4).
2020-04-15T15:16:07.914237Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) starting as process 823
2020-04-15T15:16:08.417015Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-15T15:16:08.419859Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-04-15T15:16:08.438983Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19-0ubuntu4'  socket: '/tmp/tmp.ZIXXiJG3rt/mysqld.sock'  port: 0  (Ubuntu).
2020-04-15T15:16:09.996504Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-0ubuntu4)  (Ubuntu).
2020-04-15T15:16:11.113455Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) starting as process 889
2020-04-15T15:16:11.505743Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-15T15:16:11.508814Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-04-15T15:16:11.532407Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.19-0ubuntu4).
2020-04-15T15:16:12.666035Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-0ubuntu4)  (Ubuntu).
2020-04-15T17:23:41.618212Z 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.
2020-04-15T17:23:41.621401Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) initializing of server in progress as process 310
2020-04-15T17:23:47.866970Z 0 [Warning] [MY-010161] [Server] You need to use --log-bin to make --expire_logs_days work.
2020-04-15T17:23:48.379572Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.
2020-04-15T17:23:48.379801Z 0 [ERROR] [MY-013455] [Server] The newly created data directory /workspace/mysql/ by --initialize is unusable. You can remove it.
2020-04-15T17:23:48.380125Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-15T17:23:49.510715Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-0ubuntu4)  (Ubuntu).
2020-04-15T17:23:50.136874Z 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.
2020-04-15T17:23:50.142022Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) starting as process 355
mysqld: Table 'mysql.plugin' doesn't exist
2020-04-15T17:23:50.915481Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.
2020-04-15T17:23:51.038016Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T17:23:51.079236Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T17:23:51.088182Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-15T17:23:51.094547Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2020-04-15T17:23:51.094984Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2020-04-15T17:23:51.095200Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2020-04-15T17:23:51.095711Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.
2020-04-15T17:23:51.096167Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-15T17:23:52.382345Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-0ubuntu4)  (Ubuntu).
2020-04-15T17:24:02.885376Z 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.
2020-04-15T17:24:02.890743Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) starting as process 1462
mysqld: Table 'mysql.plugin' doesn't exist
2020-04-15T17:24:04.288274Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.
2020-04-15T17:24:04.502258Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T17:24:04.571220Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T17:24:04.587256Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-15T17:24:04.611645Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2020-04-15T17:24:04.612810Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2020-04-15T17:24:04.613159Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2020-04-15T17:24:04.614476Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.
2020-04-15T17:24:04.615259Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-15T17:24:05.824464Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-0ubuntu4)  (Ubuntu).
2020-04-15T17:24:07.255943Z 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.
2020-04-15T17:24:07.261040Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu4) starting as process 1815
mysqld: Table 'mysql.plugin' doesn't exist
2020-04-15T17:24:08.200129Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.
2020-04-15T17:24:08.309110Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T17:24:08.395018Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-15T17:24:08.407560Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-15T17:24:08.419400Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2020-04-15T17:24:08.419898Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2020-04-15T17:24:08.420107Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.

Example repository

https://github.com/kunxin-chor/gitpod-mysql-trial2

[lechien73]

Having the same problem since the Ubuntu 20.04 upgrade.

[jankeromnes]

Many thanks for reporting this. I'm not sure exactly what causes the problem, but it's likely due to the upgrade to MySQL 8 (side-effect of the Ubuntu 20.04 upgrade).

I'll try to find what exactly is incompatible with MySQL 8 in our default MySQL configuration: https://github.com/gitpod-io/workspace-images/tree/master/mysql and then fix it.

[jankeromnes]

Also, it seems that @lechien73 was able to fix / work around this problem in this msyql.cnf by removing a few config parameters:

diff --git a/mysql/mysql.cnf b/mysql/mysql.cnf
index b656494..3762ea9 100644
--- a/mysql/mysql.cnf
+++ b/mysql/mysql.cnf
@@ -12,7 +12,6 @@ datadir               = /workspace/mysql
 tmpdir         = /tmp
 lc-messages-dir        = /usr/share/mysql
 skip-external-locking
-bind-address           = 127.0.0.1
 
 key_buffer_size                = 16M
 max_allowed_packet     = 16M
@@ -20,12 +19,7 @@ thread_stack         = 192K
 thread_cache_size   = 8
 
 myisam-recover-options  = BACKUP
-query_cache_limit          = 1M
-query_cache_size        = 16M
 
 general_log_file        = /var/log/mysql/mysql.log
 general_log             = 1
 log_error               = /var/log/mysql/error.log
-
-expire_logs_days       = 10
-max_binlog_size     = 100M

Maybe we could try removing some of these as well. I'm not even sure why we have them to be honest.

[jankeromnes]

The MySQL 8 configuration originally proposed by @eryjus in https://github.com/gitpod-io/workspace-images/pull/100/files#diff-f8507bef6331e741bc1ebaf18bcfab35 has similar notable differences:

diff --git a/mysql/mysql.cnf b/mysql/mysql.cnf
index b656494..e81be0b 100644
--- a/mysql/mysql.cnf
+++ b/mysql/mysql.cnf
@@ -11,6 +11,7 @@ basedir               = /usr
 datadir                = /workspace/mysql
 tmpdir         = /tmp
 lc-messages-dir        = /usr/share/mysql
+lc-messages = en_US
 skip-external-locking
 bind-address           = 127.0.0.1
 
@@ -20,12 +21,7 @@ thread_stack         = 192K
 thread_cache_size   = 8
 
 myisam-recover-options  = BACKUP
-query_cache_limit          = 1M
-query_cache_size        = 16M
 
 general_log_file        = /var/log/mysql/mysql.log
 general_log             = 1
 log_error               = /var/log/mysql/error.log
-
-expire_logs_days       = 10
-max_binlog_size     = 100M

[jankeromnes]

I guess we'll remove query_cache_limit, query_cache_size, expire_logs_days and max_binlog_size from our config. That could fix it. I'll make a PR.

[lechien73]

I guess we'll remove query_cache_limit, query_cache_size, expire_logs_days and max_binlog_size from our config. That could fix it. I'll make a PR.

The error log specifically showed query_cache_limit as being an unknown option, which could be part of the new MySQL implementation. I'd also removed the bind_address, since localhost was assumed if not specified.

[jankeromnes]

I've just merged the fix to deploy a new gitpod/workspace-mysql as quickly as possible. E.T.A. ~60 minutes.

(No need to wait for CI on the PR, because Gitpod's MySQL image is already broken, and I'm pretty confident that this change can fix it.)

Thanks a lot for your help @lechien73 @kunxin-chor and @eryjus! 🙏