MySql error

[N0BGS]

Hi All:

I've just started experimenting with Cumulus MX 3.0.0 b3025 this weekend. Very nice bit of work, Steve. Thank you!

I have it running successfully on an Odroid U3 running Ubuntu and Windows 7.

This is the problem I've run into when attempting to make the MySql connection from Windows 7:

Code: Select all

2015-06-14 23:16:03.234 Error encountered during MySQL operation.
2015-06-14 23:16:03.234 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 ')' at line 1

I'm using MariaDB, aka Mysql, version 10.0 on Linux when I get the above error. This occurred after I was able to create the new tables successfully.

I would normally try to figure out a syntax error myself but thus far I have been unable to determine which file is creating the mysql query, so...

Any suggestions appreciated.

Thanks,

--Kurt

[mcrossley]

The MySQL statements are constructed on the fly by MX, they are logged into the diags files though, so dig one out from there and see why it is failing.

MariaDB is NOT MySQL though, it is separate fork, so I don't think you can expect Steve to support it if it requires any significant changes.

EDIT: You may need to add the following entry to your cumulus.ini file in the station section - Logging=1
See Steve's notes on logging in MX.

[N0BGS]

Thanks, Mark. I'll check the logs.

I've yet to have any problem with MariaDB accepting all of the standard MySQL commands.

Is Cumulus MX using mysql_connect or mysqli_connect? I assume the latter since mysql_connect is deprecated since PHP 5.5.

--Kurt

[mcrossley]

Is Cumulus MX using mysql_connect or mysqli_connect? I assume the latter since mysql_connect is deprecated since PHP 5.5.

Neither I would think, it is using network connectivity to SQL, PHP isn't involved.

[N0BGS]

Well in that case I guess I can scratch that one off my list!

[N0BGS]

Meanwhile, 1.5 year later...

As discussed earlier, using Mariadb on Linux, the CumulusMX query:

Code: Select all

DELETE IGNORE FROM Realtime WHERE LogDateTime < DATE_SUB('2016-11-16 11:58:35', INTERVAL 50)

Generates the error:

Code: Select all

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 ')' at line 1

I've found that specifying the interval duration as MINUTE (assuming is should be minutes) like so:

Code: Select all

DELETE IGNORE FROM Realtime WHERE LogDateTime < DATE_SUB('2016-11-16 11:58:35',INTERVAL 50 MINUTE)

works and does not generate an error. This same syntax also works in Mysql databases.

So with that in mind, Steve, would it be possible to make that addition to the next version of MX? I don't think the query can be modified by the end user, can it?

Thanks,

--Kurt

[mcrossley]

The MX realtime SQL option has a field for "retention", you just put the interval parameter in there - for example "50 MINUTE" or "5 DAY" or "12 HOUR" or "2 WEEK" etc.

[mcrossley]

I use a custom "insert" statement for my realtime table as I have modified it slightly from the standard structure - I have just added an "after insert" trigger to the table* that runs the delete...

Code: Select all

BEGIN
	DELETE FROM realtime WHERE LogDateTime < (NOW() - INTERVAL 14 DAY);
END

* not quite true, I have added to another table that doesn't get updated as frequently as the realtime table to save a few CPUs.

And my server (well Steve's actually) is running MariaDB as well.

[N0BGS]

Wow, good call, Mark. I never would have thought to add MINUTE to the retention string field. That's done it.

That error was the only thing that's kept me from running MX up until now. Maybe I should have read the documentation a bit more closely?

Thanks much,

--Kurt