Field 'id' doesn't have a default value

[Alexandros G.]

Hi! 

I am a quite new jOOQ user and I would appreciate a little help.

I have 2 mysql tables ("logs" and "datasets") that both use as a primary key a field named "id" with AUTO_INCREMENT as default value.

I generate all the required files for jOOQ and then try to store records in these 2 tables.

Storing a record to the first table works fine:

DSLContext database = DSL.using(this.conn, SQLDialect.MYSQL);  

LogsRecord logItem = database.newRecord(Tables.LOGS);

logItem.setUserEmail(user_email);

logItem.setAction(action);

logItem.setMessage(message);

logItem.store();  

Storing to the second table:

DSLContext database = DSL.using(this.conn, SQLDialect.MYSQL);  

DatasetsRecord record = database.newRecord(Tables.DATASETS);

record.setUserEmail(user_email);

record.setName(datasetName);

record.store();  

raises an Exception with the following messsage:

SQL [insert into `metacatalogue`.`datasets` (`name`, `user_email`) values (?, ?)]; Field 'id' doesn't have a default value

I am really confused! What is going wrong here?

Alexandros

[Lukas Eder]

Hello,

I've tried to reproduce this locally:

create table datasets (

  id int not null primary key auto_increment,

  name varchar(250),

  user_email varchar(120),

  created_at datetime default current_timestamp

);

-- works

insert into datasets(name, user_email) values('a', 'b');

and then

DatasetsRecord rec = create().newRecord(Tables.DATASETS);

rec.setName("x");

rec.setUserEmail("y");

// works too

rec.store();

which works just fine:

12:37:54,987 DEBUG [org.jooq.tools.LoggerListener                     ] - Executing query          : insert into `test`.`datasets` (`name`, `user_email`) values (?, ?)

12:37:54,989 DEBUG [org.jooq.tools.LoggerListener                     ] - -> with bind values      : insert into `test`.`datasets` (`name`, `user_email`) values ('x', 'y')

12:37:55,356 DEBUG [org.jooq.tools.LoggerListener                     ] - Affected row(s)          : 1

12:37:55,357 DEBUG [org.jooq.tools.StopWatch                          ] - Query executed           : Total: 1.175s

12:37:56,009 DEBUG [org.jooq.tools.StopWatch                          ] - Finishing                : Total: 1.828s, +652.807ms

Are you sure you have everything set up correctly in your database?

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alexandros G.]

The CREATE code that corresponds to my tables is:

CREATE TABLE `datasets` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(250) NOT NULL DEFAULT '0',

`user_email` VARCHAR(120) NOT NULL,

`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

)

COLLATE='utf32_general_ci'

ENGINE=InnoDB

AUTO_INCREMENT=3

;

CREATE TABLE `logs` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`user_email` VARCHAR(120) NOT NULL DEFAULT '0',

`action` VARCHAR(50) NOT NULL DEFAULT '0',

`message` VARCHAR(250) NULL DEFAULT NULL,

`related_dataset` VARCHAR(250) NULL DEFAULT '0',

`when` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

)

COLLATE='utf32_general_ci'

ENGINE=InnoDB

AUTO_INCREMENT=113549

;

[Alexandros G.]

Moreover, the id field seems to be defined in the same way in both "Datasets" and "Logs" classes.

That's drives me crazy.

It is like the auto increment doesn't work for the second class.

[Alexandros G.]

And these are the complete servlet functions that are used to add records into these 2 tables:

protected void log2db(HttpServletRequest request, String action, String message) {

        

        try {

            if(this.conn == null ){

                Class.forName("com.mysql.jdbc.Driver").newInstance();

                this.conn = DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPwd);

            } 

            HttpSession session = request.getSession(false);

            String user_email = (String) session.getAttribute("user_email");

            DSLContext database = DSL.using(this.conn, SQLDialect.MYSQL);  

            LogsRecord logItem = database.newRecord(Tables.LOGS);

            logItem.setUserEmail(user_email);

            logItem.setAction(action);

            logItem.setMessage(message);

            logItem.store();  

        } catch (Exception ex){

            logText(ex.getMessage());

        }

                

    }

    

    protected void dataset2db(HttpServletRequest request, String datasetName){

        try {

            if(this.conn == null ){

                Class.forName("com.mysql.jdbc.Driver").newInstance();

                this.conn = DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPwd);

            } 

            HttpSession session = request.getSession(false);

            String user_email = (String) session.getAttribute("user_email");

            DSLContext database = DSL.using(this.conn, SQLDialect.MYSQL);  

            DatasetsRecord record = database.newRecord(Tables.DATASETS);

            record.setUserEmail(user_email);

            record.setName(datasetName);

            record.store();  

        } catch (Exception ex){

            log2db(request,"info","Adding dataset record in DB failed. Reason: "+ex.getMessage());

        }

    }

[Lukas Eder]

Does it work when you run the insert statement directly in mysql - or MySQL Workbench?

[Alexandros G.]

Hmmm...

Yes, but a similar message (a warning) about the missing id field was displayed.

I removed the "unsigned" property from the id field and now it works!

I don't know if this a quirk of MySQL and if it should be taken into account by jOOQ but nevertheless it is a very strange behaviour.

Dear Lukas, thanks for your last question that led me to the solution.

Alexandros

[Lukas Eder]

Interesting, this seems to be a bug somewhere in MySQL or in the connector. What versions (database / connector) are you using?

--

[Alexandros G.]

I am using HeidiSQL client (version 9.1.0.4867 , compiled on 2014-11-11)

The MySQL version that the client reports (since I don't have ssh access to the database server) is  5.6.19-0ubuntu0.14.04.1

[Lukas Eder]

OK, interesting. Thanks for the feedback. I suspect that there might be a bug in that client, then. We haven't integration tested the HeidiSQL client yet...

2015-06-05 15:00 GMT+02:00 Alexandros G. <spama...@gmail.com>:

I am using HeidiSQL client (version 9.1.0.4867 , compiled on 2014-11-11)

The MySQL version that the client reports (since I don't have ssh access to the database server) is  5.6.19-0ubuntu0.14.04.1

--