Bug #107843 General error: 1296 Got error 839 'Illegal null attribute' from NDBCLUSTER

• Submitted: 12 Jul 2022 3:13
• Modified: 14 Jul 2022 12:35
• Reporter: Sucre Sucre
• Status: Verified
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.29
• OS: Linux (oracle linux7)
• CPU Architecture: Any

[12 Jul 2022 3:13] Sucre Sucre

Description:
Description:
Got error 839 when insert into table after alter it with a tinyint field's default value added: default '0'; it has no default value set before that.

https://forums.mysql.com/read.php?25,704578,704603#msg-704603

How to repeat:
How to repeat:
First create a table with a field setting:
`the_field` tinyint NOT NULL
Then alter the table change that field's setting to:
CHANGE the_field the_field tinyint NOT NULL DEFAULT '0';
CHANGE the_field the_field tinyint NOT NULL DEFAULT 0;(or)

Then do a simple insert statement like below you'll get the error message.
insert into mytable(another_field) value('somevalue');

Suggested fix:
No
My workaroud:
deleted the table and recreated with the DEFAULT VALUE setting set in the create table statement.

[13 Jul 2022 17:39] MySQL Verification Team

Hi,
I cannot reproduce this using 8.0.29

node1 [localhost:29930] {msandbox} (b107843) > create table t1 (id int not null auto_increment primary key, bug tinyint not null) engine=ndbcluster;
Query OK, 0 rows affected (0.23 sec)

node1 [localhost:29930] {msandbox} (b107843) > alter table t1 change bug bug tinyint not null default 0;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

node1 [localhost:29930] {msandbox} (b107843) > insert into t1 (id) values (null);
Query OK, 1 row affected (0.00 sec)

node1 [localhost:29930] {msandbox} (b107843) > select * from t1;
+----+-----+
| id | bug |
+----+-----+
|  1 |   0 |
+----+-----+
1 row in set (0.00 sec)

node1 [localhost:29930] {msandbox} (b107843) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bug` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

node1 [localhost:29930] {msandbox} (b107843) > select @@version
    -> ;
+----------------+
| @@version      |
+----------------+
| 8.0.29-cluster |
+----------------+
1 row in set (0.00 sec)

node1 [localhost:29930] {msandbox} (b107843) > \q
Bye
[arhimed@localdev ndb_msb_ndb8_0_29]$ ./ndb_mgm -e show
Connected to Management Server at: localhost:22900
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @127.0.0.1  (mysql-8.0.29 ndb-8.0.29, Nodegroup: 0, *)
id=3    @127.0.0.1  (mysql-8.0.29 ndb-8.0.29, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-8.0.29 ndb-8.0.29)

[mysqld(API)]   4 node(s)
id=4    @127.0.0.1  (mysql-8.0.29 ndb-8.0.29)
id=5    @127.0.0.1  (mysql-8.0.29 ndb-8.0.29)
id=6    @127.0.0.1  (mysql-8.0.29 ndb-8.0.29)
id=7 (not connected, accepting connect from localhost)

[arhimed@localdev ndb_msb_ndb8_0_29]$

[14 Jul 2022 7:32] Sucre Sucre

There should be another tinyint field with default value set in the create table statement, you can repeat it like below(version:8.0.21): 

mysql> create table `t1` (`id` bigint not null auto_increment primary key, `bug1` tinyint not null default 0, `bug2` tinyint not null) engine=ndbcluster;
Query OK, 0 rows affected (0.30 sec)

mysql> alter table t1 change bug2 bug2 tinyint not null default 0;
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1(id) value(null);
ERROR 1296 (HY000): Got error 839 'Illegal null attribute' from NDBCLUSTER
mysq

[14 Jul 2022 12:35] MySQL Verification Team

Hi,

Interesting, verified as described, thanks

all best