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
Clone of https://issues.jboss.org/browse/JBPM-4768
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
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); ... 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'.
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.
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
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.
PR: https://github.com/droolsjbpm/jbpm/pull/318 Please validate.
master: https://github.com/droolsjbpm/jbpm/commit/b1b5c3480
6.3.x: http://github.com/droolsjbpm/jbpm/commit/b5b410edc
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 [1]. [1] https://github.com/droolsjbpm/jbpm/pull/336.
My previous comment applies for DB2.
This PR was already merged in 6.3.x: https://github.com/droolsjbpm/jbpm/commit/b29c4fd348b33821623930c4631db8d282ba8e35
Verified in 6.2.0.CR1.