Изменить тип большой таблицы MySQL c INT на BIGINT?
Как можно сменить тип колонки автоинкремент с INT на BIGINT для большой таблицы?
Сейчас запрос выполняется уже несколько часов, и не известно сколько еще займет
База около 60 гигов.
Запрос:
ALTER TABLE table_name MODIFY COLUMN ID BIGINT AUTO_INCREMENT;
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.
Простой
