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)