[mythtv-users] BE3 - Issues with installation and database

[Stephen Worthington]

On Sun, 19 May 2019 15:34:12 -0500, you wrote:

Sorry, the "show database" problem was mine - I left the ; off the end
of the command.  The "mysql", "information_schema" and
"performance_schema" databases are all internal databases used by
MariaDB to run itself.  They are real databases, and you can look at
the contents if you want to.  But they are automatically created by
MariaDB when it is installed.

The MySQL/MariaDB command line program "mysql" has two sorts of
commands.  The usual commands (that get terminated by a ; or \G) are
SQL commands - commands in the SQL database language.  These commands
are sent to the "mysqld" database program that is running as a daemon,
and it sends the results back to "mysql".  The other sort of commands
are executed directly by the "mysql" executable.  These commands are
the ones that are shown by the "help" command, and are terminated by
an end-of-line when you hit the <Enter> key.  The "mysql" program may
then talk to "mysqld" to get what it needs for one of those commands,
but it does that itself as necessary.  So the "status" command, for
example, will do that.  But the "quit", "help", "source" "pager" and
"tee" commands are only executed by "mysql" locally, for example.
These two sorts of commands are very confusing when you are a beginner
and just learning.

The ; at then end of an SQL command can be replaced by \G.  If you do
that, the command that is sent to "mysqld" will actually be sent with
a ; - the \G tells "mysql" to change the output format it uses to put
one field on each line, instead of all fields for a row on one line in
a box.

Now, from the output you got from "show databases", as you discovered,
it is clear that there is no mythconverg database.  Which is why
mythbackend does not work - when it attempts to open the mythconverg
database, it gets an error.  So you will need to create the
mythconverg database.  So I will repeat the instructions for doing
that.

There is a file here:

/usr/share/mythtv/sql/mc.sql

which is used to create a new mythconverg database.  Take a copy of
the file somewhere you can edit it, for example:

sudo cp -a /usr/share/mythtv/sql/mc.sql /tmp/mc.sql

then edit the copy and make some changes.  Use your favourite editor,
or nano:

sudo nano /tmp/mc.sql

At the start of the file, add this line:

DROP DATABASE IF EXISTS mythconverg;

In the both the lines with the clause

IDENTIFIED BY "mythtv";

change "mythtv" to be the password in your /etc/mythtv/config.xml
file.

Then shut down mythbackend, if it is running:

sudo systemctl stop mythtv-backend

Make sure MariaDB is running:

sudo systemctl status mariadb

Then run these commands:

sudo mysql
source /tmp/mc.sql
quit

Note that the source and quit commands do not have a ; terminator. The
source command reads the mc.sql file and sends the commands in it to
mysqld one line at a time to be executed.

That should delete the old database (if there is one) and create a new
mythconverg database and set it up correctly.  But will not create all
the tables in the database.  When you start mythbackend:

sudo systemctl start mythtv-backend

that should then be able to connect to MariaDB and create and populate
all the tables used by mythbackend.  The next time you run
mythfrontend, it should create and populate all the tables used only
by mythfrontend and its frontend only plugins (such as mythmusic).

As an alternative to running mythbackend, if you run mythtv-setup, it
should also create and populate all the backend database tables when
it sees they are not there.

When you have been previously running mythtv-setup, I am guessing that
it was unable to create and populate the mythconverg database because
it did not have the correct permissions.  The GRANT command done in
mc.sql should fix that.  However, if you are intending to run
mythfrontend on other PCs and connect back to this backend box, you
will need to provide permission for external connections to the
database.  To do that, you would need to change the first GRANT line
in your copy of mc.sql to:

GRANT ALL ON mythconverg.* TO mythtv@'%' IDENTIFIED BY "mythtv";

(with the correct password in the IDENTIFIED BY clause).

You can also add the extra permissions later at any time by doing:

sudo mysql
GRANT ALL ON mythconverg.* TO mythtv@'%' IDENTIFIED BY "mythtv";
FLUSH PERMISSIONS;
quit

Note that if you do it later, the second GRANT command will add a
second (wider) set of permissions - the original mythtv at localhost
permissions will remain in the permissions database.  You can see all
the GRANTs in MariaDB by doing this:

sudo mysql
show grants;
quit

In SQL strings, the % character is used to match string of zero or
more characters, so '%' is a string that will match against any string
of characters.  So using it in the GRANT command after the @ symbol
tells MariaDB to match any IP address as having that permission.  The
name on the left of the @ symbol is the username that is matched in
that GRANT.  Unless you change it, mythbackend always uses the
username 'mythtv'.  So mythtv@'%' allows the mythtv user to login to
MariaDB from any IP address.  You can use a more limited range of IP
addresses, if you prefer.  For example, if your network uses the
192.168.*.* addresses, then you could put mythtv@'192.168.%' to only
allow access from your local network.