Showing entries 1 to 10 of 70
10 Older Entries »
Displaying posts with tag: stored procedure (reset)
Handy stored procedure for regular DBA tasks

As a stored procedures fan, I use MySQL stored procedures to get some of my DBA tasks accomplished. To make it simple, I have a template stored procedure that can be customized for several purposes.
The template syntax contains cursor, continue handler, loop, if condition and prepared statement. Thought it may be useful for others – at least, who are searching for the MySQL Stored Procedure syntax – so I’m publishing this post!

Use case: Reorganize tables partition:

In MySQL Cluster, it is required to reorganize all NDB tables’ partitions after adding new data nodes to rebalance the data across all nodes. Also the tables need to be optimized afterwards to reclaim the memory space. For this task I use the following procedure:

DROP PROCEDURE IF EXISTS reorganize_tables;
CREATE PROCEDURE reorganize_tables (IN db_name VARCHAR(50))

[Read more]
How to call MySQL stored procedures and functions with JPA and Hibernate

Introduction This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational … Continue reading How to call MySQL stored procedures and functions with JPA and Hibernate →

MySQL inline query versus stored procedure comparison

Simple query using group clause for 1 million records resulting in final list of 27 records.

First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600 secs.

So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure no_cache_comparison.png where left one is simple inline query and right one is stored procedure query.

There are some actions which are extra in the inline query:

1. freeing items
2. logging slow query
3. cleaning up

Running both second time retrieve data from cache taking
0.00048025 secs for simple query and 0.00036625 for stored procedure.

Profiling comparison for …

[Read more]
MySQL:The user specified as a definer does not exist (error 1449)-Solutions

Explaining and providing solutions of MySQL error 1449: The user specified as a definer does not exist using SQL SECURITY INVOKER and DEFINER.

Take the long view on the MySQL PERFORMANCE_SCHEMA with ps_history and sys_history

The performance_schema is a powerful tool for analyzing MySQL performance and behavior. One aspect of the performance_schema is that the view of the data is "right now", and very little historical information is present. You can see that there are 10 threads connected right now, but what about five minutes ago?

ps_history is a set of stored routines and events for periodically collecting the data in the performance_schema into another schema called ps_history. The ps_history schema contains a copy of each performance_schema view as a real table, and timestamp and server_id columns have been added to each table. Periodically (by default every 30 seconds) the performance_schema data is written into the history tables.

ps_history comes as one script (setup.sql) which will create the ps_history schema, the tables within it, and …

[Read more]
How to use value from row derived column from a table in another query?

SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.

"accounts" table

| Field        | Type        | Null | Key | Default | Extra          |
| account_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| account_name | varchar(50) | NO   |     | NULL    |                |
| balance      | float       | …

[Read more]
Use of loops in MySQL stored procedures

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true

Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql




[Read more]
Using conditional expressions inside MySQL Stored Procedure

Sometimes there is need to use conditional expression inside stored procedure to control the flow of execution.
We can use IF or CASE statements for this.

Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql


DROP PROCEDURE IF EXISTS get_performance$$

CREATE PROCEDURE get_performance
    (score NUMERIC(8, 2),
    OUT result VARCHAR(11))

    IF (score >= 90) THEN
        SET result = 'OUTSTANDING';
    ELSEIF (score >= 70 …

[Read more]
How to use and get output arguments in stored procedure using OUT parameter mode?

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

To do so, we can use the "OUT" parameter mode while defining stored procedures.

In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.

Stored Procedure Definition:

Store the below stored procedure in a file named my_sqrt.sql and save it.



CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
    SET op_number=SQRT(inp_number);

[Read more]
An approach to MySQL dynamic cross-reference query

I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:

1. Write the main query with rows: project identifier, task subject and total hours in a date range.

SELECT p.identifier, 
        e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours
        FROM redmine_nsp.time_entries e
        INNER JOIN redmine_nsp.issues i ON e.issue_id =
        INNER JOIN redmine_nsp.projects p ON e.project_id =
        WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07'
 GROUP BY p.identifier,e.issue_id;
| identifier | issue_id | subject                    | total_hours | …
[Read more]
Showing entries 1 to 10 of 70
10 Older Entries »