[SQOOP-3288] Incremental import's upper bound ignores session time zone in Oracle

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.4.7
• Fix Version/s: 1.5.0
• Component/s: connectors/oracle
• Assignee: Daniel Voros
• Reporter: Daniel Voros
• Created: 19/Feb/18 16:15
• Updated: 23/Feb/18 09:52
• Resolved: 23/Feb/18 09:28

Description

At the moment we're using SELECT SYSDATE FROM dual when getting current time from Oracle.

SYSDATE returns the underlying operating system's current time, while CURRENT_TIMESTAMP uses the session time zone. This could lead to problems during incremental imports when Oracle's time zone is different from the OS.

Consider the following scenario when Oracle is configured to +0:00, while the OS is +5:00:

Oracle time	OS time	Event
2:00	7:00	sqoop import --last-value 1:00 ... => imports [1:00, 7:00)
2:30	7:30	update ... set last_updated = current_timestamp ... => set to 2:30 Won't be imported!
3:00	8:00	sqoop import --last-value 7:00 ... => imports [7:00, 8:00)

This way records updated within 5 hours after the last sqoop import won't get imported.

Please note, that the example above assumes, that the user/administrator who's updating the Oracle table will use the current session time of Oracle when setting the "last updated" column of the table.

I think the solution is to use CURRENT_TIMESTAMP instead of SYSDATE. Other connection managers, like MySQL or PostgreSQL are using that as well.

Issue Links

• links to: review request