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]
