MySQL Forums
Forum List  »  InnoDB

problems with an innodb table
Posted by: markus fröhlich
Date: February 06, 2013 04:01AM

i've a problem with a innodb table.
when i connect to the database, i can list all tables by using "show tables".
but when i make a "select" or "describe table", i get the error "ERROR 1146 (42S02): Table 'xxx' doesn't exist"

here the output of mysql error log:

130131 21:34:18 [ERROR] Cannot find or open table mydb/xxx from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

so i tried to repair the table:

turned off mysql

copied binary files of the table from production to backup

copied old table xxx.frm file from an old db-server to the prod-db

started mysql

still the same error

mysql stop

copied back the xxx.frm from backup

mysql start

again still same error


the next try was:
i made a dump of the whole DB with excluding the table xxx.
i created a new empty db and imported the dump.
from the old db server i only made a dump of the table xxx and also imported it on the new db server in new the db.
(on the old database server i can make a select and describe of table xxx)
"show tables" worked - but when i made a "select" - mysql said mysql server gone away - and the table isn't shown any more when i run "show tables" - the table isn't available in the output of show tables any more, but on the filesystem the innodb-data-file xxx.ibd exists.

has anyone a idea how to fix this?

mysql version 5.1.41.

my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
relay-log-index=/mnt/mysql/ip-10-224-110-15-relay-log.index
relay-log=/mnt/mysql/ip-10-224-110-15-relay-bin
user = mysql
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
port=3306
old_passwords=1
skip-locking
key_buffer = 128M
max_allowed_packet = 16M
table_cache = 256
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size= 24M
max_connections = 500
log-slow-queries = /var/log/mysql/mysqlslow.log
long_query_time = 5
log_queries_not_using_indexes
log-error=/var/log/mysql.err
thread_cache_size=0
expire_logs_days=15
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_file_per_table
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 100M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_support_xa = 1
innodb_flush_method=O_DIRECT
innodb_fast_shutdown = 1
log-bin=/mnt/mysql-binlogs/mysql-bin
server-id=1359790395

[mysql.server]
user=mysql
basedir=/usr
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

Options: ReplyQuote


Subject
Views
Written By
Posted
problems with an innodb table
2902
February 06, 2013 04:01AM
993
February 06, 2013 10:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.