Help :( - Plesk admin died: ERROR: Unable to query: Can't open file: 'sessions.ibd' (

[WebDork]

Just in case anyone else has encountered similar...

I have been trying to optimise my box. When I went to bed all seemed fine, when I woke up and tried to enter PLESK admin I got:

ERROR: Unable to query: Can't open file: 'sessions.ibd' (errno: 1) 0: /usr/local/psa/admin/plib/common_func.php3:203 psaerror(string "Unable to query: Can't open file: 'sessions.ibd' (errno: 1)") 1: /usr/local/psa/admin/plib/class.Session.php:363 db_query(string "select * from sessions where sess_id="28pf9dra2iom87kc8f01530oi4" and click_time >= NOW() - INTERVAL 1800 SECOND") 2: /usr/local/psa/admin/plib/class.Session.php:302 Session->isExpired() 3: /usr/local/psa/admin/auto_prepend/auth.php3:170 Session->init(string "203.217.37.142")

I think I was playing with my.cnf last. I have tried different my.cnf to fix but no luck. Here is current content in case it is relevant.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-locking
#skip-innodb
skip-grant-tables
innodb_data_file_path=ibdata1:10M:autoextend
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=200
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=8M
join_buffer=1M
max_allowed_packet=8M
table_cache=1024
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=32M
#log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

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

[mysqldump]
quick
max_allowed_packet=8M

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

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=8M
write_buffer=8M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout

I tried to reload PLESK via the /var/lib/psa/dump but when I restored
these again there seems to be lots of tables missing.

I think the problem is MyISAM / InnoDB. When I look at the database in phpmyadmin it seems a mess of the two.

Any ideas greatly appreciated.

 

[wagnerch]

1. skip-grant-tables is a *really* bad idea. You shouldn't use this option unless you know what it does.

2. it sounds like you may have totally wiped out your ibdata1 file, and have orphaned .frm files. See http://dev.mysql.com/doc/refman/4.1/en/innodb-troubleshooting-datadict.html for some suggestions.

 

[WebDork]

Thanks Wagnerch - no - Im not deliberatley using grant-tables - was just modifying bits and pieces to try and cope with load better.

I have 2Gb ram and a heavily loaded box running a couple of large forums. Any suggestions on my.cnf ?

I'll check out the link and hope it helps - thanks.

Originally posted by wagnerch
1. skip-grant-tables is a *really* bad idea. You shouldn't use this option unless you know what it does.

2. it sounds like you may have totally wiped out your ibdata1 file, and have orphaned .frm files. See http://dev.mysql.com/doc/refman/4.1/en/innodb-troubleshooting-datadict.html for some suggestions.

 

[servertune]

This message means that psa data in /var/lib/mysql/psa is corrupted and you need to retrieve a copy form the backup at: /var/lib/psa/dumps
gunzip one of the *.gz files and insert tables back to the psa
database and you should be good.

 

[WebDork]

Originally posted by servertune
This message means that psa data in /var/lib/mysql/psa is corrupted and you need to retrieve a copy form the backup at: /var/lib/psa/dumps
gunzip one of the *.gz files and insert tables back to the psa
database and you should be good.

Thanks servertune. I tried that before, but basically gave me same error. I tried it again however and basically it would keep telling me certain tables were missing. So I added them manually. Had to to do this for maybe 5 or 6 tables. Would click something in plesk and it would tell me table was missing. Any reason why it wouldnt import them all reliably ? I turned off PSA. Im just worried one that I havent spotted yet will bite me down the track

 

[servertune]

Repairing a table is not the best option. Any way, run these 3 commands at the prompt (in this order):

gunzip /var/lib/psa/dumps/mysql.preupgrade.dump.gz

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e"DROP DATABASE psa;"

mysql -uadmin -p`cat /etc/psa/.psa.shadow` < /var/lib/psa/dumps/mysql.preupgrade.dump

See if that helps

 

[WebDork]

Any way, run these 3 commands at the prompt (in this order):

Thanks Andy, ended up being a corrupt table missing some data. I fixed it and all seems to be fine again.

I removed the skip-grant-tables line from my.cnf

Is there anything anyone else would recommend or change with my current config ? Remember I have 2Gb Ram.

 

[wagnerch]

I would definately tune your query cache, since it sounds like:

a) you have lots of memory (actually you mentioned it twice )
b) you are running mysql 4.0 or later


The query cache may have a big pay off, or not. It depends if your database is doing heavy writes then the query cache may just be flushed too frequently.

http://www.mysql.com/news-and-events/newsletter/2003-01/a0000000108.html


Before tuning too many things, I would recommend profiling what your server is doing *now*. Take a look at
mysqlreport, and generally at http://hackmysql.com/. This link will help you understand what is coming out of mysqlreport.

Another decent tool is mytop, which is useful for watching the server live.


I must say I am not a huge fan of mysql, it has some very very awkward things that just seem strange to me for good database design. The lack of a real buffer cache is very disturbing to me.