Cannot get mysql database installed properly on Monterey on Apple Silicon

Question

I am trying to set up a development environment on my new MacBookPro 14 inch M1 Pro. I am using MacPorts because that is what I have used for many years. Setting up apache and php have mostly just worked, with only a minimal amount of tweaks.

But setting up the database gives lots of different kinds of errors. So I am wondering if it could be a Monterey problem or an Apple Silicon problem.

I have tried mariadb-10.6, mariadb-10.5 (just to see if that would work), and even mysql8. They all give different errors, which might be related to permissions.

I was under the impression that the Macports installs should "just work". But there are error messages that should be helpful.

So I uninstalled all the databases and started over so I could capture the error messages. Here is what I get:

    jas@himalaya2 ~ % sudo -u _mysql /opt/local/lib/mariadb-10.6/bin/mysql_install_db
    Password:
    shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    Installing MariaDB/MySQL system tables in '/opt/local/var/db/mariadb-10.6' ...
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
    /opt/local/lib/mariadb-10.6/bin/mysql_install_db: line 525: 71810 Abort trap: 6           "$mysqld_bootstrap" $defaults $defaults_group_suffix 
    "$mysqld_opt" --bootstrap $silent_startup "--basedir=$basedir" "--datadir=$ldata" --log-warnings=0 --enforce-storage-engine="" "--plugin-dir=${plugindir}" $args --max_allowed_packet=8M --net_buffer_length=16K
    job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied

    Installation of system tables failed!  Examine the logs in
    /opt/local/var/db/mariadb-10.6 for more information.

    The problem could be conflicting information in an external
    my.cnf files. You can ignore these by doing:

    shell> /opt/local/lib/mariadb-10.6/bin/mysql_install_db --defaults-file=~/.my.cnf

    You can also try to start the mysqld daemon with:

    shell> /opt/local/lib/mariadb-10.6/bin/mariadbd --skip-grant-tables --general-log &

    and use the command line tool /opt/local/lib/mariadb-10.6/bin/mariadb
    to connect to the mysql database and look at the grant tables:

    shell> /opt/local/lib/mariadb-10.6/bin/mysql -u root mysql
    mysql> show tables;

    Try 'mysqld --help' if you have problems with paths.  Using
    --general-log gives you a log in /opt/local/var/db/mariadb-10.6 that may be helpful.

    The latest information about mysql_install_db is available at
    https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
    You can find the latest source at https://downloads.mariadb.org and
    the maria-discuss email list at https://launchpad.net/~maria-discuss

    Please check all of the above before submitting a bug report
    at https://mariadb.org/jira

    jas@himalaya2 ~ % 

So you can see why I say there is a permissions issue. At this point, looks like some major failures. Looking at the logs as advised is no help because the only log files there are not text files:

    jas@himalaya2 ~ % cd /opt/local/var/db/mariadb-10.6
    jas@himalaya2 mariadb-10.6 % ls
    aria_log.00000001   ib_logfile0     mysql
    aria_log_control    ibdata1
    ddl_recovery.log    ibtmp1
   jas@himalaya2 mariadb-10.6 % 

Also trying to start without the grant tables gives yet more errors that seem to be permissions errors.

And there are no other my.cnf files on the system, so that hint is no help either.

So why does the plain vanilla steps I have take lead to all these issues? Monterey?

Answer 1

macOS 12.3.1 Monterey (M1 Pro)
MacPorts 2.7.2
mariadb-10.6

I used MacPorts to install the port mariadb-10.6, then I used the following instructions to configure mariadb:

How to install MariaDB server on Mac with MacPorts

Apparently, to configure mariadb you need to:

1. Create a directory for the database files.
2. Use the tool mysql_install_db to create the initial database files, which will also create two users: _mysql@localhost and root@localhost.
3. Start mariadb using the tool mysqld_safe and use a flag to specify where the database files are located.
4. Shutdown mariadb in another Terminal window using the command mysqladmin shutdown.

I got some permission denied errors along the way, so I must have done something wrong. As a result, I started over by deleting the directory I created in this step:

sudo mkdir -p /opt/local/var/db/mariadb-10.6

like so:

/opt/local/var/db% sudo rm -rf ./mariadb-10.6 

Then I followed the instructions again and that eliminated the permission denied errors:

% sudo mkdir -p /opt/local/var/db/mariadb-10.6
% sudo chown -R _mysql:_mysql /opt/local/var/db/mariadb-10.6 
% sudo -u _mysql /opt/local/lib/mariadb-10.6/bin/mysql_install_db 
Installing MariaDB/MySQL system tables in '/opt/local/var/db/mariadb-10.6' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is _mysql@localhost, it has no password either, but
you need to be the system '_mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:
cd '/opt/local' ; /opt/local/lib/mariadb-10.6/bin/mysqld_safe --datadir='/opt/local/var/db/mariadb-10.6'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/opt/local/mysql-test' ; perl mysql-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

But, then I got another permission denied error when I tried to start mariadb:

% sudo -u _mysql /opt/local/lib/mariadb-10.6/bin/mysqld_safe --datadir='/opt/local/var/db/mariadb-10.6'
220511 16:26:13 mysqld_safe Logging to '/opt/local/var/db/mariadb-10.6/7studs-MacBook-Pro.local.err'.
mkdir: /opt/local/var/run/mariadb-10.6: Permission denied
220511 16:26:13 mysqld_safe Fatal error Can't create database directory '

So, I decided to create the directory in the error message myself:

 % sudo mkdir /opt/local/var/run/mariadb-10.6

and change the ownership to match the earlier directory that the instructions had me create:

 % sudo chown -R _mysql:_mysql /opt/local/var/run/mariadb-10.6 

Then, I tried to start mariadb again:

% sudo -u _mysql /opt/local/lib/mariadb-10.6/bin/mysqld_safe --datadir='/opt/local/var/db/mariadb-10.6'
220511 16:30:48 mysqld_safe Logging to '/opt/local/var/db/mariadb-10.6/7studs-MacBook-Pro.local.err'.
220511 16:30:48 mysqld_safe Starting mariadbd daemon with databases from /opt/local/var/db/mariadb-10.6

Success! Enough of these long paths to the command names. I added the following to my ~/.zshrc file:

#For mariadb:                                                                 
export PATH="/opt/local/lib/mariadb-10.6/bin/:$PATH" 

Then, in another terminal window instead of using /opt/local/lib/mariadb-10.3/bin/mysql I was able to simply use the command:

~% mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.7-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.002 sec)

Personally, I would rather have the mariadb configuration set up for me out of the box, and just be able to start/stop mariadb with some simple commands after adding something to my PATH. Then, I could add on layers of security or move db files to different locations and change a configuration file to indicate where they were--as needed.

Then there was this:

% mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Enter current password for root (enter for none): 
Aborting!

Cleaning up...
~% 

Hitting enter produced the access denied error. So, see here:

https://stackoverflow.com/questions/20270879/whats-the-default-password-of-mariadb-on-fedora

At some point, I shut my Terminal window without shutting down mariadb, and when I tried to start mariadb I got a bunch of permission denied errors:

%sudo -u _mysql /opt/local/lib/mariadb-10.6/bin/mysqld_safe --datadir='/opt/local/var/db/mariadb-10.6'
~% sudo -u _mysql /opt/local/lib/mariadb-10.6/bin/mysqld_safe --datadir='/opt/local/var/db/mariadb-10.6'
shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
chdir: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
220511 18:05:18 mysqld_safe Logging to '/opt/local/var/db/mariadb-10.6/7studs-MacBook-Pro.local.err'.
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
220511 18:05:18 mysqld_safe A mysqld process already exists
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: Permission denied

Shutting down mariadb cured that. I went into another Terminal window:

~% mysqladmin shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user '7stud'@'localhost''
~% sudo mysqladmin shutdown
~%

Also, I did not set a root password in response to the mysql_secure_installation questions, and now to launch the mariadb client, I have to use sudo:

% sudo mysql

Answer 2

Macports Mariadb 10.3 doesn't work with Monterey. I have to use brew until someone fixes it.