Bug 1272120 - SQL upgrade scripts - changing SessionInfo primary key column data type fails
SQL upgrade scripts - changing SessionInfo primary key column data type fails
Status: VERIFIED
Product: JBoss BPMS Platform 6
Classification: JBoss
Component: jBPM Core (Show other bugs)
6.1.0
Unspecified Unspecified
high Severity high
: CR1
: 6.2.0
Assigned To: Kris Verlaenen
Tibor Zimanyi
:
Depends On: 1235091 1273622
Blocks:
  Show dependency treegraph
 
Reported: 2015-10-15 10:23 EDT by Kris Verlaenen
Modified: 2015-11-19 09:06 EST (History)
5 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed:
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)

  None (edit)
Description Kris Verlaenen 2015-10-15 10:23:47 EDT
In database upgrade scripts, which are located in jbpm-installer module, we update data type of primary key column "id" of "SessionInfo" table. It's script jbpm-6.1-to-6.2.sql. Some database systems can do this update, but some others not.

E.g. in SQL Server you must first drop the primary key constraint from the column and after data type change, create it again. But the constraint can be dropped only if there are no foreign key references referencing the column. So when there is a lot of data, this can be a problem.

I'm creating this because I cannot find a "clear" solution for this case. An idea was to disable constraints temporarily, drop primary key constraint, make the data type change, enable constraints. But this can be risky, because when something fails, constraints can remain disabled.

Tested databases where it fails:

    SQL Server 2012
    DB2 10.1

Reproducer:

    Set up appropriate Maven properties so some failing database system is used.
    Run UpdateScriptsTest.testExecutingScripts() method from this PR:
    https://github.com/droolsjbpm/jbpm/pull/304
Comment 1 Kris Verlaenen 2015-10-15 10:24:23 EDT
Clone of https://issues.jboss.org/browse/JBPM-4768
Comment 4 Alessandro Lazarotti 2015-10-20 15:17:27 EDT
Hi Kris,
it seems that you can disable the constraints instead of drooping/recreating them:
SQL Server discussion: http://www.coderanch.com/t/463860/JDBC/databases/Foreign-Key-Restricts-Alter-data
DB2 discussion: http://stackoverflow.com/questions/421518/is-there-a-way-to-enable-disable-constraints-in-db2-v7
Comment 5 Alessandro Lazarotti 2015-10-20 15:19:17 EDT
Disabling/Enabling is safer than dropping and also it is easier to maintain in scripts
Comment 6 Kris Verlaenen 2015-10-21 11:53:55 EDT
Wasn't successful on disabling constraints on MySQL, if I do the following:

ALTER TABLE SessionInfo NOCHECK CONSTRAINT ALL;
ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0);
...

still getting:

 ALTER TABLE SessionInfo NOCHECK CONSTRAINT ALL
17:43:23.682 [main] DEBUG b.t.r.c.TransactionContextHelper - enlisting a JdbcPooledConnection from datasource jdbc/testDS1 in state ACCESSIBLE with usage count 1 wrapping SQLServerXAConnection:2 into null
17:43:23.682 [main] DEBUG b.t.r.c.TransactionContextHelper - in local transaction context, skipping enlistment
 ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)
17:43:23.992 [main] DEBUG b.t.r.c.TransactionContextHelper - enlisting a JdbcPooledConnection from datasource jdbc/testDS1 in state ACCESSIBLE with usage count 1 wrapping SQLServerXAConnection:2 into null
17:43:23.992 [main] DEBUG b.t.r.c.TransactionContextHelper - in local transaction context, skipping enlistment
...

java.lang.RuntimeException: The object 'PK__SessionI__3213E83FB20D16EE' is dependent on column 'id'.
Comment 7 Alessandro Lazarotti 2015-10-21 17:35:02 EDT
yes, I think we will not have only a solution to satisfy all databases . For MySQL seems that dropping the constraints is the solution:

http://stackoverflow.com/questions/13606469/cannot-change-column-used-in-a-foreign-key-constraint

I have a MySQL instance and it seems working even with data.
Comment 8 Kris Verlaenen 2015-10-22 10:22:22 EDT
Sorry, I meant SQL server 2012, not mysql.  Afaik, no issues were reported on mysql with the existing scripts.
Comment 9 Alessandro Lazarotti 2015-10-22 17:30:43 EDT
@Fspolti from GSS is looking to help with the script for DB2 and SQLServer. It should update this BZ or sent the PR still today or tomorrow
Comment 10 Spolti 2015-10-22 20:31:11 EDT
Hello Team.

I updated the SQL Server scripts and seems and it worked in my environment.
Changes:

Remove: ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)

Add:

IF EXISTS (SELECT * FROM sys.objects WHERE type ='P' AND name = 'alter_table_session_info')
DROP PROCEDURE alter_table_session_info
-- Creating the procedure to delete the constraint from SessionInfo table
GO
CREATE PROCEDURE alter_table_session_info
AS
        DECLARE @const_name VARCHAR(255)
        DECLARE @sqlDroppingConstraint VARCHAR(255)
        DECLARE @sqlAlterTableSessionInfo VARCHAR(255)
        DECLARE @sqlRecriateConstraint VARCHAR(255)
 
        SELECT @const_name = (SELECT CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='SessionInfo')
        BEGIN
                -- dropping Constraint
                SELECT @sqlDroppingConstraint = 'ALTER TABLE SessionInfo DROP CONSTRAINT ' + @const_name
                EXEC (@sqlDroppingConstraint)
 
                -- Changing the SessionInfo table
                SELECT @sqlAlterTableSessionInfo = 'ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)'
                EXEC (@sqlAlterTableSessionInfo)
 
                -- Recriating the constraint
                SELECT @sqlRecriateConstraint = 'SELECT @sqlRecriateConstraint = 'ALTER TABLE SessionInfo ADD ' + @const_name + 'PRIMARY KEY CLUSTERED ([id] ASC)'
                EXEC @sqlRecriateConstraint
        END
GO
-- Executing the procedure
EXECUTE alter_table_session_info
-- Deleting the procedure to clean it from database
DROP PROCEDURE alter_table_session_info
-- Recriating the Index
ALTER INDEX ALL ON SessionInfo REORGANIZE;


and where have something like it: UPDATE Task t SET name = (SELECT shortText FROM I18NText WHERE Task_Names_Id = t.id)
Change to UPDATE Task SET name = (SELECT shortText FROM I18NText WHERE Task_Names_Id = Task.id)

do the same to TaskEvent.




I am now verifying the DB2 script, when I get it finished I'll send the PR.
Comment 11 Spolti 2015-10-26 09:12:31 EDT
PR: https://github.com/droolsjbpm/jbpm/pull/318

Please validate.
Comment 12 Kris Verlaenen 2015-10-27 21:25:27 EDT
master: https://github.com/droolsjbpm/jbpm/commit/b1b5c3480
Comment 14 Kris Verlaenen 2015-10-28 09:38:34 EDT
6.3.x: http://github.com/droolsjbpm/jbpm/commit/b5b410edc
Comment 15 Ryan Zhang 2015-10-30 01:52:42 EDT
I set this to MODIFIED since it will be included in ER5.
Comment 16 Rajesh Rajasekaran 2015-11-12 10:06:11 EST
Based on Ryan's comment, I'm moving this to ON_QA
Comment 17 Tibor Zimanyi 2015-11-12 10:54:34 EST
Returning this. When there are no sessions stored in SessionInfo table, the datatype change fails. I made a PR for this [1].

[1] https://github.com/droolsjbpm/jbpm/pull/336.
Comment 18 Tibor Zimanyi 2015-11-12 10:55:01 EST
My previous comment applies for DB2.
Comment 19 Kris Verlaenen 2015-11-13 10:57:00 EST
This PR was already merged in 6.3.x:
https://github.com/droolsjbpm/jbpm/commit/b29c4fd348b33821623930c4631db8d282ba8e35
Comment 21 Tibor Zimanyi 2015-11-19 09:06:59 EST
Verified in 6.2.0.CR1.

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