{"id":9242,"date":"2024-10-01T06:01:43","date_gmt":"2024-10-01T06:01:43","guid":{"rendered":"https:\/\/www.infinitivehost.com\/knowledge-base\/?p=9242"},"modified":"2024-10-01T06:52:51","modified_gmt":"2024-10-01T06:52:51","slug":"how-to-set-sql_mode-in-vtiger-without-the-pain","status":"publish","type":"post","link":"https:\/\/www.infinitivehost.com\/knowledge-base\/how-to-set-sql_mode-in-vtiger-without-the-pain\/","title":{"rendered":"How to Set sql_mode in vTiger Without the Pain"},"content":{"rendered":"<div class='epvc-post-count'><span class='epvc-eye'><\/span>  <span class=\"epvc-count\"> 2,154<\/span><span class='epvc-label'> Views<\/span><\/div>\n<p class=\"wp-block-paragraph\">To <strong>painfully<\/strong> set the <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> for vTiger CRM, you may face several challenges due to MySQL strict mode settings. However, here&#8217;s a step-by-step guide to manually configuring and resolving issues caused by <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> in MySQL for vTiger CRM:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. <strong>Log in to MySQL\/MariaDB<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Access your server via SSH or directly from a terminal.<\/li>\n\n\n\n<li>Log in to MySQL or MariaDB as a root or admin user:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">bash mysql -u root -p<\/mark><\/code><\/li>\n\n\n\n<li>Enter your root password when prompted.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">2. <strong>Check Current SQL Mode<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Once logged in, check the current <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> to understand the current settings:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">sql SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;<\/mark><\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">3. <strong>Set the SQL Mode Temporarily (Optional)<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>To modify <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> temporarily for the current session (useful for testing), you can use:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">sql SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';<\/mark><\/code><\/li>\n\n\n\n<li>Alternatively, to set it globally until MySQL is restarted:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">sql SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';<\/mark><\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">4. <strong>Painfully Adjust the SQL Mode for Permanent Settings<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>To set the <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> permanently, you\u2019ll need to edit the MySQL configuration file (<code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">my.cnf<\/mark><\/code> or <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">my.ini<\/mark><\/code> depending on your system).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">For <strong>Linux\/Unix<\/strong> (typically <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">my.cnf<\/mark><\/code>):<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open the MySQL configuration file:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">bash sudo nano \/etc\/mysql\/my.cnf<\/mark><\/code><br>Or:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">bash sudo nano \/etc\/my.cnf<\/mark><\/code><\/li>\n\n\n\n<li>Look for the <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">[mysqld]<\/mark><\/code> section, or add it if it\u2019s missing.<\/li>\n\n\n\n<li>Under <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">[mysqld]<\/mark><\/code>, set the <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> to a value that works well with vTiger (e.g., removing strict mode):<br><code>ini<\/code><\/li>\n<\/ul>\n\n\n<p>[mysqld]<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">sql_mode = \"NO_ENGINE_SUBSTITUTION\"<\/mark><\/code> Save the changes and exit the editor.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">For <strong>Windows<\/strong> (typically <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">my.ini<\/mark><\/code>):<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">my.ini<\/mark><\/code> in a text editor (e.g., Notepad++).<\/li>\n\n\n\n<li>Locate the <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">[mysqld]<\/mark><\/code> section and add or modify the <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> as follows:<br><code>ini<\/code><\/li>\n<\/ul>\n\n\n<p>[mysqld]<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">sql_mode = \"NO_ENGINE_SUBSTITUTION\"<\/mark><\/code><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">5. <strong>Restart MySQL\/MariaDB<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>After making the changes to the configuration file, restart your MySQL or MariaDB server to apply the changes.<br>For <strong>Linux<\/strong>:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">bash sudo systemctl restart mysql<\/mark><\/code><br>For <strong>Windows<\/strong>, restart the MySQL service from the <strong>Services<\/strong> panel.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">6. <strong>Verify the Changes<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Once MySQL\/MariaDB restarts, log back in to verify the new <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> settings:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">sql SELECT @@GLOBAL.sql_mode;<\/mark><\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">7. <strong>Troubleshooting Common Pain Points<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Strict Mode Issues<\/strong>: If vTiger throws errors like <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">Incorrect integer value<\/mark><\/code> or issues with date formats, this is often due to MySQL strict mode settings (<code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">STRICT_TRANS_TABLES<\/mark><\/code>, <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">STRICT_ALL_TABLES<\/mark><\/code>). Removing these from <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> resolves these issues.<\/li>\n\n\n\n<li><strong>Query Issues<\/strong>: vTiger may generate queries that aren&#8217;t compatible with strict SQL modes. Removing strict modes like <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">ONLY_FULL_GROUP_BY<\/mark><\/code> or <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">NO_ZERO_DATE<\/mark><\/code> may solve compatibility issues.<\/li>\n\n\n\n<li><strong>Reverts After Restart<\/strong>: If the changes don&#8217;t persist, ensure you&#8217;re editing the correct configuration file and that MySQL has proper permissions to load the configuration.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Common Working <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> for vTiger CRM:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A common <code><mark style=\"background-color:#fcb900\" class=\"has-inline-color\">sql_mode<\/mark><\/code> configuration that works well for vTiger CRM is:<br><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">ini sql_mode = \"NO_ENGINE_SUBSTITUTION\"<\/mark><\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Setting the correct SQL mode is crucial for ensuring that vTiger CRM functions smoothly, especially when dealing with legacy queries or modules that are sensitive to strict MySQL rules.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Conclusion<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To simply set the sql_mode for vTiger CRM painfully, you may experience a lot of challenges just because of MySQL strict mode settings. Moreover, check out the above-mentioned comprehensive guide to manually setting up and troubleshooting problems caused by sql_mode in the case of MySQL for vTiger CRM with the <a href=\"https:\/\/www.infinitivehost.com\/managed-vtiger-solutions\"><mark style=\"background-color:#8ed1fc\" class=\"has-inline-color\"><strong>best Vtiger hosting solutions<\/strong><\/mark><\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>2,154 Views To painfully set the sql_mode for vTiger CRM, you may face several challenges due to MySQL strict mode settings. However, here&#8217;s a step-by-step guide to manually configuring and resolving issues caused by sql_mode in MySQL for vTiger CRM: 1. Log in to MySQL\/MariaDB 2. Check Current SQL Mode 3. Set the SQL Mode [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[204],"tags":[],"class_list":["post-9242","post","type-post","status-publish","format-standard","hentry","category-vtiger-solutions"],"_links":{"self":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts\/9242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/comments?post=9242"}],"version-history":[{"count":2,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts\/9242\/revisions"}],"predecessor-version":[{"id":9247,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/posts\/9242\/revisions\/9247"}],"wp:attachment":[{"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/media?parent=9242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/categories?post=9242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.infinitivehost.com\/knowledge-base\/wp-json\/wp\/v2\/tags?post=9242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}