Answers.Body: <p>I would edit CRM/Utils/SQL/TempTable.php and change the 3 places where it says <code>MEMORY</code> to <code>InnoDB</code> instead so it always uses InnoDB.</p>
  <p>I would also then enable the scheduled job <a href="https://docs.civicrm.org/user/en/latest/initial-set-up/scheduled-jobs/#job_cleanup" rel="nofollow noreferrer">Clean-up Temporary Data and Files</a> just to make sure those tables get cleaned up.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-16T03:35:05.013Z
Answers.Text: But then I would have to do that change every time I upgrade civicrm, wouldn't I?
  
  Also, wouldn't it be much easier to change `const MEMORY = 'ENGINE=MEMORY';` of line 67 to `const MEMORY = 'ENGINE=InnoDB';` ??
Answers.UserDisplayName: Robert Garrigos
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-16T03:38:59.813Z
Answers.Text: Confirmed that this fixed the problem, but I don't like to change the source code...
  
  Many thanks, anyway!
Answers.UserDisplayName: Robert Garrigos
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-16T13:25:25.397Z
Answers.Text: It might not be too hard to make that more configurable with a setting. Are you up for [making a PR](https://docs.civicrm.org/dev/en/latest/core/contributing/#open-a-pull-request)?
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-17T15:51:12.087Z
Answers.Text: Sure. I'm working on it: https://lab.civicrm.org/dev/core/-/issues/4782
Answers.UserDisplayName: Robert Garrigos
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-17T17:29:34.693Z
Answers.Text: Done: https://github.com/civicrm/civicrm-core/pull/28191
Answers.UserDisplayName: Robert Garrigos
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-16T02:44:53.030Z
Answers.LastActivityDate: 2023-11-16T02:44:53.030Z
Answers.UserDisplayName: Demerit
Body: <p>I have this error:</p>
  <p>Possibly unhandled rejection: {&quot;error_code&quot;:-1,&quot;sql&quot;:&quot;CREATE TEMPORARY TABLE <code>civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37</code> (contact_id int primary key) ENGINE=MEMORY COLLATE utf8mb4_unicode_ci&quot;,&quot;debug_info&quot;:&quot;CREATE TEMPORARY TABLE <code>civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37</code> (contact_id int primary key) ENGINE=MEMORY COLLATE utf8mb4_unicode_ci [nativecode=3161 ** Storage engine MEMORY is disabled (Table creation is disallowed).]&quot;,&quot;entity&quot;:&quot;Mailing&quot;,&quot;action&quot;:&quot;create&quot;,&quot;is_error&quot;:1,&quot;error_message&quot;:&quot;DB Error: unknown error&quot;,&quot;debug_information&quot;:&quot;CREATE TEMPORARY TABLE <code>civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37</code> (contact_id int primary key) ENGINE=MEMORY COLLATE utf8mb4_unicode_ci&quot;}</p>
  <p>on a civicrm installation which uses a managed database at DigitalOcean.</p>
  <p>This happens when adding a new group as recipient of a mailing. Instead of showing the recipients' count, civicrm shows an &quot;estimating&quot; locked message.</p>
  <p>I'm pretty sure this is not a problem with civicrm, but with the managed database. Sorry to ask this here, but I'm stuck. Any ideas how to fix this? Thanks.</p>
  <p>SOLUTION: Demerit's answer helped me to work around the problem, but the solution came from setting internal_tmp_mem_storage_engine to MEMORY with <a href="https://docs.digitalocean.com/reference/api/api-reference/#operation/databases_patch_config" rel="nofollow noreferrer">DigitalOcean API</a>. You need to wait some time for the new setting to have effect.</p>
  <p>UPDATE: DigitalOcean disabled the ability to set internal_tmp_mem_storage_engine by setting disabled_storage_engines to &quot;ARCHIVE,BLACKHOLE,CSV,MEMORY,MRG_MYISAM,MyISAM&quot;, so the workaround has become the solution :-(</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-06-10T21:05:37.823Z
Comments.Text: Any update if you got digital ocean to allow the MEMORY storage engine on managed DB?
Comments.UserDisplayName: Jose Torres
Tags: <mysql><digitalocean>
Title: Storage engine MEMORY is disabled with digitalocean managed database
ThreadText: # Storage engine MEMORY is disabled with digitalocean managed database
  
  (Asked by: username_0 on 2023-11-16)
  
  I have this 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"}
  
  on a civicrm installation which uses a managed database at DigitalOcean.
  
  This happens when adding a new group as recipient of a mailing. Instead of showing the recipients' count, civicrm shows an "estimating" locked message.
  
  I'm pretty sure this is not a problem with civicrm, but with the managed database. Sorry to ask this here, but I'm stuck. Any ideas how to fix this? Thanks.
  
  SOLUTION: username_2's answer helped me to work around the problem, but the solution came from setting internal\_tmp\_mem\_storage\_engine to MEMORY with [DigitalOcean API](https://docs.digitalocean.com/reference/api/api-reference/#operation/databases_patch_config). You need to wait some time for the new setting to have effect.
  
  UPDATE: DigitalOcean disabled the ability to set internal\_tmp\_mem\_storage\_engine by setting disabled\_storage\_engines to "ARCHIVE,BLACKHOLE,CSV,MEMORY,MRG\_MYISAM,MyISAM", so the workaround has become the solution :-(
  
  **Tags:** mysql, digitalocean
  
  > **Comments:**
  > **username_1**: Any update if you got digital ocean to allow the MEMORY storage engine on managed DB?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2023-11-16. Score: 2 | Accepted answer
  
  I would edit CRM/Utils/SQL/TempTable.php and change the 3 places where it says `MEMORY` to `InnoDB` instead so it always uses InnoDB.
  
  I would also then enable the scheduled job [Clean-up Temporary Data and Files](https://docs.civicrm.org/user/en/latest/initial-set-up/scheduled-jobs/#job_cleanup) just to make sure those tables get cleaned up.
  
  > **Comments:**
  > **username_0**: But then I would have to do that change every time I upgrade civicrm, wouldn't I?  Also, wouldn't it be much easier to change `const MEMORY = 'ENGINE=MEMORY';` of line 67 to `const MEMORY = 'ENGINE=InnoDB';` ??  
  > **username_0**: Confirmed that this fixed the problem, but I don't like to change the source code...  Many thanks, anyway!  
  > **username_2**: It might not be too hard to make that more configurable with a setting. Are you up for [making a PR](https://docs.civicrm.org/dev/en/latest/core/contributing/#open-a-pull-request)?  
  > **username_0**: Sure. I'm working on it: https://lab.civicrm.org/dev/core/-/issues/4782  
  > **username_0**: Done: https://github.com/civicrm/civicrm-core/pull/28191  
  
  ---
  
  