Unable to delete rows using PHPMyAdmin

Question

I am using PHPMyAdmin 4.6.0 (current stable) on my Mac to manage the MySQL tables. I have created a table with the following schema:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `table2id` int(11) NOT NULL,
  `col2` float DEFAULT NULL,
  `col3` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `table1`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_table1_table2` (`table2id`);

ALTER TABLE `table1`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

When viewing the data from table1, I select some of the rows using the checkboxes on the left hand side and then press delete link below the table. I am redirected to another page, showing the following SQL queries to be executed:

DELETE FROM `table1` WHERE ;
DELETE FROM `table1` WHERE ;
DELETE FROM `table1` WHERE ;

Note that the ID of the dataset is missing here, resulting in the queries to fail. Something similar happens if I press the "delete" button within a row. A modal appears, showing the following output:

Do you really want to execute "DELETE FROM `table1` WHERE LIMIT 1"?

Note that the WHERE clause is missing here, too. It however works with different tables in another database or even another table within the same database. For example, another table has the following schema:

CREATE TABLE `table3` (
  `id` int(11) NOT NULL,
  `name` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shortname` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `table3`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `table3`
   MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Actually I can't see any difference here that would cause this behaviour of PHPMyAdmin. Any idea?

I'm not able to reproduce this problem. Can you reproduce it on the demo server at https://demo.phpmyadmin.net ? If you copy that table to another database, does the problem persist? If you export then import (or copy and paste the SQL) to a new database (rather than copying from the Operations tab) does that help at all? — Isaac Bennetch, May 11 '16 at 22:35
Also, you might try clearing your browser cache. Is this a new install or has it been upgraded from a previous phpMyAdmin version? — Isaac Bennetch, May 11 '16 at 22:36
I have the same problem, with a clean version up to date of phpmyadmin — user1532587, Aug 12 '16 at 17:14

score 1 · Accepted Answer · edited May 23 '17 at 12:08

UPDATE - phpMyAdmin 4.6.6 fixes this bug.

How to upgrade phpMyAdmin in XAMPP to latest?

ORIGINAL POST

I can reproduce the error in my environment but not on demo.phpmyadmin.net. If your InnoDB or MyISAM table name has any upper case character in any position as shown below, it may have this problem. I'm on XAMPP on Mac running phpMyAdmin version 4.5.2, 10.1.19--MariaDB - Source distribution, PHP version 5.6.28.

CREATE TABLE `MyTable1` (
  `id` int(11) NOT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `table2id` int(11) NOT NULL,
  `col2` float DEFAULT NULL,
  `col3` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `MyTable1`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_MyTable1_table2` (`table2id`);

ALTER TABLE `MyTable1`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

score 1 · Answer 2 · answered Feb 13 '18 at 11:17

1

You have probably set a foreign key constraint on_delete. So, go to the Structure tab and click on Relation view. There, you can find foreign key constraints. Change the On delete option to No Action.

It may work.

answered Feb 13 '18 at 11:17

Omid Reza Abbasi

60
8

Unable to delete rows using PHPMyAdmin

2 Answers2