Re: Debugging Memory Engine Problem

[Charles Earl]

Rick,
Thanks. I tried placing table create as you had suggested and also reduced the tmp_table_size but to no avail. I'll include some of the variable settings also in case that rings any bells.

mysql> set tmp_table_size = 1024*1024*1024*10;
Query OK, 0 rows affected (0.00 sec)

mysql> set max_heap_table_size = 1024*1024*1024*10;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%table_size';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| max_heap_table_size | 10737418240 |
| tmp_table_size | 10737418240 |
+---------------------+-------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE points_mem (val int, x int, y int, INDEX(x,y) USING BTREE ) ENGINE = MEMORY MAX_ROWS=5000000000;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_maplarge |
+--------------------+
| points |
| points_mem |
+--------------------+
2 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+----------+
12 rows in set (0.00 sec)

mysql> INSERT INTO points_mem SELECT * FROM points;
ERROR 1114 (HY000): The table 'points_mem' is full
mysql> select count(*) from points_mem;
+-----------+
| count(*) |
+-----------+
| 102268245 |
+-----------+
1 row in set (0.00 sec)