Trellis NET::ERR_CERT_REVOKED

Since my new MacOS Catalina setup I bumped into multiple Trellis issues. Issues with loading the proper interpretor as well as loading the proper Ansible version for the setup. Final issue I had now was dealing with failed SSL certificates and to be more specific NET::ERR_CERT_REVOKED .

NET::ERR_CERT_REVOKED

So how to deal with this NET::ERR_CERT_REVOKED error? Let’s first look at the error in more detail. We have the following:

Your connection is not private
Attackers might be trying to steal your information from domain.test (for example, passwords, messages, or credit cards). Learn more
NET::ERR_CERT_REVOKED
Subject: domain.test
Issuer: domain.test
Expires on: Nov 14, 2029
Current date: Nov 17, 2019
PEM encoded chain:-----BEGIN CERTIFICATE-----
MIIC7TCCAdWgAwIBAgIUYZ8d88npaf4YadiK/tt5dmovK5MwDQYJKoZIhvcNAQEL
BQAwGDEWMBQGA1UEAwwNYnJpY2ttYWcudGVzdDAeFw0xOTExMTcwNzU2NTBaFw0y
OTExMTQwNzU2NTBaMBgxFjAUBgNVBAMMDWJyaWNrbWFnLnRlc3QwggEiMA0GCSqG
------------------
LI3sU31VNnyMaq31seU+FdZjBO7LS0n4u1Rv5FKhsGC6goozx0LhVsHG659HJbMn
i0Yd3C1rl+DJClQS9LVCNzkXVrvJtwLI8LLraUmLJYWTC5cfGuL7C/J0dzqj48uG
6Vzouywoceuy5aVXvgV3cxOi9vkC2e8idkd28JvHEQ4s
-----END CERTIFICATE-----

So somehow the certificate is being revoked . This either has to do with Catalina or Chrome being stricter on local self signed certificates with locally generated authorities. So what to do about this?

Trellis Cert Trust Vagrant Plugin

Well after some Googling I bumped into this Github thread on the same SSL certificate issue. And I found out that there is a Vagrant plugin that can deal with the browser no longer accepting locally set certificates as it did before. Simply install it and run it inside the trellis directory:

vagrant plugin install vagrant-trellis-cert
vagrant trellis-cert trust

And yes, now you can run the site again with the generated SSL certificate and now you are no longer blocked by the browser!

Trellis ‘PlaybookCLI’ object has no attribute ‘options’

With latest Ansible we had an issue starting up / provisioning vagrant properly. It would not provision properly stating there was an attribute called options missing.

Attribute Options Errors

Got the following error doing a

ansible reload --provision
ERROR! Unexpected Exception, this is probably a bug: 'PlaybookCLI' object has no attribute 'options'

Suggestion at Roots Discourse was to downgrade Ansible to < 2.8 using

sudo pip uninstall ansible 
sudo pip install ansible==2.7

And although that is again not an ideal solution it is the solution if you do not want to upgrade Trellis and or work with Python 3 as discussed at this blog post.

Trellis Ansible Bad Interpreter Error

Recently upgraded to MacOS Catalina and zsh shell. Since the upgrade it seems we have Python 3 and no longer 2.x so to work with older Trellis setups using Python 2.7 you need a fix

Bad Interpreter Error

Error we got using Ansible was a bad interpreter error. Python 2.7 is not to be found:

zsh: /usr/local/bin/ansible-vault: bad interpreter: /usr/local/opt/python@2/bin/python2.7: no such file or directory

And that was correct because when we checked /usr/local/opt we only had Python 3.

Brew Installation Python 2

So we did a

brew install python@2

to install Python 2 as an option besides the natively installed Python 3. This took quite some time as we had not updated Brew in a long time and as compilation was done from source

➜  trellis git:(master) brew install python@2
 Updating Homebrew…
 ==> Auto-updated Homebrew!
 Updated 4 taps (heroku/brew, homebrew/core, homebrew/cask and homebrew/services).
 ==> New Formulae
 adios2                     git-delta                  mpv                        pnpm
 alp                        gleam                      mysql-connector-c++@1.1    prestosql
 appium                     gmt@5                      navi                       pylint
 arduino-cli                govc                       nbdime                     tdkjs
 atasm                      grin                       ngt                        tektoncd-cli
 awsume                     grin-wallet                node@12                    toast
 bingrep                    helm@2                     notifiers                  trader
 calceph                    javacc                     numpy@1.16                 ttyplot
 cf-tool                    jd                         oauth2l                    tweak
 comby                      kyma-cli                   onefetch                   virustotal-cli
 cups                       libtensorflow@1            openjdk                    wagyu
 dafny                      manticoresearch            openjdk@11                 wal2json
 diffr                      minikube                   openjdk@12                 xgboost
 dvc                        mpi4py                     pnetcdf
 ==> Updated Formulae
 aspell ✔                            gitless                             pdf2htmlex
 cmake ✔                             gitmoji                             pdf2json
 curl ✔                              gitup                               pdfcpu
 curl-openssl ✔                      gitversion                          pdftk-java
 git ✔                               gjs                                 pdftoipe
 glib ✔                              glade                               percona-server
 gnutls ✔                            glances                             percona-xtrabackup
 heroku/brew/heroku ✔                glib-networking                     perltidy
 heroku/brew/heroku-node ✔           glooctl                             petsc
 iproute2mac ✔                       gmic                                petsc-complex
 kubernetes-cli ✔                    gmime                               pgbadger
 libtiff ✔                           gmt                                 pgcli
 libxml2 ✔                           gnome-builder                       pgformatter
 mariadb ✔                           gnome-latex                         pgrouting
 node ✔                              gnome-recipes                       pgweb
 node@10 ✔                           gnumeric                            phoronix-test-suite
 p11-kit ✔                           gnunet                              php-code-sniffer
 php ✔                               gnuradio                            php-cs-fixer
 python ✔                            go                                  php@7.1
 redis ✔                             go-bindata                          php@7.2
 sqlite ✔                            go@1.12                             phpmyadmin
 terraform ✔                         gobby                               phpstan
 tor ✔                               gocryptfs                           phpunit
 yarn ✔                              godep                               picard-tools
 abcm2ps                             goffice                             pioneer
 ........
 gifski                              osm-gps-map                         zbar
 ginac                               osmium-tool                         zint
 git-annex                           osqp                                zita-convolver
 git-archive-all                     osquery                             znc
 git-cola                            ott                                 zola
 git-quick-stats                     oxipng                              zookeeper
 git-revise                          packer                              zrepl
 git-town                            pagmo                               zsh-completions
 gitfs                               paket                               zsh-history-substring-search
 gitg                                pango                               zshdb
 gitlab-gem                          parallel                            zydis
 gitlab-runner                       pastel
 gitleaks                            pdal
 ==> Renamed Formulae
 jupyter -> jupyterlab      kubernetes-helm -> helm    presto -> prestodb         usbmuxd -> libusbmuxd
 ==> Deleted Formulae
 aiccu             dcal              gmtl              mariadb@10.0      pound             riak
 bdsup2sub         erlang@17         hana              mysql@5.5         protobuf@3.1      supersonic
 cockroach         gmt@4             llvm@4            pbrt              raine             wine
 ==> minikube has been moved to Homebrew.
 To uninstall the cask run:
   brew cask uninstall --force minikube
 ==> Installing minikube…
 ==> Installing dependencies for minikube: kubernetes-cli
 ==> Installing minikube dependency: kubernetes-cli
 ==> Downloading https://homebrew.bintray.com/bottles/kubernetes-cli-1.16.3.catalina.bottle.tar.gz
 ==> Downloading from https://akamai.bintray.com/0f/0ffd9ee9bb5026ae526a09fe50591a94a1a987f52c5866e32aefb79b
 ################################################################## 100.0%
 ==> Pouring kubernetes-cli-1.16.3.catalina.bottle.tar.gz
 ==> Caveats
 Bash completion has been installed to:
   /usr/local/etc/bash_completion.d
 zsh completions have been installed to:
   /usr/local/share/zsh/site-functions
 ==> Summary
 🍺  /usr/local/Cellar/kubernetes-cli/1.16.3: 232 files, 52.3MB
 ==> Installing minikube
 ==> Downloading https://homebrew.bintray.com/bottles/minikube-1.5.2.catalina.bottle.tar.gz
 ==> Downloading from https://akamai.bintray.com/6c/6cab6126b65a45912587339eddf252729e4ad16b4bfd327454204b46
 ################################################################## 100.0%
 ==> Pouring minikube-1.5.2.catalina.bottle.tar.gz
 ==> minikube cask is installed, skipping link.
 ==> Caveats
 Bash completion has been installed to:
   /usr/local/etc/bash_completion.d
 zsh completions have been installed to:
   /usr/local/share/zsh/site-functions
 ==> Summary
 🍺  /usr/local/Cellar/minikube/1.5.2: 8 files, 51.5MB
 ==> brew cleanup has not been run in 30 days, running now…
 Removing: /Users/jasper/Library/Caches/Homebrew/aspell--0.60.7.mojave.bottle.tar.gz… (115.6MB)
 Removing: /Users/jasper/Library/Caches/Homebrew/curl-openssl--7.66.0.mojave.bottle.tar.gz… (1MB)
 Removing: /Users/jasper/Library/Caches/Homebrew/glib--2.62.0_1.mojave.bottle.tar.gz… (4.5MB)
 Removing: /usr/local/Cellar/kubernetes-cli/1.10.1… (178 files, 52.8MB)
 Removing: /Users/jasper/Library/Caches/Homebrew/php--7.3.10.mojave.bottle.tar.gz… (19.9MB)
 Removing: /Users/jasper/Library/Caches/Homebrew/python--3.7.4_1.mojave.bottle.tar.gz… (14.6MB)
 Removing: /Users/jasper/Library/Caches/Homebrew/python--3.7.4.mojave.bottle.tar.gz… (14.6MB)
 Removing: /Users/jasper/Library/Caches/Homebrew/sqlite--3.29.0.mojave.bottle.tar.gz… (1.9MB)
 Removing: /Users/jasper/Library/Logs/Homebrew/nghttp2… (64B)
 ......
 Removing: /Users/jasper/Library/Logs/Homebrew/openssl@1.1… (64B)
 Removing: /Users/jasper/Library/Logs/Homebrew/openldap… (64B)
 Removing: /Users/jasper/Library/Logs/Homebrew/libev… (64B)
 Pruned 17 symbolic links and 1 directories from /usr/local
 ==> Caveats
 ==> kubernetes-cli
 Bash completion has been installed to:
   /usr/local/etc/bash_completion.d
 zsh completions have been installed to:
   /usr/local/share/zsh/site-functions
 ==> minikube
 Bash completion has been installed to:
   /usr/local/etc/bash_completion.d
 zsh completions have been installed to:
   /usr/local/share/zsh/site-functions
 Linking /usr/local/Cellar/minikube/1.5.2… 3 symlinks created
 ==> Installing dependencies for python@2: sqlite
 ==> Installing python@2 dependency: sqlite
 ==> Downloading https://homebrew.bintray.com/bottles/sqlite-3.30.1.catalina.bottle.tar.gz
 ==> Downloading from https://akamai.bintray.com/38/38c39121f7634ec563bb201b483f66cf567dfe61e02624ffb06f620f
 ################################################################## 100.0%
 ==> Pouring sqlite-3.30.1.catalina.bottle.tar.gz
 ==> Caveats
 sqlite is keg-only, which means it was not symlinked into /usr/local,
 because macOS provides an older sqlite3.
 If you need to have sqlite first in your PATH run:
   echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.zshrc
 For compilers to find sqlite you may need to set:
   export LDFLAGS="-L/usr/local/opt/sqlite/lib"
   export CPPFLAGS="-I/usr/local/opt/sqlite/include"
 For pkg-config to find sqlite you may need to set:
   export PKG_CONFIG_PATH="/usr/local/opt/sqlite/lib/pkgconfig"
 ==> Summary
 🍺  /usr/local/Cellar/sqlite/3.30.1: 11 files, 3.9MB
 ==> Installing python@2
 Warning: Building python@2 from source:
   The bottle needs the Apple Command Line Tools to be installed.
   You can install them, if desired, with:
     xcode-select --install
 ==> Downloading https://www.python.org/ftp/python/2.7.17/Python-2.7.17.tar.xz
 ################################################################## 100.0%
 ==> ./configure --prefix=/usr/local/Cellar/python@2/2.7.17 --enable-ipv6 --datarootdir=/usr/local/Cellar/py
 ==> make
==> make install PYTHONAPPSDIR=/usr/local/Cellar/python@2/2.7.17
 ==> make frameworkinstallextras PYTHONAPPSDIR=/usr/local/Cellar/python@2/2.7.17/share/python@2
 ==> Downloading https://files.pythonhosted.org/packages/f4/d5/a6c19dcbcbc267aca376558797f036d9bcdff344c9f78
 ################################################################## 100.0%
 ==> Downloading https://files.pythonhosted.org/packages/ce/ea/9b445176a65ae4ba22dce1d93e4b5fe182f953df71a14
 ################################################################## 100.0%
 ==> Downloading https://files.pythonhosted.org/packages/59/b0/11710a598e1e148fb7cbf9220fd2a0b82c98e94efbdec
 ################################################################## 100.0%
 ==> /usr/local/Cellar/python@2/2.7.17/bin/python -s setup.py --no-user-cfg install --force --verbose --sing
 ==> /usr/local/Cellar/python@2/2.7.17/bin/python -s setup.py --no-user-cfg install --force --verbose --sing
 ==> /usr/local/Cellar/python@2/2.7.17/bin/python -s setup.py --no-user-cfg install --force --verbose --sing
 ==> Caveats
 Pip and setuptools have been installed. To update them
   pip install --upgrade pip setuptools
 You can install Python packages with
   pip install 
 They will install into the site-package directory
   /usr/local/lib/python2.7/site-packages
 See: https://docs.brew.sh/Homebrew-and-Python
 ==> Summary
 🍺  /usr/local/Cellar/python@2/2.7.17: 6,645 files, 91.4MB, built in 4 minutes 16 seconds
 ==> Caveats
 ==> sqlite
 sqlite is keg-only, which means it was not symlinked into /usr/local,
 because macOS provides an older sqlite3.
 If you need to have sqlite first in your PATH run:
   echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.zshrc
 For compilers to find sqlite you may need to set:
   export LDFLAGS="-L/usr/local/opt/sqlite/lib"
   export CPPFLAGS="-I/usr/local/opt/sqlite/include"
 For pkg-config to find sqlite you may need to set:
   export PKG_CONFIG_PATH="/usr/local/opt/sqlite/lib/pkgconfig"
 ==> python@2
 Pip and setuptools have been installed. To update them
   pip install --upgrade pip setuptools
 You can install Python packages with
   pip install 
 They will install into the site-package directory
   /usr/local/lib/python2.7/site-packages
 See: https://docs.brew.sh/Homebrew-and-Python

Python Crashing Hard

Next, on Ansible version check I got another error

➜  trellis git:(master) ansible --version    
[1]    19153 abort      ansible --version

It was somehow crashing Python 2.7 though it should just work with it. I decided to upgrade Ansible as well

➜  ~ sudo pip install ansible --upgrade
.....
Requirement already satisfied, skipping upgrade: six>=1.4.1 in /usr/local/lib/python2.7/site-packages (from cryptography->ansible) (1.11.0)
 Requirement already satisfied, skipping upgrade: pycparser in /usr/local/lib/python2.7/site-packages (from cffi>=1.7; platform_python_implementation != "PyPy"->cryptography->ansible) (2.18)
 Installing collected packages: ansible
   Found existing installation: ansible 2.7.5
     Uninstalling ansible-2.7.5:
       Successfully uninstalled ansible-2.7.5
 Successfully installed ansible-2.9.1

Still I had the Python error and iTerm was showing a MacOS popup that Python was crashing unexpectedly:

Python quit unexpectedly.
Click Reopen to open the application again. Click Report to see more detailed information and send a report to Apple.
Application Specific Information:
 /usr/lib/libcrypto.dylib
 abort() called
 Invalid dylib load. Clients should not load the unversioned libcrypto dylib as it does not have a stable ABI.

Invalid DyLib

Found https://stackoverflow.com/questions/58272830/python-crashing-on-macos-10-15-beta-19a582a-with-usr-lib-libcrypto-dylib on the error with the Dynamic library loaded being the wrong one and decided to install openssl

brew install openssl

But it was already installed so no need for that. So then I added this line to .zshrc to load the correct library inside zsh:

# Python crash fix
export DYLD_LIBRARY_PATH=/usr/local/opt/openssl/lib:$DYLD_LIBRARY_PATH

All Working

And then restarted iTerm and once I had done that I was in the clear

➜  ~ ansible --version
ansible 2.9.1
config file = None
configured module search path = [u'/Users/jasper/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/local/lib/python2.7/site-packages/ansible
executable location = /usr/local/bin/ansible
python version = 2.7.17 (default, Nov 17 2019, 10:31:11) [GCC 4.2.1 Compatible Apple LLVM 11.0.0 (clang-1100.0.33.12)]

I was also able to view the encrypted content again using

ansible-vault view group_vars/all/vault.yml

Will definitely upgrade the Trellis package soon so I can work with Python 3 as next year Python 2 will be completely abandoned. But for now I can work with this setup.

Trellis DO SSL Upgrade

To do a Trellis DO SSL Upgrade to have your Digital Ocean Droplet run Trellis with all secure https SSL urls several steps will need to be taken. Let me take you through them.

Backup Digital Ocean Droplet

Snapshot of server as a backup via Digital Ocean Control Panel. This will take 10-20 minutes on a running server. Probably quicker when the server is shutdown, but we often don’t want that to happen.

Database Backup

Database backup using Sequel Pro or MySQL Workbench. Just so we have not just a server backup, but a database backup as well. In case of a multisite that could mean backing up over 250 tables like in the case we use for the migration to SSL for this blog post.

Update Trellis to Latest

Make sure you upgrade Trellis to latest while you are at it. Sometimes your Trellis is too outdated and then the Let’s Encrypt SSL will work with old background checks and fail to work properly. The post I just linked to goes through all the steps. Basically you need to add an upstream new repo which is the Trellis Repo, fetch it and then merge it with your own repo. Once that is done you can do a
ansible-playbook server.yml -e env=environment

Make sure you get the all green like:

PLAY RECAP ********************************************************************************************************
xxx.xxx.xxx.xx             : ok=95  changed=6    unreachable=0    failed=0   
localhost                  : ok=0    changed=0    unreachable=0    failed=0   

Activate SSL and Provision SSL

To activate SSL and have Let’s Encrypt SSL work on your server change /Users/jasper/webdesign/uniquehotelspa.com/uniquehotelspa-com/trellis/group_vars/production/wordpress_sites.yml and make it

ssl:
enabled: true
provider: letsencrypt
Once that is done and pushed to Github you can run a
ansible-playbook server.yml -e env= --tags letsencrypt

Database Search and Replace

For database search and replace to replace or no secure http urls with https SSL urls we ssh into the Trellis box and do a search and replace with WordPress CLI. We talked about it for changing a .dev url into a .com url before, but now we will change the protocol to https.
We will start with this test command:
wp search-replace 'http://domain.com' 'https://domain.com' --skip-columns=guid --dry-run
This shows the changes that will be made needed to have all links with https. Once you are satisfied with changes to be made you can skip –dry-run and actually execute the command and make the database changes:
wp search-replace 'http://domain.com' 'https://domain.com' --skip-columns=guid
 Once this is done all non secure links should be secure now.

Multi Site

If you are using multisite please use:

wp search-replace --network http://domain.com https://domain.com --skip-columns=guid --dry-run

Mixed Content Check

Although all should be well by now do check for mixed content. There can always be some elements that somehow bypassed the changes made. Mixed content means that you for example still have fonts loaded over http. Anything still loaded over http will cause the green lock not to appear in your browser address bar. Use the Chrome inspector to find the issue in question and address it appropriately.

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.

WordPress Multisite Database Migration

Recently we did a WordPress Multisite Database Migration on Trellis. All is pretty straightforward files wise though we did have to do some extra media work using a volume. The database on the other hand was a bit more problematic.

WP CLI Database Import & URL Replacement

Using wp-cli and aliases for the import and replacement:

# sync-db-prod-to-staging.sh
# chmod +x sync-db-prod-to-staging.sh
read -r -p "Do you solemnly swear that you have had fewer than 2 alcoholic beverages in the last hour and that you would really like to reset your staging database and pull the latest from production? [y/N] " response
if [[ $response =~ ^([yY][eE][sS]|[yY])$ ]]; then
wp @staging db reset --yes &&
wp @production db export -> sql-dump-production.sql &&
wp @staging db import sql-dump-production.sql &&
wp @staging search-replace --network --url=domain.com http://domain.com https://staging.domain.com --skip-columns=guid
fi

things didn’t work out. The database was not imported properly. It was a large database so we decided to just import it.

WP CLI Search & Replace

For the replacing of the urls we tried to use WP CLI however. This as this did work locally on a new Vagrant setup. So we did the following command:

wp search-replace --network --url=domain.com http://domain.com https://staging.domain.com --skip-columns=guid

we got the following error:

2018/04/22 08:04:11 [error] 13636#13636: *92588 FastCGI sent in stderr: "PHP message: WordPress database error Table 'staging_domain_com_staging.wp_blogs' doesn't exist for query SELECT  blog_id FROM wphsm_blogs  WHERE domain IN ( 'staging.domain.com' ) AND path IN ( '/de/', '/' )  ORDER BY CHAR_LENGTH(path) DESC LIMIT 1 made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('/srv/www/staging.domain.com/releases/20180418081603/web/wp-config.php'), require_once('wp-settings.php'), require('wp-includes/ms-settings.php'), ms_load_current_site_and_network, get_site_by_path, get_sites, WP_Site_Query->query, WP_Site_Query->get_sites, WP_Site_Query->get_site_ids" while reading response header from upstream, client: xx.xxx.xx.xx, server: staging.domain.com, request: "GET /de/europe/country/province/region/flaine/feed HTTP/1.1", upstream: "fastcgi://unix:/var/run/php-fpm-wordpress.sock:", host: "staging.domain.com"

NB Related Trellis issue

Oddly enough the table was there. In another Github issue https://github.com/Chassis/Chassis/issues/324 I read we should use wp core multisite-convert .Or at least that helped him, but we were just cloning the multisite database from production to staging here so why should I use that?

Manual Multisite unique table changes

We did some more research and bumped into a great article by Eric Holmes. So we did some manual database changes in:

  • wp_blogs – this holds record of all your WPMU blogs. Alter the domain field(s).
  • wp_options – here do the normal alterations from home_url and site_url, as if you were transferring a regular WordPress website. If you have multiple blogs, you will need to go into wp_2_options, wp-3_options, etc.
  • wp_site – This defines the multisite domain and path. Change the domain here.
  • wp_sitemeta – Search for the meta key siteurl – change the domain.
  • wp_usermeta – Search for the meta key source_domain – change the domain.

Items listed as recommended by Eric and also partly recommended in the Codex .

Media URLS

Now the staging site seemed to be loading again with latest database from the production site. Only the image paths needed updating so we did a wp search replace using:

wp search-replace http://domain.com https://staging.domain.com --skip-columns=guid

This fixed all further mixed content issues and allowed the site to be loaded properly. WordPress Multisite Database Migration complete!

Update Trellis like a Pro

professional

Here a quick blog post how to update Trellis like a pro.

Requirements

Here are some requirements or basically tips before you get started

  • Trellis should be in its own repo not together with Bedrock/Site
  • Always make sure you’re working in a Git repo
  • Make sure all your work is committed
  • Use a great IDE like Visual Code to deal with merge conlficts

Trellis As Upstream Remote

Add Trellis as a remote

git remote add upstream git@github.com:roots/trellis.git

The pull the upstream changes using

 git fetch upstream

You will then see something like

git fetch upstream
remote: Counting objects: 40, done.
remote: Compressing objects: 100% (6/6), done.
remote: Total 40 (delta 24), reused 29 (delta 24), pack-reused 9
Unpacking objects: 100% (40/40), done.
From github.com:roots/trellis
   c8515b3..14cce04  master                -> upstream/master
 * [new branch]      memcached-disable-udp -> upstream/memcached-disable-udp

Once that is done rebase upstream changes into your master repository using

 git rebase upstream/master

Git Merge Conflicts

Manually fix all conflicts. I recommend Visual Code as it visually helps you to deal with it all as you see in the image below. Also read: https://help.github.com/articles/resolving-merge-conflicts-after-a-git-rebase/46 and http://tedfelix.com/software/git-conflict-resolution.html26

Visual Code Git Merge Conflicts

In Visual Studio Code you simply need to accept current change (added by upstream) or keep incoming change. And the latter you have to do for most if not all group_vars files.

Rebase Continued

Once you have fixed all conflicts you can do a

git add .
git rebase --continue

Merge Conflicts Leftovers

When I continued the rebasing I got a few more merge conflicts:

Falling back to patching base and 3-way merge...
Auto-merging group_vars/development/vault.yml
CONFLICT (content): Merge conflict in group_vars/development/vault.yml
Auto-merging Vagrantfile
Auto-merging README.md
Auto-merging CHANGELOG.md
CONFLICT (content): Merge conflict in CHANGELOG.md
error: Failed to merge in the changes.
Patch failed at 0002 first commit
The copy of the patch that failed is found in: .git/rebase-apply/patch

When you have resolved this problem, run "git rebase --continue".
If you prefer to skip this patch, run "git rebase --skip" instead.
To check out the original branch and stop rebasing, run "git rebase --abort".

So I fixed the files and overwrote changelog, README and Vagrantfile with the latest from the repo at Github. Then I rebased again and… all good!

NB This blog post is based on Swalkinshaw’s post at Roots Discourse on updating Sage

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]

 

WooCommerce Checkout Sendgrid Issue

We had a WooCommerce Checkout Sendgrid Issue on one of our Trellis servers. Payments did work, but no feedback was sent to client or very late. No confirmation of successful sale was given. This is very inconvenient obviously so we checked out what was the issue and solution. We soon found out WooCommerce and Sendgrid were not plating nice. Here below the whole discovery process.

Upstream Timed Out

The error we had was:

2018/01/25 08:27:10 [error] 16241#16241: *42582 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 213.165.184.36, server: domain.com, request: "POST /?wc-ajax=checkout HTTP/2.0", upstream: "fastcgi://unix:/var/run/php-fpm-wordpress.sock", host: "domain.com", referrer: "https://domain.com/checkout/"

Port 110 is the post office protocol port and the ip address 213.165.184.36 an address of a Malta Cable company. Not much to go on early other than that there seems to be a time-out issue and that Nginx could perhaps use some more Ks for its buffer.

Nginx Buffering

So we decided to up the Nginx buffer using:

nginx_fastcgi_buffers: 16 16k
 nginx_fastcgi_buffer_size: 32k

inside group/vars/production/main.yml. This I added and re-provisioned our Trellis server.

Sendgrid

The other thing we wondered about if Sendgrid was having issues sending out details after a successful Stripe payment had been made. We were after all using it for outgoing emails using:

Documentation: https://roots.io/trellis/docs/mail/
mail_smtp_server: smtp.sendgrid.net:587
mail_admin: admin@publiqly.com
mail_hostname: publiqly.com
mail_user: publiqly
mail_password: "{{ vault_mail_password }}" # Define this variable in group_vars/all/vault.yml

When we checked Sendgrid we hardly saw any traffic. Something to worry about.

WP Mail Logging & sSMTP Logging

So we decided to install WP Mail Logging to facilitate the checking of all outgoing email. We also activated sSMTP mail logging. This you can do by setting

Debug=Yes

in ssmtp.conf and then check syslog for any errors.

Mail Logs

And then I thought about the standard mail logs. And when I checked at /var/log/mail.err I found:

Jan 25 08:29:28 domain sSMTP[16416]: Cannot open smtp.sendgrid.net:587
Jan 25 08:33:42 domain sSMTP[16424]: Unable to connect to "smtp.sendgrid.net" port 587.
Jan 25 08:33:42 domain sSMTP[16424]: Cannot open smtp.sendgrid.net:587
Jan 25 09:07:42 domain sSMTP[16603]: Unable to connect to "smtp.sendgrid.net" port 587.
Jan 25 09:07:42 domain sSMTP[16603]: Cannot open smtp.sendgrid.net:587

Well there you go. It seems the connection cannot be made properly. I contacted Sendgrid one this.

Port 587

Found out port 587 like most ports aren’t open on Trellis. This by doing a:

# netstat -ntlp | grep LISTEN
tcp        0      0 0.0.0.0:443             0.0.0.0:*              LISTEN      1500/nginx -g daemo
tcp        0      0 127.0.0.1:11211      0.0.0.0:*              LISTEN      1343/memcached  
tcp        0      0 0.0.0.0:80              0.0.0.0:*              LISTEN      1500/nginx -g daemo
tcp        0      0 0.0.0.0:22              0.0.0.0:*              LISTEN      23307/sshd      
tcp6      0      0 :::443                    :::*                     LISTEN      1500/nginx -g daemo
tcp6      0      0 :::3306                  :::*                     LISTEN      1618/mysqld     
tcp6      0      0 :::80                     :::*                     LISTEN      1500/nginx -g daemo

So based on a Roots forum search I added:

- type: dport_accept
dport: [587]
protocol: tcp
- type: dport_accept
dport: [587]
protocol: udp

to group_vars/all/security.yaml. Then I re-provisioned those playbooks:

ansible-playbook server.yml --tags "ferm,ssmtp, mail" -e env=production

Ports not the Issue

Then based on the Roots Discourse thread I had running I realized we were talking outgoing port. It is not incoming traffic that is the issue. And that the issue was more with Sendgrid or the way Sendgrid dealt with the incoming requests. SSH and https/http ports are listening for incoming requests. I was recommended to do a telnet test do debug and to use Sendgrid api keys to make the connection work better. So I removed the new port rules. Then I implemented the recommendations.

Telnet check

To do a telnet test you have to get a key and convert it to the appropriate version to do a test with it using telnet securely. So I went to Sendgrid, generated an api key with full access minus billing. Then I converted it to base64 with openssl from the command line using:

echo '<<YOUR_API_KEY>>' | openssl base64

I stored the api key and converted key in KeepassX for later use. When I just ran

telnet smtp.sendgrid.net 587

from the Trellis server in question I got:

telnet smtp.sendgrid.net 587
Trying 108.168.183.160...
telnet: Unable to connect to remote host: Connection timed out

Well, and that was the error we had in the logs basically.

DO Ipv6 mail issues?

Then I read Digital Ocean’s port setup. So it seemed it was an ipv6 Digital Ocean port issue. So based on this DO question I edited gai.conf:

nano /etc/gai.conf

and made the appropriate lines look like this:

precedence ::ffff:0:0/96 100

where 10 becomes 100 and the whole line is uncommented. This to run via ipv4. Well, it did not help.

Sendgrid API Plugin

So I installed the Sendgrid API plugin. Adding details in Safari got the Sendgrid settings page reloading like crazy . In Chrome things did work fine as well as a test email using the plugins settings page for this.

Final Test with Sendgrid API

So final test that needed to be done was a new (test) purchase and see if Sendgrid was working and no longer blocking the whole checkout process. I did and the payment worked, a on page and by email confirmation were done right away. And that is amazing news. Sendgrid API all the way!

NB Did have one JS error in the console stil:

TypeError: undefined is not an object (evaluating '$(".woocommerce-billing-fields__field-wrapper").position().left')

but that may be caused by other plugins used on the page and did not seem to interfere. So that can be debugged in time.

Updating Trellis – WordPress LEMP

Updating Trellis can be a challenge initially and there is no one way to do it. Lots of people wrote about it at Roots Discourse and on Github. Most of them require some major git foo. Did write about updating the Trellis server before, but not on how to maintain Trellis itself. Here is my- manual – take on it.

Trellis Repository Update

I first rename the current Trellis folder to trellis-old and git clone the latest Roots Trellis version:

  • mv trellis trellis-old
  • git clone –depth=1 git@github.com:roots/trellis.git && rm -rf trellis/.git

That way I can keep the old copy and have the latest so I can copy over changes I need. I also put trellis-old on the .gitignore list with some other directories and files:

.DS_Store
database-backups/
trellis-old/
miscelaneous/

Trellis files to be updated

Then I make all the changes to files in the following directories:

  • group_vars/all
  • group_vars/production
  • group_vars/staging
  • hosts

I skipped group_vars/development as there hardly ever is a need for me there. Don’t do tweaks in development really as Trellis handles this pretty well out of the box with Vagrant.

Common Variables

The group all with common variables alone has:

  • mail.yml,
  • main.yml,
  • vault.yml,
  • users.yml

to updateMail.yml has the mail details so your Trellis server can send out email. Something like:

# Documentation: https://roots.io/trellis/docs/mail/
mail_smtp_server: smtp.sendgrid.net:587
mail_admin: admin@domain.com
mail_hostname: domain.com
mail_user: user
mail_password: "{{ vault_mail_password }}" # Define this variable in group_vars/all/vault.yml

when you are using Sendgrid.

Main has the main vars including some of your own custom ones.I made sure all customizations to PHP settings are added to group_vars/all/main.yml:

php_max_execution_time: 300
php_max_input_vars: 1000
php_memory_limit: 256M
php_post_max_size: 128M

In vault.yml the vault mail password is stored. That is needed for sending out email which is mainly set up in mail.yml. Under users.yml you add the server users and the keys used for which we normally use our own Github ones:

https://github.com/jasperf.keys

Though users.yml is not hard to set up and admin for admin_user is correct most of the time you do need to make sure all is well and no changes were made.

Staging and Production

Then staging and development have two files each that need updating:

  • vault.yml
  • wordpress_sites.yml

These files do not change much in Trellis, but they contain major details on your WordPress setup so do need to be updated with your customizations properly.

NB Did add php_memory_limit: 512M to production and staging, but I guess that could be moved to group_vars/all as well. Still two files each there. So nine files in total.

Hosts

Host files for staging and production need their ips updated so they have the ones you added before. This is pretty easy to do and as these files hardly every change you can overwrite them. Example staging hosting file:

# Add each host to the [staging] group and to a "type" group such as [web] or [db].
# List each machine only once per [group], even if it will host multiple sites.
[staging]
 xxx.xxx.x.xxx
[web]
 xxx.xxx.x.xxx