MySQL Bugs: #13893: INSERTION BLOCKED (WITH TRIGGER) MEANWHILE A BIG SELECT ON THE TABLE IS RUNNING

Bug #13893	INSERTION BLOCKED (WITH TRIGGER) MEANWHILE A BIG SELECT ON THE TABLE IS RUNNING
Submitted:	10 Oct 2005 10:59	Modified:	24 May 2006 12:17
Reporter:	ANGEL MARTIN	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.0.13/5.0.16 BK source	OS:	Windows (WINXP/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The conditions to have the problems are:
- Table A has a trigger, and this trigger inserts its values at another table.
- One connection (Con1) in READ-COMMITTED, with AUTOCOMMIT ON. A Select on table A is in progress, in which a lot of rows are scanned, and group by is done in the sentence.
- One connection (Con2) in REPEATABLE-REA, with AUTOCOMMIT OFF. A Insert on table A is done after the Select sentence have started in Con1.
- The state of Con2 is locked, meanwhile the sentence Select on Con1 is in progress.
- When Select has ended, the Insert is done.

If I repeat the same process, but without the Trigger on table A, there is no lock    
at the insert, and the Insert is finished at the moment, meanwhile the Select is in progress.

How to repeat:
Every time I put the trigger on the table.

The deamon used for the test is MYSQLD-MAX-NT.EXE

One of the files needed for the problem

Attachment: pfpreev.zip (application/zip, text), 201.81 KiB.

Second file ot the compressed file wrkcomisics (2/9)

Attachment: wrkcomisics.part002.rar (application/octet-stream, text), 195.31 KiB.

third file ot the compressed file wrkcomisics (3/9)

Attachment: wrkcomisics.part003.rar (application/octet-stream, text), 195.31 KiB.

fourth file ot the compressed file wrkcomisics (4/9)

Attachment: wrkcomisics.part004.rar (application/octet-stream, text), 195.31 KiB.

fifth file ot the compressed file wrkcomisics (5/9)

Attachment: wrkcomisics.part005.rar (application/octet-stream, text), 195.31 KiB.

sixth file ot the compressed file wrkcomisics (6/9)

Attachment: wrkcomisics.part006.rar (application/octet-stream, text), 195.31 KiB.

seventh file ot the compressed file wrkcomisics (7/9)

Attachment: wrkcomisics.part007.rar (application/octet-stream, text), 195.31 KiB.

eighth file ot the compressed file wrkcomisics (8/9)

Attachment: wrkcomisics.part008.rar (application/octet-stream, text), 195.31 KiB.

nineth file ot the compressed file wrkcomisics (9/9)

Attachment: wrkcomisics.part009.rar (application/octet-stream, text), 150.48 KiB.

wrkcash table (1/23)

Attachment: wrkcash.part0001.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (2/23)

Attachment: wrkcash.part0002.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (3/23)

Attachment: wrkcash.part0003.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (4/23)

Attachment: wrkcash.part0004.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (5/23)

Attachment: wrkcash.part0005.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (6/23)

Attachment: wrkcash.part0006.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (7/23)

Attachment: wrkcash.part0007.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (8/23)

Attachment: wrkcash.part0008.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (9/23)

Attachment: wrkcash.part0009.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (10/23)

Attachment: wrkcash.part0010.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (11/23)

Attachment: wrkcash.part0011.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (12/23)

Attachment: wrkcash.part0012.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (13/23)

Attachment: wrkcash.part0013.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (14/23)

Attachment: wrkcash.part0014.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (15/23)

Attachment: wrkcash.part0015.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (16/23)

Attachment: wrkcash.part0016.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (17/23)

Attachment: wrkcash.part0017.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (18/23)

Attachment: wrkcash.part0018.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (19/23)

Attachment: wrkcash.part0019.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (20/23)

Attachment: wrkcash.part0020.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (21/23)

Attachment: wrkcash.part0021.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (22/23)

Attachment: wrkcash.part0022.rar (application/octet-stream, text), 195.31 KiB.

wrkcash table (23/23)

Attachment: wrkcash.part0023.rar (application/octet-stream, text), 117.05 KiB.

More tables needed for the problem.

Attachment: MORETABLES.zip (application/zip, text), 36.07 KiB.

Sentences in which there is the problem

Attachment: sentences.txt (text/plain), 2.65 KiB.

I was unable for to expand the rar files, could you please
provide them on only one file and upload it at:

ftp://ftp.mysql.com:/pub/mysql/upload

with a name like bug13893.*

Thanks in advance.

I've been trying to send the files via FTP, but I couldn't.
I can connect to ftp.mysql.com, and later go to the directory pub/mysql/upload.
When I try to copy the file appears the error: 553 Could not create file.
The first time we tried to copy the file, it started to copy, but it was aborted.

Please, could you help me in order to have the files to be transmitted to you?

Thanks in advance for your help and ccoperation.

Best Regards

Angel Martin

I'll send you the insert that should be done on table WRKCOMISICS, when the insert on WRKCASH table has no trigger. In this way you can check that inserting in wrkcash table and later in wrkopta table through a sentences works, but it doesn't work if the insert at wrkopta is done through a trigger.

The sentence is :
INSERT INTO WRKCOMISICS VALUES ('AE','ICSOFT    ','ICS','ANGELXP','9999999','MANOL32','AA1',4,18,4000,NULL,'99',0,'99',0,'11','I',0,60,4000,'001',CURDATE(),CURTIME(),' ',' ',0,'B',' ',' ',' ',' ','ICSOFT    ',CURDATE(),'99','9','99','010000753894',0,318545,'          ','99','999','TAQ','001',0,0,'2095','AA','SIN DESCRIPCION',1,'999','N',NOW());

Best regards

Hi,

Sorry I gave you the wrong path, please try:

ftp://ftp.mysql.com:/pub/mysql/download

and please don't split the files, use one file.

Thanks

I've tried to put the files on the download folder, but it appears the same error.
I've made the connection with Anonynous and my e-mail as password : angelmartin@icsoftware.es, but I can't send the fil via FTP.
I try to send a file in ZIP format, and the size of the file is 13,8MB.

Thanks for your help

Best regards

Angel Martin

I'm using anonynous user and my e-mail as password to copy the files at the FTP but it doesn't work. The error es 553 "can't create the file".

Maybe I need another user and password in order to have privileges to copy the files at the FTP.

Thanks in advance for your help

Best Regards

Angel

wrkcash (1/24)

Attachment: wrkcash.part0001.exe (application/x-msdownload, text), 195.31 KiB.

wrkcash (2/24)

Attachment: wrkcash.part0002.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (3/24)

Attachment: wrkcash.part0003.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (4/24)

Attachment: wrkcash.part0004.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (5/24)

Attachment: wrkcash.part0005.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (6/24)

Attachment: wrkcash.part0006.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (7/24)

Attachment: wrkcash.part0007.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (8/24)

Attachment: wrkcash.part0008.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (9/24)

Attachment: wrkcash.part0009.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (10/24)

Attachment: wrkcash.part0010.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (11/24)

Attachment: wrkcash.part0011.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (12/24)

Attachment: wrkcash.part0012.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (13/24)

Attachment: wrkcash.part0013.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (14/24)

Attachment: wrkcash.part0014.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (15/24)

Attachment: wrkcash.part0015.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (16/24)

Attachment: wrkcash.part0016.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (17/24)

Attachment: wrkcash.part0017.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (18/24)

Attachment: wrkcash.part0018.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (19/24)

Attachment: wrkcash.part0019.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (20/24)

Attachment: wrkcash.part0020.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (21/24)

Attachment: wrkcash.part0021.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (22/24)

Attachment: wrkcash.part0022.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (23/24)

Attachment: wrkcash.part0023.rar (application/octet-stream, text), 195.31 KiB.

wrkcash (24/24)

Attachment: wrkcash.part0024.rar (application/octet-stream, text), 191.53 KiB.

wrkcomisics (1/23)

Attachment: wrkcomisics.part0001.exe (application/x-msdownload, text), 195.31 KiB.

wrkcomisics (2/23)

Attachment: wrkcomisics.part0002.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (3/23)

Attachment: wrkcomisics.part0003.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (4/23)

Attachment: wrkcomisics.part0004.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (5/23)

Attachment: wrkcomisics.part0005.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (6/23)

Attachment: wrkcomisics.part0006.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (7/23)

Attachment: wrkcomisics.part0007.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (8/23)

Attachment: wrkcomisics.part0008.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (9/23)

Attachment: wrkcomisics.part0009.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (10/23)

Attachment: wrkcomisics.part0010.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (11/23)

Attachment: wrkcomisics.part0011.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (12/23)

Attachment: wrkcomisics.part0012.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (13/23)

Attachment: wrkcomisics.part0013.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (14/23)

Attachment: wrkcomisics.part0014.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (15/23)

Attachment: wrkcomisics.part0015.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (16/23)

Attachment: wrkcomisics.part0016.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (17/23)

Attachment: wrkcomisics.part0017.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (18/23)

Attachment: wrkcomisics.part0018.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (19/23)

Attachment: wrkcomisics.part0019.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (20/23)

Attachment: wrkcomisics.part0020.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (21/23)

Attachment: wrkcomisics.part0021.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (22/23)

Attachment: wrkcomisics.part0022.rar (application/octet-stream, text), 195.31 KiB.

wrkcomisics (23/23)

Attachment: wrkcomisics.part0023.rar (application/octet-stream, text), 19.91 KiB.

I compressed the two files wrkcash.txt and wrkcomisics.txt into rar files, but this time with autoextracted files. If you put all this files at the same directory, you should be able to extract the files executing the exe files.

Best regards

Angel Martin

Thank you for the feedback, I was able for to expand and to dump
the tables:

mysql> show tables;
+---------------+
| Tables_in_v63 |
+---------------+
| wrkcash       |
| wrkcomisics   |
+---------------+
2 rows in set (0.00 sec)

now I need you provide me the SQL for trigger, select and insert queries.

Thank you in advance.

trigger that makes the problem and the view for SELECT sentence

Attachment: trigger_and_view.txt (text/plain), 3.19 KiB.

Table needed for the view

Attachment: pflradm.txt (text/plain), 4.13 KiB.

Table needed for the select sentence.

Attachment: pfrcnto.txt (text/plain), 76.92 KiB.

To repeat the problem you need the following files:
pfpreev.zip -> table pfpreev, because wrkcash table has a foreign key to this table
pfdtdes.txt -> table pfdtdes, because wrkcash table has a foreign key for this table
pfrcnto.txt -> table needed for the select sentence
pflradm.txt -> table needed for the view at the table pfrcnto
trigger_and_view.txt -> trigger that makes the problem, and the view for the table pfrcnto, needed for the sentence.
sentences.txt -> this file has to sentences, one with the INSERT sentence and the other with the select sentence.
In a previous message I sent you the INSERT into WRKCOMISICS table, that it makes the same effect as the trigger, if the wrkcash table hasn't got the trigger.

I''ll remember you that the INSERT sentence is done with autocommit off and REPEATABLE READ in one connection. Also in another connection, the is the SELECT running in AUTOCIMMIT ON and READ-COMITTED.

I'm using the MYSQLD-MAX-NT.EXE deamon for the test in WINXP.

I just would like to know if you could reproduce the problem that I reported?

Best Regards

Angel Martin

Sorry but i had problem for to run your sample case, i am getting
an empty result set from the select sentence:

    ->   and atfunc=func and atzona=zona
    ->    group by attipo,atcicl,atfunc,atzona,atfila  order by 1 asc,2 asc,3 asc,4 asc,5 asc;
Empty set (0.48 sec)

mysql> show tables;
+---------------+
| Tables_in_v63 |
+---------------+
| icsrcnto      |
| pfdtdes       |
| pflradm       |
| pfpreev       |
| pfrcnto       |
| wrkcash       |
| wrkcomisics   |
+---------------+
7 rows in set (0.00 sec)

mysql> select count(*) from icsrcnto;
+----------+
| count(*) |
+----------+
|      216 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from pfdtdes;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from pflradm;
+----------+
| count(*) |
+----------+
|       70 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from pfpreev;
+----------+
| count(*) |
+----------+
|    59499 |
+----------+
1 row in set (4.69 sec)

mysql> select count(*) from pfrcnto;
+----------+
| count(*) |
+----------+
|      220 |
+----------+
1 row in set (0.14 sec)

mysql> select count(*) from wrkcash;
+----------+
| count(*) |
+----------+
|   730848 |
+----------+
1 row in set (2.75 sec)

mysql> select count(*) from wrkcomisics;
+----------+
| count(*) |
+----------+
|   611889 |
+----------+
1 row in set (1.11 sec)

mysql>

The above counts is according with your test case?

PFDTDES FILE

Attachment: pfdtdes.txt (text/plain), 4.05 KiB.

Hello Miguel

I've sent you pfdtdes table, with its contents. 

I've download the following files from the web:
- 24 files for wrkcash with autoextracted files
- 23 files for wrkcash with autoextracted files
- pfpreev.zip
- pflradm.txt
- pfrcnto.txt
- trigger and views.txt
- sentences.txt

I've created a new schema just for simulate the error. I've put all the tables on the new schema, including the table pfdtdes that I've sent today to you and I've executed that sentence:

SELECT ATTIPO,ATCICL,ATFUNC,'---',-1,COUNT(ATTIPO) AS CANTIDAD
  FROM WRKCASH,ICSRCNTO
 WHERE ATTIPO=RTRCTO AND (attras='T' OR ATTRAS='F') AND ATSIST='2095'
   AND (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
 GROUP BY ATTIPO,ATCICL,ATFUNC HAVING COUNT(ATTIPO)<1000
 UNION
SELECT ATTIPO,ATCICL,ATFUNC,ATZONA,-1,COUNT(ATTIPO)
  FROM WRKCASH,  ( SELECT ATSIST AS SIST,ATTIPO AS TIPO,ATCICL AS CICL,ATFUNC AS FUNC
                     FROM WRKCASH,ICSRCNTO
                    WHERE ATTIPO=RTRCTO AND (attras='T' OR ATTRAS='F') AND ATSIST='2095'
                      AND  (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
                    GROUP BY ATSIST,ATTIPO,ATCICL,ATFUNC HAVING COUNT(ATTIPO)>=1000 ) AS B
 WHERE (attras='T' OR ATTRAS='F') AND  (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
   AND ATSIST=SIST AND ATTIPO=TIPO AND ATCICL=CICL AND ATFUNC=FUNC
 GROUP BY ATTIPO,ATCICL,ATFUNC,ATZONA HAVING COUNT(ATTIPO)<1000
 UNION
SELECT ATTIPO,ATCICL,ATFUNC,ATZONA,ATFILA,COUNT(ATTIPO)
  FROM WRKCASH,  ( SELECT ATSIST AS SIST,ATTIPO AS TIPO,ATCICL AS CICL,ATFUNC AS FUNC,ATZONA AS ZONA
                     FROM WRKCASH,( SELECT ATSIST AS SIST,ATTIPO AS TIPO,ATCICL AS CICL,ATFUNC AS FUNC
                                      FROM WRKCASH,ICSRCNTO
                                     WHERE ATTIPO=RTRCTO AND (attras='T' OR ATTRAS='F') AND ATSIST='2095'
                                       AND (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
                                     GROUP BY ATSIST,ATTIPO,ATCICL,ATFUNC HAVING COUNT(ATTIPO)>=1000 ) AS B
                    WHERE (attras='T' OR ATTRAS='F') AND  (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
                      AND ATSIST=SIST AND ATTIPO=TIPO AND ATCICL=CICL AND ATFUNC=FUNC
                    GROUP BY ATSIST,ATTIPO,ATCICL,ATFUNC,ATZONA HAVING COUNT(ATTIPO)>=1000) AS C
 WHERE (attras='T' OR ATTRAS='F') AND  (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
   AND ATSIST=SIST AND ATTIPO=TIPO AND ATCICL=CICL AND ATFUNC=FUNC AND ATZONA=ZONA
 GROUP BY ATTIPO,ATCICL,ATFUNC,ATZONA,ATFILA  ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC;

The result of the sentence is 1530 lines. The execution time is 152 seconds.

I'll send you in another file the result of the sentence.

I hope it'll work the sentence now with PFDTDES table.

Best rgards

Angel Martin

file that contents the result of the sentence

Attachment: result_of_the_sentence.txt (text/plain), 62.87 KiB.

Hi Miguel

This problem also happen in version 5.0.15.

Could you check the result of the select?

Best Regards

Angel Martin

Hi Angel,

Something wrong is still happening on my side, I created again all the
tables, view and trigerr and your latest sentence still has an output
empty:

mysql> select attipo,atcicl,atfunc,'---',-1,count(attipo) as cantidad
    -> from wrkcash,icsrcnto
    -> where attipo=rtrcto and (attras='t' or attras='f') and atsist='2095'
    -> and (atdate<'2005-10-10' or (atdate='2005-10-10' and athora<='09:32:26'))
    -> group by attipo,atcicl,atfunc having count(attipo)<1000
    -> union
    -> select attipo,atcicl,atfunc,atzona,-1,count(attipo)
    -> from wrkcash,  ( select atsist as sist,attipo as tipo,atcicl as cicl,atfunc as
    -> func
    -> from wrkcash,icsrcnto
    -> where attipo=rtrcto and (attras='t' or attras='f') and
    -> atsist='2095'
    -> and  (atdate<'2005-10-10' or (atdate='2005-10-10' and
    -> athora<='09:32:26'))
    -> group by atsist,attipo,atcicl,atfunc having
    -> count(attipo)>=1000 ) as b
    -> where (attras='t' or attras='f') and  (atdate<'2005-10-10' or
    -> (atdate='2005-10-10' and athora<='09:32:26'))
    -> and atsist=sist and attipo=tipo and atcicl=cicl and atfunc=func
    -> group by attipo,atcicl,atfunc,atzona having count(attipo)<1000
    -> union
    -> select attipo,atcicl,atfunc,atzona,atfila,count(attipo)
    -> from wrkcash,  ( select atsist as sist,attipo as tipo,atcicl as cicl,atfunc as
    -> func,atzona as zona
    -> from wrkcash,( select atsist as sist,attipo as tipo,atcicl
    -> as cicl,atfunc as func
    -> from wrkcash,icsrcnto
    -> where attipo=rtrcto and (attras='t' or
    -> attras='f') and atsist='2095'
    -> and (atdate<'2005-10-10' or
    -> (atdate='2005-10-10' and athora<='09:32:26'))
    -> group by atsist,attipo,atcicl,atfunc having
    -> count(attipo)>=1000 ) as b
    -> where (attras='t' or attras='f') and  (atdate<'2005-10-10'
    -> or (atdate='2005-10-10' and athora<='09:32:26'))
    -> and atsist=sist and attipo=tipo and atcicl=cicl and
    -> atfunc=func
    -> group by atsist,attipo,atcicl,atfunc,atzona having
    -> count(attipo)>=1000) as c
    ->  where (attras='t' or attras='f') and  (atdate<'2005-10-10' or
    -> (atdate='2005-10-10' and athora<='09:32:26'))
    ->    and atsist=sist and attipo=tipo and atcicl=cicl and atfunc=func and
    -> atzona=zona
    ->  group by attipo,atcicl,atfunc,atzona,atfila  order by 1 asc,2 asc,3 asc,4 asc,5
    -> asc;
Empty set (0.02 sec)

mysql>

Hello Miquel

Could you execute the following sentences and tell the results
SELECT ATTRAS,COUNT(*) FROM WRKCASH GROUP BY ATTRAS;     

SELECT ATTIPO,ATCICL,ATFUNC,'---',-1,COUNT(ATTIPO) AS CANTIDAD
  FROM WRKCASH
 WHERE ATSIST='2095'
   AND (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
 GROUP BY ATTIPO,ATCICL,ATFUNC HAVING COUNT(ATTIPO)<1000
 UNION
SELECT ATTIPO,ATCICL,ATFUNC,ATZONA,-1,COUNT(ATTIPO)
  FROM WRKCASH,  ( SELECT ATSIST AS SIST,ATTIPO AS TIPO,ATCICL AS CICL,ATFUNC AS
FUNC
                     FROM WRKCASH
                    WHERE ATSIST='2095'
                      AND (ATDATE<'2005-10-10' OR (ATDATE='2005-10-10' AND
ATHORA<='09:32:26'))
                    GROUP BY ATSIST,ATTIPO,ATCICL,ATFUNC HAVING
COUNT(ATTIPO)>=1000 ) AS B
 WHERE  (ATDATE<'2005-10-10' OR
(ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
   AND ATSIST=SIST AND ATTIPO=TIPO AND ATCICL=CICL AND ATFUNC=FUNC
 GROUP BY ATTIPO,ATCICL,ATFUNC,ATZONA HAVING COUNT(ATTIPO)<1000
 UNION
SELECT ATTIPO,ATCICL,ATFUNC,ATZONA,ATFILA,COUNT(ATTIPO)
  FROM WRKCASH,  ( SELECT ATSIST AS SIST,ATTIPO AS TIPO,ATCICL AS CICL,ATFUNC AS
FUNC,ATZONA AS ZONA
                     FROM WRKCASH,( SELECT ATSIST AS SIST,ATTIPO AS TIPO,ATCICL
AS CICL,ATFUNC AS FUNC
                                      FROM WRKCASH
                                     WHERE ATSIST='2095'
                                       AND (ATDATE<'2005-10-10' OR
(ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
                                     GROUP BY ATSIST,ATTIPO,ATCICL,ATFUNC HAVING
COUNT(ATTIPO)>=1000 ) AS B
                    WHERE (ATDATE<'2005-10-10'
OR (ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
                      AND ATSIST=SIST AND ATTIPO=TIPO AND ATCICL=CICL AND
ATFUNC=FUNC
                    GROUP BY ATSIST,ATTIPO,ATCICL,ATFUNC,ATZONA HAVING
COUNT(ATTIPO)>=1000) AS C
 WHERE (ATDATE<'2005-10-10' OR
(ATDATE='2005-10-10' AND ATHORA<='09:32:26'))
   AND ATSIST=SIST AND ATTIPO=TIPO AND ATCICL=CICL AND ATFUNC=FUNC AND
ATZONA=ZONA
 GROUP BY ATTIPO,ATCICL,ATFUNC,ATZONA,ATFILA  ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5
ASC;

Please could you tell me the results.

Best Regards

Angel Martin

Hi Angel,

c:\mysql\bin>mysql -uroot v63
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT ATTRAS,COUNT(*) FROM WRKCASH GROUP BY ATTRAS;
+--------+----------+
| ATTRAS | COUNT(*) |
+--------+----------+
|        |     1886 |
| T      |   728962 |
+--------+----------+
2 rows in set (6.20 sec)

2nd sentence:

| ZA     | ZABONO  | 9999999 | ---  |   -1 |        2 |
| ZA     | ZABONO2 | 9999999 | ---  |   -1 |        3 |
| ZD     | 9999999 | ZD00000 | ---  |   -1 |        4 |
| ZD     | 9999999 | ZDDAVID | ---  |   -1 |        4 |
| ZE     | PEÐAZO2 | 9999999 | ---  |   -1 |       10 |
| ZY     | 9999999 | TEMP    | ---  |   -1 |        9 |
| ZZ     | 9999999 | MAN1    | ---  |   -1 |       41 |
+--------+---------+---------+------+------+----------+
1532 rows in set (4 min 2.94 sec)

Hello Miquel

You can use the last sentence that I've sent you today to check the problem with the locked insert when it has the trigger. You shouldn't have the result of sentence in the cache in order to reproduce the problem.

You can have a look at the previous message in which I explain to you how to reproduce the lock problem.

Thanks for your help and cooperation

Best regards

Angel Martin

Thank you for the bug report and feedback.

Hi, Angel!

I was able to repeat this problem using version 5.0.16. But I was not able to do this using MySQL built from current sources (5.0.22-bk). Investigation shows that your bug is duplicate of bug#16229 "MySQL/InnoDB uses full explicit table locks in trigger processing" which was fixed in version 5.0.19.