4

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

Thanks

EDIT: Updated my.ini with suggested changes to innodb-log-file-size, innodb_log_buffer_size, tmp_table_size, max_heap_table_size,

Improve this question
13
  • This article probably has your answer. dev.mysql.com/doc/refman/5.7/en/table-size-limit.html
    – Shaulinator
    Feb 22, 2018 at 15:05
  • I have looked at that document and edited my post to include some more information. I cannot see a reason why the query wouldn't work. Thanks.
    – SkelDave
    Feb 22, 2018 at 16:43
  • Is it innodb or myisam? I'd look at settings for your TempDB too. It feels to me like your query is trying to store all of the data into a temp table and the temp table reaches a limit and cannot grow anymore. Whether that temp table is inno or myisam would prompt where you go next for evaluating the source of the issue.
    – Shaulinator
    Feb 22, 2018 at 16:55
  • It's an innodb table - I will update my post
    – SkelDave
    Feb 22, 2018 at 17:01
  • Tblinvoice and the temp are both inno?
    – Shaulinator
    Feb 22, 2018 at 17:01

3 Answers 3

Reset to default
4

Please do not get fooled by the error message.

Note that the error is table is full. It does not say disk is full.

What would create a table is full condition ?

It has to do with the changes that are pouring into the rollback segments for a transaction.

I have addressed this many times

It appears that one of the following occurred

  • The SELECT was in the middle of a transaction
  • The SELECT attempted to hold too much rollback info in the face of many transactions

In either case, the tblinvoice table probably had so many changes pending that the SELECT just had to give up. This could have caused all write transactions against tblinvoice to rollback.

If you were running this SELECT on a busy Master with lots of writes against tblinvoice, then I could this SELECT having this problem.

What you should do is set up a reporting slave (running MySQL Replication) and run this type of query on it instead on the main server.

UPDATE 2018-02-27 10:10 EDT

Your problem may be the amount of RAM free.

You currently have this

tmp_table_size=2G
max_heap_table_size=2G

Try running the query again so that the temp table goes immediately to disk

set tmp_table_size = 1024 * 1024 * 16;
set max_heap_table_size = 1024 * 1024 * 16;
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;

Keep watch on the C:\Windows\SERVIC~2\NETWOR~1\AppData\Local folder. Watch the temp file that shows up in that folder. What I am hoping is that the temp table will reach the needed size on disk. Why ?

With tmp_table_size=2G and max_heap_table_size=2G, the temp table has to reach 2G in RAM before transferring to disk. My working theory is that you do not have 2GB of RAM free (We are talking Windows, right ???). This is why I am suggesting lowering these to 16M (which is the default value anyway). Give it a try !!!

Improve this answer
5
  • Hi, I am doing this query on a backup of the database so there are no writes to tblinvoice. I am running the query by itself outside of any explicit transaction and I am using innodb_file_per_table. I have read the posts and made the innodb_log_file_size=1G and innodb_log_buffer_size=64M and it still fails. What is the next thing you'd recommend. Thanks
    – SkelDave
    Feb 23, 2018 at 10:10
  • I have already read that having a large ibdata1 file is bad. Mine is currently 34GB. Should I look at adding ibdata2 according to your 2013 post?
    – SkelDave
    Feb 23, 2018 at 10:45
  • In my 2013 post, the client had 106GB free inside ibdata1/ibdata2 out of 2TB and still ran out of room for the undo log. Adding ibdata3 to the innodb_data_file_path along with :autoextend allowed for larger undo logs, thus it was the solution. Look for innodb_data_file_path. If autoextend is already there, then there may be another issue. If Windows caps the largest file at 32GB, then adding ibdata2 may be your solution. Give it a try and see.
    – RolandoMySQLDBA
    Feb 23, 2018 at 16:03
  • I added an ibdata2 file and I still get the table is full error. innodb_data_file_path=ibdata1:34025472K;ibdata2:10M:autoextend. Any suggestions as to what I can do next? Thanks.
    – SkelDave
    Feb 27, 2018 at 9:33
  • That seems to be the problem! Thanks for all your help.
    – SkelDave
    Mar 1, 2018 at 8:42
2 
tmp_table_size=2G
max_heap_table_size=2G

Those are dangerously large. Keep them to no more than about 1% of RAM -- about 300M. Those settings are used in tmp tables inside complex SELECTs; your query is such. If several complex queries are running at the same time, several RAM allocations of 2GB could be happening simultaneously, thereby blowing out RAM.

Temp#sql1664_349_19be looks like such a tmp table name.

ibdata1 can be arbitrarily large -- limited only by disk space (and some really big hard limit, in the terabytes, or an OS limit). Did you have autoextend on ibdata1? 34025472K is more than 32G, so I don't see it as being an OS limit.

It might be useful to see SHOW CREATE TABLE.

Please provide EXPLAIN SELECT ...

Improve this answer
0

I don't think locating big amount of RAM to MySQL parameters like,

sort_buffer_size=
join_buffer_size=
read_buffer_size=
read_rnd_buffer_size
tmp_table_size
max_heap_table_size

, is a good idea, what I know is reserving big size of RAM is a slow process, so big amount of values will slow down MySQL, and this is what I found on previous tests, so even if the system has plenty of RAM, I don't think locating high amount of RAM to similar parameters is a good idea.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.