/*
 * This SQL script demonstrates a problem that causes the mysql server
 * process to crash.  The problem happens when a row in a table with an 
 * 'AFTER UPDATE' trigger is modified.  The problem only seems to happen
 * if the table has composite primary key.
 *
 * The problem does not happen with MySQL version 5.0.18 but it does
 * exist in at least 5.0.24 through 5.0.27.  The problem exists in 
 * Windows and Linux.
 */

USE test;
DROP TABLE IF EXISTS fubar;
CREATE TABLE fubar
(
    id   INTEGER(10) NOT NULL DEFAULT '0',
    a    VARCHAR(10) NOT NULL,
    b    VARCHAR(10),
    c    VARCHAR(10),
    d   TIMESTAMP NOT NULL,

    PRIMARY KEY (id, a)   /* MySQL crashs when this primary key is used */
    /* PRIMARY KEY (id)  The script works correctly with this primary key */
) ENGINE=InnoDB;

DROP TABLE IF EXISTS fubar_changes;
CREATE TABLE  fubar_changes
(
        fubar_id                INTEGER(10) unsigned NOT NULL DEFAULT '0',
        last_change_time    DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY  (fubar_id)
) ENGINE=InnoDB;

DELIMITER |

CREATE TRIGGER fubar_change
    AFTER UPDATE ON fubar
      FOR EACH ROW
        BEGIN
            INSERT INTO fubar_changes (fubar_id, last_change_time)
                SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
                FROM fubar WHERE (id = NEW.id)
                AND
                (
                    (OLD.c != NEW.c)
                )
            ON DUPLICATE KEY UPDATE
                last_change_time =
                IF ((fubar_id = NEW.id)
                AND
                (
                    (OLD.c != NEW.c)
                ) , NOW() , last_change_time);
        END
|

DELIMITER ;

insert into fubar (id, a,   b,   c,  d) VALUES (1, 'a', 'b', 'c', now());
update fubar set c='Bang!' where id=1;