Sunday, March 09, 2008

Simulating procedural logic

Sometimes I see people having great difficulties in describing how to fetch data for a report.
They are unable to reason by sets and tend to describe things in procedural terms.
Here I'm posting a small example of how you can write a query that reproduces that procedural reasoning and lets the optimizer do the work of translating it into efficient SQL.
Say someone has a table structure like this, a main table named guys holding their id and name and two tables bads_attributes and goods_attributes, if you are a bad guy your attributes will be in the bads_attributes table and vice versa.
Looks ugly? It is, but you'll find it around, sooner or later :-(
And it's not even the worst case scenario, confronted with a similar requirement I've heard that stored procedures where proposed as the ideal solution.
The table structure:

DROP TABLE IF EXISTS `test`.`guys`;
CREATE TABLE `test`.`guys` (
`guy_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_name` varchar(45) NOT NULL,
PRIMARY KEY (`guy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`bads_attributes`;
CREATE TABLE `test`.`bads_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_bads_attributes_1` (`guy_id`),
CONSTRAINT `FK_bads_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`goods_attributes`;
CREATE TABLE `test`.`goods_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_goods_attributes_1` (`guy_id`),
CONSTRAINT `FK_goods_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;



Now let's retrieve those attributes with a query that mimics the logic described above

  1. SELECT
  2. g.guy_id,
  3. g.guy_name,
  4. CASE
  5. WHEN EXISTS (SELECT 0 FROM bads_attributes b WHERE b.guy_id = g.guy_id)
  6. THEN (SELECT group_concat(b.attribute_name separator ', ') FROM bads_attributes b WHERE b.guy_id = g.guy_id GROUP BY b.guy_id)
  7. WHEN EXISTS (SELECT 0 FROM goods_attributes a WHERE a.guy_id = g.guy_id)
  8. THEN (SELECT group_concat(a.attribute_name separator ', ') FROM goods_attributes a WHERE a.guy_id = g.guy_id GROUP BY a.guy_id)
  9. ELSE 'no attributes for this guy'
  10. END right_attributes
  11. FROM
  12. guys g

This goes after the reasoning described above, if you are found in the bads_attributes then your data is retrieved from there, the same for goods_attributes.
Output is

+--------+----------+----------------------------+
| guy_id | guy_name | right_attributes |
+--------+----------+----------------------------+
| 1 | Paolo | Fichissimo |
| 2 | Carlo | Fico |
| 3 | Ciccio | Ugly, Uglier |
| 4 | Bender | Ugliest |
| 5 | New kid | no attributes for this guy |
+--------+----------+----------------------------+
5 rows in set (0.02 sec)

It's explain plan is

+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
| 1 | PRIMARY | g | ALL | NULL | NULL | NULL | NULL | 5 | |
| 5 | DEPENDENT SUBQUERY | a | ref | FK_goods_attributes_1 | FK_goods_attributes_1 | 4 | test.g.guy_id | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | a | ref | FK_goods_attributes_1 | FK_goods_attributes_1 | 4 | test.g.guy_id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | b | ref | FK_bads_attributes_1 | FK_bads_attributes_1 | 4 | test.g.guy_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | b | ref | FK_bads_attributes_1 | FK_bads_attributes_1 | 4 | test.g.guy_id | 1 | Using index |
+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
5 rows in set (0.00 sec)

Well, it could be worse, note that I'm using InnoDB tables and I've declared foreing keys, whose indexes are picked up by the optimizer.

Let's a more SQLish version of this query

  1. SELECT
  2. g.guy_id,
  3. g.guy_name,
  4. COALESCE(
  5. group_concat(b.attribute_name separator ', '),
  6. group_concat(a.attribute_name separator ', ')
  7. ) right_attributes
  8. FROM
  9. guys g LEFT OUTER JOIN bads_attributes b
  10. ON g.guy_id = b.guy_id
  11. LEFT OUTER JOIN goods_attributes a
  12. ON g.guy_id = a.guy_id
  13. GROUP BY
  14. g.guy_id,
  15. g.guy_name

Output is the same

+--------+----------+---------------------------+
| guy_id | guy_name | right_attributes |
+--------+----------+---------------------------+
| 1 | Paolo | Fichissimo |
| 2 | Carlo | Fico |
| 3 | Ciccio | Ugly, Uglier |
| 4 | Bender | Ugliest |
| 5 | New kid | NULL |
+--------+----------+---------------------------+
5 rows in set (0.00 sec)

And the explain plan

+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
| 1 | SIMPLE | g | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
| 1 | SIMPLE | b | ref | FK_bads_attributes_1 | FK_bads_attributes_1 | 4 | test.g.guy_id | 1 | |
| 1 | SIMPLE | a | ref | FK_goods_attributes_1 | FK_goods_attributes_1 | 4 | test.g.guy_id | 1 | |
+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
3 rows in set (0.00 sec)

Well it looks certainly better, but there's a nasty filesort (this is a very small data set) which should be checked against a much larger dataset.

Doing the same on Firebird 2.1 beta 2 (which supports LIST() a function similar to MySQL's group_concat()) leads to:

  1. SELECT
  2. g.guy_id,
  3. g.guy_name,
  4. CASE
  5. WHEN EXISTS (SELECT 0 FROM bads_attributes b WHERE b.guy_id = g.guy_id)
  6. THEN (SELECT CAST(list(b.attribute_name) AS varchar(5000)) FROM bads_attributes b WHERE b.guy_id = g.guy_id
  7. GROUP BY b.guy_id
  8. )
  9. WHEN EXISTS (SELECT 0 FROM goods_attributes a WHERE a.guy_id = g.guy_id)
  10. THEN (SELECT CAST(list(a.attribute_name) AS varchar(5000)) FROM goods_attributes a WHERE a.guy_id = g.guy_id
  11. GROUP BY a.guy_id
  12. )
  13. ELSE 'no attributes for this guy'
  14. END right_attributes
  15. FROM
  16. guys g

and

  1. SELECT
  2. g.guy_id,
  3. g.guy_name,
  4. CAST(
  5. COALESCE(
  6. list(b.attribute_name),
  7. list(a.attribute_name)
  8. ) AS varchar(5000)) right_attributes
  9. FROM
  10. guys g
  11. LEFT OUTER JOIN bads_attributes b
  12. ON g.guy_id = b.guy_id
  13. LEFT OUTER JOIN goods_attributes a
  14. ON g.guy_id = a.guy_id
  15. GROUP BY
  16. g.guy_id,
  17. g.guy_name


Note that both queries need an explicit cast as list's results in Firebird are blobs.
The respective explain plans show that the set oriented one is better.

Prepare time: 00:00:00.
Field #01: GUYS.GUY_ID Alias:GUY_ID Type:INTEGER
Field #02: GUYS.GUY_NAME Alias:GUY_NAME Type:STRING(45)
Field #03: . Alias:RIGHT_ATTRIBUTES Type:STRING(5000)
PLAN (B INDEX (FK_BADS_ATTRIBUTES_1))
PLAN (B ORDER FK_BADS_ATTRIBUTES_1 INDEX (FK_BADS_ATTRIBUTES_1))
PLAN (A INDEX (FK_GOODS_ATTRIBUTES_1))
PLAN (A ORDER FK_GOODS_ATTRIBUTES_1 INDEX (FK_GOODS_ATTRIBUTES_1))
PLAN (G NATURAL)


Executing...
Done.
116 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 21 index, 5 seq.
Delta memory: 54852 bytes.
Execute time: 00:00:00.

and

Prepare time: 00:00:00.
Field #01: GUYS.GUY_ID Alias:GUY_ID Type:INTEGER
Field #02: GUYS.GUY_NAME Alias:GUY_NAME Type:STRING(45)
Field #03: . Alias:RIGHT_ATTRIBUTES Type:STRING(5000)
PLAN JOIN (SORT (JOIN (G NATURAL, B INDEX (FK_BADS_ATTRIBUTES_1))), A INDEX (FK_GOODS_ATTRIBUTES_1))


Executing...
Done.
66 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 9 index, 5 seq.
Delta memory: 38168 bytes.
Execute time: 00:00:00.

Saturday, March 01, 2008

How to sync two tables in MySQL

A question pops out quite often on Devshed forums, "How do I keep table x of my local database in sync with a remote copy?"
Usually replication is the suggested answer, but it might be a little overkill, think of those who just want to push the new product catalogue from a local server to their hosted site? No permanent connection and so on ...
In this case the right tool might be a mix of the new MySQL features, federated tables, extended insert synthax, stored procedures, events, triggers ... quite a fest.
Say you have a local catalogue table which holds the products on sale:

  • CREATE TABLE `test`.`catalogue` (
  • `product_code` CHAR(10) NOT NULL,
  • `product_name` VARCHAR(45) NOT NULL,
  • `product_desc` VARCHAR(500) NOT NULL,
  • `product_weight` DECIMAL NOT NULL,
  • `product_colour` VARCHAR(45) NOT NULL,
  • PRIMARY KEY (`product_code`)
  • )


  • Load it with a sample row

    1. INSERT INTO
    2. catalogue
    3. VALUES
    4. ('AA12F', 'Sm. Widget', 'This is a small widget', 5, 'Red');

    In this sample I'll suppose that a similar table exists in the remote database, no need to check.

    First of all we need to be able to issue queries against that table right from this server, we'll use federated tables:

  • DROP TABLE IF EXISTS `test`.`remote_catalogue`;
  • CREATE TABLE `test`.`remote_catalogue` (
  • `product_code` CHAR(10) NOT NULL,
  • `product_name` varchar(45) NOT NULL,
  • `product_desc` varchar(500) NOT NULL,
  • `product_weight` decimal(10,0) NOT NULL,
  • `product_colour` varchar(45) NOT NULL,
  • PRIMARY KEY (`product_code`)
  • )
  • ENGINE=FEDERATED
  • DEFAULT CHARSET=latin1
  • CONNECTION='mysql://root:nt300jk@127.0.0.1:3306/remote_test/catalogue';
  • --don't use a privileged user in real life!!!

  • Now we have our federated table pointing to the remote server, let's set up the most basic sync, one query will be enough

    REPLACE INTO remote_catalogue SELECT * FROM catalogue 


    A quick test:

    mysql> use remote_test
    Database changed
    mysql> select * from catalogue;
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | product_code | product_name | product_desc | product_weight | produc
    t_colour |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | AA12F | Sm. Widget | This is a small widget | 5 | Red
    |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    1 row in set (0.01 sec)

    mysql>

    After a while data in our local database changes, a new product is added and the existing one is updated

    1. INSERT INTO
    2. catalogue
    3. VALUES
    4. ('AB12G', 'Lg. Widget', 'A large widget', 34, 'blue');
    5. UPDATE
    6. catalogue
    7. SET
    8. product_weight = 4
    9. WHERE
    10. product_code = 'AA12F';

    we are ready to push the changes to remote site with the query used before, reissueing the REPLACE leads to this on the remote server

    mysql> select * from catalogue;
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | product_code | product_name | product_desc | product_weight | produc
    t_colour |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    | AA12F | Sm. Widget | This is a small widget | 4 | Red
    |
    | AB12G | Lg. Widget | A large widget | 34 | blue
    |
    +--------------+--------------+------------------------+----------------+-------
    ---------+
    2 rows in set (0.00 sec)

    So now the two tables hold the same values!
    Enough for now! I'll show you the rest later

    BTW, while writing this I filed two bug reports (34973 and 34971) hope those will turn into errors on my side ...

    MySQL's support guys took care of the bug reports, the outcome is:

    Bug #34973 is not a bug (it's a duplicate of closed bug 25511 which says that's it's fine for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE to fail with federated tables), however bug 25511 suggests that REPLACE doesn't work too, but, as you can see it works fine.

    Bug #34971 is verified, from Miguel's comments it looks like it's fixed in 5.1.24 (not released yet)

    Sunday, January 06, 2008

    New challenges, new synthax

    This post wants to be:

    1. A quick glance at the new "common table expression" (aka hierarchical queries) in Firebird 2.1
    2. A call to action for other opensource databases

    So, on with 1., Firebird recently added another great feature, common table expressions (CTE) which, to my eye at least, boils down to hierarchical queries.
    This is basically the ability to efficiently and easily query hierarchical data stored in the most intuitive way, the adjacency list, because the nested set (see Celko's articles and book) might be the most efficient, but it misses one important thing, widespread adoption ...

    A recursive CTE is basically made of two parts, a base query which gives you the starting point and a recursive query which is the action to be performed in a "loop".
    You see that the structure is:

    WITH RECURSIVE which tells us that this CTE will use recursion
    n(....) AS ( which describes the output of the base query, n is just an alias, you can use whatever name you like
    SELECT ... which is the base query and sets the starting point, this query will be merged with results of the recursive query through an union
    UNION ALL which marks the merge described above
    SELECT ... which is the recursive query, note that it's joined to the base query through it's alias
    ) which closes the recursive block
    SELECT ... which ends up the CTE and builds it's output, note that in the second example I applied a further where condition to this last query, something that might be non optimal.

    I'll use an example table which mimics a BOM (Bill Of Materials) taken from the web.

    1. CREATE TABLE BILL_OF_MATERIAL(
    2. PART_NO Varchar(50) NOT NULL,
    3. PARENT_PART_NO Varchar(50),
    4. REVISION CHAR(1),
    5. QUANTITY Integer,
    6. UOM CHAR(2) NOT NULL,
    7. DESCRIPTION Varchar(255),
    8. MAKE_BUY CHAR(1) NOT NULL,
    9. CONSTRAINT PK_BOM PRIMARY KEY (PART_NO)
    10. );
    11. CREATE INDEX IDX_PARENT_PART_NO ON BILL_OF_MATERIAL (PARENT_PART_NO);
    12. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
    13. ON BILL_OF_MATERIAL TO "SYSDBA" WITH GRANT OPTION;

    Then I'll populate it with some sample rows, the result should be


    A query to retrieve BOM for a specific item:

    1. WITH RECURSIVE n(lvl, part_no, description) AS (SELECT 1 lvl, part_no, description
    2. FROM BILL_OF_MATERIAL
    3. WHERE description = 'Field Adapter'
    4. UNION ALL
    5. SELECT n.lvl + 1, nplus1.part_no, nplus1.description
    6. FROM BILL_OF_MATERIAL AS nplus1, n
    7. WHERE n.part_no = nplus1.parent_part_no)
    8. SELECT lvl, part_no, description FROM n;

    A query to retrieve all buy parts for an item:

    1. WITH RECURSIVE n(part_no, description, quantity, make_buy) AS (SELECT part_no, description, quantity, make_buy
    2. FROM bill_of_material
    3. WHERE description = 'Field Adapter'
    4. UNION ALL
    5. SELECT nplus1.part_no, nplus1.description, nplus1.quantity, nplus1.make_buy
    6. FROM BILL_OF_MATERIAL AS nplus1, n
    7. WHERE n.part_no = nplus1.parent_part_no)
    8. SELECT part_no, description, quantity FROM n WHERE make_buy = 'B';

    And the result is:



    About 2., I'd love to see other mainstream opensource databases run at this, not because MsSQL (standard WITH ... synthax), Oracle (proprietary) and DB2 (standard WITH ... synthax) support it, but because it helps solving real life problems which are becoming more and more common.
    I mean, PostgreSQL has a patch floating around which reproduces Oracle's non standard synthax, this patch has been somewhat maintained for a long time, this means that there is active interest, why don't they add it to the codebase? Just for the sake of standards?
    And what about MySQL?

    As always comments and corrections are more than welcome!

    BTW: for comparison, you can find an example about BOM and nested sets here.

    UPDATE: Looks like PostgreSQL users and developers felt the hitch, a patch to add CTE has been committed on 04-10-2008 see here.

    Wednesday, December 26, 2007

    Back to the Blog ... back to the bug (report)

    Today I decide to put up a post about a common requirement which leads to an interesting SQL query (see an example here) using Firebird and Flamerobin, after a few seconds I was writing bug reports :-( see:

    [ 1858394 ] Error selecting from mon$statements

    and

    [ 1858397 ] Error running SELECT query. (Not a Flamerobin bug)

    Hope these will be fixed soon.

    EDIT: the developers instantly responded to my bug reports saying that the first one is already fixed in SVN and that will be in the next release and the second is not a bug

    Well actually bug 1858397 looks like a Firebird bug, unrelated to Flamerobin or IBPP :-( time to close that bug report and post to the right place, which is

    [#CORE-1668] Error running SELECT query.

    EDIT: looks like this is a duplicate of bug 94, a long standing one that won't be fixed anytime soon :-(

    After the ordeal I ended up with

    [ 1858592 ] Problem with LIST() function.
    List() result is represented as a BLOB so it appears in the grid as a marker and you can't read it's content.
    Even in this case the developers quickly clarified the situation:

    There are plans to make textual blobs behave just like regular varchar
    columns - if not sooner it will be available in version 0.9.0.

    Milan.


    BTW this query doesn't work:

    1. SELECT
    2. e.emp_no, e.FIRST_NAME, e.LAST_NAME,
    3. CASE
    4. WHEN ep.emp_no || ep.PROJ_ID IS NULL THEN ' didn''t take part in '
    5. ELSE ' took part in '
    6. END,
    7. p.proj_name
    8. FROM
    9. employee e,
    10. project p
    11. LEFT OUTER JOIN EMPLOYEE_PROJECT ep
    12. ON e.EMP_NO = ep.EMP_NO AND p.proj_id = ep.PROJ_ID

    (If you change the above to a cross join it works fine).
    While this does:

    1. SELECT
    2. a.emp_no, a.FIRST_NAME, a.LAST_NAME,
    3. CASE
    4. WHEN ep.emp_no || ep.PROJ_ID IS NULL THEN ' didn''t take part in '
    5. ELSE ' took part in '
    6. END,
    7. a.proj_name
    8. FROM
    9. (SELECT * FROM employee e, project p) a
    10. LEFT OUTER JOIN
    11. EMPLOYEE_PROJECT ep
    12. ON a.EMP_NO = ep.EMP_NO AND a.proj_id = ep.PROJ_ID;

    Saturday, August 04, 2007

    Lufthansa ships with ...

    For those who are spending their miles at the Lufthansa Worldshop for the first time, they ship with UPS ... I haven't been able to find this out from their site and the tracking code remained rather obscure to me until I asked for clarification.
    Their answer was quick and accurate, but, is it so difficult to put a link to the UPS site?

    Thursday, June 28, 2007

    Gnuwin32, what about the docs????

    Today I had to surrender and use grep on a pile of rubbish, er ..., on some Cognos files, those pesky .icr, so I went to the GNUWin32 site, downloaded the grep installer for windows and run, just like any good (windows) boy would do ... and the first surprise came, the gnuwin32/bin dir is not added to path ... anyway, I added it by hand and went on, in hope that a simple grep --help would give me all infos, here it is:
    D:\FOLDER>grep --help
    Usage: grep [OPTION]... PATTERN [FILE] ...
    Search for PATTERN in each FILE or standard input.
    Example: grep -i 'hello world' menu.h main.c

    So I'd say single quotes are good, but ... this is what worked ... I'm just pissed by this

    D:\FOLDER>grep -H "Table :" *.icr > tables_in_catalog.txt

    NO single quotes, but DOUBLE quotes!

    Wednesday, June 20, 2007

    Opensource database comparison

    "Which is the best opensource database?", "How does MySQL/PostgreSQL/Firebird ... (you name it) stand against Firebird/PostgreSQL/MySQL ... (you name it again)?" I'm shure we've all heard this questions many times, but now I have the definitive answer, by Jim Starkey himself, you can read the full post here, but in short is

    "I think the short answer is that nobody with the experience to do this
    is interested in doing it. I know that this isn't a particularly useful
    answer, but I'm afraid it is the truth."

    GREAT!!!

    Sunday, May 27, 2007

    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:

    1. DELIMITER $$
    2. DROP PROCEDURE IF EXISTS `test`.`sp_4_each_table` $$
    3. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_4_each_table`(db_name VARCHAR(64), template VARCHAR(21845))
    4. BEGIN
    5. #
    6. DECLARE done INT DEFAULT 0;
    7. #
    8. DECLARE tname VARCHAR(64);
    9. #
    10. DECLARE c CURSOR FOR
    11. #
    12. SELECT table_name FROM information_schema.TABLES WHERE table_schema=db_name AND table_type = 'BASE TABLE';
    13. #
    14. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done= 1;
    15. #
    16. #
    17. OPEN c;
    18. #
    19. REPEAT
    20. #
    21. FETCH c INTO tname;
    22. #
    23. SET @stmt_text=REPLACE(template, " ?", CONCAT(" ", tname));
    24. #
    25. BEGIN
    26. #
    27. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    28. #
    29. SELECT CONCAT("'", @stmt_text, "' command failed") AS "error";
    30. #
    31. PREPARE stmt FROM @stmt_text;
    32. #
    33. EXECUTE stmt;
    34. #
    35. DEALLOCATE prepare stmt;
    36. #
    37. END;
    38. #
    39. UNTIL done END REPEAT;
    40. #
    41. END $$
    42. 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 !!!

    Sunday, May 20, 2007

    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

    Friday, May 04, 2007

    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