How to resolve issue Drop ,Delete ,Alter commands taking long time?

 

Why this issue arises ?

This is probably due to Metadata locking .It is most common to see table gets struck in "Waiting for Metadata Locks". ..It comes basically due to some uncommitted Transactions.

Solution

In order to resolve this first we should wait for sometime for transaction to complete that might be using that table.If it is taking very long time then following are the steps to resolve this :

1.) Login Mysql 

     mysql -u root -p 

     ******

2.) RUN  

     Show Full Processlist.

     (Here you will get list of transactions that are creating lock on other transactions )

+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 404 | root | localhost | example           | Sleep   | 297  |       |                  |
| 410 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

Here process 404 is creating lock on other transactions.

3.) kill process 404 by running:

      kill 404

4.) If above process kill doesn't works then you can go for killing other processes as mentioned in  Step 3.

5.) Now try executing Drop ,Delete,Alter or any command it would now work correctly.

Above approach is a better approach instead of MySQL server restart.

Hope the above solution works .Looking forward for your questions and suggestions ..  :)

Create Routines in Jasper ETL  

 

What are Routine ? 

Routines are habits which do not change and are repeated every time . In Jasper ETL Routines are Java Methods or reusable code that are required generally in Jobs for manipulating data.

Below are the list of default System Routines present in Jasper ETL.

Creating Custom/User Defined Routines   

There could be a point when we would require to create our own methods and classes to transform data in our jobs.In that case we could create our own Routines also known as User Defined Routines.

Below is the procedure to create user defined routines :-

a.) Right Click Routines inside Code and click on Create Routine.

  b.) Normaly Create a Java Class in any IDE or Notepad which ever is better for you.

       For e.g Here is the code I first wrote in NetBeans to convert any timezone to UTC

c.) Paste the same code inside the Routines window opened and save it.

d.) No you could continue to use this method in your jobs.Inside Categories select User Defined you will see the method created by you.
     

NOTE : Always declare custom  Methods as Public Static so that it could be accessed publicly and shown inside User Defined option.

Hope you find this article helpful :) .

Scheduling Export of Reports to FTP Server from JasperServer

In this article I would like to demonstrate How we could export reports to a ftp server in Synchronization with Scheduling a job.

As there are client requirements that reports should be regular bulk export of data to the site or FTP server ,JasperServer has provided options that by Scheduling Job we could either mail reports to user send notifications to clients/user or could directly export reports to a FTP server or could choose both mailing and export of reports simultaneously as in my case.

First I would show you how we could export reports to an FTP server 

Preconditions :

1.) Working JasperServer either Community or Pro Edition

2.) There should be a report on JasperServer for Scheduling Job

Follow the following Steps to Schedule Job:

1.) Right Click on Report and Select Schedule Option.

 

2.) Click on Create Schedule.

3.) Under Schedule there are various options like 

•   Schedule Start
•   Recurrence Type

    Under Scheduled Start :  Selected  immediately and Under Recurrence Type : Selected Calender

4.) Under times select Hours and Minutes,Here I have chosen 0-23 for running every hour and 0 in minutes for running at start of every hour. 

5.) Select Output Options.

6.) In Output Options under Formats Select PDF Format and Enters FTP Details
      FTP Details :

•      Server
•      Directory
•      UserName
•      Password

     And Click on Test Connections to verify whether connection is successful or not

7.)  Final Step Click on Submit and Check the Scheduled Transfer of Reports on FTP Server.


Now the Scheduled export of reports on FTP server would work successfully .

I would also share video with all regarding the Steps.Hope you would find this article helpful ,

Thanks :)
Amit