Merge database failed

[Meteo Oberwallis]

Hello, everyone.
I wanted to merge my old weewx.sbd (Version 3.9.2) with the new version of weewx 4.8.0. I did this via:
Dump the db1.sdb database to a file:

$sqlite3 /path/to/db1.sdb
sqlite> .output /var/tmp/db1.dump
sqlite> .dump
sqlite> .quit

Now import the dumped data into the final database weewx.sdb:

$ sqlite3 /home/weewx/archive/weewx.sdb
sqlite> .read /var/tmp/db1.dump
sqlite> .quit

now I get the following error:

Error: near line 333827: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333828: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333829: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333830: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333831: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333832: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333833: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333834: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333835: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333836: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333837: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333838: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333839: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333840: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333841: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333842: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333843: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333844: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333845: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333846: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333847: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333848: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333849: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333850: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333851: table archive has 114 columns but 53 values ​​were supplied
Error: near line 333852: table archive has 114 columns but 53 values ​​were supplied

Why could that be. The database was previously with
[data bindings]
   
    [[wx_binding]]
        # The database must match one of the sections in [Databases].
        # This is likely to be the only option you would want to change.
        database = archive_sqlite
        # The name of the table within the database
        table_name = archive
        # The manager handles aggregation of data for historical summaries
        manager = weewx.wxmanager.WXDaySummaryManager
        # The schema defines the structure of the database.
        # It is *only* used when the database is created.
        #schema = schemas.wview.schema
        schema = user.radiationhours.schema_with_sunshine_hours

thank you for your help

[vince]

v4 shipped with the wview_extended schema as the default, which has 114 fields.   You need to convert your old db to the extended schema, then redo your dump and import commands (which look good).

See if https://github.com/weewx/weewx/wiki/Switching-to-the-new-wview_extended-schema helps any.

The important step is the "wee_database weewx.conf --reconfigure" command in the wiki page.  You can likely just run that command versus a 'copy' of your v3 database if you run it on your v4 system which already has a weewx.conf file set to the newer/bigger schema.

Always work off a copy of your databases just in case.  The procedure can take a while on a slow system like a pi if you have many years of data, as can the --rebuild-daily you need to do at the end after you merge the two archive tables.

[Schnidrig Stefan]

Hello Vince Many thanks for your response. I moved my old weewx.sdb to the new weewx version and then created it in the new format with "wee_database weewx.conf --reconfigure". Unfortunately, the "sunshine_hours" was deleted in the process. Can I somehow transfer these to the new scheme? Greeting

[vince]

I have no idea where sunshine_hours comes from.  Did you add it to your old weewx schema ?   If so you'd need to similarly add it to the new schema probably before doing your conversion and see if wee_database is smart enough to do the right thing.

(untested - but give it a try.  This is why you always work off copies of your data..).

[Meteo Oberwallis]

The old schema was "schema=user.radiationhours.schema_with_sunshine_hours". If I load my old weewx.sdb and then configure it with "wee_database weewx.conf --reconfigure" then this deletes the "sunshine_hours" column. All other data will be accepted. I'll try your instructions to see if it works.
Thanks very much

[Burns Fisher]

I've been a WeeWX user since late 2013.  At first I ported it to a BeagleBone Black but  I moved it to a Raspberry Pi somewhere around late 2018.  The point of all this is that I have been running it for years--last version was 3.8.0, I think, but just this weekend I actually upgraded the whole Pi to the latest Raspbian as well as WeeWX 4.8.  I appreciate that WeeWX can upgrade the database files automagically, but just like the original poster here, I am stuck with an old database that I'd like to import. 

The Raspberry Pi folks have a tendency to say "Yes, you can upgrade Raspbian, but it is much easier to just install the new version fresh" (and this is my experience in other cases too!).  This precludes upgrading WeeWX and thus the database does not automatically get upgraded.  I found this post after I had already installed the new system and it had been running for a day or so (without any old data).  It turns out that the "reconfigure" above was just what I needed.  I stopped WeeWX, made a copy of the day-old database and replaced it with the old one.  Then ran re-configure and rebuilt the daily summaries (must use "sudo"!)  and started up WeeWX.  Interestingly, WeeWX must have had to rebuild some more because it was not till I looked this morning that a whole year's worth of data was showing under "Year".  But the other interesting thing is that it apparently re-downloaded data from my Vantage's buffer so it seems to have all the data from the past couple days and I don't have to merge in the "new" database.

Anyway, besides giving my experience, I'd like to suggest that idea that an upgrade on Raspberry Pi might often consist of a complete new system be included in the documentation.  Then also mention 'reconfigure'.  The only hard part about this was finding the information!  Thanks to everyone for it being here at all!

Thanks for listening...

Burns

[Tom Keffer]

My apologies, but I'm just not following this.

There is no "upgrading" of the database. They are all the same. You can take a database from wview 15 years ago, and run it on a modern WeeWX with no changes.

Now it's true that the default schema for WeeWX changed in V4.0 from the "wview" schema to the "wview-extended" schema, but old schemas will run without change.   Simply move the old database into place --- no need to "import" it.

Perhaps I'm missing some subtlety here, but I keep seeing this. 

There is no need to "upgrade" your database! Just use it.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/89f57f3b-6aff-4de6-a55c-4df3bd9f5d38n%40googlegroups.com.

[Meteo Oberwallis]

Hello, everyone

Maybe I can help the whole thing along a bit. I installed the Weewx system on a Raspberry and also installed the Davis fine dust sensor extension. After that I made a backup of the whole system so I can use it for my other weather stations. Since the old database (weewx.sdb) now lacks the fields of qm2.5 etc, I reconfigured the database with the new weewx schema. After that I was missing all "sunshine_hours" entries. I use this "https://github.com/Jterrettaz/sunduration" to track the hours of sunshine. But for this to work, I had to set the schema to "schema = user.sunduration.schema_with_sunshine_time". But if you do this with the latest weewx version, the particulate matter sensor no longer works. That means the columns in weewx.sdb are missing. I have had hours of sunshine since 2017, but unfortunately they are missing now. I had to reconfigure the scheme again after "wview-extended". I'm probably making a misconception. Tom is right. I was able to copy and start the old weewx.sdb without any problems. However, the particulate matter sensor then stopped working.

Greeting
Stefan

[Tom Keffer]

If you want to add something to the database, you have two choices:

1. Create a new database using a custom schema, then transfer over the contents of the old database to the new database using the tool wee_database with the --transfer option.

2. Simply add a new column to the old database by using the tool wee_database with the --add-column option. For example

wee_database --add-column=pm2_5

would add a column for your air quality sensor.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/8563748b-eaf9-43c0-9b61-dc52fe71fca1n%40googlegroups.com.

[Meteo Oberwallis]

I have a question.

Is it possible to use the solar data to create the hours of sunshine retrospectively with weewx?