[mythtv-users] Resetting mysql password for mythtv user
Stephen Worthington
stephen_agent at jsw.gen.nz
Wed Nov 16 12:31:51 UTC 2016
On Wed, 16 Nov 2016 10:07:15 +0000, you wrote:
>On 15/11/16 23:13, Karl Newman wrote:
>> On Tue, Nov 15, 2016 at 3:01 PM, Mike Perkins <mikep at randomtraveller.org.uk>
>> wrote:
>>
>>> On 15/11/16 20:03, Simon Waldman wrote:
>>>
>>>> Thanks everybody for the help. See below.
>>>>
>>>> On 14 November 2016 at 17:18, R. G. Newbury <newbury at mandamus.org> wrote:
>>>>
>>>>
>>>>> Then check on your users and hosts with
>>>>>
>>>>> MariaDB [mysql]>select host,user,password from user;
>>>>>
>>>>>
>>>> mysql> select host, user, password from user;
>>>> ERROR 1054 (42S22): Unknown column 'password' in 'field list'
>>>>
>>>> and, indeed, "describe user;" shows that there's no password field.
>>>> There's
>>>> password_expired, password_last_changed, password_lifetime, but no
>>>> password.
>>>> However, the "plugin" field contains "mysql_native_password", and the
>>>> "authentication_string" field contains longish strings that look as though
>>>> they could be password hashes?
>>>> (is it possible that mythbuntu does things slightly differently here?)
>>>>
>>>> As for the users that exist,
>>>>
>>>> mysql> select host, user from user;
>>>> +-----------+------------------+
>>>> | host | user |
>>>> +-----------+------------------+
>>>> | % | mythtv |
>>>> | localhost | debian-sys-maint |
>>>> | localhost | mysql.sys |
>>>> | localhost | mythtv |
>>>> | localhost | root |
>>>> +-----------+------------------+
>>>> 5 rows in set (0.00 sec)
>>>>
>>>> So there's one for localhost and one for %. I have no idea how the % came
>>>> about! I notice that the localhost one has a load of privs (select_priv,
>>>> insert_priv, update_priv, etc) that the % one doesn't. But from the sound
>>>> of it I can probably just ignore the duplicate user, since
>>>> mythtv at localhost
>>>> is unique.
>>>>
>>>> The '%' is a wild-card. In theory it should be available from every host.
>>> Without a password to match it, however, it isn't going anywhere, and I
>>> suspect none of your other users will either.
>>>
>>> I'm afraid you'll have to nuke your database and start with a fresh one.
>>> This one does not contain the fields a normal mysql/mariadb application
>>> would expect.
>>>
>>> Without any password fields, this isn't a mythtv problem - unless some
>>> kind of bad data caused it in the first place, but I have trouble believing
>>> that.
>>>
>>
>> The authentication_string is the new name for the password field in mysql
>> 5.7. See here: http://stackoverflow.com/questions/30692812/mysql-user-
>> db-does-not-have-password-columns-installing-mysql-on-osx
>>
>> Karl
>>
>...and that is exactly why I suggested using "select * from user;" instead of guessing which columns
>the table contained. What is the point? You could miss something significant doing it that way. Why
>bother when it is easier to display everything?
The reason for not posting a "select * from user" is normally that the
user table has heaps of columns - way too many to fit across the
screen:
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| 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) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
If you want to display a small number of rows of a wide table like
user, try using a \G instead of a ; at the end of the command. That
produces a vertical display:
MariaDB [mysql]> select * from user\G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: unix_socket
authentication_string:
password_expired: N
is_role: N
*************************** 2. row ***************************
Host: %
User: mythtv
Password: *5F297F2043BF2718E77675011BC280D63C591F50
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
is_role: N
2 rows in set (0.00 sec)
More information about the mythtv-users
mailing list