UUID, wrong relation INSERT statements

[rogerwalt]

Hi

I'm happy to file a pull request for this issue if you could provide me with a quick hint where to look for this error.

Best,
Roger

Steps to reproduce

1. Create 3 tables in a new schema:

    CREATE TABLE `LookupRole` (
     `name` varchar(215) COLLATE utf8_unicode_ci NOT NULL,
     `description` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
     PRIMARY KEY (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;    

    CREATE TABLE `User` (
     `id` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
     `user_name` varchar(107) COLLATE utf8_unicode_ci NOT NULL,
     `password` varchar(107) COLLATE utf8_unicode_ci NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `user_pass_unique` (`user_name`,`password`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;    

    CREATE TABLE `role_user` (
     `user_id` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
     `role_name` varchar(215) COLLATE utf8_unicode_ci NOT NULL,
     PRIMARY KEY (`user_id`,`role_name`),
     KEY `IDX_332CA4DDA76ED395` (`user_id`),
     KEY `IDX_332CA4DDE09C0C92` (`role_name`),
     CONSTRAINT `FK_332CA4DDA76ED395` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`),
     CONSTRAINT `FK_332CA4DDE09C0C92` FOREIGN KEY (`role_name`) REFERENCES `LookupRole` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;    

    INSERT INTO `User` (`id`, `user_name`, `password`) VALUES ('2a5b4587-31f8-11e6-8cfa-080027fb84e1', 'Admin', 'asdf');
    INSERT INTO `LookupRole` (`name`, `description`) VALUES ('Admin', 'asdf');

1. Go to the INSERT page of the table role_user.
2. Select the corresponding user an role to create a n:n relation entry into the table.
3. Press Go -> wrong UUID value is copied into the SQL statement:

INSERT INTO `role_user` (`user_id`, `role_name`) VALUES ('f04ce654-31f8-11e6-8cfa-080027fb84e1', 'Admin')

Expected behaviour

A correct INSERT statement should be generated.

Actual behaviour

The wrong user_id value is copied into the INSERT statement.

Server configuration

Operating system:
Debian 8.4 (64-bit)

Web server:
Apache/2.4.10

Database:
mysql Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.3

PHP version:
PHP 7.0.6-1~dotdeb+8.1

phpMyAdmin version:
8a0e392

Client configuration

Browser:
Chromium 50.0.2661.94

Operating system:
Debian 8.4 (64-bit)

[nijel]

The problem is that the UUID function is preselected for this field. If you remove it, it will work properly. However it really doesn't make sense to preselect UUID function for foreign key field. (The UUID function is interpreted in phpMyAdmin and generates new UUID before sending the query)

[nijel]

To change this behavior, following code would have to be changed to detect foreign keys as well (or to only add the function in case the is complete primary key, not just part of it): https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/Util.php#L3959

[rogerwalt]

I'm missing information about other fields and foreign keys in this context https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/Util.php#L3952. How could I find out if a foreign key exists for the field? We have the information $foreigners until https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/insert_edit.lib.php#L2795, then we loose it. Should it be decided in insert_edit.lib.php?

[nijel]

I think the index information should be passed down so that the function ca decide on it's own. When $foreignData['foreign_link'] is true, the default function should not be used for any field.

It needs a bit reordering of code in PMA_getHtmlForInsertEditFormColumn and then pass $foreignData down to getDefaultFunctionForField.