Automating mantenance tasks on MySQL

I'm trying to automate some trivial maintenance tasks for my own MySQL server, and trying also to minimize the effort, so ... Here is the recipe:

Take an excellent generalized stored procedure like the one by Konstantin Osipov, see "Dynamic SQL is Stored Procedures" on MySQLForge (example 4).
Tune it a bit so that it takes into account only non system tables (I'm trying to turn it into something similar to Microsoft's sp_MSforeachtable), here is the code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`sp_4_each_table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_4_each_table`(db_name VARCHAR(64), template VARCHAR(21845))
BEGIN
#
DECLARE done INT DEFAULT 0;
#
DECLARE tname VARCHAR(64);
#
DECLARE c CURSOR FOR
#
SELECT table_name FROM information_schema.TABLES WHERE table_schema=db_name AND table_type = 'BASE TABLE';
#
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done= 1;
#
 
#
OPEN c;
#
REPEAT
#
FETCH c INTO tname;
#
SET @stmt_text=REPLACE(template, " ?", CONCAT(" ", tname));
#
BEGIN
#
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
#
SELECT CONCAT("'", @stmt_text, "' command failed") AS "error";
#
PREPARE stmt FROM @stmt_text;
#
EXECUTE stmt;
#
DEALLOCATE prepare stmt;
#
END;
#
UNTIL done END REPEAT;
#
END $$
 
DELIMITER ; 

Now with the procedure at hand I'm going on with the scheduling part, taking advantage of the great new "EVENT" feature of MySQL 5.1, see it in action:
First of all check if events are enabled;

Code:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.18-beta-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

Add the event to the database, we'll activate events later

Code:

mysql> create event analyze_all
-> on schedule every 1 day
-> starts timestamp '2007-05-27 23:59:59'
-> ends timestamp '2007-05-27 23:59:59' + interval 1 year
-> on completion preserve
-> comment 'updates stats by analyzing tables'
-> do call sp_4_each_table('test', 'analyze table ?');
Query OK, 0 rows affected (0.03 sec)

Check it's existance from the related information_schema table:

Code:

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+---------+
| event_name  | event_type | status  |
+-------------+------------+---------+
| analyze_all | RECURRING  | ENABLED |
+-------------+------------+---------+
1 row in set (0.02 sec)

Ok, the event is here.
Now I'll have to enable events on the server and let it rip, it's very simple
just issue an:

Code:

SET GLOBAL event_scheduler=1;

Or start the server with the:

Code:

--event_scheduler=1

Stopping an event without deleting it's also very simple:

Code:

mysql> alter event analyze_all disable;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+----------+
| event_name  | event_type | status   |
+-------------+------------+----------+
| analyze_all | RECURRING  | DISABLED |
+-------------+------------+----------+
1 row in set (0.00 sec)

Sooner or later I'll show you how this, with "federated tables", "csv storage engine" and "upserts" can really ease and improve an ETL process.

Featured on MySQL Newsletter June 2007. WOW !!!

MYSQLDUMP, mind your options!!

In the process of upgrading my desktop installation of MySQL I noticed that, unlike triggers, stored procedures are not automatically dumped, that's a "not so nice" feature of mysqldump, sic, here is a snippet for those who don't have time to check the manual

C:\Documents and Settings\user>mysqldump -uroot --single-transaction --routines
--databases test remote_test > c:/backup_mysql_20_05_07.sql

-- routines does the trick.
--single-transaction is InnoDB specific for a consistent backup (doesn't apply to MyISAM and other table handlers)
--databases is used because I want to backup only a specific number of databases

Note that after MySQL 5.0.11 triggers are dumped automagically (option enabled by default), use --skip-triggers if downgrading your table structure.

Note also that before 5.0.11 triggers were not dumped and routines had to wait till 5.0.13 (well, 5.0.20 for routines with DEFINER ...) so act accordingly!!!

Another trick, you can use mysqldump to copy database structure from one db or server to another this way:

C:\Documents and Settings\pcuser>mysqldump -uusername -ppassword -d sourcedatabase -n | mysql -uusername -ppassword targetdatabase

This is common on *nix, but it works in windows too.
Key is the
-n no create database
parameter

SAP BW, IF for your BEx formulas

Have you ever felt the need for an IF in your BEx formulas? Didn't find it? That's because SAP expects you to know about boolean math ...
See, we are subtracting two quantities and we want our result to be shown as 0 if the actual result is negative, the trick is very simple, look at this example:

( ( 'GR quantity' - 'PO quantity' ) > 0 ) * ( 'GR quantity' - 'PO quantity' )

The first block will evaluate to 1 when GR quantity is greater than PO quantity and to 0 when it's smaller, so the result will be something like:

GR quantity: 6
PO quantity: 4

(( 6 - 1) > 0) * (6 - 4) will turn into 1 (true) * (6 - 4) = 2

The other case:

GR quantity: 4
PO quantity: 7

(( 4 - 7) > 0) * (4 - 7) will turn into 0 (false) * (4 - 7) = 0