MySQL’de Otomatik Artış (AUTO_INCREMENT) Taşma Sorunları ve Çözümleri

Tarafındankomut
0 Comments

Yıllarca sorunsuz çalışan bir prodüksiyon veritabanınızda bir sabah şu hatayla karşılaşıyorsunuz: ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'. Uygulama çökmüş, kullanıcılar işlem yapamıyor, telefon çalıyor. Bu, MySQL’in en sinsi ve en geç fark edilen sorunlarından biri olan AUTO_INCREMENT taşma problemidir. Fark edildiğinde genellikle iş işten geçmiş olur, ama önceden hazırlıklı olursanız bu felaketi önleyebilirsiniz.

AUTO_INCREMENT Nasıl Çalışır ve Neden Taşar?

MySQL’de bir kolona AUTO_INCREMENT tanımladığınızda, her yeni kayıt için otomatik artan bir sayısal değer atanır. Sorun şu: bu sayıların bir üst sınırı var. Kullandığınız veri tipine göre bu sınır farklılaşır.

  • TINYINT UNSIGNED: Maksimum 255
  • SMALLINT UNSIGNED: Maksimum 65.535
  • MEDIUMINT UNSIGNED: Maksimum 16.777.215
  • INT UNSIGNED: Maksimum 4.294.967.295
  • BIGINT UNSIGNED: Maksimum 18.446.744.073.709.551.615
  • INT (SIGNED): Maksimum 2.147.483.647
  • BIGINT (SIGNED): Maksimum 9.223.372.036.854.775.807

Peki neden bu kadar sık INT kullanılıyor ve neden sorun yaşanıyor? Çünkü çoğu uygulama framework’ü varsayılan olarak INT tipini kullanır. Laravel, Django, Rails… hepsi primary key için INT ile başlar. 2 milyar kayıt çok gibi görünür, ama yüksek trafikli sistemlerde, silinen kayıtların ID’lerinin geri dönüştürülmediği durumlarda ve özellikle loglama tablolarında bu sınıra ulaşmak sandığınızdan çok daha kolaydır.

Bir de şu gerçek senaryoyu düşünün: Bir e-ticaret sitesinde orders tablosunu INT ile açtınız. Yılda 500.000 sipariş alıyorsunuz. 4295 yılına kadar sorun yok gibi görünüyor. Ama aynı tabloda A/B testleri sırasında test siparişleri oluşturuyorsunuz, bunları siliyorsunuz ama ID sayacı geri gitmiyor. Üstelik bir ara sistemin bug’ı nedeniyle aynı siparişi 10 kez insert etmeye çalışmış ve her denemede sayaç ilerlemiş. İşte böyle bir tabloda 10 yıl içinde sınıra ulaşmak pekala mümkün.

Mevcut Durumu Kontrol Etmek

Sisteminizde hangi tabloların tehlike bölgesinde olduğunu görmek için şu sorguyu çalıştırın:

mysql -u root -p information_schema -e "
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    AUTO_INCREMENT,
    TABLE_ROWS,
    ROUND((AUTO_INCREMENT / (
        CASE DATA_TYPE
            WHEN 'tinyint' THEN 127
            WHEN 'smallint' THEN 32767
            WHEN 'mediumint' THEN 8388607
            WHEN 'int' THEN 2147483647
            WHEN 'bigint' THEN 9223372036854775807
        END
    )) * 100, 2) AS kullanim_yuzdesi
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c 
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
    AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.EXTRA LIKE '%auto_increment%'
    AND t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY kullanim_yuzdesi DESC
LIMIT 20;
"

Bu sorgu size hangi tablonun ne kadar dolduğunu yüzde olarak gösterir. %70 üzerindeki tablolar acil eylem gerektirir. %50 üzerindekiler ise planlı bir migrasyon takvimi ister.

Daha basit bir kontrol için de şunu kullanabilirsiniz:

mysql -u root -p -e "
SELECT 
    table_schema AS 'Veritabani',
    table_name AS 'Tablo',
    auto_increment AS 'Mevcut_ID'
FROM information_schema.tables
WHERE table_schema = 'uygulama_db'
    AND auto_increment IS NOT NULL
ORDER BY auto_increment DESC;
"

Bu kontrolü cron job olarak her gün çalıştırıp sonuçları bir monitoring sistemine göndermek iyi bir pratiktir.

Alarm Kurma: Proaktif İzleme

Prodüksiyonda bu tür sorunları önceden yakalamak için bir izleme scripti yazalım:

#!/bin/bash
# /usr/local/bin/check_auto_increment.sh

DB_HOST="localhost"
DB_USER="monitor_user"
DB_PASS="guclu_sifre"
ALERT_EMAIL="[email protected]"
THRESHOLD=80  # Yuzde 80 dolduğunda alarm ver

RESULT=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -N information_schema -e "
SELECT 
    CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS tablo,
    AUTO_INCREMENT AS mevcut_deger,
    ROUND((AUTO_INCREMENT / (
        CASE c.DATA_TYPE
            WHEN 'tinyint' THEN 127
            WHEN 'smallint' THEN 32767
            WHEN 'mediumint' THEN 8388607
            WHEN 'int' THEN 2147483647
            WHEN 'bigint' THEN 9223372036854775807
        END
    )) * 100, 2) AS yuzde
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c 
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
    AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.EXTRA LIKE '%auto_increment%'
    AND t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND (AUTO_INCREMENT / (
        CASE c.DATA_TYPE
            WHEN 'tinyint' THEN 127
            WHEN 'smallint' THEN 32767
            WHEN 'mediumint' THEN 8388607
            WHEN 'int' THEN 2147483647
            WHEN 'bigint' THEN 9223372036854775807
        END
    )) * 100 > $THRESHOLD;
" 2>/dev/null)

if [ -n "$RESULT" ]; then
    echo "UYARI: Asagidaki tablolar AUTO_INCREMENT limitinin %${THRESHOLD} uzerine ulasti:" > /tmp/ai_alert.txt
    echo "$RESULT" >> /tmp/ai_alert.txt
    mail -s "[KRITIK] AUTO_INCREMENT Alarm - $(hostname)" $ALERT_EMAIL < /tmp/ai_alert.txt
    echo "$(date): Alarm gonderildi" >> /var/log/auto_increment_check.log
fi

Bu scripti crontab’a ekleyin:

# Her gece saat 02:00'de calistir
0 2 * * * /usr/local/bin/check_auto_increment.sh

# Ayrica crontab'i duzenlemek icin:
crontab -e

Taşma Olduğunda Acil Müdahale

Felaketi yaşıyorsunuz, uygulama çökmüş durumda. Hızlı bir triage yapmanız gerekiyor.

Önce tablonun mevcut yapısını kontrol edin:

mysql -u root -p uygulama_db -e "SHOW CREATE TABLE siparislerG"

Sonra tablo INT ise ve taşmaya yakınsa ya da taştıysa, bakım penceresi olmadan yapabileceğiniz en hızlı çözüm şudur:

# Signed INT'i Unsigned INT'e cevir - hemen 2 milyar ek alan kazanirsiniz
mysql -u root -p uygulama_db -e "
ALTER TABLE siparisler 
MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;
"

Bu işlem tablonun boyutuna göre zaman alabilir. Büyük tablolar için pt-online-schema-change veya MySQL 8.0+’ın ALGORITHM=INPLACE seçeneğini kullanın:

# Percona Toolkit ile online schema degisiklik
pt-online-schema-change 
    --host=localhost 
    --user=root 
    --password=sifre 
    --alter "MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT" 
    D=uygulama_db,t=siparisler 
    --execute 
    --print 
    --progress time,30

Eğer zaman varsa en kalıcı çözüme geçin:

# INT'den BIGINT'e gecis - en kalici cozum
pt-online-schema-change 
    --host=localhost 
    --user=root 
    --password=sifre 
    --alter "MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" 
    D=uygulama_db,t=siparisler 
    --execute 
    --chunk-size=1000 
    --sleep=0.1

Gerçek Dünya Senaryosu: Log Tablosu Kabusu

Müşterinin sistemine baktım; kullanici_aktivite_log tablosu 2.1 milyar satırı geçmişti. INT SIGNED ile açılmıştı ve artık insert atılamıyordu. Tablo yaklaşık 300 GB büyüklüğündeydi. Normal ALTER TABLE saatlerce sürecek ve bu sürede uygulama çalışamayacaktı.

Çözüm adımları şöyle oldu:

# 1. Adim: Yeni tabloyu dogru veri tipiyle olustur
mysql -u root -p uygulama_db -e "
CREATE TABLE kullanici_aktivite_log_yeni LIKE kullanici_aktivite_log;
ALTER TABLE kullanici_aktivite_log_yeni 
    MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
"

# 2. Adim: Son 90 gunden eski loglari arsivle (ihtiyaca gore)
mysql -u root -p uygulama_db -e "
INSERT INTO kullanici_aktivite_log_arsiv 
SELECT * FROM kullanici_aktivite_log 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
"

# 3. Adim: Yeni tabloya son 90 gunluk veriyi kopyala
mysql -u root -p uygulama_db -e "
INSERT INTO kullanici_aktivite_log_yeni 
SELECT * FROM kullanici_aktivite_log 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY);
"

# 4. Adim: Tabloyu degistir (cok hizli, sadece rename)
mysql -u root -p uygulama_db -e "
RENAME TABLE 
    kullanici_aktivite_log TO kullanici_aktivite_log_eski,
    kullanici_aktivite_log_yeni TO kullanici_aktivite_log;
"

Bu yaklaşımda rename işlemi atomik ve anlık gerçekleşir, dolayısıyla downtime neredeyse sıfırdır.

MySQL 8.0’daki Önemli Değişiklik

MySQL 8.0 öncesinde ciddi bir bug vardı: MySQL yeniden başladığında AUTO_INCREMENT değerini tablodaki maksimum ID değerinden yeniden hesaplıyordu. Yani şöyle bir senaryo yaşanabiliyordu:

  • Tablodaki en yüksek ID: 1000
  • AUTO_INCREMENT sayacı: 1001
  • En yüksek ID’li kayıt (1000) silindi
  • MySQL restart edildi
  • AUTO_INCREMENT sayacı 999’a döndü (tablodaki yeni maksimum)
  • Yeni insert: ID 1000 verildi
  • Ama bu ID’yi işaret eden foreign key’ler hala 1000’e bakıyordu

MySQL 8.0 ile birlikte AUTO_INCREMENT değeri artık redo log‘a yazılıyor ve restart sonrasında doğru şekilde korunuyor. Eğer hala 5.7 kullanıyorsanız bu bug’a karşı dikkatli olun.

Versiyonunuzu kontrol etmek için:

mysql -u root -p -e "SELECT VERSION();"

# MySQL 5.7'de mevcut AUTO_INCREMENT degerini goruntule
mysql -u root -p -e "
SELECT table_name, auto_increment 
FROM information_schema.tables 
WHERE table_schema = 'uygulama_db';
"

Yeni Projeler İçin Doğru Başlangıç

Eğer yeni bir uygulama kuruyorsanız ya da framework migration’ı yapıyorsanız, başlangıçta doğru veri tipini kullanmak ileride büyük dertten kurtarır.

Laravel için migration örneği:

# Laravel migration - her zaman BIGINT kullan
# database/migrations/xxxx_create_siparisler_table.php icinde:

# Eski yanlis yontem (default):
# $table->increments('id');  // INT kullanir

# Dogru yontem:
# $table->bigIncrements('id');  // BIGINT kullanir
# Laravel 8+ icin:
# $table->id();  // Artik BIGINT UNSIGNED kullaniyor, guvenli

# Mevcut tabloyu kontrol et:
php artisan tinker
DB::select("SHOW COLUMNS FROM siparisler WHERE Field = 'id'");

Direkt SQL ile tablo oluşturuyorsanız:

mysql -u root -p uygulama_db -e "
CREATE TABLE siparisler (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    musteri_id BIGINT UNSIGNED NOT NULL,
    toplam_tutar DECIMAL(10,2) NOT NULL,
    durum ENUM('beklemede','tamamlandi','iptal') DEFAULT 'beklemede',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_musteri_id (musteri_id),
    INDEX idx_durum_created (durum, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"

UUID Alternatifi: Dağıtık Sistemler İçin

Eğer microservice mimarisi kullanıyorsanız veya birden fazla veritabanı sunucusundan aynı tabloya yazıyorsanız, AUTO_INCREMENT yerine UUID kullanmayı düşünebilirsiniz. Taşma sorununuz olmaz ama performans trade-off’ları var.

# UUID kullanarak tablo olusturma
mysql -u root -p uygulama_db -e "
CREATE TABLE siparisler_uuid (
    id CHAR(36) NOT NULL DEFAULT (UUID()),
    musteri_id CHAR(36) NOT NULL,
    toplam_tutar DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"

# Alternatif: UUID_SHORT() - daha kucuk, ama yeterince benzersiz
# 8 byte integer dondurur
mysql -u root -p uygulama_db -e "
SELECT UUID_SHORT();
"

UUID’nin dezavantajları:

  • Index boyutu: CHAR(36) INT’in neredeyse 9 katı yer kaplar
  • Insert performansı: Rastgele UUID’ler B-tree index’e sıralı eklenemiyor, bu da page split’lere yol açıyor
  • Okunabilirlik: Sorgu sonuçlarını okumak zorlaşıyor

Bu yüzden UUID kullanacaksanız UUID v7 veya Twitter Snowflake gibi sıralı ID üreticilerini tercih edin. MySQL 8.0.35+ için UUID() fonksiyonu üretilen UUID’ler hala v1 formatında.

AUTO_INCREMENT Değerini Manuel Yönetme

Bazı durumlarda AUTO_INCREMENT sayacını manuel olarak ayarlamanız gerekebilir. Mesela veri migration’ı sonrasında ya da test verilerini temizledikten sonra:

# Mevcut AUTO_INCREMENT degerini goster
mysql -u root -p uygulama_db -e "
SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'uygulama_db' 
AND TABLE_NAME = 'siparisler';
"

# AUTO_INCREMENT degerini manuel ayarla
mysql -u root -p uygulama_db -e "
ALTER TABLE siparisler AUTO_INCREMENT = 1000000;
"

# Not: AUTO_INCREMENT degerini tablodaki max ID'den dusuge ayarlayamazsiniz
# MySQL otomatik olarak max(id)+1 kullanacaktir
# Bunu test etmek icin:
mysql -u root -p uygulama_db -e "
SELECT MAX(id) FROM siparisler;
"

Bir diğer önemli nokta: Silinen kayıtların ID’leri asla geri dönmez ve kullanılmaz. Bu tasarım gereğidir. Eğer silme işlemleriniz çok fazlaysa (özellikle temp tablolar, queue tabloları), bu tablolar için periyodik TRUNCATE ve ardından yeniden doldurma stratejisi düşünebilirsiniz.

Monitoring Dashboard ile Entegrasyon

Grafana/Prometheus kullanıyorsanız, AUTO_INCREMENT doluluk oranını bir metrik olarak export edebilirsiniz:

#!/bin/bash
# /usr/local/bin/mysql_auto_increment_exporter.sh
# Prometheus node_exporter textfile collector ile kullan

OUTPUT_FILE="/var/lib/node_exporter/textfile_collector/mysql_auto_increment.prom"

mysql -u monitor -pmonitor_pass -N information_schema -e "
SELECT 
    CONCAT(
        'mysql_auto_increment_usage_percent{schema="',
        TABLE_SCHEMA, '",table="', TABLE_NAME, '"} ',
        ROUND((AUTO_INCREMENT / (
            CASE c.DATA_TYPE
                WHEN 'int' THEN 2147483647
                WHEN 'bigint' THEN 9223372036854775807
                WHEN 'mediumint' THEN 8388607
                WHEN 'smallint' THEN 32767
                ELSE 127
            END
        )) * 100, 4)
    )
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c 
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
    AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.EXTRA LIKE '%auto_increment%'
    AND t.TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
    AND AUTO_INCREMENT IS NOT NULL;
" 2>/dev/null > "$OUTPUT_FILE"

# Metadata ekle
echo "# HELP mysql_auto_increment_usage_percent AUTO_INCREMENT kullanim yuzdesi" >> "$OUTPUT_FILE"
echo "# TYPE mysql_auto_increment_usage_percent gauge" >> "$OUTPUT_FILE"

Bu scripti crontab‘a 5 dakikada bir ekleyin, Grafana’da %80 üzerine çıktığında alert oluşturun.

Sık Yapılan Hatalar ve Kaçınma Yöntemleri

  • SIGNED INT kullanmak: Varsayılan olarak MySQL INT kullandığınızda bu signed’dır. Her zaman UNSIGNED ekleyin, kapasiteyi anında ikiye katlarsınız.
  • Framework defaults’a körü körüne güvenmek: Framework’ün ne ürettiğini her migration sonrasında kontrol edin. SHOW CREATE TABLE tablo_adiG alışkanlık haline gelmeli.
  • Sadece row count’a bakmak: Tabloda 1 milyon satır var ama AUTO_INCREMENT 800 milyonda olabilir. Satır sayısı sizi yanıltır, sayacı takip edin.
  • Test ortamında fark etmemek: Test veritabanında aynı şemayı kullanıyorsanız, otomatik test runner’larınız sürekli insert/delete yapıyorsa sayaç hızla ilerler. Test DB’lerini periyodik olarak reset edin.
  • Replikasyon ortamında sadece master’ı kontrol etmek: Slave’lerin sayaçları farklı olabilir, özellikle manual insert yapıldıysa.

Sonuç

AUTO_INCREMENT taşması, sessiz sedasız büyüyen ve patladığında prodüksiyonu çökerten türden bir sorundur. Güzel yanı, tamamen önlenebilir olmasıdır. Yapmanız gereken birkaç temel şey var:

İlk olarak, bugün tüm veritabanlarınızda bir denetim yapın. Yazıdaki ilk sorguyu çalıştırın, %50 üzerindeki tablolara bakım penceresi planlayın. İkinci olarak, yeni tablolarda her zaman BIGINT UNSIGNED kullanın; neredeyse sonsuz kapasiteye sahip olacaksınız. Üçüncü olarak, izleme kurunu ihmal etmeyin; alarm gelmeden haberdar olmak istiyorsanız günlük cron job şarttır. Son olarak, MySQL 5.7 kullanıyorsanız 8.0’a geçişi ciddi biçimde değerlendirin; yalnızca AUTO_INCREMENT bug fix’i bile yeterli bir sebep sayılabilir.

Prodüksiyonda patlak vermiş bir AUTO_INCREMENT sorununu gece yarısı çözmek zorunda kalmak, bu satırları okuyandan çok daha zor bir deneyimdir. Biraz önceden yatırım, sonradan saatlerce süren kriz yönetiminin önüne geçer.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir