1

I'm getting the following error when trying to query a large amount of data from my home project database:

Error Code: 1114. The table '/var/tmp/#sql73_198dd_af' is full

The query is pulling nearly all the data from 5-6 tables that have a combined on disk size of about 3Gb.

I gather from reading around that I need to amend the my.cnf file to increase the size of my temporary tables. I'm a little uncertain as to what configuration I need to add as I'm a long way away from being an expert on mysql. The my.cnf file was pretty sparse to begin with but with a couple of additional lines it now looks like this:

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
tmp_table_size = 12G
innodb_buffer_pool_size = 12G
max_heap_table_size = 12G

I've also specified the location of the config file in System Preferences:

enter image description here

However, after restarting mysql and restarting my machine I'm still getting the same error when I try to run the query. Additionally, when I go to Server > Options File in Workbench I get a screen that says Location of MySQL configuration file (ie: my.cnf) not specified. Furthermore the Find text entry box, where I think I should be able to find and set the location, is greyed out.

It would appear I have an issue with mysql not reading my config file - could anyone point me in the right direction?

I'm also aware that I may not be adding the right settings to the config file. This should probably be the subject of another post but any tips would be greatly appreciated :)

Last, how would I find out what settings my current instance of mysql is using? I've tried running SELECT @innodb_buffer_pool_size from Workbench but I get a null result. I'm not sure if I'm not running the right command or it actually is null (unlikely!).

I'm running mysql 8.0.26 on a MacBook Pro with Big Sur 11.6 and 16G of RAM.

Thanks in advance.

Update:

I've now worked out how to point Workbench at the my.cnf file. You can do this via Database > Manage Connections... > System Profile > Configuration File:. However, I can't actually find any of the settings like innodb_buffer_pool_size! The query also still doesn't run after a restart of mysql.

Now I reckon one or more of the following things could be happening:

  1. I haven't added enough to the config file to fix the temp table issue
  2. My 12G settings aren't enough to run the query
  3. mysql still isn't reading the config file

Could someone provide me with any pointers?

Jossy
  • 589
  • 2
  • 12
  • 36
  • 1
    Maybe this might help https://www.percona.com/blog/2019/07/17/mysql-disk-space-exhaustion-for-implicit-temporary-tables/ – Thallius Sep 24 '21 at 12:24
  • There is no any restriction for temporary table size in MySQL. Check for available disk space... – Akina Sep 24 '21 at 12:39
  • @ClausBönnhoff - thanks. I've added in `max_heap_table_size` but the query still won't run. The article also refers to storing temp tables on disk but doesn't actually explain what settings to change to do this. – Jossy Sep 24 '21 at 12:43
  • @Akina - thanks. I thought temp tables were supposed to be stored in RAM? On the HDD side I have 79Gb of free space so pretty sure that's not the bottleneck... – Jossy Sep 24 '21 at 12:44
  • I guess you cannot know how big the tmp table will be. It may vary on the complexity of your query. So, as mentioned in the article, if you have a query with order or group by clauses this table can complete fill your RAM. – Thallius Sep 24 '21 at 12:46
  • @ClausBönnhoff - exactly! How can I check the settings in the `my.cnf` file have taken effect? I've tried running SELECT @innodb_buffer_pool_size from Workbench but I get a null result. I'm not sure if I'm not running the right command or it actually is null (unlikely!). – Jossy Sep 24 '21 at 12:48
  • 1
    https://stackoverflow.com/questions/1493722/mysql-command-for-showing-current-configuration-variables you musst query for the variables – Thallius Sep 24 '21 at 12:49
  • At the end I would be interesting of the use csse to query 3GB of data in one query. What is the goal of this? – Thallius Sep 24 '21 at 12:50
  • @ClausBönnhoff - I need to extract it to import into and then train a machine learning model – Jossy Sep 24 '21 at 12:52
  • why don't you use mysqldump then? – Thallius Sep 24 '21 at 12:52
  • @ClausBönnhoff - I thought that was just for exporting single or multiple tables to SQL files. I see that you can export to CSV which could work however I can't find anywhere that explains the syntax to do a dump from a query? – Jossy Sep 24 '21 at 12:57
  • https://stackoverflow.com/questions/935556/mysql-dump-by-query – Shadow Sep 24 '21 at 21:09
  • @Shadow - thanks. This seems to be more slightly focussed on exporting whole tables. There is one post that uses `OUTFILE` which I've managed to get working but this also fails with the error: `Error Code: 1114. The table '/var/tmp/#sqla79_9_16' is full` – Jossy Sep 24 '21 at 22:24
  • Nope, you can use mysql cli to export query results as well as described in the accepted answer. You can substitute any query in place of select * from mytable... – Shadow Sep 24 '21 at 22:38
  • I also have this problem, my DB is 400GB and my disk has more than 600GB of free space, yet I am getting this error... no idea why – Aviad P. Oct 04 '21 at 17:41

0 Answers0