2

I have a mysqldump from a server running mysql Ver 14.14 Distrib 5.6.35, for Linux (x86_64) using EditLine wrapper centos 6 I am trying to import it to a server that is running mariadb mysql Ver 15.1 Distrib 10.1.22-MariaDB, for Linux (x86_64) using readline 5.1 That is running Centos7

I am constantly getting a syntax error like this ERROR 1064 (42000) at line 4908: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '),('5051888098585',2512,131872,359,'Pending','completed','0','1','2016-03-15 17' at line 1

The line number appears random, if i drop the tables and start the restore again it can fail again at a point before that line or after. Sometimes it almost makes it to the end. The one think that is constant is that the line is always a big insert query.

Similar questions here do not solve my problem. I have completely rebuild the server ,changed multiple mysqldump settings and my.cnf settings, nothing changes.

Current my.cnf

    [mysqld]
bind-address = ::
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd



max_allowed_packet =  1G
max_connections = 600
thread_cache_size = 16
query_cache_size = 64M
tmp_table_size= 512M
max_heap_table_size= 512M
wait_timeout=60

#Innodb Settings
innodb_file_per_table=1
innodb_buffer_pool_size = 25G
innodb_log_file_size = 2048M
innodb_flush_log_at_trx_commit = 0
innodb_file_format = Barracuda
innodb_flush_neighbors = 0

#Log

log-error =/var/log/error.log
tmpdir = /dev/shm

I have tried dozens of different settings for the import and for the dump nothing worked.These are the latest: For the dump

mysqldump -u admin -p`cat /etc/psa/.psa.shadow` --master-data=2 db_name --default-character-set=utf8 -c -Q  --result-file=dump.sql

For the import

mysql -uadmin -p`cat /etc/psa/.psa.shadow` db_name < dump.sql
O. Jones
  • 103,626
  • 17
  • 118
  • 172
Thomas Nikoleris
  • 172
  • 10

1 Answers1

2

If you don't override it, mysqldump generates vast INSERT statements that handle lots of rows at a time. It seems likely something's going wrong on your restore because of the length of the INSERT lines.

Try using the --net_buffer_length=8192 option on mysqldump when you create your dump file. It will generate shorter INSERT statements. Your dump file will be longer and will take longer to restore, but it may actually run to completion.

If that doesn't work, and you have time, try the --skip-opt option to skip all optimization.

See this: How to deal with enormous line lengths created by mysqldump

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172