Reloading database dump stops suddenly ... ?

[unixman]

Man, this is a weird one.

I run nightly scripts to backup my database - basically, a rolling mysqldump.

I wanted to create a test forum, so I created a new database, and proceed to mysql -uxyz ... < /path/to/backup.dump.

Interesting thing. It stops about midway through my "post" table. Just stops. No errors, no nothing. The production post table is about 178MB, and my newly created post table is about 88MB.

Anyone have any ideas?

[eva2000]

1. check mysql error log for any messages
2. post you my.cnf file contents

[unixman]

Mysql error log is clean. my.cnf below:


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = connect_timeout=20
set-variable = max_connections=350
set-variable = key_buffer=64M
set-variable = join_buffer=16
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=16M
set-variable = record_buffer=12
set-variable = myisam_sort_buffer_size=16M
set-variable = thread_cache_size=256
set-variable = thread_stack=256K

# Set to number of CPUs * 2
set-variable = thread_concurrency=4


Box is a Dual Athlon 1.5Ghz setup, with 3GB of DDR RAM.

Cheers.
Scott

[eva2000]

it's because your buffers and settings are too low

change it to below and restart mysql


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = connect_timeout=20
set-variable = max_connections=350
set-variable = key_buffer=64M
set-variable = join_buffer=2M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1024
set-variable = sort_buffer=4M
set-variable = record_buffer=2M
set-variable = myisam_sort_buffer_size=16M
set-variable = thread_cache_size=256
set-variable = thread_stack=256K

# Set to number of CPUs * 2
set-variable = thread_concurrency=4

[unixman]

Wow - I just realized that I left the "M" out of a couple of key settings (i.e. 16, rather than 16M). Not sure if this is a problem or not. I think I recall "M" being the default - perhaps not. I'll add that. Otherwise, the only change I see that you made was increasing the table_cache, which I doubt had anything to do with it. You also lowered several settings, rather than increasing them. I assume that what you posted was boilerplate. I feel like my settings were already jacked up a bit.

Confused.... :)

[eva2000]

take a closer look ;)





set-variable = max_allowed_packet=16M

;)

[unixman]

I saw that, but in your original post, you said my "buffers and settings were too low", which made me think all sorts of things were off. :)

I've plugged it in and will give it another shot a bit later. Thanks much for the help!

Cheers.
Scott

[unixman]

No dice. Same thing. :( And nothing in the mysql error log, either.

Current my.cnf is:


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = connect_timeout=20
set-variable = max_connections=350
set-variable = key_buffer=64M
set-variable = join_buffer=16M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1024
set-variable = sort_buffer=16M
set-variable = record_buffer=12M
set-variable = myisam_sort_buffer_size=16M
set-variable = thread_cache_size=256
set-variable = thread_stack=256K

# Set to number of CPUs * 2
set-variable = thread_concurrency=4

[eva2000]

hmm the only time i have come across this is when the sql dump file was corrupt due to a bad hard drive corrupting stuff.. and hence when sql was imported as it came to the corrupt entry to just killed the rest of the procedure

i'd try

1. check live forum database for any corruption
2. repair any corruption with live database
3. do a new import with another fresh mysql dump of your live forum (checked already for corruption)