Bug #36920 Server (5.1.x) stops error Can't write; duplicate key in table 'C\Windows\temp'
Submitted: 23 May 2008 15:27 Modified: 31 Oct 2008 13:03
Reporter: Louis Breda van Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.x OS:Microsoft Windows (XP32, VISTA64)
Assigned to: CPU Architecture:Any
Tags: 80040e14, duplicate, temp, write

[23 May 2008 15:27] Louis Breda van
Description:
During an update query the serverengine sends a message

'Error 80040e14 can't write; duplicate key in table 'C"\Windows\Temp\#sql etc'

This error:
- has been seen via ODBC as wel direct on the MySQL command line
- is *probably* related to large table update joins
- the duplicate key error can not be true
(it is imposible in my situation / tables query)
- the table mentioned in the error message did not exist at the moment the warning visable
- the error does occur on my vista64 (8GByte) as well as my windos xp pro 32 system (4 Gbyte)
- I do not know what triggers the problem (large tables probably), but there where the problem occurs, it probably will occur next time the query is executed as well.

This error is a show stopper for me.

Sincerely

Louis

How to repeat:
Don't know, but I can reproduce the problem.

So If a potential patch or testversion is available, I can test it. 

Better today than tomorow
[23 May 2008 19:06] Louis Breda van
Hello,

I just found another bug report, quite differentm but might be related.  
Bug #28606 5.1.18 mysql_upgrade fails on Windows

Louis
[23 May 2008 19:29] Louis Breda van
For Info I did a test where I did reassign the MySQL tmpdir to 
some other than the standard windows tempdir 
(my.ini ad e.g. tmpdir=F:/temp2 in the server section).

This did *not* thange the problem.

Louis
[23 May 2008 19:54] Louis Breda van
did a test with the max temptable setting raised from 512 to 3500 (the max I can afford on this 8G vista64 system).

No other result.

Louis
[24 May 2008 8:25] Louis Breda van
Here by part of a mail exchange I had with MySQL-support before I raised this bug. 

I decided to raise the bug yesterday at the moment I not only saw the bug on my xp-system but under vista64 as well.

--------

On my XP-system 32-bit running .23 I have the following problem

Query:

        SQLSTR = "UPDATE from_tabel LEFT JOIN dest_tabel ON from_tabel.pk_fromtab = dest_tabel.pk_fromtab " + _
        "SET desttabel.AddressKey = fromtabel.AddressKey " + _
        "WHERE ((dest_tabel.pk_fromtab Is Not Null) AND (dest_tabel.Final<>True) AND (dest_tabel.AddressKey Is Null));"
        
        cn_kd.Execute SQLSTR '### impossible dupkey error (addresskey does not have an index unic)

T tried a couple of ways to work around this :
- using RIGHT JOIN same functionality => same error
- adding ORDER BY pk_destable => same error
- adding set pk_destable = pk_desttable => same error
- tried 5.1.24 32 bit, other errors (.23 is better IMHO), so no option

It seems that the error does not occur on my Vista64 system !!(?)
(note: Update I saw it on VISTA64 as well)

I did a few tests from the mysql command line. 
- First thing to conclude is that that leads to exactly the same problem
- To exclude the possibility of corrupted tables or so, I also did a test 
Where I truncated and repaired the tables. Also that did not change it
- the only error seen in the error log is the well known :>
080519 13:53:19  InnoDB: Started; log sequence number 7 3002931402
080519 13:53:19 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
(note: I repaired that error myself on the vista system, but if you do a fresh install it is still there)

080519 13:53:19 [Note] Event Scheduler: Loaded 0 events
080519 13:53:19 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.23-rc-community'  socket: ''  port: 3306  MySQL Community Server (GPL)

Errors seen on the command line:
ERROR 1022 (23000): Can't write; duplicate key in table 'C:\WINDOWS\TEMP\#sql_20
0_0'
Error (Code 1022): Can't write; duplicate key in table 'C:\WINDOWS\TEMP\#sql_200
_0'
Error (Code 1105): Unknown error

When running from visual basic cn.execute On the screen 

 

In a earlier run on the commandline:

ERROR 1022 (23000): Can't write; duplicate key in table 'C:\WINDOWS\TEMP\#sql_f4
_0'

-	Again note that the updated addresskey (varchar(20)) is not a key unique.
-	Apart form the PK there is one other index unique in the recordset
an index from type BTREE containing two fields. Index or fields are not 
at all part of the problem giving update 
-	And strange enough I did not see the problem on my portable (xp-system running not working .24! ) and also not on my vista64 system
-	One thing for sure very reproducible 

Hope you hear some bell ringing somewhere 

Sincerely,

Louis
[25 May 2008 11:36] Louis Breda van
Hello,

I am terribly sorry, but I made a mistake. I assumed to easy that the problem I say on my vista system did equal the problem on my XP-system.
They where not. 

Since the problem seems to be on the XP-system only, I did lower the prio to non-critical. Up to either the problem is solved or it is clear what is causing it.

Sorry again,

Louis
[3 Jun 2008 19:03] Louis Breda van
Halo,

Here we have the TempKey Error Again. And in a MySQL-only situation on  VISTA64 system.

This error is realy causing me trouble

History:
- Because of the Update Errors (other critical bug), where there is still discusion if it is a microsoft or an mysql bug, I am moving more and more tables into MySQL 
- And this TempKey Error is another error 
I am moving more and more tables towards mysql, to by pass problems and not to have discussions. 

But this is an example I use 100% MySQL !!

Below a query where
- some datafields in recordset2 are updated with data from recordset1. 
- where source. MyIdentifier (= PK !! recordset 1) equals a corresponding field in the destination recordset (just a field and index [not unique] there)
- the pk of the destination recordset does not play a role

Becoming crazy from all those errors!

Louis

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.23-bug34297-community MySQL Community Server (GPL)

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

mysql> connect lta
Connection id:    11
Current database: lta

mysql> UPDATE destrecords dest RIGHT JOIN sourcerecords source ON dest.MyIdentifier = source. MyIdentifier 
 SET dest.LocKey = source.LocKey, dest.NamePart = Left(source.TheName,5);
ERROR 1022 (23000): Can't write; duplicate key in table 'E:\temp2\#sql_92c_0'
mysql>
mysql> show warnings;
+-------+------+-----------------------------------------------------------+
| Level | Code | Message                                                   |
+-------+------+-----------------------------------------------------------+
| Error | 1022 | Can't write; duplicate key in table 'E:\temp2\#sql_92c_0' |
| Error | 1105 | Unknown error                                             |
+-------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

I have no work a round for this problem! Blocking!

Louis
[4 Jun 2008 14:10] Susanne Ebrecht
Many thanks for writing a bug report.

What exactly let you think that this is a bug?

The error message tells you that you will try to create a duplicate key.
This means you have a column in your table with an unique index on it.

When you know try to insert/update a column with a value that already is existing in your table, you will get a duplicate key error.

Windows is using strict mode as default. This means, it will check for stuff like duplicate keys first and will throw error messages for that.

You can disable strict mode and then you won't get this error messages you only will get warnings.

Please check first, if you really don't try to insert the same value twice or if your update is trying to update a value to an already existing value.

Let us know if this helps you.

If this won't help you we need to know the following:

1) exact description how to reproduce this.
If necessary provide a dump of your database.

2) the exact version, that you are using. You can figure it out by using: SELECT version();
[4 Jun 2008 15:08] Louis Breda van
Susanne,

There is only one key unique in the destination table, that is the PK. 

The PK is not at all changed by this update, and it is an update so no new records added.

So, the error message is really not valid!

Sincerely,

Louis
[4 Jun 2008 15:15] Louis Breda van
Sorry,

I should add this extra info:

- The problem is probably related to large tables / large joins (mln's of records)
- Yesterday evening I did split an update in 10 smaller one's by
 selecting where PK like %LastDig{0-9}. Doing that made the query did run
(but of course did not change the query at all / nor data / nor table defs)
- I have a lot of mail exchanged with Tonci about this error. 

Sincerely,

Louis
[4 Jun 2008 16:08] Tonci Grgin
Louis, yes, I have notified Susanne of our conversation and forwarded your last e-mail with my.ini to her. But, if we don't have table structure and a portion of data to provoke this error with I don't think Susanne will be able to do anything. Susanne?
[4 Jun 2008 16:16] Louis Breda van
Tonci,

It is hard if not imposible to give you data structure and data.

The data is confidential and appart from that we are talking about GByte's of data.

What I can offer is to do some testing or tracing work. Mostly thinking about a debug version giving more info on internal steps. 

But be aware, it is probably after 1 mln records or so the db-executable is hitting some wall.

Sincerely,

Louis
[4 Jun 2008 16:16] Louis Breda van
Tonci,

It is hard if not imposible to give you data structure and data.

The data is confidential and appart from that we are talking about GByte's of data.

What I can offer is to do some testing or tracing work. Mostly thinking about a debug version giving more info on internal steps. 

But be aware, it is probably after 1 mln records or so the db-executable is hitting some wall.

Sincerely,

Louis
[4 Jun 2008 16:21] Tonci Grgin
Louis, make an effort and provide at least table structures and queries, with private flag if necessary, and we'll try to find the way to fill it with enough data.
[12 Oct 2008 23:00] MySQL Verification Team
Hi Louis,
If you upload the data to ftp://ftp.mysql.com/pub/mysql/upload it will be private only for developers. Did you provide the data to Tonci?, otherwise could you please it using our ftp server and comment private here when done. Thanks in advance.
[15 Oct 2008 13:12] Louis Breda van
Hello Miguel,

I doubt if I can help you this moment in time, for a couple of reasons:

1) was using 5.1.23 special build by then and also older odbc versions
2) I did a lot of changes to the application to by pass all kinds of performance more precise memmory size problems (MsAccess etc 2Gbyte limmit). 
a) Big Big problem in relation to that is that MySQL does not support Server Side Cusors, what forces me to fetch all data towards the client side (MsAccss)
b) by passed problems by using selections on last digit of PK and using limmit statements etc. 
3) The application is running now, appart from some maintenance. So I am hardly working on it now.

So querys and joins have been split up and actual software is newer. 

Still having unpedicted / incorrect behaver e.g. in relation to ODBC.
Stupid but some situations *only* work with ODBC3 and others *only* work with ODBC5. Can't tell you why or when :) 

Sincerely,

Louis
Actually using 5.1.28, latest ODBC3, latest ODBC5 all on a VISTA64 and a XP32 systems.
[31 Oct 2008 13:03] Susanne Ebrecht
Louis,

I will set this into "can't repeat" status because we can't repeat it without getting further information from you here that you can't provide to us.

Please, feel free to re-open it, when you will run into this problem again and paste the needed informations to our ftp server.