This question came back on my radar after a recent comment was posted.
This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.
The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).
RBR events do not fire triggers.
This sounds very unusual for a table using the ARCHIVE Storage Engine. Why? A duplicate key error is not characteristic for ARCHIVE Storage Engine since
- Engine does not support the creation of indexes
- Engine supports INSERTs and SELECTs
Surprisingly, there can be a key internally present. How?
According to the MySQL Documentation
The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.
Given this information, look back at the table and the query
insert into test_table (id,arch) values (123,'FILE_CONTENT')
If the id
column has the AUTO_INCREMENT
attribute, you should not specify id
with a value. It would produce a normal 1062 error (Duplicate Key) for other Storage Engines.
SUGGESTIONS
Change the insert
to a format that can handle the AUTO_INCREMENT attribute of id
insert into test_table (id,arch) values (0,'FILE_CONTENT')
or
insert into test_table (arch) values ('FILE_CONTENT')
Give it a Try !!!
UPDATE 2013-08-06 16:57 EST
If you are planning to do queries from the archive table, you need to get away from the ARCHIVE Storage Engine. Why? Again, according to the MySQL Documentation
Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used
Note that every SELECT against an ARCHIVE table is a full table scan. If you lookup id 123 with 1,000,000 rows, you gotta read 1,000,000 rows every time.
SUGGESTION
Convert the table to MyISAM. Then you can have a proper index on id
plus the ability to create other indexes on other columns as needed.
CREATE TABLE test_table_myisam ENGINE=MyISAM as SELECT * FROM test_table WHERE 1=2;
ALTER TABLE test_table_myisam ADD PRIMARY KEY (id);
INSERT IGNORE INTO test_table_myisam SELECT * FROM test_table;
DROP TABLE test_table;
ALTER TABLE test_table_myisam RENAME test_table;
Best Answer
if you want to change the value with the new one, you can use:
or if you want to add the new value to the existing one: