NATS4 MySQL Settings

From TMM Wiki
Jump to navigationJump to search
NATS 4
NATS Setup
Post-Installation Steps
Apache Configuration
MySQL Settings
Creating Admin Accounts
Multiple Server Setup
Using Memcached for Caching
File Upload Setup
Link Domain
HTTPS Setup
NATS4 Files and Directories
NATS4 Go Live Checklist
NATS3 to NATS4 Going Live Checklist
Moving NATS4
Members Admin
The Members Admin
View Member Details
Add Member
MySQL Auth
Mod Authn DB
Multisite Access
Member Logging
Member Password Retrieval
OpenID Connect
Mod Auth OpenIDC
ID Numbers

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.