Today I was faced with migrating my P202 installation from one host to another. As normal, I ran the mysqldump command on the old server to dump the database and scp’d it over to my new host – went to import and I got a bunch of errors like this;
ERROR 1016 (HY000): Can't open file: './202_SITENAME/202_locations_block.frm' (errno: 24)
ERROR 1016 (HY000): Can't open file: './202_SITENAME/202_locations_block.frm' (errno: 24)
ERROR 23 (HY000): Out of resources when opening file './202_SITENAME/202_locations_block#P#p413.MYD' (Errcode: 24)
WTF?
I have plenty of RAM, and resources on my new box so this was a little confusing. Turns out that you need to make a couple adjustments in order to re-import the database on Linux servers. Here’s what I did to make it import properly.
first, you have to edit the security limits for mysql on your NEW server, especially if you have a high number of connections to databases. This is done like this;
# nano /etc/security/limits.conf
and add these lines to the bottom
mysql soft nofile 24000
mysql hard nofile 32000
Then, you’re going to have to edit your my.cnf, which for me on Ubuntu is done like this;
# nano /etc/mysql/my.cnf
and add this line in the tuning section
open_files_limit = 20000
Now, go back to your original server, and re-dump the database like this;
# mysqldump -C -i –single-transaction -u root -p DATABASENAME >>DATABASENAME.sql
Then, scp the file over to your new server like this;
scp DATABASENAME.SQL username@hostname.com:
Once the file has transferred, ssh over to your new host and issue the following commands (for Ubuntu, may be different on other Linux flavors)
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
# mysql -u root -p
<enter password>
then, once you are inside the MySQL command line you’re going to want to select your database like this
> use DATABASENAME;
then, you’re going to want to import your database from the old server
> source DATABASENAME.SQL;
wait a few seconds, you shouldn’t see any errors and then you’re done. You can exit the mysql command line now;
> exit;
Now you should have a perfectly working P202 installation, on your new server migrated from an old one.
Incase you’re wondering, no I did not go remove these settings – figured higher limits were good anyway – if they cause a problem on my server I’ll update this post for y’all.
Hope this helps someone else as I had to do a couple hours of searching, and testing to get all this sorted out.
Comments
Comment by ceedawg on 2010-12-14 03:51:02 -0500
Excellent! I had a very similar issue when trying to optimize my p202 tables and whenever I’d restart mysqld I would get this error msg on the same 202_locations_block — your edits resolved this.
202 needs some optimizing every once in a while, phpMyAdmin can do this very easily.
OPTIMIZE TABLE '202_aff_campaigns', 'etc', 'etc'