MySQL error - "Access denied for user 'root'@'localhost'"

za0

Hi. After a recent rebuild, but with no changes on my side, Indigo won't start my stack any more - giving the error:

"The process /Users/[me]/.indigo/stacks/EB353D/mysql_B561/bin/mysql ran with PID 67495 and returned exit code 1 but one of the following was expected: "0". The stdErr contained: mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)."

I deleted MySQL 8 entirely from my stack, and re-added it - which then works fine, but of course is missing all my databases. Running Indigostack Version 1.0.0-rc.9 (117) on macOS 14.5 - any tips on how to move forward? Thanks!

Indigo

Hey 🙂 That's very strange. I really suspect (and hope) that the problem is a one-off oddity and won't recur. It certainly never has occurred for me over numerous years of running many MySQL services inside Indigo.

There are logs for MySQL which might be of use if it happens again; I say 'might' because mine are full of stuff even in normal use, but none-the-less perhaps worth a look. To find them, right-click your MySQL service and select "Show Compiled in Finder". There you'll find the logs directory.

MySQL users are actually defined inside one of several special MySQL databases, in this case the one simply called mysql. If that database got corrupted somehow, or otherwise messed with, I guess your symptoms would be a possible result.

za0 I deleted MySQL 8 entirely from my stack, and re-added it - which then works fine, but of course is missing all my databases. Running Indigostack Version 1.0.0-rc.9 (117) on macOS 14.5 - any tips on how to move forward?

I'm a little unclear on where you're currently at and what you're hoping to do next. When you deleted the MySQL8 service, were you expecting this to still keep your databases? If so don't panic — it actually does 🙂 but not in a way that is straightforward to retrieve — it's intended more as an emergency safeguard. The databases are stored in MySQL's native file format in a folder called, in your example, mysql_B561_data.

However, if you simply get your new MySQL service to use your old databases folder (which I can help you do), I suspect the problem will still exist.

If it's at all possible, I'd recommend starting with fresh databases. If that's not possible, and you'll be losing significant work if your databases are not available, let me know, and we can see if we can resurrect them.

za0

Hi, thanks for the quick reply. I can recreate all seven of the databases if needed, but would very much prefer not to have to, if at all possible.

I was aware of the MySQL ..._data folder so backed that up before removing MySQL from my stack (though good that it's preserved, just in case!) As you say, trying to swap the contents of that in to replace the newly-created data folder just results in the same issue.

Looking at the error ("...Access denied for user 'root'@'localhost' (using password: NO)") again makes me curious - how come it's saying no password is being passed at that point? Surely the root user does have a password ('password')?

za0

PS as expected log file is not very useful:

2024-08-08T13:50:41.810540Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-08-08T13:50:42.089243Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-08-08T13:50:42.091088Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-08-08T13:50:42.091096Z 0 [System] [MY-010116] [Server] /Users/[me]/.indigo/stacks/EB353D/mysql_9593/bin/mysqld (mysqld 8.3.0) starting as process 72114
2024-08-08T13:50:42.101165Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /Users/[me]/.indigo/stacks/EB353D/mysql_9593_data/ is case insensitive
2024-08-08T13:50:42.313817Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-08T13:50:42.546747Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-08T13:50:42.831921Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-08-08T13:50:42.831954Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-08-08T13:50:42.861470Z 0 [Warning] [MY-015032] [Server] 'SET_USER_ID' (granted to 'root@localhost') is deprecated and will be removed in a future release.
2024-08-08T13:50:42.881352Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /tmp/mysqlx.sock
2024-08-08T13:50:42.881415Z 0 [System] [MY-010931] [Server] /Users/[me]/.indigo/stacks/EB353D/mysql_9593/bin/mysqld: ready for connections. Version: '8.3.0'  socket: '/Users/[me]/.indigo/stacks/EB353D/mysql_9593/run/mysqld.sock'  port: 5555  Homebrew.
2024-08-08T13:50:43.532054Z 8 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-08-08T13:50:43.588367Z 9 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-08-08T13:50:43.632003Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.3.0).
2024-08-08T13:50:43.856548Z 0 [System] [MY-010910] [Server] /Users/[me]/.indigo/stacks/EB353D/mysql_9593/bin/mysqld: Shutdown complete (mysqld 8.3.0)  Homebrew.
2024-08-08T13:50:43.856574Z 0 [System] [MY-015016] [Server] MySQL Server - end.

za0

Okay, good news - have managed to fix this - am relieved! Bit complex. First stopped my stack in Indigo and copied the contents of the complete-but-inaccessible MySQL ..._data folder into the newly-created-but-empty one, having deleted the contents of that first.

Next ran the internal mysql daemon using the default/original uncustomised my.cnf file and with the skip-grant-tables option so it doesn't check credentials:

/Users/[me]/.indigo/stacks/[STACKID]/mysql_[ID]/bin/mysqld --defaults-file=/Users/[me]/.indigo/stacks/[STACKID]/mysql_[ID]/conf-original/my.cnf --datadir=/Users/[me]/.indigo/stacks/[STACKID]/mysql_[ID]_data --skip-grant-tables

Then in separate terminal opened the mysql client:
mysql -u root

Then ran:
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> EXIT;

After exiting out, I killed the mysqld process then initiated a new mysqld process just for testing with the Indigo customised my.cnf file and without the skip-grant-tables option:

/Users/[me]/.indigo/stacks/[STACKID]/mysql_[ID]/bin/mysqld --defaults-file=/Users/[me]/.indigo/stacks/[STACKID]/mysql_[ID]/conf/my.cnf --datadir=/Users/[me]/.indigo/stacks/[STACKID]/mysql_[ID]_data

....which seemed to work fine. Killed that then returned to Indigo and started my stack, to find all was working fine and my data was perfectly accessible.

All in all rather strange and not really sure of the cause! In my travels I did find the 1-users-databases.sql file in the mysql 'scripts' folder, and for MySQL v8 and up I think the line...

SET PASSWORD FOR 'root'@'localhost' = 'password';

...should be replaced with

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

That file also talks about "generated commands to create the required databases [...] also grant all privileges to all users on all databases [...] feel free to remove these GRANTs and provide your own" but there are no further lines in the file, not sure if that could have anything to do with it? Anyway, all is well that ends well!

Indigo

Hi, my apologies, I've been flat out, but in lieu of a deeper response at least wanted to acknowledge I've read your posts — I really appreciate your work digging so deeply into this, and super glad you were able to resolve it.

You've raised some interesting points which I'm keen to look into once workloads allow. Some brief comments:

it really looks like somehow your mysql database got messed up somehow. I can't think of any scenario where that could have happened, and really hope there's no super-obscure Indigo cause for it.
"using password: NO" is because, in one of many efforts for added resilience, on each launch of the stack we first run mysql with --initialize-insecure. My code comment on that states "As per the docs this is fine; -initialize-insecure simply fails if there's anything already in the data-dir specified in the given conf."
you're absolutely right, the user setup script should be updated for 8.x. Thanks for the heads-up on that!
1-users-databases.sql is involved in an undocumented Indigo feature which allows the stack file to specify a set of default databases that should be created when a stack is first started up. This was implemented for my own use of Indigo inside a team, to speed onboarding of new devs. However it hasn't really been as useful as I'd hoped, since a) the databases are all empty after creation, b) importing db dumps to fix a) would be straying well outside of scope because c) databases are pretty easy to create with any number of other methods. So I'll almost certainly be removing that feature. But that hopefully explains the oddity you discovered 🙂

za0

No problem! I do think it might be worth delving more into whether the PHP 8 change from SET PASSWORD… to ALTER USER… might have been the cause. Also I guess that init SQL script could be run with the —skip-grant-tables option (if it’s not already) so it doesn't check credentials. Anyway I’m relieved to have the data back and not have to rebuild! Cheers.