It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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.

1 answer

0 votes
Alexis_Robert Community Leader Oct 14, 2019

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Confluence

What project did you transition or start on Confluence with the shift to remote work?

It’s been great to hear from fellow users over the last few weeks about the best tips and fun moments you’ve had working on Confluence since the transition to working remote. I’d love to keep the c...

109 views 3 7
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you