Linked Questions

0
votes
1answer
10k views

How to resolve “InnoDB data dictionary has tablespace id N” [duplicate]

I recently messsed up a migration to table-per-file for my dbs. In the end I (thought) I dropped all dbs, deleted any left over files, and then started over from scratch, importing only dbs I needed ...
35
votes
10answers
229k views

restore table from .frm and .ibd file?

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-...
51
votes
3answers
66k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
7
votes
6answers
15k views

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. ...
6
votes
3answers
16k views

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log). I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log). The DB on S1 was using one data file (...
5
votes
3answers
25k views

ERROR Cannot find or open table?

MySQL version: 5.5.24 Due to the following problem: mysql> desc reportingdb.v3_zone_date_cpm7k; ERROR 1146 (42S02): Table 'reportingdb.v3_zone_date_cpm7k' doesn't exist /var/log/mysqld....
9
votes
1answer
20k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
3
votes
2answers
13k views

Table compression in InnoDB?

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? ...
2
votes
2answers
9k views

remove duplicate rows in mysql table that does not contain primary key

I have a table item having just one column name:- name ----- toys shirt mobile Shirt speaker Toys .... .... I am trying to delete duplicate rows except one duplicated record with the help of below ...
2
votes
3answers
2k views

Truncate temporary table commits transaction?

Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction? ...
4
votes
1answer
2k views

How do you extend the tablespace when using innodb_file_per_table?

With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc. With the option on, how do you control growth of the files? Do ...
1
vote
1answer
897 views

Restore the data from Snapshot Backup when the data spans over multiple directories?

Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF. We have done some testing work on one of the slave say server A.and it takes a huge amount of ...
0
votes
0answers
40 views

How do I use the .ibd/.frm files to recover an InnoDB table that went missing when MySQL crashed during OPTIMIZE TABLE?

I have seen How to Recover an InnoDB table whose files were moved around but I am not sure if it's relevant, since MySQL renamed the files and there was no tablename.frm afterwards. I ran OPTIMIZE ...