[JIRA] Created: (MYO-42) Document nullDateTimeIsZero option and its effects

[Gilles Rayrat (JIRA)]

Document nullDateTimeIsZero option and its effects
--------------------------------------------------

Key: MYO-42
URL: https://forge.continuent.org/jira/browse/MYO-42
Project: Myosotis
Type: Task

Components: MySQL protocol
Reporter: Gilles Rayrat
Assigned to: Gilles Rayrat
Fix For: 0.6.1


Option nullDateTimeIsZero option allows user to have zero dates, times and timestamps instead of nulls
This option should be fully explained at least in a README file

[Gilles Rayrat (JIRA)]

[ https://forge.continuent.org/jira/browse/MYO-42?page=comments#action_14246 ]

Gilles Rayrat commented on MYO-42:
----------------------------------

There is a limitation in the proxy that comes from JDBC, where zero datetime, date or time are invalid values.
MySQL java driver uses a special hack to work that issue around, which is zeroDateTimeBehavior=<convertToNull|round|exception>
The problem is that when reading a null date/time, myosotis cannot know if it was originally a zero or a real null.
So the same type of workaround as mysql connector has been implemented, allowing the connector to either give zeros OR nulls for nulls, but not both.
When nullDateTimeIsZero=false (default), myosotis will return nulls for null date/time/datetime types
When nullDateTimeIsZero=true, myosotis will return "0000-00-00 00:00:00" for null datetimes, "0000-00-00" for null dates and "00:00:00" for null times,

Post by Gilles Rayrat (JIRA)
Document nullDateTimeIsZero option and its effects
--------------------------------------------------
Key: MYO-42
URL: https://forge.continuent.org/jira/browse/MYO-42
Project: Myosotis
Type: Task
Components: MySQL protocol
Reporter: Gilles Rayrat
Assignee: Gilles Rayrat
Fix For: 0.6.1
Option nullDateTimeIsZero option allows user to have zero dates, times and timestamps instead of nulls
This option should be fully explained at least in a README file

[Gilles Rayrat (JIRA)]

[ https://forge.continuent.org/jira/browse/MYO-42?page=comments#action_14302 ]

Gilles Rayrat commented on MYO-42:
----------------------------------

This discards and replaces the previous comment.

MySQL specific zero dates (00-00-0000) and datetimes (00-00-0000 00:00:00) cannot be represented in Java. In order to workaround that issue, MySQL java connector provides a setting in the connection URL "zeroDateTimeBehavior=..." offering 3 possibilities:

. exception (default): this will throw an error when trying to retrieve zero dates
. convertToNull: this will convert zero values to null
. round: this will convert zero dates/times to the nearest jdbc representable value, which is 01-01-0001 (00:00:00)

Myosotis 0.6.1 introduces a new feature that mirrors this behavior:
mirrorZeroDateTimeBehavior = < convertToNull | round | exception | no (default) >
Due to Sequoia architecture, the "exception" mirroring will not work, as the exception comes too early, ie. at execute() time (and not at getDate()).
Thus, two options are available:
convertToNull: null values of type date or datetime will be converted to all zeros. Drawback for this option is that even "real nulls" will be converted, offering no possibility to determine what was null and what was zero.
round: will make connector transform 01-01-0001 [00:00:00] to 00-00-0000 [00:00:00]. A side effect of this feature appears for date calculations. If one makes MySQL compute a date as 01-01-0001, zeros result will be returned instead.

Summary of what needs to be done for enabling these options:
1/ change vdb.xmls backend urls to use connection option zeroDateTimeBehavior=convertToNull or round
2/ edit connector's conf/myosotis.properties to add mirrorZeroDateTimeBehavior=convertToNull or round
3/ reload vdbs and restart connector.

Document mirrorZeroDateTimeBehavior option
------------------------------------------
Key: MYO-42
URL: https://forge.continuent.org/jira/browse/MYO-42
Project: Myosotis
Type: Task
Components: MySQL protocol
Reporter: Gilles Rayrat
Assignee: Gilles Rayrat
Fix For: 0.6.1
Option mirrorZeroDateTimeBehavior = [ convertToNull | round | exception | no ] option allows full transparency for MySQL behavior on zero dateTimes
This option should be fully explained

[Tommi Takkunen-Lauri (JIRA)]

[ https://forge.continuent.org/jira/browse/MYO-42?page=all ]

Tommi Takkunen-Lauri resolved MYO-42:
-------------------------------------

Resolution: Fixed

Documented in 2008 Beta documentation

Document mirrorZeroDateTimeBehavior option
------------------------------------------
Key: MYO-42
URL: https://forge.continuent.org/jira/browse/MYO-42
Project: Myosotis
Type: Task
Components: MySQL protocol
Reporter: Gilles Rayrat
Assignee: Tommi Takkunen-Lauri
Fix For: 0.6.2
Option mirrorZeroDateTimeBehavior = [ convertToNull | round | exception | no ] option allows full transparency for MySQL behavior on zero dateTimes
This option should be fully explained

[Gilles Rayrat (JIRA)]

[ https://forge.continuent.org/jira/browse/MYO-42?page=all ]

Gilles Rayrat closed MYO-42:
----------------------------

Document mirrorZeroDateTimeBehavior option
------------------------------------------
Key: MYO-42
URL: https://forge.continuent.org/jira/browse/MYO-42
Project: Tungsten Connector
Type: Task
Components: MySQL protocol
Reporter: Gilles Rayrat
Assignee: Tommi Takkunen-Lauri
Fix For: 0.6.2
Option mirrorZeroDateTimeBehavior = [ convertToNull | round | exception | no ] option allows full transparency for MySQL behavior on zero dateTimes
This option should be fully explained