PHPerの僕がMySQLからPostgreSQL移行で学んだZeroDateアンチパターン/database_migration_zero_date_untipattern

by Takahashi Ikki

Slide 1

Slide 1 text

PHPerͷ๻͕MySQL͔Β PostgreSQLҠߦͰֶΜͩ ZeroDateΞϯνύλʔϯ 2018/07/13 PHPΧϯϑΝϨϯε 2018 ؔ੢ લ໷ࡇ

Slide 2

Slide 2 text

ઌ೔ͷ஍਒ʹඃࡂ͞Εͨํ ઌ೔ͷେӍʹඃࡂ͞Εͨํ ৺͔Β͓ݟ෣͍Λਃ্͛͠·͢

Slide 3

Slide 3 text

͓͠ͳ͕͖

Slide 4

Slide 4 text

ຊ೔ͷ͓඼ॻ͖ ̍ɽ ͋͹͏ͱΈʔ ̎ɽ DMS(DataBaseMigrationServices)ʹ͍ͭͯ ̏ɽ ZeroDate ͷѱເ ̐ɽ ·ͱΊ 4

Slide 5

Slide 5 text

ਆ( @soudai1025 )͸ ஌͍ͬͯΔ

Slide 6

Slide 6 text

͓ɺࠓ೔ͷ࿩ ͦʔ͍ͩͳΔΒ͕͖͘ா ʹશ෦ॻ͍ͯ͋Δ͡ΌΜ

Slide 7

Slide 7 text

ͦʔ͍ͩͳΔΒ͕͖͘ா ϦϯΫ ʰDBϦϑΝΫλϦϯάΛ΍͍ͬͯΔͬͯ࿩ʱ https://soudai.hatenablog.com/entry/2018/05/25/121801 ʰMySQLͷ0000-00-00 00:00:00͸࢖ͬͯ͸ͳΒͳ͍ʱ https://soudai.hatenablog.com/entry/2018/05/12/191050 7

Slide 8

Slide 8 text

·͋ɺதͷਓͷ࿩ͬͯࣄͰ

Slide 9

Slide 9 text

̍ɽ͋͹͏ͱΈʔ

Slide 10

Slide 10 text

̍ɽ͋͹͏ͱΈʔ • ߴڮ Ұٍ ɾ ԬࢁͰWebΤϯδχΞΛͯ͠·͢ɻ ɾ PHPerɺPythonista ɹ ԬࢁϞόΠϧΞϓϦ։ൃ΋͘΋͘ձ Ͱ Swift ΍ͬͯΔ ɾ JPUG தࠃࢧ෦௕ ɾ ϋϦωζϛ ࣂͬͯ·͢ 10

Slide 11

Slide 11 text

ࠂ஌ IUUQTECTUVEZDIVHPLVDPOOQBTTDPNFWFOU 11

Slide 12

Slide 12 text

̍ɽ͋͹͏ͱΈʔ ࠗ׆ͱ͍͑͹ ʮΦϛΧϨʯʂʂ https://party-calendar.net/ 12

Slide 13

Slide 13 text

̍ɽ͋͹͏ͱΈʔ ݁ࠗ͸͍͍ͧʂʂ 13

Slide 14

Slide 14 text

̍ɽ͋͹͏ͱΈʔ Զͨͪ͸͜ͷসإͱ޾ͤͷͻͱ࣌ͷͨΊʹ ࢓ࣄΛ͍ͯ͠Δʂʂ 14

Slide 15

Slide 15 text

̍ɽ͋͹͏ͱΈʔ ઈࢍɺ࠾༻தʂ https://party-calendar.net/corp/recruit 15

Slide 16

Slide 16 text

̎ɽDMSʹ͍ͭͯ

Slide 17

Slide 17 text

̎ɽDMSʹ͍ͭͯ ݱࡏɺΦϛΧϨͰ͸௕ظܭըͰ DBͷϦϑΝΫλϦϯάΛ΍ͬͯ·͢ɻ 17

Slide 18

Slide 18 text

̎ɽDMSʹ͍ͭͯ 18

Slide 19

Slide 19 text

̎ɽDMSʹ͍ͭͯ 19

Slide 20

Slide 20 text

̎ɽDMSʹ͍ͭͯ 20

Slide 21

Slide 21 text

̎ɽDMSʹ͍ͭͯ ͪͳΈʹɺDMS࢖ͬͨࣄ͋Δਓ $ʁ 21

Slide 22

Slide 22 text

̎ɽDMSʹ͍ͭͯ ʢ༻ޠઆ໌ʣ 22

Slide 23

Slide 23 text

̎ɽDMSʹ͍ͭͯʢσʔλϩʔυʣ 23

Slide 24

Slide 24 text

̎ɽDMSʹ͍ͭͯʢλεΫͷઃఆʣ 24 ࢦఆͨ͠εΩʔϚʹରͯ͠ ҎԼͷ৚݅ͰϨϓϦέʔγϣϯ͢ΔςʔϒϧΛࢦఆ ɾ ਖ਼نදݱΛ࢖͏ ɹuser_% ɿ user_XXXX ͬͯςʔϒϧ͚ͩର৅/ର৅֎ʹ͢Δ ɾ׬શҰகͰࢦఆ͢Δɻ ɹusers ɿ users ςʔϒϧ͚ͩର৅/ର৅֎ʹ͢Δ

Slide 25

Slide 25 text

̎ɽDMSʹ͍ͭͯ ͜ΕͰօ͞Μ %.4׬શʹϚελʔͨ͠ 25

Slide 26

Slide 26 text

̏ɽZeroDate ͷѱເ

Slide 27

Slide 27 text

̏ɽZeroDate ͷѱເ (τϦΨʔ͑͞ॻ͚Ε͹)؆୯ͦ͏ উͬͨͳΨϋϋʂ 27

Slide 28

Slide 28 text

̏ɽZeroDate ͷѱເ (τϦΨʔ͑͞ॻ͚Ε͹)؆୯ͦ͏ উͬͨͳΨϋϋʂ γϡο 28

Slide 29

Slide 29 text

̏ɽZeroDate ͷѱເ ͕ͬ……ବ໨ͬ……! ERROR: date/time ﬁeld value out of range: "0000-00-00 00:00:00” 29

Slide 30

Slide 30 text

̏ɽZeroDate ͷѱເ ʊਓਓਓਓਓਓਓਓਓਓਓਓਓʊ ʼɹlzɹʻ ʉ:?:?:?:?:?:?:?:?:?:?:?ʉ 30

Slide 31

Slide 31 text

̏ɽZeroDate ͷѱເ lz ɾΛڐ༰͍ͯ͠Δͷ͸.Z42-ͷΈ ɾ42-ඪ४ͱͯ͠͸͕࠷খͷൣғ ɾ8)&3&IPHF@EBUF*4/6--Λ౤͛Δͱ IPHF@EBUF͕ͷౕ͕Ҿ͔͔ͬΔ IPHF@EBUFJTOPUOVMMͰ΋ 31

Slide 32

Slide 32 text

̏ɽZeroDate ͷѱເ ৭ʑͳબ୒എܠ͸͋Δ ϝϦοτ΋͋Δ͠σϝϦοτ΋͋Δ ͦΕ͕ࠓճҟछ%#ҠઃʹΑͬͯݦࡏԽ͚ͨͩ͠ 32

Slide 33

Slide 33 text

̏ɽZeroDate ͷѱເ ରॲ̍ #&'03&*/4&35τϦΨʔΛ࢓ࠐΉ ೔࣌b`ͩͬͨΒ /08 ͱ͔ʹม׵͢Δɻ 33

Slide 34

Slide 34 text

̏ɽZeroDate ͷѱເ ͕ͬ……ବ໨ͬ……! ERROR: date/time ﬁeld value out of range: "0000-00-00 00:00:00” 34

Slide 35

Slide 35 text

̏ɽZeroDate ͷѱເ ʊਓਓਓਓਓਓਓਓਓਓਓਓਓʊ ʼɹlzɹʻ ʉ:?:?:?:?:?:?:?:?:?:?:?ʉ 35

Slide 36

Slide 36 text

̏ɽZeroDate ͷѱເ */4&35ॲཧ࣌ʹlzΛ EBUFUJNFܕʹม׵͠Α͏ͱͯ͠ΤϥʔʹͳΔɻ ͦ΋ͦ΋*/4&35ॲཧ͕ίέΔͷͰ #&'03&*/4&35τϦΨʔ͕૸Βͳ͍ɻ 36

Slide 37

Slide 37 text

̏ɽZeroDate ͷѱເ 37

Slide 38

Slide 38 text

̏ɽZeroDate ͷѱເ ରॲ̎ EBUFUJNFܕͷΧϥϜΛWBSDIBSܕʹม͑Δɻ 38

Slide 39

Slide 39 text

̏ɽZeroDate ͷѱເ ͍ͱ͓͔͠ 39

Slide 40

Slide 40 text

̏ɽZeroDate ͷѱເ ྲྀΕͨ 40

Slide 41

Slide 41 text

̏ɽZeroDate ͷѱເ ͜ΕΛºඞཁͳςʔϒϧ਺΍Ε͹ղܾ ΊͰͨ͠ΊͰͨ͠ 41

Slide 42

Slide 42 text

̏ɽZeroDate ͷѱເ (ରॲํ๏) .Z42-Ҏ߱ ;FSP%BUFΛڐ༰͠ͳ͍ઃఆ͕σϑΥϧτ ෆ༻ҙʹ৮Βͳ͍ࣄେ੾ 42

Slide 43

Slide 43 text

̏ɽZeroDate ͷѱເ (ରॲํ๏) ͳ͓ɺ"NB[PO"VSPSB .Z42-ޓ׵ 43

Slide 44

Slide 44 text

̏ɽZeroDate ͷѱເ (ରॲํ๏) TRM@NPEFΛద੾ʹઃఆ͍ͯ͘͠ࣄ͕େࣄ 44

Slide 45

Slide 45 text

·ͱΊ

Slide 46

Slide 46 text

̐ɽ·ͱΊ %.4΋ສೳͰ͸ແ͍ $%$ͷ৔߹ɺτϥϯβΫγϣϯϩά͕ ࡟আ͞ΕͯΔͱ࠶։ग़དྷͳ͍ %.4ΛϩʔΧϧ؀ڥʹ࠶ݱग़དྷͳ͍ ˢ͜ͷ஌ݟ΋ͬͯΔਓڭ͑ͯཉ͍͠ʂ 46

Slide 47

Slide 47 text

̐ɽ·ͱΊ ࢓্༷ͷ໰୊͕ग़ͨͱ͖ ͙͢Ϋιͬͯݴ͍͕ͪ ˞ݸਓͷମݧͰ͢ 47

Slide 48

Slide 48 text

̐ɽ·ͱΊ ࢓্༷ͷ໰୊͕ग़ͨͱ͖ ͙͢Ϋιͬͯݴ͍͕ͪ ˞ݸਓͷҙݟͰ͢ 48

Slide 49

Slide 49 text

̐ɽ·ͱΊ ΦϛΧϨ ʮ໰୊఺l՝୊zʯ ͑ʁ͡Ό͋ͦͷ՝୊ΛԶ͕ղܾ͢Ε͹ αʔϏε΋ͬͱྑ͘ͳΔ͡ΌΜʁ 49

Slide 50

Slide 50 text

̐ɽ·ͱΊ ՝୊ղܾʹ͸ٕज़ྗ͕ඞཁෆՄܽ 50

Slide 51

Slide 51 text

̐ɽ·ͱΊ 51 ΑΓྑ͍Πϯϓοτ͕େ੾ 1)1ΧϯϑΝϨϯεؔ੢ ָ͠Έ·͠ΐ͏ʂ

Slide 52

Slide 52 text

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠