Home / server / Questions / 980385
In Process
dfrankow
Asked: 2019-08-23 13:16:54 +0800 CST

mysql 5.7 on Amazon RDS getting "table foo doesn't exist"

  • 772

After restoring a mysql backup in AWS RDS, I get "table doesn't exist":

Server version: 5.7.22-log Source distribution
mysql> show tables;
+----------------------------+
| Tables_in_db               |
+----------------------------+
| foo                        |
...

mysql> select * from foo;
ERROR 1146 (42S02): Table 'db.foo' doesn't exist

This error is all around serverfault and stackoverflow. The suggestion is usually to stop servers, examine files in the file system, and so on.

I don't think I have access to the server or the filesystem on AWS. Am I hosed?

How do I even file a ticket against AWS?

EDIT: I removed a stray period above, because it wasn't in the real example. This stray period (now gone) caused someone to do some amazing debugging below, but that's not my issue.

Also, I found out how to file a ticket against AWS here.

mysql

2 Answers

  1. I think I have recreated your problem:

    mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| collorder      |
| collorder2     |
+----------------+
2 rows in set (0.00 sec)

mysql> CREATE DATABASE `test .`;
mysql> USE `test .`;
mysql> CREATE TABLE foo (x int);
mysql> SHOW TABLES;
+------------------+
| Tables_in_test . |
+------------------+
| foo              |
+------------------+
1 row in set (0.00 sec)
mysql> USE test;
mysql> SELECT * FROM foo;
ERROR 1146 (42S02): Table 'test.foo' doesn't exist

    If you don't see what I did, let's try this:

    mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test .             |
+--------------------+

    Now is it obvious?

    Do this:

    SELECT schema_name, char_length(schema_name), hex(schema_name)
    FROM information_schema.schemata;

    to see details.

    • 2

  2. The problem turned out to be that I used some complicated directions to convert MySQL from utf8 (a trap) to utf8mb4, and broke some foreign key constraints by changing the two sides of the constraint to different types while the constraint was disabled. I'm not blaming the directions, but it is a delicate operation. I blame MySQL for a bad default ("utf8" is not all utf8), and no easy process to get to the right place.

    These faulty foreign key constraints made the backup "succeed" but with data that couldn't be restored properly (but with no restore errors until I tried to select from the table).

    • 0