[jira] [Updated] (SQOOP-3320) Commit behaviour leads to errors on read-only databases

["Tijmen Ramakers (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SQOOP-3320?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tijmen Ramakers updated SQOOP-3320:
-----------------------------------
    Summary: Commit behaviour leads to errors on read-only databases  (was: Commit behaviour leeds to errors on read-only databases)

> Commit behaviour leads to errors on read-only databases
> -------------------------------------------------------
>
>                 Key: SQOOP-3320
>                 URL: https://issues.apache.org/jira/browse/SQOOP-3320
>             Project: Sqoop
>          Issue Type: Bug
>          Components: connectors/generic
>    Affects Versions: 1.4.7
>            Reporter: Tijmen Ramakers
>            Priority: Major
>
> The current Sqoop behaviour is to do a commit after having retrieved the results of a query. This leads to errors when the database (or the connection) is in read-only mode.
> Example stack traces:
> {code:java}
> ERROR manager.SqlManager: SQLException closing ResultSet: org.netezza.error.NzSQLException: ERROR:  Database 'XXXPRD' is available for read-only
>         at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)
>         at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)
>         at org.netezza.sql.NzConnection.execute(NzConnection.java:2819)
>         at org.netezza.sql.NzConnection.execute(NzConnection.java:2791)
>         at org.netezza.sql.NzConnection.commit(NzConnection.java:1161)
>         at org.apache.sqoop.manager.SqlManager.getColumnNamesForRawQuery(SqlManager.java:182)
>         at org.apache.sqoop.manager.SqlManager.getColumnNamesForQuery(SqlManager.java:142)
> {code}
> {code:java}
> tool.EvalSqlTool: SQL exception executing statement: org.netezza.error.NzSQLException: ERROR:  Database 'XXXPRD' is available for read-only         at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)         at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)         at org.netezza.sql.NzConnection.execute(NzConnection.java:2819)         at org.netezza.sql.NzConnection.execute(NzConnection.java:2791)         at org.netezza.sql.NzConnection.commit(NzConnection.java:1161)         at org.apache.sqoop.tool.EvalSqlTool.run(EvalSqlTool.java:78)         at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>  
> {code}
>  
> When using _sqoop eval_, it is not a major issue since it happens after the data is already fetched and displayed. When using _sqoop import_ however, the spawned mapreduce jobs all fail, so this is blocking the whole import.
> This commit behaviour exists in several methods, at least the following:
> org.apache.sqoop.tool.EvalSqlTool::run (line 78)
> org.apache.sqoop.mapreduce.db.DBRecordReader::close (line 169)
> org.apache.sqoop.manager.SqlManager::getColumnNamesForRawQuery (line 182)
> org.apache.sqoop.manager.SqlManager::getColumnNamesForProcedure (line 226)
>  
> When a connection is in autocommit mode, then a manual commit is not needed anyway. When a connection is in read-only mode, then it should still be possible to do a rollback in order to close an outstanding transaction.
> I propose to change the mentioned method lines ("connection.commit();" or equivalent), to:
> {code:java}
> if (!connection.getAutoCommit()) {
>   if (connection.isReadOnly())
>     connection.rollback();
>   else
>     connection.commit();
> }
> {code}
> (or equivalent)
>  
> (Overall, I don't know why a commit is done in the above mentioned methods, as data should have only been read, if the idea is to just close the transaction, why not do a rollback instead just as a safety measure?)



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)