Re: UPDATE triggers with REPLACE statements
Perfect!! This is the answer I was looking for. Thanks! I didn't know about this. Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
Good to know and good that you took time to read the manual, good approach. But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY UPDATE? The storage engine is a property of your table and you can set it and/or change it, it is the low-level layer (physical) of the database that takes care on how data is actually stored and retrieved. You can check your table with: SHOW TABLE STATUS LIKE 'your-table-name'; Manual page: http://kae.li/iiiga Cheers Claudio 2011/12/19 Jim McNeely j...@newcenturydata.com In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio
Re: UPDATE triggers with REPLACE statements
With REPLACE, you just set up the query the same as an INSERT statement but otherwise it just works. With ON DUPLICATE UPDATE you have to set up the whole query with the entire text all over again as an update. The query strings for what I'm doing are in some cases pushing enough text in medical report fields that it uses a MediumText data type, and I am watchful of bandwidth and performance, so this seems better - I'm not sending the field names and values twice. It is also something I don't have to program, I can just set the engine. The performance bottleneck is NOT likely going to be MySQL with either engine, but the processes creating these queries have some limitations. Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Jim McNeely On Dec 19, 2011, at 1:28 PM, Claudio Nanni wrote: Good to know and good that you took time to read the manual, good approach. But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY UPDATE? The storage engine is a property of your table and you can set it and/or change it, it is the low-level layer (physical) of the database that takes care on how data is actually stored and retrieved. You can check your table with: SHOW TABLE STATUS LIKE 'your-table-name'; Manual page: http://kae.li/iiiga Cheers Claudio 2011/12/19 Jim McNeely j...@newcenturydata.com In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
Not if you are using innoDB tables. For these, you use INSERT and UPDATE triggers. Jim McNeely On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote: 2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/19 13:55 -0800, Jim McNeely Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Thank you. Which version? Well, then the documentation is wrong: it is indeed visible to the user which happens. And what would innoDB do in this case, UNIQUE index X1 and UNIQUE index X2, with a VARCHAR f, too, X1 X2 f 1 2 a 2 3 b and one REPLACEs with (2, 2, 'b')? (Earlier in the section such a situation is referred to.) Delete one and UPDATE the other? User beware! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
