[BUG] ERROR 1062 on huge distinct mysql-4.0.4

>Description:

ERROR 1062: Duplicate entry on huge distinct

>How-To-Repeat:

select distinct max(IF(@pid <> sp.product_id, @name :=3Dsp.name , if(sp.na=
me
not in(@name), @name :=3D concat(ifnull(@name,''),' , ',sp.name),''))) as =
n,
max(@pid :=3D sp.product_id) as pid from shop_product sp left join full_sea=
rch
fs on (fs.product_id=3Dsp.product_id) where fs.product_id is NULL group by
sp.product_id limit 10

ERROR 1062: Duplicate entry '16133' for key 1

I have uploaded file huge_distinct-4.0.4.sql.gz
to ftp://support.mysql.com/pub/mysql/secret/

You can test it with
zcat huge_distinct-4.0.4.sql | mysql test

Let me know if you need further information.

>Fix:

Do not use DISTINCT.

>Submitter-Id: gluk@php4.ru
>Originator: Sergey S. Kostyliov
>Organization:

eHouse

>MySQL support: none
>Synopsis: 1062 error on huge distict.
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.4-beta (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.37 Distrib 4.0.4-beta, for pc-linux-gnu
> on

i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.4-beta-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 10 min 3 sec

Threads: 1 Questions: 6 Slow queries: 1 Opens: 8 Flush tables: 1 Open
tables: 2 Queries per second avg: 0.001

>Environment:


System: Linux alien.vh.com.ru 2.4.18-3smp #1 SMP Thu Apr 18 07:27:31 EDT 20=
02
i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake
/usr/local/gcc/bin/gcc -v
Reading specs from
/usr/local/gcc/bin/../lib/gcc-lib/i686-pc-linux-gnu/3.2/spec s
Configured with: /var/tmp/gcc-3.2/configure --prefix=3D/usr/local/gcc-3.2
=2D-enable-threads=3Dposix --enable-languages=3Dc++,c
Thread model: posix
gcc version 3.2
Compilation info: CC=3D'/usr/local/gcc/bin/gcc' CFLAGS=3D'-O4 -march=3Dpen=
tium3
=2Dmcpu=3Dpentium3 -msse -pipe' CXX=3D'/usr/local/gcc/bin/gcc' CXXFLAGS=
=3D'-O4
=2Dmarch=3Dpentium3 -mcpu=3Dpentium3 -msse -pipe -felide-constructors
=2Dfno-exceptions -fno-rtti -DUSE_MYSYS_NEW' LDFLAGS=3D''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 8 16:22 /lib/libc.so.6 ->
libc-2.2.5.so
=2Drwxr-xr-x 1 root root 1260480 Aug 7 21:09 /lib/libc-2.2.5.so
=2Drw-r--r-- 1 root root 2312410 Aug 7 20:46 /usr/lib/libc.a
=2Drw-r--r-- 1 root root 178 Aug 7 20:36 /usr/lib/libc.so
Configure command: ./configure --prefix=3D/usr --libexecdir=3D/usr/sbin
=2D-localstatedir=3D/var/lib/mysql --mandir=3D/usr/share/man
=2D-infodir=3D/usr/share/info --enable-assembler
=2D-with-mysqld-ldflags=3D-all-static --with-mysql-user=3Dmysql --with-inno=
db
=2D-with-unix-socket-path=3D/var/lib/mysql/mysql.sock
=2D-with-extra-charsets=3Dlatin1,koi8_ru,cp1251 --enable-thread-safe-client
CC=3D/usr/local/gcc/bin/gcc 'CFLAGS=3D-O4 -march=3Dpentium3 -mcpu=3Dpentium=
3 -msse
=2Dpipe' 'CXXFLAGS=3D-O4 -march=3Dpentium3 -mcpu=3Dpentium3 -msse -pipe
=2Dfelide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW'
CXX=3D/usr/local/gcc/bin/gcc


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12690@lists.mysql.com
To unsubscribe, e-mail

Re: [BUG] ERROR 1062 on huge distinct mysql-4.0.4

Hello,
Sergey S. Kostyliov (by way of Alexander Y. Fomichev ) wrote:
>>Description:
>
>
> ERROR 1062: Duplicate entry on huge distinct

Thank you for bug report.

I downloaded your dump file and will come back when I have more information for you.


--
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12698@lists.mysql.com
To unsubscribe, e-mail

Re: [BUG] ERROR 1062 on huge distinct mysql-4.0.4

Sergey S. Kostyliov (by way of Alexander Y. Fomichev ) wrote:
>>Description:
>
>
> ERROR 1062: Duplicate entry on huge distinct
>
>
>>How-To-Repeat:
>
>
> select distinct max(IF(@pid <> sp.product_id, @name :=sp.name , if(sp.name
> not in(@name), @name := concat(ifnull(@name,''),' , ',sp.name),''))) as n,
> max(@pid := sp.product_id) as pid from shop_product sp left join full_search
> fs on (fs.product_id=sp.product_id) where fs.product_id is NULL group by
> sp.product_id limit 10
>
> ERROR 1062: Duplicate entry '16133' for key 1
>
> I have uploaded file huge_distinct-4.0.4.sql.gz
> to ftp://support.mysql.com/pub/mysql/secret/
>
> You can test it with
> zcat huge_distinct-4.0.4.sql | mysql test
>
> Let me know if you need further information.
>
>
>>Fix:
>
>
> Do not use DISTINCT.
>
>
>>Submitter-Id: gluk@php4.ru
>>Originator: Sergey S. Kostyliov
>>Organization:
>
>
> eHouse
>
>
>>MySQL support: none
>>Synopsis: 1062 error on huge distict.
>>Severity: serious
>>Priority: medium
>>Category: mysql
>>Class: sw-bug
>>Release: mysql-4.0.4-beta (Source distribution)
>>Server: /usr/bin/mysqladmin Ver 8.37 Distrib 4.0.4-beta, for pc-linux-gnu
>>on
>
>
> i686
> Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
> This software comes with ABSOLUTELY NO WARRANTY. This is free software,
> and you are welcome to modify and redistribute it under the GPL license
>
> Server version 4.0.4-beta-log
> Protocol version 10
> Connection Localhost via UNIX socket
> UNIX socket /var/lib/mysql/mysql.sock
> Uptime: 1 hour 10 min 3 sec
>
> Threads: 1 Questions: 6 Slow queries: 1 Opens: 8 Flush tables: 1 Open
> tables: 2 Queries per second avg: 0.001
>
>
>>Environment:
>
>
>
> System: Linux alien.vh.com.ru 2.4.18-3smp #1 SMP Thu Apr 18 07:27:31 EDT 2002
> i686 unknown
> Architecture: i686
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake
> /usr/local/gcc/bin/gcc -v
> Reading specs from
> /usr/local/gcc/bin/../lib/gcc-lib/i686-pc-linux-gnu/3.2/spec s
> Configured with: /var/tmp/gcc-3.2/configure --prefix=/usr/local/gcc-3.2
> --enable-threads=posix --enable-languages=c++,c
> Thread model: posix
> gcc version 3.2
> Compilation info: CC='/usr/local/gcc/bin/gcc' CFLAGS='-O4 -march=pentium3
> -mcpu=pentium3 -msse -pipe' CXX='/usr/local/gcc/bin/gcc' CXXFLAGS='-O4
> -march=pentium3 -mcpu=pentium3 -msse -pipe -felide-constructors
> -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW' LDFLAGS=''
> LIBC:
> lrwxrwxrwx 1 root root 13 Oct 8 16:22 /lib/libc.so.6 ->
> libc-2.2.5.so
> -rwxr-xr-x 1 root root 1260480 Aug 7 21:09 /lib/libc-2.2.5.so
> -rw-r--r-- 1 root root 2312410 Aug 7 20:46 /usr/lib/libc.a
> -rw-r--r-- 1 root root 178 Aug 7 20:36 /usr/lib/libc.so
> Configure command: ./configure --prefix=/usr --libexecdir=/usr/sbin
> --localstatedir=/var/lib/mysql --mandir=/usr/share/man
> --infodir=/usr/share/info --enable-assembler
> --with-mysqld-ldflags=-all-static --with-mysql-user=mysql --with-innodb
> --with-unix-socket-path=/var/lib/mysql/mysql.sock
> --with-extra-charsets=latin1,koi8_ru,cp1251 --enable-thread-safe-client
> CC=/usr/local/gcc/bin/gcc 'CFLAGS=-O4 -march=pentium3 -mcpu=pentium3 -msse
> -pipe' 'CXXFLAGS=-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe
> -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW'
> CXX=/usr/local/gcc/bin/gcc
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12690@lists.mysql.com
> To unsubscribe, e-mail
>
>
>



--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12701@lists.mysql.com
To unsubscribe, e-mail

[BUG] ERROR 1062 on huge distinct mysql-4.0.4

Hi!

>>>>> "Sergey" == Sergey S Kostyliov < (by way of Alexander Y. Fomichev )> writes:

>> Description:
Sergey> ERROR 1062: Duplicate entry on huge distinct

>> How-To-Repeat:

Sergey> select distinct max(IF(@pid <> sp.product_id, @name :=sp.name , if(sp.name
Sergey> not in(@name), @name := concat(ifnull(@name,''),' , ',sp.name),''))) as n,
Sergey> max(@pid := sp.product_id) as pid from shop_product sp left join full_search
Sergey> fs on (fs.product_id=sp.product_id) where fs.product_id is NULL group by
Sergey> sp.product_id limit 10

Sergey> ERROR 1062: Duplicate entry '16133' for key 1

Sergey> I have uploaded file huge_distinct-4.0.4.sql.gz
Sergey> to ftp://support.mysql.com/pub/mysql/secret/

Sergey> You can test it with
Sergey> zcat huge_distinct-4.0.4.sql | mysql test



Sorry for the slow response; We fixed this a while ago but forgot to
send a note about this to the bugs@ lists :(

Anyway the duplicate entry problem will be fixed in 4.0.5.

There is however another problem with this query:

select distinct max(IF(@pid <> sp.product_id, @name :=sp.name , if(sp.name not in(@name), @name := concat(ifnull(@name,''),' , ',sp.name),''))) as n, max(@pid := sp.product_id) as pid from shop_product sp left join full_search fs on (fs.product_id=sp.product_id) where fs.product_id is NULL group by sp.product_id limit 10

This query assumes that we MySQL process the rows in the group by
order. (@name' concatenates 'sp_name' to the previous value). This
is however something that is not true and because of this the above
query will probably not return the answer you expected or produce a
result set that takes more than 4G to process.

In 4.1 we will have a concat_group() function that will return the
result you require.

Until then, you have to find some other way to do this. (proably on
the client side).

Regards,
Monty
CTO of MySQL AB


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12800@lists.mysql.com
To unsubscribe, e-mail

Re: [BUG] ERROR 1062 on huge distinct mysql-4.0.4

Hello Michael,

On Sunday 20 October 2002 11:24, Michael Widenius wrote:
> Hi!
>
> >>>>> "Sergey" == Sergey S Kostyliov < (by way=
of
> >>>>> Alexander Y. Fomichev )> writes:
> >>
> >> Description:
>
> Sergey> =09ERROR 1062: Duplicate entry on huge distinct



>
> Sergey> I have uploaded file huge_distinct-4.0.4.sql.gz
> Sergey> to ftp://support.mysql.com/pub/mysql/secret/
>
> Sergey> You can test it with
> Sergey> zcat huge_distinct-4.0.4.sql | mysql test
>
>
>
> Sorry for the slow response; We fixed this a while ago but forgot to
> send a note about this to the bugs@ lists :(
>
> Anyway the duplicate entry problem will be fixed in 4.0.5.

Just want let you know that the same test case still works against today
bk 4.0 snapshot.
("ERROR 1062 at line 567029: Duplicate entry '16077' for key 1")

>
> There is however another problem with this query:
>
> select distinct max(IF(@pid <> sp.product_id, @name :=3Dsp.name , if(sp=
name
> not in(@name), @name :=3D concat(ifnull(@name,''),' , ',sp.name),''))) =
as n,
> max(@pid :=3D sp.product_id) as pid from shop_product sp left join
> full_search fs on (fs.product_id=3Dsp.product_id) where fs.product_id i=
s NULL
> group by sp.product_id limit 10
>
> This query assumes that we MySQL process the rows in the group by
> order. (@name' concatenates 'sp_name' to the previous value). This
> is however something that is not true and because of this the above
> query will probably not return the answer you expected or produce a
> result set that takes more than 4G to process.
>
> In 4.1 we will have a concat_group() function that will return the
> result you require.
>
> Until then, you have to find some other way to do this. (proably on
> the client side).

Thank you for explanation. Will try to follow your advice.

>
> Regards,
> Monty
> CTO of MySQL AB

--=20
Best regards,
Sergey S. Kostyliov
Public PGP key: http://sysadminday.org.ru/rathamahata.=
asc

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12803@lists.mysql.com
To unsubscribe, e-mail

Re: [BUG] ERROR 1062 on huge distinct mysql-4.0.4

Hello Sergey,
Sergey S. Kostyliov wrote:
> Hello Michael,


First of all sorry for delayed responce as it was mine duty to answer you as I
nivestigated your case.

Monty> Anyway the duplicate entry problem will be fixed in 4.0.5.
>
>
> Just want let you know that the same test case still works against today
> bk 4.0 snapshot.
> ("ERROR 1062 at line 567029: Duplicate entry '16077' for key 1")

You mean that you get same error with 4.0 from bk tree?
This is a bit strange.

I wasn't able to repeat this error on my machine. Instead it crashed mysqld with
SIGSEV which helped us to fix very rare bug. Very rare because your query is
extremely wrong - it combines different mistakes.

However BUG is BUG and must be fixed.
I just tried your original query, but it failed with

ERROR 1114: The table '#sql_3819_0' is full

So it will take me more time to repeat your error :)


Can you provide some more information?

I would like to see output of
show variables
as well as my.cnf and more about your setup - amount of RAM, filesystems you use
and diskspace especially for mysql datadir and tmpdir.


Monty> There is however another problem with this query:

I will say that there is more than one problem.

This query abuses several basics of SQL, but Monty already pointed you to most
important one:

Relational Database Model requires that rows has no internal order because it
defines tables as unordered sets and queries as operations over unordered sets.
So nothing must rely on 'expected' order. Only result of query can be ordered.

As your left join just filters out rows which has not related rows in second
table (full_search) the query in question actually is:

select distinct max(IF(@pid <> sp.product_id, @name :=sp.name , if(sp.name not
in(@name), @name := concat(ifnull(@name,''),' , ',sp.name),''))) as n, max(@pid
:= sp.product_id) as pid from sp6 as sp group by sp.product_id;

Note that:

1.
SELECT DISTINCT ... GROUP BY ...; usually is wirtten by mistake. There are very
rare cases when using both group by and distinct really makes sense.

2.
SELECT MAX(x) FROM table GROUP BY x; (as in your query max(@pid := sp.product_id)

Above makes no sense as it will always return same as:

SELECT x FROM table GROUP BY x;


3. When you use User variables better set them to some value ('' or 0 for
example) before running query.

Take a look at following sequence:


mysql> select id from i limit 1;
+------+
| id |
+------+
| 1 |
+------+

mysql> select @var:=id+@var from i limit 1;
+---------------+
| @var:=id+@var |
+---------------+
| NULL |
+---------------+

mysql> select @var:=0;
+---------+
| @var:=0 |
+---------+
| 0 |
+---------+

mysql> select @var:=id+@var from i limit 1;
+---------------+
| @var:=id+@var |
+---------------+
| 1 |
+---------------+

mysql> select @var:=id+@var from i limit 1;
+---------------+
| @var:=id+@var |
+---------------+
| 2 |
+---------------+



Mine suggestion is to forget about this query at all and rewrite it from scratch.

What ever it does now result is wrong.

It also seems that Table shop_product has too many redundant rows which just
waste more space without helping at all.

Best regards

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12806@lists.mysql.com
To unsubscribe, e-mail

Re: [BUG] ERROR 1062 on huge distinct mysql-4.0.4

Hello all,

Seems like lists.mysql.ru ignore mails with altered Message-Id
My fault.
So, yet another resend (hope last).

On Sunday 20 October 2002 20:53, you wrote:
> Hello Sergey,




> You mean that you get same error with 4.0 from bk tree?
> This is a bit strange.

Yes, the same error. In fact the same error on two my boxes, but see
belough...



> Can you provide some more information?
>
> I would like to see output of
> show variables
> as well as my.cnf and more about your setup - amount of RAM, file-syste=
ms
> you use and diskspace especially for mysql datadir and tmpdir.

All affected partitions are reiserfs mounted with default options (rw).

Server #1 (devel-server)
UP PIII-500
RAM: 1GB

/etc/my.cnf:
http://sysadminday.org.ru/1_my.cnf
~/.my.cnf:
No such file.
'SHOW VARIABLES" output:
http://sysadminday.org.ru/1_show_variables

tmpdir:=20
df -h `mysql -e"SHOW VARIABLES" | grep tmpdir | awk -- '{print $2}'`
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 3.4G 2.3G 967M 71% /

datadir:
df -h `mysql -e"SHOW VARIABLES" | grep datadir | awk -- '{print $2}'`
Filesystem Size Used Avail Use% Mounted on
/dev/sdc1 8.3G 5.9G 2.0G 75% /var/lib

Server #2 (test-server)
SMP 2xPII-300
RAM: 512MB

/etc/my.cnf:
http://sysadminday.org.ru/2_my.cnf
~/.my.cnf:
No such file
'SHOW VARIABLES" output:
http://sysadminday.org.ru/2_show_variables

tmpdir:
df -h `mysql -e"SHOW VARIABLES" | grep tmpdir | awk -- '{print $2}'`
Filesystem Size Used Avail Use% Mounted on
/dev/ida/c0d0p2 1.6G 148M 1.4G 10% /

datadir:
df -h `mysql -e"SHOW VARIABLES" | grep datadir | awk -- '{print $2}'`
Filesystem Size Used Avail Use% Mounted on
/dev/ida/c0d0p8 8.7G 891M 7.8G 10% /var

Let me know if you need more information about this systems.

>
>
> Monty> There is however another problem with this query:
>
> I will say that there is more than one problem.
>
> This query abuses several basics of SQL, but Monty already pointed you =
to
> most important one:
>
> Relational Database Model requires that rows has no internal order beca=
use
> it defines tables as unordered sets and queries as operations over
> unordered sets. So nothing must rely on 'expected' order. Only result o=
f
> query can be ordered.
>
> As your left join just filters out rows which has not related rows in
> second table (full_search) the query in question actually is:
>
> select distinct max(IF(@pid <> sp.product_id, @name :=3Dsp.name , if(sp=
name
> not in(@name), @name :=3D concat(ifnull(@name,''),' , ',sp.name),''))) =
as n,
> max(@pid
>
> :=3D sp.product_id) as pid from sp6 as sp group by sp.product_id;
>
> Note that:
>
> 1.
> SELECT DISTINCT ... GROUP BY ...; usually is wirtten by mistake. There =
are
> very rare cases when using both group by and distinct really makes sens=
e.
>
> 2.
> SELECT MAX(x) FROM table GROUP BY x; (as in your query max(@pid :=3D
> sp.product_id)
>
> Above makes no sense as it will always return same as:
>
> SELECT x FROM table GROUP BY x;
>
> 3. When you use User variables better set them to some value ('' or 0 f=
or
> example) before running query.
>
> Take a look at following sequence:
>
> mysql> select id from i limit 1;
> +------+
> | id |
> +------+
> | 1 |
> +------+
>
> mysql> select @var:=3Did+@var from i limit 1;
> +---------------+
> | @var:=3Did+@var |
> +---------------+
> | NULL |
> +---------------+
>
> mysql> select @var:=3D0;
> +---------+
> | @var:=3D0 |
> +---------+
> | 0 |
> +---------+
>
> mysql> select @var:=3Did+@var from i limit 1;
> +---------------+
> | @var:=3Did+@var |
> +---------------+
> | 1 |
> +---------------+
>
> mysql> select @var:=3Did+@var from i limit 1;
> +---------------+
> | @var:=3Did+@var |
> +---------------+
> | 2 |
> +---------------+
>
> Mine suggestion is to forget about this query at all and rewrite it fro=
m
> scratch.

I'm really agree with you, this query is terrible wrong from all points o=
f
view.

>
> What ever it does now result is wrong.
>
> It also seems that Table shop_product has too many redundant rows which
> just waste more space without helping at all.

Yes, this is also valid for me, but this is a long story.

Anyway, thanks for the suggestions.

>
> Best regards

magic words: sql, query
How-To-Repeat:

--=20
Best regards,
Sergey S. Kostyliov
Public PGP key: http://sysadminday.org.ru/rathamahata.=
asc



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12813@lists.mysql.com
To unsubscribe, e-mail

Re: [BUG] ERROR 1062 on huge distinct mysql-4.0.4

Hello,
Sergey S. Kostyliov wrote:
> Hello Alexander,
>
> I'm sorry for the resend :(, I just realize that attachments are not allowed
> on bugs@lists.mysql.com.

No problem.



>>You mean that you get same error with 4.0 from bk tree?
>>This is a bit strange.
>
>
> Yes, the same error. In fact the same error on two my boxes, but see
> belough...


Sorry, but I have to give up.
I can't reproduce your Duplicate Error.

In one of occasions yout query ran for about 60 hours till I decided to stop it.
It was in State "Removing Duplicates"

In all other occasions it fails with one of following:

ERROR 1114: The table '#sql_5ddf_0' is full

when temp table reaches 4GB limit

or if mysqld is started with --big-tables

ERROR 1030: Got error 28 from table handler
as it eats all empty diskspace in tmpdir ~7Gb in my case.

du -h
-rw-rw---- 1 mysql mysql 7.0G Oct 25 16:58 #sql_7db9_0.MYD
-rw-rw---- 1 mysql mysql 1.0K Oct 25 16:35 #sql_7db9_0.MYI



However I have to mention something interesting.
You said that:

> tmpdir:
> df -h `mysql -e"SHOW VARIABLES" | grep tmpdir | awk -- '{print $2}'`
> Filesystem Size Used Avail Use% Mounted on
> /dev/sda3 3.4G 2.3G 967M 71% /


> tmpdir:
> df -h `mysql -e"SHOW VARIABLES" | grep tmpdir | awk -- '{print $2}'`
> Filesystem Size Used Avail Use% Mounted on
> /dev/ida/c0d0p2 1.6G 148M 1.4G 10% /


So you have less than 1Gb in your tmpdir on both machines.
But your query tries to produces > 7Gb temp table.

Keep it in mind for further optimizations :)

Best regards

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12859@lists.mysql.com
To unsubscribe, e-mail