before all thank you for your answer,
next
while waiting for an answ3er I backup the db then tried to convert tables
in Inno db format
everything run fine now, the same query execute ok
but i prefer myIsam format i use it as default,
then I'd like to understand what is the problem,
here are all infos you requested,
thnks again for your time;
the query is right when there were few rows worked fine,
actually just to i set limit 10 but nothing same error
these are th tables;
mysql> explain mail_archive;
+--------------+----------------------+------+-----+------------+---------------
-+
| Field | Type | Null | Key | Default |
Extra |
+--------------+----------------------+------+-----+------------+---------------
-+
| message_id | int(11) | | PRI | NULL |
auto_increment |
| message_file | varchar(150) | | MUL | |
|
| subject | varchar(150) | | | |
|
| date | varchar(100) | | | |
|
| real_date | date | | | 0000-00-00 |
|
| headers | text | | | |
|
| body | text | | | |
|
| body_html | text | | | |
|
| flag | smallint(5) unsigned | | | 0 |
|
+--------------+----------------------+------+-----+------------+---------------
-
mysql> explain mail_recipients;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| foxbox_user_id | int(11) | | | 0 | |
| message_id | int(11) | | MUL | 0 | |
| name | varchar(100) | | | | |
| email | varchar(100) | | | | |
+----------------+--------------+------+-----+---------+-------+
mysql> explain mail_senders;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| foxbox_user_id | int(11) | | | 0 | |
| message_id | int(11) | | MUL | 0 | |
| name | varchar(100) | | | | |
| email | varchar(100) | | | | |
+----------------+--------------+------+-----+---------+-------+
mysql> explain mail_attachment_archive;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| message_id | varchar(150) | | | 0 | |
| file_name | varchar(150) | | | | |
| type | varchar(100) | | | | |
+------------+--------------+------+-----+---------+-------+
and finally this is the query
SELECT mail_senders.name AS mitt, mail_recipients.name AS dest,
mail_archive.message_id, mail_archive.subject, mail_archive.message_file,
mail_archive.date, mail_archive.real_date, mail_archive.*
FROM (mail_archive
INNER JOIN mail_recipients ON mail_archive.message_id =
mail_recipients.message_id)
INNER JOIN mail_senders ON mail_archive.message_id = mail_senders.message_id
order by message_id desc limit 10
it is also my thought the problem is in anyway related to space,
but can't understand were and which setting is related to this
4
2006/10/21, Dan Buettner <[EMAIL PROTECTED]>:
Adriano -
MySQL names temp tables like that (starting with # sign), in paticular
while storing a result set to be sorted. I would guess that your
query is returning a result set large enough to hit a filesystem limit
on your box. How big are you expecting the results to be? Perhaps
you are missing a join, or have a malformed query in some way, and
therefore producing a cartesian product.
You could try removing any sort you have on the data to see if the
query will return results that way, since MySQL won't attempt to sort
the data and therefore may not fill up a temp table. You could also
try a LIMIT x on your statement to see if your query is returning what
you expect.
Finally, it is very helpful in this forum if you post your table
structures, your actual query as well as the output you get from
running
EXPLAIN <query>;
Best,
Dan
On 10/21/06, adriano ghezzi <[EMAIL PROTECTED]> wrote:
> Hy thank you all, I'm getting the following err msg
>
> -> The table '#sql_2c52_0' is full
>
> running a complex query (and only running that query) with three outer join
>
> it is a simple db that stores mail msgs
>
> my query is about mail msg->recipients->sender
>
> the biggest table mail_archive has 90467 rows
>
> I checked every things I know:
> -disk space
> tables integrity
> isam check etc...
>
>
> my server is-> server version: 4.0.24_Debian-10sarge2-log
>
> all tables are myISAM tables
>
> this snippets is in csv format
> output from command:
> SHOW TABLE STATUS FROM mydbLIKE 'mail%'
>
> mysql>
>
"Name","Type","Row_format","Rows","Avg_row_length","Data_length","Max_data_length","Index_length","Data_free","Auto_increment","Create_time","Update_time","Check_time","Create_options","Comment"
>
"mail_archive","MyISAM","Dynamic",90467,4307,389686660,4294967295,2044928,0,102183,"2006-10-01
> 02:38:34","2006-10-01 23:15:31","2006-10-01 02:43:55","",""
>
"mail_attachment_archive","MyISAM","Dynamic",47107,47,2233816,4294967295,1024,0,,"2006-01-20
> 09:53:26","2006-10-01 23:15:31","","",""
>
"mail_recipients","MyISAM","Dynamic",682960,51,34918212,4294967295,5704704,19044,,"2006-01-20
> 09:53:26","2006-10-01 23:15:31","","",""
>
"mail_senders","MyISAM","Dynamic",49247,50,2497204,4294967295,421888,3560,,"2006-01-20
> 09:53:26","2006-10-01 23:15:31","2006-03-23 23:53:08","",""
>
>
> any help would be appreciate.
>
> TIA
> ag.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
