วิธีแก้ปัญหาเมื่อตาราง InnoDB ไม่สามารถ alter, optimize, dump ได้เลย by heha

เนื่องจากช่วงนี้มีปัญหาโลกแตกที่ตาราง InnoDB อยู่ๆ ก็เกิด alter, optimize, dump ไมได้เลยซักกะอย่าง มันจะบอกว่า “MySQL server has gone away” หรือ “Error 2013: Lost connection to MySQL server during query when …” ซึ่งช่างเป็น error ที่สื่อความหมายได้ดีเยี่ยมเสียจริง! ไปดูใน log error ก็ฟ้องว่า thread MySQL ตายกลางทาง กรุณา submit bug ไปให้ MySQL เอ่อ… พอสั่ง Check table มันก็บอกว่าปกติดี เอะยังไง! สุดท้ายเนื่องจากจนปัญญาจึงต้องมานั่ง query ทีละ rowๆ ออกมาใส่ตารางใหม่ที่โครงสร้างเหมือนเดิม ซึ่งได้ผลดังนี้

1. ตารางที่ query มันใหญ่มาก! ไม่สามารถสั่ง INSERT INTO user2 (SELECT * FROM user) ได้ตรงๆ เลยแบบง่ายๆ

2. ลองสั่ง INSERT INTO user2 (SELECT * FROM user LIMIT 0,1000) ไรทำนองนี้ก็ไม่ให้อีก! มันฟ้องว่า subquery ไม่ support limit เวรจริงๆ ครับ 55

3. ลองใช้ php ช่วย คือสั่ง SELECT * FROM user WHERE user_id NOT IN (SELECT user_id FROM user2) LIMIT 0,1000 แล้วค่อยเอา user_id ที่ได้มา query ต่อเป็น “INSERT INTO user2 (SELECT * FROM user WHERE user_id IN(“.implode(‘,’, $a_user_id).”))” ก็ยังไม่ได้เพราะแต่ละ row มันใหญ่มาก กว่าจะเสร็จก็ช้ามากๆ

4. สุดท้ายจึงได้เป็น SELECT user_id FROM user WHERE user_id NOT IN (SELECT user_id FROM user2) LIMIT 0,1000 แล้วต่อด้วย ”INSERT INTO user2 (SELECT * FROM user WHERE user_id IN(“.implode(‘,’, $a_user_id).”))” เหมือนเดิม ก็ใช้ได้ละครับ

5. รันไปซักพักพบ “MySQL Server has gone away” อีกแล้ว – -” จึงค่อยๆ ลด Limit มาเหลือ 1 ก็ยังพัง และค่อยๆ ลอง SELECT * FROM user WHERE id=1 ไปเรื่อยๆ โดยเปลี่ยน * เป็น field ในตารางที่มีทีละ field ปรากฏว่าก็เจอ field เจ้าปัญหาจนได้ เป็น Text field นี่เอง! ซึ่งเราไม่สามารถแม้กระทั่งสั่ง DELETE FROM user WHERE id=1 ช่างโหดร้ายยิ่ง!

6. ต่อมาจึงต้องใส่ offset เพื่อ skip row ที่มีปัญหาไป แล้วก็รันๆ ไปซักพักอีกก็เจออีก สรุปว่าข้อมูล user แสนคน เจอ row ที่มีปัญหาทั้งหมด 5 row มันไปทำอีท่าไหนถึงพังก็ไม่อาจทราบได้ และตอนนี้ก็สามารถกลับมาใช้งานได้ตามปกติซะที