Should we be overriding the default temp table size? (#1523) · Issues · Development / Core

Should we be overriding the default temp table size?

Overview

While working with ACLs today, I got this error message:

[nativecode=1114 ** The table 'civicrm_tmp_e_aclccache_67845b31b6526452bc0e37b6ce4c68fd' is full]

This temp table is set to use the MEMORY engine. Which makes sense - but then we can't create a table larger than MySQL's max_heap_table_size, which by default is 16MB.

Now, we should ideally never show this error. We can take one of two approaches:

We can override max_heap_table_size on a per-MySQL session basis. With our centralized temp table creation, this should be easy.
We can document this issue in the sysadmin guide, maybe under a "steps for large installations" page.

I'm inclined toward the first option - we should handle problems for sysadmins, not ask them to - but would like the input of folks who know MySQL better than me before I write a patch.

Reproduction steps

Create an ACL for a user who can view a lot of contacts (in my case, ~127,000).
As that user, search for contacts.

Current behaviour

Error above.

Expected behaviour

No error.

Overview
----------------------------------------
While working with ACLs today, I got this error message:
```
[nativecode=1114 ** The table 'civicrm_tmp_e_aclccache_67845b31b6526452bc0e37b6ce4c68fd' is full]
```

This temp table is set to use the MEMORY engine.  Which makes sense - but then we can't create a table larger than MySQL's `max_heap_table_size`, which by default is 16MB.

Now, we should ideally never show this error.  We can take one of two approaches:
* We can override `max_heap_table_size` on a per-MySQL session basis.  With our centralized temp table creation, this should be easy.
* We can document this issue in the sysadmin guide, maybe under a "steps for large installations" page.

I'm inclined toward the first option - we should handle problems for sysadmins, not ask them to - but would like the input of folks who know MySQL better than me before I write a patch.

Reproduction steps
----------------------------------------
1. Create an ACL for a user who can view a lot of contacts (in my case, ~127,000).
2. As that user, search for contacts.

Current behaviour
----------------------------------------
Error above.

Expected behaviour
----------------------------------------
No error.

To upload designs, you'll need to enable LFS. More information