# Moving database from live to test

## <span class="mw-headline" id="bkmrk-option%3A-database-cre-1">Option: Database creation</span>

I can there is no database already please perform the following steps (assuming new db is named "sandbox")

```bash
mysql -uUSERNAME -pPASSWORD
```

```mysql
CREATE DATABASE IF NOT EXISTS sandboxbase; 
CREATE DATABASE IF NOT EXISTS sandboxtest;
CREATE DATABASE IF NOT EXISTS sandboxlive;
CREATE USER 'sandboxroot'@'localhost' IDENTIFIED BY 'TempusServaFTW!';
GRANT ALL PRIVILEGES ON sandboxlive.* TO 'sandboxroot'@'localhost';
GRANT ALL PRIVILEGES ON sandboxtest.* TO 'sandboxroot'@'localhost';
GRANT ALL PRIVILEGES ON sandboxbase.* TO 'sandboxroot'@'localhost';
FLUSH PRIVILEGES;
```

## <span class="mw-headline" id="bkmrk-database-transfer-1">Database transfer</span>

Choose ONE of the options below that best suits your requirements.

### <span id="bkmrk-"></span><span class="mw-headline" id="bkmrk-database-transfer%3A-d-1">Database transfer: Different MySQL server / Same schema name</span>

Export data from LIVE server

```bash
mysqldump -uUSERNAME -pPASSWORD --databases tsbase tslive tstest > dump.sql
```

Import data to TEST server

```bash
mysql -u USERNAME -pPASSWORD --force < dump.sql
```

If port 3306 is open you might consider network streaming and using pipes. Note that a remote user is needed on the other server.

```bash
mysqldump ... | mysql ... -h SERVERNAME
```

```bash
mysqldump -uLOCAL_USR -pLOCAL_PWD --databases tsbase tslive tstest | mysql -u REMOTE_USR -pREMOTE_PWD -h SERVERNAME --force
```

### <span id="bkmrk--1"></span><span class="mw-headline" id="bkmrk-database-transfer%3A-s-1">Database transfer: Same MySQL server / Different schema name</span>

#### <span class="mw-headline" id="bkmrk-dumping-data-1">Dumping data</span>

Export data from LIVE server

```bash
mysqldump -uUSERNAME -pPASSWORD tsbase > dumpBase.sql
mysqldump -uUSERNAME -pPASSWORD tslive > dumpLive.sql
mysqldump -uUSERNAME -pPASSWORD tstest > dumpTest.sql
```

For production servers you can avoid table locking using if consistency is not a huge issue (data can be changed and loose internal integrity)

```bash
mysqldump --skip-lock-tables ...
```

If you are handling BLOB data larger than 24mB consider increasing the buffer

```bash
mysqldump --max-allowed-packet=1G ...
```

#### <span class="mw-headline" id="bkmrk-importing-data-1">Importing data</span>

Import data to TEST server (assuming name is 'sandbox')

```bash
mysql -u USERNAME -pPASSWORD sandboxbase < dumpBase.sql
mysql -u USERNAME -pPASSWORD sandboxlive < dumpLive.sql
mysql -u USERNAME -pPASSWORD sandboxtest < dumpTest.sql
```

##### <span class="mw-headline" id="bkmrk-errors-during-import-1">Errors during import</span>

Using MySQL 5.6 or higher you might get an error like this

```
Error code: 1118 Row size too large (> 8126).
```

Resolve by setting this configuration in the MySQL configuration file (see below)

```ini
innodb_strict_mode = 0
```

##### <span class="mw-headline" id="bkmrk-errors-during-import-3">Errors during import</span>

Using MySQL 5.6 or higher you might get an error like this

```
ERROR 1067 (42000) at line ... : Invalid default value for ...
```

Resolve by setting this configuration in the MySQL configuration file (see below)

```ini
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
```

##### <span class="mw-headline" id="bkmrk-errors-during-import-5">Errors during import 2</span>

Using MySQL 5.6 or higher you might get syntax errors like

```
"(0) NULL DEFAULT NULL"
```

Remove the incorrect syntax in dump file

```bash
sed -i 's/(0) / /g' tslive.sql
```

##### <span class="mw-headline" id="bkmrk-editing-the-mysql-co-1">Editing the MySQL configuration file</span>

Conf file may vary but is often found at

```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```

#### <span class="mw-headline" id="bkmrk-export-and-importing-1">Export and importing in one go</span>

It is possible to pipe the output from dump directly to the mysql import

```bash
mysqldump ... | mysql ...
```

For test and live on same machine the full live to test overwrite is

```bash
mysqldump --skip-lock-tables --max-allowed-packet=1G -uUSERNAME -pPASSWORD tsbase | mysql -u USERNAME -pPASSWORD sandboxbase
mysqldump --skip-lock-tables --max-allowed-packet=1G -uUSERNAME -pPASSWORD tslive | mysql -u USERNAME -pPASSWORD sandboxlive
mysqldump --skip-lock-tables --max-allowed-packet=1G -uUSERNAME -pPASSWORD tstest | mysql -u USERNAME -pPASSWORD sandboxtest
```

### <span class="mw-headline" id="bkmrk-partial-transfers-wi-1">Partial transfers without files</span>

In some situations we want to move data around without attached files. Example usage: Frequent overwrite of test environment with data from live.

#### <span class="mw-headline" id="bkmrk-windows-partial-tran-1">Windows partial transfer</span>

Assuming that password for the root account is 'TempusServa' and the scheme is tsXXXX the dump commands are.

```bash
mysqldump --skip-lock-tables -q -Q -u root -pTempusServa tsbase > c:\temp\tsbase.sql
```

```bash
for /F %A in ('mysql -u root -pTempusServa tslive -Bse "SELECT table_name FROM information_schema.tables WHERE table_schema = 'tslive' AND table_name NOT LIKE '%_file'"') DO @Echo Dumping %A & CALL mysqldump --skip-lock-tables -q -Q -u root -pTempusServa tslive %A >> c:\temp\tslive.sql
```

```bash
for /F %A in ('mysql -u root -pTempusServa tstest -Bse "SELECT table_name FROM information_schema.tables WHERE table_schema = 'tstest' AND table_name NOT LIKE '%_file'"') DO @Echo Dumping %A & CALL mysqldump --skip-lock-tables -q -Q -u root -pTempusServa tstest %A >> c:\temp\tstest.sql
```

For even faster transfers consider excluding the accesslogs too using

```bash
for /F %A in ('mysql -u root -pTempusServa tslive -Bse "SELECT table_name FROM information_schema.tables WHERE table_schema = 'tslive' AND table_name NOT LIKE '%_file' AND table_name NOT LIKE '%_accesslog'"') DO @Echo Dumping %A & CALL mysqldump --skip-lock-tables -q -Q -u root -pTempusServa tslive %A >> c:\temp\tslive.sql
```

## <span class="mw-headline" id="bkmrk-configuration-change-1">Configuration changes</span>

Important information: The following script will update the database **tslive**, for the sandbox copy example please **USE sandboxlive;**.

Run the following commands **before** you start the server

```bash
mysql -uUSERNAME -pPASSWORD
```

```mysql
USE tslive;
UPDATE systempolicy SET PolicyValue = 'false' WHERE PolicyName='serviceAutostart';
UPDATE systempolicy SET PolicyValue = 'true' WHERE PolicyName='smtpTestMode';
UPDATE systempolicy SET PolicyValue = 'localhost' WHERE PolicyName='smtpServer';
UPDATE systempolicy SET PolicyValue = 'localhost' WHERE PolicyName='applicationServer';
UPDATE systempolicy SET PolicyValue = 'http://localhost' WHERE PolicyName='applicationURL';
UPDATE systempolicy SET PolicyValue =  WHERE PolicyName LIKE 'folder%';
UPDATE systempolicy SET PolicyValue = 'C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps' WHERE PolicyName='applicationBasePath';
```

Note: The following changes can be replaced by setting the values in the application deployment descriptor, starting from version 2500

## <span class="mw-headline" id="bkmrk-rebuild-views-1">Rebuild views</span>

Ensure that views exist by rebuilding them

```
Designer > Modules > Admin services > RebuildViews
```

## <span class="mw-headline" id="bkmrk-troubleshooting-1">Troubleshooting</span>

### <span class="mw-headline" id="bkmrk-server-not-started-1">Server not started</span>

- Check application server log files for information
- Tomcat 
    - Check xml descriptor is found in \[TOMCAT\]/conf/Catalina/localhost
    - Check war file is unpacked correctly in \[TOMCAT\]/webapps

### <span class="mw-headline" id="bkmrk-cannot-select-fieldt-1">Cannot select fieldtype when adding or editing fields</span>

1. Run the following SQL command

```mysql
USE tsbase;
DROP VIEW IF EXISTS viewdatatypeselector;
CREATE VIEW viewdatatypeselector AS
 SELECT
   systemdatatype.FeltTypeID AS `FeltTypeID`,
   concat(systemdatatypepackage.TypePrefix,': ',systemdatatype.FeltType) AS `FeltType` 
 FROM systemdatatype 
 JOIN systemdatatypepackage 
   ON systemdatatype.PackageID = systemdatatypepackage.PackageID 
 WHERE systemdatatype.IsSelectable = 1)
 ORDER BY systemdatatypepackage.SortOrder, systemdatatype.FeltType;
```

1. Repeat the "Rebuild views above"

### <span id="bkmrk--3"></span><span class="mw-headline" id="bkmrk-moving-parts-of-an-e-1">Moving parts of an existing installation (application, no data)</span>

Moving part of solution

- Create a new database
- Copy all tables starting with "form"
- Remove all unwanted entries
- Dump the full database
- Restore database on target system

After deleting records you might want to clean up the foreign keys constraints

```mysql
DELETE FROM formconfig WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formfield WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formfieldblock WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formfieldcategory WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formfieldlookup WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM forminterface WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formpage WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formpermission WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formstatus WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formstatusaction WHERE SagID NOT IN (SELECT SagID FROM form);
DELETE FROM formstatusflow WHERE SagID NOT IN (SELECT SagID FROM form);
```