MySQL Bugs: #37792: dead lock and slow insert

Bug #37792	dead lock and slow insert
Submitted:	2 Jul 2008 3:55	Modified:	25 Jul 2008 19:20
Reporter:	jove yee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	mysql community 5.0.41	OS:	Windows (XP sp2)
Assigned to:	Mikhail Izioumtchenko	CPU Architecture:	Any
Tags:	insert select on duplicate key update

Description:
Running an application reading excel data and insert it into Mysql table.

Using a vb.net program with mysql .Net connector version 5.1.6.

After connected to mysql via localhost(3306) , execution start insert row by row (20000++ to 40000++ records) 

the table has 2 key, 1 of the column using (select max(fld_name) + 1), on duplicate key update all the other field.
(insert into (key_1,key_2,fld_1,fld_2,fld_3) select 'key1_data','key2_data', max(fld_1) + 1, 'fld2_data','fld3_data' on duplicate key update fld_2 = values(fld_2), fld_3 = values(fld_3); )

the CPU processes of mysql-nt(task manager) is around 20+ to 50+ , until suddenly the whole processing speed drop to (1 query > 1 second) compare to previously (10 query < 2 second ). CPU process drop to 0+ to 5+ . try executing the query using query browser, the error return is "encounter deadlock, ErrorNr. 1213. insert query at the application side still performing and mysql not hang but very slow. 

 

   

    

How to repeat:

using this type of query 

(insert into (key_1,key_2,fld_1,fld_2,fld_3) select 'key1_data','key2_data', max(fld_1) + 1, 'fld2_data','fld3_data' on duplicate key update fld_2 = values(fld_2), fld_3 = values(fld_3); )

execute 1 query with data too long to fit in 1 particular field or
execute 1 query with syntax error in it.

use a long live connection to insert each query. after the error query executed, the whole process becomes very very slow.

Suggested fix:
at the client side, make sure every query sent to the server is valid. Escape all the ' " \ . and make sure the data is not longer than the field limit. 

i even restart the PC and try to insert again, but still very slow from the start before encountering any error query. 

the problem goes away after i expand the field size.

sorry ... the fix i recommended did not fix any issue, the execution speed still reduce a lot even though not more query error. 
the total records now is 55679 rows. inserting each record takes around 1 second each. Is this the normal speed for such a big table?

Many thanks for writing a bug report.
Do you enabled strict mode?
Which kind of datatyp has the columns where the data will get longer then the limit?

the strict mode is enable.

currently i still monitoring the updates (still running) . the insert query has no more error, however, execution time increase to about 6 second per insert (14726 or 22967) took more than 5 hours ( 1 single connection ).

the field that is over limit (exceeded the size) is varchar. 

thanks

MySQL 5.0.41 is quite old. Please try newer version. At the moment MySQL 5.0.51b is our newest version.

dear all

i found out the issue after testing the sql query part by part, then i notice it is the MAX() that is giving the server heavy processing ( > 20000 records). after i remove the MAX() it is back to high speed, thanks for your support.