MySqlConnect

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

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.

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 collection of languages. 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).

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". Here is a screenshot of that page (with all sections closed up) as applicable from release 3.20.0 (build 3202):

Please note, the text included elsewhere on this Wiki page may be for different release.

Changes by MX release

This Wiki page was created 20 August 2022, by doing a "cut" from the Cumulus.ini page as it had existed prior to then, and a "paste" onto this page.

1. In May 2015, Steve Loft added SQL functionality to his Cumulus 3 software (MX beta 3.0.0).
2. The information transferred in the cut/paste mentioned above related to the beta build 3135 of 3.12.0 (before subsequent development that led to formal release on 29 July 2021).
3. Further development of MX, by Mark Crossley, resulted in changes at releases between 3.20.0 and 3.21.1, which have been reported below.

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.

Whether this page reflects the latest MX release, depends on whether there have been any further developments, and whether any contributor has kept this page up to date.

Release	Date	Description
3.0.0 b.3025	Fri 22 May 2015 (Beta release)	Cumulus can now update a MySQL database. There are six options: The first three are for realtime.txt data, monthly logfile data, and dayfile.txt data; schema as ImportCumulusFile script. If respective option enabled, the appropriate SQL upload will take place when Cumulus creates the realtime.txt file, or appends a line to a monthly log or dayfile.txt. The MySQL settings screen has buttons for creating the tables, once you have submitted the configuration details. The last three options are for 'custom' MySQL uploads. For these, you need to supply the SQL insert statement, using webtags for the data, and you need to have created the table yourself. See forum announcement for example SQL.
3.12.0 b.3134-BETA	Thu 29 July 2021 (Released as b.3140)	New: Adds the ability to buffer failed MySQL commands until the MySQL server becomes available again, or Cumulus MX is restarted - when they will be lost Enabled via an option in MySQL Settings Note: Whilst Realtime updates are buffered, the uploading of failed queries is only performed by the Log updates
3.20.0 b.3199 commit a (31 July 2022)	Sun 21 Aug 2022 (Released as b.3202)	Multiple changes: New: Custom MySQLConnector Uploads (seconds, minutes, rollover) can now each have up to 10 commands: The 10 commands are not available if MX is stopped and restarted, due to bugs: A copy and paste of code from 3.20.0 - b3197 beta (which changed Custom HTTP seconds/minutes/rollover strings so set to 10 URLs) error, meant the 10 SQL custom seconds, custom minute, commands are stored in [Http] section of file Another copy and paste error meant that custom rollover comands are only stored if custom minutes are enabled Change: Changes to dayfile MySQLConnector query, add specific reference to dayfile table, add extra columns for rain24hour A one-off MySQL script in the MXutils folder to alter existing dayfile table adding the 2 new extra columns for rain24hour Change: In interface, the MySQL Settings page gets new functions for checking number of columns, allowing updating of existing tables by adding columns to match the current schema
3.21.0 b.3204	Fri 2 Sept 2022	Failed MySQL commands are stored in SQLite RecentData database table "SqlCache" to persist when CMX stopped/restarted (30 Aug commit 1) and can be individually edited/deleted (29 Aug commit 3 and 30 Aug commit 3)
3.21.1 b.3205	Sun 4 Sept 2022	The 10 custom MySQLConnector Uploads (seconds, minutes, rollover) are now stored in/read from [MySql] section of Cumulus.ini, so now preserved when CMX stopped/restarted. Also the bug re custom rollover is fixed, its commands are stored if custom rollover is enabled.

Introduction to the 6 SQL update options

MX supports (default) tables where it determines the columns in the table , and (custom) tables where it determines when actions take place.

The MX default database tables

There are three default tables (by default called "realtime", "monthly", and "dayfile"; although these names can be changed by the Cumulus user) where Cumulus determines the schema (what columns appear in the database table)

For the default tables, every MySqlConnect command issued by MX specifies column names. 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. The commands generated by CMX will fail if all named columns are not present in the table.

Creation of default tables

You must define how to access your database server, enable the particular table and indicate what the table is to be called first. Then you must click Save settings so all those details are registered before any create command will work.

For each default table, MX provides a button which (after database server details, table name and other settings have been saved) can send a CREATE table_name command to your database server. If a table with that name already exists, or certain other standard errors happen, MX can give you feedback. MX will also tell you when the SQL has worked, and the table has been created.

Note that as MX names the columns in any commands it generates, the named columns can be in any order in the table. The default order (as named in CREATE table_name command) represents when the relevant derivative was added to the corresponding Cumulus file. If you have the knowledge of the SQL command required to, or can use a tool (such as PhpMyAdmin) that provides a user-friendly interface to do this, reorganise column order; then you can collect all wind-related columns together, all rain-related columns together, and so on, or simply put what you regard as most important columns before others.

Modifying schema (columns in table)

Some release announcements for MX mention that extra columns have been added to a particular table. A script may be provided (either in release announcement, or in the MXutils directory within the download, that you can run to add the extra columns. For example b3089-AlterMySqlTables.sql was provided in the MXutils directory with build 3089 to add the Feels Like temperature columns

In release 3.20.0, v3.20.0-AlterMySqlTables.sql was provided to edit the "dayfile" table and add 3 new columns (cumulative chill hours, highest 24 hour rainfall, and time when highest 24 hour rainfall ended).

Release 3.20.0 as seen on screenshot below provides buttons (under heading of Update database table) for each of the default tables. The code here is rather crude, it counts the number of columns currently defined in the table (does not check what names those columns have, nor what properties those columns have) and compares against number of columns that MX can automatically insert/update at that release in that table. It assumes columns appear in same order as the fields in related file, and modifies the table to add the extra columns in the correct position to match the respective file.

(If a Cumulus user adds extra columns to a default table, those extra columns must be defined with null as default value, so MX can ignore them, but the columns can be in any position).

Populating rows that do not exist

Use the utility described at Software#Export_To_MySQL for monthly or dayfile tables.

The only way to populate realtime is via the action described in settings page description.

Populating missing/incorrect columns in existing rows

The options provided in the Data logs menu of the interface can be used to edit a single line of a file, and there is a setting that lets that edit also update the corresponding single row of a default table.

Whilst it may not take long to send SQL for a single line update to your database server, it will take a lot of time to select each line in file in turn, and to send the SQL to insert every line to your database server.

To update one or more columns in multiple rows, you need to generate a succession of UPDATE queries, keeping the text to send to the database server as short as possible.

One way to do this is to open the relevant file using a spreadsheet (e.g. Libre Office has a "calc" option, this is free and available for most operating systems). Create an extra column after existing columns in the spreadsheet for the "primary key", you should be able to generate this from the first one or two columns of the spreadsheet with some manipulation. Now "Hide" all the columns except those with data that you want to include in the update, and the primary key column. With some spreadsheet skills (see https://cumulus.hosiene.co.uk/viewtopic.php?p=165767#p165767) you can generate the required SQL in this format:

UPDATE name-of-table SET first-column-name=first-row-and-first-column-value, second-column-name=first-row-and-second-column-value WHERE primary-key-column-name=first-row-primary-key-value;
UPDATE name-of-table SET first-column-name=second-row-and-first-column-value, second-column-name=second-row-and-second-column-value WHERE primary-key-column-name=second-row-primary-key-value;

Alternatively, if you have skills in a script language like PHP Hypertext Preprocessor (PHP) you can write a small script that reads the file within a loop, picks the fields required from the line of the file, and generates the SQL (as above), and after ending loop closes file and sends the SQL to the database server.

Custom Interval actions on tables

MX can run SQL (using MySqlConnect commands) that work with Oracle's MySQL, or the MariaDB, database servers at one, or more, of three intervals (MX calls these "custom seconds", "custom minutes", and "custom rollover").

You can enable/disable individual settings, but the mechanism is not designed to do any one-off actions like creating the custom tables.

The Cumulus user can create any number of custom tables, where the user chooses the "schema" (columns in the table). At each custom interval, actions can affect any number of these pre-created tables; and in fact you can define several commands to run in succession against any one table.

Predetermined SQL

The custom table options in MX require you to specify the SQL in advance of it being used.

This means you have to predetermine all the SQL you might use (conditionals can be included to decide what queries actually get executed if there are alternatives).

WARNING: The SQL syntax for "My SQL Connector" used by .NET and therefore by MX, differs in various ways from the Oracle 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>', '<#tag_name_1 modifier_1>', '<#tag_name_2 modifier_2>'   ....);


BEGIN NOT ATOMIC
IF '<#tag_name>' = '---' THEN 
simpler query to run (perhaps creating a row for a particular Cumulus tag value);
ELSE 
UPDATE table_name_2 SET  column_name_1 = '<#tag_name_1 modifier_1>', column_name_2 =  '<#tag_name_2 modifier_2>'   ....   WHERE primary_key = '<#primary_key>';
END IF
END;

From MX release 3.21.1, the INSERT and conditional can be defined in separate command boxes on the settings page.

Structure of Cumulus.ini

The settings described here are held internally (stored in RAM) while CMX is running.

If you click Save settings on the Settings → MySQL settings page, then the settings are also stored in a file called "Cumulus.ini".

File sections

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. [MySQL]).

These File sections can appear in any order, MX has a default order set by the order in which the sections appear in the code that writes to the file.

If you prefer the File sections in another order, you can stop MX, and then rearrange the file section order. On restart of the software, MX will still find them, although it may spend more time searching! However, some MX releases discard existing file, and create a new file, so then order will revert too what MX chooses.

Parameters

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.

Steve Loft recommended that the user sorted the parameters alphabetically, this was because the file used to have to be edited manually as many settings were not included on settings pages. Having the parameters in alphabetical order made it easier to find what to edit, and to check a parameter did not appear more than once, as Cumulus ignores any duplicates. Now all settings are controlled by the interface, CMX determines the parameter order.

Content of "Cumulus.ini" relating to MySQL

• File Section is [MySQL]
• MX page is Settings menu → MySQL settings

Parameter Line Entry	Applicability	MX Section	Label on Settings page	Default	Description
BufferOnFailure=0	3.12.0 onwards	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=	3.0.0 to 3.21.0	Custom Upload - minutes interval	Option to enter "SQL command" only shown if Custom Minutes Enabled parameter is ticked	(empty)	See "predetermined SQL" example above
10 commands: CustomMySqlMinutesCommandString= CustomMySqlMinutesCommandString1= to CustomMySqlMinutesCommandString9=	3.21.1 onwards	Custom Upload - minutes interval	Option to enter "SQL command" only shown if Custom Minutes Enabled parameter is ticked	(empty commands don't appear)	See "predetermined SQL" example above
CustomMySqlMinutesEnabled=0	3.0.0 onwards	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	3.0.0 onwards	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=	3.0.0 to 3.21.0	Custom Upload - at rollover	Option to enter "SQL command" only shown if Custom Rollover Enabled parameter is ticked	(empty)	See "predetermined SQL" example above
10 commands: CustomMySqlRolloverCommandString= CustomMySqlRolloverCommandString1= to CustomMySqlRolloverCommandString9=	3.21.1 onwards	Custom Upload - at rollover	Option to enter "SQL command" only shown if Custom Rollover Enabled parameter is ticked	(empty commands don't appear)	See "predetermined SQL" example above
CustomMySqlRolloverEnabled=0	3.0.0 onwards	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=	3.0.0 to 3.21.0	Custom Upload - seconds interval	Option to enter "SQL command" only shown if Custom Seconds Enabled is ticked	(empty)	See "predetermined SQL" example above
10 commands: CustomMySqlSecondsCommandString= CustomMySqlSecondsCommandString1= to CustomMySqlSecondsCommandString9=	3.21.1 onwards	Custom Upload - at Seconds interval	Option to enter "SQL command" only shown if Custom Seconds Enabled parameter is ticked	(empty commands don't appear)	See "predetermined SQL" example above
CustomMySqlSecondsEnabled=0	3.0.0 onwards	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	3.0.0 onwards	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	3.0.0 onwards	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=	3.0.0 onwards	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	3.0.0 onwards	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	3.0.0 onwards	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=	3.0.0 onwards	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=	3.0.0 onwards	Server details	Password	(empty)	Set to whatever password is used for update access with the database user name (see below)
Port=3306	3.0.0 onwards	Server details	Port number	3306	The port related to the host name (defined above) for update access to your database server
RealtimeMySql1MinLimit=0	3.0.0 onwards	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	3.0.0 onwards	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=	3.0.0 onwards	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	3.0.0 onwards	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	3.12.0 - b3134 - BETA onwards	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 beginning of previous table for explanation and warning about how all defaults must be set for this to work
User=	3.0.0 onwards	Server details	User name	(empty)	Database access user name, that matches with password described earlier