CODEWITHSUNDEEP
mysqlsqlstringnullmariadb
harpocrates
harpocrates

Reputation: 140

MARIADB - Can SQL convert NULL to an empty string if no default value is provided?

I have a table that has nullable columns:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

I insert a row with name set to NULL;

INSERT INTO some_table (id, name) VALUES (1, NULL);
Query OK, 1 row affected (0.02 sec)

SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

If I alter the table's name column to be not-nullable it apparently converts NULL to an empty string:

ALTER TABLE some_table CHANGE COLUMN name name VARCHAR(255) NOT NULL;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 |      |
+------+------+
1 row in set (0.02 sec)

At this point I would expect an exception to be raised telling me that I have NULL in my dataset and I can not set the column name to NOT NULL.

Is this a configurable option in SQL/MariaDB? Why is NULL being converted to an empty string?

There is a warning being invoked when altering the table:

SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)

Version:

SELECT version();
+----------------+
| version()      |
+----------------+
| 5.5.62-MariaDB |
+----------------+
1 row in set (0.02 sec)

Upvotes: 2

Views: 2795

Answers (2)

Erik Nellessen
Erik Nellessen

Reputation: 412

Using 10.3.15-MariaDB-1 on Debian Buster, I cannot reproduce the problem:

MariaDB [foo]> CREATE TABLE some_table(id int(11), name varchar(255));
Query OK, 0 rows affected (0.009 sec)

MariaDB [foo]> INSERT INTO some_table (id, name) VALUES (1, NULL);
Query OK, 1 row affected (0.003 sec)

MariaDB [foo]> SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.000 sec)

MariaDB [foo]> ALTER TABLE some_table CHANGE COLUMN name name VARCHAR(255) NOT NULL;
ERROR 1265 (01000): Data truncated for column 'name' at row 1
MariaDB [foo]> SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.000 sec)

MariaDB [foo]> SELECT version();
+-------------------+
| version()         |
+-------------------+
| 10.3.15-MariaDB-1 |
+-------------------+
1 row in set (0.000 sec)

If possible, I suggest you update your MariaDB version. It seems very old to me.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Apparently, from the documentation for ALTER TABLE, enabling strict mode would prevent your alter statement from succeeding:

This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE.

One way to enable strict mode from within MySQL:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

See here for other options.

Upvotes: 3

Related Questions