GCP MySQL 5.7.25-google-log 索引查询异常求助

Hey Dean, let's dig into the issues you're facing with Google Cloud SQL's MySQL 5.7.25-google-log instance—these problems are pretty common with managed MySQL services, so let's break them down one by one:

1. The Mysterious "Table Doesn't Exist" Error (Even Though SHOW TABLES Sees It) #

The biggest red flag here is the case difference between your table name (CUSTOMERS uppercase) and your query (customers lowercase). Managed MySQL services like Google Cloud SQL often have different case-sensitivity settings than local instances.

Here's what's happening:

• Your local MySQL (running 5.7.29 Community) probably uses lower_case_table_names=0 (case-sensitive, matching your filesystem), so it treats CUSTOMERS and customers as interchangeable.
• Google Cloud SQL for MySQL typically defaults to lower_case_table_names=2—this means table names are stored exactly as you create them, but queries can fail if the case doesn't match depending on your collation setup.

Quick Fix: #

Try running your index query with the exact uppercase table name:

SHOW INDEX FROM CUSTOMERS;

Long-Term Fix: #

• Standardize on lowercase table names for all your queries and schema creation—this avoids cross-environment case issues entirely.
• Verify the case-sensitivity setting on both environments with:

  SHOW VARIABLES LIKE 'lower_case_table_names';
  

2. Why Your 'authservice'@'%' Grant Isn't Showing Up #

Google Cloud SQL doesn't let you manage remote users (% host) entirely through vanilla MySQL commands—there's a layer of managed user control via the Google Cloud Console or gcloud CLI that you're missing.

Correct Way to Set Up Single-Database Full Access for Remote Users: #

1. Head to your Cloud SQL instance in the Google Cloud Console, navigate to the Users tab.
2. Click Add user account:
   • Enter authservice as the username
   • For the host, choose either a specific IP range (more secure) or "Allow any host" (which maps to %)
   • Set a strong password
3. Once the user is created, run this SQL command to restrict them to only the authtables database:

   GRANT ALL PRIVILEGES ON `authtables`.* TO 'authservice'@'%';
   FLUSH PRIVILEGES;
   

   Note: The SHOW GRANTS command in Cloud SQL might not display grants for console-created users immediately, but the permissions will still apply as expected.

3. Is This a Bug in 5.7.25-google-log? #

While version-specific quirks are possible, the case sensitivity and managed user restrictions are far more likely than a bug. That said, if you rule out those factors and the issue persists, consider upgrading your Cloud SQL instance to a newer 5.7.x release (like 5.7.42-google) to eliminate any known bugs in the older version.

Extra Checks to Confirm: #

• Make sure you're connected to the authtables database when running your index query—run SELECT DATABASE(); to verify. It's easy to accidentally be in the wrong database!
• Double-check the table's existence with this exact-case query:

  SELECT * FROM information_schema.tables WHERE table_schema = 'authtables' AND table_name = 'CUSTOMERS';
  

内容的提问来源于stack exchange，提问作者Dean Hiller