Bug #22045 Got error 839 'Illegal null attribute' from NDBCLUSTER when 'Replace Into'
Submitted: 6 Sep 2006 9:00 Modified: 31 May 2008 10:09
Reporter: Li Ma Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.11 OS:Linux (RedHat AS4)
Assigned to: Kristian Nielsen CPU Architecture:Any
Tags: error 839, Illegal null attribute, NDB Cluster

[6 Sep 2006 9:00] Li Ma
Description:
cant run  'Replace ' in NDB Cluster.
and it got such error:
mysql> REPLACE INTO memberfields (uid) VALUES ('274295');
ERROR 1296 (HY000): Got error 839 'Illegal null attribute' from NDBCLUSTER

How to repeat:

can run 'insert INTO', though there has some warnings:
mysql> insert INTO memberfields (uid) VALUES ('274295');
Query OK, 1 row affected, 5 warnings (0.51 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1364 | Field 'bio' doesn't have a default value        |
| Warning | 1364 | Field 'signature' doesn't have a default value  |
| Warning | 1364 | Field 'sightml' doesn't have a default value    |
| Warning | 1364 | Field 'ignorepm' doesn't have a default value   |
| Warning | 1364 | Field 'groupterms' doesn't have a default value |
+---------+------+-------------------------------------------------+

MyISAM allows the REPLACE with the same warnings.

the following is the structure of table:
mysql> desc memberfields;
+--------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | NO | PRI | 0 | |
| nickname | varchar(30) | NO | | | |
| site | varchar(75) | NO | | | |
| alipay | varchar(50) | NO | | | |
| icq | varchar(12) | NO | | | |
| qq | varchar(12) | NO | | | |
| yahoo | varchar(40) | NO | | | |
| msn | varchar(40) | NO | | | |
| taobao | varchar(40) | NO | | | |
| location | varchar(30) | NO | | | |
| customstatus | varchar(30) | NO | | | |
| medals | varchar(255) | NO | | | |
| avatar | varchar(255) | NO | | | |
| avatarwidth | tinyint(3) unsigned | NO | | 0 | |
| avatarheight | tinyint(3) unsigned | NO | | 0 | |
| bio | mediumtext | NO | | | |
| signature | mediumtext | NO | | | |
| sightml | mediumtext | NO | | | |
| ignorepm | mediumtext | NO | | | |
| groupterms | mediumtext | NO | | | |
| authstr | varchar(20) | NO | | | |
+--------------+-----------------------+------+-----+---------+-------+

Suggested fix:
enable  REPLACE in NDB Cluster
[6 Sep 2006 9:08] Sveta Smirnova
Changed category to more appropriate one
[6 Sep 2006 9:20] Jon Stephens
Verified using a simplified test case:

# using MyISAM:

mysql> CREATE TABLE t (c1 INT NOT NULL, c2 INT NOT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'c2' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t (c1) VALUES (5);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'c2' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'c2' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

# using NDB:

+----------------+
2 rows in set (0.01 sec)

# using NDB:

mysql> CREATE TABLE t (c1 INT NOT NULL, c2 INT NOT NULL) ENGINE = NDB;
Query OK, 0 rows affected (3.55 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
ERROR 1296 (HY000): Got error 839 'Illegal null attribute' from NDBCLUSTER
mysql> INSERT INTO t (c1) VALUES (5);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'c2' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
ERROR 1296 (HY000): Got error 839 'Illegal null attribute' from NDBCLUSTER
[6 Sep 2006 9:21] Jon Stephens
I verified using 5.1-bk-20060902 (tagged 5.1.12-beta).
[6 Sep 2006 9:48] Jon Stephens
mysql> CREATE TABLE t (c1 INT NOT NULL DEFAULT 0, c2 INT NOT NULL DEFAULT 0) ENGINE = NDB;
Query OK, 0 rows affected (3.35 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
ERROR 1296 (HY000): Got error 839 'Illegal null attribute' from NDBCLUSTER
mysql> DROP TABLE t;
Query OK, 0 rows affected (0.54 sec)

mysql> CREATE TABLE t (c1 INT NOT NULL DEFAULT '0', c2 INT NOT NULL DEFAULT '0') ENGINE = NDB;
Query OK, 0 rows affected (4.98 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
ERROR 1296 (HY000): Got error 839 'Illegal null attribute' from NDBCLUSTER

mysql> DROP TABLE t;
Query OK, 0 rows affected (0.85 sec)

mysql> CREATE TABLE t (c1 INT NULL, c2 INT NULL) ENGINE = NDB;
Query OK, 0 rows affected (2.92 sec)

mysql> REPLACE INTO t (c1) VALUES (5);
Query OK, 1 row affected (0.06 sec)
[7 Nov 2006 10:49] li zhou
the trace file when do "replace..." and "insert..."

Attachment: master.trace.gz (application/x-gzip, text), 122.25 KiB.

[6 Dec 2006 9:39] Bugs System
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/16500

ChangeSet@1.2320, 2006-12-06 17:38:41+00:00, lzhou@dev3-63.dev.cn.tlan +1 -0
  BUG#22045 Enable "REPLACE Into" in NDB cluster
[11 Mar 2007 20:00] Stewart Smith
What's the progress on this bug?

Still happens on recent BK (5.1.17 based).

without this fixed i have no chance of running my mythtv setup fully on NDB :(
[25 Oct 2007 8:23] Egbert Rabe
the problem still exists in 5.1.22 RC 
Are there any Chances to have it fixed in 5.1 GA?
[10 Dec 2007 10:43] Bugs System
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/39635

ChangeSet@1.2582, 2007-12-10 11:42:45+01:00, knielsen@ymer.(none) +3 -0
  BUG#22045: Got error 839 'Illegal null attribute' from NDBCLUSTER when 'Replace Into'
  
  Temporary workaround/fix for REPLACE INTO in NDB with partial column
  list.
  
  REPLACE INTO with partial column list expects handler::write_row() to
  overwrite columns not in write_set() with default values.
  
  However, NDB replication expects handler::write_row() to leave columns
  not in write_set alone.
  
  Replication team is working to resolve this inconsistency. This is a
  temporary work-around that implements different semantics based on
  whether running in slave SQL thread or not.
[17 Dec 2007 7:45] Bugs System
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/40084

ChangeSet@1.2639, 2007-12-17 08:45:18+01:00, knielsen@ymer.(none) +3 -0
  BUG#22045: Got error 839 'Illegal null attribute' from NDBCLUSTER when 'Replace Into'
  
  Temporary workaround, check if running in slave SQL thread or not, and
  change semantics of write_set in write_row() as expected by the different
  upper layers.
[11 Mar 2008 10:59] Kristian Nielsen
To clarify, here is an explanation of what the bug is:

The issue is with REPLACE INTO where not all columns are listed in the list of columns.

From documentation, these columns are supposed to be set to the table default
values (irrespectively of whether the row already existed or not).

However, for an NDB table, such columns instead receive values as follows:

 - If the row already existed, the old value of the column is used.
 - If the row did not exist, and the column is NULL-able, it is set to NULL.
 - If the row did not exist, and the column is not NULL-able, an error occurs.

A workaround from the user/application side is to specify all columns in the
column list in REPLACE INTO. So instead of this:

    REPLACE INTO t (column1, column4) VALUES (1,1)

use this:

    REPLACE INTO t (column1, column2, column3, column4) VALUES (1,DEFAULT,DEFAULT,1)
[4 Apr 2008 9:58] Bugs System
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/44903

ChangeSet@1.2533, 2008-04-04 11:54:23+02:00, knielsen@ymer.(none) +7 -0
  BUG#22045: Got error 839 'Illegal null attribute' from NDBCLUSTER when 'Replace Into'
  
  Temporary workaround, check if running in slave SQL thread or not, and
  change semantics of write_set in write_row() as expected by the different
  upper layers.
[7 Apr 2008 6:38] Kristian Nielsen
Pushed to mysql-5.1-new-ndb, mysql-5.1-telco-6.2, mysql-5.1-telco-6.3, and
mysql-5.1-telco-6.4.
[7 Apr 2008 7:08] Kristian Nielsen
Pushed into mysql-6.0-ndb
[7 Apr 2008 12:28] Bugs System
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/44988

ChangeSet@1.2534, 2008-04-07 14:27:57+02:00, knielsen@ymer.(none) +6 -0
  BUG#22045.
  
  Move testcases to separate file as they currently fail in statement-based
  replication due to bug.
[11 Apr 2008 12:19] Jon Stephens
Documented in the 5.1.24-ndb-6.3.13 changelog as follows:

        REPLACE statements did not work correctly with NDBCLUSTER tables when all 
        columns were not explicitly listed.

Left in PQ status pending additional merges.
[31 May 2008 10:09] Jon Stephens
Closed per discussion with Jonas.
[12 Dec 2008 23:28] Bugs System
Pushed into 6.0.6-alpha  (revid:sp1r-knielsen@ymer.(none)-20080407122757-51176) (version source revid:sp1r-tomas@poseidon.ndb.mysql.com-20080516085603-30848) (pib:5)