Изменить тип большой таблицы MySQL c INT на BIGINT?

Question

[nejmatovich]

Как можно сменить тип колонки автоинкремент с INT на BIGINT для большой таблицы?
Сейчас запрос выполняется уже несколько часов, и не известно сколько еще займет
База около 60 гигов.
Запрос:

ALTER TABLE table_name MODIFY COLUMN ID BIGINT AUTO_INCREMENT;

Comments

[nejmatovich]

pt-online-schema-change  --dryrun --alter "MODIFY COLUMN ID BIGINT AUTO_INCREMENT"  D=database,t=table

дает ошибку

pt-online-schema-change --dry-run --alter "MODIFY COLUMN ID BIGINT AUTO_INCREMENT"  D=database,t=table
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `database`.`table` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table database._table_new OK.
Altering new table...
Altered `database`.`_table_new` OK.
Not creating triggers because this is a dry run.
Error creating triggers: DBD::mysql::db selectall_arrayref failed: Incorrect database name '#mysql50#logs.2015-01-23' [for Statement "SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = ?    AND ACTION_TIMING = ?    AND TRIGGER_SCHEMA = ?    AND EVENT_OBJECT_TABLE = ?"] at /usr/bin/pt-online-schema-change line 11366.
Not dropping triggers because this is a dry run.
2021-06-02T23:02:04 Dropping new table...
2021-06-02T23:02:04 Dropped new table OK.
Dry run complete.  `database`.`table` was not altered.

И да, я осознанно тестирую с -dryrun

[nejmatovich]

имя базы верное

[Everything_is_not_so_bad]

nejmatovich, уже закончилась операция?

Answer 1

[toxa82]

pt-online-schema-change

Comments

[nejmatovich]

как должна будеть команда в этом случае?

[toxa82]

Ну примерно так, давно не работал с MySQL.

pt-online-schema-change --alter "MODIFY COLUMN ID BIGINT AUTO_INCREMENT"  D=database,t=table

[toxa82]

Роман Мирр, так речь о MySQL