Moving database from live to test - Revision history

Kpe: /* Export and importing in one go */

2024-05-16T20:42:18Z

Export and importing in one go

← Older revision		Revision as of 22:42, 16 May 2024
Line 104:		Line 104:
	For test and live on same machine the full live to test overwrite is		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 --max-allowed-packet=1G -uUSERNAME -pPASSWORD tsbase \| mysql -u USERNAME -pPASSWORD sandboxbase
	mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tslive \| mysql -u USERNAME -pPASSWORD sandboxlive		mysqldump --skip-lock-tables --max-allowed-packet=1G -uUSERNAME -pPASSWORD tslive \| mysql -u USERNAME -pPASSWORD sandboxlive
	mysqldump --skip-lock-tables -uUSERNAME -pPASSWORD tstest \| mysql -u USERNAME -pPASSWORD sandboxtest		mysqldump --skip-lock-tables --max-allowed-packet=1G -uUSERNAME -pPASSWORD tstest \| mysql -u USERNAME -pPASSWORD sandboxtest

	=== Partial transfers without files ===		=== Partial transfers without files ===

2024-05-16T20:41:45Z

Dumping data

← Older revision		Revision as of 22:41, 16 May 2024
Line 43:		Line 43:

	mysqldump --skip-lock-tables ...		mysqldump --skip-lock-tables ...

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

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

	==== Importing data ====		==== Importing data ====

2022-11-16T10:56:10Z

Cannot select fieldtype when adding or editing fields

← Older revision		Revision as of 12:56, 16 November 2022
Line 171:		Line 171:

	# Repeat the "Rebuild views above"		# Repeat the "Rebuild views above"


			=== Moving parts of an existing installation (application, no data) ===
			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

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

2021-12-10T10:55:15Z

44 revisions imported

← Older revision	Revision as of 12:55, 10 December 2021
(No difference)

2021-02-24T06:17:01Z

Errors during import 2

← Older revision		Revision as of 08:17, 24 February 2021
Line 80:		Line 80:
	"(0) NULL DEFAULT NULL"		"(0) NULL DEFAULT NULL"

	Remove the incorrect syntax		Remove the incorrect syntax in dump file

	sed -i 's/(0) ~~NOT NULL~~//g' ~~demolive.sql~~		sed -i 's/(0) / /g' tslive.sql
	~~sed -i 's/(0) NULL//g' demolive~~.sql

	===== Editing the MySQL configuration file =====		===== Editing the MySQL configuration file =====

2021-02-24T05:43:37Z

Errors during import 2

← Older revision		Revision as of 07:43, 24 February 2021
Line 82:		Line 82:
	Remove the incorrect syntax		Remove the incorrect syntax

	sed -i 's/(0) NULL ~~DEFAULT~~ NULL/ ~~DEFAULT NULL~~/g' demolive.sql		sed -i 's/(0) NOT NULL//g' demolive.sql
			sed -i 's/(0) NULL//g' demolive.sql

	===== Editing the MySQL configuration file =====		===== Editing the MySQL configuration file =====

2021-02-24T05:41:04Z

Errors during import

← Older revision		Revision as of 07:41, 24 February 2021
Line 72:		Line 72:
	sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION



			===== Errors during import 2 =====

			Using MySQL 5.6 or higher you might get syntax errors like

			"(0) NULL DEFAULT NULL"

			Remove the incorrect syntax

			sed -i 's/(0) NULL DEFAULT NULL/ DEFAULT NULL/g' demolive.sql

	===== Editing the MySQL configuration file =====		===== Editing the MySQL configuration file =====

2017-02-15T09:47:20Z

Database transfer: Same MySQL server / Different schema name

← Older revision		Revision as of 11:47, 15 February 2017
Line 58:		Line 58:
	Error code: 1118 Row size too large (> 8126).		Error code: 1118 Row size too large (> 8126).

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

	innodb_strict_mode = 0		innodb_strict_mode = 0
Line 68:		Line 68:
	ERROR 1067 (42000) at line ... : Invalid default value for ...		ERROR 1067 (42000) at line ... : Invalid default value for ...

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

	sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2017-02-15T09:46:44Z

Database transfer: Same MySQL server / Different schema name

@@ Line 61: / Line 61: @@
    innodb_strict_mode = 0
 ===== Errors during import =====
@@ Line 76: / Line 71: @@
     sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 ==== Export and importing in one go ====

2017-02-15T09:45:30Z

Errors during import

← Older revision		Revision as of 11:45, 15 February 2017
Line 65:		Line 65:

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


			===== Errors during import =====

			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

			sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

	==== Export and importing in one go ====		==== Export and importing in one go ====