6:54 p.m.
Graeme Gillies created TEIID-1928:
-------------------------------------
Summary: PSQL emulation layer has problems converting timestamp '0000
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:10 p.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1928:
---------------------------------------
We are strict about converting strings to timestamps (see TEIID-745). For example
'0000-00-00 00:00:00' becomes 0002-11-30 00:00:00.0 due to the conversion from the
Julian calendar back to Gregorian and that 0 is not a valid day/month. Thus
'0000-00-00 00:00:00' does not represent a consistent timestamp using conversion
in Java.
Why are you getting values of this sort? A simple workaround would be to use nullif,
assuming that the value represents null.
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:59 p.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Graeme Gillies commented on TEIID-1928:
---------------------------------------
Doing a query against the database itself we can see
{noformat}
mysql> select distinct(changed) from History order by changed asc limit 3;
+---------------------+
| changed |
+---------------------+
| 0000-00-00 00:00:00 |
| 1989-11-08 08:00:00 |
| 1990-06-15 10:00:00 |
+---------------------+
3 rows in set (0.09 sec)
{noformat}
That the field has got values set to "0000-00-00 00:00:00". So it looks like the
code that does the conversion is failing somewhere? It seems to work fine via JDBC, just
fails with the PSQL layer.
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:58 a.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1928:
---------------------------------------
By works just fine over JDBC, you mean that through Teiid JDBC correct? That can be
because either you are pulling the value as timestamp or string without any conversion
along the way. In the PG case you are converting from string to timestamp. You would not
receive an error in the PG layer if it did the same thing - pull the value as a string or
a timestamp without a conversion. You'll want to check the types for the column
through out.
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:40 p.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Graeme Gillies commented on TEIID-1928:
---------------------------------------
Ahh I see, so the PG layer is failing to convert that value to a timestamp. I guess the
next question is, is there an easy way for me to stop it doing that conversion? I have a
feeling there isn't as the PG layer is out of our control and it will do what it wants
to do
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:25 a.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1928:
---------------------------------------
Have you checked what types your are dealing with through out? I don't think that the
PG layer does any particular conversion to timestamp, since it uses non-binary data
transfer almost everything is retrieved as string from the JDBC resultset.
Can you get the server-side stacktrace to better indicate when this is happening?
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:52 p.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Graeme Gillies commented on TEIID-1928:
---------------------------------------
Ahhhh sorry for wasting your time I figured it out.
The server stack trace revealed the error was coming from the mysql driver itself.
I then discovered the mysql jdbc driver has a property especially designed to configure
what happens when you hit invalid dates like this
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuratio...
it's called zeroDateTimeBehavior and setting it to convertToNull in the connection
string of the Datasource connection made the problem go away.
Not sure if it's worth mentioning in any teiid documentation or FAQ, but I guess we
should make sure that property is set on all our mysql connection strings.
Feel free to close/reject this issue.
Thanks
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:52 p.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1928.
-----------------------------------
Resolution: Rejected
Marking as rejected, but will add a note under the mysql translator docs.
Thanks Graeme
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:06 a.m.
[
https://issues.jboss.org/browse/TEIID-1928?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1928.
---------------------------------
PSQL emulation layer has problems converting timestamp '0000
------------------------------------------------------------
Key: TEIID-1928
URL: https://issues.jboss.org/browse/TEIID-1928
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64)
OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)
on
Red Hat Enterprise Linux Server
Teiid 7.6
Perl client running on either Fedora 16 or RHEL 5.7
Using perl-DBD-Pg-2.18.0-3.fc16.x86_64
Reporter: Graeme Gillies
Assignee: Steven Hawkins
When we attempt to run the following query against our teiid 7.6 install we get an error
about converting timestamps. Note this only seems to affect the PSQL emulation layer, jdbc
layer is fine
{noformat}
EngVDBF=> select * from orgchart2s.history;
ERROR: Error Code:S1009 Message:OrgChart2DS: Error Code:S1009 Message:Unexpected
exception while translating results: Cannot convert value '0000-00-00 00:00:00'
from column 6 to TIMESTAMP.
DETAIL: org.teiid.jdbc.TeiidSQLException: Error Code:S1009 Message:OrgChart2DS: Error
Code:S1009 Message:Unexpected exception while translating results: Cannot convert value
'0000-00-00 00:00:00' from column 6 to TIMESTAMP.
EngVDBF=>
{noformat}
It looks like there is a problem converting a timestamp of '0000-00-00 00:00:00'
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
5162
days inactive
5178
days old
8 comments
2 participants
participants (2)
-
Graeme Gillies (JIRA) -
Steven Hawkins (JIRA)