MySQL database keeps being corrupted on re-opening workspace

[kunxin-chor]

Bug description

I've created on a gitpod workspace base on this template: https://github.com/kunxin-chor/gitpod-full-template.

The template had been working well for the past year and already started giving issues the beginning of this week. In case it's relevant, the workspace was being publicly shared.

Everything works well, until the workspace restarts. On the first restart, all new databases and users created were gone, but they were still on the hard disk, because attempting to recreate the database fails.

On the second restart, MySQL is unable to start. Here's the error log:

2022-04-01T02:55:45.529650Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) initializing of server in progress as process 2684
2022-04-01T02:55:45.643916Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-01T02:55:50.373250Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-01T02:56:01.743840Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-04-01T02:56:14.231599Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.28-0ubuntu0.20.04.3).
2022-04-01T02:56:25.783466Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) starting as process 2736
2022-04-01T02:56:25.827579Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-01T02:56:26.426965Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-01T02:56:26.629721Z 0 [ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
2022-04-01T02:56:26.629775Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
2022-04-01T02:56:27.051070Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-04-01T02:56:27.051127Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-04-01T02:56:27.082989Z 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.
2022-04-01T02:56:27.113651Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-0ubuntu0.20.04.3'  socket: '/tmp/tmp.Z39YBNzzHY/mysqld.sock'  port: 0  (Ubuntu).
2022-04-01T02:56:27.203440Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.28-0ubuntu0.20.04.3).
2022-04-01T02:56:28.917888Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-0ubuntu0.20.04.3)  (Ubuntu).
2022-04-01T02:56:29.855421Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) starting as process 2805
2022-04-01T02:56:29.960507Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-01T02:56:31.271649Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-01T02:56:31.500007Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
2022-04-01T02:56:31.708461Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-04-01T02:56:31.708529Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-04-01T02:56:31.736260Z 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.
2022-04-01T02:56:31.805802Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.28-0ubuntu0.20.04.3).
2022-04-01T02:56:31.806976Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060
2022-04-01T02:56:33.272553Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-0ubuntu0.20.04.3)  (Ubuntu).
2022-04-14T05:29:47.110900Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) starting as process 390
2022-04-14T05:29:47.235392Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-14T05:29:47.240998Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2022-04-14T05:29:47.241050Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 2 = ['organic/migrations.ibd', 'organic2/migrations.ibd'] 
2022-04-14T05:29:47.241081Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4 = ['organic/brands.ibd', 'organic2/brands.ibd'] 
2022-04-14T05:29:47.241099Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 5 = ['organic/categories.ibd', 'organic2/categories.ibd'] 
2022-04-14T05:29:47.241116Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 7 = ['organic/products.ibd', 'organic2/products.ibd'] 
2022-04-14T05:29:47.241136Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 8 = ['organic/tags.ibd', 'organic2/tags.ibd'] 
2022-04-14T05:29:47.241156Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 11 = ['organic/products_tags.ibd', 'organic2/products_tags.ibd'] 
2022-04-14T05:29:47.241175Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 12 = ['organic/users.ibd', 'organic2/users.ibd'] 
2022-04-14T05:29:47.241198Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2022-04-14T05:29:47.241232Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-04-14T05:29:47.241478Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-04-14T05:29:47.241626Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-14T05:29:47.243323Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-0ubuntu0.20.04.3)  (Ubuntu).
2022-04-14T05:29:48.855424Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) starting as process 862
2022-04-14T05:29:48.875793Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-14T05:29:48.885435Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2022-04-14T05:29:48.885467Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 2 = ['organic/migrations.ibd', 'organic2/migrations.ibd'] 
2022-04-14T05:29:48.885482Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4 = ['organic/brands.ibd', 'organic2/brands.ibd'] 
2022-04-14T05:29:48.885494Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 5 = ['organic/categories.ibd', 'organic2/categories.ibd'] 
2022-04-14T05:29:48.885513Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 7 = ['organic/products.ibd', 'organic2/products.ibd'] 
2022-04-14T05:29:48.885531Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 8 = ['organic/tags.ibd', 'organic2/tags.ibd'] 
2022-04-14T05:29:48.885544Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 11 = ['organic/products_tags.ibd', 'organic2/products_tags.ibd'] 
2022-04-14T05:29:48.885559Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 12 = ['organic/users.ibd', 'organic2/users.ibd'] 
2022-04-14T05:29:48.885576Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2022-04-14T05:29:48.885603Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-04-14T05:29:48.885724Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-04-14T05:29:48.885844Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-14T05:29:48.886819Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-0ubuntu0.20.04.3)  (Ubuntu).
2022-04-14T05:29:55.752707Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) starting as process 1232
2022-04-14T05:29:55.763825Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-14T05:29:55.772661Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2022-04-14T05:29:55.772719Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 2 = ['organic/migrations.ibd', 'organic2/migrations.ibd'] 
2022-04-14T05:29:55.772743Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4 = ['organic/brands.ibd', 'organic2/brands.ibd'] 
2022-04-14T05:29:55.772763Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 5 = ['organic/categories.ibd', 'organic2/categories.ibd'] 
2022-04-14T05:29:55.772783Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 7 = ['organic/products.ibd', 'organic2/products.ibd'] 
2022-04-14T05:29:55.772806Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 8 = ['organic/tags.ibd', 'organic2/tags.ibd'] 
2022-04-14T05:29:55.772860Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 11 = ['organic/products_tags.ibd', 'organic2/products_tags.ibd'] 
2022-04-14T05:29:55.772887Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 12 = ['organic/users.ibd', 'organic2/users.ibd'] 
2022-04-14T05:29:55.772912Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2022-04-14T05:29:55.772944Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-04-14T05:29:55.773111Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-04-14T05:29:55.773221Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-14T05:29:55.773957Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-0ubuntu0.20.04.3)  (Ubuntu).

Steps to reproduce

1. Create a Github repo based on: https://github.com/kunxin-chor/gitpod-full-template
2. Create a database in mysql
3. Restart the workspace (or let it sleeps normally)
4. Log into mysql terminal and the database is gone
5. Attempting to recreate the database will fail with the database already exists
6. Restart the workspace (or let it sleeps normally)
7. MySQL won't be able to start (see the error log below)

Workspace affected

https://kunxinchor-tgc16framewor-gc3ug1wz0xf.ws-us39a.gitpod.io/

Expected behavior

MySQL should start properly

Example repository

https://github.com/kunxin-chor/tgc16-framework

Anything else?

No response

[axonasif]

Hey @kunxin-chor, I just opened your provided repository, stopped and re-opened twice. But unable to reproduce your errors on my terminal. My databases still remain and I can see them. I suggest that you try again.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[atduarte]

Given the non-reproducibility and staleness, will close this issue.