Default values for binary columns not escaped correctly

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
digiflex
Posts: 11
Joined: Tue 16 Jun 2015 02:16

Default values for binary columns not escaped correctly

Post by digiflex » Mon 16 Apr 2018 03:07

I have a table with a not-null binary column. "SHOW CREATE TABLE" says:

Code: Select all

CREATE TABLE `panel_data_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `packet_data` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3238 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
(extra columns removed for this example)

But when I view that table in DBForge I get a create table with extra escaping:

Code: Select all

CREATE TABLE `panel_data_requests` (
  id int(11) NOT NULL AUTO_INCREMENT,
  packet_data binary(16) NOT NULL DEFAULT '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
  ...
That also happens in the "Columns" editor in that table view tab - the default column has the twice-escaped text.

Obviously that is invalid SQL and it's not possible to create or set a non-ASCII default via DBForge.

Both hex and escaped zeros are valid in that situation:

Code: Select all

ALTER TABLE panel_data_requests
  CHANGE COLUMN packet_data packet_data binary(16) NOT NULL DEFAULT 0x00000000000000000000000000000000;
ALTER TABLE panel_data_requests
  CHANGE COLUMN packet_data packet_data binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0';

alexa
Devart Team
Posts: 2871
Joined: Fri 24 Jun 2011 14:17

Re: Default values for binary columns not escaped correctly

Post by alexa » Mon 16 Apr 2018 08:50

We will investigate this issue and will answer you as soon as possible.

alexa
Devart Team
Posts: 2871
Joined: Fri 24 Jun 2011 14:17

Re: Default values for binary columns not escaped correctly

Post by alexa » Thu 26 Apr 2018 14:27

We will fix this issue in one of the next product builds and will notify you once it's available for downloading.

digiflex
Posts: 11
Joined: Tue 16 Jun 2015 02:16

Re: Default values for binary columns not escaped correctly

Post by digiflex » Sun 29 Apr 2018 22:54

thanks

Post Reply