Share
Embed
Active Record connection adaptersについて私が知っているいくつかの事柄
Yasuo Honda
July 14, 2018
Transcript
44 slides
-
Who am I • Yasuo Honda (@yahonda at GitHub and
twitter) • Maintainer of Oracle enhanced adapter • Rails contributor • Asakusa.rb / OSS ύονձ • freee גࣜձࣾ -
ͳ͞ͳ͍͜ͱ • Active Record Oracle enhanced adapterͷ • Connection adapterҎ֎ͷActive
Recordͷ • RailsΞϓϦέʔγϣϯ։ൃͷ -
Databases supported by Rails • σʔλϕʔεɺActive RecordσʔλϕʔεΞμϓλɺgemͷΈ߹Θͤ • SQLite database
• “sqlite" adapter with “sqlite3” gem • MySQL database • “mysql2” adapter with “mysql2” gem • PostgreSQL database • “postgresql” adapter with “pg” gem -
Database versions supported by Rails 5.2 • raise "Your version
of MySQL (#{version_string}) is too old. Active Record supports MySQL >= 5.1.10." • raise "Your version of PostgreSQL (#{postgresql_version}) is too old. Active Record supports PostgreSQL >= 9.1.” • No “too old” in SQLite adapter yet -
too new? • “too new”ఆٛ͞Ε͍ͯͳ͍ • Rails 5.2Ͱ“αϙʔτ”͞Ε͍ͯΔόʔδϣϯ: • MySQL
5.1, 5.5, 5.6, 5.7 and 8.0 • PostgreSQL 9.1, 9.2, 9.3, 9.4, 9.5, 9.6 and 10 • Note: ”αϙʔτ””it should work”͙Β͍ͷҙຯ -
Changes to support new databases • MySQL 5.7 • MySQL
8.0 • PostgreSQL 10 • MariaDB 10.3 • MariaDB 10.2 -
MySQL 5.7 (1) • rails/rails#13247 Remove `DEFAULT NULL` for primary
key column to support MySQL 5.7.3 • MySQL 5.7.3͔ΒPRIMARY KEYͱDEFAULT NULLͷΈ߹Θ ͕ͤΤϥʔͱͳͬͨ • Migration͕ੜ͢ΔPrimary Key͕͜ͷΈ߹Θͤͩͬͨ • ”int(11) DEFAULT NULL auto_increment PRIMARY KEY” • Fix: “DEFAULT NULL”Λআ͢Δ -
MySQL 5.7 (2) • rails/rails#19359 "Materialize subqueries by adding `DISTINCT`
to suport MySQL 5.7.6 and later" • MySQL 5.7.6͔Β”optimizer_switch=‘derived_merge=on’"͕σϑΥϧτ Ͱಋೖ͞ΕɺಉҰDMLͰߋ৽ରͷςʔϒϧΛαϒΫΤϦͰࢀরͰ ͖ͳ͘ͳͬͨ • Fix: αϒΫΤϦʹඞͣDISTINCTΛ༩͠ɺϚςϦΞϧԽ͢Δ͜ͱͰผ ςʔϒϧѻ͍ʹ͢Δ(Refer rails/rails#22241 for the better fix) • https://bugs.mysql.com/bug.php?id=76259 ܦ༝ͰMySQL 5.7ϦϑΝϨ ϯεΨΠυʹࡌͤͯΒ͍·ͨ͠ -
MySQL 5.7 (3) • rails/rails#21318 "Support MySQL 5.7.8 which enables
show_compatibility_56=off” • MySQL 5.7.8͔Β`show variables .. where` ͕ ΤϥʔΛฦ͢Α͏ʹͳͬͨ • Fix: `select @@…`ʹॻ͖͑ -
MySQL 8.0 (1) • rails/rails#28730 “MySQL 8.0.1 DMR : ActiveRecord::StatementInvalid:
Mysql2::Error: Unknown collation: ‘utf8_0900_ai_ci'" • Fix : rails/rails#28733 "Don't fallback to utf8mb3 after MySQL 8.0.0” • ࠷େ͕4byteҎ্ͷΩϟϥΫληοτ(ྫ: utf8mb4)ར༻࣌ɺar_internal_metadata schema_migrationsͷ෦ςʔϒϧ࡞࣌ɺ Mysql2::Error: Index column size too large. ΤϥʔΛճආ͢ΔͨΊɺcollationΛม͍ͤͯͨ͞ • MySQL 8.0Ҏ߱ͰͦͷϑΥʔϧόοΫΛߦΘͳ͍Α͏ʹͨ͠ • ϑΥʔϧόοΫϧʔϧʹ͋͏collation͕MySQL 8.0Ͱͳ͘ͳͬͨ • ΠϯσοΫεͷ࠷େ͕3072byteͰɺͦͦϑΥʔϧόοΫ͕ෆཁʹͳͬͨ -
MySQL 8.0 (2) • rails/rails#26476 "MySQL 8.0.0-dmr test_multiple_foreign_keys_can_be_added_to_the_same_tabl e fails
due to only 1 fk information shown” • MySQL 8.0.0Ͱ1ͭͷςʔϒϧʹ2ͭͷforeign keys͕ுΒΕ͍ͯ Δͷʹɺinfomation_schema͔Β1͔ͭ͠ݕࡧ͞Εͳ͍ • Fix: MySQL 8.0ͷͲ͔͜ͷDMRͰमਖ਼͞Ε·ͨ͠ • https://bugs.mysql.com/bug.php?id=82961 "Information_schema Foreign key meta data differs in 8.0.0 -
MySQL 8.0 (3) • brianmario/mysql2# 840 "Use `bool` instead of
`my_bool` which has been removed since MySQL 8.0.1” • MySQL 8.0.1Ͱmy_bool͕ഇࢭʹͳͬͨ • Fix: #include <stdbool.h> ͯ͠ɺmy_boolΛboolʹ͓͖ ͔͑Δ • mysql2 0.4.6ͰϦϦʔε͞Ε·ͨ͠ -
PostgreSQL 10 • rails/rails#28864 “Support PostgreSQL 10 `pg_sequence`” • PostgreSQL
10Ͱ֤γʔέϯε͕”increment_by”Λ ࣋ͨͳ͘ͳΓɺ”pg_sequence”Χλϩά͔Βऔಘ͢ ΔΑ͏ʹͳͬͨ • Fix: PostgreSQL 10Ҏ্ͳΒpg_sequence͔Βऔಘ ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠ -
MariaDB 10.3 • rails/rails#33028 CI against MariaDB 10.3 #33028 •
MariaDB 10.3ͰOracleޓػೳͷ෭࡞༻ ͱͯ͠ɺ `LENGTH()`͕ؔ`OCTET_LENGTH()`ʹม͞ΕΔΑ ͏ʹͳͬͨ • MySQLͱҟͳΔಈ࡞ • Fix: MariaDB 10.3ͷಈ࡞ʹैͬͨςετίʔυͷมߋ -
MariaDB 10.2 • rails/rails#30485 "test_remove_column_with_multi_column_ind ex gets error with MariaDB
10.2.8" • multi column ͔ͭ uniqueΠϯσοΫεͷ͏ ͪɺҰͭͷΧϥϜΛremove_column͠Α͏ͱ͠ ͨͱ͖ͷಈ࡞͕MariaDB 10.2Ͱมߋʹͳͬͨ -
MariaDB 10.2 - cont • MariaDB 10.2.7Ҏલ & MySQL •
ΧϥϜ͕আ͞ΕɺΓͷΧϥϜͰ୯ҰΧϥϜΠϯσοΫεͱͯ͠Δ • MariaDB 10.2.8Ҏ߱ • ΧϥϜ͕আ͕ڐՄ͞Εͳ͍ • “Key column 'hat_size' doesn't exist in table: ALTER TABLE `test_models` DROP `hat_size`” • PostgreSQL ͱ Oracle • ΧϥϜ͕আ͞Εෳ߹ΠϯσοΫεશ෦ΛDROP͢Δ -
“Every database and/or database adapter has their own behavior if
it drops the multi-column index when any of the indexed columns dropped by remove_column.” rails/rails#8678 -
৽͍͠σʔλϕʔεαϙʔτ • Fixσʔλϕʔε͕GAʹͳΔલʹߦΘΕ͍ͯΔ • Fixର͕MySQLσʔλϕʔεࣗମؔ࿈gem(mysql2)ͷ͜ͱ͋Δ • σʔλϕʔεؔ࿈gemΦʔϓϯιʔεͩͬͨΓɺ৽͍͠ৼΔ͍ʹࢸΔ ͕ٞެ։͞Ε͍ͯΔ͜ͱ͕ଟ͍ • Ұ࣍ใͷϦϯΫΛGitHubͷissuepull
requestʹ͔͍͓͖ͯɺٞΛ ੵΈॏͶΔ • RailsҎ֎Ͱͷͷใࠂͦ͜ͷݴޠͰߦ͍ɺRailsΘͳ͍ • ྫ: MySQLσʔλϕʔεͳΒɺ࠶ݱέʔεSQLจ͚ͩͰ࡞Δ -
৽͍͠σʔλϕʔεαϙʔτ • मਖ਼ʹࢸΒͳ͍߹Ͱɺ࠶ݱέʔεͷ࡞͓ͯ͘͠ͱɺ୭͔ (ະདྷͷࣗͩͬͨΓଞͷਓͩͬͨΓ)͕ͬͯ͘ΕΔ͜ͱ͋Δ • σϑΥϧτͰಈ࡞ͤ͞Δඞཁ͕͋Δ • ύϥϝʔλมߋͳͲͷϫʔΫΞϥϯυΛRailsϢʔβʔશһʹ ظͰ͖ͳ͍ •
SQLͱͯ͠୯७ͳมߋ(NOT NULLΛআ͢Δ)Ͱ͋ͬͯɺϑ ϨʔϜϫʔΫͷόʔδϣϯΞοϓ͕ඞཁͱͳΔ -
Database versions tested • Refer https://travis-ci.org/rails/rails • PostgreSQL 9.2 •
PostgreSQL 9.6 • MySQL 5.6 • MariaDB 10.3 • SQLite 3.8.2 -
Database versions tested at Travis CI (2) • "αϙʔτ"͞Ε͍ͯΔσʔλϕʔεόʔδϣϯ͕ ͯ͢ςετ͞Ε͍ͯΔΘ͚Ͱͳ͍
• PostgreSQL 10ͷpull request(rails/ rails#33112)Φʔϓϯ͞Ε͍ͯΔ • PostgreSQL 10 native partition support (rails/ rails#31336) -
͜Μͳ͜ͱ͓͜Δ • rails/rails#27422 “Upgrading from Rails 5.0.0.1 to Rails 5.0.1
breaks MySQL 5.0 compatibility” • Rails 5.0͕”αϙʔτ”͍ͯ͠ΔMySQL 5.0ʹଘࡏ͠ͳ ͍`infomation_schema.referential_constraints`Λར ༻͍ͯͨ͠ • rails/rails#27435ͰRails 5.1͔ΒMySQL 5.1.10Ҏ ্Λαϙʔτ͢ΔΑ͏ʹมߋ͞Ε·ͨ͠ -
Example of unlock minitest • rails/rails#29271 “Unlock minitest for Rails'
test suite” • minitestΛ5.3.3͔Β5.10.2ʹόʔδϣϯΞοϓ • 5.3.4 ”Test classes are randomized before running.” • RailsϑϨʔϜϫʔΫunit testॱংʹґଘ͍ͯͨ͠ͷ Λղܾ͢ΔͨΊͷIssue • ࠷ҹਂ͍मਖ਼Λհ͠·͢ -
MySQL͚ͩͰΤϥʔʹͳΔྫ • rails/rails#30294 “Move `test_index_is_created_for_both_timestamps` to `TimestampsWithoutTransactionTest`” • ಛఆͷॱং͔ͭMySQL͚ͩͰൃੜ͢Δ •
ಉ͡ॱংͰPostgreSQLͱSQLiteͰ࠶ݱ͠ͳ͍ • मਖ਼ςετͷॴΛҠ͚ͩ͢ -
MySQL͚ͩͰΤϥʔʹͳΔྫ • TimestampTest#test_index_is_created_for_both_timestamps`Λ։࢝͢Δ • TimestampTest#setup ͕࣮ߦ͞ΕΔ • @developer.update_columns(updated_at: Time.now.prev_month)Ͱ ΧϥϜͷ͕ߋ৽͞ΕΔ
• TimestampTest#test_index_is_created_for_both_timestamps`Λ࣮ߦ͢Δ • `create_table`ͱ`create_index`͕࣮ߦ͞ΕΔ • ্ه@developer.update_columns͕҉తʹίϛοτ͞ΕΔ -
MySQL͚ͩͰΤϥʔʹͳΔྫ • IntegrationTest#test_cache_key_format_for_existing_record_wit h_updated_atΛ࣮ߦ͢Δ • ಉ͡Developer.firstͷupdated_at͕ઌ΄Ͳͷ҉ͷίϛοτͰ 1ਐΈɺظͱҧ͏ʹͳ͍ͬͯΔ • ಉ͡ཧ༝Ͱ͜ͷ2ͭͷςετFail͢Δ •
test_cache_key_format_for_existing_record_with_updated_at _and_custom_cache_timestamp_formattest_cache_key_for_n ewer_updated_at -
Unlock minitest • Fix: self.use_transactional_tests = false • MySQLDDLൃߦ࣌ʹ҉తʹίϛοτ͢Δ •
PostgreSQLͱSQLite͠ͳ͍ -
Rails 6 requires SQLite 3.8 • SQLiteͷϛχϚϜόʔδϣϯ͕3.8ʹͳΓ·͢ • raise "Your
version of SQLite (#{sqlite_version}) is too old. Active Record supports SQLite >= 3.8." • 3.8͕ϦϦʔε͞Εͨͷ2013-08-26 (3.8.0) • Ubuntu 14.04 LTS͕ར༻͍ͯ͠Δ -
SQLite 3.8 to support modify column for foreign key referenced
tables • SQLiteσʔλϕʔεςʔϒϧఆٛΛมߋͰ͖ͳ͍ • SQLite adapterͰɺalter_tableϝιου͕ผ໊Ͱ৽͍͠ఆٛͷςʔϒϧΛ ࡞͠ɺಉҰτϥϯβΫγϣϯͰɺςʔϒϧ໊Λݩʹ͍ͯͨ͠ • ςʔϒϧ͕Foreign KeyͰࢀর͞Ε͍ͯͨ߹ɺݩͷςʔϒϧΛDrop͢Δ ͜ͱͰ͖ͳ͔ͬͨ • τϥϯβΫγϣϯͰForeign Keyଈ࣌ධՁ͞ΕΔ͔Β(ͷͣ) • SQLite 3.8ͰɺԆ੍(defer_foreign_keys)Λαϙʔτ͠ɺτϥϯβΫ γϣϯͷ࠷ޙͰForeign Key͕ධՁ͕Մೳʹͳͬͨ -
SQLite adapterͷίʔυ # alter_table def alter_table(table_name, options = {}) altered_table_name
= "a#{table_name}" caller = lambda { |definition| yield definition if block_given? } transaction do disable_referential_integrity do move_table(table_name, altered_table_name, options.merge(temporary: true)) move_table(altered_table_name, table_name, &caller) end end end # move_table def move_table(from, to, options = {}, &block) copy_table(from, to, options, &block) drop_table(from) end -
Rails 5.2 introduces SchemaDumper per database adapter • rails/rails#30337 “Refactor
`SchemaDumper` to make it possible to adapter specific customization “ • rails/rails#30984 “Move extensions to PostgreSQL::SchemaDumper” • Rails 5.2Ͱ֤σʔλϕʔεΞμϓλʔ͕SchemaDumper Λ࣋ͭΑ͏ʹͳΓɺPostgreSQLʹ͔͠ଘࡏ͠ͳ͍ #extensionsͷॴ͕PostgreSQL::SchemaDumperʹҠಈ ͨ͠ -
Migration compatibility support per database adapter • rails/rails#33269 "[WIP] Introduce
migration compatibility per database adapters” • ActiveRecord::Migration::Compatibility͕ҎԼͷ2ͭΛٵऩ͍ͯ͠Δ • MigrationόʔδϣϯʹΑΔৼΔ͍ͷҧ͍ - ActiveRecord::Migration[5.1] • ྫ: 5.1ͰͷPrimary key͕σϑΥϧτͰbigintʹͳͬͨ • σʔλϕʔεΞμϓλʔʹΑΔৼΔ͍ͷҧ͍ • ྫ: options: “ENGINE=InnoDB” Λmysql2ͰՃ͢Δ • ͜Εbundled adapterʹݶΒΕɺ3rd party adapterมߋͰ͖ͳ͍ • σʔλϕʔε͝ͱͷৼΔ͍ΛผϞδϡʔϧ͔Ϋϥεʹ͠Α͏ͱ͢ΔࢼΈ