کلینیک خطاهای رایج و نگاهی به مهاجرت به/از PostgreSQL

هفت خطایی که حتماً می‌بینید

ابزارهای تشخیص

-- 1366: کدام ستون‌ها هنوز utf8mb4 نیستند؟
SELECT table_name, column_name, character_set_name
FROM information_schema.columns
WHERE table_schema = 'carpet_factory' AND character_set_name <> 'utf8mb4';
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 1205: چه کسی منتظر چه کسی است؟
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age
FROM sys.innodb_lock_waits;

-- 1045: با کدام حساب شناخته شدید؟
SELECT USER(), CURRENT_USER();
SELECT user, host, plugin, account_locked FROM mysql.user WHERE user = 'web';

-- 1452: اقلام بدون سفارش
SELECT oi.id, oi.order_id
FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;

-- max_allowed_packet (کلاینت هم سقف خودش را دارد: mysql --max-allowed-packet=256M)
SET PERSIST max_allowed_packet = 268435456;

برای 2013 اول لاگ خطای MySQL و خروجی dmesg | grep -i oom را ببینید؛ اگر سرور ری‌استارت شده، مشکل حافظه است نه شبکه. اگر فقط کوئری‌های طولانی قطع می‌شوند، net_read_timeout و timeout پروکسی یا load balancer بین برنامه و دیتابیس را بررسی کنید.

مهاجرت به PostgreSQL یا از آن

برای پروژه‌ی جنگو کوچک، dumpdata و loaddata کافی است؛ برای دیتابیس بزرگ ابزار pgloader از MySQL به PostgreSQL را خودکار انجام می‌دهد. تفاوت‌هایی که کد را می‌شکنند: بک‌تیک در برابر کوتیشن دوتایی برای نام‌ها، ON DUPLICATE KEY UPDATE در برابر ON CONFLICT، TINYINT(1) در برابر boolean، مقایسه‌ی رشته که در collation پیش‌فرض MySQL به حروف کوچک و بزرگ حساس نیست ولی در PostgreSQL حساس است، و تاریخ‌های 0000-00-00 که PostgreSQL نمی‌پذیرد.

نکته‌هایی که کمتر کسی می‌داند

• MySQL همیشه یک اتصال بیش از max_connections برای کاربر دارای CONNECTION_ADMIN نگه می‌دارد؛ اگر برنامه با root وصل شود، همین راه نجات را هم مصرف کرده است. از 8.0.14 پورت مدیریتی جدا (admin_address، پورت 33062) هم هست.
• بعد از Lock wait timeout فقط همان دستور ROLLBACK می‌شود، نه کل تراکنش؛ مگر innodb_rollback_on_timeout = ON باشد. برنامه‌ای که بعد از خطا COMMIT می‌زند، نیمی از کار را ثبت می‌کند.
• SET FOREIGN_KEY_CHECKS = 0 برای ورود داده سریع است، اما وقتی دوباره روشن شود داده‌ی موجود را بررسی نمی‌کند؛ یتیم‌ها بی‌صدا باقی می‌مانند.
• پیام Access denied با (using password: NO) یعنی برنامه اصلاً رمز را نفرستاده؛ معمولاً متغیر محیطی خالی است، نه رمز اشتباه.
• در مهاجرت، شمارنده‌ی sequenceها در PostgreSQL پس از ورود داده خودکار به‌روز نمی‌شود؛ بدون setval اولین INSERT خطای کلید تکراری می‌دهد.