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 -uUSERNAME -pPASSWORD --force < dump.sql
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
Tip: For production servers you can avoid table locking using if consistency is not a huge issue
mysqldump --skip-lock-tables ...
Importing data
Import data to TEST server (assuming name is 'sandbox')
mysql -uUSERNAME -pPASSWORD sandboxbase < dumpBase.sql mysql -uUSERNAME -pPASSWORD sandboxlive < dumpLive.sql mysql -uUSERNAME -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 -uUSERNAME -pPASSWORD sandboxbase mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tslive | mysql -uUSERNAME -pPASSWORD sandboxlive mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tstest | mysql -uUSERNAME -pPASSWORD sandboxtest
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
- 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;
- Repeat the "Rebuild views above"