Shantanu's Blog

aws command prompt

2012-02-11T19:16:00.000-08:00

In order to use S3 and ec2 Simple Command-Line Access to AWS we need to install an utility:

http://aws.amazon.com/developertools/Amazon-SimpleDB/739

Here are the steps to follow. This will help to manage the buckets using command prompt and there is no need to use the web console.

curl https://raw.github.com/timkay/aws/master/aws -o aws
chmod +x aws
vi ~/.awssecret
(type key and secret key)
chmod go-rwx ~/.awssecret

perl aws --install

s3mkdir shantanuo_BUCKET_NAME
s3put shantanuo_BUCKET_NAME test.txt
s3ls shantanuo_BUCKET_NAME
s3get shantanuo_BUCKET_NAME/test.txt
s3delete shantanuo_BUCKET_NAME/test.txt
s3delete shantanuo_BUCKET_NAME

ec2run
ec2din --simple
ec2tin INSTANCEID

AWS product stack

2012-02-11T01:03:00.001-08:00

Here is the list of tabs those I can see when I log in. I am not impressed by most of them, but EC2, RDS and DynamoDB will prove miracles in the hosting world.

Elastic Beanstalk: makes it even easier to deploy and manage scalable applications on the AWS cloud, while retaining the ability to control the underlying resources.

S3: Buckets to store data that can also be shared on the net.

EC2: Start or terminate different type of virtual servers.

VPC: Virtual Private cloud for your EC2 resources.

CloudWatch: monitors operational and performance metrics for your AWS cloud resources and applications.

Elastic MapReduce: Creating a job flow to process your data in a simple and quick way.

CloudFront: Allow public access to your S3 buckets

CloudFormation: pre installed Drupal, Joomla or wordpress

RDS: hosted Relational Database Systems like MySQL / Oracle

ElastiCache: a web service that makes it easier to launch, manage, and scale a distributed in-memory cache in the cloud.

SQS: Simple Queue Service offers a reliable, highly scalable, hosted queue for storing messages as they travel between applications.

IAM: Identity and Access Management (IAM) enables you to manage Users, groups and permissions within your AWS account.

SNS: Simple Notification Service (SNS) is a web service that makes it easy to set up, operate, and send notifications in the cloud.

SES: Simple Email Service (SES) allows you to send emails.

Route 53: Allows you to manage your domain name and sub-domains.

DynamoDB: a fully managed non-relational fast database service that comes with seamless scalability.

Storage Gateway: An on-premises virtual appliance that provides seamless and secure integration between your appliances and AWS's storage infrastructure.

Import, Export and SQLize DynamoDB

2012-02-10T19:41:00.000-08:00

You can use Hive to export data from DynamoDB to S3 bucket using Hadoop

In order to use hadoop, you need Elastic MapReduce instance. Click on "Create New Job Flow" and use defauls to create a new job called "My Job Flow". You will now get the Master Public DNS Name to connect to using "hadoop" as username. Once connected, type "hive" to get the command prompt where you can link to DynamoDB table.

hive> CREATE EXTERNAL TABLE hivereply (col1 string, col2 string, col3 string, col4 string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "reply",
"dynamodb.column.mapping" = "col1:Id,col2:ReplyDateTime,col3:Message,col4:PostedBy");

You can now use the table hivereply just like any other MySQL table. For e.g.
hive> select * from hivereply where col2 = '2012-01-31 23:09:46';

Amazon DynamoDB#DynamoDB Thread 2 2012-01-31 23:09:46 null

You can call the INSERT OVERWRITE command to write the data to an external directory. You can use this to create an archive of your Amazon DynamoDB data in Amazon S3.

hive> INSERT OVERWRITE DIRECTORY 's3://php-sdk-getting-started-aki-19/' SELECT * FROM hiveTableName;

Using Comparision Operator in dynamodb

2012-02-10T07:59:00.000-08:00

If you want to query the data, you need to index it on Range key. For e.g. in the following "reply" table we have a primary key that is combination of Hash key and a Range key. Range key is nothing but the timestamp as on the reply was received. We can write a query that will return all the replies those are older than 7 days.
In this case the column "ReplyDateTime" column is indexed and we can use Comparison Operator.


require_once 'sdk.class.php';

$ten_days_ago = date('Y-m-d H:i:s', strtotime("-10 days"));

// Instantiate the class
$dynamodb = new AmazonDynamoDB();

$add_response = $dynamodb->put_item(array(
    'TableName' => 'reply',
    'Item' => array(
        'Id'            => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ), // Hash Key
        'ReplyDateTime' => array( AmazonDynamoDB::TYPE_STRING => $ten_days_ago                        ), // Range Key
        'Message'       => array( AmazonDynamoDB::TYPE_STRING  => 'DynamoDB Thread 2 Reply 1 text'   ),
        'PostedBy'      => array( AmazonDynamoDB::TYPE_STRING => 'User A'                            ),
    )
));

// Success?
print_r($add_response);


$seven_days_ago = date('Y-m-d H:i:s', strtotime("-7 days"));

$response = $dynamodb->query(array(
    'TableName' => 'reply',
    'HashKeyValue' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ),
// optional parameters
    'AttributesToGet' => array( 'ReplyDateTime', 'Message', 'PostedBy' ),
    'ConsistentRead' => true,
    'RangeKeyCondition' => array(
        'ComparisonOperator' => AmazonDynamoDB::CONDITION_LESS_THAN_OR_EQUAL,
        'AttributeValueList' => array(
            array( AmazonDynamoDB::TYPE_STRING => $seven_days_ago )
        )
    )
));

// 200 response indicates Success
print_r($response);

NoSQL support by AWS

2012-02-10T04:42:00.000-08:00

Click on "Create Table" button on "DynamoDB" tab while using AWS web Management Console.

https://console.aws.amazon.com/dynamodb/home

Type table name "testme1" and choose Number as Primary Key Type. Hash attribute name can be "Id". Assuming that you have correctly installed AWS SDK, the following PHP code should add a key - value to the table testme1 table.


require_once 'sdk.class.php';

// Instantiate the class
$dynamodb = new AmazonDynamoDB();

$add_response = $dynamodb->put_item(array(
    'TableName' => 'reply',
    'Item' => array(
        'Id' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ), // Hash Key
        'ReplyDateTime' => array( AmazonDynamoDB::TYPE_STRING => '2012-01-31 23:28:40' ), // Range Key
        'Message' => array( AmazonDynamoDB::TYPE_STRING => 'DynamoDB Thread 2 Reply 1 text' ),
        'PostedBy' => array( AmazonDynamoDB::TYPE_STRING => 'User A' ),
    )
));

// Success?
print_r($add_response);

_____

get item is obviously similar to put_item as shown above:


$get_response = $dynamodb->get_item(array(
    'TableName' => 'reply',
    'Key' => array(
        'HashKeyElement' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' )
    )
));

_____

In order to drop the table, use the following:


$delete_response = $dynamodb->delete_table(array(
    'TableName' => 'reply'
    )
);

_____

The following code will create a table called "reply":


$create_response = $dynamodb->create_table(array(
    'TableName' => 'reply',
    'KeySchema' => array(
        'HashKeyElement' => array(
            'AttributeName' => 'Id',
            'AttributeType' => AmazonDynamoDB::TYPE_STRING
        ),
        'RangeKeyElement' => array(
            'AttributeName' => 'ReplyDateTime',
            'AttributeType' => AmazonDynamoDB::TYPE_STRING
        )
    ),
    'ProvisionedThroughput' => array(
        'ReadCapacityUnits' => 10,
        'WriteCapacityUnits' => 5
    )
));

_____

Here are 3 easy steps you may need to follow if you have not already downloaded and configured Software Development kit from Amazon.

# Download AWS SDK for PHP
svn co http://svn.github.com/amazonwebservices/aws-sdk-for-php.git AWSSDKforPHP
cd AWSSDKforPHP
_____

# copy sample to config file
mv config-sample.inc.php config.inc.php
_____

# add your actual key and secret found on "security credentials" page that can be found here...
# https://aws-portal.amazon.com/gp/aws/securityCredentials

vi config.inc.php

'key' => 'ABCD',
'secret' => 'XYZ',
'default_cache_config' => '/tmp/',

Hashes and sets in redis

2012-01-05T05:12:00.000-08:00

Hashes

Redis's implementation of hashes makes for a perfect solution to store the object data application typically use.

Let's also assume we want to store a number of fields about our users, such as a full name, email address, phone and number of visits to our application. We will use Redis's hash management commands like HSET, HGET and HINCRBY to store this information.

redis> hset users:jdoe name "John Doe"
redis> hset users: jdoe email "jdoe@test.com"
redis> hset users: jdoe phone "+1234567890"
redis> hincrby users:jdoe visits 1

With our has built and in place, we can fetch single fields with HGET or the full hash by using the HGETALL command, for e.g.

redis> hget users:jdoe email
"jdoe@test.com"
redis> hgetall users:jdoe
1) "name"
2) "John Doe"
3) "email"
4) "jdoe@test.com"
5) "phone"
6) "+1234567890"
7) "visits"
8) "1"

There are other commands like HKEYS, which return the keys stored in a particular has, and HVALS, which returns only the values. Depending on how you want to retrieve your data, you may find it useful to use HGETALL or one of these to retrieve data from Redis into your application.

redis> hkeys users:jdoe
1) "name"
2) "email"
3) "phone"
4) "visits"

redis> hvals users:jdoe
1) "John Doe"
2) "jdoe@test.com"
3) "+1234567890"
4) "1"

sets

Sets are a natural fit for circles, because sets represent collections of data, and have native functionality to do interesting things like intersections and unions.

redis> sadd circle:jdoe:family users:anna
redis> sadd circle:jdoe:family users:richard
redis> sadd circle:jdoe:family users:mike

redis> sadd circle:jdoe:soccer users:mike
redis> sadd circle:jdoe:soccer users:adam
redis> sadd circle:jdoe:soccer users:toby
redis> sadd circle:jdoe:soccer users:apollo

redis> smembers circle:jdoe:family
1) users:anna
2) users:richard
3) users:mike
redis> hgetall users:mike
(...)

redis> sinter circle:jdoe:family circle:jdoe:soccer
1) "users:mike"

redis> sunion circle:jdoe:family circle:jdoe:soccer

1) users:anna
2) users:richard
3) users:mike
4) users:adam
5) users:toby
6) users:apollo

redis tips

2012-01-04T01:58:00.000-08:00

BGSAVE can cause redis to hang upto 20 minutes and lose data. The info command will let us know the peak memory used.
used_memory_peak_human:5.46G

This shows peak usage of 5.46G. When you get close to the edge of your RAM limits, redis can start to misbehave on BGSAVE.

Something else to check -- find your .rdb file and make sure it's timestamp is getting updated (ls -l) after the bgsave completes. While the bgsave
processes, you'll see a temp rdb file next to it growing. It should grow by 10's to 100's megs a second.

___

You can write your own pooling layer in PHP:

$connection = $pool->getConnection(...);
...
$pool->releaseConnection($connection);

MySQL SSL connection

2011-12-12T20:36:00.000-08:00

Usually ssl is installed but "disabled" by default. Here are 6 easy steps to create required files to connect using SSL

mysql> show variables like '%have_ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_ssl | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)

# which openssl
/usr/bin/openssl

# cd /var/lib/mysql/
# mkdir certs
# cd certs
# openssl genrsa 2048 > ca-key.pem
# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem > ca-cert.pem
# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem > server-req.pem
# openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem > client-req.pem
# openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

# Create SSL User:
GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;

# Update my.cnf and restart mysql
[mysqld]
ssl-ca="/var/lib/mysql/certs/ca-cert.pem"
ssl-cert="/var/lib/mysql/certs/server-cert.pem"
ssl-key="/var/lib/mysql/certs/server-key.pem"

After restart, connect via SSL with your newly created SSL user
mysql -ussluser -pssluser -P3430 --ssl-key=

You can verify with the ‘status’ command once connected:
mysql> status
Current user: ssluser@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA

redis, who?

2011-11-10T03:37:00.000-08:00

Redis is an open source, advanced key-value store. Basically, if you can map a use case to Redis and discover you aren't at risk of running out of RAM by using Redis there is a good chance you should probably use Redis.

A few examples:

# You can sort by create timestamp if that is stored in epoch time.

> HMSET users:1 firstname 'john' lastname 'smith' created 1319729878
"OK"
> HMSET users:2 firstname 'Jane' lastname 'Forbes' created 1319729910
"OK"
> sadd users 1
true
> sadd users 2
true
> sort users get users:*->firstname by users:*->created
["john","Jane"]
> sort users get users:*->firstname by users:*->created desc
["Jane","john"]
_____

APPLE: RED ROUND FRUIT would map to the following inserts:

SADD RED:ROUND:FRUIT APPLE
SADD :ROUND:FRUIT APPLE
SADD RED::FRUIT APPLE
SADD RED:ROUND: APPLE
SADD RED:: APPLE
SADD :ROUND: APPLE
SADD ::FRUIT APPLE
SADD ::: APPLE

_____

redis 127.0.0.1:6379> SADD P1:YELLOW MANGO
(integer) 1
redis 127.0.0.1:6379> SADD P2:TASTE MANGO
(integer) 1
redis 127.0.0.1:6379> SADD P3:FRUIT MANGO
(integer) 1
redis 127.0.0.1:6379> SINTER P1:YELLOW P2:TASTE
1) "MANGO"

_____

redis 127.0.0.1:6379> HMSET id:4532143215432 username davejlong email dave@davejlong.com
OK
redis 127.0.0.1:6379> HMSET user:davejlong id 4532143215432 email dave@davejlong.com
OK
redis 127.0.0.1:6379> HGET id:4532143215432 username
"davejlong"
redis 127.0.0.1:6379> HGET user:davejlong id
"4532143215432"
redis 127.0.0.1:6379> HMGET user:davejlong email id
1) "dave@davejlong.com"
2) "4532143215432"
redis 127.0.0.1:6379> DEL user:davejlong
(integer) 1
redis 127.0.0.1:6379> DEL id:4532143215432
(integer) 1

_____

Redis can be easily used in shell script or at command prompt.
It will be extremely useful for storing the standard out data of other commands.

[root@server]# echo -n "testing" | /pat/to/redis/src/redis-cli -x set my_pass > /dev/null 2>&1
[root@server]# echo $?
0

[root@server]# /pat/to/redis/src/redis-cli --raw get my_pass
testing

_____

We can write a shell script to import the data from mysql or use a tool like "awk"

#!/bin/sh
mysql pdb_name -Bse"select comments, concat(fee, status) as myfee from company limit 100000;" | while read -r account myfee
do
echo -n $myfee | /home/redis-2.2.12/src/redis-cli -h 10.10.10.100 -p 6379 -x set $account > /dev/null 2>&1

done
_____

mysql pdb_name -Bse"select comments, concat(fee, status) as myfee from company limit 100000;" | awk "{print \"set \" \$1 \" \" \$2}" | /home/redis-2.2.12/src/redis-cli -h 10.10.10.100 -p 6379 > /dev/null 2>&1

_____

redis> select 3
All subsequent commands will then use database 3, until you issue another SELECT

redis> flushdb
drop all the data in a single database

redis> FLUSHALL
drop all the data in all databases

in redis.conf — by default, it is set to 16. Simply set it to a higher number if you need more:
databases 42

Using Splunk with forwarding

2011-10-28T02:13:00.000-07:00

Splunk is a log, monitoring and reporting tool for IT system administrators with search capabilities. It crawls logs, metrics, and other data from applications, servers and network devices and indexes it in a searchable repository from which it can generate graphs, SQL reports and alerts.

#### Setup splunk as a Reciever #####

Install splunk:
splunk-4.2.4-110225-linux-2.6-x86_64.rpm (26M)

start splunk:
# /opt/splunk/bin/splunk start

Splunk web UI is here...
http://localhost:8000

1. Login to WebUI using the above mentioned credentionals. eg. http://10.10.10.35:8000
2. Go to Manager » Forwarding and receiving » Receive data
3. Click on New Button and add default port i.e. 9997
4. Click on save button to save the settings.
Note: If you are running any firewall please allow the above Port.

####### Setup Splunk as a Forwarder ####
Install forwarder in addition to splunk as mentioned above:
splunkforwarder-4.2.4-110225-linux-2.6-x86_64.rpm (13M)

# /opt/splunk/bin/splunk start
# ./splunk enable app SplunkLightForwarder -auth
Splunk username: admin
Password: changeme

# ./splunk add forward-server 10.10.10.35:9997 -auth admin
# ./splunk restart
# ./splunk add monitor /etc/hosts

_____

Splunk can also be used to search within data.

time mysqldump -h10.10.10.10 -uroot -proot@123 -P3311 -f --databases db1 db2 db3 db4 --skip-extended-insert --skip-lock-tables --ignore-table=db1.balance_extra --ignore-table=db1.fee_log --ignore-table=db1.transaction > search_selected_data.txt
_____

It is recommended to create a new index at the time of adding a file to splunk. The index file can be removed using the following syntax.

// Remove the source from future searches events
source='search_selected_data.txt' | delete

// delete indexed data
source='search_selected_data.txt' | clean
_____

// Splunk will not merge lines into combined events.
set SHOULD_LINEMERGE to "false" in props.conf

The above setting will affect all searches. To apply the new setting to a single source,

[source:///path/to/file]
SHOULD_LINEMERGE = false

or to do it on a sourcetype level,

[your_sourcetype]
SHOULD_LINEMERGE = false

Object oriented Python

2011-09-23T05:11:00.001-07:00

Main advantage of using python is that it is a truly Object oriented scripting language.

outfile = open('/home/shantanu/test.txt', 'w')
outfile.write('this is test')

The above will not explicitly close the file. Following are the 2 ways to close the file once it is not required anymore.

outfile = open('/home/shantanu/test.txt', 'w')
try:
outfile.write('this is test')
finally:
outilfe.close()

_____

with open(r'/home/shantanu/test1.txt', 'w') as myfile:
outfile.write('this is test1')

_____

Compare it with PHP and you will see what I mean. PHP seems to be more like variables and function prone language.

$fh = fopen("testfile.txt", 'w');
fwrite($fh, "adding one line");
fclose($fh);
?>

_____

Finally nothing can beat shell, since it is so robust, logical and have direct approach like this...

echo "shell is the king" > /home/shantanu/testfile.txt

Using performance schema to know the query locks

2011-09-23T03:16:00.000-07:00

The following line should be added to my.cnf to enable performance schema.
performance-schema

It is not enabled by default because it has some performance over-heads. You can now run the following queries to find the thread ID's those are having issues with mysql query locks.

select THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, OBJECT_INSTANCE_BEGIN, OPERATION
FROM performance_schema.events_waits_current
WHERE THREAD_ID in(select LOCKED_BY_THREAD_ID FROM performance_schema.mutex_instances where LOCKED_BY_THREAD_ID is not null);

select * from performance_schema.threads;

select * from performance_schema.mutex_instances where LOCKED_BY_THREAD_ID is not null;

Script to search locked threads

2011-08-29T05:07:00.001-07:00

The following script will pick up the thread-id from innodb status page and select the last queries executed by that thread.

#!/bin/sh

mysql -e"show engine innodb status\G" | grep "MySQL thread id " | awk -F"," '{print $1}' | replace 'MySQL thread id' '' | tail | while read -r connectid
do
tail -1000000 /var/log/mysql/general.log | awk '$1 == "'$connectid'" || $3 == "'$connectid'" { print $0 }' | head
tail -1000000 /var/log/mysql/general.log | awk '$1 == "'$connectid'" || $3 == "'$connectid'" { print $0 }' | tail
done

Analysing general query log

2011-08-15T23:57:00.000-07:00

Here are a few commands to know the schema, IP accessing the data using general log.

time grep -w Connect /var/log/mysql/general.log | awk -F'Connect' '{print $2}' > connectme.log

// connection string
sort connectme.log | uniq -c | sort -n
3350 application_user@10.10.11.26 on db1_16JUL2011
7330 application_user@172.16.55.133 on FWQAdb2
7716 application_user@10.10.11.14 on FWQAdb3
10084 application_user@172.16.155.133 on FWQAdb4

// schema, user and IP count
awk -F' on' '{print $2}' connectme.log | sort | uniq -c | sort -n
4528 db1_29062011
5027 db2_16JUL2011
9295 FWQAdb3
10084 FWQAdb4
10488 FWQAdb5

awk -F'@' '{print $1}' connectme.log | sort | uniq -c | sort -n
46 amar
28450 akbar
31453 anthony
41646 dbagroup

awk -F'@' '{print $2}' connectme.log | awk '{print $1}' | sort | uniq -c | sort -n
4189 10.10.11.167
7157 10.10.11.26
8600 10.10.10.67
9073 10.10.11.14
17414 172.16.155.133

// failed connections analysis
grep 'Access denied' connectme.log | awk -F'@' '{print $1}' | awk '{print $NF}' | sort | uniq -c | sort -n
1 'amar'
1 'akbar'
13 'anthony'
16 'user1'
831 'user2'

grep 'Access denied' connectme.log | awk -F'@' '{print $2}' | awk '{print $1}' | sort | uniq -c | sort -n
6 '10.10.10.69'
26 '10.10.11.140'
815 '10.10.11.57'

new data-directory for MySQL

2011-08-12T00:48:00.000-07:00

Sometimes you need fresh mysql installation. You need to run script to create default mysql database and system tables. The script is called "mysql_install_db" and is either in the bin or in scripts directory of your mysql installation. Change the my.cnf to reflect new datadir and run mysql_install_db for it to create mysql database and system tables in the new datatir and then start mysqld_safe.

/usr/bin/mysql_install_db --datadir=/mnt/mysql3310

Resetting root password

2011-08-07T00:48:00.000-07:00

If you have forgotten your root password, you will need to start mysql service with --skip-grant-tables mode and then run the following 2 commands to reset it to root / root@123

UPDATE mysql.user SET Password = password('root@123') WHERE Host = 'localhost' AND User = 'root';

REPLACE INTO mysql.user VALUES ('%','root','*A00C34073A26B40AB4307650BFB9309D6BFA6999', 'Y','Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','','','','', 0,0,0,0,'','');

You may now remove the skip grant tables option and restart mysql service. I assume you are not using old-passwords option in my.cnf that makes it compatible with mysql version 3.0!

Changing all users and passwords

2011-08-06T21:33:00.000-07:00

Hi,
When the data is made available for development, the DBA needs to change the user privileges to make it more restrictive.
The following statement will create all current users with localhost and % hosts and have the same password for all these users.

mysql -hIP_ADDRESS -uroot -pold_password -P3311 -Bse"
select concat(\"grant all on *.* to '\",User,\"'@'%' identified by 'new_password' with grant option;\") from mysql.user group by user
UNION
select concat(\"grant all on *.* to '\",User,\"'@'localhost' identified by 'new_password' with grant option;\") from mysql.user group by user;" | mysql -h10.10.10.24 -uroot -pold_password -P3311

socket file location

2011-08-03T23:07:00.000-07:00

There are times when you need to supply the IP address even if you are accessing mysql from localhost. This may happen due to changed socket file.

If I do not change the default location of socket file, I can access mysql from localhost with or without IP
socket=/var/lib/mysql/mysql.sock

But If I change the socket location to some other path in my [mysqld] section then I need to add it to [mysql] and [client] section.

[mysql]
socket=/mysqldata1/mysql/mysql.sock

[client]
socket=/mysqldata1/mysql/mysql.sock

Finding errors in the .sql script

2011-07-25T23:23:00.002-07:00

You can disable foreign key and run the script to check if mysql throws any errors other than foreign key constraint.

# cat foreign_key.txt
set foreign_key_checks=0;

# cat foreign_key.txt db_name_updates.sql | mysql db_name
_____

When you need to import data, it is better to disable foreign key checks temporarily so that queries do not fail.
mysql> SET foreign_key_checks = 0

The problem with this is that you have to insert the statement at the beginning of every import file. If mysql reconnects automatically, it "forgets" the setting. There is no way to disable it at server level.

mysql> SET GLOBAL init_connect='SET foreign_key_checks = 0';

We can use initial connect feature to disable foregin keys. Any user that does not have "super" privilege will follow this command.
If you are creating users with "grant all on *.* " syntax, then you are out of luck.
I would suggest that a new user can be created for the purpose of executing the scripts those needs to bypass the foreign key checks. The easiest way to create such user is given below:

grant all on *.* to 'reader'@'%' identified by 'reader@123' with grant option;
revoke super on *.* from 'reader'@'%';

You can now use the "reader" user to execute the scripts those needs to by executed at any cost and should bypass foreign-keys.

_____

In order to know the exact queries where mysql found an error, use the following syntax.
# cat db_name_updates.sql | mysql db_name -f -vvv > db_name_updates_success.txt 2> db_name_updates_err.txt

Managing Auto-incremented ID

2011-07-25T02:03:00.000-07:00

Managing the auto_ids can be very difficult while automating the mysql process.
Here is a way to handle it more efficiently.

mysql> insert into country values (NULL, 'Norway');
Query OK, 1 row affected (0.00 sec)

The following statement will save the last auto id to a variable as well as write it to a text file.

mysql> select @myvar:=last_insert_id() into outfile 'country_last_id.txt';

# cat /var/lib/mysql/test/country_last_id.txt
5

Any script can now pick up this auto_incremented value from the text file and use it.

schema and data sync

2011-07-23T21:42:00.000-07:00

Remove all except latest 2 files

2011-07-14T23:45:00.000-07:00

Let's assume I want to remove all the older backup files from the current directory, except the last 2 latest files. One way of doing this is to move the latest file to another location and then remove all the files starting with data_diff_*

But such a process can not be set in cron nor it can be used in a script.
Here is a linux command that will do this for you. For free!!

# ls -lht
total 169M
-rw-r--r-- 1 root root 1.8M Apr 25 00:55 data_diff_0425.txt.zip
-rw-r--r-- 1 mysql mysql 1.8M Apr 24 00:55 data_diff_0424.txt.zip
-rw-r--r-- 1 mysql mysql 1.8M Apr 23 00:55 data_diff_0423.txt.zip
-rw-r--r-- 1 mysql mysql 1.8M Apr 22 00:55 data_diff_0422.txt.zip
-rw-r--r-- 1 mysql mysql 3.5M Apr 21 00:55 data_diff_0421.txt.zip
-rw-r--r-- 1 mysql mysql 1.8M Apr 20 00:55 data_diff_0420.txt.zip
...

# (ls data_diff_*.txt.zip -t | head -n 2; ls data_diff_*.txt.zip)| sort | uniq -u | xargs rm

# ls -lht
total 3.5M
-rw-r--r-- 1 root root 1.8M Apr 25 00:55 data_diff_0425.txt.zip
-rw-r--r-- 1 mysql mysql 1.8M Apr 24 00:55 data_diff_0424.txt.zip

Triggers management

2011-06-22T04:10:00.000-07:00

List of all triggers:
SELECT TRIGGER_SCHEMA AS Database_Name, EVENT_OBJECT_TABLE AS Table_Name,
REPLACE(group_concat(IF(EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING='BEFORE', TRIGGER_NAME, '')), ',', '') AS BEFORE_UPDATE,
REPLACE(group_concat(IF(EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER', TRIGGER_NAME, '')), ',', '') AS AFTER_UPDATE,
REPLACE(group_concat(IF(EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING='BEFORE', TRIGGER_NAME, '')), ',', '') AS BEFORE_INSERT,
REPLACE(group_concat(IF(EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER', TRIGGER_NAME, '')), ',', '') AS AFTER_INSERT,
REPLACE(group_concat(IF(EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING='BEFORE', TRIGGER_NAME, '')), ',', '') AS BEFORE_DELETE,
REPLACE(group_concat(IF(EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER', TRIGGER_NAME, '')), ',', '') AS AFTER_DELETE
from information_schema.triggers GROUP BY TRIGGER_SCHEMA, EVENT_OBJECT_TABLE ;

Remove all triggers:
select concat('drop trigger if exists `',trigger_schema,'`.`',trigger_name,'`;') cmd from information_schema.triggers
where trigger_schema='test';

Disable all AFTER triggers:
mysql -uroot -proot@123 -Bse"SELECT IF(ACTION_TIMING = 'AFTER', CONCAT('mv /var/lib/mysql/', TRIGGER_SCHEMA, '/', EVENT_OBJECT_TABLE, '.TRG', ' ', ' /var/lib/mysql/', TRIGGER_SCHEMA, '/', EVENT_OBJECT_TABLE, '.TRG.bak' ), '') AS Backup from information_schema.triggers GROUP BY TRIGGER_SCHEMA, EVENT_OBJECT_TABLE " | sh

Disable all triggers:
find `grep datadir /etc/my.cnf | awk -F'=' '{print $2}'` -name "*.TRG" | xargs -i mv {} {}.bak

Enable all Triggers:
find `grep datadir /etc/my.cnf | awk -F'=' '{print $2}'` -name "*.TRG.bak" | xargs -i rename '.bak' '' {}

// following trigger will dump each trigger from selected dbName into a separate file.

#!/bin/sh
dbName=${1:-'default_db'}
host='10.10.10.10'
user='root'
password='root@123'
port='3311'
mysql -u$user -p$password -h$host -P$port -Bse"SELECT TRIGGER_NAME FROM information_schema.triggers WHERE TRIGGER_SCHEMA = '$dbName' ;" | while read -r trigName

do
echo $trigName
mysql -u$user -p$password -h$host -P$port -Bse"select sql_code from mysql.SHOW_CREATE_TRIGGERS where TRIGGER_SCHEMA='$dbName' AND trigger_name = \"$trigName\"\G" > $trigName.sql
done

definers in triggers and procedures

2011-06-06T03:05:00.000-07:00

The stored procedures and triggers have the "definer" clause that keeps the non-privileged users from accessing these objects. But most often it is a problem since the trigger won't work unless the user is present. The update/ insert statements will fail if the respective triggers can not be activated. The only way is to find and create required users.

mysql> select DEFINER from information_schema.TRIGGERS AS a
LEFT JOIN mysql.user AS b ON a.DEFINER = CONCAT(User, '@', Host) WHERE CONCAT(uSER, '@', Host) IS NULL group by a.DEFINER;
+---------------------+
| DEFINER |
+---------------------+
| ABC@199.168.101.% |
+---------------------+
1 row in set (1.41 sec)

mysql> select DEFINER from information_schema.ROUTINES AS a
LEFT JOIN mysql.user AS b ON a.DEFINER = CONCAT(User, '@', Host) WHERE CONCAT(uSER, '@', Host) IS NULL group by a.DEFINER;
+-----------------------+
| DEFINER |
+-----------------------+
| MNP@199.168.100.% |
| companycuser@ |
+-----------------------+
2 rows in set (0.02 sec)

Check slave data

2011-05-26T04:30:00.000-07:00

We need to check if the slave is in sync with the master data. Let's assume the slave IP is 10.10.10.3
Now assuming that the localhost is the master server, I can simply run the slave_check.sh script to check the data discrepancy.

# cat slave_check.sh
#/bin/sh
myslave=${1:-10.10.10.3}
for dbName in `mysqlshow`
do
mk-table-checksum h=localhost,u=root,p=root@123,P=3306 --databases $dbName h=$myslave,u=root,p=root@123,P=3306 --databases $dbName --no-crc --count | mk-checksum-filter
done

hierarchy in parameters

2011-05-26T02:28:00.001-07:00

While connecting to mysql, the sequence of options does matter. For e.g. the following does not work...
mysql -uUser -pPassWd --socket=/tmp/mysql.sock2

But this works as expected.
mysql --socket=/tmp/mysql.sock2 -uUser -PPassWd

The following does not work either...
mysql --socket=/tmp/mysql.sock2 -uUser -pPassWd --no-defaults

But the following should work:
mysql --no-defaults --socket=/tmp/mysql.sock2 -uUser -pPassWd

Interesting!

NFS mounting to share free disk space

2011-05-25T01:41:00.001-07:00

If is very easy to mount a drive from one server to the other on the same network. NFS sharing will come handy when we have a lot of disk space on server 1 and very less space on 2. Once mounted, the files in that directory can be easily shared between the two (or more) servers.

1) Check if portmap and nfs services are running on both servers. start service if necessary.

# /sbin/service portmap status
portmap (pid 2557) is running...

# /sbin/service nfs status
rpc.mountd (pid 2376) is running...
nfsd (pid 2373 2372 2371 2370 2369 2368 2367 2366) is running...
rpc.rquotad (pid 2344) is running...

2) On the server (10.10.10.1) where enough disk is available...
i) create directory to share:
# mkdir /db-nfs

ii) Add entry to exports file:
# cat /etc/exports
/db-nfs 10.10.10.0/24(rw,sync,no_root_squash)

iii) activate sharing:
# /usr/sbin/exportfs -a

3) On the server (10.10.10.2) where we need more disk space...
i) Mount the drive

/bin/mount 10.10.10.1:/db-nfs /mnt

ii) add it to fstab
cat /etc/fstab
10.10.10.1:/db-nfs/ /mnt/db-nfs nfs rw,hard,intr 0 0

The maximum allowed size for varchar

2011-05-24T04:44:00.000-07:00

# The maximum width of the varchar type is 65535 across all columns. So the following create table statement fails.

mysql> create table encrypted ( can_des text, tran_address varchar(300), can_resume varchar(1300), can_remark varchar(65000) ) engine=MyISAM;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

# We will have to change some columns to text, mediumtext or longtext. I guess the big column like can_remark should be converted to text.

mysql> create table encrypted ( can_des text, tran_address varchar(300), can_resume varchar(1300), can_remark text ) engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)

# The key can be added on smaller columns as usual.

mysql> alter table encrypted add key (tran_address);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

# If I try to add a key on a varchar column that is more than say 700 bytes long, I will get the following warning and a partial index will be silently created which is useless.

mysql> alter table encrypted add key (can_resume);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table encrypted\G
*************************** 1. row ***************************
Table: encrypted
Create Table: CREATE TABLE `encrypted` (
`can_des` text,
`tran_address` varchar(300) default NULL,
`can_resume` varchar(1300) default NULL,
`can_remark` text,
KEY `tran_address` (`tran_address`),
KEY `can_resume` (`can_resume`(1000))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

# I will prefer to drop the partial index

mysql> alter table encrypted drop key can_resume;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

# full text index can be built on big varchar and text columns

mysql> ALTER TABLE encrypted ADD FULLTEXT my_ft (can_des, can_resume, can_remark);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

# finally the create table statement looks like this...

mysql> show create table encrypted\G
*************************** 1. row ***************************
Table: encrypted
Create Table: CREATE TABLE `encrypted` (
`can_des` text,
`tran_address` varchar(300) default NULL,
`can_resume` varchar(1300) default NULL,
`can_remark` text,
KEY `tran_address` (`tran_address`),
FULLTEXT KEY `my_ft` (`can_des`,`can_resume`,`can_remark`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

multiple instances of mysql

2011-05-18T23:02:00.000-07:00

If you want to start mysql using different data directory, the simple way is to change the datadir variable path in my cnf. But there are several disadvantages of doing so.
1) I have to change the my.cnf
2) restart
3) I can not access data from 2 different locations at the same time
4) Does not work as expected with 5.1+ version

Welcome to mysqld_multi

http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html

Add the following code to my.cnf and restart the mysql service. You can continue to use the data at the default location as you used to before.

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass

[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /var/lib/mysql2/hostname.pid2
datadir = /var/lib/mysql2

[mysqld3]
socket = /tmp/mysql.sock3
port = 3308
pid-file = /var/lib/mysql3/hostname.pid3
datadir = /var/lib/mysql3

[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /var/lib/mysql4/hostname.pid4
datadir = /var/lib/mysql4

[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /mnt/company-nfs/mysql/hostname.pid6
datadir = /mnt/company-nfs/mysql

Now you need to start mysqld_multi instance to initiate the "mysqld6" instance. I have saved the data on nfs partition for the sake of flexibility.

# mysqld_multi --no-log start 6
mysqld_multi log file version 2.16; run: Thu May 19 10:48:30 2011

Starting MySQL servers
# Starting mysqld daemon with databases from /mnt/company-nfs/mysql

I can now use the new mysql instance by using the following credentials...

mysql -h10.10.10.10 -usome_user -psome_password --socket=/tmp/mysql.sock6 --port=3311

I can use the other sockets like 2,3 and 4 as well. All I need to do is to create the respective data-directories and change the ownership and permission so that mysql is allowed to read/write to these locations.

mysqld_multi stop 6

If the stop command as shown above does not work, try...

mysqladmin --socket=/tmp/mysql.sock6 shutdown

InnoDB monitor

2011-05-17T20:47:00.000-07:00

If you suspect that due to innodb deadlocks, the application performance is affected, turn on the innodb monitor tool.

to enable the standard InnoDB Monitor, create the innodb_monitor table:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

To stop the Monitor, drop the table:
DROP TABLE innodb_monitor;

This will save the innodb status to error log.

Users without super privileges

2011-05-12T01:34:00.000-07:00

It is recommended to have 2 types of users.

1) A user with all the privileges including with grant option. This user is equivalent to root.
grant all on *.* to 'foo'@'%' identified by 'bar' with grant option;

2) Another set of users is the ones who have all the privileges except "super".
revoke super on *.* from 'foo'@'%';

There are 3 advantages of having users without "super" access.
1) More secure
2) Read only slave
3) Logging

a) The first point does not need explanation.
b) When I start a slave in "read-only" mode, all the users without super privileges can only read the data and can not write.
c) When the server is not started in "read-only" mode, we can log the users source access points in a mysql table.

SET GLOBAL init_connect='create table if not exists test.connect(db_name varchar(100), user_with_ip varchar(100), user_permission varchar(100),
thread_id varchar(100), connect_time datetime) engine=MyISAM; insert into test.connect select @@hostname as db_name, user() as user_with_ip,
current_user() as user_permission, connection_id() as connect_id, now() as connect_time';

This will help to analyse how many times a particular credential was used. The thread_id can also be linked to binary log extract to know the commands executed by a user in that thread.

Aspersa to watch CPU load

2011-05-11T01:33:00.000-07:00

Here is a tool shell script that will collect server info when the CPU load is too high.

http://aspersa.googlecode.com/svn/html/stalk.html

The files are usually saved in the /root/user/collected folder.

cat aspersa.sh
#!/bin/sh
# cron to check CPU load every 5 minutes
# */5 * * * * /home/aspersa.sh > /home/aspersa_success.txt 2 > /home/aspersa_err.txt
# this will collect server info when the CPU load more than 10

echo "aspersa tool required, download from the following location"
echo "svn checkout http://aspersa.googlecode.com/svn/trunk/ aspersa-read-only"
# default threshold is 10 unless specified from command line

cpuload=${1:-10}

mynum=`cat /proc/loadavg | awk '{print $2}' | awk -F"." '{print $1}'`

if [[ $mynum < $cpuload ]];then
echo "running collect tool"
sh /home/aspersa/collect

else
echo "not running collect tool since CPU load is less than $cpuload"
fi

Compare table count between 2 schema

2011-05-08T21:53:00.001-07:00

If I need to compare 2 schemas from the same database, here is the script that will come handy.

# cat mydiff.sh
#!/bin/sh
db1=${1:-'mydb'}
db2=${2:-'mydb_log'}
echo $db1
mysqlshow $db1 --count > $db1.txt
mysqlshow $db2 --count > $db2.txt
diff $db1.txt $db2.txt --side-by-side --suppress-common-lines

Logging records for archival purpose

2011-05-03T03:22:00.001-07:00

There are times when we need to log all the old values of the table. For e.g. when a user updates his security answer, the old value should be saved in a log table. In other words, if a user changed his answer 4 times, I need to save all those 4 values. I do prefer to create archival tables on slave instead of master. Here is the shell script that will create the exact replica of the schema "account" as "account_log" with all the current data. The account database will get updated as and when there is an update/ insert on master. The log database will keep saving old and new values of each table.

back_track.sh

#!/bin/sh
# duplicate schema table to hold the archival values
# change the schema name below that you want to track
schema_name='account'
# username and password to access MySQL
user_name='root'
pass_word='root@123'

echo "drop database if exists "$schema_name"_log;"
echo "create database if not exists "$schema_name"_log;"
mysql -u$user_name -p$pass_word -Bse"SELECT CONCAT(\"CREATE TABLE "$schema_name"_log.\",TABLE_NAME,\" SELECT * FROM $schema_name.\",TABLE_NAME,\";\") from information_schema.columns WHERE TABLE_SCHEMA = \"$schema_name\" group by TABLE_NAME"
echo "use $schema_name;"
mysql -u$user_name -p$pass_word -Bse"SELECT CONCAT(\"DROP TRIGGER IF EXISTS \",TABLE_NAME, \"_update;\") from information_schema.columns WHERE TABLE_SCHEMA = \"$schema_name\" group by TABLE_NAME"
echo 'DELIMITER $$'
mysql -u$user_name -p$pass_word -Bse"SELECT CONCAT(\"CREATE TRIGGER \",TABLE_NAME,\"_update AFTER UPDATE ON \",TABLE_NAME, \" FOR EACH ROW BEGIN INSERT INTO "$schema_name"_log.\",TABLE_NAME,\" values (\", GROUP_CONCAT(CONCAT(\"NEW.\",COLUMN_NAME)), \"); END;\") from information_schema.columns WHERE TABLE_SCHEMA = \"$schema_name\" group by TABLE_NAME"
mysql -u$user_name -p$pass_word -Bse"SELECT CONCAT(\"CREATE TRIGGER \",TABLE_NAME,\"_insert AFTER INSERT ON \",TABLE_NAME, \" FOR EACH ROW BEGIN INSERT INTO "$schema_name"_log.\",TABLE_NAME,\" values (\", GROUP_CONCAT(CONCAT(\"NEW.\",COLUMN_NAME)), \"); END;\") from information_schema.columns WHERE TABLE_SCHEMA = \"$schema_name\" group by TABLE_NAME"
echo '$$'
_____

You can call the script and push the output to mysql using...
sh back_track.sh | mysql -uroot -proot@123 -h10.10.10.65 -P4040

Let's assume one record of the user table (account schema) was updated. You can check the old and new values from the account_log schema by executing query.

_____

The following script will help to know the changes taken place for each column.

# cat audit.sh
#/bin/sh
tbl_name=${1:-'address'}
mysql -Bse"select id, count(*) as cnt from database_log.$tbl_name group by id having cnt > 1" | while read -r id cnt
do
echo "**************************"
echo "values changed for ID $id"
echo "**************************"
mysql -Bse"select * from database_log.$tbl_name where id = $id\G" | sort -t":" -k1,1 -s | grep -v '^*'
done

Replace into and On duplicate key

2011-05-01T23:16:00.000-07:00

A detailed analysis of what happens when we use different options to insert/ update a record.

Here is a sample table...
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+

We want to insert a new record with emp_id 3, name - anthony and the address should be 'South Africa'. If the record is already present in the DB it should be updated.

This may look like a simple task, but checking the table structure, it is clear that there is no composite unique index, but separate per column unique keys.

CREATE TABLE `mykey` (
`id` int(11) NOT NULL auto_increment,
`emp_id` int(11) default NULL,

`name` varchar(100) default NULL,
`address` varchar(100) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `emp_id` (`emp_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB

The challenge is to write an insert/ update statement that will satisfy all constraint and will fail predictably only in certain cases.

1) "insert ignore" will prefer not to do anything.
2) "replace into" may look like the best choice, but it will also change the auto_incremented ID and has some issues when there is more than one unique key.
For e.g. in the following example, while trying to insert emp_id 3 with name - akbar, it actually removed one row.

3) I guess the "on duplicate key update " clause is the right choice. It does not change the auto_id while updating the record. And even if there are no composite unique indexes in the table, it works as if they are present.

mysql> drop table if exists mykey;
Query OK, 0 rows affected (0.02 sec)

mysql> create table mykey(id int not null auto_increment, emp_id int, name varchar(100), address varchar(100), primary key (id), unique(emp_id), unique(name)) engine=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into mykey (emp_id, name, address) values (1, 'amar', 'hyderabad, AP, india') ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into mykey (emp_id, name, address) values (2, 'akbar', 'karachi, pakistan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mykey (emp_id, name, address) values (3, 'anthony', 'london, UK') ;
Query OK, 1 row affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint savepoint_one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)
_____

# Case 1: "insert ignore"

mysql> insert ignore into mykey (emp_id, name, address) values (3, 'anthony', 'South Africa') ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

_____

# Case 2: "replace into"

mysql> replace into mykey (emp_id, name, address) values (3, 'anthony', 'South Africa') ;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 4 | 3 | anthony | South Africa |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

mysql> rollback to savepoint_one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

mysql> replace into mykey (emp_id, name, address) values (3, 'akbar', 'South Africa') ;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+-------+----------------------+
| id | emp_id | name | address |
+----+--------+-------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 5 | 3 | akbar | South Africa |
+----+--------+-------+----------------------+
2 rows in set (0.00 sec)
_____

# Case 3: "on duplicate key"

mysql> rollback to savepoint_one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | london, UK |
+----+--------+---------+----------------------+
3 rows in set (0.01 sec)

mysql> insert into mykey (emp_id, name, address) values (3, 'akbar', 'South Africa') on duplicate key update emp_id = 3, name = 'akbar', address='South Africa' ;
ERROR 1062 (23000): Duplicate entry 'akbar' for key 3

mysql> insert into mykey (emp_id, name, address) values (3, 'akbar', 'South Africa') on duplicate key update emp_id = 3, name = 'anthony', address='South Africa' ;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from mykey;
+----+--------+---------+----------------------+
| id | emp_id | name | address |
+----+--------+---------+----------------------+
| 1 | 1 | amar | hyderabad, AP, india |
| 2 | 2 | akbar | karachi, pakistan |
| 3 | 3 | anthony | South Africa |
+----+--------+---------+----------------------+
3 rows in set (0.00 sec)

Saving images in a table

2011-04-25T01:23:00.000-07:00

The binary data should not be saved in a table. It should be saved as a file on the application server and only the name and path should be saved in the table. If you must save the image in a table, you can do so in a normalized way. Here is an example...

This is how a typical table that saves the binary data like user images look like.

CREATE TABLE `user_info` (
`id` int(11) not null auto_increment,
`name` varchar(100),
`address1` varchar(256),
`city` varchar(256),
`country` varchar(256),
`acc_no` varchar(256),
`home_phone` varchar(256),
`office_phone` varchar(256),
`mobile_no` varchar(256),
`date_of_birth` varchar(256),
`age` varchar(256),
`user_photo` blob,
primary key (id)
) ENGINE=InnoDB;

In order to normalize the data, we need to save the images in a separate table. and link the image using the primary key ID of user_info table.

CREATE TABLE `user_pic` (
`id` int(11) NOT NULL auto_increment,
`myfile` longblob,
`dbname` varchar(100),
`tblname` varchar(100),
user_id int,
PRIMARY KEY (`id`),
FOREIGN KEY (`user_id`) references user_info (id)
) ENGINE=InnoDB;
_____

The following shell script will move the user photos from user_info table to user_pic table and link it back to the parent table.

#!/bin/sh
mynum=`date '+%m%d%H%M%S'`
num=1

while (( num < 100 ))
do
mysql test -uroot -pPasswd -e"select user_photo into dumpfile '/tmp/myfile_mar_$mynum_$num.png' from user_info where id = $num"

if [[ -s /tmp/myfile_mar_$mynum_$num.png ]];then
mysql test -uroot -pPasswd -e"insert into user_pic values (NULL, load_file('/tmp/myfile_mar_$mynum_$num.png'), database(), 'user_info', $num)"
fi

let num+=1

done

_____

Once the script is successfully run, drop the column "user_photo" from the user_info table.
ALTER TABLE user_info DROP COLUMN user_photo;

Escaping in MySQL

2011-04-18T05:25:00.000-07:00

Usually Mysql returns the text as is and does not escape the special characters.

# mysql -ushantanu -pshantanu@123 -Bse"select name from test.todel"
shantanu's
amar"s

If I need the following output, I will have to write a small shell script.

shantanu\'s
amar\"s

You can use mysql_real_escape_string if you are comfortable with PHP, but if you insist on shell, here is a solution assuming name is the second column in the table.
_____

#!/bin/sh
mysqldump -ushantanu -pshantanu@123 test todel --compact --skip-extended-insert --no-create-info > tofile.txt

for customer in `sed -rn 's/.*$//;s/$.*//p' tofile.txt | awk -F"," '{print $2}' | sed "s/^'//" | sed "s/'$//"`
do
echo $customer
done

MySQL and RAMdisk to make queries faster

2011-04-06T04:12:00.000-07:00

Here is how you get MySQL to use a 1G size tmpfs.
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir

0. mkdir -p /db/tmpfs01 && chown mysql:mysql /db/tmpfs01
1. mount tmpfs /db/tmpfs01 -t tmpfs -o size=1G
3. edit my.cnf file and add: tmpdir = /db/tmpfs01
4. restart MySQL

To have persistent tmpfs with reboot put the following into /etc/fstab. On this system the mysql user is uid/gid of 27, yours might vary so adjust accordingly.
tmpfs /db/tmpfs01 tmpfs rw,uid=27,gid=27,size=1G,nr_inodes=10k,mode=0755 0 0

Controlling mysql access using firewall

2011-04-05T21:37:00.000-07:00

If I want to allow a new application server to access mysql on current 3306 port, I need the following command

iptables -I INPUT 13 -s 111.222.333.444 -p tcp --dport 3306 -j ACCEPT

I can check if the rule is set as expected.

iptables -nvL

4 240 ACCEPT tcp -- * * 111.222.333.444 0.0.0.0/0 tcp dpt:3306

Hacking FRM files to switch numeric data types

2011-03-24T01:36:00.000-07:00

In order to change any numeric data type to UNSIGNED here is a simple and basic alter table statement that needs to be executed.

alter table frm_hack modify id tinyint unsigned;

But if the table is big, it will take a lot of time and here is a frm hack suggested...

http://www.facebook.com/note.php?note_id=10150115679045933

And here is how it works:

mysql> create table frm_hack(id tinyint, name varchar(100), primary key (id)) engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into frm_hack values (1, 'shantanu'), (2, 'john'), (121, 'ram'), (234, 'shyam');
Query OK, 4 rows affected, 1 warning (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 1

mysql> create table frm_hack_new (id tinyint unsigned, name varchar(100), primary key (id)) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> \q
Bye

[root@localhost test]# cp frm_hack_new.frm frm_hack.frm
cp: overwrite `frm_hack.frm'? y

[root@localhost test]# mysql test

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table frm_hack\G
*************************** 1. row ***************************
Table: frm_hack
Create Table: CREATE TABLE `frm_hack` (
`id` tinyint(4) NOT NULL default '0',
`name` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table frm_hack_new\G
*************************** 1. row ***************************
Table: frm_hack_new
Create Table: CREATE TABLE `frm_hack_new` (
`id` tinyint(3) unsigned NOT NULL default '0',
`name` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from frm_hack;
+-----+----------+
| id | name |
+-----+----------+
| 1 | shantanu |
| 2 | john |
| 121 | ram |
| 127 | shyam |
+-----+----------+
4 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table frm_hack\G
*************************** 1. row ***************************
Table: frm_hack
Create Table: CREATE TABLE `frm_hack` (
`id` tinyint(3) unsigned NOT NULL default '0',
`name` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into frm_hack values (213, 'abc');
Query OK, 1 row affected (0.02 sec)

mysql> select * from frm_hack;
+-----+----------+
| id | name |
+-----+----------+
| 213 | abc |
| 1 | shantanu |
| 2 | john |
| 121 | ram |
| 127 | shyam |
+-----+----------+
5 rows in set (0.01 sec)

Finding data / comma in a table record

2011-03-20T22:01:00.001-07:00

Here is an easy trick to find the table names where comma "," is being used. I will export the data in "tab" delimited format using maatkit and then look for the comma.

rm -rf /pdump/ && mkdir /pdump/
chmod 777 /pdump/

/usr/bin/mk-parallel-dump --no-gzip --base-dir '/pdump' --databases 'my_db' --tab

# grep -r ',' *.txt | awk -F":" '{print $1}' | sort -u | awk -F"." '{print $1}'
address
address_log

The 2 tables address and address_log in the my_db database has the comma somewhere in its record. This tip can be used to find a word or phrase as well.

Batch processing

2011-03-17T04:44:00.000-07:00

Let's assume you want to update the second column of the table to upper case. The following SQL statement will do the needful.
update tbl set second_col = upper(second_col)

But if you want to do it in some other way, or if there are more complex processes involved where an SQL won't work, then here is the solution. This is 4 steps procedure to change the data. An utility called "maatkit" is required for this.

##### create directory and make it writable #####

# cd /home/
# rm -rf /pdump/
# mkdir /pdump
# chmod 777 /pdump/*

##### Download data in TAB or CSV format #####

/usr/bin/mk-parallel-dump -h'localhost' --no-gzip --base-dir '/pdump' --databases 'myDB' --tables 'country' --csv
CHUNK TIME EXIT SKIPPED DATABASE.TABLE
db 0.02 0 0 myDB
all 0.02 0 0 -

##### Change the third column data from one encryption to another #####

sh /home/shantanu/line.sh > /pdump/myDB/temp.txt
mv /pdump/myDB/temp.txt /pdump/myDB/country.000000.txt

##### drop the table insert the updated data back to the table #####

/usr/bin/mk-parallel-restore -h'localhost' --database 'myDB' --tables 'country' '/pdump' --csv
1 tables, 2 files, 1 successes, 0 failures, 0.48 wall-clock time, 0.48 load time

_____

The line.sh shell script looks like this...

#!/bin/sh
# read file line by line
file="/pdump/myDB/country.000000.txt"
while read line
do
# select the second column
mycol=`echo $line | awk -F"," '{print $2}'`

# convert it to upper case
mycol_new=`echo $mycol | tr "[:lower:]" [:upper:]`

# do some more java processing with the value
# replace the upper case text in the text file
echo $line | awk -v var="$mycol_new" -F"," '{print $1 "," var "," $3 "," $4 "," $5 "," $6 "," $7 "," $8}'

done < $file

Mysql Monitor

2011-03-09T01:25:00.000-08:00

mysql monitor is the nagios of Database. It is especially useful when you have a bunch of mysql DB servers to manage.

http://www.mysql.com/products/enterprise/monitor.html

Here are 8 steps to enable the monitoring agent on the DB server. It is assumed that the monitor server is already configured and active on a server (for e.g. 10.10.10.63)

1) sudo root
2) download agent
3) Change file permissions
chmod +x mysqlmonitoragent-2.3.1.2044-linux-glibc2.3-x86-64bit-installer.bin

4) Run file
[root@localhost]# ./mysqlmonitoragent-2.3.1.2044-linux-glibc2.3-x86-64bit-installer.bin

5) Start agent
/etc/init.d/mysql-monitor-agent start

6) check status
ps -ef | grep monitor

7) check log
cat /opt/mysql/enterprise/agent/mysql-monitor-agent.log

8) create mysql user for e.g.
mysql> grant all on *.* to 'agent'@'%' identified by 'agent';

_____

The following options must be selected very carefully or else monitor will not work as expected.

Installation directory
Installation directory [/opt/mysql/enterprise/agent]:

How will the agent connect to the database it is monitoring?
[1] TCP/IP
[2] Socket
Please choose an option [1] : 1

----------------------------------------------------------------------------
Components to Install

Monitoring:
Enable the Agent to monitor a MySQL Server [Y/n]: Y

Query Analyzer:
Enable the Aggregator to aggregate Query Analyzer data from an external source [y/N]: N
Enable MySQL Proxy to collect and aggregate Query Analyzer data [y/N]: y

----------------------------------------------------------------------------
Monitored Database Information

MySQL hostname or IP address [127.0.0.1]:

Validate MySQL hostname or IP address [y/N]: N

MySQL Port [3306]:

MySQL Username [agent]: agent

MySQL Password :
Re-enter :
----------------------------------------------------------------------------
Agent User Account Creation

[1] Yes, use the credentials below to create the Agent's user account
[2] No, I've already created the account and I'm sure it's correct
Please choose an option [1] : 2

----------------------------------------------------------------------------
MySQL Enterprise Monitor Options

Hostname or IP address []: 10.10.10.63

Tomcat Server Port [18080]:

Tomcat SSL Port [18443]:

Use SSL? [y/N]: y

Agent Username [agent]: agent

Agent Password :
Re-enter :

Update Slaves those are out of sync

2011-02-09T23:02:00.000-08:00

You can use a simple MySQL trick to get all the rows of a table to replicate through to slaves. The idea is to dump the table, delete the rows, then reload it again. The delete and subsequent reload replicate out to slaves, after which everything is consistent again. Let's say we have a table called tpcb.history that needs to be fixed. Login with mysql and run the following commands:

BEGIN;
SELECT * FROM tpcb.history
INTO OUTFILE '/tmp/tpcb.history.dmp' FOR UPDATE;
DELETE FROM tpcb.history;
LOAD DATA INFILE '/tmp/tpcb.history.dmp' REPLACE
INTO TABLE tpcb.history FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
COMMIT;

You can do the reload several ways in MySQL, but this particular code has some advantages over other approaches, such as using LOCK TABLES. First, it uses a transaction, so if something goes wrong the changes roll back and you do not lose your data. Second, the SELECT ... FOR UPDATE locks your data and ensures serialization. You can run this while applications are running without problems.

Performance problem with Innodb and DROP TABLE

2011-02-09T00:19:00.000-08:00

innodb_file_per_table options is commonly used these days and it does no doubt help to improve the performance a little bit ( may be 5-10%). But using this option coupled with the large buffer pool will make dropping the tables and schemas very difficult. It takes a lot of time, in some cases hours and it does not allow mysql process other queries. This seems to be a serious bug and I guess one should try to avoid InnoDB table types if the table is expected to be dropped or if it is expected to be very large. Deleting large recordset also takes time and that needs to be addressed as well.

Updatable views

2011-01-28T03:36:00.000-08:00

You can use the "with check option" property of view to create constraint that will do the needful of checking the data.
For e.g. if I want to allow the ID 1 into the table parent, I can use "enum" datatype or create a view with appropriate where clause.

drop table child;
drop table parent;

CREATE TABLE parent(
`ID` int(11) default NULL,
`name` varchar(100) default NULL,
`city` varchar(100) default NULL,
primary key (name)
) ENGINE=InnoDB;

create table child(
username varchar(100) not null,
FOREIGN KEY (username) REFERENCES parent(name) on update cascade,
primary key (username)
) ENGINE=InnoDB;

insert into parent values (1, 'abc', 'delhi');
insert into child values ('abc');

create or replace view myview as select * from parent where ID > 0 and ID < 2 with check option;

mysql>insert into myview values (3, 'qqabc', 'wwdelhi');
ERROR 1369 (HY000): CHECK OPTION failed 'test.myview'

mysql>insert into myview values (1, 'qqabc', 'wwdelhi');
Query OK, 1 row affected (0.02 sec)

MySQL Case Study - 182

2011-01-18T23:05:00.000-08:00

Finding rows those are NOT 10 digits long

I need to find the rows those are not 10 digits in length and must include a digit and not a alphabet.

CREATE TABLE mobile (mno varchar(100) default NULL);
INSERT INTO mobile VALUES ('223456780'),('2234567890'),('4234567890'),('6234567890'),('22345678aaaa0'),('123456789a');

mysql> select * from mobile;
+---------------+
| mno |
+---------------+
| 223456780 |
| 2234567890 |
| 4234567890 |
| 6234567890 |
| 22345678aaaa0 |
| 123456789a |
+---------------+
6 rows in set (0.00 sec)

mysql> select * from mobile WHERE length(mno) != 10;
+---------------+
| mno |
+---------------+
| 223456780 |
| 22345678aaaa0 |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from mobile WHERE mno NOT RLIKE '[0-9]{10}';
+---------------+
| mno |
+---------------+
| 223456780 |
| 22345678aaaa0 |
| 123456789a |
+---------------+
3 rows in set (0.00 sec)

One more way to select

2011-01-12T01:45:00.000-08:00

I can write the following query if I know the exact column name where I expect to find the matching value.
select * from parent where city = 'Delhi'
But it is interesting to know that grep can be used at the command prompt that will return only the records where that appear.

mysql>pager grep Delhi
PAGER set to 'grep Delhi'

mysql>select * from parent;
| 2 | Amar | Delhi |
| 5 | Akbar | Delhi |
| 8 | Anthony | Delhi |
10 rows in set (0.00 sec)

mysql>pager
Default pager wasn't set, using stdout.

Top N records per group

2011-01-06T03:43:00.000-08:00

Selecting top N records per group is not as easy as it seems. The query may get as complex as you want.

http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group

Here is how I would solve it by taking help of composite index with one of the column being auto_id.
InnoDB table types do not support such indexes though.

drop table if exists Country_sort;

CREATE TABLE `Country_sort` (
id int not null auto_increment,
`Code` char(3) NOT NULL default '',
`Name` char(52) NOT NULL default '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
`Region` char(26) NOT NULL default '',
`SurfaceArea` float(10,2) NOT NULL default '0.00',
`IndepYear` smallint(6) default NULL,
`Population` int(11) NOT NULL default '0',
`LifeExpectancy` float(3,1) default NULL,
`GNP` float(10,2) default NULL,
`GNPOld` float(10,2) default NULL,
`LocalName` char(45) NOT NULL default '',
`GovernmentForm` char(45) NOT NULL default '',
`HeadOfState` char(60) default NULL,
`Capital` int(11) default NULL,
`Code2` char(2) NOT NULL default '',
PRIMARY KEY (Continent, id)
) ENGINE=MyISAM;

insert into Country_sort select NULL, Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2 from Country order by Continent, SurfaceArea desc;

select Continent, name from Country_sort where ID = 1 order by Continent;

select Continent, name, SurfaceArea, Population from Country_sort where ID <= 5 order by Continent, SurfaceArea desc;

Binary collation and text comparison

2011-01-05T00:50:00.001-08:00

MySQL by default, ignores capital and small letters in the data:

mysql> create table todel (description varchar(100) COLLATE utf8_bin);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into todel (description) values ('This is MIXED case');
Query OK, 1 row affected (0.00 sec)

mysql> select * from todel where description like '%MIXED%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from todel where description like '%mixed%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

_____

If you want mysql to consider the case, you have to use the "binary" keyword as shown below. The comparison is now case sensitive.

mysql> select * from todel where binary description like '%mixed%';
Empty set (0.00 sec)

mysql> select * from todel where binary description like '%MIXED%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

_____

If you want all the queries on that column (for e.g. description) to be case sensitive, change the collation to binary in the create table statement.

mysql> create table todel (description varchar(100) COLLATE utf8_bin);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into todel (description) values ('This is MIXED case');
Query OK, 1 row affected (0.00 sec)

mysql> select * from todel where description like '%mixed%';
Empty set (0.00 sec)

mysql> select * from todel where description like '%MIXED%';
+--------------------+
| description |
+--------------------+
| This is MIXED case |
+--------------------+
1 row in set (0.00 sec)

_____

It is recommended NOT to use the utf8_bin collation type for any column. If your table already has one, get rid of it using "alter table modify" statement.

mysql> show create table todel\G
*************************** 1. row ***************************
Table: todel
Create Table: CREATE TABLE `todel` (
`description` varchar(100) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> alter table todel modify description varchar(100);

mysql> show create table todel\G
*************************** 1. row ***************************
Table: todel
Create Table: CREATE TABLE `todel` (
`description` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> alter table todel modify description varchar(100) collate utf8_bin;

mysql> show create table todel\G
*************************** 1. row ***************************
Table: todel
Create Table: CREATE TABLE `todel` (
`description` varchar(100) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Savepoints in transaction

2011-01-03T21:20:00.000-08:00

rollbacks usually let you go back to the start of the transaction, but not necessarily if you use savepoints.
mysql> rollback;

will undo all the statements till the beginning of the transaction, whereas
mysql> rollback to savepoint savepoint_one;

Will revert back to the saved position in the transaction.
You can run the following script to see how savepoints work in transactions/ Stored procedures.

drop table if exists test.savepoint_test;
drop procedure if exists second_fail;
drop procedure if exists prc_work;
CREATE TABLE test.savepoint_test (
id int not null default '0',
name varchar(100),
primary key (id)
)engine=InnoDB;
insert into test.savepoint_test values ('1', 'jack');
insert into test.savepoint_test values ('2', 'akbar');
select * from test.savepoint_test;

start transaction;
update test.savepoint_test set name = 'amar' where id = 1;
select * from savepoint_test;

savepoint savepoint_one;
update savepoint_test set name = 'jill' where id = 2;

select * from savepoint_test;
rollback to savepoint_one;

select * from savepoint_test;

commit;

select 'using savepoints in stored procedures' as message;

delimiter $$
drop procedure if exists second_fail$$
CREATE PROCEDURE second_fail()
BEGIN
INSERT into test.savepoint_test values ('3', 'anthony');
INSERT into test.savepoint_test values ('2', 'xyz');
END;
$$

select 'ID 3 manages to get into the table but ID 2 fails due to duplicate primary key error' as message $$
drop procedure if exists prc_work$$
CREATE PROCEDURE prc_work()
BEGIN
SAVEPOINT sp_prc_work;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
INSERT into test.savepoint_test values ('4', 'krishna');
INSERT into test.savepoint_test values ('2', 'abc');
END;
END;
$$

select 'ID 4 qualifies to be inserted but fails because ID 2 fails and the entire transaction is reverted to the savepoint' as message $$

delimiter ;

start transaction;
call second_fail();
select * from test.savepoint_test;

call prc_work();
select * from test.savepoint_test;
commit;

MySQL Case Study - 181

2010-12-21T02:10:00.000-08:00

ON DUPLICATE KEY insert into an audit or log table

Is there a way to accomplish this?

INSERT IGNORE INTO some_table (one,two,three) VALUES(1,2,3)
ON DUPLICATE KEY (INSERT INTO audit_table VALUES(NOW(),'Duplicate key ignored')

I really don't want to use PHP for this :(

http://stackoverflow.com/questions/3884344/mysql-on-duplicate-key-insert-into-an-audit-or-log-table
_____

If you want to consider using a stored procedure, you can use a DECLARE CONTINUE HANDLER. Here's an example:

CREATE TABLE users (
username VARCHAR(30),
first_name VARCHAR(30),
last_name VARCHAR(30),
PRIMARY KEY (username)
);

CREATE TABLE audit_table (timestamp datetime, description varchar(255));

DELIMITER $$
CREATE PROCEDURE add_user
(in_username VARCHAR(30),
in_first_name VARCHAR(30),
in_last_name VARCHAR(30))
MODIFIES SQL DATA
BEGIN
DECLARE duplicate_key INT DEFAULT 0;
BEGIN
DECLARE EXIT HANDLER FOR 1062 SET duplicate_key = 1;

INSERT INTO users (username, first_name, last_name)
VALUES (in_username, in_first_name, in_last_name);
END;

IF duplicate_key = 1 THEN
INSERT INTO audit_table VALUES(NOW(), 'Duplicate key ignored');
END IF;
END$$
DELIMITER ;

Let's add some data, trying to insert a duplicate key:

CALL add_user('userA', 'Bob', 'Smith');
CALL add_user('userB', 'Paul', 'Green');
CALL add_user('userA', 'Jack', 'Brown');

Result:

SELECT * FROM users;
+----------+------------+-----------+
| username | first_name | last_name |
+----------+------------+-----------+
| userA | Bob | Smith |
| userB | Paul | Green |
+----------+------------+-----------+
2 rows in set (0.00 sec)

SELECT * FROM audit_table;
+---------------------+-----------------------+
| timestamp | description |
+---------------------+-----------------------+
| 2010-10-07 20:17:35 | Duplicate key ignored |
+---------------------+-----------------------+
1 row in set (0.00 sec)

Replication: step-by-step

2010-12-15T03:47:00.000-08:00

ssh to master and slave in 2 different windows. Make sure maatkit is installed on both servers. start the script logging and mysql logging.

Master: 10.10.10.56
Slave: 10.10.10.3

$$ On 10.10.10.3 (Slave)
// check disk space
// make sure innodb is configured but all logs are disabled in my.cnf
// create a super user equivalent to root
# mysql -e"grant all on *.* to 'shantanu'@'%' identified by 'shantanu@123' with grant option;"
# mysql -e"grant all on *.* to 'shantanu'@'localhost' identified by 'shantanu@123' with grant option;"

// open the port for copying the dump file from master
# time nc -l 1234 > toall_master_data.sql

$$ On 10.10.10.56 (Master)
// create a super user equivalent to root
# mysql -e"grant all on *.* to 'shantanu'@'%' identified by 'shantanu@123' with grant option;"
# mysql -e"grant all on *.* to 'shantanu'@'localhost' identified by 'shantanu@123' with grant option;"

// copy the users and privileges to slave
# mk-show-grants -ushantanu -pshantanu@123 | mysql -h10.10.10.3 -ushantanu -pshantanu@123

// take the dump
# time mysqldump --all-databases -R --single-transaction --master-data=2 | perl /home/shantanu/dumper.pl --delete > to_all_db_to_3.sql
real 3m21.331s
user 1m10.234s
sys 0m10.378s

// send the dump file to slave
# time nc 10.10.10.3 1234 < to_all_db_to_3.sql
real 1m17.768s
user 0m1.800s
sys 0m12.713s

$$ On 10.10.10.3 (Slave)
# time mysql < to_all_db_to_3.sql

~ 40 minutes

// run the chnage master to command generated from the following file
# head -100 toall_master_data.sql | grep 'CHANGE MASTER TO' | sed 's/--//' | replace ";" " , MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_HOST='10.10.10.56';"

// check slave status
mysql -e"show slave status\G"

// compare row count in test database with master
mk-table-checksum h=localhost,u=shantanu,p=shantanu@123,P=3306 --databases test h=10.10.10.56,u=shantanu,p=shantanu@123,P=3306 --databases test | mk-checksum-filter

// drop user shantanu
mysql -ushantanu -pshantanu@123 -e"drop user shantanu;"

$$ On 10.10.10.56 (Master)
// compare row count in test database with slave
mk-table-checksum h=localhost,u=shantanu,p=shantanu@123,P=3306 --databases test h=10.10.10.3,u=shantanu,p=shantanu@123,P=3306 --databases test | mk-checksum-filter

// drop user shantanu
mysql -ushantanu -pshantanu@123 -e"drop user shantanu;"

Or run the shell script to check if all the tables are in sync

# time sh -xv slave_check.sh

# vi slave_check.sh
#/bin/sh
# change the slave IP as default IP address if no IP is provided
myslave=${1:-10.10.10.3}
for dbName in `mysqlshow`
do
mk-table-checksum h=localhost,u=shantanu,p=shantanu@123,P=3306 --databases $dbName h=$myslave,u=shantanu,p=shantanu@123,P=3306 --databases $dbName --no-crc --count | mk-checksum-filter
done

This can also do done in a single command as shown below:

for dbName in `mysqlshow -uroot -proot@123`; do /usr/bin/mk-table-checksum h=localhost,u=root,p=root@123,P=3306 --databases $dbName h=10.10.10.11,u=root,p=root@123,P=3306 --databases $dbName --count --no-crc | /usr/bin/mk-checksum-filter; done

Script extract to warn user

2010-11-29T23:22:00.000-08:00

There are times when you want to make sure that the user has verified certain parameters. Here is an extract that can be placed at the top of any script.

# cat mycheck.sh
#!/bin/sh
# Warn and force user to check certain settings before starting the execution of script
echo "
********

Make sure the following and continue :

1. There is enough disk space available
2. general and binary logging is disabled in my.cnf
3. you have taken the required permissions from your boss, parents and girl friend

********

Press 'y' to continue 'n' to abort "
read answer
if [ "$answer" = "n" ] || [ "$answer" = "N" ]
then
echo "Aborting"
exit 160
fi

echo "welcome and let us start the process"

MySQL alert system

2010-11-29T02:48:00.000-08:00

#!/bin/sh
# filename myalert.sh
# a cron that will record the mysql status to a text file every minute
# record status of both masters
# * * * * * mysqladmin -h111.222.333.101 -uroot -pPassWord status >> /home/ricie/status_success_101.txt 2>> /home/develop/status_err_101.txt
# * * * * * sh -xv /home/develop/myalert.sh 101 1> /home/develop/alert_succ_101.txt 2> /home/develop/alert_err.txt

# analyse the change in the status figures and prompt the user when MySQL is restarted, receives less than 3 queries or more than 10 slow queries
# myalert email

if [ -z "$1" ]
then
filename='/home/develop/status_success.txt'
else
filename="/home/develop/status_success_$1.txt"
fi

path='/home/shantanu'
User='root'
PassWord='root'
mydate=`date '+%d %B %H:%M'`
myhostname=`hostname`
ADMIN="shantanu.oak+$myhostname@gmail.com"

uptime=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -1)

threads=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -2 | tail -1)

questions=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -3 | tail -1)

slow=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -4 | tail -1)

opens=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -5 | tail -1 )

flush=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -6 | tail -1 )

otables=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -7 | tail -1)

aqueries=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -8 | tail -1)

echo "differnce in uptime is " $uptime
echo "differnce in Threads are " $threads
echo "difference in Questions are " $questions
echo "difference in Slow queries are " $slow
echo "difference in Opens " $opens
echo "difference in Flush tables " $flush
echo "difference in Open tables " $otables
echo "difference in Queries per second " $aqueries

if [[ $uptime -lt 0 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="MySQL restarted on $1 at $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "mysql restarted on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
done << mnumber_list
09702977470
mnumber_list

fi

if [[ $questions -lt 3 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="Queries $questions on $1 too few at $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "Few questions on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi

if [[ $slow -gt 10 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="Slow queries $slow on $1 are inordinately high as on $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "slow queries high on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi

# count the processes and alert the user if the processes are more than 230
myvar2=$(mysqladmin -u$User -p$PassWord processlist | wc -l | awk '{print int($1)}')
if [[ $myvar2 -gt 230 ]];then
mymessage="processes $myvar2 on $1 are inordinately high as on $mydate"

echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "slow queries high on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi

common my.cnf file

2010-11-24T04:34:00.000-08:00

[mysqld]

# change the default datadir path and hostname used for bin and relay log
# make sure that the binary data directory is available and has proper permissions
# /var/lib/mysql
# /var/log/mysql/
# /var/run/mysqld/

datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql

# Server ID should always be there preferably IP address
server-id=10101067

# default 8MB packet size is not enough
max_allowed_packet=1G

group_concat_max_len=500000
ft_min_word_len=2

collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake

########## Troubleshooting ##########

# change to strict sql mode or use traditional more more constraints
# sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE"
sql-mode=NO_ENGINE_SUBSTITUTION

# if mysql does not start
#user=root

#to skip password
#init_file=sql_commands.sql
#skip-grant-tables
#skip-networking

# values 1 to 6 with varying severity
#innodb_force_recovery=3

# to avoid replication issue
# slave_skip_errors=1062
# lower_case_table_names=1

########## Performance ##########

# faster responses
skip-name-resolve

# skip-locking # deprecated
skip-external-locking

# avoid error like Lost connection to MySQL server default 5
connect_timeout=15
max_connect_errors=4294967295

## global non engine-specific buffers
tmp_table_size=64M
max_heap_table_size=64M

# Following parameters can be changed after running analysis
# http://genomewiki.ucsc.edu/index.php/Tuning-primer.sh

#default table cache is 64
table_cache=1064
# default thread cache size is 0
thread_cache_size=16

# cat /proc/sys/net/core/somaxconn
back_log=128

# default 28800 seconds
#interactive_timeout=600
#default wait timeout 28800
#wait_timeout=600
# default is 100 for max connections
max_connections=900
# default is 0 for max user connections that means no limit
#max_user_connections=800

### per Thread buffers ###

#default for sort buffer is 2M
#sort_buffer_size=2M
#join_buffer_size=2M

#default for read buffer is .1 M
read_buffer_size=1M
# default for rnd buffer is .2M
read_rnd_buffer_size=8M
# default is 8M
# bulk_insert_buffer_size=16M

########## Caching ##########

## Query Cache
# query cache between 64-128M
query_cache_size=64M
#query_cache_limit=1M
#query_cache_type=1

########## Logging ##########
# General log can be enabled whenever required
# log=/var/log/mysql/general.log

# general_log=1 # for 5.1
# general_log_file=/var/log/mysql/general.log
# Slow query log should be enabled by default
# slow_query_log=1 # for 5.1
# slow_query_log_file=/var/log/mysql/slow.log

# log-slow-queries=/var/log/mysql/slow-log
long_query_time = 2
log-queries-not-using-indexes

# Error log with warnings should be enabled
log_warnings
log-error=/var/log/mysqld.err

# Binary logs should be there for trobleshooting and replication
# the hostname can be used as binary filename
# make sure that partition has enough space to save binary files
# and the directory should be available and owned by mysql
log-bin=/var/log/mysql/hostname

max_binlog_size=1024M
expire_logs_days=12
log_bin_trust_function_creators=TRUE
relay-log=/var/run/mysqld/hostname-relay-bin
# default of binlog cache is 32K
#binlog_cache_size=4M

# enabling this will make it slow but will help at the time of crash
#sync_binlog=1

########## MyISAM ##########

## myisam
# try du -sch /var/lib/mysql/*/*.MYI
key_buffer_size=256M

# sort, alter and repair functions uses this buffer
myisam_sort_buffer_size=64M

########## InnoDB ##########

default-storage-engine=InnoDB

# 50-80% of RAM
innodb_buffer_pool_size=1G

# innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_file_size=100M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit=2
#innodb_lock_wait_timeout = 50
innodb_additional_mem_pool_size=16M
innodb_flush_method=O_DIRECT

# cache tables while using file per table
innodb_open_files=1024
# number of cpus * number of disks * 2
innodb_thread_concurrency=16

# does not scale well with a large number of tables
innodb_file_per_table=1

########## Multi-maser replication ##########

## multi-master replication setup
#auto_increment_increment=2
#auto_increment_offset=1
slave_net_timeout=60
#log_slave_updates=1
#read_only=1

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
# default 1024
open_files_limit=4096
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

ACID compliant engine

2010-11-23T21:04:00.001-08:00

As all of us know that MyISAM is not ACID compliant as is InnoDB. In the following example, the first record is updated while the second one fails due to duplicate key error. This will not happen if you specify the engine as InnoDB.

mysql> create table test (a INT UNIQUE, b INT );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test VALUES (NULL, 2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test VALUES (NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> update test set a = 1 where b = 2;
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
mysql> select * from test;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| NULL | 2 |
+------+------+
2 rows in set (0.00 sec)

Unix Case Study 26

2010-11-17T22:56:00.000-08:00

Left outer join SQL like query from shell

basically i have two files:

frequency.txt: (multiple lines, space separated file containing words and a frequency)

de 1711
a 936
et 762
la 530
les 482
pour 439
le 425
...

and i have a file containing "prohibited" words:

stopwords.txt: (one single line, space separated file)

au aux avec le ces dans ...

so i want to delete from frequency.txt all the lines containing a word found on stopwords.txt

how could i do that? i'm thinking that it could be done with awk.. something like

awk 'match($0,SOMETHING_MAGICAL_HERE) == 0 {print $0}' frequency.txt > new.txt

but i'm not really sure... any ideas?? thxs in advance

http://stackoverflow.com/questions/3978626/shell-to-filter-prohibited-words-on-a-file

Creating a filler table

2010-11-15T22:28:00.000-08:00

If I need to create a table with a million auto generated id's then I can use the filler table as shown below.


CREATE TABLE filler (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=Memory;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

CALL prc_filler(500000);

MySQL strict mode

2010-11-15T01:07:00.000-08:00

If you want mysql to care for the data-integrity then you need to add the following line to the my.cnf file.

sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE"

NO_ENGINE_SUBSTITUTION
If innodb engine is not available, the create table statement will throw a warning and create a MyISAM table. This mode will avoid this and throw an error instead of using default engine.

STRICT_TRANS_TABLES
Enable strict mode for InnoDB. Instead of truncating 20 characters to fit into varchar(10), it will throw an error. If you want MyISAM tables to behave the same way, use STRICT_ALL_TABLES

NO_ZERO_IN_DATE
Do not accept all zeros in the date or month field. (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If you do not want zero dates like 0000-00-00 then you need to use NO_ZERO_DATE

The following is the default behavior of MySQL

create table sqlmode(id varchar(2), birth_date date);
insert into sqlmode values ('abc', '1970-00-16');
insert into sqlmode values ('ab', '1970-00-16');
insert into sqlmode values ('abc', '1970-11-16');

mysql> select * from sqlmode;
+------+------------+
| id | birth_date |
+------+------------+
| ab | 1970-00-16 |
| ab | 1970-00-16 |
| ab | 1970-11-16 |
+------+------------+
3 rows in set (0.00 sec)

When the sql-mode is turned on, all the above insert statements will fail. It will allow the record to enter only when the varchar and date columns are valid.

mysql> insert into sqlmode values ('abc', '1970-00-16');
ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> insert into sqlmode values ('ab', '1970-00-16');
ERROR 1292 (22007): Incorrect date value: '1970-00-16' for column 'birth_date' at row 1

mysql> insert into sqlmode values ('abc', '1970-11-16');
ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> insert into sqlmode values ('ab', '1970-11-16');
Query OK, 1 row affected (0.00 sec)

mysql> select * from sqlmode;
+------+------------+
| id | birth_date |
+------+------------+
| ab | 1970-11-16 |
+------+------------+
1 row in set (0.00 sec)

Upgrading to 5.1 version from 5.0

2010-11-10T02:05:00.001-08:00

1) Download Server from...
http://mysql.com/downloads/mirror.php?id=395761

MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm

Download client from ...
http://mysql.com/downloads/mirror.php?id=395683

MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm

Stop current MySQL deamon before installing the new version.

2) I have to uninstall 5.0 before installing 5.1 or else I get an error as shown below:

# rpm -ivh MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm
error: Failed dependencies:
MySQL conflicts with mysql-5.0.77-3.el5.x86_64
MySQL-server conflicts with mysql-server-5.0.77-3.el5.x86_64

3) While uninstalling 5.0 I was informed that the dependencies too needs to be removed.

# rpm -e mysql-5.0.77-3.el5
error: Failed dependencies:
libmysqlclient.so.15()(64bit) is needed by (installed) perl-DBD-mysql-4.014-1.el5.rf.x86_64
libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by (installed) perl-DBD-mysql-4.014-1.el5.rf.x86_64
mysql is needed by (installed) perl-DBD-mysql-4.014-1.el5.rf.x86_64

4) SO I remove everything that is getting in my way.

# rpm -e mysql-5.0.77-3.el5 perl-DBD-mysql-4.014-1.el5.rf.x86_64 mysql-mmm-agent-2.2.1-1.el5.noarch

5) And now I am all set to install the shiny new MySQL version

# time rpm -ivh MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm

6) Make sure it is installed
# rpm -qa | grep -i mysql
MySQL-server-community-5.1.52-1.rhel5
mysql-mmm-2.2.1-1.el5
MySQL-client-community-5.1.52-1.rhel5

7) Use the mysql_upgrade command to update the user tables. If your mysql.proc table gets corrupted due to any reason (for e.g. while trying to install 5.0 dump), use the following:

mysql_upgrade -f

This will forcefully fix the privilege tables. The 5.0 dump can be imported using the same -f (force) option as well. For e.g.

mysql -uroot -proot@123 -f < /home/today.sql

_____

Installing 5.0 version of mysql is easy.

# check what is installed
# rpm -qa | grep mysql-*
mysql-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2
mod_auth_mysql-3.0.0-3.2.el5_3

# remove any conflicting version of mysql
# rpm -e mysql-5.0.77-4.el5_5.3.x86_64 --nodeps

# install using yum
# yum install mysql-*

Setting up Install Process
Package mysql-5.0.77-4.el5_4.2.x86_64 already installed and latest version
Package mysql-5.0.77-4.el5_4.2.i386 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package mysql-bench.x86_64 0:5.0.77-4.el5_4.2 set to be updated
=
Install 16 Package(s)
Update 5 Package(s)
Remove 0 Package(s)

Total size: 27 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : krb5-libs 1/26
Installing : perl-DBD-MySQL 2/26
Installing : mysql-server 3/26
Updating : libsepol 4/26
Cleanup : libsepol 24/26
Cleanup : krb5-workstation 25/26
Cleanup : krb5-libs 26/26

Installed:
mysql-bench.x86_64 0:5.0.77-4.el5_4.2 mysql-connector-odbc.x86_64 0:3.51.26r1127-1.el5 mysql-devel.i386 0:5.0.77-4.el5_4.2 mysql-devel.x86_64 0:5.0.77-4.el5_4.2
mysql-server.x86_64 0:5.0.77-4.el5_4.2 mysql-test.x86_64 0:5.0.77-4.el5_4.2

Dependency Installed:
e2fsprogs-devel.x86_64 0:1.39-23.el5 keyutils-libs-devel.x86_64 0:1.2-1.el5 krb5-devel.x86_64 0:1.6.1-36.el5_4.1 libselinux-devel.x86_64 0:1.33.4-5.5.el5
libsepol-devel.x86_64 0:1.15.2-3.el5 libtool-ltdl.x86_64 0:1.5.22-7.el5_4 openssl-devel.x86_64 0:0.9.8e-12.el5_4.6 perl-DBD-MySQL.x86_64 0:3.0007-2.el5
unixODBC.x86_64 0:2.2.11-7.1 zlib-devel.x86_64 0:1.2.3-3

Dependency Updated:
krb5-libs.i386 0:1.6.1-36.el5_4.1 krb5-libs.x86_64 0:1.6.1-36.el5_4.1 krb5-workstation.x86_64 0:1.6.1-36.el5_4.1 libsepol.i386 0:1.15.2-3.el5
libsepol.x86_64 0:1.15.2-3.el5

Complete!

# start mysql
# /etc/init.d/mysqld start
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h vishnuvm7.hyd.wc password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]
Starting MySQL: [ OK ]
[root@vishnuvm7 shantanu]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

# rpm -qa | grep -i mysql-*
mysql-5.0.77-4.el5_4.2
mysql-server-5.0.77-4.el5_4.2
mysql-test-5.0.77-4.el5_4.2
mysql-bench-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2
perl-DBD-MySQL-3.0007-2.el5
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-devel-5.0.77-4.el5_4.2
mysql-devel-5.0.77-4.el5_4.2

Full text search

2010-11-08T02:50:00.000-08:00

MySQL's full text search is not the only option to search for a word anywhere in the table. For e.g. I have a table called tblName in the database "DbName" and I want to search for a word "denmyr".
I am not sure about which column to search for. I can use the following shell script to generate XML file of the records those contain the word.

# vi fulltext.sh

#!/bin/sh
mydata=`mysqldump DbName tblName --skip-extended-insert | grep -i 'denmyr' | awk '{print $5}' | awk -F',' '{print $1}' | replace '(' ''`
for record in $mydata
do
mysql DbName -Xe"select * from tblName where ofac_key=$record"
done

Logging status of MMM

2010-11-01T23:35:00.000-07:00

You can check the Multi Master status and log it to a text file whenever an issue is detected.

#!/bin/sh
qsec=`/usr/sbin/mmm_control show | grep ONLINE | grep -v "^$" | wc -l`
SEC=0
if [ $qsec -eq $SEC ]; then

curl -Ld"user=shantanu.oak@gmail.com:password&senderID=TEST SMS&receipientno=9702977470&msgtxt=problem with MMM detected as on `date`&state=4" http://api.mVaayoo.com/mvaayooapi/MessageCompose

echo "problem with MMM detected as on `date`" >> /home/shantanu/mmstatus.txt
/usr/sbin/mmm_control show >> /home/shantanu/mmstatus.txt

fi

Master - Maser Replication Manager

2010-11-01T21:49:00.001-07:00

MMM (Master-Master Replication Manager) is a set of flexible scripts to perform monitoring/failover and management of MySQL Master-Master replication configurations (with only one node writable at any time). The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication.

The main functionality is provided through the following three scripts:

mmm_mond: monitoring daemon which does all monitoring work and makes all decisions about roles moving and so on.
mmm_agentd: agent daemon which runs on each MySQL server and provides monitoring node with simple set of remote services.
mmm_control: simple script dedicated to management of the mmm_mond processes by commands.

# start and stop MMM Monitor
/etc/init.d/mysql-mmm-monitor start
/etc/init.d/mysql-mmm-monitor status
/etc/init.d/mysql-mmm-monitor stop

# Show MMM details
/usr/sbin/mmm_control show
/usr/sbin/mmm_control show | grep -v 'ONLINE'
/usr/sbin/useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

# Config files
vi /etc/default/mysql-mmm-monitor
vi /etc/mysql-mmm/mmm_common.conf
vi /etc/mysql-mmm/mmm_mon.conf

A typical MMM setup looks like this...
/usr/sbin/mmm_control show
db1(192.168.100.101) master/ONLINE. Roles: reader(192.168.100.122)
db2(192.168.100.102) master/ONLINE. Roles: reader(192.168.100.121), writer(192.168.100.123)

And in order to check the logs on monitor and DB servers you can use the command...
tail /var/log/mysql-mmm/mmm_mond.log
tail /var/log/mysql-mmm/mmm_agentd.log

Here are the permissions those need to be created in order to monitor the replication.

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'agent_password';

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password';

grant replication slave on *.* to 'slave_user'@'%' IDENTIFIED BY 'slave_user';
# One of the last 2 statements can be used

And do not forget to check if the following setting is there in my.cnf file
log_slave_updates = 1

More info can be found here...
http://mysql-mmm.org/mmm2:guide

MySQL Case Study - 180

2010-11-01T03:33:00.000-07:00

Finding records in a given date range

I'm very new to MySQL and I'm trying to query between 4 specific times each day;

1. Where >= PREVIOUS DAY 15:00:00 and < TODAY 11:00:00
OR
2. Where >= TODAY 11:00:00 and < TODAY 15:00:00

Is it even possible to use the NOW() plus a specific time? This would query each day. If anyone can helps, that would be great!

http://forums.mysql.com/read.php?22,388833,388833#msg-388833

MySQL Data Directory

2010-11-01T02:36:00.000-07:00

MySQL Data directory should have it's own partition. The main adtantage is that you can simplify the backup process. So assuming that the sda and sdb both have equal size, we can copy the data from one partition to another.

Backup Entire Harddisk
To backup an entire copy of a hard disk to another hard disk.
Device name of the source hard disk is /dev/hda, and device name of the target hard disk is /dev/hdb.
# dd if=/dev/sda of=/dev/sdb

* “if” represents inputfile, and “of” represents output file. So the exact copy of /dev/sda will be available in /dev/sdb.
* If there are any errors, the above command will fail. If you give the parameter “conv=noerror” then it will continue to copy if there are read errors.
* Input file and output file should be mentioned very carefully, if you mention source device in the target and vice versa, you might loss all your data.

In the copy of hard drive to hard drive using dd command given below, sync option allows you to copy everything using synchronized I/O.
# dd if=/dev/sda of=/dev/sdb conv=noerror,sync

Create an Image of a Hard Disk
# dd if=/dev/hda of=~/hdadisk.img

Restore using Hard Disk Image
# dd if=hdadisk.img of=/dev/hdb

Aspersa tools

2010-11-01T00:22:00.000-07:00

The Aspersa tool does tell you almost everything that I need to know about MySQL variables.

http://aspersa.googlecode.com/svn/html/mysql-summary.html

It seems to be one of the most important tool any DBA will like to have in his toolbox.

MySQL Case Study - 179

2010-10-27T05:32:00.001-07:00

Efficient full-text searchs on large sets of data

This is the answer to the question posted by one of the Experts on this blog.

http://community.livejournal.com/mysql/138390.html

If you want to search for a word in a given table, the best option is to use the 'Full Text Search' that is available only for the MyISAM tables.
As you can see from the following statements, I can search for the word "sham" without using Full Text indexes.
You will have to add the Stored procedure available here...

http://forge.mysql.com/tools/tool.php?id=204

The only statement that needs explanation is the call for the stored procedure. Here is how to use the procedure to explode almost any table to your advantage.

CALL mysql.normalize_table('test.Employee', 'id', 'Name', ' ');

"mysql" can be replaced with the DB Name where the procedure is stored.
"normalize_table" is the name of the procedure that you can download from URL mentioned above.
"test.Employee" is the table to be split.
"id" is the Unique key of that table (usually auto incremented primary key)
"Name" is the column name that needs to be exploded.
Space ' ' is the delimiter.

_____

mysql>drop table if exists mysql.SplitValues;
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE mysql.SplitValues (cid varchar(40), value varchar(500) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql>drop table if exists test.Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE test.Employee (
`id` int(11) default NULL,
`Name` varchar(50) NOT NULL,
`PhoneNo` varchar(15) default 'Unknown Phone',
PRIMARY KEY (`id`)
);
Query OK, 0 rows affected (0.03 sec)

mysql>INSERT INTO test.Employee VALUES (1, 'Manohar Ram','12345');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT INTO test.Employee VALUES (2, 'Manohar1 sham','12345');
Query OK, 1 row affected (0.00 sec)

mysql>select * from test.Employee;
+----+---------------+---------+
| id | Name | PhoneNo |
+----+---------------+---------+
| 1 | Manohar Ram | 12345 |
| 2 | Manohar1 sham | 12345 |
+----+---------------+---------+
2 rows in set (0.00 sec)

mysql>CALL mysql.normalize_table('test.Employee', 'id', 'Name', ' ');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>select * from mysql.SplitValues;
+------+----------+---------------------+
| cid | value | dateadded |
+------+----------+---------------------+
| 1 | Manohar | 2010-10-27 08:18:56 |
| 1 | Ram | 2010-10-27 08:18:56 |
| 2 | Manohar1 | 2010-10-27 08:18:56 |
| 2 | sham | 2010-10-27 08:18:56 |
+------+----------+---------------------+
4 rows in set (0.00 sec)

mysql>alter table SplitValues add key (value, cid);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>select a.* from test.Employee as a inner join mysql.SplitValues as b on a.id = b.cid where b.value = 'sham';
+----+---------------+---------+
| id | Name | PhoneNo |
+----+---------------+---------+
| 2 | Manohar1 sham | 12345 |
+----+---------------+---------+
1 row in set (0.00 sec)

(08:22) mysql>explain select a.* from test.Employee as a inner join mysql.SplitValues as b on a.id = b.cid where b.value = 'sham';
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | b | ref | value | value | 503 | const | 1 | Using where; Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | mysql.b.cid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
2 rows in set (0.01 sec)

Understanding Foreign Keys

2010-10-26T02:45:00.000-07:00

1) Foreign keys are used to restrict the data being inserted unless the parent record is created. Isn't it logical that the child can not be born unless the parents are born? Therefore when I try to create the child table first, I get an error 1005 - (Can't create table).
2) While I am inserting the records, I have to follow the same sequence.
Therefore the first insert into child table fails with error no 1452 - (Cannot add or update a child row: a foreign key constraint fails) since no corresponding record is present in the parent table. Once I insert a record in parent table, my child table will allow it as well.
3) When I am trying to drop both the tables, I need to drop the child first and then the parent. Or else I get an error 1217 - (Cannot delete or update a parent row)

# Remember, the child has to reference a column to parent and not the other way round.

create table child(
userID int not null,
FOREIGN KEY (userID) REFERENCES parent(ID),
primary key (userID)
) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table './test/child.frm' (errno: 150)

CREATE TABLE parent(
`ID` int(11) default NULL,
`name` varchar(100) default NULL,
`city` varchar(100) default NULL,
key (ID)
) ENGINE=InnoDB;
create table child(
userID int not null,
FOREIGN KEY (userID) REFERENCES parent(ID),
primary key (userID)
) ENGINE=InnoDB;

insert into child values (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `parent` (`ID`));

insert into parent values (1, 'abc', 'delhi');
insert into child values (1);

drop table if exists parent;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails;

drop table if exists child;
drop table if exists parent;

The same table can be a parent and a child. It's like a family tree. The grandpa has 2 sons and each son will have 1 or 2 children.

http://dev.mysql.com/downloads/workbench/

If you are using mysql workbench, you can easily create a graphical representation of the data structure. On the "Database" menu, there is a "Reverse Engineer" wizard. It will let you connect to a MySQL server and reverse engineer schema. The graph generated will show the "References:" and "Referenced By:" columns. "References" will show the parents of the current table and "Referenced By" will show its children. Parents are shown in green dotted line with the sing of ">". The child tables are linked with blue line.

Dates as text?

2010-09-02T04:20:00.000-07:00

Saving dates in text column is a bad idea. But if we already have data entered into the column, how to correct it?
In the following example, I have stored all the values in mm/dd/YYYY format and want to change it to the standard mysql date format.

mysql>create table datevar(mydate varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql>insert into datevar values ('12/31/2009'), ('3/23/2010'), ('09/14/1999');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql>select * from datevar;
+------------+
| mydate |
+------------+
| 12/31/2009 |
| 3/23/2010 |
| 09/14/1999 |
+------------+
3 rows in set (0.00 sec)

mysql>update datevar set mydate = str_to_date(mydate, '%m/%d/%Y');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql>select * from datevar;
+------------+
| mydate |
+------------+
| 2009-12-31 |
| 2010-03-23 |
| 1999-09-14 |
+------------+
3 rows in set (0.00 sec)

mysql>alter table datevar modify mydate date;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql>show create table datevar\G
*************************** 1. row ***************************
Table: datevar
Create Table: CREATE TABLE `datevar` (
`mydate` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

MySQL Case Study - 178

2010-08-31T00:13:00.000-07:00

substring to return all values after delimiter
How do I get all the values after the first delimiter? In the following example I am expecting 'xyz@yahoo.com,pqr@company.com'

(02:40) mysql>select substring_index('abc@hotmail.com,xyz@yahoo.com,pqr@company.com', ',', 1) as first;
+-----------------+
| first |
+-----------------+
| abc@hotmail.com |
+-----------------+
1 row in set (0.00 sec)

(02:41) mysql>select substring_index('abc@hotmail.com,xyz@yahoo.com,pqr@company.com', ',', -1) as last;
+-----------------+
| last |
+-----------------+
| pqr@company.com |
+-----------------+
1 row in set (0.00 sec)

http://stackoverflow.com/questions/3606623/substring-to-return-all-values-after-delimiter

Using different location for Data

2010-08-25T02:01:00.000-07:00

You can specify the path of the data directory while creating a table. For e.g.

create table mytest (id int, name varchar(100))
ENGINE = MyISAM
COMMENT = 'data directory is var mylink configured by shantanu'
DATA DIRECTORY = '/var/mylink';

I have added the comment so that others will know about my expertise. The index file .MYI can also be saved in the same location using the following parameter.
INDEX DIRECTORY = '/var/mylink';
_____

If you have already created the table, you will have to manually move the file to the new location and crate a symbolic link.

1) Check if symbolic links are supported.
mysql>SHOW VARIABLES LIKE 'have_symlink';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink | YES |
+---------------+-------+
1 row in set (0.06 sec)

2) Lock the table so that no writes are possible.
mysql>flush tables with read lock;

3) Create a directory on the partition where there is enough space. For e.g. /var/
mkdir /var/mylink

4) Change the permission to 777 or 770
chown mysql:mysql /var/mylink
chmod 770 /var/mylink

5) Move the big data file to the new location
mv /mysql-data/shantanu/contact_attach.MYD /var/mylink/contact_attach.MYD

6) Symbolically link it
ln -sf /var/mylink/contact_attach.MYD /mysql-data/shantanu/contact_attach.MYD

7) Unlock tables
mysql> unlock tables;

8) flush tables so that mysql will reread table.
mysql>flush tables shantanu.contact_attach;

Repair the table if necessary.

When I issue the repair table command, a tempoarary file (.MYD) is created in the new location and not in the data-directory.
# ls -lht
total 3.0G
-rw-rw---- 1 mysql mysql 1.3G Aug 24 01:07 contact_attach.TMD
-rw-rw---- 1 mysql mysql 1.7G Aug 24 00:55 contact_attach.MYD

Note: The read lock will be global. It is necessary while we move the file to the new location. The expected downtime will be less than one minute per GB. You can move the file without the lock if you are sure that the company is not in use at that time.

3 reasons to avoid timestamp

2010-08-21T03:40:00.000-07:00

Here are 3 reasons why you should avoid "timestamp" column type.

1) Range: It can store values only from 1st Jan 1970 to 31 Dec 2037. This range is not enough for most applications.

2) On update clause:
As you can see from the following screen, I specified only a "timestamp" column type in the create table statement. MySQL silently added the "on update CURRENT_TIMESTAMP". When I tried to update a column, the value of "mytime" column was changed as well from 02 to 33 (seconds). This is unexpected behavior for a novice coder.

mysql>use test;
mysql>create table stamptest(id int, name varchar(100), mytime timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql>show create table stamptest\G
*************************** 1. row ***************************
Table: stamptest
Create Table: CREATE TABLE `stamptest` (
`id` int(11) default NULL,
`name` varchar(100) default NULL,
`mytime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql>insert into stamptest values (1, 'john', NULL);
Query OK, 1 row affected (0.01 sec)

mysql>select * from stamptest;
+------+------+---------------------+
| id | name | mytime |
+------+------+---------------------+
| 1 | john | 2010-08-21 06:21:02 |
+------+------+---------------------+
1 row in set (0.00 sec)

mysql>update stamptest set name = 'abcd' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(06:21) mysql>select * from stamptest;
+------+------+---------------------+
| id | name | mytime |
+------+------+---------------------+
| 1 | abcd | 2010-08-21 06:21:33 |
+------+------+---------------------+
1 row in set (0.00 sec)

3) Back-up using dump
The time saved in the timestamp column will get adjusted to the timezone of the server from where you are trying to run the mysqldump command.
The --skip-tz-utc switch needs to be added to the dump command and most people are unaware of this.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tz-utc

The only advantage that I can see is that it consumes 4 bytes compared to datetime's 8 bytes. This does not make much difference to most general purpose applications.

The poor PHP programmers have their own deadlines and are less interested in learning these things. Do not use "timestamp" column type and avoid using from_unixtime() and unix_timestamp() functions to save datetime at any cost. The only option left to save the date+time is... what else? "datetime"!!

Information Schema Tables

2010-08-20T23:02:00.001-07:00

When the database has too many tables or when the innodb table space is too large, you can not run queries against information_Schema Database. It is a good idea to copy the data of those tables to a new database, for e.g. "schemaBackup". Here is the script that will do the needful.

# cat stats.sh
#!/bin/sh
mysql -e"create database if not exists schemaBackup";

for tblName in `mysql -e"show tables from information_schema"`
do
mysql -e"drop table if exists schemaBackup.$tblName;"
mysql -e"create table schemaBackup.$tblName select * from information_schema.$tblName;"
done

for tblName in `mysql -e"show tables from mysql"`
do
mysql -e"drop table if exists schemaBackup.$tblName;"
mysql -e"create table schemaBackup.$tblName select * from mysql.$tblName;"
done

Backup using mylvmbackup

2010-08-19T23:14:00.000-07:00

Here are the commands to create a mylvmbackup user with minimal privileges:

CREATE USER 'mylvmbackup'@'localhost' IDENTIFIED BY '12345';
GRANT RELOAD, REPLICATION CLIENT ON *.* TO 'mylvmbackup'@'localhost';
GRANT SELECT ON mysql.* TO 'mylvmbackup'@'localhost';

In the mylvmbackup.conf file, the correlating rows are:

[mysql]
user=mylvmbackup
password=12345
host=localhost

By default, mylvmbackup creates a .tar.gz compressed backup file of your data.

mylvmbackup supports backing up the uncompressed files using rsync. To copy MySQL data to a remote host, configure the following in the mylvmbackup.conf file:
[fs]
backupdir=root@backuphost:/data/backup/mysql
[misc]
backuptype=rsync

You can backup to a remote host or save the files locally.
[fs]
backupdir=/mnt/backup/mysql
[misc]
backuptype=rsync

Memory loss

2010-08-18T03:35:00.000-07:00

There are times when you see the following PHP error in the error log.

[Mon Aug 02 14:28:11 2010] [error] PHP Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 292995097 bytes) in /var/www/html/text.inc on line 25, referer: https://yahoo.com/Compose.php?con_id=cand74035

The usual fix for this problem is as follows. At the top of the compose.php page use the following statement.

ini_set("memory_limit","200M");

This will increase the memory allocation for the current page only.

Disable or enable logging

2010-08-18T03:27:00.001-07:00

When you are restoring data from a dump file, the logging needs to be disabled. Here are 2 shell scripts that will disable (or enable) the binary, slow and general logs completely. The mysql service needs to be restarted after you run this script.

If you do not want to restart the mysql service then login to mysql prompt and type...
mysql> set sql_log_bin = 0;
mysql> use DB_Name;
mysql> source dumpfile.sql;

# cat enable_logs.sh
#/bin/sh
# enable logs by removing the comment
# first backup the current my.cnf
cp /etc/my.cnf /home/develop/my_$(date +'%d-%m-%y-%H-%M').cnf

sed -i "s/^#log-slow-queries=/log-slow-queries=/" /etc/my.cnf
sed -i "s/^#log-bin=/log-bin=/" /etc/my.cnf
#sed -i "s|^log=.*$|#\\0|" /home/develop/development.cnf
# general logs are disable

# restart mysql service
#/etc/init.d/mysql restart

# cat disable_logs.sh
#/bin/sh
# disable logs by adding the comment
# backup the current my.cnf file to develop subfolder
cp /etc/my.cnf /home/develop/my_$(date +'%d-%m-%y-%H-%M').cnf

sed -i "s|^log-slow-queries=.*$|#\\0|" /etc/my.cnf
sed -i "s|^log-bin=.*$|#\\0|" /etc/my.cnf
#sed -i "s|^log=.*$|#\\0|" /home/develop/development.cnf
# general logs are disabled

# restart mysql service
#/etc/init.d/mysql restart

Extract of Apache error log

2010-08-18T02:30:00.000-07:00

There are times when you need to check the latest entries in the apache error log.
Here is the easy way to do so.

Cron entry:
# extract of apache error log
# updated every 5 minutes and posted to http://yourSite.com/shantanu/index.txt
*/5 * * * * sh /home/develop/apache.sh >> /home/develop/apache_succ.txt 2>> /home/develop/apache_err.txt

Shell script:

# cat /home/develop/apache.sh
#!/bin/sh

mytemp='/home/develop/mytemp.txt'
mypath='/home/develop/index.txt'
> $mypath
> $mytemp

for file in `find /var/log/httpd/ -name "*log"`
do
tail -100 $file | grep "`date +%h\ %d`" >> $mytemp
done

sed -i '/^$/d' $mytemp
grep -v 'favicon' $mytemp > $mypath
mv $mypath /var/www/html/shantanu/

Processlist snapshot

2010-08-18T01:59:00.000-07:00

You can check the slow query log to find the query that is taking too long to complete. But What if you want to see the entire processlist at that time?
Here is the shell script that will do the needful. You will have to add it to cron like this...
# log high processes
*/10 * * * * sh /home/develop/log_process.sh >> /home/develop/kill_process.txt 2>> /home/develop/kill_proc_err.txt

# cat log_process.sh
#!/bin/bash
SEC=100
mnumber=919702977470
IFS='|'

mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then

echo `date`
echo " "
echo `mysqladmin proc`
# mysqladmin kill $qid
# echo "Killed query $qid..."
echo "##############################"
echo "##############################"

fi

if [ $qsec -gt $SEC ]; then

curl -Ld'username=SomeUser&password=PassWord&source=oksoft&dmobile='$mnumber'&message=process running more than 1000 seconds on QA DB server' http://67.23.229.95
/smsclient//api.php

fi

done

mysqlreport

2010-08-18T01:52:00.001-07:00

mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative to manually interpreting SHOW STATUS.

http://hackmysql.com/mysqlreport

Partitions problem

2010-08-18T01:44:00.000-07:00

This is how a typical server look like...

# df -HP
Filesystem Size Used Avail Use% Mounted on
/dev/hda8 1.1G 502M 489M 51% /
/dev/hda1 128M 11M 111M 9% /boot
tmpfs 1.1G 0 1.1G 0% /dev/shm
/dev/hda7 1.1G 44M 947M 5% /home
/dev/hda3 27G 15G 9.8G 61% /mysql-bin-log
/dev/hda2 105G 18G 82G 18% /mysql-data
/dev/hda6 3.2G 1.1G 1.9G 36% /usr
/dev/hda9 16G 4.7G 11G 32% /var

The root / directory has space of only 1 GB and that is not enough. Mysql uses the /tmp folder to save its .sock file and other temporary files those are created at the time of alter and repair. There should be at least 10 GB space allocated for root.

disable trigger

2010-08-18T01:38:00.001-07:00

There are times when you want to add some data in a table and the trigger on that table has some complex logic built-in. In order to disable the trigger temporarily, add a condition in your triggers like:

if (@DISABLE_TRIGER IS NULL) then
#trigger body
end if;

and than if you want to disable triggers on import just:

SET @DISABLE_TRIGER=1;
do imports
SET @DISABLE_TRIGER=NULL;

Finding relations

2010-08-17T22:57:00.000-07:00

Finding the relations within the tables is possible by looking into the mysql general log. Here is an example

# grep -Eo '( *[^ ]* *){4}some_tbl_name( *[^ ]* *){4}' /mysql-bin-log/mysql-gen.log | head -10000 | sort -u

In the example above, the table "accounts" is related to sourcing. You can add word boundaries like \less than sign and \greater than sign.
Make sure that the general logs are enabled, before using the command.

Repair broken tables

2010-08-17T00:13:00.000-07:00

You can find out the names of corrupt tables from the error log and repair them.

tail -10000 `grep 'error=' /etc/my.cnf | awk -F'=' '{print $2}'` | grep "`date +%y%m%d`" | awk -F"ERROR" '{print $2}' | sort -u | sed -e "s/.*\.\//REPAIR TABLE /g" -e "s/[\\.'].*/;/g" -e "s/\//./"

It will grab the error log location from the my.cnf file. The statement above will output the "repair table db.tbl;" statements. You can pass them on to mysql using
| mysql -f

Finding optimum column width

2010-08-16T23:53:00.000-07:00

I have a column "fromadd" in the table headers. The table exist in all the databases. How do I find out what is the maximum length of data that has been stored in this column?

# cat whycorrupt.sh
#!/bin/sh

mypath='/home/develop/toprocess.txt'
> $mypath

for dbname in `mysqlshow`
do
mysql -e"select * from $dbname.headers procedure analyse ()\G" >> $mypath
done

echo "fromadd"
grep -A4 'fromadd' toprocess.txt | grep 'Max_length' | sort -nrk2 | head

The above script will output something like this...

fromadd
Max_length: 120
Max_length: 119
Max_length: 119
Max_length: 119
Max_length: 119
Max_length: 116
Max_length: 107
Max_length: 70
Max_length: 70
Max_length: 68

Now I can set varchar(200) for the column "fromadd". I can use the same loop mentioned above to run the alter table statement.

_____

You can create a text file with all the details of the Max and minimum values stored in that table. You will have to supply the Database name as the first positional parameter.

$ sh proan.sh dbName

#!/bin/bash
> max.txt

mysqlshow $1 --count >> max.txt
while read tblname ; do

mysql -e"select '$tblname' as tableName, count(*) as count from $tblname" >> max.txt
mysql -e"show create table $tblname\G" >> max.txt
mysql -e"select * from $tblname procedure analyse()\G" >> max.txt

done < `mysql -BNe"select CONCAT(TABLE_SCHEMA, '.' , TABLE_NAME) as noname from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$1'"`

HERE

Timezone error in apache

2010-08-16T05:17:00.000-07:00

There are times when you see the following error in the apache error log.

[Sun Aug 08 14:06:12 2010] [error] [client 199.199.199.199] PHP Strict Standards: date() [function.date]: It is not safe to rely on the system's timezone settings. Please use the date.timezone setting, the TZ environment variable or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EDT/-4.0/DST' instead in /var/www/html/global_fun.inc on line 524, referer: https://yahoo.com/edit.php?acc=deliver&addr=543

This is because of timezone mismatch.

You can export the time zone variable as shown below.
export TZ="America/New_York"

Or add it to the php.ini file.

[Date]
; Defines the default timezone used by the date functions
date.timezone = America/New_York

Or else, set the timezone in the code itself.

date_default_timezone_set('America/New_York');
echo date('D,F j, Y, h:i:s A');
?>

You can check the current time in the following file.

# cat /etc/sysconfig/clock
ZONE="America/New_York"
UTC=true
ARC=false

The following file shows that we are not sure about the current localtime. It is probably taking the time from system clock.

# ls -l /etc/localtime
-rw-r--r-- 1 root root 1267 Mar 5 2008 /etc/localtime

The localtime file needs to be linked to the correct time zone.
ln -sf /usr/share/zoneinfo/America/New_York /etc/localtime

All the above examples use America (New York) timezone has default one. You will have to change it if you need a different zone.

Table size

2010-08-16T04:36:00.001-07:00

You can check the size of the table using the following query. If it is a myisam table, you can check the actual file size on disk.

# mysql -e"SELECT COUNT(*) TABLES,
> SUM(table_rows) rows,
> SUM(data_length) DATA,
> SUM(index_length) idx,
> SUM(data_length + index_length) total_size
> FROM information_schema.TABLES where table_schema = 'customers' and table_name = 'mail_headers'"
+--------+------+--------+--------+------------+
| TABLES | rows | DATA | idx | total_size |
+--------+------+--------+--------+------------+
| 1 | 1695 | 518708 | 270336 | 789044 |
+--------+------+--------+--------+------------+

# ls -l /mysql-data/customers/mail_headers.*
-rw-rw---- 1 mysql mysql 9460 Aug 16 06:39 /mysql-data/customers/mail_headers.frm
-rw-rw---- 1 mysql mysql 518708 Aug 16 06:39 /mysql-data/customers/mail_headers.MYD
-rw-rw---- 1 mysql mysql 270336 Aug 16 06:39 /mysql-data/customers/mail_headers.MYI

The same query can be used to know the total size of the database. You have to remove the table_name clause. You can simply check the disk consumption if all the tables are myisam.

# mysql -e"SELECT COUNT(*) TABLES,
SUM(table_rows) rows,
SUM(data_length) DATA,
SUM(index_length) idx,
SUM(data_length + index_length) total_size
FROM information_schema.TABLES where table_schema = 'customers'"
+--------+-------+-----------+---------+------------+
| TABLES | rows | DATA | idx | total_size |
+--------+-------+-----------+---------+------------+
| 496 | 28406 | 274267486 | 4054016 | 278321502 |
+--------+-------+-----------+---------+------------+

# du -h /mysql-data/customers
274M /mysql-data/customers

To comma or not to comma

2010-08-12T21:29:00.000-07:00

This is related to the discussion on comma joins.

http://www.mysqlperformanceblog.com/2010/04/14/is-there-a-performance-difference-between-join-and-where/

Here are a few points to note:

1) "Inner join" syntax is ANSI compliant. Using the standard way of writing queries is better.

2) When you need to rewrite the comma syntax to left join, it will have to re-code the whole structure and adopt the join syntax.

SELECT * FROM A,B WHERE A.id = B.id and A.x=123;

If you have already written the above query, it will be difficult to write the left join as shown below.
SELECT * FROM A LEFT OUTER JOIN B ON A.id=B.id WHERE A.x=123;

If you had written the inner join as shown below, it is easy to change to Left join.
SELECT * FROM A INNER JOIN B ON A.id = B.id WHERE A.x=123

3) In the following example, isn't it difficult to find the relations between tables?

SELECT * FROM A, B, C, D WHERE A.x=123,
B.DATE > CURDATE()
...
some more conditions
...

D.id = C.id and
C.id = B.id and
A.id = B.id

Isn't the following much easy to read and understand?

SELECT * FROM A
INNER JOIN B ON B.id = A.id
INNER JOIN C C.id = B.id
INNER JOIN D D.id = C.id
WHERE A.x=123, B.DATE > CURDATE()

I prefer that the column of table that is being joined come first in the sequence and therefore B.id = A.id and not A.id = B.id though both are the same.

4) The forth point is that if I forget to add the comma, it becomes a table alias and things become difficult to find what went wrong.

SELECT * FROM A B WHERE B.id=123;

The above is a perfectly valid query but with wrong results since the table A is aliased as B!!

5) Updates
We need to join tables not just in "select", but in updates too. The update statement can be written using comma joins. But see the difference between these two queries and decide which one would you prefer.

UPDATE phpbb2_posts_text A, phpbb3_posts3 B -- implicit comma join
SET B.bbcode_uid=A.bbcode_uid
, B.post_subject=A.post_subject
, B.post_text=A.post_text
WHERE A.somecolumn = B.somecolumn
AND B.post_id<=218727

UPDATE phpbb2_posts_text A
INNER
JOIN phpbb3_posts3 B -- explicit join
ON A.somecolumn = B.somecolumn
SET B.bbcode_uid=A.bbcode_uid
, B.post_subject=A.post_subject
, B.post_text=A.post_text
WHERE B.post_id<=218727

Unix Power

2010-08-10T22:45:00.001-07:00

Here are 2 important tips.

A) Create a verbatim copy of any directory, including symbolic links, with tar:

tar cf - /path/to/original | \
(mkdir -p /path/to/copy; cd /path/to/copy; tar xvf -)

The first tar archives the directory /path/to/original and emits the archive file to stdout; the hyphen (-) used with the create (c) option specifies stdout. The command in parentheses is a subshell: Commands in the subshell don't affect the environment of the current shell. mkdir -p creates the named directory, including any intermediate directories that need to be created; and cd changes to the new directory. The second tar reads an archive from stdin and expands it in place; the hyphen used with the extract (x) option refers to stdin.

B) find all Web pages on your server that reference www.example.com, you can use this command line:

% find / -name '*html' -print \
| xargs grep -l 'www.example.com' \
| less -Opages

xargs consumes the filenames from find and runs grep -l repeatedly to process every file, no matter how many files are named. (grep -l prints the name of the file if a match is found and then stops further matching in that file.) less allows you to page through the results and saves the list in the file named pages. The result is a list of filenames that contain the string "www.example.com".

skip name resolve

2010-08-10T02:33:00.001-07:00

There are 2 ways of granting access to the database. One is allow an IP address to log-in or a host name to access the database.

grant all on *.* to 'redhat'@'10.0.2.143' identified by 'SecRet';
grant all on *.* to 'redhat'@'mysite.net' identified by 'SecRet';

The first method mentioned above is very popular and it's versatile as well. For e.g. I can use wildcard 192.168.% to allow access from all the machines of the network. The second method involves a lookup of hostname and this extra step takes time. There are times when the DNS server is down and mysql goes down as well for no fault of itself. The people can not figure out the reason.

1) Use only IP "numbers" in the grant statement. Do not use hostname "text".
2) use skip-name-resolve line in the my.cnf so that mysql will allow quick connections.

I have experienced as much as 15% improvement in overall performance and less frequent downtime.

vi tips and tricks

2010-08-09T21:16:00.000-07:00

Numbering
:set number
:set nonumber

Indent
:set autoindent
:set noautoindent

Case Sensitivity
:set ignorecase
:set noignorecase
If you want to ignore case sensitivity on searches every time you enter a vi session, you can add the line set ignorecase to the .exrc file in your home directory.

Search
/echo
to seach for the word echo
/echo.*file
to search for the word echo followed by the word file

Bookmarks
You can tell vi to place a bookmark at a point in a file by pressing the Escape key followed by the M key followed by another alphabetic character that denotes the bookmark reference. Therefore, you have up to 26 bookmarks named a to z. To return to the previous bookmark, press the Escape key followed by the back tick (`) followed by the bookmark reference alphabetic character.

Execute UNIX command
:!pwd

Filtering
:1,$!sort
# pass lines 1 through the end of the file ($) into the sort command
:1,$!awk '{print $1}' | sort | tr [:lower:] [:upper:]
4!!awk '{print "New text",$0}'

Section save
:6,9w >> /tmp/newfile
You can use the $ notation for the last line to specify to the end of the file

// Delete DOS Characters via VIM (^M)
:set ff=unix
:set ff=dos

Altering huge table

2010-08-08T23:27:00.000-07:00

CREATE TABLE `testhack` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unq` (`unq`)
) ENGINE=MyISAM

I need to drop the unique key. So, i create a new table testhack_new with the following schema

CREATE TABLE `testhack_new` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

mysql> Flush tables with read lock;

Open another terminal. And go to the mysql/data/ directory. Do the following:

$ mv testhack.frm testhack_old.frm; mv testhack_new.frm testhack.frm; mv testhack_old.frm testhack_new.frm;

$ mv testhack.MYI testhack_old.MYI; mv testhack_new.MYI testhack.MYI; mv testhack_old.MYI testhack_new.MYI;

So, what is happening here is that the index, table definitions are being switched. After this process, the table definition of testhack will not contain the unique key. Now unlock the tables in the main window. And run repair tables to remove any issues.

mysql> unlock tables;
mysql> repair tables testhack;

http://jayant7k.blogspot.com/2010/06/mysql-hack-altering-huge-tables.html

restore DB script loadme.sh

2010-08-05T04:17:00.000-07:00

If you need to restore database from the dump back files, here is a easy way to do so. You simply have to supply the database name and the SQL file name to this script and it will load the data on the localhost. If you want to restore data on a remote server don't forget to change the $myhost variable.

# cat loadme.sh
#!/bin/sh
# the first parameter is the name of the database and
# second parameter is the name of the sql file
# you call the script using the following syntax
# sh loadme.sh shantanu aug03_final.sql
# if the .sql file is not in the current directory, supply the complete path

myhost='localhost'
myuser='root'
mypassword=''

# Check if the DB and file name is supplied
# if missing, exit with the code 199

missing=199

if [ -z $1 ]
then
echo "DB and file name both are required"
exit $missing
fi

if [ -z $2 ]
then
echo "DB and file name both are required"
exit $missing
fi

# Create output file for results

mydate=`date +'%d_%h_%Y_V-%H%M%S'`
output="$1_$mydate".txt
> $output

if [[ $mypassword ]];then
mysql -h$myhost -u$myuser -p$mypassword -e"drop database if exists $1"
mysqladmin -h$myhost -u$myuser -p$mypassword create $1
time mysql -h$myhost -u$myuser -p$mypassword $1 < "$2"
mysqlshow -h$myhost -u$myuser -p$mypassword $1 --count > $output
else
mysql -h$myhost -u$myuser -e"drop database if exists $1"
mysqladmin -h$myhost -u$myuser create $1
time mysql -h$myhost -u$myuser $1 < "$2"
mysqlshow -h$myhost -u$myuser $1 --count > $output
fi

Extracting queries from binary files

2010-08-02T05:56:00.001-07:00

There are times when you need to check the actual queries being executed. The best way is to open up the binary file and look into it.
Here is the script that will do it for you.

sh extract.sh tbl_name

You have to supply the table name and call the shell script. -xv will enable the debug mode so that you can check if it running fine.

# cat extract.sh
#!/bin/sh
# the my.cnf path should have the filename initials like
# log-bin=/mysql-bin-log/db1-bin
# find the binary path
mypath=`grep log-bin /etc/my.cnf | awk -F'=' '{print $2}'`*.*

# extract the latest binary file to a text file
mysqlbinlog `ls $mypath -t | head -1` > /home/bintext.txt

# select the table name that is supplied as an argument
grep -C2 "$1" /home/bintext.txt | tail -500 > /home/tosend.txt
# save the last 500 lines to a text file

_____

** analyse binary files **
You will get to see the insert/ update statements along with its count.
You can supply the binary file name. The latest file is used as default.

sh /bin/mylog
OR
sh /bin/mylog/ db1-bin.000223

# cat /bin/mylog
#!/bin/sh

if [[ $1 ]];then

mysqlbinlog ${1} | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

echo "table list from binary log ${1}"
mysqlbinlog ${1} | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | replace 'insert into ' '' | replace 'delete from ' '' | replace 'update ' '' | awk '{print $1}' | sort | uniq -c | sort -nr

else

mypath=`grep log-bin /etc/my.cnf | awk -F'=' '{print $2}'`*.*
latest=`ls $mypath -t | head -1`

mysqlbinlog ${latest} | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr | head -50

echo "table list from binary log ${1}"
mysqlbinlog ${latest} | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | replace 'insert into ' '' | replace 'delete from ' '' | replace 'update ' '' | awk '{print $1}' | sort | uniq -c | sort -nr | head -30

fi

// If you are using row based binary logging, you need to decode the data
mysqlbinlog --base64-output=decode-rows --verbose /var/lib/mysql/server-bin.000022

Analyse Corrupt Tables

2010-08-02T00:44:00.000-07:00

If you are using myisam only table types then you must have come across tables those are being corrupt without any known reason. The following command shows that the tables (from several databases) those have crashed today.

# tail -10000 /mysql-db-path/db1.err | grep `date "+%y%m%d"` | awk -F'ERROR' '{print $2}' | sort -u | awk -F"'" '{print $2}'
./db1/mail_me
./db2/mail_me
./db3/customers

In order to know why does only one table crashed and not any other table, we need to take a closer look at the table structure. The following comand will find the tables those are corrupt or going to be corrupted soon. You can add -r option to myisamchk to repair the tables on the fly.
# find `grep 'datadir' /etc/my.cnf | awk -F"=" '{print $2}'` -not -name '#*.MYI' -name '*.MYI' -exec myisamchk -sF {} \;

The options are:

* -s, --silent option: Prints only errors. You can use two -s to make myisamchk very silent.
* -f, --force option: Restart myisamchk automatically with repair option -r, if there are any errors in the table.
* -F, --fast option: Check only tables that haven?t been closed properly.
* -U --update-state option: Marks tables as crashed, when it finds any error.
* -r --recover: Can fix almost anything except unique keys that aren't unique.

You will need add -f or -r to recover the corrupt tables. The command mentioned above can be added to the cron so that it will run every day and repair the corrupt tables automatically.

using Functions instead of Joins

2010-07-31T01:56:00.001-07:00

Here is an interesting way to join two tables, using function! I do not know why anyone will need to use this method though.

drop table if exists hindi;
drop table if exists english;
create table hindi(id int, HindiWord varchar(100), primary key(id));
create table english(id int, EnglishWord varchar(100), primary key(id));
insert into hindi values ('1', 'ek'), ('2', 'do'), ('3', 'teen');
insert into english values ('3', 'three'), ('4', 'four'), ('5', 'five');

DROP FUNCTION IF EXISTS getEnglishName;
delimiter ;;
CREATE FUNCTION getEnglishName(EngId INT(15)) RETURNS VARCHAR(300)
BEGIN
DECLARE name VARCHAR(300) DEFAULT '';
select Englishword INTO name from english where id=EngId;
return name;
END;
;;

delimiter ;

mysql> select * from hindi;
+------+-----------+
| id | HindiWord |
+------+-----------+
| 1 | ek |
| 2 | do |
| 3 | teen |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select * from english;
+------+-------------+
| id | EnglishWord |
+------+-------------+
| 3 | three |
| 4 | four |
| 5 | five |
+------+-------------+
3 rows in set (0.00 sec)

The "normal" way of joining tables is using the INNER JOIN syntax as shown below:

mysql> select a.id, a.HindiWord, b.EnglishWord from hindi as a inner join english as b on a.id = b.id;
+------+-----------+-------------+
| id | HindiWord | EnglishWord |
+------+-----------+-------------+
| 3 | teen | three |
+------+-----------+-------------+
1 row in set (0.01 sec)

mysql> select a.id, a.HindiWord, b.EnglishWord from hindi as a left join english as b on a.id = b.id;
+------+-----------+-------------+
| id | HindiWord | EnglishWord |
+------+-----------+-------------+
| 1 | ek | NULL |
| 2 | do | NULL |
| 3 | teen | three |
+------+-----------+-------------+
3 rows in set (0.00 sec)

mysql> select a.id, a.HindiWord, b.EnglishWord from hindi as a left join english as b on a.id = b.id where b.id is null;
+------+-----------+-------------+
| id | HindiWord | EnglishWord |
+------+-----------+-------------+
| 1 | ek | NULL |
| 2 | do | NULL |
+------+-----------+-------------+
2 rows in set (0.00 sec)

_____

You can achive the same results of Inner, Left and Left Outer joins using function. The only difference is that the "NULL" value is not getting generated.

mysql> select a.id, a.HindiWord, getEnglishName(a.id) from hindi as a where getEnglishName(a.id) != '';
+------+-----------+----------------------+
| id | HindiWord | getEnglishName(a.id) |
+------+-----------+----------------------+
| 3 | teen | three |
+------+-----------+----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select a.id, a.HindiWord, getEnglishName(a.id) from hindi as a;
+------+-----------+----------------------+
| id | HindiWord | getEnglishName(a.id) |
+------+-----------+----------------------+
| 1 | ek | |
| 2 | do | |
| 3 | teen | three |
+------+-----------+----------------------+
3 rows in set, 2 warnings (0.01 sec)

mysql> select a.id, a.HindiWord, getEnglishName(a.id) from hindi as a where getEnglishName(a.id) = '';
+------+-----------+----------------------+
| id | HindiWord | getEnglishName(a.id) |
+------+-----------+----------------------+
| 1 | ek | |
| 2 | do | |
+------+-----------+----------------------+
2 rows in set, 4 warnings (0.00 sec)

The query using function will not use indexes and hence the query will be slower than the standard join. But it is interesting to know different ways of joining data. I had a chance to see PHP code that used a loop to take one record from a table and join with the other. and yes, that too was inefficient way of joining tables.

Enable and Disable logging

2010-07-31T01:08:00.001-07:00

There are times when you need to stop logging the general queries as well as slow and binary queries. For e.g. when I am restoring data from a dump file, I do not want all the queries to enter in the log files for obvious reasons.
Here is the simple shell script that will enable and disable the logs as and when required.

$ cat disable_logs.sh
#/bin/sh
# disable logs by adding the comment
# backup the current my.cnf first
cp /etc/my.cnf /home/my_$(date +'%d-%m-%y-%H-%M').cnf
sed -i "s|^log-slow-queries=.*$|#\\0|" /etc/my.cnf
sed -i "s|^log-bin=.*$|#\\0|" /etc/my.cnf
sed -i "s|^log=.*$|#\\0|" /etc/my.cnf

$ cat enable_logs.sh
#/bin/sh
# enable logs by removing the comment
# backup the current my.cnf first
cp /etc/my.cnf /home/my_$(date +'%d-%m-%y-%H-%M').cnf
sed -i "s/^#log-slow-queries=/log-slow-queries=/" /etc/my.cnf
sed -i "s/^#log-bin=/log-bin=/" /etc/my.cnf
sed -i "s/^#log=/log=/" /etc/my.cnf

Restart the MySQL service after running this script.

Primary requirement of a database

2010-07-28T22:56:00.000-07:00

Every table in your database should have a primary key. A table without the primary key is like an orphan child without the support of parents or a youth without a spouse. You never know what you are missing!

1) You can easily check if the table has a primary key or not.
show create table tblName;

2) Once you have found that there is no primary key, you must check if a column or a group of columns can be declared as a primary key.
ALTER TABLE `tblName` ADD PRIMARY KEY ( `myID` , `yourID` , `type` )

3) If there is no such candidate column or a group of columns that is Unique and not null, you can simply add an auto-incremented column as shown below.
ALTER TABLE `tblName` add column autoID int not null auto_increment, add primary key (autoID)

4) If you want to remove the newly added column autoID, you can simply use the following statement.
ALTER TABLE `tblName` drop primary key, drop column autoID

This simple task can dramatically improve the performance and will make it compliant without affecting the application in any way.

Use the following shell script to find out the candidate column that can be declared as Primary.

# cat primary.sh

#!/bin/sh
# the script to find out the tables those do not have a primary key
# It will show the max and min values stored in each column along with the create table output
# change the dbname to the database name that you want to analyse
dbname='shantanu'
> /home/develop/tosave.txt

while read mytable
do
mysql shantanu -Bse"show create table $mytable\G" >> /home/develop/tosave.txt
mysql shantanu -Bse"select * from $mytable procedure analyse()\G" >> /home/develop/tosave.txt

done << heredoc
`mysql -Bse"select t.table_name as tbl from INFORMATION_SCHEMA.TABLES AS t LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema='$dbname' AND constraint_name IS NULL;"`
heredoc

_____

== need for Primary Key ==

It is interesting to know what happens if we do not have a primary key to uniquely differnetiate the rows.
Here is an example table.

mysql> select * from hari;
+------+------------+-------+
| id | mydate | email |
+------+------------+-------+
| 1 | 2009-10-10 | 1111 |
| 1 | 2009-10-10 | 1111 |
| 1 | 2009-10-10 | 2222 |
| 2 | 2010-11-11 | 3333 |
| 2 | 2010-11-11 | 3333 |
+------+------------+-------+
5 rows in set (0.00 sec)

The problem with the above table is that the first 2 and last 2 rows are actually duplicate and there is no way to identify them since there is no unique or primary key used in the table.
Here is the SQL code that you can run to create this test table.

drop table if exists hari;
CREATE TABLE `hari` (
`id` int(11) default NULL,
`mydate` date default NULL,
`email` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `hari` VALUES (1,'2009-10-10','1111'),(1,'2009-10-10','1111'),(1,'2009-10-10','2222'),(2,'2010-11-11','3333'),(2,'2010-11-11','3333');

In order to get the count of the unique ID's we need the following query.

SELECT
count(sel2.refid2 ) as recCount,
refid2, recDate2
FROM
(SELECT id AS refid2, mydate AS recDate2
FROM hari AS ol_Email1
GROUP BY ol_Email1.id, ol_Email1.mydate, ol_Email1.email
) AS sel2
GROUP BY sel2.refid2;

+----------+--------+------------+
| recCount | refid2 | recDate2 |
+----------+--------+------------+
| 2 | 1 | 2009-10-10 |
| 1 | 2 | 2010-11-11 |
+----------+--------+------------+
2 rows in set (0.00 sec)

The query shown above is ugly, confusing and slow since the indexes will not be used properly. So I add a primary key to the table.

ALTER TABLE hari ADD COLUMN pid INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (pid);

I get the same results as shown above, but with much cleaner query that is also using the primary key.

select count(*) as ercCount, a.id as refid2, a.mydate as recDate2 from hari as a inner join
(SELECT pid, id, mydate FROM hari GROUP BY id, mydate, email) as dt
on a.pid = dt.pid group by a.id;

+----------+--------+------------+
| ercCount | refid2 | recDate2 |
+----------+--------+------------+
| 2 | 1 | 2009-10-10 |
| 1 | 2 | 2010-11-11 |
+----------+--------+------------+
2 rows in set (0.00 sec)

MySQL Schema Versioning and Migration Utility

2010-07-02T09:43:00.000-07:00

http://schemasync.org/

Schema Sync will generate the SQL necessary to migrate the schema of a source database to a target database (patch script), as well as a the SQL necessary to undo the changes after you apply them (revert script).

_____

MySQLdiff is another little Tool to detect layout differences between two databases.

http://www.mysqldiff.org

Almost every developer knows that changes of the database on the development system have to be logged in order to update the live-system later on.
To make this easier the tool MySQLdiff has been developed. MySQLdiff will create a SQL-ALTER-Script which has to be run onto the live-system to 'patch' it to the state of the development system.

Error reporting page

2010-06-14T01:33:00.000-07:00

With the Apache webserver it’s possible to use custom directives for your error script, place this code into your .htaccess file (place the file into the site root):

ErrorDocument 400 /error.php?err=400
ErrorDocument 401 /error.php?err=401
ErrorDocument 403 /error.php?err=403
ErrorDocument 404 /error.php?err=404
ErrorDocument 500 /error.php?err=500

We use for the custom error script the most common HTTP errors.

The error.php file will display the custom error message based on the error number. It will also log the IP and referrer to a text file.

http://docs.google.com/View?id=dddjxz53_477dwhdwqck