MySQL stack keeps wanting to reinitialise

filliph

Because the .indigostack file is stored in my Documents folder, which is synced with iCloud, I customised the my.cnf file like so:

# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
default-character-set = utf8mb4

[mysql]
prompt=indigo>
default-character-set = utf8mb4

[mysqld]
basedir="{{service_path}}"
datadir="/Users/Shared/www/mysql"
port = "{{port}}"
socket = "{{sock_file}}"
pid-file = "{{pid_file}}"
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_allowed_packet = {{max_allowed_packet}}
innodb_file_per_table
lower_case_table_names = 2

# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

If the stack ever shuts down, it's impossible to restart it, because it keeps wanting to reinitialise.

2022-06-07T16:04:58.364872Z 0 [System] [MY-013169] [Server] /Users/filliph/.indigo/stacks/C72FBF/mysql_A6E6/bin/mysqld (mysqld 8.0.29) initializing of server in progress as process 99625
2022-06-07T16:04:58.366415Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2022-06-07T16:04:58.366471Z 0 [ERROR] [MY-013236] [Server] The designated data directory /Users/Shared/www/mysql/ is unusable. You can remove all files that the server added to it.
2022-06-07T16:04:58.366556Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-06-07T16:04:58.366690Z 0 [System] [MY-010910] [Server] /Users/filliph/.indigo/stacks/C72FBF/mysql_A6E6/bin/mysqld: Shutdown complete (mysqld 8.0.29)  Homebrew.

I can't trust this app with my work data if I have to constantly delete the data directory in order to let MySQL reinitialise it every time I shut down Indigo or restart my computer.

Steps to reproduce:

Create a stack containing a MySQL service
Start the stack
Stop the stack
Try to start the stack again. A popup saying "Unknown" will show up, and the log file will contain the above error message about --initialize being ran on a non-empty directory.

Indigo

You're certainly exposing all the edge-cases! 🤪 You get the beta-tester of the month award haha.

The default location for Indigo MySQL's data directory is inside the ~/.indigo path; is there some other reason the default config doesn't work in the context of iCloud?

Regardless, when you start up a MySQL service, Indigo first checks whether it needs to initialise that mysql's data directory. The logic it uses to determine that at present is the issue here — it's currently "functional placeholder code": it checks for the existence of {{dataDirectory}}/sys/sys_config.ibd, that being one of the files that will definitely exist when you have initialised mysql.

However since you are overriding the datadir, {{dataDirectory}}/sys/sys_config.ibd won't exist, and Indigo will try to initialise MySQL.

I always anticipated the need for a better check than the filesystem one described above. The real issue is there's no mysql command that simply checks status; they all require mysqld to actually be running. However there are a couple of options, I think. I'm very interested in your feedback on them:

1) just run mysqld --initialize-insecure prior to every MySQL launch. As per the MySQL docs, "the command that initializes the data directory does not overwrite any existing mysql database tables, so it is safe to run in any circumstances."

2) attempt to run something like mysqld --user=root. If the data directory is not initialised, that command returns an error of some kind (mysqld: Table 'mysql.plugin' doesn't exist) which I could use to flag the need for initialisation.

3) blindly attempt to start up mysqld and if it fails to start, check why. In my testing without a valid data directory it threw Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist for me.

I fear 1) might have unintended consequences, but the docs do state that not to be the case.

I dislike 2) because, like the current solution, it feels like it relies on some preconceptions about the data directory.

So I'm leaning towards 3) at present.

filliph

Indigo tl;dr: MySQL databases should default to the .indigo folder in the user's folder, rather than inside the stack, and the data directory should be configurable in the UI the same way the web root is for Nginx.

Having MySQL database files inside the stack to sync it with iCloud is not a good idea primarily because it places an artificial limit on your local database size.

Imagine you're not a heavy iCloud user, so the default 5 GB is enough for you. Your shiny new M2 MacBook Pro with 2 TB of SSD space etc now has a maximum database size of (5 GB - photos - otherDocuments - otherData) which can end up being as low as 1 or 2 GB. You take some photos and you have created some documents, but not a lot, so you're using maybe 3 or 4 GB of iCloud space.

The secondary reason why it's not a good idea is because it can create desynchronisation issues. Let's say that iCloud has a derp moment and accidentally reverts one of your table files. There can be any number of reasons why that happens, I don't know exactly when MySQL updates the files on disk. There's a reason why backups of MySQL databases are not raw file copies, but rather mysqldump commands.

Another scenario; let's say you have an iMac at home where you do 99% of your work, and a MacBook you take with you to perform presentations to clients. Normally, your code would have migration paths so that you can bring any other database up to speed with the latest changes, and you'd be able to create more presentable test data than njkdsfjkjkdfsnjfdsnjkdfnjk etc when showing it off to clients. This is impossible if the database is synced via iCloud.

I'm sure if I had more time I could come up with a slew of other reasons why it would be a bad idea.

The default should, for all the reasons above, be inside the ~/.indigo folder as opposed to inside the ~/Documents/<stackname>.indigostack.

Your existing check for the existence of a file is fine, if you allow us to set the data directory ourselves the same way we can set the web root in a Nginx site.

Indigo

Wow, this one turned out really tricky; Indigo monitors the console output when starting its services, but in the case of MySQL it gets nothing useful, because when you configure MySQL's log-error destination, it writes ALL stdout errors to the log file and nothing to the console.

Thus if it fails to start, Indigo can do nothing other than direct the user to look at the log file. It can't check the logs itself and determine, for example, that the data directory is not initialised, because the logs contain old data as well so we can't tell what's what.

So without somehow parsing log files, 3) won't be possible.

2) also won't be feasible, because if the data dir is initialised, mysqld starts up right there, which we don't want.

So I'm left with option 1. I've tested it thoroughly and it does work; MySQL specifies that --initialize-insecure fails when the data directory is not empty. So in your case it will attempt to initialise /Users/Shared/www/mysql, see that it already contains files, and move on with startup.

This is done and will be in the next release.

Indigo

filliph Your existing check for the existence of a file is fine, if you allow us to set the data directory ourselves the same way we can set the web root in a Nginx site.

Great idea! I hadn't really considered people wanting their own location for the raw db directory, but I like the idea. The less "magical" everything is, the better, and IMO feeling like you have a handle on where things are stored goes a long way toward that goal.

Indigo

Thanks for your time responding in this detail. Just to clarify, the default for Indigo's MySQL database directories is indeed already inside the .indigo folder in the user's root.

In my example stack, the full path of the database folder is:

/Users/jeffh/.indigo/stacks/C4273B/mysql_8434_data

As a side-note: my intention (and personal use-case) is that the projectname.indigostack file is able to be version-controlled ie stored in its related project's GIT repo, so other team members share the same infrastructure. In doing this, it was clear early on that everyone would need their own independent copies of the raw database files — you wouldn't put that in GIT (nor in iCloud, as you've identified).

filliph

Indigo You're right, this appears to have been me not being able to tell apart the different Finder tabs I had open 😅

I've reset my.cnf's data dir directive and everything appears to be working 🙂

I would still like the custom data dir setting though, but at least the default config is good 😃

Indigo

Phew, glad it's working as intended 🙂 And yes, a custom data directory config in the UI would be good; I've put it in my list.