ERROR 1146 (42S02): Table 't.test_0' doesn't exist comes out, but the table is in the table list

Question

I am a beginner of mysql. I installed the mysql 8.0.16. Then I log in with root, created a database. Then I tried to use Sequel pro to create a table. After I finished that, ERROR 1146 (42S02) Table 'securities_master.exchange' doesn't exist comes out. I tried many way to fix it, but nothing worked. I really don't know how to deal with it.

Server version: 8.0.16 MySQL Community Server - GPL System: MacOS 10.14 At first, I use homebrew to install mysql. Then the problem comes out. I tried to uninstall it and install with DMG. Then, I tried to type database name and table name with lowercase. I deleted the ib_logfile0 and ib_logfile1. I restarted the server. I have dropped the database and create again. They didn't work.

'''

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database t;
Query OK, 1 row affected (0.01 sec)

mysql> use t;
Database changed
mysql> CREATE TABLE ‘test’(
    -> ‘id’ int(11) unsigned NOT NULL AUTO_INCREMENT, 
    -> ‘name’ varchar(10) NOT NULL,
    -> PRIMARY KEY (‘id’)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------+
| Tables_in_t |
+-------------+
| ‘test’      |
+-------------+
1 row in set (0.00 sec)

mysql> desc test;
ERROR 1146 (42S02): Table 't.test' doesn't exist
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.00 sec)

'''

Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Martin, Jul 10 '19 at 11:51

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

You appear to be getting single quotes ' and backticks ` confused. Your quotes as shown are not recognised by MySQL as quotes, instead they're simply characters.

The quotes you appear to be using are actually not even standard single quotes but are mixed quotes ‘ and ’ .

You show:

+-------------+
| Tables_in_t |
+-------------+
| ‘test’      |
+-------------+

The name of the table is ‘test’ not test.

Therefore

ERROR 1146 (42S02): Table 't.test' doesn't exist

Do it this way:

mysql> desc `‘test’`;

Alternatively, create the table from scratch using the correct quotation symbols -- backticks:

mysql> CREATE TABLE `test`(
    -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
    -> `name` varchar(10) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> );

Please also not that name is a MySQL keyword and is not advisable as a column name.

The backtick is usually on the key to the immediate left of the number 1 at the top left of the keyboard.

Thanks a million. You really solved my problem. I worked on it for a whole day. — JJDon, Jul 10 '19 at 12:17

ERROR 1146 (42S02): Table 't.test_0' doesn't exist comes out, but the table is in the table list

1 Answers1

The backtick is usually on the key to the immediate left of the number 1 at the top left of the keyboard.