search

keedio / flume-ng-sql-source

Flume Source to import data from SQL Databases
Apache License 2.0
264 stars 164 forks source link

Oracle Database Source -Error with SQL Status File #7

Closed Deepak-Vohra closed 9 years ago

Deepak-Vohra commented 9 years ago

The same settings do not generate an error with MySQL Database but with Oracle Database the following settings in flume.conf generate an error. agent1.sources.sql-source.status.file.path = /var/lib/flume agent1.sources.sql-source.status.file.name = sql-source.status

The following error is generated: 15/01/22 11:35:53 ERROR source.SQLSourceUtils: Error reading incremental value from status file!!! java.io.FileNotFoundException: /var/lib/flume/sql-source.status (No such file or directory) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.(FileInputStream.java:146) at java.io.FileReader.(FileReader.java:72) at org.apache.flume.source.SQLSourceUtils.getStatusFileIncrement(SQLSourceUtils.java:95) at org.apache.flume.source.SQLSource.process(SQLSource.java:96) at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:139) at java.lang.Thread.run(Thread.java:745) 15/01/22 11:35:53 INFO source.SQLSource: Query: SELECT * FROM WLSLOG WHERE id>-1 ORDER BY id; 15/01/22 11:35:53 ERROR source.SQLSource: SQL exception, check if query for source sql-source is correctly constructed java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
at org.apache.flume.source.SqlDBEngine.runQuery(SqlDBEngine.java:66)
at org.apache.flume.source.SQLSource.process(SQLSource.java:103)
at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:139)
at java.lang.Thread.run(Thread.java:745)

If the sql-source.status file is created before running the Flume agent the following error is generated. The sql-source.status file is created with vi sql-source.status followed by :wq or touch sql-source.status.

15/01/22 11:42:09 WARN source.SQLSourceUtils: /var/lib/flume/sql-source.status corrupt!!! 15/01/22 11:42:09 INFO source.SQLSource: Query: SELECT * FROM WLSLOG WHERE id>-1 ORDER BY id; 15/01/22 11:42:09 ERROR source.SQLSource: SQL exception, check if query for source sql-source is correctly constructed java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
at org.apache.flume.source.SqlDBEngine.runQuery(SqlDBEngine.java:66)
at org.apache.flume.source.SQLSource.process(SQLSource.java:103)
at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:139)
at java.lang.Thread.run(Thread.java:745)
Deepak-Vohra commented 9 years ago

With Oracle Database as Sql source to Flume the status file is not getting created as it is with MySQL Database.

Deepak-Vohra commented 9 years ago

The sql-source.status file is jdbc:oracle:thin:@127.0.0.1:1521:ORCL WLSLOG id

flume.conf agent1.sources.sql-source.connection.url = jdbc:oracle:thin:@127.0.0.1:1521:ORCL agent1.sources.sql-source.table = WLSLOG agent1.sources.sql-source.incremental.column.name = id

FileReader reader = new FileReader(file); char[] chars = new char[(int) file.length()]; reader.read(chars); String[] statusInfo = new String(chars).split(" "); if (statusInfo[0].equals(connectionURL) && statusInfo[1].equals(table) && statusInfo[2].equals(incrementalColumnName))

lazaromedina commented 9 years ago

This issue is related with issue #4, we close issue as duplicated and support for oracle db is currently in development. Please continue the comments in issue #4.

Deepak-Vohra commented 9 years ago

Removed /var/lib/flume. As support for Oracle database has been added please reopen the issue. Still getting :(PollableSourceRunner-SQLSource-OrclSource) [INFO - org.apache.flume.source.SQLSource.process(SQLSource.java:100)] Query: SELECT * FROM wlslog WHERE id>-1 ORDER BY id; 2015-09-16 11:07:02,896 (PollableSourceRunner-SQLSource-OrclSource) [ERROR - org.apache.flume.source.SQLSource.process(SQLSource.java:157)] SQL exception, check if query for source OrclSource is correctly constructed java.sql.SQLSyntaxErrorException: ORA-00911: invalid character