Tag Archives: MySQL

06 Dec

Escaping “Lesson Learned” dealing with MySQL Databases & Case Sensitiviy

Do you ever need to transfer databases between servers? Different OSes?

Two common ways of data migration:
1. Create mysqldump and load it.
2. File transfer using SCP.

Case Sensitivity and Issue:
Yes, that can create big issues when you have to deal with systems having different case sensitivity.
E.g. on linux you can create directories with names “Kedar” or “kedar” which is not possible on windows!
It will be harmful when file-system itself restricting the names to lower case.
MySQL has a variable for that: lower_case_file_system.

Documentation says: This variable describes the case sensitivity of file names on the file system where the data directory is located. OFF means file names are case sensitive, ON means they are not case sensitive. This variable is read only because it reflects a file system attribute and setting it would have no effect on the file system.

Try loading data having different values for lower_case_file_system and you will get it!

How MySQL tries to solves this problem:
It asks user to handle the issue by changing system variable lower_case_table_names accordingly.
Check the options available:
0 : Table names are stored as specified and comparisons are case sensitive.
1 : Table names are stored in lowercase on disk and comparisons are not case sensitive.
2 : Table names are stored as given but compared in lowercase.

So where do I see the problem?
Here control is given to user with the variable lower_case_table_names and ignoring which can definitely lead to issues. These issues can be avoided by putting restrictions.
Also queries working on MAC / Windows may not work well on Linux / Other Flavours; cause problems on application side.

Documentation Says: You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result.

Now, I recently read the change: PERFORMANCE_SCHEMA is renamed to lowercase in MySQL 5.5 to answer a bug [http://bugs.mysql.com/bug.php?id=57609] pointed by Marc Alff’s post [http://marcalff.blogspot.com/].

In one line I wish: lower_case_table_names, RIP!
I wish MySQL does the same for all tables in order to avoid case sensitivity problems :).

I feel MySQL should not allow upper cases. What do you think?

30 Nov

Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL

Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround.

Download MySQL UDF:

[root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz

[refer: http://www.mysqludf.org/]

Extract and Install:

[root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz
install.sh
lib_mysqludf_sys.c
lib_mysqludf_sys.html
lib_mysqludf_sys.so
lib_mysqludf_sys.sql
Makefile

[root@localhost kedar]# sh install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
MySQL UDF compiled successfully

Please provide your MySQL root password
Enter password:
MySQL UDF installed successfully

Create function sys_exec as follows:

CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so';

sys_exec – executes an arbitrary command, and returns it’s exit code.

You also can similarly create functions:
sys_eval – executes an arbitrary command, and returns it’s output.
sys_get – gets the value of an environment variable.
sys_set – create an environment variable, or update the value of an existing environment variable.

Example – How to load txt file to MySQL using Stored Procedure & Load Data syntax:

Step-1. Creating table:

CREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM

Step-2. Create a sample file to load:

vi loadtest.txt
1
2
3

Step-3. Create a shell script:

vi /tmp/load.sh
mysql -u mysql_user -p mysql_password -e “load data local infile \”$1\” into table $2;”

Step-4. Create a Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `load_data_SP` $$
CREATE PROCEDURE `load_data_SP` (in_filepath varchar(100),in_db_table varchar(100))
BEGIN
declare exec_str varchar(500);
declare ret_val int;
set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
set ret_val=sys_exec(exec_str);
if ret_val=0 then
select “Success” as Result;
else
select “Please check file permissions and paths” as Result;
end if;
END $$
DELIMITER ;

Step 5. Execute:

CALL `load_data_SP`(‘/tmp/loadtest.txt’ , ‘test.t’);

…and that’s it Stored Procedure will return Success or Failure accordingly.

Make sure you’re having file permissions well set and MySQL can access the files.
Here I’ve kept the files under /tmp directory with chmod 777 & chown mysql:mysql to remove the permission-issue possibility.

Hope this helps.

18 Nov

10 Steps: MySQL Monitoring through Nagios: Install & Configure

Nagios is a powerful monitoring system and here we will learn how to monitor MySQL through Nagios. We will be installing Nagios, required plugins and configuring it to monitor MySQL Database Server.

Let’s unleash the power step by step:

Installing and configuring Nagios

Step-1 : Install required stuff:
yum install httpd
yum install gcc
yum install glibc*
yum install gd*

Step-2 :Create Nagios user account and group
useradd nagios
passwd nagios
groupadd nagcmd
usermod -G nagcmd nagios
usermod -G nagcmd apache

Step-3: Downloads:
Create directory:
mkdir NagiosSetup
cd NagiosSetup

Download nagios
wget -X Get "http://sourceforge.net/projects/nagios/files/nagios-3.x/nagios-3.2.1/nagios-3.2.1.tar.gz/download"

Download Nagios Plugins:
wget -X Get "http://sourceforge.net/projects/nagiosplug/files/nagiosplug/1.4.15/nagios-plugins-1.4.15.tar.gz/download"

Step-4: Install Nagios

tar -xzvf nagios-3.2.1.tar.gz
cd nagios-3.2.1
./configure --with-command-group=nagcmd
make all
make install
make install-config
make install-commandmode
make install-init
chkconfig --add nagios

[If you miss step “make install-init” you may get:: error reading information on service nagios: No such file or directory ]

Configure Nagios Web Interface:
make install-webconf
htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin

[specify password for nagios admin]

Step-5: Install plugins
tar xvf nagios-plugins-1.4.11.tar.gz
cd nagios-plugins-1.4.11
./configure --with-nagios-user=nagios --with-nagios-group=nagios
make
make install

Step-6: Verify Installation, Starting nagios for the first time

service nagios start
Browse: http://localhost/nagios

Here if you get Error:
“You don’t have permission to access /nagios/ on this server.”

Check /etc/httpd/conf/httpd.conf for DirectoryIndex.
If it’s not having index.php add it as follows:

vi /etc/httpd/conf/httpd.conf
DirectoryIndex index.php index.html index.html.var

Make sure you do restart apache(httpd) and nagios every time you change the config file. You must have php installed.

Monitoring MySQL:

Step-7: Download, Extract and install the MySQL Plugin:

wget http://labs.consol.de/wp-content/uploads/2010/10/check_mysql_health-2.1.3.tar.gz

tar -zxvf check_mysql_health-2.1.3.tar.gz
cd check_mysql_health-2.1.3
./configure --prefix=/usr/local/nagios --with-nagios-user=nagios --with-nagios-group=nagios --with-perl=/usr/bin/perl
make
make install

Step-8: Create database user:
grant usage, replication client on *.* to 'nagios'@'localhost' identified by 'nagios';

Step-9: Provide email address for nagiosadmin:
[Change contacts.cfg file accordingly.]
vi /usr/local/nagios/etc/objects/contacts.cfg

define contact{
contact_name nagiosadmin ; Short name of user
use generic-contact ; Inherit default values from generic-contact template (defined above)
alias Kedar ; Full name of user
email kedar@nitty-witty.com ; <<***** CHANGE THIS TO YOUR EMAIL ADDRESS ******
}

Step-10: Configuring Nagios to Monitor MySQL Server

vi /usr/local/nagios/etc/nagios.cfg
add following line:
cfg_file=/usr/local/nagios/etc/objects/mysqlmonitoring.cfg

Define check_mysql_health command as follows:

vi /usr/local/nagios/etc/objects/commands.cfg

define command{
command_name check_mysql_health
command_line $USER1$/check_mysql_health -H $ARG4$ --username $ARG1$ --password $ARG2$ --port $ARG5$ --mode $ARG3$
}

Enter services to be monitored in mysqlmonitoring.cfg:

vi /usr/local/nagios/etc/objects/mysqlmonitoring.cfg
Add:

define service{
use local-service
host_name localhost
service_description MySQL connection-time
check_command check_mysql_health!nagios!nagios!connection-time!127.0.0.1!3306!
}


define service{
use local-service
host_name localhost
service_description MySQL slave-io-running
check_command check_mysql_health!nagios!nagios!slave-io-running!127.0.0.1!3306!
}


define service{
use local-service
host_name localhost
service_description MySQL slave-sql-running
check_command check_mysql_health!nagios!nagios!slave-sql-running!127.0.0.1!3306!
}

Here we’ve monitored 3 services: Connection-time, io thread and sql thread (replication) status. You can monitor more parameters described here: http://labs.consol.de/nagios/check_mysql_health/

Note: Every time you change configuration file, verify before starting nagios using command:
/usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg

Finally start nagios service and you’re done with nagios installation and configuration for monitoring MySQL.

Download PDF

I hope you’ve found this useful.

28 Oct

Installing MySQL On Mac OS X (Darwin Kernel)

Recently I happen to install MySQL on Mac OS X (Darvin Kernel).  Below are the quick 5 steps to accomplish the task.

Step 1: Check Mac Version
Very first step is to verify the Mac OS X’s current version to decide MySQL Installation file to be downloaded.
For example for Mac version: 10.4 you should download “Mac OS X 10.4 (x86, 32-bit), Compressed TAR Archive (mysql-5.1.51-osx10.4-i686.tar.gz)”

How to Check Mac Version:
cat /System/Library/CoreServices/SystemVersion.plist

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN"

"http://www.apple.com/DTDs/PropertyList-1.0.dtd">

</p> <plist version="1.0"> <dict>

<key>ProductBuildVersion</key>  <string>8S2169</string>

<key>ProductCopyright</key><string>1983-2007 Apple Inc.</string>

<key>ProductName</key> <string>Mac OS X</string>

<key>ProductUserVisibleVersion</key> <string>10.4.11</string>

<key>ProductVersion</key> <string>10.4.11</string> </dict>

Installation Error:

dyld: unknown required load command 0x80000022

I ran in to problems while I installed MySQL for Mac OS X 10.5. Ofcourse I missed the step one and paid for it.
It’s about MySQL binary’s version difference errors: “dyld: unknown required load command 0x80000022″.

Step 2: Download MySQL Installation (tar.gz) file from dev.mysql’s download section.
For example go to: http://dev.mysql.com/downloads/mysql/5.1.html

Select appropriate OS & version and get the exact download link from mirror:

cd /usr/local/
wget http://dev.mysql.com/get/Downloads/
MySQL-5.1/mysql-5.1.51-osx10.4-i686.tar.gz/from/http://mysql.mirror.rafal.ca/

Step 3: Create MySQL User and Group.

Through command line we can use NetInfo Utility as:
niutil -create / /groups/mysql
niutil -createprop / /groups/mysql gid 401
niutil -create / /users/mysql
niutil -createprop / /users/mysql gid 401
niutil -createprop / /users/mysql uid 401

Assign password to “mysql” user
passwd mysql

Step: 4 Installing MySQL using Generic Binaries
cd /usr/local
tar -xzvf mysql-VERSION-OS.tar.gz
ln -s full-path-to-mysql-VERSION-OS mysql
cd mysql
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data
cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf

Step: 5 Start MySQL Server:

bin/mysqld_safe –user=mysql &

This finishes basic installation of MySQL on Mac OS X. Make sure you follow “best practices” or “after installation steps” as recommended.

Recently I happen to install MySQL on Mac OS X (Darvin Kernel). Below are the quick 5 steps to accomplish the task.

<strong>Step 1:  Check Mac Version</strong>
Very first step is to verify the Mac OS X’s current version to decide MySQL Installation file to be downloaded.
For example for Mac version: 10.4 you should download “Mac OS X 10.4 (x86, 32-bit), Compressed TAR Archive (mysql-5.1.51-osx10.4-i686.tar.gz)”

How to Check Mac Version:
<code>cat /System/Library/CoreServices/SystemVersion.plist
<xmp>
<?xml version=”1.0″ encoding=”UTF-8″?>
<!DOCTYPE plist PUBLIC “-//Apple Computer//DTD PLIST 1.0//EN” “http://www.apple.com/DTDs/PropertyList-1.0.dtd”>
<plist version=”1.0″>
<dict>
<key>ProductBuildVersion</key>
<string>8S2169</string>
<key>ProductCopyright</key>
<string>1983-2007 Apple Inc.</string>
<key>ProductName</key>
<string>Mac OS X</string>
<key>ProductUserVisibleVersion</key>
<string>10.4.11</string>
<key>ProductVersion</key>
<string>10.4.11</string>
</dict></code>
</xmp>

Installation Error: <h2>dyld: unknown required load command 0x80000022<h2>
I ran in to problems while I installed MySQL for Mac OS X 10.5. Ofcourse I missed the step one and paid for it.
It’s about MySQL binary’s version difference errors: “dyld: unknown required load command 0x80000022″.

<strong>Step 2: Download MySQL Installation (tar.gz) file from dev.mysql’s download section.</strong>
For example go to: http://dev.mysql.com/downloads/mysql/5.1.html

Select appropriate OS & version and get the exact download link from mirror:
<code>
cd /usr/local/
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.51-osx10.4-i686.tar.gz/from/http://mysql.mirror.rafal.ca/</code>

<strong>Step 3: Create MySQL User and Group.</strong>

Through command line we can use NetInfo Utility as:
<code>niutil -create / /groups/mysql
niutil -createprop / /groups/mysql gid 401
niutil -create / /users/mysql
niutil -createprop / /users/mysql gid 401
niutil -createprop / /users/mysql uid 401</code>

Assign password to “mysql” user
passwd mysql

<strong>Step: 4   Installing MySQL using Generic Binaries</strong>
<code>cd /usr/local
tar -xzvf mysql-VERSION-OS.tar.gz
ln -s full-path-to-mysql-VERSION-OS mysql
cd mysql
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db –user=mysql
chown -R root .
chown -R mysql data
cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf</code>

<strong>Step: 5     Start MySQL Server:</strong>

bin/mysqld_safe –user=mysql &

This finishes basic installation of MySQL on Mac OS X. Make sure you follow “best practices” or “after installation steps” as recommended.

16 Sep

MySQL Function to Convert Date To Words

Recently I saw a MySQL Stored Function requirement on Experts-Exchange for converting date into some specific words format.
You may find MySQL function for date to words conversion online; even udfs might be ready, but I decided to write my own.
I wrote this simple function mainly based on SELECT CASE to convert dates in to words as follows:

mysql>SELECT date_to_words(‘2010-05-08′);
Eighth Day of May Two Thousand Ten

Download sql file below the code.

DELIMITER $$

DROP FUNCTION IF EXISTS `date_to_words` $$
CREATE FUNCTION `date_to_words` (mydate DATE) RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN

/* Converts date into words */

DECLARE yr INT;
DECLARE dateval INT;
DECLARE thousand INT;
DECLARE hundred INT;
DECLARE tens INT;

DECLARE tensword VARCHAR(10);
DECLARE onesword VARCHAR(10);
DECLARE thousandsword VARCHAR(20);
DECLARE hundredsword VARCHAR(20);
DECLARE datevalsword VARCHAR(20);

SET yr=year(mydate);
SET dateval=day(mydate);

/* DAY TO WORDS */

SELECT CASE dateval
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
WHEN 4 THEN ‘Fourth’
WHEN 5 THEN ‘Fifth’
WHEN 6 THEN ‘Sixth’
WHEN 7 THEN ‘Seventh’
WHEN 8 THEN ‘Eighth’
WHEN 9 THEN ‘Ninth’
WHEN 10 THEN ‘Tenth’
WHEN 11 THEN ‘Eleventh’
WHEN 12 THEN ‘Twelfth’
WHEN 13 THEN ‘Thirteenth’
WHEN 14 THEN ‘Fourteenth’
WHEN 15 THEN ‘Fifteenth’
WHEN 16 THEN ‘Sixteenth’
WHEN 17 THEN ‘Seventeenth’
WHEN 18 THEN ‘Eighteenth’
WHEN 19 THEN ‘Nineteenth’
WHEN 20 THEN ‘Twentieth’
WHEN 21 THEN ‘Twenty-first’
WHEN 22 THEN ‘Twenty-second’
WHEN 23 THEN ‘Twenty-third’
WHEN 24 THEN ‘Twenty-fourth’
WHEN 25 THEN ‘Twenty-fifth’
WHEN 26 THEN ‘Twenty-sixth’
WHEN 27 THEN ‘Twenty-seventh’
WHEN 28 THEN ‘Twenty-eighth’
WHEN 29 THEN ‘Twenty-ninth’
WHEN 30 THEN ‘Thirtieth’
WHEN 31 THEN ‘Thirty-first’
END into datevalsword;

/* YEAR TO WORDS */
set thousand=floor(yr/1000) ;
set yr = yr – thousand * 1000;
set hundred = floor(yr / 100);
set yr = yr – hundred * 100;

IF (yr > 19) THEN
set tens = floor(yr / 10);
set yr = yr mod 10;
ELSE
set tens=0;
END IF;

SELECT CASE thousand
WHEN 1 THEN ‘One’
WHEN 2 THEN ‘Two’
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 6 THEN ‘Six’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
END INTO thousandsword;
SET thousandsword=concat(thousandsword,’ Thousand ‘);

SELECT CASE hundred
WHEN 0 then ”
WHEN 1 THEN ‘One’
WHEN 2 THEN ‘Two’
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 6 THEN ‘Six’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
END INTO hundredsword;
if (hundredsword<>”) then
SET hundredsword=concat(hundredsword,’ Hundred ‘) ;
else
set hundredsword=”;
end if;

/*TENS To WORDS*/
SELECT CASE tens
WHEN 2 THEN ‘Twenty’
WHEN 3 THEN ‘Thirty’
WHEN 4 THEN ‘Fourty’
WHEN 5 THEN ‘Fifty’
WHEN 6 THEN ‘Sixty’
WHEN 7 THEN ‘Seventy’
WHEN 8 THEN ‘Eigthy’
WHEN 9 THEN ‘Ninety’
ELSE ”
END INTO tensword;

/*ONES To WORDS*/
SELECT CASE yr
WHEN 0 THEN ”
WHEN 1 THEN ‘One’
WHEN 2 THEN ‘Two’
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 6 THEN ‘Six’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
WHEN 10 THEN ‘Ten’
WHEN 11 THEN ‘Eleven’
WHEN 12 THEN ‘Twelve’
WHEN 13 THEN ‘Thirteen’
WHEN 14 THEN ‘Fourteen’
WHEN 15 THEN ‘Fifteen’
WHEN 16 THEN ‘Sixteen’
WHEN 17 THEN ‘Seventeen’
WHEN 18 THEN ‘Eighteen’
WHEN 19 THEN ‘Nineteen’
END into onesword;

return concat(datevalsword, ‘ Day of ‘, date_format(mydate,’%M’),’ ‘,thousandsword,hundredsword, tensword,’ ‘,onesword);
END $$

DELIMITER ;

Download SQL Code for converting date to words.date_to_words.sql

-- Kedar Vaijanapurkar --