WG: Re: howto implement a fifo queue in MySQL Cluster ?

[Hendrik Woltersdorf]

the shortened version for the cluster list

Hendrik Woltersdorf
XCOM AG
Tel.: [phone]
Tel. mobil: 0178/5710078
Email: Hendrik.Woltersdorf@stripped
----- Weitergeleitet von Hendrik Woltersdorf/DE/XCOM am 02.02.2010 07:57
-----
                                                                           
             Hendrik                                                       
             Woltersdorf/DE/XC                                             
             OM                                                         An 
                                         Tom Hanlon <Tom.Hanlon@stripped>   
             02.02.2010 07:48                                        Kopie 
                                         Cluster                           
                                         <cluster@stripped>,        
                                         [e-mail]                
                                                                     Thema 
                                         Re: howto implement a fifo queue  
                                         in MySQL Cluster ?(Document link: 
                                         Mail)                             
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           



Hi Tom,

thanks for the interesting details about "set timestamp" and "now()", but
the results of now() or last_insert_id() are ok.
The problem is, that the primary key values, generated by auto_increment,
are not insertet into the table in order.
See the following resultset (filtered with Q2X_TABLENAME and TABLE_PK):

"ID_Q2X_HEAD","Q2X_TABLENAME","FLAG_Q2X_STATUS","DATE_Q2X_INSERT","DATE_Q2X_UPDATE","FLAG_OPCODE","ERROR_CODE","TABLE_PK"
16681,"ORDERS","F","2010-02-01 15:06:20","2010-02-01
15:06:25","U",8002,25023173
16711,"ORDERS","E","2010-02-01 15:06:18","2010-02-01
15:06:25","I",0,25023173
16714,"ORDERS","E","2010-02-01 15:06:20","2010-02-01
15:06:25","U",0,25023173
16716,"ORDERS","F","2010-02-01 15:06:21","2010-02-01
15:06:25","U",8002,25023173
16719,"ORDERS","E","2010-02-01 15:06:21","2010-02-01
15:06:25","U",0,25023173

If I sort them by ID_Q2X_HEAD I get the update event (ID_Q2X_HEAD = 16681,
FLAG_OPCODE="U") before I get the insert event (ID_Q2X_HEAD=16711,
FLAG_OPCODE="I").
Sorting by DATE_Q2X_INSERT is no solution because there will be many table
entries per second. For example the entries with the IDs 16716 and 16719
are inserted in the same second and there order IS important.

kind regards

Hendrik Woltersdorf
XCOM AG
Tel.: [phone]
Tel. mobil: 0178/5710078
Email: Hendrik.Woltersdorf@stripped


                                                                           
             Tom Hanlon                                                    
             <Tom.Hanlon@stripped                                             
             OM>                                                        An 
             Gesendet von:              Cluster <cluster@stripped>  
             Tom.Hanlon@stripped                                       Kopie 
             M                                                             
                                                                     Thema 
                                        Re: WG: Re: Antwort: Re: howto     
             29.01.2010 20:26           implement a fifo queue in MySQL    
                                        Cluster ?                          
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




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







*** 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 [phone], Telefax [phone],
www.xcom.de
Handelsregister: Amtsgericht Krefeld, HRB 10340
Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
Vorsitzender des Aufsichtsrates: Stefan H. TarachAttachment: [text/html]

Attachment: [text/html]

Attachment: [text/html]