Tag Archives: Technical

07 Jan

crontab not working with dynamic date filename – rhel linux

I have done it for so many times, setting a cronjob.
Today I was tring to set a simple cronjob which will output the log file with timestamp attached filename.

Hourly running cron entry was:

0 * * * * perl demo.pl > demo-out_`date "+%Y-%m-%d_%H-%M"`.log

Running the same command on shell prompt was working fine. Execute permissions and path were proper.Ā But it kept on failing to create the proper log!!

I changed the crontab as follows, added \ before % to escape and voila!!

Things worked !!

0 * * * * perl test.pl > test-out_`date "+\%Y-\%m-\%d_\%H-\%M"`.log

A quick lesson.

Good to note some of the date changers in linux for finding yesterdays and tomorrows and so on:
date –date=”1 days ago”
date –date=”yesterday”
date –date=”next day”
date –date=”-1 day”
date –date=’tomorrow’
date –date=’1 day’
date –date=’10 day’
date –date=’10 week’
date –date=’10 month’
date –date=’10 year’
date –date=”next Friday”
To escape problems you should learn to escape “special characters” – Krex
P.S.:This works in real life as well šŸ˜‰

05 Dec

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;

drop temporary table if exists objects;
create temporary table objects
(
object_type varchar(100),
object_name varchar(100),
object_schema varchar(100)
)
engine=myisam;

INSERT INTO objects
/* query for triggers */
(SELECT ‘TRIGGER’,TRIGGER_NAME ,TRIGGER_SCHEMA
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA like DB)

UNION

/* query for views*/
(SELECT ‘VIEW’, TABLE_NAME,TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type=’VIEW’ and TABLE_SCHEMA like DB)

UNION

/* query for procedure*/
(SELECT ‘PROCEDURE’, SPECIFIC_NAME, ROUTINE_SCHEMA
FROM information_schema.routines
WHERE routine_type=’PROCEDURE’ and ROUTINE_SCHEMA like DB)

UNION

/* query for function*/
(SELECT ‘FUNCTION’, SPECIFIC_NAME, ROUTINE_SCHEMA
FROM information_schema.routines
WHERE routine_type=’FUNCTION’ and ROUTINE_SCHEMA like DB)

UNION

/* query for tables*/
(SELECT concat(ENGINE,’ TABLE’), TABLE_NAME, TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type=’BASE TABLE’ and TABLE_SCHEMA like DB
GROUP BY ENGINE, TABLE_NAME);

/* show gathered information from temporary table */
SELECT object_name,object_type,object_schema
FROM objects;

/* Prepare and show summary */
SELECT object_schema AS `DATABASE`,
SUM(IF(object_type like ‘%TABLE’, 1, 0)) AS ‘TABLES’,
SUM(IF(object_type=’VIEW’, 1, 0)) AS ‘VIEWS’,
SUM(IF(object_type=’TRIGGER’, 1, 0)) AS ‘TRIGGERS’,
SUM(IF(object_type=’FUNCTION’, 1, 0)) AS ‘FUNCTIONS’,
SUM(IF(object_type=’PROCEDURE’, 1, 0)) AS ‘PROCEDURES’
FROM objects
GROUP BY object_schema;

END $$

DELIMITER ;

I have also published this as an article on EE.
http://www.experts-exchange.com/articles/Database/MySQL/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html

I have also published this as an article on EE.

http://www.experts-exchange.com/articles/Database/MySQL/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html

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

24 Nov

Using VLookup like Batch script to compare two excel / csv

Using Vlookup:

I have two csv files; File1 has Id and Value Columns and File2 has Id.

Problem: I need to compare both files and put respective values to File2 from File1.

Solution: VLookup in excel

Steps:

  • Open both files in Excel & arrange it vertically (Window >> Arrange; for ease)

vlookup-1

  • Click on cell where you want to put compared value: B2
  • Click on fx button and select VLOOKUP function.

vlookup-2

  • Under lookup_value, click first search field: A2.
  • Click back in Table_array text field, and click on button to select range. Select two column for comparison starting from second row, i.e. A2 to B9.
  • Put 2 in Col_index_num. If lookup succeeds in finding lookup_value in Table_array it will return 2nd value as result.
  • Put FALSE in Range_lookup to do exact match.
  • Click ok, and you will find B2 will be filled with respective value.
  • You may drag the command to other rows and values will get filled. Optionally you may copy and paste it from rows B3 to B9 to lookup and fill all values.

vlookup-3

  • You can observer in output here, if I change the value of ID in File2 which doesnā€™t exist, VLookup returns #N/A.

Descriptions in details for this function and each respected field are easily available in excel help.

Download Files: File1, File2

Batch script to compare and assign value-simulate vlookup:

Further I came up with a batch script to compare field value, simulate vlookup.Ā After doinglittle testing, I managed to fix it.

for /f “tokens=1 delims=, skip=1” %%i in (File2.csv) do @findstr Ā “%%i,” File1.csv >nul & If errorlevel 0 if not errorlevel 1 (for /f “tokens=1,2 delims=,” %%m in (‘findstr /i /L “%%i,” File1.csv’) do (@echo %%m,%%n>>output.csvĀ echo %%i)) else (echo %%i,NA>>output.csv)

Please consider this batch script is written specifically for these csv and prove my understanding.
This script will compare file2 with file1, extract similar data from file1 and put it in output.csv.

Download batch file: vlookup-batch.bat

If you find any difficulty or don’t yield required results by following above process or using script, comment.

29 Oct

Search / find through all databases, tables, columns in MySQL

What will you do if one day some one ask you to find single string in allĀ databases, all tablesĀ and in allĀ columns, In MySQL Database?

I just read such question and tried to find a “ready made” solution. Reusability is Key Concept šŸ˜‰ !!

But I ended up finding no “copy-paste” material. Some of the posts like http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm helped me out and supported my thinking of “how to do it” !

Here is the idea for how toĀ search through allĀ databasesĀ –Ā tablesĀ –Ā columns:

  • Create a table for storing output.
  • Loop through information_schema database’sĀ COLUMNSĀ table to obtain alldatabases, table and column names.
  • Execute a count(*) query on database.table for each column with appropriate search string in where condition.
  • If count(*) > 0, that perticular column hasĀ the searchĀ term.
  • Insert that triplet (database name, table name, column name) in to a table.
  • Select * from table to view respective database,table and column names havingĀ the searchĀ term.

MySQL Procedure for search in all fields of all databases

Read More

-- Kedar Vaijanapurkar --