[mythtv-users] Password error on optimize_mythdb.pl
Don Brett
dlbrett at zoominternet.net
Sun Feb 25 04:49:46 UTC 2018
On 2/24/2018 2:31 PM, R. G. Newbury wrote:
> On 2018-02-24 12:05 PM, Don Brett wrote:
>> I recently migrated from 12.04 to 16.04. optimize_mythdb.pl worked
>> fine on the older version, but I get a password error on this one.
>>
>> don at nick:~/Downloads$ sudo perl
>> /usr/share/doc/mythtv-backend/contrib/maintenance/optimize_mythdb.pl
>>
>> DBI
>> connect('database=mythconverg:host=localhost;port=3306','mythtv',...)
>> failed: Access denied for user 'mythtv'@'localhost' (using password:
>> YES) at /usr/share/perl5/MythTV.pm line 373.
>> Cannot connect to database:
>>
>> Mythtv generally works fine and config.xml has the correct password.
>> Is there a password change somewhere else to correct?
>
> The error reports that you are running 'optimize_mythdb.pl' as a user
> who does not have access to the database: that is mythtv at localhost is
> not a valid person *as far as optimize_mythdb.pl is concerned.
>
> Optimize_mythdb.pl may still use mysql.txt. Make sure that you have an
> instance in one of the places referred to in optimize_mythdb.pl at
> about line 58 or so.
> MythTV.pm looks for a config.xml file, and starts looking in using the
> variable 'MYTHCONFDIR'
> Make sure that that is set, and that copies of mysql.txt and
> config.xml are in that folder.
>
> To check on access (that is, to check that you do not have a mysql
> problem, not a config problem), log in to mysql/mariadb as follows *as
> root*:
>
> mysql -h <IP of database server> -D mythconverg (or whatever) -u root -p
>
> This makes sure that you are accessing the database server for mythtv.
> Most of us run mythtv all on one box, but we *often* end up doing
> 'maintenance' from another computer: at least *I* do.
>
> If you ssh in to the mythbox, then test variously with -h
> 192.168.1.200 or -h localhost or -h 127.0.0.1
>
> For reasons I do not understand, 127.0.0.1 DOES NOT WORK. Make sure
> that config.xml uses an actual IP address or name.domain (and make
> sure that /etc/hosts has that setup.)
>
> Then:
> 'use mysql;' and
> 'select user,host,password from user;'
>
> (Yes, there is a database called mysql with a table called user with a
> field called user!)
>
> You may have entries for user 'mythtv' but not @localhost. I ended up
> with entries for 127.0.0.1, localhost, 192.168.1 96 and
> mythbox.mydomain.org.. (Belt and braces etc).
>
> If that appears to be correct, then check lines 155 or so in
> /usr/share/perl5/Mythtv.pm and make sure that you have a copy of
> config.xml in the correct place. You may not have USER, HOME or
> MYTHCONFDIR properly defined, especially if your user 'mythtv' does
> not have a 'home' directory under /home.
>
> Do NOT use 127.0.0.1 for the DBHOSTNAME in mysql.txt or <host> in
> config.xml.
>
> My mythtv user's 'home' is /var/log/mythtv and MYTHCONFDIR is set in
> /etc/rc.d/rc.local and in /etc/profile *on the mythbox only*. rc.local
> sets up a number of things, including a link from /var/lib/mysql to
> /home/data/mysql, so that the setup and databases are preserved.
>
> Under Fedora, /var is wiped out on a re-install, so I make sure to
> save stuff elsewhere and re-create things on reboot: may not be
> necessary under Ubuntu, but it obviates needing to chase down the
> details which you long ago forgot. Those steps are not necessary on a
> daily or other re-boot but are definitely needed on upgrades. I prefer
> not to have to remember the details when a computer can remember them
> for me! My mythbox' uptime is presently 117 days.
>
> Geoff
>
> If you are compiling from scratch, there is a copy of config.xml in
> ..../mythtv/contrib/config_files/
>
> It uses the canonical user,password,database set of
> mythtv,mythtv,mythconverg so if you have changed any of those, it will
> break.
>
Thanks, great explanation. Tried lots of things; here are the results:
-I still have the old box available, so looked at it; neither it, nor
the new one has mysql.txt.
-I have notes from an earlier instance of Mythbuntu that had mysql.txt,
so I created it on this box and placed it in the same location as the
earlier version. It's also had the same location that MYTHCONFDIR
points to in /usr/share/perl5/MythTV.pm
-don at andy:~$ mysql -h localhost -D mythconverg -u root -p #works on
the old box, without sudo
-I'm not sure we're looking at the same script, my copy of
optimize_mythdb.pl is only has 50 rows. You mentioned row 58, were you
referencing a different file?
-Checking access, the results are:
mysql -h localhost -D mythconverg -u root -p #works
mysql -h 192.168.0.124 -D mythconverg -u root -p #fails
mysql -h 127.0.0.1 -D mythconverg -u root -p #works (127.0.0.1 is in
/etc/hosts....might explain why it works)
mysql -D mythconverg -u root -p #works
-Accessing the mysql client show something unexpected:
mysql> use mysql; #works
mysql> select user,host,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> describe user; # the user table doesn't have the 'password' field
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null |
Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI
| | |
| User | char(32) | NO | PRI
| | |
| Select_priv | enum('N','Y') | NO |
| N | |
| Insert_priv | enum('N','Y') | NO |
| N | |
| Update_priv | enum('N','Y') | NO |
| N | |
| Delete_priv | enum('N','Y') | NO |
| N | |
| Create_priv | enum('N','Y') | NO |
| N | |
| Drop_priv | enum('N','Y') | NO |
| N | |
| Reload_priv | enum('N','Y') | NO |
| N | |
| Shutdown_priv | enum('N','Y') | NO |
| N | |
| Process_priv | enum('N','Y') | NO |
| N | |
| File_priv | enum('N','Y') | NO |
| N | |
| Grant_priv | enum('N','Y') | NO |
| N | |
| References_priv | enum('N','Y') | NO |
| N | |
| Index_priv | enum('N','Y') | NO |
| N | |
| Alter_priv | enum('N','Y') | NO |
| N | |
| Show_db_priv | enum('N','Y') | NO |
| N | |
| Super_priv | enum('N','Y') | NO |
| N | |
| Create_tmp_table_priv | enum('N','Y') | NO |
| N | |
| Lock_tables_priv | enum('N','Y') | NO |
| N | |
| Execute_priv | enum('N','Y') | NO |
| N | |
| Repl_slave_priv | enum('N','Y') | NO |
| N | |
| Repl_client_priv | enum('N','Y') | NO |
| N | |
| Create_view_priv | enum('N','Y') | NO |
| N | |
| Show_view_priv | enum('N','Y') | NO |
| N | |
| Create_routine_priv | enum('N','Y') | NO |
| N | |
| Alter_routine_priv | enum('N','Y') | NO |
| N | |
| Create_user_priv | enum('N','Y') | NO |
| N | |
| Event_priv | enum('N','Y') | NO |
| N | |
| Trigger_priv | enum('N','Y') | NO |
| N | |
| Create_tablespace_priv | enum('N','Y') | NO |
| N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO |
| | |
| ssl_cipher | blob | NO |
| NULL | |
| x509_issuer | blob | NO |
| NULL | |
| x509_subject | blob | NO |
| NULL | |
| max_questions | int(11) unsigned | NO |
| 0 | |
| max_updates | int(11) unsigned | NO |
| 0 | |
| max_connections | int(11) unsigned | NO |
| 0 | |
| max_user_connections | int(11) unsigned | NO |
| 0 | |
| plugin | char(64) | NO |
| mysql_native_password | |
| authentication_string | text | YES |
| NULL | |
| password_expired | enum('N','Y') | NO |
| N | |
| password_last_changed | timestamp | YES |
| NULL | |
| password_lifetime | smallint(5) unsigned | YES |
| NULL | |
| account_locked | enum('N','Y') | NO |
| N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
mysql>
-Searching google, it looks like the 'password' has been replaced with
'authentication_string', so....
mysql> select user,host,authentication_string from user;
+------------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+-------------------------------------------+
Returned a bunch of stuff
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql>
Not sure where this leaves us....
-All of the /home and config.xml issues you mentioned appear to be setup
correctly. It's using the same name/password/database name as the
contrib example has, so it's pretty standard.
Anything else to try?
Don
PS - Thanks for the help.
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
More information about the mythtv-users
mailing list