MacLochlainns Weblog

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.

The original blog post with the images was causing a page loading delay, so I’ve moved the post to a page of it’s own. This link send you to the original post with how you setup MySQL Connector/ODBC driver, add a new data source in Windows 7, and a new data import source in Excel 2007. As with other “how-to” posts, it includes screen shots to clear up any ambiguity. I apologize for any inconvenience caused by moving the content.

If you want instructions for using Excel to access an Oracle database, check this older post. I’m working on documentation for a native Mac OS X and Excel 2011 solution and will update this when it’s ready.

As always, I hope this helps those trying to sort through how this works. Naturally, a Visual Basic for Applications (VBA) solution is a better alternative once you’ve set up the data source. This is also on my calendar for documentation.

I’d hoped for a better solution with Excel 2011 on Mac OS X but it appears we still need the third party drivers to connect Excel to MySQL. It’s the same as I reported earlier on Excel 2008. Here’s the message and you can click on it to go the web site and links to buy the drivers.

I tried getting away with installing the MySQL Connector/ODBC before downloading one of those recommended by Microsoft. I discovered that it didn’t work.

Therefore, I download and installed the OpenLink Software ODBC Software, instructions with screen shots are in this blog page. I discovered that this software support track isn’t purely native Mac OS X on Intel-based software because it requires Rosetta like Excel 2008. It also only connects with Microsoft Query, which appears to be a Power PC native application too, at least based on the interface and look and feel. I didn’t do much more research because I’ve concluded that connectivity isn’t ready for prime time on the Mac OS X platform. Microsoft still has more work to do.

Installing MySQL 5.5.9 (updated for MySQL 5.5.16 and 5.5.18) on Mac OS X was on my list but it finally made the top. I needed to write instructions for a class that I teach because asking students who own a Mac to install VMWare and Windows before MySQL seems an unnecessary burden. Especially when you can install it directly on Mac OS X.

You can download MySQL for the Mac OS X. I downloaded the Mac OS X, Version 10.6 (x86, 64-bit) version for this installation. There were a couple shell syntax tricks beyond the instructions and then you need to configure database. That’s required because the database is installed with an unsecured anonymous and root account. After the step-by-step installation instructions, you’ll find the configuration steps to enable you to access the MySQL Monitor from a terminal session. It also configures your $PATH environment to enable you to start and stop the MySQL Daemon.

1. After the download completes, open the file folder in the download directory.

2. The open file folder and it will look like the following. Launch the mysql-5.5.9-osx 10.6-x86_64.pkg file, which installs the product.

3. After launching the executable, you are now on the first page of the Install MySQL 5.5.9 installation application. Click the Continue button.

4. This page contains the instructions, you can pause to read them or continue with these instructions. Click the Continue button to proceed.

5. This page contains the General Public License (GPL). You agree or stop the installation. Click the Continue button to proceed.

6. The following overlay dialog contains your agreement. Click the Agree button to proceed.

7. There are fewer options in this installation than the Windows installation. While you can change the installation location, the software installs by default in the /usr/local/mysql directory. The installation requires that you have a mysql user account on the operating system, and you don’t need to do anything because one exists as part of the default Mac OS X installation. Click the Install button to proceed.

8. This dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

9. Depending on the system, this could take more than a minute. All you can do it wait.

10. This page tells you that you’ve completed the installation. Click the Close button to proceed.

11. This step requires that you return to the download folder, shown in Step #2 above. Launch the MySQLStartupItem.pkg and you’ll see the following MySQL Startup Item Installer dialog. Click the Continue button to proceed.

12. This page contains the instructions for the MySQL Startup software, you can pause to read them or continue with these instructions. Click the Continue button to proceed.

13. This page asks if you want to change the directory. I’d recommend you leave it as the default because it requires the System Administrator’s password to start and stop the database. It should also start for you every time you boot the machine. Click the Continue button to proceed.

14. Like Step #8, this dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

15. You could see a progress dialog box but generally it happens so fast you should land at the Installation was Successful dialog. Click the Close button to proceed.

16. This step requires that you return to the download folder, shown in Step #2 above. Launch the MySQL.prefPane and you’ll see the following MySQL Preferences dialog. Click the Install button to proceed.

17. Like Step #8 and #14, this dialog requires the system administrator’s password. Enter the valid password and click the OK button to proceed.

18. Don’t click in the automatic start button unless you’re sure. Otherwise, there’s a lot of cleanup to be able to return to this point and start or stop the server as required. This is the last screen, you should click the Start MySQL Server button to start MySQL. While installing MySQL 5.5.18 I discovered that the service menu is no longer installed in the Preferences, and you must start it manually.

You can start and stop the service by opening your System Preferences, where you’ll find them in the bottom Other row. If the intent was to have it start automatically, sometimes the permissions are incorrect. You’ll get the following error in MySQL 5.5.9 but not in MySQL 5.5.16 because the MySQL DMG file is fixed. You can skip the instructions until you get to Configure User’s Shell Environment below:

"/Library/StartupItems/MySQLCOM" has not been started because it does not have the proper security settings.

You can fix this by changing the permissions manually in a Terminal Session. Launch a Terminal Session from your Utilities folder inside your Applications folder.

Change directory to the /Library/StartupItems/MySQLCOM directory. Then, list the files. These commands should do the trick for you:

cd /Library/StartupItems/MySQLCOM
ls -al

If you see these permissions, you have problem because the group for startup files should be wheel not staff:

drwxr-xr-x  4 root  staff   136 Jan 20 13:46 .
drwxr-xr-x  4 root  wheel   136 Feb  9 21:11 ..
-rwxr-xr-x  1 root  staff  1300 Jan 20 13:46 MySQLCOM
-rw-r--r--  1 root  staff   469 Jan 20 13:46 StartupParameters.plist

You can change the files with this command:

sudo chown root:wheel *

However, now you need to step up the directory tree one level to /Library/StartupItems, and change the ownership of:

drwxr-xr-x   4 root  staff   136 Jan 20 13:46 MySQLCOM

There are two commands to do this. The first changes directory by moving to the parent directory in the hierarchy (the parent directory is represented by two dots). The second changes the group ownership of the MySQLCOM directory.

cd ..
sudo chown root:wheel MySQLCOM

Now you need to configure your shell environment and harden the database. Hardening means securing accounts with passwords. They’re covered in the next two sections.

Configure User’s Shell Environment

Assuming you accepted the defaults, you should be able to copy the required instructions directly into a .bash_login file if one exists. Unless you’ve created one before there won’t be a file. Mac OS X doesn’t automatically create the file. If you don’t have the file, you can create one with the following syntax:

touch .bash_login

You can open the file with the vi editor or a text editor of your choice. MySQL 5.5.9 installed in /usr/local/mysql, which has changed to /usr/local/mysql-version as noted below. You can copy the following contents into the file for MySQL 5.5.16 but will need to change the file for earlier or later releases (added sudo per Don McArthur’s comment):

# Set the MySQL Home environment variable to point to the root directory of the MySQL installation.
export set MYSQL_HOME=/usr/local/mysql-5.5.16-osx10.6-x86_64
 
# Add the /bin directory from the MYSQL_HOME location into your $PATH environment variable.
export set PATH=$PATH:$MYSQL_HOME/bin
 
# Create aliases that make it easier for you to manually start and stop the MySQL Daemon.
alias mysqlstart="sudo /Library/StartupItems/MySQLCOM start"
alias mysqlstop="sudo /Library/StartupItems/MySQLCOM stop"

Changes between MySQL 5.5.16 and 5.5.18 introduce a new directory structure, you need to use the following in the .bash_login file (added sudo and status per Don McArthur’s comments):

# Set the MySQL Home environment variable to point to the root directory of the MySQL installation.
export set MYSQL_HOME=/usr/local/mysql-5.5.18-osx10.6-x86_64
 
# Add the /bin directory from the MYSQL_HOME location into your $PATH environment variable.
export set PATH=$PATH:$MYSQL_HOME/bin
 
# Create aliases that make it easier for you to manually start and stop the MySQL Daemon.
alias mysqlstart="sudo /Library/StartupItems/MySQLCOM/MySQLCOM start"
alias mysqlstop="sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop"
alias mysqlstatus="ps aux | grep mysql | grep -v grep"

As pointed out by Shashank’s comment, you should now use the following aliases:

alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start'
alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop'

You need to save the file and close and restart a new Terminal session to place these environment variables in scope. You could also run the following to put them in scope without closing and opening the terminal:

. ./.bash_login

The preceding command sources the environment file into active memory. This should configure your environment. After restarting the shell, you should be able to run this command to confirm the new environment:

which -a mysql

It should return:

/usr/local/mysql-5.5.16-osx10.6-x86_64/bin/mysql

Secure the Database

This is presently necessary because of the different file structure in a Mac OS X MySQL install, which disables the mysql_secure_installation file from running successfully. You can manually edit the file or follow these steps.

You need to connect to the database as the privileged super user, root user. This is simple because the installation doesn’t set any passwords. You open another Terminal session to make these changes or you could install MyPHPAdmin or MySQL Workbench. The tools work as well in fixing the majority of issues.

mysql -uroot

Once connected to the database as the root user, you can confirm that passwords aren’t set and an insecure anonymous user account has been previously configured. You do that by connecting to the mysql database, which is the database catalog for MySQL. You do that by running the following command:

USE mysql;

You can query the result set with the following query:

SELECT USER, password, host FROM USER\G

You should see the following output plus the user’s name preceding the MacPro (or iMac.local) host name value:

*************************** 1. row ***************************
    user: root
password: 
    host: localhost
*************************** 2. row ***************************
    user: root
password: 
    host: MacPro.local
*************************** 3. row ***************************
    user: root
password: 
    host: 127.0.0.1
*************************** 4. row ***************************
    user: root
password: 
    host: ::1
*************************** 5. row ***************************
    user: 
password: 
    host: localhost
*************************** 6. row ***************************
    user: 
password: 
    host: MacPro.local

You now need to change the password for the root user. I would suggest that you do this with the SQL command rather than a direct update against the data dictionary tables. The syntax to fix the root user account require you enter the user name, an @ symbol, and complete host values, like:

SET PASSWORD FOR 'root'@'localhost' = password('cangetin');
SET PASSWORD FOR 'root'@'MacPro.local' = password('cangetin');
SET PASSWORD FOR 'root'@'127.0.0.1' = password('cangetin');
SET PASSWORD FOR 'root'@'::1' = password('cangetin');

You should be able to drop both anonymous user rows with the following syntax, but I did encounter a problem. Assuming you may likewise encounter the problem, the fix follows the first commands you should try:

DROP USER ''@'localhost';
DROP USER ''@'MacPro.local';

If either of the anonymous accounts remain in the USER table, you can manually drop them from the database catalog. This syntax will get rid of them:

DELETE FROM USER WHERE LENGTH(USER) = 0;

You’ve completed the configuration and can now type quit; to exit the MySQL Monitor. To reconnect, you’ll now need a password, like this:

mysql -uroot -pcangetin

Also, don’t forget to use a real password. The one shown here is trivial, which means easy to hack. Use something that others might not guess.

Configure my.cnf file

You can copy one of the sample configuration files as a starting point (as provided by Don McArthur’s comment):

sudo cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf

Starting and Stopping the Database

You can manually start the database server with the following command, which you defined as aliases in your .bash_login shell script:

mysqlstart

Stopping it is also straightforward, you do this:

mysqlstop

You can check it’s status with this command:

mysqlstatus

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";

2. 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";

3. 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.

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.

Somebody asked if you really have to run the MySQL Configuration Wizard when you want to shut out network connections. The answer is no.

If you want to secure the database server to perform maintenance, you can comment out the port assignment line in the [mysqld] section and add the following in the same section:

# The TCP/IP Port the MySQL Server will listen on
# port=3306

# Instruct it to skip networking and enable named pipes.
skip-networking
enable-named-pipe
 
# Define the Pipe the MySQL Server will use.
socket=mysql

This allows only users on the local system to connect to the database. You can test it by running the following PHP program as a command-line process form the server.

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @mysqli_connect("localhost", "username", "password", "somedb");
  if (!$c) {
    print "Sorry! The connection to the database failed.";
    die();
  }
  else {
    // Initialize a statement in the scope of the connection.
    print "Congrats! You've connected to a MySQL database!";
  }
?>

You call a command-line PHP program like this:

php phpConnect.php

It would fail when you call it from the Apache web server’s htdocs folder because network communication across TCP/IP is closed. Only local sockets are available across the mysql pipe. There’s no magic to the pipe name of mysql but it’s the default pipe name convention.

Sometimes the MySQL installation goes great but students forget their root password. It’s almost like the DBA who has the only copy of the root user’s password getting hit by a bus. How do you recover it? It’s not terribly difficult when deployed on the Windows OS (you’ll find a nice article on Linux here). This page takes you to standard documentation for resetting permissions.

There are two ways to do it. The first is quick and easy but risks letting others into the database through the network. The second requires a bit more work but ensures that network is shut while you disable security to reset the root password.

1. The quick and easy way to disable security and reset the root password.

[mysqld]
 
# These let you safely reset the lost root password.
skip-grant-tables
#enable-named-pipe
#skip-networking

net stop mysql51

net start mysql51

SET PASSWORD FOR 'student'@'%' = password('cangetin');

ERROR 1290 (HY000): The MySQL server IS running WITH the --skip-grant-tables option so it cannot execute this statement

-- Connect to the data dictionary.
USE mysql
 
-- Manually update the data dictionary entry.
UPDATE USER
SET    password = password('cangetin')
WHERE  USER = 'root'
AND    host = 'localhost';

2. The secure way to disable security and reset the root password.

[mysqld]
 
# These let you safely reset the lost root password.
skip-grant-tables
enable-named-pipe
skip-networking

net stop mysql51

net start mysql51

mysql -h . -uroot

-- Connect to the data dictionary.
USE mysql
 
-- Manually update the data dictionary entry.
UPDATE USER
SET    password = password('cangetin')
WHERE  USER = 'root'
AND    host = 'localhost';

After you’ve updated the password, remove the previous statement lines from the my.ini file. Then, reboot the server.

Hope this helps a few people.

Having downloaded and tested some basics of the MySQL 5.5 Release Candidate, I started checking out the new features. While testing the new LOAD XML LOCAL INFILE feature, I discovered that there are restrictions governing the configuration of source XML files.

You must restrict the XML file to a list of tag names that correspond to column names within a tag defined as <row>. The tag names are case sensitive to your column names. You can replace the <row> tag name with any name of your choosing provided you append a clause that maps rows to your substitution XML tag name.

You can’t convert a file that has multiple child XML tags with the same name. Any attempt simply loads the last tag name found in the row hierarchy. Therefore, you should ensure that all source files have a unique list of case-sensitive child tags that map to the column definitions of the import table.

Either of the following table definition provides for lowercase column names. The first one uses nothing to delimit the column names.

CREATE TABLE CHARACTER
( ROLE CHAR(30) NOT NULL
, actor CHAR(30) NOT NULL
, part CHAR(20) NOT NULL
, film CHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you run SHOW CREATE TABLE character; you would see the more conventional definition below.

CREATE TABLE `character`
( `role` CHAR(30) NOT NULL
, `actor` CHAR(30) NOT NULL
, `part` CHAR(20) NOT NULL
, `film` CHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The following source file format supports the table definition because the XML tags are lowercase names. The source file wouldn’t work if the column names where uppercase or mixed case names.

<row>
  <role>Indiana Jones</role>
  <actor>Harrison Ford</actor>
  <part>protagonist</part>
  <film>Indiana Jones and Raiders of the Lost Ark</film>
  <film>Indiana Jones and the Temple of Doom</film>
  <film>Indiana Jones and the Last Crusade</film>
</row>

When the XML tags and column names match in case, you can load the file with the following syntax:

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`;

If you name the <row> tag <character>, you need to add a clause as noted below.

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`
ROWS IDENTIFIED BY '<character>';

If you have two film tags in a row tag, MySQL 5.5 doesn’t raise an error or warning. It simply loads the last <film> value. If you create tables with column names that don’t match on a case-sensitive basis, you’ll receive a 1263 warning message. You can see the warning message with the following command.

SHOW warnings;

The warning message only occurs when a column is not null constrained and the column name fails to match an XML tag attribute in the source file. No error or warning is raised when a column isn’t not null constrained under the same scenario. You can test it and then show warnings.

You should see something like this:

+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Role' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Actor' at ROW 1  |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Part' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Film' at ROW 1   |
+---------+------+---------------------------------------------------------------------------------+

When you define a table with the AUTO_INCREMENT set to a value other than 1, the LOAD XML LOCAL FILE command resets the sequence to 1 before loading. I’d recommend you import into a table without an auto incrementing column and then use the REPLACE INTO to set the surrogate key values of auto incremented columns.

The last day of Oracle Open World 2010. My focus today was on attending OracleDevelop and JavaOne.

The weather picked up today and it was a nice warm Indian Summer day. There were lots of tourists out too. The photo is taken of the building where you board the Powell Street cable car. There was a lot of walking with the way the events are dispersed among the Moscone South, Moscone West, Marriott Hotel, Westin Hotel, Hilton Hotel, W Hotel, and Hotel Nikko. I can’t quite remember how many times I walked back and forth across the 6 blocks between the Moscone centers and Union Square hotels. I can tell you that we went only twice to Mel’s Drive-in.

It was amazing to see how quickly the various conference expedition centers shutdown, packed up, and had their materials shipped out. We had to step over all the plywood that protected tiles and carpets to attend events.

Day 4 also brought a smaller audience for venues. I’m not quite sure if they left earlier or slept in because they were out too late last night attending the Wednesday night event on Treasure Island. The reduced number of attendees was great for those of us who remained. You can see how few attended the .NET/Oracle hands-on lab in the Hilton, which made finding a nice spot easy. You can find the .NET/Oracle hands-on tutorial materials on the Oracle Technical site.

If you opt to use the tutorials, you may benefit from these hints. You should be able to avoid some of the issues that I ran into when working through the open labs. First, you should expand the Microsoft Studio to full screen. Second, you should look for context pop-ups attached to a small arrow at the top right corner of grids, et cetera. Lastly, there are a few small mistakes that you’ll need to work through. Look at the errors as an opportunity to think and experiment and they’re great basic .NET/Oracle tutorials.

Oracle Open World 2010 is done. Time to review the keynotes for those things that I missed while listening to them, and consider the new role of Tuxedo in the life of Oracle’s product stack. It’s also time to download and play with the MySQL 5.5 candidate release; and it’s time to kick off my shoes, put up my feet, and play with the technology again.

Tomorrow I turn my fate over to the airlines, and hope to arrive home on schedule.