NATS4 MySQL Settings
MySQL Server 5.x Requirement Information
NATS 4.0 requires MySQL 5.1 or greater.
Important Settings and Features
The following settings are critical in ensuring proper functionality with any Too Much Media product:
NO_UNSIGNED_SUBTRACTION
MySQL server modes are mostly mutually exclusive, so globally setting the MySQL mode will disable all other modes. The trick is how and where this mode is set. MySQL Server has 3 different places that settings can be changed or exist: the my.cnf file (MySQL configuration file), the start-up switches, and the current session settings (both globally and for individual sessions). Failure to turn ON unsigned subtraction causes potential problems with financial calculations causing absurdly large numbers to appear in place of fairly small numbers.
STRICT MODE
"Strict Mode" is one of many settings that are classified to be a "strict mode". Strict mode must be disabled in order for any Too Much Media product to function properly. If any of the following modes are enabled, they must be disabled and/or removed: TRADITIONAL, STRICT_ALL_TABLES, or STRICT_TRANS_TABLES. These settings alter how MySQL handles default data types and null field values, which can adversely affect normal operation. As stated above for NO_UNSIGNED_SUBTRACTION, if you remove any pre-existing strict mode settings and make sure that NO_UNSIGNED_SUBTRACTION is set, MySQL 5 will be configured properly for NATS, CARMA, and RSSdish.
You can check your current MySQL server mode by connecting to the MySQL database and running the following queries:
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
Please Note: Too Much Media does not configure or support MySQL. Any issues related to repairing, installing, or configuring any server related component must be directed to your Host, Server Admin, or Server Technician.
Triggers
Every version of NATS4 after 4.0.77.1 requires triggers to be created before you can begin setting up NATS. Use the following trigger commands to test that triggers can be created on your server.
CREATE TRIGGER testref BEFORE INSERT ON ip_log_0 FOR EACH ROW BEGIN END; DROP TRIGGER testref;
If you get any errors here, please ask your host to fix those issues. If triggers are not being created properly on your server, you may have to upgrade your version of MySQL to the latest version. Any version of MySQL from 5.1 on supports the use of triggers.
open_files_limit
Please make sure open_files_limit = 2048 is set in your my.cnf file. The default value on some servers is too low to allow partitioned table to work properly.
Database Copy Errors
The following error, which sometimes occur when you copy your NATS database from one server to another, means you didn't escape the MySQL reserved word, cascade:
ERROR 1064 at line 678: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'cascade char(16) NOT NULL default '', siteid int(10) unsigned
Escape the word cascade with a high comma (`) in the SQL query. Note: the high comma is also called a back-tic, and on US keyboards, it is usually left of the number 1 key.
