MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rhino Date:November 6 2004 12:36pm
Subject:Re: Reorder a table
View as plain text  
----- Original Message ----- 
From: "Martin" <mmmails@stripped>
To: <mysql@stripped>
Sent: Saturday, November 06, 2004 6:49 AM
Subject: Reorder a table


> 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

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