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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
