Database install instructions

Continuing the discussion from Install dependencies:

Thought I’d break this bit out :smile:

There are at least 2 different instructions for setting up the SQL database. I’m sure this is a hangover from the mySQL / MariaDB switch, but it would be nice to rationalise this as well in the various installation instructions.

Where? Can you provide links please.


I’ve not looked further TBH. Not sure why the 2 links appear differently either.

Originally they were all using mysql-server mysql-client packages, then when Stretch came out and the default changed to mariadb, Paul updated the Stretch guide to mariadb-server and mariadb-client, it was later found there were default-mysql-server and default-mysql-client “dummy” packages designed to allow the package manager to decide the correct config, so we after discussion we opted for that.

However… I now believe that last decision is not so hot.

The other night I was testing my emoncms installer and it failed to find default-mysql-server and default-mysql-client “dummy” packages for Ubuntu 16.04 (must be just a Debian/Raspbian thing) and when I reverted to just mysql-server mysql-client, it installed percona-server-server-5.6. I had to specifically define mariadb-server and mariadb-client on the next attempt to get mariadb.

So I now think we should specify mariadb-server and mariadb-client rather than allow any ambiguity.

The appeal of the “dummy” package was that we didn’t need to define the db when installing to Jessie or Stretch, it would decide the correct variants, but this is a moot point as we cannot use the same guide/script for Jessie and Stretch as they also use different php versions that cannot be auto-selected by the package manager. Besides at this point in time, it would be better to have a guide/script that works across all current distro’s rather than being able to determine between a current and older version of a single distro.

So I recommend using mariadb-server and mariadb-client for Rasbian Stretch, Debian 9 and Ubuntu 16.04 and moving forward. hopefully this approach will work for other distro’s too, but for what it’s worth once we get the php7/mariadb guide/scrpt sussed it would be easy to duplicate it and then dial back the necisary bits for Raspbian Jessie, Debian 8 and Ubuntu 14.04 users if there is a demand for it.

Once the packages are sorted there were some minor changes to the way mariadb works over mysql and that is all documented in the Raspbian Stretch thread. The most prominent thing being that you do not need to set up or use a root password with mariadb. This is a huge improvement to both security and to ease of use (and unattended installer scripts). If we stick to defining mariadb then we can align the guides. mysql and percona both insist you set up a root password so the guides cannot be the same if the db’s differ.

On closer inspection it seems we have not made a full transition to mariadb. We have “mariadb-server mysql-client” just to add a bit more variety to the mix :grin:

Also I have discovered it is not required and even frowned upon to “flush privileges” after using “grant” as grant already rebuilds the permission tables. “flush privileges” is used after manually creating, deleting and editing user without using “grant”.

Plus when you “grant priviledges” to a user it will create the user if it doesn’t already exist. So the existing command of 3 lines

sudo mysql -e "CREATE USER 'emoncms'@'localhost' IDENTIFIED BY 'new_secure_password'; GRANT ALL ON emoncms.* TO 'emoncms'@'localhost'; flush privileges;"

can be shortened to a single command line

sudo mysql -e "GRANT ALL ON emoncms.* TO 'emoncms'@'localhost' IDENTIFIED BY 'new_secure_password';"

I have been doing it this way the last couple of weeks without any issues.

1 Like

First time I tried the shorter command, emoncms failed to authenticate for some reason.

Second time, I restarted the mysql service just to sure and it worked no problems.

I’m doing this in a VM so it is really easy to take a known good snapshot and go back to it if needed :smile:

Thanks for this Paul.

Immediately after after running the command type

echo $?

to see the exit code of the last command, if it is “0” it was successful and the issue lays somewhere else, an errors will result in a “1” or higher.

1 Like