[jboss-jira] [JBoss JIRA] (WFLY-12280) DatabaseTimerPersistence does not detect mssql driver type

[David Ward (Jira)]

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

David Ward updated WFLY-12280:
------------------------------
    Description: 
The DatabaseTimerPersistence class' identifyDialect method [1] only recognizes the "microsoft" driver when it should also recognize the "mssql" driver, similar to how the code checks for both "hsql" and "hypersonic".

The result of this is that instead of the "create-table.mysql" property being loaded from timer-sql.properties [2], the default "create-table" property is used.

The repercussion of _that_ is that the TIMESTAMP column type is used instead of the DATETIME2 column type when creating the table, which results in the error "WFLYEJB0163: Cannot create table for timer persistence: com.microsoft.sqlserver.jdbc.SQLServerException: A table can only have one timestamp column. Because table 'JBOSS_EJB_TIMER' already has one, the column 'NEXT_DATE' cannot be added." Similar reports can be found all over the internet, for example, here: [3]

We ran into this problem when trying to fix RHPAM-2002. Please read the comments from this point forward: [4]

I _believe_ fix would be as simple as changing this line:
{code:java}
}else if (name.toLowerCase().contains("microsoft")) {
{code}
to this:
{code:java}
}else if (name.toLowerCase().contains("mssql") || name.toLowerCase().contains("microsoft")) {
{code}

[1] https://github.com/wildfly/wildfly/blob/master/ejb3/src/main/java/org/jboss/as/ejb3/timerservice/persistence/database/DatabaseTimerPersistence.java#L256

[2] https://github.com/wildfly/wildfly/blob/master/feature-pack/src/main/resources/modules/system/layers/base/org/jboss/as/ejb3/main/timers/timer-sql.properties#L9

[3] https://stackoverflow.com/questions/12063850/multiple-timestamp-columns-in-sql-server-2000/12063938

[4] https://issues.jboss.org/browse/RHPAM-2002?focusedCommentId=13757468&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-13757468

  was:
The DatabaseTimerPersistence class' identifyDialect method [1] only recognizes the "microsoft" driver when it should also recognize the "mssql" driver, similar to how the code checks for both "hsql" and "hypersonic".

The result of this is that instead of the "create-table.mysql" property being loaded from timer-sql.properties [2], the default "create-table" property is used.

The repercussion of _that_ is that the TIMESTAMP column type is used instead of the DATETIME2 column type when creating the table, which results in the error "WFLYEJB0163: Cannot create table for timer persistence: com.microsoft.sqlserver.jdbc.SQLServerException: A table can only have one timestamp column. Because table 'JBOSS_EJB_TIMER' already has one, the column 'NEXT_DATE' cannot be added." Similar reports can be found all over the internet, for example, here: [3]

We rean into this problem when trying to fix RHPAM-2002. Please read the comments from this point forward: [4]

I _believe_ fix would be as simple as changing this line:
{code:java}
}else if (name.toLowerCase().contains("microsoft")) {
{code}
to this:
{code:java}
}else if (name.toLowerCase().contains("mssql") || name.toLowerCase().contains("microsoft")) {
{code}

[1] https://github.com/wildfly/wildfly/blob/master/ejb3/src/main/java/org/jboss/as/ejb3/timerservice/persistence/database/DatabaseTimerPersistence.java#L256

[2] https://github.com/wildfly/wildfly/blob/master/feature-pack/src/main/resources/modules/system/layers/base/org/jboss/as/ejb3/main/timers/timer-sql.properties#L9

[3] https://stackoverflow.com/questions/12063850/multiple-timestamp-columns-in-sql-server-2000/12063938

[4] https://issues.jboss.org/browse/RHPAM-2002?focusedCommentId=13757468&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-13757468



> DatabaseTimerPersistence does not detect mssql driver type
> ----------------------------------------------------------
>
>                 Key: WFLY-12280
>                 URL: https://issues.jboss.org/browse/WFLY-12280
>             Project: WildFly
>          Issue Type: Bug
>          Components: EJB
>    Affects Versions: 17.0.1.Final
>            Reporter: David Ward
>            Assignee: Cheng Fang
>            Priority: Major
>
> The DatabaseTimerPersistence class' identifyDialect method [1] only recognizes the "microsoft" driver when it should also recognize the "mssql" driver, similar to how the code checks for both "hsql" and "hypersonic".
> The result of this is that instead of the "create-table.mysql" property being loaded from timer-sql.properties [2], the default "create-table" property is used.
> The repercussion of _that_ is that the TIMESTAMP column type is used instead of the DATETIME2 column type when creating the table, which results in the error "WFLYEJB0163: Cannot create table for timer persistence: com.microsoft.sqlserver.jdbc.SQLServerException: A table can only have one timestamp column. Because table 'JBOSS_EJB_TIMER' already has one, the column 'NEXT_DATE' cannot be added." Similar reports can be found all over the internet, for example, here: [3]
> We ran into this problem when trying to fix RHPAM-2002. Please read the comments from this point forward: [4]
> I _believe_ fix would be as simple as changing this line:
> {code:java}
> }else if (name.toLowerCase().contains("microsoft")) {
> {code}
> to this:
> {code:java}
> }else if (name.toLowerCase().contains("mssql") || name.toLowerCase().contains("microsoft")) {
> {code}
> [1] https://github.com/wildfly/wildfly/blob/master/ejb3/src/main/java/org/jboss/as/ejb3/timerservice/persistence/database/DatabaseTimerPersistence.java#L256
> [2] https://github.com/wildfly/wildfly/blob/master/feature-pack/src/main/resources/modules/system/layers/base/org/jboss/as/ejb3/main/timers/timer-sql.properties#L9
> [3] https://stackoverflow.com/questions/12063850/multiple-timestamp-columns-in-sql-server-2000/12063938
> [4] https://issues.jboss.org/browse/RHPAM-2002?focusedCommentId=13757468&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-13757468



--
This message was sent by Atlassian Jira
(v7.12.1#712002)