Zapobiegaj resetowaniu identyfikatora auto_increment w bazie danych Innodb po restarcie serwera

Niedawno przeczytałem, że z powodu tego, jak InnoDB ponownie oblicza wartość AUTO_INCREMENT po ponownym uruchomieniu serwera, wszelkie rekordy z górnej listy listy identyfikatorów mogą zostać ponownie użyte.

Zwykle nie stanowi to problemu, ponieważ po usunięciu użytkownika wszystko powiązane z identyfikatorem jest również usuwane z innych tabel.

Ale celowo zostawiam ich posty na forum osierocone, oznaczone jako „Wysłane przez = Użytkownik # 123 =”, aby zachować wcześniejsze rozmowy. Oczywiście, jeśli identyfikator zostanie ponownie użyty, będzie to stanowić problem.

Nigdy wcześniej nie miałem tego problemu, ponieważ zawsze było wystarczającej liczby nowych użytkowników, aby uniemożliwić ponowne użycie identyfikatora w ten sposób. Jednak w moim nowym projekcie rejestracje są rzadkie, a nieaktywne usuwanie użytkowników jest częste (zwłaszcza, że ​​konta „Open Alpha” trwają tylko trzy dni jako podgląd), a takie ponowne użycie identyfikatora zdarzyło się teraz trzy przez trzy.

„Naprawiłem” problem, zapisując poprawną wartość parametru AUTO_INCREMENT w innym miejscu i używając go zamiast polegać na wartości wewnętrznej. Czy istnieje sposób, aby InnoDB zapamiętał ostatnią wartość?

(unikanie problemu, nigdy go nie usuwając)

Ponieważ chcesz zachować "Posted by =User #123="informacje po usunięciu użytkownika id=123, możesz również rozważyć użycie 2 tabel do przechowywania danych użytkowników. Jeden dla Activeużytkowników i jeden dla wszystkich (w tym usunięte od aktywnych użytkowników). I nigdy nie usuwaj tych identyfikatorów z AllUsertabeli:

CREATE TABLE AllUser
( user_id INT AUTO_INCREMENT
, ...
, PRIMARY KEY (user_id)
) ;

------
--- Forum posts FK should reference the `AllUser` table

CREATE TABLE ActiveUser
( user_id INT 
, ...
, PRIMARY KEY (user_id)
, FOREIGN KEY (user_id)
    REFERENCES AllUser (user_id)
) ;

------
--- All other FKs should reference the `ActiveUser` table

To oczywiście skomplikuje operację wstawiania nowego użytkownika. Każdy nowy użytkownik będzie oznaczał 2 wstawki, po jednej w każdej tabeli. Usunięcie użytkownika będzie jednak możliwe tylko poprzez usunięcie z ActiveUsertabeli. Wszystkie FK zostaną usunięte kaskadowo, z wyjątkiem postów na forum, które będą odnosić się do Allusertabeli (gdzie nigdy nie nastąpi usunięcie).

Nie ma naturalnego sposobu, aby to zrobić, oprócz użycia information_schema.tables do zarejestrowania wszystkich kolumn z opcją auto_increment.

Możesz zebrać te kolumny w następujący sposób:

CREATE TABLE mysql.my_autoinc ENGINE=MyISAM
SELECT table_schema,table_name,auto_increment
FROM information_schema.tables WHERE 1=2;
ALTER TABLE mysql.my_autoinc ADD PRIMARY KEY (table_schema,table_name);
INSERT INTO mysql.my_autoinc
SELECT table_schema,table_name,auto_increment
FROM information_schema.tables WHERE auto_increment IS NOT NULL;

Utwórz skrypt, który zresetuje wartości auto_increment

AUTOINC_SCRIPT=/var/lib/mysql/ResetAutoInc.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' AUTO_INCREMENT=',auto_increment,';') FROM mysql.my_autoinc" > ${AUTOINC_SCRIPT}

Możesz wtedy zrobić jedną z dwóch rzeczy:

OPCJA 1: Uruchom skrypt ręcznie po uruchomieniu

mysql> source /var/lib/mysql/ResetAutoInc.sql

OPCJA 2: Niech mysqld wykona skrypt przed zezwoleniem na połączenia

Musisz dodać tę opcję

[mysqld]
init-file=/var/lib/mysql/ResetAutoInc.sql

W ten sposób za każdym razem, gdy ponownie uruchomisz mysql, ten skrypt jest wykonywany na początku. Będziesz musiał pamiętać, aby zregenerować /var/lib/mysql/ResetAutoInc.sql przed wykonaniem planowanego ponownego uruchomienia mysql.

Dokumenty 5.5 sugerują przechowywanie wartości automatycznego przyrostu w innym miejscu, tak jak już to masz.

Alternatywnym rozwiązaniem byłoby naśladowanie SEKWENCJI, aby nie używać automatycznego przyrostu w samej tabeli. Zostało to omówione na SO przed i znowu . Blog Wydajność MySQL wspomina.

Jeszcze jedno wkręcenie danych MySQL, których inne RDBMS nie mają ...

Po prostu nie usuwaj użytkownika. Ważniejsza jest integralność relacyjna. Jeśli musisz z powodów związanych z prywatnością lub cokolwiek innego, po prostu zmień nazwę użytkownika na „usunięte” i wyczyść wszystkie inne pola.

To stare pytanie i wciąż aktualne.

1) To zachowanie zostało naprawione w Mysql 8.0.

2) Jednym z rozwiązań jest użycie fałszywego wiersza do danych, aby utrzymać AUTO_INCREMENT powyżej określonej wartości. Niezbyt wygodne w zależności od tego, co przechowujesz, ale w niektórych przypadkach jest to proste rozwiązanie.

Potrzebowaliśmy ekstrapolowanego rozwiązania dla naszego własnego systemu na podstawie instrukcji zawartych w tym poście. Jeśli to pomoże komukolwiek osiągnąć cel w jeszcze łatwiejszy sposób.

Nasz system używa wzorca tabeli nagrobków do przechowywania usuniętych elementów, ponieważ wykonujemy synchronizację dwukierunkową w odłączonych systemach, dlatego używamy tego kodu, aby dopasować tabele nagrobków do ich tabel aktywnych i wyodrębnić najwyższą możliwą wartość :)

DROP PROCEDURE IF EXISTS `reset_auto_increments`;
DELIMITER $
CREATE PROCEDURE reset_auto_increments()
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE schemaName VARCHAR(255) DEFAULT '';
    DECLARE liveTableName VARCHAR(255) DEFAULT '';
    DECLARE tombstoneTableName VARCHAR(255) DEFAULT '';
    DECLARE liveAutoIncrement INT DEFAULT 0;
    DECLARE tombstoneAutoIncrement INT DEFAULT 0;
    DECLARE newAutoIncrement INT DEFAULT 0;

    DECLARE autoIncrementPairs CURSOR FOR 
        SELECT
            liveTables.TABLE_SCHEMA AS schemaName,
            liveTables.TABLE_NAME AS liveTable, 
            tombstoneTables.TABLE_NAME AS tombstoneTable,
            liveTables.AUTO_INCREMENT AS live_auto_increment,
            tombstoneTables.AUTO_INCREMENT AS tombstone_auto_increment,
            GREATEST(liveTables.AUTO_INCREMENT, tombstoneTables.AUTO_INCREMENT) AS new_auto_increment
        FROM 
            information_schema.tables AS liveTables
            JOIN information_schema.tables AS tombstoneTables
                ON liveTables.TABLE_SCHEMA = tombstoneTables.TABLE_SCHEMA
                    AND CONCAT('deleted', UCASE(LEFT(liveTables.TABLE_NAME, 1)), SUBSTRING(liveTables.TABLE_NAME, 2))
                        = tombstoneTables.TABLE_NAME
        WHERE
            GREATEST(liveTables.AUTO_INCREMENT, tombstoneTables.AUTO_INCREMENT) IS NOT NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET done = 0;

    SET schemaName = '';
    SET liveTableName = '';
    SET tombstoneTableName = '';
    SET liveAutoIncrement = 0;
    SET tombstoneAutoIncrement = 0;
    SET newAutoIncrement = 0;

    OPEN autoIncrementPairs;
    REPEAT

        FETCH autoIncrementPairs INTO 
            schemaName, 
            liveTableName, 
            tombstoneTableName, 
            liveAutoIncrement, 
            tombstoneAutoIncrement, 
            newAutoIncrement;

        SET @statement = CONCAT('ALTER TABLE ', schemaName, '.', liveTableName, ' AUTO_INCREMENT=', newAutoIncrement);
        PREPARE updateAutoIncrementStatement FROM @statement;
        EXECUTE updateAutoIncrementStatement;
        DEALLOCATE PREPARE updateAutoIncrementStatement;

    UNTIL done END REPEAT;

    CLOSE autoIncrementPairs;

END$

DELIMITER ;