Re: [omd-users] Need help with mysql in OMD
Yeah - I was doing some deeper investigation into how to repair this change in behavior and found that newer versions of mariadb have a newer 'mysql_install_db' routine that allows specifying '--auth-root-authentication-method=' but it's only in newer versions and not available in the repos yet for this distro rev. I'm doing my experimenting now on a 17.10 of ubuntu, which at least has mariadb 10.1 available, which does have this parameter available...and is working great already, so no need to fix for this newer setup. It seems to just affect the 16 series ubuntu and the older mariadb that's in the repos. Thanks a bunch for cracking the nut on solving this for other folks though. I'm in love with this integration you all have pulled off. -Chris On 4/13/18 7:12 AM, Sven Nierlein wrote: > On 13.04.2018 12:48, Sven Nierlein wrote: >> On 10.04.2018 21:09, Chris Moody wrote: >>> This might be something that needs factored into the OMD deployment >>> routines so that this plugin value gets cleared (or explicitly set) upon >>> creation of a new site instance...otherwise every new site created will end >>> up with the same problem. I just created a new site and verified. >> That might be tricky, because `mysql_install_db` runs in site context only, >> so we don't have root permissions here to connect to mysql. >> > Found a way, should be better next time. > > Cheers, > Sven > ___ omd-users mailing list omd-users@lists.mathias-kettner.de http://lists.mathias-kettner.de/mailman/listinfo/omd-users
Re: [omd-users] Need help with mysql in OMD
On 13.04.2018 12:48, Sven Nierlein wrote: > On 10.04.2018 21:09, Chris Moody wrote: >> This might be something that needs factored into the OMD deployment routines >> so that this plugin value gets cleared (or explicitly set) upon creation of >> a new site instance...otherwise every new site created will end up with the >> same problem. I just created a new site and verified. > That might be tricky, because `mysql_install_db` runs in site context only, > so we don't have root permissions here to connect to mysql. > Found a way, should be better next time. Cheers, Sven ___ omd-users mailing list omd-users@lists.mathias-kettner.de http://lists.mathias-kettner.de/mailman/listinfo/omd-users
Re: [omd-users] Need help with mysql in OMD
On 10.04.2018 21:09, Chris Moody wrote: > This might be something that needs factored into the OMD deployment routines > so that this plugin value gets cleared (or explicitly set) upon creation of a > new site instance...otherwise every new site created will end up with the > same problem. I just created a new site and verified. That might be tricky, because `mysql_install_db` runs in site context only, so we don't have root permissions here to connect to mysql. ___ omd-users mailing list omd-users@lists.mathias-kettner.de http://lists.mathias-kettner.de/mailman/listinfo/omd-users
Re: [omd-users] Need help with mysql in OMD
Figured out the problem. It's due to the default behavior of MariaDB on ubuntu 16.x installs. I found a couple other threads indicating similar behavior where the actual 'root' user could connect into mysql/maria but non-root users could not connect, even with the same credentials. It all boils down to how MariaDB is defaulting new installs. https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/ ===[ Brief summary This plugin allows the user to use operating system credentials when connecting to MariaDB via Unix socket. It works by retrieving |uid| of the process that has connected to the socket (using the |getsockopt(..., SO_PEERCRED, ...)| call) and allowing to connect to the MariaDB account with the corresponding user name. === Logging into the site specific mysql socket as the actual root user, I checked the following. == MariaDB [mysql]> SELECT user,host,plugin FROM user; +--+---+-+ | user | host | plugin | +--+---+-+ | root | localhost | *unix_socket* | +--+---+-+ 1 row in set (0.00 sec) == *unix_socket* is the culprit. Running the following clears this default plugin setting and allows the site-specific user to connect successfully. == MariaDB [mysql]> UPDATE user SET plugin=""; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [mysql]> SELECT user,host,plugin FROM user; +--+---++ | user | host | plugin | +--+---++ | root | localhost | | +--+---++ 1 row in set (0.00 sec) MariaDB [mysql]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) == Now all of the init scripts work (and the mysqladmin ping command that was complaining) and OMD is happy again. :o) This might be something that needs factored into the OMD deployment routines so that this plugin value gets cleared (or explicitly set) upon creation of a new site instance...otherwise every new site created will end up with the same problem. I just created a new site and verified. == root@omd1:~# omd create testsql Adding /omd/sites/testsql/tmp to /etc/fstab. Creating temporary filesystem /omd/sites/testsql/tmp...OK Apache port 5000 is in use. I've choosen 5003 instead. Restarting Apache...OK Created new site testsql with version 2.70-labs-edition. The site can be started with omd start testsql. The default web UI is available at http://omd1.node-nine.com/testsql/ The admin user for the web applications is omdadmin with password omd. Please do a su - testsql for administration of this site. root@omd1:~# su - testsql OMD[testsql]:~$ omd config set MYSQL on OMD[testsql]:~$ omd start Starting MySQL... first start of MySQL, creating initial database... .OK. Starting rrdcached...OK Starting npcd...OK Starting naemon...OK Starting dedicated Apache for site testsql...OK Initializing Crontab...OK OMD[testsql]:~$ omd restart Removing Crontab...OK Stopping dedicated Apache for site testsqlOK Stopping naemonOK Stopping npcd...OK Stopping rrdcached...waiting for terminationOK Stopping MySQL... mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost'' ..ERROR. Starting MySQL... already running. ERROR. Starting rrdcached...OK Starting npcd...OK Starting naemon...OK Starting dedicated Apache for site testsql...OK Initializing Crontab...OK OMD[testsql]:~$ == Thanks again for the pointers in the right direction to track down what was even spitting out the errors in the first place. Cheers, -Chris On 4/9/18 4:41 PM, Chris Moody wrote: > Thank you for the pointer Sven. > > I executed that in the /omd/sites/ directory and su'd to the > user in question since I noticed the proceeding '.' before the > path. Here's the results. > > root@omd1:/omd/sites/nsight# su - nsight > OMD[nsight]:~$ > OMD[nsight]:~$ > OMD[nsight]:~$ > OMD[nsight]:~$ bash -x ./etc/init.d/mysql start > + cd /omd/sites/nsight > + . lib/omd/init_profile > + . etc/omd/site.conf > ++ CONFIG_ADMIN_MAIL=hostmas...@node-nine.com > ++ CONFIG_ALERTMANAGER=off > ++ CONFIG_ALERTMANAGER_TCP_ADDR=127.0.0.1 > ++ CONFIG_ALERTMANAGER_TCP_PORT=9094 > ++ CONFIG_APACHE_MODE=own > ++ CONFIG_APACHE_TCP_ADDR=127.0.0.1 > ++ CONFIG_APACHE_TCP_PORT=5001 > ++ CONFIG_AUTOSTART=on > ++ CONFIG_BLACKBOX_EXPORTER=off > ++ CONFIG_BLACKBOX_EXPORTER_TCP_ADDR=127.0.0.1 > ++ CONFIG_BLACKBOX_EXPORTER_TCP_PORT=9116 > ++ CONFIG_CORE=nagios > ++ CONFIG_CRONTAB=on > ++ CONFIG_DATASCRYER=on > ++ CONFIG_DEFAULT_GUI=thruk > ++ CONFIG_DOKUWIKI_AUTH=off > ++ CONFIG_DOWNTIMEAPI=on > ++ CONFIG_GEARMAND=on > ++ CONFIG_GEARMAND_PORT=localhost:4730 > ++ CONFIG_GEARMAN_NEB=on > ++ CONFIG_GEARMAN_WORKER=on > ++ CONFIG_GRAFANA=on > ++ CONFIG_GRAFANA_TCP_PORT=8004 > ++ CONFIG_INFLUXDB=on > ++ CONFIG_INFLUXDB_HTTP_TCP_PORT=127.0.0.1:8086 > ++ CONFIG_INFLUXDB_META_TCP_PORT=8089 > ++ CONFIG_INFLUXDB_RETEN
Re: [omd-users] Need help with mysql in OMD
Thank you for the pointer Sven. I executed that in the /omd/sites/ directory and su'd to the user in question since I noticed the proceeding '.' before the path. Here's the results. root@omd1:/omd/sites/nsight# su - nsight OMD[nsight]:~$ OMD[nsight]:~$ OMD[nsight]:~$ OMD[nsight]:~$ bash -x ./etc/init.d/mysql start + cd /omd/sites/nsight + . lib/omd/init_profile + . etc/omd/site.conf ++ CONFIG_ADMIN_MAIL=hostmas...@node-nine.com ++ CONFIG_ALERTMANAGER=off ++ CONFIG_ALERTMANAGER_TCP_ADDR=127.0.0.1 ++ CONFIG_ALERTMANAGER_TCP_PORT=9094 ++ CONFIG_APACHE_MODE=own ++ CONFIG_APACHE_TCP_ADDR=127.0.0.1 ++ CONFIG_APACHE_TCP_PORT=5001 ++ CONFIG_AUTOSTART=on ++ CONFIG_BLACKBOX_EXPORTER=off ++ CONFIG_BLACKBOX_EXPORTER_TCP_ADDR=127.0.0.1 ++ CONFIG_BLACKBOX_EXPORTER_TCP_PORT=9116 ++ CONFIG_CORE=nagios ++ CONFIG_CRONTAB=on ++ CONFIG_DATASCRYER=on ++ CONFIG_DEFAULT_GUI=thruk ++ CONFIG_DOKUWIKI_AUTH=off ++ CONFIG_DOWNTIMEAPI=on ++ CONFIG_GEARMAND=on ++ CONFIG_GEARMAND_PORT=localhost:4730 ++ CONFIG_GEARMAN_NEB=on ++ CONFIG_GEARMAN_WORKER=on ++ CONFIG_GRAFANA=on ++ CONFIG_GRAFANA_TCP_PORT=8004 ++ CONFIG_INFLUXDB=on ++ CONFIG_INFLUXDB_HTTP_TCP_PORT=127.0.0.1:8086 ++ CONFIG_INFLUXDB_META_TCP_PORT=8089 ++ CONFIG_INFLUXDB_RETENTION=104 ++ CONFIG_LIVESTATUS_TCP=on ++ CONFIG_LIVESTATUS_TCP_PORT=6557 ++ CONFIG_MKEVENTD=on ++ CONFIG_MKEVENTD_SNMPTRAP=on ++ CONFIG_MKEVENTD_SYSLOG=on ++ CONFIG_MKEVENTD_SYSLOG_TCP=off ++ CONFIG_MOD_GEARMAN=on ++ CONFIG_MULTISITE_AUTHORISATION=off ++ CONFIG_MULTISITE_COOKIE_AUTH=off ++ CONFIG_MYSQL=on ++ CONFIG_NAGFLUX=on ++ CONFIG_NAGIOS_THEME=classicui ++ CONFIG_NAGVIS_URLS=thruk ++ CONFIG_NSCA=on ++ CONFIG_NSCA_TCP_PORT=5667 ++ CONFIG_PNP4NAGIOS=on ++ CONFIG_PROMETHEUS=on ++ CONFIG_PROMETHEUS_SNMP_ADDR=127.0.0.1 ++ CONFIG_PROMETHEUS_SNMP_EXPORTER=off ++ CONFIG_PROMETHEUS_SNMP_PORT=9217 ++ CONFIG_PROMETHEUS_TCP_ADDR=127.0.0.1 ++ CONFIG_PROMETHEUS_TCP_PORT=9091 ++ CONFIG_SNMPTRAPD=off ++ CONFIG_SNMPTRAPD_UDP_PORT=9163 ++ CONFIG_THRUK_COOKIE_AUTH=on ++ CONFIG_THRUK_LOGCACHE=on ++ CONFIG_TMPFS=on + '[' on '!=' on ']' + MYSQLD_PID=tmp/run/mysqld/mysqld.pid + MYSQL_CNF=.my.cnf + __init_hook ./etc/init.d/mysql start pre + '[' -h ./etc/init.d/mysql ']' + file=./etc/init.d/mysql ++ printf /omd/sites/nsight/etc/init-hooks.d/%s-%s-%s mysql start pre + hook=/omd/sites/nsight/etc/init-hooks.d/mysql-start-pre + '[' -x /omd/sites/nsight/etc/init-hooks.d/mysql-start-pre ']' + '[' -e /omd/sites/nsight/etc/init-hooks.d/mysql-start-pre ']' + case $1 in + echo -n 'Starting MySQL... ' Starting MySQL... + check_install_db + '[' '!' -e /omd/sites/nsight/var/mysql/mysql ']' + mysql_wait_start + mysqld_status check_alive nowarn ++ mysqladmin --defaults-file=.my.cnf ping *+ ping_output='mysqladmin: connect to server at '\''localhost'\'' failed* *error: '\''Access denied for user '\''root'\''@'\''localhost'\'''\'''* + ping_alive=1 + ps_alive=0 + '[' -f tmp/run/mysqld/mysqld.pid ']' ++ cat tmp/run/mysqld/mysqld.pid + ps 8643 + ps_alive=1 + '[' check_alive = check_alive -a 1 = 1 ']' + return 0 + echo -n 'already running. ' already running. + return 1 + echo ERROR. ERROR. + __init_hook ./etc/init.d/mysql start post 1 + '[' -h ./etc/init.d/mysql ']' + file=./etc/init.d/mysql ++ printf /omd/sites/nsight/etc/init-hooks.d/%s-%s-%s mysql start post + hook=/omd/sites/nsight/etc/init-hooks.d/mysql-start-post + '[' -x /omd/sites/nsight/etc/init-hooks.d/mysql-start-post ']' + '[' -e /omd/sites/nsight/etc/init-hooks.d/mysql-start-post ']' + exit 1 OMD[nsight]:~$ Reading the init script (which again, thank you for the pointer - I didn't even think that there would be localized ./etc/init.d/ scripts per site/user. I need to explore the filesystem layout a bit more) it's the mysqladmin 'ping' that's complaining in the init script. Testing a bit more, when su'd as the site-user, mysqladmin throws the error upon trying to login/connect however when just logged in as root, a user can get in just fine. Now I at least know where to keep digging and adjusting to help get the permissions functioning. I'll post my results once I have it functioning. Cheers, -Chris On 4/8/18 11:40 PM, Sven Nierlein wrote: > bash -x ./etc/init.d/mysql start ___ omd-users mailing list omd-users@lists.mathias-kettner.de http://lists.mathias-kettner.de/mailman/listinfo/omd-users
