replacing a timestamped row
Hi everyone, I'm fairly new to MySQL and I have a procedure that writes some status info to a table. The problem is, I just want the values and row to be replaced, rather than constantly adding a new row. I tried using REPLACE as well as different timestamp methods, but I always get a new row. How can I ensure the old row is overwritten? Here's what I'm trying to do: create table check_master_log (ts timestamp, server_id int, master_status varchar(30)); mysql describe check_master_log; +---+-+--+-+---+-+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+---+-+ | ts| timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | server_id | int(11) | YES | | NULL | | | master_status | varchar(30) | YES | | NULL | | +---+-+--+-+---+-+ Then I have a script that runs every 15 seconds and does one of the following: insert into check_master_log values (now(), @@server_id, master OK); insert into check_master_log values (now(), @@server_id, master is dead); Everything works great, but I end up with a growing table that adds a new row every 15 seconds: | 2008-11-25 11:42:12 | 1 | master OK | | 2008-11-25 11:42:27 | 1 | master OK | | 2008-11-25 11:42:42 | 1 | master OK | | 2008-11-25 11:42:57 | 1 | master OK | +-+---+---+ What I'd like to do is just have it replace the existing one so I just have one status row that I can select and check. I'm sure it's just a matter of the proper replace and table syntax, but I haven't been able to get it to work without constantly appending rows. Any suggestions? Thanks in advance, -Dave -- David Halik System Administrator OIT-CSS Rutgers University [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replacing a timestamped row
Hi Dave, You have no primary key on your table, thus MySQL has no way of knowing when the row is unique and needs to be updated rather than inserted. REPLACE INTO effectively does the following: - insert into table - did a primary key violation occur? --- yes - delete existing row from table where primary key matches the record to be inserted - insert into table --- no - row was inserted OK Judging by your table layout, I'm guessing the server_id column would be a good candidate for a primary key - providing of course that you only need one status row per server. Delete all existing records, make server_id your primary key (and not null-able) and retry your script. Regards, Andy David Halik wrote: Hi everyone, I'm fairly new to MySQL and I have a procedure that writes some status info to a table. The problem is, I just want the values and row to be replaced, rather than constantly adding a new row. I tried using REPLACE as well as different timestamp methods, but I always get a new row. How can I ensure the old row is overwritten? Here's what I'm trying to do: create table check_master_log (ts timestamp, server_id int, master_status varchar(30)); mysql describe check_master_log; +---+-+--+-+---+-+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+---+-+ | ts| timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | server_id | int(11) | YES | | NULL | | | master_status | varchar(30) | YES | | NULL | | +---+-+--+-+---+-+ Then I have a script that runs every 15 seconds and does one of the following: insert into check_master_log values (now(), @@server_id, master OK); insert into check_master_log values (now(), @@server_id, master is dead); Everything works great, but I end up with a growing table that adds a new row every 15 seconds: | 2008-11-25 11:42:12 | 1 | master OK | | 2008-11-25 11:42:27 | 1 | master OK | | 2008-11-25 11:42:42 | 1 | master OK | | 2008-11-25 11:42:57 | 1 | master OK | +-+---+---+ What I'd like to do is just have it replace the existing one so I just have one status row that I can select and check. I'm sure it's just a matter of the proper replace and table syntax, but I haven't been able to get it to work without constantly appending rows. Any suggestions? Thanks in advance, -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replacing a timestamped row
Ack! Such a simple but important step. No wonder it wasn't replacing the existing row. That worked great, thank you very much. Also, thanks to Brent who replied first. I ended up going with the primary key over the unique index, but I'm sure that would have worked as well. -Dave Andy Shellam wrote: Hi Dave, You have no primary key on your table, thus MySQL has no way of knowing when the row is unique and needs to be updated rather than inserted. REPLACE INTO effectively does the following: - insert into table - did a primary key violation occur? --- yes - delete existing row from table where primary key matches the record to be inserted - insert into table --- no - row was inserted OK Judging by your table layout, I'm guessing the server_id column would be a good candidate for a primary key - providing of course that you only need one status row per server. Delete all existing records, make server_id your primary key (and not null-able) and retry your script. Regards, Andy David Halik wrote: Hi everyone, I'm fairly new to MySQL and I have a procedure that writes some status info to a table. The problem is, I just want the values and row to be replaced, rather than constantly adding a new row. I tried using REPLACE as well as different timestamp methods, but I always get a new row. How can I ensure the old row is overwritten? Here's what I'm trying to do: create table check_master_log (ts timestamp, server_id int, master_status varchar(30)); mysql describe check_master_log; +---+-+--+-+---+-+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+---+-+ | ts| timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | server_id | int(11) | YES | | NULL | | | master_status | varchar(30) | YES | | NULL | | +---+-+--+-+---+-+ Then I have a script that runs every 15 seconds and does one of the following: insert into check_master_log values (now(), @@server_id, master OK); insert into check_master_log values (now(), @@server_id, master is dead); Everything works great, but I end up with a growing table that adds a new row every 15 seconds: | 2008-11-25 11:42:12 | 1 | master OK | | 2008-11-25 11:42:27 | 1 | master OK | | 2008-11-25 11:42:42 | 1 | master OK | | 2008-11-25 11:42:57 | 1 | master OK | +-+---+---+ What I'd like to do is just have it replace the existing one so I just have one status row that I can select and check. I'm sure it's just a matter of the proper replace and table syntax, but I haven't been able to get it to work without constantly appending rows. Any suggestions? Thanks in advance, -Dave -- David Halik System Administrator OIT-CSS Rutgers University [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
