Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL. All code is documented and can be downloaded in a zip file.

Setting the mode to STRICT_ALL_TABLES when you compile your stored procedure or function can prevent a lot of subtle bugs in your MySQL application. Consider this example: DELIMITER // CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT BEGIN     DECLARE v_result TINYINT;     SET v_result := p_first + p_second;     RETURN v_result; END// DELIMITER ; compile it and then test the funcion: mysql> [...]

A common problem with storing counters in a table is that every time your application update your counter, a row lock needs to be set on the row the table. If your application has a need for storing counters you can use this package which contains the scripts for a table and some stored procedures to handle managing the counters.

LinkedIn has what they call "inDays" where employees may so something interesting which may not be directly related to their day job. I spent my inDay by porting my old WL820 project (External Language Stored Procedures) to MariaDB 5.3.

The code, as usual, is available on LaunchPad ... To get the branch, simply do:

bzr branch lp:~atcurtis/maria/5.3-wl820 The test cases pass... I haven't tested

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

1. Create the table to store the current sequence values:

   CREATE TABLE _sequence
   (
       seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
       seq_group VARCHAR(10) NOT NULL,
       seq_val INT UNSIGNED NOT NULL
   );

2. Create a function to get and increment the current value:

   delimiter //
   DROP FUNCTION IF EXISTS getNextCustomSeq//
   CREATE FUNCTION getNextCustomSeq
   (
       sSeqName VARCHAR(50),
       sSeqGroup VARCHAR(10)
   ) RETURNS VARCHAR(20)
   BEGIN
       DECLARE nLast_val INT; 
    
       SET nLast_val =  (SELECT seq_val …

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       | …

Did you ever need to run LOAD DATA INFILE in a procedure? May be to automate or dynamically perform the large data file load to the MySQL database. In this…

The post MySQL Load Data Infile with Stored Procedure first appeared on Change Is Inevitable.

Sometimes we have some special need for a stored procedure to call another to do something. That is fine, but what if the second stored proc failed for some reason? Maybe you want to halt the first stored procedure (the caller) and not proceed with the work until the problem is verified and resolved. So How do you do it?

Simple, get a return code from the called procedure to determine if it worked or not!

Here’s a sample piece of code to explain better:


DROP PROCEDURE IF EXISTS `test`.`testing123`;
DELIMITER $$

CREATE
PROCEDURE `test`.`testing123`(OUT a INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET a=2;
END;
SET a=0;

# toggle the below as comment or not to see the call at the bottom working
# if you uncomment select abc you'll see the error, otherwise all is cool

# select abc;

…

Lets paint the picture:

Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
Scenario part 2 : Legacy system where the people who wrote it left a long time ago
Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt yourself.

I wonder, who’s gonna cover my health insurance if i go crazy? :)

mysql 02:55:47 DEV …

One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

First, a simple table, not partitioned (yet):

use test;
DROP TABLE IF EXISTS my_part;
CREATE TABLE IF NOT EXISTS my_part (
  id int NOT NULL,
  creationDate datetime NOT NULL,
  PRIMARY KEY (id,creationDate)
) ENGINE=InnoDB;

In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.

Now, add the partition definition. This can be done in the CREATE statement, but I have found that it is easier for me to think …