CODEWITHSUNDEEP
mysql
phil
phil

Reputation: 4908

Changing column precision on mysql table with 52 million rows

Some idiot (read: me) inadvertently added columns to a MySQL table with the wrong precision. Everything was fine until we started to deal with Japanese customers, and the columns are now not large enough.

The problem is that the table in question now has 52 million rows in it.

There are actually 18 columns (!) that are wrong, but only two that are in need of changing. If there is a way to do all 18 at once, ok. Otherwise just the two.

How can I go about doing this (on production) without locking the table or causing other issues? And ideally not taking 24 hours to do.

Is creating a new table, copying the data and then renaming/dropping the way to go?

  1. Create new table with correct definition
  2. Copy data from old to new
  3. Rename old table
  4. Rename new table
  5. Drop old table

Which is best?

Upvotes: 1

Views: 656

Answers (1)

fancyPants
fancyPants

Reputation: 51868

That is basically the way to go, yes. I'd recommend to use a tool for this. Have a look at pt-online-schema-change. It's part of the the percona-toolkit.

What it does is creating triggers on the table, so that new/updated data is transfered over to the temporary table. When it's done copying the data to the new (altered) table, it renames the tables.

An example usage would be like this:

# pt-online-schema-change --dry-run --alter "change col1 col1 decimal(10,2), change col2 col2 decimal(10,2)" --alter-foreign-keys-method=auto --max-load Threads_connected=500 --critical-load Threads_connected=1000 D=your_database_name,t=your_table_name,u=your_mysql_user,p=your_mysql_user_password

But please, read the manual carefully. This approach also doesn't work, when there are already triggers on the table (depending on the MySQL version you're using. Newer versions allow multiple triggers on the same table).

Upvotes: 1

Related Questions