Cannot get MySQL to accept mc.sql

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

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.
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

mysql> SHOW GRANTS FOR mythtv;
+---------------------------------------------------------+
| Grants for mythtv@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%` |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
+---------------------------------------------------------+
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

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.
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

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 |
+-----------+------------------+------------------------------------------------------------------------+
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

Good, mythtv@% says remote users are OK. I have an entry for localhost as well. This may fail:

SHOW GRANTS FOR mythtv@localhost;
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

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'
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

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.
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

$ 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.
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

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'
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

I had localhost in the above and corrected it to %
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

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.
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

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).
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

I have now fully restored MySQL having deleted the database.
However I can only get into it from a root (#) prompt.
User avatar
bill6502
Developer
Posts: 2299
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Cannot get MySQL to accept mc.sql

Post by bill6502 »

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.
pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Re: Cannot get MySQL to accept mc.sql

Post by pinnerite »

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?
Post Reply