MySQL table is there but it says it doesn't exist

[tangara goh]

Hi expert,

I have a table which I have created.

When I run a query to show all the tables in my database.

It shows the table name.

when I run a select * from tablename, it gives me error 1146.

But, when I tried to insert some dummy data, it said it does not exists.

So, I tried to drop the table.  But, it doesn't work either cos it gives me error :1051 unknown table.

How do I overcome the problem to re-create the table again ?

Mysql 5.7 server on Windows 10 using workbench 6.3.

Thanks.

[Pete Letkeman]

Have you looked into running any of the MySQL tools to repair tables and databases such as this tool
https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html?
From the command prompt you could run:
mysqlcheck --all-databases --auto-repair
You will fine mysqlcheck in the MySQL\bin directory.

There is also a chance that you are have not used the use database command.
Assuming that the name of the database is test and that the name of the table is named test_table what are the results when you try this?
USE test;
SELECT * FROM test_table;
Alternatively you could run this command:
SELECT * FROM test.test_table;

[Jeanne Boyarsky]

Tangara,
Try to look if the table is in a schema. If it was created under one user/schema and you are trying to access it using another, you'd need to write schema.test_table.

[tangara goh]

Pete Letkeman wrote:Have you looked into running any of the MySQL tools to repair tables and databases such as this tool
https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html?
From the command prompt you could run:
mysqlcheck --all-databases --auto-repair
You will fine mysqlcheck in the MySQL\bin directory.

There is also a chance that you are have not used the use database command.
Assuming that the name of the database is test and that the name of the table is named test_table what are the results when you try this?
USE test;
SELECT * FROM test_table;
Alternatively you could run this command:
SELECT * FROM test.test_table;

Hi Pete,

I did as you said but still I am getting the same error

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqlcheck --all-databases --auto-repair
mysqlcheck: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

I am very sure I used the right Mysql command including use the schema name before the select statement.

It did not help.

[Liutauras Vilda]

tangara goh wrote:When I run a query to show all the tables in my database.

It shows the table name.

when I run a select * from tablename, it gives me error 1146.

But, when I tried to insert some dummy data, it said it does not exists.

So, I tried to drop the table.  But, it doesn't work either cos it gives me error :1051 unknown table.

Could you please support these statements with some copied/pasted snippets?

[tangara goh]

Jeanne Boyarsky wrote:Tangara,
Try to look if the table is in a schema. If it was created under one user/schema and you are trying to access it using another, you'd need to write schema.test_table.

Hi Jeanne,

I am not sure what you meant by if the table is in a schema.

I did create tablename  query and I got error code 1813 Tablespace '`hi5s`.`user`' exists.

Kindly advise.

Tks.

[Pete Letkeman]

tangara goh wrote:C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqlcheck --all-databases --auto-repair
mysqlcheck: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

Yes, for mysqlcheck to run correctly you also need to specify the user and password to connect to server.
You can read more about this here https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html.
So the command then becomes mysqlcheck --all-databases --auto-repair --user=root --password=[passwordForRoot]

I agree with Liutauras:

Liutauras Vilda wrote:Could you please support these statements with some copied/pasted snippets?

Here are the commands that you can from the command line program after you are connected to the MySQL server which will provide some insight into this:
show databases; /* command to run */
should produce something like:
+--------------------+ | Database           | +--------------------+ | information_schema | | test               | +--------------------+ 2 rows in set (0.00 sec) use test; /* command to run */
should produce something like:
Database changedshow tables; /* command to run */
should produce something like:
+----------------+ | Tables_in_test | +----------------+ | new_table      | +----------------+ 1 row in set (0.00 sec)select * from new_table; /* command to run */
should produce something like:
+------------------+-----------------------------------------------+--------------------+-----+ | help_category_id | name                                          | parent_category_id | url | +------------------+-----------------------------------------------+--------------------+-----+ |                1 | Geographic                                    |                  0 |     | |                2 | Geographic_2                                  |                  2 |     | |                3 | Geographic_4                                  |                  4 |     | +------------------+-----------------------------------------------+--------------------+-----+ 3 rows in set (0.00 sec)

[Pete Letkeman]

Your output from running the previous commands should be different as you have different database schemas defined with different tables and different data.

[tangara goh]

Hi all,

I managed to find the solution.

Go to the Data folder of MySQL where there is a named folder of your Database schema.

Delete the table.ibd file and then create the table again.

No more error again.

The solution can be found also at

https://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should?rq=1

Thanks to all for the various replies.  I appreciate all the learning provided.  Tks again.

[Tim Holloway]

One "gotcha" about MySQL is that table names are case-sensitive, but actions on tables often are not. Other DBMS's have similar problems, and, unfortunately, the particular quirks of each brand of database are different. Some pay attention to case in table names, but only when the table name is specified in double-quotes, for example.

Where this gets me most often is when I'm working with the tables used by the Bacula backup system. Bacula keeps its catalogs in a MySQL database, but instead of the generally-accepted approach where a table would be named "file" for example, their tables are named with initial capital letters. That is, "File".

I can't count the number of times I've had to edit or re-enter queries because I didn't capitalize the table name correctly.

It sounds like this particular instance was something more serious, but when stuff that's supposed to be "there" isn't there, often it's something like this. On platforms like Windows, where capitalization of the underlying table file names is allowed, but filename searches are case-independent, you can have double the fun.