| ij> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| -- This script tests online backup functionality |
| -- 1) in a non-idle tranaction. |
| -- 2) mutiple backup calls on the same connection. |
| -- 3) when unlogged operations are running in parallel |
| -- to the backup thread. |
| connect 'wombat' as c1 ; |
| ij(C1)> create procedure sleep(t BIGINT) dynamic result sets 0 |
| language java external name 'java.lang.Thread.sleep' |
| parameter style java; |
| 0 rows inserted/updated/deleted |
| ij(C1)> create function fileExists(fileName varchar(128)) |
| returns VARCHAR(100) external name |
| 'org.apache.derbyTesting.functionTests.util.FTFileUtil.fileExists' |
| language java parameter style java; |
| 0 rows inserted/updated/deleted |
| ij(C1)> create function removeDirectory(fileName varchar(128)) |
| returns VARCHAR(100) external name |
| 'org.apache.derbyTesting.functionTests.util.FTFileUtil.removeDirectory' |
| language java parameter style java; |
| 0 rows inserted/updated/deleted |
| ij(C1)> autocommit off; |
| ij(C1)> create table t1(a int ) ; |
| 0 rows inserted/updated/deleted |
| ij(C1)> insert into t1 values(1) ; |
| 1 row inserted/updated/deleted |
| ij(C1)> insert into t1 values(2) ; |
| 1 row inserted/updated/deleted |
| ij(C1)> commit ; |
| ij(C1)> -- make sure backup calls are not allowed in a transaction that |
| -- has executed unlogged operations before the backup calls. |
| insert into t1 values(3); |
| 1 row inserted/updated/deleted |
| ij(C1)> create index idx1 on t1(a); |
| 0 rows inserted/updated/deleted |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('extinout/mybackup') ; |
| ERROR XSRSB: Backup cannot be performed in a transaction with uncommitted unlogged operations. |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT('extinout/mybackup') ; |
| ERROR XSRSB: Backup cannot be performed in a transaction with uncommitted unlogged operations. |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE( |
| 'extinout/mybackup', 1); |
| ERROR XSRSB: Backup cannot be performed in a transaction with uncommitted unlogged operations. |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT( |
| 'extinout/mybackup', 1); |
| ERROR XSRSB: Backup cannot be performed in a transaction with uncommitted unlogged operations. |
| ij(C1)> --backup failures should not rollback/commit the transaction. |
| select * from t1 ; |
| A |
| ----------- |
| 1 |
| 2 |
| 3 |
| ij(C1)> insert into t1 values(4) ; |
| 1 row inserted/updated/deleted |
| ij(C1)> commit; |
| ij(C1)> drop index idx1; |
| 0 rows inserted/updated/deleted |
| ij(C1)> commit; |
| ij(C1)> --- make sure backup calls can be run one after another. |
| insert into t1 values(5) ; |
| 1 row inserted/updated/deleted |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('extinout/mybackup') ; |
| 0 rows inserted/updated/deleted |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT('extinout/mybackup'); |
| 0 rows inserted/updated/deleted |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE( |
| 'extinout/mybackup', 1); |
| 0 rows inserted/updated/deleted |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT( |
| 'extinout/mybackup', 1); |
| 0 rows inserted/updated/deleted |
| ij(C1)> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1); |
| 0 rows inserted/updated/deleted |
| ij(C1)> commit; |
| ij(C1)> -- make sure backup is not allowed when non-logged |
| -- operations are pending |
| connect 'wombat' as c2 ; |
| ij(C2)> autocommit off ; |
| ij(C2)> -- index creaton is a non-logged ops, backup should not run |
| -- until it is committed |
| create index idx1 on t1(a) ; |
| 0 rows inserted/updated/deleted |
| ij(C2)> set connection c1 ; |
| ij(C1)> -- following two backup calls should fail , because they are not waiting |
| -- for the unlogged index creation in anothere transaction to commit/rollback. |
| call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT('extinout/mybackup') ; |
| ERROR XSRSA: Cannot backup the database when unlogged operations are uncommitted. Please commit the transactions with backup blocking operations. |
| ij(C1)> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT( |
| 'extinout/mybackup', 1); |
| ERROR XSRSA: Cannot backup the database when unlogged operations are uncommitted. Please commit the transactions with backup blocking operations. |
| ij(C1)> set connection c2; |
| ij(C2)> rollback ; |
| ij(C2)> -- make sure backup call waits, if wait parameter value is non-zero or |
| -- the procedures used from before 10.2( old backup procedures wait by |
| -- default for unlogged operation to finish.) |
| -- This testing is done by starting backup in a different thread and then |
| -- wait for few seconds and check if the backup dir is created ? |
| -- If backup dir is not created , the backup thread is waiting for unlogged |
| -- op to finih. |
| -- Note: Not a 100% foolproof approach because checking for backupdir |
| -- might occur before backup thread gets into action. But I think |
| -- test will fail atleast on some systems, if backup is not waiting |
| -- for unlogged ops to complete. |
| -- case1 : simple database backup with unlogged ops pending. |
| set connection c2; |
| ij(C2)> -- index is a non-logged operation |
| create index idx1 on t1(a) ; |
| 0 rows inserted/updated/deleted |
| ij(C2)> set connection c1; |
| ij(C1)> -- make sure backup does not already exists at the backup location. |
| values removeDirectory('extinout/ulbackup1'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> values fileExists('extinout/ulbackup1'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C1)> async bthread1 'call SYSCS_UTIL.SYSCS_BACKUP_DATABASE( |
| ''extinout/ulbackup1'')' ; |
| ij(C1)> set connection c2; |
| ij(C2)> -- sleep for a while for the backup thread to |
| -- really get into the wait state |
| call sleep(1000); |
| 0 rows inserted/updated/deleted |
| ij(C2)> -- make sure backup did not really proceed, backup dir should not exist |
| values fileExists('extinout/ulbackup1'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C2)> -- rollback the unlogged op for backup to proceed. |
| rollback; |
| ij(C2)> set connection c1; |
| ij(C1)> -- wait for backup thread to finish the work. |
| wait for bthread1; |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- check if backup is created. |
| values fileExists('extinout/ulbackup1'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> commit; |
| ij(C1)> -- case2: simple backup call with the default wait for ulogged ops |
| set connection c2; |
| ij(C2)> create index idx1 on t1(a) ; |
| 0 rows inserted/updated/deleted |
| ij(C2)> set connection c1; |
| ij(C1)> -- make sure backup does not already exists at the backup location. |
| values removeDirectory('extinout/ulbackup2'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> values fileExists('extinout/ulbackup2'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C1)> async bthread1 |
| 'call SYSCS_UTIL.SYSCS_BACKUP_DATABASE(''extinout/ulbackup2'')'; |
| ij(C1)> set connection c2; |
| ij(C2)> -- sleep for a while for the backup thread to |
| -- really get into the wait state |
| call sleep(1000); |
| 0 rows inserted/updated/deleted |
| ij(C2)> -- make sure backup did not really proceed, backup dir should not exist |
| values fileExists('extinout/ulbackup2'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C2)> -- rollback the unlogged op for backup to proceed. |
| rollback; |
| ij(C2)> set connection c1; |
| ij(C1)> -- wait for backup thread to finish the work. |
| wait for bthread1; |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- check if backup is created. |
| values fileExists('extinout/ulbackup2'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> commit; |
| ij(C1)> --- case 3: log archive backup with with unlogged ops pending. |
| set connection c2; |
| ij(C2)> create index idx1 on t1(a) ; |
| 0 rows inserted/updated/deleted |
| ij(C2)> set connection c1; |
| ij(C1)> --make sure backup does not already exists at the backup location. |
| values removeDirectory('extinout/ulbackup3'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> values fileExists('extinout/ulbackup3'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C1)> async bthread1 |
| 'call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE( |
| ''extinout/ulbackup3'' , 1)' ; |
| ij(C1)> set connection c2; |
| ij(C2)> -- sleep for a while for the backup thread to |
| -- really get into the wait state |
| call sleep(1000); |
| 0 rows inserted/updated/deleted |
| ij(C2)> -- make sure backup did not really proceed, backup dir should not exist |
| values fileExists('extinout/ulbackup3'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C2)> -- rollback the unlogged op for backup to proceed. |
| rollback; |
| ij(C2)> set connection c1; |
| ij(C1)> -- wait for backup thread to finish the work. |
| wait for bthread1; |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- check if backup is created. |
| values fileExists('extinout/ulbackup3'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1); |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- case4 : log archive backup with the defailt wait for unlogged ops. |
| set connection c2; |
| ij(C2)> create index idx1 on t1(a) ; |
| 0 rows inserted/updated/deleted |
| ij(C2)> set connection c1; |
| ij(C1)> --make sure backup does not already exists at the backup location. |
| values removeDirectory('extinout/ulbackup4'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> values fileExists('extinout/ulbackup4'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C1)> async bthread1 |
| 'call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE( |
| ''extinout/ulbackup4'' , 1)' ; |
| ij(C1)> set connection c2; |
| ij(C2)> -- sleep for a while for the backup thread to |
| -- really get into the wait state |
| call sleep(1000); |
| 0 rows inserted/updated/deleted |
| ij(C2)> -- make sure backup did not really proceed, backup dir should not exist |
| values fileExists('extinout/ulbackup4'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| false |
| ij(C2)> -- commit the unlogged op for backup to proceed. |
| commit; |
| ij(C2)> set connection c1; |
| ij(C1)> -- wait for backup thread to finish the work. |
| wait for bthread1; |
| 0 rows inserted/updated/deleted |
| ij(C1)> -- check if backup is created. |
| values fileExists('extinout/ulbackup4'); |
| 1 |
| ---------------------------------------------------------------------------------------------------- |
| true |
| ij(C1)> call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1); |
| 0 rows inserted/updated/deleted |
| ij(C1)> |
