Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-14874

DVCS Connector failed with SQLException: Incorrect syntax near the keyword 'MERGE'

      After upgrading to JIRA 6.3.3, users hit into error like this:

      2014-08-07 19:41:52,461 http-bio-80-exec-6 ERROR admin 1181x4x4 1qbcxv0 10.60.0.171 /secure/admin/ConfigureDvcsOrganizations!default.jspa [com.atlassian.velocity.DefaultVelocityManager] MethodInvocationException occurred getting message body from Velocity: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
      Database:
      	- name:Microsoft SQL Server
      	- version:11.00.3128
      	- minor version:0
      	- major version:11
      Driver:
      	- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
      	- version:1.2.4
      
      java.sql.SQLException: Incorrect syntax near the keyword 'MERGE'.
      com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
      Database:
      	- name:Microsoft SQL Server
      	- version:11.00.3128
      	- minor version:0
      	- major version:11
      Driver:
      	- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
      	- version:1.2.4
      
      java.sql.SQLException: Incorrect syntax near the keyword 'MERGE'.
      	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:48)
      	at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:52)
      	at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:39)
      	at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory.createActiveObjects(ActiveObjectsServiceFactory.java:136)
      	at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory.access$000(ActiveObjectsServiceFactory.java:51)
      	at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory$1$1.get(ActiveObjectsServiceFactory.java:65)
      	at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory$1$1.get(ActiveObjectsServiceFactory.java:61)
      	at com.atlassian.activeobjects.osgi.DelegatingActiveObjects$MemoizingSupplier.get(DelegatingActiveObjects.java:156)
      	at com.atlassian.activeobjects.osgi.DelegatingActiveObjects.executeInTransaction(DelegatingActiveObjects.java:133)  <+3>
      	at java.lang.reflect.Method.invoke(Unknown Source)
      	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
      	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
      	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56)
      	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      	at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
      	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
      	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
      	at com.sun.proxy.$Proxy2402.executeInTransaction(Unknown Source)
      	at com.atlassian.jira.plugins.dvcs.dao.impl.OrganizationDaoImpl.getAll(OrganizationDaoImpl.java:137)
      	at com.atlassian.jira.plugins.dvcs.service.OrganizationServiceImpl.getAll(OrganizationServiceImpl.java:54)
      	at com.atlassian.jira.plugins.dvcs.webwork.ConfigureDvcsOrganizations.loadOrganizations(ConfigureDvcsOrganizations.java:85)  <+3>
      	at java.lang.reflect.Method.invoke(Unknown Source)
      	at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.doInvoke(UberspectImpl.java:381)
      	at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:370)
      	at com.atlassian.velocity.htmlsafe.introspection.UnboxingMethod.invoke(UnboxingMethod.java:30)
      	at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:270)
      	at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:262)
      	at org.apache.velocity.runtime.parser.node.ASTReference.value(ASTReference.java:507)
      	at org.apache.velocity.runtime.directive.Foreach.render(Foreach.java:282)
      	at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:175)
      	at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:72)
      	at org.apache.velocity.runtime.directive.VelocimacroProxy.render(VelocimacroProxy.java:212)
      	at org.apache.velocity.runtime.directive.RuntimeMacro.render(RuntimeMacro.java:247)
      	at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:175)
      	at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:336)
      	at org.apache.velocity.Template.merge(Template.java:328)
      	at org.apache.velocity.Template.merge(Template.java:235)
      	at org.apache.velocity.app.VelocityEngine.mergeTemplate(VelocityEngine.java:381)
      	at com.atlassian.velocity.DefaultVelocityManager.writeEncodedBodyImpl(DefaultVelocityManager.java:117)
      	at com.atlassian.velocity.DefaultVelocityManager.writeEncodedBody(DefaultVelocityManager.java:97)
      	at com.atlassian.jira.template.velocity.DefaultVelocityTemplatingEngine$DefaultRenderRequest.toWriterImpl(DefaultVelocityTemplatingEngine.java:146)
      ...
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      	at java.lang.Thread.run(Unknown Source)
      Caused by: java.sql.SQLException: Incorrect syntax near the keyword 'MERGE'.
      	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
      	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
      	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
      	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
      	at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
      	at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
      	at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
      	at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
      	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
      	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
      	at net.java.ao.DatabaseProvider.executeUpdate(DatabaseProvider.java:2309)
      	at net.java.ao.DatabaseProvider.executeUpdateForAction(DatabaseProvider.java:2379)
      	at net.java.ao.DatabaseProvider.executeUpdatesForActions(DatabaseProvider.java:2342)
      	at net.java.ao.schema.SchemaGenerator.migrate(SchemaGenerator.java:99)
      	at net.java.ao.EntityManager.migrate(EntityManager.java:143)
      	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:44)
      	... 235 more
      

      Affected Databases

      Microsoft SQL Server 2005/2008/2012

      WORKAROUND for JIRA 6.3.3

      This workaround is for JIRA 6.3.3 only

      1. Download version 0.23.8 of activeobjects plugin
      2. Login to JIRA as sysadmin
      3. Go to Admin -> Manage Add-ons
      4. Select All add-ons from the drop down and find the JIRA DVCS Connector Plugin
      5. Disable the JIRA DVCS Connector plugin
      6. Click on Upload an add-on
      7. Select the download 0.23.8 activeobjects plugin jar file: activeobjects-plugin-0.23.8.jar and click Upload to upload it, which should start the upgrade of the activeobjects plugin
      8. Once the upgrade is completed, select All add-ons from the drop down, find the JIRA DVCS Connector Plugin, and enable it

      The above steps should fix the issue. If the issue still persists, please comment on this issue and keep the DVCS Connector disabled.

            [JSWSERVER-14874] DVCS Connector failed with SQLException: Incorrect syntax near the keyword 'MERGE'

            Yes. Exactly.

            S. Bilgehan Ustundag added a comment - Yes. Exactly.

            ZehuaA added a comment -

            Ah, I think it's the infamous Turkish locale issue. Glad to hear that it works for you now.

            ZehuaA added a comment - Ah, I think it's the infamous Turkish locale issue . Glad to hear that it works for you now.

            Ok. I've found the problem. I had set the collation as case-insensitive but it was Turkish Case-Insensitive. In Turkish, uppercase of i is İ and lower case of I is ı. So, lowercase of Id is ıd in Turkish. That's a typical programming error, which we frequently see in Turkiye from foreign programs. Changing collation to SQL_Latin1_General_CP437_CI_AI solved the problem. Thank you.

            S. Bilgehan Ustundag added a comment - Ok. I've found the problem. I had set the collation as case-insensitive but it was Turkish Case-Insensitive . In Turkish, uppercase of i is İ and lower case of I is ı . So, lowercase of Id is ıd in Turkish. That's a typical programming error, which we frequently see in Turkiye from foreign programs. Changing collation to SQL_Latin1_General_CP437_CI_AI solved the problem. Thank you.

            ZehuaA added a comment -

            Could you check whether the collation setting of the database is set according to: https://confluence.atlassian.com/display/JIRA/Connecting+JIRA+to+SQL+Server+2012 ?

            What's the name of the table that's having the Invalid column name 'id' problem?

            ZehuaA added a comment - Could you check whether the collation setting of the database is set according to: https://confluence.atlassian.com/display/JIRA/Connecting+JIRA+to+SQL+Server+2012 ? What's the name of the table that's having the Invalid column name 'id' problem?

            We were using JIRA 6.2.5 on Windows Server 2012 R2 + SQL Server 2012 Service Pack 1 combo. After finding certain problem in Bamboo which seems to fixed in new version, we've decided to upgrade all Atlassian products at once. But, only JIRA failed to upgrade. Accidentally we've skipped database backup prior to upgrade. And now JIRA refuse to start with following error:

            Exception thrown during upgrade: Invalid column name 'id'.
            java.sql.SQLException: Invalid column name 'id'.
            

            I've read somewhere in the logs that same "MERGE" error. So, I thought that's a related problem. But, after seeing your comment, that could be an another upgrade error.

            S. Bilgehan Ustundag added a comment - We were using JIRA 6.2.5 on Windows Server 2012 R2 + SQL Server 2012 Service Pack 1 combo. After finding certain problem in Bamboo which seems to fixed in new version, we've decided to upgrade all Atlassian products at once. But, only JIRA failed to upgrade. Accidentally we've skipped database backup prior to upgrade. And now JIRA refuse to start with following error: Exception thrown during upgrade: Invalid column name 'id'. java.sql.SQLException: Invalid column name 'id'. I've read somewhere in the logs that same "MERGE" error. So, I thought that's a related problem. But, after seeing your comment, that could be an another upgrade error.

            ZehuaA added a comment -

            Hi info707,

            What's the actual error that prevents JIRA from starting up? This issue should not cause JIRA to fail to start up.

            Cheers,
            Zehua

            ZehuaA added a comment - Hi info707 , What's the actual error that prevents JIRA from starting up? This issue should not cause JIRA to fail to start up. Cheers, Zehua

            How can I disable DVCS Connector from accidentally upgraded database? JIRA now fails to start.

            S. Bilgehan Ustundag added a comment - How can I disable DVCS Connector from accidentally upgraded database? JIRA now fails to start.

            Sam Grooms added a comment -

            I ended up disabling this system plugin completely for now. We do not use Bitbucket or GitHub, so this has no impact to my company. I'm glad I caught this early; otherwise our VM's disk space would have been consumed by the incremental log files all capturing this same error over-and-over.

            Sam Grooms added a comment - I ended up disabling this system plugin completely for now. We do not use Bitbucket or GitHub, so this has no impact to my company. I'm glad I caught this early; otherwise our VM's disk space would have been consumed by the incremental log files all capturing this same error over-and-over.

            ZehuaA added a comment -

            Hi martin.cassidy1, this issue does not affect Fish Eye integration.

            To be clear, this issue affects only integration with Bitbucket and GitHub.

            ZehuaA added a comment - Hi martin.cassidy1 , this issue does not affect Fish Eye integration. To be clear, this issue affects only integration with Bitbucket and GitHub.

            Will this affect Fish Eye?

            Martin Cassidy added a comment - Will this affect Fish Eye?

              ohernandez@atlassian.com Oswaldo Hernandez (Inactive)
              cchan Chung Park Chan
              Affected customers:
              11 This affects my team
              Watchers:
              30 Start watching this issue

                Created:
                Updated:
                Resolved: