Run pt-online-schema-change on a deprecated ROW_FORMAT table


trying to run pto on a table that has ROW_FORMAT=fixed.

Mysql 5.6 used to silently convert to compact, but mysql 5.7 just straight up refuses to create a table in that format.

Unfortunately this means pto is unusable in tables like this. I am trying to remove all table definition row_formats where we have them (so it defaults to whatever is default for the version), but for that i need an alter, which i cannot run because pto cannot create the new table. I cannot run straight alter table commands as some tables are quite big and it will cause delays and cluster lock ups.

Any ideas how i can solve the issue? Hopefully other than going in and editing the pto script.

My only thought so far is that i can run pto through our proxysql and using the proxysql query rewriting features to force removal of the row_format clause on table creation when running pto. But that sounds too hacky.

just found out i can use: --[no]create-tmp-table


Sorry for wasting time, you can consider this question closed.

EDIT: OK, i spoke too soon :slight_smile: i cannot find that option in pto 3.0 ideas why thats missing? Looks like it got removed in version 2.1

EDIT2: And if i have to edit the pto script something like this makes sense to me, from 10695 line:

my $quoted = $q->quote($orig_tbl->{db}, $new_table_name);
      my $sql    = $ddl;
      $sql       =~ s/ROW_FORMAT=.*?( |$)/ /m; # this is my change - replace any ROW_FORMAT statements with empty ones, making sure it uses the engine default
      $sql	 =~ s/\ACREATE TABLE .*?\($/CREATE TABLE $quoted (/m;
but again, this feels veeery hacky.

EDIT3: Turning off innodb_strict_mode for the duration of the change also seems  to be an option:
 --set-vars innodb_strict_mode="off"