Running into problems importing mysql data from confluence 5.10.8/mysql 5.5 to confluence 6.15/msyql

[Marga Genova]

Current specs:
CentOS 6.8
Confluence 5.10.8
MySQL 5.5.53

Upgrading to:
Centos 7.6
Confluence 6.15
MySQL 5.6.45

Hello, I've tried upgrading our confluence to the specs above following the instructions from this article: https://confluence.atlassian.com/doc/installing-confluence-on-linux-from-archive-file-255362363.html

However, when I try to import the data (data size is 5.85G) to mysql (mysql -p confluence < db.sql), It hangs and I get a "MySQL server has gone away" error. "ERROR 2006 (HY000) at line 1399: MySQL server has gone away" to be exact.

I've tried tuning /etc/my.cnf with the following:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
max_allowed_packet=64G
innodb_log_file_size=2GB
transaction-isolation=READ-COMMITTED
binlog_format=row
wait_timeout=6000
net_read_timeout=90
net_write_timeout=90
interactive_timeout=300
connect_timeout=90

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=1000

Restarted mysql after changes to my.cfn and tried the following command:

mysql -p confluence < db.sql --verbose --force --wait --reconnect

I can see the data importing for at least 30 minutes and in the end, it just crashes. I get the following error messages:

ERROR 2006 (HY000) at line 9698: MySQL server has gone away
--------------
/*!40000 ALTER TABLE `users` ENABLE KEYS */
--------------

ERROR 2006 (HY000) at line 9699: MySQL server has gone away
--------------
UNLOCK TABLES
--------------

ERROR 2006 (HY000) at line 9700: MySQL server has gone away
--------------
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
--------------

ERROR 2006 (HY000) at line 9701: MySQL server has gone away
--------------
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */
--------------

ERROR 2006 (HY000) at line 9703: MySQL server has gone away
--------------
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
--------------

ERROR 2006 (HY000) at line 9704: MySQL server has gone away
--------------
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */
--------------

ERROR 2006 (HY000) at line 9705: MySQL server has gone away
--------------
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
--------------

ERROR 2006 (HY000) at line 9706: MySQL server has gone away
--------------
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */
--------------

ERROR 2006 (HY000) at line 9707: MySQL server has gone away
--------------
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
--------------

ERROR 2006 (HY000) at line 9708: MySQL server has gone away
--------------
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
--------------

ERROR 2006 (HY000) at line 9709: MySQL server has gone away

How do I import a large data to mysql? Is there any configurations I've missed? Any suggestions or recommendations are appreciated. Thank you in advance.

[Alexis Robert]

Hello @Marga Genova , 

I've already done imports of larger files in MySQL without any issues, maybe it's a problem with the MySQL server sizing ? How much memory do you have on this server ? 

Also, I think the max_allowed_packet_size setting should be 256M, as per the documentation : https://confluence.atlassian.com/confkb/exceeds-max-allowed-packet-for-mysql-179443425.html

Can you try changing this parameter and see if it works ? 

Thanks, 

--Alexis