MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 6 2004 12:55pm
Subject:Re: Reorder a table
View as plain text  
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.


Rhino wrote:

> ----- Original Message ----- 
> From: "Martin" <mmmails@stripped>
>>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
>>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
> 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
Reorder a tableMartin6 Nov
  • Re: Reorder a tableRhino6 Nov
    • Re: Reorder a tableMichael Stassen6 Nov
  • Re: Reorder a tableRhino6 Nov
Re: Reorder a tableMartin6 Nov
  • Re: Reorder a tableMichael Stassen7 Nov
Re: Reorder a tableBill Easton7 Nov