List:Cluster« Previous MessageNext Message »
From:Tom Hanlon Date:January 29 2010 7:26pm
Subject:Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster
?
View as plain text  
Martin Skold wrote:
> 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.
> 


Have you tried getting the now() value associated with a timestamp on 
the state_history table ?

Something like

Create table (blah blah blah, mt timestamp);

And then the trigger on that table extracts the value of the timestamped 
column..

Create trigger after insert in on table insert into new table values 
NEW.mt ??

Then you would not be dependent on the now when the trigger execute, but 
the now() when the table was updated ?

Or you could perhaps leverage this somewhat interesting behavior for 
your purposes..
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-01-29 14:17:38 |
+---------------------+
1 row in set (0.10 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-01-29 14:17:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET TIMESTAMP=1263450854;
Query OK, 0 rows affected (0.02 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-01-14 01:34:14 |
+---------------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-01-14 01:34:14 |
+---------------------+
1 row in set (0.00 sec)

*note*
the set TIMESTAMP freezes now(); Until you set it to something else, or 
reset with 0.


The SET timestamp, in the trigger.. based upon the value in a timestamp 
in the table.

Or something. Just putting some options to deal with the value of now();

--
Tom


> 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
> 


-- 

Tom Hanlon
Certified MySQL DBA
Certified Cloudera Certified Hadoop Professional (CCHP)

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