[GTALUG] [OT] mySQL help

[Jason Shaw]

On Tue, May 28, 2019 at 7:50 PM Stephen via talk <talk at gtalug.org> wrote:

> Can anyone spot what is wrong?
>
> So I connect to the server with:
> stephen at Avalon:~$ mysql -u root -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 11
> Server version: 5.7.26-0ubuntu0.18.04.1 (Ubuntu)
>
> Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights
> reserved.
>
> Oracle is a registered trademark of Oracle Corporation and/or its
> affiliates. Other names may be trademarks of their respective
> owners.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> mysql>
> ===========================================
> I create grants
> mysql> grant all on rois3324_stephen.*  to
> "rois3324_stephen"@"localhost" with grant option;
> Query OK, 0 rows affected (0.00 sec)
> ===========================================
> I try to see grants
> mysql> "rois3324_stephen"@"localhost"
> ->
>
> It looks like no grants were created. And I get a confirming error from
> PHP.
>
> Can anyone see what is wrong?
>
> Thank you!
> --
> Stephen
> ---
> Talk Mailing List
> talk at gtalug.org
> https://gtalug.org/mailman/listinfo/talk
>

Hey Stephen, you are running mysql 5.7, so I'm going to link some docs and
walk through what I'd do in this situation.  I apologize if you know this
stuff, or have already tried any of it as I'm operating off the set of
information you've provided. :)

First off, I'd run
SHOW GRANTS FOR "rois3324_stephen"@"localhost";
to ensure that the grants you want match the grants it displays.
https://dev.mysql.com/doc/refman/5.7/en/show-grants.html

Second, I'd run
SHOW CREATE USER "rois3324_stephen"@"localhost"\G
to further make sure that things look as they should.
https://dev.mysql.com/doc/refman/5.7/en/show-create-user.html

Next up is to run FLUSH PRIVILEGES;
To ensure that what you've just written to disk is loaded into a freshly
cleared cache.
https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-privileges

I'm unsure of how GRANT works if CREATE USER hasn't already been executed,
but to me, this appears to be a possible case. The above steps should
hopefully reveal any discrepancies.  It's also worth remembering that
'localhost' and 127.0.0.1 are not necessarily treated identically,
depending on your mysql configuration and /etc/hosts files.
https://dev.mysql.com/doc/refman/5.7/en/grant.html
https://stackoverflow.com/questions/19712307/mysql-localhost-127-0-0-1

If you can sanitize the output from the above commands so they don't
contain personal information and send them along, we can help troubleshoot
further, but this is where I'd get started.
-jason
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://gtalug.org/pipermail/talk/attachments/20190529/390d877a/attachment.html>