[Monetdb-developers] Possible bug in MonetDB and JDBC driver in sequence management

Cimballi cimballi at cimballi.net
Tue Mar 3 14:17:06 CET 2009


Hi,

I'm a recent user of MonetDB. I'm using it to store data from XML files from
"http://oasissta.caiso.com/".
I parse the XML files and I insert the data into MonetDB using the JDBC
driver (1.9).
I make a batch insert of each xml data element.
The ID column of my table is a sequence I created like this :

CREATE TABLE "oasis_report_data" (
  "oasis_report_data_id" INT NOT NULL,
  "oasis_report_header_id" INT NOT NULL,
  "data_item" VARCHAR(255) NOT NULL,
  "resource_name" VARCHAR(255) NOT NULL,
  "opr_date" DATE NOT NULL,
  "interval_num" INT NOT NULL,
  "value" DOUBLE NOT NULL,
  CONSTRAINT "pk_oasis_report_data" PRIMARY KEY ("oasis_report_data_id"),
  CONSTRAINT "fk_oasis_report_data_oasis_report_header" FOREIGN KEY
("oasis_report_header_id")
  REFERENCES "oasis_report_header" ("oasis_report_header_id")
);

CREATE SEQUENCE seq_oasis_report_data_id AS INT;

And I'm using iBatis to insert the data like that :

<insert id="insertOasisReportData" parameterClass="oasisReportData">
    <selectKey keyProperty="id" resultClass="int" type="pre">
      SELECT NEXT VALUE FOR seq_oasis_report_data_id
    </selectKey>
    INSERT INTO oasis_report_data (oasis_report_data_id,
oasis_report_header_id,
    data_item, interval_num, opr_date, resource_name, value)
    VALUES (#id#, #header.id#, #dataItem#, #intervalNum#, #oprDate#,
    #resourceName#, #value#)
</insert>

Here is the bug I found :

When the file is small (up to 2 Mo, around 15 000 items), no problem.
If the file is bigger (20 Mo, around 100 000 items), then at a moment the
select next value return null.
Here is the java.sql log :

2009-03-02 21:14:30,324 | DEBUG | java.sql.Connection                  |
debug      | {conn-170420} Connection
2009-03-02 21:14:30,324 | DEBUG | java.sql.Connection                  |
debug      | {conn-170420} Preparing Statement:        SELECT NEXT VALUE FOR
seq_oasis_report_data_id
2009-03-02 21:14:30,334 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170421} Executing Statement:        SELECT NEXT VALUE FOR
seq_oasis_report_data_id
2009-03-02 21:14:30,334 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170421} Parameters: []
2009-03-02 21:14:30,334 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170421} Types: []
2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet                    |
debug      | {rset-170422} ResultSet
2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet                    |
debug      | {rset-170422} Header: [next_value_for_sys]
2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet                    |
debug      | {rset-170422} Result: [16901]
2009-03-02 21:14:30,344 | DEBUG | java.sql.Connection                  |
debug      | {conn-170420} Preparing Statement:           INSERT INTO
oasis_report_data (oasis_report_data_id, oasis_report_header_id,
data_item, interval_num, opr_date, resource_name, value)     VALUES (?, ?,
?, ?, ?,     ?, ?)
2009-03-02 21:14:30,354 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170423} Executing Statement:           INSERT INTO
oasis_report_data (oasis_report_data_id, oasis_report_header_id,
data_item, interval_num, opr_date, resource_name, value)     VALUES (?, ?,
?, ?, ?,     ?, ?)
2009-03-02 21:14:30,354 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170423} Parameters: [16901, 705, LMP_PRC, 5, 2009-01-26
00:00:00.0, AVOCADO_6_N002, 31.80941]
2009-03-02 21:14:30,354 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170423} Types: [java.lang.Integer, java.lang.Integer,
java.lang.String, java.lang.Integer, java.sql.Timestamp, java.lang.String,
java.lang.Double]
2009-03-02 21:14:30,364 | DEBUG | java.sql.Connection                  |
debug      | {conn-170424} Connection
2009-03-02 21:14:30,364 | DEBUG | java.sql.Connection                  |
debug      | {conn-170424} Preparing Statement:        SELECT NEXT VALUE FOR
seq_oasis_report_data_id
2009-03-02 21:14:30,364 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170425} Executing Statement:        SELECT NEXT VALUE FOR
seq_oasis_report_data_id
2009-03-02 21:14:30,364 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170425} Parameters: []
2009-03-02 21:14:30,364 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170425} Types: []
2009-03-02 21:14:34,500 | DEBUG | java.sql.Connection                  |
debug      | {conn-170424} Preparing Statement:           INSERT INTO
oasis_report_data (oasis_report_data_id, oasis_report_header_id,
data_item, interval_num, opr_date, resource_name, value)     VALUES (?, ?,
?, ?, ?,     ?, ?)
2009-03-02 21:14:34,510 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170426} Executing Statement:           INSERT INTO
oasis_report_data (oasis_report_data_id, oasis_report_header_id,
data_item, interval_num, opr_date, resource_name, value)     VALUES (?, ?,
?, ?, ?,     ?, ?)
2009-03-02 21:14:34,510 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170426} Parameters: [null, 705, LMP_PRC, 6, 2009-01-26
00:00:00.0, AVOCADO_6_N002, 35.59336]
2009-03-02 21:14:34,510 | DEBUG | java.sql.PreparedStatement      |
debug      | {pstm-170426} Types: [null, java.lang.Integer,
java.lang.String, java.lang.Integer, java.sql.Timestamp, java.lang.String,
java.lang.Double]

You can see that in the second case there are two lines missing :

2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet                    |
debug      | {rset-170422} Header: [next_value_for_sys]
2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet                    |
debug      | {rset-170422} Result: [16901]

And what strange is that each time it appears at a different moment (I mean
on a different set of data, using the same file). Maybe on nig stress the
sequence generator of MonetDB bugs or maybe the JDBC driver.

HTH,

Cimballi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20090303/87714d22/attachment.html>


More information about the developers-list mailing list