Invalid use of NULL value

This topic describes errors related to the invalid use of null values and the troubleshooting procedure.

Error: ERROR1138(22004)

Error code

• Error code: 5596

• MySQL error code: 1138

Error message

ERROR1138(22004) : Invalid use of NULL value

Example

Create a table named tny. When you modify the id field and set a NOT NULL constraint, this error is returned.

obclient> CREATE TABLE tny (
            id DECIMAL COMMENT 'Primary ey',
            trade_no VARCHAR(64),
            channel VARCHAR(2)
            );
Query OK, 0 rows affected

obclient> DESC tny;
+----------+---------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | decimal(10,0) | YES  |     | NULL    |       |
| trade_no | varchar(64)   | YES  |     | NULL    |       |
| channel  | varchar(2)    | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
3 rows in set

obclient> ALTER TABLE tny MODIFY id DECIMAL(40,2) NOT NULL;
ERROR 1138 (22004): Invalid use of NULL value

Troubleshooting procedure

In MySQL tenants of OceanBase Database V3.2.3, including all 1.x, 2.x, and 3.x versions, you cannot directly modify the NOT NULL constraint of columns after creating a table. You must set the NOT NULL constraint for fields when you create the table, as shown in the following example:

1. Create a table named tny1 and set the NOT NULL constraint for the fields.

   obclient> CREATE TABLE tny1 (
               id DECIMAL not null COMMENT 'Primary key',
               trade_no VARCHAR(64) not null,
               channel VARCHAR(2) not null
               );
   Query OK, 0 rows affected
   

2. View the schema of the tny1 table.

   obclient> DESC tny;
   +----------+---------------+------+-----+---------+-------+
   | Field   | Type         | Null | Key | Default | Extra |
   +----------+---------------+------+-----+---------+-------+
   | id       | decimal(10,0) | NO   |     | NULL    |       |
   | trade_no | varchar(64)   | NO   |     | NULL    |       |
   | channel  | varchar(2)    | NO   |     | NULL    |       |
   +----------+---------------+------+-----+---------+-------+
   3 rows in set