[mythtv-users] Password error on optimize_mythdb.pl (solved)
Don Brett
dlbrett at zoominternet.net
Sat Mar 3 03:55:18 UTC 2018
On 2/26/2018 7:22 PM, Don Brett wrote:
> On 2/24/2018 11:49 PM, Don Brett wrote:
>> 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
>>
>> _______________________________________________
>> mythtv-users mailing list
>> mythtv-users at mythtv.org
>> http://lists.mythtv.org/mailman/listinfo/mythtv-users
>> http://wiki.mythtv.org/Mailing_List_etiquette
>> MythTV Forums: https://forum.mythtv.org
>
> Right!.....at the time is was thinking the those were some kind of
> strange passwords, then realized they were hash's. Here's the real
> output,
>
> Don
>
> mysql> use mysql;
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Database changed
> mysql>
> mysql> select user,host,authentication_string from user;
> +------------------+-----------+-------------------------------------------+
>
> | user | host |
> authentication_string |
> +------------------+-----------+-------------------------------------------+
>
> | root | localhost |
> *CC8F35F587CA5A556B4132C2407E556D92172FFC |
> | mysql.session | localhost |
> *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
> | mysql.sys | localhost |
> *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
> | debian-sys-maint | localhost |
> *004535334F32BA3A7606102F9336E6B737964423 |
> | mythtv | % |
> *CC8F35F587CA5A556B4132C2407E556D92172FFC |
> | mythtv | localhost |
> *CC8F35F587CA5A556B4132C2407E556D92172FFC |
> +------------------+-----------+-------------------------------------------+
>
> 6 rows in set (0.00 sec)
>
> mysql>
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org
I added some debug prints into the script and discovered the config file
was being read different than I expected. When I first setup the box, I
changed the password in the config file, like so:
<Password>newpassword</Password>
<!-- <Password>oldpassword</Password> -->
The script was using the commented-out line, so I switched it to this,
which worked:
<!-- <Password>oldpassword</Password> -->
<Password>newpassword</Password>
These are the debug lines added to /usr/share/perl15/MythTV.pm, just
before the connect code (around line 370).
print "\n";
print "Config directory = $conf\n";
print "Database name = $self->{'db_name'}\n";
print "Database host = $self->{'db_host'}\n";
print "Database port = $self->{'db_port'}\n";
print "Database user = $self->{'db_user'}\n";
print "Database password = $self->{'db_pass'}\n\n";
More information about the mythtv-users
mailing list