MySQL not working after attempting my.cnf change

elbuttano

Been using indigo for a long time now with no issues at all. I tried to remove ONLY_FULL_GROUP_BY for an old website compatibility issue today and so I tried editing the my.cnf file by right clicking mysql8 in the relevant stack and editing the file in there (I know this isn't the correct way to do it now, just telling you my process). It didn't work, and when I refreshed my browser it was then giving me the error that mysql wasn't connected, also couldn't connect to any dbs via sequel. So I thought ok let's undo what I did to at least get mysql working again, but I've not been able to fix it, it's as if that one attempt at a config change has had a huge knock on effect.

I made a copy of the my.cnf before I made the change (just in case) and the version I see now doesn't include this line that was there before:

default_authentication_plugin = mysql_native_password

I tried to put it back in, including the version in the Services folder, but with each restart the line is removed. I don't know if this is the main issue or not.

Here is the error log

2025-09-29T15:02:17.505481Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start. 2025-09-29T15:02:17.509235Z 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. 2025-09-29T15:02:17.509362Z 0 [System] [MY-013169] [Server] /Users/turvey/.indigo/stacks/28717A/mysql_A853/bin/mysqld (mysqld 8.4.6) initializing of server in progress as process 8861 2025-09-29T15:02:17.512398Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /Users/turvey/.indigo/stacks/28717A/mysql_A853_data/ is case insensitive 2025-09-29T15:02:17.520268Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-09-29T15:02:17.920920Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2025-09-29T15:02:18.960115Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-09-29T15:02:19.797013Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.4.6). 2025-09-29T15:02:20.211118Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.

In the end I tried deleting the app (followed all the instructions on the website including removal of the hidden files etc) but when I load up the "new" app it loads up all my sites etc and I have the same problem. MySQL just isn't working. Please help!

elbuttano

I think mysql is working again now after another fresh install, however, I hadn't realised when I deleted Indigo it was also going to delete all my local db instances. I have found them backed up as raw files in various folders at mysql_A993_data but now within the Indigo UI clicking on the stack > mysql > show compiled it takes me to a different location. I tried directly copying the folders from mysql_A993_data (old) to mysql_9892_data (new) but the dbs don't show up in sequel. If I create a new db in sequel that does show up as an empty folder in mysql_9892_data so I know it's the right place. Please assist how to restore those dbs.

Indigo

Hi @elbuttano — sorry to hear your troubles, and thanks for putting in such a big effort debugging it! I just realised that the uninstallation instructions don't mention that you will lose your DBs. FWIW I've added that warning now.

MySQL does not intend for its data directory to be user-modified. Within version constraints you should be able to point a MySQL binary at an arbitrary data directory and have it work. Here's what I would try:

quit Indigo
locate mysql_9892_data in the Finder and delete it (I'm assuming you have no DBs of value in the new MySQL service)
copy in your old mysql_A993_data and rename it to mysql_9892_data
launch Indigo and start your stack

Hopefully the new MySQL service will successfully start up using the original data directory. Can I ask how many databases you had in the original mysql service, and how critical it is that you recover them?

As you already noticed, there's potentially another factor at play here, which is that the just-released version of Indigo (v1.2.1) performs a MySQL auth plugin migration for existing MySQL installs. This is necessary as MySQL have deprecated the previous method (mysql_native_password) and mandated the newer method (caching_sha2_password).

In your instance, this opens the possibility (depending at what point you updated Indigo to v1.2.1) that your mysql_A993_data directory will contain users set up with the old mysql_native_password and as such you may not be able to authenticate. However I believe the service should still start. Let me know if this is the case and we can hopefully come up with a workaround.

elbuttano

Thanks I will try this. Firstly, does it matter that the new mysql_9892_data exists within stacks/87E124 and the old mysql_A993_data existed within stacks/91F6B6?

There are about 10 dbs to recover, some more critical than others.

Also thanks for putting up the db loss warning for others. There's no way I would have gone ahead with this had I seen that, so at least it might stop someone else from going through this in future.

Indigo

Hi again!

No, the originating stack won't affect this.

I’m really sorry this has happened to you. Indigo is designed to prevent data loss, but in this instance it occurred despite my best intentions.

Fingers crossed you're still able to recover the DBs. If you don't have success, and they're not too private, feel free to zip the original data directory and email it to me and I'll see what I can do here.

elbuttano

Update: the method worked as described and the dbs have showed up in sequel, thank you. However, the point you made about mysql_native_password updating to caching_sha2_password is now the issue - I can't authenticate. The CMS that most of the websites use is older and won't work with PHP8. I don't think I have the available stack options to properly load the sites 🙁

Indigo

That's great news!

Just have to sort out the auth now, I think? I'm a little confused that you say the DBs are showing up in Sequel Ace. Are you saying you can see all your DBs and their full contents? To even see a list of them you would need to be authenticated with the MySQL server (likely with root and password) and if that works, you're not dealing with an auth plugin incompatibility issue.

elbuttano The CMS that most of the websites use is older and won't work with PHP8. I don't think I have the available stack options to properly load the sites

This sounds like an unrelated issue, or am I misunderstanding? Is this issue related to the MySQL issue?

Based on your comment, I assume you're running Indigo Basic, which does provide PHP 7.2. Will your CMS run under that? What CMS & Version is it?

elbuttano

Sorry for confusion. Yes I have the dbs and contents, so authentication is fine in terms of accessing via Sequel Ace. The problem I have is that I require an older version of MySQL to run some of my Perch CMS sites. On the live env they run on PHP 7.3 and 10.6.23-MariaDB. I believe MariaDB 10.x is compatible with mysql_native_password but my only available Indigo option is MySQL8.4 which isn't. I think before all this happened I had an older version of MySQL within Indigo that was working fine.

Indigo

Ah, great news! Very glad all your DBs are alive again.

You can tell MySQL 8.4 to use the old password plugin for the root user, thereby allowing old PHPs such as 7.2 to connect. I've put together a tiny stack to demo this for you. It has:

mysql 8.4
php 7.2
nginx to host the single file site (you'll need to point it at the location of the site on your mac)

Inside the stack bundle is the appropriate my.cnf override to add mysql_native_password=ON.

Once you start this test stack, check out https://testing.test — you should see ~~failures~~ success messages.

Ignore from here down; see follow-up post below.

Next connect to the new MySQL 8.4 in Sequel Ace, select the mysql database, and locate the root user in the user table. If you scroll far enough you'll find the plugin column which will contain caching_sha2_password. Don't change this directly; you need to run the following SQL in Sequel Ace's "Query" tab to do so cleanly:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Now reload https://testing.test; you should see "Connected successfully" and Sequel Ace will show mysql_native_password as the plugin for the root user.

Let me know how you go!

test-stack.zip

6kB

Indigo

Actually, forget the second half of the above; I've attached a new version of the stack which also overrides the MySQL startup script (1-users-databases.sql) to configure the password plugin correctly for you. The stack should work right out of the box now.

To get your own stack working in a similar fashion, you should be able to do nothing more than copy the mysql_AB52 folder into your own stack config bundle, rename it appropriately (mysql_AB52 to eg mysql_XXXX) and restart the stack when prompted.

test-stack.zip

9kB

elbuttano

I downloaded and opened the test stack and that came up fine but it has removed all my other stacks!! I now only have your php & mysql test and System in the stack list. What happened to them? (Tried to attach/paste a screenshot here but it comes up with 'Error'). Help!

elbuttano

Whereabouts is the default location for the stack config files so I can check if they are still there?

elbuttano

Phew, I found them, and added them back. That was scary for a moment! I'll now check your test stack and report back.

elbuttano

Hi again, the stack you sent works fine but when I copy mysql_AB52 into my own stack, renaming it to replace the one in there, I then fire up the service and I can see in the my.cnf at the bottom mysql_native_password=ON isn't there

Indigo

Wow, you've been having quite the experience! Thanks for sticking with it 🙂

elbuttano I can see in the my.cnf at the bottom mysql_native_password=ON isn't there

I'm guessing this is evidence that the override isn't happening correctly, and that is almost certainly because the path inside your stack config bundle isn't quite right.

Indigo merges everything put inside a folder named with a service ID inside the stack config. So as a check I'd suggest:

double-check the mysql service ID you've used is definitely correct (ie what you renamed mysql_AB52 to, inside your stack config bundle)
add a dummy file alongside the my.cnf ie at stackfilename.indigostack/mysql_AB52/conf/testing.txt

Then, start the stack and search around inside ~/.indigo/stacks/STACKID/mysql_ID and see if your testing.txt was merged in. That should help you sort out any path errors.

Also, make sure your own stack config bundle also includes the mysql script directory override, as per the sample stack I sent above.

Let me know how you go!

elbuttano

Get the following error when firing up:

The process /Users/xxx/.indigo/stacks/91F6B6/mysql_8807/bin/mysqld ran with PID 12459 and returned exit code 124 but one of the following was expected: "0". The stdErr contained: 2025-10-03T13:31:45.177236Z 0 [ERROR] [MY-010946] [Server] Failed to start mysqld daemon. Check mysqld error log.

Log:

2025-10-03T13:34:24.940615Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 35
2025-10-03T13:34:24.952474Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 35 in a file operation.
2025-10-03T13:34:24.955889Z 1 [ERROR] [MY-012596] [InnoDB] Error number 35 means 'Resource temporarily unavailable'
2025-10-03T13:34:24.956005Z 1 [ERROR] [MY-012215] [InnoDB] Cannot open datafile './ibdata1'
2025-10-03T13:34:24.956069Z 1 [ERROR] [MY-012959] [InnoDB] Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2025-10-03T13:34:24.957132Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Cannot open a file.
2025-10-03T13:34:24.960634Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2025-10-03T13:34:24.962322Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2025-10-03T13:34:24.962459Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-10-03T13:34:25.013363Z 0 [System] [MY-010910] [Server] /Users/xxx/.indigo/stacks/91F6B6/mysql_8807/bin/mysqld: Shutdown complete (mysqld 8.4.6)  Homebrew.
2025-10-03T13:34:25.013455Z 0 [System] [MY-015016] [Server] MySQL Server - end.

Indigo

Hey, hoping the following might be helpful. However the first thing I would do if I were you would be to back up any databases that are important to you at the first opportunity, by dumping them to sql files (Sequel Ace makes this easy).

Most Likely Causes:

Another MySQL instance is already running - The most common cause. There's probably another mysqld process already using this data directory.
Stale lock file - A previous MySQL process crashed and left a lock behind.
File permissions issue - The MySQL process doesn't have proper permissions to access the file.

How to Fix:
Check for running MySQL processes: use Activity Monitor (ensure "view > All Processes" is ticked), or on the command line run:

bash   ps aux | grep mysqld

If you find any, stop them first.

Check for lock files in the data directory:

bash   ls -la ~/.indigo/stacks/91F6B6/mysql_8807/data/

Look for files like ibdata1.lock or similar and remove them if MySQL isn't running.

elbuttano

I have it working now, thanks for all your help. I'll keep this open for a couple more days in case anything comes up.

Indigo

Great! Thanks for reporting back 🙂