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 got a coupon for a free 16 oz. Cocoa Butter (thank you, Publix!).

What do you think cocoa butter is? I expected something eatable…

… And went to butter\margarine section immediately. Nope… Then tried baking section. There were different kinds of chocolate and some cocoa. Oops, no cocoa butter agian.
Hmm… Almost forgot about it. And suddenly it was found exactly in skin care section 🙂 .

So right answer – cocoa butter is a skin lotion:

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.

It looks like State Farm’s “Sixteen” commercial became quite popular because of soundtrack. So the song is now available for free download on their web site.

Enjoy!

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

Everybody knows Skype, everybody recommends Skype. For sure I have been using Skype for years and has been talking for hours. My first video call was with Skype. Undoubtedly it is excellent application for a voice and video communication.

But it is going to be quite expensive if you need to make calls to a regular phone. That is where 12Viop could help. First of all their rates are twice lower (of course it varies depending on destination country). Second, it is possible to test it for free by making calls to various popular destinations. Third, 12Voip allows to make calls via regular phone with the Phone-to-Phone feature.

However bad news are that their rates are in euro which means that price may vary.

Enjoy your calls whatever application you use!