Export Issue with Recursive CTEs in Views - RECURSIVE Keyword Missing

[ddman]

Describe the bug

When exporting a view that uses a WITH RECURSIVE common table expression (CTE) from phpMyAdmin, the RECURSIVE keyword is omitted in the exported SQL file. This omission results in an invalid SQL script that fails during import.

To Reproduce

Steps to reproduce the behavior:

1. Create a view in phpMyAdmin using the following SQL with a WITH RECURSIVE CTE:

CREATE VIEW number_sequence_view AS
WITH RECURSIVE number_sequence AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1
    FROM number_sequence
    WHERE number < 5
)
SELECT * FROM number_sequence;

2. Export the view using phpMyAdmin.
3. Open the exported SQL file and inspect the contents.

Expected behavior

The exported SQL file should contain the full SQL script including the WITH RECURSIVE keyword to ensure the view can be correctly imported into another MySQL instance.

Actual Behavior

The exported SQL file omits the RECURSIVE keyword, resulting in an incomplete SQL script that cannot be used to recreate the view.

Server configuration

• Operating system: Debian
• Web server: Apache/2.4.57
• Database version: 8.2.0 - MySQL Community Server - GPL
• PHP version: 8.2.14
• phpMyAdmin version: 5.2.1

Client configuration

• Browser: chrome
• Operating system: macos 13.6.3

[williamdes]

Hi @ddman
Some fixes have be done for this, I tried this right now on the the latest 5.2 version in development (phpMyAdmin 5.2+snapshot) and it worked fine.
Can you please confirm ?

[williamdes]

Small mistake on my side, it is missing the RECURSIVE keyword

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `number_sequence_view`  AS WITH number_sequence AS (SELECT 1 AS `number` UNION ALL SELECT `number_sequence`.`number`+ 1 AS `number + 1` FROM `number_sequence` WHERE `number_sequence`.`number` < 5) SELECT `number_sequence`.`number` AS `number` FROM `number_sequence``number_sequence`  ;
COMMIT;

[williamdes]

Only export is affected, view edit and show the view definition work fine