dbase confusion

[John B]

Two problems, possibly related but maybe not.

I have put links to four images as they are large-ish files (1 to 1.5MB).

Firstly: The reformat of the database doesn't work. It worked once - but now claims to have done the reformat but has not. The result can be seen in the dbase-first image, which is the earliest page in the set.

Secondly: You will see in the last page image and the penpenult page image that records are being inserted multiple times. One entry I looked at (it's not on these images) had 18 iterations. The penult image is fine. Don't ask me what's going on as I just don't know.

Most of the duplicates I've looked at are identical, however this is not always the case. Looking at the top two entries on the penpenult image you will note that the wind bearing reading is 1º different.

A little background: I noticed these anomolies a week or two age. The dbase was downloaded so I could have a more leisurely look at it. Anyway, in the end I deleted the alldata table from the server and reloaded it. Not being all that familiar with dbs this took some time and a couple of false starts. Basically, the db that is now active is the same as the original download. OK, that's probably useless information, but you (well, I, really) never know.

Right, that's it. Does any expert on here have an inkling as to what might be happening? At this point I'm going around in logic-circles. Could it even be the way my host is serving up the data? It seems unlikely, but at this point I'll grab at any passing straw.

Your thoughts, ladies and gentlemen, would be very much appreciated.

Cheers,
John


http://www.blaxlandweather.com/Images/dbase-first.bmp
http://www.blaxlandweather.com/Images/dbase-last.bmp
http://www.blaxlandweather.com/Images/dbase-penult.bmp
http://www.blaxlandweather.com/Images/d ... penult.bmp

[Jachym]

This looks as if your db was being updated by two independent templates,, did you change website recently? If so, did you turn off the original update?

[Luc]

Hi John,
A few things puzzels me.
Why does the reformat not work?
How does meteotemplate get its data? Via a data file/CRON-job, API or both?
What is the cause of those 18 iterations in your database?
Why does the database allow multiple records with the same date-time key?
I can help you to sort these things out.
Probably it's better to have private mail contact about this.
Luc

[John B]

You first, Jáchym:

Yes, I did start a new web site. It is totally divorced from the original (other than a link to it).

The original still has your template installed, with a message and link as the landing page and the menus disabled, but anything left there would still point to the original site. Should anyone have 'bookmarked' a particular page then they will find only old data. I have checked the db and it shows the last entry as 2017-09-02 (or thereabouts, I can't remember exactly).

The API for the Weather Display program I use has been changed to the new address. It is not possible to have it forwarded to two addresses.

I did have a second incidence of Weather Display's clientraw suite being directed to the new site as I was unsure at the time whether all my data was now API controlled or not. I checked all my blocks and plugins and found none, but although I'd changed them in WD they were still active. Fixed now - apparently required a restart. Still, that does not explain the duplicates from the earlier dates in the db.

I am not claiming innocence. It's possible that I have inadvertently merged databases, although I thought that was impossible with identical date/time stamps.


Now you, Luc:

Puzzles you? You can imagine my total and utter puzzlement.

I think I've covered most of what you have stated in my comments to Jáchym.

As a matter of interest, when I came across those 18 repetitions I counted the previous two time-stamps. The previous one was 16 and the one prior to that 14. Unfortunately I didn't make a note as to what the times were as it was then that I decided to walk to my local shops and purchase a bottle of single malt. Maybe that is why I have been a bit light-headed.

As far as your offer goes, I would be most appreciative. I have done a bit of research on the Internet on the subject of cleaning duplicates but the results seem to conflict. Still, why not continue the discussion here? I don't mind being embarrassed if I've made an error. By the same token I'm quite happy for you to use the PM facility and then publish the solution for the edification of any other member who finds themselves in a similar situation.

It's your choice.

Thanks to both of you for your attention.

Cheers,
John

[Luc]

Hi John,

I like to start with examining you database structure. Normally it isn't possible to store two or more records with the same time stamp.
I did the following test.
On my webserver I started program phpMyAdmin.
I hope you also have this program, because you will be able to perform the same test.
BTW. I have phpMyAdmin in Dutch and have translated the commands to English, may be the descriptions differ slightly.

• select your alldata table

• Export tab

• Export method - extended

• Select - Rows | Export a few rows

• change the listed number to 10

(with 10 rows exported we might see some double date-time entries)

• Scroll to the bottom and select Start

A file alldata.sql should now be downloaded to your computer.
In my example the file looks like:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 4.7.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Gegenereerd op: 17 sep 2017 om 03:29
-- Serverversie: 5.6.36
-- PHP-versie: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ptmh234278_mttst`
--

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `alldata`
--

CREATE TABLE `alldata` (
  `DateTime` datetime NOT NULL,
  `T` decimal(4,1) DEFAULT NULL,
  `Tmax` decimal(4,1) DEFAULT NULL,
  `Tmin` decimal(4,1) DEFAULT NULL,
  `H` decimal(4,1) DEFAULT NULL,
  `D` decimal(4,1) DEFAULT NULL,
  `W` decimal(4,1) DEFAULT NULL,
  `G` decimal(4,1) DEFAULT NULL,
  `B` decimal(4,1) DEFAULT NULL,
  `RR` decimal(7,3) DEFAULT NULL,
  `R` decimal(7,3) DEFAULT NULL,
  `P` decimal(7,3) DEFAULT NULL,
  `S` decimal(5,1) DEFAULT NULL,
  `A` decimal(4,1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Gegevens worden geëxporteerd voor tabel `alldata`
--

INSERT INTO `alldata` (`DateTime`, `T`, `Tmax`, `Tmin`, `H`, `D`, `W`, `G`, `B`, `RR`, `R`, `P`, `S`, `A`) VALUES
('2017-09-04 20:35:00', '26.6', '26.6', '26.6', '82.8', '23.4', '1.1', '3.2', '48.0', NULL, '0.400', '1009.100', '0.0', '31.8'),
('2017-09-04 20:40:00', '26.6', '26.6', '26.6', '83.0', '23.4', '0.3', '3.2', '42.0', NULL, '0.000', '1009.300', '0.0', '31.9'),
('2017-09-04 20:45:00', NULL, NULL, NULL, '83.2', NULL, '3.0', '0.0', '15.0', NULL, '0.000', NULL, '0.0', NULL),
('2017-09-04 20:50:00', NULL, NULL, NULL, '83.3', NULL, '2.7', '0.0', '7.0', NULL, '0.000', NULL, '0.0', NULL),
('2017-09-04 20:55:00', NULL, NULL, NULL, '83.3', NULL, '1.2', '0.0', '5.0', NULL, '0.000', NULL, '0.0', NULL),
('2017-09-04 21:00:00', NULL, NULL, NULL, '83.4', NULL, '1.2', '0.0', '5.0', NULL, '0.000', NULL, '0.0', NULL),
('2017-09-04 21:05:00', NULL, NULL, NULL, '83.5', NULL, '3.5', '0.0', '65.0', NULL, '0.000', NULL, '0.0', NULL),
('2017-09-04 21:10:00', '26.4', '26.4', '26.4', '83.7', '23.4', '2.4', '0.0', '58.0', NULL, '0.400', '1009.500', '0.0', '31.4'),
('2017-09-04 21:15:00', '26.3', '26.4', '26.3', '83.7', '23.3', '0.9', '6.4', '40.0', NULL, '0.400', '1009.600', '0.0', '31.6'),
('2017-09-04 21:20:00', '26.3', '26.3', '26.3', '83.7', '23.3', '0.8', '6.4', '32.0', NULL, '0.400', '1009.700', '0.0', '31.6');

--
-- Indexen voor geëxporteerde tabellen
--

--
-- Indexen voor tabel `alldata`
--
ALTER TABLE `alldata`
  ADD PRIMARY KEY (`DateTime`),
  ADD UNIQUE KEY `DateTime` (`DateTime`);
COMMIT;

/*!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 */;

At the bottom you see:

Code: Select all

ALTER TABLE `alldata`
  ADD PRIMARY KEY (`DateTime`),
  ADD UNIQUE KEY `DateTime` (`DateTime`);
COMMIT;

The statements would prevent double records with the same DateTime.

Please do the same test as I did and show your export file.

Cheers, Luc

[John B]

OK, Luc.
This might be a bit big - I hope it scrolls.

Code: Select all

-- phpMyAdmin SQL Dump
-- version 4.7.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Sep 17, 2017 at 12:12 PM
-- Server version: 5.6.37
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `johnsbla_Meteo`
--

-- --------------------------------------------------------

--
-- Table structure for table `alldata`
--

CREATE TABLE `alldata` (
  `DateTime` datetime NOT NULL,
  `T` decimal(4,1) DEFAULT NULL,
  `Tmax` decimal(4,1) DEFAULT NULL,
  `Tmin` decimal(4,1) DEFAULT NULL,
  `H` decimal(4,1) DEFAULT NULL,
  `D` decimal(4,1) DEFAULT NULL,
  `W` decimal(4,1) DEFAULT NULL,
  `G` decimal(4,1) DEFAULT NULL,
  `B` decimal(4,1) DEFAULT NULL,
  `RR` decimal(7,3) DEFAULT NULL,
  `R` decimal(7,3) DEFAULT NULL,
  `P` decimal(7,3) DEFAULT NULL,
  `S` decimal(5,1) DEFAULT NULL,
  `A` decimal(4,1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `alldata`
--

INSERT INTO `alldata` (`DateTime`, `T`, `Tmax`, `Tmin`, `H`, `D`, `W`, `G`, `B`, `RR`, `R`, `P`, `S`, `A`) VALUES
('2017-09-15 21:20:00', '13.1', '13.2', '13.1', '53.0', '3.7', '0.0', '1.9', '88.0', '0.000', '0.000', '1019.900', NULL, '11.7'),
('2017-09-15 21:25:00', '13.1', '13.1', '13.0', '53.0', '3.7', '0.0', '1.9', '27.0', '0.000', '0.000', '1020.000', NULL, '11.7'),
('2017-09-15 21:25:00', '13.1', '13.1', '13.0', '53.0', '3.7', '0.0', '1.9', '27.0', '0.000', '0.000', '1020.000', NULL, '11.7'),
('2017-09-15 21:30:00', '12.9', '13.0', '12.8', '53.8', '3.7', '0.0', '0.0', '0.0', '0.000', '0.000', '1020.000', NULL, '11.6'),
('2017-09-15 21:35:00', '12.8', '12.8', '12.6', '54.4', '3.7', '0.0', '1.9', '16.0', '0.000', '0.000', '1020.200', NULL, '11.4'),
('2017-09-15 21:35:00', '12.8', '12.8', '12.6', '54.4', '3.7', '0.0', '1.9', '16.0', '0.000', '0.000', '1020.200', NULL, '11.4'),
('2017-09-15 21:40:00', '12.6', '12.6', '12.6', '55.0', '3.7', '1.5', '3.7', '81.0', '0.000', '0.000', '1020.200', NULL, '11.0'),
('2017-09-15 21:45:00', '12.5', '12.6', '12.4', '55.0', '3.6', '0.0', '1.9', '228.0', '0.000', '0.000', '1020.300', NULL, '11.1'),
('2017-09-15 21:50:00', '12.3', '12.4', '12.1', '55.0', '3.4', '0.4', '1.9', '331.0', '0.000', '0.000', '1020.300', NULL, '10.9'),
('2017-09-15 21:55:00', '12.1', '12.2', '11.9', '55.8', '3.5', '0.8', '3.7', '35.0', '0.000', '0.000', '1020.300', NULL, '10.5'),
('2017-09-15 22:00:00', '11.9', '11.9', '11.8', '56.0', '3.3', '1.1', '3.7', '344.0', '0.000', '0.000', '1020.400', NULL, '10.2'),
('2017-09-15 22:05:00', '11.8', '11.8', '11.8', '56.8', '3.4', '0.0', '1.9', '96.0', '0.000', '0.000', '1020.400', NULL, '10.4'),
('2017-09-15 22:05:00', '11.8', '11.8', '11.8', '56.8', '3.4', '0.0', '1.9', '95.0', '0.000', '0.000', '1020.400', NULL, '10.4'),
('2017-09-15 22:10:00', '11.8', '11.8', '11.8', '57.0', '3.5', '0.7', '5.6', '108.0', '0.000', '0.000', '1020.400', NULL, '10.3'),
('2017-09-15 22:10:00', '11.8', '11.8', '11.8', '57.0', '3.5', '0.7', '5.6', '108.0', '0.000', '0.000', '1020.400', NULL, '10.3'),
('2017-09-15 22:15:00', '11.8', '11.8', '11.6', '57.0', '3.5', '2.6', '3.7', '28.0', '0.000', '0.000', '1020.500', NULL, '9.9'),
('2017-09-15 22:15:00', '11.8', '11.8', '11.6', '57.0', '3.5', '2.6', '3.7', '29.0', '0.000', '0.000', '1020.500', NULL, '9.9'),
('2017-09-15 22:20:00', '11.6', '11.6', '11.5', '57.0', '3.3', '1.5', '3.7', '44.0', '0.000', '0.000', '1020.500', NULL, '9.8'),
('2017-09-15 22:20:00', '11.6', '11.6', '11.5', '57.0', '3.3', '1.5', '3.7', '42.0', '0.000', '0.000', '1020.500', NULL, '9.8'),
('2017-09-15 22:25:00', '11.4', '11.5', '11.4', '57.0', '3.1', '0.8', '1.9', '115.0', '0.000', '0.000', '1020.300', NULL, '9.8'),
('2017-09-15 22:25:00', '11.4', '11.5', '11.4', '57.0', '3.1', '0.8', '1.9', '115.0', '0.000', '0.000', '1020.300', NULL, '9.8'),
('2017-09-15 22:30:00', '11.4', '11.4', '11.3', '57.6', '3.3', '0.0', '3.7', '336.0', '0.000', '0.000', '1020.300', NULL, '10.0'),
('2017-09-15 22:30:00', '11.4', '11.4', '11.3', '57.6', '3.3', '0.0', '3.7', '336.0', '0.000', '0.000', '1020.300', NULL, '10.0'),
('2017-09-15 22:35:00', '11.3', '11.3', '11.2', '58.0', '3.3', '1.2', '3.7', '82.0', '0.000', '0.000', '1020.300', NULL, '9.7'),
('2017-09-15 22:35:00', '11.3', '11.3', '11.2', '58.0', '3.3', '1.2', '3.7', '82.0', '0.000', '0.000', '1020.300', NULL, '9.7'),
('2017-09-15 22:40:00', '11.2', '11.2', '11.2', '58.0', '3.2', '0.0', '0.0', '44.0', '0.000', '0.000', '1020.300', NULL, '9.7'),
('2017-09-15 22:40:00', '11.2', '11.2', '11.2', '58.0', '3.2', '0.0', '0.0', '44.0', '0.000', '0.000', '1020.300', NULL, '9.7'),
('2017-09-15 22:45:00', '11.1', '11.2', '10.9', '58.2', '3.1', '0.0', '0.0', '24.0', '0.000', '0.000', '1020.300', NULL, '9.6'),
('2017-09-15 22:45:00', '11.1', '11.2', '10.9', '58.2', '3.1', '0.0', '0.0', '24.0', '0.000', '0.000', '1020.300', NULL, '9.6'),
('2017-09-15 22:50:00', '10.9', '10.9', '10.9', '59.0', '3.1', '0.0', '0.0', '102.0', '0.000', '0.000', '1020.200', NULL, '9.4'),
('2017-09-15 22:50:00', '10.9', '10.9', '10.9', '59.0', '3.1', '0.0', '0.0', '102.0', '0.000', '0.000', '1020.200', NULL, '9.4'),
('2017-09-15 22:55:00', '10.8', '10.9', '10.7', '59.0', '3.0', '0.0', '3.7', '52.0', '0.000', '0.000', '1020.100', NULL, '9.3'),
('2017-09-15 22:55:00', '10.8', '10.9', '10.7', '59.0', '3.0', '0.0', '3.7', '52.0', '0.000', '0.000', '1020.100', NULL, '9.3'),
('2017-09-15 23:00:00', '10.6', '10.7', '10.6', '60.0', '3.1', '1.1', '5.6', '199.0', '0.000', '0.000', '1020.100', NULL, '8.9'),
('2017-09-15 23:00:00', '10.6', '10.7', '10.6', '60.0', '3.1', '1.1', '5.6', '199.0', '0.000', '0.000', '1020.100', NULL, '8.9'),
('2017-09-15 23:05:00', '10.6', '10.6', '10.4', '60.0', '3.1', '0.0', '1.9', '70.0', '0.000', '0.000', '1020.100', NULL, '9.1'),
('2017-09-15 23:05:00', '10.6', '10.6', '10.4', '60.0', '3.1', '0.0', '1.9', '68.0', '0.000', '0.000', '1020.100', NULL, '9.1'),
('2017-09-15 23:10:00', '10.4', '10.5', '10.4', '60.8', '3.1', '1.2', '3.7', '358.0', '0.000', '0.000', '1020.000', NULL, '8.7'),
('2017-09-15 23:10:00', '10.4', '10.5', '10.4', '60.8', '3.1', '1.2', '3.7', '358.0', '0.000', '0.000', '1020.000', NULL, '8.7'),
('2017-09-15 23:15:00', '10.4', '10.4', '10.4', '61.0', '3.1', '3.0', '5.6', '52.0', '0.000', '0.000', '1019.900', NULL, '8.4'),
('2017-09-15 23:15:00', '10.4', '10.4', '10.4', '61.0', '3.1', '3.0', '5.6', '51.0', '0.000', '0.000', '1019.900', NULL, '8.4'),
('2017-09-15 23:20:00', '10.4', '10.4', '10.4', '61.0', '3.1', '1.1', '5.6', '102.0', '0.000', '0.000', '1019.700', NULL, '8.7'),
('2017-09-15 23:20:00', '10.4', '10.4', '10.4', '61.0', '3.1', '1.1', '5.6', '103.0', '0.000', '0.000', '1019.700', NULL, '8.7'),
('2017-09-15 23:25:00', '10.4', '10.4', '10.3', '61.0', '3.1', '0.8', '3.7', '55.0', '0.000', '0.000', '1019.700', NULL, '8.7'),
('2017-09-15 23:25:00', '10.4', '10.4', '10.3', '61.0', '3.1', '0.8', '3.7', '57.0', '0.000', '0.000', '1019.700', NULL, '8.7'),
('2017-09-15 23:30:00', '10.3', '10.3', '10.3', '61.0', '3.0', '0.4', '1.9', '43.0', '0.000', '0.000', '1019.700', NULL, '8.7'),
('2017-09-15 23:30:00', '10.3', '10.3', '10.3', '61.0', '3.0', '0.4', '1.9', '43.0', '0.000', '0.000', '1019.700', NULL, '8.7'),
('2017-09-15 23:35:00', '10.2', '10.3', '10.2', '61.0', '2.9', '0.7', '1.9', '63.0', '0.000', '0.000', '1019.600', NULL, '8.6'),
('2017-09-15 23:35:00', '10.2', '10.3', '10.2', '61.0', '2.9', '0.7', '1.9', '62.0', '0.000', '0.000', '1019.600', NULL, '8.6'),
('2017-09-15 23:40:00', '10.2', '10.2', '10.2', '61.0', '2.9', '0.0', '1.9', '94.0', '0.000', '0.000', '1019.500', NULL, '8.7');
COMMIT;

/*!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 */;

There is no unique or primary key!

[Luc]

Hi John,

Your database has not the right settings, so we should fix that.
First of all make sure you have a backup of all your meteotemplate databases.
I'm thinking out loud, hopefully I will give the right advice
What I should try is the following:

• May be you can change with phpMyAdmin the DateTime field properties and set the primary key and unique value fields to true (but I'm afraid that won't work with the current duplicate DateTime entries)

So the other method would be:

• rename with phpMyAdmin your alldata table into something like 'alldata_old' (then you will have this as a backup)

• Go to your main setup

• Locate section MySQL Database

• perform a Test Connection (this will create a blank alldata table with the right constrains)

• read back (i.e. import file) one of your backupped database files and check the contents of the database with phpMyAdmin

• When all looks OK, import all other database backups

Success!

[John B]

Thanks, Luc,

I have been experimenting for a couple of hours - without success.

I shall try your suggestions if I get a chance later this afternoon my time. Are not you supposed to be asleep now?

Thank you very much for your support.

Regards,
John

[Jachym]

Just want to add to this, phpmyadmin has this very handy function for exporting and importing entire databases, not the individual tables, great for backup and essential for moving sites.

Unfortunately this cannot be added to meteotemplate, in this case it is technically not possible to do, I tried it already 3 yrs ago on my own website before meteotemplate, the problem lies in limitations imposed by webhosting providers which are only turned off for this one particular case in phpmyadmin directly on the server, I talked to them about it back then, I actually downloaded the raw phpmyadmin code and the same function performed outside phpmyadmin gives fatal error, so there really is no point in trying, it will never work

[John B]

Well, Luc, Jáchym, it didn't work.

[Luc]

Hi John,
Can you be more specific please?
Which steps did you take and what were the problems?
Luc

[John B]

Sorry to be so late, Luc, but time differences and my research/experimentation interferes.

OK, the reason it failed was because it has duplicates in the original db file. Oddly, though, when I looked at the fresh table, expecting it to be empty, it did have entries and many of those were duplicates. Anyway, the result was that the import of the backed up table was aborted by MySQL due to duplicates.

I might have found out why the duplicates are still happening, but I'll have to wait a few hours to see if this is so. If I can be satisfied that no further duplicates will occur then I'll sacrifice the last two or three weeks data and start again from my latest non-duplicate backup. It seems that there is little choice.

I have made the DateTime column an index, which is fine, but as soon as I attempt to make it unique it's the same problem - duplicates. That's where I'm at, Luc. One thing is for sure, I'm not going through 1.4 million rows deleting the dupes.

[Luc]

Hi John,
With the method below you can correct the duplicates.

• Make a backup of your alldata database with Meteotemplates Control panel - Backup.

• Let Meteotemplate create a new (empty) alldata table.
  Rename your alldata table with phpMyAdmin to something else (e.g. alldata_old)
  Go to Meteotemplates Main Setup and do a Test Connection: Meteotemplate will create a new empty alldata table.

• Use Meteotemplates Data Import function to import all your backupped csv (year) files. It will skip duplicate entries by default or you can choose overwrite a duplicate entry with the new data.

I can assist with the Data Import settings when needed: it takes some study before all entries are setup correctly.
Cheers, Luc

[John B]

Thanks for your advice, Luc.

I'm trying to find the culprit for the duplicated inserts. My earlier surmising was wrong. Once I am happy with only single data sets being inserted then I shall attempt the routine you have suggested.

Cheers,
John

[Luc]

Suppose you are in a situation that you have a backup of all years of your alldata table and you want to start all over
(on a new webserver for instance) with importing the backupped data in a new created alldata table.

When you perform a database backup of a certain year (e.g. 2017) with Control panel - Backup - 2017 - OK
there will be created the following backup file: [template_home]/backup/[date]db/alldata[year].csv.
In my case this would be the following file: http://www.lucdesign.nl/template/backup ... ta2017.csv

Follow the instructions below to import these year csv-files into a new created alldata table.

Start Control panel - Database - Data import

"Data Import from txt/CSV file"
Use the following settings:

URL: ../backup/20170917db/alldata2017.csv (set the date and the year file name for your situation)
Field delimiter: , (comma)
Decimal separator: . (period)
Header Row: No
Import type: Skip existing
Date and time fields: Date and time in a single field
Date and time field number: 0
Date and time format: "Y-m-d H:i:s" (check the date format in your backupped files, may be you need "Y/m/d H:i:s")
TIP: don't forget to put the double quotes (") around the date-time format!
Field numbers:
Temperature: 1
Humidity: 4
Pressure: 11
Wind speed: 6
Wind gust: 7
Cumulative daily precipitation: 10
Rain rate: 9
Wind direction: 8
Solar radiation: Enabled/Disabled 12

IMPORTANT: Don't forget to set the units the same as your Database units.

Perform a 'Test Import'
If all looks OK do an 'Import Data'

Change the URL to another year file and do again an 'Import Data'.
Repeat this for all saved year backup files.

Success!

[Luc]

Hi John,
You wrote:

I'm trying to find the culprit for the duplicated inserts. My earlier surmising was wrong. Once I am happy with only single data sets being inserted then I shall attempt the routine you have suggested.

Check the CRON jobs on your web server.
May be there is still an entry active which reads your weather data from a file and thus creating duplicate entries in the data base.

[John B]

Lovely, Luc, thanks for the step-by-step instructions.

The Cron job - that's what I thought I had found, but it's still updating (sometimes) two data sets. Ho-hum, I'm missing something, somewhere but I'll track it down eventually.

I'm off to have something like that which the little chap to the right is having.

[Luc]

Hi John,
I wonder what happens when you stop sending data to the api.
When your data still is updated each 5 minutes, we know for sure there is a second source.
Luc

[John B]

Great minds think alike, Luc,

That's what I've been doing. It's not sure as yet but tomorrow - 12-hours or so - I too will know the answer.
Obviously I have turned off the API section of my Weather Display program.

I shall see then.

Rgds,
John

[Luc]

John,
Until this moment the api gets each 5 á 6 seconds a call from http to the api with weather data.
Are you sure you turned off the api section of WD?
Cheers, Luc

[John B]

OK, bear with me on this.

This morning MT reported a 15-minute outage (01:10 - 01:15) and a 25-minute one (06:00 - 06:25). So I looked at the web server with CPanel.

The first outage:
1:00 - 4 dupes
1:05 - 2
1:10 - 56
1:15 - 2
After that it settled down to the 'normal' single entries/double entries.

The second outage:
5:50 - 5
5:55 - 28
6:00 - 221
6:25 - return to 'normal'

So, if my thinking is correct, it seems that the program is treating the transaction as though it was testing an off-line situation by attempting to re-send the data. However, the data is actually getting through. When the connection seems to be clear again the current data is transmitted.

If I hadn't mucked up my data base by dropping somehow the index/unique attribute I wouldn't have known about the situation as the duplicated datetime would have been rejected and all I'd be aware of would be an outage report. If it is the case then something is amiss with the client/server channel. I don't think it's my connection as I've FTP'd some pretty large files of late without any mishaps (unless the disappearance of attributes was one).

Am I misreading all this, do you think?

I haven't looked at the db backup at this point. A year's worth of data I suspect will be too much I think - in CSV format - and will probably 'break' my host's upload limit (50MiB). I haven't checked the db in control panel so if it has the option for shorter periods all well be fine.

Thanks for all your trouble, it's greatly appreciated.

[Luc]

Hi John,

I'm trying to understand what is happening.
I don't know the meaning of the word 'dupes', so I got lost with your outage presentations.

Secondly I still have no explanation of the double database entries. The API won't write double entries.
I'm still thinking a 'forgotten' cron job triggers an outdated MT file which still reads the realtime.txt file and writes a second entry to the database (thus not via the API).

I had a look at your API log this morning and noticed some abnormalities. The time between two messages, which is typically between 5 and 6 seconds, was much longer: 150, 120 and 34 seconds.
time diff (s)
8:30:01
8:32:31 150
8:34:31 120
8:35:05 34
8:35:15 10
8:35:20 5
8:35:25 5
8:35:30 5
8:35:36 6
8:35:41 5
8:35:46 5
BTW. One single registration per 5 minutes is enough to let the API write an entry to the database.

I have looked at the size of my backup files. A full year CSV-file has a size of 8.8 - 9.4 MB.

[Luc]

John,
I forgot you to ask to change your website link in your profile. It is still pointing to a non-existing page of your old website.

[John B]

Sorry, Luc,

By "dupes" I meant duplicate entries. *Note to self - keep colloquialisms out of postings*

The double entries come about, I think, because at some time I have accidentally removed the 'unique' parameter from the datetime in the database structure. The duplication of earlier entries has probably come about at the same time, when I have accidentally allowed the merging of two databases.

I'll have a look at your other information after my luncheon.

Regards,
John

[John B]

Web address: Fixed.
Thanks for the advice.