WDMYSQL vers 10.5

Ask a Question
1

I been writting to mysql using customer crated files although working fine I decided to give wdmysql another go this then would save me having all these php files and cron jobs doing stuff.

When using MDMYSQL vers 10.5 I get this error.

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 ‘:49,18:40,23:51,11:43,13:51,818,23.0,17.0,23.0,23.0,19.3,22.7,43,70.3,17.1,19.0,’ at line 1

custommysqlout.txt is
Lyndhurst-Hill 09:48 48 09 18 12 2011 December Sunday 18/12/11 04:49 18:40 23:51 11:43 13:51 877 23.1 16.6 23.1 23.1 19.1 22.7 43 70.2 21.4 17.6 1.188 7.2953 3 2.2 SE 1016.9 0.0 589 01:08 7.3 14.0 0.0 0 67 126 2443 0.9 0.0 Dry 22.9 0.0 1 1209 963 209 256 492 163

custommysqlin.txt is
%stationname% %time% %time-minute% %time-hour% %date-day% %date-month% %date-year% %monthname% %dayname% %date% %sunrise% %sunset% %moonrise% %moonset% %hoursofpossibledaylight% %cloudheightmeters% %tempinmetric% %dewinmetric% %heatindexinmetric% %windchillinmetric% %wetbulb% %indoortempinmetric% %indoorhum% %THI% %HLI% %HLIAvwind% %airdensity% %abshum% %avnodp% %gstspd% %dirlabel% %baroinmetric% %todayraininmm% %VPsolar% %sunshinehourstodateday% %VPuv% %VPsoilmoisture% %VPleaf% %lighteningcountlast5minutes% %hum% %dirdeg% %freememory% %VPet% %vpstormrain% %weathercond% %apparenttemp% %currentrainrate% %dayswithnorain% %currentcostch1% %currentcostch2% %currentcostch3% %current2costch1% %current2costch2% %current2costch3%

My wd_data_mysql table and mysql version details.

Source Server : Localdb
Source Server Version : 50141
Source Host : xxx.xxx.0.211:3306
Source Database : lyndhurs_weather

Target Server Type : MYSQL
Target Server Version : 50141
File Encoding : 65001

Date: 2011-12-18 09:51:32
*/

SET FOREIGN_KEY_CHECKS=0;

– Table structure for wx_data_mysql

DROP TABLE IF EXISTS wx_data_mysql;
CREATE TABLE wx_data_mysql (
station_name varchar(20) NOT NULL DEFAULT ‘’,
time time NOT NULL DEFAULT ‘00:00:00’,
minute float NOT NULL DEFAULT ‘0’,
hour float NOT NULL DEFAULT ‘0’,
day float NOT NULL DEFAULT ‘0’,
month float NOT NULL DEFAULT ‘0’,
year float NOT NULL DEFAULT ‘0’,
month_name varchar(20) NOT NULL DEFAULT ‘’,
day_name varchar(20) NOT NULL DEFAULT ‘’,
date date NOT NULL DEFAULT ‘0000-00-00’,
sun_rise varchar(20) NOT NULL DEFAULT ‘’,
sun_set varchar(20) NOT NULL DEFAULT ‘’,
moon_rise varchar(20) NOT NULL DEFAULT ‘’,
moon_set varchar(20) NOT NULL DEFAULT ‘’,
hours_sun_light varchar(20) NOT NULL DEFAULT ‘’,
cloud_height float NOT NULL DEFAULT ‘0’,
temperature float NOT NULL DEFAULT ‘0’,
dew_point_temperature float NOT NULL DEFAULT ‘0’,
heat_index float NOT NULL DEFAULT ‘0’,
current_windchill varchar(20) NOT NULL DEFAULT ‘’,
wet_bulb float NOT NULL DEFAULT ‘0’,
indoor_temperature float NOT NULL DEFAULT ‘0’,
indoor_humidity float NOT NULL DEFAULT ‘0’,
THI float NOT NULL DEFAULT ‘0’,
HLI float NOT NULL DEFAULT ‘0’,
HLIavWind varchar(20) NOT NULL DEFAULT ‘’,
air_density float NOT NULL DEFAULT ‘0’,
absolute_humidity float NOT NULL DEFAULT ‘0’,
average_windspeed float NOT NULL DEFAULT ‘0’,
gust_windspeed float NOT NULL DEFAULT ‘0’,
wind_direction_label varchar(20) NOT NULL DEFAULT ‘’,
barometer float NOT NULL DEFAULT ‘0’,
daily_rainfall float NOT NULL DEFAULT ‘0’,
solar_energy_number float NOT NULL DEFAULT ‘0’,
today_sunshine_hours_solar varchar(20) NOT NULL DEFAULT ‘’,
davis_vp_uv float NOT NULL DEFAULT ‘0’,
davis_vp_soil_moisture float NOT NULL DEFAULT ‘0’,
davis_vp_leaf_wetness float NOT NULL DEFAULT ‘0’,
light_count_last_5min float NOT NULL DEFAULT ‘0’,
nexstorm_bearing varchar(20) NOT NULL DEFAULT ‘’,
nexstorm_distance varchar(20) NOT NULL DEFAULT ‘’,
outdoor_humidity float NOT NULL DEFAULT ‘0’,
wind_direction_degree float NOT NULL DEFAULT ‘0’,
free_memory varchar(20) NOT NULL DEFAULT ‘’,
evapor_transpiration float NOT NULL DEFAULT ‘0’,
davis_storm_rain_value float NOT NULL DEFAULT ‘0’,
current_weather_desc varchar(20) NOT NULL DEFAULT ‘’,
appearent_temperature float NOT NULL DEFAULT ‘0’,
current_rain_mm float NOT NULL DEFAULT ‘0’,
consecutative_days_no_rain float NOT NULL DEFAULT ‘0’,
currentcostch1 float(10,0) NOT NULL,
currentcostch2 float(10,0) NOT NULL,
currentcostch3 float(10,0) NOT NULL,
current2costch1 float(10,0) NOT NULL,
current2costch2 float(10,0) NOT NULL,
current2costch3 float(10,0) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

It looks like WD is struggling with something, perhaps a wrong column specification? I used the standard create table and added the currentcost channels manually, even before adding these WD did not uploaded the db.

Any ideas?

H.

2

I’m no expert but I wonder about some field definitions which may or may not be a big deal in MySQL:

time time NOT NULL DEFAULT ‘00:00:00’, - but your time is 00:00
date date NOT NULL DEFAULT ‘0000-00-00’, - but your date is 0000/00/00

Just a general comment, I don’t understand why you have redundant time/date fields, or why so many numeric fields are defined as char, but then again my dba experience is not with MySQL…

3

Good pickup Niko, but in this case it does not make any difference

Re time, yes good point then again it makes it easier to search and code I guess. I am just lazy and not a db either.

Having said that, this is almost stock standard in WD.

H.

4

Reason I mention the date separator is that php will assume the US MM DD date order if the separator is a “/”, and the euro DD MM order if the separator is a “-”, so I wondered if the separator would also be significant in MySQL.