MYsql help needed [RESOLVED]

Let's Chat
1

Didnt want to put this question into wd questions, so i’ve put it here.

Basically, i have a mysql server based on a qnap network drive server.
It has been running lovely for the past month or so, but i made a change to add currentcost1-currentcost6 in the other day, now i am getting errors.

Here are the last few lines of errors as output by the wdmysql program:-

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 ‘.01.614.550.661111.200830.0)’ at line 1
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 ‘.01.714.450.661111.200830.0)’ at line 1
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 ‘.01.914.650.661111.200830.0)’ at line 1
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 ‘.01.914.550.661111.200830.0)’ at line 1
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 ‘.01.814.550.661111.200830.0)’ at line 1
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 ‘.01.914.450.661111.200830.0)’ at line 1
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 ‘.02.014.450.661111.200830.0)’ at line 1
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 ‘.01.814.450.661111.200830.0)’ at line 1
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 ‘.01.714.350.661111.200830.0)’ at line 1
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 ‘.01.914.350.661111.200830.0)’ at line 1
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 ‘.01.914.350.661111.200830.0)’ at line 1
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 ‘.01.814.350.661111.200830.0)’ at line 1
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 ‘.01.714.350.661111.200830.0)’ at line 1
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 ‘.01.814.250.661111.200830.0)’ at line 1
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 ‘.02.014.250.661111.200830.0)’ at line 1
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 ‘.01.814.250.661111.200830.0)’ at line 1
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 ‘.01.614.250.661111.200830.0)’ at line 1
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 ‘.01.614.250.661111.200830.0)’ at line 1
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 ‘.01.514.250.661111.200830.0)’ at line 1
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 ‘.01.414.250.661111.200830.0)’ at line 1
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 ‘.01.414.350.661111.200830.0)’ at line 1
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 ‘.01.414.250.661111.200830.0)’ at line 1
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 ‘.01.414.150.661111.200830.0)’ at line 1
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 ‘.01.314.350.661111.200830.0)’ at line 1
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 ‘.01.214.350.661111.200830.0)’ at line 1
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 ‘.01.314.450.661111.200830.0)’ at line 1
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 ‘.01.314.450.661111.200830.0)’ at line 1
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 ‘.01.214.350.661111.200830.0)’ at line 1
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 ‘.01.414.550.661111.200830.0)’ at line 1
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 ‘.01.414.450.661111.200830.0)’ at line 1
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 ‘.01.414.450.661111.200830.0)’ at line 1
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 ‘.01.614.450.661111.200830.0)’ at line 1
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 ‘.01.914.450.661111.200830.0)’ at line 1
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 ‘.02.314.450.661111.200830.0)’ at line 1

NOt sure where to start, other than line 1, but that don’t help me much.
Any geniuses about?

Thanks!!
Stuart

2

Can you post the inject statement your trying to use? I’m guessing the “.01.614.550.661111.200830.0” are the 6 new fields you tried to add to the db.

3

thats lost me, inject statement, i guess thats what i copied and paste when i started the database.
I didnt use a statement when i added those columns, i used the web interface of the database to add the 6 new fields.

I just unticked the currentcost option in the wdmysql window and saved that and i am getting data saved to the database again.
I assume i need to add the new fields another way, using a inject script?

4

you need to use a sql to add those currentcost fields to the data base
otherwise there will be an error

for first time users, create a table for first time use, those fields are added if that is selected

I will do some testing today

5

fixed a problem
I had not added commas between the current cost data
vers 10.1
http://www.weather-display.com/downloadfiles/wdmysql.zip

6

just fixed one more thing…working now…download from that link again…

7

seems to be working now.
I re-added the 6 new fields for currentcost, then ticked the current cost options and its still updating every minute with no errors.

Thanks Brian!

8

Same problem for me:

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 ‘621mB,)’ at line 1
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 ‘622mB,)’ at line 1
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 ‘619mB,)’ at line 1
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 ‘592mB,)’ at line 1
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 ‘594mB,)’ at line 1

All I’ve done is a computer change, from an old WD 10.37P b30 to the newer 10.37Q b80 (WDMYSQL 8.6 to 10.1). No changes at all on my database structure. I’m using a custom MySQL setup:

%date% %time% %temp% %hum% %indoortemp% %indoorhum% %dew% %baro% %10minavspeedinmetric% %avgspd% %1mingustwind% %dirdeg% %dayrn% %monthrn% %yearrn% %currentrainratehr% %VPsolar% %VPuv% %currentsolarpercent% %windch% %humidexcelsius% %heati% %wetbulb% %feelslikedp% %cloudheightfeet% %vpreception2% %freememory%

10/10/11 22:06 14.2 88 22.8 53 12.2 1025.9 0.8 1.3 1.7 317 0.2 1.6 464.6 0.0 0 0.0 0 14.2 16.5 14.2 13.1 14.2 2247 95% 590mB

9

it looks like the problem is the last comma
for when a custom mysql is being used
someone else reported that problem

I thought I had the code added to check for that
I will set up a custom mysql data base and do some checking/testing

10

Brietling, you did not say which version of the wdmysql you are using
I set up a custom mysql
its working OK
vers 10.1

I have it now with version 10.2 where if you have setup a custom myql, then when you click to create the table, that is now created
http://www.weather-display.com/downloadfiles/wdmysql.zip

11

He’s using 10.1 acc to his post.

12

I must have missed that
I will need his settings files to try and duplicate the problem here

13

I see the problem now

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 '594[color=red]mB[/color],)'

you cant have units like that for a value

so you will need to either unset to use units for the custom tags in WD in the custom web page setup
or use a different custom tag for the barometer reading that does not have units added

14

Hi Brian, thanks for your time. That’s not a baro tag, it is %freememory%. In earlier versions the tag gave the unit separated by a blank, so I could add a dummy field to get rid of it (eg: “540 MB”). Now there’s no blank space between value and unit.

I’m not using any option on the custom webpage setup (main switch OFF).

15

I am a bit confused…
how should it be reported?
with a space, or?

16

Either with a space or no unit at all. In earlier versions it had a space.

17

OK, use a new .zip update so that its like it was before
(I had changed the routine, so as to be able to work on 8gb+64k systems)

18

Ok, now the tag is right (blank space) but I have a similar error on WDMYSQL, it shows a coma instead:


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 ‘422,MB)’ at line 1
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 ‘424,MB)’ at line 1

%date% %time% %temp% %hum% %indoortemp% %indoorhum% %dew% %baro% %10minavspeedinmetric% %avgspd% %1mingustwind% %dirdeg% %dayrn% %monthrn% %yearrn% %currentrainratehr% %VPsolar% %VPuv% %currentsolarpercent% %windch% %humidexcelsius% %heati% %wetbulb% %feelslikedp% %cloudheightfeet% %vpreception2% %freememory%

11/10/11 21:37 14.2 74 22.8 54 9.6 1024.9 0.0 0.0 0.0 332 0.2 1.8 464.8 0.0 0 0.0 0 14.2 15.3 14.2 11.8 14.2 3323 100% 423 MB

19

what data type do you have the %freememory% set as?

20

It is as VARCHAR(20), same as %vpreception2%