una - on delete on update mysql

¿Cambiar el nombre de una tabla InnoDB sin actualizar las referencias de clave externa a ella?

Question

Estoy tratando de reemplazar una tabla InnoDB con una nueva tabla, y quiero que todas las referencias de claves externas que apuntan a la tabla anterior apunten a la nueva tabla.

Así que intenté esto:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

Desafortunadamente, incluso con foreign_key_checks deshabilitado, todas las referencias que apuntan a foo se cambian para apuntar a foo_old. Ahora estoy buscando ya sea

• una forma de volver a cambiar las referencias de clave externa sin reconstruir la tabla completa, O
• una forma de cambiar el nombre de una tabla sin actualizar las referencias de clave externa.

Intenté soltar las claves externas y recrearlas, pero como las mesas son enormes, lleva horas. El objetivo principal de reemplazar la tabla era realizar un cambio de esquema con un tiempo de inactividad limitado.

Answer 1

Desafortunadamente, no creo que haya una manera de evitar su problema sin eliminar primero las claves externas y volver a crearlas.

Esto es menor, pero también detecté algo con tus comandos RENAME . Puede encadenarlos y, a menos que todos los pasos hayan tenido éxito, se deshacen de todos los demás nombres. Aquí está la sintaxis:

RENAME TABLE foo TO foo_old, foo_new TO foo;

Answer 2

En MySQL 5.6 con innodb_file_per_table=ON permite intercambiar los espacios de tabla sobre la marcha. Esto no se puede hacer completamente utilizando SQL, ya que las operaciones de archivo deben realizarse por separado. Primero prepare la tabla foo_new para copiar y suelte los datos foo :

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

En este punto, debe copiar los archivos InnoDB relevantes para corregir el nombre. Los archivos se almacenan en su directorio de datos. En Debian, por ejemplo, están por defecto en /var/lib/mysql/yourdatabase y los archivos son foo_new.ibd , foo_new.cfg y foo_new.frm . foo.ibd en foo.ibd , foo.cfg y foo.frm , respectivamente. Por ejemplo:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Preste atención a que MySQL tiene acceso a los nuevos archivos (por ejemplo, tienen el propietario correcto, los derechos de acceso). Una vez hecho esto, puede importar la tabla de nuevo y habilitar las claves externas:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

Esto solo copia foo_new a foo . Repita los pasos si necesita copiar foo a foo_old .

Answer 3

Encontré una forma de evitar esto ... simplemente suelta la tabla de origen en lugar de cambiarle el nombre.

Para este ejemplo, llamaremos a la tabla ''mytbl''.

1. crear una copia de la tabla de origen, por ejemplo, ''mytbl_new''
2. copiar datos en la nueva tabla
3. suelta la tabla fuente ''mytbl''
4. renombra ''mytbl_new'' a ''mytbl''

El único inconveniente es que no puede mantener una copia de seguridad de su tabla original, pero podría hacerlo de antemano. Alternativamente, puede crear una copia de tabla adicional si desea una copia literal de la tabla original.

Answer 4

InnoDB usa el puntero interno de la tabla en la clave externa, por lo que no importa el nombre dado a esta tabla (usando RENAME ), las restricciones se conservarán, incluso cuando use SET foreign_key_checks = 0 .

una forma de cambiar las referencias de clave externa de nuevo sin reconstruir toda la tabla

Usar innodb_file_per_table=ON sería lo más cercano que podemos ir (ver @vhu respuesta).

una forma de cambiar el nombre de una tabla sin actualizar las referencias de clave externa.

La solución que implicará el menor tiempo de inactividad y esfuerzo, y no requiere acceso de shell al servidor, podría ser simplemente trabajar con dos bases de datos y activarlas en el momento oportuno , si los datos no cambian mucho

Podría ser incluso más rápido sincronizar todas las tablas que no sean la grande, o el comando mysql duplicado temporal (eliminar, actualizar, insertar) en su aplicación hasta el cambio, si tiene algunos cambios.

Answer 5

Viejas preguntas, pero seguirlas es una forma posible. Básicamente mueva los datos en vez de renombrar las tablas. Por supuesto, debe asegurarse de que los nuevos datos se adhieran a las reglas de clave externa.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Asegúrate de ejecutarlo como una consulta para que foreign_key_checks se aplique a todo. Espero que esto ayude.