Moving database from live to test

Option: Database creation

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

 mysql -uUSERNAME -pPASSWORD

 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';

Database transfer

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

Database transfer: Different MySQL server / Same schema name

Export data from LIVE server

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

Import data to TEST server

 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.

 mysqldump ... | mysql ... -h SERVERNAME

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

Database transfer: Same MySQL server / Different schema name

Dumping data

Export data from LIVE server

 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)

 mysqldump --skip-lock-tables ...

Importing data

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

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

Export and importing in one go

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

 mysqldump ... | mysql ...

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

 mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tsbase | mysql -u USERNAME -pPASSWORD sandboxbase
 mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tslive | mysql -u USERNAME -pPASSWORD sandboxlive
 mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tstest | mysql -u USERNAME -pPASSWORD sandboxtest

Partial transfers without files

Windows partial transfer

Assuming that pasword for the root account is 'TempusServa' the dump scripts are.

 mysqldump -q -Q -u root -pTempusServa tsbase > c:\temp\tsbase.sql

 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 -q -Q -u root -pTempusServa tslive %A >> c:\temp\tslive.sql

 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 -q -Q -u root -pTempusServa tstest %A >> c:\temp\tstest.sql

Configuration changes

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

Run the following commands before you start the server

 mysql -uUSERNAME -pPASSWORD

 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

Rebuild views

Ensure that views exist by rebuilding them

 Designer > Modules > Admin services > RebuildViews

Troubleshooting

Server not started

• 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

Cannot select fieldtype when adding or editing fields

1. Run the following SQL command

 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"