Hendrik Woltersdorf wrote:
> Hi Tom,
>
> thanks for the interesting details about "set timestamp" and "now()", but
> the results of now() or last_insert_id() are ok.
Well MySQL is all about "interesting" solutions, so those "features"
might come in handy at a later date.
I imagine that the auto_increment is being reserved at transaction
start, and not at use, or at transaction commit.
That might cause this issue.
I can not think of any way to get the functionality you seek without
adding a table to maintain the sequences, and therefore a lock and a delay.
--
Tom
> 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.: 0375/27008-580
> 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
>
>
>> 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)
>
>
> --
> 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
--
Tom Hanlon
Certified MySQL DBA
Certified Cloudera Certified Hadoop Professional (CCHP)
