Answers.Body: <p>You probably need to delete &amp; rebuild the triggers in your CMS DB so they point to your new separate LOG DB.</p>
  <p>References:</p>
  <p><a href="https://docs.civicrm.org/sysadmin/en/latest/troubleshooting/#trigger-rebuild" rel="nofollow noreferrer">https://docs.civicrm.org/sysadmin/en/latest/troubleshooting/#trigger-rebuild</a></p>
  <p><a href="https://civicrm.stackexchange.com/questions/45028/are-there-cv-commands-for-these-operations">Are there &#39;cv&#39; commands for these operations?</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-07T14:59:27.733Z
Answers.Text: Thanks for the pointer. I dropped all the existing triggers in the the CMS DB where civiCRM data exists, and then called:
  
  https://mydomain.com/administrator/?option=com_civicrm&task=civicrm/menu/rebuild?reset=1&triggerRebuild=1
  
  But no triggers were created in the CMS DB.
  
  The civicrm.settings.php file has CIVICRM_LOGGING_DSN set to point to the new logging database.
  
  Any thoughts as to why the triggers wouldn't be recreated?
Answers.UserDisplayName: A. Block
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-07T15:07:47.170Z
Answers.Text: Is your logging set to "yes" in Civi Admin?
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-07T17:32:21.330Z
Answers.Text: Excellent question! And I tried to enable it, but the setting won't save (which is odd since any other changes that I make do save without error). See https://1drv.ms/v/s!ApZ2h3V5HB1io_47h89O4h8X4maF2Q?e=fHElmQ
Answers.UserDisplayName: A. Block
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-07T19:06:17.603Z
Answers.Text: Sounds like the old triggers are still in place. I faced something similar when I split off the civi logs to a separate db. In my case, the old triggers in the cms db were still there and I couldn't save the logging setting like you. You're likely getting that "insufficient privileges" error in the Civi UI when trying to save the logging value because the old `civicrm_setting_after_update` trigger on the `civicrm_setting` table is still in place. I'd suggest going into PhpMyAdmin, open your cms db, drop all the triggers (at least all the Civicrm triggers)...
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-07T19:06:57.513Z
Answers.Text: ... then you can go back into the Civi UI and turn ON logging again. Once you have logging turned on, then you can rebuild your triggers using the URL command above.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T17:26:39.280Z
Answers.Text: It's very odd. I've deleted all the triggers in the CMS database, and I've dropped all the log_civicrm_* tables. I've updated the civicrm.settings.php file to set the DSN for logging to point to the new DB. In civiCRM status page I can rebuild the triggers, but still I get the [DB Error: insufficient permissions] when I try to set Logging to Yes. If I set Logging to Yes, and change another option on the page Misc (Undelete, PDFs, Limits, Logging, etc.) the other option will save, but not the Yes for logging. So mysterious!
Answers.UserDisplayName: A. Block
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T17:27:01.367Z
Answers.Text: ... The logging DB has a different username and password, but that shouldn't matter, since it is correctly specified in the setttings.php file.
Answers.UserDisplayName: A. Block
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-10T19:24:47.527Z
Answers.Text: I think you need to use the same MySQL username and password for both databases (Civicrm and Civicrm Log). The triggers run entirely within the MySQL layer. The DSN settings in civicrm.settings.php don't affect the running of the triggers. That's for reading/writing from the PHP app layer. If you take a look at one of your triggers (on your cms db), you'll see it's trying to do a SQL INSERT on the corresponding log table in your civicrm log db. I would try using the same user/pw on both dbs.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-13T14:19:21.387Z
Answers.Text: Oh awesome, that is the solution! Thank you so much for the back and forth to sort this out. Hopefully others will find this information helpful!
Answers.UserDisplayName: A. Block
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-13T15:21:34.647Z
Answers.Text: Awesome! Happy to hear it worked for you.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-30T19:18:24.663Z
Answers.LastActivityDate: 2024-04-30T19:18:24.663Z
Answers.UserDisplayName: Justin657
Body: <p>I have a site running civiCRM 5.72.1 on Joomla 3.10.12.</p>
  <p>My civicrm_log tables are huge, so I planned to separate them from the Joomla database where they have been 5+ years.</p>
  <p>To do this I:</p>
  <ul>
  <li>Created a new mysql DB and user with full permissions (<a href="https://app.screencast.com/W4b3XM38FE3Qh" rel="nofollow noreferrer">https://app.screencast.com/W4b3XM38FE3Qh</a>)</li>
  <li>Set Logging to No in civiAdmin</li>
  <li>Updated civicrm.settings.php in both administrator/components/civicrm and /components/civicrm to have CIVICRM_LOGGING_DSN point to the new mysql DB</li>
  <li>Exported the log tables from the CMS DB</li>
  <li>Imported them into the new DB.</li>
  <li>Returned to civiAdmin and set Logging to Yes</li>
  </ul>
  <p>When I save the changes in civiCRM, I see the error [DB Error: insufficient permissions]</p>
  <p>Is there something I'm missing? Why would I get this error (only happens if I try to enable Logging)?</p>
  
Tags: <logging>
Title: SOLVED - DB Error: insufficient permissions when separating log tables from main civiCRM DB
ThreadText: # SOLVED - DB Error: insufficient permissions when separating log tables from main civiCRM DB
  
  (Asked by: username_0 on 2024-04-30)
  
  I have a site running civiCRM 5.72.1 on Joomla 3.10.12.
  
  My civicrm\_log tables are huge, so I planned to separate them from the Joomla database where they have been 5+ years.
  
  To do this I:
  
  * Created a new mysql DB and user with full permissions (<https://app.screencast.com/W4b3XM38FE3Qh>)
  * Set Logging to No in civiAdmin
  * Updated civicrm.settings.php in both administrator/components/civicrm and /components/civicrm to have CIVICRM\_LOGGING\_DSN point to the new mysql DB
  * Exported the log tables from the CMS DB
  * Imported them into the new DB.
  * Returned to civiAdmin and set Logging to Yes
  
  When I save the changes in civiCRM, I see the error [DB Error: insufficient permissions]
  
  Is there something I'm missing? Why would I get this error (only happens if I try to enable Logging)?
  
  **Tags:** logging
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2024-04-30. Score: 1
  
  You probably need to delete & rebuild the triggers in your CMS DB so they point to your new separate LOG DB.
  
  References:
  
  <https://docs.civicrm.org/sysadmin/en/latest/troubleshooting/#trigger-rebuild>
  
  [Are there 'cv' commands for these operations?](https://civicrm.stackexchange.com/questions/45028/are-there-cv-commands-for-these-operations)
  
  > **Comments:**
  > **username_0**: Thanks for the pointer. I dropped all the existing triggers in the the CMS DB where civiCRM data exists, and then called:  https://mydomain.com/administrator/?option=com_civicrm&task=civicrm/menu/rebuild?reset=1&triggerRebuild=1  But no triggers were created in the CMS DB.  The civicrm.settings.php file has CIVICRM_LOGGING_DSN set to point to the new logging database.  Any thoughts as to why the triggers wouldn't be recreated?  
  > **username_1**: Is your logging set to "yes" in Civi Admin?  
  > **username_0**: Excellent question! And I tried to enable it, but the setting won't save (which is odd since any other changes that I make do save without error). See https://1drv.ms/v/s!ApZ2h3V5HB1io_47h89O4h8X4maF2Q?e=fHElmQ  
  > **username_1**: Sounds like the old triggers are still in place. I faced something similar when I split off the civi logs to a separate db. In my case, the old triggers in the cms db were still there and I couldn't save the logging setting like you. You're likely getting that "insufficient privileges" error in the Civi UI when trying to save the logging value because the old `civicrm_setting_after_update` trigger on the `civicrm_setting` table is still in place. I'd suggest going into PhpMyAdmin, open your cms db, drop all the triggers (at least all the Civicrm triggers)...  
  > **username_1**: ... then you can go back into the Civi UI and turn ON logging again. Once you have logging turned on, then you can rebuild your triggers using the URL command above.  
  > **username_0**: It's very odd. I've deleted all the triggers in the CMS database, and I've dropped all the log_civicrm_* tables. I've updated the civicrm.settings.php file to set the DSN for logging to point to the new DB. In civiCRM status page I can rebuild the triggers, but still I get the [DB Error: insufficient permissions] when I try to set Logging to Yes. If I set Logging to Yes, and change another option on the page Misc (Undelete, PDFs, Limits, Logging, etc.) the other option will save, but not the Yes for logging. So mysterious!  
  > **username_0**: ... The logging DB has a different username and password, but that shouldn't matter, since it is correctly specified in the setttings.php file.  
  > **username_1**: I think you need to use the same MySQL username and password for both databases (Civicrm and Civicrm Log). The triggers run entirely within the MySQL layer. The DSN settings in civicrm.settings.php don't affect the running of the triggers. That's for reading/writing from the PHP app layer. If you take a look at one of your triggers (on your cms db), you'll see it's trying to do a SQL INSERT on the corresponding log table in your civicrm log db. I would try using the same user/pw on both dbs.  
  > **username_0**: Oh awesome, that is the solution! Thank you so much for the back and forth to sort this out. Hopefully others will find this information helpful!  
  > **username_1**: Awesome! Happy to hear it worked for you.  
  
  ---
  
  