top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

InnoDB problem on MySQL - table does not exist

+1 vote
543 views

I've restored an MySQL backup from our MySQL server into another server. The backup includes InnoDB tables. After the import, MySQL recognized the innodb tables fine but when I try to do a check table it returns that the table doesn't exists.

Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Innodb engine is enabled..

Which can cause the tables to appears as "non existent", as far as they do really exist?

posted Jul 22, 2013 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

You should always read the fine manual.

You took file-level backups, yes? Did they include the ibdata1 and similar files? Those contain innodb's dictionary - and in default installs also all the actual tables. The database/* files only contain the .frm, for innodb.

If I'm right, you haven't got a backup at all. I'm crossing my fingers that I'm wrong...

answer Jul 22, 2013 by anonymous
Similar Questions
+1 vote
Optimizing InnoDB tables in MySQL?

I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables.

SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND Data_free > 0

After that, I have seen that there are 49 fragmented tables. With one table, I have executed "optimize table table_name;" and "analyze table table_name;". The result is the same, the table continuous fragmented.

Any suggestions? I have followed the mysqltuner recommendations...

+1 vote
How can I work with Big innodb tables?

I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created.

For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions).

So, with 8GB of data in one table, what are your advises to follow? Fragmentation and sharing discarded because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I
can't. For the other hand, maybe the only possible solution is increase the resources (RAM).

Any suggestions?

+2 votes
MySQL Archive Engine

I have a customer who is wanting to use the Archive Engine. I have no experience with this engine, other than what I am been reading. Why would I want to use Archive over InnoDB. They are only going to be placing audit information in the table.

+1 vote
SHOW CREATE TABLE suddenly slow on InnoDB?

I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down?

mysql> SHOW CREATE TABLE my_table;
...
1 row in set (37.48 sec)

We tend to execute many of these statements concurrently, but it's never been a problem until recently. I upgraded the IO subsystem, and our statistics indicate that it's not maxing out IO (at least IOPS).

+1 vote
Mysql Innodb Performance - How to minimise multicolumn index?

Below table contains billion of rows,

CREATE TABLE `Sample1` (
  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` varchar(45) NOT NULL,
  `c3` tinyint(4) DEFAULT NULL,
  `c4` tinyint(4) DEFAULT NULL,
  `time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `varchar_time_idx` (`c2`,`Time`),
  KEY `varchar_c3_time_idx` (`c2`,`c3`,`Time`),
  KEY `varchar_c4_time_idx` (`c2`,`c4`,`Time`),
  KEY `varchar_c3_c4_time_idx` (`c2`,'c3', `c4`,`Time`),
) ENGINE=InnoDB AUTO_INCREMENT=10093495 DEFAULT CHARSET=utf8

Four multi column index created because having below conditions in where

1) c2 and time
2) c2 and c3 and time
3) c2 and c4 and time
4) c2 and c3 and c4 and time

Cardinality wise c2, c3 and c4 are very low. (ex: Out of one million c2, c3 and c4 have 50 unique column in each)

Column time contains mostly unique fields.

Select, insert and update happened frequently.

Tables has 5 indexing fields(4 multi column). Due to this, 1) Insert and update on index fields become costlier. 2) As the table keep on growing (Nearly one billion rows), Index size also increase more rapidly.

Kindly suggest good approach in mysql to solve this use case.

...