Alex Stetsenko

Recursion in MySQL stored procedures is allowed but disabled by default.

So if you are using recursion and getting error like ‘Recursive limit 0 was exceeded for routine TestMyRecursion’, just enable recursion by setting the max_sp_recursion_depth server system variable to a value greater than zero:

SET @@SESSION.max_sp_recursion_depth = 100;
CALL TestMyRecursion();
SET @@SESSION.max_sp_recursion_depth = 0;

The other day I was asked what happens to already invoked stored procedure if it is being altered or dropped. And common sense says that altering and even dropping of stored routine should not affect already invoked instances as their results.

Unfortunately, I did not manage to find anything in MySQL manual to prove that. However I did the following test.

Step 1. Declare stored procedure which has sleep() inside to make execution time long enough to be able to run another query.

DROP PROCEDURE IF EXISTS Procedure_AlterDropTest;
 
CREATE PROCEDURE Procedure_AlterDropTest()
BEGIN
    SELECT SLEEP(5);
END;

Step 2. Call created procedure.

CALL Procedure_AlterDropTest();

Step 3. Modify procedure by changing return to 1 instead of 0 (yes, sleep() returns 0).

DROP PROCEDURE IF EXISTS Procedure_AlterDropTest;
 
CREATE PROCEDURE Procedure_AlterDropTest()
BEGIN
    SELECT 1;
END;

As expected result was the following: already invoked stored procedure was successfully running and returned 0 even though it was dropped and modified to return 1.

There is no built-in function to split comma-separated string in MySQL. However it is possible to google lots of different ways to do that.

Here it is how it was done in my case when comma-separated list of integer IDs had been provided to be processed inside another stored procedure. But it should be pretty simple to adjust this procedure if comma-separated values are not numbers but for example words.

CREATE PROCEDURE String_Split
(
  vString VARCHAR(8000),
  vSeparator VARCHAR(5)
)
BEGIN
 
DECLARE vDone tinyint(1) DEFAULT 1;
DECLARE vIndex INT DEFAULT 1;
DECLARE vSubString VARCHAR(15);
 
DROP TABLE IF EXISTS tmpIDList;
CREATE TEMPORARY TABLE tmpIDList (ID INT);
 
WHILE vDone > 0 DO
  SET vSubString = SUBSTRING(vString, vIndex,
                    IF(LOCATE(vSeparator, vString, vIndex) > 0,
                      LOCATE(vSeparator, vString, vIndex) - vIndex,
                      LENGTH(vString)
                    ));
  IF LENGTH(vSubString) > 0 THEN
      SET vIndex = vIndex + LENGTH(vSubString) + 1;
      INSERT INTO tmpIDList VALUES (vSubString);
  ELSE
      SET vDone = 0;
  END IF;
END WHILE;
 
END;

So table tmpIDList contains all values and can be used in any SQL query later (and then dropped).

If it is required to insert into table hundreds of thousand records (for any reason), just keep in mind that the query below works faster:

INSERT INTO `User` (ID, Name)
    VALUES (1, 'Jack'), (2, 'Jacob'), (3, 'John');

than one by one insert like this:

INSERT INTO `User` (ID, Name) VALUES (1, 'Jack');
INSERT INTO `User` (ID, Name) VALUES (2, 'Jacob');
INSERT INTO `User` (ID, Name) VALUES (3, 'John');

Usually MySQL GUI tools support export of table data as one by one insert. But still it is helpful ‘trick’ in case you need to generate tons of test data.

From time to time I am asked to advise a good administration and development tool for MySQL.

Of course, there is an official MySQL GUI Tools Bundle for 5.0. But is it the best option to manage MySQL database? Unfortunately, I doubt it.

For instance, I have found three much more interesting tools: SQLyog, EMS MySQL Manager and Toad for MySQL.

SQLyog and EMS MySQL Manager are good tools unless you want to save your budget for something else. It shouldn’t be a big deal for business but for an independent developer could be quite important.

So the best option in my opinion is Toad for MySQL. Granted it has everything required for database development (basically as any other mentioned above tool) but it also has perfect user interface and ‘look and feel’. You will understand that especially if you had to switch to MySQL development after experience with either MS Query Analyzer or MS SQL Server Management Studio.

There is also EMS MySQL Manager Lite which is freeware too. But it has limits on database size as well as all concept of EMS user interface ‘logic’ is completely different which can be uncomfortable for many developers. But also keep in mind that EMS releases comprehensive MySQL products family.

For the moment I cannot advise any tool for Mac or Linux but phpMyAdmin will work for 100% 😉 .

Below is Toad for MySQL screenshot.

P.S.: I am still looking for anything worthy for MySQL routines development and version control. I.e. something similar or better than MS Visual Studio Database Projects. There was no sense of such tools before since MySQL did not support stored procedures, functions and views. But now it would be good to have tools for database code management.

Like row maximum and minimum there is another interesting function GROUP_CONCAT which returns values inside group as separated string. For example:

SELECT UserType, GROUP_CONCAT(ID) FROM `User` GROUP BY UserType;

will return:
1 | 1,2,3,4,5
2 | 6,7,8,9,10

Also it is possible to set custom separator (comma by default), sort order and eliminate repeated values by using DISTINCT:

SELECT
  UserType,
  GROUP_CONCAT(DISTINCT County ORDER BY Country SEPARATOR '; ')
FROM `User`
GROUP BY UserType;

But draw attention that GROUP_CONCAT has length limit 1024 symbols. It is possible to change that limit by updating global variable group_concat_max_len.

Just reminder that MySQL statement for selecting more than one field into variables should look like below:

SELECT ID, `Name` INTO UserID, UserName FROM `User` WHERE ID = 100;

I.e. single INTO per SELECT statement instead of own INTO for each field.

There is a good MySQL string function QUOTE(str) which quotes a string to produce a result that enclosed by single quotes and with each instance of single quote (“’”) or backslash (“\”) preceded by a backslash.

Here is detailed description and simple usage example. But I found this function very helpful in dynamic SQL where string variable is used:

SET @SQL = CONCAT('INSERT INTO SomeTable (Field0, Field1, Field2)
SELECT ', QUOTE(someStringVariable), ', Field1, Field2
FROM AnotherTable');
 
PREPARE query FROM @SQL;
EXECURE query;
DEALLOCATE PREPARE query;

Here using QOUTE(str) helps to avoid writing SQL like below (no worry about quotes):

SET @SQL = CONCAT('INSERT INTO SomeTable (Field0, Field1, Field2)
SELECT \'', someStringVariable, '\', Field1, Field2
FROM AnotherTable');

as well as SQL statement will not fail in case someStringVariable contains single quote or backslash.

The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command:

SET profiling = 1;

Two things happen once you issue this command. First, any query you issue from this point on will be traced by the server with various performance diagnostics being created and attached to each distinct query. Second, a memory table named profiling is created in the INFORMATION_SCHEMA database for your particular session (not viewable by any other MySQL session) that stores all the SQL diagnostic results. This table remains persistent until you disconnect from MySQL at which point it is destroyed.

Read more here – Using the New MySQL Query Profiler.

Robin Schumacher, MySQL’s Director of Product Management overviews MySQL 6.0’s backup and restore utility.

MySQL 6.0’s backup/restore utility is a completely new tool in the server that allows for backup and restore commands to be issued right from a mysql client command prompt, which means no shelling out to an operating system prompt and/or scripting shell scripts to run a backup.

The syntax in the current alpha release looks like the following:

BACKUP {DATABASE | SCHEMA} { * | db_name [, db_name] ... }
TO 'image_file_name'
[WITH COMPRESSION [COMPRESSION_ALGORITHM [=] algorithm_name]];
 
RESTORE FROM 'image_file_name';