Cannot login to database

[pinnerite]

Following a week of problems with MySQL I installed MariaDB on Mint 19.1
and created the mythtv user, mythconverg etc.

The user is in the mythtv group

mythtv-setup went Ok.
host IP address, password etc were as in config.xml, both in ~./mythtv and /etc/mythtv except the former has an entry for rendering.

But but when I try to start the backend it fails to connect to the database.

I cannot figure out what I might have missed.

[bill6502]

Before going further, look for a line similar to this in the backend log:

Code: Select all

... Using configuration directory = ...

That tells you where the backend will find config.xml (and other stuff.)
It could be as above, but please verify it. Then a pastebin of the latest
backend log here if config.xml isn't the problem.

Be sure you can connect to the DB from the command line using the
same credentials as found in config.xml. If that fails, the backend will
not start:

Code: Select all

mysql --host=... --user mythtv --password mythconverg

For a combined FE/BE, the preferred host is: localhost (assuming the DB
is on the same box) not the IP address. That way a socket is used instead
of TCP.

[pinnerite]

The configuration directory contents are spot on.

I can indeed access the DB from the command line.

I am unfamiliar with pastebin but I have pasted the backend.log here.

https://pastebin.com/nZrzCKsn

[bill6502]

Code: Select all

Using configuration directory = /home/mythtv/.mythtv

is in play here, which could be a symbolic link to /etc/mythtv mentioned
above. Verify that because that link has been known to get broken:
ls -ls /home/mythtv/.mythtv

Ref: https://www.mythtv.org/wiki/Config.xml#Troubleshooting

[pinnerite]

thelma@origen /var/log/mythtv $ ls -ls /home/mythtv/.mythtv
total 20
4 drwxr-xr-x 2 sshd mythtv 4096 Nov 30 19:06 3rdParty
4 drwxr-xr-x 5 sshd mythtv 4096 Dec 6 21:56 cache
4 drwxr-xr-x 2 sshd mythtv 4096 Nov 30 19:06 channels
0 lrwxrwxrwx 1 sshd mythtv 22 Jun 26 2018 config.xml -> /etc/mythtv/config.xml
4 drwxr-xr-x 2 sshd mythtv 4096 Nov 30 19:06 themes
4 drwxr-xr-x 4 sshd mythtv 4096 Nov 30 19:06 tmp

Just to be sure I ran cat at both locations. It is fine.

[bill6502]

Good. The owner of the files/directories above is odd, I'd expect it
to be mythtv too. That's not a solution, just an observation for now.

To be clear, exactly this works:

Code: Select all

mysql --host=192.168.0.5 --port=3306 --user mythtv --password mythconverg

And port 3306 is in config.xml too. I'd still rather see localhost rather than an IP in case the
IP isn't known to mysql.

I'd do: sudo cat /var/log/mysql/error.log
if the above works.

[pinnerite]

This worked
mysql --host=localhost --user mythtv --password mythconverg

But this did not -- at first
mysql --host=192.168.0.5 --port=3306 --user mythtv --password mythconverg

Following your pointer, I discovered that the bind address in /etc/mysql/mariadb.conf.d/50-server.cnf was still 127.0.0.1.
I changed it to 192.168.0.5 and rebooted the machine.

UsIng either line I can now get into mysql.

But I still cannot get mythtv-setup to let me in:

$ /usr/bin/mythtv-setup
ERROR 1045 (28000): Access denied for user 'mythtv'@'origen.asandco' (using password: YES)

This is followed by:

/usr/bin/mythtv-setup: 42: /usr/bin/mythtv-setup: status: not found
Gtk-Message: 15:11:59.986: GtkDialog mapped without a transient parent. This is discouraged.

origen.asandco is indeed the hostname for 192.168.0.5 and per the hosts file but I do not know where mythtv-status is collecting it.

config.xml has 192.168.0.5

[bill6502]

1. The output of these two commands please, include the input:

Code: Select all

ls -ld {~,~mythtv}/.mythtv/config.xml /etc/mythtv/config.xml
diff {~,~mythtv}/.mythtv/config.xml

The 1045 error means that username and/or password
is wrong.

2. Again, localhost is the proper <Host> value.

3. If you want to use the IP, there's no good reason to do that,
then let's see the /etc/hosts entry (assuming that's where
origen.asandco is defined:

Code: Select all

grep origen.asandco /etc/hosts

The reason is that origen.asandco must an FQDN, not an alias. MariaDB
won't connect on my host if I use an alias.

4. I suspect mythtv-setup is a shell script in Mint. It does some other
magic and then probably executes mythtv-setup.real. Try running
that to see if the two error messages go away. They may be harmless
errors, I don't have the script and can't test.

[pinnerite]

This is the result:

# ls -ld {~,~mythtv}/.mythtv/config.xml /etc/mythtv/config.xml

-rw-rw---- 1 mythtv mythtv 454 Feb 21 19:16 /etc/mythtv/config.xml
lrwxrwxrwx 1 sshd mythtv 22 Jun 26 2018 /home/mythtv/.mythtv/config.xml -> /etc/mythtv/config.xml
lrwxrwxrwx 1 root root 22 Jan 27 17:28 /root/.mythtv/config.xml -> /etc/mythtv/config.xml

# diff {~,~mythtv}/.mythtv/config.xml
#

# grep origen.asandco /etc/hosts
192.168.0.5 origen.asandco origen
#

[bill6502]

Good, and using the MariaDB root password, what does this say:

Code: Select all

mysql --user=root --password mysql --host=localhost --execute="SELECT Host,User,Password FROM user ORDER BY User,Host"

To test the ability of origen.asandco to access the DB, you'd type:

Code: Select all

mysql --host=origen.asandco --port=3306 --user mythtv --password mythconverg

[pinnerite]

$ mysql --user=root --password mysql --host=localhost --execute="SELECT Host,User,Password FROM user ORDER BY User,Host"

I did not set a root password, yet I could not get in with the above line.

$ mysql --host=origen.asandco --port=3306 --user mythtv --password mythconverg

The above line ran OK.

Incidentally, the reason that I set <Host>192.168.0.5</Host> in both /etc/mythtv/config.xml and ~/.mythtv/config.xml is that a remote frontend also uses the backend machine.

[bill6502]

The Host must be the IP or hostname of the DB host on remotes, but in the master, it should
be localhost. It may still fail, but at least it the most efficient setting.

As long as ~ and ~mythtv's .mythtv/config.xml are links to /etc/mythtv/config.xml, you don't
need to check both files, they point to the same thing. That's what the ls -ld you did above
verified.

I can't explain why the 1st command doesn't work. You can enter mysql
using whatever credentials you used to load mc.sql and then enter the
SELECT query there. I don't have Mint, so can't help there.

Everything looks good. The only thing I can't see is the actual contents of config.xml.

The other thing I can't do, as already mentioned, is run your copy of mythtv-setup. You
could cat that file to be sure it's a script and look in it to see if there's a path to
mythtv-setup.real. Then run that program to prove that the script isn't setting something
else that uses a different config.xml. It may set MYTHCONFDIR which can change it.

[pinnerite]

I must apologise. I mislead you. I had set a root password. It was 'mysql'.

I do not remember doing it but running through all the terminal commands executed as root, I came across it.

I will follow up later after a period of sackcloth and ashes.

[pinnerite]

mythtv-setup is a script but it does not set MYTHCONFDIR
I have never had to set it manually.

Here is ~/.mythtv/config.xml

<Configuration>
<LocalHostName>origen.asandco</LocalHostName>
<Database>
<PingHost>1</PingHost>
<Host>localhost</Host>
<UserName>mythtv</UserName>
<Password>hFclugQ7</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>
<WakeOnLAN>
<Enabled>0</Enabled>
<SQLReconnectWaitTime>0</SQLReconnectWaitTime>
<SQLConnectRetry>5</SQLConnectRetry>
<Command>echo 'WOLsqlServerCommand not set'</Command>
</WakeOnLAN>
<UPnP>
<UDN>
<MediaRenderer>f4f5ed88-bf28-421f-b803-45e05cf38e5c</MediaRenderer>
</UDN>
</UPnP>
</Configuration>

This is what I get when running # mythbackend
2019-02-24 14:24:51.769988 C mythbackend version: fixes/29 [v29.1-13-g329c235] www.mythtv.org
2019-02-24 14:24:51.770004 C Qt version: compile: 5.9.4, runtime: 5.9.5
2019-02-24 14:24:51.770006 N Enabled verbose msgs: general
2019-02-24 14:24:51.770014 N Setting Log Level to LOG_INFO
2019-02-24 14:24:51.780524 I Added logging to the console
2019-02-24 14:24:51.780759 I Setup Interrupt handler
2019-02-24 14:24:51.780768 I Setup Terminated handler
2019-02-24 14:24:51.780774 I Setup Segmentation fault handler
2019-02-24 14:24:51.780780 I Setup Aborted handler
2019-02-24 14:24:51.780785 I Setup Bus error handler
2019-02-24 14:24:51.780791 I Setup Floating point exception handler
2019-02-24 14:24:51.780796 I Setup Illegal instruction handler
2019-02-24 14:24:51.780804 I Setup Real-time signal 0 handler
2019-02-24 14:24:51.780810 I Setup Hangup handler
2019-02-24 14:24:51.780889 N Using runtime prefix = /usr
2019-02-24 14:24:51.780891 N Using configuration directory = /root/.mythtv
2019-02-24 14:24:51.780939 I Assumed character encoding: en_GB.UTF-8
2019-02-24 14:24:51.781158 N Empty LocalHostName.
2019-02-24 14:24:51.781163 I Using localhost value of origen.asandco
2019-02-24 14:24:51.781192 I Start up testing connections. DB localhost, BE , attempt 0, status dbAwake
.... ditto .....
2019-02-24 14:25:37.814016 I Start up testing connections. DB localhost, BE , attempt 10, status dbAwake
2019-02-24 14:25:42.817532 I Start up failure. host localhost, status dbAwakeFail
Cannot login to database

I noticed that the line:
<LocalHostName>origen.asandco</LocalHostName>did not appear in the /etc/mythtv/config.xml
so I added it. It apeared in /root/.mythtv/config.xml, referred to in the list above but as you can see the backend still will not find the database.

[bill6502]

The backend should never run as the root user:
sudo rm -rf /root/.mythtv
But until mythtv-setup works, no need to start the backend for now.

Back to the originally scheduled program:

This should work: mysql --host=localhost --password=hFclugQ7 --user=mythtv mythconverg

If it still does, then exit SQL and type: locate -b '\mythtv-setup.real I expect it to return
one line. Type ls -l on the file(s) returned, one should be executable.

As yourself, not root, run the result of the above.

Remember, input and output of commands please.