Mysql – error 1146 when importing thesql DB

MySQLmysqldump

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.