MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 7 2004 5:16pm
Subject:Re: Reorder a table
View as plain text  
I'm still skeptical this will solve the problem, but here's one way to do 
what you asked:

   ALTER TABLE news_table
   DROP id,
   ORDER BY timestamp_column;

That will drop the id column and reorder the table according to the values 
in timestamp_column.  (If there have been no deletions, aren't the rows 
already in order by timestamp?)  Change the table and column names to suit, 
of course.  Then

   ALTER TABLE news_table
   ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

to create and fill a new id column.

Now, as I said, I'm not sure this is the best way to fix the problem with 
the news app.  You haven't said what news app you are using (and I probably 
haven't used it anyway), so I have to admit that I'm speculating, but I see 
two potential problems:

1) Referential integrity:  If you have anything which references news items 
by id (follow-up news items, perhaps), you will break that link when you 
renumber.

2) I am skeptical that the news app chooses ids as you expect.  Your 
description doesn't fit your theory.  You say, "...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...", but then you 
say, "...it looks for the first free primary key and inserts the dataset 
with this key."  I find it very hard to believe that the app looks at the id 
column of the news table to find the first free id, then assumes all ids 
above that are free.  That would be very poor design, not to mention that it 
is much easier to write the query to find the MAX id than to find the first 
free id.  It seems like a lot of extra work to achieve a broken result, when 
the correct course is easier.  I suppose it is possible, but I'd be very 
surprised.

If the app looked for free ids to use before each insert, there should be no 
problem (other than poor performance relative to simply using the next 
auto_increment id).  The duplicate key error rules this out.

I suspect your first statement is right.  That is, the app creates its own 
keys with no regard to the table.  If that's the case, altering the table 
won't help.  I expect the app stores the next available id somewhere (it has 
to be able to find it on startup), and it is set to 25, which is wrong. 
Your best bet, I think, is to find where the news app keeps this and correct it.

Michael

Martin wrote:

>>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

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