Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

by

A little late here but generally I’ve seen this problem occur when you get a ‘tablespace full’ error when running in a ‘innodb_file_per_table’ mode. Without going into too much detail (more here), the database server’s tablespace is defined by the innodb_data_file_path setting and by default is rather small. Even made larger, the ‘tablespace full’ can still occur with larger queries and such (lots of non-table ‘stuff’ is stored in there, undo logs, caches, etc…).

Anyways, I found that if you look in the OS directory where the files-per-table are stored, /var/lib/mysql by default on OSX, /usr/local/var/mysql with homebrew iirc, you’ll find an orphaned tablename.ibd file without it’s normal companion tablename.frm file. If you move that .ibd file to a safe temporary location (just to be safe) that should fix the problem.

$ ls /var/lib/mysql

table1.frm
table1.idb
table2.frm
table2.idb
table3.idb <- problem table, no table3.frm
table4.frm
table4.idb

$ mkdir /tmp/mysql_orphans
$ mv /var/lib/mysql/table3.ibd /tmp/mysql_orphans/

One caveat though, make sure what ever is causing the problem originally, e.g. long running query, locked table, etc… has been cleared. Otherwise you just end up with another orphaned .ibd file when you try a second time.

More Related Contents:

  1. Location of my.cnf file on macOS
  2. brew install mysql on macOS
  3. Setting the MySQL root user password on OS X
  4. How to send email from MySQL 5.1
  5. MySQL user DB does not have password columns – Installing MySQL on OSX
  6. How do you uninstall MySQL from Mac OS X?
  7. Restoring deleted ‘root’ user and password for MySQL
  8. Ruby on Rails 3 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ on OSX
  9. MySQL Install: ERROR: Failed to build gem native extension
  10. libmysqlclient15-dev on macs?
  11. rails + MySQL on OSX: Library not loaded: libmysqlclient.18.dylib
  12. Connect to Docker MySQL container from localhost?
  13. How do you stop MySQL on a Mac OS install?
  14. Reset MySQL root password using ALTER USER statement after install on Mac
  15. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ [duplicate]
  16. PID error on mysql.server start?
  17. How to start MySQL server from command line on Mac OS Lion?
  18. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  19. OSX ld: library not found for -lssl
  20. MySQL data directory location
  21. 1052: Column ‘id’ in field list is ambiguous
  22. How to add levenshtein function in mysql?
  23. Is the primary key automatically indexed in MySQL?
  24. Is there ANY_VALUE capability for mysql 5.6?
  25. MySQL SELECT DISTINCT multiple columns
  26. Fetching linked list in MySQL database
  27. How can I correct MySQL Load Error
  28. ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
  29. How to format number with “.” as thousands separator and “,” as decimal separator?
  30. Count duplicates records in Mysql table?

Leave a Comment