CODEWITHSUNDEEP
phpmyadmin
Daniel
Daniel

Reputation: 1660

Unable to delete rows using PHPMyAdmin

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?

Upvotes: 2

Views: 3463

Answers (2)

Shalom
Shalom

Reputation: 60

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.

Upvotes: 1

Cuong Vu
Cuong Vu

Reputation: 26

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;

Upvotes: 1

Related Questions