Tabelle orfane

In questa pagina vengono descritti i problemi noti relativi alle tabelle orfane in MySQL.

Che cosa sono le tabelle orfane?

Le tabelle orfane sono tabelle con definizioni disconnesse tra i dizionari di dati MySQL e possono verificarsi in MySQL 5.6 o MySQL 5.7. Uno dei seguenti scenari può bloccare un upgrade della versione principale (MVU) da MySQL 5.7 a MySQL 8.0:

  • La presenza di file di dati InnoDB (.ibd) senza file di definizione corrispondenti (.frm) o viceversa.
  • La presenza di tabelle intermedie lasciate da istruzioni ALTER TABLE che non sono più referenziate o utilizzate da alcuna logica di applicazione attiva.

Tabelle temporanee orfane

I nomi delle tabelle temporanee orfane iniziano con il prefisso #sql-, ad esempio #sql-123.

Utilizza la seguente query per identificare le tabelle temporanee orfane:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME RLIKE '#sql-[0-9].*';

Puoi utilizzare il comando DROP TABLE per eliminare le tabelle temporanee orfane senza altri passaggi aggiuntivi. In questo modo, è possibile risolvere la maggior parte dei casi:

DROP TABLE `DB`.`#mysql50#TEMPORARY_ORPHAN_TABLE`;

Sostituisci DB con il nome del database che vuoi utilizzare.

Un esempio potrebbe essere il seguente:

DROP TABLE `testdb`.`#mysql50##sql-1234`;

Se il comando precedente DROP table non funziona, il file di definizione (.frm) potrebbe essere riutilizzato da un'altra operazione ALTER TABLE. In questi casi, è necessario creare un file segnaposto .frm sul disco per rimuovere la tabella. Contatta l'assistenza Cloud SQL per ricevere aiuto. Se non hai un contratto di assistenza, consulta i metodi self-service per i passaggi di risoluzione dei problemi.

Tabelle intermedie orfane

I nomi delle tabelle intermedie orfane iniziano con il prefisso #sql-ib, ad esempio #sql-ib23-343224.

Utilizza la seguente query per identificare le tabelle intermedie orfane:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql-ib%';

Per rimuovere le tabelle orfane intermedie, modifica prima il nome file della definizione orfana (.frm) in modo che corrisponda al nome della tabella, quindi elimina la tabella dalla riga di comando.

Per rimuovere le tabelle orfane intermedie, contatta il team di assistenza Cloud SQL. Se non hai un contratto di assistenza, consulta i metodi self-service per i passaggi di risoluzione dei problemi.

Tabelle normali orfane

Una tabella InnoDB orfana si verifica quando il file di dati corrispondente (.ibd) rimane nel file system, ma il dizionario dei dati non fa più riferimento al file di dati in modo corretto. Questo scenario richiede un intervento manuale.

Per risolvere il problema, contatta l'assistenza Cloud SQL. Il team di assistenza può creare un file .frm segnaposto e poi utilizzare un comando DROP TABLE per provare a rimuovere la tabella. In caso contrario, è probabile che il file InnoDB (.ibd) debba essere rimosso manualmente dalla directory di dati.

Dopo aver rimosso manualmente il file, puoi eseguire il backup di tutte le tabelle e le strutture del database.

Elimina un database utilizzando DROP DATABASE e crea un database utilizzando CREATE DATABASE. Quest'ultimo passaggio potrebbe richiedere un tempo di inattività per le applicazioni connesse al database interessato.

Se non hai un contratto di assistenza, consulta i metodi self-service per i passaggi di risoluzione dei problemi.

Risoluzione dei problemi self-service

I seguenti metodi di risoluzione dei problemi self-service prevedono l'eliminazione o la migrazione dell'intero database per rimuovere le tabelle orfane quando l'eliminazione di una singola tabella non funziona. Questo metodo è invasivo. Se la tua organizzazione ha un contratto di assistenza, ti consigliamo vivamente di contattare il team di assistenza di Cloud SQL per ricevere aiuto.

Per rimuovere le tabelle temporanee orfane, assicurati di seguire prima i passaggi descritti in Tabelle temporanee orfane. Se il comando DROP TABLE non va a buon fine, prova i seguenti suggerimenti.

Prima di iniziare

  • Ti consigliamo vivamente di eseguire un backup completo dell'istanza per ridurre il rischio di perdita di dati.

  • Per ridurre la durata di un eventuale tempo di inattività dell'applicazione, ti consigliamo vivamente di clonare l'istanza e di verificare i seguenti passaggi di migrazione prima di completarli in un ambiente di produzione.

    Per saperne di più, consulta Clona istanze.

Elimina lo schema utilizzando la migrazione degli oggetti

La migrazione degli oggetti del database è un processo in più passaggi per spostare gli oggetti del database, come le tabelle, in uno schema temporaneo:

  1. Esegui il backup di altri oggetti del database, tra cui procedure, funzioni e viste.
  2. Elimina e ricrea lo schema interessato.
  3. Importa gli oggetti di cui è stato eseguito il backup nello schema originale.

Questo metodo di migrazione in genere causa tempi di inattività dell'applicazione. Per ridurre al minimo le interruzioni, prepara in anticipo tutti gli script necessari. Ad esempio, assicurati che gli script siano pronti per gestire:

  • Ridenominazione delle tabelle e spostamento in uno schema temporaneo.
  • Backup di altri oggetti del database, come procedure, funzioni, viste e altri.
  • Ripristino di tutti gli oggetti del database nello schema originale.

Una volta pronti questi script, completa i seguenti passaggi:

  1. Crea uno schema temporaneo (ad esempio: fix_orphan_tables) sulla stessa istanza.
  2. Interrompi il traffico dell'applicazione sullo schema interessato.
  3. Sposta tutte le tabelle nello schema temporaneo utilizzando RENAME TABLE:

    RENAME TABLE DB.TABLE_NAME TO fix_orphan_tables.TABLE_NAME;
    

    Effettua le seguenti sostituzioni:

    • DB: il nome del database che vuoi utilizzare.
    • TABLE_NAME: il nome della tabella.
  4. Esegui il backup degli oggetti del database, come viste, routine, stored procedure, trigger ed eventi. Un modo per farlo è utilizzare mysqldump:

    mysqldump -u USER --password=PASSWORD \
      -h HOST_IP --set-gtid-purged=OFF --no-data --no-create-db  \
      --no-create-info --routines --triggers --skip-opt --events \
      DB > DB_export.sql
    

    Effettua le seguenti sostituzioni:

    • USER: il nome utente.
    • PASSWORD: la password del database.
    • HOST_IP: l'indirizzo IP dell'host.
    • DB: il nome del database che vuoi utilizzare.

    Ti consigliamo vivamente di eseguire manualmente il backup delle visualizzazioni utilizzando lo snippet di comando SHOW CREATE VIEW.

  5. Elimina lo schema contenente le tabelle orfane.

  6. Crea lo schema con il nome originale.

  7. Verifica se la tabella orfana è stata rimossa:

    SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%ORPHAN_TABLE_NAME</var>';
    

    Sostituisci ORPHAN_TABLE_NAME con il nome della tabella orfana.

  8. Copia le tabelle nello schema originale:

    RENAME TABLE fix_orphan_tables.TABLE_NAME TO DB.TABLE_NAME;
    

    Effettua le seguenti sostituzioni:

    • TABLE_NAME: il nome della tabella.
    • DB: il nome del database che vuoi utilizzare.
  9. Copia tutti gli oggetti del database dal backup eseguito nel passaggio 4.

    mysql -u USER \
      --password=PASSWORD \
      -h <var>HOST_IP \
      -D<var>DB < >varDB_export.sql
    

    Effettua le seguenti sostituzioni:

    • USER: il nome utente.
    • PASSWORD: la password del database.
    • HOST_IP: l'indirizzo IP dell'host.
    • DB: il nome del database che vuoi utilizzare.

    Ti consigliamo vivamente di ripristinare manualmente le viste creandole di nuovo utilizzando l'istruzione CREATE VIEW.

  10. Riprendi il traffico dell'applicazione che avevi interrotto in precedenza.

Elimina lo schema utilizzando il dump e il caricamento nella stessa istanza

Un altro modo per rimuovere una tabella orfana è eseguire un dump completo dello schema interessato, eliminare e ricreare lo schema e poi ripristinare il dump. In alcuni scenari, questo metodo potrebbe essere più rapido e meno complesso. Per ridurre al minimo le interruzioni, assicurati di preparare in anticipo tutti gli script di backup e ripristino.

Una volta pronti questi script, completa i seguenti passaggi:

  1. Interrompi il traffico dell'applicazione sullo schema interessato.
  2. Esegui il backup dello schema in cui si trova la tabella orfana, incluse tutte le stored procedure, i trigger, le visualizzazioni e gli eventi utilizzando mysqldump.
  3. Elimina lo schema.
  4. Crea di nuovo lo schema e ripristina il file di backup.
  5. Ripristina il traffico dell'applicazione interrotto nel primo passaggio.

Dump e caricamento in un'istanza nuova o ricreata

In determinate condizioni, lo schema contenente la tabella orfana non può essere eliminato. In questi casi, devi eseguire la migrazione a una nuova istanza o ricreare l'istanza esistente utilizzando un dump e un caricamento logici. Entrambi gli approcci possono causare interruzioni dell'applicazione e potrebbero richiedere la riconfigurazione delle applicazioni in modo che puntino all'istanza del database appena creata o ricreata. Nelle sezioni seguenti vengono descritti entrambi i metodi.

Esegui la migrazione dei dati a una nuova istanza utilizzando Database Migration Service (DMS)

  1. Utilizza Database Migration Service per creare una nuova istanza Cloud SQL per MySQL.
  2. Una volta che l'istanza di replica ha terminato la replica dei dati associati alla nuova istanza, arresta tutte le applicazioni che si connettono all'istanza di origine.
  3. Promuovi l'istanza Cloud SQL per MySQL di replica.
  4. Modifica tutte le connessioni delle applicazioni in modo che puntino all'istanza Cloud SQL per MySQL appena promossa e riavvia le applicazioni.

Dump e ripristino manuali

  1. Se stai creando una nuova istanza di database, creane una con la stessa configurazione dell'istanza corrente.
  2. Interrompi tutto il traffico dell'applicazione sull'istanza di database corrente.
  3. Esegui il backup di tutti gli schemi utilizzando mysqldump o un'utilità simile.
  4. Se utilizzi la stessa istanza, eliminala e ricreala.
  5. Utilizzando il backup creato nel terzo passaggio, ripristina il backup nella nuova istanza o nella stessa istanza ricreata.
  6. Indirizza le tue applicazioni alla nuova istanza o alla stessa istanza ricreata e riprendi le operazioni dell'applicazione.

Passaggi successivi

  1. Risoluzione dei problemi
  2. Esegui l'upgrade della versione principale del database in loco