space character in field is ignored

[makkaba]

in:
 ...
  type: mysql
  query: |-
    SELECT metaID, metaName, metaDesc, createDate FROM A 

out:
...
  type: mysql
  create_table_constraint: primary key (metaID), UNIQUE KEY `unique_index` (`metaName`)
  mode: merge
  options: {useUnicode: true, characterEncoding: UTF-8}
  column_options:
  ...

metaName column is already unique key in source db.
but error is caused by unique key
error message is

Duplicate entry 'somevalue?' for key 'unique_index'

i think the reason is...

metaName
'somevalue'
'somevalue '

(with space at the end)

i think embulk input plugin ignore a space.
is there any solution?

[hiroyuki-sato]

Hello, @makkaba

In my environment, embulk-input-mysql keep space as is.
It seems that the problem is not embulk-input-jdbc issue.

Could you tell me the reproduce steps and sample data?

• os: macOS 10.14.15
• Java: 1.8.0_192
• embulk: 0.9.17
• embulk-input-mysql: 0.10.0
• MySQL: 5.7.19

create table space_test (id int not null, name varchar(255));
insert into space_test values(1,'apple '), (2,'peach '), (3,'banana ');

in:
  type: mysql
  user: user
  password: xxx
  database: embulk_test
  query: select name,id from space_test
  host: localhost
out:
  type: stdout

embulk run test.yml
...
apple ,1
peach ,2
banana ,3

[hiroyuki-sato]

Hello, @makkaba

It seems that your issue is embulk-output-mysql specific.

• embulk: 0.9.17
• embulk-output-jdbc: 0.8.5
• MySQL: 5.7.19

mysql-connector-java-5.1.47.jar doesn't work too. (duplicate error)
mysql-connector-java-8.0.16.jar doesn't work correctly.

create table space_test (id int not null, name varchar(255));
insert into space_test values(1,'apple '), (2,'apple');

in:
  type: mysql
  user: user
  password: xxx
  database: embulk_test
  query: select name,id from space_test
  host: localhost
out:
  type: mysql
  user: user
  password: xxx
  #driver_path: /tmp/mysql-connector-java-8.0.16.jar
  database: embulk_test
  table: space_test2
  host: localhost
  mode: merge
  create_table_constraint: "primary key (id), UNIQUE KEY `unique_index` (`name`)"
  column_options:
    name:
      type: varchar(255)

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: java.sql.BatchUpdateException: Duplicate entry 'apple' for key 'unique_index'
	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:178)
	at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:292)
	at org.embulk.EmbulkRunner.run(EmbulkRunner.java:156)
	at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:433)
	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.BatchUpdateException: Duplicate entry 'apple' for key 'unique_index'
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput.finish(AbstractJdbcOutputPlugin.java:1169)
	at org.embulk.exec.LocalExecutorPlugin$ScatterTransactionalPageOutput.finish(LocalExecutorPlugin.java:445)
	at org.embulk.spi.PageBuilder.finish(PageBuilder.java:228)
	at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:510)
	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.BatchUpdateException: Duplicate entry 'apple' for key 'unique_index'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1162)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1587)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1253)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
	at org.embulk.output.jdbc.StandardBatchInsert.flush(StandardBatchInsert.java:82)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput$2.run(AbstractJdbcOutputPlugin.java:1140)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1284)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1272)
	at org.embulk.spi.util.RetryExecutor.run(RetryExecutor.java:81)
	at org.embulk.spi.util.RetryExecutor.runInterruptible(RetryExecutor.java:62)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1257)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1249)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput.flush(AbstractJdbcOutputPlugin.java:1130)
	at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput.finish(AbstractJdbcOutputPlugin.java:1160)
	... 11 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'apple' for key 'unique_index'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1548)
	... 23 more

Error: java.lang.RuntimeException: java.sql.BatchUpdateException: Duplicate entry 'apple' for key 'unique_index'

[hiroyuki-sato]

Created embulk/embulk-output-jdbc#256

[hiroyuki-sato]

Hello, @makkaba

Could you check this thread?
This issue seems MySQL spec.
embulk/embulk-output-jdbc#256 (comment)