Innodb errors

Databases get corrupted for many reasons. In my case our SAN (or your hard drive) went down during writes to the database from a power failure. InnoDB corruption can cause all of the databases running on that server to become inaccessible.

First of all, I have to say what everyone else out there says, “Backup, Backup, and Backup”. Make sure that you maintain a good backup schedule by running a dump script to get your dbs back in order in case of serious corruption or data loss (which is inevitable).

MySQL Won’t Start: Now, that being said let’s get to how you can restore your InnoDB database.

My server-side set-up is Centos 5.x and MySQL 5.5 running cPanel 11.34. Ubuntu and Debian flavors will differ and good command-line knowledge is helpful here. You will not be able to do any of these steps without shell access to your server via SSH.  You also will not be able to repair or check your tables via phpmyadmin, WHM or cPanel.

InnoDB corruption can cause all of the databases running on that server to be inaccessible.  Without going into the technical reasons as to why that is, you will find that your databases are unavailable and your MySQL server just won’t start.  You may get a simple response from the MySQL server like:

Starting MySQL..The server quit without updating PID file (/var/lib/mysql/my.server.com.pid).[FAILED] or MySQL server PID file could not be found!

These errors can only usually be found when trying to re-start the MySQL server. You must now dig deeper and this where checking the MySQL error log for “my.server.com” (in the above example) will somewhat give you an idea as to what to do next. First we have to get to the server and this is where command-line experience comes into play. You should have sudo or root access to your server running MySQL. Let’s take a look at the MySQL error log for my.server.com

ssh user@my.server.com
tail -500 /var/lib/mysql/my.server.com.err

130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin ‘FEDERATED’ is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): ’0.0.0.0′; port: 3306
130306 22:02:18 [Note] – ’0.0.0.0′ resolves to ’0.0.0.0′;
130306 22:02:18 [Note] Server socket created on IP: ’0.0.0.0′.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: ’5.5.30-cll’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)
130306 22:02:19 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:02:19 UTC – mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Steps to get it back up.

1. Stop mysqld.
2. Backup /var/lib/mysql/ib*
3. Add the following line into /etc/my.cnf

innodb_force_recovery = 4

4. Restart mysqld.
5. Dump all tables:# mysqldump -A > dump.sql
6. Drop all databases which need recovery.
7. Stop mysqld.
8. Remove /var/lib/mysql/ib*
9. Comment out innodb_force_recovery in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/hostname.com.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < dump.sql

**Hint : A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';

Share this:

Manually removing the domains from plesk – when plesk frontend don’t work

Could be a bug with Plesk 9.5 version. But Plesk 9.5 is giving issues once in a while in deleting the domains which are “partially” created.

ISSUE

DNS zones are never created, and the DBs are not properly updated. Even after reconfiguring the domains using websrvmng.exe –reconfigure-vhost –vhost-name =”domain.com” it couldn’t be deleted. Shows the message “Information: Domains are now removed”, but in real, the domain will be still shown under the domains list.

And the domain will be in suspended state. Unsuspension of the domain will result in an error message “DNSZone::Table::select() failed: no such row in the table”

Solution for the above issue is explained at http://www.supportsages.com/blog/2009/08/plesk-account-unsuspension-tableselect-failed-no-such-row-in-the-table/

However even after fixing the DNS like this, the issue cycles.

Removal of domain results in suspension of domain -> Unsuspension of domains results in above error -> Fixing the error allows to unsuspend and Removal of domain results in suspension of domain

SOLUTION

So solution I used to solve was to manually remove the domain related entries from plesk database, from the file system and also from the email services. Get into MySQL or whichever DB plesk is using. For finding the current DB, use Plesk Reconfigurator and then follow the parallels KB found at http://kb.parallels.com/en/3472

The instructions below assumes that you are using Mailenable and Plesk vhosts directory is at “C:\Inetpub\Vhosts” . This location can be at D:\ or E:\ even. Also, I hereby assume that there are no two domains which can match the mysql LIKE query I used below i.e There are no domains like 1domain.com , 2domain.com along with actual domain.com. If there is one, please use your “brain” to find the right domain and the IDs associated with it. When in doubt, please comment.

Disclaimer : If you mess up the database neither me or SS will be responsible for it

Let’s first gather all the ‘id”s first, like dom_id, sys_user_id, id etc which will be helpful in the deletion of database entries relating to the domain name which you want to removed.

1. Getting the IDs.

Domain ID – dom_id or id


 mysql> SELECT id FROM domains where name=’domain.com’

We are assuming that dom_id you got as a result of above command is 8880

Database ID – db_id


mysql> select id FROM data_bases where dom_id=(SELECT id FROM domains where name=’domain.com’);

We are assuming that db_id you got as a result of above query is 1433. Please note that you could get more than one result. Please save all the db_id and re-execute the sql query for each db_id.

System User ID – sys_user_id


mysql> select id FROM sys_users where home like '%domain.com%';

We are assuming that sys_user_id you got as a result of above command is 8843

Let’s start the actual deletion process

Shall we backup ? Better be safe!


C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\bin>mysqldump -P8306 -uadmin -p psa > c:\psa.sql

You are now ready to delete. Please execute the follow sql commands in mysql prompt


delete from db_users where db_id='1433';
delete from data_bases where dom_id='8880';
delete from mail where id='8880';
delete from subdomains where id='8880';
delete from dns_recs where id='8880';
delete from domains where id='8880';
delete from domain_aliases where dom_id='8880';
delete from hosting where sys_user_id='8843';
delete FROM sys_users where id='8843';
delete from dns_zone where displayName=’domain.com’;

Database entries are deleted. Now it’s time to remove the users and files from the filesystems, IIS, Server, Mail server, DNS server etc.

Remove the domain from inetpub -> vhosts
Remove from Mail Enable Administrator. If it is SmarterMail, follow other steps.
Start => run => mailenable.msc => Post Offices and remove it.
Remove Sites “domain.com” and “Application Pool” from Internet Information Services manager. If IIS7, use command line option => %systemroot%\system32\inetsrv\APPCMD.EXE delete site “domain.com” .
Remove from Computer management Console -  System Tools > Local Users and Groups > Users. You must remove references to the domain for Plesk Domain User and Plesk IIS User as well.
Remove DNS entry – depending on the DNS server.
Finally,


cd %plesk_bin%
domain.exe -r domain.com

These steps should help remove the domain manually from the plesk. Please note that this article is based on the second attempt and could be buggy. I assume some “common sense” when running these commands. Understand the commands and each step before you actually execute it. Good luck. Don’t MESS up!!

Linux Commands to Monitor Memory usage

1) vmstat: Monitor virtual memory
    Commands
    ----------------------
      vmstat
      vmstat -s -S M

2) top command

3)pmap Display/examine memory map and libraries (so). Usage: pmap pid

4)sar -B Show statistics on page swapping.

5) time -v date Show system page size, page faults, etc of a process during execution. Note you must fully qualify the command as "/usr/bin/time" to avoid using the bash shell command "time".

6)free Display amount of free and used memory in the system.

7)ps aux

To see only the memory resources occupied by each category of processes, such as Apache httpd, MySQL mysqld or Java, use the following command:

ps aux | awk '{print $4"\t"$11}' | sort | uniq -c | awk '{print $2" "$1" "$3}' | sort -nr

Mysql:Give Root User Logon Permission From Any Host
------------------------

 Note that this is Not very secure, and should only be used for a local development box .

To configure this feature, you’ll need to update the mysql user table to allow access from any remote host, using the % wildcard.

Open the command-line mysql client on the server using the root account. There is a chance getting Access denied error.
-------------------

[root@vps mysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO
 -------------------

 Then you need to reset mysql password. For doing the same you need to stop the current mysql instance and then start mysql in safe mode.

--------------------

 #service mysqld stop or
#killall mysqld

 /usr/bin/mysqld_safe  --skip-grant-tables&


Now Connect to MySQL
#/usr/bin/mysql
Use the mysql database:
mysql>use mysql;
Change the MySQL admin(root) Password using following command


update user set password=password('wgstlfdse') where user='root' and host='localhost';

lush the privileges so everything will take (It removes any privileges set by the server)
mysql>flush privileges;
Then Quit : mysql>quit
Stop MySQL Service
/etc/init.d/mysqld stop
Start MySQL Service
/etc/init.d/mysqld start


Then connect mysql again 

 
 [root@vps ~]# mysql -u root -p
Enter password:


Use the mysql database:
mysql>use mysql;

Now I’ll update the server host to use the wildcard, and then issue the command to reload the privilege tables.

 update user set host='%' where user='root' and host='localhost';

FLUSH PRIVILEGES;

Also change  bind-address value in my.cnf like below.

--------------------
bind-address= *
---------------------

That’s all there is to it.


 

How To Fix “Missing Dependency: perl(URI) >= 1.17 is needed by package subversion”

How To Fix “Missing Dependency: perl(URI) >= 1.17 is needed by package subversion”


If you are a cPanel user on CentOS 5.6 64 bit and wanting to install Subversion, you might get this error after running ‘yum install subversion’

Missing Dependency: perl(URI) >= 1.17 is needed by package subversion-1.6.11-7.el5_6.4.x86_64

yum seems to unable to find needed dependency and just fails

To fix this issue there’s a workaround.

++++++++++++++++++++
cd /tmp
wget http://sdkit.com/download/centos/5/rpms/perl-URI-1.35-3.noarch.rpm
rpm -i perl-URI-1.35-3.noarch.rpm
The dependency is now fixed. You can proceed to installing subversion by running:
+++++++++++++++++++++++++++++

yum install subversion

How To Add DomainKeys and SPF Records on CPanel Servers

This how to describe how to add domainkeys and SPF (Sender Policy Framework) records on CPanel Servers. Whenever you create a domain on CPanel server using WHM, it wont add domainkeys and SPF records for particular domains. To add domainkeys and SPF records please use following steps.

We consider here we have a domain sohailriaz.com with cpanel username sohailri

Please note on CPanel servers most of the work is done using cpanel username which then reflect setting on particular domains. If you have several domains / sub domains under one username, it will add domainkeys and SPF records for all domains / subdomains.

Execute the following command on your CPanel server using root.

=================================================================================================
1 DomainKeys
1.1 Add DomainKeys for single user

/usr/local/cpanel/bin/domain_keys_installer sohailri

where sohailri is a username
1.2 Add DomainKeys for all CPanel users.

If you wish to add DomainKeys for all Cpanel users on your server, execute following command.

ls /var/cpanel/users | while read a; do
/usr/local/cpanel/bin/domain_keys_installer $a
done
=================================================================================================


2 SPF (Sender Policy Framework)
2.1 Add SPF for single account.

/usr/local/cpanel/bin/spf_installer sohailri

2.2 Add SPF for all Cpanel accounts.

If you want to add SPF records for all cpanel accounts exist on your server then use the following command.

ls /var/cpanel/users | while read a; do
/usr/local/cpanel/bin/spf_installer $a
done
=================================================================================================

3 Enable DomainKeys and SPF for every new CPanel account.

If you want to add DomainKeys and SPF for every new account created on Cpanel, then do the following.

vi /scripts/postwwwacct

postwwwacct is a file which execute after wwwacct (used to create account), paste following lines in the file

#!/usr/bin/perl

my %OPTS = @ARGV;
$ENV{USER} = “$OPTS{‘user’}”;
system q(/usr/local/cpanel/bin/domain_keys_installer $USER);
system q(/usr/local/cpanel/bin/spf_installer $USER);

RLimitMEM Directive

Description: Limits the memory consumption of processes launched by Apache children
Syntax: RLimitMEM bytes|max [bytes|max]
Default: Unset; uses operating system defaults
Context: server config, virtual host, directory, .htaccess
Override: All
Status: Core
Module: core

Takes 1 or 2 parameters. The first parameter sets the soft resource limit for all processes and the second parameter sets the maximum resource limit. Either parameter can be a number, or max to indicate to the server that the limit should be set to the maximum allowed by the operating system configuration. Raising the maximum resource limit requires that the server is running as root, or in the initial startup phase.

This applies to processes forked off from Apache children servicing requests, not the Apache children themselves. This includes CGI scripts and SSI exec commands, but not any processes forked off from the Apache parent such as piped logs.

Memory resource limits are expressed in bytes per process.