WD MYSQL "Duplicate Entry"

Ask a Question
,
1

G’day,

I’m currently running WD 10.36y with a WS3600 weather station. When using the mysql bolt on, occassionally data stops being inserted into the table and the following error message appears in the error log on the MYSQL bot on…

Server:5.0.24a-community-nt at time/date 2:00:10 PM 01/20/07
ERROR: [1062] #23000Duplicate entry ‘-12:59:58_-2007-01-20-12:00:00’ for key 1:
Client:4.1.14
Server:5.0.24a-community-nt at time/date 2:01:10 PM 01/20/07
ERROR: [1062] #23000Duplicate entry ‘-12:59:58_-2007-01-20-12:00:00’ for key 1:

The same thing is repeated over and over until i nuke the data in the table. At this point it’s not a problem, because i’m only using the last entry, but soon i want to code some more bits that look at all the data over time.

Any help and comments would be appreciated.
Cheers,
Dave.

2

Dave,

I can’t say I’m experiencing that issue - loading data into MySQL at 1 minute intervals for about a month now without problems.

Could you do a dump of your MySQL table specification and post it here - it looks like index key problems in the table…

3 
CREATE TABLE `wx_data` (
  `station_id` varchar(10) collate latin1_general_ci 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) collate latin1_general_ci 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) collate latin1_general_ci 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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='wx_data from Weather Display';
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


D:\bin\xampp\mysql\bin>
4

I just wonder if we really need to update every minute to a mysql db as we do with WD data to our local HD’s.

Is it really necessary ? Okay, you might miss a wind gust, but you might also miss that same windgust at a 1 minute interval too.

5

Dave,

That table def. looks ok - same index as I’m using.

The error is being caused by two entries having the same time stamp - this is causing the index to barf as its specified as ‘unique’

Speculation; its possible that a time lag between sending the data to MySQL and it being acknowledged is long enough for WDMySQL to decide the data entry request was sent, but not received, and its trying to send the same data entry request again, by which time the data has been written to the database, and rightly, MySQL has a fit as the unique index requirement is breached.

Is the MySQL db on the same local machine, or is it elsewhere on a network?

I don’t know very much about how WDMySQL works - perhaps this needs Brian’s input…

6

G’day EI4HQ,

The mysql server is the same machine as the wd box.

G’day weatheroz,
I’m updateing the DB every minute as the php website i’m building pulls the data from the db, and i want reasonably uptodate weather to display on the website. I’m thinking of changing to use the clientraw.txt to drive the website instead, and pull the core values out of that and dump them into a DB. As their is just too much extra info that’s being dumped into the table. Although it would be nice to get the DB working properly.

Cheers.,
Dabve

7
I just wonder if we really need to update every minute to a mysql db as we do with WD data to our local HD's.

I use the MySQL data to generate my own graphs which sample at 60s intervals (http://ei4hq.shacknet.nu/weather/mygraphs.php). What is stored is of course a duplication of what is in the WD database, but I like to have the ‘raw’ data so I can manipulate it myself…

Is it really necessary ? Okay, you might miss a wind gust, but you might also miss that same windgust at a 1 minute interval too.

The question that goes to the heart of scientific research - what is an acceptable sampling rate - answer depends entirely on what you’re trying to investigate… I’d agree that one minute samples represent a high sampling rate for many meteological phenomena e.g. barometeric pressure, but for others e.g. wind gust, perhaps one minute is a bit sparse…

8

Dave,

Strange - can you check the WD database (View->NOAA Extremes->Verify Data, or something like that) and see if there are any duplicate entries being made there?

I’ve reported a few oddities with WDMySQL (see bugs 256,257,287 in BugTracker), however they all related to importing historical data from WD, not live data - maybe you’ve uncovered an issue?

9

I’ve been using wdmysql for about 2 years now updating every 1 minute on the same (slow) WD PC and I’m not seeing any duplicate entries at all. I would strongly suggest that you check further to see if there are duplicates in WD. I also note you have a different station to me so maybe it could be station related?

Stuart

10

Oh ok, yeah I see what you’re doing there, and also think the clientraw is the better option for ‘current’ data.

Like you, I agree there’s way too much junk being put in to the DB as it currently stands, and why I have suggested a Custom / Expert mode in WDmySQL where we can trim down the amount of data being stored in a DB. :slight_smile:

11

That’s the sort of thing I am looking at doing here for my website, but I can’t currently upload all my old data to the DB at the moment, as per the less than 31 day months issue that has been reported, so I’ve let it go by the wayside, and will probably wait and see if Brian takes up my suggestion of a Custom / Expert mode for WDMySQL.

The question that goes to the heart of scientific research - what is an acceptable sampling rate - answer depends entirely on what you're trying to investigate... I'd agree that one minute samples represent a high sampling rate for many meteological phenomena e.g. barometeric pressure, but for others e.g. wind gust, perhaps one minute is a bit sparse...

That’s so right, what is an acceptable sampling rate.

Old data of say more than 2 years ago, do we really need (and I’m only talking from what I believe is the perspective of most people here, and could be a gross generalisation), a sampling rate of every minute, when a sampling rate of 20 minutes, or perhaps even an hour might be more than sufficient ?

A sampling rate of 1 minute is quite relevant for data over the last month or two (or even seasonal), but when you’re going beyond 12 months for this year/last year comparitive purposes, then perhaps for online purposes we might be putting too much data in to the DB ?

Also aren’t we also looking at slow’ish DB access when the DB would be getting quite huge over that period of time? I’ve vaguely heard about issues with large DB’s , and with the excess data generated with the current WDMySQL, it’d certainly would be a rather huge DB over a year, let alone longer than that.

Anyone know the size of their DB after 12 months or 2 years ? :slight_smile:

12

I used to have the “duplicate” problem periodically. The advice I got here was to use Access to change the spec so that the timestamp was not Unique. I did that, and have had no further trouble.

13
I used to have the "duplicate" problem periodically. The advice I got here was to use Access to change the spec so that the timestamp was not Unique. I did that, and have had no further trouble.

That will work - change the index to be non-unique. It won’t affect most people much, if at all, though it will slow queries. You could just create a new unique index of auto-incrementing numbers of course…

On the question of table size; I import each month’s data into a different table - e.g. wxdata012007 is the table for January 2007. Each table is approximately 4.8mB in size, and contains circa 44.5k records. This is sampling every minute. I certainly wouldn’t recommend one humongous table - there are some issues with MySQL with very large tables. A year’s worth of one minute samples is over 500k records, and will be a bit of a beast to manage…

A different table each month complicates queries a bit e.g. interval MySQL queries may have to access multiple tables, however it is more efficient from a memory and administrative standpoint.

The 31 days data import bug I reported, is a pain. Brian will get around to it I’m sure, but its a minority used feature I suspect, and it’ll have to wait its turn. I’ve been unable to import a lot of my historical data, and its a blocking issue for the historical data part of my website, but its hardly a show stopper :wink:

14

G’day all,

Thanks for the feedback. have changed to non-unique index & have fingers & toes crossed!!!

For the longer term i think i’ll php up a routine to suck in the clientraw.txt file and only store the core values.

Cheers & thanks again to all those who replied!

Dave.

15

How did you change that index? I’m seeing that duplicate error just about every time I start WDMysql…
Thanks!

16

Have you guys using MySQL seen my thread and can you please check to see if you have the problem with the hour record being written one hour after it should?

Stuart

17

try this update:
http://www.weather-display.com/downloadfiles/wdmysql.zip

should fix the on the hour problem
( it was happening to me too, checking now (it was just the hour that was not going forward when the minute was going to 0 (the data is OK))
and you need to use this updated WD version too
http://www.weather-display.com/downloadfiles/WeatherD.zip

18

Are these fixes relating the wdmysql and the duplicate entries all part of the current ‘FULL’ download?

19

Yes :wink: