SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';

DROP TABLE IF EXISTS data;

CREATE TABLE `data` (
  `ac` int(10) unsigned default NULL,
  `src` int(10) unsigned default NULL,
  `dst` int(10) unsigned default NULL,
  `bytes` int(10) unsigned default NULL,
  `packets` int(10) unsigned default NULL,
  `srcuser` char(200) default NULL,
  `dstuser` char(200) default NULL,
  `stamp` datetime default NULL,
  `hash` binary(8) NOT NULL,
  `id` bigint unsigned NOT NULL DEFAULT 0,
  KEY `stamp` (`stamp`),
  PRIMARY KEY (hash, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS hashseq;

CREATE TABLE `hashseq` (
  `hash` binary(8) PRIMARY KEY,
  `id` bigint unsigned
);

DROP FUNCTION IF EXISTS nextid;

DELIMITER //

/* The actual sequencing function. Call nextid(hash) and it returns the next value. */
/* If the hash doesn't exist yet, it will be created with the initial value of 1 */
CREATE FUNCTION nextid (hash binary(8))
RETURNS BIGINT UNSIGNED
BEGIN
INSERT INTO hashseq VALUES (hash,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id+1);
RETURN LAST_INSERT_ID();
END
//

DELIMITER ;SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';

DROP TABLE IF EXISTS data;

CREATE TABLE `data` (
  `ac` int(10) unsigned default NULL,
  `src` int(10) unsigned default NULL,
  `dst` int(10) unsigned default NULL,
  `bytes` int(10) unsigned default NULL,
  `packets` int(10) unsigned default NULL,
  `srcuser` char(200) default NULL,
  `dstuser` char(200) default NULL,
  `stamp` datetime default NULL,
  `hash` binary(8) NOT NULL,
  `id` bigint unsigned NOT NULL DEFAULT 0,
  KEY `stamp` (`stamp`),
  PRIMARY KEY (hash, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS hashseq;

CREATE TABLE `hashseq` (
  `hash` binary(8) PRIMARY KEY,
  `id` bigint unsigned
);

DROP FUNCTION IF EXISTS nextid;

DELIMITER //

/* The actual sequencing function. Call nextid(hash) and it returns the next value. */
/* If the hash doesn't exist yet, it will be created with the initial value of 1 */
CREATE FUNCTION nextid (hash binary(8))
RETURNS BIGINT UNSIGNED
BEGIN
INSERT INTO hashseq VALUES (hash,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id+1);
RETURN LAST_INSERT_ID();
END
//

DELIMITER ;

#DROP TRIGGER nextid; 

CREATE TRIGGER nextid BEFORE INSERT ON data
FOR EACH ROW SET new.id=IF(new.id=0,nextid(new.hash),new.id);


#DROP TRIGGER nextid; 

CREATE TRIGGER nextid BEFORE INSERT ON data
FOR EACH ROW SET new.id=IF(new.id=0,nextid(new.hash),new.id);

-----------------
accountingcache accounting # mysql -uroot -p accounting < createdata.sql 
Enter password: 
ERROR 1005 (HY000) at line 5: Can't create table './accounting/data.frm' (errno: 121)