Page 1 of 2
Renamed database -> can't select Form Report, Procedure
Posted: Mon Jul 06, 2020 4:39 pm
by stevenmiller
Hi all,
I've renamed the database with phpmyadmin, changed it in nuconfig as well. Looked, like everything still works, but now I can't select a form, report, procedure anymore, if I want to include a new run element. Also in existing run elements there is no selected element shown, but they still work. Unfortunately I have no backup from before. What can I do?
Thanks.
Steven
Re: Renamed database -> can't select Form Report, Procedure
Posted: Mon Jul 06, 2020 4:58 pm
by kev1n
Hi Steven,
The most likely reason is that the views (zzzzsys_report_data and zzzzsys_run_list) have not been transferred to/recreated in the new DB.
If they do not exist, create them. Also see here:
https://forums.nubuilder.cloud/viewtopic. ... ate#p20304
Re: Renamed database -> can't select Form Report, Procedure
Posted: Mon Jul 06, 2020 6:14 pm
by Janusz
From my experience - when I transfer/copy database from one DB to the other DB with different name I am doing it following way:
So first I am exporting sql file do the drive using mysql terminal:
Code: Select all
mysqldump --events -u globeadmin -p yourdb > /root/temp/yourdb.sql
after I change in sql file all the names of old DBname to the new DBname.
you can do it with any txt editor or if on linux with:
Code: Select all
sed -i 's/`yourdb`/`yourdb2`/g' /root/temp/yourdb.sql
after name changed - I import it to the new empty database.
first - make it empty.
Code: Select all
mysql -u root -p -e "DROP DATABASE IF EXISTS yourdb2;
CREATE DATABASE yourdb2; GRANT ALL PRIVILEGES ON yourdb2.* TO 'globeadmin'@'%';"
and import the sql file
Code: Select all
mysql -u globeadmin -p yourdb2 < /root/temp/yourdb.sql
It should be done from mysql terminal not with phpmyadmin due to file size limitation of phpmyadmin.
It can works if DBname is not a simple name - otherwise it can change to much words - if it's the case you can try to go word by word with context checking.
So now maybe you can still try to make some changes to the sql file in txt editor replacing old name to the new name.
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 10:49 am
by stevenmiller
Thanks for your quick replies, Kev1n and Janusz,
@Kevin - yes one table was missing, but unfortunately it still doesn't work
@ Janusz I have unfortunately no sql file of the old database
What can I do now?
Best wishes
Steven
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 10:58 am
by Janusz
Maybe try to export SQL file with current status if possible and try to replace names on it and load again. But for trials its better to create additional DB not use existing one to avoid complete loss of data.
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 11:50 am
by kev1n
Do you get an output when you run this SQL statement in e.g. phpMyAdmin?
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 11:56 am
by stevenmiller
Hi Kev1n,
no, there is no output when I run SELECT * FROM zzzzsys_run_list
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 12:17 pm
by kev1n
Can you paste the SQL of that view here?
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 12:44 pm
by stevenmiller
The output after
SHOW CREATE VIEW zzzzsys_run_list
is
#1347 - 'db.zzzzsys_run_list' is not VIEW
Re: Renamed database -> can't select Form Report, Procedure
Posted: Tue Jul 07, 2020 1:05 pm
by kev1n
Delete these two tables:
zzzzsys_report_data
zzzzsys_run_list
and run this SQL:
Code: Select all
CREATE VIEW `zzzzsys_report_data` AS select concat('PROCEDURE:',`zzzzsys_php`.`zzzzsys_php_id`) AS `id`,`zzzzsys_php`.`sph_code` AS `code`,`zzzzsys_php`.`sph_description` AS `description` from `zzzzsys_php` where `zzzzsys_php`.`sph_system` <> '1' and locate('#TABLE_ID#',`zzzzsys_php`.`sph_php`) > '0' union select concat('SQL:',`zzzzsys_select`.`zzzzsys_select_id`) AS `id`,'nuSQL' AS `code`,`zzzzsys_select`.`sse_description` AS `description` from `zzzzsys_select` where `zzzzsys_select`.`sse_system` is null or `zzzzsys_select`.`sse_system` = '' union select concat('TABLE:',`zzzzsys_table`.`zzzzsys_table_id`) AS `id`,'nuTABLE' AS `code`,`zzzzsys_table`.`zzzzsys_table_id` AS `description` from `zzzzsys_table` ;
CREATE VIEW `zzzzsys_run_list` AS select `zzzzsys_form`.`zzzzsys_form_id` AS `id`,'Form' AS `run`,`zzzzsys_form`.`sfo_code` AS `code`,`zzzzsys_form`.`sfo_description` AS `description` from `zzzzsys_form` union select `zzzzsys_report`.`zzzzsys_report_id` AS `id`,'Report' AS `run`,`zzzzsys_report`.`sre_code` AS `code`,`zzzzsys_report`.`sre_description` AS `description` from `zzzzsys_report` union select `zzzzsys_php`.`zzzzsys_php_id` AS `id`,'Procedure' AS `run`,`zzzzsys_php`.`sph_code` AS `code`,`zzzzsys_php`.`sph_description` AS `description` from `zzzzsys_php` where `zzzzsys_php`.`sph_system` <> 1 order by `code` ;