MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PSM’ Category

Create a synonym in MySQL

with 3 comments

A friend wanted to know how to create a synonym to a library of stored functions and procedures in MySQL. I had to deliver the sad news that you can’t create a synonym in MySQL 5.0, 5.1, or 5.5. You need to write a wrapper function or procedure.

A synonym (available in Oracle) would eliminate the need to call a function from another database through the component selector (or period). That’s because it maps a name to the reference and name instead of requiring you to include it with each call. The following shows a call from outside of the lib database:

SELECT lib.demo('Hello World!') AS "Statement";

He asked how to do it, so here’s how you do it below. Assume the following for this example:

  • You have a lib database where you define all your functions, procedures, and tables.
  • You have a app database that supports web connections.
  • You don’t want to allow web users to connect directly to the lib database because access to the tables should only be available through the functions and procedures.

This base function is simplified to avoid interaction with table data but illustrate the technique of definer rights functions. The function takes a string of up to 20 characters and returns it enclosed in double quotes. To mimic these behaviors, as the root user, you should create the app and lib databases, the dev and web users, and grant privileges to the dev user to act in the lib database and the web user to act in the app database. The example below does this as the root user, but in real life don’t use a trivial password like the example:

/* Create the two databases. */
CREATE DATABASE app;
CREATE DATABASE lib;
 
/* Create the two users, the developer can only connect locally. */
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
CREATE USER 'web'@'%' IDENTIFIED BY 'web';
 
/* Grant privileges to be a definer in both databases. */
GRANT ALL ON app.* TO 'dev'@'localhost';
GRANT ALL ON lib.* TO 'dev'@'localhost';
 
/* Grant privileges to any function or privilege in the APP database to the WEB user. */
GRANT EXECUTE ON app.* TO 'web'@'%';

After creating and granting all the appropriate privileges, here are the steps to create the test case.

  1. You create and test the function as the dev user in the lib database.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */
DELIMITER $$
 
/* Create a function that echoes back the string with double quotes. */
CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  RETURN CONCAT('"',str,'"');
END;
$$
 
/* Reset the DELIMITER value. */
DELIMITER ;
 
/* Query the function. */
SELECT demo('Ciao amico!') AS "Statement";
  1. You create and test the wrapper function as the dev user in the app database.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */
DELIMITER $$
 
/* Create a function that echoes back the string with double quotes. */
CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  RETURN lib.demo(str);
END;
$$
 
/* Reset the DELIMITER value. */
DELIMITER ;
 
/* Query the function. */
SELECT demo('Ciao amico!') AS "Statement";

If you’re wondering why a GRANT wasn’t required from the lib database to the app database, it’s because the dev user has access to both databases and defined both objects.

  1. You can test the wrapper function as the web user in the app database.
SELECT demo('Yes, it works!') AS "Statement";

This is the closest to a synonym for a function or procedure that is possible. I know this solves his problem and hope it solves a couple others too.

Written by maclochlainn

February 5th, 2011 at 11:02 pm

Prepared Statement Failure

with 3 comments

One of my students asked for some help on developing a MySQL stored procedure that would conditionally drop a foreign key constraint. I put together a quick example, which compiled fine but failed at run time. With some investigation it appears that either the MySQL 5.5 Documentation in Section 12.6 is incorrect or you can’t ALTER TABLE inside a MySQL Prepared Statement with placeholders.

A subsequent test showed me that you couldn’t use ALTER TABLE statement in a prepared statement outside of a stored procedure. I logged Bug #59604 with my other test case, and put the test case there. They closed the bug and validated what I suspected and clarified what I missed, you can’t use placeholders in prepared DDL statements.

The following is the test code example that failed:

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
DROP PROCEDURE IF EXISTS dropForeignKey;
 
DELIMITER $$
 
CREATE PROCEDURE dropForeignKey
( pv_database   VARCHAR(64)
, pv_table      VARCHAR(64)
, pv_constraint 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 := 'ALTER TABLE ? DROP FOREIGN KEY ?';
 
  /* Assign the formal parameters to session variables because prepared statements require them. */
  SET @sv_table := pv_table;
  SET @sv_constraint := pv_constraint;
 
  /* Check if the constraint exists. */  
  IF EXISTS (SELECT NULL
             FROM   information_schema.referential_constraints
             WHERE  constraint_schema = pv_database
             AND    TABLE_NAME = pv_table
             AND    constraint_name = pv_constraint)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt USING @sv_table, @sv_constraint;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
DELIMITER ;

Calling this with the following syntax:

CALL dropForeignKey(DATABASE(),'telephone','telephone_fk4');

It raised the following error message.

ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near '? DROP FOREIGN KEY ?' at line 1

Based on the comment from Bug #59604, I concluded that the ALTER TABLE statement doesn’t support using session variables. However, the CONCAT() function solves the problem. Given this is a DDL command, and any extraneous quoting would simply fail parsing rules because of the CONCAT function, SQL injection doesn’t appear a threat.

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
DROP PROCEDURE IF EXISTS dropForeignKey;
 
DELIMITER $$
 
CREATE PROCEDURE dropForeignKey
( pv_database  VARCHAR(64)
, pv_table      VARCHAR(64)
, pv_constraint 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,' DROP FOREIGN KEY ',pv_constraint);
 
  /* Check if the constraint exists. */    
  IF EXISTS (SELECT NULL
             FROM   information_schema.referential_constraints
             WHERE  constraint_schema = pv_database
             AND    TABLE_NAME = pv_table
             AND    constraint_name = pv_constraint)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
DELIMITER ;

A more useful approach would be to drop all foreign keys that reference a table. Here’s how you would accomplish that.

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
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropForeignKeys;
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropForeignKeys
( pv_database          VARCHAR(64)
, pv_referenced_table  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name       VARCHAR(64);
  DECLARE lv_constraint_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE foreign_key_cursor CURSOR FOR
    SELECT   rc.table_name
    ,        rc.constraint_name
    FROM     information_schema.referential_constraints rc
    WHERE    constraint_schema = pv_database
    AND      referenced_table_name = pv_referenced_table
    ORDER BY rc.table_name
    ,        rc.constraint_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN foreign_key_cursor;
  cursor_foreign_key: LOOP
 
    FETCH foreign_key_cursor
    INTO  lv_table_name
    ,     lv_constraint_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('ALTER TABLE ',lv_table_name,' DROP FOREIGN KEY ',lv_constraint_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_foreign_key;
  CLOSE foreign_key_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

You would test it with this call:

CALL dropForeignKeys(DATABASE(),'system_user');

As always, I hope this helps somebody.

Written by maclochlainn

January 19th, 2011 at 1:39 am

Posted in Mac OS X,MAMP,MySQL,PSM

Correlated Update Statement

without comments

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.

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.

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

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

Written by maclochlainn

June 27th, 2010 at 10:12 pm

Debugging MySQL Functions

with 4 comments

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.

Written by maclochlainn

June 26th, 2010 at 6:51 pm

Alice and Assignments

without comments

As I continue down the warren hole of Persistent Stored Modules (SQL/PSM) in MySQL, I keep wondering about that mad hare, Johnny Depp. Alice isn’t a programming language to teach me anything in this dream. Moreover, TIm Burton’s tale this seems oddly familiar, like a child’s story gone mad.

A quick update on comparative SQL expression assignments between PL/SQL and MySQL. When you want to filter a value through SQL functions before assigning it to another variable in MySQL, it’s not like PL/SQL. Just like the new Alice in Wonderland movie isn’t like the book.

The programmatic differences lies in their origins. PL/SQL evolved from Pascal through Ada to become a recursive language where you can call SQL from PL/SQL and PL/SQL from SQL. MySQL implemented PSMs from the ANSI SQL:2003 specification, which didn’t see it the same way, apparently (a disclaimer since I’ve not read the details of the specification).

Personally, I think PL/SQL is easier to write but I’ve been using it for almost 20 years. Naturally, there may be a consistency thread on this that I’m missing and an opportunity that I may exploit. After all, it is dark in this warren hole.

Oracle PL/SQL Assignments from SQL Expressions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Enable output printing.
SET SERVEROUTPUT ON SIZE 1000000
 
-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  lv_left_operand := UPPER(SUBSTR(lv_right_operand,1,3));
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

Oracle also supports this syntax, which isn’t frequently used because it’s much more verbose syntactically. It is also equivalent to the PSM syntax adopted by MySQL.

-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  SELECT UPPER(SUBSTR(lv_right_operand,1,3)) INTO lv_left_operand FROM dual;
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

That means we can do it like the White Queen wants it or the Red Queen wants it in Oracle. Flexibility in PL/SQL is clearly broader because of the assignment options. Not so in MySQL, as you’ll see.

MySQL PSM Assignment from SQL Expressions

First, MySQL’s PSM approach doesn’t support anonymous blocks. The example must create a stored function or procedure, and then call it. A procedure seems like the best fit for the example.

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
-- Conditionally drop procedure.
SELECT 'DROP PROCEDURE IF EXISTS assignit' AS "Statement";
DROP PROCEDURE IF EXISTS assignit;
 
-- Create the proceudre
SELECT 'CREATE PROCEDURE assignit' AS "Statement";
 
DELIMITER $$
 
-- Define the procedure.
CREATE PROCEDURE assignit()
BEGIN
  /* Declare a source variable. */
  DECLARE lv_right_operand VARCHAR(10) DEFAULT 'March';
 
  /* Define a target variable for the assignment. */
  DECLARE lv_left_operand  VARCHAR(3);
 
  /* Assign the modified value through the SELECT-INTO model. */
  SELECT UCASE(SUBSTRING(lv_right_operand,1,3)) INTO lv_left_operand;
 
  /* Display assigned value. */
  SELECT lv_left_operand;
 
END;
$$
 
DELIMITER ;
 
-- Call the procedure.
CALL assignit();

The only question here in the warren is: Who’s the White Queen; and who’s the Red Queen. Which semantic should I choose? My I hope is that I wake up before it’s … oops, off with his head. Actually, 3D or not, I’ll probably not see it, that’s the new Alice in Wonderland film.

Likewise, when my students wake up and read this they’ll know I was just answering a question on how to perform assignments in MySQL stored procedures. By the way, I’ve updated this assignment process in my Debugging MySQL Procedures post.

As an aside, I’ve got a new MySQL debugger that I’m testing later in the week. When I complete the test cases, I’ll post a review.

Written by maclochlainn

March 15th, 2010 at 10:59 pm

Posted in MySQL,Oracle,pl/sql,PSM,sql