Migrations: utf8_unicode_ci collation allowing NULL values in varchar NOT NULL columns

Yii 2.0 General Discussions
1

Not sure if this is a question specific to Yii or not but I am hoping someone might be able to help me with this issue regardless as I can’t seem to find any references to the issue on Google or StackOverflow

I am building a database that is to accept values in the likes or Arabic script. I am by no means an expert on databases collation or character sets, but from researching the issues, I am using charset UTF8 and collation utf8_unicode_ci in my database. However, after creating and testing the table, I found out I can still enter NULL values into any fields that are varchar NULL NOT (integer NOT NULL is correctly enforcing the rule).

Below is an example of a table created with Yii migration: -




CREATE TABLE ref_language (

id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

language_id varchar(6) NOT NULL UNIQUE,

iso_639 varchar(3) NOT NULL,

iso_3166 varchar(2) NOT NULL,

name_native varchar(40) NOT NULL,

name_english varchar(40) NOT NULL,

ltr tinyint(1) NOT NULL DEFAULT '1',

active tinyint(1) NOT NULL DEFAULT '1'

) 

ENGINE InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci

Does anyone have any advice on what is going on here and the best solution?

Thanks in advance!

2

Charset and collation are correct. The constraint issue is very weird.

Try setting the following in my.cnf of MySQL:




[mysqld]

sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE"
3

Thank you for you all your help.

FYI I am using XAMPP.

I didnt have a my.cnf files so I added that line to my.ini




# The MySQL server

[mysqld]

port= 3306

socket = "C:/xampp/mysql/mysql.sock"

basedir = "C:/xampp/mysql" 

tmpdir = "C:/xampp/tmp" 

datadir = "C:/xampp/mysql/data"

pid_file = "mysql.pid"

# enable-named-pipe

key_buffer = 16M

max_allowed_packet = 1M

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

log_error = "mysql_error.log"

sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE"

# Change here for bind listening

# bind-address="127.0.0.1" 

# bind-address = ::1          # for ipv6

I restarted MySQL and revert and re-ran the migration but it was still the same. I then copied the my.ini file (keeping the original) and renamed the copy ‘my.cnf’. Restarted MySQL, reverted and re-ran the migration and the bug is still there.

4

Are you absolutely sure it’s null and not an empty string?

5

I accept I am very possibly going to look incredibly stupid here…

Have I got this right, that on a database level you can set a field to NOT NULL and if the field is varchar and an empty string is entered (i.e. you go into a record and delete the string in the varchar field) that this will be accepted as it’s technically an empty string as opposed to a NULL value?

However, when the a model is created with Gii, then it will find the properties for the field and if it’s set to NOT NULL, it will automatically filter it with the required validator, which will stop an empty string from being entered. Is that right? I have never been formally trained on databases and, if that scenario is the case, I have just misunderstood that technicality and I have probably always been testing out varchar/string NOT NULL behaviour through the Yii frontend.

6 

Have I got this right, that on a database level you can set a field to NOT NULL and if the field is varchar and an empty string is entered (i.e. you go into a record and delete the string in the varchar field) that this will be accepted as it's technically an empty string as opposed to a NULL value?

Yes. Empty string is not a null value.

Validator would prevent both nulls and empty strings, that’s correct.

7

:(

One of those times when I have missed out a certain detail without realising it.

Thanks again :)