Search code examples
mysqlperconapt-online-schema-change

Percona modify table to allow nulls

how do you modify a table to allow nulls using percona.

pt-online-schema-change --modfiy mycolumn default null d=database, t=table

I see the --alter but nothing to modify an existing column.

Solution

  • First of all, you wouldn't HAVE to use pt-online-schema-change to achieve this as you can do it in native SQL (though you may have a reason for asking how to do it with pt-online-schema-change)

    For this table:

    DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE t1 (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40)
) ENGINE=InnoDB;

    You can use this SQL:

    ALTER TABLE `test`.`t1` CHANGE COLUMN name name VARCHAR(40) NULL;

    Next, though, if you have a good reason to use pt-online-schema-change, for example if the table is very big, then this would be the syntax:

    pt-online-schema-change h=127.0.0.1,P=3306,u=user,p=password,D=test,t=t1 --alter "CHANGE COLUMN name name VARCHAR(40) NULL" --execute

    Here's the link to the tool's documentation https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html