0

I'm running a local copy of MariaDB on OS X from Homebrew. It's working fine, but I've forgotten my root password (yeah, I know) and the usual reset is failing like this:

$ mysqld_safe ‐‐skip-grant-tables &
[1] 22120
$ 190818 13:47:37 mysqld_safe Logging to '/usr/local/var/mycomputername.err'.
190818 13:47:37 mysqld_safe Starting mysqld daemon with databases from /usr/local/var

so far, so good? However, I get no mysql prompt, and hitting return brings me back to my CL prompt. Then (as a root user) entering:

mysql -u root

gives:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

So I'm not getting in without a password and I can't proceed with the standard password reset.

The error logs shows:

2019-08-18 12:38:44 4527658432 [Note] Plugin 'FEEDBACK' is disabled.
2019-08-18 12:38:44 4527658432 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
/usr/local/bin/mysqld: Too many arguments (first extra is '‐‐skip-grant-tables').
2019-08-18 12:38:44 4527658432 [ERROR] Aborting

I cannot update user set plugin='' where User='root'; as I can't get a mysql shell.


UPDATE:

error log after upgrading and trying skip-grant-tables again:

2019-08-20 16:01:06 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-08-20 16:01:06 0 [Note] InnoDB: Uses event mutexes
2019-08-20 16:01:06 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-08-20 16:01:06 0 [Note] InnoDB: Number of pools: 1
2019-08-20 16:01:06 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-08-20 16:01:06 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-08-20 16:01:06 0 [Note] InnoDB: Completed initialization of buffer pool
2019-08-20 16:01:06 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=4766241
2019-08-20 16:01:06 0 [Note] InnoDB: Upgrading redo log: 2*50331648 bytes; LSN=4766259
2019-08-20 16:01:07 0 [Note] InnoDB: Starting to delete and rewrite log files.
2019-08-20 16:01:07 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
2019-08-20 16:01:07 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
2019-08-20 16:01:07 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2019-08-20 16:01:07 0 [Note] InnoDB: New log files created, LSN=4766259
2019-08-20 16:01:07 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-08-20 16:01:07 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-08-20 16:01:07 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-08-20 16:01:07 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-08-20 16:01:07 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-08-20 16:01:07 0 [Note] InnoDB: Waiting for purge to start
2019-08-20 16:01:07 0 [Note] InnoDB: 10.4.6 started; log sequence number 4766259; transaction id 47107
2019-08-20 16:01:07 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/var/mysql/ib_buffer_pool
2019-08-20 16:01:07 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-08-20 16:01:07 0 [Note] Server socket created on IP: '::'.
2019-08-20 16:01:07 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU
2019-08-20 16:01:07 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2019-08-20 16:01:07 6 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-08-20 16:01:07 6 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-08-20 16:01:07 0 [Note] Reading of all Master_info entries succeeded
2019-08-20 16:01:07 0 [Note] Added new Master_info '' to hash table
2019-08-20 16:01:07 0 [Note] /usr/local/opt/mariadb/bin/mysqld: ready for connections.
Version: '10.4.6-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
2019-08-20 16:01:07 0 [Note] InnoDB: Buffer pool(s) load completed at 190820 16:01:07

mysql_upgrade gave:

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
FATAL ERROR: Upgrade failed

This is a Homebrew installation on MariaDB.

10
  • 2
    You probably want to run mysqld_safe as the root user (via sudo), which you don't seem to be doing in your example.
    – mustaccio
    Aug 18, 2019 at 14:03
  • 1
    Was there a recent upgrade? Did you run mysql_upgrade?
    – Rick James
    Aug 18, 2019 at 18:17
  • mysqld_safe still wants my password… I’ve held off an upgrade because I need to try a MySQL dump from the production site into my local MariaDB and the versions are already too far apart for comfort Aug 18, 2019 at 18:26
  • 1
    Have you tried running (as OS root) mysqld --skip-grant-tables &? The error message appears to say mysqld_safe has tried to start mysqld with too many arguments, so by circumventing mysqld_safe and running mysqld directly, perhaps you won't see that problem. If that still doesn't work, let's see the error log you get now.
    – dbdemon
    Aug 19, 2019 at 9:32
  • 1
    You need to run mysql_upgrade before doing anything with it.
    – Rick James
    Aug 19, 2019 at 21:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.