0

I have table with partitions by timestamp column (milliseconds). Theoretically it is unlikely to coincide, but can make the case that 2 inserts try to made with the same timestamp. Obviously it would throw an error for being duplicated the primary key.

What possible solution can there be in this case?

Improve this question
14
  • Why are you using the timestamp as the primary key? The column for partitioning doesn't have to be the primary key, it doesn't even have to be unique. Commented Jun 2, 2022 at 16:19
  • Solutions for solving the duplicate key? Try the ON DUPLICATE option that INSERT has Commented Jun 2, 2022 at 16:20
  • 2
    The most elegant solution is not to use partitioning. Partitioning, like every form of optimization, sacrifices elegance and simplicity. It optimizes for one type of query at the expense of other types of queries. Commented Jun 2, 2022 at 16:36
  • 1
    How does partitioning have anything to do with preventing corruption? I think this is an XY Problem. Commented Jun 2, 2022 at 16:37
  • 2
    The partition column has to be part of the unique key. So you can use PRIMARY KEY(id, timestamp) where id is a unique ID. timestamp doesn't have to be unique by itself. Commented Jun 2, 2022 at 16:46
Related questions
2
MySQL partitioning query on timestamp
25
how to partition a table by datetime column?
2
Mysql 5.6 Partitioning on timestamp (with microseconds)
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.