MySQL history table; Lost revision sequence

Question

I had implemented this solution some time ago. https://stackoverflow.com/a/12657012/112680 It was working great until yesterday. I had added a couple of columns and I believe an index (which I had done before w/o issue), now it doesn't do the revisions like 1,2,3 per eventid, it's just incrementing for every row. I did a compare from what that is working and I don't see any differences other than the auto increment starting value.

My After Insert Trigger on the main table

thisTrigger: BEGIN
IF ((@TRIGGER_CHECKS = FALSE)
  OR (@TRIGGER_AFTER_INSERT_CHECKS = FALSE))
  AND (USER() = xxx)
THEN
  LEAVE thisTrigger;
END IF;

INSERT INTO eventshistory 
  SELECT 'initial booking', NULL, UTC_TIMESTAMP(), e.* 
  FROM events AS e 
  WHERE e.eventid = NEW.eventid;
END

After Update Trigger

thisTrigger: BEGIN
IF ((@TRIGGER_CHECKS = FALSE)
  OR (@TRIGGER_AFTER_UPDATE_CHECKS = FALSE))
  AND (USER() = xxxx)
THEN
  LEAVE thisTrigger;
END IF;

INSERT INTO eventshistory SELECT 'update', NULL, UTC_TIMESTAMP(), e.*
  FROM events AS e WHERE e.eventid = NEW.eventid;
END

DDL

CREATE TABLE eventshistory (
  action VARCHAR(15) NOT NULL DEFAULT 'insert'
 ,revision INT(6) NOT NULL AUTO_INCREMENT
 ,actiondate DATETIME DEFAULT NULL
 ,eventid INT(11) NOT NULL
 ,franchiseid INT(11) NOT NULL
 ,territoryid INT(11) DEFAULT NULL
 ,customerinfoid INT(11) DEFAULT NULL
 ,eventaddressid INT(11) DEFAULT NULL
 ,eventstartdate DATETIME DEFAULT NULL
 ,eventenddate DATETIME DEFAULT NULL
 ,createdon DATETIME DEFAULT NULL
 ,createdby INT(11) DEFAULT NULL
 ,updatedon DATETIME DEFAULT NULL
 ,updatedby INT(11) DEFAULT NULL
 ,attendeecount SMALLINT(6) DEFAULT NULL
 ,onhold TINYINT(1) NOT NULL DEFAULT 0
 ,holduntildate DATETIME DEFAULT NULL
 ,canceledon DATETIME DEFAULT NULL
 ,canceledby INT(11) DEFAULT NULL
 ,isquotesent TINYINT(1) NOT NULL DEFAULT 0
 ,quoteexpireson DATETIME DEFAULT NULL
 ,haspriceoverride TINYINT(1) NOT NULL DEFAULT 0
 ,pretaxtotal DECIMAL(8,2) DEFAULT NULL
 ,couponcodeid INT(11) DEFAULT NULL
 ,eventnotes VARCHAR(500) DEFAULT NULL
 ,discounttype CHAR(1) DEFAULT NULL COMMENT 'P for percentage, F for fixed dollar'
 ,discountamount DECIMAL(8,2) DEFAULT NULL
 ,istaxexempt TINYINT(1) NOT NULL DEFAULT 0
 ,videogameratingid INT(11) DEFAULT NULL
 ,quotedon DATETIME DEFAULT NULL
 ,tax DECIMAL(8,2) DEFAULT NULL
 ,receiptnotes VARCHAR(500) DEFAULT NULL
 ,outstandingbalance DECIMAL(8,2) DEFAULT NULL
 ,flatparkingsetup TINYINT(1) DEFAULT NULL
 ,honoreename VARCHAR(50) DEFAULT NULL
 ,HonoreeAge VARCHAR(10) DEFAULT NULL
 ,honoreegender CHAR(1) DEFAULT NULL
 ,referralsourceid INT(11) DEFAULT NULL
 ,eventtypeid INT(11) DEFAULT NULL
 ,pricingmodel CHAR(11) DEFAULT NULL COMMENT 'Hourly 1, Hourly 2 etc.'
 ,depositamt DECIMAL(8,2) DEFAULT NULL
 ,isquoteconverted TINYINT(1) DEFAULT NULL
 ,status VARCHAR(25) DEFAULT NULL
 ,activitiestotal DECIMAL(8,2) DEFAULT NULL
 ,coupontotal DECIMAL(8,2) DEFAULT NULL
 ,overridediff DECIMAL(8,2) DEFAULT NULL
 ,refunddue DECIMAL(8,2) DEFAULT NULL
 ,bookeddate DATETIME DEFAULT NULL
 ,cancelationfee DECIMAL(8,2) DEFAULT NULL
 ,eventtotal DECIMAL(8,2) DEFAULT NULL
 ,discounttotal DECIMAL(8,2) DEFAULT NULL
 ,g2uwareeid INT(11) DEFAULT NULL
 ,islocked TINYINT(1) DEFAULT NULL
 ,lockeddate DATETIME DEFAULT NULL
 ,isQuotePaid TINYINT(1) DEFAULT NULL
 ,taxrate DECIMAL(6,5) DEFAULT 0.00000
 ,quotepaidpct TINYINT(4) UNSIGNED DEFAULT NULL
 ,cancelationfeecash DECIMAL(8,2) DEFAULT NULL
 ,canceltype CHAR(1) DEFAULT NULL
 ,isguaranteed TINYINT(1) DEFAULT 0
 ,commission DECIMAL(6,2) DEFAULT 0.00
 ,commissionreportedbooked TINYINT(1) NOT NULL DEFAULT 0
 ,commissionreportedcompleted TINYINT(1) NOT NULL DEFAULT 0
 ,CommissionableCost DECIMAL(8,2) NOT NULL DEFAULT 0.00
 ,CouponType CHAR(1) DEFAULT NULL
 ,CouponCode VARCHAR(25) DEFAULT NULL
 ,CouponAmount DECIMAL(6,2) DEFAULT NULL
 ,PRIMARY KEY (eventid,revision)
 ,INDEX IDX_eventshistory_franchiseid (franchiseid)
 ,INDEX UK_eventshistory (status)
 ,INDEX UK_eventshistory2 (canceledon)
 ,INDEX UK_eventshistory3 (revision,eventid,franchiseid)
)
ENGINE = MYISAM
AUTO_INCREMENT = 235
AVG_ROW_LENGTH = 251
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

score 0 · Answer 1 · edited Aug 19 '17 at 19:29

So, RTM... https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

I had just added a new index that contained the PK.

Also for auto increment reset, MySQL states for MyISAM that

you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one

But when I reset it to 1000, it actually put it back to 1. So that is good news.

MySQL history table; Lost revision sequence

1 Answers1