consolewd and MySQL

Check out the new FAQs (Frequently Asked Questions) board for a growing list of hints, tips and diagnostic suggestions.

[CAV77]

Greetings all,

I have installed consolewd ver 4.8 on a CentOS 5.2 platform but am having trouble getting it to write to a MySQL database.

I have the username and password set up correctly and have created a database called "weather" but when consolewd trys to write to the wx_data table it generates the following error:
Connecting to table wx_data

An unhandled exception occurred at $0807FD7E :
EDatabaseError :  : Error executing query: Table 'weather.wx_data' doesn't exist
  $0807FD7E
  $080792A6
  $0807A2CB
  $0807CE46
  $0807D916
  $0804B6D4  main,  line 1128 of cronmysql.lpr

I have been unable to create the wx_data table as I don't know what the structure of the table should be.
The config.txt files refers to "(see the readme for the table structure)" however the readme.txt supplied with consolewd has no mention of the table structure so I'm unable to make the table for it to write to.  I did create a table called wx_data with three fields just as a test and consolewd then comes up with an error to the effect that the column count of wx_data is not equal to the value count.  This, I assume, means that the connection to the MySQL DB is working even though the table structure is obviously wrong as I estimate that there are 65 fields in it.

Also, even though I have "Do wunderground=N", consolewd still tries to send data to wunderground every 5 minutes.
Since I don't want to do this at the moment I have moved the cronwu file to another location.

I'm using station type 166 (WH1081 or Watson W-8681 in the UK).

Any help with the MySQL table structure would be greatly appreciated.

Regards
John,
Gateshead, UK

[windy]

#
# Table structure for table `wx_data`
#

CREATE TABLE `wx_data` (
`station_id` varchar(10) NOT NULL default ' ',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`average_windspeed` float NOT NULL default '0',
`wind_direction` smallint(3) NOT NULL default '0',
`gust_windspeed` float NOT NULL default '0',
`temperature` float NOT NULL default '0',
`outdoor_humidity` float NOT NULL default '0',
`barometer` float NOT NULL default '0',
`daily_rainfall` float NOT NULL default '0',
`monthly_rainfall` float NOT NULL default '0',
`yearly_rainfall` float NOT NULL default '0',
`rain_rate` float NOT NULL default '0',
`max_rain_rate_curent_day` float NOT NULL default '0',
`indoor_temperature` float NOT NULL default '0',
`indoor_humidity` float NOT NULL default '0',
`soil_temperature` float NOT NULL default '0',
`forecast_icon` int(3) NOT NULL default '0',
`wmr968_extra_temperature` float NOT NULL default '0',
`wmr968_extra_humidity` float NOT NULL default '0',
`wmr968_extra_sensor_number` smallint(2) NOT NULL default '0',
`yesterday_rainfall` float NOT NULL default '0',
`extra_temperature_sensor_2` float NOT NULL default '0',
`extra_temperature_sensor_3` float NOT NULL default '0',
`extra_temperature_sensor_4` float NOT NULL default '0',
`extra_temperature_sensor_5` float NOT NULL default '0',
`extra_temperature_sensor_6` float NOT NULL default '0',
`extra_temperature_sensor_7` float NOT NULL default '0',
`extra_humidity_2` float NOT NULL default '0',
`extra_humidity_3` float NOT NULL default '0',
`extra_humidity_4` float NOT NULL default '0',
`hour` smallint(2) NOT NULL default '0',
`minute` smallint(2) NOT NULL default '0',
`second` smallint(2) NOT NULL default '0',
`station_name` varchar(100) NOT NULL default '',
`dallas_1_wire_lightning_count` int(11) NOT NULL default '0',
`actual_solar_reading` int(11) NOT NULL default '0',
`day` smallint(2) NOT NULL default '0',
`month` smallint(2) NOT NULL default '0',
`wmr968_battery_level_1` smallint(3) NOT NULL default '0',
`wmr968_battery_level_2` smallint(3) NOT NULL default '0',
`wmr968_battery_level_3` smallint(3) NOT NULL default '0',
`wmr968_battery_level_4` smallint(3) NOT NULL default '0',
`wmr968_battery_level_5` smallint(3) NOT NULL default '0',
`wmr968_battery_level_6` smallint(3) NOT NULL default '0',
`wmr968_battery_level_7` smallint(3) NOT NULL default '0',
`current_windchill` float NOT NULL default '0',
`current_humidex` float NOT NULL default '0',
`max_daily_temperature` float NOT NULL default '0',
`min_daily_temperature` float NOT NULL default '0',
`icon_type` smallint(3) NOT NULL default '0',
`current_weather_desc` varchar(20) NOT NULL default '',
`barometer_trend_last_hour` float NOT NULL default '0',
`max_gust_current_day` float NOT NULL default '0',
`dew_point_temperature` float NOT NULL default '0',
`cloud_height` float NOT NULL default '0',
`max_humidex` float NOT NULL default '0',
`min_humidex` float NOT NULL default '0',
`max_windchill` float NOT NULL default '0',
`min_windchill` float NOT NULL default '0',
`davis_vp_uv` float NOT NULL default '0',
`max_heat_index` float NOT NULL default '0',
`min_heat_index` float NOT NULL default '0',
`heat_index` float NOT NULL default '0',
`max_average_windspeed_day` float NOT NULL default '0',
UNIQUE KEY `station_id` (`station_id`,`date`,`time`)
) TYPE=MyISAM COMMENT='wx_data from Weather Display';

 

[windy]

found a bug:
(hopefully fixed)
try this new update
http://www.tokaanuskihire.co.nz/cronmysql.dat
(remove the .dat after downloaded and set the permissions correctly)

[CAV77]

Thanks Brian,

what was the bug?
I don't seem to have had any problems with the MySQL stuff since creating the initial database (thanks for the table structure by the way).
Is there any way the MySQL DB could be updated at a user-defined interval, say, every one or two minutes instead of the fixed five-minutes?

Regards,
John

[windy2]

the bug was not with the consolewd version of consolmysql (my mistake for posting that here)
re the other than 5 minute update, is that with the consolewd version in use?

[CAV77]

Hi Brian,

I'm using consolewd 5.2 and can't seem to find a way of updating the MySQL database at any interval apart from the default 5-minutes.  Is it possible to change this?  Hopefully I'm not missing something obvious!

Regards,
John

[windy]

its not possible at the moment, but I could add that ability....to what interval?

[CAV77]

One or two minute intervals would be nice.
Is it possible to add a line to config.txt something like mysql_interval=N where N is the interval in minutes?

As a matter of interest, when the data is written every five minutes, is the data an average (or maximum for gust_windspeed) since the last 5-minute reading or is it an instant snapshot of conditions at the 5-minute mark?

I'm currently using a WH1081 station on USB but I also have a La Crosse WS2300 (anemometer has failed) and was wondering if it's possible to run two instances of consolewd - one to talk to the WH1081 and another to talk to the La Crosse (RS232)?

Cheers,
John

[windy]

i can add option to update every 1 minute no problem

yes, a second install, would work..different settings you see...

[CAV77]

That would be great.

I'll (hopefully) have a go with the dual install this weekend.

Regards,
John

[windy]

latest update has that flag available to be set in the config.txt file
i.e
Domysqlmin=

[CAV77]

Hi Brian,

I have installed consolewd 5.3 and set the line Do mysqlmin=Y in config.txt but the MySQL DB is still being updated at 5-minute intervals.
I also tried setting it to Do mysqlmin=1 (restarting consolewd each time) but to no avail.
When consolewd is ran I notice that there is no indication that the 1-minute flag has been read whereas other settings are indicated on startup such as:

Will do mysql updates
mysql username = xxxx
mysql server = localhost
.
.
Will do mysql metric

Can you confirm that the setting for 1-minute mysql updates should be as above, i.e. Do mysqlmin=Y

Cheers,
John