At 04:35 PM 6/15/2005, you wrote:
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
--- [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>:
>
> > 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
> >
> > --- [EMAIL PROTECTED] wrote:
> >
> > > hi,
> > > seems to be a temp table (sybase notation).
> > > see max_temp_table_size
> > >
> > > Mathias
> > > Selon Emmett Bishop <[EMAIL PROTECTED]>:
> > >
> > > > 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/[EMAIL PROTECTED]
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > >
> >
>
http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
>
>
>
Tripp,
This problem may occur if your table is quite large (several gb in
size). The update may make the table too large to address using
conventional MySQL pointers. You may need to modify the table so it has a
"Max Rows=nnnn" option where "nnnn" is the max rows you expect the table to
have and this forces MySQL to use a larger table pointer. When my tables
exceeded 100 million rows, I got a similar error. I added "Max
Rows=1000000000" to the table definition to solve the problem.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
