problems inserting segments into postgresql database

Pablo Nebrera

unread,

Jun 7, 2013, 2:10:29 PM6/7/13

to druid-de...@googlegroups.com

Helllo

I have seen this error on realtime when it insert data into segment.

[2013-06-07 18:06:00,819] ERROR DbSegmentPublisher:98 - Exception inserting into DB

org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "end"

Position: 65 [statement:"INSERT INTO prod_segments (id, dataSource, created_date, start, end, partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", located:"INSERT INTO prod_segments (id, dataSource, created_date, start, end, partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", rewritten:"INSERT INTO prod_segments (id, dataSource, created_date, start, end, partitioned, version, used, payload) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", arguments:{ positional:{}, named:{created_date:'2013-06-07T18:06:00.818Z',id:'rb_monitor_2013-06-07T17:54:00.000Z_2013-06-07T17:55:00.000Z_2013-06-07T17:54:00.000Z_-1',dataSource:'rb_monitor',partitioned:-1,start:'2013-06-07T17:54:00.000Z',payload:'{"dataSource":"rb_monitor","interval":"2013-06-07T17:54:00.000Z/2013-06-07T17:55:00.000Z","version":"2013-06-07T17:54:00.000Z","loadSpec":{"type":"s3_zip","bucket":"redborder","key":"rb/rb_monitor/2013-06-07T17:54:00.000Z_2013-06-07T17:55:00.000Z/2013-06-07T17:54:00.000Z/-1/index.zip"},"dimensions":"monitor,type,value","metrics":"events,sum_value","shardSpec":{"type":"linear","partitionNum":-1},"binaryVersion":9,"size":2038,"identifier":"rb_monitor_2013-06-07T17:54:00.000Z_2013-06-07T17:55:00.000Z_2013-06-07T17:54:00.000Z_-1"}',used:true,end:'2013-06-07T17:55:00.000Z',version:'2013-06-07T17:54:00.000Z'}, finder:[]}]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:262)

at com.metamx.druid.realtime.DbSegmentPublisher.publishSegment(DbSegmentPublisher.java:57)

at com.metamx.druid.realtime.plumber.RealtimePlumberSchool$1$7$1.doRun(RealtimePlumberSchool.java:574)

at com.metamx.druid.guava.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:42)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

at java.lang.Thread.run(Thread.java:722)

Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "end"

Position: 65 [statement:"INSERT INTO prod_segments (id, dataSource, created_date, start, end, partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", located:"INSERT INTO prod_segments (id, dataSource, created_date, start, end, partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", rewritten:"INSERT INTO prod_segments (id, dataSource, created_date, start, end, partitioned, version, used, payload) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", arguments:{ positional:{}, named:{created_date:'2013-06-07T18:06:00.818Z',id:'rb_monitor_2013-06-07T17:54:00.000Z_2013-06-07T17:55:00.000Z_2013-06-07T17:54:00.000Z_-1',dataSource:'rb_monitor',partitioned:-1,start:'2013-06-07T17:54:00.000Z',payload:'{"dataSource":"rb_monitor","interval":"2013-06-07T17:54:00.000Z/2013-06-07T17:55:00.000Z","version":"2013-06-07T17:54:00.000Z","loadSpec":{"type":"s3_zip","bucket":"redborder","key":"rb/rb_monitor/2013-06-07T17:54:00.000Z_2013-06-07T17:55:00.000Z/2013-06-07T17:54:00.000Z/-1/index.zip"},"dimensions":"monitor,type,value","metrics":"events,sum_value","shardSpec":{"type":"linear","partitionNum":-1},"binaryVersion":9,"size":2038,"identifier":"rb_monitor_2013-06-07T17:54:00.000Z_2013-06-07T17:55:00.000Z_2013-06-07T17:54:00.000Z_-1"}',used:true,end:'2013-06-07T17:55:00.000Z',version:'2013-06-07T17:54:00.000Z'}, finder:[]}]

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1306)

at org.skife.jdbi.v2.Update.execute(Update.java:57)

at com.metamx.druid.realtime.DbSegmentPublisher$2.withHandle(DbSegmentPublisher.java:63)

at com.metamx.druid.realtime.DbSegmentPublisher$2.withHandle(DbSegmentPublisher.java:59)

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259)

... 6 more

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "end"

Position: 65

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1300)

... 10 more

Thanks

Pablo

Eric Tschetter

unread,

Jun 7, 2013, 2:14:23 PM6/7/13

to druid-de...@googlegroups.com

Hrm, that could be because "end" is a reserved word on postgres. Perhaps it you single or double quote the name of the column it'll work (and be compatible with MySQL as well)?

DbSegmentPublisher is the place that has the sql.

--Eric

Pablo

--
You received this message because you are subscribed to the Google Groups "Druid Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-developm...@googlegroups.com.
To post to this group, send email to druid-de...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-development/7359c7bf-4197-4af0-93cd-77a9ccd6c8fb%40googlegroups.com?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Pablo Nebrera

unread,

Jun 7, 2013, 3:16:58 PM6/7/13

to druid-de...@googlegroups.com

Added and I get new exception:

[2013-06-07 19:15:00,441] INFO S3DataSegmentPusher:70 - Deleting zipped index File[/tmp/druid1271564176898721993index.zip]

[2013-06-07 19:15:00,441] INFO S3DataSegmentPusher:70 - Deleting descriptor file[/tmp/druid2093764762439022784descriptor.json]

[2013-06-07 19:15:00,447] ERROR DbSegmentPublisher:98 - Exception inserting into DB

org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: column "used" is of type smallint but expression is of type boolean

Hint: You will need to rewrite or cast the expression.

Position: 145 [statement:"INSERT INTO prod_segments (id, dataSource, created_date, start, "end", partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", located:"INSERT INTO prod_segments (id, dataSource, created_date, start, "end", partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", rewritten:"INSERT INTO prod_segments (id, dataSource, created_date, start, "end", partitioned, version, used, payload) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", arguments:{ positional:{}, named:{created_date:'2013-06-07T19:15:00.444Z',id:'rb_monitor_2013-06-07T19:12:00.000Z_2013-06-07T19:13:00.000Z_2013-06-07T19:12:00.000Z_-1',dataSource:'rb_monitor',partitioned:-1,start:'2013-06-07T19:12:00.000Z',payload:'{"dataSource":"rb_monitor","interval":"2013-06-07T19:12:00.000Z/2013-06-07T19:13:00.000Z","version":"2013-06-07T19:12:00.000Z","loadSpec":{"type":"s3_zip","bucket":"redborder","key":"rb/rb_monitor/2013-06-07T19:12:00.000Z_2013-06-07T19:13:00.000Z/2013-06-07T19:12:00.000Z/-1/index.zip"},"dimensions":"monitor,type,value","metrics":"events,sum_value","shardSpec":{"type":"linear","partitionNum":-1},"binaryVersion":9,"size":2415,"identifier":"rb_monitor_2013-06-07T19:12:00.000Z_2013-06-07T19:13:00.000Z_2013-06-07T19:12:00.000Z_-1"}',used:true,end:'2013-06-07T19:13:00.000Z',version:'2013-06-07T19:12:00.000Z'}, finder:[]}]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:262)

at com.metamx.druid.realtime.DbSegmentPublisher.publishSegment(DbSegmentPublisher.java:57)

at com.metamx.druid.realtime.plumber.RealtimePlumberSchool$1$7$1.doRun(RealtimePlumberSchool.java:574)

at com.metamx.druid.guava.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:42)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

at java.lang.Thread.run(Thread.java:722)

Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: column "used" is of type smallint but expression is of type boolean

Hint: You will need to rewrite or cast the expression.

Position: 145 [statement:"INSERT INTO prod_segments (id, dataSource, created_date, start, "end", partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", located:"INSERT INTO prod_segments (id, dataSource, created_date, start, "end", partitioned, version, used, payload) VALUES (:id, :dataSource, :created_date, :start, :end, :partitioned, :version, :used, :payload)", rewritten:"INSERT INTO prod_segments (id, dataSource, created_date, start, "end", partitioned, version, used, payload) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", arguments:{ positional:{}, named:{created_date:'2013-06-07T19:15:00.444Z',id:'rb_monitor_2013-06-07T19:12:00.000Z_2013-06-07T19:13:00.000Z_2013-06-07T19:12:00.000Z_-1',dataSource:'rb_monitor',partitioned:-1,start:'2013-06-07T19:12:00.000Z',payload:'{"dataSource":"rb_monitor","interval":"2013-06-07T19:12:00.000Z/2013-06-07T19:13:00.000Z","version":"2013-06-07T19:12:00.000Z","loadSpec":{"type":"s3_zip","bucket":"redborder","key":"rb/rb_monitor/2013-06-07T19:12:00.000Z_2013-06-07T19:13:00.000Z/2013-06-07T19:12:00.000Z/-1/index.zip"},"dimensions":"monitor,type,value","metrics":"events,sum_value","shardSpec":{"type":"linear","partitionNum":-1},"binaryVersion":9,"size":2415,"identifier":"rb_monitor_2013-06-07T19:12:00.000Z_2013-06-07T19:13:00.000Z_2013-06-07T19:12:00.000Z_-1"}',used:true,end:'2013-06-07T19:13:00.000Z',version:'2013-06-07T19:12:00.000Z'}, finder:[]}]

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1306)

at org.skife.jdbi.v2.Update.execute(Update.java:57)

at com.metamx.druid.realtime.DbSegmentPublisher$2.withHandle(DbSegmentPublisher.java:63)

at com.metamx.druid.realtime.DbSegmentPublisher$2.withHandle(DbSegmentPublisher.java:59)

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259)

... 6 more

Caused by: org.postgresql.util.PSQLException: ERROR: column "used" is of type smallint but expression is of type boolean

Hint: You will need to rewrite or cast the expression.

Position: 145