Category Archives: Mysql

Ignore table and dump mysql database

-bash-4.1# mysqldump satmn13_drup189 > satmn13_drup1891.sql
mysqldump: Got error: 1146: Table ‘satmn13_drup189.dr_cache_admin_menu’ doesn’t exist when using LOCK TABLES

-bash-4.1# mysqldump satmn13_drup189 –ignore-table=satmn13_drup189.dr_cache_admin_menu > satmn13_drup1891.sql

Emails going to Spam folder issue

We do get complaints of emails going to spam folder most of the times while working.

The solution to such a issues depends on 3 things

rdns for the IP address and matching A record for the domain name in question.
Need to setup right SPF
Need to setup right DKIM

a) To setup SPF for a domain name can use tool http://www.spfwizard.net/

b) For DKIm you can use tool from Socketslab

Domain Key / DKIM Generation Wizard

for DKIm you can use key name as key1 or domain1 or any random words

c) Once done you can check whether its valid or not using a vaiidator

http://dkimvalidator.com/results

For validitor you need to send email to email ID shown on screen and check once done click on show results

Where it will show if set DKIM and spf are valid or not

Error: exim logged Undefined subroutine &main::user2uid called. LOG: MAIN

Exim error :

LOG: MAIN
cwd=/root 4 args: /usr/sbin/exim -i -v testemail@gmail.com
LOG: MAIN REJECT
F=<root@darkmatter.dnsprotect.com> rejected by non-SMTP ACL: failed to expand ACL string "${if eq{$originator_uid}{${perl{user2uid}{nobody}}}{1}{0}}": Undefined subroutine &main::user2uid called. LOG: MAIN
cwd=/var/spool/exim 8 args: /usr/sbin/exim -v -t -oem -oi -f <> -E1UY3r2-000

root@server-web [~]# /scripts/buildeximconf
Configuration file passes test! New configuration file was installed.
/etc/exim.pl.local.build syntax OK
Enabled system filter options: attachments|spam_rewrite
..........
...
........
/etc/exim.pl.local installed!
razor2 is not installed, disabling it in SpamAssassin to save memory
pyzor is not installed, disabling it in SpamAssassin to save memory
SPF is disabled in exim or unavailable, enabling SPF for SpamAssassin
Refreshing SMTP Mail protection.

root@server-web [~]# cp -rpf /etc/exim.pl.local /etc/exim.pl

Disable Strict Mode mysql mariadb

While working on WHMCS I was not able to generate tickets

I found out issue was due to mysql being in strict mode

I tried disabling mysql strict mode by adding entries in /etc/my.cnf

sql_mode=””

However it didnt work out.

I have found that there is a /usr/my.cnf file that is enabling strict mode on server.

This file contained the following:

====
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
====

I removed the STRICT_TRANS_TABLES option and saved the file. All that is left is to restart the MySQL server. You can do this at your convenience with the following command:

====
/scripts/restartsrv_mysql
====

Once it is restarted, strict mode should be disabled.

mysqldump: Error: ‘Out of resources when opening file ‘/tmp/#sql_466b_2.MAI’

While creating of backup of mysql I was getting below error

mysqldump: Error: ‘Out of resources when opening file ‘/tmp/#sql_466b_2.MAI’ (Errcode: 24 “Too many open files”)’

The error is due to Mysql exceeding the limit of open files on mysql

So I just went ahead and saw the open file limit

I went ahead and added a parameter in /etc/my.cnf and restarted mysql to fix this issue

open_files_limit = 5000

[AuthFailure] Either the username was not recognised, or the password was incorrect)

Getting error on XVNC for password authentication

I could see below error in logs

[AuthFailure] Either the username was not recognised, or the password was incorrect)

After googling I found vnc server will need to be restarted once password is reset

root@online [~]# /etc/init.d/vncserver-virtuald restart
root@online [~]# /etc/init.d/vncserver-x11-serviced restart

Now VNC work fine

Got error 28 from storage engine (1030) Mysql

While working on clients server today I got the error on database while making backup of all the database using mysql dump

The error was ” Got error 28 from storage engine (1030)”

root@hostonline [/tmp]# mysqldump –all-databases > all_databases.sql &
[1] 19341
root@hostonline [/tmp]# mysqldump: Couldn’t execute ‘SHOW TRIGGERS LIKE ‘phpbb\_posts”: Got error 28 from storage engine (1030)

After doing bit of googling I could see the error is due to lack of disk space or inodes in the hard disk or partition

I could see it was due to lack of space in /tmp

I increased the /tmp size using below steps

root@hostonline [/tmp]# /etc/init.d/cpanel stop
Stopping tailwatchd: [30832] [2015-09-10 01:27:18 -0500] [main] Current process ‘25903’ stopped
[ OK ]
Stopping cPanel services: [ OK ]
Stopping cPanel dav services: [ OK ]
Stopping cPanel queue services: [ OK ]
Stopping cPanel brute force detector services: [ OK ]
Stopping cPanel log services:
Stopping cPanel Chat services: [FAILED]

Stopping cPanel ssl services: Waiting for (?^:^(?:stunnel$|stunnel-[0-9\.]+local)) to shutdown … not running.
[ OK ]
root@hostonline [/tmp]# /etc/init.d/mysql stop
Shutting down MySQL…… [ OK ]
root@hostonline [/tmp]# /etc/init.d/httpd stop
httpd stopped
root@hostonline [/tmp]# umount -l /tmp
root@hostonline [/tmp]# umount -l /var/tmp
root@hostonline [/tmp]#
root@hostonline [/tmp]#
root@hostonline [/tmp]# mv /usr/tmpDSK /usr/tmpDSK_back
root@hostonline [/tmp]# vi /scripts/securetmp

Now change tmpdsksize paramater to 2048000

Save file and now run below command
root@hostonline [/tmp]# /scripts/securetmp
Would you like to secure /tmp & /var/tmp at boot time? (y/n) y
Would you like to secure /tmp & /var/tmp now? (y/n) y
Securing /tmp & /var/tmp
Calculating size on /tmp
/tmp calculated to be 4096 M based on available disk space in /usr
No separate partition for tmp!
Building /usr/tmpDSK…4194304+0 records in
4194304+0 records out
4294967296 bytes (4.3 GB) copied, 37.5703 seconds, 114 MB/s
mke2fs 1.39 (29-May-2006)
/usr/tmpDSK is not a block special device.
Proceed anyway? (y,n) Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
524288 inodes, 1048576 blocks
52428 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1073741824
32 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736

Writing inode tables: done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
tune2fs 1.39 (29-May-2006)
Creating journal inode: done
This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Done
Setting up /tmp… Done
Setting up /var/tmp… Done
Checking fstab for entries …Done
Logrotate TMPDIR already configured
Process Complete
root@hostonline [/tmp]# /etc/init.d/cpanel start
Starting cpanel:
Waiting for cpaneld to shutdown … not running.
Waiting for webmaild to shutdown … not running.
Waiting for cpdavd-ssl to shutdown … not running.
Waiting for cpanellogd to shutdown … not running.
Waiting for entropychat to shutdown … not running.
Waiting for eximstats to shutdown … not running.
mailmanctl: no process killed
Waiting for eximstats to shutdown … not running.
[+] No running cPGreyList process found.
You may be running this update interactively from within WHM.
It is necessary we restart cpsrvd, but this will kill the session displaying update progress.
Please see the logs in /var/cpanel/updatelogs/ for further information if the update progress stops

==> SMTP Mail protection has been enabled.
All outbound SMTP connections will be redirected to localhost except:
uid is root (ports: 25,26,465,587)
uid is cpanel (ports: 25,26,465,587)
gid is mail (ports: 25,26,465,587)
gid is mailman (ports: 25,26,465,587)
The “cphulkd” service is disabled.

root@hostonline [/tmp]# /etc/init.d/httpd start [ OK ]
httpd starting

root@hostonline [/tmp]# /etc/init.d/mysql start
Starting MySQL [ OK ]

root@hostonline [/home]# mysqldump –all-databases > all_databases6.sql

Mysqldump is ready now 🙂

libMagickWand.so.2: cannot open shared object file

While working on one cPanel server we could see below error in error_log file for one client

libMagickWand.so.2: cannot open shared object file

The error log were filled by such errors

reinstalling imagemagick using command /scripts/installimagemagick didnt work as error were still persisting .

So renamed the php,ini file

mv /usr/local/lib/php.ini /usr/local/lib/php.ini-old

Now to regenerate the new php.ini file I had to use command

/scripts/phpini

A new PHP.ini was create with default entries like a newly set server.

Modified the PHP.ini to customize it as needed

Then had to finish it by rebuilding apache and php using easyapache

/scripts/easyapache

Now all sites work fine without any error 🙂

Can’t create new tempfile Mysql

While working today came across new error in Mysql

”

[myfuncol_student.ow_base_config] error: Can't create new tempfile: './myfuncol_student/ow_base_config.TMD

Repairing the Mysql didn’t fix this issue

I ran below command to fix this issue

root@hostonl [/var/lib/mysql]# myisamchk -r -f myfuncol_student/ow_base_config.MYI
– recovering (with sort) MyISAM-table ‘myfuncol_student/ow_base_config.MYI’
Data records: 228
– Fixing index 1
– Fixing index 2

Now database is working fine

Innodb recovery

We were getting below error on checking

# /etc/init.d/mysqld status

ERROR! MySQL is running but PID file could not be found

On trying to restart were getting below error

root@alpha [~]# /scripts/restartsrv_mysql
Waiting for “mysql” to restart …………
…failed.

Service Error
The “mysql” service failed to start.

Startup Log
Starting MySQL………… SUCCESS!

Log Messages
150627 23:53:37 mysqld_safe mysqld restarted
150627 23:53:37 mysqld_safe Number of processes running now: 0
150627 23:53:37 mysqld_safe mysqld restarted
150627 23:53:37 mysqld_safe mysqld restarted
150627 23:53:37 mysqld_safe Number of processes running now: 1
150627 23:53:37 mysqld_safe mysqld process hanging, pid 16496 – killed
150627 23:53:37 mysqld_safe mysqld process hanging, pid 16501 – killed
150627 23:53:37 mysqld_safe Number of processes running now: 2
/usr/sbin/mysqld[0x84cbc2]
/usr/sbin/mysqld[0x857c7a]
/usr/sbin/mysqld[0x91b362]
/usr/sbin/mysqld[0x9267d2]
/usr/sbin/mysqld[0x85942b]
/usr/sbin/mysqld[0x858f82]
150627 23:53:36 mysqld_safe mysqld restarted

mysql has failed. Contact your system administrator if the service does not automagically recover.

The error was due to corruption in innodb in server

We found the below fix successful to fix this issue

First make backup of my.cnf file as backup.my.cnf

Open the mysql configuration file my.cnf which is in

root@alpha [~]# vi /etc/my.cnf

Add below entry in it

[mysqld]
innodb_force_recovery = 1

restart Mysql using below command in cpanel server

root@alpha [~]# /scripts/restartsrv_mysql

Waiting for “mysql” to restart ……waiting for “mysql” to initialize ……
…finished.

Service Status
mysql (/usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/lib/mysql/alpha.redlogic.com.err –open-files-limit=100000 –pid-file=/var/lib/mysql/alpha.redlogic.com.pid) is running as mysql with PID 28440 (pidfile check method).

Startup Log
Starting MySQL.. SUCCESS!

Log Messages
150628 0:03:54 [Note] /usr/sbin/mysqld: ready for connections.
150628 00:03:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150628 00:03:53 mysqld_safe mysqld from pid file /var/lib/mysql/alpha.redlogic.com.pid ended
150628 0:03:53 [Note] /usr/sbin/mysqld: Shutdown complete
150628 0:03:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 119 user: ‘eximstats’
150628 0:03:50 [Note] /usr/sbin/mysqld: Normal shutdown
150627 23:56:34 [Note] /usr/sbin/mysqld: ready for connections.
150627 23:56:33 mysqld_safe mysqld from pid file /var/lib/mysql/alpha.redlogic.com.pid ended
150627 23:56:33 [Note] /usr/sbin/mysqld: Shutdown complete
150627 23:56:29 [Warning] /usr/sbin/mysqld: Forcing close of thread 1 user: ‘eximstats’
150627 23:56:29 [Warning] /usr/sbin/mysqld: Forcing close of thread 5 user: ”
150627 23:56:27 [Note] /usr/sbin/mysqld: Normal shutdown

mysql restarted successfully.

root@alpha [~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.42-cll MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> quit
Bye
root@alpha [~]#

Mysql was up and running now

innodb_force_recovery=1
skip-grant-tables