Error: java.lang.RuntimeException: java.sql.SQLException: Before start of result set

[hyunjipark]

Hello,

I am having an issue using embulk-input-mysql for migrating big table (w/ about 6295805825 rows of data)
Migration stops after fetching exactly 2147483136 rows of data, and outputs the following error:
Error: java.lang.RuntimeException: java.sql.SQLException: Before start of result set

The full error is written below.
I have tried migrating several times and always end up failing after migrating 2147483136 rows of data.

Would there be any ideas regarding the cause of such issue?

ENV

openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
source mysql version: Ver 8.0.19-10 for Linux on x86_64 (Source distribution)

Embulk v0.9.23

USECASE

mysql -> mongodb
input plugin: mysql
output plugin: mongodb_nest

FULLERROR

`org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: java.sql.SQLException: Before start of result set
at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
at org.embulk.spi.Exec.doWith(Exec.java:22)
at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:242)
at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:291)
at org.embulk.EmbulkRunner.run(EmbulkRunner.java:155)
at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
at org.embulk.cli.Main.main(Main.java:64)
Caused by: java.lang.RuntimeException: java.sql.SQLException: Before start of result set
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:534)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.runInputTask(LocalExecutorPlugin.java:269)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.access$100(LocalExecutorPlugin.java:194)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:233)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:230)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Before start of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790)
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2654)
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2650)
at org.embulk.input.jdbc.getter.LongColumnGetter.fetch(LongColumnGetter.java:25)
at org.embulk.input.jdbc.getter.AbstractColumnGetter.getAndSet(AbstractColumnGetter.java:32)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.fetch(AbstractJdbcInputPlugin.java:603)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:504)
... 8 more

Error: java.lang.RuntimeException: java.sql.SQLException: Before start of result set`

[hiroyuki-sato]

Hello, @hyunjipark. You probably need to use string instead of long
Could you try column_options? See this example. https://gist.github.com/hiroyuki-sato/53f64c232a4698ab5100

[hyunjipark]

Hello @hiroyuki-sato ,
The error message given in the example seems to be different from our issue, where our error indicates: java.sql.SQLException: Before start of result set.

The error is not caused when inserting data to target server, but is caused while fetching data from source mysql.
Would there be any additional ideas causing this issue in the stage of fetching rows from source mysql?

Thank you

[hiroyuki-sato]

Hello, @hyunjipark
It seems that your setting failed in before_select part. Could you provide config and data for reproducing this issue in my environment?

[hyunjipark]

@hiroyuki-sato
Hello, config/data is as following:

config.yml

in:
  type: mysql
  host: 
  port: 3306
  user: 
  socket_timeout: 0
  password: 
  database: dis
  table: matrix_status1m
  options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Seoul}
out:
  type: mongodb_nest
  host: 
  database: dis_embulk
  user: 
  password: 
  collection: embulk_matrix_status1m
  key: [db_server_id,v_name_id,ctime]

Data

mysql> use dis
mysql> show table status;
+----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------------------------------------------+---------+
| Name                       | Engine | Version | Row_format | Rows       | Avg_row_length | Data_length  | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options                                     | Comment |
+----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------------------------------------------+---------+
| matrix_status1m            | InnoDB |      10 | Compressed | 6295805825 |             29 | 187936145408 |               0 |            0 | 217579520 |           NULL | 2022-01-22 03:02:23 | NULL        | NULL       | latin1_bin         |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=8 partitioned |         |                   |         |
+----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------------------------------------------+---------+

mysql> select * from matrix_status1m limit 3;
+--------------+-----------+---------------------+---------------+
| db_server_id | v_name_id | ctime               | v_value       |
+--------------+-----------+---------------------+---------------+
|         8039 |         1 | 2020-08-24 19:04:00 |    1598263440 |
|         8039 |         2 | 2020-08-24 19:04:00 |       5411402 |
|         8039 |         3 | 2020-08-24 19:04:00 | 2994029093880 |
+--------------+-----------+---------------------+---------------+
3 rows in set (0.05 sec)

** All rows in 'matrix_status1m' table is similar to the rows above. I couldn't post all data as there are about 6000000000 rows in the table.

Migration stops after fetching exactly 2147483136 rows of data everytime I try.
Please let me know if you need additional information.
Thank you

[hiroyuki-sato]

Hello, @hyunjipark

Could you tell me the following?

show create table matrix_status1m
\G

select max(v_value) from matrix_status1m;

[hyunjipark]

Hello, @hiroyuki-sato
The outputs for each command is as below:

mysql> show create table matrix_status1m\G
*************************** 1. row ***************************
       Table: matrix_status1m
Create Table: CREATE TABLE `matrix_status1m` (
  `db_server_id` mediumint unsigned NOT NULL,
  `v_name_id` smallint unsigned NOT NULL,
  `ctime` datetime NOT NULL,
  `v_value` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`db_server_id`,`v_name_id`,`ctime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
/*!50500 PARTITION BY RANGE  COLUMNS(ctime)
(PARTITION p20200701 VALUES LESS THAN ('20200702') ENGINE = InnoDB,
 PARTITION p20200702 VALUES LESS THAN ('20200703') ENGINE = InnoDB,
 PARTITION p20200703 VALUES LESS THAN ('20200704') ENGINE = InnoDB,
 PARTITION p20200704 VALUES LESS THAN ('20200705') ENGINE = InnoDB,
 PARTITION p20200705 VALUES LESS THAN ('20200706') ENGINE = InnoDB,
...

mysql> select max(v_value) from matrix_status1m;
+--------------------+
| max(v_value)       |
+--------------------+
| 159603512246607360 |
+--------------------+

Thank you,
Jenna

[hiroyuki-sato]

Hello, @hyunjipark

You probably hit Integer.MAX_VALUE
maximum integer 2147483647

I suppose you need to fetch data from each partition.
(You can't get 2147483646 rows at once. You need to split a query).

[hyunjipark]

Hello @hiroyuki-sato ,

I see. Is this the limitation of cursor.fetch() function?
can you tell me which part of the source code might cause this error(by hitting Interger.MAX_VALUE)?

[hiroyuki-sato]

Hello, @hyunjipark

Not 100% sure, But at least java.sql.SQLException: Before start of result set exception raises in Connector/J.

https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/RowDataCursor.java#L52
https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/ResultSetImpl.java#L794