MySQL strict mode causing loops and havoc on WHMCS domain renewals

You might find that some emails repeat, e.g. domain deletions emails. Here is how you troubleshoot:

user@server:~$ /usr/bin/php8.1 -q /home/user/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php --poll
Processing Domain Deletions:
----------------------------
Domain : example.com - No Outstanding Invoice
[WHMCS Application] ERROR: PDOException: SQLSTATE[HY000]: General error: 1364 Field 'ipaddr' doesn't have a default value in /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php:308 Stack trace: #0 /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(308): PDO->query() #1 /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(351): domainsync_logActivity() #2 /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(378): domainsync_delDomain() #3 /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(807): domainsync_processDelPoll() #4 /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(1033): domainsync_poll() #5 {main} {"exception":"[object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 1364 Field 'ipaddr' doesn't have a default value at /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php:308)"} []
PDOException: SQLSTATE[HY000]: General error: 1364 Field 'ipaddr' doesn't have a default value in /home/my/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php:308

#0 /home/user/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(308): PDO->query()
#1 /home/user/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(351): domainsync_logActivity()
#2 /home/user/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(378): domainsync_delDomain()
#3 /home/user/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(807): domainsync_processDelPoll()
#4 /home/user/public_html/modules/registrars/domainsDotCOZA/tools/domains_sync.php(1033): domainsync_poll()

This error is due to MySQL strict mode. See here:

https://docs.whmcs.com/system/database-and-storage/database-and-storage-tutorials/disable-mysql-strict-mode/

mc -e /etc/my.cnf

sql_mode=

A more logical approach is to retain the existing sql_mode settings. Follow this procedure:

1. Access the server’s command line as the ‘root’ user via SSH or “Terminal” in WHM.
2. Run the following command to determine which sql_mode options are enabled.

   mysql -sse "SELECT @@GLOBAL.sql_mode;"

3. Copy the output from the above command.
4. Open the /etc/my.cnf file in your preferred text editor.
5. Add sql-mode= to the bottom of the [mysqld] section.
6. Paste the copied output from above as the sql-mode value.
7. Remove ONLY_FULL_GROUP_BY from the sql-mode value.
8. Save the changes and exit the text editor.
9. Restart MySQL.

   /scripts/restartsrv_mysql

10. Run the following command to confirm that ONLY_FULL_GROUP_BY was removed from the enabled sql_mode options.

    mysql -sse "SELECT @@GLOBAL.sql_mode;"
    
    
    

Reference: https://support.cpanel.net/hc/en-us/articles/360050889873-How-to-disable-ONLY-FULL-GROUP-BY-in-MySQL