List:General Discussion« Previous MessageNext Message »
From:Emmett Bishop Date:June 15 2005 9:35pm
Subject:Re: Table full
View as plain text  
Mathias,

Here's the query:

UPDATE customer_indicator INNER JOIN
customer_listing_pref
ON customer_listing_pref.customer_id =
customer_indicator.customer_id
AND customer_listing_pref.store_id =
customer_indicator.store_id
AND customer_listing_pref.store_id = @OLD_STORE_ID
LEFT JOIN contact_log ON contact_log.customer_id = 
customer_indicator.customer_id AND
contact_log.store_id = @OLD_STORE_ID
LEFT JOIN sent ON sent.pref_id =
customer_listing_pref.pref_id 
SET customer_indicator.employee_id = @NEW_EMPLOYEE_ID,
customer_indicator.store_id = @NEW_STORE_ID, 
customer_listing_pref.store_id = @NEW_STORE_ID, 
sent.store_id = @NEW_STORE_ID,
contact_log.store_id = @NEW_STORE_ID
WHERE customer_indicator.employee_id =
@OLD_employee_id 
AND customer_indicator.store_id = @OLD_STORE_ID
AND customer_indicator.customer_id BETWEEN 20000 AND
23000;

+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| lower_case_table_names | 0        |
| max_heap_table_size    | 16777216 |
| max_tmp_tables         | 32       |
| table_cache            | 64       |
| table_type             | MYISAM   |
| tmp_table_size         | 33554432 |
+------------------------+----------+

I don't explicitly create any tables for this
operation. I'm just trying to run the query. If I make
the range in the BETWEEN condition of the WHERE claus
sufficiently small the query runs. Otherwise I get the
table is full error.

So it seems that MySQL is doing some table creation
behind the scenes. I pretty certain that I have enough
disk space to perform the operation (I have about 10GB
free).

Thanks,

Tripp


--- mfatene@stripped wrote:

> sorri it's tmp_table_size.
> 
> mysql> show variables like '%table%';
> +------------------------+----------+
> | Variable_name          | Value    |
> +------------------------+----------+
> | innodb_file_per_table  | OFF      |
> | innodb_table_locks     | ON       |
> | lower_case_table_names | 1        |
> | max_heap_table_size    | 16777216 |
> | max_tmp_tables         | 32       |
> | table_cache            | 256      |
> | table_type             | InnoDB   |
> | tmp_table_size         | 9437184  |
> +------------------------+----------+
> 8 rows in set (0.00 sec)
> 
> 
> What are :
> show create table toto;
> the count(*) ?
> the query ?
> 
> 
> 
> Mathias
> Selon Emmett Bishop <dyne_erg@stripped>:
> 
> > Mathias,
> >
> > Thanks for the reply. I couldn't find a server
> > variable named "max_temp_table_size" but I did
> find
> > one named "max_heap_table_size". Is that what you
> > meant? BTW, I forgot to mention that I'm using
> MySQL
> > 4.0.20. Could it be that this variable that you
> > mention is only in later versions?
> >
> > Basically, what I'm trying to do is a multi-table
> > update statement. If I select too many rows I get
> the
> > "Table #sql-123 is full" error. If I bite off a
> small
> > enough chunk, the query works.
> >
> > Thanks again,
> >
> > Tripp
> >
> > --- mfatene@stripped wrote:
> >
> > > hi,
> > > seems to be a temp table (sybase notation).
> > > see max_temp_table_size
> > >
> > > Mathias
> > > Selon Emmett Bishop <dyne_erg@stripped>:
> > >
> > > > Howdy all, I have a question about a SQL
> statement
> > > > that I'm trying to execute. When I execute the
> > > > statement I get the following error: The table
> > > > '#sql_bd6_3' is full.
> > > >
> > > > What does this mean exactly?
> > > >
> > > > Thanks,
> > > >
> > > > Tripp
> > > >
> > > >
> > > >
> > > > __________________________________
> > > > Yahoo! Mail Mobile
> > > > Take Yahoo! Mail with you! Check email on your
> > > mobile phone.
> > > > http://mobile.yahoo.com/learn/mail
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives:
> http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > >
> http://lists.mysql.com/mysql?unsub=1
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > >
> >
>
http://lists.mysql.com/mysql?unsub=1
> > >
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
> 
> 
> 



		
__________________________________ 
Do you Yahoo!? 
Make Yahoo! your home page 
http://www.yahoo.com/r/hs
Thread
How to write subqueries?Unknown Sender14 Jun
  • Re: How to write subqueries?SGreen14 Jun
    • RE: How to write subqueries?Rhino14 Jun
  • RE: How to write subqueries?Rhino14 Jun
  • Re: How to write subqueries?mfatene14 Jun
    • Re: How to write subqueries?Unknown Sender14 Jun
      • Re: How to write subqueries?mfatene14 Jun
        • Table fullEmmett Bishop15 Jun
          • Re: Table fullmfatene15 Jun
            • Re: Table fullEmmett Bishop15 Jun
              • Re: Table fullmfatene15 Jun
                • Re: Table fullEmmett Bishop15 Jun
                  • Re: Table fullmos16 Jun
                    • Re: Table fullEmmett Bishop16 Jun
                      • Re: Table fullmfatene16 Jun
        • Re: How to write subqueries?Unknown Sender15 Jun
    • Re: How to write subqueries?Unknown Sender14 Jun