การย้าย Magento 2 จาก utf8 ไปเป็น utf8mb4

ลูกค้าคนหนึ่งใช้ emoji ในชื่อของเขา ฐานข้อมูลจึงตัดชื่อให้สั้นลงทันทีหลังจาก emoji นั้น โดยไม่มีข้อผิดพลาดใดๆ ปรากฏใน log

ปัญหานี้เกิดจาก MySQL utf8 ซึ่งไม่ใช่ UTF-8 ที่แท้จริง แต่มันมีข้อจำกัดอยู่ที่ 3 ไบต์

Emoji และสัญลักษณ์สมัยใหม่จำนวนมากจำเป็นต้องใช้ 4 ไบต์ หากคุณใช้ utf8 แบบเก่า MySQL จะตัดข้อมูลของคุณทิ้ง (truncate) ซึ่งเหตุการณ์นี้จะเกิดขึ้นเงียบๆ หาก SQL mode ของคุณไม่ได้ตั้งค่าเป็นแบบ strict ทำให้คุณสูญเสียข้อมูลโดยไม่รู้ตัว

วิธีแก้ไขคือการใช้ utf8mb4 ซึ่งรองรับ 4 ไบต์ต่อหนึ่งตัวอักษร

คุณไม่สามารถรันคำสั่งแปลงข้อมูล (conversion command) กับร้านค้า Magento ขนาดใหญ่ได้โดยตรง เพราะคุณมักจะพบข้อผิดพลาดที่ระบุว่า key ของคุณยาวเกินไป

สิ่งนี้เกิดขึ้นเพราะ MySQL คำนวณขนาดของ index โดยอิงจากจำนวนไบต์สูงสุดที่เป็นไปได้ คอลัมน์ VARCHAR(255) จะใช้ 765 ไบต์ใน utf8 แต่ใน utf8mb4 คอลัมน์เดียวกันนั้นต้องใช้ถึง 1020 ไบต์ ซึ่งเกินขีดจำกัดเดิมที่ 767 ไบต์

วิธีแก้ไข:

  • ใช้ MySQL 8.0 หรือ MySQL 5.7 โดยเปิดใช้งาน innodb_large_prefix ซึ่งจะช่วยเพิ่มขีดจำกัดของคุณเป็น 3072 ไบต์
  • หากคุณใช้เซิร์ฟเวอร์รุ่นเก่า ให้ลดขนาดคอลัมน์ VARCHAR ที่มีการทำ index ลงเหลือ 191 ตัวอักษร เนื่องจาก 191 ตัวอักษรคูณด้วย 4 ไบต์ จะเท่ากับ 764 ไบต์ ซึ่งยังอยู่ในขีดจำกัด

การย้ายข้อมูลที่ประสบความสำเร็จต้องทำ 3 ขั้นตอนดังนี้:

  1. อัปเดตฐานข้อมูลและตาราง โดยใช้ ALTER TABLE CONVERT TO CHARACTER SET utf8mb4 ซึ่งจะเป็นการเขียนข้อมูลที่มีอยู่เดิมใหม่
  2. อัปเดตค่าเริ่มต้นของเซิร์ฟเวอร์ในไฟล์ my.cnf เพื่อให้แน่ใจว่าตารางใหม่จะใช้การตั้งค่าที่ถูกต้อง
  3. อัปเดต charset ของการเชื่อมต่อแอปพลิเคชัน หากการเชื่อมต่อยังคงเป็น utf8 ข้อมูลจะเสียหายก่อนที่จะส่งไปถึงฐานข้อมูล

ข้อควรระวังเกี่ยวกับความเสี่ยงเหล่านี้:

  • การล็อกตาราง (Table locks): คำสั่ง CONVERT TO จะทำการเขียนตารางใหม่ทั้งหมด ซึ่งจะทำให้ตารางขนาดใหญ่ถูกล็อก ควรใช้เครื่องมืออย่าง pt-online-schema-change เพื่อให้ร้านค้าของคุณยังคงออนไลน์ได้ตามปกติ
  • การใช้ Collation ไม่ตรงกัน (Collation mismatches): ตารางทั้งหมดที่นำมา Join กันต้องใช้ collation เดียวกัน ควรใช้ utf8mb4_unicode_ci ตลอดทั้ง schema ของคุณ

หากคุณยังคงใช้ utf8 ร้านค้าของคุณจะไม่ปลอดภัยต่อ Unicode คุณเพียงแค่กำลังรอให้ตัวอักษรที่ผิดพลาดมาทำให้ข้อมูลของคุณเสียหายเท่านั้น

Source: https://dev.to/iamrobindhiman/migrating-a-magento-2-store-from-utf8-to-utf8mb4-without-losing-data-khn