MySQL Bugs: #55121: error 839 'Illegal null attribute' from NDB for fields with default value

Bug #55121	error 839 'Illegal null attribute' from NDB for fields with default value
Submitted:	9 Jul 2010 11:46	Modified:	20 Aug 2010 9:58
Reporter:	mingfai ma	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-7.1	OS:	Linux
Assigned to:	Frazer Clement	CPU Architecture:	Any
Tags:	mysql-cluster-7.1.4b

Description:
For the table in the "how to repeat" section of this bug report, when i do an insert that default value of a field, error 839 happens.

from my tests, it seems to me the problem exists only when there are both varchar and text fields with defaults. if the table contains only varchar fields with default, or text fields with defaults, there won't be error. error occurs only when both types exist.

there is no error when the default value is not used, e.g.
insert into test (fid, f03) value(100, 'no problem');

How to repeat:
CREATE TABLE test (
  fid smallint(6) unsigned NOT NULL DEFAULT '0',
  f01 text NOT NULL,
  f02 varchar(255) NOT NULL DEFAULT '',
  f03 text NOT NULL DEFAULT '',
  PRIMARY KEY (fid)
) TYPE=NDBCLUSTER;

insert into test (fid) value(100);

SHOW ERRORS;
Got error 839 'Illegal null attribute' from NDB

Proposed patch against 7.0.

Attachment: bug55121.patch (text/x-patch), 3.61 KiB.

Seems like supplying the (ignored) default on the Blob column causes the NO_DEFAULT_VALUE_FLAG *not* to be set on the column, so we incorrectly assume that it must have a native default.

Ndb handler is modified to never assume that a Blob (or Geom etc.) has a native default.

Perhaps this is really a SQL-layer bug?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116254

3704 Frazer Clement	2010-08-19
      Bug#55121 error 839 'Illegal null attribute' from NDB for fields with default value
      
      User supplying an (ignored) default on the Blob column causes the
      NO_DEFAULT_VALUE_FLAG *not* to be set on the column, so we incorrectly 
      assume that it must have a native default and send no data for it.
      
      Ndb handler is modified to never assume that a Blob (or Geom etc.) has 
      a native default value.

Pushed to 7.0.18, 7.1.7.

Pushed into mysql-5.1-telco-7.0 5.1.47-ndb-7.0.18 (revid:frazer@mysql.com-20100819133545-ugm2wr3g2p03tna2) (version source revid:frazer@mysql.com-20100819133545-ugm2wr3g2p03tna2) (merge vers: 5.1.47-ndb-7.0.18) (pib:20)

Sorry, this is not a correct fix. Setting back to Open for a proper one.

Per http://dev.mysql.com/doc/refman/5.1/en/blob.html, DEFAULT is not allowed for TEXT and BLOB columns: "BLOB and TEXT columns cannot have DEFAULT values."

Trying to use DEFAULT for a BLOB or TEXT column should result in an warning or error + failed statement, as shown here in 5.1:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.50    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%SQL_MODE%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

# non-empty default: error/fail
mysql> create table b (c TEXT NOT NULL DEFAULT 'ASDFGHJKL');
ERROR 1101 (42000): BLOB/TEXT column 'c' can't have a default value

# empty default: warning
mysql> create table b (c BLOB NOT NULL DEFAULT '');
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'c' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

There seems to be no reason to break the established behaviour.

Can you clarify in what way the behaviour is changed?

As you show, MySQL allows a zero-length string default for a TEXT/BLOB column with a WARNING.
When a row is inserted, the Blob picks up this (special?) default value.

Prior to this fix, Ndb in 7.0 was crashing in this scenario.

Now it behaves as it did before, and as MyISAM does, by setting the TEXT/BLOB column to a zero length string.

Perhaps this zero-length-string for a non-nullable BLOB/TEXT column is a special case that needs documented?
 
mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.1.47-ndb-7.0.18-debug-log |
+-----------------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%SQL_MODE%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create table myisamblob (a int primary key, b text not null default '') engine=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'b' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into myisamblob (a) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisamblob;
+---+---+
| a | b |
+---+---+
| 1 |   |
+---+---+
1 row in set (0.00 sec)

mysql> select a,length(b) from myisamblob;
+---+-----------+
| a | length(b) |
+---+-----------+
| 1 |         0 |
+---+-----------+
1 row in set (0.00 sec)

mysql> create table ndbblob (a int primary key, b text not null default '') engine=ndb;
Query OK, 0 rows affected, 1 warning (0.41 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'b' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into ndbblob (a) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from ndbblob;
+---+---+
| a | b |
+---+---+
| 1 |   |
+---+---+
1 row in set (0.00 sec)

mysql> select a, length(b) from ndbblob;
+---+-----------+
| a | length(b) |
+---+-----------+
| 1 |         0 |
+---+-----------+
1 row in set (0.00 sec)

mysql>

Frazer,

The example I gave was with 5.1.50 (mainline). If I understand the developer comments correctly, DEFAULT is now simply going to be ignored by NDB for TEXT/BLOB, which is not the same.

At the time I was hampered by a build issue on a new machine with a new GCC (see BUG#52514) but got that sorted. I'll re-test using a new telco-7.1 clone/build.

Set back to Documenting to remind myself to do this.

Documented bugfix in the NDB-7.0.18 and 7.1.7 changelogs as follows:

        Trying to create a table having a BLOB or TEXT column with
        DEFAULT '' failed with the error -Illegal null attribute-. 
        (An empty default is allowed and ignored by MyISAM; NDB 
        should do the same.)

Closed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html