CODEWITHSUNDEEP
mysqlsql
Paritosh Singh
Paritosh Singh

Reputation: 6404

Mysql: Duplicate key error with autoincrement primary key

I have a table 'logging' in which we log visitor history. We have 14 millions pageviews in a day, so we insert 14 million records in table in a day, and traffic is highest in afternoon. From somedays we are facing the problems for duplicate key entry 'id', which according to me should not be the case, since id is autoincremented field and we are not explicitly passing id in insert query. Following are the details

logging (MyISAM)
----------------------------------------
| id                 | int(20)         |
| virtual_user_id    | varchar(1000)   |
| visited_page          | varchar(255) |
| /* More such columns are there */    |
----------------------------------------

Please let me know what is the problem here. Is keeping table in MyISAM a problem here.

Upvotes: 0

Views: 2896

Answers (1)

Arnout
Arnout

Reputation: 131

Problem 1: size of your primary key

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

The max size of an INT regardless of the size you give it is 2147483647, twice that much if unsigned. That means you get a problem every 153 days.

To prevent that you might want to change the datatype to an unsigned bigint. Or for even more ridiculously large volumes even a unix timestamp + microtime as a composite key. Or a different DB solution altogether.

Problem 2: the actual error

It might be concurrency, even though I don't find that very plausible. You'll have to provide the insert IDs / errors for that. Do you use transactions?

Another possibility is a corrupt table. Don't know your mysql version, but this might work:

CHECK TABLE tablename

See if that has any complaints.

REPAIR TABLE tablename

General advice:

Is this a sensible amount of data to be inserting into a database, and doesn't it slow everything down too much anyhow? I wonder how your DB performs with locking and all during the delete during for example an alter table.

The right way to do it totally depends on the goals and requirements of your system which I don't know, but here's an idea:

Log lines into a log. Import the log files in our own time. Don't bother your visitors with errors or delays when your DB is having trouble or when you need to do some big operation that locks everything.

Upvotes: 3

Related Questions