Emergency

how to modify a table.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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


  • #2
    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

    Comment


    • #3
      Originally posted by lorraine.pocklington View Post
      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?

      Comment


      • #4
        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-...ma-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...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/we...dba-should-use

        Comment

        Working...
        X