Martin, The following will do the resequencing you requested. Hope it helps.
create table temporary_table select * from the_table; set @T=0; update temporary_table set id=(@T:=(@T+1)), the_time=the_time order by the_time; delete from the_table; insert into the_table select * from temporary_table; drop table temp_table; ========== Original message follows ========== Date: Sat, 6 Nov 2004 15:18:50 +0100 (MET) From: "Martin" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] MIME-Version: 1.0 Subject: Re: Reorder a table Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit > He means that his primary key column has rows with id=1 and > id=3, but not with id=2. Thanks, this is exactly the problem. I did not blame mysql for anything - I like it a lot. I blamed the news application, to which I referred as "the application". I did not develop the application/ don't have the sources and this is why I am trying to find a workaround by modifying the table structure. >That is, deletions have left holes in the sequence. He > theorizes that renumbering will help. I agree with you that it won't. Yes, there are holes in the sequence. But the reason is not due to deletions, it is because the news application does not seem to use mysql's auto increment mechanism. The application seems to have an internal mechanism to increase the primary key and I cannot change this behaviour. However this behaviour was no problem until I had to restore the news table with a backup version. Since then I get the error message, which I described. So what the application does, is that it looks for the first free primary key and inserts the dataset with this key. Referring back to my example: I have datasets with IDs 1 and 3, id=2 is missing. In this case the application inserts a new dataset with id=2. For my next dataset it would try to use id=3, this is blocked (good! I want to keep this dataset), but it prevents the storage of all further news datasets. I am theorizing that renumbering the IDs will help, because in this case the application would find id=4 as first free key, could insert it and I could go on with inserting further datasets. So 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 Thanks, Martin > > 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 > > > > > -- Geschenkt: 3 Monate GMX ProMail + 3 Top-Spielfilme auf DVD ++ Jetzt kostenlos testen http://www.gmx.net/de/go/mail ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
