how to modify a table.

How do i modify a table. I need to set a column to allow nulls. But I am not finding any information. I see the --alter command, but nothing for alter.
pt-online-schema-change --modfiy mycolumn default null d=database, t=table

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 we appreciate 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

You can see how those two examples compare I think? But always (of course) test, test, test :slight_smile:

Here’s the link to the tool’s documentation https://www.percona.com/doc/percona-…ma-change.html

Ok thanks. I am really new to percona and I wasn’t sure how to use the tool. I am niew at this position and I was told to use the percona tool. Now I did read that the tools haven’t been tested with replication? Is that going to change in the future or not because you can’t account for individual setups?

Hello, no problem at all - we are always happy to hear from new users :slight_smile:

There is quite a lot about replication on the pt-online-schema-change manual page so it’s not quite right that it doesn’t work with replication. However, if you see that page there are conditions that have to be met. For example, by default the tool will abort if any replication filters are set. https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

There’s a blog post here that discusses pt-online-schema-change being used with replication https://www.percona.com/blog/2014/09/17/syncing-mysql-slave-table-with-pt-online-schema-change/

Do you have a link to where you read that it won’t work with replication? Only then we could see if it’s a specific circumstance that is being discussed.

Meanwhile, though,if you have a specific use case you would like advice on, let us know?

You might also find some useful webinars on our website, such as this one that looks at aspects of the Percona Toolkit: https://www.percona.com/resources/webinars/tools-every-sys-admindba-should-use