List:Cluster« Previous MessageNext Message »
From:Martin Skold Date:January 29 2010 2:03pm
Subject:Re: Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue in
MySQL Cluster ?
View as plain text  
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
> 
Thread
WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
  • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Martin Skold29 Jan
    • Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQLCluster ?Hendrik Woltersdorf29 Jan
      • Re: Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue inMySQL Cluster ?Martin Skold29 Jan
        • Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
          • Re: howto implement a fifo queue in MySQL Cluster ?Martin Skold29 Jan
            • Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
    • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Tom Hanlon29 Jan
    • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Tom Hanlon29 Jan
Re: howto implement a fifo queue in MySQL Cluster ?Tom Hanlon2 Feb