MySQL Bugs: #22045: Got error 839 'Illegal null attribute' from NDBCLUSTER when 'Replace Into'

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

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

Changed category to more appropriate one

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

I verified using 5.1-bk-20060902 (tagged 5.1.12-beta).

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)

the trace file when do "replace..." and "insert..."

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

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

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 :(

the problem still exists in 5.1.22 RC 
Are there any Chances to have it fixed in 5.1 GA?

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.

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.

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)

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.

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.

Pushed into mysql-6.0-ndb

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.

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.

Closed per discussion with Jonas.

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)