Red Hat Bugzilla – Bug 1272120
SQL upgrade scripts - changing SessionInfo primary key column data type fails
Last modified: 2015-11-19 09:06:59 EST
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
Set up appropriate Maven properties so some failing database system is used.
Run UpdateScriptsTest.testExecutingScripts() method from this PR:
Clone of https://issues.jboss.org/browse/JBPM-4768
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
Disabling/Enabling is safer than dropping and also it is easier to maintain in scripts
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);
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'.
yes, I think we will not have only a solution to satisfy all databases . For MySQL seems that dropping the constraints is the solution:
I have a MySQL instance and it seems working even with data.
Sorry, I meant SQL server 2012, not mysql. Afaik, no issues were reported on mysql with the existing scripts.
@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
I updated the SQL Server scripts and seems and it worked in my environment.
Remove: ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)
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
CREATE PROCEDURE alter_table_session_info
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')
-- dropping Constraint
SELECT @sqlDroppingConstraint = 'ALTER TABLE SessionInfo DROP CONSTRAINT ' + @const_name
-- Changing the SessionInfo table
SELECT @sqlAlterTableSessionInfo = 'ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)'
-- Recriating the constraint
SELECT @sqlRecriateConstraint = 'SELECT @sqlRecriateConstraint = 'ALTER TABLE SessionInfo ADD ' + @const_name + 'PRIMARY KEY CLUSTERED ([id] ASC)'
-- Executing the procedure
-- 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.
I set this to MODIFIED since it will be included in ER5.
Based on Ryan's comment, I'm moving this to ON_QA
Returning this. When there are no sessions stored in SessionInfo table, the datatype change fails. I made a PR for this .
My previous comment applies for DB2.
This PR was already merged in 6.3.x:
Verified in 6.2.0.CR1.