Tag Archives: code

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.

16 Nov

Calculate Mysql Memory Usage – Quick Stored Procedure

In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory usage estimation for the present MySQL instance.

We have global buffers which are allocated irrespective of connections as and when mysql server is started. Along with that mysql server allocates memory to each thread to perform respective tasks.

So the formula goes:

Mysql Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables).
Continue Reading…

04 Nov

Replication slave lag monitoring using heartbeat and windows batch scripts

“Show Slave Status” command has a last column “Seconds_Behind_Master”, which gives us idea about how much time slave is lagging behind master. It is an important to be considered parameter in monitoring and maintaining replication.

This article explains us a way to monitor replication slave lag time. It also includes a sample batch scripts to automate the monitoring process, makes it easy to understand.

Whats wrong with “Seconds_Behind_Master”:

Show Slave Status command; does shows us Seconds_Behind_Master.

Now Documentation says: The field measures the time difference in seconds between the slave SQL thread and the slave I/O thread. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks.

Now this has been discussed at a lot of places for a lot of times, the solution is to periodically insert a row into a “heartbeat” table on the master server. Let it get replicated on slave and check the “heartbeat”s on the slave. This will surely explain you replication time and the slave behind master.

So whats the idea?

Consider descriptions of following two mysql functions:

current_timestamp() : The function when used in a query replicates its output same across the slaves. Thus even if the query is executed at a later time on slave, the value stored would be same as what is there in the master.

sysdate() : The behavior of this function is different from the above. It stores the current output of the function at the slave when executed. Thus the output produced could be different from what was produced at the master.

Considering behavior, if we have both values inserted on master server; we will get it replicated on slave. But for sysdate() function, the time will be of slave’s and that will help us calculating the slave lag comparing with current_timestamp() value.

Implementing Slave Behind Master Monitoring:

I’ve tried to implement replication slave lag monitoring through “heartbeat” method.

Here for making an example I’ve considered MySQL Server with port 3307 as Master and Server with port 3306 (Default) as Slave for monitoring Slave Lag.

Create table on Master MySQL Server:

1:
2:
3:
4:
5:
create table heartbeat
(
  master_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  slave_time TIMESTAMP NOT NULL
) ENGINE=MyISAM;

Periodical value insertion – Generate Heartbeat:

1:
insert into heartbeat(slave_time) values(SYSDATE());

One more case is, when slave is intentionally kept behind master. We can surely add up that time and make according insertion.

1:
2:
3:
Insert Into heartbeat(slave_time)
Values( from_unixtime((unix_timestamp(sysdate()) + time_to_sec('HH:MM:SS'))) ;

Here, HH:MM:SS is slave’s known time of lagging behind master.

We have following queries to monitor slave-lag:

First query will tell us the time difference of slave and master query execution time. Which is actually caused by the functions explained above.

1:
2:
3:
4:
5:
Select master_time, timediff(slave_time, master_time)
From heartbeat
Where DATE(master_time) = DATE(NOW())
Order By master_time;

This second query will tell us the time difference of last query execution time from replication to now.

1:
select timediff(NOW(), max(master_time)) from heartbeat;

Batch Scripts For Windows:

To understand above setup and making it little automated, I wrote couple of batch scripts.

Following batch script will auto insert one row into heartbeat table on per minute basis on Master.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
##insert-master.bat
:begin
@echo off
set StartTime=%TIME%
set StartMin=%StartTime:~3,2%
:start
set EndTime=%TIME%
set EndMin=%EndTime:~3,2%
set /a Hour_Diff=StartMin - EndMin >nul
if %Hour_Diff%==0 (
goto start )
echo Beating...
mysql -uroot -pXXXX -P3307 -e "use master;insert into heartbeat(slave_time) values(SYSDATE());"
goto begin
pause

Similar batch created to monitor the lag time on Slave MySQL Server.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:




19:
20:
21:
22:
23:
##monitor-slave.bat
@echo off
echo Observing slave delay - per minute basis
echo SBM -  Slave Behind Master / Last Query Executed.
echo ETD - Execution Time Difference
echo ----------------------------------
echo SBM  -  ETD - Slave Status
echo ----------------------------------
:begin
set StartTime=%TIME%
set StartMin=%StartTime:~3,2%
:start
set EndTime=%TIME%
set EndMin=%EndTime:~3,2%
set /a Hour_Diff=StartMin - EndMin >nul
if %Hour_Diff%==0 (
         goto start )
FOR /F "skip=1 tokens=*" %%I IN ('mysql -uroot -pXXXX -e
"use master;select TIME_TO_SEC(timediff(NOW(), max(master_time)) )
last_query_time, second(timediff(max(slave_time), max(master_time)))
master_slave_difference from heartbeat where DATE(master_time) = DATE(NOW())
order by master_time desc;"') DO set var=%%I%
@echo %var%
FOR /F "tokens=11 delims='|        '" %%G IN ('mysql -uroot -pXXXX -e "show slave status;"') do @echo                  %%G
FOR /F "tokens=12 delims='|        '" %%G IN ('mysql -uroot -pXXXX -e "show slave status;"') do @echo                  %%G
goto begin
pause

PS: Set passwords accordingly.

Using sample batch scripts we will be able to understand the replication lag.
ETD, the Execution Time Difference from master to slave shows us how long slave takes to replicate once master has executed the statement. By query we can understand it is difference between current_timestamp() and sysdate().
SBM, Slave Behind Master, points us the time in seconds last query executed on slave from master.
The monitoring script will also tell you the IO and SQL thread status after each minute.

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

08 May

The Dos Chat – Lan Messenger

This is a LAN Messenger which allows you to chat with friends on local network. The Dos based chat program is just like a chat program but only for LAN. What you need to have is: This tool, a desire for communication and an IP / Machine Name. In one line, its a “batch program for chatting”.

To Install LAN Messenger / Dos Chat:

The file setup-dos-chat.bat enables the Messenger Service on your windows machine. Or you can enable dos based chat manually by enabling Messenger Service.

  • To manually enable Messenger Service:
  • Start -> Run -> type: services.msc -> Press Enter
  • Search for Messenger Service and enable it.

The Code:

@echo off

mode con cols=75 lines=20

TITLE Dos Chat – Lan Messenger by Krex :: Starting…

COLOR 4F

echo ********** Dos-Chat by Krex **********

set /p n=Enter IP Address or Machine-Name:

TITLE Chatting with – %n% – Lan Messenger by Krex: http://kedar.nitty-witty.com

echo **********  Chatting with: %n%  **********

:A

set /p m=Message:

net send %n% %m%

Goto A

: : : : : : : : : : : : : : : : : : : : : : : : : : :  : :  : : : : : :

: : Dos-chat – A GNU/GPL Lan Messenger ;): :

: : http://kedar.nitty-witty.com                          : :

: : : : : : : : : : : : : : : : : : : : : : :  : : : : : : : : : : : : :

I hope you’ll enjoy it.

-- Kedar Vaijanapurkar --