New innodb tables stop PSA from loading

[justinrossetti]

I just changed my /etc/my.cnf (mysql database config file)

from the default

to

# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:200M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=256M
set-variable = innodb_additional_mem_pool_size=100M
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=100M
set-variable = innodb_log_buffer_size=50M
#
innodb_flush_log_at_trx_commit=1

by first stoping mysql with "/etc/rc.d/init.d/mysqld stop", then changing the file, the starting mysql with "/etc/rc.d/init.d/mysqld start"

now I get the errors:

https://mysite:8443/login.php3?previous_page=index

ERROR: Unable to query: Can't open file: 'misc.InnoDB'. (errno: 1) 0: /usr/local/psa/admin/plib/common_func.php3:203 psaerror(string "Unable to query: Can't open file: 'misc.InnoDB'. (errno: 1)") 1: /usr/local/psa/admin/plib/common_func.php3:390 db_query(string "select param, val from misc") 2: /usr/local/psa/admin/plib/class.Session.php:113 get_param(string "login_timeout") 3: /usr/local/psa/admin/auto_prepend/auth.php3:139 Session->Session()

and when I try to restart PSA i get the error:

[root@z mysql]# service psa stop1
/usr/local/psa/admin/bin/httpsdctl stop: httpd stopped
Stopping Plesk:                                            [  OK  ]
[root@z mysql]# service psa start
Unable to fetch SpamAssassin config from database: Can't open file: 'sa_conf.InnoDB'. (errno: 1)
Starting psa-spamassassin service:                         [FAILED]
ERROR 1016 at line 1: Can't open file: 'DomainServices.InnoDB'. (errno: 1)
ERROR 1016 at line 1: Can't open file: 'sessions.InnoDB'. (errno: 1)
Mysql error: Can't open file: 'key_history.InnoDB'. (errno: 1)
Processing config directory: /usr/local/psa/admin/conf/httpsd.*.include
/usr/local/psa/admin/bin/httpsdctl start: httpd started
Starting Plesk:                                            [  OK  ]

how do I have plesk use these new innodb files without using a backup or re-configuring my old plesk?

thanks!

 

[justinrossetti]

Ok, i didn't relize was set to use InnoDB. I did not even know what InnoDB was, thought all databases were store in there directory.

So I deleted my old files below

-rw-rw----   1 mysql mysql 209715200 Jan 21 03:07 ibdata1
-rw-rw----   1 mysql mysql 104857600 Jan 21 03:07 ib_logfile0
-rw-rw----   1 mysql mysql 104857600 Jan 21 01:53 ib_logfile1

and now I can't open the PSA database with phpMyAdmin because new InnoDB files exsist.

But I still see PSA files under

/var/lib/mysql/psa

So does my database still exist, if so how do I propergate InnoDB?

If I did indeed delete my PSA database, how can I undelete those 3 files in linux? (i don't know the undelete command)

will continue to research, any help would be great!

 

[justinrossetti]

If something goes wrong when InnoDB attempts to initialize its tablespace or its log files, you should delete all files created by InnoDB. This means all ibdata files and all ib_logfile files. In case you have already created some InnoDB tables, delete the corresponding .frm files for these tables (and any .ibd files if you are using multiple tablespaces) from the MySQL database directories as well. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening.

http://dev.mysql.com/doc/refman/4.1/en/error-creating-innodb.html

wow, let me try that. will report back.
-back
ok, that sucked all my files are *.frm. so no database?

much search for backup ?

wget [url]http://recover.sourceforge.net/linux/recover/download/recover-1.3c.tar.gz[/url]
tar zxvf recover-1.3c.tar.gz
cd recover-1.3c
make
make install
df
recover
/dev/hda3
Getting inodes (this can take some time)...
debugfs 1.35 (28-Feb-2004)
(waited 5 minutes....)
(answered questions....)

did not work

 

[justinrossetti]

from /var/log/mysqld.log

060121 3:09:17 InnoDB error:
Cannot find table psa/upgrade_history 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?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

060121 04:07:02  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
060121  4:07:09  InnoDB: Setting file ./ibdata1 size to 200 MB
InnoDB: Database physically writes the file full: wait...
060121  4:07:56  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait...
060121  4:08:21  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
060121  4:08:49  InnoDB: Started
/usr/libexec/mysqld: ready for connections

ERROR: Unable to query: Table 'psa.misc' doesn't exist 0: /usr/local/psa/admin/plib/common_func.php3:203 psaerror(string "Unable to query: Table 'psa.misc' doesn't exist") 1: /usr/local/psa/admin/plib/common_func.php3:390 db_query(string "select param, val from misc") 2: /usr/local/psa/admin/plib/class.Session.php:113 get_param(string "login_timeout") 3: /usr/local/psa/admin/auto_prepend/auth.php3:139 Session->Session()

so in atempt to make mysql faster by creating a larger InnoDB, i delete PSA database because I didn't relize that was where the data was stored.

and I have zero restore options!! so i am lame. but atleast I understand how it works now right?

 

[justinrossetti]

guess my next question is,

do you have a database schema import script I can use to get pleask running again (with no settings ofcorse)

and if I do run this, whats going to happen to my current clients setup ? will plesk delete all clients and vitural hosting because they are not in the database?

is it safe to reinstall plesk without losing current system configs?

 

[justinrossetti]

after all of that, no commets or help?

 

[AlleyKat]

http://www.pleskaddons.com/forum/4_4_0.html is what solved mysql4-problems for me.