Cannot get MySQL to accept mc.sql

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

Moderator: Forum Moderators

pinnerite
Senior
Posts: 256
Joined: Mon Apr 07, 2014 2:34 pm
Great Britain

Cannot get MySQL to accept mc.sql

Post by pinnerite »

This is a fresh Mythtv setup on Mint 20.3

$ mysql -uroot -p gets me into mysql.

$mysql -uroot -p < mc.sql does not.

I decided to try the contents line by line.
The second line generated an error msg:

CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH 'mythtv';
ERROR 1524 (HY000): Plugin 'mythtv' is not loaded

Yes I confess that I replaced 'localhost' with '192.168.1.4' .
I had also changed password parameters:
[mysql> SHOW VARIABLES LIKE 'validate_password%';
+-----------------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+------------------------------------------------------+-------+
I am at a loss :(
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 »

Check what version of SQL Mint is using. If it's MySQL v8, then the
syntax has changed. mc.sql shoudl look like this:

Code: Select all

CREATE DATABASE IF NOT EXISTS mythconverg;
CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@localhost;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost;
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Of course you can replace localhost with remote IPs as you already tried.
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 »

Thanks Bill.
At least it has moved forward to this old chestnut. :(

mysql -uroot -p < mc.sql
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
I have mysql-server installed.
I cannot find the lengthy version of my.cnf on my system that I have a listing of from 14 years ago!
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 »

By default, mysql will use --host=localhost, which can (I'd argue should) use a socket.

Try adding the --host=theHostNameOrIP switch to tell it where your DB lives. Or, run it on
that host directly.
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 tried every combination that I could without success.
I then tried to create mysqld.sock at /var/run/mysqld also without success.
I then created it at /etc/mysqld and edited /etc/mysqld/mysql.cnf adding the following to the foot:

######## Added by AS 15/01/2022 ########
[mysqld]
socket = /etc/mysql/mysqld.sock
Still no success.The command insisted it was searching at '/var/run/mysqld/mysqld.sock'
I went back and ran:
$ mysql --host=albury.asandco -uroot -p < mc.sql
and got:
ERROR 2003 (HY000): Can't connect to MySQL server on 'albury.asandco:3306' (113)
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 socket is created when the MySQL daemon starts. I <think> you're still trying to do this from the remote frontend.

Don't. I suspect you don't have permissions there.

On the backend (where the DB is) check the existing user permissions:

Code: Select all

$ mysql --user=mythtv --host=localhost --password mythconverg

mysql> SHOW GRANTS;
+-----------------------------------------------------------------+
| Grants for mythtv@localhost                                     |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost`                      |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
exit
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 a fresh installation of Mint 20.3 only a couple of days ago.
It has both mythtv backend and frontends installed.

$ mysql --user=mythtv --host=localhost --password mythconverg
Enter password:

Which password? mythtv or none produce the same result:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
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 »

If the socket isn't there, then typically the server isn't running. Try: systemctl status mysql

The password would be the one in config.xml.

Eventually, to add a user, the root mysql user is required. The method for accessing it
seems to be distribution dependant. This 'works for me':

Code: Select all

sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
But let's see the output of the SHOW GRANTS command 1st. Include the mysql version
that appears in the output please when mysql starts.
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 »

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
[sudo] password for me:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I never get to SHOW GRANTS.

I have just discovered mysqld.cnf in /etc/mysqld/mysqld.conf.d but the basic settings (including the socket address) are commented out.

So where does MySQL grab its configuration parameters from?
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 lines that are commented out are the defaults. No need to touch them.
In fact, NEVER make any changes to that file because a package manager
can (and does) update things there and your changes get blown away.

What did the command above: systemctl status mysql show?
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 »

$ systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:
Active: activating (start) since Sat 2022-01-15 22:18:26 GMT; 2s ago
Process: 374880 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code
Main PID: 374888 (mysqld)
Status: &quot;Server startup in progress&quot;
Error: 99 (Cannot assign requested address)
Tasks: 11 (limit: 19021)
Memory: 353.0M
CGroup: /system.slice/mysql.service
└─374888 /usr/sbin/mysqld

Jan 15 22:18:26 albury.asandco systemd[1]: Starting MySQL Community Server...
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 »

sudo systemctl stop mysql

grep -ir bind.address /etc/mysql

I don't think the grep is going to find a problem, but it's worth checking.

sudo systemctl start mysql

sudo cat /var/log/mysql/error.log

No need to paste the whole log, just look for the timestamps from you just started the daemon 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 »

alan@albury:~$ sudo systemctl stop mysql

alan@albury:~$ grep -ir bind.address /etc/mysql
/etc/mysql/conf.d/mythtv.cnf-vanilla:#bind-address=::
/etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.4
grep: /etc/mysql/debian.cnf: Permission denied
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 127.0.0.1

alan@albury:~$ sudo systemctl start mysql
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

$ sudo cat /var/log/mysql/error.log
2022-01-15T11:25:23.214821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 1224

Is that OK?
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 »

Yes, that's OK.

Delete the line: /etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.4

Restart mysql.

I'd check systemctl status --no-pager mysql.service
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 »

Thanks. That got me in.
Going back to an earlier request:

$ mysql --user=mythtv --host=localhost --password mythconverg

returned:

ERROR 1045 (28000): Access denied for user 'mythtv'@'localhost' (using password: YES)

Not surprising as I had not been able to get mysql to read mc.sql, so I tried again:

This time it returned ERROR 1819 (HY000) at line 2: Your password does not satisfy the current policy requirements.
The line is:

CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH mysql_native_password;

I repeated the exercise with:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.4' IDENTIFIED WITH 'mythtv';

That returned:
ERROR 1524 (HY000) at line 2: Plugin 'mythtv' is not loaded
Post Reply