MySQL Bugs: #17511: INSERT: Erratic Errcode: 11 reporting and "Unknown error": Error 1105

Bug #17511	INSERT: Erratic Errcode: 11 reporting and "Unknown error": Error 1105
Submitted:	17 Feb 2006 8:58	Modified:	21 Feb 2006 21:30
Reporter:	Mark V	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-nt	OS:	Windows (WinXP Pro latest patches)
Assigned to:		CPU Architecture:	Any

Description:
Hi
First.  Thanks you for all your development efforts which have provided an excellent product.

Issue A) Erratic Errcode: 11 reporting
========================
I've some SQL code (see attached files) that generates an 'ERROR 11' in two different places depending on how the code is submitted....

First run "INSERT_Behavior_DataTable.sql" to set up the data table (file attached).

If submitted via:
mysql -u root -e "SOURCE "INSERT_Behavior.sql";"
 There is no error reported (hence the "SHOW WARNINGS;" statement has been inserted). In this case code following this can run and then later an "Errcode:11" is reported.  At this point the MySQL stops processing the script.

The SHOW WARNINGS; statement produces the following:
+---------+------+---------------------------------------------------------------+
| Level   | Code     | Message                                                              |
+---------+------+---------------------------------------------------------------+
| Error        |     11 | Can't unlock file (Errcode: 11)                                                              |
| Error        |     1105 | Unknown error                                                              |
| Warning        |     1196 | Some non-transactional changed tables couldn't be rolled back                                                              |
+---------+------+---------------------------------------------------------------+

If the same code is submitted via a stored procedure there is a warning report. and MySQL stops processing the code.

Note this occurs even after running myisamchk -f ,-r, -o.  I'm sure this is not due to using myisamchk while the server is running.

Issue B) Unkown Error 1105
==================
The Error 1105 is never reported in either of the cases described above, unless the SHOW WARNINGS; statement is included.

Regards
Mark

How to repeat:
Run the scripts (available)
INSERT_Behavior_DataTable.sql
 and then 
INSERT_Behavior.sql

Suggested fix:
I've rated this 'S1' since I'm not aware of what a workaround might be.

Revision #2: Data table and contents

Attachment: INSERT_Behavior_DataTable.sql (application/octet-stream, text), 154.82 KiB.

Thank you for a problem report. I was not able to repeat the problem you described neither with 5.0.19-BK on Linux nor with 5.0.18-nt on XP:

C:\work>mysql -uroot -p -P3307 -e "SOURCE INSERT_Behavior_DataTable.sql;"
Enter password: ****

C:\work>mysql -uroot -p -P3307 -e "SOURCE INSERT_Behavior.sql;"
Enter password: ****
+-----------------------------+
|                             |
+-----------------------------+
| Start insert into temp_q... |
+-----------------------------+

C:\work>

On Linux, executing statements "manually" in mysql I've got:

mysql> SELECT 'Start insert into temp_q...' AS '';
+-----------------------------+
|                             |
+-----------------------------+
| Start insert into temp_q... |
+-----------------------------+
1 row in set (0.00 sec)

mysql>
mysql> INSERT INTO `scratch`.`temp_q`( date, time, bid, bidsize, bid_min, bidsize_min, bid_max, bidsize_max, offer, offersize, offer_min, offersize_min, offer_max, offersize_max, quotecount )
    -> (
    ->  SELECT
    -> `t1`.`date`,
    -> `t1`.`time`,
    -> (CASE WHEN SUM( `t1`.`bidsize` ) = 0 THEN 0 ELSE SUM( `t1`.`bid` * `t1`.`bidsize`)/SUM( `t1`.`bidsize` )  END ) AS bid,
    -> CAST( AVG( `t1`.`bidsize`) AS UNSIGNED ) AS bidsize,
    -> MIN( `t1`.`bid`) AS bid_min,
    -> MIN( `t1`.`bidsize`) AS bidsize_min,
    -> MAX( `t1`.`bid`) AS bid_max,
    -> MAX( `t1`.`bidsize`) AS bidsize_max,
    -> (CASE WHEN SUM( `t1`.`offersize` ) = 0 THEN 0 ELSE SUM( `t1`.`offer` * `t1`.`offersize` )/SUM( `t1`.`offersize` ) END ) AS offer ,
    -> CAST( AVG( `t1`.`offersize` ) AS UNSIGNED ) AS offersize,
    -> MIN( `t1`.`offer` ) AS offer_min,
    -> MIN( `t1`.`offersize` ) AS offersize_min,
    -> MAX( `t1`.`offer` ) AS offer_max,
    -> MAX( `t1`.`offersize` ) AS offersize_max,
    -> COUNT(offer) AS quotecount
    -> FROM
    -> `scratch`.`quotes` AS `t1` FORCE INDEX ( idxdt, idxmode, idxbid, idxoffer)
    -> WHERE `t1`.`mode` IN  (1,2,3,5,6,12)
    -> GROUP BY
    -> `t1`.`date`, `t1`.`time`
    -> ORDER BY date, time
    -> );

Query OK, 300 rows affected (0.03 sec)
Records: 300  Duplicates: 0  Warnings: 0

mysql>
mysql> SHOW warnings;
Empty set (0.00 sec)

I used the latest test cases you uploaded. I can not repeat the behaviour you described.

Hi,
Thanks again for your efforts.  
I've tried everything I can think of to try and resolve this; restarting the MySQL service, rebooting.

I still get the same problem:

D:\MySQL\SQL>mysql -u root <INSERT_Behavior_DataTable.sql

D:\MySQL\SQL>mysql -u root <INSERT_Behavior.sql

Start insert into temp_q...
Level   Code    Message
Error   11      Can't unlock file (Errcode: 11)
Error   1105    Unknown error
Warning 1196    Some non-transactional changed tables couldn't be rolled back

D:\MySQL\SQL>

In case it helps I'm running off a NTFS compressed drive.  This just occured to me so apologies if I should have mentioned it before.

Regards
Mark

A full uninstall and re-install of the server seems to have done the trick.
Pretty drastic, but it worked :)
Consequently I've downgraded the severity and closed the bug report.
Thanks again for all the good work.