2

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with InnoDB storage engine for the table.

However, the table must be fully available for SELECT and UPDATE queries while doing the change. Every value in the column is a lot smaller than 512 bytes (in fact, no value is larger than 11 bytes). Is it somehow possible to change the column type without any downtime or at least very small downtime?

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
pp3345
  • 21
  • 1

1 Answers1

2

There are at least two tools which attempt to mitigate the pain on online shema modifications - both by big hitters in the MySQL world. One is pt-online-schema-change from Percona and the other is oak-online-alter-table from Shlomi Noach. Could depend on whether you're more at ease with perl (percona) or python (Noach).

Needless to say, I urge you to test before implementing a solution on production.

[EDIT1]

It's unclear as to which of the toolkits (or which particular tool, if any) works with Windows - no mention of an install for oak-toolkit - there's one mention of Windows in the 366 page Percona pt-toolkit manual. In any case, for Windows, an install from source appears to be necessary.

There is however a tool by Facebook called OSC (Online Schema Change) which is a PHP script - could be suitable for Windows? Windows isn't really my thing, but there's an article here.

[EDIT2]

There's yet another solution available here - it involves replication, cutting over and cutting back. Appears to have the advantage of working under Windows. Again, you can test (and YMMV).

Found something even better - here. Fromdual are big on the MySQL scene. You're on 5.6 so this should apply.

Community
  • 1
Vérace
  • 30,923
  • 9
  • 73
  • 85