0

Why does phpMyAdmin give me this warning , and lack of function, when selecting from a table named only in lowercase (and underscores) that does have a (single column) primary key? I checked these elements after seeing this

Specifically my query is

SELECT su.* FROM `r8u2d_comps_testsubitem` su 
JOIN `r8u2d_comps_testitem` ti ON ti.id=su.testitemid 
JOIN `r8u2d_comps_test` t ON ti.testid=t.id 
WHERE t.id=241
ORDER BY ti.ordering

The table aliased as "su" has a column "id" (int(11), autoincrement) and a primary key using only this field. It looks to me like this query avoids all the restrictions listed in this answer, so what's the problem? Is it phpMyAdmin (my hosting company has 4.7.9, but I get the same problem locally with 5.0.4) or MySQL (host has 5.7.29-0ubuntu0.16.04.1 - (Ubuntu), I have 10.4.17-MariaDB - MariaDB Server, not strictly comparable I suppose).

Table structure

`id`            INT NOT     NULL AUTO_INCREMENT, 
`testitemid`    INT NOT     NULL
`marker`        CHAR(20)    NULL
`text`          TEXT        NOT NULL,
`ordering`      TINYINT     NOT NULL,
PRIMARY KEY (`id`),
KEY `testitemid` (`testitemid`),
KEY `ordering` (`ordering`),
CONSTRAINT `subelementToElement` 
    FOREIGN KEY (`testitemid`) REFERENCES `#__comps_testitem`(`id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
Markers
  • 328
  • 1
  • 13
  • Please share with us your tables structure. And an important thing: MySQL is not totally same as with MariaDB!!! – adampweb Dec 04 '20 at 18:45
  • Structure added. In this respect it appears that MySQL and MariaDB are (sufficiently, for the purposes of my question) the same, since I get this error on both platforms independently – Markers Dec 04 '20 at 18:54

1 Answers1

1

phpMyAdmin makes an effort to work with a primary/unique key for the purposes of enabling grid editing, but that detection logic doesn't hold up very well when using with multiple JOIN statements. It gets difficult for the phpMyAdmin parser to work backwards through some queries and determine which columns come from which tables and whether there's a primary key that could be used for editing the data. I suppose the warning message could better be written as something like "This table or query does not contain a unique column, or your query is a join that obfuscates the original table structure enough that we don't want to risk damaging your data."

Unfortunately, aside from someone rewriting this part of phpMyAdmin, the best solution I can recommend right now is to find the data you want to modify through your JOIN query then open that individual table and scroll through the Browse view to (or use Search to find) the row you wish to modify from the table directly.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
  • Thanks for that @Isaac. I thought that might be the problem since a selection from the table alone *does* recognise the key. I was hoping phpMyAdmin would "do better" in this instance since the field selection is exclusively from one table... Oh well. While much appreciating your answer, I'm conflicted about marking it as a solution :/ – Markers Dec 07 '20 at 10:38