code

Stored procedure to Find database objects

This procedure lists available database objects under passed database name. It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database. It also lists storage engine of tables. It uses information schema database to gather information and storing in a temporary table. Usage: call xplore(database-name); – Procedure will search through information schema for database objects under database-name. Download Stored Procedure: explore-database DELIMITER $$ DROP PROCEDURE IF EXISTS `xplore` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `xplore`(IN_DB varchar(100)) BEGIN DECLARE DB VARCHAR(100); DECLARE NO_TABLES INT(10); DECLARE NO_VIEWS INT(10); DECLARE NO_FUNCTIONS INT(10); DECLARE NO_PROCEDURES INT(10); DECLARE NO_TRIGGERS INT(10); DECLARE SUMMARY VARCHAR(200); SET DB=IN_DB;...

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: And we want output as follows: 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 null 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...

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...

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...

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...