0

I'm doing a import in mysql with this bash script:

ddl="set names utf8; "
ddl="$ddl set global net_buffer_length=1000000;"
ddl="$ddl set global net_write_timeout=1000000;"
ddl="$ddl set global max_allowed_packet=1000000000; "
ddl="$ddl set global key_buffer_size=1000000000; "
ddl="$ddl set global connect_timeout=100000; "
ddl="$ddl set global wait_timeout=100000; "
ddl="$ddl SET foreign_key_checks = 0; "
ddl="$ddl SET UNIQUE_CHECKS = 0; "
ddl="$ddl SET AUTOCOMMIT = 0; "
ddl="$ddl USE ${database}; "
ddl="$ddl source $reducedfile; "
ddl="$ddl SET foreign_key_checks = 1; "
ddl="$ddl SET UNIQUE_CHECKS = 1; "
ddl="$ddl SET AUTOCOMMIT = 1; "
ddl="$ddl COMMIT ; "

echo "Import started"

time mysql -h 127.0.0.1 -u root -proot -e "$ddl"

I have table which contains 15 columns, no indexes (only a primary key) and around 350k records. a simple log table.. the info is very basic just ints and dates.

When i try to import this table i'm getting the famous message 'Lost connection to MySQL server during query'. Does anybody know where i can find the error why mysql is aborting? there is nothing useful in /var/log/mysql/error.log only that it's restarting.

When i reduce the amount of records to 20 it imports just fine. Not sure where to look now to find out whats the problem.. the table is to large for my config is the only conclusion for now but i've set all the params before import and this is my.cnf

[mysqld]
max_connections = 1000
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 256M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
Daniel
  • 225
  • 3
  • 14
  • This would be a lot less messy if you declared a multi-line string instead of taking the same string and re-defining it with `$ddl` in the string repeatedly. [Examples here](http://stackoverflow.com/questions/23929235/multi-line-string-with-extra-space-preserved-indentation). – tadman Jul 27 '16 at 16:48
  • thanks for the link @tadman, i agree its messy and would be nice if i didn't need to add these settings at all. This code comes straight from a example on the net on how to increase these settings to handle a big import so i though i give it a go. – Daniel Jul 27 '16 at 17:02
  • Why not load in via `LOAD DATA` using a CSV source instead? – tadman Jul 27 '16 at 17:07
  • Because i receive these as dumps as sql files, coming from a mysqldump. And i know we can skip tables during mysqldump but i don't create these dumps myself and it should not be a problem to import a table with 350k records right? – Daniel Jul 28 '16 at 08:04

0 Answers0