MacLochlainns Weblog

In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.

Below is my dropIndexIfExists stored procedure:

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;
 
-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$
 
-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name  VARCHAR(64)
, pv_index_name  VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);
 
  /* Check if the constraint exists. */
  IF EXISTS (SELECT NULL
             FROM   information_schema.statistics s
             WHERE  s.index_schema = database()
             AND    s.table_name = pv_table_name
             AND    s.index_name = pv_index_name)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
-- Reset the default semicolon delimiter.
DELIMITER ;

You call the procedure like:

CALL dropIndexIfExists('payment','idx_payment01');

As always, I hope this helps those looking for a solution.

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
    ...
  ELSE
    ...
  END IF;

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.
DROP TABLE IF EXISTS road_runner;
 
-- Create roadrunner table.
CREATE TABLE road_runner
( road_runner_id    int unsigned  auto_increment primary key
, road_runner_text  varchar(20)
, CONSTRAINT road_runner_nk UNIQUE (road_runner_text));
 
-- Drop coyote table if exists.
DROP TABLE IF EXISTS coyote;
 
-- Create coyote table.
CREATE TABLE coyote
( coyote_id       int unsigned  auto_increment primary key
, coyote_text     varchar(20)
, road_runner_id  int unsigned
, CONSTRAINT coyote_nk UNIQUE (coyote_text));

The following creates a procedure that:

• Writes data to two tables when the values are unique, returning a value of zero when it works.
• Writes data to neither table when the values to either table are non-unique, returning a value of one when it fails.

The procedure uses a 0 as a false value and a 1 as a true value. The use of a 0 and 1 for truth is a consistent approach for languages where they don’t support a Boolean data type.

-- Change the delimiter to a "$$"
DELIMITER $$
 
-- Drop the paired procedure.
DROP PROCEDURE IF EXISTS two_table$$
 
-- Create the paired procedure.
CREATE PROCEDURE two_table
(IN    pv_road_runner_text  varchar(20)
,IN    pv_coyote_text       varchar(20)
,  OUT pv_confirm_it        int)
BEGIN
  /* Declare a variable to hold a sequence value for an 
     auto incrementing value. */
  DECLARE lv_road_runner_id  int unsigned;
 
  /* Declare a condition variable for attempting to write
     a non-unique record to a table. */
  DECLARE duplicate CONDITION FOR 1062;
 
  /* Declare an event handler for a duplicate condition 
     variable, rollback transaction, and set 1 as a false
     condition. */
  DECLARE EXIT HANDLER FOR duplicate
    BEGIN
      ROLLBACK to all_or_none;
      SET pv_confirm_it = 1;
    END;
 
  /* Start the transaction. */
  START TRANSACTION;
 
  /* Set the save point for a multiple table transaction. */
  SAVEPOINT all_or_none;
 
  /* Insert into road runner table. */
  INSERT INTO road_runner
  (road_runner_text)
  VALUES  
  (pv_road_runner_text);
 
  /* Capture the auto incrementing sequence value as a 
     local variable. */
  SET lv_road_runner_id := last_insert_id();
 
  /* Insert into the coyote table. */
  INSERT INTO coyote
  (coyote_text
  ,road_runner_id)
  VALUES  
  (pv_coyote_text
  ,lv_road_runner_id);
 
  /* Commit the record. */
  COMMIT;
 
  /* Set the control variable to a true value. */
  SET pv_confirm_it := 0;
 
END;
$$
 
DELIMITER ;

You can test the pass-by-reference procedure with the following code:

-- Set a control variable with a null value.
SET @sv_control := null;
 
-- Query the results from a join between the road_runner and coyote tables.
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Call the two_table procedure with unique results.
CALL two_table('Road Runner 1','Coyote 1',@sv_control);
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT @sv_control AS "1st Insert";
 
-- Reset the value for a second test.
SET @sv_control := 0;
 
-- Call teh two_table procedure with unique results.  
CALL two_table('Road Runner 2','Coyote 1',@sv_control);  
 
-- Query the results from a join between the road_runner and coyote tables.  
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT   @sv_control AS "2nd Insert";

I hope this helps anybody trying to implement a pass-by-reference procedure with a control variable. You can find other examples in Chapter 14 of Oracle Database 11g and MySQL 5.6 Developer Handbook (pp. 446-449 and 450-451).

Somebody asked how to work around an error message they got after converting one of their who-audit columns to a TIMESTAMP column. A TIMESTAMP column has a DEFAULT or ON UPDATE current timestamp. They wanted to have two TIMESTAMP columns in the same table, with the intention of:

1. Having the created column assign a current timestamp value on insert
2. Having the updated column assign a current timestamp value on insert and update

This is a sample table with two of the traditional four who-audit columns:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    TIMESTAMP NOT NULL 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

When they tried to create the table they got the following error:

ERROR 1293 (HY000): Incorrect TABLE definition; there can be ONLY one TIMESTAMP COLUMN WITH CURRENT_TIMESTAMP IN DEFAULT OR ON UPDATE clause

While you can define a table with two columns that have a TIMESTAMP data type, you can’t define a table with two TIMESTAMP columns when one holds a DEFAULT or ON UPDATE CURRENT_TIMESTAMP value. However, you can use a DATETIME data type for the created column provided it’s null allowed, like:

CREATE TABLE sample
( sample_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, created    DATETIME 
, updated    TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP);

You can then define an on insert database trigger that fires when you create a new row, like this:

CREATE TRIGGER sample_t
BEFORE INSERT ON sample
FOR EACH ROW
BEGIN
  SET NEW.created = CURRENT_TIMESTAMP();
END;
$$

An INSERT statement would look like this:

INSERT INTO sample VALUES (NULL, NULL, NULL);

You would see the following if you query the table:

+-----------+---------------------+---------------------+
| sample_id | created             | updated             |
+-----------+---------------------+---------------------+
|         1 | 2011-08-11 00:26:21 | 2011-08-11 00:26:21 |
+-----------+---------------------+---------------------+

Hope this helps other too.

Somebody raised the question about writing stored functions while we were discussing scalar subqueries against COMMON_LOOKUP tables. Common look up tables store collections of possible <OPTION> elements. They’re basically generalized tables that contain a set of smaller tables, where the row sets make up a list of unique values.

When you write a function that must return one and only one row, the SELECT-INTO syntax is ideal in Oracle because it automatically raises an exception when the query returns no row or two or more rows. MySQL doesn’t raise an automatic exception when a SELECT-INTO fails to return a row but it does raise an ERROR 1722 when two or more rows are found.

Here are sample implementations written in Oracle’s PL/SQL and MySQL’s SQL/PSM languages. If you’re an Oracle developer and new to MySQL, the biggest oddity may be the delimiter, or it may be the sizing of formal parameters. Hopefully, its not the lack of formal declaration and exception blocks in SQL/PSM. For those coming from MySQL, the OR REPLACE command or dynamic sizing of formal parameter list values are the big changes.

Oracle PL/SQL Function

The Oracle PL/SQL function is very straightforward and helped by pre-defined exceptions for both the no data found and too many rows found errors. Formal parameters inherit their physical size at run time from the calling scope program.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Create or replace existing function with new implementation.
CREATE OR REPLACE FUNCTION get_common_lookup_id
( pv_table   VARCHAR2
, pv_column  VARCHAR2
, pv_type    VARCHAR2 )
RETURN NUMBER IS
 
  -- Declare a return variable.
  lv_return  NUMBER;
 
BEGIN
 
  -- Query data and assign it to a local variable.
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  -- Return the value found.
  RETURN lv_return;
 
EXCEPTION
 
  -- Handle errors.
  WHEN NO_DATA_FOUND THEN
    RAISE;
  WHEN TOO_MANY_ROWS THEN
    RAISE;
 
END;
/

MySQL SQL/PSM Function

The lack of an implicitly raised exception when a SELECT-INTO structure fails to return a row adds several steps to a SQL/PSM function. You must define a custom exception and an if-block to raise the exception, which is trigger by returning a null value into the lv_return local variable.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

/* Conditionally drop the function before trying to create it. */
DROP FUNCTION IF EXISTS get_common_lookup_id;
 
/* Change the delimiter to write a stored procedure into the database. */
DELIMITER $$
 
/* Create a function that replaces a scalar subquery. */
CREATE FUNCTION get_common_lookup_id
( pv_table   VARCHAR(30)
, pv_column  VARCHAR(30)
, pv_type    VARCHAR(30))
RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a return variable. */
  DECLARE lv_return  INT UNSIGNED;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE no_data_found CONDITION FOR SQLSTATE '99001';
 
  /* Query data and assign it to a local variable. */
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  /* The local variable is only null when no rows are returned by the implicit cursor. */
  IF lv_return IS NULL THEN
    SIGNAL no_data_found SET MESSAGE_TEXT = 'Result was no rows found.';
  END IF;  
 
  /* Return the value found. */
  RETURN lv_return;
 
END;
$$
 
/* Reset the delimiter to the default to run other programs. */
DELIMITER ;

Hope this help solve your problems.

My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.

Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.

This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.

The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.

A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.

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

UPDATE member m
SET    member_type =
 (SELECT common_lookup_id
  FROM   common_lookup
  WHERE  common_lookup_table = 'MEMBER'
  AND    common_lookup_column = 'MEMBER_TYPE'           
  AND    common_lookup_type =
          (SELECT  dt.member_type
           FROM   (SELECT   c.member_id
                   ,        CASE
                              WHEN COUNT(c.member_id) > 1 THEN 'GROUP'
                              ELSE 'INDIVIDUAL'
                            END AS member_type
                   FROM     contact c
                   GROUP BY c.member_id) dt
           WHERE   dt.member_id = m.member_id));

While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.

Procedural equivalents (or user-defined functions) simplify the update statement like so:

UPDATE member m
SET    member_type = get_member_type(m.member_id);

If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

CREATE OR REPLACE FUNCTION get_member_type
(pv_member_id NUMBER) RETURN NUMBER IS
 
  -- Define a collection of strings.
  TYPE type_options IS TABLE OF VARCHAR2(10);
 
  -- Declare local variables.
  lv_dependent NUMBER := 1;
  lv_return_id NUMBER;
 
  -- Declare a local variable of the collection.
  lv_member_type TYPE_OPTIONS := type_options('INDIVIDUAL','GROUP');
 
  -- Define a dynamic cursor to count the number of foreign key values. 
  CURSOR count_contact (cv_member_id NUMBER) IS
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = cv_member_id;
 
  -- Define a dynamic cursor to find a key for an individual or group member type.    
  CURSOR get_lookup_id (cv_type VARCHAR2) IS
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_context = 'MEMBER'
    AND    common_lookup_type = cv_type;
 
BEGIN
 
  -- Get the number of foreign key values for a contact.
  OPEN  count_contact(pv_member_id);
  FETCH count_contact
  INTO  lv_dependent;
  CLOSE count_contact;
 
  -- Open the dynamic cursor with the required value.  
  IF lv_dependent = 1 THEN
    OPEN get_lookup_id(lv_member_type(1));
  ELSE
    OPEN get_lookup_id(lv_member_type(2));
  END IF;
 
  -- Get the correct surrogate primary key value.
  FETCH get_lookup_id
  INTO  lv_return_id;
  CLOSE get_lookup_id;
 
  -- Return the correct primary key for use as a foreign key.
  RETURN lv_return_id;
END;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

SELECT 'DROP FUNCTION IF EXISTS get_member_type' AS "Statement";
DROP FUNCTION IF EXISTS get_member_type;
 
SELECT 'DELIMITER $$' AS "Statement";
DELIMITER $$
 
SELECT 'CREATE FUNCTION get_member_type' AS "Statement"$$
CREATE FUNCTION get_member_type(pv_member_id INT) RETURNS INT 
BEGIN
 
  /* Define three local variables. */
  DECLARE lv_contact_number  INT;
  DECLARE lv_member_type     CHAR(30);
  DECLARE lv_return_value    INT;
 
  /* Define a dynamic cursor to count the number of foreign key values. */  
  DECLARE contact_cursor CURSOR FOR
    SELECT COUNT(c.member_id)
    FROM   contact c
    WHERE  c.member_id = pv_member_id;
 
  /* Define a dynamic cursor to find a key for an individual or group member type. */
  DECLARE common_lookup_cursor CURSOR FOR
    SELECT common_lookup_id
    FROM   common_lookup
    WHERE  common_lookup_table = 'MEMBER'
    AND    common_lookup_column = 'MEMBER_TYPE'           
    AND    common_lookup_type = lv_member_type;
 
  /* Get the number of foreign key values for a contact. */    
  OPEN  contact_cursor;
  FETCH contact_cursor
  INTO  lv_contact_number;
  CLOSE contact_cursor;
 
  /* Assign group membership when more than one foreign key value is found; and
     assign individual membership when only one foreign key value is found. */  
  IF lv_contact_number > 1 THEN
    SET lv_member_type = 'GROUP';
  ELSE
    SET lv_member_type = 'INDIVIDUAL';
  END IF;
 
  /* Get the correct surrogate primary key value. */  
  OPEN  common_lookup_cursor;
  FETCH common_lookup_cursor
  INTO  lv_return_value;
  CLOSE common_lookup_cursor;
 
  /* Return the correct primary key for use as a foreign key. */  
  RETURN lv_return_value;
END;
$$
 
SELECT 'DELIMITER $$' AS "Statement"$$
DELIMITER ;

You can query the results of the update statement with the following.

1
2
3
4
5
6
7
8
9
10
11

SELECT   m.member_id
,        dt.quantity
,        m.member_type
,        cl.common_lookup_type
FROM     member m JOIN (SELECT   member_id
                        ,        COUNT(c.member_id) AS quantity
                        FROM     contact c
                        GROUP BY c.member_id) dt
ON       m.member_id = dt.member_id JOIN common_lookup cl
ON       m.member_type = cl.common_lookup_id
ORDER BY m.member_id;

As always, I look forward to helping and gaining insight.

Somebody, who read this post on Debugging MySQL Procedures, asked why the strategy of selecting a string literal didn’t work in a MySQL function. That’s easy, they’re not designed to support a SELECT statement, only a SELECT-INTO statement.

Why? That’s the purpose of a function to perform something and return a single reply.

That’s also why a MySQL functions only support the IN mode of operation for formal and call parameters. When formal parameters are restricted to in-mode-only operations, they implement a pass-by-value function model. This can also be expressed from the other side of the looking glass. In that case, MySQL functions don’t support pass-by-reference functions that use the INOUT or OUT mode operations.

If you put a SELECT statement in a function to print internal values or comments, it raises an error. Take for example the following attempt to create the debugging function with an echo of output (that works in stored procedures).

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$

It fails to create the function because you’ve violated a key integrity rule. It also raises the following error:

ERROR 1415 (0A000): Not allowed to return a result set from a function

You have two potential solutions to this problem. The first is limited and inflexible. The second isn’t as limited or inflexible and is the recommended way to debug your functions without a tool. That’s to use a temporary table to record run-time debugging events.

SET @counter := 0;
SET @msg := '';

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$

SELECT debugger();
SELECT @msg;

+------------+
| @msg       |
+------------+
| [Debug #1] |
+------------+

SET @counter := 0;

CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;

1
2
3
4
5
6
7

CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$

SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;

+---------------+
| debug_comment |
+---------------+
| [Debug #1]    |
| [Debug #2]    |
| [Debug #3]    |
+---------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that returns 1.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT '[Debug #1]';
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Declare session level variables.
SET @counter := 0;
SET @msg := '';
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to local session variables.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  SELECT CONCAT('[Debug #',@counter,']') INTO @msg;
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT @msg;
 
-- Declare a session level variable.
SET @counter := 0;
 
-- Conditionally drop the function when it exists.
DROP TABLE IF EXISTS debugger;
 
-- Create a temporary (in-memory) table to record debugging information.
CREATE TABLE debugger
( debug_comment CHAR(80)) ENGINE=MEMORY;
 
-- Conditionally drop the function when it exists.
DROP FUNCTION IF EXISTS debugger;
 
-- Set delimiter to create a function with semicolon statment terminators.
DELIMITER $$
 
-- Create a function that writes to a debugging table.
CREATE FUNCTION debugger() RETURNS INT
BEGIN
  SELECT @counter + 1 INTO @counter;
  INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']'));
  RETURN 1;
END;
$$
 
-- Reset the delimiter to enable normal execution.
DELIMITER ;
 
-- Test the function code and read the session-level variable contents.
SELECT debugger();
SELECT debugger();
SELECT debugger();
SELECT debug_comment FROM debugger;