Question 174055 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to do drop column in my sql when ndb_allow_copying_alter_table=OFF

we are dropping a column using mysql alter command but admin has set ndb_allow_copying_alter_table=OFF recently and now while running the alter query we are getting below error:

ERROR 1846 (0A000): Implicit copying alter is not supported. Reason:ndb_allow_copying_alter_table=0. Try ALGORITHM=COPY to force the alter.

Has anyone faced this earlier?

Tags:

2

Answers


  1. 0

    I would try using the instant method to see if that works:
    Instant Operation

    This should look like this:

    ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

    If you are getting again an error that you should use "ALGORITHM=COPY" instead, please let me know.

    Login or Signup to reply.
  2. 0

    config:ndb_allow_copying_alter_table=OFF to Confirm that the modified table structure will not be displayed

    when your mysql version > 8.0，you can use

    ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

    it’s will force modification of table structure,but this operation will modified other mysql machine in the cluster, you must be clear to know what you are doing.

    The command can be used to immediately delete table columns without copying the table, without the need for temporary tables, making it faster and more efficient, If the deleted column does not exist on any other related objects such as indexes, constraints, triggers, etc., the ALGORITHM=INSTANT option can be used to perform the immediate deletion. Otherwise, MySQL will fall back to copying the table to perform the deletion operation.

    if you want to use coping method to delete this column,you can use other command:

    ALTER TABLE table_name DROP COLUMN column_name, ALGORITHM=COPY;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search