Not the answer you need?
Register and ask your own question!

how to modify a table.

pythondev1pythondev1 EntrantCurrent User Role Contributor
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

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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 :)

    Here's the link to the tool's documentation https://www.percona.com/doc/percona-...ma-change.html
  • pythondev1pythondev1 Entrant Current User Role Contributor
    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 :)

    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?
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello, no problem at all - we are always happy to hear from new users :)

    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
    Abort if any replication filter is set on any server. The tool looks for server options that filter replication, such as binlog_ignore_db and replicate_do_db. If it finds any such filters, it aborts with an error.

    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.