Then in the /var/lib/mysql/database_name directory i did the following as root acknowledging the overwriting of innodb_table.ibd causing us issues cp -a innodb_m innodb_m In order to fix this, what i did was first create table innodb_table2(`id` int(10) unsigned NOT NULL) Please DISCARD the tablespace before IMPORT MariaDB > alter table innodb_table discard tablespace ĮRROR 1146 (42S02): Table 'database_name.innodb_table' doesn't existĬreate table also fails like so: MariaDB > create table innodb_table(`id` int(10) unsigned NOT NULL) ĮRROR 1813 (HY000): Tablespace for table '`database_name`.`innodb_table`' exists. MariaDB > drop table innodb_table ĮRROR 1051 (42S02): Unknown table 'database_name.innodb_table' With drop table not working as well as alter table. already exists though the corresponding table did not exist in the InnoDB data dictionary. Your mileage and errors may vary but the main one i assume is. You can resolve the problem by removing the file. 9:43:58 140323764213504 InnoDB: The file './database_name/innodb_table.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. This is exactly what i did in mariadb 10.2.16 on fedora when i had a table that showed exactly the same errors in the log file i suppose. Otherwise you just end up with another orphaned. One caveat though, make sure what ever is causing the problem originally, e.g. $ mv /var/lib/mysql/table3.ibd /tmp/mysql_orphans/ ibd file to a safe temporary location (just to be safe) that should fix the problem. 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.).Īnyways, 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 m file. 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. resetting my local MySQL server, or maybe user permission rights might have to do with it, but I am just hypothesizing here.Ī 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.
I suspect that re-booting my laptop, i.e. Is it possible that there is some type of remnant of this table at a different place where the DISCARD query isn't checking? And does anybody have an idea what could trigger all that - completely randomly as it seems?Īs I said, I'm new to the subject and pretty much clueless. So that means that I am advised to discard the table space but when I try to do so the table does not exist. I get the following error messages: Error : Unknown table 'database.temp'Įrror : Table 'database.temp' doesn't exist However, if I try to drop the table, or try to discard the tablespace for this table, using DROP TABLE temp Please DISCARD the tablespace before IMPORT. named "temp", that was previously there, I get the following error message: Error : Tablespace for table '`database`.`temp`' exists. So far so good - here comes the good part: When I try to run queries using these tables, Navicat then warns me that the particular table does not exist. The error I get is that after running and managing my database just fine for a couple of days/weeks something triggers to (it appears incompletely) delete some of the tables I created using queries from within Navicat. I am running a local server of MySQL 5.6.10 on MacOS 10.8.3 and manage my database via Navicat essentials for MySQL. I am fairly new to MySQL and I am getting a pretty interesting error on which I cannot find any help via google and the stackoverflow search.
0 kommentar(er)
