Slow MySQL Performance over a USB Bus

Slow MySQL Performance over a USB Bus

Parsing a 3gig MySQL dB with ~5 million datasets can be agonizingly slow. The bottleneck here being the USB bus.

I had to get MySQL running off an external USB HDD due to the heat generated on my notebook (Intel Core 2 Duo, SATA HDD).

The only advantage of this setup is the heat issue. As for the cons, there are plenty:

- USB connector can break the connection for some reason
- Slow USB bus
- USB external is powered through AC, brick needs to convert from AC -> DC
- The external IDE connector

Time consumed:
100 tables per week
~2.5 weeks
22/7, One 10-15 minute standby break before script execution

Filesystem: NTFS

dB dumped and imported to an ext3 filesystem

Send Attachments with Mutt

Use mutt to send an email through shell.
-a flag to attach an attachment
-s flag is the subject
echo -e creates the message in the body

#!/bin/bash
echo -e "This is the body message of the email" | mutt -a attachment.zip foo@recipient.com -s "This is the email subject"

Reset the TCP/IP Stack

Reset the TCP/IP Stack

Windows XP - Is the TCP/IP config acting up? Enter the command in a DOS box and this will hopefully clear the mess!

Note: Write down or screen capture DNS addresses, LAN configs and other variables. The reset will wipe out all network settings.

netsh int ip reset c:\resetlog.txt

GPRS Access Numbers

GPRS Access Numbers

*99#
*99***1#

Disable Data Execution Prevention (DEP)

Disable Data Execution Prevention (DEP)

Edit the BOOT.INI file in the root of the partition from which the computer boots.

• Right-click on My Computer and select Properties.
• Click on the Advanced tab.
• Click on the third button—settings for system start options.
• Click on the Edit button to edit the boot.ini file.
• Carefully edit the /NoExecute=OptIn string and change it to AlwaysOff. This part at the end of the line should now read: /NoExecute=AlwaysOff
• Save and close the dialog boxes by clicking on OK.

Alternative Method:

• ATTRIB -S -H -R C:\BOOT.INI
• NOTEPAD BOOT.INI
• Carefully edit the /NoExecute=OptIn string and change OptIn to AlwaysOff. This part at the end of the line should now read: /NoExecute=AlwaysOff
• Close the editor and resave the BOOT.INI file back to where it was.
• ATTRIB +S +H +R C:\BOOT.INI

To disable DEP by modifying the Boot.ini file, change the /noexecute policy level to alwaysoff.

/NoExecute=AlwaysOff

Shell Environment Variables

Shell Environment Variables

List the environment vars from a shell prompt:

-sh-3.00$ env

Linux Shell

Linux Shell

List Files and Directories without Color Coding

$ls --color=none -all


List Files with Auto-Refresh

while [ 1 ]; do clear; ls -allh; sleep 5; done


Generate and Check MD5

$md5sum FILE > md5sum.txt
$md5sum -c md5.txt

Generate the Date of the Form: January_01_YYYY.$ext

$NOW=`date +%B_%d_%a_%Y


Get the Total Size of a Directory and File

$du -hcs dir/dir
$du -h file.tar.gz


Search for Text Pattern in Files

$egrep "stuff.php" *

Split and Join Files

Split and Join Files

$split -b 450m file.tar.gz

With Prefix
$split -b 450m file.tar.gz PREFIX

Join Files
$cat xaa xab xac >> file.tar.gz

Split and Join Files

mysqldump: Couldn't execute SHOW TRIGGERS LIKE

mysqldump: Couldn't execute SHOW TRIGGERS LIKE

Importing a database with 251+ tables on Windows XP results in the following error:
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE table_name. Can't create
ate/write to file '\tmp\#sql_8d4_0.MYD' (Errcode: 17) (1)

This issue is due to the windows file handles.

[quote]
Please examine the server status variable "open_files_limit" and note that
mysqld requires two open files for each myisam table.

On my machine it looks like this:
mysql> SHOW VARIABLES LIKE 'Open_files_limit';
Variable_name Value
open_files_limit 1024

From the manual:
The number of files that the operating system allows mysqld to open. This is
the real value allowed by the system and might be different from the value you
gave using the --open-files-limit option to mysqld or mysqld_safe. The value is
0 on systems where MySQL can't change the number of open files.

When mysqldump tries to dump the files, it will first take a read lock on all
the tables in the database. That requires it to open all of them at the same
time. So if the number of available open files is low, this kind of error can
occur.

To make mysqldump avoid taking the read lock use --skip-lock-tables option. I
successfully used that to dump more tables than my system had file descriptors.

It should also be possible to put a smaller number of tables in each database or
only dump a selected number of tables at a time.

But that are workarounds, best thing is to increase the number of open files on
the system.
[/quote]

Source:
http://bugs.mysql.com/bug.php?id=17089


mysqldump: Couldn't execute SHOW TRIGGERS LIKE

CSV Invalid Field When Importing Into MySQL

CSV Invalid Field When Importing Into MySQL

Check that there are no funny characters such as single quotes or double quotes.
The import fields need to be specified correctly.
The CSV input file needs to be saved in the CSV format.


CSV Invalid Field When Importing Into MySQL

Restore a MySQL Database

Restore a MySQL Database

Through Shell:

$ mysql -uUserName -pPassword -hlocalhost DatabaseName < mysql_db.sql


Restore a MySQL Database

Backup a MySQL Database

Backup a MySQL Database

Through Shell:

Backup an entire database

$mysqldump -udatabase_user -p --host="host.com" --opt -f database_name > database_backup.sql

Backup through Windows:

c:\> mysqldump -udatabase_user -p --host="host.com" --skip-lock-tables database_name > database_backup.sql


Backup a table

$mysqldump -udatabase_user -p --host="host.com" --opt -f database_name table_name > database_table_backup.sql

-p prompts for a password after the above command is executed

--force, -f

Continue even if an SQL error occurs during a table dump.

One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the defintion refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes a SQL comment containing the view definition to the dump output and continues executing.

--opt

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by --opt.

Backup a MySQL Database

Places to Visit

Places to Visit

India:
Taj Mahal, Red Fort Delhi - Visited - 2006

Basilica Old Goa - UNESCO - Visited

Australia:
Sydney Opera House
http://en.wikipedia.org/wiki/Sydney_Opera_House

Australia Zoo

Egypt:
Egyptian Pyramids
http://en.wikipedia.org/wiki/Egyptian_pyramids


Germany:
Magdeburg Water Bridge
http://en.wikipedia.org/wiki/Magdeburg_Water_Bridge

Autobahn
http://en.wikipedia.org/wiki/Autobahn

France:
Eiffel Tower
http://en.wikipedia.org/wiki/Eiffel_Tower

USA:
Niagara Falls
http://en.wikipedia.org/wiki/Niagara_Falls

Hollywood
http://en.wikipedia.org/wiki/Hollywood

Disneyland
http://en.wikipedia.org/wiki/Disneyland

Places to Visit

Find and Replace a Matching String of Text

Find and Replace a Matching String of Text

This script will find all .html files (in the current directory and all subdirectories) and replace the string of text 123 with 456

#!/bin/bsh

for file in $(find . -type f -name '*.html')

do

cat $file |sed "s|123|456|g" > $file.new

mv -v $file.new $file

done

Prefix and escape double quotes with a backslash, (double quotes are used by SED)

#!/bin/bsh

for file in $(find . -type f -name '*.php')

do
cat $file |sed "s|include \"file.php\";|require_once (\'/path/file.php\');|g" > $file.new

mv -v $file.new $file
done


Find and Replace a Matching String of Text

Archive and Compress a Directory

Archive, ZIP/Unzip and Compress/Decompress a Directory

$ tar -zcvf archive.tar.gz sourcedirectory/

Exclude a directory(s) from the archive:

$ tar -zcvf archive.tar.gz sourcedirectory/ --exclude=exclude01-dir/* --exclude=exclude02-dir/subdir/*


ZIP

$ zip db.zip foo-file

$ zip -r dir.zip directory/

Extract contents of a GZ archive
$ gunzip archive.sql.gz

or

$ gzip -d archive.sql.gz


Extract contents of a TAR GZ Archive
$ tar -zxvf archive.tar.gz

Recursively Copy a Directory

Recursively Copy a Directory

cp -r source newdestination

Note: newdestination does not need to exist

Recursively Copy a Directory

Firefox List of Installed Plugins

Firefox List of Installed Plugins

Last updated: Sun, 17 Dec 2006 16:23:01 GMT

User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1) Gecko/20061010 Firefox/2.0

Extensions (enabled: 7, disabled: 1):

• Adblock 0.5.3.043
• CustomizeGoogle 0.55
• del.icio.us 1.2
• FlashGot 0.5.97.02
• InfoLister 0.9f
• infoRSS 1.1.1 [disabled]
• netcrafttoolbar 1.1.1.7
• Sage 1.3.9

Themes (3):

• Firefox (default) 2.0
• Qute 3.2.2 [selected]
• Winestripe 1.2.1

Plugins (9):

• Adobe Acrobat
• DivX Player Netscape Plugin
• DivX® Web Player
• Java(TM) 2 Platform Standard Edition 5.0 Update 6
• Microsoft® DRM
• Mozilla Default Plug-in
• QuickTime Plug-in 7.1.3
• Shockwave Flash
• Windows Media Player Plug-in Dynamic Link Library

Firefox List of Installed Plugins

SPAM and the Catch All Feature

SPAM and the Catch All Feature

One of our domains had been receiving a huge amount of SPAM. The Catch All feature had been enabled due to the fact that usernames could be created on-the-fly and used without creating inboxes.

With this "feature" enabled, a spammer could load up a dictionary with a list of usernames and blast the domain with SPAM. The reason why all email would be received is because Catch All does not bounce non-existent addresses/usernames. Instead, it forwards all incoming email to a POP email address.

We disabled the feature today and voila! SPAM levels reduced by 90%!!

SPAM and the Catch All Feature

Procedure to Name MySQL DB's

I've been using the following method as the naming convention to name DB's/tables.

DB name-> host_dbtitle

Table name-> dbtitle_tableTopic

Foreign keys are created across all tables and _not_ on the last relation only.

PHP Extract a String that does not Match a given String of Text

PHP Extract a String that does not Match a given String of Text

Use explode, to strip and remove a line of text or word that matches "Foo"

$string = "Test Foo Bar";
$split = explode("Foo",$string);
$strip0 = $split[0];
$strip1 = $split[1];

echo "$strip0"; => Test
echo "$strip1"; => Bar

The [0] var gets the char previous to the match
The [1] var gets the char after the match

Tracking a User Posing as You on Social Networking Sites - Orkut, Hi5 et al

Tracking a User Posing as You on Social Networking Sites - Orkut, Hi5 et al

Requirements:
Pen and a book (papers get misplaced)
Or
Create a new word/notepad file

This list is by no means complete. It is not a guarantee that you will find the user behind the fake or spoofed account. This is a checklist of to-dos, just so that you can go through before hitting the panic button.

The first step is to note the date/time when you became aware of your fake account. So, if you know about the fake account at 9 in the morning on 1st January, make sure you enter the time in the diary as January 1st, 9AM and _not_ January 1st, 12PM or whatever else comes to your mind.

This will help you backtrack a couple of days and figure out if you pissed off a friend earlier. If you _did_ create foes lately, do _not_ jump to conclusions and accuse them. What you really need is evidence.

You are trying to piece together a puzzle and not accuse.

- How to gather the evidence?

+ Begin by checking the fake profile for patterns.
Look for words and sentences and compare them with emails you received from friends, your ex etc. Check the alphabet case. Are certain words in upper or lowercase and can these be matched with previous emails or letters?

+ Repeat the steps above and check for patterns in scraps (Orkut) and emails or letters received from the creator of the fake account.

+ Note the date/time when you receive messages from the spoofed account.
Does the person reply at a certain time? Does the time correspond with your local time zone? If you send a message at 9 in the morning does the reply come in within a few minutes or at midnight? If the message is received within a short period of time, then most likely the person is in the same time zone as you.

Does the person reply on Sundays? If not, then the person is probably at work and sending the emails or messages from a work computer.

+ You could begin an email conversation with the person behind the fake account and again look for patterns in the replies. Do _not_ ask questions such as, who are you? Where you from? The person has to be a real moron to tell you who (s)he really is.

+ Photos
If the fake account contains your photos, try to think. Do all your friends and family have copies of these photos? Or did you send the photos to a group of friends or a person? Is it possible that the photos were grabbed online from a public space? If so, who had access to them and when?

+ Trace and log the IP Address
Every email sent contains an IP address. The IP address can be used to trace the source Internet Service Provider. Check the email headers for the IP address from the emails received. Trace the IP and find the name of the Internet Service Provider. Pinpoint the telephone if the IP address is in the same city as you.

Does the IP address point to a company? Try to think of people you may know who work there and see if someone can help you out.

Remember to note the IP address along with the sent and received timestamp. An email header is like a diary, it logs detailed information as it makes its way to your Inbox. The time and date that an email was sent could be different from the time and date when you received it.

+ Talk
Bring up the conversation with your friends. Look around and listen to what they say.
Does someone know more than you?
Could a buddy in your group know who is behind the fake account?
Does a person you know have a history of creating fake accounts? If so, watch him/her closely.
Ask questions, look for clues

+ Check the friend list in the fake account
Go through the friend list and check if they were added in the same day?
It is likely that the creator of the account will know those friends.
List the common friends between you and those on the fake account.
Check the messages (for patterns) that your friends received from the fake account.

+ Know your Information Technology laws
Get on a search engine (http://www.google.com) and do a search for:
your-country-name information technology law
(substitute your-country-name with your country)
This will help you better understand the issues and discuss with the authorities.

+ Law enforcement
Send an email warning the person that you take up the matter with the authorities. If (s)he does not comply then file a complaint. The email should be sent with your full name and not from the fake account.

FTP Upload Files Through Windows DOS Prompt

FTP Upload Files Through Windows DOS Prompt

Listing a huge list of directories and files from a server in a FTP client can be time consuming. This solution involves uploading files directly through the Windows FTP program.

To upload files to the server without a FTP client, create the following two files in the d:\ftp directory:

upload-ftp.bat (copy and paste the contents below)

d:\
cd ftp
ftp -s:files.txt

(Note: d: is the drive where these two files are located)

files.txt (copy and paste the contents below)

open server-name.com
username
password
bin
prompt
cd /var/www/remotedir
send file1.zip
send file2.zip
send file3.zip
send file4.zip
quit

Double click to run the file upload-ftp.bat. The zip files to be uploaded need to be in the same directory (d:\ftp\ in this case) as the two files above.

Note: Ensure that the remote directory exists before uploading the files.

CRON line 1: Unexpected EOF while looking for matching ``'

CRON line 1: Unexpected EOF while looking for matching ``'

When running a CRON job that uses the command:
NOW=`date +%B_%d_%a_%Y`

the script will abort execution and display the following error:

/bin/sh: -c: line 1: unexpected EOF while looking for matching ``'
/bin/sh: -c: line 2: syntax error: unexpected end of file

To prevent this issue from occurring, dump the contents of the script into a .sh file and run the .sh from CRON.

Sometimes, CRON is set to use sh as opposed to the bash shell that the script needs.

The CRON shell can be modified in the /etc/crontab config file. Requires r00t privileges. Ensure that the CRON daemon is restarted after the modifications.

/sbin/service crond restart

Frequently Used .htaccess Directives in Apache

Frequently Used .htaccess Directives in Apache

# Force www
RewriteEngine On
RewriteCond %{HTTP_HOST} !^www.domain\.com$ [NC]
RewriteRule (.*) http://www.domain.com/$1 [R=301,L]
or
RewriteRule ^(.*)$ http://www.domain.com/$0 [R=301,L]

# Add mod-rewrite rules (if needed)
# This checks if a file or directory exists before calling the var
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

# This sends the var to the PHP file
RewriteRule ^([A-Za-z0-9_-]+) http://www.domain.com/foo/bar/get.php?var=$0 [PT]

# Disable and turn off PHP register globals
php_flag register_globals OFF

# Enable PHP errors
php_flag display_errors ON

# Disable directory listing
IndexIgnore *


# Override common PHP settings
php_value post_max_size 16M
php_value upload_max_filesize 20M
php_value memory_limit 25M
php_value max_execution_time 900
php_value session.gc_maxlifetime 7200


# Hide the directory indexes
Options All -Indexes

# Show the directory indexes
Options All +Indexes


# Disable access and prevent viewing of htaccess

opentag Files .htaccess closetag
order allow,deny
deny from all
opentag /Files closetag

Alternatively,
CHMOD .htaccess to 644 or RW-R--R--

# Disallow or prevent hotlinking of images, photos or any other file type
RewriteEngine on
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} !^http://(www\.)?domain.com/.*$ [NC]
RewriteRule \.(gif|jpg)$ - [F]


# Redirect an old path to a new one
# Redirect an old file to a new file
Redirect /old-dir/foo.html http://www.domain.com/foo/new.html

# Redirect an old directory to a new directory
Redirect /old-dir/ http://www.domain.com/new-dir/

# Set the default index file
DirectoryIndex index.html
or

# Set multiple files as the default if the first doesn't exist
DirectoryIndex index1.html index2.php index3.shtml foo.htm

# Block or ban offline browsers or leechers
RewriteEngine On
RewriteCond %{HTTP_USER_AGENT} ^wget [OR]
RewriteCond %{HTTP_USER_AGENT} ^flashget [OR]
RewriteCond %{HTTP_USER_AGENT} ^getright
RewriteRule ^.* - [F,L]

# Ban traffic from a single or multiple domains
RewriteEngine on
# Options +FollowSymlinks
RewriteCond %{HTTP_REFERER} foo\.com [NC]
RewriteRule .* - [F]

or

RewriteEngine on
# Options +FollowSymlinks
RewriteCond %{HTTP_REFERER} foo1\.com [NC,OR]
RewriteCond %{HTTP_REFERER} foo2\.com
RewriteRule .* - [F]

preg_replace(): Error - Delimiter must not be alphanumeric or backslash

preg_replace(): Error - Delimiter must not be alphanumeric or backslash

preg_replace() will output a delimiter error if the "$pattern" does not have a / delimiter or if quotes are used when calling the preg_replace() function itself.

The Error:

$patterns[0] = '/PHP/4.4.4/';
$replacements[0] = "FooBar!";

preg_replace("$patterns",$replacements,"$string[$counter]");

The above will result in the following error:
"preg_replace(): Parameter mismatch, pattern is a string while replacement in an array"


Corrected Version:

// Delimiter is /
// Note: the forward slash after PHP is to be escaped with a backslash
// The i after the / is for case-insensitive matches. This means, you can match lower case words with upper case words and vice-versa

$patterns[0] = '/PHP\/4.4.4/i';
$replacements[0] = "FooBar!";

// Double quotes are to be removed
preg_replace($patterns,$replacements,"$string[$counter]");

CRON Fields

CRON Fields

# (Use to post in the top of your crontab)
# ------------- minute (0 - 59)
# | ----------- hour (0 - 23)
# | | --------- day of month (1 - 31)
# | | | ------- month (1 - 12)
# | | | | ----- day of week (0 - 6) (Sunday=0)
# | | | | |
# * * * * * command to be executed

* The comma (',') operator specifies a list of values, for example: "1,3,4,7,8"
* The dash ('-') operator specifies a range of values, for example: "1-6", which is equivalent to "1,2,3,4,5,6"
* The asterisk ('*') operator specifies all possible values for a field. For example, an asterisk in the hour time field would be equivalent to 'every hour'..

Source: http://en.wikipedia.org/wiki/Crontab

Delete All Email Using MUTT, Through SSH

Delete All Email Using MUTT, Through SSH

Since I login through SSH frequently, I use the MUTT email client to check email generated through automated scripts, logs etc. There were 40,000 emails that needed to be deleted quickly. I've been unable to find a "select all" option.

The solution involves, pressing the SHIFT and t key simultaneously. This will bring up the "tag messages matching feature". Enter the match that is common with all the emails. Eg: / and hit enter. (this will flag all the matching email with a *)

shift + t
Tag messages matching: /
;d
$

OR

q
yes

The $ key will sync mutt - deleted emails will be purged, mailbox updated etc.

Disable CRON Output From Flooding Email

Disable CRON Output From Flooding Email

When a script is executed through CRON, the output can quickly flood an inbox. To disable output from specific scripts, append the following at the end of the script:

# Disable output completely
*/2 * * * * /usr/local/bin/php /test/foobar.php > /dev/null 2>&1

This script runs every two minutes.
Standard output (1) is redirected to /dev/null
Standard error (2) is directed to the same as standard output (1)

# Redirect script output to a log file
*/2 * * * * /usr/local/bin/php /test/foobar.php > /localpath/log.txt

# Redirect script output to a log file, append log
*/2 * * * * /usr/local/bin/php /test/foobar.php >> /localpath/log.txt

# Redirect script output and CLI error output to the same log file
*/2 * * * * /usr/local/bin/php /test/foobar.php > /localpath/log.txt 2>&1

Note:
/localpath/log.txt - This is standard output (1)
The 2 in the 2>&1 will direct standard error (2) to standard output. In this case, log.txt

# Redirect script output and CLI error output to a different log file
*/2 * * * * /usr/local/bin/php /test/foobar.php > /localpath/log.txt 2>/localpath/cli-errors.txt

# Redirect script output and CLI error output to a different log file, append log
*/2 * * * * /usr/local/bin/php /test/foobar.php >> /localpath/log.txt 2>>/localpath/cli-errors.txt

To test:
Execute this command
ls . IS >> errors.txt 2>>errors.txt

The second alternative is to modify the MAILTO= option and set it to "". This will however, disable all output from being sent to the email address specified in the MAILTO feature.

Another option would be to redirect the output to a text file and call the text file through the browser for analysis.

Note:
The php -q flag suppresses HTTP header output.

Find and Replace the Exact Match in a String

To find and replace the exact match in a string, use the function ereg_replace. ereg will find the exact match and not a pattern.

// Strip the extra --
$string= ereg_replace("--","-",$string);

// Replace \n with a br tag
$string = str_replace("\n","
",$string);

// Find and replace multiple needles in a haystack

$patterns[0] = '/>/';
$patterns[1] = '/1/';
$patterns[2] = "/2/";
$patterns[3] = '/3/';
$patterns[4] = '/4/';

// Escape non alpha characters
$patterns[5] = '/\"/';
$patterns[6] = '/\+/';
$patterns[7] = '/\'/';
$patterns[8] = '/\./';
$patterns[9] = '/Some Text/';

$replacements = '-';

$data = preg_replace($patterns, $replacements, $data);

PHP DOCUMENT_ROOT Include does not work with CRON

When including libraries or external files in PHP, the variable $_SERVER['DOCUMENT_ROOT'] will not call the external files if the script is run through CRON.

This is because, usually a PHP script would be executed through a /usr/local/bin/php -q directive. Since Apache does not play a role here, the DOCUMENT_ROOT variable will not work.

To ensure that the DOCUMENT_ROOT works, call the script through curl, wget or lynx. As a security measure, apps like wget are disabled on most servers. The alternative option is to get rid of the DOCUMENT_ROOT variable all together.

When including files in PHP scripts, it is best to create an includes.php in the base dir of the application. All scripts in the sub-directories can call the include file through a define path. The includes file can in turn define paths to other dependencies.

Note:

The -q flag suppresses HTTP header output. As long as your script itself does not send anything to stdout, -q will prevent cron from sending you an email every time the script runs. For example, print and echo send to stdout. Avoid using these functions if you want to prevent cron from sending you email.

Note:
The ../dirname directory include path does not work with cron too. The path needs to be included in full

Source:
http://www.modwest.com/help/kb5-125.html

Further Reference:
http://www.us2.php.net/features.commandline