Setting default value of BIT in MySQL

dmikester1's profile image dmikester1 posted 3 years ago in General Permalink

I have my local DB as MariaDB and am able to set the default value of the BIT column by setting the "Expression" to b'1'

Now I have the prod DB duplicated of that one except it is MySQL. But I cannot figure out how to set the default value to 1. When I try the same thing of setting the "Expression" to b'1', it converts it to "custom text" of 'b\'1\''

rednoise's profile image rednoise posted 3 years ago Permalink

I'm having the same problem and today I noticed one of my partners is using heidisql v10.3 and default value for bit columns are shown correctly as b'0' or b'1'.

If I browse the same table in v11.3 the default values are shown as 'b\'0\''. And the worst part is when you try to modify some column then heidsql can't save changes because there is an invalid default value in a bit column.

I have checked heidisql settings and cannot figure out what is causing that issue.

rednoise's profile image rednoise posted 3 years ago Permalink

I have downloaded v10.3 and v11.3 portable versions and definitely it is a bug. I attach screenshots of both versions showing the same table.

2 attachment(s):
  • v103
  • v113
rednoise's profile image rednoise posted 3 years ago Permalink

@ansgar could you please bring us some light on this issue?

dmikester1's profile image dmikester1 posted 4 weeks ago Permalink

Any progress on this issue? Running into it again today. Thanks!

Code modification/commit e028e3f from Ansgar Becker <anse@heidisql.com>, 4 weeks ago, revision 12.7.0.6895
Do not quote default value of a BIT column on MySQL. See https://www.heidisql.com/forum.php?t=38149
ansgar's profile image ansgar posted 4 weeks ago Permalink

Sorry for the long delay.

Could you please update to build 6895 once it's available, and check if it fixes this issue? Also please report on which server/version you are looking at.

My tests on MySQL 8.3 make me believe this is now fixed, for any MySQL version including the older ones. MariaDB should not have been affected, at least in the latest builds.

ansgar's profile image ansgar posted 4 weeks ago Permalink

Related: issue #1663

Please login to leave a reply, or register at first.