[SOLVED] Cannot modify the database

[pinnerite]

This is humiliating. I seem to have variations of the same problem every couple of years and always somehow getting to this stage on Friday evening.

This is a clean install of Mint 20 and MythTV 31.
I could not get into the database and finally tried to check it in MySQL.
I had not set a root password but could not get in as a a user,
From # mysql I was in.

mysql> use mysql;
mysql> select host, user, authentication_string from user;

Code: Select all

+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| %         | mythtv           | *3BF4B8AE22C4EEE5D9938F5234B12E1BE4B08ACA                              |
| localhost | debian-sys-maint | $A$005$[	YEF
                                                   @
                                                    l^i!CG2pQyOkP0/Bf82KdhmfZm5r3RfVzPhh/B4aflcKH04 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mythtv           |                                                                        |
| localhost | root             |                                                                        |
+-----------+------------------+------------------------------------------------------------------------+

What I wanted to do was to replace localhost with "192.168.1.5" and authentication_string with password="("mythtv") where user ="mythtv"
Just trying:
mysql> insert into mysql.user (host) values ("192.168.1.5");
Returns
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
But I cannot even overcome that!
I would appreciate a push in the right direction.

[bill6502]

See if your distribution has a copy of "mc.sql" somewhere. Also, what does mysql --version show?
(sudo updatedb && locate mc.sql)

I wouldn't remove the localhost entry, but add to the users changing localhost
to whatever host(s) you'd like.

[pinnerite]

Mint 20 with Mythtv installed does have mc.sql. This is the contents:

1. CREATE DATABASE IF NOT EXISTS mythconverg;
2. CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
3. ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
4. GRANT ALL ON mythconverg.* TO mythtv@localhost;
5. FLUSH PRIVILEGES;
6. GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost;
7. FLUSH PRIVILEGES;
8. ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

The MySQL version is 8.0.20-0ubuntu.20.04.01

Thank you Bill. You must be getting fed up with me!

[bill6502]

Things have changed a bit in MySQL v8. The mc.sql above has those changes.

You already have a mythtv user that works for any host ('%'). You probably want to fix,
not remove or insert, the mythtv user at localhost.

It should have been created correctly, but do this 1st:

Code: Select all

SELECT User,Host,plugin,authentication_string FROM user WHERE User= 'mythtv';

If the plugin for both mythtv users is: mysql_native_password, then
use the 3rd command (ALTER) above to change the password. In the above,
the password is 'mythtv' so change it to whatever password you want. It should
be in your config.xml file.

[pinnerite]

"You already have a mythtv user that works for any host ('%')"
Actually I haven't.
I can no longer get into mysql.
Something has become screwed up.
When I reboot the system, I see this warning as Cinnamon comes up:
"System program problem detected"

When checking via synaptic (the package manager), I find the mysql-server-8.0 is broken but the 'fix broken packages' cannot fix it.

Eventually I did this:

sudo apt-get clean
sudo apt-get purge mysql*
sudo apt-get update
sudo apt-get install -f
sudo apt-get install mysql-server-8.0
sudo apt-get dist-upgrade

It made no difference, I could not shift it. I had also caused other issues.

$ service mysql restart

returned "job for mysql failed because the control process exited with error code" But no code.

$ systemctl status mysql.service

returned "/etc/mysql/my.cnf cannot be read."

It had been deleted during the apt-get sequence above. In any event the default one had almost nothing in it.
I had saved the version from my Mythtv 29.0 version and copied that there but with a fundamental error underpinning it all, it is hardly likely to be much help.

I feel that I need a way of ensuring that mysql-server becomes completely deleted and then reinstalled before I can move forward.

If the reason that it will not budge, it may be that Cinnamon needs it. If so perhaps I should try from run level 1(?)
Please let me know if I am barking up the wrong tree.
TIA

[bill6502]

The mythtv@localhost DB user existed. I'm guessing you meant the linux mysql user
that isn't there. In *buntu, it's created automatically.

A missing /etc/mysql/my.cnf file suggests you're running MariaDB, not MySQL.
You could check to see what exists on your host now. I wouldn't try to run two
DB servers (it can be done, but why?). mysql --version will help, or:

Code: Select all

dpkg -l mysql* mariadb* | egrep -v "^un|^rc"

[pinnerite]

Mariadb is shown as available on the repository list but is not installed.
I meant run level 3 not 1 in my last message.
I have since tried it but continue to get:

dpkg: too many errors stopping
Errors were reported while processing:
mysql-server-8.0
Processing was halted because ...

I am convinced that my only recourse is to reinstall.

[pinnerite]

I had to reinstall Mint 20 to remove ther persistent MySQL error.
The reinsall of MythTV has been trouble free, except that Setup cannot find the database.
The first step would be to change the password in the second line in the mysql table below.
I have tried:
update mysql.user set authentication_string=password("mythtv") where "host"="192.168.1.%";
but it fails.

[+-------------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-------------+------------------+------------------------------------------------------------------------+
| % | mythtv | *AC42BA9D6480C3ED7C0AA212F8B2107D5931C5CF |
| 192.168.1.% | mythtv | $A$005$y{h)=txCfh_KV1!4alDy3G4Fw5GTPbXZU8U6jFQcVG7myZcNMS1okp4Tq4 |
| localhost | debian-sys-maint | $A$005$|lgh_
*/D{
rTMdQK24yfqwH8gjtRuCpHN/pbQkSUIIbs/uzTDG6v4 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mythtv | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
| localhost | root | |
+-------------+------------------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

Thanks in advance.

[bill6502]

In my 2nd response, I asked for: SELECT User,Host,plugin,authentication_string FROM user WHERE User= 'mythtv';
but never saw the output. Based on the authentication_string above, mysql_native_password wasn't used when the
user was created and.so attempting to use PASSWORD(...) fails. You didn't include the error text in the above.

Since the user was already added, remove it with: DROP USER 'mythtv'@'192.168.1.%';

NEVER modify that table with INSERT/UPDATE.... Use the commands in mc.sql. In this case the
2nd-5th. Change localhost to the what you want. See: posting.php?mode=reply&f=36&t=3911#pr19021]

[pinnerite]

"In my 2nd response, I asked for: SELECT User,Host,plugin,authentication_string FROM user WHERE User= 'mythtv';
but never saw the output"

My apologies Bill. Because of the underlying corruption and a need to undertake a complete reinstall I assumed that was in relation to a horse that had already bolted.
I dropped the user as suggested.
As suggested I edited mc.sql to reflect the host's static address and hostname and saved it.
But when I tried to insert it with:

root@origen:~# mysql -uroot -p < /usr/share/mythtv/sql/mc.sql
Enter password:
ERROR 1410 (42000) at line 6: You are not allowed to create a user with GRANT
It didn't matter which password (or none).

From user ($) prompt it returned:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I couldn't trim the log files to fit the limit. If necessary I will do that to,morrow.
Thanks for your patience.

[pinnerite]

In desperation I remove MySQL and MythTV and reinstalled.

# apt purge mysql-server mysql-common mysql-client-8.0
# rm -rf /var/lib/mysql
# rm -rf /etc/mysql
# apt install mythtv

That cleared the problem. the mythttv-setup and mythbackend configuration went well.
I am left with a frontend problem that is best dealt with under a separate thread.
I will therefore mark this as SOLVED. Thank you again.

[bill6502]

Sorry, but the intent was to use mc.sql as an example. The same format,
but for the host you wanted. Not to edit mc.sql as it is correct as it is (was).
And, line 6 shouldn't have been changed. I really meant:

Use the commands in mc.sql. In this case the
2nd-5th. Change localhost to the what you want.

Not to edit mc.sql as it is fine as it is (was).
And, line 6 shouldn't have been changed.

You could always keep a copy of mc.sql (like my.mc.sql) with
only the changes for lines 2-5. then you'd use mc.sql 1st and
then my.mc.sql.

[pinnerite]

I documented every move I made after the reinstall and now, it seems there is a missing library file (libcec.so.4) between me and getting the frontend to behave. It is reported on a new Topic.

Your insights have all been recorded in my documentation.
Thank you again for your advice.
I do learn from my mistakes but I still somehow manage to create new ones.