Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unable to start mysql with --mysql-native-password=ON #1059

Open
ngmathiou opened this issue May 13, 2024 · 5 comments
Open

Unable to start mysql with --mysql-native-password=ON #1059

ngmathiou opened this issue May 13, 2024 · 5 comments

Comments

@ngmathiou
Copy link

ngmathiou commented May 13, 2024 •
edited
Loading

When I'm adding: --mysql-native-password=ON on the docker-compose file, I'm unable to start the mysql database

2024-05-13 10:44:25+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.0-1.el9 started.
2024-05-13 10:44:25+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-05-13 10:44:25+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.4.0-1.el9 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-05-13T10:44:25.987664Z 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run.
2024-05-13T10:44:25.801206Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-05-13T10:44:25.985834Z 0 [Note] [MY-013932] [Server] BuildID[sha1]=353ae40c5329277f3033302aee25ef64367c8a66
2024-05-13T10:44:25.985839Z 0 [Note] [MY-010949] [Server] Basedir set to /usr/.
2024-05-13T10:44:25.985850Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.0) starting as process 1
2024-05-13T10:44:25.989820Z 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO
2024-05-13T10:44:25.989887Z 0 [Note] [MY-015140] [InnoDB] Using innodb_flush_method=O_DIRECT
2024-05-13T10:44:25.990069Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2024-05-13T10:44:25.990117Z 0 [Note] [MY-010747] [Server] Plugin 'ndbcluster' is disabled.
2024-05-13T10:44:25.990130Z 0 [Note] [MY-010747] [Server] Plugin 'ndbinfo' is disabled.
2024-05-13T10:44:25.990139Z 0 [Note] [MY-010747] [Server] Plugin 'ndb_transid_mysql_connection_map' is disabled.
2024-05-13T10:44:25.991260Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-13T10:44:25.991279Z 1 [Note] [MY-013546] [InnoDB] Atomic write enabled
2024-05-13T10:44:25.991318Z 1 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2024-05-13T10:44:25.991329Z 1 [Note] [MY-012944] [InnoDB] Uses event mutexes
2024-05-13T10:44:25.991333Z 1 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2024-05-13T10:44:25.991337Z 1 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2024-05-13T10:44:25.993251Z 1 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2024-05-13T10:44:25.993615Z 1 [Note] [MY-012203] [InnoDB] Directories to scan './'
2024-05-13T10:44:25.993664Z 1 [Note] [MY-012204] [InnoDB] Scanning './'
2024-05-13T10:44:25.999174Z 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 346 files.
2024-05-13T10:44:26.000055Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
2024-05-13T10:44:26.004946Z 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2024-05-13T10:44:26.007476Z 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2024-05-13T10:44:26.008831Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-13T10:44:26.030614Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-05-13T10:44:26.081374Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2024-05-13T10:44:26.081396Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 128
2024-05-13T10:44:26.081417Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-13T10:44:26.081452Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-05-13T10:44:26.110476Z 1 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 1774736813 in redo log file ./#innodb_redo/#ib_redo481.
2024-05-13T10:44:26.110570Z 1 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 1774736403, whereas checkpoint_lsn = 1774736813 and start_lsn = 1774736384
2024-05-13T10:44:26.110579Z 1 [Note] [MY-012547] [InnoDB] Log scan progressed past the checkpoint LSN 1774736403.
2024-05-13T10:44:26.110583Z 1 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2024-05-13T10:44:26.110587Z 1 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2024-05-13T10:44:26.229677Z 1 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 1774876353
2024-05-13T10:44:26.231285Z 1 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-05-13T10:44:26.231799Z 1 [Note] [MY-012532] [InnoDB] Applying a batch of 46 redo log records ...
2024-05-13T10:44:26.342228Z 1 [Note] [MY-012533] [InnoDB] 100%
2024-05-13T10:44:26.842403Z 1 [Note] [MY-012535] [InnoDB] Apply batch completed!
2024-05-13T10:44:26.947387Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2024-05-13T10:44:26.948129Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2024-05-13T10:44:26.948983Z 1 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2024-05-13T10:44:26.949030Z 1 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 1918478
2024-05-13T10:44:27.029856Z 1 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete
2024-05-13T10:44:27.029890Z 1 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 4 thread: 80 ms.
2024-05-13T10:44:27.030034Z 1 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1"
2024-05-13T10:44:27.030046Z 1 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2024-05-13T10:44:27.030086Z 1 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-05-13T10:44:27.103073Z 1 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2024-05-13T10:44:27.103185Z 1 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2024-05-13T10:44:27.117343Z 1 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2024-05-13T10:44:27.117926Z 1 [Note] [MY-012976] [InnoDB] 8.4.0 started; log sequence number 1774876470
2024-05-13T10:44:27.118005Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-13T10:44:27.123987Z 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80300'.
2024-05-13T10:44:27.230155Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2024-05-13T10:44:27.343425Z 1 [Note] [MY-012356] [InnoDB] Scanned 348 tablespaces. Validated 348.
2024-05-13T10:44:27.351977Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80300'.
2024-05-13T10:44:27.358096Z 0 [Note] [MY-011332] [Server] Plugin mysqlx reported: 'IPv6 is available'
2024-05-13T10:44:27.359481Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '::' port: 33060'
2024-05-13T10:44:27.359512Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. socket: '/var/run/mysqld/mysqlx.sock''
2024-05-13T10:44:27.376608Z 0 [Note] [MY-010902] [Server] Thread priority attribute setting in Resource Group SQL shall be ignored due to unsupported platform or insufficient privilege.
2024-05-13T10:44:27.379017Z 0 [Note] [MY-010855] [Server] Recovering after a crash using binlog
2024-05-13T10:44:27.379046Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2024-05-13T10:44:27.382768Z 0 [Note] [MY-013911] [Server] Crash recovery finished in binlog engine. No attempts to commit, rollback or prepare any transactions.
2024-05-13T10:44:27.382787Z 0 [Note] [MY-013911] [Server] Crash recovery finished in InnoDB engine. No attempts to commit, rollback or prepare any transactions.
2024-05-13T10:44:27.382791Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2024-05-13T10:44:27.384792Z 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin
2024-05-13T10:44:27.385064Z 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end
2024-05-13T10:44:27.385228Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2024-05-13T10:44:27.385702Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 240513 10:44:27
2024-05-13T10:44:27.388436Z 0 [Note] [MY-012922] [InnoDB] Waiting for purge to start
2024-05-13T10:44:27.448354Z 0 [Note] [MY-010913] [Server] You have not provided a mandatory server-id. Servers in a replication topology must have unique server-ids. Please refer to the proper server start-up parameters documentation.
2024-05-13T10:44:27.450126Z 0 [Note] [MY-010182] [Server] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2024-05-13T10:44:27.452031Z 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory.
2024-05-13T10:44:27.454824Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-13T10:44:27.454857Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-13T10:44:27.455234Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2024-05-13T10:44:27.455549Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2024-05-13T10:44:27.457586Z 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3306
2024-05-13T10:44:27.457626Z 0 [Note] [MY-010253] [Server] IPv6 is available.
2024-05-13T10:44:27.457633Z 0 [Note] [MY-010264] [Server]   - '::' resolves to '::';
2024-05-13T10:44:27.457641Z 0 [Note] [MY-010251] [Server] Server socket created on IP: '::'.
2024-05-13T10:44:27.458772Z 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-05-13T10:44:27.470510Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'authelia@%'; Ignoring user
2024-05-13T10:44:27.470532Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'exporter@%'; Ignoring user
2024-05-13T10:44:27.470541Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'giannis@%'; Ignoring user
2024-05-13T10:44:27.470563Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'leantime@%'; Ignoring user
2024-05-13T10:44:27.470581Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mailslurper@%'; Ignoring user
2024-05-13T10:44:27.470588Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'nextcloud@%'; Ignoring user
2024-05-13T10:44:27.470659Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'oneproduct@%'; Ignoring user
2024-05-13T10:44:27.470680Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'photoprism@%'; Ignoring user
2024-05-13T10:44:27.470687Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'redmine@%'; Ignoring user
2024-05-13T10:44:27.470695Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'root@%'; Ignoring user
2024-05-13T10:44:27.470702Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'wordpress@%'; Ignoring user
2024-05-13T10:44:27.470709Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mysql.infoschema@localhost'; Ignoring user
2024-05-13T10:44:27.470719Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mysql.session@localhost'; Ignoring user
2024-05-13T10:44:27.470726Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'mysql.sys@localhost'; Ignoring user
2024-05-13T10:44:27.470785Z 0 [Warning] [MY-010319] [Server] Found invalid password for user: 'root@localhost'; Ignoring user
2024-05-13T10:44:27.485167Z 0 [Note] [MY-011025] [Repl] Failed to start replica threads for channel ''.
2024-05-13T10:44:27.487438Z 5 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 5
2024-05-13T10:44:27.487691Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.0'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
terminate called after throwing an instance of 'ngs::Error_code'
2024-05-13T10:44:27Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=353ae40c5329277f3033302aee25ef64367c8a66
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x100000
 #0 0xc14ec0 <unknown>
 #1 0x779086a476ef <unknown>
 #2 0x779086a9498c <unknown>
 #3 0x779086a47645 <unknown>
 #4 0x779086a317f2 <unknown>
 #5 0x779086dacb20 <unknown>
 #6 0x779086db852b <unknown>
 #7 0x779086db8596 <unknown>
 #8 0x779086db87f8 <unknown>
 #9 0x93fe49 <unknown>
 #10 0x1cdd6be <unknown>
 #11 0x1d2830f <unknown>
 #12 0x1c28fb1 <unknown>
 #13 0x779086a92c11 <unknown>
 #14 0x779086b16f53 <unknown>
 #15 0xffffffffffffffff <unknown>
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

When removing the above mentioned command I'm able to start the database but cannot access it with error ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

I'm using mysql:latest tag 8.4.0 and prior I altered all user authentications to mysql_native_password, when I added the command in the docker-compose it led to the above error and I'm locked out of the database.

Is there any way I can gain access again to the database files and start the database with mysql_native_password support ON ?
@LaurentGoderre
Copy link
Member

Duplicate of #1048

@LaurentGoderre LaurentGoderre marked this as a duplicate of #1048 May 13, 2024
@ngmathiou
Copy link
Author

As it seems duplicate of the #1048, I assume that using the bellow docker-compose should enable the container to start with mysql_native_password.

---

version: '3.3'

volumes:
  data:
    driver: local

services:
  mysql:
    image: mysql:latest
    container_name: mysql
    restart: unless-stopped
    command:
      - --mysql-native-password=ON
      - --log-error-verbosity=3
    environment:
      MYSQL_DATABASE: 'db'
      MYSQL_USER: 'username'
      MYSQL_PASSWORD: '*********'
      MYSQL_ROOT_PASSWORD: '*********'
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - data:/var/lib/mysql

Using the section:

    command:
      - --mysql-native-password=ON

In my case I'm unable to start and access the database I have no previous backups in order to use 8.3 version for example.

The approach of enable mysql-native-password should have worked or am I missing something like completely deprecating the option to enable mysql_native_password and I should start with fresh install of mysql and lose any data prior to the update.

@yosifkit
Copy link
Member

You could try using mysql:8.4-oraclelinux8

So, it could also be related to #1058 (#1058 (comment)).

I think you can gain access with --skip-grant-tables and then docker exec into the container and run mysql commands from there to alter (or backup) the database as needed.

@tianon
Copy link
Member

tianon commented May 13, 2024

If you do manage to get it working with the officially deprecated mysql_native_password functionality enabled, you'll want to keep #1054 (comment) in mind:

With the caveat/warning that they do intend to remove that functionality completely in the future (so eventually the square wheel will roll again and this will come back out from under the rug 🙈).

@ngmathiou
Copy link
Author

You could try using mysql:8.4-oraclelinux8

So, it could also be related to #1058 (#1058 (comment)).

I think you can gain access with --skip-grant-tables and then docker exec into the container and run mysql commands from there to alter (or backup) the database as needed.

I've tried using mysql:8.4-oraclelinux8 with no luck. Also I've already checked your comment and tried the resolution you suggested on issue #1058 with no luck.

What actually worked for me was the --skip-grant-tables

Thank you very much, from this point I can run any necessary mysql commands!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@tianon @yosifkit @LaurentGoderre @ngmathiou