I am getting the following error whist trying to execute a long running query.
The table 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp#sql1664_349_19be' is full
The C drive (NTFS) on the server has 135GB free space. The D drive (NTFS) which holds the data has 365GB free out of 800GB.
The server has 32GB RAM.
The query I am running is reasonably simple but it is run against 61 million rows.
I have 18 indexes on tblinvoice and the table is INNODB.
SELECT InvoiceNo, NetAmountAbs, InvoiceDate FROM tblinvoice WHERE GrossAmountAbs >= 200 GROUP BY InvoiceNo, NetAmountAbs, InvoiceDate HAVING COUNT(*) > 1 AND SUM(CASE WHEN NetAmount_Doc >= 0 THEN 1 ELSE 0 END) > 1 AND MIN(AccountNumber) != MAX(AccountNumber) AND MAX(GrossAmountAbs) != MIN(GrossAmountAbs) AND MAX(ImportID) = 2
This is my.ini file
[mysqld] log_bin_trust_function_creators=1 datadir=E:/MariaDB 10.2.8 port=3306 sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" default_storage_engine=innodb innodb_buffer_pool_size=25000M character-set-server=utf8 innodb_io_capacity=200 innodb_read_io_threads=64 innodb_thread_concurrency=0 innodb_write_io_threads=64 max_connections=1000 innodb_lock_wait_timeout=50 lower_case_table_names=2 event_scheduler=on character-set-server=utf8 max_allowed_packet=100M connect_timeout=10 innodb-log-file-size=1G innodb_log_buffer_size=64M tmp_table_size=2G max_heap_table_size=2G [client] port=3306 plugin-dir=C:/ProgramFiles/MariaDB 10.2/lib/plugin
EDIT: Updated my.ini with suggested changes to innodb-log-file-size, innodb_log_buffer_size, tmp_table_size, max_heap_table_size,