1

Okay, I'm aware that the MySQL Error 1050 is a common one. I've spent the past hour and a half reading other questions and answers and trying lots of things, but for some reason I can't get mysql back working again.

  1. MySQL does not show the table.
  2. SELECT * FROM `table` yields Error 1146: Table doesn't exist.
  3. DROP TABLE `table` yields Error 1051: Unknown table.
  4. DESCRIBE TABLE yields Error 1146.

  5. CREATE `table` yields Error 1051: Table already exists.

  6. SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = 'table' yields an empty result set.

  7. Ran the FLUSH TABLES command, which executed successfully, but with the result of 0 row(s) affected.
  8. REPAIR TABLE yields an Operation failed message stating Table doesn't exist

What the...?

So, frustrated, I try deleting the whole database schema and I get 'Lost connection to MySQL server during query', 2013.

I feel like something got corrupted, so I tried going to /usr/local/mysql/data/ and deleted the folder. The schema disappeared from MySQL Workbench. Recreated the schema and tried syncing my database model and it says the table already exists.

I don't understand what is going on here and I don't understand what I can do to fix it. I just want to get back to work!

jeffjenx
  • 17,041
  • 6
  • 57
  • 99
  • it's probably still listed in information_schema and/or mysql dbs. – Marc B Jun 10 '14 at 14:27
  • @MarcB, I've queried the information_schema.tables and am returned an empty result set. In fact, none of the schema's tables show up in there after I deleted the schema folder and tried resync'ing my model. – jeffjenx Jun 10 '14 at 14:34
  • try issuing the FLUSH TABLES command from the command line or your MySQL tool. – Len_D Jun 10 '14 at 14:39
  • The FLUSH TABLES command executes with the response `0 rows affected.` and the error stating that the table exists persists. – jeffjenx Jun 10 '14 at 14:40
  • You might want to try this: http://stackoverflow.com/a/3302837/899126 – Chris Forrence Jun 10 '14 at 14:41
  • @ChrisForrence Thanks for the suggestion, I actually have read that answer and attempted the solution, to no avail. – jeffjenx Jun 10 '14 at 14:43
  • @MrSlayer - Ah, worth a shot. Had you also read [this](http://stackoverflow.com/q/10538908/899126) one? In any case, best of luck! – Chris Forrence Jun 10 '14 at 14:44
  • check things like `mysql.db` as well. information_schema is a live view of current table stats, but it's a virtual db, and "permanent" data is kept in various tables in the `mysql` db. – Marc B Jun 10 '14 at 14:46
  • @MrSlayer what about you create a dump of your database with `mysqldump -u root -p > db.sql` command and open the file and look that table to see where it is on the file! – Jorge Campos Jun 10 '14 at 14:47
  • @JorgeCampos the dumped field doesn't show anything except a bunch of comments. MarcB, what is mysql.db and how to I "check" it? – jeffjenx Jun 10 '14 at 14:51
  • @MrSlayer the normal dumped file starts with a bunch of comments yes, but it should have the entire databases creation and inserts scripts inside it. Thats odd that your file doesn't. – Jorge Campos Jun 10 '14 at 14:55
  • Well, I've been doing a bunch of tests and deleted the folder from /mysql/data, so I'm not surprised. – jeffjenx Jun 10 '14 at 14:57
  • Your db is corrupt. Clean it up http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine – bancer Jun 10 '14 at 15:01

2 Answers2

0

My solution was to export all of the other databases and delete MySQL entirely.

From: http://johnmcostaiii.net/2011/removing-mysql-osx-lion/

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

# Edit the following file, removing the line `MYSQLCOM=-YES-`.
# you may need sudo for write privileges to edit the file
# TIP: when using vim, use `dd` to delete the line and then `:wq` to save
#      the file
sudo vim /etc/hostconfig   # remove the line MYSQLCOM=-YES-

After reinstalling MySQL, the startup utilities, the preferences pane, and Workbench, I was able to successfully synchronize my database model.

@bancer's link in the comments may provide an alternative solution that doesn't require deleting MySQL entirely, but I'll be honest, it was quite overwhelming to look at that answer.

jeffjenx
  • 17,041
  • 6
  • 57
  • 99
0

In the database folder I copied another .frm file and gave it the name of the problem table. Then ran query DROP TABLE. Problem solved!

David T D
  • 1
  • Welcome, and thank you for your answer. Please consider that SO is not a forum, but a knowledge database built on top of real problems. You should consider improving your answer so that other users having the same problem and landing here can understand and apply your solution to their situation. Happy programming! – Cilyan Mar 28 '15 at 03:12