0

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 */;
  • What does the line 8432 contain? – jkavalik May 4 '16 at 15:09
  • added the lines of the error to my question – lcjury May 4 '16 at 15:17
  • Is the error about table newdatabase.product or newdatabase.producto? – jkavalik May 4 '16 at 15:28
  • Sorry, they're the same. I translated when it from spanish to english. – lcjury May 4 '16 at 15:31
  • No problem, just wanted to be sure. – jkavalik May 4 '16 at 15:38
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.