• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 1868
81
Android IntelliJ IDE MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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



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

It did not help.

 
Marshal
Posts: 8856
637
Mac OS X VI Editor BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1868
81
Android IntelliJ IDE MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:
use test; /* command to run */
should produce something like:
show tables; /* command to run */
should produce something like:
select * from new_table; /* command to run */
should produce something like:
 
Pete Letkeman
Bartender
Posts: 1868
81
Android IntelliJ IDE MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic