#4734 (ok 4.3.9) Default values for binary fields do not support binary values

[Marcus Bointon]

I have a 16-byte BINARY field I use for storing md5 hashes (as it takes half the space of the text-based hex equivalent) and read and write to it using HEX and UNHEX functions, which all works fine. I have a problem setting the default value of the field in phpmyadmin because it doesn't seem to support the necessary syntax for binary values. An empty BINARY type is filled with \0 (null) chars, but I can't enter that as a default value. It would be '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' in normal CLI syntax, and mysql also supports two other formats: 0x00000000000000000000000000000000 (ODBC standard) and x'00000000000000000000000000000000' (SQL standard), however, none of these work in phpmyadmin in the default value box. It would perhaps be good to map a simple 0 to a full set of null bytes as the default value.

All of these work correctly in the CLI, none in phpmyadmin:

CREATE TABLE s (c BINARY(16) default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0');
CREATE TABLE t (c BINARY(16) default 0x00000000000000000000000000000000);
CREATE TABLE u (c BINARY(16) default x'00000000000000000000000000000000');

and all show like this in 'show create table' (with my defaults):

CREATE TABLE u (
c binary(16) DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I'm running latest git 4.4.0-dev from Jan 26 2015.

[Marcus Bointon]

When the default value is set correctly via CLI, it appears in phpmyadmin like the attached image when you edit the field, and does not show a default value at all when viewing the table structure.

[Madhura Jayaratne]

Applicable to 4.3 series as well.

[Madhura Jayaratne]

Fixed with https://github.com/phpmyadmin/phpmyadmin/commit/6171c6c8bde9bf20b466793c712d51c1fce93ff1