He means that his primary key column has rows with id=1 and id=3, but not with id=2. That is, deletions have left holes in the sequence. He theorizes that renumbering will help. I agree with you that it won't.
Michael
Rhino wrote:
----- Original Message ----- From: "Martin" <[EMAIL PROTECTED]>
Hi all,
I have a problem with a news application, which writes news items into a mysql table. Recently it gets confused, when it inserts new news items
into
the table. I get the following exception:
"Duplicate entry '25' for key 1". root cause was java.sql.SQLException: Invalid argument value, message from server: "Duplicate entry '25' for key 1"
It seems that this application tries to overwrite existing news items and mysql blocks this (which is very good actually). This is a problem with
the
application, but I can't change this behaviour. I had a look at the table and the primary key of the datasets is not available for all datasets, for example I have primary keys 1 and 3, but not 2. So I could imagine that it would help to reorder the table.
This is a very confusing paragraph: first you express your happiness that MySQL is preventing the insertion of a duplicate, then you call it a problem. Why do you think it's a problem? Preventing duplicate keys in a table is one of the most important jobs a database has to do and your database is doing it.
You state that this is an application yet that you can't change the behaviour. Why not? If it's your application rather than a purchased one without source code, you CAN change the application: it's yours.
You state that you "had a look at the table and the primary key is not available for all datasets". What do you mean "not available": that the *primary key* doesn't exist or that the information about what the primary key is doesn't exist?
You say that "for example I have primary keys 1 and 3, but not 2": are you aware that a table can only EVER have one primary key? That is a rule in all relational databases, including MySQL. You can't possibly have two or three primary keys on a given table. Your primary key can contain several columns and you can have several UNIQUE keys on a table but you can only have ONE primary key on a table, regardless of how many columns it contains. Why do you think that re-ordering the table would help your situation? Also, I'm not clear what you mean by re-ordering: sorting the rows so that they appear in key sequence or unloading and reloading the rows into the table. Unless I'm completely misunderstanding your situation, NEITHER will help you with this "problem" because the database is not at fault here.
Could someone please tell me how to do the following with mysql? 1) copy the old news table into a new one and in this new table: 2) order the datasets by timestamp 3) change the primary key (an integer) for all datasets, so that the
oldest
datasets gets id 1
If you are attempting to insert duplicate rows, there are really only two solutions, neither of which involves "re-ordering" you table, whatever you mean by that: 1) Improve the error handling in the program so that it handles the attempted insertion of a duplicate more gracefully. Frankly, I think the program is working pretty well right now: it detects that a duplicate occurs and tells you about it so that you can determine which key was duplicated so that you can investigate why your input has a duplicate of that particular key; that's pretty much exactly what I would do if I were writing this kind of program. However, you *could* change the program to not tell you about the duplicates if the messages are bothering you. The only thing I would change is that I wouldn't throw an exception when this problem happens, I would simply report the duplicate to the user and move on to the next input record. 2) Stop supplying duplicate rows that cause this situation to occur. In other words, WHY are these duplicate keys appearing? Is there anything you can do BEFORE your program runs that would screen out the duplicates?
The other thing you could would be to remove whatever constraint is causing the incoming records to be perceived as duplicates; then MySQL would store the duplicates without generating an error message. But I get the impression that you don't want to store the duplicates; if that is right, DON'T DROP THE PRIMARY KEY OR UNIQUE CONSTRAINTS.
Don't blame MySQL for this situation though: the problem is either in the application or the incoming data. MySQL isn't doing anything wrong here.
Rhino
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
