1

I am trying create a table using a cross join or Cartesian Product subquery that creates a table with a large number of rows, like at least 148 million rows. I get this

ERROR 1114 (HY000): The table is full.

I plan on filtering and querying the table later on, but how can I create this table?

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
Dataman
  • 11
  • 1
  • 4
  • 1
    Ensure you have enough disk space, may be? – mustaccio Nov 04 '17 at 15:12
  • I changed a couple of system global variables and now I can't connect to the database on Workbench or at the command line prompt. I think it was the max_heap_table_size variable that's messed things up. – Dataman Nov 04 '17 at 16:36
  • Is the disk full? Is the tmp dir separate from the root dir? If so, it it full? Don't set max_heap_table_size bigger than about 1% of RAM. What other settings did you change? – Rick James Nov 04 '17 at 17:47
  • I got the server backup and running. whew! I changed the system variable back to its original default. I changed inno_db_file_path back to ibdata1:12M:autoextend instead of ibdata:512M:autoextend. I am still curious on how to build a massive table. I set max_heap_table_size to the maximum value allowed just to experiment. – Dataman Nov 04 '17 at 19:26
  • I have 16 gb of ram (expandable to 32 gb) and a terabyte hard disk drive with 128 gb solid state drive. My cpu is corei7. I have pretty good specs, but I don't know if it's possible or my laptop. I definitely don't want to output the Cartesian (Cross Join). I do want to create the table with the cross join as a subquery, and then query and filter it. – Dataman Nov 04 '17 at 19:46
  • using show variables,I found out the location of tmp dir: C:\WINDOWS\SERVIC~2\NETWOR~1\AppData\Local\Temp – Dataman Nov 04 '17 at 21:19
  • For some reason I can't raise the value in the innodb_db_file_path ibdata12M:autoextend – Dataman Nov 15 '17 at 02:48

1 Answers1

-2

Check, if you did not run out of disk-space (this is the first step, check the tmp file)

From the comments, You seem to have a too low maximum size for your innodb_data_file_path, You cannot host more than 512MB of data in all innodb tables combined.

I think you should switch to an innodb-per-table scheme using innodb_file_per_table.

AnouarZ
  • 215
  • 2
  • 6
  • I am not sure what you mean with tmp file, but I was able to change my datadir to my d drive. I have 128 gb solid state drive, and a terabyte hard disk drive on my laptop. Mysql workbench is stored on my c drive. I think it has something to do with the settings of Mysql. – Dataman Nov 19 '17 at 05:02
  • It is becoming clearer to me that I am going to have to pay to do more. Mysql is open source after all. – Dataman Nov 20 '17 at 23:12
  • You cannot host more than 512MB of data in all innodb tables combined. you copy-pasted and didn't cite your source. That's just half the answer and that statement makes no sense without context. – Evan Carroll Jul 13 '18 at 15:09