check referencial integrity show table-content

[matthiasradde]

Describe the bug

checking referencial integrity is not working as expected.

To Reproduce

Steps to reproduce the behavior:

1. Create tables that are linked somehow (in this case use database kb4 with tables kb_docs, kb_tags and a table kb_doc_tag to link the other tables together) - you may use the same database structure I've used:

CREATE DATABASE `kb4`;
USE `kb4`;
CREATE TABLE `kb_docs` (
  `id` int(10) UNSIGNED NOT NULL,
  `modifydatetime` datetime NOT NULL,
  `subject1` varchar(255) NOT NULL,
  `subject2` varchar(255) NOT NULL,
  `details` longtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `kb_doc_tag` (
  `doc_id` int(10) UNSIGNED NOT NULL,
  `tag_id` int(10) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `kb_tags` (
  `id` int(10) UNSIGNED NOT NULL,
  `tag` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `kb_docs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `modifydatetime` (`modifydatetime`);
--   ADD KEY `public` (`public`);
ALTER TABLE `kb_doc_tag`
  ADD PRIMARY KEY (`doc_id`,`tag_id`);
ALTER TABLE `kb_tags`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tag` (`tag`);
ALTER TABLE `kb_docs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `kb_tags`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

INSERT INTO `phpmyadmin`.`pma__relation` (`master_db`, `master_table`, `master_field`, `foreign_db`, `foreign_table`, `foreign_field`) VALUES
('kb4', 'docs_without_tag', 'doc_id', 'kb4', 'kb_docs', 'id'),
('kb4', 'kb_doc_tag', 'doc_id', 'kb4', 'kb_docs', 'id'),
('kb4', 'kb_doc_tag', 'tag_id', 'kb4', 'kb_tags', 'id');

2. Within database kb4 click on table doc_tag, click on structure, click on relation view and set relations to the other two tables.
3. Click on table doc_tag, click on Operations, scroll down and click on one of the links to check referential integrity
4. The link itself contains the correct SQL-statement as parameter sql_query, but after clicking on it I end up showing "SELECT * FROM `kb_doc_tag` ORDER BY `doc_id` ASC" as SQL-statement and of course showing all entries from table doc_tag

Expected behavior

expected behaviour is to use the statement provided in parameter sql_query

Server configuration

• Operating system: Windows 7
• Web server: Apache 2.4.34
• Database version: MySQL 5.7.23
• PHP version: 7.2.9
• phpMyAdmin version: 4.8.3

Client configuration

• Browser: Firefox 61.0.2
• Operating system: Windows 7

[matthiasradde]

Checked on another machine:
Same issue with Safari 11.1.2 and Firefox 52.9.0 (ESR) on OSX 10.13.6 accessing an apache-2.4.33-webserver running PHP 7.2.9 and MariaDB 10.3.9 on the same OSX 10.13.6.
Also the language used (English / German) doesn't make a difference in the behaviour.

[williamdes]

Successfully reproduced on demo servers.
Thanks @matthiasradde for the detailed instructions and test data !

Demo servers (root, no password)

• 5.0.0-dev
• 4.8.x

[aschuch247]

I can reproduce the issue in phpMyAdmin 4.9.1.

Is there a chance that some magic as done in #15325 could get it working? :) sql_query= is present as GET parameter.

[williamdes]

@aschuch247 Yes, absolutely
I will use that same system

[williamdes]

@matthiasradde Reproduced for the 2nd time, but I edited your script.
Will be fixed in next phpMyAdmin version !

[williamdes]

@matthiasradde Fixed by e87c6b8
You can try the patch is you want :)

[matthiasradde]

You can try the patch is you want :)

Tested - works great :-)
Thanks to all the people involved in fixing this issue