Accidentally updated MySQL to version 9: plugin `mysql_native_password` is gone

I use docker compose, where I have two services, traccar and mysql. Everything was working flawlessly until I accidentally pulled latest version of MySQL 9 and the plugin mysql_native_password is gone.

I have been trying to fix it but can't figure out how. Also, I have not found anything online about this issue.

The only thing left I can think of is backup the database and restore it on top of a new MySQL container. Is there any other way? If not, could someone here help me doing that?

Thanks in advance.

I don't think you need that driver. You probably just need to update your user or create a new one.

This is my compose:

services:
  traccar:
    container_name: baboontracker_traccar
    image: traccar/traccar:latest
    depends_on:
      - mysql
    environment:
      - MYSQL_HOST=mysql # needed?
      - MYSQL_DATABASE=${mysql_db}
      - MYSQL_USER=${mysql_user}
      - MYSQL_PASSWORD=${mysql_password}
    networks:
      - swag-proxy
      - default
    ports:
      - 5013:5013
    volumes:
      - ./traccar.xml:/opt/traccar/conf/traccar.xml:ro
      - traccar_logs:/opt/traccar/logs:rw
    restart: always

  mysql:
    container_name: baboontracker_mysql
    image: mysql:latest
      #command: --mysql-native-password=ON
    environment:
      - MYSQL_ROOT_PASSWORD=${mysql_root_password}
      - MYSQL_DATABASE=${mysql_db}
      - MYSQL_USER=${mysql_user}
      - MYSQL_PASSWORD=${mysql_password}
    networks:
      - default
    volumes:
      - mysql_data:/var/lib/mysql
    restart: always

volumes:
  traccar_logs:
  mysql_data:

And these are the logs the commented --mysql-native-password=ON:

mysql    | 2024-08-16 13:33:16+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 9.0.1-1.el9 started.
mysql    | 2024-08-16 13:33:16+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
mysql    | 2024-08-16 13:33:16+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 9.0.1-1.el9 started.
mysql    | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
mysql    | 2024-08-16T13:33:17.165109Z 0 [System] [MY-015015] [Server] MySQL Server - start.
mysql    | 2024-08-16T13:33:17.383730Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 9.0.1) starting as process 1
mysql    | 2024-08-16T13:33:17.394797Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
mysql    | 2024-08-16T13:33:18.074898Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysql    | 2024-08-16T13:33:18.548799Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
mysql    | 2024-08-16T13:33:18.548871Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
mysql    | 2024-08-16T13:33:18.560778Z 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.
mysql    | 2024-08-16T13:33:18.584931Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.
mysql    | 2024-08-16T13:33:18.584958Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'root'@'%' is not loaded. Nobody can currently login using this account.
mysql    | 2024-08-16T13:33:18.584967Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'traccar_user'@'%' is not loaded. Nobody can currently login using this account.
mysql    | 2024-08-16T13:33:18.605735Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
mysql    | 2024-08-16T13:33:18.605974Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '9.0.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
traccar exited with code 0
traccar exited with code 0
traccar exited with code 0
traccar exited with code 1

Seems that Traccar can't really login anymore and shuts down.

What do you mean with update the user? Creating a new one would mean not keeping data (unless I migrate it to the new user).

I think this explains what I'm trying to say:

The plugin 'mysql_native_password' used to authenticate user 'traccar_user'@'%' is not loaded. Nobody can currently login using this account.

If you want to reuse it, you have to change the authentication method for the user. Or create a new user.

I wonder why mysql-native-password=ON was enabled in the first place.

I recall not being able to make Traccar work with MySQL correctly without that option, that's why it was enabled. Ok, I'll research how to change the auth method for the user. Thanks for your help!

@Anton Tananaev: fixed :)

For others with same issue:

1. I started the database container docker compose up mysql with flag --skip-grant-tables like this command: --skip-grant-tables
2. Connect to db container docker exec -it db_container bash and execute mysql
3. FLUSH PRIVILEGES;
4. SELECT User, Host, plugin FROM mysql.user; and look for all the users with plugin mysql_native_password
5. ALTER USER 'user_with_native_password_plugin'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_or_old_password';
6. exit both db and stop the db container
7. Remove command line that had --skip-grant-tables and previously the --mysql-native-password=ON that made the mess
8. docker compose up -d
9. Enjoy :)

Thanks for your help.