How to Set sql_mode in vTiger Without the Pain

To painfully set the sql_mode for vTiger CRM, you may face several challenges due to MySQL strict mode settings. However, here’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

• Access your server via SSH or directly from a terminal.
• Log in to MySQL or MariaDB as a root or admin user:
  bash mysql -u root -p
• Enter your root password when prompted.

2. Check Current SQL Mode

• Once logged in, check the current sql_mode to understand the current settings:
  sql SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

3. Set the SQL Mode Temporarily (Optional)

• To modify sql_mode temporarily for the current session (useful for testing), you can use:
  sql SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
• Alternatively, to set it globally until MySQL is restarted:
  sql SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

4. Painfully Adjust the SQL Mode for Permanent Settings

• To set the sql_mode permanently, you’ll need to edit the MySQL configuration file (my.cnf or my.ini depending on your system).

For Linux/Unix (typically my.cnf):

• Open the MySQL configuration file:
  bash sudo nano /etc/mysql/my.cnf
  Or:
  bash sudo nano /etc/my.cnf
• Look for the [mysqld] section, or add it if it’s missing.
• Under [mysqld], set the sql_mode to a value that works well with vTiger (e.g., removing strict mode):
  ini

[mysqld]

sql_mode = "NO_ENGINE_SUBSTITUTION" Save the changes and exit the editor.

For Windows (typically my.ini):

• Open my.ini in a text editor (e.g., Notepad++).
• Locate the [mysqld] section and add or modify the sql_mode as follows:
  ini

[mysqld]

sql_mode = "NO_ENGINE_SUBSTITUTION"

5. Restart MySQL/MariaDB

• After making the changes to the configuration file, restart your MySQL or MariaDB server to apply the changes.
  For Linux:
  bash sudo systemctl restart mysql
  For Windows, restart the MySQL service from the Services panel.

6. Verify the Changes

• Once MySQL/MariaDB restarts, log back in to verify the new sql_mode settings:
  sql SELECT @@GLOBAL.sql_mode;

7. Troubleshooting Common Pain Points

• Strict Mode Issues: If vTiger throws errors like Incorrect integer value or issues with date formats, this is often due to MySQL strict mode settings (STRICT_TRANS_TABLES, STRICT_ALL_TABLES). Removing these from sql_mode resolves these issues.
• Query Issues: vTiger may generate queries that aren’t compatible with strict SQL modes. Removing strict modes like ONLY_FULL_GROUP_BY or NO_ZERO_DATE may solve compatibility issues.
• Reverts After Restart: If the changes don’t persist, ensure you’re editing the correct configuration file and that MySQL has proper permissions to load the configuration.

Common Working sql_mode for vTiger CRM:

• A common sql_mode configuration that works well for vTiger CRM is:
  ini sql_mode = "NO_ENGINE_SUBSTITUTION"

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.

Conclusion

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 best Vtiger hosting solutions.