Thanks Michael! That seems like a more reasonable explanation of what the original poster meant than what I understood. I assume he'll clarify what he meant if either of us got it wrong....
Rhino ----- Original Message ----- From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "Martin" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, November 06, 2004 7:55 AM Subject: Re: Reorder a table > > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
