Renamed database -> can't select Form Report, Procedure

[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

[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

[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.

[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

[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.

[kev1n]

Do you get an output when you run this SQL statement in e.g. phpMyAdmin?

Code: Select all

SELECT * FROM zzzzsys_run_list

[stevenmiller]

Hi Kev1n,
no, there is no output when I run SELECT * FROM zzzzsys_run_list

[kev1n]

Can you paste the SQL of that view here?

Code: Select all

SHOW CREATE VIEW zzzzsys_run_list

[stevenmiller]

The output after
SHOW CREATE VIEW zzzzsys_run_list
is
#1347 - 'db.zzzzsys_run_list' is not VIEW

[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` ;