[mythtv-users] Re: remote and local database access - solved!

Jay and Sue Wardle jaywardle at prodigy.net
Thu Oct 13 09:25:31 EDT 2005


The short story was that I was adding a remote frontend and followed the
modifying access to MySQL instructions in section 6 of the HOWTO.
Since my MBE and database machine is named "mythtv", I used this
command to test network access to the database:

 >> mysql -hmythtv -u mythtv mythconverg -p

After that work, the localhost mythtv user was still able to access the  
database.
The mythtv user on remote machines was able to access the database,
and the remote frontend partially worked.
But, mythtv on the same machine as the database still couldn't connect.


More searching in the MythTV Users archive found a discussion named
"How do I removed existing mysql database and start over?".
That discussion referenced this page about MySQL access:
http://dev.mysql.com/doc/mysql/en/access-denied.html
Both were very interesting, and gave lots of hints.

One suggestion was to pay attention to the MySQL error message.   :-)
The error msgs all refered to "mythtv at mythtv", so I added a specific  
user
for mythtv on this same machine like this:

# create the new user and check what happened
grant all on mythconverg.* to mythtv@"mythtv" identified by "mythtv";
select User,Host,Password,Create_tmp_table_priv,Lock_tables_priv from  
user where User="mythtv";

I saw that the password was different for the newly made user, so set  
it to the same for all entries:

# fix the password
update user set Password=PASSWORD('secretPassword') WHERE user='mythtv';
select User,Host,Password,Create_tmp_table_priv,Lock_tables_priv from  
user where User="mythtv";

I noticed that the new user had different table privileges:

# now set the temporary table privilege than localhost had.
# I didn't test how the backend worked before trying this, it may be  
unneeded:
update user set Create_tmp_table_priv="Y" where User="mythtv";
select User,Host,Password,Create_tmp_table_priv,Lock_tables_priv from  
user where User="mythtv";

# now set the lock table privilege
# I didn't test how the backend worked before trying this, it may be  
unneeded:
update user set Lock_tables_priv="Y" where User="mythtv";
select User,Host,Password,Create_tmp_table_priv,Lock_tables_priv from  
user where User="mythtv";

flush privileges;


The final result of the select query gives this:

mysql> select User,Host,Password,Create_tmp_table_priv,Lock_tables_priv  
from user where Us
er="mythtv";
+--------+-----------+------------------+----------------------- 
+------------------+
| User   | Host      | Password         | Create_tmp_table_priv |  
Lock_tables_priv |
+--------+-----------+------------------+----------------------- 
+------------------+
| mythtv | localhost | 32f6d8ac08075287 | Y                     | Y      
            |
| mythtv | %         | 32f6d8ac08075287 | Y                     | Y      
            |
| mythtv | mythtv    | 32f6d8ac08075287 | Y                     | Y      
            |
+--------+-----------+------------------+----------------------- 
+------------------+
3 rows in set (0.00 sec)

And everything works!

G'day,
Jay Wardle
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: text/enriched
Size: 3104 bytes
Desc: not available
Url : http://mythtv.org/pipermail/mythtv-users/attachments/20051013/66a97603/attachment.bin


More information about the mythtv-users mailing list