BINARY display format seems broken

I have a BINARY column in a MySQL database. It typically stores two-bytes that represent various status bits. SQL queries seem to work correctly (bin(ps_status)). However in the table data viewer (grid and also the binary editor), the binary status 1111 is shown as 0x3135 which is the hex representation of the ASCII string representing the value of the field ("15"); the value 1101 is similarly shown as 0x3133 (ASCII bytes for string "13"). I can't imagine anyone would want to see BINARY data this way. Is there any way to get it to show BINARY data as 0x0F and 0x0D or bits or similar?

Sorry if I'm missing something obvious. I'm using HeidiSQL 12.7.0.6907 with MariaDB 8.0.37

That's a common question, you're not the first one asking :)

There's a button in the main toolbar, with 1s and 0s on it. That button toggles hex/ascii displaying of binary data. See the help page for a screenshot.

Thank you for the reply @ansgar.

I appreciate you pointing out the button (which was not obvious to me), but the display format really is broken. I doubt there is a single user who will want to view binary data as hex bytes representing the ASCII value of the data interpreted as a numeric value. It took me a while to even figure out what I was looking at. The button changes the value to the decimal representation of the binary data which is slightly better, but also not a great way to look at binary data...if the stored data were numeric values, the column would be INTEGER or similar.

I realize that the underlying problem is in MariaDB's HEX() implementation (which IMO is broken). It displays BINARY columns differently depending on their length! Smaller than X bytes and the data is interpreted as numeric values rather than a byte string and displayed as the HEX bytes of that ASCII string...this makes no sense at all. If the database designer wanted to store a 2-byte numeric value, they would use SMALLINT, not BINARY(2). Longer BINARY columns are displayed as you would expect (a set of HEX bytes).

Since HeidiSQL is a (fantastic) tool to make data viewing easier, I suggest considering displaying binary columns in a consistent (hex) format: CONV(CAST(col_name AS BINARY),10,16) which would be much more useful to the vast majority of users than the bizarre choice MariaDB made.

Oh there are many people including myself storing text data in binary columns. Binary columns are perfect for storing web urls for example. Also, MySQL and MariaDB's collation on binary columns is case sensitive, which you might want to achieve sometimes. In such cases that "View binary data as text" button is a good way for seeing what you might have stored. On the other hand you're right, and that's why the button is unpressed by default.

Btw, HeidiSQL does not use the HEX() SQL function, but its internal implementation for converting an ASCII string to Hex.

Thank you again. If HeidiSQL does not use the HEX() fn, it is even harder for me to see a rationale for presenting two binary bytes as the ascii characters that represent the decimal value of those bytes as a SMALLINT converted to a string. I can understand presenting the bytes themselves and the ASCII string they might represent (hexdump type utilities have been doing that since the dawn of computing: 0x00 0x0F 0x35 0x41 0x42 ..5AB) However the current inconsistent/length-dependent display of binary data seems counter-intuitive and undesirable for all but the rarest case. How often will someone want to see the value 0x000F as 0x31 0x35 ? (I suspect the answer is: almost never).

I have the gutt feeling this is a misunderstanding. Can you please post a screenshot of an example value which seems broken to you?

Sorry for the delay. Please consider the following table that holds periodic reports from remote solar-powered devices about their power status. The ps_status column is a 16-bit bitmap indicating status information about the system's power supplies and charging states:

Now consider how that column is displayed in the data browser:

I would expect the contents of a 2-byte BINARY column to be shown as 0x0007, 0x0005, 0x000F, not as 0x3700, 0x3500, 0x3135 (which is the ASCII representation of the column treated as a SMALLINT).

If I click the 100010 tool (thanks for pointing that out), I get somewhat better presentation, but the values are shown in decimal which again seems unlikely to be what folks will want from a BINARY column...if they wanted to treat the value as a number the column would be a SMALLINT.

Finally, please note that a BINARY(6) column displays as one might expect:

Probably the BIT data type is something you should consider for holding status information?

BLOBs, MEDIUMBLOBs etc. are more for holding large data. Like TEXT and MEDIUMTEXT, just with a binary collation.

@ansgar you're probably right. I'm not sure why I didn't use a BIT type in the first place.

Changing the column from BINARY(2) to BIT(16) doesn't work of course, so I had to create a new BIT(16) column and then move the data via an UPDATE ... SET query. The data conversion from BINARY(2) to BIT(16) is hideous because of the ridiculous default format: the binary value b'0001' is represented as '0x3100' and the value b'1100' as '0x3132' and so you can't just UPDATE ... SET col1 = col2. (I realize that these are all MYSQL issues, not HeidiSQL).

Although changing the column to BIT(16) and moving the data via an appropriate query did solve my display complaint, I think my original comment about the display of BINARY(2) columns remains valid: very few people are going to want to see a two-byte binary value displayed as the values of the ASCII bytes that would comprise the string when the two binary bytes are interpreted as a SMALLINT in decimal. Moreover, the inconsistent display of BINARY data types depending on length just seems wrong.

Thanks again for the discussion and suggestion.

Best regards, Dave