Ծրագրավորողի դառը փորձ

Մեր համակարգի արագ աճին զուգահեռ սկսեցինք հանդիպել դեպքեր, երբ մեր MySQL-ի autoincrement դաշտերը որոնք ունեն integer տիպ սկսեցին հասնել իրենց մաքսիմալ արժեքին։

Այդ ժամանակ MySQL-ը սկսում է վերադարձնել Duplicate entry '*' for key 'PRIMARY': Պատճառն էլ այն է, որ եթե դու ցանկանում ես դաշտում գրել թիվ, որը ավելի մեծ է քան մաքսիմալ արժեքը, ապա դրա փոխարեն MySQL-ը փորձում է դաշտում գրել այդ մաքսիմալ արժեքը։ Ստացվում է որ կատարվում է նոր տողի ավելացում id-ով որը արդեն կա։

Եթե տեսնում եք MySQL error, որոնց մեջ հանդիպում են 2147483647 կամ 4294967295 թվերը, ուրեմն հասել եք ձեր առավելագույն արժեքին

Խնդիրը լուծվեց monitoring-ի ավելացմամբ, որը ստուգում էր բոլոր աղյուսակների autoincrement-ի դաշտի առավելագույն արժեքը և ահազանգում եթե մոտենում ենք առավելագույն արժեքին։ Դրանով այս խնդրի մասին մոռացանք շատ երկար ժամանակով։

Ամեն ինչ լավ էր, բայց մի օր գիշերը ժամը 3ին մեր սերվերը սկսեց կախել։ Պատճառը մի query էր, որը մինչ այդ պահը բավակակնին թեթև էր աշխատում։ Explain-ը ոչ մի բան ցույց չտվեց, ամեն ինչ լավ էր, բայց query-ն աշխատում էր 20-30 վարկյանում նախատեսված 0.02 վարկյանի։

Մի քանի ժամ փնտրելուց հետո գտանք, որ պատճառը առավելագույնին հասած integer դաշտն էր, որը սակայն primary չէր և նրա համար ստուգումները չէին գործել։

table1.id դաշտը ստեղծվել էր biging, իսկ հարևան աղյուսակում table2.id_table1 դաշտը ստեղծվել էր integer: EXPLAIN-ը ցույց է տալիս որ ամեն ինչ լավ է, քանի որ ընդհանուր ինդեքսները ճիշտ էն, բայց մաքսիմալ արժեքից մեծ արժեքով query-ի դեպքում ինդեքսը չէր աշխատում։ Շատ տհաճ դեպք։

Եթե EXPLAIN- ով ամեն ինչ լավ է բայց query-ն դանդաղ է աշխատում ապա համոզվեք որ խնդիրը սխալ արժեքների մեջ չէ։

Լուծումը գտնվեց սքրիփթի մեջ, որը ժամանակ առ ժամանակ ստուգում է բոլոր ինդեքսավորված ֆիլդերի առավելագույն արժեքները։

Եթե ինչ որ մեկին պետք է կկցեմ սքրիփթը այստեղ։

<?php
define("DB_HOST", "mysql-5.7.ddk");
define("DB_PORT", "3306");
define("DB_NAME", "city");
define("DB_USER", "root");
define("DB_PASS", "password");

function q($sql, $values = [])
{
    global $pdo;
    try {
        $stm = $pdo->prepare($sql);
        $stm->execute($values);
        return $stm->fetchAll(PDO::FETCH_OBJ);
    } catch (PDOException $e) {
        echo "Exception: {$e->getMessage()}\n";
        exit(1);
    }
}

$pdo = false;
try {
    $pdo = new PDO(
        'mysql:host=' . DB_HOST . ';port=' . DB_PORT,
        DB_USER,
        DB_PASS);
} catch (PDOException $e) {
    echo "Exception: {$e->getMessage()}\n";
    exit(1);
}

// Վերցնում ենք մեր բազայի բոլոր աղյուսակների բոլոր դաշտերը
$sql = "
    SELECT c.*, t.TABLE_ROWS
    FROM information_schema.columns c
             LEFT JOIN information_schema.tables t ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    WHERE c.TABLE_SCHEMA = ?
      AND t.TABLE_TYPE = 'BASE TABLE';
";

$fields = q($sql, [DB_NAME]);

if ($fields === false) {
    echo "Can't select fields\n";
    echo "*****\n";
    exit(1);
}

foreach ($fields as $field) {
    // Եթե դաշտը int տիպի է
    if ($field->DATA_TYPE === 'int') {
        // Եթե դաշտը ինդեքսավորված է
        if ($field->COLUMN_KEY) {
            // առանց նշան դաշտերի համար մաքսիմալ արժեքն է 4294967295 իսկ նշանով դաշտերի համար 2 անգամ քիչ
            $length = stripos($field->COLUMN_TYPE, 'unsigned') !== false ? 4294967295 : 2147483647;

            // վերցնում ենք դաշտի առավելագույն արժեքը
            $max = q("SELECT MAX(`{$field->COLUMN_NAME}`) AS m FROM `{$field->TABLE_NAME}`");
            $max = $max ? $max[0]->m : 0;

            $percent = round($max / $length, 1) * 100;
            // համեմատում ենք արժեքը ընտտրած մինիմալ արժեքի հետ, որից սկսած համարում ենք որ խնդիր ունենք
            if ($percent > $percent_check) {
                // ԱՅՍՏԵՂ ԱՎԵԼԱՑՆՈՒՄ ԵՆՔ ԱՅՆ ԳՈՐԾՈՒՂՈՒԹՅՈՒՆԸ ՈՐԸ ՊԵՏՔ Է ԱՆԵԼ ՄԱՔՍԻՄԱԼ ԱՐԺԵՔԻՆ ՀԱՍՆԵԼՈՒ ԴԵՊՔՈՒՄ
            }
        }
    }
}

echo "Finished!\n";