5.1.2 - SQL export of views from MySQL database has several mistakes

[HBoskugel]

Describe the bug

During export of views from a MySQL database the end of the view statement in some cases contains duplications or wrong/misformated statement parts.

To Reproduce

Export a database as SQL and try to import it again

Expected behavior

No error should occur during import

Screenshots

Samples:

-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_dates`  AS SELECT DISTINCT `dates_fromall`.`d_date2` AS `d_date2`, date_format(`dates_fromall`.`d_date2`,'%Y') AS `d_year`, date_format(`dates_fromall`.`d_date2`,'%y') AS `d_year2`, date_format(`dates_fromall`.`d_date2`,'%m') AS `d_month` FROM (select distinct `ep_data`.`d_date2` AS `d_date2`,'ep_data' AS `table_n` from `ep_data` union select distinct `data_project`.`d_date2` AS `d_date2`,'data_project' AS `table_n` from `data_project` union select distinct `zg_table`.`d_date2` AS `d_date2`,'zg_table' AS `table_n` from `zg_table` union select distinct `mbz_table`.`d_date2` AS `d_date2`,'mbz_table' AS `table_n` from `mbz_table` union select distinct `md_table`.`d_date2` AS `d_date2`,'md_table' AS `table_n` from `md_table` union select distinct `md_table`.`d_date2` AS `d_date2`,'md_table' AS `table_n` from `md_table`) AS `dates_fromall``dates_fromall`  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_dates`  AS SELECT DISTINCT `dates_fromall`.`d_date2` AS `d_date2`, date_format(`dates_fromall`.`d_date2`,'%Y') AS `d_year`, date_format(`dates_fromall`.`d_date2`,'%y') AS `d_year2`, date_format(`dates_fromall`.`d_date2`,'%m') AS `d_month` FROM (select distinct `ep_data`.`d_date2` AS `d_date2`,'ep_data' AS `table_n` from `ep_data` union select distinct `data_project`.`d_date2` AS `d_date2`,'data_project' AS `table_n` from `data_project` union select distinct `zg_table`.`d_date2` AS `d_date2`,'zg_table' AS `table_n` from `zg_table` union select distinct `mbz_table`.`d_date2` AS `d_date2`,'mbz_table' AS `table_n` from `mbz_table` union select distinct `md_table`.`d_date2` AS `d_date2`,'md_table' AS `table_n` from `md_table` union select distinct `md_table`.`d_date2` AS `d_date2`,'md_table' AS `table_n` from `md_table`) AS `dates_fromall` ;



-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_md_rvm_bypartner`  AS SELECT `d`.`md_id` AS `md_id`, `d`.`d_id` AS `d_id`, `d`.`d_date2` AS `d_date2`, `d`.`d_remark` AS `d_remark`, `d`.`d_status` AS `d_status`, `d`.`d_status_text_en` AS `d_status_text_en`, `d`.`d_status_text_de` AS `d_status_text_de`, `d`.`partner_id` AS `partner_id`, `d`.`p_code` AS `p_code`, `d`.`p_title_en` AS `p_title_en`, `d`.`p_title_de` AS `p_title_de`, `d`.`is_active` AS `is_active`, `d`.`is_active_text` AS `is_active_text`, sum(`d`.`dv_total`) AS `sum_dv_total`, sum(`d`.`dv_total_female`) AS `sum_dv_total_female`, sum(`d`.`dv_cancel`) AS `sum_dv_cancel`, sum(`d`.`dv_cancelfemale`) AS `sum_dv_cancelfemale`, sum(`d`.`dv_success`) AS `sum_dv_success`, sum(`d`.`dv_successfemale`) AS `sum_dv_successfemale`, sum(`d`.`dv_fixed`) AS `sum_dv_fixed`, sum(`d`.`dv_fixedfemale`) AS `sum_dv_fixedfemale`, sum((case when (`d`.`c_code` <> 'oth') then `d`.`dv_total` else 0 end)) AS `sum_dv_total_pme` FROM `view_md_rvm_value` AS `d` GROUP BY `d`.`md_id`, `d`.`partner_id``partner_id`  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_md_rvm_bypartner`  AS SELECT `d`.`md_id` AS `md_id`, `d`.`d_id` AS `d_id`, `d`.`d_date2` AS `d_date2`, `d`.`d_remark` AS `d_remark`, `d`.`d_status` AS `d_status`, `d`.`d_status_text_en` AS `d_status_text_en`, `d`.`d_status_text_de` AS `d_status_text_de`, `d`.`partner_id` AS `partner_id`, `d`.`p_code` AS `p_code`, `d`.`p_title_en` AS `p_title_en`, `d`.`p_title_de` AS `p_title_de`, `d`.`is_active` AS `is_active`, `d`.`is_active_text` AS `is_active_text`, sum(`d`.`dv_total`) AS `sum_dv_total`, sum(`d`.`dv_total_female`) AS `sum_dv_total_female`, sum(`d`.`dv_cancel`) AS `sum_dv_cancel`, sum(`d`.`dv_cancelfemale`) AS `sum_dv_cancelfemale`, sum(`d`.`dv_success`) AS `sum_dv_success`, sum(`d`.`dv_successfemale`) AS `sum_dv_successfemale`, sum(`d`.`dv_fixed`) AS `sum_dv_fixed`, sum(`d`.`dv_fixedfemale`) AS `sum_dv_fixedfemale`, sum((case when (`d`.`c_code` <> 'oth') then `d`.`dv_total` else 0 end)) AS `sum_dv_total_pme` FROM `view_md_rvm_value` AS `d` GROUP BY `d`.`md_id`, `d`.`partner_id` ;



-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_programlevelpartner`  AS SELECT `p`.`id` AS `id`, `p`.`component` AS `component`, `p`.`partner` AS `partner`, `p`.`o_type` AS `o_type`, `p`.`country_id` AS `country_id`, `p`.`giz_project_code` AS `giz_project_code`, `p`.`p_project_title` AS `p_project_title`, `p`.`activity_regional` AS `activity_regional`, `p`.`comment` AS `comment`, `p`.`is_active` AS `is_active`, `p`.`start_date` AS `start_date`, `p`.`end_date` AS `end_date`, `p`.`total_volume` AS `total_volume`, `p`.`user_type_id` AS `user_type_id`, `p`.`p_type_id` AS `p_type_id`, `p`.`user_id` AS `user_id`, `p`.`ua_program_id` AS `ua_program_id`, `p`.`is_confirmed` AS `is_confirmed`, `p`.`confirm_delete` AS `confirm_delete`, `p`.`is_deleted` AS `is_deleted`, `p`.`updated_at` AS `updated_at`, `p`.`created_at` AS `created_at`, `c`.`cmp_name_de` AS `cmp_name_de`, `c`.`cmp_name_en` AS `cmp_name_en`, `o`.`org_name_de` AS `org_name_de`, `o`.`org_name_en` AS `org_name_en`, `s`.`p_title_de` AS `giz_project_de`, `s`.`p_title_en` AS `giz_project` FROM (((`program_level_partner` `p` left join `s_program` `s` on((`p`.`giz_project_code` = `s`.`p_code`))) join `components` `c` on((`p`.`component` = convert(`c`.`libelle` using utf8)))) join `organisations` `o` on((`p`.`o_type` = convert(`o`.`libelle` using utf8)))) ORDER BY `p`.`id` AS `DESCdesc` ASC  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_programlevelpartner`  AS SELECT `p`.`id` AS `id`, `p`.`component` AS `component`, `p`.`partner` AS `partner`, `p`.`o_type` AS `o_type`, `p`.`country_id` AS `country_id`, `p`.`giz_project_code` AS `giz_project_code`, `p`.`p_project_title` AS `p_project_title`, `p`.`activity_regional` AS `activity_regional`, `p`.`comment` AS `comment`, `p`.`is_active` AS `is_active`, `p`.`start_date` AS `start_date`, `p`.`end_date` AS `end_date`, `p`.`total_volume` AS `total_volume`, `p`.`user_type_id` AS `user_type_id`, `p`.`p_type_id` AS `p_type_id`, `p`.`user_id` AS `user_id`, `p`.`ua_program_id` AS `ua_program_id`, `p`.`is_confirmed` AS `is_confirmed`, `p`.`confirm_delete` AS `confirm_delete`, `p`.`is_deleted` AS `is_deleted`, `p`.`updated_at` AS `updated_at`, `p`.`created_at` AS `created_at`, `c`.`cmp_name_de` AS `cmp_name_de`, `c`.`cmp_name_en` AS `cmp_name_en`, `o`.`org_name_de` AS `org_name_de`, `o`.`org_name_en` AS `org_name_en`, `s`.`p_title_de` AS `giz_project_de`, `s`.`p_title_en` AS `giz_project` FROM (((`program_level_partner` `p` left join `s_program` `s` on((`p`.`giz_project_code` = `s`.`p_code`))) join `components` `c` on((`p`.`component` = convert(`c`.`libelle` using utf8)))) join `organisations` `o` on((`p`.`o_type` = convert(`o`.`libelle` using utf8)))) ORDER BY `p`.`id` DESC ;



-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_tz_dates`  AS SELECT date_format(`d`.`d_date2`,'%Y-%m') AS `date2`, date_format(`d`.`d_date2`,'%Y') AS `year_no`, (select `s_monthnames`.`m_name_en` from `s_monthnames` where (`s_monthnames`.`m_id` = date_format(`d`.`d_date2`,'%m'))) AS `m_name_en`, (select `s_monthnames`.`m_name_de` from `s_monthnames` where (`s_monthnames`.`m_id` = date_format(`d`.`d_date2`,'%m'))) AS `m_name_de` FROM `data_project` AS `d` GROUP BY date_format(`d`.`d_date2`,'%Y-%m') ORDER BY `d`.`d_date2` AS `DESCdesc` ASC  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_tz_dates`  AS SELECT date_format(`d`.`d_date2`,'%Y-%m') AS `date2`, date_format(`d`.`d_date2`,'%Y') AS `year_no`, (select `s_monthnames`.`m_name_en` from `s_monthnames` where (`s_monthnames`.`m_id` = date_format(`d`.`d_date2`,'%m'))) AS `m_name_en`, (select `s_monthnames`.`m_name_de` from `s_monthnames` where (`s_monthnames`.`m_id` = date_format(`d`.`d_date2`,'%m'))) AS `m_name_de` FROM `data_project` AS `d` GROUP BY date_format(`d`.`d_date2`,'%Y-%m') ORDER BY `d`.`d_date2` DESC ;



-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_unique_benificier_kpi`  AS SELECT `view_kpi`.`d_date2` AS `d_date2`, `view_kpi`.`p_type_id` AS `p_type_id`, `view_kpi`.`country_id` AS `country_id`, `view_kpi`.`program_id` AS `program_id`, `view_kpi`.`organisation_id` AS `organisation_id`, sum(`view_kpi`.`dv_total`) AS `dv_total`, sum(`view_kpi`.`dv_germanreturnee`) AS `dv_germanreturnee` FROM `view_kpi` GROUP BY `view_kpi`.`d_date2`, `view_kpi`.`p_type_id`, `view_kpi`.`country_id`, `view_kpi`.`program_id`, `view_kpi`.`organisation_id``organisation_id`  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_unique_benificier_kpi`  AS SELECT `view_kpi`.`d_date2` AS `d_date2`, `view_kpi`.`p_type_id` AS `p_type_id`, `view_kpi`.`country_id` AS `country_id`, `view_kpi`.`program_id` AS `program_id`, `view_kpi`.`organisation_id` AS `organisation_id`, sum(`view_kpi`.`dv_total`) AS `dv_total`, sum(`view_kpi`.`dv_germanreturnee`) AS `dv_germanreturnee` FROM `view_kpi` GROUP BY `view_kpi`.`d_date2`, `view_kpi`.`p_type_id`, `view_kpi`.`country_id`, `view_kpi`.`program_id`, `view_kpi`.`organisation_id` ;



-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_zg_bycountry`  AS SELECT `d`.`zg_id` AS `zg_id`, `d`.`d_date2` AS `d_date2`, `d`.`country_id` AS `country_id`, `d`.`c_code` AS `c_code`, `d`.`c_name_en` AS `c_name_en`, `d`.`c_name_de` AS `c_name_de`, `d`.`c_status` AS `c_status`, `d`.`c_status_text` AS `c_status_text`, `d`.`new_ordre` AS `new_ordre`, `d`.`ind_id` AS `ind_id`, `d`.`ind_code` AS `ind_code`, `d`.`ind_name_en` AS `ind_name_en`, `d`.`ind_name_de` AS `ind_name_de`, sum(`d`.`dv_surveyed`) AS `sum_dv_surveyed`, sum(`d`.`dv_qualitative`) AS `sum_dv_qualitative`, sum(`d`.`dv_total`) AS `sum_dv_total`, sum(`d`.`dv_returnee`) AS `sum_dv_returnee`, sum(`d`.`dv_germanreturnee`) AS `sum_dv_germanreturnee`, sum(`d`.`dv_otherreturnee`) AS `sum_dv_otherreturnee`, sum(`d`.`dv_female`) AS `sum_dv_female`, sum(`d`.`dv_local`) AS `sum_dv_local`, sum(`d`.`dv_youth`) AS `sum_dv_youth`, sum(`d`.`dv_children`) AS `sum_dv_children` FROM `view_zg_value` AS `d` GROUP BY `d`.`zg_id`, `d`.`country_id`, `d`.`ind_id``ind_id`  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_zg_bycountry`  AS SELECT `d`.`zg_id` AS `zg_id`, `d`.`d_date2` AS `d_date2`, `d`.`country_id` AS `country_id`, `d`.`c_code` AS `c_code`, `d`.`c_name_en` AS `c_name_en`, `d`.`c_name_de` AS `c_name_de`, `d`.`c_status` AS `c_status`, `d`.`c_status_text` AS `c_status_text`, `d`.`new_ordre` AS `new_ordre`, `d`.`ind_id` AS `ind_id`, `d`.`ind_code` AS `ind_code`, `d`.`ind_name_en` AS `ind_name_en`, `d`.`ind_name_de` AS `ind_name_de`, sum(`d`.`dv_surveyed`) AS `sum_dv_surveyed`, sum(`d`.`dv_qualitative`) AS `sum_dv_qualitative`, sum(`d`.`dv_total`) AS `sum_dv_total`, sum(`d`.`dv_returnee`) AS `sum_dv_returnee`, sum(`d`.`dv_germanreturnee`) AS `sum_dv_germanreturnee`, sum(`d`.`dv_otherreturnee`) AS `sum_dv_otherreturnee`, sum(`d`.`dv_female`) AS `sum_dv_female`, sum(`d`.`dv_local`) AS `sum_dv_local`, sum(`d`.`dv_youth`) AS `sum_dv_youth`, sum(`d`.`dv_children`) AS `sum_dv_children` FROM `view_zg_value` AS `d` GROUP BY `d`.`zg_id`, `d`.`country_id`, `d`.`ind_id` ;



-- phpMyAdmin 5.1.2
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_zg_bycountry_new`  AS SELECT `d`.`zg_id` AS `zg_id`, `d`.`d_date2` AS `d_date2`, `d`.`country_id` AS `country_id`, `d`.`c_code` AS `c_code`, `d`.`c_name_en` AS `c_name_en`, `d`.`c_name_de` AS `c_name_de`, `d`.`c_status` AS `c_status`, `d`.`c_status_text` AS `c_status_text`, `d`.`ind_id` AS `ind_id`, `d`.`ind_code` AS `ind_code`, `d`.`new_ordre` AS `new_ordre`, `d`.`ind_name_en` AS `ind_name_en`, `d`.`ind_name_de` AS `ind_name_de`, sum(`d`.`dv_surveyed`) AS `sum_dv_surveyed`, sum(`d`.`dv_qualitative`) AS `sum_dv_qualitative`, sum(`d`.`dv_total`) AS `sum_dv_total`, sum(`d`.`dv_returnee`) AS `sum_dv_returnee`, sum(`d`.`dv_germanreturnee`) AS `sum_dv_germanreturnee`, sum(`d`.`dv_otherreturnee`) AS `sum_dv_otherreturnee`, sum(`d`.`dv_female`) AS `sum_dv_female`, sum(`d`.`dv_local`) AS `sum_dv_local`, sum(`d`.`dv_youth`) AS `sum_dv_youth`, sum(`d`.`dv_children`) AS `sum_dv_children` FROM `view_zg_value_new` AS `d` GROUP BY `d`.`zg_id`, `d`.`country_id`, `d`.`new_ordre``new_ordre`  ;

-- phpMyAdmin 5.1.1
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_zg_bycountry_new`  AS SELECT `d`.`zg_id` AS `zg_id`, `d`.`d_date2` AS `d_date2`, `d`.`country_id` AS `country_id`, `d`.`c_code` AS `c_code`, `d`.`c_name_en` AS `c_name_en`, `d`.`c_name_de` AS `c_name_de`, `d`.`c_status` AS `c_status`, `d`.`c_status_text` AS `c_status_text`, `d`.`ind_id` AS `ind_id`, `d`.`ind_code` AS `ind_code`, `d`.`new_ordre` AS `new_ordre`, `d`.`ind_name_en` AS `ind_name_en`, `d`.`ind_name_de` AS `ind_name_de`, sum(`d`.`dv_surveyed`) AS `sum_dv_surveyed`, sum(`d`.`dv_qualitative`) AS `sum_dv_qualitative`, sum(`d`.`dv_total`) AS `sum_dv_total`, sum(`d`.`dv_returnee`) AS `sum_dv_returnee`, sum(`d`.`dv_germanreturnee`) AS `sum_dv_germanreturnee`, sum(`d`.`dv_otherreturnee`) AS `sum_dv_otherreturnee`, sum(`d`.`dv_female`) AS `sum_dv_female`, sum(`d`.`dv_local`) AS `sum_dv_local`, sum(`d`.`dv_youth`) AS `sum_dv_youth`, sum(`d`.`dv_children`) AS `sum_dv_children` FROM `view_zg_value_new` AS `d` GROUP BY `d`.`zg_id`, `d`.`country_id`, `d`.`new_ordre` ;

Server configuration

• Operating system:
• Web server:
• Database version:
• PHP version:
• phpMyAdmin version:

Client configuration

• Browser:
• Operating system:

Additional context

Add any other context about the problem here.

[williamdes]

Hi @iifawzi
Would you mind having a look please ?

[iifawzi]

Hi, sure, I will take a look on it in a week ( after finishing my final exams ).

[williamdes]

Hi @HBoskugel
Would you mind trying this fix on your local setup by changing the file in vendor/phpmyadmin/sql-parser: https://github.com/phpmyadmin/sql-parser/pull/371/files#diff-1fc95c4608ac9255e416f3fe0f3be8ea99d11f95351dbda8f00ba2b5419d16e0 ?

[HBoskugel]

Sorry, can't test it next days. The problem came up during my work. Our customer uses a central installation of phpMyAdmin from a web hosting provider. So we don't have an own instance. And also im currently not working in case of illness. May be in around one or two week I have a possibility to test it. Then I will test also the other issues #16549 and #16510.

Additional background:
I have an own written import functionality for Laravel/Symfony/MySQL for console. With this I use the phpMyAdmin exports to fill up into our test and local databases. So I faced this problem and #16549 and #16510 too during my last rollout while refreshing the databases. For #16549 and #16510 I worked around to extract the view by MySQL Workbench and added them after my import additionally. In case of this issue I corrected the statements in a copy of the export file and then imported it.

[iifawzi]

Hi @HBoskugel, I wish you get better very soon.

I can confirm that all of the mentioned issues are now fixed, when the first two issues got fixed, they mistakenly caused the one you've mentioned here, which's fixed at phpmyadmin/sql-parser#371.

For #16549 and #16510 they're fixed and released since a while, if the provider is using the latest version, parser will parse them as expected. In order for this issue to be fixed, we would need to wait until the next releases of sql-parser and phpmyadmin.

[HBoskugel]

Now it looks well. Tested with phpMyAdmin 5.1.0 (from XAMPP 7.4.16 for Windows) and 5.1.3 (from download in XAMPP 7.4.16 for Windows). In both cases detected no error.

[iifawzi]

Thanks for testing, I’m really happy to hear that it worked.

[HBoskugel]

Still waiting to become part of the final version. Checked out 5.2.0 already.

[williamdes]

Still waiting to become part of the final version. Checked out 5.2.0 already.

The (phpMyAdmin 5.2+snapshot) also available as a non official docker image is updated with the fix 🚀

[williamdes]

Closing it as #17830 can be subscribed to know when the release is out