Mailing system doesn't work with DigitalOcean managed databases
Overview
When creating a new mailing adding a group in the recipients field triggers an error:
Possibly unhandled rejection: {"error_code":-1,"sql":"CREATE TEMPORARY TABLE civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37 (contact_id int primary key) ENGINE=MEMORY COLLATE utf8mb4_unicode_ci","debug_info":"CREATE TEMPORARY TABLE civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37 (contact_id int primary key) ENGINE=MEMORY COLLATE utf8mb4_unicode_ci [nativecode=3161 ** Storage engine MEMORY is disabled (Table creation is disallowed).]","entity":"Mailing","action":"create","is_error":1,"error_message":"DB Error: unknown error","debug_information":"CREATE TEMPORARY TABLE civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37 (contact_id int primary key) ENGINE=MEMORY COLLATE utf8mb4_unicode_ci”}
Reproduction steps
- Create a new mailing
- add a group to the recipients field
- Got error on console as well as "estimating" label get locked
- You need to have the civicrm database hosted in a DigitalOcean manage database cluster
Expected behaviour
Groups should get added and count of contacts should be shown next to the recipients field
Comments
Fixed by changing line 67 of CRM/Utils/SQL/TempTable.php from const MEMORY = 'ENGINE=MEMORY';
to const MEMORY = 'ENGINE=InnoDB';
I'm aware that you could think that this is a DigitalOcean Problem (they have unsetted the ability to set internal_tmp_mem_storage_engine to MEMORY), but I wonder if the Memory Engine is necessary at all.