MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘LAMP’ Category

Configure Fedora on VMWare

with 4 comments

It seems Fedora is always a bit of work. You begin by mistakenly downloading Fedora Live, which isn’t really the product but a run-time demonstration product. After finding the product, if you choose a full installation, there are post installation steps to complete. The first time you launch it in VMWare, you’ll see a Gnome 3 Failed to Load error dialog like this:

I suspected that installing VMWare Tools would fix that, and it did. However, your entry account doesn’t have “sudoers” permissions. You must add them before you can run VMWare Tools. There are six steps to enable your user with the sudoers permissions and four others to configure the standard installation:

  1. Navigate to the Applications menu choice in the upper left hand corner. You’ll get the following drop-down menu. Click on Other menu item to launch a dependent floating menu.

  1. The following floating menu displays to the right. Click on Users and Groups menu item at the bottom of the list.

  1. The choice from the floating menu prompts account validation. Enter your password and click the OK button.

  1. After validating your password, the User Manager dialog opens. Click on the single user that should be installed – mclaughlinm. Click the Properties button to change the groups assigned to the user.

  1. The User Properties dialog opens with the default User Data tab clicked. Click on the Groups tab to add the user to the wheel group as a property of your user.

  1. Scroll down through the list of groups and click the wheel group check box. Like the Mac OS, the wheel group provides “sudoer” privileges. Click the OK button to assign the group to the user.

  1. Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Fedora virtual machine.

  1. Navigate to the Places menu choice and then Computer. Inside the Computer, choose the VMware Tools from the Devices section and you’ll see the following:

  1. Open a terminal session by choosing Applications, within the drop down choose System Tools, and then launch a Terminal session. You can then run the VMWare Toolkit by following these instructions:
cd /media/VMware\ Tools
cp VMwareTools-8.4.7-416484.tar.gz /tmp
cd /tmp
gunzip VMwareTools-8.4.7-416484.tar.gz
tar -xvf VMwareTools-8.4.7-416484.tar
cd vmware-tools-distrib
sudo ./vmware-install.pl

The last step requires that you reply to a set of prompts. If you’d like to accept the default at one time, you can use the following command:

sudo ./vmware-install.pl --default

If you find limited access to the system after installing or upgrading VMWare Tools, you may have packages in the caught between start and finish. You can clean them up with the following syntax, as the root user:

sudo yum-complete-transaction
  1. In the terminal session you should configure three files to make sure your networking works. I found that the dialogs failed to set one key element, so it’s simply easier to do this manually. Rather than using sudo, you should open a root shell with the following command:
sudo sh

Enter your user’s password:

[sudo] password for mclaughlinm:

You should use vi to edit and save the resolv.conf file with appropriate domain, search, and nameserver values. The values below work for VMWare when the gateway IP address is 172.16.123.2.

# Generated by NetworkManager
domain localdomain
search localdomain
nameserver 172.16.123.2

Using vi, edit the /etc/sysconfig/network file to include an appropriate gateway IP address, like so:

NETWORKING=yes
HOSTNAME=localhost.localdomain
GATEWAY=172.16.123.2

The last file to fix is /etc/sysconfig/network-scripts/ifcfg-eth0 file. This is the file that isn’t completely configured by the GUI component (it fails set the ONBOOT value to yes).

DEVICE=eth0
HWADDR=00:0c:29:31:ef:46
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=dhcp
TYPE=Ethernet
DNS1=172.16.123.2
USERCTL=no
PEERDNS=yes
IPV6INIT=no

You reset networking with the following command:

/etc/rc.d/init.d/network restart

As always, I hope this helps a few folks.

Written by maclochlainn

September 25th, 2011 at 10:40 pm

Posted in Fedora,LAMP,Linux,Red Hat

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

PHP leveraging PL/SQL

with one comment

Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type.

Here’s the schema-level PL/SQL function:

CREATE OR REPLACE FUNCTION like_boolean
( a NUMBER, b NUMBER ) RETURN NUMBER IS
 
  /* Declare default false return value. */
  lv_return_value NUMBER := 0;
 
BEGIN
 
  /* Compare numbers and return true for a match. */
  IF a = b THEN
    lv_return_value := 1;
  END IF;
 
  /* Return value. */
  RETURN lv_return_value;
 
END;
/

Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:

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
<?php
  // Capture local variables when provided.
  $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1;
  $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1;
 
  // Open a connection.
  if(!$c = oci_connect("student","student","localhost/xe"))
  {
    die;
  }
  else
  {
 
    // Parse a statement.  
    $s = oci_parse($c,"BEGIN
                         :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                       END;");
 
    // Bind input and output values to the statement.
    oci_bind_by_name($s,":returnValue",$returnValue);
    oci_bind_by_name($s,":thingOne",$thingOne);
    oci_bind_by_name($s,":thingTwo",$thingTwo);
 
    // Execute the statement.
    if (@oci_execute($s))
    {
      // Print lead in string.
      print "[".$thingOne."] and [".$thingTwo."] ";  
      if ($returnValue)
        print "are equal.<br />";
      else
        print "aren't equal.<br />";
    }
 
    // Clean up resources.
    oci_close($c);
  }
?>

If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }

If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..

10
11
12
13
  $s = oci_parse($c,strip_special_characters(
                    "BEGIN
                       :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                     END;"));

Hope that answers the question and helps some folks.

Written by maclochlainn

December 22nd, 2010 at 11:53 pm

Posted in LAMP,OPAL,Oracle,PHP,pl/sql

Tagged with , , ,

PHP Database Authentication

with one comment

A few years ago I wrote a couple articles showing how to use PHP to connect to an Oracle Database 10g Express Edition instance. They’re still there on Oracle’s Technical Network but the source files are missing. It appears that Oracle may have migrated the articles to a new server but failed to migrate the source files.

Don’t forget that you’ll need to Install the Zend Community Server. Then, you need to create an IDMGMT1, IDMGMT2, and IDMGMT3 users and run the create_identity_db2.sql or create_identity_db3.sql seeding script for both database validation models. You’ll find links to the original articles, the source code in zip files. The newer version with CSS is the IDMGMT3 code.

The CSS sign-on form looks like this:

The portal page looks like this:

The add new user page looks like this:

The source files are as follows:

  1. Database Authentication #1IdMgmt1.zip
  2. Database Authentication #2IdMgmt2.zip
  3. Database Authentication #3IdMgmt3.zip includes the CSS but works with the same write-up as Idmgmt2.

Hope this helps those who wanted the files.

Written by maclochlainn

December 19th, 2010 at 1:44 am

MySQL Sunday

without comments

The merry-go-round is running and Oracle Open World 2010 has begun with MySQL Sunday. The merry-go-round is a fixture at the northwest corner of the Moscone South building, at the intersection shared between the Moscone North building.

The keynote was interesting because Oracle confirmed that they have and will continue to invest in MySQL. The MySQL 5.5 Candidate Release is now available for download. Key features that you’ll note are 200% to 300% improved performance, the InnoDB is now the default engine, backup and recovery are dramatically improved, and the enterprise model is integrated to provide more information about internals and performance.

A little research for the comprehensive new feature list for MySQL 5.5 found a well documented page in the MySQL 5.5 Reference. Examples of things left out of the presentation, not an inclusive list of all features, are: improved operation on the Solaris platform (surprise ;-)), support for semisynchronous replication, support for SQL standard SIGNAL and RESIGNAL statements, support for Unicode character sets, a LOAD XML statement, expanded partitioning options – including the ability to truncate only a partition of a table, and may new command options.

Ronald Bradford gave a good presentation on MySQL idiosyncrasies. I thought spelling out behaviors common to Windows and Mac platforms that differ from Linux and Unix platforms was great. He’s got a new book that he co-authored on MySQL and PHP. I’ve look into the book yet.

I also enjoyed Giuseppe Maxia’s presentation on partitioning. His regular web site is here, but beware if you’ve a slow web connection because the photos delay page loading. ;-)

Update on MySQL Sunday

Here are some links provided by Oracle to presentations:

  • Edward Screven’s presentation
  • Live Webinar: ‚ÄúDelivering Scalability and High Availability with MySQL 5.5 Replication Enhancements

Written by maclochlainn

September 19th, 2010 at 2:30 pm

Posted in InnoDB,LAMP,MAMP,MySQL

PHP Function Primer

without comments

Added a new PHP Function Primer for my students. This one’s not large but fits as a page better. If you’re interested it’s here.

It covers:

  • Function Definitions
  • Creating & Using Functions
  • Pass-by-value Functions
  • Pass-by-reference Functions
  • Dynamic Functions
  • Function Default Parameters
  • Variable-length Parameter Lists

Written by maclochlainn

April 2nd, 2010 at 10:17 pm

Posted in LAMP,MAMP,PHP

PHP Tutorial Available

with 5 comments

I finally got around to writing that PHP Tutorial. It’s a bit large and takes about 10 seconds to load or longer depending on your connection and machine. It covers the basics from writing your first page to loops. It was too large for a blog post, so it’s a blog page. Click on the link if you’d like to check it out.

I plan others on functions, objects, and files. Then, I’ll get to tutorials against databases.

Naturally, suggestions are always welcome.

Written by maclochlainn

March 29th, 2010 at 11:11 am

Posted in LAMP,MAMP,OPAL,PHP

MySQL Standard Group By

with 7 comments

Teaching SQL is interesting because folks try syntax that experience would tell you shouldn’t work. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL. It became clearer to me why it’s there as I did some experimenting with it enabled and disabled. While I’d still argue it’s broken, it’s the only way to get support for advanced aggregation concepts.

The reason that I find that it broken is a matter of perspective not standards. The fact that you can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the GROUP BY clause seemed like a broken behavior. The MySQL behavior is explained in Chapter 11.12.3 of the MySQL 5.1 Reference. What wasn’t clear from the documentation when I read it was why the behavior is allowed. Gary’s comment on the original post made me look more deeply into the behavior.

If you check the referenced documentation, the process is called GROUP BY hidden columns. That label didn’t immediately resonate with me. That’s probably of my background with IBM’s DB2 and Oracle. I’m so accustom to how they work, that sometimes I think they set the standards but they don’t. It turns out that the GROUP BY clause in the ANSI SQL standards doesn’t allow for expressions.

The default configuration of MySQL allows for the expressions (functions) in the GROUP BY clause. The documentation refers to hidden columns, which are in point of fact expressions in the GROUP BY clause. MySQL does support expressions in the GROUP BY by default, and requires, like Oracle, that when you use an expression in the SELECT clause that you mirror it in the GROUP BY clause. This means you group on the result of the expression, not a column in the table or view.

In the default configuration, you have three options. They are determinate results, indeterminate results, and hidden column results. The following cover these components.

Determinate Results

Determinate results are straight forward. They require that all non-aggregated columns in the SELECT clause are mirrored in the GROUP BY clause. This means that the non-aggregated column values are the key upon which results are aggregated.

An example of determinate results is:

1
2
3
4
5
6
SELECT   key_one
,        key_two
,        SUM(counter)
FROM     GROUPING
GROUP BY key_one
,        key_two;

Indeterminate Results

Indeterminate results isn’t straight forward but isn’t hard to grasp. An indeterminate result set is returned when one or more non-aggregated columns in a SELECT clause aren’t listed in the GROUP BY clause. The columns listed in the SELECT clause but excluded from the GROUP BY clause return meaningless values because they’re column values chosen indeterminately from all pre-aggregated rows.

The following query runs in a generically configured MySQL instance without an error. It returns a meaningless key_two column value from the pre-aggregated row set. In the example, the aggregation column counts the unique key_one column values. This behavior makes key_one a determinate value, and key_two an indeterminate value.

1
2
3
4
5
SELECT   key_one
,        key_two
,        SUM(counter)
FROM     GROUPING
GROUP BY key_one;

You can fix this mixed return set by adding the key_two column to the GROUP BY clause, which would return a determinate set. Alternatively, you can prevent the default behavior for the GROUP BY clause by adding the ONLY_FULL_GROUP_BY mode variable to your SQL_MODE system variable.

After setting the SQL_MODE> variable, a GROUP BY must contain all non-aggregated columns. When you make this change to the SQL_MODE system variable, you also disable any queries that use an expression in their GROUP BY clause. Those queries with expressions in the group by will now raise an ERROR 1055 exception, like this:

ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY

If you want to prevent indeterminate results and don't use expressions in the GROUP BY clause, you can add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable. You can do that during a session with the following syntax:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

Or, you can start the mysqld with the following option:

mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"

A better alternative, is to add it to the my.cnf configuration file on Linux, or my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

If you opt for changing it at the server configuration file, you must stop and restart the mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is mysql. If you've set the Windows service to mysql51, then you need to substitute mysql51 for mysql.

Hidden Column Results

A hidden column result, is a result generated by an expression in the GROUP BY clause. The following is a query that lets you sum transactions by the month name. This is supported by the default behavior of hidden columns. The following is an approach that you might find in Oracle because they don't support aggregation by only part of the non-aggregated columns in a query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT   il.MONTH AS "MON-YEAR"
,        il.base AS "BASE"
,        il.plus10 AS "10_PLUS"
,        il.plus20 AS "20_PLUS"
FROM    (SELECT   CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date)) AS MONTH
         ,        MONTH(t.transaction_date) AS sortkey
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
         FROM     TRANSACTION t
         WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
         GROUP BY CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date))
         ,        MONTH(t.transaction_date)) il
ORDER BY il.sortkey;

The interesting thing about MySQL's approach is that the Oracle equivalent of an inline view is unnecessary. You can simply switch the functions like the exmaple below.

1
2
3
4
5
6
7
8
9
10
SELECT   DATE_FORMAT(t.transaction_date,'%m-%Y') AS MONTH
,        MONTH(t.transaction_date) AS sortkey
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
FROM     TRANSACTION t
WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
GROUP BY DATE_FORMAT(t.transaction_date,'%m-%Y')
,        MONTH(t.transaction_date)
ORDER BY sortkey;

When you add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable, you disallow this type of behavior and the query would no longer work. It would return the following error:

ERROR 1055 (42000): 'sampledb.t.transaction_date' isn't in GROUP BY

Written by maclochlainn

March 10th, 2010 at 12:54 am

Posted in LAMP,MAMP,MySQL,sql

Debugging MySQL Procedures

with 2 comments

In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Procedure (or subroutines according to some documentation). You can see this post for how to create an equivalent solutions for MySQL functions.

In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related functions and procedures, and are a corollary to System.out.println() in Java.

Before you can see the output at the command-line in Oracle (that is if you’re not using SQL*Developer or Toad), you must set a SQL*Plus environment variable. These variables don’t exist in MySQL or SQL Server command-line tools because they never served the function of a report writer like SQL*Plus.

You enable output display in Oracle by setting this in SQL*Plus:

SQL> SET SERVEROUTPUT ON SIZE 1000000

You can test your anonymous or named block. Since MySQL doesn’t support anonymous named block, the examples using a trivial procedure that prints Hello World! (orginal, right ;-)).

1
2
3
4
5
6
7
8
9
10
11
12
-- Create a procedure in Oracle.
CREATE OR REPLACE PROCEDURE hello_world IS
BEGIN
  -- Print a word without a line return.
  DBMS_OUTPUT.put('Hello ');
  -- Print the rest of the phrase and a line return.
  DBMS_OUTPUT.put_line('World!');
END;
/
 
-- Call the procedure.
EXECUTE hello_world;

It’s seems useless to print the output because it should be evident. MySQL procedures are a bit different because there’s no OR REPLACE syntax. The equivalent to calling the DBMS_OUTPUT package procedures in MySQL is to simply select a string. Now you can do this with or without the FROM dual clause in MySQL, don’t we wish we could do the same thing in Oracle. :-)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE hello_world' AS "Statement";
DROP PROCEDURE IF EXISTS hello_world;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE hello_world()
BEGIN
  -- Print the phrase and a line return.
  SELECT 'Hello World!';
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL hello_world' AS "Statement";
CALL hello_world();

Originally, I tried to keep this short but somebody wanted an example in a loop. Ouch, loops are so verbose in MySQL. Since I was modifying this post, it seemed like a good idea to put down some guidelines for successful development too.

Guidelines for Development of Procedures

Declaration Guidelines

The sequencing of components in MySQL procedures is important. Unlike, PL/SQL, there’s no declaration block, declarations must be at the top of the execution block. They also must appear in the following order:

  1. Variable declarations must go first, you can assign initial values with the DEFAULT keyword. While not required, you should:
  • Consider using something like lv_ to identify them as local variables for clarity and support of your code.
  • Consider grouping local variables that relate to handlers at the bottom of the list of variables.
  1. After local variables and before handlers, you put your cursor definitions. You should note that MySQL doesn’t support explicit dynamic cursors, which means you can’t define one with a formal signature. However, you do have prepared statements and they mimic dynamic cursor behaviors.
  2. Last in your declaration block, you declare your handler events.

Execution Guidelines

  1. Variable assignments are made one of two ways:
  • You should start each execution block with a START TRANSACTION and then a SAVEPOINT, which ensures the procedure acts like a cohesive programming unit.
  • You assign a left_operand = right_operand; as a statement.
  • You use the SELECT column INTO variable; syntax to filter a value through SQL functions and assign the resulting expression to a local variable.
  • You assign a single row cursor output to variables using a SELECT column INTO variable FROM ....
  1. You must assign values from cursors called in a loop into local variables when you want to use the results in nested SQL statements or loops.
  2. You must reset looping variables, like the fetched control variable at the end of the loop to reuse the handler variable in subsequent loops.
  3. You must assign values to local variables if you want to use them in the exception handler.
  4. If you’ve started a transaction, don’t forget to COMMIT your work.

Exception Guidelines

  1. Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
  2. When you deploy exception blocks, they’re the last element at the bottom of the exception block.
  3. You should consider explicit exception handlers for each error unless the action taken is the same.
  4. You should consider grouping all exception handlers when the action taken is the same.
  5. You should include a ROLLBACK whenever you’ve performed two or more SQL statements that may modify data.

Below is an example for putting debug code inside a loop.

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- Conditionally drop a sample table.
SELECT 'DROP TABLE IF EXISTS sample' AS "Statement";
DROP TABLE IF EXISTS sample;
 
-- Create a table.
CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_msg   VARCHAR(20));
 
-- Insert into sample.
INSERT INTO sample (sample_msg) VALUES
 ('Message #1')
,('Message #2')
,('Message #3');
 
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE debug_loop' AS "Statement";
DROP PROCEDURE IF EXISTS debug_loop;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE debug_loop()
BEGIN
 
  /* Declare a counter variable. */
  DECLARE lv_counter INT DEFAULT 1;
 
  /* Declare local control loop variables. */
  DECLARE lv_sample_id  INT;
  DECLARE lv_sample_msg VARCHAR(20);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE sample_cursor CURSOR FOR
    SELECT * FROM sample;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a sample cursor. */
  OPEN sample_cursor;
  cursor_sample: LOOP
 
    /* Fetch a row at a time. */  
    FETCH sample_cursor
    INTO  lv_sample_id
    ,     lv_sample_msg;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_sample; END IF;
 
    -- Print the cursor values.
    SELECT CONCAT('Row #',lv_counter,' [',lv_sample_id,'][',lv_sample_msg,']') AS "Rows";
 
    -- Increment counter variable.
    SET lv_counter = lv_counter + 1;
 
  END LOOP cursor_sample;
  CLOSE sample_cursor;
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL debug_loop' AS "Statement";
CALL debug_loop();

This post certainly answers the student question. Hopefully, it also helps other who must migrate Oracle skills to MySQL. Since IBM DB2 has introduced a PL/SQL equivalent, wouldn’t it be nice if Oracle did that for MySQL. That is, migrate PL/SQL to MySQL. Don’t tell me if you think that’s a pipe dream, I’d like to hope for that change.

Written by maclochlainn

February 27th, 2010 at 11:01 pm

Multi-row Merge in MySQL

with 3 comments

After I wrote the post for students on the multiple row MERGE statement for an upload through an external table in Oracle, I thought to check how it might be done with MySQL. More or less because I try to keep track of how things are done in several databases.

MySQL’s equivalent to a MERGE statement is an INSERT statement with an ON DUPLICATE KEY clause, which I blogged about a while back. You may also use the REPLACE INTO when you want to merge more than one row. At the time that I wrote this, I thought there wasn’t support for an INSERT ON DUPLICATE KEY clause statement with a subquery but I found that I was wrong. Fortunately, somebody posted a comment to remind me about this and now both solutions are here for anybody that would like them.

The workaround with a VALUES clause was to write a stored procedure with two cursor loops, explicitly pass the values from the cursor to local variables, and then put the local variables in the VALUES clause. I’ll post the other with a subquery soon. On parity, clearly Oracle’s MERGE statement (shown here) is far superior than MySQL’s approach.

Demonstration

Here are the steps to accomplish an import/upload with the INSERT statement and ON DUPLICATE KEY clause. In this example, you upload data from a flat file, or Comma Separated Value (CSV) file to a denormalized table (actually in unnormalized form). This type of file upload transfers information that doesn’t have surrogate key values. You have to create those in the scope of the transformation to the normalized tables.

Step #1 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the C:\Data\Download directory or folder. If you have Windows UAC enabled in Windows Vista or 7, you should disable it before performing this step.

Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.

Narnia, 77600,'Peter the Magnificent',12720320,12920609,
Narnia, 77600,'Edmund the Just',12720320,12920609,
Narnia, 77600,'Susan the Gentle',12720320,12920609,
Narnia, 77600,'Lucy the Valiant',12720320,12920609,
Narnia, 42100,'Peter the Magnificent',15310412,15310531,
Narnia, 42100,'Edmund the Just',15310412,15310531,
Narnia, 42100,'Susan the Gentle',15310412,15310531,
Narnia, 42100,'Lucy the Valiant',15310412,15310531,
Camelot, 15200,'King Arthur',06310310,06861212,
Camelot, 15200,'Sir Lionel',06310310,06861212,
Camelot, 15200,'Sir Bors',06310310,06351212,
Camelot, 15200,'Sir Bors',06400310,06861212,
Camelot, 15200,'Sir Galahad',06310310,06861212,
Camelot, 15200,'Sir Gawain',06310310,06861212,
Camelot, 15200,'Sir Tristram',06310310,06861212,
Camelot, 15200,'Sir Percival',06310310,06861212,
Camelot, 15200,'Sir Lancelot',06700930,06821212,

Step #2 : Connect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

mysql -ustudent -p

Connect to the sampledb database, like so:

mysql> USE sampledb;

Step #3 : Run the script that creates tables and sequences

Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

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
-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Conditionally drop objects.
SELECT 'KINGDOM' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM;
 
SELECT 'KNIGHT' AS "Drop Table";
DROP TABLE IF EXISTS KNIGHT;
 
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM_KNIGHT_IMPORT;
 
-- Create normalized kingdom table.
SELECT 'KINGDOM' AS "Create Table";
CREATE TABLE kingdom
( kingdom_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, kingdom_name  VARCHAR(20)
, population    INT UNSIGNED) ENGINE=INNODB;
 
-- Create normalized knight table.
SELECT 'KNIGHT' AS "Create Table";
CREATE TABLE knight
( knight_id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, knight_name           VARCHAR(24)
, kingdom_allegiance_id INT UNSIGNED
, allegiance_start_date DATE
, allegiance_end_date   DATE
, CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom (kingdom_id)) ENGINE=INNODB;
 
-- Create external import table in memory only - disappears after rebooting the mysqld service.
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table";
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR(20)
, population            INT UNSIGNED
, knight_name           VARCHAR(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE) ENGINE=MEMORY;

Step #4 : Load the data into your target upload table

There a number of things that could go wrong but when you choose LOCAL there generally aren’t any problems. Run the following query from the student account while using the sampledb database, and check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6
LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Step #5 : Create the upload procedure

Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.

Then, run it as the student account. As you look at the structure to achieve this simple thing, the long standing complaint about PL/SQL being a verbose language comes to mind. Clearly, stored procedures are new to MySQL but they’re quite a bit more verbose than PL/SQL.

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_kingdom_id            INT UNSIGNED;
  DECLARE lv_kingdom_name          VARCHAR(20);
  DECLARE lv_population            INT UNSIGNED;
  DECLARE lv_knight_id             INT UNSIGNED;
  DECLARE lv_knight_name           VARCHAR(24);
  DECLARE lv_kingdom_allegiance_id INT UNSIGNED;
  DECLARE lv_allegiance_start_date DATE;
  DECLARE lv_allegiance_end_date   DATE;
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
  DECLARE fetched       INT DEFAULT 0;
 
  /* Cursors must come after variables and before event handlers. */
 
  /* Declare a SQL cursor with a left join on the natural key. */  
  DECLARE kingdom_cursor CURSOR FOR
    SELECT   DISTINCT
             k.kingdom_id
    ,        kki.kingdom_name
    ,        kki.population
    FROM     kingdom_knight_import kki LEFT JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population; 
 
  /* Declare a SQL cursor with a join on the natural key. */  
  DECLARE knight_cursor CURSOR FOR
    SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn 
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date; 
 
  /* Event handlers must always be last in the declaration section. */
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Open a local cursor. */  
  OPEN kingdom_cursor;
  cursor_kingdom: LOOP
 
    FETCH kingdom_cursor
    INTO  lv_kingdom_id
    ,     lv_kingdom_name
    ,     lv_population;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_kingdom; END IF;
 
    INSERT INTO kingdom
    VALUES
    ( lv_kingdom_id
    , lv_kingdom_name
    , lv_population ) 
    ON DUPLICATE KEY
    UPDATE kingdom_name = lv_kingdom_name;
 
  END LOOP cursor_kingdom;
  CLOSE kingdom_cursor;
 
  /* Reset the continue handler to zero. */
  SET fetched = 0;  
 
  /* Open a local cursor. */  
  OPEN knight_cursor;
  cursor_knight: LOOP
 
    /* Fetch records until they're all read, and a NOT FOUND SET is returned. */  
    FETCH knight_cursor
    INTO  lv_knight_id
    ,     lv_knight_name
    ,     lv_kingdom_allegiance_id
    ,     lv_allegiance_start_date
    ,     lv_allegiance_end_date;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_knight; END IF;
 
    INSERT INTO knight
    VALUES
    ( lv_knight_id
    , lv_knight_name
    , lv_kingdom_allegiance_id
    , lv_allegiance_start_date
    , lv_allegiance_end_date ) 
    ON DUPLICATE KEY
    UPDATE knight_name = lv_knight_name;
 
  END LOOP cursor_knight;
  CLOSE knight_cursor;
 
  /* Reset the continue handler to zero. */
  SET fetched = 0;  
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Here’s the better option with an embedded query:

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
77
78
79
80
81
-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Using subqueries update the targets. */  
  INSERT INTO knight
  ( SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date )
  ON DUPLICATE KEY
  UPDATE knight_id = kn.knight_id;
 
  INSERT INTO knight
  ( SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date )
  ON DUPLICATE KEY
  UPDATE knight_id = kn.knight_id;
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Step #6 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

CALL upload_kingdom;

Step #7 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

-- Check the kingdom table.
SELECT * FROM kingdom;
SELECT * FROM knight;

It should display the following information:

+------------+--------------+------------+
| kingdom_id | kingdom_name | population |
+------------+--------------+------------+
|          1 | Narnia       |      77600 |
|          2 | Narnia       |      42100 |
|          3 | Camelot      |      15200 |
+------------+--------------+------------+
 
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
| knight_id | knight_name             | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
|         1 | 'Peter the Magnificent' |                     1 | 1272-03-20            | 1292-06-09          |
|         2 | 'Edmund the Just'       |                     1 | 1272-03-20            | 1292-06-09          |
|         3 | 'Susan the Gentle'      |                     1 | 1272-03-20            | 1292-06-09          |
|         4 | 'Lucy the Valiant'      |                     1 | 1272-03-20            | 1292-06-09          |
|         5 | 'Peter the Magnificent' |                     2 | 1531-04-12            | 1531-05-31          |
|         6 | 'Edmund the Just'       |                     2 | 1531-04-12            | 1531-05-31          |
|         7 | 'Susan the Gentle'      |                     2 | 1531-04-12            | 1531-05-31          |
|         8 | 'Lucy the Valiant'      |                     2 | 1531-04-12            | 1531-05-31          |
|         9 | 'King Arthur'           |                     3 | 0631-03-10            | 0686-12-12          |
|        10 | 'Sir Lionel'            |                     3 | 0631-03-10            | 0686-12-12          |
|        11 | 'Sir Bors'              |                     3 | 0631-03-10            | 0635-12-12          |
|        12 | 'Sir Bors'              |                     3 | 0640-03-10            | 0686-12-12          |
|        13 | 'Sir Galahad'           |                     3 | 0631-03-10            | 0686-12-12          |
|        14 | 'Sir Gawain'            |                     3 | 0631-03-10            | 0686-12-12          |
|        15 | 'Sir Tristram'          |                     3 | 0631-03-10            | 0686-12-12          |
|        16 | 'Sir Percival'          |                     3 | 0631-03-10            | 0686-12-12          |
|        17 | 'Sir Lancelot'          |                     3 | 0670-09-30            | 0682-12-12          |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

Written by maclochlainn

February 24th, 2010 at 9:15 am