Why can’t I edit a “routine”, “function” or “trigger” within phpMyAdmin?

We’ve been asked why our copy of phpMyAdmin doesn’t allow you to create or edit “routines” , “functions” or “triggers”, even though it works properly for some people who install the phpMyAdmin software on their own computers.

This is due to a limitation of phpMyAdmin: this feature only works properly when you’re connected to the database as a MySQL SUPER user, which isn’t the case (and can’t be the case) on our servers.

This doesn’t affect working with routines, functions and triggers from the MySQL command line, which works fine.

Hopefully the authors of phpMyAdmin will improve this in the future.

Technical details

This happens because in the “rte_list.lib.php” file of phpMyAdmin, the function PMA_RTN_getRowForList expects to be able to ultimately query the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES view, but unprivileged users cannot do so within MySQL. This note in the source code alludes to the problem:

// There is a problem with PMA_Util::currentUserHasPrivilege():
// it does not detect all kinds of privileges, for example
// a direct privilege on a specific routine. So, at this point,
// we show the Execute link, hoping that the user has the correct rights.
// Also, information_schema might be hiding the ROUTINE_DEFINITION
// but a routine with no input parameters can be nonetheless executed.

This causes multiple problems with these lines:

if ($routine['ROUTINE_DEFINITION'] !== null
    && PMA_Util::currentUserHasPrivilege('CREATE ROUTINE', $db)

The problem causes $routine['ROUTINE_DEFINITION'] to incorrectly be null, and also causes PMA_Util::currentUserHasPrivilege('CREATE ROUTINE', $db) to incorrectly be false.