یک روز فرم مقاله‌ی پنل مدیریت شروع کرد به خطای ۵۰۰ دادن. فقط برای بعضی مقاله‌ها، نه همه. در لاگ این بود:

Code
SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'searchable' at row 1

راه‌حلش پنج دقیقه طول کشید. ولی درسی که داد ارزش یک نوشته‌ی کامل را دارد، چون دو تصمیم مهم پشت این خطا پنهان است که اکثر آموزش‌ها ردشان می‌کنند.

خطا چه می‌گوید#

ساده است: مقداری که می‌خواهید در یک ستون بنویسید از طول تعریف‌شده‌ی آن ستون بلندتر است.

در مورد ما ستون searchable از نوع varchar(191) بود و از عنوان به‌علاوه‌ی خلاصه پر می‌شد. فرم پنل تا ۵۰۰ کاراکتر خلاصه می‌پذیرفت. هر مقاله‌ای که خلاصه‌ی بلند داشت از سقف ستون رد می‌شد.

پیدا کردن ستون واقعی#

پیام خطا نام ستون را می‌دهد ولی همیشه واضح نیست کدام جدول است. با این دستور ساختار را ببینید:

SQL
SHOW CREATE TABLE articles;

یا فقط ستون‌های مشکوک:

SQL
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'articles';

و برای اینکه ببینید طولانی‌ترین مقدار موجود چقدر است:

SQL
SELECT MAX(CHAR_LENGTH(excerpt)) FROM articles;

چرا روی سیستم خودتان خطا نمی‌گیرید#

این بخش مهم‌تر از خود خطاست.

MySQL یک تنظیم به نام sql_mode دارد. اگر STRICT_TRANS_TABLES در آن باشد، سرور در برابر داده‌ی نامعتبر خطا می‌دهد. اگر نباشد، سرور مقدار را بی‌صدا کوتاه می‌کند، یک هشدار ثبت می‌کند و ادامه می‌دهد.

مقدار فعلی را ببینید:

SQL
SELECT @@sql_mode;

حالا تصور کنید حالت سخت‌گیرانه خاموش بود. چه اتفاقی می‌افتاد؟

  • مقاله ذخیره می‌شد.
  • هیچ خطایی نمی‌دیدید.
  • ستون جستجو نصفه ذخیره می‌شد.
  • ماه‌ها بعد یک نفر می‌گفت «چرا این مقاله در جستجو پیدا نمی‌شود؟» و شما هیچ سرنخی نداشتید.

قاعده‌ای که از این ماجرا نگه داشتیم: STRICT_TRANS_TABLES این‌جا دوست ماست. خطای بلند بهتر از خرابی خاموش است. اگر روی سرور شما روشن است، خاموشش نکنید؛ اگر خاموش است، در محیط توسعه روشنش کنید تا این باگ‌ها را زودتر ببینید.

در لاراول می‌توانید در config/database.php صریح تعیینش کنید:

PHP
'mysql' => [
    // ...
    'strict' => true,
],

راه‌حل: کدام ستون، کدام نوع#

سه گزینه دارید و انتخاب اشتباه هزینه دارد.

نوعحداکثرکجا مناسب است
varchar(191)۱۹۱ کاراکترچیزی که ایندکس می‌شود و واقعاً کوتاه است
varchar(500)۵۰۰ کاراکترمتن کوتاه با سقف روشن، بدون ایندکس کامل
text و mediumtext۶۴ کیلوبایت و ۱۶ مگابایتمتن بلند بدون سقف روشن

قاعده‌ی انتخاب ساده است: به سقفی نگاه کنید که اعتبارسنجی فرم اجازه می‌دهد، نه به حدس خودتان. اگر فرم تا ۵۰۰ کاراکتر می‌پذیرد، ستون نمی‌تواند ۱۹۱ باشد. این دو باید همیشه با هم هماهنگ بمانند.

مهاجرت اصلاح در لاراول:

PHP
Schema::table('articles', function (Blueprint $table) {
    $table->mediumText('searchable')->change();
});

تله‌ی اصلی: چرا ستون از اول ۱۹۱ بود#

عدد ۱۹۱ تصادفی نیست. روی جدول‌های utf8mb4 هر کاراکتر تا چهار بایت جا می‌گیرد و سقف قدیمی ایندکس ۷۶۷ بایت بود. تقسیم ۷۶۷ بر ۴ می‌شود ۱۹۱. یعنی این عدد فقط برای جا شدن در ایندکس انتخاب شده بود. اگر با این ماجرا آشنا نیستید، نوشته‌ی خطای ۱۰۷۱ دقیقاً همین را توضیح می‌دهد.

حالا سؤال درست این است: آن ایندکس اصلاً به چه دردی می‌خورد؟

در مورد ما، ستون searchable فقط با کوئری زیر استفاده می‌شد:

SQL
SELECT * FROM articles WHERE searchable LIKE '%عبارت%';

و این نکته‌ی کلیدی است: وایلدکارت ابتدایی ایندکس را بی‌اثر می‌کند. MySQL برای LIKE '%x%' نمی‌تواند از ایندکس B-Tree استفاده کند، چون نمی‌داند از کجای درخت شروع کند. یعنی آن ایندکس از روز اول یک بار هم استفاده نشده بود، ولی طول ستون را محدود کرده بود.

با EXPLAIN خودتان ببینید:

SQL
EXPLAIN SELECT * FROM articles WHERE searchable LIKE '%تست%';

اگر در ستون key مقدار NULL و در type مقدار ALL دیدید، یعنی ایندکس نادیده گرفته شده.

پس راه‌حل درست این بود: ایندکس را برداریم، نه ستون را کوتاه کنیم.

PHP
Schema::table('articles', function (Blueprint $table) {
    $table->dropIndex('articles_searchable_index');
    $table->mediumText('searchable')->change();
});

قاعده: قبل از کوتاه‌کردن یک ستون برای رعایت سقف ایندکس، بپرسید آن ایندکس واقعاً در کوئری‌ها استفاده می‌شود یا نه. اگر نه، ایندکس را بردارید نه ستون را.

اگر واقعاً به جستجوی سریع نیاز دارید#

برداشتن ایندکس یعنی پذیرفتن اسکن کامل جدول. برای چند هزار ردیف مشکلی نیست، برای چند صد هزار ردیف هست.

گزینه‌ها به ترتیب هزینه:

  1. جستجو را به عنوان و خلاصه محدود کنید و متن کامل را کنار بگذارید. ساده ولی نتیجه ضعیف می‌شود.
  2. ایندکس پیشوندی روی بخش ابتدایی ستون. فقط وقتی مفید است که وایلدکارت ابتدایی نداشته باشید.
  3. جستجوی تمام‌متن. برای فارسی محدودیت‌های جدی دارد؛ چرایش را در جستجوی فارسی در MySQL نوشتیم.
  4. موتور جستجوی جدا. روی هاست اشتراکی معمولاً در دسترس نیست.

ما گزینه‌ی یک را با یک ستون جداگانه‌ی متن کامل ترکیب کردیم و برای حجم فعلی کاملاً کافی است. مهم این است که تصمیم را آگاهانه بگیرید، نه اینکه از ترس خطا ستون را کوچک کنید.

خطاهای هم‌خانواده که با هم اشتباه می‌شوند#

سه خطای MySQL هست که علامتشان شبیه است و علتشان کاملاً فرق دارد. تشخیص درست نصف راه است.

کدپیاممعنی
۱۴۰۶Data too long for columnمقدار از طول ستون بلندتر است
۱۰۷۱Specified key was too longایندکس از سقف بایتی رد شده
۱۳۶۶Incorrect string valueکاراکتر در مجموعه‌نویسه‌ی ستون جا نمی‌شود

خطای ۱۳۶۶ مخصوصاً برای فارسی‌زبان‌ها آشناست و علامتش این است:

Code
SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x99\x82...'

آن بایت‌ها معمولاً یک ایموجی‌اند. اگر ستون utf8 باشد — نه utf8mb4 — کاراکترهای چهاربایتی مثل ایموجی در آن جا نمی‌شوند. utf8 در MySQL نام گمراه‌کننده‌ای است و حداکثر سه بایت را پشتیبانی می‌کند.

مجموعه‌نویسه‌ی جدول را ببینید:

SQL
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

اگر چیزی جز utf8mb4 دیدید، همان علت است. راه‌حل تبدیل جدول است، ولی حواستان باشد که این تبدیل طول بایتی رشته‌ها را زیاد می‌کند و می‌تواند شما را از ۱۳۶۶ به ۱۰۷۱ ببرد. این سه خطا یک زنجیره‌اند.

کاربر باید چه ببیند#

بحث تا این‌جا فنی بود، ولی یک نکته‌ی تجربه‌ی کاربری هم دارد: خطای ۱۴۰۶ هیچ‌وقت نباید به کاربر برسد.

اگر فرم شما تا ۵۰۰ کاراکتر می‌پذیرد و ستون هم ۵۰۰ کاراکتر جا دارد، مشکلی پیش نمی‌آید. ولی اگر اعتبارسنجی سقف نداشته باشد، کاربر یک صفحه‌ی خطای ۵۰۰ می‌بیند که هیچ نمی‌گوید چه کار باید بکند.

پس سقف را در اعتبارسنجی هم بگذارید:

PHP
'excerpt' => ['nullable', 'string', 'max:500'],

و در فرم هم نشانش بدهید — یک شمارنده‌ی کاراکتر ساده، یا حداقل صفت maxlength روی فیلد. سه لایه‌ی هماهنگ: رابط کاربری، اعتبارسنجی، ستون دیتابیس. اگر این سه با هم بخوانند، این خطا هرگز به کاربر نمی‌رسد.

نکته‌ای درباره‌ی ستون‌های مشتق#

ستون searchable که این ماجرا را ساخت یک ویژگی مهم داشت: مشتق بود. یعنی از روی داده‌های دیگر ساخته می‌شد، نه اینکه کاربر مستقیم پرش کند.

ستون‌های مشتق دو خطر خاص دارند:

۱. طولشان قابل پیش‌بینی نیست. وقتی ستونی از ترکیب دو فیلد دیگر ساخته می‌شود، سقفش برابر مجموع سقف آن‌هاست — نه سقف یکی‌شان. ما همین را جا انداخته بودیم.

۲. فقط از یک مسیر پر می‌شوند. اگر جای دیگری رکورد ساخته یا بازیابی شود و از آن مسیر رد نشود، ستون مشتق خالی می‌ماند. یک بار یک ردیف حذف‌نرم در بک‌فیل جا افتاد و وقتی بازیابی شد بدون مقدار برگشت.

قاعده‌ای که برای همیشه نگه داشتیم: هر بک‌فیل روی جدولی که حذف نرم دارد باید ردیف‌های حذف‌شده را هم شامل شود.

PHP
Article::withTrashed()->chunkById(200, function ($rows) {
    foreach ($rows as $row) {
        // بازسازی ستون مشتق
    }
});

مسیر کامل بعد از تغییر ستون#

یک نکته که راحت فراموش می‌شود: عوض‌کردن نوع ستون در مهاجرت تنها نیمی از کار است. کل مسیر را دنبال کنید:

  • مهاجرت
  • آرایه‌ی fillable مدل
  • قوانین اعتبارسنجی درخواست
  • متد مقداردهی در کنترلر

اگر ستونی را بزرگ کردید ولی قانون اعتبارسنجی همچنان سقف قدیمی را می‌گیرد، کاربر همچنان پیام خطا می‌بیند — فقط این بار از سمت لاراول نه از سمت MySQL.

مهاجرت روی جدولی که داده دارد#

تغییر نوع ستون روی جدول خالی بی‌دردسر است. روی جدولی که داده دارد چند نکته هست.

۱. تغییر نوع ستون در لاراول به پکیج نیاز دارد یا نه؟ در نسخه‌های امروزی لاراول متد change بدون وابستگی اضافه کار می‌کند. اگر پروژه‌ی قدیمی دارید و خطای مربوط به نبود پکیج گرفتید، همین را چک کنید.

۲. تغییر نوع، ویژگی‌های دیگر ستون را بازنویسی می‌کند. اگر ستون nullable بوده و در مهاجرت جدید ننویسیدش، بعد از تغییر دیگر nullable نیست و ردیف‌های خالی خطا می‌دهند. همه‌ی ویژگی‌ها را دوباره بنویسید:

PHP
$table->mediumText('searchable')->nullable()->change();

۳. روی جدول بزرگ، تغییر ساختار جدول را قفل می‌کند. برای چند هزار ردیف در کسری از ثانیه تمام می‌شود، ولی روی جدول‌های بزرگ می‌تواند سایت را برای مدتی متوقف کند. اگر جدول بزرگی دارید، این کار را در ساعت کم‌ترافیک انجام دهید.

۴. همیشه راه برگشت داشته باشید. متد down را واقعی بنویسید، نه خالی:

PHP
public function down(): void
{
    Schema::table('articles', function (Blueprint $table) {
        $table->string('searchable', 191)->nullable()->change();
    });
}

هرچند توجه کنید که برگشت به ستون کوچک‌تر روی داده‌ای که از سقف رد شده، همان خطای ۱۴۰۶ را می‌دهد. برگشت همیشه بی‌هزینه نیست و بهتر است قبل از اجرا این را بدانید.

خلاصه#

  • خطای ۱۴۰۶ یعنی داده از سقف ستون بلندتر است.
  • اگر این خطا را نمی‌گیرید، احتمالاً حالت سخت‌گیرانه خاموش است و داده‌تان بی‌صدا بریده می‌شود. آن بدتر است.
  • قبل از کوچک‌کردن ستون، بپرسید ایندکسی که این محدودیت را تحمیل کرده اصلاً استفاده می‌شود یا نه.
  • طول ستون و سقف اعتبارسنجی فرم باید همیشه با هم بخوانند.