Bug 246694 - databases/mariadb104-server: my.cnf no longer found unter /usr/local/etc
Summary: databases/mariadb104-server: my.cnf no longer found unter /usr/local/etc
Status: Closed FIXED
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: Any Any
: --- Affects Only Me
Assignee: Bernard Spil
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-05-24 13:33 UTC by theis
Modified: 2020-07-04 21:08 UTC (History)
3 users (show)

See Also:
bugzilla: maintainer-feedback? (brnrd)


Attachments
mkdir /var/run/mysql during install (1.12 KB, patch)
2020-05-31 15:38 UTC, VVD
brnrd: maintainer-approval+
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description theis 2020-05-24 13:33:59 UTC
After my update to 10.4.13 it seems that the server doesn't look for my.cnf under /usr/local/etc anymore but in the mysql_dbdir. At least one server suddenly had the server_id 1 instead of the configured 3, and after I added the option mysql_optfile="/usr/local/etc/my.cnf" to /etc/rc.conf, it went back to normal.

I'm quite sure it was OK with 10.4.12 because after that update the server also stopped replication and writing the binary log files.

I haven't found any notice in UPDATING. Nor could I figure out what change could possibly triggered this ...
Comment 1 rshare 2020-05-25 22:38:31 UTC
The latest revision 10.4.13_1 removed these lines from the post-patch section of the Makefile:

${REINPLACE_CMD} 's|/usr/bin/env python|${PYTHON_CMD}|' ${WRKSRC}/CMakeLists.txt
${REINPLACE_CMD} 's|%%PREFIX%%|${PREFIX}|g' ${WRKSRC}/mysys/my_default.c
${REINPLACE_CMD} 's|%%LOCALBASE%%|${LOCALBASE}|g' ${WRKSRC}/scripts/mysql_config.sh \ 

and only left

${REINPLACE_CMD} 's|%%LOCALBASE%%|${LOCALBASE}|g' \
${WRKSRC}/storage/tokudb/PerconaFT/cmake_modules/TokuThirdParty.cmake


Now on my machine mysqld is looking for the my.cnf file in %%PREFIX%%/etc instead of /usr/local/etc, so I guess the seds related to %%PREFIX%% shouldn't have been removed, or the patch that puts these into the source needs to be adapted, too.

When I put the seds back into the Makefile and build the port again, it works as expected.
Comment 2 Bernard Spil freebsd_committer 2020-05-26 09:48:30 UTC
Sorry for this! Fix coming up, taken a bit longer as it is part of an overhaul of the port.
Comment 3 commit-hook freebsd_committer 2020-05-26 09:59:33 UTC
A commit references this bug:

Author: brnrd
Date: Tue May 26 09:58:41 UTC 2020
New revision: 536602
URL: https://svnweb.freebsd.org/changeset/ports/536602

Log:
  databases/mariadb104-server: Fix configuration location / overhaul

   * Fix configuration location [1]
   * Share patches between client and server
   * Provide minimal configuration for client and server
   * Make the wsrep config a sample only
   * Fixup plists

  PR:		246694 [1]
  Reported by:	<theis gmx at>
  MFH:		2020Q2

Changes:
  head/databases/mariadb104-client/Makefile
  head/databases/mariadb104-client/files/
  head/databases/mariadb104-client/pkg-plist
  head/databases/mariadb104-server/Makefile
  head/databases/mariadb104-server/files/client.cnf.sample.in
  head/databases/mariadb104-server/files/my.cnf.sample.in
  head/databases/mariadb104-server/files/patch-man_CMakeLists.txt
  head/databases/mariadb104-server/files/pkg-message.in
  head/databases/mariadb104-server/files/server.cnf.sample.in
  head/databases/mariadb104-server/pkg-plist
Comment 4 theis 2020-05-26 10:41:42 UTC
No problem and thanks for the fix.

Wouldn't it make sense to add a notice somewhere (UPDATING or pkg message) that the location was wrong? It could have affected more people who may not be aware of it. And in my case I had to manually intervene to make sure replication is working again.
Comment 5 VVD 2020-05-27 23:47:57 UTC
In databases/mariadb104-server/files/mysql-server.in file should be something like this:
if [ ! -d /var/run/${name} ]; then
install -d -o %%USERS%% -g %%GROUPS%% /var/run/${name}
fi
Comment 6 VVD 2020-05-27 23:48:57 UTC
(In reply to theis from comment #4)
20200526:
  AFFECTS: users of databases/mariadb104-client, databases/mariadb104-server
  AUTHOR: brnrd@FreeBSD.org

  The ports now add sample configuration files to /usr/local/etc/mysql. You
  must merge your client configation with the conf.d/client.cnf and your
  server configuration with conf.d/server.cnf.
Comment 7 VVD 2020-05-27 23:50:44 UTC
(In reply to VVD from comment #5)
Into mysql_prestart().
Comment 8 theis 2020-05-28 08:33:23 UTC
Is it a result of the changes that PHP's mysqli_connect cannot connect to localhost anymore and I have to use 127.0.0.1 instead?
Comment 9 VVD 2020-05-28 11:32:36 UTC
(In reply to theis from comment #8)
/var/run/mysql/mysql.sock instead /tmp/mysql.sock

You have to set:
mysqli.default_socket = /var/run/mysql/mysql.sock

There are 2 other:
pdo_mysql.default_socket = /var/run/mysql/mysql.sock
mysql.default_socket = /var/run/mysql/mysql.sock
Comment 10 VVD 2020-05-28 11:34:01 UTC
(In reply to VVD from comment #9)
In /usr/local/etc/php.ini file.
Comment 11 theis 2020-05-28 12:23:32 UTC
I have a server with a dozen or so PHP applications running, each using mariadb. So my question is not "how do I configure mysqli connector" but "why do I have to change my configurations from localhost to its IP address". That shouldn't make a difference.
 
Maybe it was a change I did in the past, my initial installation of PHP was a couple of years before. But it could also be a change which came with the last update and then many other people would be affected. I can't test it at the moment, I don't have a system with a fresh installation of mariadb and e.g. phpMyAdmin at hand.
Comment 12 VVD 2020-05-28 12:28:53 UTC
(In reply to theis from comment #11)
> "why do I have to change my configurations from localhost to its IP address"
You do not have to do this.
Comment 13 theis 2020-05-28 13:14:02 UTC
Ah, the fog begins to lift: mysqli uses unix sockets when you use 'localhost' as a host name and TCP when you use '127.0.0.1'. 

And don't forget to restart Apache ... :)
Comment 14 VVD 2020-05-28 14:39:24 UTC
(In reply to theis from comment #13)
> Ah, the fog begins to lift: mysqli uses unix sockets when you use 'localhost' as a host name and TCP when you use '127.0.0.1'. 
> 
> And don't forget to restart Apache ... :)
Exactly!

It took me 30 minutes to solve this issue…
Comment 15 Bernard Spil freebsd_committer 2020-05-29 22:41:52 UTC
(In reply to VVD from comment #9)
I'm not fully sure why this happens.

The client should default to ${LOCALBASE}/etc/my.cnf first. ONLY if you have no my.cnf there should it fall back to the (new) ${LOCALBASE}/etc/mysql/my.cnf 

> $ mysql --help | head
> mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for FreeBSD12.1 (amd64) using  EditLine  wrapper
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
> 
> Usage: mysql [OPTIONS] [database]
> 
> Default options are read from the following files in the given order:
> /usr/local/etc/my.cnf /usr/local/etc/mysql/my.cnf ~/.my.cnf
Comment 16 Bernard Spil freebsd_committer 2020-05-29 23:02:52 UTC
(In reply to Bernard Spil from comment #15)
Just dawned on me...

If you hadn't previously configured anything, the socket would default to /tmp/mysql.sock. This now changed to a more appropriate /var/run/mysql/mysql.sock.
 This isn't sufficiently reflected in the UPDATING entry.

Sorry for the inconvenience! I'll align the default socket patch in a future update!
Comment 17 VVD 2020-05-29 23:20:19 UTC
(In reply to Bernard Spil from comment #16)
And, plz, do this:
> In databases/mariadb104-server/files/mysql-server.in file should be something like this:
mysql_prestart()
{
…
        if [ ! -d "/var/run/${name}" ]; then
                install -d -o %%USERS%% -g %%GROUPS%% /var/run/${name}
        fi
}
Comment 18 theis 2020-05-30 06:05:32 UTC
(In reply to Bernard Spil from comment #16)
Exactly. I wasn't even aware (PHP) clients were using unix sockets and not TCP.

I think the new location of the socket is better. 

The problem is I wasn't aware it changed. An entry about it, and how to change it back, in UPDATING may have helped.

Do you know if (PHP) clients, esp. mysqli, are supposed to read my.cnf? If so, then I just missed "restart apache". If not things become more complicated ...
Comment 19 VVD 2020-05-30 11:45:53 UTC
(In reply to theis from comment #18)
> o you know if (PHP) clients, esp. mysqli, are supposed to read my.cnf? If so, then I just missed "restart apache". If not things become more complicated ...
I don't know. Perhaps they don't use my.cnf at all…
For me application with pdo_mysql (MODX) didn't work until I set pdo_mysql.default_socket=/var/run/mysql/mysql.sock in /usr/local/etc/php.ini and restart apache.
Same with MediaWiki with mysqli: didn't work until I set mysqli.default_socket=/var/run/mysql/mysql.sock in /usr/local/etc/php.ini and restart apache.
But! Both my pdo_mysql and mysqli are php 5.6. But I don't think that the current versions for 7.x have changed the behavior.
Comment 20 Bernard Spil freebsd_committer 2020-05-31 09:32:55 UTC
Sorry for all the inconvenience due to this POLA violation! The 10.4.13_1 version was broken, 10.4.13_2 fixed that but introduced side-effects. The addition of the ${ETCDIR}/my.cnf, local.cnf and server.cnf files may overlap with your current configuration. Hence the UPDATING entry.

Please always verify the paths using methods below

10.4.13_1
> root@db:/root$ mysql --help | head
> mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for FreeBSD12.1 (amd64) using  EditLine wrapper
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
> 
> Usage: mysql [OPTIONS] [database]
> 
> Default options are read from the following files in the given order:
> /usr/local/etc/my.cnf ~/.my.cnf
> root@db:/root$ /usr/local/libexec/mysqld --verbose --help | head
> 2020-05-30 15:36:12 0 [Note] Plugin 'FEEDBACK' is disabled.
> 2020-05-30 15:36:12 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
> /usr/local/libexec/mysqld  Ver 10.4.13-MariaDB for FreeBSD12.1 on amd64 (FreeBSD Ports)
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
> Starts the MariaDB database server.
>
> Usage: /usr/local/libexec/mysqld [OPTIONS]
>
> Default options are read from the following files in the given order:
> %%PREFIX%%/etc/my.cnf %%PREFIX%%/etc/mysql/my.cnf ~/.my.cnf
> The following groups are read: mysqld server mysqld-10.4 mariadb mariadb-10.4 mariadbd mariadbd-10.4 client-server
Note how this is broken: PREFIX wasn't replaced by REINPLACE_CMD in PORTREVISION=1
Also note how client and server differ in location of my.cnf files.

Then came PORTREVISION=2
> root@db:/root$ mysql -h | head
> mysql: option '-h' requires an argument
> root@db:/root$ mysql --help | head
> mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for FreeBSD12.1 (amd64) using  EditLine wrapper
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
> Usage: mysql [OPTIONS] [database]
>
> Default options are read from the following files in the given order:
> /usr/local/etc/my.cnf /usr/local/etc/mysql/my.cnf ~/.my.cnf
> The following groups are read: mysql mariadb-client client client-server client-mariadb
> The following options may be given as the first argument:
> --print-defaults          Print the program argument list and exit.
> root@db:/root$ /usr/local/libexec/mysqld --verbose --help | head
> 2020-05-31  9:13:56 0 [Note] Plugin 'FEEDBACK' is disabled.
> 2020-05-31  9:13:56 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
> /usr/local/libexec/mysqld  Ver 10.4.13-MariaDB for FreeBSD12.1 on amd64 (FreeBSD Ports)
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
> 
> Starts the MariaDB database server.
> 
> Usage: /usr/local/libexec/mysqld [OPTIONS]
> 
> Default options are read from the following files in the given order:
> /usr/local/etc/my.cnf /usr/local/etc/mysql/my.cnf ~/.my.cnf
> The following groups are read: mysqld server mysqld-10.4 mariadb mariadb-10.4 mariadbd mariadbd-10.4 client-server
Note that both client and server use the same configuration files.
Also note that the commit adds 4 sample configuration files in ${ETCDIR}/. One unifying my.cnf file that includes conf.d/*.cnf: a client.cnf for mariadb104-client and a server.cnf for mariadb104-server (and wsrep.cnf only as a sample).
Where this goes wrong is when you DIDN'T have a my.cnf in ${PREFIX}/etc/my.cnf. It will now apply defaults from client.cnf which are different from the defaults in the binary file (mysys/my_default.c).
Lastly, mariadb104-server will use /var/db/mysql/my.cnf file unless you define mysql_optfile in rc.conf. This is all a historical thing I inherited when I took maintainership of the MariaDB ports in 2017 (after prior involvement since 2014).

Ahead is more churn. I want to make to force the port to use hier (7)
 1. Remove files/patch-mysys_my__default.c, replace with CMAKE_ARGS+= DEFAULT_SYSCONFDIR=${ETCDIR}
 2. Default mysql_optfile to ${ETCDIR}/my.cnf (i.e. etc/mysql/mysql.cnf)
 3. Remove as many patches as possible
This will be default in 10.5 at minimum.

Maintaining these ports is too labor instensive, hence I a deprecating older versions.

Again, sorry for your inconvenience! (please keep providing feedback and patches and PRs!)
Comment 21 theis 2020-05-31 10:03:54 UTC
(In reply to Bernard Spil from comment #20)
Dear Bernard,
First thank you for your work as maintainer. I know this is not easy with MariaDB being such a large port and having ties into so many others.
Also I like the new config layout. And the new location of mysql.sock is better than the old one.

If you had just added a hint in UPDATING that you changed the default of mysql.sock and how / where to set it back to before. That's all I missed :)

Btw., I did have a /usr/local/etc/my.cnf, it just had settings for replication. Maybe that caused some problems?

Anyway, I got it working again, I understand somewhat better how it works. And thank you again for spending so much time into improving MariaDB!
Comment 22 VVD 2020-05-31 15:38:31 UTC
Created attachment 215110 [details]
mkdir /var/run/mysql during install

Without it (/var/run/mysql) server didn't start silently during trying create /var/run/mysql/mysql.sock file.
Comment 23 Bernard Spil freebsd_committer 2020-05-31 15:49:57 UTC
Comment on attachment 215110 [details]
mkdir /var/run/mysql during install

Looks very good to me!
Comment 24 eborisch+FreeBSD 2020-06-18 16:17:39 UTC
The patch to create /var/run/mysql during install has not been applied, so updated servers (beyond the conf.d POLA issue) silently fail when starting.

Thanks for the hard work maintaining the port!
Comment 25 VVD 2020-06-18 17:15:00 UTC
(In reply to Bernard Spil from comment #23)
Bernard, commit it, plz.
Comment 26 VVD 2020-06-18 17:16:49 UTC
(In reply to VVD from comment #25)
Possible 10.1, 10.2, 10.3 and other mysql clones need this patch too.
Comment 27 commit-hook freebsd_committer 2020-06-21 13:33:07 UTC
A commit references this bug:

Author: brnrd
Date: Sun Jun 21 13:32:28 UTC 2020
New revision: 539762
URL: https://svnweb.freebsd.org/changeset/ports/539762

Log:
  MFH: r536602

  databases/mariadb104-server: Fix configuration location / overhaul

   * Fix configuration location [1]
   * Share patches between client and server
   * Provide minimal configuration for client and server
   * Make the wsrep config a sample only
   * Fixup plists

  PR:		246694 [1]
  Reported by:	<theis gmx at>

  Approved by:	ports-secteam (joneum)

Changes:
_U  branches/2020Q2/
  branches/2020Q2/databases/mariadb104-client/Makefile
  branches/2020Q2/databases/mariadb104-client/files/
  branches/2020Q2/databases/mariadb104-client/pkg-plist
  branches/2020Q2/databases/mariadb104-server/Makefile
  branches/2020Q2/databases/mariadb104-server/files/client.cnf.sample.in
  branches/2020Q2/databases/mariadb104-server/files/my.cnf.sample.in
  branches/2020Q2/databases/mariadb104-server/files/patch-man_CMakeLists.txt
  branches/2020Q2/databases/mariadb104-server/files/pkg-message.in
  branches/2020Q2/databases/mariadb104-server/files/server.cnf.sample.in
  branches/2020Q2/databases/mariadb104-server/pkg-plist
Comment 28 eborisch+FreeBSD 2020-06-22 21:34:43 UTC
Still no sign of /var/run/mysql being made in -server/Makefile or in the plist... Or has something else changed to remove the need for it?
Comment 29 VVD 2020-07-04 21:08:13 UTC
(In reply to eborisch+FreeBSD from comment #28)
Patch with this is here: https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=247747#c7