Odd MySQL Problem using 100% CPU with Nothing Running

For some reason, MySQL (5.5.15) on my home server (running kernel 2.6.32-220.17.1.el6.x86_64) was using all my CPU:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10949 mysql 20 0 804m 53m 5868 S 125.8 1.6 415:09.88 mysqld

There was nothing running on MySQL.

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 36 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

My disk wasn’t full.

[root@nas leo]# df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 51606140 22222600 26762100 46% /

I tried restarting MySQL a few times, but it continued to use up all the CPU immediately after starting. After scratching my head and not wanting to waste any more time, I simply rebooted the machine and everything was back to normal again.

Documenting this in case someone else has ran into the same problem and possibly has a fix/

Booting Z/OS Emulation Files with Hercules

I recently stumbled across some (illegal) Z/OS emulation files for Hercules on The Pirate Bay a while back, but one thing which I couldn’t figure out was to actually get them working. With a little bit of searching, I’ve finally figured it out. This guide should help anyone who has absolutely no background with IBM mainframes or Hercules – but for some reason wishes to play with a mainframe – to get started.

Step 1: Required Programs and Files

You will need to download and install:

Secondly, you will need to obtain the Z/OS emulation files for Hercules (hint: search TPB or google ‘IBM Z OS Emulation files’)

Step 2: Configure your 3270 Terminal Emulator

Open the wc3270 Session Wizard and create a new session with the host set to localhost and the TCP port number set to 3270. Be sure to save your session by hitting ‘[Enter]’ at the menu. You may want to create a desktop shortcut to be used later.

Step 3: Configure hercules.cnf

Ensure that your hercules.cnf file references your DASD devices correctly. The paths from the configuration file may be relative. However, in my case, I’ve provided absolute paths.

Note that my DSAD files are on my D:\ibm\cckd\ directory. If you are using relative paths, ensure that your hercules.cnf file is in the correct location, relative to your cckd files.

Ensure your display terminals are set to 3270.

My hercules.cnf is as follows:


#
# Hercules Emulator Control file...
# Description:
# MaxShutdownSecs: 15
#
#
# System parameters
#

ARCHMODE z/Arch
ALRF ENABLE
CCKD RA=2,RAQ=4,RAT=2,WR=2,GCINT=5,GCPARM=0,NOSTRESS=0,TRACE=0,FREEPEND=-1
CNSLPORT 3270
CONKPALV (3,1,10)
CPUMODEL 3090
CPUSERIAL 012345
DIAG8CMD ENABLE
ECPSVM YES
LOADPARM 0A95DB..
LPARNAME HERCULES
MAINSIZE 1024
MOUNTED_TAPE_REINIT DISALLOW
NUMCPU 4
OSTAILOR Z/OS
PANRATE 80
PGMPRDOS LICENSED
SHCMDOPT NODIAG8
SYSEPOCH 1900
TIMERINT 50
TZOFFSET +1400
YROFFSET 0

HERCPRIO 0
TODPRIO -20
DEVPRIO 8
CPUPRIO 0
PANTITLE z/OS 1.9 IPL A80?

# Display Terminals

0700 3270
0701 3270
0702 3270
0703 3270
0704 3270

# DASD Devices

0A81 3390 D:/ibm/cckd/zares1.cckd sf=shadow/zares1_*
0A82 3390 D:/ibm/cckd/zares2.cckd sf=shadow/zares2_*
0A83 3390 D:/ibm/cckd/zadb81.cckd sf=shadow/zadb81_*
0A84 3390 D:/ibm/cckd/zadb82.cckd sf=shadow/zadb82_*
0A85 3390 D:/ibm/cckd/zadb83.cckd sf=shadow/zadb83_*
0A86 3390 D:/ibm/cckd/zadb84.cckd sf=shadow/zadb84_*
0A87 3390 D:/ibm/cckd/zadb91.cckd sf=shadow/zadb91_*
0A88 3390 D:/ibm/cckd/zadb92.cckd sf=shadow/zadb92_*
0A89 3390 D:/ibm/cckd/zacic1.cckd sf=shadow/zacic1_*
0A8A 3390 D:/ibm/cckd/zadis1.cckd sf=shadow/zadis1_*
0A8B 3390 D:/ibm/cckd/zadis2.cckd sf=shadow/zadis2_*
0A8C 3390 D:/ibm/cckd/zadis3.cckd sf=shadow/zadis3_*
0A8D 3390 D:/ibm/cckd/zadis4.cckd sf=shadow/zadis4_*
0A8E 3390 D:/ibm/cckd/zadis5.cckd sf=shadow/zadis5_*
0A8F 3390 D:/ibm/cckd/zadis6.cckd sf=shadow/zadis6_*
0A90 3390 D:/ibm/cckd/zaims1.cckd sf=shadow/zaims1_*
0A91 3390 D:/ibm/cckd/zaprd1.cckd sf=shadow/zaprd1_*
0A92 3390 D:/ibm/cckd/zaprd2.cckd sf=shadow/zaprd2_*
0A93 3390 D:/ibm/cckd/zaprd3.cckd sf=shadow/zaprd3_*
0A94 3390 D:/ibm/cckd/zaprd4.cckd sf=shadow/zaprd4_*
0A95 3390 D:/ibm/cckd/zasys1.cckd sf=shadow/zasys1_*
0A96 3390 D:/ibm/cckd/zauss1.cckd sf=shadow/zauss1_*
0A97 3390 D:/ibm/cckd/zawas1.cckd sf=shadow/zawas1_*
0A98 3390 D:/ibm/cckd/zawas2.cckd sf=shadow/zawas2_*
0A99 3390 D:/ibm/cckd/zawas3.cckd sf=shadow/zawas3_*
0A9A 3390 D:/ibm/cckd/sares1.cckd sf=shadow/sares1_*

 

Step 4: Starting Hercules

Start hercules on the same directory as your hercules.cnf file. In my case:

D:\ibm>"C:\Program Files\Hercules\Hercules 3.07 (64 Bit)\hercules.exe"

Hercules should start and you should see something similar to:

Press ‘ESC’ to switch to the graphic mode. Start two instances of the 3270 terminals that was made and configured in Step 2. The first will be the console terminal, the second will be the TSO terminal. If everything works, you should see two terminals binded to Hercules:

To start Z/OS, press ‘L’ for IPL, then F for the first Z/OS disk device (as highlighted above). Z/OS should begin loading.

The terminal console should halt and prompt whether to initialize sysplex ADCDPL or to reinitalize XCF. Enter ‘r 00,r’

It will then prompt asking for the couple system parameter. Enter ‘r 00,couple=**’

Step 5: Have Fun

The login screen should appear on the second terminal. You may login as ‘IBMUSER’ with the password ‘SYS1’.

cPanel movingpage.cgi hacked

I got an email today about a list of pages being hacked on a friend’s server. The hacked page were all served from the /cgi-sys/movingpage.cgi location and contained the title “Indonesian Hacker”, displayed the hacker’s name (hmei7) along with a random picture (in this case, Mr. Bean).

Unfortunately, I have no idea how it happened, but it according to the file modified date, this happened about 3 months ago. Any logs from then have since been rotated. It would seem as though they managed to edit the default template for the moving page. These can be edited via WHM and are stored in the /var/cpanel/webtemplates/ directory. In order to remove the hacked page, you’ll need to remove the altered templates from there.

I’d be interested if you happen to know how this was exploited exactly.

Compiling Streamripper on CentOS 5 – Problems & Solutions

If you tried compiling the latest version of streamripper (1.64.6), you’ll probably see the following error:


./configure 

< Snip > 

*** Could not run GLIB test program, checking why...
*** The test program failed to compile or link. See the file config.log for the
*** exact error that occured. This usually means GLIB is incorrectly installed.
configure: error: Glib 2.16 or greater required

If so, try using an older version such as 1.62.0:


cd streamripper-1.62.0
./configure
make

< Snip >

Making all in po
make[3]: Entering directory `/home/leo/streamrip/streamripper-1.62.0/tre-0.7.2/po'
test -z "fi.gmo" || make fi.gmo
make[4]: Entering directory `/home/leo/streamrip/streamripper-1.62.0/tre-0.7.2/po'
rm -f fi.gmo && : -c --statistics -o fi.gmo fi.po
mv: cannot stat `t-fi.gmo': No such file or directory
make[4]: *** [fi.gmo] Error 1
make[4]: Leaving directory `/home/leo/streamrip/streamripper-1.62.0/tre-0.7.2/po'
make[3]: *** [stamp-po] Error 2
make[3]: Leaving directory `/home/leo/streamrip/streamripper-1.62.0/tre-0.7.2/po'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/home/leo/streamrip/streamripper-1.62.0/tre-0.7.2'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/leo/streamrip/streamripper-1.62.0/tre-0.7.2'
make: *** [all-recursive] Error 1

If you run into the error above while compiling, try to configure with the ' --disable-adplug' flag.


./configure --disable-adplug
make
make install

wget: Recursively Download a Directory

Suppose I have a directory with a set of files (and perhaps other directories) under http://example.com/files/distros/ which has directory indexes enabled.

In order to download all files under the ‘distros’ directory using wget, run:


wget -r -np -nH -R index.html http://example.com/files/distros/

Where:
-r : recurse through all files and directories
-np : no parent / do not recurse up to the parent directory
-nH : no host / disable generation of host directories when saving files
-R : reject any files named ‘index.html’

Change Places Bar Locations for File Dialog Boxes in Windows 7

In case you wanted to change the default locations displayed in the places bar on the left of a FileDialog box such as the one shown below under Windows 7, do the following:

run gpedit.msc

Navigate to User Configuration -> Administrative templates -> Windows Components -> Windows Explorer -> Common Open File Dialog -> Double Click on ‘Items displayed in Places Bar’

On the “Items displayed in Places Bar” window, select Enabled and enter the 5 items that is to be displayed below. Keywords such as ‘Desktop’, ‘Recent Places’, ‘Documents’, ‘Pictures’, etc. can be used in place of the path.

Disable Opera Error Console

One thing that’s been bugging me a lot with Opera is the fact that the error console pops up when errors occur on a web page. This is great and all if you’re trying to debug your own website, however not so much when I’m trying to find a solution to a compile error at 3 in the morning.

As of this writing, Opera 11.51 is the latest version. I can’t guarantee this will work for later versions.

In order to disable this window from popping up, go to opera:config and search for “Error Console Filter”. You should see something similar to:

The Error Console Filter

Clear the field such that the filter is empty, and then click on ‘Save’.

Presto! The error console shall bug you no more.

MySQL Binary Logs

If you have binary logging enabled in MySQL, it’s quite possible that the disk used by these binary logs to be is quite high.

For instance:

bash-3.2# ls -al /opt/mysql/var/
total 16240180
drwx------  5 mysql root        4096 Sep 30 15:33 .
drwxr-xr-x 11 mysql mysql       4096 Sep 30 15:33 ..
drwx------  2 mysql root        4096 Aug 15 02:15 mysql
-rw-rw----  1 mysql mysql      19103 Aug 15 02:15 mysql-bin.000001
-rw-rw----  1 mysql mysql     729901 Aug 15 02:15 mysql-bin.000002
-rw-rw----  1 mysql mysql        125 Aug 15 02:15 mysql-bin.000003
-rw-rw----  1 mysql mysql        106 Aug 15 02:15 mysql-bin.000004
-rw-rw----  1 mysql mysql 1074557984 Sep 18 17:59 mysql-bin.000005
-rw-rw----  1 mysql mysql 1074199027 Sep 18 18:52 mysql-bin.000006
-rw-rw----  1 mysql mysql   47809403 Sep 18 18:53 mysql-bin.000007
-rw-rw----  1 mysql mysql 1077169445 Sep 18 18:55 mysql-bin.000008
-rw-rw----  1 mysql mysql 1074419095 Sep 18 18:57 mysql-bin.000009
-rw-rw----  1 mysql mysql 1073742057 Sep 18 21:41 mysql-bin.000010
-rw-rw----  1 mysql mysql 1073742056 Sep 19 03:58 mysql-bin.000011
-rw-rw----  1 mysql mysql 1073781146 Sep 19 10:53 mysql-bin.000012
-rw-rw----  1 mysql mysql 1073742068 Sep 20 01:15 mysql-bin.000013
-rw-rw----  1 mysql mysql 1073754553 Sep 22 18:22 mysql-bin.000014
-rw-rw----  1 mysql mysql  158604158 Sep 25 18:33 mysql-bin.000015
-rw-rw----  1 mysql mysql        125 Sep 25 18:36 mysql-bin.000016
-rw-rw----  1 mysql mysql        125 Sep 25 19:21 mysql-bin.000017
-rw-rw----  1 mysql mysql        125 Sep 25 19:47 mysql-bin.000018
-rw-rw----  1 mysql mysql    5212790 Sep 25 20:34 mysql-bin.000019
-rw-rw----  1 mysql mysql 1073741925 Sep 26 01:43 mysql-bin.000020
-rw-rw----  1 mysql mysql 1073741893 Sep 26 03:43 mysql-bin.000021
-rw-rw----  1 mysql mysql 1073741937 Sep 26 14:24 mysql-bin.000022
-rw-rw----  1 mysql mysql 1073741938 Sep 27 08:38 mysql-bin.000023
-rw-rw----  1 mysql mysql 1073746311 Sep 28 20:43 mysql-bin.000024
-rw-rw----  1 mysql mysql  875141163 Sep 30 15:22 mysql-bin.000025
-rw-rw----  1 mysql mysql  488120905 Oct  4 01:52 mysql-bin.000026
-rw-rw----  1 mysql mysql        494 Sep 30 15:33 mysql-bin.index
...
bash-3.2# du -ch mysql-bin*|grep total
16G     total

I’ve had these binary logs fill up a neglected server’s disks to 100% which caused quite a bit of pain.

To manually clear these logs, run the PURGE BINARY LOGS command in MySQL.


mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |      19103 |
| mysql-bin.000002 |     729901 |
| mysql-bin.000003 |        125 |
| mysql-bin.000004 |        106 |
| mysql-bin.000005 | 1074557984 |
| mysql-bin.000006 | 1074199027 |
| mysql-bin.000007 |   47809403 |
| mysql-bin.000008 | 1077169445 |
| mysql-bin.000009 | 1074419095 |
| mysql-bin.000010 | 1073742057 |
| mysql-bin.000011 | 1073742056 |
| mysql-bin.000012 | 1073781146 |
| mysql-bin.000013 | 1073742068 |
| mysql-bin.000014 | 1073754553 |
| mysql-bin.000015 |  158604158 |
| mysql-bin.000016 |        125 |
| mysql-bin.000017 |        125 |
| mysql-bin.000018 |        125 |
| mysql-bin.000019 |    5212790 |
| mysql-bin.000020 | 1073741925 |
| mysql-bin.000021 | 1073741893 |
| mysql-bin.000022 | 1073741937 |
| mysql-bin.000023 | 1073741938 |
| mysql-bin.000024 | 1073746311 |
| mysql-bin.000025 |  875141163 |
| mysql-bin.000026 |  492014457 |
+------------------+------------+
26 rows in set (0.00 sec)

mysql> purge binary logs before '2011-08-01';
Query OK, 0 rows affected (1.61 sec)

The above purge command did not clear anything. Rather than specifying the date, it’s also possible to purge all logs before a specified file by running something like:

mysql> purge binary logs to 'mysql-bin.000005';
Query OK, 0 rows affected (6.25 sec)

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000005 | 1074557984 |
| mysql-bin.000006 | 1074199027 |
| mysql-bin.000007 |   47809403 |
| mysql-bin.000008 | 1077169445 |
| mysql-bin.000009 | 1074419095 |
| mysql-bin.000010 | 1073742057 |
| mysql-bin.000011 | 1073742056 |
| mysql-bin.000012 | 1073781146 |
| mysql-bin.000013 | 1073742068 |
| mysql-bin.000014 | 1073754553 |
| mysql-bin.000015 |  158604158 |
| mysql-bin.000016 |        125 |
| mysql-bin.000017 |        125 |
| mysql-bin.000018 |        125 |
| mysql-bin.000019 |    5212790 |
| mysql-bin.000020 | 1073741925 |
| mysql-bin.000021 | 1073741893 |
| mysql-bin.000022 | 1073741937 |
| mysql-bin.000023 | 1073741938 |
| mysql-bin.000024 | 1073746311 |
| mysql-bin.000025 |  875141163 |
| mysql-bin.000026 |  495852653 |
+------------------+------------+
22 rows in set (0.00 sec)

As you can see, all logs up to 000005 was deleted.

To disable binary logging if your server is not replicating itself by editing removing log-bin=mysql-bin in mysql.cnf

MySQL: UPDATE ON DUPLICATE KEY DELETE work-around

I had a SQL query that had to update various indexed unique fields in my database which went something like this:

UPDATE page SET path = REPLACE(path, 'junk', '') ON DUPLICATE KEY DELETE path

Obviously this isn’t a valid MySQL query. The work around is to use UPDATE IGNORE and then delete any rows after which contains the substring ‘junk’:

UPDATE IGNORE page SET path = REPLACE(path, 'junk', '');
DELETE FROM page WHERE path LIKE '%junk%'

Western Digital 2TB WD20EARS & High Load_Cycle_Count

After buying 2 new 2TB Western Digital Green hard drives and setting it up as part of a Linux software-based Raid 5, I noticed that the drives would ‘click’ every time a file is accessed after a period of inactivity. Running smartctl -a /dev/ would also trigger this ‘click’ to happen and would report the Load_Cycle_Count increment by one. What appears to be happening is the Western Digital drives are automatically parking the disk heads after a period of 8 seconds of inactivity. This will cause the head to ‘click’ every time it unparks when an IO request is made. Unfortunately, the constant park/unparking of the heads causes an unnecessary amount of strain on the disks, which I very much want to minimize on a new set of disks.

The SMART report showed the following:

=== START OF INFORMATION SECTION ===
Device Model:     WDC WD20EARS-00J99B0
Serial Number:    WD-WCAWZ0840640
Firmware Version: 80.00A80
User Capacity:    2,000,398,934,016 bytes
Device is:        Not in smartctl database [for details use: -P showall]
ATA Version is:   8
ATA Standard is:  Exact ATA specification draft version not indicated
Local Time is:    Fri Sep  2 23:26:17 2011 MDT
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

=== START OF READ SMART DATA SECTION ===
SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status:  (0x80)Offline data collection activity
was never started.
Auto Offline Data Collection: Enabled.
Self-test execution status:      (   0)The previous self-test routine completed
without error or no self-test has ever 
been run.
Total time to complete Offline 
data collection:  (40200) seconds.
Offline data collection
capabilities:  (0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off support.
Suspend Offline collection upon new
command.
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities:            (0x0003)Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability:        (0x01)Error logging supported.
General Purpose Logging supported.
Short self-test routine 
recommended polling time:  (   2) minutes.
Extended self-test routine
recommended polling time:  ( 255) minutes.
Conveyance self-test routine
recommended polling time:  (   5) minutes.
SCT capabilities:        (0x3035)SCT Status supported.
SCT Feature Control supported.
SCT Data Table supported.

SMART Attributes Data Structure revision number: 16
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
  1 Raw_Read_Error_Rate     0x002f   200   200   051    Pre-fail  Always       -       0
  3 Spin_Up_Time            0x0027   151   151   021    Pre-fail  Always       -       9441
  4 Start_Stop_Count        0x0032   100   100   000    Old_age   Always       -       16
  5 Reallocated_Sector_Ct   0x0033   200   200   140    Pre-fail  Always       -       0
  7 Seek_Error_Rate         0x002e   200   200   000    Old_age   Always       -       0
  9 Power_On_Hours          0x0032   100   100   000    Old_age   Always       -       72
 10 Spin_Retry_Count        0x0032   100   253   000    Old_age   Always       -       0
 11 Calibration_Retry_Count 0x0032   100   253   000    Old_age   Always       -       0
 12 Power_Cycle_Count       0x0032   100   100   000    Old_age   Always       -       12
192 Power-Off_Retract_Count 0x0032   200   200   000    Old_age   Always       -       8
193 Load_Cycle_Count        0x0032   200   200   000    Old_age   Always       -       121
194 Temperature_Celsius     0x0022   125   121   000    Old_age   Always       -       27
196 Reallocated_Event_Count 0x0032   200   200   000    Old_age   Always       -       0
197 Current_Pending_Sector  0x0032   200   200   000    Old_age   Always       -       0
198 Offline_Uncorrectable   0x0030   100   253   000    Old_age   Offline      -       0
199 UDMA_CRC_Error_Count    0x0032   200   200   000    Old_age   Always       -       0
200 Multi_Zone_Error_Rate   0x0008   100   253   000    Old_age   Offline      -       0

Although the Load_Cycle_count isn’t that high, the drive was mostly idle or busy rebuilding the raid array which doesn’t justify the ~1.7 cycle counts per hour.

The solution to this is to run Western Digital’s WDIdle3 utility which allows a custom timeout to be set instead of the default 8 seconds. Unfortunately, WDidle3 is a DOS only program and therefore the computer must be booted in DOS before the program can be executed.

If you’re like me and don’t have DOS readily available and have no CDRom or a floopy drive, the easiest way is to load up FreeDOS under PXE with the WDIdle3 utility as part of the FreeDOS image.
Step 1: Prepare the fdboot.img image with the WDidle3 utility

mount -o loop -t vfat fdboot.img /mnt/freedos
cp wdidle3.exe /mnt/freedos
umount /mnt/freedos

Step 2: Configure PXE to boot the fdboot.img image as a ramdisk

vi /tftpboot/pxelinux.cfg/default 

# Then add the following entry:
label FreeDOS
kernel memdisk
append initrd=fdboot.img

Step 3: Boot into PXE and boot into FreeDOS. A grub menu should appear:

Select #4 “FreeDOS Live CD with HIMEM only”

Step 4: Run the WDIdle3 utility with /S or /D to disable the timer


* Yes, these screenshots were done under vmware. Couldn’t take a picture of it in action.

After disabling the timer in my WD20EARS, the Load_Cycle_Count remains virtually static and the ‘click’s have disappeared.