This service will be undergoing maintenance at 00:00 UTC, 2016-08-01. It is expected to last about 1 hours
Bug 714426 - Provide a native mysql systemd service
Provide a native mysql systemd service
Status: CLOSED CURRENTRELEASE
Product: Fedora
Classification: Fedora
Component: mysql (Show other bugs)
rawhide
Unspecified Unspecified
unspecified Severity unspecified
: ---
: ---
Assigned To: Tom Lane
Fedora Extras Quality Assurance
:
Depends On: 725480
Blocks: SysVtoSystemd
  Show dependency treegraph
 
Reported: 2011-06-19 05:20 EDT by Harald Reindl
Modified: 2015-06-21 05:52 EDT (History)
8 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2012-02-13 19:54:08 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:


Attachments (Terms of Use)
mysqld.service (470 bytes, text/plain)
2011-06-19 12:35 EDT, Elad Alfassa
no flags Details
mysqld.socket (117 bytes, text/plain)
2011-06-19 12:36 EDT, Elad Alfassa
no flags Details
mysqld.service (323 bytes, text/plain)
2011-06-19 12:38 EDT, Elad Alfassa
no flags Details
mysqld.service (323 bytes, text/plain)
2011-06-19 13:23 EDT, Elad Alfassa
no flags Details
mysqld.service (341 bytes, text/plain)
2011-06-19 13:26 EDT, Elad Alfassa
no flags Details
mysqld.service (318 bytes, text/plain)
2011-06-19 14:17 EDT, Elad Alfassa
no flags Details
yet another mysqld.service and this one based on the old sysv script ;) (554 bytes, text/plain)
2011-06-20 15:49 EDT, Jóhann B. Guðmundsson
no flags Details
patch for mysqld which allows a socket activation (29.93 KB, patch)
2011-07-20 10:52 EDT, Honza Horak
no flags Details | Diff
mysqld unit file which honours the way how mysqld is configured (441 bytes, text/plain)
2011-07-20 10:54 EDT, Honza Horak
no flags Details
script taken from SysV init script which prepare db direcotry (808 bytes, text/plain)
2011-07-20 10:55 EDT, Honza Horak
no flags Details
script taken from SysV init script which prepare mysqld arguments (1.68 KB, text/plain)
2011-07-20 10:56 EDT, Honza Horak
no flags Details
proposed mysqld.spec patch (3.66 KB, patch)
2011-07-20 10:57 EDT, Honza Horak
no flags Details | Diff
mysqld unit file which honours the way how mysqld is configured (387 bytes, text/plain)
2011-07-22 13:38 EDT, Honza Horak
no flags Details
script to prepare arguments (969 bytes, text/plain)
2011-07-22 13:40 EDT, Honza Horak
no flags Details
proposed mysqld.spec patch (3.74 KB, patch)
2011-07-22 13:42 EDT, Honza Horak
no flags Details | Diff
patch for mysqld_safe to not fork if variable is set (4.39 KB, patch)
2011-07-22 13:44 EDT, Honza Horak
no flags Details | Diff
patch for mysqld_safe to fork, but no wait for the child (4.43 KB, patch)
2011-07-25 10:38 EDT, Honza Horak
no flags Details | Diff
mysqld unit file which honours the way how mysqld is configured (467 bytes, text/plain)
2011-07-25 10:39 EDT, Honza Horak
no flags Details
proposed mysqld.spec patch (3.87 KB, patch)
2011-07-25 10:41 EDT, Honza Horak
no flags Details | Diff
script to wait for server initialization (223 bytes, text/plain)
2011-07-25 10:42 EDT, Honza Horak
no flags Details
script to prepare arguments (1.68 KB, text/plain)
2011-07-25 10:43 EDT, Honza Horak
no flags Details
script taken from SysV init script which prepare db direcotry (808 bytes, text/plain)
2011-07-25 10:45 EDT, Honza Horak
no flags Details
rolled-up patch against git head (13.44 KB, patch)
2011-07-26 20:54 EDT, Tom Lane
no flags Details | Diff

  None (edit)
Description Harald Reindl 2011-06-19 05:20:08 EDT
please include a systemd-service file for Fedora >= 15

it is a bad behavior that F15 replacing upstart with systemd and most 
services are running in SysV compatibilty mode
Comment 1 Elad Alfassa 2011-06-19 12:35:58 EDT
Created attachment 505482 [details]
mysqld.service

https://fedoraproject.org/wiki/Features/SysVtoSystemd
Packagers should not add systemd service files in Fedora 15 if they haven't done it yet. 
Migrating services from sysv to system is recommended for rawhide though (as it is a Fedora 16 feature).

So, attached is my first attempt in porting a service to systemd, slightly based on the work of Harald Reindl (the reporter of this bug) in bug #714486.
Comment 2 Elad Alfassa 2011-06-19 12:36:49 EDT
Created attachment 505483 [details]
mysqld.socket
Comment 3 Elad Alfassa 2011-06-19 12:38:31 EDT
Created attachment 505484 [details]
mysqld.service

small fix: I don't think this giant "before" list is really needed, so I removed it.
Comment 4 Elad Alfassa 2011-06-19 12:39:43 EDT

-- 
Fedora Bugzappers volunteer triage team
https://fedoraproject.org/wiki/BugZappers
Comment 5 Harald Reindl 2011-06-19 12:46:55 EDT
thank you for the socket-file!

but how do we deal with TCP-Ports
port and listen-adress may be and is often defined in /etc/my.cnf

how does systemd act if we define here "ListenStream=127.0.0.1:3306" to fullfill the needs of the local machine and after that mysqld decides to listen on the public interface? the other direction is also possible and the admin will restrict mysqld only on the loopback-device

means that for now this maybe OK but mysqld should not have a problem to listen on other network devices at the end:
ListenStream=/var/lib/mysql/mysql.sock
ListenStream=127.0.0.1:3306

> Packagers should not add systemd service files in Fedora 
> 15 if they haven't done it yet. 

does not affect me because i am enduser and can not roll out F15 by EOL of F14 in our comapany because lazy packagers have done nothing for F15 and in a production environment you will always use Fedora-now-1
Comment 6 Elad Alfassa 2011-06-19 12:51:54 EDT
btw, default configuration in my.cnf should change the socket from /var/lib/mysql/mysql.sock to /run/mysqld/socket or something like that, putting sockets in /var/lib doesn't sound the right thing to do, esp. when we have /run for these stuff.
My socket files assumes the socket is in /var/lib/mysql/mysql.sock just because it's the default configuration, and this seems ugly and wrong.



-- 
Fedora Bugzappers volunteer triage team
https://fedoraproject.org/wiki/BugZappers
Comment 7 Harald Reindl 2011-06-19 12:55:36 EDT
sorry, but this does not work for me se the before reboot clean maillog

[root@testserver:~]$ cat /lib/systemd/system/mysqld.socket 
[Unit]
Description=MySQL Database activation socket

[Socket]
ListenStream=/var/lib/mysql/mysql.sock
ListenStream=127.0.0.1:3306

[Install]
WantedBy=sockets.target
[root@testserver:~]$ cat maillog 
Jun 19 18:52:55 testserver dbmail/timsieved[1226]: Error:[sql] dbmysql.c,db_connect(+172): mysql_real_connect failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Jun 19 18:52:55 testserver dbmail/timsieved[1226]: FATAL:[server] server.c,StartServer(+129): Unable to connect to database.
Jun 19 18:52:55 testserver dbmail/lmtpd[1223]: Error:[sql] dbmysql.c,db_connect(+172): mysql_real_connect failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Jun 19 18:52:55 testserver dbmail/lmtpd[1223]: FATAL:[server] server.c,StartServer(+129): Unable to connect to database.
Jun 19 18:52:55 testserver dovecot: master: Warning: Fixing permissions of /var/run/dovecot to be world-readable
Jun 19 18:52:55 testserver dovecot: master: Dovecot v2.0.13 starting up (core dumps disabled)
Jun 19 18:52:55 testserver dbmail/imap4d[1242]: Error:[sql] dbmysql.c,db_connect(+172): mysql_real_connect failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Jun 19 18:52:55 testserver dbmail/imap4d[1242]: FATAL:[server] server.c,StartServer(+129): Unable to connect to database.
Jun 19 18:52:57 testserver postfix/postfix-script[1403]: starting the Postfix mail system
Jun 19 18:52:57 testserver postfix/master[1412]: daemon started -- version 2.8.3, configuration /etc/postfix
Jun 19 18:53:03 testserver dovecot: auth: mysql(/var/lib/mysql/mysql.sock): Connected to database dbmail
Jun 19 18:53:03 testserver dovecot: auth: mysql(/var/lib/mysql/mysql.sock): Connected to database dbmail
Jun 19 18:53:03 testserver dovecot: imap-login: proxy(test@testserver.rhsoft.net): started proxying to 127.0.0.1:20143: user=<test@testserver.rhsoft.net>, method=CRAM-MD5, rip=84.113.45.179, lip=84.113.45.81, TLS
Jun 19 18:53:04 testserver dovecot: imap-login: proxy(test@testserver.rhsoft.net): started proxying to 127.0.0.1:20143: user=<test@testserver.rhsoft.net>, method=CRAM-MD5, rip=84.113.45.179, lip=84.113.45.81, TLS
Comment 8 Harald Reindl 2011-06-19 12:58:56 EDT
> btw, default configuration in my.cnf should change the 
> socket from /var/lib/mysql/mysql.sock

BTW:
this is only in theory so easy 

if you have running mysql with httpd, dovecot, dbmail, policy-services, a lot of postfix-mysql-configs an so on change this is an adventure game more in context of systemd - my real problem is taht the socket does not work :-(
Comment 9 Tom Lane 2011-06-19 13:03:31 EDT
We are *not* moving the socket.
Comment 10 Elad Alfassa 2011-06-19 13:06:51 EDT
(In reply to comment #8)
> this is only in theory so easy 
> 
> if you have running mysql with httpd, dovecot, dbmail, policy-services, a lot
> of postfix-mysql-configs an so on change this is an adventure game more in
> context of systemd - my real problem is taht the socket does not work :-(
Ah, crap.

(In reply to comment #7)
> sorry, but this does not work for me se the before reboot clean maillog
Maybe adding After=sockets.target to dmail's service file, and also make sure that mysqld.socket is "enabled", which means has a symlink to in /etc/systemd/system/sockets.target.wants
If not, please systemctl enable mysqld.socket



-- 
Fedora Bugzappers volunteer triage team
https://fedoraproject.org/wiki/BugZappers
Comment 11 Harald Reindl 2011-06-19 13:17:16 EDT
> If not, please systemctl enable mysqld.socket

thank you - this does the trick but why in the world is this not enabled implicitly with "systemctl enable mysqld.service" or in the .service file for mysqld - i fear it will not happen from maintainers of the official packages dealing with mysql to do this in their service-files

i feel that the next two weeks holidays will be degraded of the thougs of systemd in production environment :-(
Comment 12 Elad Alfassa 2011-06-19 13:23:01 EDT
Created attachment 505490 [details]
mysqld.service

Fixed, I added Also=mysqld.socket to the [install] section.
Now "systemctl enable mysqld.service" will enable the socket, not just the service.
Comment 13 Elad Alfassa 2011-06-19 13:26:28 EDT
Created attachment 505491 [details]
mysqld.service

Really add it this time, I forgot to hit "save" before :)
Comment 14 Elad Alfassa 2011-06-19 13:32:24 EDT
(In reply to comment #5)
> but how do we deal with TCP-Ports
This one is rather simple actually: If you systemctl enable mysqld.service it would be started at boot, even if it didn't get any message in the socket.
It will listen to whatever connection it is set to listen to.
The socket activation here is only useful for during boot stuff, because I don't think you want to start mysqld on-demand when someone tries to connect to it using TCP.



-- 
Fedora Bugzappers volunteer triage team
https://fedoraproject.org/wiki/BugZappers
Comment 15 Harald Reindl 2011-06-19 14:08:24 EDT
agreed!

BTW: 
you should remove the "Requires=mysqld.socket"

this resulted in a loop for another service here i try to repackage 
tyring enable/disable/stop/start in random order

thank you for your help! you are the only one not saying "targeted for F15" what means implicitly "if F14 is EOL look how you live with your servers"
Comment 16 Elad Alfassa 2011-06-19 14:17:32 EDT
Created attachment 505493 [details]
mysqld.service

Done.
Harald, please test these service and socket files and see if they work correctly for you (you can do much more testing then I did). If it does work well for you, I'll CC some systemd experts on this bug, so they would check the files to see if I didn't do any stupid mistake.
After we will have this tested properly (at least a bit), I think it's ready to be included in rawhide's mysql package.
Comment 17 Harald Reindl 2011-06-19 18:02:46 EDT
socket-activation seems to work fine

but the stop/start/restart-code of systemd is horrible broken
even with "Restart=on-failure" it will be most of the time started shortly after "systemctl stop mysqld.service" and i would not expect this with "Restart=always" since there is a difference who is terminating a service - systemctl/systemd should realize that this was manual stop via the systemctl

anyways, below my service/socket which are working fine now

but what is with this to use "largepages" of mysqld?
see the two innodb-errors if enabled

# WE DO NOT KNOW HOW TO INCLUDE SOMETHING LIKE THIS TO MAKE "largepages" WORKING
# [root@srv-rhsoft:~]$ cat /etc/my.memory.cnf
# echo 200 > /proc/sys/vm/nr_hugepages
# echo 27 > /proc/sys/vm/hugetlb_shm_group
# echo 2097152 > /proc/sys/kernel/shmall
# ulimit -l unlimited
# ulimit -n 30000
#
# InnoDB: HugeTLB: Warning: Failed to allocate 109051904 bytes. errno 1
# InnoDB HugeTLB: Warning: Using conventional memory pool



[root@testserver:~]$ cat /lib/systemd/system/mysqld.service 
[Unit]
Description=MySQL Database
After=sockets.target syslog.target 
Before=httpd.service postfix.service

[Service]
Type=simple
PIDFile=/var/run/mysqld/mysqld.pid
ExecStart=/usr/libexec/mysqld --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --user=mysql
ExecStop=/bin/kill -15 $MAINPID
Restart=always
RestartSec=2
TimeoutSec=60

[Install]
WantedBy=multi-user.target
Also=mysqld.socket 


[root@testserver:~]$ cat /lib/systemd/system/mysqld.socket
[Unit]
Description=MySQL Database activation socket

[Socket]
ListenStream=/var/lib/mysql/mysql.sock

[Install]
WantedBy=sockets.target
Comment 18 Elad Alfassa 2011-06-20 01:16:39 EDT
Why do you want to start it before httpd?



-- 
Fedora Bugzappers volunteer triage team
https://fedoraproject.org/wiki/BugZappers
Comment 19 Harald Reindl 2011-06-20 03:22:50 EDT
because on a high-traffic-webserver you can kill the machine while it boots since socket activation buffers the requests but finally has to wait until mysqld anserws - have fun if there are some thousand user requests waiting on database-responses and this in the worst case directly after a crash because too much load
Comment 20 Jóhann B. Guðmundsson 2011-06-20 15:49:30 EDT
Created attachment 505683 [details]
yet another mysqld.service and this one based on the old sysv script ;)

Elad ping me on irc to review his service file so I wrote my own based on the sysv script and compared to what is here and it turned out a bit different from what you guys have been playing with. 

So Elad my first and comment to you is look at the maintainers script and base your systemd service file on it not from some random guy of the internet. 

Tom you should take a look at http://fedoraproject.org/wiki/Packaging:Tmpfiles.d for the directory creation of the old sysv init script. 

Now if you are not using anything from the /etc/sysconfig/network you can just drop it from the file along with ExecStartPre=!/usr/bin/mysqladmin --socket=var/lib/mysql/mysql.sock --user=UNKNOWN_MYSQL_USER ping 2>&1 check which I'm not so sure it serves any purpose anymore but I threw it in there encase you needed it.. 

Now I defaulted the TimeoutSec=60 since it's the same for start and stop .

Now I'm not sure what you want to do with this 
/usr/bin/mysql_install_db --datadir=/var/lib/mysql --user=mysql

Arguable this should happen at install time or simply admins/power users should possess the knowledge to run this from cli..
Comment 21 Elad Alfassa 2011-07-06 13:15:07 EDT
Blocking 713562



-- 
Fedora Bugzappers volunteer triage team
https://fedoraproject.org/wiki/BugZappers
Comment 22 Jóhann B. Guðmundsson 2011-07-06 13:40:52 EDT
Tom Ping status
Comment 23 Honza Horak 2011-07-20 10:51:34 EDT
Hi, 

thank you all for the proposed unit files. They can work in many use cases, but don't follow the process how mysql is configured in SysVinit. Generally, there is a /usr/bin/my_print_defaults which reads several config files and generates arguments for mysqld_safe. I've adjusted your spec files with ability to read a configuration this way (will be attached).

The configuration loading is a bit problem, because systemd can parse only a file which contains "new-line separated variable assignments", not a shell script.

I've solved this by creating a temporary file /var/run/mysqld/config (using /usr/bin/my_print_defaults, so users' configuration can stay unchanged). The file is created shortly before the main process is executed, so systemd parses only this temporary file.

It works good (for mysqld.service), but it's not possible to set a socket file path this way, because only static path can be used in myslqd.socket, afaik. I'm trying to discuss this with systemd developers (http://lists.freedesktop.org/archives/systemd-devel/2011-July/002989.html), no respond so far.

Besides that I have another problem with a socket activation, which doesn't work properly in some cases. If I have mysqld.socket active, mysqld.service nonactive and run mysql client, which connects to the socket, systemd activates mysqld.service. It works good so far. 

The problem is that the client which is responsible to activating the mysqld.service will stay blocked forever on "read" the socket (first package), even if server starts properly and another clients work fine then. There is probably a race-condition somewhere during activating mysqld.service, but I've no idea if it is an issue in server, daemon or systemd. 

I've noticed, that systemd doesn't recognize the main PID file even if I set it using PIDFile= in mysqld.service, which can be related to the problem described above. systemd still thinks mysqld_save process is the main one, which isn't true and may cause problems.
Comment 24 Honza Horak 2011-07-20 10:52:59 EDT
Created attachment 514023 [details]
patch for mysqld which allows a socket activation
Comment 25 Honza Horak 2011-07-20 10:54:27 EDT
Created attachment 514025 [details]
mysqld unit file which honours the way how mysqld is configured
Comment 26 Honza Horak 2011-07-20 10:55:26 EDT
Created attachment 514026 [details]
script taken from SysV init script which prepare db direcotry
Comment 27 Honza Horak 2011-07-20 10:56:08 EDT
Created attachment 514027 [details]
script taken from SysV init script which prepare mysqld arguments
Comment 28 Honza Horak 2011-07-20 10:57:05 EDT
Created attachment 514028 [details]
proposed mysqld.spec patch
Comment 29 Harald Reindl 2011-07-20 19:31:53 EDT
please can anybody explain QA that this update is hardly needed for F15 because without the socket EVERY service which relies on mysqld ready for connections is borken?

YES a personal rebuild for F15 works fine now but a GA distribution should not require rawhide-rebuilds to work as expected
Comment 30 Honza Horak 2011-07-21 11:32:28 EDT
(In reply to comment #23)
> I've noticed, that systemd doesn't recognize the main PID file even if I set it
> using PIDFile= in mysqld.service, which can be related to the problem described
> above. systemd still thinks mysqld_save process is the main one, which isn't
> true and may cause problems.

I've reported this as a bug #723942.
Comment 31 Harald Reindl 2011-07-21 11:43:20 EDT
the main question is why is "mysqld_safe" needed any longer since "systemd" can restart the service at its own if it dies?

[Service]
Type=simple
PIDFile=/var/run/mysqld/mysqld.pid
ExecStart=/usr/libexec/mysqld --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --user=mysql
ExecStop=/bin/kill -15 $MAINPID
Restart=always
RestartSec=2
TimeoutSec=60
Comment 32 Jóhann B. Guðmundsson 2011-07-21 12:04:48 EDT
PIDFile= with type simple does not work afaik ( has to be Type=forking )  and you dont want to default the shipped unit files to use Restart=always but rather Restart=on-failure
Comment 33 Tom Lane 2011-07-21 16:24:29 EDT
It seems to me that there's a whole lot of complication being added here to support socket activation, which is a feature that I think is entirely counterproductive for a database server.  Let's rip all that out.
Comment 34 Harald Reindl 2011-07-21 16:32:18 EDT
> which is a feature that I think is entirely
> counterproductive for a database server

this is wrong and maybe only seem for you so because you have never used complex mysqld-driven infrastructures where nameservers, dhcp, mailservices, administration and a lot of automated operations rely on a ready-for-connections mysqld!

i have here dbmail, postfix, dovecot-auth/proxy and some mail-policyd and they all starting simply dirty with systemd as long "mysqld" has no socket-activation

Before/After does not help here, you get database-errors in the maillog, some serivces my not start correctly and the whole system is not useable

fact is: if anybody would have tested the mysqld/dbmail-pakcages shipped with F15 mysqld would NEVER has been released without socket-activation and this is a hardly needed feature for F15 regardless what QA says about provide native systemd-services for daemons shipped without until now with F15
Comment 35 Harald Reindl 2011-07-21 16:35:45 EDT
additional info: 

i have included https://bugzilla.redhat.com/attachment.cgi?id=514023 in my own mysqld-build on Fedora 15 and after that this was the first reboot of my testing-environment where i hated systemd not completly

without https://github.com/jnorell/dbmail-postfix-policyd will not start clean and your mailservice is simply unuseable

so "Let's rip all that out" is a useless comment since Fedora ships systemd
Comment 36 Tom Lane 2011-07-21 16:36:13 EDT
(In reply to comment #31)
> the main question is why is "mysqld_safe" needed any longer since "systemd" can
> restart the service at its own if it dies?

After a quick look at the mysqld_safe script, I'd say the main issue there is that mysqld_safe knows about shutting down subprocesses too.  There are also some logging frammishes that might be hard to duplicate in the systemd world.  And keep in mind that it's been a lot of years since mysqld got any significant usage when not launched via mysqld_safe ... who knows what subtle environmental assumptions might have got baked into it by now?  I think getting rid of mysqld_safe would be high risk, low reward.
Comment 37 Tom Lane 2011-07-21 16:39:48 EDT
(In reply to comment #34)
> > which is a feature that I think is entirely
> > counterproductive for a database server
> 
> this is wrong and maybe only seem for you so because you have never used
> complex mysqld-driven infrastructures where nameservers, dhcp, mailservices,
> administration and a lot of automated operations rely on a
> ready-for-connections mysqld!

Oh?  And what were you running that on?  Not Fedora, because it never had socket-activated mysqld before.  Nor is it apparent to me that socket activation would make such a configuration safe anyway.  systemd already has features to ensure proper ordering of service startup, we can just use those.
Comment 38 Harald Reindl 2011-07-21 16:46:24 EDT
damend i do not want to start the discussion from scratch

you can declare "After=mysqld.service" in all services relying on mysqld and it will NOT help you because "mysqld" gives a 0 result back while some initializing is running and mysqld IS NOT ready for connections and since upgrade to F15 this results in hughe troubles

beliebe me: i maintain since 10 years a lot of mysql-driven things and most of them are borked with F15/systemd and i have spent FIVE DAYS without luck until yesterday the socket-activation patch for F16 came to this bugreport and since i included it for F15 all services are running fine
Comment 39 Harald Reindl 2011-07-21 16:50:03 EDT
addititionl comment:

until F15 you had only to make sure taht mysqld is starting early and all depending services nearly at the end of the boot-process, this worked for 9.999 of 10.000 without a single warning

since systemd tries to fire up services as fast as possible and if it looks possible at the same time you get raise-conditions and since it was a big fault release F15 in such a borked state it is only contraproductive taht you coming to this bug report saying "let us remove socket-activation because i do not understand the real-world troubles"
Comment 40 Harald Reindl 2011-07-21 17:00:13 EDT
> After a quick look at the mysqld_safe script, I'd say the main issue there 
> is that mysqld_safe knows about shutting down subprocesses too

you should look a little longer at the right place

* mysqld has no "subprcesses", mysqld uses threads
* "mysqld_safe" is NOT used for stop mysqld
* "mysqld_safe" primary restarts mysqld if it exists with non-zero-code
* this is exactly the same systemd does
* at the end the stop-code of /etc/init.d/mysqld on F14

[root@thx1138:~]$ > mysql_error.log 
[root@thx1138:~]$ ps aux | grep mysql
root     11646  0.1  0.1 110524  1672 pts/0    S    22:58   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/Volumes/dune/mysql_data --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql    12553  0.0  0.8 216656  8716 pts/0    Sl   22:58   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/Volumes/dune/mysql_data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/Volumes/dune/www-servers/_logs/mysql_error.log --open-files-limit=1000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root     12583  0.0  0.0 105440   884 pts/0    S+   22:58   0:00 grep --color mysql
[root@thx1138:~]$ killall mysqld
[root@thx1138:~]$ ps aux | grep mysql
root     12591  0.0  0.0 105440   884 pts/0    S+   22:58   0:00 grep --color mysql
[root@thx1138:~]$ cat mysql_error.log 
110721 22:58:49 [Note] /usr/libexec/mysqld: Normal shutdown

110721 22:58:49 [Note] Event Scheduler: Purging the queue. 0 events
110721 22:58:49 [Note] /usr/libexec/mysqld: Shutdown complete

110721 22:58:49 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

______________


stop(){
        if [ ! -f "$mypidfile" ]; then
            # not running; per LSB standards this is "ok"
            action $"Stopping $prog: " /bin/true
            return 0
        fi
        MYSQLPID=`cat "$mypidfile"`
        if [ -n "$MYSQLPID" ]; then
            /bin/kill "$MYSQLPID" >/dev/null 2>&1
            ret=$?
            if [ $ret -eq 0 ]; then
                TIMEOUT="$STOPTIMEOUT"
                while [ $TIMEOUT -gt 0 ]; do
                    /bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
                    sleep 1
                    let TIMEOUT=${TIMEOUT}-1
                done
                if [ $TIMEOUT -eq 0 ]; then
                    echo "Timeout error occurred trying to stop MySQL Daemon."
                    ret=1
                    action $"Stopping $prog: " /bin/false
                else
                    rm -f $lockfile
                    rm -f "$socketfile"
                    action $"Stopping $prog: " /bin/true
                fi
            else
                action $"Stopping $prog: " /bin/false
            fi
        else
            # failed to read pidfile, probably insufficient permissions
            action $"Stopping $prog: " /bin/false
            ret=4
        fi
        return $ret
}
Comment 41 Jóhann B. Guðmundsson 2011-07-21 17:05:51 EDT
(In reply to comment #36)
> (In reply to comment #31)
> > the main question is why is "mysqld_safe" needed any longer since "systemd" can
> > restart the service at its own if it dies?
> 
> After a quick look at the mysqld_safe script, I'd say the main issue there is
> that mysqld_safe knows about shutting down subprocesses too.  There are also
> some logging frammishes that might be hard to duplicate in the systemd world. 
> And keep in mind that it's been a lot of years since mysqld got any significant
> usage when not launched via mysqld_safe ... who knows what subtle environmental
> assumptions might have got baked into it by now?  I think getting rid of
> mysqld_safe would be high risk, low reward.

I was thinking about this as well as if we actully would need mysql-safe in
systemd world

Briefly running over that script reveals that we have a bunch of
options/settings build into systemd that mysql-safe seems to be using.. 

Like the condition check

Various Condition$foo=

--pid-file=

PIDFile=

--user=

User=

--socket=

Also=mysqld.socket ( activate the mysql.socket )

--nice=

Nice=

--open-files-limit=

LimitNoFile=

--syslog

StandardOutput=syslog
StandardError=syslog

etc..
Comment 42 Jóhann B. Guðmundsson 2011-07-21 17:16:29 EDT
(In reply to comment #35)
> additional info: 
> 
> i have included https://bugzilla.redhat.com/attachment.cgi?id=514023 in my own
> mysqld-build on Fedora 15 and after that this was the first reboot of my
> testing-environment where i hated systemd not completly
> 
> without https://github.com/jnorell/dbmail-postfix-policyd will not start clean
> and your mailservice is simply unuseable
> 
> so "Let's rip all that out" is a useless comment since Fedora ships systemd

If you are using dbmail for your server you might want to take a look at bug 722341 since I did an inital convertion of all that dbmail stuff the other day and I'm pretty sure the maintainer would like to get some feed back from some one that can test those files under working environment. 

And looking at 

https://github.com/jnorell/dbmail-postfix-policyd/blob/master/debian/dbmail-postfix-policyd.init

It seems to be relatively easy convert to native systemd service files.
Comment 43 Tom Lane 2011-07-21 17:24:37 EDT
(In reply to comment #38) 
> you can declare "After=mysqld.service" in all services relying on mysqld and it
> will NOT help you because "mysqld" gives a 0 result back while some
> initializing is running and mysqld IS NOT ready for connections and since
> upgrade to F15 this results in hughe troubles

Well, the problem with F15 is that systemd claims to support sysv-script-based services but does a pretty damn poor job of it.  That does not imply that socket activation is a necessary part of the fix.  The problems you are describing come from the fact that systemd doesn't know when the database daemon is actually up and ready to accept connections, and (AFAICT) it supposes for a sysv-script-based service that the daemon is ready instantly after start.  What we actually need here IMO is to use service type "forking" with a start script that doesn't exit until the server is ready for connections.  We already have logic for waiting for the service to be ready in the existing initscript, we just have to pull it out of there into a new scriptlet that we can launch from the unit file.

BTW, even if I wanted to have socket activation here, my reading of the packaging guidelines
https://fedoraproject.org/wiki/Packaging:Systemd
is that socket activation is only permitted for services that will start in a default configuration, which is most certainly not the case for mysqld.
Comment 44 Harald Reindl 2011-07-21 17:28:32 EDT
> If you are using dbmail for your server you might want to take a look at 
> bug 722341 since I did an inital convertion of all that dbmail stuff the 
> other day and I'm pretty sure the maintainer would like to get some feed back 
> from some one that can test those files under working environment. 

i will take a look at the weekend

i am running dbmail since some weeks on my test-machines
as natve services under F15, the only probkem until yesterday
was the raise-condition of starting mysqld and the other services too sonn what is fixed with the socket-activation yesterday, thas why it makes me really angry in Tom Lane joining this party with useless comments in the wrong direction

Below are mine running since some weeks and as expected the random error messages while boot are gone away with mysqld.socket and that is why i try another request: backport this to F15 official and explain QA that their job is simply forcing this backport instead blocking it for F15

[root@testserver:~]$ cat systemd-services/dbmail-imapd.service 
[Unit]
Description=DBMail IMAP Server
After=syslog.target local-fs.target network.target sockets.target
Before=dovecot.service
[Service]
Type=simple
ExecStart=/usr/sbin/dbmail-imapd -D
Restart=always
RestartSec=2
[Install] 
WantedBy=multi-user.target

[root@testserver:~]$ cat systemd-services/dbmail-pop3d.service 
[Unit]
Description=DBMail POP3 Server
After=syslog.target local-fs.target network.target sockets.target
Before=dovecot.service
[Service]
Type=simple
ExecStart=/usr/sbin/dbmail-pop3d -D
Restart=always
RestartSec=2
[Install] 
WantedBy=multi-user.target

[root@testserver:~]$ cat systemd-services/dbmail-lmtpd.service 
[Unit]
Description=DBMail LMTP Server
After=syslog.target local-fs.target network.target sockets.target
Before=postfix.service
[Service]
Type=simple
ExecStart=/usr/sbin/dbmail-lmtpd -D
Restart=always
RestartSec=2
[Install] 
WantedBy=multi-user.target


> And looking at 
>
> https://github.com/jnorell/dbmail-postfix-policyd/blob/master/debian/dbmail-> postfix-policyd.init
>
> It seems to be relatively easy convert to native systemd service files

i have running this since many weeks with systemd, but the problem until the mysqld-socket-activation was that the policyd dislikes if mysql is not full operational at start and will not accept connections nor die, not a godd sign i know but that is how it is now, was no probkem until systemd was introduced and is only fixed this time with mysqld.scoket what shows that mysqld.socket is a imporant peice for reliable mysqld services

[Unit]
Description=DBMail Policy Service
After=syslog.target sockets.target mysqld.service
Before=postfix.service

[Service]
Type=simple
PIDFile=/var/spool/postfix/dbmail-postfix-policyd/pid
ExecStartPre=/bin/rm -f /var/spool/postfix/dbmail-postfix-policyd/pid
ExecStartPre=/bin/rm -f /var/spool/postfix/dbmail-postfix-policyd/socket
ExecStart=/usr/sbin/dbmail-postfix-policyd --user=postfix --group=postfix --dbmail-conf=/etc/dbmail-postfix-policyd.conf --dbmail-version=2 --unix=/var/spool/postfix/dbmail-postfix-policyd/socket --pidfile=/var/spool/postfix/dbmail-postfix-policyd/pid
Restart=always
RestartSec=2

[Install] 
WantedBy=multi-user.target
Comment 45 Harald Reindl 2011-07-21 17:39:45 EDT
(In reply to comment #43)
> Well, the problem with F15 is that systemd claims to support 
> sysv-script-based services but does a pretty damn poor job of it.  

yes and that is why F15 is broken

> That does not imply that socket activation is a necessary part of the fix.  
> The problems you are describing come from the fact that systemd doesn't
> know when the database daemon is actually up and ready to accept 
> connections, and (AFAICT) it supposes for a sysv-script-based service 
> that the daemon is ready instantly after start

but socket activation exists and solve all this troubles i a clean way

> What we actually need here IMO is to use service type "forking" with a 
> start script that doesn't exit until the server is ready for connections.  
> We already have logic for waiting for the service to be ready in the 
> existing initscript, we just have to pull it out of there into a 
> new scriptlet that we can launch from the unit file

in other words:

* systemd was intended to replace initscripts
* systemd was intended to konw about the main-process
* but you want knock out all benefits of systemd with a new script

> BTW, even if I wanted to have socket activation here, my reading of the
> packaging guidelines
> https://fedoraproject.org/wiki/Packaging:Systemd
> is that socket activation is only permitted for services that will start 
> in a default configuration, which is most certainly not the case for mysqld

in other words: systemd with all it's benefits it could have is simply useless because the guidelines forbid to use it as designed and would force us to new dirty solutins because someone in theory designed a nice document far away from the real world

sorry i can not resist:

it is hard enough to have this discoussins AFTER F15 was released with systemd and it is the biggest bullshit i have ever heard that fedora should go ahead in the wrong direction because of braindead guidelines, if this is the truth systemd is useless, eating everybodys time for 3 seconds faster boot 

this whole discussion normally should have been fighted long before systemd arrived in GA and that is why rational peopole would normally not release suuch a replacement without having all packages of the distribution adopted for it - anyways, this all had happended and NOW IT IS TIME TO SOLVE THE TROUBLES and if it is needed to adopt some guidelines to allow people solve problems without introduce new dirty workaorunds that NOW it is the time for it

if this happens not NOW we will end with a messed F16, not such hard as F15 but too hard  for me and you take me away the option to backport F16 packages to F15 on our environment to fix the thing someone called "F15 GA"
Comment 46 Jóhann B. Guðmundsson 2011-07-21 17:55:03 EDT
I probably should mention that the ordering requirement are not effective anymore
if mysqld.service has Type=simple and $foo.service has  After= ... mysqld.service

That's because for "Type=simple" service there is no way to let systemd know
when the service is ready to accept external connections, so systemd just fires
off the service and then goes on starting subsequent services. This problem
goes away if the service is patched to use socket activation, because then the
socket can always accept connections or use Type=forking in the relevant service
Comment 47 Jóhann B. Guðmundsson 2011-07-21 18:01:34 EDT
(In reply to comment #43)
> (In reply to comment #38) 
> > you can declare "After=mysqld.service" in all services relying on mysqld and it
> > will NOT help you because "mysqld" gives a 0 result back while some
> > initializing is running and mysqld IS NOT ready for connections and since
> > upgrade to F15 this results in hughe troubles
> 
> Well, the problem with F15 is that systemd claims to support sysv-script-based
> services but does a pretty damn poor job of it.  That does not imply that
> socket activation is a necessary part of the fix.  The problems you are
> describing come from the fact that systemd doesn't know when the database
> daemon is actually up and ready to accept connections, and (AFAICT) it supposes
> for a sysv-script-based service that the daemon is ready instantly after start.
>  What we actually need here IMO is to use service type "forking" with a start
> script that doesn't exit until the server is ready for connections.  We already
> have logic for waiting for the service to be ready in the existing initscript,
> we just have to pull it out of there into a new scriptlet that we can launch
> from the unit file.
> 
> BTW, even if I wanted to have socket activation here, my reading of the
> packaging guidelines
> https://fedoraproject.org/wiki/Packaging:Systemd
> is that socket activation is only permitted for services that will start in a
> default configuration, which is most certainly not the case for mysqld.

As I read it only if the service is enabled by default when installed but I see if I cant contact any of the FPC guys to clarify what they mean.
Comment 48 Tom Lane 2011-07-21 18:13:45 EDT
(In reply to comment #45)
> [ ranting ]

Actually, I quite agree with you that the introduction of systemd has been badly mismanaged: it was made the default at least one release cycle before it was ready.  But this bugzilla entry is not the place to discuss overall Fedora project management issues.

> but socket activation exists and solve all this troubles i a clean way

No, it doesn't exist.  We have a patch that doesn't work tremendously well (cf comment #23), and that upstream probably wouldn't accept anytime soon even if it were perfect.  What I'm looking for is something simple and noninvasive that will make things reliable in F16.  Trying to get rid of every shell script isn't a productive use of time here, especially not in the short run.  If systemd actually does take over the world, maybe our upstreams will be interested in patches to reduce the impedance mismatch, but that's a long way away; it will be some time before people will believe this isn't just the next "upstart".

> * systemd was intended to replace initscripts

Yeah, it will.  That's not the same as not having any shell script anywhere.

> * systemd was intended to konw about the main-process

Which it will.

> * but you want knock out all benefits of systemd with a new script

Not at all.  IMO the main benefit of systemd for a database server is that it gets rid of the hard-wired serial execution of initscripts in the SysV world: services that don't need to depend on the database won't have to wait for it to start.  Socket activation doesn't actually bring much of anything to that party: if you have to wait, you have to wait.
Comment 49 Harald Reindl 2011-07-21 18:40:18 EDT
(In reply to comment #48)

> Actually, I quite agree with you that the introduction of systemd 
> has been badly mismanaged: it was made the default at least one 
> release cycle before it was ready

so we have to deal with it in the most cleanest way and try to avoid dirty workarounds

> But this bugzilla entry is not the place to discuss overall Fedora
> project management issues.

systemd-socket-activation fixes ALL problems with mysqld-driven services and as i shown you above even mysqld_safe is useless with systemd becuase it does only exactly the same as systemd: restart mysqld if it dies with non-zero-code

> > but socket activation exists and solve all this troubles i a clean way
> 
> No, it doesn't exist.  

sure it does

> We have a patch that doesn't work tremendously well (cf
> comment #23), and that upstream probably wouldn't accept anytime soon 
> even if it were perfect.  

#23: well, so systemd-maintainers have to fix this until F16 goes GA
i am able to rebuild mysqld for F15 to get my services running in december/jan but if someone here decides to remove the socket-patch i have no chance 

what upstream PROBABLY include NOW is not relevant, for the users fedora is upstream and if feodra takes away this patch this would be a signal to mysqld-upstream that it is not important and finally it will take longer to get this upstream included

> What I'm looking for is something simple and noninvasive that
> will make things reliable in F16

they are with socket activation

with this mysqld is as relieable as it was the last 10 years before
without all sorts of services which are not directly includded in fedora will be more or less broken

> Trying to get rid of every shell script
> isn't a productive use of time here, especially not in the short run

short run?

systemd was planned for F15, now we have F15 with a mixed system and months before F16 we should targeting NOT wait until F17 to finish this

> systemd actually does take over the world, maybe our upstreams will be
> interested in patches to reduce the impedance mismatch, but that's a 
> long way away

and if fedora as the first place for systemd will revert this you send a signal that even the project is not trusting in new subsystems they are forcing

> it will be some time before people will believe this isn't just 
> the next "upstart"

and as longer the fedora-project is waiting to use systemd really and improve it were needed, fix theoretical guidelines in a way that they are conform with the reality out there the longer it will take

> > * systemd was intended to replace initscripts
> 
> Yeah, it will.  That's not the same as not having any shell script anywhere.

i did not say this
but they are NOT needed for starting mysqld as shipped with fedora

anybody needs a second mysqld?

i do, many of them
make a copy of "mysqld.service" and "mysqld.socket" with the socket path, create a /etc/my-whatever.cnf and use it in the service and you can have 10 mysqld-servcies without a single shellscript

> > * systemd was intended to konw about the main-process
> Which it will.

not if you fire up a dumb shell-script to wait until mysqld is ready for connections, in this case you have the same as with the sysv/klsb-compatibilty-layer and really needing mysqld_safe because you take away the full control from systemd

> Socket activation doesn't actually bring much of anything to that
> party: if you have to wait, you have to wait

but it does not longer matter in what order services driven by myswld are started and you have a lot things out there which CAN use mysqld but will never get "After=mysqld" because it is optional, all of them will be tricky and with "mysqld.socket" all of them are happy instead spamming error-logs or in the worst case dying or getting started in a non-working state
Comment 50 Tom Lane 2011-07-21 18:42:41 EDT
(In reply to comment #24)
> Created attachment 514023 [details]
> patch for mysqld which allows a socket activation

Hm, I don't understand how this works?  You've got

EnvironmentFile=-/var/run/mysqld/config
ExecStartPre=/usr/libexec/mysqld-get-config
ExecStartPre=/usr/libexec/mysqld-prepare-db-dir
ExecStart=/usr/bin/mysqld_safe $OPTIONS

where mysqld-get-config creates the file /var/run/mysqld/config, and then in the ExecStart line you're assuming that systemd will have read the EnvironmentFile.  The systemd man pages are pretty darn vague about the timing of these actions, but it seems likely to me that this only works during boot cycles where /var/run/mysqld/config already exists with the desired contents --- otherwise systemd will have no value or a stale value for $OPTIONS.

Since I'm thinking that we need a bit of shell script for the ExecStart step anyway, I'd prefer not to rely on an intermediate file here.  ISTM we could just duplicate the config-variable-reading code in both mysqld-prepare-db-dir and the ExecStart script.  Or we could unify those two steps back into one script, although I tend to agree with you that pushing the database directory creation into a separate ExecStartPre step will be a better structure in the long run.
Comment 51 Jóhann B. Guðmundsson 2011-07-21 18:57:51 EDT
(In reply to comment #50)
> (In reply to comment #24)
> > Created attachment 514023 [details]
> > patch for mysqld which allows a socket activation

<snip>
 
> where mysqld-get-config creates the file /var/run/mysqld/config, and then in
> the ExecStart line you're assuming that systemd will have read the
> EnvironmentFile.  The systemd man pages are pretty darn vague about the timing
> of these actions, but it seems likely to me that this only works during boot
> cycles where /var/run/mysqld/config already exists with the desired contents
> --- otherwise systemd will have no value or a stale value for $OPTIONS.

Environment and EnvironmentFile= are read before starting the service then Exec* lines are executed sequentially and after each Exec* line has finished.

As a simple test unit will reveal.. 

[Unit]
Description=Test ordering + time of Exec

[Service]
Type=oneshot
ExecStartPre=/usr/bin/logger 1
ExecStartPre=/bin/sleep 1
ExecStartPre=/usr/bin/logger 2
ExecStartPre=/bin/sleep 2
ExecStart=/usr/bin/logger 3
ExecStart=/bin/sleep 3
ExecStart=/usr/bin/logger 4
ExecStart=/bin/sleep 4
ExecStartPost=/usr/bin/logger 5
ExecStartPost=/bin/sleep 5
ExecStartPost=/usr/bin/logger 6
ExecStartPost=/bin/sleep 6
ExecStartPost=/usr/bin/logger 7
RemainAfterExit=yes
Comment 52 Tom Lane 2011-07-21 19:13:13 EDT
(In reply to comment #51)
> Environment and EnvironmentFile= are read before starting the service then
> Exec* lines are executed sequentially and after each Exec* line has finished.

Did you mean to say that they are *re-read* for each Exec* line?  If so, that would explain why Honza's code works; but unless this is documented somewhere in the systemd documentation, I'm disinclined to rely on it.   It looks like the sort of thing that might get changed as an optimization, if they're not promising that it will continue to work that way.
Comment 53 Harald Reindl 2011-07-21 19:46:07 EDT
http://bugs.mysql.com/bug.php?id=61948

this feature-request in any form should have ben done from the fedora-project beofre F14 where systemd was planned the first time

maybe there starts a discusssion
maybe it needs soem time to get this upstream

well, if such things would be cleared up early enough they CAN be ready until they needed, this is a fact for every subsystem which is replaced - push out replacements and then look what is broken should NEVER happen and not over two distribution-releases
Comment 54 Harald Reindl 2011-07-22 08:44:38 EDT
http://bugs.mysql.com/bug.php?id=61948

-------- Original-Nachricht --------
Betreff: #61948 [Com,Opn->Ver]: mysql / systemd socket activation
Datum: Fri, 22 Jul 2011 13:49:46 +0200
Von: Bug Database <do-not-reply@mysql.com>
An: spam2@rhsoft.net

ATTENTION! Do NOT reply to this email!
To reply, use the web interface found at
http://bugs.mysql.com/?id=61948

 Updated by:           Valeriy Kravchuk
 Reported by:          Harald Reindl
-Category:             Server: Compiling
+Category:             Server: Packaging
 Severity:             S3 (Non-critical)
-Status:               Open
+Status:               Verified
 OS:                   Linux
-Defect Class:         
+Defect Class:         D5 (Feature request)

[22 Jul 13:49] Valeriy Kravchuk
Thank you for the feature request.
Comment 55 Honza Horak 2011-07-22 11:44:24 EDT
(In reply to comment #52)
> (In reply to comment #51)
> > Environment and EnvironmentFile= are read before starting the service then
> > Exec* lines are executed sequentially and after each Exec* line has finished.
> 
> Did you mean to say that they are *re-read* for each Exec* line?  If so, that
> would explain why Honza's code works; but unless this is documented somewhere
> in the systemd documentation, I'm disinclined to rely on it. 

systemd.exec(5) says:
"The files listed with this directive will be read shortly before the process is executed."

...which is not a big promise but if it changes in the future, there should be an alternative, how to do the same thing otherwise.

Then there could be another solution available for our problem on Lennart's blog:
"If you need a programming language to make sense of these settings, then use a programming language like shell. For example, place an short shell script in /usr/lib/<your package>/ which reads these files for compatibility, and then exec's the actual daemon binary. Then spawn this script instead of the actual daemon binary with ExecStart= in the unit file."
Comment 56 Honza Horak 2011-07-22 11:45:19 EDT
(In reply to comment #41)
> I was thinking about this as well as if we actully would need mysql-safe in
> systemd world
> 
> Briefly running over that script reveals that we have a bunch of
> options/settings build into systemd that mysql-safe seems to be using.. 

I've gone through the mysql_safe and I don't see anything that cannot be done in systemd at the same time. I think most of users use default configuration (besides some easy argument adjustments) and if someone changes something, he will be able to do the same stuff in systemd too.

To me, it seems now like the cleanest solution to drop mysqld_safe off. The question is how many problems some possible differences in configuration can cause to users. Maybe some hacks for allowing to use mysqld_safe can do more mess than accepting some theoretic incompatibility in configuration.
Comment 57 Honza Horak 2011-07-22 13:38:57 EDT
Created attachment 514752 [details]
mysqld unit file which honours the way how mysqld is configured

It seems there is a solution that enables to use socket activation and mysqld_safe script together without any problems.

The way how it is done is the same as Lennart wrote in his blog (see previous comment) - we just won't fork a new process in mysqld_safe, but use "exec" instead. 

This solution needs only a couple of changes in mysqld_safe script, because we don't want to use "nohup" and "nice" ability (nice can be set in unit file) as well as watching a child's process (systemd does it). We won't do this if an environment variable MYSQLD_NOWATCH is set to 1. This change doesn't affect anything when the variable is not set, so I think the change can be accepted by upstream.

What is the best on this solution - it works and I don't see any issues so far.

I've changed the configuration loading this way, too, so we don't need any /var/run/mysql/config file any more.
Comment 58 Honza Horak 2011-07-22 13:40:48 EDT
Created attachment 514753 [details]
script to prepare arguments

script taken from SysV init script preparing arguments and executing mysqld_safe with these arguments
Comment 59 Honza Horak 2011-07-22 13:42:31 EDT
Created attachment 514754 [details]
proposed mysqld.spec patch
Comment 60 Honza Horak 2011-07-22 13:44:38 EDT
Created attachment 514756 [details]
patch for mysqld_safe to not fork if variable is set
Comment 61 Tom Lane 2011-07-22 14:50:45 EDT
(In reply to comment #60)
> Created attachment 514756 [details]
> patch for mysqld_safe to not fork if variable is set

Cute idea, but doesn't this patch break the daemon's logging behavior?  You're bypassing eval_log_error.

I'm still a bit nervous about assuming that the process cleanup behavior is not needed, anyway.  That part of mysqld_safe dates back at least to 3.23.58 (the oldest copy I have on hand), so it *might* be that it's just obsolete code that they forgot to remove ... or it might not.  I can't find any fork() calls in the current server source tree, except in the ndb code which we don't (currently) support, but who's to say what plugin storage engines might do?
Comment 62 Tom Lane 2011-07-22 15:06:38 EDT
Despite all this effort getting socket activation to sort-of work, I still feel that that's a direction we must not go in (for now anyway).  Database servers aren't designed to start on client demand, and their clients aren't designed to expect them to, and changing that is a larger activity than we can get into here.  A couple of example problems:

1. "mysqladmin ping" causes the server to start if it wasn't running already.  This is unexpected and undesirable.  To avoid that we'd need to design some other API for checking the server status, and then teach every monitoring tool to use it.

2. If the server has to do crash recovery, it will take many seconds or even minutes to come up.  Clients that get started before that's done are likely to timeout and report failure, which is not desirable.  Even if their connection attempts don't time out, they'll be nonfunctional, which may be worse than not starting them at all.

Even if we find a way around #1, I feel that because of #2 we *must* arrange things so that systemd is explicitly aware of when server startup is complete, so that it doesn't try to start services declared to start "after" the database until the database can actually accept queries.  Whether people feel a need to put After commands into specific services is not the point here; we have to have the capability.

Because of this, type = simple is simply not workable.  We have to use type = forking with an ExecStart script that doesn't return until the server is functional.  That change has enough follow-on consequences that I'm not sure how much of the work done so far will still be usable.
Comment 63 Tom Lane 2011-07-22 15:48:50 EDT
(In reply to comment #62)
> Because of this, type = simple is simply not workable.  We have to use type =
> forking with an ExecStart script that doesn't return until the server is
> functional.  That change has enough follow-on consequences that I'm not sure
> how much of the work done so far will still be usable.

Actually ... wait a minute.  Could we use type = simple and add an ExecStartPost script that waits for the server to become ready?  The systemd documentation, with its trademark lack of precision, doesn't specify whether ExecStartPost has to be finished before the service is considered started, but it seems reasonable that it would be.

The advantage of avoiding type = forking is mainly just that we'd not have to specify PIDFile in the service file, which would be one less opportunity for configuration mismatches.
Comment 64 Harald Reindl 2011-07-22 18:09:59 EDT
(In reply to comment #62)
> Despite all this effort getting socket activation to sort-of work, I still feel
> that that's a direction we must not go in (for now anyway).  Database servers
> aren't designed to start on client demand, and their clients aren't designed to
> expect them to, and changing that is a larger activity than we can get into
> here.  

again: you do not understand the problem!

in a nearly 100% mysqld-driven environment mysqld should be as early as possible ready for connections - the reason is that systemd fires up services braindead for maximal fast boot

so that anything which try to connect to mysqld is FORCING a start of mysqld because of socket-activation is a design-problem of systemd, but what should we do? playing poker if services needing mysqld was per random started late enough?

no! taht can not be the solution!

so for a clean boot mysqld MUST use socket activation

the whole systemd-design is simply crap in the context of socket-activation but we need it hardly to get services trsutable started 

if systemd would be desigend with a little brain there would be not needed a "mysqld.socket", this would be simply an optin in "mysqld.service" in a optional way "dear systemd, while starting the service offer the socket for incoming connections but let the service fuck in peace if it would be manually stopped or is not running" - this option does not exist - so we have to make sure as a minimum taht after systemd thinks mysqld is started and begins to fire up the next services mysqld is READY FOR CONNECTIONS - how does not interest me becasue i am not one of this brainedead peopole which are tjinking 3 senconds faster boot will make the world better!
Comment 65 Tom Lane 2011-07-22 18:47:23 EDT
(In reply to comment #63)
> Actually ... wait a minute.  Could we use type = simple and add an
> ExecStartPost script that waits for the server to become ready?

That aspect seems to work as expected, but I'm finding that the idea of having mysqld_safe exec mysqld does not work at all if you have selinux in enforcing mode --- apparently, the selinux rules expect a regular fork/exec to be done, and won't transition from mysqld_safe_t to mysqld_t until that happens.  The first way this shows up is that the socket doesn't get passed correctly :-( but I suspect there's more problems beyond that.  Honza, were you testing with selinux off, or did I break it somehow in messing around with it?
Comment 66 Honza Horak 2011-07-25 10:38:49 EDT
Created attachment 515078 [details]
patch for mysqld_safe to fork, but no wait for the child

(In reply to comment #61)
> (In reply to comment #60)
> > Created attachment 514756 [details]
> > patch for mysqld_safe to not fork if variable is set
> 
> Cute idea, but doesn't this patch break the daemon's logging behavior?  You're
> bypassing eval_log_error.

I think it is needed no more, since systemd provides number of logging options (http://0pointer.de/public/systemd-man/systemd.exec.html), arguments StandardOutput= and StandardError=. But on the other hand it shouldn't be problem to use it, I've kept the original style of logging in the following try.

> I'm still a bit nervous about assuming that the process cleanup behavior is not
> needed, anyway.  That part of mysqld_safe dates back at least to 3.23.58 (the
> oldest copy I have on hand), so it *might* be that it's just obsolete code that
> they forgot to remove ... or it might not.  I can't find any fork() calls in
> the current server source tree, except in the ndb code which we don't
> (currently) support, but who's to say what plugin storage engines might do?

It seems this shouldn't be any problem in systemd:
http://lists.freedesktop.org/archives/systemd-devel/2011-July/003021.html

(In reply to comment #62)
> 1. "mysqladmin ping" causes the server to start if it wasn't running already. 
> This is unexpected and undesirable.  To avoid that we'd need to design some
> other API for checking the server status, and then teach every monitoring tool
> to use it.

That's true, I haven't realized this issue before. It seems to be a problem in dbus activation, too so I'm wondering how systemd can handle this. Maybe `systemctl is-active mysqld.socket` can solve this, however the socket activation has been eliminated for now in the following workaround.

> Because of this, type = simple is simply not workable.  We have to use type =
> forking with an ExecStart script that doesn't return until the server is
> functional.  That change has enough follow-on consequences that I'm not sure
> how much of the work done so far will still be usable.

(In reply to comment #65)
> I suspect there's more problems beyond that.  Honza, were you testing with
> selinux off, or did I break it somehow in messing around with it?

I don't remember, why I've turned it off, but you're right, it doesn't work when systemd is enforcing. 

So it seems there are some issues not easy to solve with the current design with socket activation. I've prepared a "forking" service without mysqld.socket file at all. mysqld_safe now doesn't wait for mysqld, but finishes immediatelly. It doesn't suffer from selinux issues and works in my simple testing environment very well.  

It is based on Tom's idea in comment #63 (mysqld_safe forks without waiting but we wait for the server initialization in ExecStartPost section). If other services, which have Requires=mysqld.service and After=mysqld.service, it should work.

Can you please test it in your a bit more complicated environment as well, Harald?

scratch build for easy deploying: http://koji.fedoraproject.org/koji/taskinfo?taskID=3227721
Comment 67 Honza Horak 2011-07-25 10:39:55 EDT
Created attachment 515079 [details]
mysqld unit file which honours the way how mysqld is configured
Comment 68 Honza Horak 2011-07-25 10:41:48 EDT
Created attachment 515080 [details]
proposed mysqld.spec patch
Comment 69 Honza Horak 2011-07-25 10:42:50 EDT
Created attachment 515081 [details]
script to wait for server initialization
Comment 70 Honza Horak 2011-07-25 10:43:30 EDT
Created attachment 515082 [details]
script to prepare arguments
Comment 71 Honza Horak 2011-07-25 10:45:05 EDT
Created attachment 515084 [details]
script taken from SysV init script which prepare db direcotry
Comment 72 Tom Lane 2011-07-25 11:12:17 EDT
(In reply to comment #66)
> > I'm still a bit nervous about assuming that the process cleanup behavior is not
> > needed, anyway.  That part of mysqld_safe dates back at least to 3.23.58 (the
> > oldest copy I have on hand), so it *might* be that it's just obsolete code that
> > they forgot to remove ... or it might not.  I can't find any fork() calls in
> > the current server source tree, except in the ndb code which we don't
> > (currently) support, but who's to say what plugin storage engines might do?

> It seems this shouldn't be any problem in systemd:
> http://lists.freedesktop.org/archives/systemd-devel/2011-July/003021.html

Oh, good.  So we don't need that part of mysqld_safe, even if it's not just a leftover.  I think that it would be a good idea to change mysqld_safe to exec mysqld and use type = simple, so that we don't have to rely on the PIDfile property ... but first we will have to get Dan Walsh to tweak the selinux rules to allow that.  I'll file a BZ for that, but unless he responds really fast I think we'll have to use type = forking as a stopgap.
Comment 73 Tom Lane 2011-07-25 11:30:52 EDT
(In reply to comment #72)
> I'll file a BZ for that

Done at bug #725480
Comment 74 Honza Horak 2011-07-25 11:42:42 EDT
(In reply to comment #72)
> Oh, good.  So we don't need that part of mysqld_safe, even if it's not just a
> leftover.  I think that it would be a good idea to change mysqld_safe to exec
> mysqld and use type = simple, so that we don't have to rely on the PIDfile
> property ... but first we will have to get Dan Walsh to tweak the selinux rules
> to allow that.  I'll file a BZ for that, but unless he responds really fast I
> think we'll have to use type = forking as a stopgap.

Well, the PIDFile= property is not used at all currently. Service's cgroup contains only one process at most, which is the main one and systemd is smart enough to recognize it.

Then there is another reason why to use "forking" type. After we've dropped mysqld.socket, we need to say to systemd that initialization has finished, which is not easy in "simple" type. There is a "notify" type, which should be able to use for that purpose, but I haven't test it yet. 

And what's more, I think logging options should have to be provided by systemd, if we use "simple" type, or we need to change stdout/stderr somehow before "exec" command.

So the "forking" type seems to be a much better way now, at least for me.

If you're not very pleased with the /var/run/mysql/config file to load config in the unit file (to be honest I don't like it very much), it should be possible to use mixed solution: To have a script /usr/libexec/mysql_safe_config, which loads config and execs mysqld_safe. mysqld_safe will fork a child (mysqld as it is currently done) and it should do the thing (but not tested yet). It also shouldn't need a selinux policy update.
Comment 75 Tom Lane 2011-07-25 12:28:24 EDT
(In reply to comment #74)
> Then there is another reason why to use "forking" type. After we've dropped
> mysqld.socket, we need to say to systemd that initialization has finished,
> which is not easy in "simple" type. There is a "notify" type, which should be
> able to use for that purpose, but I haven't test it yet. 

In either simple or forking mode, you really need an ExecStartPost step that probes to see if the daemon is able to accept connections yet, else you can have clients hanging for a long time during database recovery.  So I'm not convinced that there's any advantage to forking mode.

We could perhaps avoid that with "notify" mode, but that would require hacking the mysqld sources, which I'd much prefer not to.  I've spent years maintaining some of the patches we do carry, and every one of them is a continuing pain in the rear because of upstream changes touching the same code.

> And what's more, I think logging options should have to be provided by systemd,
> if we use "simple" type, or we need to change stdout/stderr somehow before
> "exec" command.

As long as we're still using the mysqld_safe script, it will set up logging the way users expect.  I'm not on board with the idea that we should drop everything everybody knows about how to configure their daemons and replace it with systemd-isms.  That might be appropriate a few years from now, but not today.

> If you're not very pleased with the /var/run/mysql/config file to load config
> in the unit file (to be honest I don't like it very much), it should be
> possible to use mixed solution: To have a script
> /usr/libexec/mysql_safe_config, which loads config and execs mysqld_safe.

Yeah, I was playing with that approach on Friday, but it wasn't working for me because of selinux issues.

Given that we're patching mysqld_safe anyway, I'm inclined to patch it a little more to pick up those defaults for itself.  Or we could just fall back on the assumption that it will arrive at the same answers we do and forcing that result is unnecessary.  The initscript is really only doing that because it's cheap insurance; if it gets to be expensive insurance, it might not be worth the trouble.
Comment 76 Tom Lane 2011-07-26 20:54:01 EDT
Created attachment 515389 [details]
rolled-up patch against git head

Attached is a rolled-up patch that includes all the added files as well as the specfile changes.  It's based on Honza's latest version with some corrections.  I am not intending to commit it as-is, because there's some debug cruft in it (some /usr/bin/logger calls, and the build-time regression tests are temporarily disabled by default since they take an hour and are unrelated to packaging issues).  But if anyone would like to help test it, this is easier to apply than the individual files.

I'd like to get this or something close to it committed before we start to worry about whether there's any sane way of enabling socket-based activation.
Comment 77 Fedora Update System 2011-07-27 19:59:51 EDT
mysql-5.5.14-3.fc16 has been submitted as an update for Fedora 16.
https://admin.fedoraproject.org/updates/mysql-5.5.14-3.fc16
Comment 78 Fedora Update System 2011-08-22 11:30:06 EDT
mysql-5.5.14-3.fc16 has been pushed to the Fedora 16 stable repository.  If problems still persist, please make note of it in this bug report.
Comment 79 Harald Reindl 2011-09-16 19:57:01 EDT
"/usr/libexec/mysqld-prepare-db-dir" and "/usr/libexec/mysqld-wait-ready" not working if /etc/my.cnf does not contain default-values - why in the world is mysqld started with totally wrong configuration?

mysql     4765  2.8  1.5 479864 40656 ?        Sl   01:48   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/testserver.rhsoft.net.err --pid-file=/var/lib/mysql/testserver.rhsoft.net.pid

why is "mysqld_safe" used for start and patched for "--nowatch" instead throw it away and call mysqld directly?

[Service]
Type=simple
PIDFile=/var/run/mysqld/mysqld.pid
ExecStart=/usr/libexec/mysqld --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --user=mysql
ExecStop=/bin/kill -15 $MAINPID

It makes me really tired have no useable mysqld after months :-(
______________

[root@testserver:~]$ /usr/bin/my_print_defaults mysqld datadir
--socket=/var/lib/mysql/mysql.sock
--datadir=/Volumes/dune/mysql_data
--pid-file=/Volumes/dune/mysql_data/mysql.pid
--tmpdir=/var/www/mysqltemp
--character-set-server=latin1
--default-time-zone=Europe/Vienna
--default-storage-engine=myisam
--lower_case_table_names=1
--log-error=/Volumes/dune/www-servers/_logs/mysql_error.log
--slow_query_log=1
--slow_query_log_file=/Volumes/dune/www-servers/_logs/mysql_slow_query.log
--port=3306
--old_passwords=0
--local-infile=0
--thread_concurrency=8
--sql-mode=STRICT_ALL_TABLES
--delay-key-write=ALL
--log_queries_not_using_indexes=1
--open-files-limit=8192
--wait_timeout=300
--interactive_timeout=300
--max_allowed_packet=200M
--max_connections=100
--max_tmp_tables=50
--max_connect_errors=250
--max_delayed_threads=15
--flush_time=0
--query_cache_limit=100K
--query_cache_size=75M
--query_cache_type=1
--table_cache=500
--thread_cache=100
--tmp_table_size=128M
--max_heap_table_size=128M
--key_buffer=50M
--sort_buffer=256K
--sort_buffer_size=256K
--myisam_sort_buffer_size=256K
--join_buffer_size=1M
--preload_buffer=128K
--read_buffer=128K
--read_buffer_size=128K
--read_rnd_buffer=128K
--innodb_buffer_pool_size=50M
--innodb_buffer_pool_instances=1
--innodb_purge_threads=1
--innodb_max_purge_lag=200000
--innodb_max_dirty_pages_pct=60
--innodb_additional_mem_pool_size=1M
--innodb_log_file_size=80M
--innodb_log_buffer_size=2M
--innodb_thread_concurrency=16
--innodb_thread_sleep_delay=10
--innodb_flush_log_at_trx_commit=2
--innodb_support_xa=1
--innodb_lock_wait_timeout=50
--innodb_table_locks=0
--innodb_checksums=0
--innodb_file_format=barracuda
--innodb_file_per_table=1
--innodb_open_files=300
--innodb_io_capacity=300
--innodb_read_io_threads=1
--innodb_write_io_threads=1
--transaction-isolation=READ-COMMITTED
--low-priority-updates
--skip-symbolic-links
--skip-name-resolve
--safe-user-create
--slave_compressed_protocol
Comment 80 Tom Lane 2011-09-16 20:14:28 EDT
(In reply to comment #79)
> "/usr/libexec/mysqld-prepare-db-dir" and "/usr/libexec/mysqld-wait-ready" not
> working if /etc/my.cnf does not contain default-values

Oh?  Please file a bug with a concrete example.  They certainly should work, since they use my_print_defaults to get the values they need.

> why is "mysqld_safe" used for start and patched for "--nowatch" instead throw
> it away and call mysqld directly?

Please read the extremely long discussion that 's already in this bugzilla.  I'm not interested in repeating it to you once again.
Comment 81 Harald Reindl 2011-09-16 21:03:30 EDT
the scripts and "my_print_defaults" are working but your useless call of "safe_mysqld" is falling back to default-values, use my "mysqld.service" and "mysqld" is working like a charme even without socket-activation and using "/etc/my.cnf" properly

the discussion of mysqld_safe does not interest me since you are not able to provide a usefull mysqld since many months and unwilling to understand that "mysqld_safe" is unneeded complexity in context of "systemd" and to get a real benefit of systemd using socket-activation would be the right way instead a dumb "waiting..." 

i undrstood long beofre you joined the party of this bug-report that "mysqld_safe" is bullshit since i have running two mysqld-instances with different ports/datadirs on different disks (master/lsave) and using mysqld_safe in a native-service resultetd in both using the same datadir

additionally add the following lines in "mysqld-prepare-db-dir" to make sure that the folder for the default-socket of all mysql-packages of fedora exists with the right permissions in case "datadir" is not set to "/var/lib/mysql"

# we need "/var/lib/mysql" independent of the dadadir for the default-socket
# which is used in most applications like php as default
mkdir -p /var/lib/mysql 2> /dev/null > /dev/null
chown mysql:mysql /var/lib/mysql 2> /dev/null > /dev/null
chmod 755 /var/lib/mysql 2> /dev/null > /dev/null


[root@testserver:~]$ cat /lib/systemd/system/mysqld.service 
[Unit]
Description=MySQL Database
After=syslog.target network.target
Before=httpd.service postfix.service dovecot.service dbmail-imapd.service dbmail-lmtpd.service dbmail-pop3d.service dbmail-timsieved.service dbmail-postfix-policyd.service
[Service]
Type=simple
PIDFile=/var/run/mysqld/mysqld.pid
ExecStartPre=/usr/libexec/mysqld-prepare-db-dir
ExecStart=/usr/libexec/mysqld --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --user=mysql
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
ExecStop=/bin/kill -15 $MAINPID
Restart=always
RestartSec=5
TimeoutSec=300
[Install]
WantedBy=multi-user.target
Comment 82 Harald Reindl 2011-09-16 21:05:32 EDT
BTW: Please file a bug with a concrete example

what more would you need than my outputs above of "/usr/bin/my_print_defaults mysqld datadir" and the output auf "ps aux" how your dumb service is calling mysqld?
Comment 83 Honza Horak 2011-09-19 07:29:37 EDT
(In reply to comment #82)
> what more would you need than my outputs above of "/usr/bin/my_print_defaults
> mysqld datadir" and the output auf "ps aux" how your dumb service is calling
> mysqld?

I've tested the new build again and found no problem. All my configuration from /etc/my.cfg was correctly used. 

If you won't provide a better example of what is wrong, there is no way to help.
Comment 84 Harald Reindl 2011-09-19 07:47:37 EDT
i used http://koji.fedoraproject.org/koji/buildinfo?buildID=256279 as reference for my F15 build and as you see in my outputs "mc.cnf" is only used in the shellscipts but mysqld fired up with "/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql" from "mysqld_safe"

using mysql 5.5.16 in this case since we are in production with 5.5.16 since this weekend even on Fedora 14

anyways - for me i see no reason to use "mysqld_safe" and problems with SELinux have to fixed there and not with a dirty workaround using shellscripts, normally this all should have been happend for the GA of F15 and because i am tired of this topic my own mysqld-package works fine now and if other people have the same problems with mysqld like a had the rest of F15 and even with F16 does not affect me any longer - i have to see get other troubles of the too soon introduced systemd in F15 cleard to prepare the dist-upgrade for > 20 servers here in december
Comment 85 Honza Horak 2011-09-19 08:22:33 EDT
(In reply to comment #84)
> i used http://koji.fedoraproject.org/koji/buildinfo?buildID=256279 as reference
> for my F15 build and as you see in my outputs "mc.cnf" is only used in the
> shellscipts but mysqld fired up with "/usr/libexec/mysqld --basedir=/usr
> --datadir=/var/lib/mysql" from "mysqld_safe"

I've just installed this build (which is not in updates btw.) on fresh F15 virtual machine. Then I've changed datadir value in my.cfg to "/var/lib/mynewdirectory" and started mysqld. This is what systemd runs:

# ps aux | grep mysqld

root      1855  0.0  0.1 110468  1500 ?        S    14:13   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mynewdirectory --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql     2048  0.3  5.3 546140 43152 ?        Sl   14:13   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mynewdirectory --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

This is correct, afaik.
Comment 86 Jon Ciesla 2012-02-13 14:45:38 EST
Tom, this appears largely if not totally complete.  Can it be closed, or does anything remain to be done?
Comment 87 Tom Lane 2012-02-13 19:54:08 EST
I had left it open on the expectation that we'd have to do more work.  But given the lack of other interest in socket activation of mysql, I'm fine with closing it now.
Comment 88 Jon Ciesla 2012-02-13 21:32:15 EST
Cool, thanks!

Note You need to log in before you can comment on or make changes to this bug.