[mythtv-users] Password error on optimize_mythdb.pl
Don Brett
dlbrett at zoominternet.net
Tue Feb 27 00:22:42 UTC 2018
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>
More information about the mythtv-users
mailing list