0

I have a rather big problem here... We have a MySQL database with a table containing over 26 million rows. Now, the problem is that my Java code is generating the primary keys (via a Hibernate annotation), since the column doesn't have an auto_increment flag. (Don't know why)

This setup can cause problems from time to time, such as constraint violations on the PK column. I would like to change it so that MySQL generates the auto incremented PKs.

Is there any way to add an auto_increment to the column without having to rebuild this massive table? In a timely manner, since our service window is at only 2 hours.

Regards

Robin Jonsson
  • 2,761
  • 3
  • 22
  • 42
  • I suggest you try it on a small sample of the table as a temporary table. I'd be surprised if it took any kind of a rebuild at all. You'll have to set the next value of the autoincrement key after you redefine it as such. – user207421 Dec 05 '14 at 08:38

1 Answers1

0

Yes, that's quite easy. First, you have to find out what your hightest index is:

SELECT max(id_column) FROM your_table;

Then alter the table to auto-increment

ALTER TABLE your_table CHANGE COLUMN id_column AUTO_INCREMENT;
ALTER TABLE your_table AUTO_INCREMENT = the_value_from_above + 1;

I'm not 100% sure about the exact syntax, so you might want to check here or use MySQL Workbench. This will not rebuild the table, just change the meta-data.

Keep in mind you also have to make hibernate aware of the auto_increment.

Chris
  • 834
  • 1
  • 10
  • 23
  • This will cause MySQL to copy the entire table to a temporary table, and after that it will make the modifications and copy the stuff back. Trust me, it does not work. It would take up to 12 hours, because of our huge amount of data in this table – Robin Jonsson Dec 05 '14 at 08:58
  • Oh, I was absolutely sure it would not do that! Maybe it depends on the version or table engine? How about hacking the table-definitions directly. You do have a test-system, don't you? – Chris Dec 05 '14 at 09:04
  • We're using INNODB, and we tried running a similar question, which did what I commented above :( – Robin Jonsson Dec 05 '14 at 09:40
  • MODIFY COLUMN and CHANGE COLUMN does not make any difference, sorry. It seems like you can't modify a column without it dumping the whole table into a temp table. Modifying the table-def is not 100% secure, and we have decided that it's better to just leave it as it is.Sadly, we can't allow this to take the amount of time it does, so – Robin Jonsson Dec 05 '14 at 10:10