Showing entries 1 to 10 of 52
10 Older Entries »
Displaying posts with tag: Stored Procedures (reset)
MySQL stored procedures: all ways to produce an output

There are many non-obvious ways to return values from MySQL stored procedures, and each of them solves particular problems.

Working with JSON arrays in MySQL/MariaDB

When you write stored procedures in MySQL or MariaDB, one of the missing features is an ARRAY type. But actually, it is possible to create arrays by using the JSON type. This article explains how to work with JSON arrays.

The post Working with JSON arrays in MySQL/MariaDB appeared first on Federico Razzoli.

Variable number of parameters and optional parameters in MySQL/MariaDB procedures

MySQL and MariaDB don't allow optional parameters for stored procedures, nor procedures accepting any number of parameters. However, these features can be emulated by passing JSON documents. Let's see how.

The post Variable number of parameters and optional parameters in MySQL/MariaDB procedures appeared first on Federico Razzoli.

Updating triggers online with MySQL and MariaDB

Changing triggers in production is problematic, because for a short time triggers will be missing, or inconsistent with each other. This article proposes a solution to resolve the problem.

The post Updating triggers online with MySQL and MariaDB appeared first on Federico Razzoli.

Why MySQL Stored Procedures, Functions and Triggers Are Bad For Performance

MySQL stored procedures, functions and triggers are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines. Not being entirely sure of what I was seeing during a customer visit, I set out to create some simple tests to measure the impact of triggers on database performance. The outcome might surprise you.

Why stored routines are not optimal performance wise: short version

Recently, I worked with a customer to profile the performance of triggers and stored routines. What I’ve learned about stored routines: “dead” code (the code in a branch which will never run) can still significantly slow down the response time of a function/procedure/trigger. We will need to be careful to clean up what we do not need.

Profiling MySQL stored functions

Let’s compare these four simple stored functions (in MySQL 5.7): …

[Read more]
Profiling Stored Procedures in MySQL 5.7

With the changes to performance_schema in MySQL 5.7 Development Milestone Release it is now possible to analyze and profile the execution of stored programs. This is highly useful if you develop more complex stored procedures and try to find the bottlenecks. The "old" performance_schema up to MySQL 5.6 only reported a CALL statement with a runtime, but no information on statements that were executed WITHIN the stored procedure. Now let's try this in the latest MySQL 5.7.6 DMR release. After creating some test table and a test stored procedure we need to activate the events_statements_history_long consumer, which is OFF by default:

mysql> UPDATE setup_consumers SET ENABLED="YES" 
           WHERE NAME = "events_statements_history_long"; 

Then let's call the stored procedure that we want to inspect:

mysql> CALL …

[Read more]
Automatic Logging of Table Data Changes and Creation of Backups via a Stored Procedure


The stripped down stored procedure shown below will accept any Data Manipulation Language statement as its parameter and automatically log the statement and create table backup copies before the statement is executed. The logging functionality is similar to MySQL's binary log but exclusive to DML statements and is useful for table data recovery operations, such as undoing the last table data change or to revert databases back to a certain point in time. All this is done exclusively using stored routines (procedures and functions).

Its assumed that the databases and tables that will be used are already formed to specific business requirements since DDL statements will not be logged by the stored procedure. Though logging of table data changes can also be achieved using triggers, it is not practical to alter each and …

[Read more]
MariaDB 10.1 Brings Compound Statements

A very old post of mine in 2009, MySQL’s stored procedure language could be so much more Useful suggested that it would be nice if MySQL could be adapted to use compound statements directly from the command line in a similar way to the language used for stored procedures. I’ve just seen that this seems to … Continue reading MariaDB 10.1 Brings Compound Statements

Capture MySQL Foreign Keys

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
               ,'ADD CONSTRAINT',' …
[Read more]
Signal from a procedure

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

  IF add_numbers(1,3) > 3 THEN

You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.

The example first creates two tables, the road_runner and coyote tables:

-- Drop road_runner table if exists.
-- Create roadrunner table. …
[Read more]
Showing entries 1 to 10 of 52
10 Older Entries »