I've got a table on my small live database with several 1000 records.

Monitoring a bug today I discovered the table "id" column wasn't set to auto_increment and so i have a large number of records with id = .

:(


mysql> explain feed_products

+--------------+--------------+------+-----+---------+-------+

 Area         Type          Null  Key  Default  Extra 

+--------------+--------------+------+-----+---------+-------+

 id            int(11)       NO                       

 feed_id       int(11)       YES   MUL  NULL           

 title         varchar(255)  YES        NULL           

 description   text          YES        NULL           

 item_link     varchar(255)  NO         NULL           

 item_up-to-date  datetime      YES        NULL           

 item_id       varchar(255)  YES        NULL           

 produced_at    datetime      YES        NULL           

 up-to-date_at    datetime      YES        NULL           

 is_hidden     int(11)       YES                      

+--------------+--------------+------+-----+---------+-------+

What's strange is the fact that older records appear to possess auto_incremented. So 1 / 2 of my records possess the expected id, 1 / 2 of them don't

What Let me do is placed this column to auto_increment after which operate a statement that does the next:

  • take all feed_products where id is purchased by produced_at ASC
  • for all these products Let me set the id into the previous item's id + 1

I am much less hot with SQL though and I'd rather not corrupt the information on my small live server.

can anybody advise?

with respect to the size your table, and the potential of taking it offline, you can simply produce a second table with similar area schema and place choose your computer data ...

INSERT INTO _YourSecondTableName_  (feed_id,title,description,item_link,item_updated,item_id,created_at,updated_at,is_hidden)  
SELECT feed_id,title,description,item_link,item_updated,item_id,created_at,updated_at,is_hidden  
FROM _YourFirstTableName_  
ORDER BY created_at ASC

you can then drop the initial table and relabel the brand new one ...