MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rhino Date:November 6 2004 2:17pm
Subject:Re: Reorder a table
View as plain text  
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" <Michael.Stassen@stripped>
To: "Rhino" <rhino1@stripped>
Cc: "Martin" <mmmails@stripped>; <mysql@stripped>
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" <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
> >>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/mysql?unsub=1
>
>

Thread
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