[ https://issues.apache.org/jira/browse/SQOOP-3320?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tijmen Ramakers updated SQOOP-3320: ----------------------------------- Description: 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::getColumnInfoForRawQuery 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?) was: 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?) > Commit behaviour leads to exceptions on read-only database > ---------------------------------------------------------- > > 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 > Labels: newbie > > 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::getColumnInfoForRawQuery > 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)