BE3 - MariaDB - May 20

Re: BE3 - Issues with installation and database

Hi Stephen!

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.
After I ‘discovered’ the problem was the lack of the semi-colon figured it was just a ‘shorthand’: all part of my learning!  ...Wasn’t sure on the databases (and other ‘excess’ information); sometimes what one sees (or doesn’t see) is important but I don’t have the experience to know and so give it to someone who does.


<snip>
sudo nano /tmp/mc.sql

At the start of the file, add this line: DROP DATABASE IF EXISTS mythconverg;
Command already inserted.  Just doing ‘live commentary’; the /tmp file was not present prior to me creating this morning.

In the both the lines with the clause IDENTIFIED BY "mythtv";
change "mythtv" to be the password in your /etc/mythtv/config.xml file.

Sort of as a follow-up to the above, the password field was ‘mythtv’ originally.

Then shut down mythbackend, if it is running: sudo systemctl stop mythtv-backend
Immediately back to command line. (No delay noted.)

Make sure MariaDB is running: sudo systemctl status mariadb
Active….

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@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.

------------------------------


For some reason still displays as “Mariadb [(none)]>” – may not be exact but know ‘mythconverg’ should be where ‘name’ is.
Try rebooting to reload.  Still ‘none’. :(
/tmp/mc.sql gone on reboot – was going to show you my work should I have made an error.

Recreate  /tmp/mc.sql….
Ah!  The first line is “Create”, not “drop’!      ==> mc_sql.txt

DROP DATABASE IF EXISTS mythconverg;
CREATE DATABASE IF NOT EXISTS mythconverg;
GRANT ALL ON mythconverg.* TO mythtv@'%' IDENTIFIED BY "Z******S";
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost IDENTIFIED BY "Z******S";
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

     (Password partially obliterated)
     Line 3	'%'  for running with other Frontends.

At    sudo mysql   source /tmp/mc.sql quit  steps prior to ‘quit’ still displays MariaDB [(none)].
barry@Backend-3:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.1.38-MariaDB-0ubuntu0.18.04.2 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source /tmp/mc.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> quit
Bye
barry@Backend-3:~$

sudo systemctl start mythtv-backend   ==> immediate to Terminal prompt

At   sudo mysql   show grants;  quit     steps:


barry@Backend-3:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.1.38-MariaDB-0ubuntu0.18.04.2 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]>

Obviously still not connecting/seeing mythconverg.
	<grumble><mumble> Take a break.
Try altering /tmp/mc.sql  from mythtv@localhost  to  mythtv@192.168.0.3 (Line 5):

DROP DATABASE IF EXISTS mythconverg;
CREATE DATABASE IF NOT EXISTS mythconverg;
GRANT ALL ON mythconverg.* TO mythtv@'%' IDENTIFIED BY "Z******S";
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@192.168.0.3 IDENTIFIED BY "Z******S";
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Then shut down mythbackend, if it is running: sudo systemctl stop mythtv-backend
Immediately back to command line. (No delay noted.)

Make sure MariaDB is running: sudo systemctl status mariadb

● mariadb.service - MariaDB 10.1.38 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2019-05-20 08:31:00 CDT; 1h 49min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 1131 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 1128 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
  Process: 924 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
  Process: 912 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 894 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 1062 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 27 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └─1062 /usr/sbin/mysqld

May 20 08:30:54 Backend-3 systemd[1]: Starting MariaDB 10.1.38 database server...
May 20 08:30:56 Backend-3 mysqld[1062]: 2019-05-20  8:30:56 140166783814784 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB-0ubuntu0.18.04.2) starting as process 1062 ...
May 20 08:31:00 Backend-3 systemd[1]: Started MariaDB 10.1.38 database server.
May 20 08:31:01 Backend-3 /etc/mysql/debian-start[1134]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
May 20 08:31:01 Backend-3 /etc/mysql/debian-start[1134]: Looking for 'mysql' as: /usr/bin/mysql
May 20 08:31:01 Backend-3 /etc/mysql/debian-start[1134]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
May 20 08:31:01 Backend-3 /etc/mysql/debian-start[1134]: This installation of MySQL is already upgraded to 10.1.38-MariaDB, use --force if you still need to run mysql_upgrade
May 20 08:31:01 Backend-3 /etc/mysql/debian-start[1180]: Checking for insecure root accounts.
May 20 08:31:01 Backend-3 /etc/mysql/debian-start[1185]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
~
~


Why showing 8:30 (a.m.) when it’s two hours later and I've played with stuff???
	Ran the rest of the commands but nothing changed.
	sudo mysql   still displays MariaDB [(none)],   so know still not seeing mythconverg

	Reboot.

	Still ‘none’.

	Upload this reply to let you find out where I misunderstood something.

Thanks!
Barry

Received automated error message:  Message body is too big: 50392 bytes with a limit of 40 KB
Decided to post here.