Archive for the ‘mysql’ tag
Placement over substance
I was stunned when a SQL query raised an ERROR 1630 (42000)
telling me the SUM
function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM
function must be on the same line as the SUM
keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM
function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT
function and it seems to me that this parsing inconsistency is problematic.
Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE
settings. This assumption was borne out by Kolbe Kegel’s comment on this post, and there are 30 remaining built in functions that have specialized parsing and resolution markers.
A simplified version of the code that raises the error follows. As you’ll notice the opening parenthesis for the FORMAT
and SUM
function have intervening white space and a line return.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT t.transaction_account AS "Transaction" , LPAD(FORMAT (SUM (CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND EXTRACT(YEAR FROM transaction_date) = 2011 THEN CASE WHEN t.transaction_type = cl.common_lookup_type THEN t.transaction_amount ELSE t.transaction_amount * -1 END END),2),10,' ') AS "JAN" FROM TRANSACTION t CROSS JOIN common_lookup cl WHERE cl.common_lookup_table = 'TRANSACTION' AND cl.common_lookup_column = 'TRANSACTION_TYPE' AND cl.common_lookup_type = 'DEBIT' GROUP BY t.transaction_account; |
Based on the comments, the SQL_MODE is:
mysql> SELECT @@version, @@sql_mode; +-----------+----------------------------------------------------------------+ | @@version | @@sql_mode | +-----------+----------------------------------------------------------------+ | 5.5.23 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------+----------------------------------------------------------------+ 1 ROW IN SET (0.00 sec) |
It raises the following error:
ERROR 1630 (42000): FUNCTION studentdb.SUM does NOT exist. CHECK the 'Function Name Parsing and Resolution' SECTION IN the Reference Manual |
Moving ONLY the opening parenthesis to the end of the SUM
keyword (or removing the line return and white space from between the SUM
keyword and opening parenthesis) prevents the error but it would be more convenient if it supported both approaches. It seems odd that an intervening line return and white space for the SUM
function raises an exception while the same intervening line return and white space doesn’t raise an exception for the FORMAT
function. It strikes me the parser should support both or reject both. Here’s the fixed code that works without enabling the IGNORE_SPACE
SQL Mode option.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT t.transaction_account AS "Transaction" , LPAD(FORMAT (SUM( CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND EXTRACT(YEAR FROM transaction_date) = 2011 THEN CASE WHEN t.transaction_type = cl.common_lookup_type THEN t.transaction_amount ELSE t.transaction_amount * -1 END END),2),10,' ') AS "JAN" FROM TRANSACTION t CROSS JOIN common_lookup cl WHERE cl.common_lookup_table = 'TRANSACTION' AND cl.common_lookup_column = 'TRANSACTION_TYPE' AND cl.common_lookup_type = 'DEBIT' GROUP BY t.transaction_account; |
As noted by the comments, adding the IGNORE_SPACE
to the SQL_MODE
lets both queries work without moving the open parenthesis. You can do that in a session with the following syntax (which is covered in an older post):
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE')); |
Hope this helps folks…
Implicit Commit Functions?
Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT
, UPDATE
, and DELETE
. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT
statement or a Data Definition Language (DDL) statement (CREATE
, ALTER
, DROP
, or RENAME
) inside a MySQL function. The actual error message displayed is:
ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER. |
While an explicit COMMIT
is obvious when placed inside a function, the implicit COMMIT
statement isn’t obvious unless you know a DDL statement generates one. This means you can’t include any DDL statement inside a stored function.
The following example shows how a DDL statement creates an immediate implicit COMMIT
. It requires two sessions and Transaction Control Language (TCL) statements.
In session one, create a table, start a transaction scope, and insert one row into the table:
-- Create a table. CREATE TABLE message ( message_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , message_text VARCHAR(20)); -- Start a transaction context. BEGIN WORK; -- Insert a row into the MESSAGE table. INSERT INTO message (message_text) VALUES ('Implicit Commit?'); |
In session two, the empty set is displayed when you query the MESSAGE
table. Returning to session one, add a new column to the MESSAGE
table with this statement:
ALTER TABLE message ADD (sent DATETIME); |
The ALTER
statement automatically makes current any pending data changes, effectively committing the record from session one. You can return to the second session and query the table you get the following results:
+------------+------------------+------+ | message_id | message_text | sent | +------------+------------------+------+ | 1 | Implicit Commit? | NULL | +------------+------------------+------+ |
When you embed DML statements inside functions, they rely on automatic commit behaviors or an external transaction scope. This function compiles and returns 1 when successful and 0 when unsuccessful, which effectively controls all error conditions within the function:
CREATE FUNCTION modifies_data ( pv_message CHAR(20) ) RETURNS INT MODIFIES SQL DATA BEGIN /* Declare Boolean-like variables as FALSE. */ DECLARE lv_return_value INT DEFAULT FALSE; DECLARE lv_error_value INT DEFAULT FALSE; /* Declare a generic exit handler to reset error control variable to true. */ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET lv_error_value := TRUE; /* Insert statement with auto commit enabled. */ INSERT INTO message (message_text) VALUES (pv_message); /* True unless the CONTINUE HANDLER disables the error control variable. */ IF lv_error_value = FALSE THEN SET lv_return_value := TRUE; END IF; /* Return local variable. */ RETURN lv_return_value; END; $$ |
With auto commit enabled, any call to the function in a query writes a row to the table. If you start a transaction and in the scope of the transaction test the function in one session and query the table in another you’ll see that transaction control can be managed outside the function. You can also manage the transaction control inside a stored procedure, which holds all the TCL commands. An example follows:
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 | CREATE PROCEDURE test_modifies_data ( pv_message CHAR(20) ) MODIFIES SQL DATA BEGIN /* Declare a control variable that manages transaction success or failure. */ DECLARE lv_success_value INT DEFAULT FALSE; /* Start a transaction context. */ START TRANSACTION; /* Set a SAVEPOINT in the transaction context. */ SAVEPOINT before_transaction; /* Call the function. */ SET lv_success_value := modifies_data(pv_message); /* Check the status of the control variable, and commit or rollback the transaction. */ IF lv_success_value = TRUE THEN COMMIT; ELSE ROLLBACK TO before_transaction; END IF; END; $$ |
Test the two with the following call to the procedure:
CALL test_modifies_data('Agent Coulson'); |
Answers my question and I hope it helps others.
Reset MySQL root Password
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.
- The quick and easy way to disable security and reset the
root
password.
You add the following parameter to the my.ini
configuration file in the [mysqld]
block. While you’re editing the configuration file, you should also enter the other two. You’ll uncomment them in subsequent steps because they’re necessary to connect via a localhost
OS pipe when you suppress the listener.
[mysqld] # These let you safely reset the lost root password. skip-grant-tables #enable-named-pipe #skip-networking |
After you’ve saved these changes in the my.ini
file, you should stop and restart the mysql51
service. If you named the Microsoft service something else, you should substitute it for mysql51
in the sample statements. The command-line steps are:
To stop the service:
net stop mysql51 |
To start the service:
net start mysql51 |
Now you can sign on as the root
(superuser) without a password and change the password. However, you can’t do it through the normal command:
SET PASSWORD FOR 'student'@'%' = password('cangetin'); |
If you attempt that normal syntax, MySQL raises the following exception:
ERROR 1290 (HY000): The MySQL server IS running WITH the --skip-grant-tables option so it cannot execute this statement |
You need to first connect to the mysql
database, which holds the data dictionary or catalog. Then, you use a simple UPDATE
statement to reset the root
password.
-- 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'; |
- The secure way to disable security and reset the
root
password.
Remove the comment marks before the enable-named-pipe
and skip-networking
, if you added all three parameters while testing the easy way. Otherwise you should add the following three parameters to the my.ini
configuration file in the [mysqld]
block. The enable-named-pipe
opens an OS pipe
through which you can connect to the database. The skip-networking
instructs the database not to start the MySQL listener.
[mysqld] # These let you safely reset the lost root password. skip-grant-tables enable-named-pipe skip-networking |
After you’ve saved these changes in the my.ini
file, you should stop and restart the mysql51
service. The command-line steps are:
To stop the service:
net stop mysql51 |
To start the service:
net start mysql51 |
You still can’t reset a password with the SET PASSWORD FOR 'user'@'host'
syntax when you’ve disabled reading the database instance’s metadata. The syntax to connect to the database through the OS pipe as the unauthenticated root
user is:
mysql -h . -uroot |
Unfortunately, once you’ve connected, you can’t reset the password through the normal command because that’s disabled by the skip-grant-tables
parameter. Check the example in the quick and easy way above.
With the data dictionary validation disabled, you need to first connect to the mysql
database to make this change. The mysql
database holds the data dictionary or catalog. You use a simple UPDATE
statement to reset the root
password once connected to the mysql
database.
-- 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.
Debugging MySQL Functions
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.
Session Variable Debugging ↓
Expand this section to see the steps for debugging functions with session variables.
- Create two session level variables, like these:
SET @counter := 0; SET @msg := ''; |
- Create a function that uses the
SELECT-INTO
statement to collect and store debugging information during function execution.
CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; SELECT CONCAT('[Debug #',@counter,']') INTO @msg; RETURN 1; END; $$ |
- Run the function and then query the session variable for results
SELECT debugger(); SELECT @msg; |
You’ll see the following text:
+------------+ | @msg | +------------+ | [Debug #1] | +------------+ |
Temporary Table Debugging ↓
Expand this section to see the steps for debugging functions with session variables.
- Only when you want a counter, create one session level variables.
SET @counter := 0; |
- Create an in-memory table to store debugging information from function execution.
CREATE TABLE debugger ( debug_comment CHAR(80)) ENGINE=MEMORY; |
- Create a function that supports inserts into the in-memory table. Naturally, you may need to make the columns larger when your debugging results are large. I’ve found that 80 characters is generally adequate for most debugging exercises.
1 2 3 4 5 6 7 | CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']')); RETURN 1; END; $$ |
- Call the function and query the debugging results.
SELECT debugger(); SELECT debugger(); SELECT debugger(); SELECT debug_comment FROM debugger; |
You’ll see the following text:
+---------------+ | debug_comment | +---------------+ | [Debug #1] | | [Debug #2] | | [Debug #3] | +---------------+ |
Complete Code Sample ↓
Expand this section to see the sample working code for all examples.
This script creates, runs, and tests the code from the above discussions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | -- Conditionally drop the function when it exists. DROP FUNCTION IF EXISTS debugger; -- Set delimiter to create a function with semicolon statment terminators. DELIMITER $$ -- Create a function that returns 1. CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT '[Debug #1]'; RETURN 1; END; $$ -- Reset the delimiter to enable normal execution. DELIMITER ; -- Declare session level variables. SET @counter := 0; SET @msg := ''; -- Conditionally drop the function when it exists. DROP FUNCTION IF EXISTS debugger; -- Set delimiter to create a function with semicolon statment terminators. DELIMITER $$ -- Create a function that writes to local session variables. CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; SELECT CONCAT('[Debug #',@counter,']') INTO @msg; RETURN 1; END; $$ -- Reset the delimiter to enable normal execution. DELIMITER ; -- Test the function code and read the session-level variable contents. SELECT debugger(); SELECT @msg; -- Declare a session level variable. SET @counter := 0; -- Conditionally drop the function when it exists. DROP TABLE IF EXISTS debugger; -- Create a temporary (in-memory) table to record debugging information. CREATE TABLE debugger ( debug_comment CHAR(80)) ENGINE=MEMORY; -- Conditionally drop the function when it exists. DROP FUNCTION IF EXISTS debugger; -- Set delimiter to create a function with semicolon statment terminators. DELIMITER $$ -- Create a function that writes to a debugging table. CREATE FUNCTION debugger() RETURNS INT BEGIN SELECT @counter + 1 INTO @counter; INSERT INTO debugger VALUES (CONCAT('[Debug #',@counter,']')); RETURN 1; END; $$ -- Reset the delimiter to enable normal execution. DELIMITER ; -- Test the function code and read the session-level variable contents. SELECT debugger(); SELECT debugger(); SELECT debugger(); SELECT debug_comment FROM debugger; |
Debugging MySQL Procedures
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:
- 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.
- 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.
- Last in your declaration block, you declare your handler events.
Execution Guidelines
- Variable assignments are made one of two ways:
- You should start each execution block with a
START TRANSACTION
and then aSAVEPOINT
, 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 ...
.
- 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.
- You must reset looping variables, like the
fetched
control variable at the end of the loop to reuse the handler variable in subsequent loops. - You must assign values to local variables if you want to use them in the exception handler.
- If you’ve started a transaction, don’t forget to
COMMIT
your work.
Exception Guidelines
- Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
- When you deploy exception blocks, they’re the last element at the bottom of the exception block.
- You should consider explicit exception handlers for each error unless the action taken is the same.
- You should consider grouping all exception handlers when the action taken is the same.
- 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.