odb with MySQL sql-mode NO_AUTO_VALUE_ON_ZERO

Question

I use MySQl with enabled sql-mode NO_AUTO_VALUE_ON_ZERO, which means that it's not possible to use 0 in INSERT statements for PK column to indicate MySQL that it should automatically assign value for PK.

I have persistent object of following definition:

#pragma db object
struct person
{
    #pragma db id auto
    uint64_t id_ = 0;

    std::string first_name_;
    std::string last_name_;
};

And table:

CREATE TABLE `person` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(50) NOT NULL,
`last_name` VARCHAR(50) NOT NULL,
 PRIMARY KEY(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 collate=utf8_unicode_ci;

I assume that 'id auto' specificator should generate following sql in mapper classes:

INSERT INTO person (first_name, last_name) VALUES ("John", "Dohn");

but real sql is:

INSERT INTO person (id, first_name, last_name) VALUES (0, "John", "Dohn");

which leads to inserting row with PK value equal to zero and leads to duplicate PK errors, instead of inserting row with autoincremented id.

Is there some way to force odb compliter to generate proper SQL-code because I don't want to disable NO_AUTO_VALUE_ON_ZERO?

Answer 1

I found out that only possible way to achieve proper behavior is usage of odb::nullable:

#pragma db object
struct person
{
    #pragma db id auto
    odb::nullable<uint64_t> id_;

    std::string first_name_;
    std::string last_name_;
};

With this approach odb will generate following query if id_ left uninitialized:

INSERT INTO person (id, first_name, last_name) VALUES (NULL, "John", "Dohn");

which is exact what I need.