#1216 Empty default value reknown as NULL

[Anonymous]

Originally created by: d.alb...@gmail.com

What exact steps will reproduce the problem?
1. Create a table using the folling sql code CREATE TABLE `aTable`
(`aField` TEXT NOT NULL COLLATE utf8_general_ci)
2. Edit the table

What was the expected output?
the aField column should report empty default value

What happened instead?
the aField column report NULL default value

Suggested fix (optional)?

Version used?
HeidiSQL revision: 2476
MySQL Server version: 5.1.35
Operating system: Windows Seven RC1 Build 7100

[Anonymous]

Originally posted by: a...@anse.de

> What happened instead?
> the aField column report NULL default value

Not here - says empty string. 5.1.22 server here. Hmmm... What's going on here?

Labels: Severity-Problematic
Status: Accepted

[Anonymous]

Originally posted by: d.alb...@gmail.com

mmm, i've tried to execute the query that heidisql launch to look for fields
SHOW FULL COLUMNS FROM `atable` ;

And the output reports that NULL value for Default column on aField.

Launching the same query from php reports

object(stdClass)#1 (9) {
  ["Field"]=>
  string(6) "aField"
  ["Type"]=>
  string(4) "text"
  ["Collation"]=>
  string(15) "utf8_general_ci"
  ["Null"]=>
  string(2) "NO"
  ["Key"]=>
  string(0) ""
  ["Default"]=>
  NULL
  ["Extra"]=>
  string(0) ""
  ["Privileges"]=>
  string(31) "select,insert,update,references"
  ["Comment"]=>
  string(0) ""
}

so, it seems that is mysql that reports the column as NULL.

sql_mode is set to nothing, actually

This is really strange, what is your sql_mode value?

[Anonymous]

Originally posted by: d.alb...@gmail.com

i've checked my web server, and there empty default values works correctly

On my web server i use mysql version 5.0.45 compiled from sources

sql_mode is unset

[Anonymous]

Originally posted by: a...@anse.de

@@sql_mode is also empty here - no clue what that has to do with this NULL issue?

[Anonymous]

Originally posted by: d.alb...@gmail.com

mmm, i was thinking that can be that the new version of mysql can show the default
value for text/blob fields as null because they can't have one

Look my response at issue #1215 (comment n. 4)

[Anonymous]

Originally posted by: a...@anse.de

Yes, could be related.

Not a bug in HeidiSQL - setting it to Upstream.

Status: Upstream

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Note: under normal circumstances, do not unset sql_mode.  STRICT mode tells MySQL to
avoid doing really stupid things.

[Anonymous]

Originally posted by: d.alb...@gmail.com

with STRICT mode set isin't possible to use HeidiSQL with non null text fields so there
isin't any other way.

[Anonymous]

Originally posted by: rosenfie...@gmail.com

With STRICT mode, the server tells you that you're trying to do something that it
does not support.

When you disable STRICT mode, it stops telling you that things will go wrong - but
they still do.