ContentPermissionConstraintsUpgradeTask fails when there is NULL in CONTENT_PERM.CPS_ID or CONTENT_PERM_SET.CONTENT_ID

Steps to reproduce

1. Upgrade to Confluence 3.5.13 or higher from a Confluence where there is NULL in CONTENT_PERM.CPS_ID or CONTENT_PERM_SET.CONTENT_ID
   • Devs or supporters can get a sample data of 3.5.11 dump from CSP-68372
2. The upgrade will fail and here what will appear in the log:

   2011-09-15 23:22:09,074 INFO [main] [confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask] doUpgrade Beginning task to add not null and unique constraints on Content Permissions and Content Permission Set columns
   2011-09-15 23:22:09,861 ERROR [main] [confluence.upgrade.ddl.AlterTableExecutor] alterTableUngrouped Failed to run alter table SQL: alter table CONTENT_PERM modify CPS_ID bigint not null
   2011-09-15 23:22:09,899 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@1d849ae failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID bigint not null]; SQL state [01000]; error code [1265]; Data truncated for column 'CPS_ID' at row 10; nested exception is java.sql.SQLException: Data truncated for column 'CPS_ID' at row 10
   com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@1d849ae failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID bigint not null]; SQL state [01000]; error code [1265]; Data truncated for column 'CPS_ID' at row 10; nested exception is java.sql.SQLException: Data truncated for column 'CPS_ID' at row 10
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:216)
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:135)
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:92)
   	at com.atlassian.confluence.upgrade.impl.DefaultUpgradeManager.upgrade(DefaultUpgradeManager.java:140)
   	at com.atlassian.confluence.upgrade.UpgradeLauncherServletContextListener.contextInitialized(UpgradeLauncherServletContextListener.java:28)
   	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4205)
   	at org.apache.catalina.core.StandardContext.start(StandardContext.java:4704)
   	at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
   	at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
   	at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
   	at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
   	at org.apache.catalina.core.StandardService.start(StandardService.java:525)
   	at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
   	at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
   	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   	at java.lang.reflect.Method.invoke(Method.java:597)
   	at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
   	at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
   Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID bigint not null]; SQL state [01000]; error code [1265]; Data truncated for column 'CPS_ID' at row 10; nested exception is java.sql.SQLException: Data truncated for column 'CPS_ID' at row 10
   	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:120)
   	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
   	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:354)
   	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376)
   	at com.atlassian.confluence.upgrade.ddl.DdlExecutor.executeDdlStatements(DdlExecutor.java:50)
   	at com.atlassian.confluence.upgrade.ddl.DdlExecutor$$FastClassByCGLIB$$cf93a722.invoke(<generated>)
   	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
   	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:696)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)
   	at com.atlassian.confluence.upgrade.ddl.DdlExecutor$$EnhancerByCGLIB$$f25c29de.executeDdlStatements(<generated>)
   	at com.atlassian.confluence.upgrade.ddl.AlterTableExecutor.alterTableUngrouped(AlterTableExecutor.java:94)
   	at com.atlassian.confluence.upgrade.ddl.AlterTableExecutor.alterTable(AlterTableExecutor.java:68)
   	at com.atlassian.confluence.upgrade.ddl.AlterTableExecutor$$FastClassByCGLIB$$d4efbaa8.invoke(<generated>)
   	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
   	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:696)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)
   	at com.atlassian.confluence.upgrade.ddl.AlterTableExecutor$$EnhancerByCGLIB$$40fb6fe4.alterTable(<generated>)
   	at com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask.doUpgrade(ContentPermissionConstraintsUpgradeTask.java:163)
   	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   	at java.lang.reflect.Method.invoke(Method.java:597)
   	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   	at $Proxy35.doUpgrade(Unknown Source)
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager$UpgradeStep$3.execute(AbstractUpgradeManager.java:428)
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:190)
   	... 19 more
   Caused by: java.sql.SQLException: Data truncated for column 'CPS_ID' at row 10
   	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
   	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3563)
   	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3495)
   	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
   	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
   	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2687)
   	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2616)
   	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782)
   	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625)
   	at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
   	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:369)
   	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:343)
   	... 53 more
   2011-09-15 23:22:09,921 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized 1 errors were encountered during upgrade:
   2011-09-15 23:22:09,921 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized 1: StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID bigint not null]; SQL state [01000]; error code [1265]; Data truncated for column 'CPS_ID' at row 10; nested exception is java.sql.SQLException: Data truncated for column 'CPS_ID' at row 10
   

Workaround

Remove any NULLS in CONTENT_PERM.CPS_ID or CONTENT_PERM_SET.CONTENT_ID:

1. Run the following SQL queries:

   delete from CONTENT_PERM where CPS_ID is null;
   delete from CONTENT_PERM_SET where CONTENT_ID is null;
   

2. Re-run Confluence 3.5.13 or higher to trigger the upgrade