Cannot get MySQL to accept mc.sql
Moderator: Forum Moderators
Re: Cannot get MySQL to accept mc.sql
Don't use Mint's mc.sql or CREATE/ALTER commands in it, you're clearly on mysql v8.
Last time the following was tried, the mysql server was constantly restarting, so
lets try again:
sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
Then, SHOW GRANTS FOR mythtv; exit to leave mysql.
I'm verifying that you (Mint) can become the mysql root user and what
grants, if any, are in play for mysql user mythtv. Then we'll move on to
the proper mc.sql above.
Last time the following was tried, the mysql server was constantly restarting, so
lets try again:
sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
Then, SHOW GRANTS FOR mythtv; exit to leave mysql.
I'm verifying that you (Mint) can become the mysql root user and what
grants, if any, are in play for mysql user mythtv. Then we'll move on to
the proper mc.sql above.
Re: Cannot get MySQL to accept mc.sql
mysql> SHOW GRANTS FOR mythtv;
+---------------------------------------------------------+
| Grants for mythtv@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%` |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
+---------------------------------------------------------+
+---------------------------------------------------------+
| Grants for mythtv@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%` |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
+---------------------------------------------------------+
Re: Cannot get MySQL to accept mc.sql
OK, the grants are already there. from the same root mysql login as above:
SELECT host,user,authentication_string FROM user ORDER BY user,host;
Looking to see if native passwords are in use.
SELECT host,user,authentication_string FROM user ORDER BY user,host;
Looking to see if native passwords are in use.
Re: Cannot get MySQL to accept mc.sql
mysql> SELECT host,user,authentication_string FROM user ORDER BY user,host;
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | debian-sys-maint | $A$005$F5 %d'zoHMtJEX88t/x1bvIEMnwtseub5Tc7Z02gRpckab8.tZPnvPL5 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| % | mythtv | *B5BCD029F2268798922CDC55B5253D354B2C0246 |
| localhost | root | *2B2E29BFA4C432ED2C7C49E07B175220796B98EE |
+-----------+------------------+------------------------------------------------------------------------+
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | debian-sys-maint | $A$005$F5 %d'zoHMtJEX88t/x1bvIEMnwtseub5Tc7Z02gRpckab8.tZPnvPL5 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| % | mythtv | *B5BCD029F2268798922CDC55B5253D354B2C0246 |
| localhost | root | *2B2E29BFA4C432ED2C7C49E07B175220796B98EE |
+-----------+------------------+------------------------------------------------------------------------+
Re: Cannot get MySQL to accept mc.sql
Good, mythtv@% says remote users are OK. I have an entry for localhost as well. This may fail:
SHOW GRANTS FOR mythtv@localhost;
SHOW GRANTS FOR mythtv@localhost;
Re: Cannot get MySQL to accept mc.sql
You are correct it did.
mysql> SHOW GRANTS FOR mythtv@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on host 'localhost'
mysql> SHOW GRANTS FOR mythtv@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on host 'localhost'
Re: Cannot get MySQL to accept mc.sql
I need to be sure that an entry for localhost is mandatory or if the % covers that as well.
Before adding the localhost user, from the shell command line:
mysql --user=mythtv --host=localhost --password mythconverg
The password from config.xml
I don't where Fedora keeps config.xml. Typical locations are under ~/.mythtv, ~mythtv/.mythtv and /etc/mythtv or
if MYTHCONFDIR is set, under the location there. If multiple locations, they must match.
Before adding the localhost user, from the shell command line:
mysql --user=mythtv --host=localhost --password mythconverg
The password from config.xml
I don't where Fedora keeps config.xml. Typical locations are under ~/.mythtv, ~mythtv/.mythtv and /etc/mythtv or
if MYTHCONFDIR is set, under the location there. If multiple locations, they must match.
Re: Cannot get MySQL to accept mc.sql
$ mysql --user=mythtv --host=localhost --password mythconverg
Enter password: (mythtv)
ERROR 1045 (28000): Access denied for user 'mythtv'@'localhost' (using password: YES)
Both copies of config.xml at:
~/.mythtv; and /home/mythtv/.mythtv
have mythv as the password.
Enter password: (mythtv)
ERROR 1045 (28000): Access denied for user 'mythtv'@'localhost' (using password: YES)
Both copies of config.xml at:
~/.mythtv; and /home/mythtv/.mythtv
have mythv as the password.
Re: Cannot get MySQL to accept mc.sql
The hashed password for mythtv is CC8F35F587CA5A556B4132C2407E556D92172FFC
but in the above, the hash for whatever got selected is B5BCD029F2268798922CDC55B5253D354B2C0246
I can't, or at least don't know how to, reverse that to tell you what it is. I would have expected the
correct value to have been put in config.xml. Try: ALTER USER 'mythtv'@'%' IDENTIFIED BY 'mythtv'
but in the above, the hash for whatever got selected is B5BCD029F2268798922CDC55B5253D354B2C0246
I can't, or at least don't know how to, reverse that to tell you what it is. I would have expected the
correct value to have been put in config.xml. Try: ALTER USER 'mythtv'@'%' IDENTIFIED BY 'mythtv'
Re: Cannot get MySQL to accept mc.sql
I had localhost in the above and corrected it to %
Re: Cannot get MySQL to accept mc.sql
I have been wrestling with errors in the table.
I will have to continue to read up on MySQL and/or get help!
I'll come back to you. Thank you for your generous help.
I will have to continue to read up on MySQL and/or get help!
I'll come back to you. Thank you for your generous help.
Re: Cannot get MySQL to accept mc.sql
After advice from the MySQL newsgroup that simply repeated what I had already tried and checking my statements with a syntax checker, I came to the conclusion that there had been a corruption and would uninstall MySQL and reinstall.
This did not go well. The corrupt User table was still there!
I am awaiting further advice on how to completely purge MySQL.
(I had already used purge and autoremove).
This did not go well. The corrupt User table was still there!
I am awaiting further advice on how to completely purge MySQL.
(I had already used purge and autoremove).
Re: Cannot get MySQL to accept mc.sql
I have now fully restored MySQL having deleted the database.
However I can only get into it from a root (#) prompt.
However I can only get into it from a root (#) prompt.
Re: Cannot get MySQL to accept mc.sql
No idea what made you think the user table was corrupt. ANALYZE TABLE user;
would have answered that. You printed a portion of it earlier and it looked OK.
Did the ALTER USER command not work?
Not removing the DB when removing/reinstalling is normal.
By removing the mysql DB, the user table was removed and
the newly installed one has no idea what user mythtv is.
You could uninstall the mythtv package too and reinstall it,
then the mythconverg DB will be created.
Or you can use the proper version of mc.sql pasted much earlier in the
thread. That will create the mythconverg DB and mythtv user, for localhost.
You can then add the mythtv user for remote hosts.
would have answered that. You printed a portion of it earlier and it looked OK.
Did the ALTER USER command not work?
Not removing the DB when removing/reinstalling is normal.
By removing the mysql DB, the user table was removed and
the newly installed one has no idea what user mythtv is.
You could uninstall the mythtv package too and reinstall it,
then the mythconverg DB will be created.
Or you can use the proper version of mc.sql pasted much earlier in the
thread. That will create the mythconverg DB and mythtv user, for localhost.
You can then add the mythtv user for remote hosts.
Re: Cannot get MySQL to accept mc.sql
This is what the User table looked like. Look at the penultimate item. I could not remove it using any statements.
+------------------+------------------------------------------------------------------------+-----------+
| User | authentication_string | Host |
+------------------+------------------------------------------------------------------------+-----------+
| mythtv | *B5BCD029F2268798922CDC55B5253D354B2C0246 | % |
| debian-sys-maint | $A$005$F5 %d'zoHMtJEX88t/x1bvIEMnwtseub5Tc7Z02gRpckab8.tZPnvPL5 | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
!ug2Ym[S%/K+ca2n7M06VWGOgOzJFxxKkrg/c7pjNT6Dm3n3FzLXm56 | localhost |
| root | *2B2E29BFA4C432ED2C7C49E07B175220796B98EE | localhost |
+------------------+------------------------------------------------------------------------+-----------+
No. ALTER USER would not work nor CREATE nor strings taken from the MySQL web site.
I decided that the law of diminishing returns had to be obeyed.
I uninstalled mythtv and reinstalled it. Then:
$ sudo mysql < mc.sql
S sudo service mysql stop
$ sudo service mysql start
MythTV Backend Setup.
That seemed to go through OK with a few niggles.
Eventually I was able to complete the backend setup and achieved a complete channel scan.
But when I started the frontend it could not connect to the database and keeps restarting!
I wanted to restore a backup from 10 days ago Is that possible?
+------------------+------------------------------------------------------------------------+-----------+
| User | authentication_string | Host |
+------------------+------------------------------------------------------------------------+-----------+
| mythtv | *B5BCD029F2268798922CDC55B5253D354B2C0246 | % |
| debian-sys-maint | $A$005$F5 %d'zoHMtJEX88t/x1bvIEMnwtseub5Tc7Z02gRpckab8.tZPnvPL5 | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
!ug2Ym[S%/K+ca2n7M06VWGOgOzJFxxKkrg/c7pjNT6Dm3n3FzLXm56 | localhost |
| root | *2B2E29BFA4C432ED2C7C49E07B175220796B98EE | localhost |
+------------------+------------------------------------------------------------------------+-----------+
No. ALTER USER would not work nor CREATE nor strings taken from the MySQL web site.
I decided that the law of diminishing returns had to be obeyed.
I uninstalled mythtv and reinstalled it. Then:
$ sudo mysql < mc.sql
S sudo service mysql stop
$ sudo service mysql start
MythTV Backend Setup.
That seemed to go through OK with a few niggles.
Eventually I was able to complete the backend setup and achieved a complete channel scan.
But when I started the frontend it could not connect to the database and keeps restarting!
I wanted to restore a backup from 10 days ago Is that possible?