rake db:schema:loadの挙動によって、開発DBをふっ飛ばしてしまうということをしてしまったので、挙動の調査をしました。基本的にrake db:schema:loadはschema.rbからテーブルを作成します。
今回はMySQLのgeneral.logのログから動作を確認しました。
環境
Rails 5.0.2
MySQL 5.7.17
詳細調査
force: trueをつけるかどうかで挙動が変わります。
force: true ををつける
schema.rb
ActiveRecord::Schema.define(version: 20170501083658) do
create_table "users", force: :true, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
t.string "name"
t.string "email"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
end
で実行
➜ dbtest rake db:schema:load
Running via Spring preloader in process 52941
-- create_table("users", {:force=>:true, :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8"})
-> 0.0194s
-- initialize_schema_migrations_table()
-> 0.0005s
-- create_table("users", {:force=>:true, :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8"})
-> 0.0121s
-- initialize_schema_migrations_table()
-> 0.0009s
MySQLのgeneral.log
2017-05-01T08:53:59.533073Z 15 Connect root@localhost on dbtest_development using Socket
2017-05-01T08:53:59.535166Z 15 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T08:53:59.567419Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:53:59.575038Z 15 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T08:53:59.576380Z 15 Query SHOW FULL FIELDS FROM `schema_migrations`
2017-05-01T08:53:59.577500Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:53:59.581508Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.581989Z 15 Query SHOW FULL FIELDS FROM `ar_internal_metadata`
2017-05-01T08:53:59.583034Z 15 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T08:53:59.583417Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:53:59.583806Z 15 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T08:53:59.584224Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.584821Z 15 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T08:53:59.585168Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:53:59.585509Z 15 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T08:53:59.585864Z 15 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.586349Z 15 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T08:53:59.587039Z 15 Quit
2017-05-01T08:53:59.588348Z 16 Connect root@localhost on dbtest_development using Socket
2017-05-01T08:53:59.588627Z 16 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T08:53:59.589555Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'users'
2017-05-01T08:53:59.595610Z 16 Query DROP TABLE `users`
2017-05-01T08:53:59.597992Z 16 Query CREATE TABLE `users` (`id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `email` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-05-01T08:53:59.607792Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:53:59.608170Z 16 Query SELECT version FROM `schema_migrations`
2017-05-01T08:53:59.608661Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'active_record_internal_metadatas'
2017-05-01T08:53:59.609065Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.610542Z 16 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T08:53:59.611298Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.613716Z 16 Query BEGIN
2017-05-01T08:53:59.615276Z 16 Query COMMIT
2017-05-01T08:53:59.615589Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'active_record_internal_metadatas'
2017-05-01T08:53:59.615929Z 16 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.616533Z 16 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T08:53:59.617055Z 16 Query BEGIN
2017-05-01T08:53:59.618133Z 16 Query COMMIT
2017-05-01T08:53:59.618672Z 16 Quit
2017-05-01T08:53:59.619391Z 17 Connect root@localhost on dbtest_test using Socket
2017-05-01T08:53:59.619640Z 17 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T08:53:59.620091Z 17 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'users'
2017-05-01T08:53:59.620428Z 17 Query DROP TABLE `users`
2017-05-01T08:53:59.622224Z 17 Query CREATE TABLE `users` (`id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `email` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-05-01T08:53:59.631706Z 17 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'schema_migrations'
2017-05-01T08:53:59.632363Z 17 Query SELECT version FROM `schema_migrations`
2017-05-01T08:53:59.632913Z 17 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'active_record_internal_metadatas'
2017-05-01T08:53:59.633217Z 17 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.633833Z 17 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T08:53:59.634513Z 17 Query BEGIN
2017-05-01T08:53:59.635633Z 17 Query COMMIT
2017-05-01T08:53:59.635931Z 17 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'active_record_internal_metadatas'
2017-05-01T08:53:59.636253Z 17 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'ar_internal_metadata'
2017-05-01T08:53:59.636826Z 17 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T08:53:59.637364Z 17 Query BEGIN
2017-05-01T08:53:59.638490Z 17 Query COMMIT
2017-05-01T08:53:59.639062Z 17 Quit
development DB, test DB を対象にテーブルを DROP and CREATEしている模様。
force: true をつけない
schema.rb
ActiveRecord::Schema.define(version: 20170501083658) do
create_table "users", force: :true, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
t.string "name"
t.string "email"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
end
usersテーブルは作成済み
➜ dbtest rake db:schema:load
Running via Spring preloader in process 53603
-- create_table("users", {:options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8"})
rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'users' already exists: CREATE TABLE `users` (`id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `email` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
/Users/katsuya/dev/dbtest/db/schema.rb:3:in `block in <top (required)>'
/Users/katsuya/dev/dbtest/db/schema.rb:1:in `<top (required)>'
-e:1:in `<main>'
Mysql2::Error: Table 'users' already exists
/Users/katsuya/dev/dbtest/db/schema.rb:3:in `block in <top (required)>'
/Users/katsuya/dev/dbtest/db/schema.rb:1:in `<top (required)>'
-e:1:in `<main>'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)
MySQLのgeneral.log
2017-05-01T08:57:38.458187Z 21 Connect root@localhost on dbtest_development using Socket
2017-05-01T08:57:38.460069Z 21 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T08:57:38.479922Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:57:38.487417Z 21 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T08:57:38.488904Z 21 Query SHOW FULL FIELDS FROM `schema_migrations`
2017-05-01T08:57:38.490100Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:57:38.494160Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:57:38.494907Z 21 Query SHOW FULL FIELDS FROM `ar_internal_metadata`
2017-05-01T08:57:38.496056Z 21 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T08:57:38.496520Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:57:38.496884Z 21 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T08:57:38.497301Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:57:38.497838Z 21 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T08:57:38.498172Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T08:57:38.498552Z 21 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T08:57:38.498942Z 21 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T08:57:38.499444Z 21 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T08:57:38.500130Z 21 Quit
2017-05-01T08:57:38.501474Z 22 Connect root@localhost on dbtest_development using Socket
2017-05-01T08:57:38.501766Z 22 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T08:57:38.503019Z 22 Query CREATE TABLE `users` (`id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `email` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-05-01T08:57:38.516536Z 22 Quit
テーブルCREATEしようとして、終了している模様
usersテーブルなし
➜ dbtest rake db:schema:load
Running via Spring preloader in process 54994
-- create_table("users", {:options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8"})
-> 0.0109s
-- initialize_schema_migrations_table()
-> 0.0007s
-- create_table("users", {:options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8"})
-> 0.0080s
-- initialize_schema_migrations_table()
-> 0.0005s
MySQLのgeneral.log
2017-05-01T09:08:47.499678Z 37 Connect root@localhost on dbtest_development using Socket
2017-05-01T09:08:47.502622Z 37 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T09:08:47.533895Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T09:08:47.546871Z 37 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T09:08:47.548897Z 37 Query SHOW FULL FIELDS FROM `schema_migrations`
2017-05-01T09:08:47.550133Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T09:08:47.555541Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.556161Z 37 Query SHOW FULL FIELDS FROM `ar_internal_metadata`
2017-05-01T09:08:47.557279Z 37 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T09:08:47.557694Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T09:08:47.558008Z 37 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T09:08:47.558405Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.558836Z 37 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T09:08:47.559174Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T09:08:47.559516Z 37 Query SELECT `schema_migrations`.* FROM `schema_migrations`
2017-05-01T09:08:47.559911Z 37 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.560341Z 37 Query SELECT `ar_internal_metadata`.`value` FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment'
2017-05-01T09:08:47.560975Z 37 Quit
2017-05-01T09:08:47.562518Z 38 Connect root@localhost on dbtest_development using Socket
2017-05-01T09:08:47.562834Z 38 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T09:08:47.563986Z 38 Query CREATE TABLE `users` (`id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `email` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-05-01T09:08:47.573695Z 38 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'schema_migrations'
2017-05-01T09:08:47.574189Z 38 Query SELECT version FROM `schema_migrations`
2017-05-01T09:08:47.574840Z 38 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'active_record_internal_metadatas'
2017-05-01T09:08:47.575122Z 38 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.576684Z 38 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T09:08:47.577414Z 38 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.580126Z 38 Query BEGIN
2017-05-01T09:08:47.581566Z 38 Query COMMIT
2017-05-01T09:08:47.581881Z 38 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'active_record_internal_metadatas'
2017-05-01T09:08:47.582190Z 38 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_development' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.582747Z 38 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T09:08:47.583250Z 38 Query BEGIN
2017-05-01T09:08:47.584157Z 38 Query COMMIT
2017-05-01T09:08:47.584717Z 38 Quit
2017-05-01T09:08:47.585455Z 39 Connect root@localhost on dbtest_test using Socket
2017-05-01T09:08:47.585775Z 39 Query SET NAMES utf8, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2017-05-01T09:08:47.586366Z 39 Query CREATE TABLE `users` (`id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255), `email` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
2017-05-01T09:08:47.593522Z 39 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'schema_migrations'
2017-05-01T09:08:47.593854Z 39 Query SELECT version FROM `schema_migrations`
2017-05-01T09:08:47.594335Z 39 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'active_record_internal_metadatas'
2017-05-01T09:08:47.594631Z 39 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.595272Z 39 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T09:08:47.595848Z 39 Query BEGIN
2017-05-01T09:08:47.597049Z 39 Query COMMIT
2017-05-01T09:08:47.597362Z 39 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'active_record_internal_metadatas'
2017-05-01T09:08:47.597746Z 39 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbtest_test' AND table_name = 'ar_internal_metadata'
2017-05-01T09:08:47.598347Z 39 Query SELECT `ar_internal_metadata`.* FROM `ar_internal_metadata` WHERE `ar_internal_metadata`.`key` = 'environment' LIMIT 1
2017-05-01T09:08:47.599279Z 39 Query BEGIN
2017-05-01T09:08:47.600515Z 39 Query COMMIT
2017-05-01T09:08:47.601027Z 39 Quit
development DB, test DB を対象にテーブルをCREATEしている模様。
挙動まとめ
force: true 付き
- testDB, developmentDB が対象
- schema.rb に書いてあるDBが存在する場合
DROPandCREATEを実行 -
force: trueの代わりにforce: cascadeとすることで、cascade deleteが行える
force: trueなしの場合
- testDB, developmentDB が対象
- schema.rb に書いてあるDBが存在しない場合
CREATEを実行 - 既存DBにテーブルが存在する場合失敗する
まとめ
-
rake db:schema:loadの挙動によってDBを飛ばしてしまう可能性があるので、必ず接続先を確認しましょう -
DROP動作の禁止をAPP側でするときは、force: trueをつけないようにしましょう - DB側で
DROP動作を禁止にすれば、DBすっ飛ばしを阻止することができます。