0

I have a table with a last_update column, I want to add a partition based on the last_update column to remove old values.

My problem is that I have a unique key in the table which does not include the last_update column.

What are my options?

Should I change the way I work with this table and remove the unique index?

EDIT

CREATE TABLE `fileso` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hostId` int(11) unsigned NOT NULL COMMENT 'code',
  `sha256` binary(32) NOT NULL COMMENT 'meir',
  `fileName` varchar(150) DEFAULT NULL COMMENT 'meir',
  `fullPath` varchar(350) DEFAULT NULL COMMENT 'meir',
  `datein` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueness` (`uniqueness`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The unique key is an Hash representation of a couple of columns in the table(sha256, fullPath,hostId).

3
  • Please provide SHOW CREATE TABLE and explain the "unique key". If it is an AUTO_INCREMENT, there is no problem; I will explain why.
    – Rick James
    Oct 8 '17 at 16:28
  • @Rick James please check my update
    – omri
    Oct 9 '17 at 14:26
  • Wtf.. uniqueness binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' that seems like a red flag, why don't you just permit null values, and set it null? Oct 9 '17 at 21:08
1

If you are going to remove 'old' records, then you are defeating the "uniqueness" of uniqueness -- since you cannot check against already-deleted rows.

So, make that just a INDEX, not UNIQUE.

Meanwhile, of what use is id? You may as well promote uniqueness to be the PRIMARY KEY. (Yeah, this contradicts my previous recommendation.)

But, back to the question of PARTITIONing forcing the inclusion of last_update on all unique keys...

Plan A:

no `id`
PRIMARY KEY (uniqueness, last_update)

Uniqueness would need to be checked with a SELECT, not INSERT IGNORE.

Plan B:

no `id`
INDEX (uniqueness)

This has problems in Replication and other situations when you need to specifically identify a row.

Plan C:

keep `id`
PRIMARY KEY(id, last_update)
INDEX(uniqueness)

Uniqueness would need to be checked with a SELECT, not INSERT IGNORE.

I prefer Plan A, unless there is somthing to indicate that id is necessary.

Meanwhile, see my advice on using PARTITIONing for a sliding dataset -- here

Addenda

With Plan A, do something like this to prevent a race condition:

BEGIN;
$result = SELECT uniqueness FROM tbl WHERE uniqueness = $x FOR UPDATE;
if $result, you have a dup; ROLLBACK and do whatever is appropriate.
INSERT INTO tbl (...) VALUES (...);
COMMIT;

Be sure to check for errors at each step -- deadlocks and timeouts can happen (rarely). If so, start over at the BEGIN.

If practical, consider batching the SELECT and INSERT so you can check and insert multiple rows at the same time. (I assume dups are rare?)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.