I assume you are using the latest version?
There was a bug fixed some months ago:
Bug #38034 last_insert_id() value not shown in table with trigger
Do I understand this correctly, the DATE (or TIMESTAMP)
is on one table and the AUTO_INCREMENT is on another?
What does your inserts into STATE_HISTORY look like?
Are you doing any multi-inserts anywhere? If you do
INSERT ... VALUES <multiple values>; then the auto_inc pre-fetch
will be the number of values inserted. If you do INSERT ... WHERE
then it will minimum 32 or the value of ndb_autoincrement_prefetch_sz
if that is > 32. The trigger will execute for each row, but the
last_insert_id() will see the value after prefetching.
Even if you only do single row inserts then this could still be a
problem, but of course difference should no be two minutes.
Try defining the auto_increment on the Q2X_HEAD table instead, that
should increase by steps of 1.
BR
-- Martin
Hendrik Woltersdorf wrote:
> Hi,
>
> I did not try this with other storage engines. And I need to do this
> inside of the ndbcluster. Locking tables explicitly commits - so says
> the manual - any open transactions. I cannot commit transactions in
> these triggers. This would break the application transactions. btw. the
> manual tells me also, that lock table works only for the sql node on
> which the lock statement is issued. So the second sql node will not see it.
> I do not think, that locking will help. In the resultset, I posted
> earlier, the ID 14719 appears more than two minutes(!) after ID 14739.
> And the clocks of the machines are in sync today.
>
> kind regards
>
> Hendrik Woltersdorf
> XCOM AG
> Tel.: 0375/27008-580
> Tel. mobil: 0178/5710078
> Email: Hendrik.Woltersdorf@stripped
>
> Inactive hide details for Martin Skold ---29.01.2010 14:06:13---Hi
> again!Martin Skold ---29.01.2010 14:06:13---Hi again!
>
> *Martin Skold <Martin.Skold@stripped>*
> Gesendet von: Martin.Skold@stripped
>
> 29.01.2010 14:05
>
>
>
> An
>
> Hendrik Woltersdorf <hendrik.woltersdorf@stripped>
>
> Kopie
>
> Cluster <cluster@stripped>
>
> Thema
>
> Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?
>
>
>
>
> Hi again!
>
> Have you tried this on some other storage engine?
> My guess is that the trigger execution interleaves between
> different threads and the execution of now() and the insert
> is not atomic.
> You can try locking table in the beginning of trigger
> and the unlock in the end this should avoid connection threads
> interleaving. This will make it very inefficient, but then we will
> know if that is the problem. This seems to be a standard synchronization
> issue.
>
> BR
> -- Martin
>
> Hendrik Woltersdorf wrote:
> > Hi,
> >
> > the inserts are done in triggers.
> >
> > for example:
> >
> > CREATE DEFINER=CURRENT_USER trigger TR_STATE_HISTORY_AFTER_INSERT AFTER
> > INSERT on STATE_HISTORY
> > FOR EACH ROW
> > BEGIN
> > ...
> > insert into Q2X_HEAD
> > (Q2X_TABLENAME ,
> > FLAG_Q2X_STATUS,
> > DATE_Q2X_INSERT,
> > DATE_Q2X_UPDATE,
> > FLAG_OPCODE ,
> > ERROR_CODE ,
> > TABLE_PK
> > )
> > values('STATE_HISTORY',
> > 'A',
> > NOW(),
> > NULL,
> > 'I',
> > null,
> > NEW.ID_STATE_HISTORY
> > );
> >
> > set $ID_Q2X_HEAD = last_insert_id();
> >
> > ... < some more inserts into another table (Q2X_DETAIL)>
> >
> > And the number of clients is between 5 and 20 and may grow.
> >
> > kind regards
> >
> > Hendrik Woltersdorf
> > XCOM AG
> > Tel.: 0375/27008-580
> > Tel. mobil: 0178/5710078
> > Email: Hendrik.Woltersdorf@stripped
> >
> > Martin.Skold@stripped schrieb am 29.01.2010 11:52:59:
> >
> >> Martin Skold <Martin.Skold@stripped>
> >> Gesendet von: Martin.Skold@stripped
> >>
> >> 29.01.2010 11:52
> >>
> >> An
> >>
> >> Hendrik Woltersdorf <hendrik.woltersdorf@stripped>
> >>
> >> Kopie
> >>
> >> cluster@stripped
> >>
> >> Thema
> >>
> >> Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?
> >>
> >> Hi!
> >>
> >> How exactly are the INSERT statements done?
> >> How many clients?
> >>
> >> BR
> >> -- Martin
> >>
> >> Hendrik Woltersdorf wrote:
> >>> Hi,
> >>>
> >>> I have already set ndb_autoincrement_prefetch_sz to 1. It does not
> > force
> >>> order.
> >>> What I get is this:
> >>>
> >>> ID DATE_INSERT
> >>> -------------------------------------
> >>> 14757, '2010-01-29 10:15:51'
> >>> 14756, '2010-01-29 10:15:50'
> >>> 14755, '2010-01-29 10:15:50'
> >>> 14721, '2010-01-29 10:15:50'
> >>> 14720, '2010-01-29 10:15:50'
> >>> 14719, '2010-01-29 10:15:50'
> >>> 14739, '2010-01-29 10:13:37'
> >>> 14738, '2010-01-29 10:13:37'
> >>> 14737, '2010-01-29 10:13:37'
> >>> 14736, '2010-01-29 10:13:37'
> >>>
> > ...
> >
> >
> >
> > *** XCOM AG Legal Disclaimer ***
> >
> > Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist
> allein
> > für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten ist
> > das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir
> bitten,
> > eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und uns
> > eine Nachricht zukommen zu lassen.
> >
> > This email may contain material that is confidential and for the sole use
> > of the intended recipient. Any review, distribution by others or
> forwarding
> > without express permission is strictly prohibited. If you are not the
> > intended recipient, please contact the sender and delete all copies.
> >
> > Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
> > Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
> > www.xcom.de
> > Handelsregister: Amtsgericht Krefeld, HRB 10340
> > Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
> > Vorsitzender des Aufsichtsrates: Stefan H. Tarach
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:
> http://lists.mysql.com/cluster?unsub=1
>
>
>
>
>
>
> *** XCOM AG Legal Disclaimer ***
>
> Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist
> allein für den Gebrauch durch den vorgesehenen Empfaenger bestimmt.
> Dritten ist das Lesen, Verteilen oder Weiterleiten dieser E-Mail
> untersagt. Wir bitten, eine fehlgeleitete E-Mail unverzueglich
> vollstaendig zu loeschen und uns eine Nachricht zukommen zu lassen.
>
> This email may contain material that is confidential and for the sole
> use of the intended recipient. Any review, distribution by others or
> forwarding without express permission is strictly prohibited. If you are
> not the intended recipient, please contact the sender and delete all copies.
>
> Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
> Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
> www.xcom.de
> Handelsregister: Amtsgericht Krefeld, HRB 10340
> Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
> Vorsitzender des Aufsichtsrates: Stefan H. Tarach
>
