Go - MySQL inserts even when if NOT NULL set

Question

I'm writing simple API in Go using MySQL but my MySQL code inserts data to row even the column is set to NOT NULL. Here is my code:

schema.sql

CREATE TABLE IF NOT EXISTS seller (
    seller_id int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL UNIQUE,
    contact varchar(255) NOT NULL,
    street varchar(255) NOT NULL,
    city varchar(255) NOT NULL,
    nip varchar(255) NOT NULL UNIQUE,
    bank varchar(255) NOT NULL,
    account_number varchar(255) NOT NULL UNIQUE,
    PRIMARY KEY (seller_id)
);

database/mysql/invoice.go

func (r *invoiceRepository) CreateSeller(seller *invoice.Seller) error {
        query := "INSERT INTO seller (name, contact, street, city, nip, bank, account_number) VALUES (?, ?, ?, ?, ?, ?, ?)"
        stmt, err := r.db.Prepare(query)
        if err != nil {
                log.Errorf("Unable to create seller %s: %v", seller.SellerName, err)
                return err
        }

        res, err := stmt.Exec(&seller.SellerName, &seller.SellerContact, &seller.SellerStreet, &seller.SellerCity, &seller.SellerNIP, &seller.SellerBank, &seller.SellerAccountNumber)
        if err != nil {
                log.Errorf("Error while creating seller: %v", err)
                return err
        }

        newID, err := res.LastInsertId()
        if err != nil {
                log.Errorf("Error while getting inserted id: %v", err)
                return err
        }

        log.Infof("Created seller %s [id=%d]", seller.SellerName, newID)
        return nil
}

Now when I'm making a POST request (using httpie) with the only seller_name my code creates row even with null columns. It looks like this: http POST localhost:8000/api/seller/create seller_name=testt

Here is how my row looks in table:

+-----------+-------+---------+--------+------+-----+------+----------------+
| seller_id | name  | contact | street | city | nip | bank | account_number |
+-----------+-------+---------+--------+------+-----+------+----------------+
|         1 | testt |         |        |      |     |      |                |
+-----------+-------+---------+--------+------+-----+------+----------------+

I don't know where I've made a mistake.

EDIT (added model)

type Seller struct {
        SellerID            int64  `json:"seller_id"`
        SellerName          string `json:"seller_name"`
        SellerContact       string `json:"seller_contact"`
        SellerStreet        string `json:"seller_street"`
        SellerCity          string `json:"seller_city"`
        SellerNIP           string `json:"seller_nip"`
        SellerBank          string `json:"seller_bank"`
        SellerAccountNumber string `json:"seller_account_number"`
}

"I don't know where I've made a mistake." Execute SELECT @@sql_mode; ? and paste it here.. — Raymond Nijland
– Raymond Nijland, Commented May 1, 2019 at 17:43
As iám pretty sure your MySQL server seams to be running without SQL mode STRICT_TRANS_TABLES see without and with — Raymond Nijland
– Raymond Nijland, Commented May 1, 2019 at 17:48
You were expecting it to return an error? An error at the DB layer should be a worst-case scenario; something before that should be sanitizing and validating user inputs. I.e., if you don't want nulls/empties inserted into the DB, they should never make it to your CreateSeller function. — Adrian
– Adrian, Commented May 1, 2019 at 17:54
A couple of things: What does seller actually look like, are the fields populated (remember: empty strings are not nil/NULL values)? More over: you create a prepared statement, but that resource is never closed, after the error check on r.db.Prepare(), add a defer stmt.Close(), please. — Elias Van Ootegem
– Elias Van Ootegem, Commented May 1, 2019 at 18:09
@RaymondNijland +--------------------------------------------+ | @@sql_mode | +--------------------------------------------+ | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ — xaos_xv
– xaos_xv, Commented May 1, 2019 at 19:44

mu is too short · Accepted Answer · 2019-05-04 03:19:36Z

4

Go isn't inserting nulls in your table, it is inserting empty strings. Your code is effectively doing this:

seller := Seller{}
seller.SellerName = "testt"

to create the seller that you're inserting. So all the fields in your seller except SellerName will have the zero value for their type; the fields causing you trouble are strings and the zero value of a string is "" (i.e an empty string) so the SQL ends up looking like:

INSERT INTO seller (name, contact, ...)
VALUES ('testt', '', ...)

and there isn't a NULL in sight.

You could validate the incoming data in Go so that empty strings are rejected, you could use NullString instead of string and let the database deal with NULLs (assuming MySQL is properly configured), or do both.

edited May 4, 2019 at 3:19

answered May 1, 2019 at 18:55

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xaos_xv Over a year ago

It's working. You're right, Go wasn't inserting null but empty string. I changed string to sql.NullString in my model and now everything works correctly.

Grigoreas P. · Accepted Answer · 2019-05-17 10:50:39Z

0

Yes, because when it comes to Mysql: emtpy string (e.g '') is NOT a null. It treats it like being a string (only empty).

answered May 17, 2019 at 10:50

Grigoreas P.

2,47226 silver badges20 bronze badges

Collectives™ on Stack Overflow

Go - MySQL inserts even when if NOT NULL set

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related