[teiid-issues] [JBoss JIRA] Updated: (TEIID-379) MySQL connector assumes that database is running in ANSI mode rather than the default mode

[Steven Hawkins (JIRA)]

     [ https://jira.jboss.org/jira/browse/TEIID-379?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-379:
---------------------------------

     Original Estimate: 1 hour
    Remaining Estimate: 1 hour
         Fix Version/s: 6.0.0
     Affects Version/s: 6.0.0
               Affects: [Documentation (Ref Guide, User Guide, etc.), Release Notes]
            Complexity: Low
              Priority: Optional  (was: Major)


> MySQL connector assumes that database is running in ANSI mode rather than the default mode
> ------------------------------------------------------------------------------------------
>
>                 Key: TEIID-379
>                 URL: https://jira.jboss.org/jira/browse/TEIID-379
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 6.0.0
>         Environment: Teiid trunk as of 2/23/09 at 3:22 PM eastern
>            Reporter: Greg Haber
>            Assignee: Steven Hawkins
>            Priority: Optional
>             Fix For: 6.0.0
>
>   Original Estimate: 1 hour
>  Remaining Estimate: 1 hour
>
> I noticed with the legacy MetaMatrix product that our MySQL connector seems to assume that it is connecting to a MySQL database running in "ANSI" SQL mode
> For instance, we list in the capabilities class that MySQL supports || as a string concatenation parameter, but it actually only supports it if the SQL mode "PIPES_AS_CONCAT" is set - and this is one of the individual modes that gets set when you set the "ANSI" meta-mode (see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi for details).
> This is a bad assumption as it is not the default setting for MySQL (the default sql-mode setting is "", or no special modes).  I took a look at some of the databases used at Red Hat for testing MetaMatrix for MySQL (specifically, slntds03) and noticed that they have sql-mode set to ANSI on the server side.
> I took a look at the current Teiid trunk code and it is the same as the legacy MetaMatrix code in this regard, so Teiid has the same issue.
> Two possible resolutions are:
> -Rework the connector to remove any assumptions that sql-mode=ANSI
> -set sql-mode to ANSI when establishing new connections to MySQL (this can be done by sending the SQL statement 'set SESSION sql-mode="ANSI" after establishing a connection).
> Unfortunately there does not appear to be a way to set this mode via a JDBC connection property - you need to send the "set" command after establishing the connection.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira