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". 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.
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:
- The MySQL settings for the original 3.0.0 beta were previously documented on Cumulus.ini (Beta) page, but that page has been cleared.
- 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
- 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!
- 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
- The table below relates to a beta for 3.12.0 (see top of this page), there will have been changes in later MX releases that are not documented
| 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
|
| 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
|
| CustomMySqlMinutesIntervalIndex=6 | Custom Upload - minutes interval | Interval: | 6 = 10 minutes | Drop-down list of predetermined intervals:
|
| 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
|
| 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
|
| 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
|
| 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
|
| 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
See explanation beside and below setting |
| RealtimeMySqlEnabled=0 | Realtime.txt upload | Real time Enabled | 0 = don't display other options | Simple tick or not
|
| 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:
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
Please see beginning 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 |
The MX default database tables
Further work is needed here, but this is a quick attempt to include some notes that might help you, written because of the extensive changes in release 3.20.0
Creation of default tables
The settings page as described above includes the ability to issue the SQL command to your server to create whichever default table you wish.
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 the create command will work.
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 thetable has been created.
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 above 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.
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 above.
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.
