Got "ID = NULL" when did "record.store()"

[Volodymyr Mudryk]

Hi Lukas.

I have the following table:

CREATE TABLE `partitioned_version` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(2048) DEFAULT NULL,
  `account_id` bigint(20) DEFAULT NULL,
  `data_for_date` date NOT NULL,
  `component_version` varchar(100) DEFAULT NULL,
  `status` smallint(6) DEFAULT NULL,
  `fb_job_id` bigint(20) DEFAULT NULL,
  `total_data_count` bigint(20) DEFAULT NULL,
  `processing_time` bigint(20) DEFAULT NULL,
  `report_generation_time` bigint(20) DEFAULT NULL,
  `processing_rate` float DEFAULT NULL,
  `api_version` tinyint(4) DEFAULT NULL,
  `create_component_version` varchar(100) DEFAULT NULL,
  `report_type` tinyint(4) NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`id`,`create_date`),
  KEY `account_id_date_partitioned_version_idx` (`account_id`,`data_for_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(create_date))
(PARTITION PART_2016_AUG VALUES LESS THAN (736573) ENGINE = InnoDB,
 PARTITION PART_2016_SEP VALUES LESS THAN (736603) ENGINE = InnoDB,
 PARTITION PART_2016_OCT VALUES LESS THAN (736634) ENGINE = InnoDB,
 PARTITION PART_2016_NOV VALUES LESS THAN (736664) ENGINE = InnoDB,
 PARTITION PART_2016_DEC VALUES LESS THAN (736695) ENGINE = InnoDB,
 PARTITION PART_2017_JAN VALUES LESS THAN (736726) ENGINE = InnoDB,
 PARTITION PART_2017_FEB VALUES LESS THAN (736754) ENGINE = InnoDB,
 PARTITION PART_2017_MAR VALUES LESS THAN (736785) ENGINE = InnoDB,
 PARTITION PART_2017_APR VALUES LESS THAN (736815) ENGINE = InnoDB,
 PARTITION PART_2017_MAY VALUES LESS THAN (736846) ENGINE = InnoDB,
 PARTITION PART_2017_JUN VALUES LESS THAN (736876) ENGINE = InnoDB,
 PARTITION PART_2017_JUL VALUES LESS THAN (736907) ENGINE = InnoDB,
 PARTITION PART_2017_AUG VALUES LESS THAN (736938) ENGINE = InnoDB,
 PARTITION PART_2017_SEP VALUES LESS THAN (736968) ENGINE = InnoDB,
 PARTITION PART_2017_OCT VALUES LESS THAN (736999) ENGINE = InnoDB,
 PARTITION PART_2017_NOV VALUES LESS THAN (737029) ENGINE = InnoDB,
 PARTITION PART_2017_DEC VALUES LESS THAN (737060) ENGINE = InnoDB,
 PARTITION PART_2018_JAN VALUES LESS THAN (737091) ENGINE = InnoDB,
 PARTITION PART_2018_FEB VALUES LESS THAN (737119) ENGINE = InnoDB,
 PARTITION PART_2018_MAR VALUES LESS THAN (737150) ENGINE = InnoDB,
 PARTITION PART_2018_APR VALUES LESS THAN (737180) ENGINE = InnoDB,
 PARTITION PART_2018_MAY VALUES LESS THAN (737211) ENGINE = InnoDB,
 PARTITION PART_2018_JUN VALUES LESS THAN (737241) ENGINE = InnoDB,
 PARTITION PART_2018_JUL VALUES LESS THAN (737272) ENGINE = InnoDB) */

As you can see in this table I have two PRIMARY keys: "id" and "create_date"

But when I try insert new row (record.store()) then I got "ID = NULL"

Why?

Because here in method

org.jooq.impl.AbstractDMLQuery#selectReturning

has line

if (returning.size() == 1 && new Fields<Record>(returning).field(field) != null) {

but in my case "returning.size() == 2" because I have two PRIMARY KEYS

Also when I try do the simple insert via MySQL query:

"insert into partitioned_version (create_date) values (NOW())"

I have possibility to insert new data AND in "partitioned_version" I see the new row.

So, why I got "ID = NULL" when I did "record.store()"?

Thanks,

Vova

[Lukas Eder]

Hi Volodymyr,

Thanks a lot for your message. Hmm, interesting. I just created a simplified test on a table like this:

CREATE TABLE t_5505 (

  id1 INT NOT NULL AUTO_INCREMENT,

  id2 INT NOT NULL DEFAULT 2,

  val TEXT NOT NULL,

  

  CONSTRAINT pk_t_5505 PRIMARY KEY (id1, id2)

)

/

Running this:

        T_5505Record record = create().newRecord(T_5505);

        record.setVal("abc");

        record.store();

        assertEquals(1, (int) record.getId1());

        assertEquals(2, (int) record.getId2());

And the test passed. Does this perhaps have anything to do with your partitioning clause? What versions of MySQL / MySQL Connector / jOOQ are you using?

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

For the record, I have created an issue for this:

https://github.com/jOOQ/jOOQ/issues/5505

2016-08-21 16:06 GMT+01:00 Lukas Eder <lukas...@gmail.com>:

Hi Volodymyr,

Thanks a lot for your message. Hmm, interesting. I just created a simplified test on a table like this:

CREATE TABLE t_5505 (

  id1 INT NOT NULL AUTO_INCREMENT,

  id2 INT NOT NULL DEFAULT 2,

  val TEXT NOT NULL,

  

  CONSTRAINT pk_t_5505 PRIMARY KEY (id1, id2)

)

/

Running this:

        T_5505Record record = create().newRecord(T_5505);

        record.setVal("abc");

        record.store();

        assertEquals(1, (int) record.getId1());

        assertEquals(2, (int) record.getId2());

And the test passed. Does this perhaps have anything to do with your partitioning clause? What versions of MySQL / MySQL Connector / jOOQ are you using?

Cheers,

Lukas

[Volodymyr Mudryk]

Hi Lukas.

Thanks for the response!

I created "t_5505" table and run your test. Test is not passed - NPE (because ID was not generated and we're trying cast NULL to int:

assertEquals(1, (int) record.getId1());

).

Record before "store" equal after and equal:

+------+------+----+

|   id1|   id2|val |

+------+------+----+

|{null}|{null}|abc |

+------+------+----+

I'm using versions:

JOOQ - 3.7.2

MySQL Connector Java - 5.1.37

MySQL - 5.6.16

Thanks,

Vova

On Sunday, August 21, 2016 at 6:07:30 PM UTC+3, Lukas Eder wrote:

For the record, I have created an issue for this:

https://github.com/jOOQ/jOOQ/issues/5505

2016-08-21 16:06 GMT+01:00 Lukas Eder <lukas...@gmail.com>:

Hi Volodymyr,

Thanks a lot for your message. Hmm, interesting. I just created a simplified test on a table like this:

CREATE TABLE t_5505 (

  id1 INT NOT NULL AUTO_INCREMENT,

  id2 INT NOT NULL DEFAULT 2,

  val TEXT NOT NULL,

  

  CONSTRAINT pk_t_5505 PRIMARY KEY (id1, id2)

)

/

Running this:

        T_5505Record record = create().newRecord(T_5505);

        record.setVal("abc");

        record.store();

        assertEquals(1, (int) record.getId1());

        assertEquals(2, (int) record.getId2());

And the test passed. Does this perhaps have anything to do with your partitioning clause? What versions of MySQL / MySQL Connector / jOOQ are you using?

Cheers,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2016-08-22 7:10 GMT+01:00 Volodymyr Mudryk <volodymyr....@gmail.com>:

I'm using versions:

JOOQ - 3.7.2

MySQL Connector Java - 5.1.37

MySQL - 5.6.16

Hmm, have you tried upgrading to the latest jOOQ version 3.8.4 (or 3.7.4 on your minor release)?

[Volodymyr Mudryk]

3.7.4: the same result

3.8.4: failing on "store"

org.jooq.exception.NoDataFoundException: Exactly one row expected for refresh. Record does not exist in database.

at org.jooq.impl.UpdatableRecordImpl.refresh(UpdatableRecordImpl.java:342)

at org.jooq.impl.TableRecordImpl.getReturningIfNeeded(TableRecordImpl.java:218)

at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:194)

at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:155)

at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)

at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:151)

at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:195)

at org.jooq.impl.UpdatableRecordImpl.access$000(UpdatableRecordImpl.java:85)

at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:135)

at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:128)

at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:131)

at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:123)

[Volodymyr Mudryk]

Hi Lukas.

Do you need some other info?

[Lukas Eder]

Hi Volodymyr,

I'm sorry, I have been on the road for a SQL training. I didn't get to analysing this yet. Will do, soon

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+unsubscribe@googlegroups.com.