dbase confusion

[John B]

I became increasingly weary of the problem and the outcome of my careless ineptitude.

So I have deleted my alldata table and renamed my non-corrupted backup table to 'alldata'.

Then I copied that table back as alldata_bkup.

I've lost 15-days of data, but that shouldn't be too much of a problem given time. Hopefully it will stop the duplicates occurring, although why they were happening is still a mystery to me.

We shall see.

Thanks to you both for your input to date.

Cheers,
John

[Luc]

Thanks John,
In your first message you mentioned another problem: the reformat was not working. Did you retry the reformat since then? This was my other puzzle, you know.

[John B]

I didn't yesterday, Luc, being more interested at that point in just checking on the 'new' database to make sure it was working.

This morning I checked the entries since it was sorted. No duplicates (as expected, now it has the proper structure) but about 4% of dropped/missing data rows. Looking back to the data showing prior to yesterday it looks like there's some data missing. Perhaps it always was and I hadn't noticed. I shall cross check with the back-up table later.

I ran the reformat.php a short while ago. Checking the db shows me that it worked and created a back-up. I'd renamed the original back-up to allow for that happening. It won't hurt to have two back-ups for a while, at least until I'm confident that it is stable.

Of course, the program might still be sending duplicate sets of data so I might have to do some more investigating. At least now, though, the problem seems not to be affecting the table contents.

Thanks for your moral support, Luc, I think you helped keep me sane.

[Luc]

It was a pleasure working with you, John.
Cheers, Luc

[John B]

EPILOGUE

I am happy with the result of this rather long-winded exercise, but it is not perfect.

The worry is the conversion of data base information. On the penultimate data base creation it 'lost' many data from early July to the current time. A second attempt, after deleting the newly created db got close.

The first attempt introduced data which did not exist in the back-up set and at the same time dropped all other data from early July 2017. A second iteration of the back-up files seems to have worked, other than losing a few hours of the latest information. I can live with that.

This morning (2017-09-22) my time at about 08:02 it again stopped working. After banging around Weather Display trying to sort out the problem I turned on the WD API. It now "seems" to work. Why on this Earth of ours would these odd things happen?

[Jachym]

Im afraid in this case I would have to answer - I have no clue You seem to be a bit unlucky

[kd7eir]

I am trying to import backups due to moving web hosts. The time in my csv appears as 2004-11-28 15:07:00
I have the date and time format set as Y-m-d H:i:s

I get the following error:There seems to be a problem with the date and time. Either it has not been parsed correctly or it is a date/time in the future! Check the format you specified and also the actual values.

Any ideas?

[Luc]

Probably you have set a wrong link to the csv file.
Try first option "show field numbers". This option show more error situations.

[kd7eir]

Show field numbers pulls up the file just fine.

[Luc]

Did you set the double quotes around the format? It is essential because they are also in the csv file.
"Y-m-d H:i:s"

[kd7eir]

That was it. Thank you!

[John B]

A new db problem, one which is a little more serious for visitors. MY SITE HAS BEEN SUSPENDED.

I have been in touch with my host and have done a bit of banging around on this Internet thing. I'm puzzled.

I've attached a screen image which is a bit rough (altered slightly to protect the innocent) and which depicts the answer I received from the site's host when I enquired as to what was happening to cause the suspension. It appears that there is continuous access going on (i.e. something is not closing/shutting down/truncating). I have absolutely no idea as to why this should be.

Rummaging around in the WWW, including StackOverflow and others, has not clarified anything for me.

Now I am in real trouble, so any expert advice will be most gratefully accepted.

[Luc]

Now I am in real trouble, so any expert advice will be most gratefully accepted.

Hi John,

As far as I know the command "ALTER TABLE alldata ORDER BY DateTime" is only used by the program api.php.
Normally this command should not be executed more often than once per 5 minutes.

Unless the time stamps in the api calls would jump up and down with more than 5 minutes difference. In such cases there could be extra write calls to the database.

I looked at the log of your latest received api messages and your time stamps are not in an increasing order.
Also the intervals are irregular, see below.

"2017-10-03 00:54:29",
"2017-10-03 00:53:57",
"2017-10-03 00:54:45",
"2017-10-03 00:54:35",
"2017-10-03 00:54:55"],

Do you have an explanation for this behaviour?
Are more than one sources sending data to the api?
May be someone was testing and sent api calls manually?

BTW. In the example above the time stamps would NOT have caused extra database calls.

Luc

[John B]

Thanks for you trouble, Luc.

The site is now back up and running and the database is being updated (I think). My host still has the site as 'suspended' so I cannot as yet get access to the database itself. Hopefully soon.

No one seems to know what it was all about, least of all, me. All I can do now is wait and hope it doesn't re-occur.

Cheers,
John