Learned this the hard way. Need to alter a table's auto-increment with 16 million rows.

Now, it's taking quite sometime.

Any estimate how long it would take? Sorry.

  • Please run SHOW CREATE TABLE mydb.mytable\G and post the output (you can hide sensitive columns if you wish). – RolandoMySQLDBA Jul 21 '14 at 17:01
  • 2.5 hours on aws rds, medium size server. – Louie Miranda Aug 13 '14 at 8:10

It depends, could be an hour or so. Perhaps you may like to give percona toolkit a try; pt-online-schema-change.

pt-online-schema-change alters a table’s structure without blocking reads or writes.
Specify the database and table in the DSN. Do not use this tool before reading its
documentation and checking your backups carefully.

Source: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

  • Not sure if it helps with aut increment, if python's more your thing, you could look here. – Vérace Jul 21 '14 at 8:21

Two things I learned here.

  1. Insert a record, and edit the last insert id.

  2. Alter table auto-increment, which will take sometime.

It's better to just deal with the problem using #1

  • What do you mean by #1? Could you elaborate? – Colin 't Hart Aug 13 '14 at 8:26
  • Hi. #1 - Insert a record, and edit the last insert id. – Louie Miranda Aug 14 '14 at 14:47
  • 1
    That's not elaborating. That's repeating. I still have no idea what you mean. – Colin 't Hart Aug 14 '14 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.