MySQL Bugs: #6921: "ERROR 1030 (HY000) :Got error 1 from storage engine" when using distinct

Bug #6921	"ERROR 1030 (HY000) :Got error 1 from storage engine" when using distinct
Submitted:	1 Dec 2004 17:18	Modified:	1 Dec 2004 19:14
Reporter:	Johannes Östling	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.7-standard Official MySQL-standard	OS:	Windows (Windows Xp SP3)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

the query:

create table t1 (id int);
select count(distinct c.id) from t1 c;

produces the following error message:

ERROR 1030 (HY000) :Got error 1 from storage engine

I've looked through bugs similar to this one on your site and found that this is a version of these two:
6523
6624

Maybe I shouldn't have listed this one as well however I feel that my problem represents the core of both these problems and I guess I'm hoping this issue will be resolved sooner when isolated like this. I'm quite new to MySql so to me it seems strange that so few people have noticed this flaw, is there anything wrong with my reasoning?

I'm very interested in any feedback how I could resolve this issue. Is there any patches or workarounds?

regards,

Johannes Östling

How to repeat:
create table t1 (id int);
select count(distinct c.id) from t1 c;

Suggested fix:
NA

I wasn't able to repeat with latest 4.1.8 Windows/Linux:

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

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

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.10 sec)

mysql> select count(distinct c.id) from t1 c;
+----------------------+
| count(distinct c.id) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.04 sec)

mysql>

Then this was already fixed in the source.

So the solution is to wait for 4.1.8? Is there any other ways?

thanks for your suport

/ Johannes

The problem here is that I wasn't able to repeat with 4.1.7 also:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.7-nt

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

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.11 sec)

mysql> select count(distinct c.id) from t1 c;
+----------------------+
| count(distinct c.id) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.03 sec)

then is something I don't have in my environment and you need to
provide me the information for.

I just reinstalled mysql and tried again with the same problem. Could my problems relate to the fact that the system is running on an AMD Athlon 64?

regards

/ Johannes