Perdida de Relaciones entre tablas (MyIsam to InnoDB)

[art_rockerd]

Hola buen dia, les comento que me ha sucedido algo muy raro, tenia mi base de datos en produccion ya desde el año pasado, era InnoDB con sus respectivas relaciones entre tablas (primarys, foreign, constraint, on delete on update cascade,...), el dia de ayer revisando mi base de datos, vi que no habia restricciones para los insert, al revisar la estrucutra de las tablas me doy cuenta que ahora son MyIsam y no se como paso esto, por lo tanto se perdieron todas las relaciones que habia entre las tablas, restricciones etc, ahora intento volver a pasar las tablas a InnoDB, intente con el ALTER TABLE ENGINE=INNODB, y si me las transforma a InnoDB, sin embargo las relaciones no se recuperan a pesar que en la definicion de la tabla estan los foreing key, intente con el RENAME, para crear una tabla espejo con otro nombre, luego borrar la tabla vieja y volver a renombrar la nueva como estaba antes, y aun asi sigue sin respetar las restrcciones de Foreing Key... alguien me puede ayudar? Esto fue lo que hice. Saludos!


ALTER TABLE


RENAME

[gnzsoloyo]

Algunos detalles:
1) Ninguna tabla cambia su estructura por si misma. Solo puede suceder si expresamente se ha hecho un ALTER TABLE que lo genere.

2) Lo que sí puede ocurrir es que los scripts de creación de las tablas no incluyeran la clasula ENGINE = InnoDB, o bien que el InnoDB no estuviese activado en el servidor de producción.
En el primer caso, MySQL aplica el motor de tablas por default definido en el servidor, por lo que si éste es MyISAM... pues descartará la creación de toda FK, sin nisiquiera darte una advertencia.

3) Modificar las tablas para pasarlas a InnoDB es insuficiente. No "recuperará" las relaciones de FK por la simple razón de que para las tablas MyiSAM estas jamás existieron.
Lo que debes hacer en ese caso es volver a crear una a una las FK. Una a una.

4) Migrar datos entre esta base productiva, y otra correctamente construida, puede no funcionar. Como en la base productiva las restricciones no estuvieron operativas, es altamente probable que haya datos inconsistentes, es decir datos que no respeten esas FK, y en ese caso la migración de datos será incompleta e incorrecta en muchos momentos.
La única solución para eso es hacer n plan de migración y de corrección de datos defectuosos.
La verdad, no te envidio. Es un trabajo que te llevará bastante tiempo y mucho esfuerzo. A mi ya me ha sucedido.

[art_rockerd]

Una pregunta, hice un back up de la base de datos ya con las tablas migradas a InnoDb, cree una nueva base de datos y realizo ahi la carga de este backup con las tablas con el motor InnoDB, aun asi no me respeta las relaciones... por que sucede esto? Se supone que es como crearla desde cero no?

[gnzsoloyo]

hice un back up de la base de datos ya con las tablas migradas a InnoDb

¿Y antes de ese backup, te aseguraste de volver a crear las FK?
¿Tuviste en cuenta lo que te dije en el punto 3?
Modificar las tablas NO ALCANZA. Debes volver a crear esas FK luego de modificar las tablas, porque las FK no existieron mientras las tablas fueron MyISAM. No es como si hubiesen estado "dormidas". NO EXISTEN.

¿Eso queda claro?

Las FK no se crean en automático. De hecho, puedes tener toda la base como InnoDB y jamás haberles definido FK a ninguna tabla. Y de todos modos funcionaría bien... claro que sin restricciones.

[art_rockerd]

Si es lo que etaba revisando, y al parecer desde ahi no estan las FK, lo raro es que en mi definición de mis tablas si estaban:

ejemplo de esta tabla:

Código MySQL:

CREATE TABLE users (
    id_user int not null auto_increment,
    id_profile int not null,
    username varchar (40) not null,
    pass varchar(64) not null,
    nombre varchar(200),
    id_canal varchar(5),
    clave_suc varchar(30),
    logeado bool not null default false,
    UNIQUE KEY user (username),
    CONSTRAINT PK primary key (id_user),
    CONSTRAINT FK1 foreign key (id_profile)
        references profile (id_profile) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK2 foreign key (id_canal)
        references canal_venta (id_canal) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK3 foreign key (clave_suc)
        references sucursales (clave_suc) ON DELETE CASCADE ON UPDATE CASCADE
)
 

Eso si no estaba especificado el motor InnoDB, no se si por eso al crearla no tomo en cuenta las FK, pero pues tendre que hacerlo manualmente tabla por tabla.. Muchas gracias!

[gnzsoloyo]

Eso si no estaba especificado el motor InnoDB, no se si por eso al crearla no tomo en cuenta las FK

Eso ya te lo aclaré específicamente:

2) Lo que sí puede ocurrir es que los scripts de creación de las tablas no incluyeran la clasula ENGINE = InnoDB, o bien que el InnoDB no estuviese activado en el servidor de producción.
En el primer caso, MySQL aplica el motor de tablas por default definido en el servidor, por lo que si éste es MyISAM... pues descartará la creación de toda FK, sin ni siquiera darte una advertencia.

Es decir que tu query de creación de la tabla debería haber sido:

Código MySQL:

CREATE TABLE users (
    id_user INT NOT NULL AUTO_INCREMENT,
    id_profile INT NOT NULL,
    username VARCHAR (40) NOT NULL,
    pass VARCHAR(64) NOT NULL,
    nombre VARCHAR(200),
    id_canal VARCHAR(5),
    clave_suc VARCHAR(30),
    logeado bool NOT NULL DEFAULT FALSE,
    UNIQUE KEY user (username),
    CONSTRAINT PK PRIMARY KEY (id_user),
    CONSTRAINT FK1 FOREIGN key (id_profile)
        REFERENCES profile (id_profile) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK2 FOREIGN key (id_canal)
        REFERENCES canal_venta (id_canal) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK3 FOREIGN key (clave_suc)
        REFERENCES sucursales (clave_suc) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB; # Esto es lo que debía ir al final en cada CREATE de tabla.
 

Por favor, lee lo que se te responde. De lo contrario terminaremos dando vueltas en círculo.