rebuild-daily fails

[Graham Eddy]

rebuilding daily summary on weewx database is not working, hopefully someone can help

in the leadup to this,

* i copied producton weewx.sdb to dev/test (different host) weewx-gw1000.sdb,

* in which i dropped lots of columns and renamed others (using wee_database —drop-columns and —rename-column respectively)

* then i dropped daily summaries (using wee_database —drop-daily).

* when i try to rebuild daily summaries (using wee_database —rebuild-daily), it reports a failure that a daily table already (still) exists - this table corresponds to a column that i did *not* alter

before doing —drop-daily i also deleted the content of a number of columns (obviously not the dropped ones) using sql clause of form ‘update table set altimeter = NULL, heatindex = NULL;’, preparatory to doing a —calc-missing after some new values were to be loaded into core columns such as outTemp and outHumidity

(presumably i can do an sql dump of archive table and reload to new db instance and go forward with that, but i should work through this in case there is an underlying weewx problem)

weewx-gw1000.conf points to weewx-gw1000.sdb

i am running another instance of weewx on the test system, from weewx-iwx.conf, which points to weewx-iwx.sdb, obviously doing other things

details of commands and log follow

————————————————————————————

weewx@stringybark:~ $ wee_database --config=weewx-gw1000.conf --drop-daily

Using configuration file weewx-gw1000.conf

Using database binding 'wx_binding', which is bound to database 'archive_sqlite'

Proceeding will delete all your daily summaries from database 'weewx-gw1000.sdb'

Are you sure you want to proceed (y/n)? y

Dropping daily summary tables from 'weewx-gw1000.sdb' ... 

No daily summaries found in database 'weewx-gw1000.sdb'. Nothing done.

weewx@stringybark:~ $ wee_database --config=weewx-gw1000.conf --rebuild-daily

Using configuration file weewx-gw1000.conf

Using database binding 'wx_binding', which is bound to database 'archive_sqlite'

All daily summaries will be rebuilt.

Proceed (y/n)? y

Traceback (most recent call last):

  File "/home/weewx/bin/weedb/sqlite.py", line 30, in guarded_fn

    return fn(*args, **kwargs)

  File "/home/weewx/bin/weedb/sqlite.py", line 219, in execute

    return sqlite3.Cursor.execute(self, *args, **kwargs)

sqlite3.OperationalError: table archive_day_lightning_strike_count already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/home/weewx/bin/wee_database", line 1138, in <module>

    main()

  File "/home/weewx/bin/wee_database", line 236, in main

    rebuildDaily(config_dict, db_binding, options)

  File "/home/weewx/bin/wee_database", line 336, in rebuildDaily

    db_binding, initialize=True) as dbmanager:

  File "/home/weewx/bin/weewx/manager.py", line 746, in open_manager_with_config

    return open_manager(manager_dict, initialize)

  File "/home/weewx/bin/weewx/manager.py", line 734, in open_manager

    manager_dict['schema'])

  File "/home/weewx/bin/weewx/manager.py", line 164, in open_with_create

    dbmanager = cls(connection, table_name=table_name, schema=schema)

  File "/home/weewx/bin/weewx/manager.py", line 876, in __init__

    self._initialize_day_tables(schema)

  File "/home/weewx/bin/weewx/manager.py", line 932, in _initialize_day_tables

    self._initialize_day_table(obs[0], obs[1].lower(), cursor)

  File "/home/weewx/bin/weewx/manager.py", line 953, in _initialize_day_table

    cursor.execute(sql_create_str)

  File "/home/weewx/bin/weedb/sqlite.py", line 40, in guarded_fn

    raise weedb.TableExistsError(e)

weedb.TableExistsError: table archive_day_lightning_strike_count already exists

weewx@stringybark:~ $ sqlite3 archive/weewx-gw1000.sdb 

SQLite version 3.27.2 2019-02-25 16:06:06

Enter ".help" for usage hints.

sqlite> drop table archive_day_lightning_strike_count;

Error: no such table: archive_day_lightning_strike_count

sqlite> .tables

archive

sqlite> select * from sqlite_master;

table|archive|archive|2|CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `altimeter` REAL, `barometer` REAL, `cloudbase` REAL, `dewpoint` REAL, `ET` REAL, `heatindex` REAL, `lightning_distance` REAL, `lightning_strike_count` REAL, `lightning_last_det_time` REAL, `luminosity` REAL, `pm2_5` REAL, `radiation` REAL, `soilMoist1` REAL, `soilMoist2` REAL, `UV` REAL, `windchill` REAL, `inTemp` REAL, `outTemp` REAL, `inHumidity` REAL, `outHumidity` REAL, `pressure` REAL, `windDir` REAL, `windSpeed` REAL, `windGust` REAL, `windGustDir` REAL, `rain` REAL, `stormRain` REAL, `rainRate` REAL, `pm2_52` REAL, `wh40_batt` REAL, `wh41_ch1_batt` REAL, `wh41_ch2_batt` REAL, `wh51_ch1_batt` REAL, `wh51_ch2_batt` REAL, `wh57_batt` REAL, `ws80_batt` REAL, `wh40_sig` REAL, `wh41_ch1_sig` REAL, `wh41_ch2_sig` REAL, `wh51_ch1_sig` REAL, `wh51_ch2_sig` REAL, `wh57_sig` REAL, `ws80_sig` REAL)

index|sqlite_autoindex_archive_1|archive|4110|

sqlite> .exit

weewx@stringybark:~ $ 

————————————————————————————

May  2 00:29:21 stringybark wee_database[2190] INFO __main__: All daily summaries will be rebuilt.

May  2 00:31:21 stringybark wee_database[2373] INFO __main__: All daily summaries will be rebuilt.

May  2 00:35:39 stringybark wee_database[2677] INFO __main__: All daily summaries will be rebuilt.

[vince]

On Saturday, May 1, 2021 at 8:24:12 AM UTC-7 graha...@gmail.com wrote:

* i copied producton weewx.sdb to dev/test (different host) weewx-gw1000.sdb,

* in which i dropped lots of columns and renamed others (using wee_database —drop-columns and —rename-column respectively)

* then i dropped daily summaries (using wee_database —drop-daily).

* when i try to rebuild daily summaries (using wee_database —rebuild-daily), it reports a failure that a daily table already (still) exists - this table corresponds to a column that i did *not* alter

before doing —drop-daily i also deleted the content of a number of columns (obviously not the dropped ones) using sql clause of form ‘update table set altimeter = NULL, heatindex = NULL;’, preparatory to doing a —calc-missing after some new values were to be loaded into core columns such as outTemp and outHumidity

You are changing so much (why ?) in a seemingly random order that I'd suggest starting over.

• take your working database and make a copy
• drop daily on the copy
• rebuild daily on the copy
• it should work

If you just want to start over completely, dump your archive table only and restore it to a new db name.  Then run rebuild-daily against that.  That should definitely work unless you somehow messed up the schema in all your fiddling around.

Make one change at a time.

 

[Graham Eddy]

> You are changing so much

i am trying a different split of data between two drivers (one database into two), where both have data in columns in one database

> (why ?)

you don’t jump a gap in two hops

> in a seemingly random order

the sequence is logical to me: make a copy for second driver, drop the columns in it no longer required (belong to other driver), rename some of the remaining (eg altOutTemp to outTemp), recalc derived values from the new core columns (eg. dewpoint)

> If you just want to start over completely,

i indicated i can do so to progress, but i want to flag a potential problem in weewx viz. i drop-daily, demonstrate the summary tables do not exist, attempt rebuild-daily but it claims a summary table exists

this is using the —drop-tables and —rename-tables options new to 4.5. hopefully someone else has pushed them hard too and can compare results

[Tom Keffer]

It's a new feature, so there may well be a problem. Unfortunately (or, maybe fortunately!) I am unable to replicate it. I dropped a couple columns, renamed a column, then dropped the daily summary. I then rebuilt it uneventfully. 

If you can walk me through an example, maybe I can replicate your problem.

--
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/76559C51-63DC-470E-9D9B-694817393CF2%40gmail.com.

[Graham Eddy]

i don’t know how to reproduce it (so many changes, as vince pointed out). i was hoping someone had seen it before: maybe a default value going astray

it is worth a first-pass inspection, but no more than that if i can’t reproduce it or not seen elsewhere. summary of problem statement would be “—drop-daily succeeds on db but subsequent —rebuild-daily fails and reports a summary table already exists, but sqlite3 shows that table does not exist"

my weewx-gw1000.conf contains

[[wx_binding]]

schema = user.extensions.gw1000_schema

[[archive_sqlite]]

database_name = weewx-gw1000.sdb

obviously both schema and database have been changed → could contain errors. could an inconsistency between the two cause the problem?

and how could the second half of problem statement (—rebuild-daily fails and reports a summary table already exists, but sqlite3 shows that table does not exist) come about? going to the wrong database, or simply that error message itself is incorrect, spring to mind

[Graham Eddy]

found it: duplicate entry in user.extensions.gw1000_schema

i was under the impression that that schema was used only when the database is created, but that is incorrect: the schema is also used in —rebuild-daily

[Graham Eddy]

and the table ‘was not there’ because it was created within a transaction that was rolled back because of the error