Skip to main content

Search

Welcome to Tibero GTS!

Considerations for Handling Empty Strings During MySQL Data Migration

Document Type | Troubleshooting

Category | Migration

Applicable Product Version | 6FS07

Error Code | 10005

Document Number | TMITS008

 

Issue

When migrating '' (empty string) data from MySQL to Tibero, a JDBC-10005: NOT NULL constraint violation error occurred.
 
 

Cause

In MySQL, '' (empty string) is treated as a valid string, not NULL, but in Tibero, '' (empty string) is treated as NULL.
Therefore, when migrating '' (empty string) data in columns with NOT NULL constraints to Tibero, a NOT NULL constraint violation error may occur in Tibero.
 
 

Solutions

An agreement on how to handle '' (empty string) is required as follows.
  1. Treat as NULL: Use INSERT_ZERO_LENGTH_STRING_AS_NULL=Y, remove NOT NULL constraints
  2. Treat as ' ' (space): Use INSERT_ZERO_LENGTH_STRING_AS_NULL=N
 

Example Application

Insert '' (empty string) data into a NOT NULL column as shown below.
  • mysql
mysql> CREATE TABLE empty_table (C1 VARCHAR(10) DEFAULT '' NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into empty_table values (''), ('a');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from empty_table;
+----+
| C1 |
+----+
|    |
| a  |
+----+
2 rows in set (0.00 sec)
  • tibero
SQL> CREATE TABLE empty_table (C1 VARCHAR(10) DEFAULT '' NOT NULL);
Table 'EMPTY_TABLE' created.
SQL> insert into empty_table values (''), ('a');
TBR-10005: NOT NULL constraint violation ('WORLD'.'EMPTY_TABLE'.'C1').

INSERT_ZERO_LENGTH_STRING_AS_NULL setting results for migration are as follows.
SQL>  CREATE TABLE empty_table (C1 VARCHAR(10));
Table 'EMPTY_TABLE' created.

INSERT_ZERO_LENGTH_STRING_AS_NULL=Y

SQL> select c1, length(c1) from empty_table;

C1         LENGTH(C1)
---------- -----------
a                   1

2 rows selected.


INSERT_ZERO_LENGTH_STRING_AS_NULL=N

SQL> select c1, length(c1) from empty_table;

C1         LENGTH(C1)
---------- -----------
                    1
a                   1

2 rows selected.

Note

NULL storage methods by RDBMS
INSERT VALUES   TIBERO   ORACLE   MySQL   MariaDB   SQL Server   PostgreSQL   CUBRID
--------------  -------  -------  ------  --------  -----------  -----------  -------
NULL            NULL     NULL     NULL    NULL      NULL         NULL         NULL
'NULL'          string   string   string  string    string       string       string
' ' (space)     string   string   string  string    string       string       string
''  (empty)     NULL     NULL     string  string    string       string       string
 
 
 

 

Related to