rootじゃないユーザーで実行したい。
mysql> select * from User where User = 'test-user' \G
*************************** 1. row ***************************
Host: localhost
User: test-user
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: N
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
別コンソールでmysqlに接続した状態で実行すると以下のエラーがでる
➜ ~ pt-online-schema-change --alter "DROP COLUMN last_logined_at" D=test,t=sql_test_huge,u=test-user --dry-run
Cannot connect to MySQL: DBI connect('test;;mysql_read_default_group=client','test-user',...) failed: Lost connection to MySQL server at 'sending authentication information', system error: 32 at /usr/local/Cellar/percona-toolkit/2.2.19_1/libexec/bin/pt-online-schema-change line 2282.
なんなんだ...
mysqlコマンドでは再現しない、、、と思ったが、mysqlコマンドで更に2つ接続すると再現した mysqlを再起動したら直ったが、また起きそうだ
そして再起動してから以下のエラーがでるように
➜ ~ pt-online-schema-change --alter "DROP COLUMN last_logined_at" D=test,t=sql_test_huge,u='test-user' --dry-run
DBD::mysql::db selectrow_arrayref failed: Table 'performance_schema.session_variables' doesn't exist [for Statement "SHOW VARIABLES LIKE 'wsrep_on'"] at /usr/local/Cellar/percona-toolkit/2.2.19_1/libexec/bin/pt-online-schema-change line 7866.
再現する
mysql> show variables like 'wsrep_on';
ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist
percona-toolkitを入れたときにmysql5.7が入ったので、乗っ取られた模様
501 55021 1 0 5:15PM ttys008 0:00.02 /bin/sh /usr/local/Cellar/mysql/5.7.17/bin/mysqld_safe --datadir=/usr/local/var/mysql --pid-file=/usr/local/var/mysql/mysql.pid
501 55175 55021 0 5:15PM ttys008 0:01.17 /usr/local/Cellar/mysql/5.7.17/bin/mysqld --basedir=/usr/local/Cellar/mysql/5.7.17 --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/Cellar/mysql/5.7.17/lib/plugin --log-error=/usr/local/var/mysql/PMAC258S.local.err --pid-file=/usr/local/var/mysql/mysql.pid
5.6に切り替える
brew unlink mysql
brew link mysql56 --force
起動する
➜ ~ mysql.server start
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/mysql.pid).
がーん どうやら5.7を起動したのでどこかが5.7になってて、5.6で起動しようとするとエラーになるようだ
ib_logfile0とib_logfile1を消したら起動した。 起動したけどsql_test_hugeテーブルが見えない、消せない、作れない。 しょうがないのでsql_test_huge2を作った
前と同じように1200マンレコードくらいインサートしてpt-osc実行する
➜ ~ pt-online-schema-change --alter "ADD COLUMN last_logined_at DATETIME" D=test,t=sql_test_huge2,u=test-user --execute
No slaves found. See --recursion-method if host PMAC258S.local has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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
Altering `test`.`sql_test_huge2`...
Creating new table...
Created new table test._sql_test_huge2_new OK.
Altering new table...
Altered `test`.`_sql_test_huge2_new` OK.
2017-04-06T17:55:17 Creating triggers...
2017-04-06T17:55:17 Dropping triggers...
2017-04-06T17:55:17 Dropped triggers OK.
2017-04-06T17:55:17 Dropping new table...
2017-04-06T17:55:17 Dropped new table OK.
`test`.`sql_test_huge2` was not altered.
Error creating triggers: 2017-04-06T17:55:17 DBD::mysql::db do failed: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) [for Statement "CREATE TRIGGER `pt_osc_test_sql_test_huge2_del` AFTER DELETE ON `test`.`sql_test_huge2` FOR EACH ROW DELETE IGNORE FROM `test`.`_sql_test_huge2_new` WHERE `test`.`_sql_test_huge2_new`.`id` <=> OLD.`id`"] at /usr/local/Cellar/percona-toolkit/2.2.19_1/libexec/bin/pt-online-schema-change line 10664.
SUPER権限必要なの...?
https://dev.mysql.com/doc/refman/5.6/ja/create-trigger.html
バイナリログが有効になっている場合は（なってます...）トリガーを作るのにSUPER権限が必要な場合があるらしい。
mysql> CREATE TRIGGER `pt_osc_test_sql_test_huge2_del` AFTER DELETE ON `test`.`sql_test_huge2` FOR EACH ROW DELETE IGNORE FROM `test`.`_sql_test_huge2_new` WHERE `test`.`_sql_test_huge2_new`.`id` <=> OLD.`id`;
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
なるほど〜
んでは、これで。
mysql> grant all on *.* to 'super-test-user'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql
Database changed
mysql> select * from User where User = 'super-test-user' \G
*************************** 1. row ***************************
Host: localhost
User: super-test-user
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
できた
➜ ~ pt-online-schema-change --alter "ADD COLUMN last_logined_at DATETIME" D=test,t=sql_test_huge2,u=super-test-user --execute
No slaves found. See --recursion-method if host PMAC258S.local has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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
Altering `test`.`sql_test_huge2`...
Creating new table...
Created new table test._sql_test_huge2_new OK.
Altering new table...
Altered `test`.`_sql_test_huge2_new` OK.
2017-04-06T18:06:16 Creating triggers...
2017-04-06T18:06:16 Created triggers OK.
2017-04-06T18:06:16 Copying approximately 12246097 rows...
Copying `test`.`sql_test_huge2`: 78% 00:08 remain
2017-04-06T18:06:55 Copied rows OK.
2017-04-06T18:06:55 Analyzing new table...
2017-04-06T18:06:55 Swapping tables...
2017-04-06T18:06:55 Swapped original and new tables OK.
2017-04-06T18:06:55 Dropping old table...
2017-04-06T18:06:55 Dropped old table `test`.`_sql_test_huge2_old` OK.
2017-04-06T18:06:55 Dropping triggers...
2017-04-06T18:06:55 Dropped triggers OK.
Successfully altered `test`.`sql_test_huge2`.
実行中に別コンソールからデータをインサートしてたけど問題なし。れすぽんすはちょっとおそかったが。
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.00 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.24 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.46 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.07 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
insert sql_test_huge2(password,name) values('pass','namaedayo');Query OK, 1 row affected (0.50 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.28 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.21 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.23 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.13 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.24 sec)
mysql> insert sql_test_huge2(password,name) values('pass','namaedayo');
Query OK, 1 row affected (0.25 sec)
mysql> insert sql_test_huge2(password,name) values('pass','oreda');
Query OK, 1 row affected (0.20 sec)
mysql> insert sql_test_huge2(password,name) values('pass2','namaedayo2');
Query OK, 1 row affected (0.16 sec)
mysql> desc sql_test_huge2;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| password | varchar(255) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| last_logined_at | datetime | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from sql_test_huge2 order by id desc limit 10 ;
+----------+----------+------------+-----------------+
| id | password | name | last_logined_at |
+----------+----------+------------+-----------------+
| 13041467 | pass2 | namaedayo2 | NULL |
| 13041466 | pass | oreda | NULL |
| 13041465 | pass | namaedayo | NULL |
| 13041464 | pass | namaedayo | NULL |
| 13041463 | pass | namaedayo | NULL |
| 13041462 | pass | namaedayo | NULL |
| 13041461 | pass | namaedayo | NULL |
| 13041460 | pass | namaedayo | NULL |
| 13041459 | pass | namaedayo | NULL |
| 13041458 | pass | namaedayo | NULL |
+----------+----------+------------+-----------------+
10 rows in set (0.00 sec)