InnoDB: Have you moved InnoDB .ibd files - Error

sunyl

Active Member
Oct 12, 2004
37
0
156
Hello,

Mysql error logs shows -

InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: MySQL :: MySQL 5.0 Reference Manual :: 13.2.13.3 InnoDB General Troubleshooting
InnoDB: for how to resolve the issue.
110505 15:14:52 InnoDB: Started; log sequence number 3 3095295183
110505 15:14:52 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.92-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL)

InnoDB is enabled on the server and Mysql service restarts fine, but not sure why this error came up suddenly.

Any help would be much appreciated.
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
348
somewhere over the rainbow
cPanel Access Level
Root Administrator
Do you have .ibd files on the machine?

Code:
find /var/lib/mysql -name *.ibd
You'll need to get a list of the existing .ibd files first.

Next, what are the contents of /etc/my.cnf file?

Code:
cat /etc/my.cnf
 

sunyl

Active Member
Oct 12, 2004
37
0
156
Yes, there are some .ibd files in mysql folder

[email protected] [~]# find /var/lib/mysql -name *.ibd | wc -l
4528
[email protected] [~]#

Here is my my.cnf

#DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
#Created with ELS from /http://www.servermonkeys.com
#els-build=5.0
[mysqld]
local-infile=0
datadir=/var/lib/mysql
skip-locking
innodb_file_per_table=1
#skip-networking
safe-show-database
query_cache_limit=2M
query_cache_size=512M ## 32MB for every 1GB of RAM
query_cache_type=2
max_user_connections=250
max_connections=250
interactive_timeout=100
wait_timeout=100
connect_timeout=100
thread_cache_size=256
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=2M
max_connect_errors=50
max_allowed_packet=256M
table_cache=512
record_buffer=2M
sort_buffer_size=8M ## 1MB for every 1GB of RAM
read_buffer_size=8M ## 8MB for every 1GB of RAM
read_rnd_buffer_size=8M ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
myisam_sort_buffer_size=128M
server-id=1
collation-server=latin1_general_ci

[mysql.server]
user=mysql
old-passwords = 1

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=64M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Tristan, what could be the issue as mysql service crashes every now and then..
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
348
somewhere over the rainbow
cPanel Access Level
Root Administrator
An online check for the error indicates that someone moved an .ibd table improperly and caused the error, but I'm not entirely certain how to tell which table is the one that is the issue.
 

sunyl

Active Member
Oct 12, 2004
37
0
156
Also I see lots of process and mysql service is not working -

root 24118 0.0 0.0 37116 6520 ? S 19:15 0:00 /usr/bin/perl /scripts/restartsrv_mysql --check
root 24253 0.0 0.0 30108 5564 ? S 19:15 0:00 /usr/bin/perl /scripts/mysqlconnectioncheck
root 24254 0.0 0.0 21196 904 ? S 19:15 0:00 /usr/bin/mysql
root 25979 0.0 0.0 5904 756 ? S 19:19 0:00 /usr/local/cpanel/bin/mysqlwrap
root 25980 0.0 0.1 58828 12360 ? S 19:19 0:00 /usr/bin/perl /usr/local/cpanel/bin/mysqladmin
root 26952 0.0 0.0 5908 760 ? S 19:20 0:00 /usr/local/cpanel/bin/mysqlwrap
root 26953 0.0 0.1 58828 12356 ? S 19:20 0:00 /usr/bin/perl /usr/local/cpanel/bin/mysqladmin
root 27016 0.0 0.0 5908 760 ? S 19:20 0:00 /usr/local/cpanel/bin/mysqlwrap
root 27017 0.1 0.1 58828 12356 ? S 19:20 0:00 /usr/bin/perl /usr/local/cpanel/bin/mysqladmin
root 27033 0.0 0.0 37116 6520 ? S 19:20 0:00 /usr/bin/perl /scripts/restartsrv_mysql --check
root 27168 0.0 0.0 30108 5560 ? S 19:20 0:00 /usr/bin/perl /scripts/mysqlconnectioncheck
root 27169 0.0 0.0 21196 904 ? S 19:20 0:00 /usr/bin/mysql
root 27288 0.0 0.0 5908 760 ? S 19:21 0:00 /usr/local/cpanel/bin/mysqlwrap
root 27289 0.1 0.1 58828 12356 ? S 19:21 0:00 /usr/bin/perl /usr/local/cpanel/bin/mysqladmin
root 27303 0.0 0.0 5904 756 ? S 19:21 0:00 /usr/local/cpanel/bin/mysqlwrap
root 27304 0.2 0.1 58828 12356 ? S 19:21 0:00 /usr/bin/perl /usr/local/cpanel/bin/mysqladmin
root 27412 0.0 0.0 61180 784 pts/1 S+ 19:21 0:00 grep mysql

We'd to kill the pid's and restart mysql service to make it working.. it happens more offen every now and then.