archive: unable to add record [..] to database 'weewx.sdb': UNIQUE constraint failed: archive.dateTime

[Ξ]

After a reboot and absolutely no changes something went awry: I started seeing lots of these 'unable to add record' errors. I restarted the service and then it was ok.

Here is a detailed log: http://pastebin.com/F1jDBDeU

I run 2.7, on Arch Linux with La Crosse 2800 hw.

[Andrew Milner]

re you sure that your Pi had re-established the correct time before you started weewx??  I see date/times of epoch 00000000 (1st jan 1970) and I also see the log times jumping backwards 3 hours at one point.  All looks as though you may have started weewx too soon after the reboot and before the RPi had got itself sorted out and stabilised.   From then on you are likely to get duplicate date/time stamps espeialy whilst reading (re-reading) history records from the weather station which  have already been recorded in the archive.  On your restart I suspect the RPi had got its time sorted out and all went smoothly.

Just hunches and suspicions!

[Ξ]

The weewx service always starts at boot and it's always before R-Pi get its time/ntp sorted. You could be right, it's just that it normally works without this sort of problem.
I've changed now the service to start After=network.target ; we'll see how that works out.

[Thomas Keffer]

That only guarantees that weewx will not start until "the network is up," whatever that means.

You have to delay weewx until not only is the network up, but NTP is up, and it's done a time fix.

If you do not use the fake-hwclock package (and, it looks like you don't), then you can detect when NTP has been successful by checking the time and looping until it is somewhere in the 2000s. 

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ξ]

supposedly, network.target state is when the network is up and that's when ntp synchronises. It doesn't seem to work as I expected though.

As for fake-hwclock I simply didn't know it exists. I'll try it out. Thanks :)

[Thomas Keffer]

No, you do not want fake-hwclock. It makes things even worse! What it does is pick up from the time the RPi last stopped. That means that if your RPI stopped for an hour, your clock will be behind an hour. This makes it even harder to detect if the time has been set.

It's easy to tell the difference between 1970 and 2014. Not so easy to tell the difference between now and 30 minutes ago!

-tk

[Ξ]

Cheers, I won't touch it then.

I'll further look into making weewx start after time is updated.

[Andrew Milner]

search the forum - i could not find it just now but i know there are changes to start script that ensure ntp is running before starting weewx - and it is all documented int he forum if u can find the right thread!

[Ξ]

So, I finally figured out how to make weewx start after the OS sets the time. Can somebody, please, help me out how to clean up the database from the non-records that go back to 1970?

[vds]

On Monday, December 1, 2014 10:56:33 AM UTC-8, Ξ wrote:

So, I finally figured out how to make weewx start after the OS sets the time. Can somebody, please, help me out how to clean up the database from the non-records that go back to 1970?

hmmm - are things really that dramatic ?

 

depends on what you mean by 'clean up' but I guess I'd suggest:

• figure out the date you want as your oldest possible record, convert that to seconds since the epoch
• stop weewx
• make a copy of your weewx.sdb and stats.sdb databases for safekeeping
• do a sqlite3 'update' command to delete records from the archive table with a dateTime value < your minimum acceptable dateTime value....working in a 'copy' of your weewx.sdb just in case
• verify your modified database is still good
• copy the modified weewx.sdb back into place
• delete your stats database so weewx will regenerate it on is next startup
• start weewx, wait around a bit to let it regenerate stats.sdb, watch your syslog to see it complete that
• go for the victory beer

Rather than just give you the answers, I'd suggest you try some google queries to learn how to do this stuff.  You'll likely need to do the above more than once if you run weewx for a while.

Some google queries that will get you there are:

• look up "calculate seconds since epoch for a date"
• look up "sqlite tables in database"
• look up "sqlite delete record example"
  
• look up "sqlite check database integrity"

All this stuff has come up before on the google group, which is also searchable.

[Andrew Milner]

OR

if you have a series of 'rogue' records with wrond timestamps starting at dateTime 0000000000 or thereabouts, and you know the dateTime value that this SHOULD be you can, instead of deleting the records in the update command, change the values by 

UPDATE archive SET dateTime = dateTime + dateTimevalueyouwant WHERE dateTime < firstgenuinevalueindatabase;

[Ξ]

Alright,

What I mean is: the actual records in the db begin on the 22nd of October 2014. However, because weewx used to start at boot before time synchronization, I have empty monthly and yearly summeries that go back to Jan 1970. I don't need anything older than October 2014.

Thanks for the clues, I'll look up your suggestions.

[Andrew Milner]

The all you need to do is simply delete where dateTime < 22 October from archive, delete stats.db, and restart weewx to rebuild stats.

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/8LYa2yBwPMk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.

[Thomas Keffer]

Then something like

$ sqlite3 weewx.sdb

sqlite> delete from archive where dateTime < 946684800;

(946684800 is 1-Jan-2000)

-tk

--

You received this message because you are subscribed to the Google Groups "weewx-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.