Show all Database Users in Trellis

Sometimes you want to show all users in Trellis. Just to be sure a certain database user is there. I for example keep on forgetting the database username generated based on the group_vars/group/vault.yml. And I need it to access the database properly with Sequel Pro . So how do we do this?

SSH into Box

To figure this out you first need to ssh into your Vagrant box or VPS. This you can do using

vagrant ssh

to access your Vagrant box or

ssh web@box.com

to access your staging or production server. Next you can work on accessing the database.

Access MariaDB

Then you need to enter

mysql -u root -p

to access the MySQL database. Then you add the root password which IS easily found in that same file:

vault_mysql_root_password: devpw

In our case the vault.yml for development. For production you will use something tougher of course. So in our case (local development on Vagrant) and most of your cases working locally with standard settings this will then be devpw.

Display all Database Users

Well you can now do this database query to get all users:

SELECT User FROM mysql.user;

You will then see a list similar to this one:

MariaDB [(none)]> SELECT User FROM mysql.user;
+------------------+
| User                 |
+------------------+
| root                  |
| root                  |
| root                  |
| debian-sys-maint|
| example_com     |
| root                 |
+------------------+
6 rows in set (0.00 sec)

Only in our case we have a slightly adjusted box. The main user we were looking for is example_com here. Trellis generates a username based on the domain or projectname. One with a underscore instead of the dot.

Database Password

The password is shown in the same file:

db_password: example_dbpassword
So it is example_dbpassword . To use these details and access the database I refer again to the earlier mentioned article on Sequel Pro.

MariaDB on Trellis Failed to Start – /usr/bin/mysqld_safe: No such file or directory

Post Digital Ocean restart to deal with security patches for vulnerabilities (Spectre and Meltdown Mitigation )MariaDB on Trellis failed to start. So the site failed to connect to the database and went down.

MariaDB Down

MariaDB was down and restarting it failed suggesting us to check journalctl -xe for more information. In the journalctl -xe logs we had:

Feb 23 02:26:07 domain mysql[1963]:  * Starting MariaDB database server mysqld
Feb 23 02:26:07 domain /etc/init.d/mysql[1993]: /etc/init.d/mysql: line 114: /usr/bin/mysqld_safe: No such file or directory
Feb 23 02:26:38 domain /etc/init.d/mysql[2275]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' re
Feb 23 02:26:38 domain /etc/init.d/mysql[2275]: [61B blob data]
Feb 23 02:26:38 domain /etc/init.d/mysql[2275]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' 
Feb 23 02:26:38 domain /etc/init.d/mysql[2275]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Feb 23 02:26:38 domain /etc/init.d/mysql[2275]: 
Feb 23 02:26:38 domain mysql[1963]:    ...fail!
Feb 23 02:26:38 domain systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 23 02:26:38 domain systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.

Mysqld_safe MIA

Main line that stood out was:

/etc/init.d/mysql: line 114: /usr/bin/mysqld_safe: No such file or directory

The mysql Safe was missing in action. When I finally Googled this issue I bumped into many threads- example –  recommending me to reinstall MySQL.

MySQL.Sock Restart

With a:

mysqld --datadir=/var/lib/mysql --user=mysql --socket=/var/run/mysqld/mysqld.sock

I was able to get MySQL up and running again. Earlier commands like

  • service mysql start,
  • service mysql restart, 
  • /etc/init.d/mysql start

failed.

Then I still had the issue so I go looking, and  mysqld_safe doesn’t exist on this system as stated before.

MariaDB Purge & Installation

So as suggested by Swalkinshaw I did a:

apt-get purge mariadb-client mariadb-server

and I got:

Reading package lists... Done
Building dependency tree       
Reading state information... Done
Package 'mariadb-client' is not installed, so not removed
Package 'mariadb-server' is not installed, so not removed

So it said there was nothing to remove!

Trellis Provisioning Failure

When I re-provisioned (as I did not notice the fact the removal had not worked) I got a failure in the provisioning. Here is the part of the output on the failure (shortened somewhat with dots as replacement of some parts):

'/usr/bin/apt-get -y -o "Dpkg::Options::=--force-confdef" -o "Dpkg::Options
::=--force-confold"     install 'mariadb-server'' failed: No apport report
written because the error message indicates its a followup error from a
previous failure.
E: Sub-process /usr/bin/dpkg returned an error code (1)

No apport report written because the error message indicates its a followup
error from a previous failure.
E: Sub-process /usr/bin/dpkg returned an error code (1)

fatal: [104.131.166.212]: FAILED! => {"cache_update_time": 1519360682, "cache_updated": false, "changed": false, "failed": true, "rc": 100, "stderr_lines": ["No apport report written because the error message indicates its a followup error from a previous failure.", "E: Sub-process /usr/bin/dpkg returned an error code (1)"], "stdout": "Reading package lists...\nBuilding dependency ......... mariadb-server mariadb-server-10.2\n  mariadb-server-core-10.2 socat\n0 upgraded, 18 newly installed, 0 to remove and 98 not upgraded.\nNeed to get 18.3 MB of archives.\nAfter this operation, 147 MB of additional disk space will be used.\nGet:1 http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial/main amd64 galera-3 amd64 25.3.23-xenial [8015 kB]\nGet:2 ..................http://nyc2.mirrors.digitalocean.com/ubuntu xenial/main amd64 libhttp-message-perl all 6.11-1 [74.3 kB]\nPreconfiguring packages ...\nFetched 18.3 MB in 1s (14.8 MB/s)\nSelecting previously unselected package galera-3.\r\n(Reading database ... \r(Reading database ... 5%\r(Reading database ... 10%\r(Reading database ... 15%\r(Reading database ... 20%\r(Reading database ... 25%\r(Reading database ... 30%\r(Reading database ... 35%\r(Reading database ... 40%\r(Reading database ... 45%\r(Reading database ... 50%\r(Reading database ... 55%\r(Reading database ... 60%\r(Reading database ... 65%\r(Reading database ... 70%\r(Reading database ... 75%\r(Reading database ... 80%\r(Reading database ... 85%\r(Reading database ... 90%\r(Reading database ... 95%\r(Reading database ... 100%\r(Reading database ... 97853 files and directories currently installed.)\r\nPreparing to unpack .../galera-3_25.3.23-xenial_amd64.deb ...\r\nUnpacking galera-3 (25.3.23-xenial) ...\r\nSelecting previously unselected package libaio1:amd64.\r\nPreparing to unpack .../libaio1_0.3.110-2_amd64.deb ...\r\nUnpacking libaio1:amd64 (0.3.110-2) ...\r\nSelecting previously unselected package mariadb-server-core-10.2.\r\nPreparing to unpack .../mariadb-server-core-10.2_10.2.13+maria~xenial_amd64.deb ...\r\nUnpacking mariadb-server-core-10.2 (10.2.13+maria~xenial) ...\r\nSelecting previously unselected package socat.\r\nPreparing to unpack .../socat_1.7.3.1-1_amd64.deb ...\r\nUnpacking socat (1.7.3.1-1) ...\r\nSelecting previously unselected package mariadb-server-10.2.\r\nPreparing to unpack .../mariadb-server-10.2_10.2.13+maria~xenial_amd64.deb ...\r\n/var/lib/mysql: found previous version 10.0\r\nUnpacking mariadb-server-10.2 (10.2.13+maria~xenial) ...\r\nSelecting previously unselected package libhtml-tagset-perl.\r\nPreparing to unpack .../libhtml-tagset-perl_3.20-2_all.deb ...\r\nUnpacking libhtml-tagset-perl (3.20-2) ...\r\nSelecting previously unselected package liburi-perl.\r\nPreparing to unpack .../liburi-perl_1.71-1_all.deb ...\r\nUnpacking liburi-perl (1.71-1) ...\r\nSelecting previously unselected package libhtml-parser-perl.\r\nPreparing to unpack .../libhtml-parser-perl_3.72-1_amd64.deb ...\r\nUnpacking libhtml-parser-perl (3.72-1) ...\r\nSelecting previously unselected package libcgi-pm-........../libio-html-perl_1.001-1_all.deb ...\r\nUnpacking libio-html-perl (1.001-1) ...\r\nSelecting previously unselected package liblwp-mediatypes-perl.\r\nPreparing to unpack .../liblwp-mediatypes-perl_6.02-1_all.deb ...\r\nUnpacking liblwp-mediatypes-perl (6.02-1) ...\r\nSelecting previously unselected package libhttp-message-perl.\r\nPreparing to unpack .../libhttp-message-perl_6.11-1_all.deb ...\r\nUnpacking libhttp-message-perl (6.11-1) ...\r\nSelecting previously unselected package mariadb-server.\r\nPreparing to unpack .../mariadb-server_10.2.13+maria~xenial_all.deb ...\r\nUnpacking mariadb-server (10.2.13+maria~xenial) ...\r\nProcessing triggers for libc-bin (2.23-0ubuntu10) ...\r\nProcessing triggers for man-db (2.7.5-1) ...\r\nProcessing triggers for systemd (229-4ubuntu21) ...\r\nProcessing triggers for ureadahead (0.100.0-19) ...\r\nSetting up galera-3 (25.3.23-xenial) ...\r\nSetting up libaio1:amd64 (0.3.110-2) ...\r\nSetting up mariadb-server-core-10.2 (10.2.13+maria~xenial) ...\r\nSetting up socat (1.7.3.1-1) ...\r\nSetting up mariadb-server-10.2 (10.2.13+maria~xenial) ...\r\nInstalling new version of config file /etc/init.d/mysql ...\r\nInstalling new version of config file /etc/logrotate.d/mysql-server ...\r\nJob for mariadb.service failed because the control process exited with error code. See \"systemctl status mariadb.service\" and \"journalctl -xe\" for details.\r\ninvoke-rc.d: initscript mysql, action \"start\" failed.\r\n\u001b[0;1;31m*\u001b[0m mariadb.service - MariaDB 10.2.13 database server\r\n   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)\r\n  Drop-In: /etc/systemd/system/mariadb.service.d\r\n           `-migrated-from-my.cnf-settings.conf\r\n   Active: \u001b[0;1;31mfailed\u001b[0m (Result: exit-code) since Fri 2018-02-23 04:50:20 UTC; 7ms ago\r\n     Docs: man:mysqld(8)\r\n           https://mariadb.com/kb/en/library/systemd/\r\n  Process: 18506 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION \u001b[0;1;31m(code=exited, status=1/FAILURE)\u001b[0m\r\n  Process: 18406 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)\r\n  Process: 18399 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)\r\n  Process: 18393 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)\r\n Main PID: 18506 (code=exited, status=1/FAILURE)\r\n   Status: \"MariaDB server is down\"\r\n\r\nFeb 23 04:48:08 publiqly systemd[1]: Starting MariaDB 10.2.13 database server...\r\nFeb 23 04:48:09 publiqly mysqld[18506]: \u001b[0;1;31m2018-02-23  4:48:09 139880980293824 ....\u001b[0m\r\nFeb 23 04:49:39 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Start operation timed...g.\u001b[0m\r\nFeb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Main process exited, ...RE\u001b[0m\r\nFeb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;31mFailed to start MariaDB 10.2.13 databa...r.\u001b[0m\r\nFeb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Unit entered failed state.\u001b[0m\r\nFeb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Failed with result 'e...'.\u001b[0m\r\nHint: Some lines were ellipsized, use -l to show in full.\r\ndpkg: error processing package mariadb-server-10.2 (--configure):\r\n subprocess installed post-installation script returned error exit status 1\r\nSetting up libhtml-tagset-perl (3.20-2) ...\r\nSetting up liburi-perl (1.71-1) ...\r\nSetting up libhtml-parser-perl (3.72-1) ...\r\nSetting up libcgi-pm-perl (4.26-1) ...\r\nSetting up libfcgi-perl (0.77-1build1) ...\r\nSetting up libcgi-fast-perl (1:2.10-1) ...\r\nSetting up libencode-locale-perl (1.05-1) ...\r\nSetting up libhtml-template-perl (2.95-2) ...\r\nSetting up libhttp-date-perl (6.02-1) ...\r\nSetting up libio-html-perl (1.001-1) ...\r\nSetting up liblwp-mediatypes-perl (6.02-1) ...\r\nSetting up libhttp-message-perl (6.11-1) ...\r\ndpkg: dependency problems prevent configuration of mariadb-server:\r\n mariadb-server depends on mariadb-server-10.2 (>= 10.2.13+maria~xenial); however:\r\n  Package mariadb-server-10.2 is not configured yet.\r\n\r\ndpkg: error processing package mariadb-server (--configure):\r\n dependency problems - leaving unconfigured\r\nProcessing triggers for libc-bin (2.23-0ubuntu10) ...\r\nProcessing triggers for systemd (229-4ubuntu21) ...\r\nProcessing triggers for ureadahead (0.100.0-19) ...\r\nErrors were encountered while processing:\r\n mariadb-server-10.2\r\n mariadb-server\r\n", "stdout_lines": ["Reading package lists...", "Building dependency tree...", "Reading state information...", "The following packages were automatically installed and are no longer required:", "  libmariadb3 libmariadbclient18", "Use 'apt autoremove' to remove them.", "The following additional packages will be installed:", "  galera-3 libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl", "  libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl", "  libhttp-date-perl libhttp-message-perl libio-html-perl", "  liblwp-mediatypes-perl liburi-perl mariadb-server-10.2", "  mariadb-server-core-10.2 socat", "Suggested packages:", "  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx mariadb-test", "  tinyca", "The following NEW packages will be installed:", "  galera-3 libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl", "  libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl", "  libhttp-date-perl libhttp-message-perl libio-html-perl", "  liblwp-mediatypes-perl liburi-perl mariadb-server mariadb-server-10.2", "  mariadb-server-core-10.2 socat", "0 upgraded, 18 newly installed, 0 to remove and 98 not upgraded.", "Need to get 18.3 MB of archives.", "After this operation, 147 MB of additional disk space will be used.", "Get:1 http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial/main amd64 galera-3 amd64 25.3.23-xenial [8015 kB]", "Get:2 http://nyc2.mirrors.digitalocean.com/ubuntu xenial/main amd64 libaio1 amd64 0.3.110-2 [6356 B]", "Get:3 http://nyc2.mirrors.digitalocean.com/ubuntu xenial/universe amd64 socat amd64 1.7.3.1-1 [321 kB]", "Get:4 ....................... "Get:9 http://nyc2.mirrors.digitalocean.com/ubuntu xenial/main amd64 libfcgi-perl amd64 0.77-1build1 [32.3 kB]", "Get:10 http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial/main amd64 mariadb-server-10.2 amd64 10.2.13+maria~xenial [3897 kB]", "Get:11 http://nyc2.mirrors.digitalocean.com/ubuntu xenial/main amd64 libcgi-fast-perl all 1:2.10-1 [10.2 kB]", "Get:12 http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial/main amd64 mariadb-server all 10.2.13+maria~xenial [2962 B]", "Get:13 http://nyc2.mirrors.digitalocean.com/ubuntu xenial/main amd64 libencode-locale-perl all 1.05-1 [12.3 kB]", "Get:14 .......................", "Fetched 18.3 MB in 1s (14.8 MB/s)", "Selecting previously unselected package galera-3.", "(Reading database ... ", "(Reading database ... 5%", "(Reading database ... 10%", "(Reading database ... 15%", "(Reading database ... 20%", "(Reading database ... 25%", "(Reading database ... 30%", "(Reading database ... 35%", "(Reading database ... 40%", "(Reading database ... 45%", "(Reading database ... 50%", "(Reading database ... 55%", "(Reading database ... 60%", "(Reading database ... 65%", "(Reading database ... 70%", "(Reading database ... 75%", "(Reading database ... 80%", "(Reading database ... 85%", "(Reading database ... 90%", "(Reading database ... 95%", "(Reading database ... 100%", "(Reading database ... 97853 files and directories currently installed.)", "Preparing to unpack .../galera-3_25.3.23-xenial_amd64.deb ...", "Unpacking galera-3 (25.3.23-xenial) ...", "Selecting previously unselected package libaio1:amd64.", "Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ...", "Unpacking libaio1:amd64 (0.3.110-2) ...", "Selecting previously unselected package mariadb-server-core-10.2.", "Preparing to unpack .../mariadb-server-core-10.2_10.2.13+maria~xenial_amd64.deb ...", "Unpacking mariadb-server-core-10.2 (10.2.13+maria~xenial) ...", "Selecting previously unselected package socat.", "Preparing to unpack .../socat_1.7.3.1-1_amd64.deb ...", "Unpacking socat (1.7.3.1-1) ...", "Selecting previously unselected package mariadb-server-10.2.", "Preparing to unpack .../mariadb-server-10.2_10.2.13+maria~xenial_amd64.deb ...", "/var/lib/mysql: found previous version 10.0", "Unpacking mariadb-server-10.2 (10.2.13+maria~xenial) ...", "Selecting previously unselected package libhtml-tagset-perl.", "Preparing to unpack .../libhtml-tagset-perl_3.20-2_all.deb ...", "Unpacking libhtml-tagset-perl (3.20-2) ...", "Selecting previously unselected package liburi-perl.", "Preparing to unpack .../liburi-perl_1.71-1_all.deb ...", "Unpacking liburi-perl (1.71-1) ...", "Selecting previously unselected package libhtml-parser-perl.", "Preparing to unpack .../libhtml-parser-perl_3.72-1_amd64.deb ...", "Unpacking libhtml-parser-perl (3.72-1) ...", "Selecting previously unselected package libcgi-pm-perl.", "Preparing to unpack .../libcgi-pm-perl_4.26-1_all.deb ...", "Unpacking libcgi-pm-perl (4.26-1) ...", "Selecting previously unselected package libfcgi-perl.", "Preparing to unpack .../libfcgi-perl_0.77-1build1_amd64.deb ...", "Unpacking libfcgi-perl (0.77-1build1) ...", "Selecting previously unselected package libcgi-fast-perl.", "Preparing to unpack .../libcgi-fast-perl_1%3a2.10-1_all.deb ...", "Unpacking libcgi-fast-perl (1:2.10-1) ...", "Selecting previously unselected package libencode-locale-perl.", "Preparing to unpack .../libencode-locale-perl_1.05-1_all.deb ...", "Unpacking libencode-locale-perl (1.05-1) ...", "Selecting previously unselected package libhtml-template-perl.", "Preparing to unpack .../libhtml-template-perl_2.95-2_all.deb ...", "Unpacking libhtml-template-perl (2.95-2) ...", "Selecting previously unselected package libhttp-date-perl.", "Preparing to unpack .../libhttp-date-perl_6.02-1_all.deb ...", "Unpacking libhttp-date-perl (6.02-1) ...", "Selecting previously unselected package libio-html-perl.", "Preparing to unpack .../libio-html-perl_1.001-1_all.deb ...", "Unpacking libio-html-perl (1.001-1) ...", "Selecting previously unselected package liblwp-mediatypes-perl.", "Preparing to unpack .../liblwp-mediatypes-perl_6.02-1_all.deb ...", "Unpacking liblwp-mediatypes-perl (6.02-1) ...", "Selecting previously unselected package libhttp-message-perl.", "Preparing to unpack .../libhttp-message-perl_6.11-1_all.deb ...", "Unpacking libhttp-message-perl (6.11-1) ...", "Selecting previously unselected package mariadb-server.", "Preparing to unpack .../mariadb-server_10.2.13+maria~xenial_all.deb ...", "Unpacking mariadb-server (10.2.13+maria~xenial) ...", "Processing triggers for libc-bin (2.23-0ubuntu10) ...", "Processing triggers for man-db (2.7.5-1) ...", "Processing triggers for systemd (229-4ubuntu21) ...", "Processing triggers for ureadahead (0.100.0-19) ...", "Setting up galera-3 (25.3.23-xenial) ...", "Setting up libaio1:amd64 (0.3.110-2) ...", "Setting up mariadb-server-core-10.2 (10.2.13+maria~xenial) ...", "Setting up socat (1.7.3.1-1) ...", "Setting up mariadb-server-10.2 (10.2.13+maria~xenial) ...", "Installing new version of config file /etc/init.d/mysql ...", "Installing new version of config file /etc/logrotate.d/mysql-server ...", "Job for mariadb.service failed because the control process exited with error code. See \"systemctl status mariadb.service\" and \"journalctl -xe\" for details.", "invoke-rc.d: initscript mysql, action \"start\" failed.", "\u001b[0;1;31m*\u001b[0m mariadb.service - MariaDB 10.2.13 database server", "   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)", "  Drop-In: /etc/systemd/system/mariadb.service.d", "           `-migrated-from-my.cnf-settings.conf", "   Active: \u001b[0;1;31mfailed\u001b[0m (Result: exit-code) since Fri 2018-02-23 04:50:20 UTC; 7ms ago", "     Docs: man:mysqld(8)", "           https://mariadb.com/kb/en/library/systemd/", "  Process: 18506 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION \u001b[0;1;31m(code=exited, status=1/FAILURE)\u001b[0m", "  Process: 18406 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)", "  Process: 18399 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)", "  Process: 18393 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)", " Main PID: 18506 (code=exited, status=1/FAILURE)", "   Status: \"MariaDB server is down\"", "", "Feb 23 04:48:08 publiqly systemd[1]: Starting MariaDB 10.2.13 database server...", "Feb 23 04:48:09 publiqly mysqld[18506]: \u001b[0;1;31m2018-02-23  4:48:09 139880980293824 ....\u001b[0m", "Feb 23 04:49:39 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Start operation timed...g.\u001b[0m", "Feb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Main process exited, ...RE\u001b[0m", "Feb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;31mFailed to start MariaDB 10.2.13 databa...r.\u001b[0m", "Feb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Unit entered failed state.\u001b[0m", "Feb 23 04:50:20 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Failed with result 'e...'.\u001b[0m", "Hint: Some lines were ellipsized, use -l to show in full.", "dpkg: error processing package mariadb-server-10.2 (--configure):", " subprocess installed post-installation script returned error exit status 1", "Setting up libhtml-tagset-perl (3.20-2) ...", "Setting up liburi-perl (1.71-1) ...", "Setting up libhtml-parser-perl (3.72-1) ...", "Setting up libcgi-pm-perl (4.26-1) ...", "Setting up libfcgi-perl (0.77-1build1) ...", "Setting up libcgi-fast-perl (1:2.10-1) ...", "Setting up libencode-locale-perl (1.05-1) ...", "Setting up libhtml-template-perl (2.95-2) ...", "Setting up libhttp-date-perl (6.02-1) ...", "Setting up libio-html-perl (1.001-1) ...", "Setting up liblwp-mediatypes-perl (6.02-1) ...", "Setting up libhttp-message-perl (6.11-1) ...", "dpkg: dependency problems prevent configuration of mariadb-server:", " mariadb-server depends on mariadb-server-10.2 (>= 10.2.13+maria~xenial); however:", "  Package mariadb-server-10.2 is not configured yet.", "", "dpkg: error processing package mariadb-server (--configure):", " dependency problems - leaving unconfigured", "Processing triggers for libc-bin (2.23-0ubuntu10) ...", "Processing triggers for systemd (229-4ubuntu21) ...", "Processing triggers for ureadahead (0.100.0-19) ...", "Errors were encountered while processing:", " mariadb-server-10.2", " mariadb-server"]}

However, when I checked whether the database was up and running it was and the databases were there as well.. Weird.

DPKG Dependency Problems

However when I read

dpkg: dependency problems prevent configuration of mariadb-server:", " mariadb-server depends on mariadb-server-10.2 (>= 10.2.13+maria~xenial); however:", " Package mariadb-server-10.2 is not configured yet.", "", "dpkg: error processing package mariadb-server (--configure):", " dependency problems - leaving unconfigured

I saw there was a dependency problem.

DPKG Lock Issues

I also had issues running provisioning as it stated apt was busy:

fatal: [104.131.166.212]: FAILED! => {"changed": false, "rc": 100, "stdout": "/usr/bin/python\r\nE: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable)\r\nE: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it?\r\n", "stdout_lines": ["/usr/bin/python", "E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable)", "E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it?"]}

So I did a

sudo rm /var/lib/dpkg/lock

Once I did that I ran into

non-zero return code
Shared connection to xxx.xxx.xxx.xxx closed.
fatal: [104.131.166.212]: FAILED! => {"changed": false, "rc": 100, "stdout": "/usr/bin/python\r\nE: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem. \r\n", "stdout_lines": ["/usr/bin/python", "E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem. "]}

DPKG Config issues

So I ran a

sudo dpkg --configure -a

but then I got into this error

Setting up mariadb-server-10.2 (10.2.13+maria~xenial) ...
debconf: DbDriver "config": /var/cache/debconf/config.dat is locked by another process: Resource temporarily unavailable
dpkg: error processing package mariadb-server-10.2 (--configure):
subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mariadb-server:
mariadb-server depends on mariadb-server-10.2 (>= 10.2.13+maria~xenial); however:
Package mariadb-server-10.2 is not configured yet.
dpkg: error processing package mariadb-server (--configure):
dependency problems - leaving unconfigured
Errors were encountered while processing:
mariadb-server-10.2
mariadb-server

This I think was because another MariaDB version is running, but one ignored by the Trellis MariaDB playbook.
Then I decided to try and reconfigure the MariaDB Server:

dpkg-reconfigure mariadb-server-10.2
debconf: DbDriver "config": /var/cache/debconf/config.dat is locked by another process: Resource temporarily unavailable

So I checked what user was using the process and it was root. Then I killed the PID:

sudo fuser -v /var/cache/debconf/config.dat
kill PID

And that way I was able to start upgrading PHP, but MariaDB part of

ansible-playbook server.yml -e env=production --tags=php

failed anyways..

"Setting up mariadb-server-10.2 (10.2.13+maria~xenial) ...", "Job for mariadb.service failed because the control process exited with error code. See \"systemctl status mariadb.service\" and \"journalctl -xe\" for details.", "invoke-rc.d: initscript mysql, action \"start\" failed.", "\u001b[0;1;31m*\u001b[0m mariadb.service - MariaDB 10.2.13 database server", "   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)", "  Drop-In: /etc/systemd/system/mariadb.service.d", "           `-migrated-from-my.cnf-settings.conf", "   Active: \u001b[0;1;31mfailed\u001b[0m (Result: exit-code) since Mon 2018-04-23 08:34:24 UTC; 8ms ago", "     Docs: man:mysqld(8)", "           https://mariadb.com/kb/en/library/systemd/", "  Process: 9403 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION \u001b[0;1;31m(code=exited, status=1/FAILURE)\u001b[0m", "  Process: 9303 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)", "  Process: 9296 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)", "  Process: 9291 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)", " Main PID: 9403 (code=exited, status=1/FAILURE)", "   Status: \"MariaDB server is down\"", "", "Apr 23 08:32:12 publiqly systemd[1]: Starting MariaDB 10.2.13 database server...", "Apr 23 08:32:12 publiqly mysqld[9403]: \u001b[0;1;31m2018-04-23  8:32:12 140550644848832 .....\u001b[0m", "Apr 23 08:33:42 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Start operation timed...g.\u001b[0m", "Apr 23 08:34:24 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Main process exited, ...RE\u001b[0m", "Apr 23 08:34:24 publiqly systemd[1]: \u001b[0;1;31mFailed to start MariaDB 10.2.13 databa...r.\u001b[0m", "Apr 23 08:34:24 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Unit entered failed state.\u001b[0m", "Apr 23 08:34:24 publiqly systemd[1]: \u001b[0;1;39mmariadb.service: Failed with result 'e...'.\u001b[0m", "Hint: Some lines were ellipsized, use -l to show in full.", "dpkg: error processing package mariadb-server-10.2 (--configure):", " subprocess installed post-installation script returned error exit status 1", "dpkg: dependency problems prevent configuration of mariadb-server:", " mariadb-server depends on mariadb-server-10.2 (>= 10.2.13+maria~xenial); however:", "  Package mariadb-server-10.2 is not configured yet.", "", "dpkg: error processing package mariadb-server (--configure):", " dependency problems - leaving unconfigured",

Decided to check what MariaDB stuff I have installed:

apt list --installed  |grep mariadb

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

libmariadb3/now 10.2.12+maria~xenial amd64 [installed,upgradable to: 10.2.14+maria~xenial]
libmariadbclient18/now 10.2.12+maria~xenial amd64 [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-client/now 10.2.13+maria~xenial all [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-client-10.2/now 10.2.13+maria~xenial amd64 [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-client-core-10.2/now 10.2.13+maria~xenial amd64 [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-common/now 10.2.13+maria~xenial all [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-server/now 10.2.13+maria~xenial all [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-server-10.2/now 10.2.13+maria~xenial amd64 [installed,upgradable to: 10.2.14+maria~xenial]
mariadb-server-core-10.2/now 10.2.13+maria~xenial amd64 [installed,upgradable to: 10.2.14+maria~xenial]

..

Installation from Scratch

Most SO threads including on on MariaDB recommends me something like:

1) sudo apt-get remove --purge mariadb-server mariadb-client 
2) sudo apt-get autoremove
3) sudo apt-get autoclean
4) sudo apt-get install mariadb-server mariadb-client

This is similar to what Swalkinshaw stated, but now remove and autoremove are added as well as autoclean.

Dry Run

I decided to do a dry run

sudo apt-get purge mariadb-server* mariadb-client* --dry-run

This is the gist of what was stated:

The following packages were automatically installed and are no longer required:
 galera-3 libaio1 libcgi-fast-perl libcgi-pm-perl libdbd-mysql-perl libdbi-perl libencode-locale-perl libfcgi-perl libhtml-parser-perl
 libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3
 libmariadbclient18 liburi-perl socat
 Use 'sudo apt autoremove' to remove them.
 The following packages will be REMOVED:
 mariadb-client* mariadb-client-10.0* mariadb-client-10.2* mariadb-client-core-10.2* mariadb-server* mariadb-server-10.0* mariadb-server-10.2*
 mariadb-server-core-10.2*
 0 upgraded, 0 newly installed, 8 to remove and 161 not upgraded.
 2 not fully installed or removed.
 Purg mariadb-server [10.2.13+maria~xenial]
 Purg mariadb-server-10.2 [10.2.13+maria~xenial]
 Purg mariadb-client [10.2.13+maria~xenial]
 Purg mariadb-client-10.0
 Purg mariadb-client-10.2 [10.2.13+maria~xenial]
 Purg mariadb-client-core-10.2 [10.2.13+maria~xenial]
 Purg mariadb-server-10.0
 Purg mariadb-server-core-10.2 [10.2.13+maria~xenial]

 

Access MariaDB on Trellis LEMP using Sequel Pro

To access MariaDB on Trellis LEMP using Sequel Pro from you local box is easy once you know how. Like with most things in life really. But the main thing is that you need to know the proper way to access the database once you have set up SSH access properly with Sequel Pro (see this article on more on SSH access via Sequel Pro).

No root Access to Database

The issues is that MariaDB with standard setup does not allow root access unless you are root on the system and then you can login without a password as the standard MariaDB setup uses a plugin to check whether you are root and then automatically grants you access. And we normally log in as a non-root. So now what?

Database User & Password

If you are used to accessing your VPS with the root user for the database you will fail getting access. So instead of adding root and password you should add the database user for the database in question and the password there. That way you can avoid the need root ssh-ing into the box which you normally do not want nor should really want to.

Figuring out the Username

To figure out the username you normally just need the name or your project and whether it is development, production or staging. Normally it is example_com based on your chosen example.com project name.

Or – to be really certain – you ssh into your box, change to root, mysql -u root into the database server and then check which user is for the database.

So  you would do

ssh admin@domain.com
sudo su
mysql -u root
use mysql;

NB vagrant ssh for local access but we are accessing production in this example

Check Existing Users in Database

And then you would check for all users to figure the user for the database:

MariaDB [mysql]> select user,plugin FROM user;

+---------------------+-------------+

| user                | plugin      |

+---------------------+-------------+

| root                | unix_socket |

| root                |             |

| root                |             |

| root                |             |

| sub_domain_com |             |

+---------------------+-------------+

5 rows in set (0.01 sec)

As you can see the root has unix_socket access and the only non root user is sub_domain_com (name changed) . That is the user you should use together with the password you added in your vault.yml. So normally is is domain_com as the user.

Database Access Granted

Once you change the database user and add the correct password you can acces the database and make a backup for example. Or do other manipulations like you would normally do in the database with Sequel Pro.

Trellis and Sequel Pro for OSX

If you are working with Trellis LEMP Stack on a Mac to create beautiful and professional WordPress themes on blazing fast and secure servers you are bound to wind up needing to manipulate the database. Many developers are used to PHPMyAdmin. Well, you should not install it and it has not been installed on Trellis. Install Sequel Pro instead on your Mac instead. With Trellis and Sequel Pro you will manipulate databases with ease.

Sequel Pro

Sequel Pro is a great GUI program that you can use to connect to your local and remote server’s database server. It is very versatile. You can use it to connect to database servers anywhere instead of just locally on one box. And as Trellis is a smart LEMP stack they decided they did not need PHPMyAdmin as they could approach any box using Sequel Pro.

Let’s discuss here how you can make a connection locally.

Trellis and Sequel Pro

As you know Trellis runs on a custom Vagrant box, preferably on VirtualBox. And so you can access the box using ssh vagrant. It will then allow you to access the box with your own ssh key. When you want to connect to the database using SSH tunneling you need to add several details.

Click the ssh tab after you clicked to add another favorite or chose another connection from the menu. Then add the following details. You can leave open the others:

  • mysql host: 127.0.0.1
  • username: root
  • password: password as entered in vault.yml
  • ssh host: 127.0.0.1
  • ssh user: vagrant
  • ssh key: under yoursite.com > .vagrant > machines > default > virtualbox > private_key
  • ssh port: 2222

The port is here not 22, but 2222 and you should locate the key clicking the blue key at the path I indicated above. Other details are not to hard to understand though you may pick the local network ip on your box instead of the localhost 127.0.0.1 . And well, that would not work either as you SSH tunnel your way in in this case.

You should now be able to connect. See here screenshot below:

Sequel Pro

You can do a test or save and connect right away. It will not load the database right away. You will need to choose it from the top left dropdown menu stating choose “Choose Database..” I did test and then saved and then connected. That is the best way really.

And then you will be connected. Trellis and Sequel Pro are working hand it hand as they should be. You can then start adjusting database data, import, export and so on.

Known Hosts Issues

Sometimes your known hosts setup has changed and then you won’t be able to connect. Sequel Pro error message may not be clear in console as you need to scroll to see it. Do scroll. It will tell you if that is the case or not. Make sure to edit the known hosts file and to remove the offending line using nano or another editing tool. For OSX users:

nano /Users/your-user/.ssh/known_hosts

Once opened remove the line and then try again.

NBB For Windows users I refer to MySQL Workbench