Help with SQL Error

[Big Daddy]

Hi,
So I am getting into SQL have everything set up and working but when I try to create the dayfile table in the MX SQL settings page I getthe following error.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(LogDate date NOT NULL ,HighWindGust decimal(4,1) NOT NULL,HWindGBear varchar(3)' at line 1

The Monthly and Realtime tables have been created correctly and are updating with the data as per the logging / update setting.

MX Diags shows:

2020-03-06 21:05:34.098 CREATE TABLE (LogDate date NOT NULL ,HighWindGust decimal(4,1) NOT NULL,HWindGBear varchar(3) NOT NULL,THWindG varchar(5) NOT NULL,MinTemp decimal(5,1) NOT NULL,TMinTemp varchar(5) NOT NULL,MaxTemp decimal(5,1) NOT NULL,TMaxTemp varchar(5) NOT NULL,MinPress decimal(6,1) NOT NULL,TMinPress varchar(5) NOT NULL,MaxPress decimal(6,1) NOT NULL,TMaxPress varchar(5) NOT NULL,MaxRainRate decimal(4,1) NOT NULL,TMaxRR varchar(5) NOT NULL,TotRainFall decimal(6,1) NOT NULL,AvgTemp decimal(4,1) NOT NULL,TotWindRun decimal(5,1) NOT NULL,HighAvgWSpeed decimal(3,1),THAvgWSpeed varchar(5),LowHum decimal(4,0),TLowHum varchar(5),HighHum decimal(4,0),THighHum varchar(5),TotalEvap decimal(5,1),HoursSun decimal(3,1),HighHeatInd decimal(4,1),THighHeatInd varchar(5),HighAppTemp decimal(4,1),THighAppTemp varchar(5),LowAppTemp decimal(4,1),TLowAppTemp varchar(5),HighHourRain decimal(4,1),THighHourRain varchar(5),LowWindChill decimal(4,1),TLowWindChill varchar(5),HighDewPoint decimal(4,1),THighDewPoint varchar(5),LowDewPoint decimal(4,1),TLowDewPoint varchar(5),DomWindDir varchar(3),HeatDegDays decimal(4,1),CoolDegDays decimal(4,1),HighSolarRad decimal(5,1),THighSolarRad varchar(5),HighUV decimal(3,1),THighUV varchar(5),HWindGBearSym varchar(3),DomWindDirSym varchar(3),PRIMARY KEY(LogDate)) COMMENT = "Dayfile from Cumulus"
2020-03-06 21:05:34.102 Error encountered during MySQL operation.
2020-03-06 21:05:34.103 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(LogDate date NOT NULL ,HighWindGust decimal(4,1) NOT NULL,HWindGBear varchar(3)' at line 1

SQL is on Pi-3 running Raspbian 10 (Buster) with mariadb Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

Any help appreciated.

Thanks
Andy

[Big Daddy]

Late last night I manually created a new table in my SQL databse called Dayfile and created 2 colums. The columns were based on the error I was getting in MX diags when previusly trying to create the table.

LogDate date NOT NULL ,
HighWindGust decimal(4,1) NOT NULL,

Now, as expected when I click create table in the MX SQL settings it says the table already exists, which is a step forward.

However, the data from dayfile does not appear to be populating the table, Monthly and Realtime work fine. I dont see any erros in MX Diags.

Any ideas what I am missing?

Andy

[mcrossley]

It will insert a day record during the rollover processing each day. If you want to insert all your historic data, then you can use the ImportCumulusFile script

[sfws]

I would have thought when testing, you would try creating with a different table name.

CREATE TABLE (LogDate date NOT NULL ,HighWindGust decimal(4,1) NOT NULL,

However the MX query you quote does not mention a table name, it should appear after the word 'TABLE' but before the opening bracket of the list of columns. That is your error.

The table must have been created by an earlier action when your testing said it already existed, did you do your test twice? Or as Mark suggests, did a MX rollover create the table?

I have phpMyAdmin and adminer available on my system, and I find it useful to test SQL using one of those, the first has the ability to save SQL you have entered and then offer you chance to edit it and I think I prefer phpMyAdmin, although in some ways the adminer interface takes a more logical drill-down approach.

[Big Daddy]

Thanks for the comments. SQL is totally new to me so I am on a steep learning curve but thought Cumulus would be a good tool to use in my learning process.
Understand about the dayfile table now, so I need to setup and wait till the next rollover but will try and import my dayfile using the script, which I had already read about in the wiki.
Out of interest, what is the upload frequency of the Monthly and Realtime tables. Do I need to set these somewhere?

Maybe I wasnt clear in my sequence of events.

1) Tried creating tables using SQL settings / buttons in MX. Monthly and Realtime created OK but dayfile did not create. In SQL settings page it came up with the following error

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(LogDate date NOT NULL ,HighWindGust decimal(4,1) NOT NULL,HWindGBear varchar(3)' at line 1

and MX Diags showed what I pasted earlier.

When I looked in my database using phpadmin the Monthly and Realtime tables were created but not the Dayfile table.

2) I then manually created a Dayfile table usisng phpmyadmin. When I then went back in to the SQL settings in MX and clicked the Create Dayfile table button it said the table existed which is what I expected as I had just manually created the table. All good so far.

As for the missing table name

However the MX query you quote does not mention a table name, it should appear after the word 'TABLE' but before the opening bracket of the list of columns. That is your error.

This was the result of hitting the Create Dayfile button. I dont see anywhere to enter the table name and i didnt have to do this for Monthly and Realtime tables, I just clicked the button.
Could the table name possibly be missing from the script MX uses to generate the table in SQL.

Andy

[Big Daddy]

Just to follow up.

I manually entered the following via phpmyadmin and it created the table so it looks like the table name may be missing in the generated MX script / button......but I am not an expert

CREATE TABLE Dayfile (LogDate date NOT NULL ,HighWindGust decimal(4,1) NOT NULL,HWindGBear varchar(3) NOT NULL,THWindG varchar(5) NOT NULL,MinTemp decimal(5,1) NOT NULL,TMinTemp varchar(5) NOT NULL,MaxTemp decimal(5,1) NOT NULL,TMaxTemp varchar(5) NOT NULL,MinPress decimal(6,1) NOT NULL,TMinPress varchar(5) NOT NULL,MaxPress decimal(6,1) NOT NULL,TMaxPress varchar(5) NOT NULL,MaxRainRate decimal(4,1) NOT NULL,TMaxRR varchar(5) NOT NULL,TotRainFall decimal(6,1) NOT NULL,AvgTemp decimal(4,1) NOT NULL,TotWindRun decimal(5,1) NOT NULL,HighAvgWSpeed decimal(3,1),THAvgWSpeed varchar(5),LowHum decimal(4,0),TLowHum varchar(5),HighHum decimal(4,0),THighHum varchar(5),TotalEvap decimal(5,1),HoursSun decimal(3,1),HighHeatInd decimal(4,1),THighHeatInd varchar(5),HighAppTemp decimal(4,1),THighAppTemp varchar(5),LowAppTemp decimal(4,1),TLowAppTemp varchar(5),HighHourRain decimal(4,1),THighHourRain varchar(5),LowWindChill decimal(4,1),TLowWindChill varchar(5),HighDewPoint decimal(4,1),THighDewPoint varchar(5),LowDewPoint decimal(4,1),TLowDewPoint varchar(5),DomWindDir varchar(3),HeatDegDays decimal(4,1),CoolDegDays decimal(4,1),HighSolarRad decimal(5,1),THighSolarRad varchar(5),HighUV decimal(3,1),THighUV varchar(5),HWindGBearSym varchar(3),DomWindDirSym varchar(3),PRIMARY KEY(LogDate)) COMMENT = "Dayfile from Cumulus"

Andy

[mcrossley]

Good spot @swfs.

It looks like you do not have a table name set in the SQL configuration section - odd, as it pre-populates default values.

[Big Daddy]

Mark,
Auto-populate seems to be the issue.

I deleted all my tables in SQL database and then in MX SQL settings removed the Monthly, Realtime and Dayfile settings and saved. Tried to create tables using buttons in MX and they all failed. Then added the Monthly, Realtime and Dayfile settings in MX SQL settings and saved. Tried to create tables using buttons in MX and they all created.

Maybe yesterday I only added settings for Monthly and Realtime.

Andy

[mcrossley]

Cumulus adds default names of "Monthly", "Realtime", and "Dayfile" for the three table names. If the name is blanked, then it will remain blanked, I'll change that to use the default if blank when reading from Cumulus.ini