Binary dropdown field display bug

[chrisch297]

Describe the bug

I have two tables A and B. Table A contains UUIDs, stored as BINARY(16). Table B uses the UUIDs from table A as foreign keys.

phpMyAdmin shows me for table B a nice dropdown field, where I can select the UUID from table A. But it seems that on the selected UUID in the dropdown, the number 1 is added to the end of the UUID. See screenshots.

As a result, if I want to save the changes, phpMyAdmin thinks that the UUID has changed and generates an UPDATE statement for the UUID which of course fails with a foreign key constraint failure because the UUID followed by a 1 does not exist.

I guess there must be some errors in the part where the binary value is converted to a human readable format and where the dropdown is populated.

Possibly related to this: #16013 (comment)

Expected behavior

The selected UUID in the dropdown should be shown correctly without the 1 at the end

Screenshots

On the selected UUID, a 1 is added at the end. The correct UUID for Projekt 1 is 0x ... e4a without 1 at the end! The other not-selected UUIDs in the dropdown are correct.

Saving changes gives a SQL error:

Server configuration

• Apache/2.4.48 (Win64) OpenSSL/1.1.1k PHP/8.0.7
• Database client version: libmysql - mysqlnd 8.0.7
• PHP extension: mysqli Documentation curl Documentation mbstring Documentation
• PHP version: 8.0.7
• phpMyAdmin version: 5.1.1

Client configuration

• Browser: Firefox
• Operating system: Win10

[williamdes]

Hi @chrisch297
Thank you for reporting this issue, could you post test tables to allow us to have a quicker and better reproducing of the issue please ?

[chrisch297]

@williamdes Sure.

Two test tables authors and articles with UUIDs as primary keys:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `articles` (
  `uuid` binary(16) NOT NULL,
  `authors_uuid` binary(16) NOT NULL,
  `article` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `articles` (`uuid`, `authors_uuid`, `article`) VALUES
(0x4616c613f014f7ea9611a7141a758300, 0x4616c243c014f7ea9611a7141a758300, 'Some text...');

CREATE TABLE `authors` (
  `uuid` binary(16) NOT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `authors` (`uuid`, `name`) VALUES
(0x4616c243c014f7ea9611a7141a758300, 'John'),
(0x4616c653f014f7ea9611a7121a758299, 'Tina');


ALTER TABLE `articles`
  ADD PRIMARY KEY (`uuid`),
  ADD KEY `fk_articles_authors_idx` (`authors_uuid`);

ALTER TABLE `authors`
  ADD PRIMARY KEY (`uuid`);


ALTER TABLE `articles`
  ADD CONSTRAINT `fk_articles_authors` FOREIGN KEY (`authors_uuid`) REFERENCES `authors` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;

On editing an article, phpMyAdmin uses for the authors_uuid dropdown field not the correct UUID for John (which is 0x4616c243c014f7ea9611a7141a758300), it uses 0x4616c243c014f7ea9611a7141a7583001 (Note the added 1 at the end).

Due to the incorrect UUID, it is not possible to save the edited article because the (incorrect) UUID doesn't exist in the authors table and the database throws an foreign key constrains error.

[williamdes]

it's strange I could not reproduce this

You can try on http://phpmyadmin-stable.public-instance.eu/ (root/public public/public)

[chrisch297]

You can try on http://phpmyadmin-stable.public-instance.eu/ (root/public public/public)

I have this issue also on your test database, see screenshot. The issue comes up when editing an existing entry. In your screenshot, you tried to insert a new entry which seems to work fine.

[williamdes]

I had missed that is needed to be in edit mode
Confirmed

[williamdes]

Seems like it was added by mistake in: e9d3fdd#diff-a3501a31fbdb91c217f32a573019b35304950ed21b951f56db50ddfb6340ae04R1240