Bug 1025735 - Incorrect SQL syntax in SRAMP for SQL Server (re-opened)
Incorrect SQL syntax in SRAMP for SQL Server (re-opened)
Status: CLOSED CURRENTRELEASE
Product: JBoss Fuse Service Works 6
Classification: JBoss
Component: Installer (Show other bugs)
6.0.0 GA
Unspecified Unspecified
urgent Severity urgent
: ER7
: 6.0.0
Assigned To: Steve Johnson
Len DiMaggio
: Reopened
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-11-01 08:06 EDT by Michael Burman
Modified: 2014-02-06 10:25 EST (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-02-06 10:25:46 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
logs-mssql2008 (32.48 KB, application/zip)
2013-12-17 08:30 EST, Pavol Srna
no flags Details

  None (edit)
Description Michael Burman 2013-11-01 08:06:48 EDT
Description of problem: S-RAMP (and Infinispan) tries to generate database table with incorrect length as the parameter for varchar when using the SQL Server:

Correct syntax is varchar(MAX) for SQL Server's long text fields and varbinary(MAX) for the binary data. There's no difference in performance when comparing these two options, as the SQL Server has to in any case create this data on a different data page (8k is the size of one page).

14:51:22,391 INFO  [stdout] (pool-21-thread-1) org.overlord.sramp.atom.err.SrampAtomException: The size (60000) given to
          the column 'datum' exceeds the maximum allowed for any data type (8000).

->

14:51:21,906 ERROR [org.infinispan.loaders.jdbc.TableManipulation] (http-127.0.0.1/127.0.0.1:8080-2) ISPN008011: Error while creating table; used DDL statement: 'CREATE TABLE "ispn_bucket_sramp"(id VARCHAR(500) NOT NULL, datum VARBINARY(60000), version BIGINT, PRIMARY KEY (id))': com.microsoft.sqlserver.jdbc.SQLServerException: The size (60000) given to the column 'datum' exceeds the maximum allowed for any data type (8000).


Version-Release number of selected component (if applicable): FSW 6.0.0 beta.


How reproducible:


Steps to Reproduce:
1.
2.
3.

Actual results:


Expected results:


Additional info:
Comment 2 kconner 2013-11-01 08:57:56 EDT
Assigning to Steve.  SQLServer is not a supported DB for the beta release but this will be addressed by Final.
Comment 3 Steve Johnson 2013-11-04 12:25:24 EST
Thanks Michael.  This needs further investigation, maybe into the installer.  I've checked the configuration file for SQL Server and the configuration for datum is as follows.

/subsystem=infinispan/cache-container=modeshape/local-cache=sramp/string-keyed-jdbc-store=STRING_KEYED_JDBC_STORE:add(datasource=java:jboss/datasources/srampDS,passivation=false,purge=false,string-keyed-table={prefix=ispn_bucket,id-column={name=id,type=VARCHAR(500)},data-column={name=datum,type=VARBINARY(8000)},timestamp-column={name=version,type=BIGINT}}

So it seems the configuration should have worked and has been tested in prod to have worked against sql server.  So next step is to check if this configuration file is getting run properly at installation time.  Thanks for the catch.

Point taken a better solution would be to use MAX instead of 8000 also.  Will make this amendment.


(In reply to Michael Burman from comment #0)
> Description of problem: S-RAMP (and Infinispan) tries to generate database
> table with incorrect length as the parameter for varchar when using the SQL
> Server:
> 
> Correct syntax is varchar(MAX) for SQL Server's long text fields and
> varbinary(MAX) for the binary data. There's no difference in performance
> when comparing these two options, as the SQL Server has to in any case
> create this data on a different data page (8k is the size of one page).
> 
> 14:51:22,391 INFO  [stdout] (pool-21-thread-1)
> org.overlord.sramp.atom.err.SrampAtomException: The size (60000) given to
>           the column 'datum' exceeds the maximum allowed for any data type
> (8000).
> 
> ->
> 
> 14:51:21,906 ERROR [org.infinispan.loaders.jdbc.TableManipulation]
> (http-127.0.0.1/127.0.0.1:8080-2) ISPN008011: Error while creating table;
> used DDL statement: 'CREATE TABLE "ispn_bucket_sramp"(id VARCHAR(500) NOT
> NULL, datum VARBINARY(60000), version BIGINT, PRIMARY KEY (id))':
> com.microsoft.sqlserver.jdbc.SQLServerException: The size (60000) given to
> the column 'datum' exceeds the maximum allowed for any data type (8000).
> 
> 
> Version-Release number of selected component (if applicable): FSW 6.0.0 beta.
> 
> 
> How reproducible:
> 
> 
> Steps to Reproduce:
> 1.
> 2.
> 3.
> 
> Actual results:
> 
> 
> Expected results:
> 
> 
> Additional info:
Comment 4 Steve Johnson 2013-11-04 13:09:39 EST
Hi,
The failure is due to current release under testing (Beta?) not supporting databases other than MySQL and H2 I believe.  Having spoke to the installer team, this is the case.  The installer code has not been upgraded to call correct SQL Server configuration yet for sramp.

I believe this may be scheduled for next release, but will discuss with the appropriate stakeholders.
Comment 5 Steve Johnson 2013-11-04 13:11:54 EST
Assigning over to Installer team for inclusion on next round of updates.
Comment 6 Thomas Hauser 2013-12-12 11:41:49 EST
This is corrected in ER7-2.
Comment 7 Pavol Srna 2013-12-17 08:29:16 EST
Tried with ER7-2 build and mssql2008R2 DB hitting this issue:

Wrong column type in dballo02.dbo.RTGOV_ACTIVITIES for column content. Found: text, expected: varchar(MAX)

Please see attached logs.
Comment 8 Pavol Srna 2013-12-17 08:30:18 EST
Created attachment 837690 [details]
logs-mssql2008
Comment 9 Thomas Hauser 2013-12-17 09:55:15 EST
Assigning to Stejohns for further diagnoses!
Comment 10 Steve Johnson 2013-12-20 12:27:02 EST
I need to discuss with Pavol and look at the two environments this scenario has been run in for differences.

Using the installer at http://dev138.mw.lab.eng.bos.redhat.com/candidate/soa-6.0.0-ER7-2/jboss-fsw-installer-6.0.0.ER7-redhat-2.jar I had a successful install.

I subsequently found issue https://bugzilla.redhat.com/show_bug.cgi?id=1044486 during server start up, which has since been fixed and now both installation and server start up successfully on ms sql server.

Investigation has shown that the ms sql server database changes the type to "text" from "varchar(max)" during table creation time.  The anomaly here is the QE test run has shown to error out the install step due to this conversion whereas the prod test run (and separate developer test run) has shown to install successfully and cope with the type conversion.
Comment 11 Nick Cross 2014-01-14 06:42:51 EST
We believe this is fixed. Is this related to Bug 1051566 - we should be using version 3 of the driver.
Comment 12 Michael Burman 2014-01-14 06:44:34 EST
Hi,

You should be using version 4.0 of the driver, if you want to support newer versions of SQL Server and get the XA-transactions to work (3.0 gives incorrect format_id parameter in the XA-transactions).
Comment 14 Pavol Srna 2014-01-16 06:28:45 EST
Verified in CR1.

Note You need to log in before you can comment on or make changes to this bug.