Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MySQL Table Editor/CREATE code Issue

chrisw's profile image chrisw posted 2 months ago in General Permalink

Hi,

Using the current build I've found an issue with the table editor and 'CREATE code' tab.

Where I've set the default value for a column to NULL this is not showing in HeidiSQL. The table editor claims I have 'No Default' and the code in 'CREATE code' leaves out "DEFAULT NULL" from these columns. Where I've set other values as the default it all works fine.

I've attached screenshot showing this on screen. If I use Table Tools it includes the correct default values, as below (from the same table I took a screenshot of):

`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) DEFAULT NULL,
`tenancy_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`council_id` int(11) DEFAULT NULL,
`import_id` int(11) DEFAULT NULL,
`person_ref` varchar(50) DEFAULT NULL,
`claim_ref` varchar(50) DEFAULT NULL,
`title` varchar(20) NOT NULL DEFAULT '',
`forename` varchar(50) NOT NULL DEFAULT '',
`middlename` varchar(50) NOT NULL DEFAULT '',
`surname` varchar(100) NOT NULL DEFAULT '',
`company` varchar(100) NOT NULL DEFAULT '',
`fullname` varchar(100) NOT NULL DEFAULT '',
`dob` date DEFAULT NULL,
`age_at_screen` int(11) DEFAULT NULL,
`gender` char(1) NOT NULL DEFAULT 'U',

Thanks,

Chris W

1 attachment(s):
  • Annotation-2020-02-20-170144
ansgar's profile image ansgar posted 2 months ago Permalink

Which version is it? If it's not the latest build, please update to that.

chrisw's profile image chrisw posted 2 months ago Permalink

Hi Angsar,

It is build 5889.

Thanks,

Chris

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 2 months ago Permalink

Then it's probably an older MySQL server? I created a test table on my MariaDB 10.3 server which shows the NULL defaults:

Description

chrisw's profile image chrisw posted 2 months ago Permalink

Hi Angsar,

Thanks for checking. My servers have MySQL 5.7.29 installed - the latest 5.7 update, which was released last month. Is this version still supported by Heidi?

Thanks,

Chris

ansgar's profile image ansgar posted 2 months ago Permalink

Yes, of course. I just asked because HeidiSQL has different code paths for newer and pre-5.0 servers. Will have to test the same on v5.7 again.

Code modification/commit 71ca19a from Ansgar Becker <anse@heidisql.com>, 2 months ago, revision 5896
Detect more column default values as NULL instead of "No default", on MySQL 5.7. See https://www.heidisql.com/forum.php?t=35678
ansgar's profile image ansgar posted 2 months ago Permalink

Should be fixed in the next build.

Though I'm still not 100% sure whether my new approach is correct. The documentation is a bit dodgy here, and says nothing about the difference of "NULL" and "no default":

COLUMN_DEFAULT

The default value for the column. This is NULL if the column has an explicit default of NULL, or if the column definition includes no DEFAULT clause.

Well I guess in conjunction with the "allow null" setting, this should be ok right now.

chrisw's profile image chrisw posted 1 month ago Permalink

I updated the new build this morning and it's working as it should for me now.

Thanks for fixing this.

halvor07's profile image halvor07 posted 1 week ago Permalink

Hi Angsar,

I am also having this problem. I'm running HeidiSQL 11.0.0.5934 and MariaDB 10.3.18. I have also recreated it on MySQL 5.7.28.

On MySQL 5.6.17 the opposite happens. It shows NULL as default after I have done DROP DEFAULT. (Only a problem when column is nullable.)

Example:

CREATE TABLE `test` (
  `null_default_null` int(11) DEFAULT NULL,
  `null_no_default` int(11),
  `not_null` int(11) NOT NULL
);
ALTER TABLE test ALTER null_no_default DROP DEFAULT;

After running this, I get what you see in the attached screenshot.

1 attachment(s):
  • bug

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