Lost connection to MySQL server during query

Lost connection to MySQL server during query

Postby Nimoidal » Fri 28 Nov 2008 09:36

Hi guys, I'm using 4.85.35 version.

I don't understand why but sometimes when I run a backup with mysqldump i get this error.

Lost connection to MySQL server during query

en CoreLab.MySql.a9.d(Byte[] A_0, Int32 A_1, Int32 A_2)
en CoreLab.MySql.a9.j()
en CoreLab.MySql.c.a(av A_0, Byte[] A_1, Int32 A_2, Boolean A_3)
en CoreLab.MySql.c.a(Byte[] A_0, Int32 A_1, Boolean A_2)
en CoreLab.MySql.z.e()
en CoreLab.MySql.z.o()
en CoreLab.MySql.MySqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
en CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
en System.Data.Common.DbCommand.ExecuteReader()
en CoreLab.Common.DbCommandBase.ExecuteNonQuery()
en CoreLab.MySql.MySqlTransaction..ctor(MySqlConnection A_0, IsolationLevel A_1)
en CoreLab.MySql.MySqlInternalConnection.BeginTransaction(IsolationLevel il)
en CoreLab.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationLevel)
en System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
en CoreLab.MySql.MySqlConnection.BeginTransaction(IsolationLevel il)
en CoreLab.MySql.MySqlConnection.BeginTransaction()
en CoreLab.MySql.MySqlDump.Backup(TextWriter writer)
en CoreLab.MySql.MySqlDump.Backup(String fileName)
en NIM.ClsBaseConexion.CreateBackupDB(String arch)



Also I get an Innodb status when this happen



=====================================
081127 21:11:52 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 41, signal count 41
Mutex spin waits 0, rounds 94, OS waits 4
RW-shared spins 72, OS waits 36; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 297142
Purge done for trx's n:o < 0 297105 undo n:o < 0 0
History list length 15
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 297141, not started, OS thread id 2632
MySQL thread id 2, query id 3688 localhost 127.0.0.1 root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
541 OS file reads, 375 OS file writes, 229 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 377789, used cells 7240, node heap has 8 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 93265500
Log flushed up to 0 93265500
Last checkpoint at 0 93265500
0 pending log writes, 0 pending chkp writes
85 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 107355892; in additional pool allocated 2072832
Buffer pool size 5824
Free buffers 5321
Database pages 495
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 495, created 0, written 256
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 1260, state: waiting for server activity
Number of rows inserted 67, updated 33, deleted 0, read 10454611
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================



And then my code:


Dim backup As New CoreLab.MySql.MySqlDump()
AddHandler backup.Progress, AddressOf MysqlDump_Progress2
backup.Connection = Me.MySQLConexion
backup.Database = Me.DataBase
backup.UseExtSyntax = False
backup.IncludeDrop = True
backup.Mode = CoreLab.MySql.DumpMode.All
backup.Backup(arch)
backup.Dispose()


Also, if I use Use extsyntax = true to reduce the backup, I noticed that progress event didn't get correct the progress parameter because I suppose he reads a big only one insert instead of multiple inserts so the min parameter=0 and the max parameter are 1 instead of number of rows.

But this is an estethic problem, the big problem is first. The disconnection doesn't occurs always, but when occur it is always doing a backup with mysqldump.

Any Idea?

P.D. Connection linked to mysqldump are a localhost connection. mysql server 5.0 and windows vista or xp prof.

Thanks a lot for your time
Nimoidal
 
Posts: 1
Joined: Fri 28 Nov 2008 09:21

Postby Shalex » Mon 01 Dec 2008 09:38

1. The "Lost connection to MySQL server during query" issue can occur when the MySqlConnection object is used asynchronously. Make sure it is not used in different threads.

If the problem persists, please provide me (alexsh*devart*com) with a small test project, DDL and DML script to create database objects. We will try to reproduce the issue and notify you about the results.

2. The Progress event occurs when MySqlDump processes next database object or SQL statement. If you set the UseExtSyntax property to true, there is only one Insert statement (with several parameters). That's why the max parameter is 1 (not the number of inserting rows). It's a feature of the MySqlDump implementation.
Shalex
Devart Team
 
Posts: 7194
Joined: Thu 14 Aug 2008 12:44

Postby sumit » Thu 04 Dec 2008 20:12

You can also try increasing the CommandTimeout value (or set it to 0). Remember this is not the ConnectionTimeout, this is CommandTimeout
sumit
 
Posts: 62
Joined: Wed 03 Jan 2007 22:23


Return to dotConnect for MySQL