Page 1 of 1

Deleting rows using script vs. phpmyadmin

Posted: Wed Jun 25, 2014 12:18 pm
by Bill H
I'm having an issue deleting rows from a MySQL database when using phpmyadmin. When I issue a DELETE command in a php script the deletion occurs as expected, but when using phpmyadmin and attempting to delete a row by clicking on the "delete" icon for the row it asks me if I'm sure, and then merely says that zero rows were deleted.

There are several tables in the database, and I can delete rows in all of them except this one. It does have an auto-increment field in it, but I've never had that create such an issue before, and I see nothing else in the table that would suggest anything special about it.

Phpmyadmin also does not provide me with the edit functionality for rows in this table. It merely displays the row data in a condensed form when the "edit" emblem for the row is clicked.

A click on the "truncate table" in phpmyadmin does successfully delete all of the records, but that's of limited usefulness, of course.

Any ideas?

Re: Deleting rows using script vs. phpmyadmin

Posted: Wed Jun 25, 2014 12:44 pm
by requinix
It tells you the query it executed to do the deletion, right? Is the query correct? What if you run it yourself from a script?

Re: Deleting rows using script vs. phpmyadmin

Posted: Wed Jun 25, 2014 4:47 pm
by Bill H
The query WHERE clause includes each and every one of the 47 fields with its value, which seems odd to me, and the VARCHAR fields are shown in the query as `Ireport`.`EvalBy` = CAST(0x77656a68 AS BINARY)

On other tables the WHERE clause is simply WHERE id=5, ignoring the rest of the field values, with id being the primary key. This table also has a field id which is a sole key field.

A SQL delete using only the id field is a successful delete.

Re: Deleting rows using script vs. phpmyadmin

Posted: Wed Jun 25, 2014 5:38 pm
by requinix
Bill H wrote:The query WHERE clause includes each and every one of the 47 fields with its value, which seems odd to me, and the VARCHAR fields are shown in the query as `Ireport`.`EvalBy` = CAST(0x77656a68 AS BINARY)
0x77656a68 is a safe way, that phpMyAdmin sometimes uses for strings, to express "wejh". So the query may look a bit odd, yeah, but it should be correct.
If that's what it has to do to delete a query then it suggests the table doesn't have a primary key and/or unique key. Without one, the only way phpMyAdmin can try to delete the right row is with a condition to match every single value in it - and then hope there aren't duplicate rows.

But that query - if you try it yourself, verbatim, does it work? If not then those conditions aren't matching the row and you might have to look at each one individually to see where the bad data is.
Or you can add that missing primary/unique key to the table.

Re: Deleting rows using script vs. phpmyadmin

Posted: Wed Jun 25, 2014 5:50 pm
by Bill H
Yeah, the id field in this table is a key, and is the only one, but it is not a primary key. Why it is not escapes me, because why would someone (me, in this case) make it a key and not make it a primary key? Especially when in some hundred or so other tables someone (me again) has created the "id" field is a primary key.

I even commented on that in an earlier post here without it sinking in.

I well, as you suggest, examine the query to see if phpmyadmin is creating the query with inaccurate data. Can't imagine why it's doing that, but... Occam's Razor.

I will then break out in a wild case of sanity and make that silly key a primary key.