Re: File full error in simple select statement

[Derek McKinnon]

That last SQL was actually erroring with an inability to connect to MySQL.

I've restarted the computer now. The code was altered to


CREATE TABLE tmp
SELECT w.weekend, d2.code AS family, d2.amount
FROM weekendings w
JOIN dr02 d2 ON (w.weekend >= d2.date AND d2.t1 <> "g")
OR (w.weekend >= d2.weekend AND d2.t1 = "g")
WHERE d2.amount IS NOT NULL;
SELECT weekend, family, SUM(amount) AS owing
FROM tmp
GROUP BY weekend, family
ORDER BY weekend, family


The tmp table was produced at 23 million rows.

The select statement again errored with a "full" message.


RAM. 32GB.

Yes, it is innodb.

I increased innodb_buffer_pool_size to 24GB.

I haven't touched the three innodb properties you mentioned. Here are their values.

@@global.innodb_thread_concurrency @@global.innodb_read_io_threads @@global.innodb_write_io_threads
33 4 4

I restarted the service.

This time it ran successfully! YEAHHHHH!!!!! (sorry, back to being professional now).

It appears to me the only difference was setting the innodb_buffer_pool_size to 3/4 of available RAM.

Is that what you think?