[SQL] error 1592 ivm trigger / auto increment

[Tjolk]

Situatie: ik heb een tabel `stock_actual` waarin ik de actuele voorraad bijhoud. Daarop zit de volgende trigger:

SQL:

CREATE
DEFINER=`factor`@`localhost`
TRIGGER `voorjou`.`au_stock_actual`
AFTER UPDATE ON `voorjou`.`stock_actual`
FOR EACH ROW
BEGIN
    INSERT INTO stock_log (article_id, mutation, stock_reservation_type_id)
    VALUES (NEW.article_id, (NEW.quantity - OLD.quantity), NEW.stock_reservation_type_id);
END$$

De tabel `stock_log` houdt simpelweg bij wat er allemaal in de voorraad gebeurt en is als volgt opgebouwd:

SQL:

delimiter $$

CREATE TABLE `stock_log` (
  `autonumber` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `article_id` int(11) DEFAULT NULL,
  `stock_reservation_type_id` tinyint(3) unsigned DEFAULT NULL,
  `mutation` int(11) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`autonumber`)
) ENGINE=MyISAM AUTO_INCREMENT=1299745 DEFAULT CHARSET=latin1$$

Nu krijg ik sinds een server migratie (waarbij we o.a. van MySQL 5.1 naar MySQL 5.5 gegaan zijn) de volgende warning bij een aanpassing in `stock_actual`:

1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.

Wat ik na wat googelen hiervan begrijp is dat deze waarschuwing komt omdat je er niet gegarandeerd op kunt vertrouwen dat de AUTO_INCREMENT waarde die hieruit volgt klopt.

Ik vraag me nu af hoe ik dit het beste kan oplossen. Natuurlijk kan ik die AUTO_INCREMENT eraf halen en zelf de autonumber bepalen a.h.v. de huidige hoogste autonumber +1, maar dat lijkt me niet erg handig (en net zo goed foutgevoelig, al wordt het dan niet gelogd). Ik lees suggesties over het gebruik van UUID(), maar dat vind ik nogal overkill voor zoiets.

Bruikbare tips zijn welkom.

[Phoenix1337]

Het probleem met statement based bin logging is dat de triggers op beide servers uitgevoerd worden. In plaats daarvan zou je row based logging kunnen gebruiken, waarbij de daadwerkelijke values naar de andere server gestuurd worden. (http://dev.mysql.com/doc/...on-features-triggers.html)

Het nadeel van row based logging is dat deze veel data in beslag kan nemen omdat alle waardes daadwerkelijk weggeschreven worden.

Een eventuele mogelijkheid is nog om gebruik te maken van mixed (row based + statement based). Of dit correct overweg gaat met triggers durf ik je niet te zeggen.

[Tjolk]

Hm, thanks.
Als ik het goed begrijp ligt het dus aan de manier waarop de server is ingericht. Daar is nu (met de aankomende drukte) weinig aan te veranderen denk ik en sowieso niet door mij (wij huren een geheel onderhouden servercluster, ik heb daar te weinig kaas van gegeten).

Ik denk dat er weinig anders opzit dan dat ik dit noteer voor de volgende afspraak met degene die de server beheert en dan eens gaan kijken hoe we dit op een goede manier kunnen oplossen. For the time being verwacht ik hier geen problemen mee aangezien het puur een logging ding is en niets afhangt van dat autonumber.

[Phoenix1337]

Om nog even op je andere suggesties in te springen:

Wanneer je UUID gebruikt, levert dit op beide servers een apart string op. Dit betekend dat de data op beide servers verschillend is. Nou maakt dat voor een logging tabel weinig uit, maar er zijn ongetwijfeld situaties waarin dat wel uit gaat maken.

De mogelijheid met het handmatig ophogen van een ID is ook niet ideaal. Wanneer je meerdere servers hebt waarop data geinsert wordt (multi master replicatie), dan gaat dit duplicaten opleveren waardoor replicatie er constant mee stopt.

De enige mogelijkheid is, voor zover ik weet, het gebruik van row based logging of uberhaupt geen gebruik maken van een trigger en de logregel zelf inserten. Dit zorgt er namelijk voor dat de volgorde van het uitvoeren van de queries wel gelijk is en dus hetzelfde id oplevert. Het inlezen van de binlog gebeurt in een andere thread dan het uitvoeren van de trigger. Tijdens het inserten van de rij en het uitvoeren kan enige tijd zitten waardoor er al weer een andere logregel geinsert is en data op master en slave niet meer synchroon loopt.

Je krijgt de melding nu pas omdat 5.1 (tot en met 5.1.4) alleen maar statement based replicatie ondersteunde.

[ Voor 4% gewijzigd door Phoenix1337 op 28-11-2014 15:30 ]

[Tjolk]

In alle eerlijkheid: mijn kennis over de serverkant van MySQL (of überhaupt serverinrichting) is minimaal. We hebben het niet voor niets uitbesteed

We hebben een "minicluster" momenteel; 2 fysieke servers waarop één load balancer zit, apache draait op beide servers synchroon en MySQL "woont" afwisselend op de ene en dan weer op de andere server. Als ik het goed heb begrepen kan Apache/PHP wel op 2 servers tegelijk zijn werk doen, maar MySQL niet (althans: niet in de huidige config aldus de beheerder). Er komt na het seizoen een derde server bij en dan fungeert die als slave omdat Apache maar op 2 servers tegelijk kan draaien.
(disclaimer: ik lepel dit op uit mijn hoofd zoals het mij een paar maanden terug is uitgelegd)

Maar als ik het dus goed heb begrepen, draait MySQL niet in een Master-slave opstelling, maar wordt het proces steeds naar de minst drukke server gestuurd. In hoeverre is het risico van een trigger met verkeerde auto-increment ID's dan reëel?

Ik ben trouwens in dit geval wel heel erg fan van de trigger, aangezien er vanuit heel veel verschillende processen op de centrale voorraadtabel (stock_actual) wordt gekeken en geschreven. Natuurlijk is het heel goed mogelijk om daar overal "handmatig" een insertquery voor de logging in te gooien, maar nu weet ik zeker dat alles wat er op de voorraadtabel gebeurt gelogd wordt, ongeacht wat de bron van de mutatie is.

[Tjolk]

Update:
Ik heb met de hostingpartij gesproken en dit ook ter sprake gebracht. Mijn vermoeden was juist: we draaien wel in een load balanced omgeving, maar MySQL draait maar op 1 server tegelijk. Er is dus geen Master-slave opstelling. De waarschuwingen zijn dus ook niet relevant voor onze opstelling.