MYSQL下的幽灵表怎么把它找出来(转)
2014年04月03日 01:19 存在: 11 yrs
转自:http://www.iteye.com/problems/964
MYSQL下有个库`是直接从别的机器下复制文件过来的`
在命令行MYSQL里可以show tables到全部表;
但是有些表select时说不存在.
从GUI里进去就看不到这些不存在的表``
这是怎么回事``
我的权限是ROOT``
把原库中的ibdata1复制过来MYSQL可以启动`但没有什么作用```还是提示不存在``
要是把``ib_logfile1``和ib_logfile0也复制过来```MYSQL就启动不起来``
这两个机器上的ibdata1是一样大的`10M``
ib_logfile一个是5M多`一个是10M多```
试验过`5.0.51`4.1.22`4.1.7`三个版本的MYSQL都不行``
在这三版的err文件里都有`
Cannot find table XXXX/classdb 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?
See dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
how you can resolve the problem.
我去上面说的页面看了看也没发现什么有用的信息``
mysql> show variables like '%innodb%';
+---------------------------------+------------------------+
¦ Variable_name ¦ Value ¦
+---------------------------------+------------------------+
¦ have_innodb ¦ YES ¦
¦ innodb_additional_mem_pool_size ¦ 3552256 ¦
¦ innodb_autoextend_increment ¦ 8 ¦
¦ innodb_buffer_pool_awe_mem_mb ¦ 0 ¦
¦ innodb_buffer_pool_size ¦ 173015040 ¦
¦ innodb_data_file_path ¦ ibdata1:10M:autoextend ¦
¦ innodb_data_home_dir ¦ ¦
¦ innodb_fast_shutdown ¦ ON ¦
¦ innodb_file_io_threads ¦ 4 ¦
¦ innodb_file_per_table ¦ OFF ¦
¦ innodb_flush_log_at_trx_commit ¦ 1 ¦
¦ innodb_flush_method ¦ ¦
¦ innodb_force_recovery ¦ 0 ¦
¦ innodb_lock_wait_timeout ¦ 50 ¦
¦ innodb_locks_unsafe_for_binlog ¦ OFF ¦
¦ innodb_log_arch_dir ¦ ¦
¦ innodb_log_archive ¦ OFF ¦
¦ innodb_log_buffer_size ¦ 2097152 ¦
¦ innodb_log_file_size ¦ 87031808 ¦
¦ innodb_log_files_in_group ¦ 2 ¦
¦ innodb_log_group_home_dir ¦ .\ ¦
¦ innodb_max_dirty_pages_pct ¦ 90 ¦
¦ innodb_max_purge_lag ¦ 0 ¦
¦ innodb_mirrored_log_groups ¦ 1 ¦
¦ innodb_open_files ¦ 300 ¦
¦ innodb_table_locks ¦ ON ¦
¦ innodb_thread_concurrency ¦ 8 ¦
+---------------------------------+------------------------+
27 rows in set (0.00 sec)
问题补充:
执行SHOW ENGINE INNODB STATUS and the InnoDB Monitors 后报错```
再说明一下`这个库是在一个崩掉的硬盘里救出来的``
问题补充:
.frm .isd .ism .myd .myi文件都在`
问题补充:
变着花的复制办法我差不多都试了````都不行```
这个库以前的MYSQL版本可能是4.0.13``这是刚在原服务器里找到的```
我一会装这版再试试``
问题补充:
白搞一半天`就是版本问题```在4.0.13下一点事没有