phpmyadmin / phpmyadmin/phpmyadmin

Foreign key check setting is not respected

Open
#15,989 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

affects/5.2 Bug
Dominant language
PHP
Stars
7.9k
Forks
3.6k
Avg merge
4d 18h
Merged PRs (30d)
36

Description

Describe the bug

The setting for foreign key checks has no effect. I checked the phpMyAdmin configuration and it's set to Server default which is:

SHOW VARIABLES;
// ...
foreign_key_checks | ON

I changed the phpMyAdmin setting for the current session to Enable but it's still the same behaviour.

When executing a DROP INDEX query via STRUCTURE tab -> Indexes -> Drop the following form data are sent:

server: 1
table: test_foreign_key
db: test
goto: tbl_structure.php
sql_query: ALTER TABLE `test_foreign_key` DROP INDEX `FOREIGN_KEY`;
message_to_show: Index FOREIGN_KEY has been dropped.
is_js_confirmed: 1
ajax_request: true
fk_checks: 0
_nocache: 1582562296948961610
token: 31607d473d5c724c4b6d3e2d75417562

Furthermore I tried executing the query via the SQL tab. The Enable foreign key checks checkbox is checked. The form data look different, especially the duplicate fk_checks key looks suspicious:

is_js_confirmed: 1
db: test
table: test_foreign_key
token: 31607d473d5c724c4b6d3e2d75417562
pos: 0
goto: tbl_sql.php
message_to_show: Your SQL query has been executed successfully.
prev_sql_query: 
sql_query: ALTER TABLE `test_foreign_key` DROP INDEX `FOREIGN_KEY`
sql_delimiter: ;
fk_checks: 0
fk_checks: 1
SQL: Go
ajax_request: true
ajax_page_request: true
_nocache: 1582562484931112474
token: 31607d473d5c724c4b6d3e2d75417562
To Reproduce

Two test tables:

CREATE TABLE `test_entity` (
 `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `test_foreign_key` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `FOREIGN_KEY` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `FOREIGN_KEY` (`FOREIGN_KEY`),
 CONSTRAINT `test_foreign_key_ibfk_1` FOREIGN KEY (`FOREIGN_KEY`) REFERENCES `test_entity` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Steps to reproduce the behavior:

  1. Go to Table Structure
  2. Click on "Drop" of "FOREIGN_KEY" index
  3. A modal opens, confirm it
Expected behavior

It's not allowed to drop the index because it's used in a foreign key constraint.

Screenshots

Bildschirmfoto vom 2020-02-24 17-25-31

Server configuration
  • Operating system: Debian
  • Web server: Apache 2.4
  • Database version: 5.6
  • PHP version: 7.4.1
  • phpMyAdmin version: 5.0.1
Client configuration
  • Browser: Chrome (80.0.3987.116)
  • Operating system: Ubuntu 18.04

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the issue from the Structure tab by dropping the FOREIGN_KEY index on the supplied test tables, then compare the submitted fk_checks values with the SQL tab behavior. Trace the Structure > Indexes > Drop and SQL tab entry points to determine why foreign-key checks are not respected. Done means the drop is rejected when the index is required by the foreign-key constraint.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, php
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.