Hi Dusan,
You replied to a forum post of mine on mysql.com yeah? ;)
I have tried adjusting the max_allowed_packet on both the server and
client. Both are set to 1G now (apparently the highest value
accepted) even though each row is no larger than 100M at very most.
I am thinking this may have something to do with --extended-insert.
So rather than having all of data in an extended insert I have tried
disabling this feature with --extended-insert=0, --extended-
insert=false, --skip-extended-insert (not all at once obviously)...
am I doing this correctly? Mixed results when I search google for
answers. I don't really care at this stage if backup/restore times
are reduced with this feature disabled, as long as I can get an
accurate backup.
I also set --net_buffer_length=800M; in theory --extended-insert will
only create queries up to this value, so if I keep it lower than the
--max_allowed_packet value it should all be sweet. Still no cigar
unfortunately.
On 19/06/2007, at 7:08 PM, Dušan Pavlica wrote:
Try to look for Lost connection error in MySQL manual and it can
give your some hints like
http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
Dusan
Hartleigh Burton napsal(a):
Hi All,
I have a database which is currently at ~10GB in it's test phase.
It is containing uncompressed audio and is expected to reach 1.5TB
in no time at all. I am just running some backup tests and I have
been having lots of problems creating an accurate backup.
I have tried both MySQL Administrator & mysqldump, both
applications drop out on the same table, the table `trackdata`
which contains ~9GB worth of data. There is no single row any
larger than 50MB, most average around 40MB. Windows 2000 Server,
MySQL v5.0.37-community-nt and all tables are InnoDB.
If anyone can help me out with this problem the assistance is
greatly appreciated. I have scoured google and various other
sources and not found much information that has been useful to me.
I hope I have enough info below... if more is required let me know.
mysqldump example
P:\>mysqldump -u username -p mraentertainment >
mraentertainment.sql --opt
--verbose --max_allowed_packet=500M --hex-blob --single_transaction
--net_buffer_length=100M
Enter password: ******
-- Connecting to localhost...
-- Retrieving table structure for table albums...
-- Sending SELECT query...
...
-- Retrieving rows...
-- Retrieving table structure for table trackdata...
-- Sending SELECT query...
-- Retrieving rows...
mysqldump: Error 2013: Lost connection to MySQL server during
query when dumping
table `trackdata` at row: 1
my.ini configuration file
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
log-bin=itd002-bin
server-id=1
port=3306
wait_timeout=86400
max_allowed_packet=100M
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-
mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=77M
thread_cache_size=8
#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=154M
key_buffer_size=130M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
#skip-innodb
innodb_additional_mem_pool_size=6M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3M
innodb_buffer_pool_size=252M
innodb_log_file_size=126M
innodb_thread_concurrency=8
Regards,
Hartleigh Burton
Resident Geek
MRA Entertainment Pty Ltd
5 Dividend St | Mansfield | QLD 4122 | Australia
Phone: (07) 3457 5041
Fax: (07) 3349 8806
Mobile: 0421 646 978
www.mraentertainment.com
Internal Virus Database was built: Never
Checked by MAC OSX... we don't get viruses!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
Regards,
Hartleigh Burton
Resident Geek
MRA Entertainment Pty Ltd
5 Dividend St | Mansfield | QLD 4122 | Australia
Phone: (07) 3457 5041
Fax: (07) 3349 8806
Mobile: 0421 646 978
www.mraentertainment.com
Internal Virus Database was built: Never
Checked by MAC OSX... we don't get viruses!
