How to Disable/Enable Triggers on Demand

Permalink: http://bit.ly/1kBCcQu



One of the sorely wanted features missing in MySQL is the ability to disable/enable triggers on the fly. By comparison, disabling/enabling Foreign Key constraints can be simply done by setting a server system variable:

SET FOREIGN_KEY_CHECKS = [TRUE|FALSE];

Now as of version 5.6, there is no built-in server system variable TRIGGER_CHECKS in MySQL. A simple workaround is to instead use a user-defined session variable. The setting for trigger checks stored into the session variable allows the setting to be seen by all statements, including all stored procedures and functions, as long as the user is connected, which for this workaround is in effect similar to using a server system variable.

Besides a session variable that switches the checks for all triggers, to make the control more flexible, we can also set a session variable for each trigger type. There is a total of 6 trigger types, a combination of [BEFORE|AFTER] with [INSERT|UPDATE|DELETE]. The total to consider would be 7 session variables:

#Affects all triggers 
#FALSE value overrides trigger type settings
SET @TRIGGER_CHECKS = [TRUE|FALSE];

#Trigger type settings
SET @TRIGGER_BEFORE_INSERT_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_INSERT_CHECKS = [TRUE|FALSE];
SET @TRIGGER_BEFORE_UPDATE_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_UPDATE_CHECKS = [TRUE|FALSE];
SET @TRIGGER_BEFORE_DELETE_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_DELETE_CHECKS = [TRUE|FALSE];

For the disable/enable logic, the only value for the session variables that matters is the FALSE value. Anything that isn't FALSE or 0 is considered TRUE. Even NULL values are to be considered TRUE. It makes sense that the default value for trigger checks should be TRUE, and when the user disconnects, the session variable is reset back to NULL which re-enables trigger checking.

Implementing the ability to disable/enable on existing triggers could be hard if there are several hundred tables that need to be altered and with each table having their own unique triggers. This could especially be painful if there is no easy way to automate the updating of those existing triggers.

Triggers are special stored procedures and to implement the trigger disable/enable functionality, a simple logic needs to be inserted at the BEGIN statement. Here's an example of an existing BEFORE-INSERT TRIGGER type that has been patched so that it can be disabled/enabled:

DELIMITER $$
DROP TRIGGER IF EXISTS `yearCheck_beforeInsert` $$
CREATE DEFINER=`root`@`localhost` TRIGGER `yearCheck_beforeInsert`
BEFORE INSERT ON `movies` FOR EACH ROW

#Patch starts here
thisTrigger: BEGIN
  IF ((@TRIGGER_CHECKS = FALSE)
      OR (@TRIGGER_BEFORE_INSERT_CHECKS = FALSE))
    AND (USER() = 'root@localhost')
  THEN
    LEAVE thisTrigger;
  END IF;

#Original trigger body starts here
  IF (NEW.YEAR > YEAR(NOW()))
  THEN
    SET NEW.YEAR = NULL;
  END IF;
END $$
DELIMITER ;

Here's the rundown on the workaround code patch:

• The BEGIN statement needs to have a label in order to LEAVE the TRIGGER if the trigger checking is SET to FALSE. This facilitates the patching of the functionality into existing TRIGGERs without needing to wrap the original body of the TRIGGER inside the IF-THEN construct.
• The appropriate session variable trigger type needs to be correctly chosen, in order for the specific control variable to be able to turn on/off all the triggers intended for it. In this example, it is correctly coded as  @TRIGGER_BEFORE_INSERT_CHECKS.
• We wouldn't want just any user to be able to disable TRIGGERs. Unfortunately, GRANT PRIVILEGES can't cover this case. Either we would need to hardcode the specific USER/s that can disable triggers into the TRIGGER procedure in order to have security or allow only USERs that have the SUPER privilege since disabling triggers is an administrative operation (see this comment for the condition change).

Triggers can CALL stored procedures and its possible that the trigger logic be moved into an external SP. However, this is not recommended since it would not be possible to use the special trigger-specific variables OLD.<columnName> and NEW.<columnName> in the SP.



When the database administrator logs into the MySQL server under the user 'root@localhost', they can run DML statements without invoking any trigger by setting the @TRIGGER_CHECKS session variable to FALSE. Once done, setting it back to TRUE re-enables triggers and they can continue with other tasks that require triggers to fire. They can simply disconnect immediately without setting the check back to TRUE since in their next session the triggers are enabled by default.

Another workaround on a missing MySQL functionality, support for dynamic cursors, can be found here.

Dynamic Cursor Fix

Permalink: http://bit.ly/1pl6kz5



This is the workaround to create dynamic cursors in MySQL and is meant to be used together with the general purpose dynamic cursor stored procedure.

Increase the compared value of colValN in the WHILE-DO construct to the maximum number of columns your dynamic cursor will be needing. This fixes MySQL's limitation on cursors by providing the dynamic cursor with consistent column names that are independent of the underlying SELECT statement and also provide a predictable number of columns.

The final result is that the temporarily created table will be constant, while the records that the cursor will traverse will remain dynamic. The code in part 3 of the general purpose dynamic cursor together with this fix creates a working dynamic cursor that can be called multiple times using different SELECT statement parameters with no stale table data returned.

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursorFix` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursorFix`(
 IN tableName VARCHAR(64))
BEGIN
 DECLARE columnNo TINYINT DEFAULT 0;
 DECLARE columnName TEXT;
 DECLARE noMoreColumns BOOLEAN DEFAULT FALSE;
 DECLARE columnNameCursor CURSOR FOR
 SELECT `COLUMN_NAME`
  FROM `information_schema`.`COLUMNS`
  WHERE (`TABLE_SCHEMA` = DATABASE())
   AND (`TABLE_NAME` = tableName);
 DECLARE CONTINUE HANDLER
  FOR 1329
  SET noMoreColumns = TRUE;
 OPEN columnNameCursor;
 columnNameCursorLoop: LOOP
 FETCH columnNameCursor INTO columnName;
  IF (noMoreColumns)
  THEN
   CLOSE columnNameCursor;
   LEAVE columnNameCursorLoop;
  END IF;
  SET columnNo = columnNo + 1;
  SET @changeColStmt = CONCAT(
   'ALTER TABLE `', tableName, '`
   CHANGE `', columnName, '` `column', columnNo, '` TEXT');
  PREPARE changeColStmt FROM @changeColStmt;
  EXECUTE changeColStmt;
  DEALLOCATE PREPARE changeColStmt;
 END LOOP columnNameCursorLoop;
 #Increase as needed by the dynamic cursor
 WHILE columnNo < 3
 DO
  SET columnNo = columnNo + 1;
  SET @addColStmt = CONCAT(
   'ALTER TABLE `', tableName, '`
   ADD `column', columnNo, '` TINYINT');
  PREPARE addColStmt FROM @addColStmt;
  EXECUTE addColStmt;
  DEALLOCATE PREPARE addColStmt;
 END WHILE;
END $$
DELIMITER ;

Call this fix from 'dynamicCursor' by passing the table name of its temporarily created table 'dynamic_cursor'. This same fix should be called by any nested dynamic cursor like 'dynamicCursorNested' and passing the nested dynamic cursor's temporarily created table name such as 'dynamic_cursor_nested'. See the 3rd part of the general purpose dynamic cursor for an explanation on how to nest dynamic cursors.

See part 1 for the rationale on creating a dynamic cursor. Temporary tables are not the way to go due to MySQL's limitation of hiding temporary tables from the `information_schema` database. Part 2 discusses how MySQL might fetch stale table data if different SELECT statements are passed to a dynamic cursor. This problem also occurs when the tables referred to by a fixed cursor's SELECT statement are altered by changing the table's columns.



This fix applies for the final iteration found in part 3 of the general purpose dynamic cursor blog entry and by continuing to use normal (not temporary) tables, the column names are made visible in the `information_schema` database.

A General Purpose Dynamic Cursor - Part 3 of 3

Permalink: http://bit.ly/1jxurF0



See part 1 for the rationale behind the code and part 2 to understand limitations faced by cursors in MySQL.

The code snippet below shows an example that performs an action with the cursor data as well as a good way to debug the stored procedure. This iteration also works around MySQL's cursor problem mentioned in part 2 by calling the dynamic cursor fix stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
 IN selectStmt TEXT,
 IN whatAction VARCHAR(255),
 INOUT inOutParam VARCHAR(255))
BEGIN
 #Used by whatAction ='debug'
 DECLARE cursorRead TEXT DEFAULT '';
 #Cursor-specific variables
 DECLARE colVal1, colVal2, colVal3 TEXT;
 DECLARE noMoreRows BOOLEAN DEFAULT FALSE;
 DECLARE dynamicCursor CURSOR FOR
  SELECT *
  FROM `dynamic_cursor`;
 DECLARE CONTINUE HANDLER
  FOR 1329
 SET noMoreRows = TRUE;
 DROP TABLE IF EXISTS `dynamic_cursor`;
 SET @createTableStmt = CONCAT(
  'CREATE TABLE `dynamic_cursor` AS ',
  selectStmt);
 PREPARE createTableStmt FROM @createTableStmt;
 EXECUTE createTableStmt;
 DEALLOCATE PREPARE createTableStmt;
 CALL dynamicCursorFix('dynamic_cursor');
 OPEN dynamicCursor;
 dynamicCursorLoop: LOOP
 FETCH dynamicCursor INTO colVal1, colVal2, colVal3;
  IF (noMoreRows)
  THEN
   CLOSE dynamicCursor;
   LEAVE dynamicCursorLoop;
  END IF;
  CASE whatAction
  WHEN 'debug'
  THEN
   SET cursorRead = CONCAT_WS(', ', 
     cursorRead, colVal1, colVal2, colVal3);
  ELSE
   BEGIN
   END;
  END CASE;
 END LOOP dynamicCursorLoop;
 CASE whatAction
 WHEN 'debug'
 THEN
  SELECT cursorRead 'Values read by the cursor';
 ELSE
  BEGIN
  END;
 END CASE;
 DROP TABLE `dynamic_cursor`;
END $$
DELIMITER ;

Important: The workaround for MySQL's cursor limitation that causes cursors to read stale table data is implemented in a stored procedure dynamicCursorFix(). This solution is implemented externally from the dynamic cursor so that it may be called by other nested dynamic cursors if needed. You can find this fix at this link: http://mysql-0v34c10ck.blogspot.com/2011/05/dynamic-cursor-fix.html.

You can test the function and/or debug the dynamic cursor by passing the following SELECT statement and specifying the 'debug' action:

CALL dynamicCursor(
 "SELECT T.`TABLE_NAME`
  FROM `information_schema`.`TABLES` T
  WHERE `TABLE_SCHEMA` = DATABASE()",
 'debug',
 @NULL);

The INOUT parameter is not used by the 'debug' action so we have set it to a dummy variable @NULL. For the above given SELECT statement, expect the result set to contain 'dynamic_cursor' together with any existing tables in the current database to confirm that the stored procedure is working. This also demonstrates how to implement actions that will be performed when the cursor fetches data.

You will want to replace the SELECT statement to a specific SELECT statement you are analyzing to pass to the dynamic cursor. Remember that a temporarily created table is present inside the current database and you may want to filter it out of your SELECT statement by adding a WHERE clause that excludes 'dynamic_cursor%' or as the case may be.



You may opt to nest several dynamic cursors inside the case for the 'whatAction' parameter if needed. Simply copy the stored procedure and change its name to `dynamicCursorNested` or the like, and the table name `dynamic_cursor` to another name such as `dynamic_cursor_nested`. The nested cursors will need to depend on the same fix mentioned above.

Return to part 1 or part 2 or see another workaround on a missing MySQL feature, disabling/enabling triggers on demand.

A General Purpose Dynamic Cursor - Part 2 of 3

Permalink: http://bit.ly/RcRieg



Refer to part 1 for the rationale behind the code or you can skip to part 3 for a working example as well as how you can debug the stored procedure.

Important: The SP will create a table named `dynamic_cursor`. Make sure this table does not exist in the database where you will be storing the procedure. Here's the 1st iteration of a general purpose dynamic cursor:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
 IN selectStmt TEXT,
 IN whatAction VARCHAR(255),
 INOUT inOutParam VARCHAR(255))
BEGIN
 DECLARE colVal1, colVal2, colVal3 TEXT;
 DECLARE colCount TINYINT;
 DECLARE noMoreRows BOOLEAN DEFAULT FALSE;
 DECLARE dynamicCursor CURSOR FOR
  SELECT *
  FROM `dynamic_cursor`;
 DECLARE CONTINUE HANDLER
  FOR 1329
  SET noMoreRows = TRUE;
 DROP TABLE IF EXISTS `dynamic_cursor`;
 SET @createTableStmt = CONCAT(
  'CREATE TABLE `dynamic_cursor` AS ',
  selectStmt);
 PREPARE createTableStmt FROM @createTableStmt;
 EXECUTE createTableStmt;
 DEALLOCATE PREPARE createTableStmt;
 SELECT COUNT(*)
 FROM `information_schema`.`COLUMNS`
 WHERE (`TABLE_SCHEMA` = DATABASE())
  AND (`TABLE_NAME` = 'dynamic_cursor')
 INTO colCount;
 OPEN dynamicCursor;
 dynamicCursorLoop: LOOP
  CASE colCount
   WHEN 1 THEN
    FETCH dynamicCursor INTO colVal1;
   WHEN 2 THEN
    FETCH dynamicCursor INTO colVal1, colVal2;
   WHEN 3 THEN
    FETCH dynamicCursor INTO colVal1, colVal2, colVal3;
  END CASE;
  IF (noMoreRows)
  THEN
   CLOSE dynamicCursor;
   LEAVE dynamicCursorLoop;
  END IF;
  CASE whatAction
  WHEN 'doSomething'
  THEN
   BEGIN
   END;
  ELSE
   BEGIN
   END;
  END CASE;
 END LOOP dynamicCursorLoop;
 DROP TABLE `dynamic_cursor`;
END $$
DELIMITER ;

Regarding MySQL's limitations on cursors: As of version 5.5, the above stored procedure will only reliably work on the first call in the current session. Subsequent calls may fail if the SELECT statement passed is different from the first one. This is because the cursor is traversing the column names from the first call and is not aware that the columns it's supposed to read are now different. The cursor reads its own copy of the table defined from the first call, which results to stale data when the columns of the table are subsequently altered. Part 3 implements the workaround to this inherent MySQL cursor limitation. The bug is due to MySQL's optimization on the SELECT statement where it wrongly assumes that the columns are still the same.

The code snippet can accept a SELECT statement parameter that will produce a result set from 1-3 columns. You may want to increase the maximum to around 12 columns or even more. Simply increase the number of ColValN variables and create additional cases for colCount and write their FETCH statements.

The intention here is that the statements to execute using the data fetched from the cursor can be written inside the cases for the whatAction string parameter. You may want to create additional CASE constructs before the cursor's OPEN and/or after the LOOP is ended depending on your specific needs.

Additionally, you can add additional INOUT parameters as needed like inOutParam1, inOutParam2, ... inOutParamN. However, keep in mind that the procedure relies on MySQL's automatic data type conversion. This means that in order to keep the procedure able to accept a broad range of inputs and maintain its general purpose nature, you need to keep using generic data types such as VARCHAR or TEXT. The data will be converted at run-time if it isn't a VARCHAR or TEXT variable.



Part 3 shows an example that performs an action with the cursor data as well as a good way to debug the stored procedure.

Return to part 1 for the rationale behind the code.



Updated: 10/22/2012

A General Purpose Dynamic Cursor - Part 1 of 3

Permalink: http://bit.ly/TBKQL3



Skip to part 2 for the code snippet or to part 3 for a working example and how to debug the stored procedure.

Also another workaround on a missing MySQL functionality, enabling/disabling triggers, can be found here.

As of version 5.5, MySQL still does not have the native ability to execute a dynamic cursor. This can be worked around but the resulting stored procedure will have a few limitations.

This stored procedure is a general purpose dynamic cursor. It is general purpose, in the sense that, it qualifies as a template that you can modify to suite a specific purpose and/or expand to cover a broader range. It can be called by passing the following parameters:

CALL dynamicCursor(
 "SELECT * FROM `dbName`.`tableName`", 
 'whatAction', 
 @inOutParam);

The first parameter is the SELECT statement that the cursor is declared to use. Quotation marks can be used to encapsulate the string so that single quotes can be used by the statement itself if needed.

The second parameter is a string that specifies what action to do with the cursor traversal data-- the statements to execute are written inside the stored procedure as cases of the second parameter. What you do with the data is up to you.

The third parameter is an INOUT variable which will allow you to get a return or to pass additional parameters as required by your particular case. You can expand the number of INOUT variables to the needed amount by simply adding more in the stored procedure. If a return or passing additional variables are not needed, simply set the INOUT variable to a dummy variable such as @NULL.

The dynamic cursor logic can be achieved through a dynamic MySQL statement that creates a copy of the data from the SELECT statement of the cursor declaration into a created table. The cursor, instead of using the SELECT statement, is declared to SELECT the table that was created. This causes the table data to become the part which is dynamic and not the cursor itself.

The creation of this table then stems to two methods: create the table as a temporary table or to create the table as a normal (not temporary) table. Both methods have limitations:

• If a normal table is created, MySQL will see it as a DML statement. An implicit commit will be triggered if this stored procedure is used inside transactions.
• If a temporary table is created, it will be invisible for SHOW TABLES, SHOW OPEN TABLES, SHOW TABLE STATUS, and more importantly as stated in a previous blog entry, "it is not possible to get a column list into a variable if it comes from a temporary table. This is due to the fact that temporary tables are invisible within the `information_schema` database." This means that the cursor's FETCH statement will need to have a fixed number of variables.
• Both methods have a performance impact on the system since we are creating a new table and copying data from a SELECT statement each time the stored procedure is called.

The advantages between the two are:

• By creating a normal table, we can determine the number of columns from the `information_schema` database. The FETCH statement can be nested inside a condition construct and fetch the cursor traversal into the appropriate number of variables up to a reasonable maximum quantity.
• By creating a temporary table, the stored procedure can be used inside transactions without causing an implicit commit.

Due to the fact that using the method of creating a temporary table will work only for SELECT statements with a fixed or predetermined number of columns, it defeats the purpose of being dynamic. A temporary table is not the way to go unless you absolutely need a dynamic cursor for a transaction.

The normal table method is what we will use, however, there is the issue of the maximum number of variables that the cursor's FETCH statement should have for practical coding purposes. This is because FETCH statements are not supported by prepared statements which can dynamically write the specific number of variables that need to be created and fetch data into. For example, an InnoDB table can permit up to a maximum of 1,000 columns. To simplify, this equates to 1,000 cases for each of the possible number of columns. It is not practical to write all the variables for each of the cases and the 1,000 cases themselves. It would be better to write a non-dynamic cursor construct for SELECT statements which produce result sets with a large number of columns.

Therefore, a decision as to when it is practical to use a dynamic cursor and when to use a fixed cursor needs to be made. A dynamic cursor should be used only for SELECT statements that will produce a small result set. The result set should also have a practical number of columns. Consider using a fixed cursor if these two conditions are not met.

The second part of this blog entry shows the code for a general purpose dynamic cursor stored procedure and the third part shows a working example and how to debug the cursor.

Updated: 10/20/2012