Restore MySQL database when there are views on there - Do this in order to restore it

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
Celson Aquino
Posts: 27
Joined: 2017-08-26 15:40
Location: Salvador, Bahia - Brazil

Restore MySQL database when there are views on there - Do this in order to restore it

Post by Celson Aquino » 2019-08-09 15:47

I have a mysql database with 3 tables and 2 views. Before you ask, i use these views on reports I built outside AppGini using CodeIgniter and bootstrap.

When I use the Backup/Restore tool in Admin Area to backup this database it works great. But when I try to recover the backup it will not recover my views. I was looking at the dump that was generated and I found a way to make it work.

Below is part of the dump where it creates one of my mysql views:

Code: Select all

--
-- Final view structure for view `vw1itens`
--

/*!50001 DROP VIEW IF EXISTS `vw1itens`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`orc2prod`@`%%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw1itens` AS select `itens`.`id` AS `item_id`,`grupos`.`id` AS `grupo_id`,`categorias`.`id` AS `categoria_id`,`itens`.`tirar` AS `tirar`,`itens`.`dataini` AS `dataini`,`itens`.`datafim` AS `datafim`,`itens`.`descricao` AS `descricao`,`itens`.`valor` AS `valor`,`itens`.`qt` AS `qt`,(`itens`.`valor` * `itens`.`qt`) AS `total1`,(case when isnull(`itens`.`tirar`) then (`itens`.`valor` * `itens`.`qt`) else 0 end) AS `total`,`itens`.`ordem` AS `ordem` from ((`itens` join `categorias` on((`categorias`.`id` = `itens`.`categoria_id`))) join `grupos` on((`grupos`.`id` = `categorias`.`grupo_id`))) order by `grupos`.`ordem`,`categorias`.`ordem`,`itens`.`ordem` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
I just need to delete all after the word CREATE and before the word VIEW and it works. Code above will be as below:

Code: Select all

--
-- Final view structure for view `vw1itens`
--

/*!50001 DROP VIEW IF EXISTS `vw1itens`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE VIEW `vw1itens` AS select `itens`.`id` AS `item_id`,`grupos`.`id` AS `grupo_id`,`categorias`.`id` AS `categoria_id`,`itens`.`tirar` AS `tirar`,`itens`.`dataini` AS `dataini`,`itens`.`datafim` AS `datafim`,`itens`.`descricao` AS `descricao`,`itens`.`valor` AS `valor`,`itens`.`qt` AS `qt`,(`itens`.`valor` * `itens`.`qt`) AS `total1`,(case when isnull(`itens`.`tirar`) then (`itens`.`valor` * `itens`.`qt`) else 0 end) AS `total`,`itens`.`ordem` AS `ordem` from ((`itens` join `categorias` on((`categorias`.`id` = `itens`.`categoria_id`))) join `grupos` on((`grupos`.`id` = `categorias`.`grupo_id`))) order by `grupos`.`ordem`,`categorias`.`ordem`,`itens`.`ordem` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
After you get the sql file that is in /admin/backups you just run it in phpMyAdmin. You can also edit this file and use the tool in admin area to restore it (just make a backup of the original file before).

I wish there were a way to make mysqldump strip these commands when generating the dump.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Restore MySQL database when there are views on there - Do this in order to restore it

Post by onoehring » 2019-08-10 08:15

Hi Celson,

interesting.

Code: Select all

ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`orc2prod`@`%%` SQL SECURITY DEFINER */
/*!50001
Looks like some command is not noticed by your mysql version maybe?
Which version are you using?

Olaf

User avatar
Celson Aquino
Posts: 27
Joined: 2017-08-26 15:40
Location: Salvador, Bahia - Brazil

Re: Restore MySQL database when there are views on there - Do this in order to restore it

Post by Celson Aquino » 2019-08-13 14:05

I think my problems are related to the fact I have 2 mysql servers:

- a local database where I use the root db user
- a hosted database where the db user is not root

So, when I backup on local then restore on hosted, it happens.

Post Reply