permission errors on database for full access user

Question

I am getting an error (on MySQL 5.7.25-google in Google cloud while locally MySQL 5.7.29, it works fine)

Google Cloud, I run (NOTE: show tables shows this table existing from this same user!!!)

mysql> show index from customers;
ERROR 1146 (42S02): Table 'authtables.customers' doesn't exist

and locally (same user and password and setup), I get

mysql> SHOW INDEX FROM customers;
+-----------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customers |          0 | PRIMARY                      |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |

Of course, the command SHOW TABLES; works the same on both and shows the CUSTOMERS table in both!!! grrrr.

I followed the accepted answer in the link below EXCEPT for 1 minor detail for setup of 'authservice' user to 'authtables' database...

Mysql adding user for remote access

The ONE detail is my grant statements were authtables.* so that the user had full access to the authtables database and nothing else.

NEXT is my show grants command in GCP and locally which yield the EXACT same result but in case I am missing a typo, here is the GCP then the local result

mysql> show grants for 'authservice'@'localhost';
+---------------------------------------------------------------------+
| Grants for authservice@localhost                                    |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'authservice'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `authtables`.* TO 'authservice'@'localhost' |
+---------------------------------------------------------------------+
2 rows in set (0.08 sec)

local result is

mysql> show grants for 'authservice'@'localhost';
+---------------------------------------------------------------------+
| Grants for authservice@localhost                                    |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'authservice'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `authtables`.* TO 'authservice'@'localhost' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

For completeness... GCP version: Server version: 5.7.25-google-log (Google) local version: Server version: 5.7.29 MySQL Community Server (GPL)

WOW, crap, what is 5.7.25-google-log...ICK. I wonder if it has a bug here.

Also, why is grant have . when I look at my history in mysql to verify, I clearly did authtables.* EVERY time!!!

Then, what happened to the 'authservice'@'%' grant as I don't see that either?

Anyone have any idea how to setup google cloud mysql so I have a user with full access to the database so he is restricted to that database? This seems kind of like some sort of google bug or am I doing something wrong?

thanks, Dean

Answer 1

OMG, google's mysql 'table names' are CASE SENSITIVE!!! what the heck.

I have to do

show index from CUSTOMERS;

OR

SHOW INDEX FROM CUSTOMERS;

BUT THIS DOES NOT WORK

show index from customers;

ouch! posted this to help the next person out hopefully.