I'm trying tu dump and re-import a database, I'm using the following bash script to export (I have reasons to export each table separately)
for x in `mysql --skip-column-names -h [host] -u [user] -p[pass] -P [port][database] -e 'show tables;'`; do
mysqldump -h [host] -u [user] -p[pass] -P [port] [database] $x --where="1=1 LIMIT 0" --lock-tables=false >> database.sql
done
Then I'm importing using
mysql -h [host] -u [user] -p[pass] -P [port] newdatabase < databasesql
But I'm getting
ERROR 1146 (42S02) at line 8432: Table 'newdatabase.producto' doesn't exist
I think, maybe the problem is because the definition of the product
table is after in the .sql file. But I tried to export and import a small database and it imported correctly.
Lines 8431 to 8435 have the following:
/*!50001 SET collation_connection = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`xxxxxxx`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `informes_list_v` AS SELECT `Informe`.`idinforme` AS `idinforme`,
`Informe`.`timestamp` AS `timestamp`,
`Informe`.`url` AS `url`,
`Informe`.`solicitud_idsolicitud` AS `solicitud_idsolicitud`,
`Informe`.`patente` AS `patente`,
`Informe`.`codigo` AS `codigo`,
`Informe`.`objeto_informe` AS `objeto_informe`,
`Informe`.`usuario_idusuario` AS `usuario_idusuario`,
`Informe`.`estado` AS `estado`,
`Informe`.`precio` AS `precio`,
`Informe`.`marca` AS `marca`,
`Informe`.`venta_idventa` AS `venta_idventa`,
`Informe`.`vehiculo_idvehiculo` AS `vehiculo_idvehiculo`,
`Informe`.`estado_solicitud` AS `estado_solicitud`,
`Informe`.`timestamp_solicitud` AS `timestamp_solicitud`,
`Informe`.`timestamp_eliminacion` AS `timestamp_eliminacion`,
`Informe`.`estado_garantia` AS `estado_garantia`,
`Informe`.`timestamp_garantia_pedido` AS `timestamp_garantia_pedido`,
`Informe`.`timestamp_garantia_vigente` AS `timestamp_garantia_vigente`,
`Informe`.`timestamp_subida_documentos` AS `timestamp_subida_documentos`,
`Informe`.`tipo` AS `tipo`,
`Informe`.`producto_idproducto` AS `producto_idproducto`,
`Informe`.`estado_proceso` AS `estado_proceso`,
`Informe`.`rut` AS `rut`,
`Informe`.`idinforme_padre` AS `idinforme_padre`,
`Informe`.`categoria_idcategoria` AS `idcategoria`,
`Informe`.`timestamp_entrega_informe` AS `timestamp_entrega_informe`,
`Producto`.`nombre` AS `producto_nombre`,
`Vehiculo`.`marca` AS `marca_vehiculo`,
`Vehiculo`.`modelo` AS `modelo`,
`Vehiculo`.`year` AS `year`,
`Usuario`.`nombre` AS `usuario`,
`Usuario`.`empresa_idempresa` AS `idempresa`,
`Usuario`.`mail` AS `mail`,
`Vehiculo`.`tiene_multas` AS `tiene_multas`,
`Vehiculo`.`limitaciones_al_dominio` AS `limitaciones_al_dominio`,
`Vehiculo`.`encargo_por_robo` AS `encargo_por_robo`,
`Vehiculo`.`revision_tecnica` AS `revision_tecnica`,
`Vehiculo`.`flota_vehiculo` AS `flota_vehiculo`,
`Empresa`.`grupo_empresa_idgrupo_empresa` AS `grupo_empresa`,
`Solicitud`.`estado_op1` AS `op1`,
`Solicitud`.`estado_op2` AS `op2`,
`Solicitud`.`estado_op3` AS `op3`,
`Solicitud`.`estado_op4` AS `op4`,
`Solicitud`.`estado_op5` AS `op5`,
`Solicitud`.`estado_op6` AS `op6`,
`Solicitud`.`estado_op7` AS `op7`,
`Solicitud`.`estado_op8` AS `op8`,
`Solicitud`.`estado_op9` AS `op9`,
`Solicitud`.`estado_op10` AS `op10`,
`Solicitud`.`estado_op11` AS `op11`,
`Solicitud`.`estado_op12` AS `op12`,
`Solicitud`.`estado_op13` AS `op13`,
`Solicitud`.`estado_op14` AS `op14`,
`Solicitud`.`estado_op15` AS `op15`,
`Solicitud`.`estado_op16` AS `op16`,
`Solicitud`.`estado_op17` AS `op17`,
`Solicitud`.`estado_op18` AS `op18`,
`Solicitud`.`estado_op19` AS `op19`,
`Solicitud`.`estado_op20` AS `op20`,
`Solicitud`.`estado` AS `estado_solicitud2`
FROM (((((`informe` `Informe`
JOIN `producto` `Producto` on((`Informe`.`producto_idproducto` = `Producto`.`idproducto`)))
LEFT JOIN `vehiculo` `Vehiculo` on((`Informe`.`vehiculo_idvehiculo` = `Vehiculo`.`idvehiculo`)))
JOIN `usuario` `Usuario` on((`Informe`.`usuario_idusuario` = `Usuario`.`idusuario`)))
JOIN `empresa` `Empresa` on((`Usuario`.`empresa_idempresa` = `Empresa`.`idempresa`)))
JOIN `solicitud` `Solicitud` on((`Informe`.`solicitud_idsolicitud` = `Solicitud`.`idsolicitud`)))
ORDER BY `Informe`.`idinforme` DESC */;
/*!50001 SET character_set_client = @saved_cs_client */;
Best Answer
Sounds like the view is dumped before its base table(s). You should use
information_schema.tables
or other way to get only "real" tables, dump them first and then dump views.When you use mysqldump for everything, it handles that automatically.