แอปเก่าพังหลังย้ายเซิร์ฟเวอร์ — error 1364/1366 กับ MariaDB strict mode

พอดีหลังย้ายเว็บจากเซิร์ฟเวอร์เก่า (CentOS + MariaDB รุ่นเก่า) มาเครื่องใหม่ (Debian + MariaDB รุ่นปัจจุบัน) มีระบบสมาชิกตัวหนึ่งอาการแปลกครับ: หน้าเว็บปกติทุกอย่าง แต่พอกดสมัครสมาชิกขึ้น "temporary error" เฉยๆ ไม่มีรายละเอียดอะไรเลย

เคสแบบนี้ถ้าไม่รู้จัก strict mode มาก่อน อาจนั่งไล่โค้ด PHP ทั้งวันก็ไม่เจอ เพราะโค้ดไม่ผิด — สภาพแวดล้อมมันเปลี่ยน เลยเอาวิธีไล่มาเล่าไว้

ไล่หาต้นเหตุ

โชคดีระบบนี้มีตาราง error log ของตัวเอง เข้าไปดูเจอตัวจริง:

SQLSTATE[HY000]: General error: 1364
Field 'middlename' doesn't have a default value

บางระบบจะเจออีกหมายเลขในตระกูลเดียวกัน:

SQLSTATE[22007]: Invalid datetime format: 1366
Incorrect integer value: '' for column 'status' at row 1

ทั้งคู่คืออาการเดียวกัน: โค้ด INSERT โดยไม่ส่งค่าครบทุก column หรือส่ง string ว่างใส่ column ตัวเลข ซึ่งแอปยุค PHP 5 ทำกันเป็นเรื่องปกติ เพราะ MySQL สมัยนั้นแค่ warning แล้วใส่ค่า default ให้เงียบๆ

ที่พังเพราะ MariaDB ตั้งแต่ 10.2 เปลี่ยน default ของ sql_mode ให้มี STRICT_TRANS_TABLES — จาก warning กลายเป็น error ทันที เว็บเดิมโค้ดเดิมข้อมูลเดิม แค่ย้ายเครื่องก็พัง

เช็คว่าเครื่องเราตั้งอะไรอยู่:

mysql -e 'SELECT @@GLOBAL.sql_mode;'
# STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ทางแก้

ทางที่ถูกต้องที่สุดคือแก้โค้ดให้ INSERT ครบ cast ค่าให้ถูก แต่กับแอปเก่าที่ไม่มีคนดูแลแล้ว (หรือเข้ารหัส ionCube แก้ไม่ได้) วิธีที่ workable จริงคือถอด strict mode ออก — ทำเป็นไฟล์ conf แยกต่างหาก อย่าไปแก้ไฟล์หลักของ distro:

cat > /etc/mysql/conf.d/98-legacy-sqlmode.cnf <<'EOF'
# แอปเก่า (pre-strict era) พังกับ STRICT_TRANS_TABLES หลังย้ายเซิร์ฟเวอร์
# error 1364 (no default) / 1366 (incorrect value) ตอน INSERT
[mysqld]
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

systemctl restart mariadb
mysql -e 'SELECT @@GLOBAL.sql_mode;'

เหตุผลที่ทำเป็นไฟล์ 98-*.cnf แยก: อัปเกรด MariaDB รอบหน้าไฟล์ config หลักโดนทับ ไฟล์ conf.d ของเรารอด และเปิดมาอ่านก็รู้ทันทีว่าใส่ไว้ทำไม — comment ในไฟล์คือจดหมายถึงตัวเองในอนาคตครับ

ถ้าไม่อยากถอดทั้งเซิร์ฟเวอร์

strict mode มีประโยชน์กับแอปใหม่ๆ จริง (กันข้อมูลขยะเข้า DB) ถ้าเครื่องมีทั้งแอปเก่าแอปใหม่ปนกัน มีทางเลือกละเอียดกว่า

// แบบที่ 1: หลัง new PDO(...) หรือ mysqli_connect(...)
$pdo->exec("SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'");

-- แบบที่ 2: ระดับ server (ไม่ apply กับ user สิทธิ์ SUPER)
SET GLOBAL init_connect = "SET sql_mode='NO_ENGINE_SUBSTITUTION'";

ส่วนตัวผมเลือกถอดทั้งเครื่องเพราะเครื่องนั้นแอปเก่าเป็นส่วนใหญ่ — เลือกตามหน้างานครับ

เช็คลิสต์เวลาย้ายเซิร์ฟเวอร์แล้ว "ฟอร์มพัง แต่หน้าเว็บปกติ"

1. หา error log ของแอปก่อน — หลายระบบเก็บลง DB ตัวเอง ไม่ออก PHP error log
2. เจอ 1364/1366 = strict mode ชัวร์
3. SELECT @@GLOBAL.sql_mode เทียบกับเครื่องเก่า
4. ตัดสินใจ: แก้โค้ด / SET SESSION / ถอด global
5. ทำเป็น conf.d ไฟล์แยก + comment เหตุผลกำกับ

เคสนี้ผมใช้เวลาไล่จริงๆ ไม่ถึงครึ่งชั่วโมง เพราะเคยเจอมาก่อน — ใครกำลังวางแผนย้ายเซิร์ฟเวอร์ เก็บ @@GLOBAL.sql_mode ของเครื่องเก่าไว้เทียบก่อนย้ายเลยครับ กันงงทีหลัง ตรงไหนผมอธิบายตกหล่นหรือพิมพ์ผิด เดี๋ยวกลับมาแก้เพิ่มให้ภายหลังครับ