Bug 194305

Summary: databases/mariadb,mysql,percona*-server: Does not respect/honour user-specified datadir directive in my.cnf
Product: Ports & Packages Reporter: MMacD <scratch65535>
Component: Individual Port(s)Assignee: freebsd-ports-bugs (Nobody) <ports-bugs>
Status: Open ---    
Severity: Affects Many People CC: ale, brnrd, daniel, flo, fluffy, joneum, koobs, mmokhi, never, rainer, rootservice, solarcatcher, w.schwarzenfeld
Priority: --- Keywords: needs-patch, needs-qa
Version: LatestFlags: koobs: maintainer-feedback? (never)
koobs: merge-quarterly?
Hardware: Any   
OS: Any   
URL: https://svnweb.freebsd.org/ports?view=revision&revision=132637

Description MMacD 2014-10-11 18:07:51 UTC
Despite datadir being set in my.cnf, mariadb 5.5 still expects to find the files in the default location (/var/db/mysql).

user        = mysql 
port        = 3306
socket      = /tmp/mysql.sock
datadir     = /MC/mysql_data
#key_buffer_size = 16M
key_buffer_size = 64M
Comment 1 Mark Linimon freebsd_committer freebsd_triage 2014-10-17 20:34:10 UTC
Fix synopsis and notify maintainer.
Comment 2 John Marino freebsd_committer 2014-10-31 18:42:13 UTC
moving out of "triage" and into "open" since the maintainer is well-aware.
Comment 3 MMacD 2016-08-03 14:36:56 UTC
This bug remains a problem.  Is there an ETA on a fix?
Comment 4 Markus Kohlmeyer 2016-08-05 21:05:53 UTC
Did you set mysql_dbdir in /etc/rc.conf to your datadir?

BTW: Maybe it is time to upgrade to MariaDB 10.1 or MySQL 5.7 as MariaDB 5.5 is quite old and officially unsupported.
Comment 5 MMacD 2016-08-10 12:30:24 UTC
"Did you set mysql_dbdir in /etc/rc.conf to your datadir?"

Yes, I did finally stumble across that non-standard way of doing it.  It was gratifying to be able to put data where I want it (under the care of ZFS on a 3-way mirror) but still would have preferred that the standard had been followed, and don't really understand why it wasn't.

"BTW: Maybe it is time to upgrade to MariaDB 10.1 or MySQL 5.7 as MariaDB 5.5 is quite old and officially unsupported."

Actually, finding the bug still alive and un-swatted in v10 was what prompted me to add my comment.  I've MariaDB 10.1 running under 10.2 on my server-of-all-work and under 10.3 on my utility workstation.
Comment 6 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-10 12:52:02 UTC
Maintainer, can you please provide feedback on this issue

CC other MySQL/MariaDB/Percona people
Comment 7 Markus Kohlmeyer 2016-08-10 13:02:44 UTC
This is not a bug and nothing new, introduced 11 years(!) ago with ports r132637
Comment 8 MMacD 2016-08-10 14:48:10 UTC
Gratuitously inventing a new, idiosyncratic, poorly-documented replacement for a longstanding, well-documented standard isn't a bug?  We used to call them "concept bugs" because the people who invented them always seemed to have trouble with the concept of "standard".
Comment 9 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-10 15:03:38 UTC
(In reply to MMacD from comment #8)

Let's keep the discussion civil and constructive. We are all here to improve FreeBSD.

It sounds like this is a request to allow/respect/honour user settings/override, which is perfectly reasonable, whether or not any particular individuals agree or not. 

"because that's how its been done in the past" is not legitimate reason not to ask now.

Has anyone been able to isolate exactly what/where hardcodes/locks the location?

Pending maintainer-feedback.
Comment 10 Mahdi Mokhtari freebsd_committer freebsd_triage 2016-08-10 15:22:14 UTC
(In reply to MMacD from comment #8)
(In reply to Kubilay Kocak from comment #9)
Does the bug affects MySQL57 users?
Is it upstream bug, or caused by specific patches?
Comment 11 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-10 15:27:41 UTC
(In reply to Mahdi Mokhtari from comment #10)

I don't know the root cause, but it's probably somewhere in ports r132637 as per comment 7 (at least mysql5*, mysql60, percona* ports were changed)
Comment 12 MMacD 2016-08-10 15:43:33 UTC
"because that's how its been done in the past" is not legitimate reason not to ask now."

Ages ago, I was one of the hundreds who packed an auditorium to hear the late Prof. Sir Maurice Wilkes talk about standards.  He raised a positive roar of laughter with his opening remark that "the really nice thing about setting a standard is that it forces everyone who comes after us to make the same mistake we did".  He then went on to talk about good and bad standards, what criteria should be met before abandoning them, etc.  We all talked for weeks after about the issues he raised and points he made.

If I seem overly testy about this topic, I apologise.  But it's caused me quite a bit of trouble over the years.
Comment 13 Markus Kohlmeyer 2016-08-10 17:49:19 UTC
(In reply to Mahdi Mokhtari from comment #10)
Yes, MySQL 5.7 is affected too.

Affected are *all* MySQL, MariaDB and Percona ports.

(In reply to Kubilay Kocak from comment #11)
If i underatnd this all right, then it was/is like this:

The rc.conf/initscript options had been introduced, because FreeBSD uses a different standard for the location of the databases (MySQL/MariaDB/Percona-Standard: /var/lib/mysql vs. FreeBSD-Standard: /var/db/mysql). As MySQL/MariaDB/Percona need to know the database location on first startup and FreeBSD did not installed a default my.cnf in the past, there had to be a commandline param to pass and this commandline param had been made configureable via the rc.conf option.

In the progress on updating the MySQL 5.7 port from 5.7.10 to 5.7.12 i introduced a default my.cnf and changed the default location of the my.cnf from the old FreeBSD-Location (/var/db/mysql/my.cnf) to the MySQL/MariaDB/Percona-Standard and according to hier(8) to /usr/local/etc/mysql/my.cnf
This change and the new default my.cnf should first be ported to the other MySQL/MariaDB/Percona ports and then we can think about getting rid of the rc.conf/initscript options. But without this change and the default my.cnf we have to stick with the 11 year old solution.

Just my $0.02
Comment 14 MMacD 2016-08-10 19:38:54 UTC
In rx to Markus (also Kubilay and Mahdi):
"FreeBSD uses a different standard for the location of the databases (MySQL/MariaDB/Percona-Standard: /var/lib/mysql vs. FreeBSD-Standard: /var/db/mysql"

That's not the FreeBSD standard, per hier(7).  Non-system databases shouldn't be in /var/db: "[/var/]db [holds] miscellaneous automatically generated system-specific database files".

The MariaDB docs do say that /var/lib/mysql is the default location, but they make clear that the datadir directive in my.cfg is meant to be set to provide the real location: "The data directory location is controlled by the datadir variable. ... The default is /var/lib/mysql but it is often changed, like for example if you are using a RAID array."

Since hier(7) specifies that only scratch database files generated by and for the system are meant to go in /var/db, MariaDB's /var/lib is more appropriate as a temporary staging area-- hier(7) doesn't allocate that subtree.  

The center of the problem, though, is that datadir in my.cnf is and has been being ignored, making it hard on nitwits like me who didn't/don't know about the non-standard /etc/rc.conf kludge.
Comment 15 Markus Kohlmeyer 2016-08-10 20:27:30 UTC
(In reply to MMacD from comment #14)
I don't know when and who decided to put the MySQL-Databases in /var/db/mysql but it was before MySQL 5.0 entered the portstree, so it was more then decade in the past, probably back to times when MySQL 3.23 was current, i don't know.

What i know is, that it is absolutly no problem to fix it at least for new installs, but as we have seen with the change of my.cnf location for the MySQL 5.7.12 port, it will give problems with existing installs and admins who used to use /var/db/mysql and/or rc.conf for years. Additional there seems to be a problem with binary-packages/pkg while updating from MySQL < 5.7.12 caused by the my.cnf changes (as i don't use binary-packages/pkg i can't confirm that myself, but there exists a bugreport/comment).

Personally i would be glad to get this completely fixed for new installs and "good admins" aka admins who read ports/UPDATING and pkg-message, but this type of forced and agressive changes are against FreeBSD policys for some good reasons.

I could provide these patches for MySQL 5.6 and 5.7 in the next days, but as i said, they will probably not be committed as they will break FreeBSD policy by breaking existing expected behavior for existing installs. Without manual work by admins of existing installs, there will be incompatibilities up to non-working installs and if all goes wrong there is even a (realy) small possiblity of dataloss.

So, i can't and i don't want to decide anything here, but if the maintainers and portsteam decide to do this changes anyway, then i'm glad to provide patches at least for MySQL 5.6/5.7 which can easy be ported to MariaDB and Percona.
Comment 16 MMacD 2016-08-10 21:12:15 UTC
in rx to Markus

I certainly agree that breaking anything would be A Very Bad Idea, but 99% of the problem could be solved by again following the MariaDB/MySQL docs about the datadir directive in my.cnf.  Don't disallow what's allowed now, just re-enable datadir in my.cnf.  That keeps the problem solved for those who already know about /etc/rc.conf, and again solves the problem for those who read the MariaDB/MySQL docs.

Then, a future MariaDB/MySQL port (maybe the next one?) could prominently announce in the ephemera at the end of the install that, while the db files used to be in /var/db/mysql, they can now be found in /var/lib/mysql *AND* that the datadir directive in my.cfg should be set if a different location is desired.  Then the /etc/rc.conf kludge etc. could be allowed to decay naturally.

(Sys management has never been a job or a hobby, for me.  The only boxes I've ever managed are the ones at my own desk.  Mostly VMS then, FreeBSD and Windoze now.  I've always worked in product dev:  developer, dev boss, architect, program manager, so to me the box has always and only been a tool for getting something else done, and consequently I've never had the time to be a "good admin" :-)  )
Comment 17 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-11 07:33:44 UTC
(In reply to MMacD from comment #12)

For clarity:

My statement was defending/supporting you and specifically directed toward responding to Markus in comment 7 that just because it was 'nothing new, introduced 11 years(!) ago', it is no reason not to investigate changing/improving it now.
Comment 18 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-11 07:48:51 UTC
(In reply to MMacD from comment #14)

Appreciate the insight Markus.

So there's a few 'needs' that have been covered, and summarising so far:

1) Respect heir(7), or if we don't agree that that sufficient (based on its definition), then do what most ports do for databases by default (/var/db/PORTNAME). This is what the ports currently do.

We'll leave out the discussion about whether this de-facto thing is correct, good/bad, can be improved, etc for a separate conversion. This could also be known as 'put things where users (now) expect by default for databases in FreeBSD ports/packages'

2) Respect user choices/overrides if/when explicitly specified.

This is also a reasonable expectation, and is aligned with many other mechanics within ports, such as CC, *CFLAGS, DEFAULT_VERSIONS among others. The port currently supports 'one' method of doing this (rc.conf, a standard mechanic), but not another (my.cnf), which I believe is being argued (correctly), is standard, and expected to work. The port does currently not respect/do this.

3) A reliable, "good" user experience for a) default installations b) migrations / changes if and when any port/package changes are made with respect to this issue, ie: supporting (2).

@Markus / @MMacD Is the above a reasonable summation of the requirements from both of your perspectives?
Comment 19 Markus Kohlmeyer 2016-08-11 10:19:33 UTC
(In reply to Kubilay Kocak from comment #18)

To solve this we need at least:

* Provide a default my.cnf as i've done with the MySQL 5.7 port
* Teach and force existing users to merge their old my.cnf with the new default one
* Teach and force existing users to place the my.cnf only in /usr/local/etc/mysql/my.cnf respecting hier
* Change the initscript to remove the database location from the startup commandlines
* Find a way to do prev points automatically via pkg in case of binary-updates
* Update Docs if they exist

I'm not sure if some points violate one or more FreeBSD policy and when they do, if we should just ignore that policy to solve this "issue", or if we should respect the policy and stick with the current behavior.
This should be decided by someone with more power than the maintainers or me.
If you have the power to decide this, then i'll happy to hear and provide whatever patch needed to solve, at least for MySQL 5.6/5.7.
Comment 20 Markus Kohlmeyer 2016-08-11 10:52:31 UTC
(In reply to Markus Kohlmeyer from comment #19)

I forgot some points:

* Change the default databases location from /var/db/mysql to /var/lib/mysql as per MySQL-Documentation
* Teach existing users about the new default databases location
* Create documention to tell existing users how to move their existing databases the new default databases location
* Maybe we should also create documentation about the possible rc.conf options
Comment 21 MMacD 2016-08-11 11:12:37 UTC
in rx to Kubilay's 18:

As I said to Markus, for my part just re-enabling datadir in my.cnf without doing anything else solves 99% of the problem.  find / -name is very good at locating things that aren't where they're expected to be.  As long as they can be moved when found, there's no real problem, just inconvenience.

And I very much agree with Markus that a default my.cnf should be supplied --and sample ones for different configs as well: there's nothing like having good examples in front of you when you're trying to work out how to do something.  If desired, the samples could probably be nicked from the Windows port. 

But re-enabling datadir in my.cnf, while a sufficiently complete patch from my perspective, is not really the best from a general perspective.

I hope we can agree that practice should never contradict documentation.  Documentation should always provide information, not fairy tales or misinformation.

So if /var/db is to be the place for all databases, then at a minimum hier(7) must be re-written to reflect that.  But I'd mildly prefer, with Markus, that /var/lib/mysql be used as a transient staging area (which would be hier(7) and MySQL/MariaDB docs conformant).

Confirming /var/db as the official home would increase entropy, which doesn't seem like a good idea:  per hier(7), /var is dedicated to "multi-purpose log, temporary, transient, and spool files".  Making it also the home of non-system databases is hard to understand, given the stated (and somewhat obvious to inspection) purpose of /var and the constantly reinforced /usr/local convention.  

Most people familiar with the /usr/local convention would expect the database files to be in /usr/local/mysql/data|databases, not /var.  Even people who know that maintainers put db files into /var/db would not be surprised to find a new release announcing that it was putting them in /usr/local/mysql/data instead because /usr/local is a stronger and thus anti-entropic convention.  It's "more expected".

The less entropy, the better, and the sooner the better.  But for the moment, just having a functional datadir directive in my.cnf is enough for me.  I can bear the messiness and entropy if everyone else can.
Comment 22 Markus Kohlmeyer 2016-08-11 11:44:45 UTC
(In reply to MMacD from comment #21)

> As I said to Markus, for my part just re-enabling datadir in my.cnf without doing anything else solves 99% of the problem.

This "little" change implies all the points i mentioned in my two  prev comments, so it's a lot more complicated as it looks to you.

BTW: I'm sys-admin and using MySQL since 3.23 on different OS, so i know the differences between the MySQL-Docs and the defaults on different OS and distributions like *BSD, *Linux and Windows. There are only a few OS where the paths and other defaults are exactly as documented by MySQL-Devs, most OS/Maintainers have different paths and defaults. As an admin you have to learn each system new from the ground up and documentation can assist you in most cases, but you have to adjust the docs according to your own systems "defaults" and/or "standards" and/or your personal preferences. That's why we talk about options and not hardcoded paths.
Personally i prefer to have all my data under /data so that my MySQL databases are in /data/db/mysql and not under /var/db or /var/lib or /usr/local ;)
Enough off-topic for now...
Comment 23 MMacD 2016-08-11 12:56:25 UTC
"This "little" change implies all the points i mentioned in my two  prev comments, so it's a lot more complicated as it looks to you."

I don't see in what way that's true, Markus.  It's a documented config directive, nothing more.  It would use functionally the same code as for the /etc/rc.conf kludge:  read a line, crack out the tokens, interpret the keyword, and stuff the path into the lookup table.  

The code to interpret and act on that directive probably still exists in the source, since it'd be pointless extra work to actually delete it from each new release!  I've not looked, but I'd almost be willing to bet folding money that the most that was done was comment it out in the switch in the config routine that fingers through my.cfg.
Comment 24 MMacD 2016-08-11 13:36:03 UTC
in rx to Markus's 22:  I'm separating this part out because, as you say, it's OT.

"As an admin you have to learn each system new from the ground up and documentation can assist you in most cases, but you have to adjust the docs according to your own systems "defaults" and/or "standards" and/or your personal preferences. That's why we talk about options and not hardcoded paths."

Professional sysadmins certainly have an important job-security motivation for keeping systems unstandardised and the learning curve vertical.  There's nothing wrong with that.

But I started out in the industry when there were hardly any standards that applied to more than a single product line from a single company.  My computing experience doesn't quite extend back to the days when to boot a system someone had to flip front panel switches to load the primary boot code byte by byte, but I did that once just for fun (on a PDP 4? don't remember) and was quite glad, after, that I'd never had to do it for real.

Standards are good.  Microsoft and Apple are the big winners not because their systems have any special virtues, but because they're standardised.  If you know how to make one Windoze or Mac box go, you can sit down at any other Windoze or Mac box and make it go too.  They might be slow and in the case of Windoze fragile, but they don't require people to learn each box "from the ground up".  That's a good thing, and it's what FreeBSD should aspire to, too.

I chose to build up my server-of-all-work from a FreeBSD 10.2 foundation rather than use a drop-in solution like FreeNAS because I really didn't (and don't) like not being able to understand the pfSense software in my firewall box.  It works very well, but I'd have to reverse-engineer it to understand anything about it because the pfSense guys made so many basic changes.  Maybe it runs much faster than if they'd left it alone, I don't know.  But I decided that I was not going to be that ignorant about my server box.  

The Unices are powerful, but they are a mess.  A *needless* mess.  Nobody would be buying Macs today if Apple had simply decided to run FreeBSD when they switched from Motorola to Intel.  It's something to remember.
Comment 25 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-11 13:52:57 UTC
Ladies/Gentleman, the issue tracker is for isolating/tracking issues and change requests. The mailing lists are for discussion. Please respect that distinction, as anything to the contrary wastes time we all have precious little of.

It's safe to say:

That /var/db/${PORTNAME} will remain the default given the number of ports that use it and it ought be possible to separate these two concerns. Let's beat the non-system-databases-need-a-new-home horse on the mailing lists.

That whatever change is made will need to made consistently across mysql/maria/percona/foo ports.

Can someone please comment on (and only on) exactly what precludes/prevents the correct functioning of the datadir argument in my.cnf as it stands today. This will save me (or others) the time of having to hunt it down.

I'd prefer any of the maintainers of the respective ports (all are now CC'd) to provide authoritative information, but will settle for anyone who actually knows the root cause.
Comment 26 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-11 13:55:10 UTC
Take this to coordinate isolation, change (patch) proposal, approval across multiple ports/maintainers.
Comment 27 MMacD 2016-08-11 16:09:25 UTC
It turns out, btw, that sample my.cfg files for large, medium, and small resource use are shipped in the tarball, so I'm not sure why they're not being installed at present.
Comment 28 Markus Kohlmeyer 2016-08-11 16:38:58 UTC
(In reply to Kubilay Kocak from comment #25)

As can be seen in https://svnweb.freebsd.org/ports/head/databases/mysql57-server/files/mysql-server.in?view=markup lines 51 and 56 we need the rc.conf option for the --datadir commandline option in the startup phase. The --datadir commandline option will then override the datadir option in the my.cnf and this behavior is correctly documented in the MySQL-Documentation as this is a MySQL builtin function. So the FreeBSD MySQL/MariaDB/Percona ports do absolutly nothing wrong and that's why i called this a non-issue back in comment #7

It's all about users like MMacD not understanding how initscripts and commandlines work and that FreeBSD choose other defaults than Sun/MySQL.
Comment 29 Kubilay Kocak freebsd_committer freebsd_triage 2016-08-11 16:51:35 UTC
(In reply to Markus Kohlmeyer from comment #28)

This is a final request to remain on-topic, respectful and professional. 

Considered opinions are fine and always welcome, opinions about other individuals or groups of people are not appropriate or relevant.

Finally, thank you for providing clarity on the mechanic involved.
Comment 30 Mahdi Mokhtari freebsd_committer freebsd_triage 2016-08-11 16:59:29 UTC
(In reply to Kubilay Kocak from comment #26)
(In reply to Markus Kohlmeyer from comment #28)

I like to add a point on what Markus introduced pointed (https://svnweb.freebsd.org/ports/head/databases/mysql57-server/files/mysql-server.in?view=markup).
I myself, call this incompatibility of standards [hier(7) + MySQL's default].
And that's why we used MY_DBDIR workaround in mysql-server.in (and also that words of warning)
The mysql_dbdir arg can be changed in rc-script.
-- maybe off-topic so I'm sorry to say it here --
That's why i say this is not "problem",
Not MySQL's nor FreeBSD's position is not "Wrong" or "Problematic" here IMO. because they suggest initial positions of files or default value of args, users can simply change things when they want.
Comment 31 Markus Kohlmeyer 2016-08-11 17:15:49 UTC
(In reply to Markus Kohlmeyer from comment #28)

If we realy want to get rid of the --datadir commandline option (which is possible), then we have a TODO which consists of at least comment #19 and comment #20 maybe even more.

That is a lot work and a lot possibilities to get problems with existing installs and also may break one or more FreeBSD policy.
Comment 32 MMacD 2016-08-11 19:59:24 UTC
I believe it's the case that the --datadir command-line option is meant to be a momentary alternative to the same option in my.cfg, not a replacement.  We see the same behavior in other situations, where a command-line option overrides normal behavior as defined in a config file.  I rarely use CLOs myself [nee, meine ich 'klo' nicht :-) ] but my impression is that most of what can be specified in my.cfg could be passed in the call instead, if one were so inclined.
Comment 33 Chris Hutchinson 2016-08-17 15:40:21 UTC
Couldn't all this be as simple as creating a symlink(2) in
/usr/local/etc/my.cnf to /var/db/my.cnf, and adding a bit
of additional (informative) noise in /var/db/mysql/my.cnf
indicating the symlink(2), and why things are done the way
they are on FreeBSD. So that those *not* familiar, can be
pointed to the mysql* man pages, to better understand how
they might better tailor things to their particular needs/

Just a thought.

@MMacD, @Markus; get a room! ;-)

Comment 34 Daniel Ylitalo 2016-08-21 19:07:04 UTC
I'm a quite heavy user of mysql/mariadb/percona databases myself and there is a really easy way of making this problem go away.

All thats needed in the rc script for these ports is a little check if the datadir line is present in my.cnf and if so output a notice which reads something as follows:
"datadir in FreeBSD is set through /etc/rc.conf, please see /usr/local/etc/rc.d/mysql-server"

I myself always set the datadir in /etc/rc.conf, but then again, I'm used to FreeBSD.
Comment 35 Walter Schwarzenfeld freebsd_triage 2018-02-07 11:12:46 UTC
Is this still relevant?
Comment 36 Kubilay Kocak freebsd_committer freebsd_triage 2018-05-11 04:34:31 UTC
Madhi is now a committer (and Bernard is still CC'd), both maintainers to ports for which this issue was reported, re-assign accordingly
Comment 37 rainer 2018-05-13 13:16:00 UTC
I found that it's also not that easy to change data_dir on Ubuntu etc.
Most people never bother to change it (because last time I looked, setting it in my.cnf also wasn't enough) and just create a symlink.
Additionally, Ubuntu has a habit of starting everything right after installing and that adds a whole mess of other problems...

At least on the FreeBSD-side, it always works by specifying it in rc.conf (and also specifying it in my.cnf, just to be sure and consistent). 

Personally, I'm fine with the status-quo - I do remember that it took some time to figure out you have to specify it in rc.conf, too.

That would be worth mentioning in a pkg-msg, IMO.
Comment 38 Rene Ladan freebsd_committer 2020-12-07 19:59:58 UTC
Assignee reset.
Comment 39 SolarCatcher 2021-03-08 16:03:49 UTC
Just found this bug report - after spending several hours trying to move the data-directory to the place suggested by percona for ZFS.

I can happily live with configuring the datadir through the mysql_dbdir variable in rc.conf. But a warning in /usr/local/etc/mysql/confd./server.cnf would be very useful. 

Currently the standard server.cnf file includes under [mysqld]

datadir  = /var/db/mysql

And, commented out, suggests setting it for ZFS to
datadir = /var/db/mysql/data

None of these configuration directives have any effect, because datadir is overriden by the rc variable mysql_dbdir when starting mysql-server via the script in rc.d. It would probably help to clearly state this in server.cnf.