Alex Stetsenko

A while ago I was involved in the technical part of an interview with a PHP developer. Obviously some questions were about MySQL. For instance – how to add a new column to existing table. The candidate tried to convince us that there was no way to do that except creating another table with new column and copy data from old table there. Sure thing that we advised him to read about ALTER command.

However it turned out that suggested ‘method’ could have sense. According to Chris at Everything MySQL blog a full dump and reload to a table with new column may be much more faster than alter table! For instance there is shown a test with 5Gb table and alter table is 47% slower. Of course such ‘alternative alter’ should be used only against slave as well as some other restrictions may apply.

Below is the suggested process.

1. If you have a slave, AND YOU SHOULD AT THIS POINT, run stop slave
2. Run the SELECT INTO OUTFILE local, if you have the space, or over nfs (not very fast)
3. DROP or RENAME THE TABLE you are trying to ALTER
– RENAME only if you have the space and you don’t trust THE PROCESS!
4. CREATE the same table with the ALTERATIONS, if you dropped, or a new table with the ALTERATIONS
5. IMPORT the file from step 2
6. Either RENAME the two tables, if you renamed, or start slave
7. Fail over the writes to the newly altered slave
8. Repeat steps 1 – 7 for the old master

SQL Antipatterns is Bill Karwin’s presentation which not only covers the most common mistakes in database designing but also suggests several ways to solve them.

There might be many ways to force leading zeros to be displayed when string length is less than expected.

One of them is to use string function LPAD(). For example, the following query:

SELECT LPAD('1101', 8, '0');

returns ’00001101′.

However, if string (first parameter) is longer than length (second parameter), the return value is shortened. For example:

SELECT LPAD('1101', 2, '0');

returns ’11′.

Sometimes junior database developers are being confused by field types like INT(2). Does it mean 2 bytes or 2 digits only?

However it is just optional field width which in conjunction with the optional extension attribute ZEROFILL allows to replace the default padding of spaces with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004.

Therefore the display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

But according to MySQL reference manual there is a possibility of problems when MySQL generates temporary tables for some complicated joins and larger values than the display width are stored in an integer column, because in these cases MySQL assumes that the data fits into the original column width.

Time zone support is a typical part of many web applications. But not every application takes into account daylight savings time when +1 is added to hour difference during summer time only.

When PHP allows to identify DST simply by calling something like:

print date('I', strtotime('2009-03-11 00:00:00'));

it may not be obvious that:

SELECT CONVERT_TZ('2009-03-11 00:00:00', 'GMT', 'EST');

and

SELECT CONVERT_TZ('2009-03-11 00:00:00', 'GMT', 'US/Eastern');

return different results:

• ’2009-03-11 05:00:00′ in case of ‘EST’ and
• ’2009-03-11 04:00:00′ in case of ‘US/Eastern’.

This article explains how to make work CONVERT_TZ() function (which returns NULL by default) and stay current with time zone changes.

P.S.: There are some time zones with minute adjustment like UTC+5:45 or UTC+9:30 as well as with difference more than 12 hours from GMT like UTC+13 or UTC+14.

Now MySQL allows executing tasks that run according to a schedule:

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

Here is a sample SQL to create an event:

CREATE EVENT e_hourly
    ON SCHEDULE 
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

Read more in MySQL reference manual: 19.4. Using the Event Scheduler as well as how this feature was originally designed by Andrey Hristov when he was working on his master’s thesis.

I’ve already written a post about MySQL GUI Tool for Windows.

In case you need to manage or support MySQL database on Mac OS X, Sequel Pro may be a good option. However it does not support stored routines manipulation. But hopefully it will be fixed in the next release.

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