MySqlConnect

Introduction

In May 2015, Steve Loft added SQL functionality to his Cumulus 3 software (MX beta 3.0.0).

Essentially, MX can either create and update one, or more, of three tables (by default called "realtime", "monthly", and "dayfile"; although these names can be changed by the Cumulus user) where it determines the schema (what columns appear in the database table); or MX can run SQL (devised by user) against tables (created by user) that can have any "schema" (columns in the table) chosen by user at one, or more, of three intervals (MX calls these "custom seconds", "custom minutes", and "custom rollover").

This Wiki page provides a brief introduction to SQL, and describes the settings that define how this functionality is controlled.

At last update, this Wiki page shows position as at beta build 3135, (missing some changes included in the 3.12.0 release build of 3141). It is therefore somewhat out of date, in regard to latest MX release, but it is hoped that presenting it as a separate Wiki page will make it more likely that somebody will bring it up to date.

What is SQL?

SQL is an abbreviation of "Structured Query Language", SQL is structured in the sense that keywords have to appear in the correct order, and there are rules about which words are mandatory. SQL is not just for running queries that read database tables, it can create database tables, give and revoke permissions, and do many more maintenance type tasks. SQL is a language in that each variant of SQL has a vocabulary and set of rules that are specific to that database server, although there is a sub-set, that is set by "ANSI", of words and constructions that all SQL dialects should obey.

How does MX use SQL?

MX uses two types of SQL.

MX uses SQL when updating a SQLite database (see Cumulusmx.db and Diary.db, that type of database stores data in what is known technically as a sequential file. In a sequential file, the data appears as a number of lines that have to be stored in order (by row number). Because entries can be deleted, not every possible row number has to be present, but new entries are appended at end. The various fields (or columns) are named, it is possible (but rarely done) to change the order of the columns, rewriting the entire database file. In a retrieval query you can sort ascending/descending on value in any column. You can select a row by specifying content, but that gets translated into a row number to actually determine the row that is updated.

MX also uses SQL when creating, or updating, a relational database type. The commands issued by "MySqlConnect" software work with two types of relational databases, MySQL (commercial software by Oracle) and MariaDB (free software from an independent provider). A relational database also uses the row, column, and field terminology; but there is no control over the order in which rows are stored, there is an order for columns (although you can change the column order), and rows are identified by a unique key (known as "primary key" as a row can contain a secondary key that links to data in another table). Any retrieval query can specify that what is returned from that query should be sorted in an ascending/descending order of the value(s) in specified column(s).

As mentioned earlier, MX supports (default) tables where it determines the columns in the table , and (custom) tables where the user determines the columns. For the default tables, every MySqlConnect command issued by MX specifies column names, so those columns must be present in the table, but can be in any order. (If the user adds extra columns, those extra columns must be defined with null as default value, so MX can ignore them). Some MX releases add columns that were not present in earlier MX releases, and these releases should provide a utility that will add the extra columns to existing tables.

How does MX use MySqlConnect?

MySqlConnect is used by both CumulusMX.exe and ExportToMySQL.exe, but the latter only works with two of the default tables (those called "monthly" and "dayfile", or as renamed by the user).

Both "CumulusMX.exe" and "ExportToMySQL.exe" make use of the following settings:

Executable	Function	Description
CumulusMX.exe	Data Logs menu	An option "Update MySQL on Edit" allows you to decide whether MX issues SQL when you edit a data log or "dayfile.txt", so the same edit is made to the corresponding database table, provided that the table has been created with the default name and has the column names defined by default.
CumulusMX.exe	Settings menu → MySQL settings → General Options	An option "Buffer commands on failure" allows you to decide whether MX tries to run SQL commands again, if they fail. IMPORTANT: This is to cope with failure due to the database server going down; the option should not be ticked if you are testing new "custom" SQL and may have made a mistake in the coding. You need to ensure the SQL is either generated by MX as a fixed query, or has been tested and is correct synta before considering this option.
	Settings menu → MySQL settings → Realtime.txt Upload	An option that can be enabled that automatically updates a "Realtime" table, each new row is effectively what is stored in the file realtime.txt.
	Settings menu → MySQL settings → Dayfile.txt Upload	An option that can be enabled that at end of day ensures that a new line is added to dayfile.txt and a new row (with similar data) is inserted into a "Dayfile" table.
	Settings menu → MySQL settings → Monthly log file Upload	An option that can be enabled that automatically updates a "Monthly" table, every time a new line is added to the standard data log file.
	Settings menu → MySQL settings → Custom Upload - seconds interval	An option that can be enabled that runs some predetermined SQL every time the specified number of seconds has elapsed.
	Settings menu → MySQL settings → Custom Upload - minutes interval	An option that can be enabled that runs some predetermined SQL every time the specified number of minutes has elapsed.
	Settings menu → MySQL settings → Custom Upload - at rollover	An option that can be enabled that runs some predetermined SQL just after dayfile.txt has had a new line, but before other end-of-day actions occur.
ExportToMySQL.exe	parameters to utility	Determine which file read (and therefore which table updated, using table names set in table name parameters explained in next table)

The My SQL settings page in interface

MX controls settings for the relational database tables on a page called "MySQL settings" although you may be using "MariaDB".

This document is 'Work In Progress' so content may not be complete.

Request for help from Wiki Readers

• Do you understand how MX works?
• Do you use hardware, or MX functionality, that is not yet documented? Can you begin that documenting?
• Can you contribute simple text for novice users, examples of what you have done, correction of typing or factual errors, or supply missing details?
• Will you make this page more useful by bringing content up-to-date as new releases change some information written for older releases?
• Does any page need a section for novices, so they don't need to read more technical information further down that page?
• Is there some information on this page, that should be on a separate page? Can you create the new page and move the less relevant information off this page, don't forget this page needs a link to the new page so people who expect to find it here know where it has moved to?

If you plan on contributing to the Wiki, then you will need an account.

• Please use the Request Account form to apply for an account. Note that the Wiki is currently undergoing restructuring and is largely locked for editing, but please apply for an account if you wish to contribute in the future.
• You will find help on how to contribute to this wiki at How to Edit.
• If you need to consult others, please use the Cumulus Wiki suggestions forum.

Please be aware that information on this page may be incorrect.

Since this Wiki page describes settings that frequently change, this page may be out of date, unless somebody is willing to update the Wiki when a MX release makes a change.

Predetermined SQL

The custom table options in MX require you to specify the SQL in advance of it being used, and are therefore intended for use to predetermine all the SQL you might use (conditionals can be included to decide what queries actually get executed if there are alternatives). You can enable/disable individual settings, but the mechanism is not designed to do any one-off actions like creating the custom tables.

WARNING: The SQL syntax for "My SQL Connector" used by .NET and therefore by MX, differs in various ways from the MySQL Client or MariaDB syntax.

Here is an example of what might be input as "predetermined SQL", showing how you can use conditionals and web tags:

INSERT IGNORE INTO table_name_1 (primary_key, column_name_1, column_name_2 ....) VALUES ('<#primary_key>', '<#web tag_name_1 modifier_1>', '<#web tag_name_2 modifier_2>'   ....);


BEGIN NOT ATOMIC
IF '<#web tag>' = '---' THEN 
simpler query to run if something not numeric;
ELSE 
UPDATE table_name_2 SET  column_name_1 = '<#web tag_name_1 modifier_1>', column_name_2 =  '<#web tag_name_2 modifier_2>'   ....   WHERE primary_key = '<#primary_key>';
END IF
END;

Why are Cumulus settings now split between several Wiki pages?

The basic answer is because there are a lot of settings, some get changed, and more are added as Cumulus develops!

MX has been developed very dramatically by Mark Crossley:

1. The MySQL settings for the original 3.0.0 beta were previously documented on Cumulus.ini (Beta) page, but that page has been cleared.
2. The dramatic development of MX produced considerable differences between that original MX beta, and the configuration that applied up to 3.7.0, that latter documentation remains available here
3. Even more dramatic changes to the MX configuration have been happening since 3.8.0, with the biggest changes at 3.9.2, 3.10.0, and 3.12.0; therefore, the decision was taken in June 2021 to abandon maintaining the page previously called "Cumulus.ini", and start again with a brand new page now found here!
4. Since the MySQL settings have continued to change, since 2021 they have been moved here from that last mentioned page.

All the pages, previously called "Cumulus.ini", can be found from the owning category. The original page was preserved at Cumulus.ini_(preserving_history) so look there for its editing history. That page also expands on the above summary of why that old page was replaced.

Structure of Cumulus.ini

The settings described here are stored in a file called "Cumulus.ini".

The file is divided into "File sections", each File section name is on a separate line (with no other content) and enclosed in square brackets (e.g. [Station]).

These File sections can appear in any order, by default new File sections are appended after all previous File sections, but if you prefer the File sections in another order, MX can still find them, although it may spend more time searching!

Within each File section, there are parameters. Each parameter is in format Attribute=Value, and appears on a line to itself.

The parameters, within a File section, can be in any order, by default new parameters are appended at end of the relevant File section.

Content of "Cumulus.ini" relating to MySQL

• Introduced in Cumulus MX 3.0.0 beta
• File Section is [MySQL]
• MX page is Settings menu → MySQL settings

Parameter Line Entry	MX Section	Label on Settings page	Default	Description
BufferOnFailure=0	General Options	Buffer commands on failure	0 = ignore errors	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want MX to store any commands that fail (see note above), and try them again (stored commands lost if MX is stopped) Don't tick (value shown as 0 in Cumulus.ini) if you want MX to ignore any errors
CustomMySqlMinutesCommandString=	Custom Upload - minutes interval	Option to enter "SQL command" only shown if next parameter is ticked	(empty)	See "predetermined SQL" example above
CustomMySqlMinutesEnabled=0	Custom Upload - minutes interval	Custom Minutes Enabled	0 = don't display other options	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want the previous and next options to be displayed, and actioned Don't tick (value shown as 0 in Cumulus.ini) if MX is not to action the previous and next settings
CustomMySqlMinutesIntervalIndex=6	Custom Upload - minutes interval	Interval:	6 = 10 minutes	Drop-down list of predetermined intervals: 0 = 1 minute 1 = 2 minutes 2 = 3 minutes 3 = 4 minutes 4 = 5 minutes 5 = 6 minutes 6 = 10 minutes 7 = 12 minutes 8 = 20 minutes 9 = 30 minutes 10 = 1 hour (60 minutes)
CustomMySqlRolloverCommandString=	Custom Upload - at rollover	Option to enter "SQL command" only shown if next parameter is ticked	(empty)	See "predetermined SQL" example above
CustomMySqlRolloverEnabled=0	Custom Upload - at rollover	Custom Rollover Enabled	0 = don't display other options	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want the previous, and next, options to be displayed, and actioned Don't tick (value shown as 0 in Cumulus.ini) if MX is not to action the previous and next settings
CustomMySqlSecondsCommandString=	Custom Upload - seconds interval	Option to enter "SQL command" only shown if next parameter is ticked	(empty)	See "predetermined SQL" example above
CustomMySqlSecondsEnabled=0	Custom Upload - seconds interval	Custom Seconds Enabled	0 = don't display other options	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want the previous and next options to be displayed, and actioned Don't tick (value shown as 0 in Cumulus.ini) if MX is not to action the previous and next settings
CustomMySqlSecondsInterval=10	Custom Upload - seconds interval	Interval (seconds):	10	A number field, takes 1 to 59
Database=	Server details	Database name:	(empty)	A text field where you enter the name of the database your tables are to be created/updated in
DayfileMySqlEnabled=0	Dayfile.txt upload	Dayfile Enabled	0	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want MX to at end of day ensure that a new line is added to dayfile.txt and a new row (with similar data) is inserted into a "Dayfile" table Don't tick (value shown as 0 in Cumulus.ini) if MX is only to ensure that a new line is added to dayfile.txt
DayfileTable=	Dayfile.txt upload	Table name	Dayfile	Having defined this table, and saved the settings, click the "Create Dayfile" button to create the database table with the standard column names. If you change the name here, normal rollover updates and "ExportToMySQl.exe" will use the table name you define. Depending which MX release you use, the option to update table when you edit "dayfile.txt" may fail!
Host=Localhost	Server details	Host name	(empty)	Set to whatever host name your database server has specified, if you host Your_Own_Server, then you may be able to use "Localhost".
MonthlyMySqlEnabled=0	Monthly logfile upload	Monthly Log Enabled	0	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want MX to at your standard logging interval ensure that a new line is added to standard data log file and a new row (with similar data) is inserted into a "Monthly" table. Don't tick (value shown as 0 in Cumulus.ini) if MX is only to ensure that a new line is added to standard data log file
MonthlyTable=	Monthly logfile upload	Table name	Monthly	Having defined this table, and saved the settings, click the "Create Monthly" button to create the database table with the standard column names. You might wish to change the name here once a year, to stop the database table getting enormous, if so you might name the table something like "standard2020". If you change the name here, normal updates and "ExportToMySQl.exe" will use the table name you define, but at some releases the option to update table when you edit "MMMyyyyLog.txt" may fail
Pass=	Server details	Password	(empty)	Set to whatever password is used for update access with the database user name (see below)
Port=3306	Server details	Port number	3306	The port related to the host name (defined above) for update access to your database server
RealtimeMySql1MinLimit=0	Realtime.txt upload	Limit Inserts:	0 = false	Simple tick or not Tick (value shown as 1 in Cumulus.ini) Don't tick (value shown as 0 in Cumulus.ini) See explanation beside and below setting
RealtimeMySqlEnabled=0	Realtime.txt upload	Real time Enabled	0 = don't display other options	Simple tick or not Tick (value shown as 1 in Cumulus.ini) if you want MX to at your real time interval ensure that a new realtime.txt file is created and a new row (with similar data) is inserted into a "Realtime" table. Don't tick (value shown as 0 in Cumulus.ini) if MX is only to ensure that a new line is added to standard data log file
RealtimeRetention=	Realtime.txt upload	Data Retention value and Data Retention unit	"7" and "Days"	The value field will take any number, the unit field is a dropdown: Seconds Minutes Hours Days Weeks Months Taken together, these determine the period for which rows are kept in the table named in "RealtimeTable", any row older than this specification will be deleted as part of the automatic SQL
RealtimeTable=Realtime	Realtime.txt upload	Table name	Realtime	Having defined this table, and saved the settings, click the "Create Realtime" button to create the database table with the standard column names. Remember that you can tailor the contents of "realtime.txt", if you want non-standard columns in this table, you have to create the table yourself.
UpdateOnEdit=1	General Options	Update MySQL on Edit	1 = update the table when edit the file	Simple tick or not Tick (value shown as 1 in Cumulus.ini) Don't tick (value shown as 0 in Cumulus.ini) Please see begining of previous table for explanation and warning about how all defaults must be set for this to work
User=	Server details	User name	(empty)	Database access user name, that matches with password described earlier