SQL query does not appear in case of editing view where definer is not you on MySQL 8

[Jay04653]

To Reproduce

Steps to reproduce the behavior:

1. Create any View by user 1
2. Login by user 2
3. Open View for editing
4. See empty AS text field (SQL query)

Expected behavior

This should be prefilled with original SQL query

Screenshots

Server configuration

• Operating system: 18.04
• Web server: Apache 2.4
• Database version: 8.0.19
• PHP version: 7.4
• phpMyAdmin version: 5.0.2

Additional context

You can type your query from scratch and it will be overwritten.
Additionally, in the case of definer is removed, it should be overwritten by the current user but currently, it stores the last one.

[williamdes]

Quite interesting !
Thank you for the report !
MySQL is breaking a lot of things recently 🤔
Confirmed on https://demo.phpmyadmin.net/QA_5_0/view_create.php?db=sakila&table=actor_info

[williamdes]

@shahlin would you want to contribute to this issue ?

[williamdes]

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html

An incompatibility resulting from this change is that the CREATE VIEW statement can no longer be recreated solely from the INFORMATION_SCHEMA VIEWS table, because the VIEW_DEFINITION value does not show the column name list. Instead, you must also consult the COLUMNS table to get the column name list. Alternatively (and more simply), use SHOW CREATE VIEW. (Bug #23265335, Bug #81377)

Maybe this comment can have some incidence for this issue

[shahlin]

@williamdes I can give it a try it in about 2 days!

[shahlin]

@williamdes Has this issue been fixed? Couldn't replicate it on QA_5_0 branch.

Tested on: elementary OS 5.1.2 Hera (Ubuntu 18.04)

[Jay04653]

@shahlin Nope, the issue has not fixed yet. But it appears on MySQL 8 only.

[williamdes]

I can confirm that the issue is not fixed @shahlin

[shahlin]

@williamdes Right, I was testing with an older version of MySQL. Gonna try it again with version 8.0

[shahlin]

@williamdes What branch should this be in? I had a look at QA_5_0, the file ViewCreateController isn't there whereas it's there in master

[williamdes]

Hi @shahlin
You should use QA_5_0
I will merge/copy the fix when merging QA_5_0 to master

[shahlin]

Hey, @williamdes! It's still not in QA_5_0? I don't see libraries/classes/Controllers/ViewCreateController.php

[williamdes]

Hey, @williamdes! It's still not in QA_5_0? I don't see libraries/classes/Controllers/ViewCreateController.php

Hey @shahlin !
Nevermind about the file, just search for the same occurrence in the codebase, it maybe has been refactored on the master branch.