when I try to import data into mysql I get this error
ERROR: [FireDAC][Phys][MySQL] 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 ‘’, ‘2022/04/09’, ‘22:00:00’, ‘3.7’, ‘5.6’, ’ 0’, ‘2.7’, ‘38’, ‘1008.0’, ‘0.2’…’ at line 1 at time/date 10:09:44 PM 2022-04-20
is there something I can do, or is this something internal in WD?
the problem looks to be that you are using MariaDB instead of MYSQL
and there is most likely a difference in the format of the date or time that it expects
is your database online or local?
MariaDB is a fork of MySQL to avoid licensing issues with Oracle. It’s virtually identical to MySQL so moving to it doesn’t need anything special doing unless you have very unusual requirements. The forum has run on MariaDB for quite a few years and I didn’t even notice at first when I installed MySQL but actually got MariaDB instead. I’d be surprised if it had date field incompatibilities. I’ve not found any so far.
i am sure this has happened to others, I am sure there is another thread on the forum about this
yes they are very similar, but a few differences all the same
does live data update OK?
ie is the problem only with trying to add past data?
This thread is the only one on the forum that mentions MariaDB.
Edit: Sorry this is wrong. I searched the board rather than the whole forum.
Maybe this applies - Datetime behavior in MariaDB vs MySQL - MariaDB Knowledge Base.
Seems like MySQL is inconsistent when you provide incorrectly formatted data.
Seems odd that this has only just surfaced though. I suspect that many people have been using MariaDB thinking it was MySQL for s few years.
This might be the thread you were thinking of Brian - MySQL / MariaDB
I have time to do searches - waiting outside a garden centre for plants to be found and bought!
Live data is fine. Just adding past data causes the error
Are you able to turn on the General Query Log for a few minutes so that we can see the SQL that triggered the error? It would also be useful to see the structure of the table you’re using.
I will have to do that from home (if I can). I should be able to do this in about 6 hours
I took A look, it doesn’t look like I am able to do it on my hosting cpanel
Hello,
I don’t want to hijack this thread and if you’d like it can be moved. I’ve been stuck using WDMYSQL v10.7 because of similar errors and composed this a while ago:
I’ve been unable to get wdmysql v11.0 running on my Win10 PC using MySQL v5.7.32. V10.7 runs fine so I have been using that, but I did find the following happening when I launch v11.0:
wdmysql displays: ERROR: Out of memory at time/date 12:10:34 AM 01/30/2021
INSERT INTO wdisplay(wx_date, wx_time, temperature, humidity, dewpoint, barometer, windspeed, windgust, wind_direction, rain, solar, uv, heat_index, wind_chill, ) VALUES (‘2021/01/30’, ‘00:11:00’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, ‘date’, )
[FireDAC][Phys][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line 1
Note: the INTO values appear to be correct so if the extra comma wouldn’t be there I believe it would insert correctly. I’m not sure why the VALUES line shows ‘date’ for fields 3 through 14 so that could also be a problem. Yes, I am using a Custom Setup but have been for years!
NOTE: I tried this on MySQL v8.0.23 and the memory error doesn’t occur but items 2 and 3 are still there.
MySQL_RegKeys_fake_user_pswd.txt (6.03 KB)
Something I just noticed.
When I try to add past data I see the date format like this. ‘2022/04/11’, ‘22:00:00’, ‘3.7’, ‘7.4’, ‘45’, ‘-6.4’, ‘58’, ‘1018.4’, ‘0.0…’ at line 1 at time/date 10:28:41 PM 2022-04-21
When I do a liive update. the date format is like this VALUES (‘weatheryyc’, ‘2022-04-21’, ‘22:32:00’, ‘0.0’, ’
According to the MariaDB documentation ‘-’ and ‘/’ are both permitted as date separators so that shouldn’t be the problem.
It’s a pity we can’t see the SQL being used. One thing I wondered was that for live data there seems to be the station ID included but the past data doesn’t seem to have that. That might just be down to the reported error not displaying an unaffected part of the data values though.
Maybe brian can add debug code to see the SQL?
maybe I will try to make a script to do it. I’m not that good at coding, but have accomplished some stuff
If you can get the data into a spreadsheet it’s not too difficult to turn it into SQL commands that can be uploaded and run into the database. CONCATENATE() is your friend…as well as knowing what the SQL commands should look like.
what I could do is make it visible what WD is trying to insert
re adding history data
and then you can edit that to see where/what is causing the error (e.g changing to using - instead of \ for the date etc)
@Chipperdog, is it the last comma that is the problem? (for live data)
https://www.weather-display.com/downloadfiles/wdmysql.zip
what I could do is make it visible what WD is trying to insert re adding history data and then you can edit that to see where/what is causing the error (e.g changing to using - instead of \ for the date etc)
this is what it shows
ERROR: [FireDAC][Phys][MySQL] 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 ‘’, ‘2022/03/01’, ‘00:02:00’, ‘0.0’, ‘0.0’, ‘68’, ‘-4.7’, ‘88’, ‘1013.2’, ‘0.0…’ at line 1 at time/date 6:12:50 PM 2022-04-25
I’m not sure, but it looks like it is skipping the stationid