[Help Wanted] Database Docker container keeps restarting

[edmondsket]

Describe the issue
I'm self-hosting standard Notes using Docker, and i have purchased for a pro subscription for 5years. After using a while, the Standardnotes app keeps popping up login windows like this:

Then I go to check the docker status, turns out the db container is keeping restarting:

the text in the blue label means "restarting"

see db container log:

'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-03-18T03:19:04.453647Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-18T03:19:08.815578Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-18T03:19:08.816488Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 1
2024-03-18T03:19:09.409410Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-18T03:19:15.085856Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-18T03:19:16.221036Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-18T03:19:16.225478Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-18T03:19:16.269948Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2024-03-18 03:19:59+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
2024-03-18 03:19:59+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-03-18 03:19:59+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-03-18T03:20:00.042390Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-18T03:20:01.178340Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-18T03:20:01.178690Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 1
2024-03-18T03:20:01.843654Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-18T03:20:03.020059Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-18T03:20:03.803340Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2024-03-18T03:20:03.895720Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2024-03-18T03:20:04.530423Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-18T03:20:04.573344Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-18T03:20:04.653988Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2024-03-18T03:20:08.139342Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.3.0'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2024-03-18T03:20:08.130725Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-03-18 03:20:21+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
2024-03-18 03:20:22+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-03-18 03:20:22+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-03-18T03:20:22.722366Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-18T03:20:24.041359Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-18T03:20:24.042011Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 1
2024-03-18T03:20:24.101800Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-18T03:20:24.621344Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

docker-compose.yml:

services:
  server:
    image: standardnotes/server
    env_file: .env
    container_name: server_sn
    restart: unless-stopped
    ports:
      - 3000:3000
      - 3125:3104
    volumes:
      - ./logs:/var/lib/server/logs
      - ./uploads:/opt/server/packages/files/dist/uploads
    networks:
      - standardnotes_self_hosted

  localstack:
    image: localstack/localstack:3.0
    container_name: localstack_sn
    expose:
      - 4566
    restart: unless-stopped
    environment:
      - SERVICES=sns,sqs
      - HOSTNAME_EXTERNAL=localstack
      - LS_LOG=warn
    volumes:
      - ./localstack_bootstrap.sh:/etc/localstack/init/ready.d/localstack_bootstrap.sh
    networks:
      - standardnotes_self_hosted

  db:
    image: mysql:8
    container_name: db_sn
    environment:
      - MYSQL_DATABASE=standard_notes_db
      - MYSQL_USER=std_notes_user
      - MYSQL_ROOT_PASSWORD=****
      - MYSQL_PASSWORD=****
    expose:
      - 3306
    restart: unless-stopped
    command: --default-authentication-plugin=mysql_native_password --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
    volumes:
      - ./data/mysql:/var/lib/mysql
      - ./data/import:/docker-entrypoint-initdb.d
    networks:
      - standardnotes_self_hosted

  cache:
    image: redis:6.0-alpine
    container_name: cache_sn
    volumes:
      - ./data/redis/:/data
    expose:
      - 6379
    restart: unless-stopped
    networks:
      - standardnotes_self_hosted

networks:
  standardnotes_self_hosted:
    name: standardnotes_self_hosted

To Reproduce
not sure, i have try redeploy the docker containers but after 1day or 2 this problem comes back.

Expected behavior
Please help me fix this problem.

Desktop (please complete the following information):

• OS: mac OS
• Browser chrome
• Version 3.191.4

Additional context
Add any other context about the problem here.

[stevesbrain]

I have tried deploying a fresh container, and received similar restart errors (whole stack is fresh):

db_self_hosted          | 2024-05-02T01:32:22.506228Z 0 [System] [MY-015015] [Server] MySQL Server - start.                                                                                                                                                 11:03:29 [26/1859]
db_self_hosted          | 2024-05-02T01:32:23.049270Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.0) starting as process 1
db_self_hosted          | 2024-05-02T01:32:23.070937Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db_self_hosted          | 2024-05-02T01:32:23.540107Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db_self_hosted          | mysqld: Table 'mysql.plugin' doesn't exist
db_self_hosted          | 2024-05-02T01:32:23.875071Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.
db_self_hosted          | 2024-05-02T01:32:23.875965Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.876545Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.877142Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.878019Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.878521Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.879022Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.879472Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:23.987167Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
db_self_hosted          | 2024-05-02T01:32:24.060907Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
db_self_hosted          | 2024-05-02T01:32:24.067213Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
db_self_hosted          | 2024-05-02T01:32:24.067296Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
db_self_hosted          | 2024-05-02T01:32:24.070582Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
db_self_hosted          | 2024-05-02T01:32:24.071402Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
db_self_hosted          | 2024-05-02T01:32:24.072060Z 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.compon
ent' doesn't exist
db_self_hosted          | 2024-05-02T01:32:24.072150Z 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.compone
nt table is missing or has an incorrect definition.
db_self_hosted          | 2024-05-02T01:32:24.072467Z 0 [ERROR] [MY-000067] [Server] unknown variable 'default-authentication-plugin=mysql_native_password'.
db_self_hosted          | 2024-05-02T01:32:24.075234Z 0 [ERROR] [MY-010119] [Server] Aborting
db_self_hosted          | 2024-05-02T01:32:25.662135Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.0)  MySQL Community Server - GPL.
db_self_hosted          | 2024-05-02T01:32:25.662189Z 0 [System] [MY-015016] [Server] MySQL Server - end.
db_self_hosted          | 2024-05-02 01:33:26+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.0-1.el8 started.
db_self_hosted          | 2024-05-02 01:33:27+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
db_self_hosted          | 2024-05-02 01:33:27+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.0-1.el8 started.
db_self_hosted          | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'

[bigjdunham]

I'm getting the same issue with an unknown variable 'default-authentication-plugin=mysql_native_password' error that appears to be crashing the db container.

[bigjdunham]

I figured it out. You need to change the "--default-authentication-plugin=mysql_native_password" to "----mysql-native-password=ON" in the COMMAND section of db container. Apparently in upgrading to the latest version of mysql 8 (which is now 8.4) the "default-authentication-plugin" is deprecated, and this workaround is how I resolved the error and got my server working again.

[stevesbrain]

Hmm, I can't bootstrap my install using that still (either with the four -'s, or if I assume that was a mistype and try the standard 2)

[anarion80]

I figured it out. You need to change the "--default-authentication-plugin=mysql_native_password" to "----mysql-native-password=ON" in the COMMAND section of db container. Apparently in upgrading to the latest version of mysql 8 (which is now 8.4) the "default-authentication-plugin" is deprecated, and this workaround is how I resolved the error and got my server working again.

Thanks, this works for me (but with two dashes).