05 Dec

MySQL Stored procedure – Split Delimited string into Rows

This procedure will split  a “;” separated column in to new fields preserving ids.

This is very specific problem, lets check it with example.

Consider a sample table test:

source-table

And we want output as follows:

output-table

So again I wrote a procedure.

Procedure will read a data from “tmp” table and will split data by my_delimiter delimiter and generate a temporary table.

You may alter it as per requirement.

Download Stored Procedure : split_string

DELIMITER $$


DROP PROCEDURE IF EXISTS `split_string` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `split_string`()
BEGIN

DECLARE my_delimiter CHAR(1);
DECLARE split_string varchar(255);
DECLARE done INT;
DECLARE occurance INT;
DECLARE i INT;
DECLARE split_id INT;
DECLARE ins_query VARCHAR(500);
DECLARE splitter_cur CURSOR FOR
SELECT id,cat from tmp;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

DROP TEMPORARY TABLE IF EXISTS `my_splits`;
CREATE TEMPORARY TABLE `my_splits` (
`splitted_column` varchar(45) NOT NULL,
`id` int(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

OPEN splitter_cur;
splitter_loop:LOOP
FETCH splitter_cur INTO split_id,split_string;

SET my_delimiter=’;’;
SET occurance=length(split_string)-length(replace(split_string,my_delimiter,”))+1;
IF done=1 THEN
LEAVE splitter_loop;
END IF;
#  select occurance;
IF occurance > 0 then
#select occurance;
set i=1;
while i <= occurance do
#        select concat(“SUBSTRING_INDEX(SUBSTRING_INDEX( ‘”,split_string ,”‘, ‘”,my_delimiter,”‘, “,i, “),'”,my_delimiter,”‘,-1);”);
SET ins_query=concat(“insert into my_splits(splitted_column,id) values(“, concat(“SUBSTRING_INDEX(SUBSTRING_INDEX( ‘”,split_string ,”‘, ‘”,my_delimiter,”‘, “,i, “),'”,my_delimiter,”‘,-1),”,split_id,”);”));
#    select ins_query;
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;
set i=i+1;
end while;
ELSE
set ins_query=concat(“insert into my_splits(splitted_column,id) values(“,split_string,”‘,”,split_id,”);”);
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;
END IF;
set occurance=0;
END LOOP;

CLOSE splitter_cur;

END $$

DELIMITER ;

Usage: call split_string();

05 Dec

MySQL Stored procedure – Execute query if table or Column exists

Well procedures mainly carried out working with information schema and it’s usage in stored procedure. Procedures are fairly simple and easy to understand.

1. Edit_table – following procedure executes queries to particular table if it exists.

Basically I created it to satisfy a need of altering a table if column exists.

Now it can be used to execute any query if table exists.

Usage: call Edit_table(database-name,table-name,query-string);

– Procedure will check for existence of table-name under database-name and will execute query-string if it exists.

Download Stored Procedure: Edit_table

DELIMITER $$

DROP PROCEDURE IF EXISTS `Edit_table` $$
CREATE PROCEDURE `Edit_table` (in_db_nm varchar(20),in_tbl_nm varchar(20),in_your_query varchar(200))
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;

select count(*) INTO @var_table_count from information_schema.TABLES where  TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm;
IF (@var_table_count > 0) THEN
SET @in_your_query = in_your_query;
#SELECT @in_your_query;
PREPARE my_query FROM @in_your_query;
EXECUTE my_query;

ELSE
select “Table Not Found”;
END IF;

END $$
DELIMITER ;

1A. A slight variation of this procedure is editing a table if column exists.

Download Stored Procedure: Edit_table_column

DELIMITER $$

DROP PROCEDURE IF EXISTS `Edit_table_column` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_table_column`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20),in_your_query varchar(200))
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;

select count(*) INTO @var_table_count from information_schema.COLUMNS where  TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm and COLUMN_NAME=in_colm_nm;
IF (@var_table_count > 0) THEN
SET @in_your_query = in_your_query;
SELECT @in_your_query;
PREPARE my_query FROM @in_your_query;
EXECUTE my_query;

ELSE
select “Table Not Found”;
END IF;

END $$
DELIMITER ;

Usage: call Edit_table_column(database-name,table-name,column-name,query-string);

04 Dec

How to echo colored text in linux shell script

Having a colourful display on shell script is something that’d beautify your experience. Using colour text and echos will help you to highlight and distinguish the shell output on a linux prompt.

Here’s how you can have colored text in linux shell, try following command on your bash:

bash $] tput setaf 1
You will see your text color will turn red.
To reset it you can type following command to make text color white
bash $] tput setaf 7

or to reset everything again text modes
bash $] tput sgr0

Same things you can use in your bash script to colorize output.

Create following sample bash script for colourize echo:

#try.sh
txtrst=$(tput sgr0) # Text reset
txtred=$(tput setaf 1) # Red
echo “Welcome to ${txtred} kedar.nitty-witty.com ${txtrst}!”

The coloured text output is as follows::

colored-shell-script

If you need more colors, you may define them as follows in shell script:

Other variables you can define as follows:
txtgrn=$(tput setaf 2) # Green
txtylw=$(tput setaf 3) # Yellow
txtblu=$(tput setaf 4) # Blue
txtpur=$(tput setaf 5) # Purple
txtcyn=$(tput setaf 6) # Cyan
txtwht=$(tput setaf 7) # White
txtrst=$(tput sgr0) # Text reset.

Following are the tput details further:
tput setab [1-7] : Set a background colour using ANSI escape
tput setb [1-7] : Set a background colour
tput setaf [1-7] : Set a foreground colour using ANSI escape
tput setf [1-7] : Set a foreground colour

tput Text Mode Capabilities:

tput bold : Set bold mode
tput dim : turn on half-bright mode
tput smul : begin underline mode
tput rmul : exit underline mode
tput rev : Turn on reverse mode
tput smso : Enter standout mode (bold on rxvt)
tput rmso : Exit standout mode
tput sgr0 : Turn off all attributes (doesn’t work quite as expected)

I hope you liked this. Happy & colourful shell scripting.  🙂

04 Dec

MySQL master master replication monitor with php code

For monitoring replication we know a lot of tools and codes – but this one is different because I wrote it 😉
Well this is fairly simple php code for monitoring a master master replication setup.

It requires a single shared login id available on both MySQL servers.

It will display in tabular format following details:

  • File: Present binary log
  • Position: Binary log position
  • Io run: Slave IO Thread Running status
  • Sql run: SQL Thread Running status
  • ErrorNum: Error number
  • ErrorMeg: Error message

You can easily stop / start each server’s slave (STOP SLAVE / START SLAVE) with a single click.
It also does provide detailed information of global status variables on same page.
It is auto refreshes by default every 2 seconds to provide updated status of both MySQL Servers in Replication.

In code you just need to adjust following parameters as per requirement:

#host-name & ports for replication servers
$slave = “localhost”;
$master = “localhost”;
$slaveport=3306;
$masterport=3307;


#Refresh rate
$refreshRate=2;


#considered a common username password for accessing both servers#
$username=”root”;
$password=”kedar”;

You can download code here: mysql-replication-monitor.php

MySQL Replication Monitor Using PHP:

mysql-replication-monitor-php

-- Kedar Vaijanapurkar --