sqlite database size limit on rPi

[Ron Knapp]

I have weewx running on a rPi. It has started corrupting the database and crashing the rPi. It appears maybe the size makes

it get in deadlock because transactions can't process fast enough. There are many different errors all having to do with

database corruption. Has anyone seen this before? I have about 8 years of data and the archive table has about 800k rows.

any suggestions? 

           I made a copy of the db, then I deleted the working copy and started weewx with any empty db, it

has been working fine since. Can I insert say 2-3 years of records into the archive table and then start weewx and will it

build all the stats for those years? The problem occurred with version 2.7x and same results with 3.x current version. I

always fix the corrupted db, start weewx and it runs fine for a few hours then crashes. I would like to have all the data

but understand I may need to limit size if want to run it on rPi.

Ron

[vince]

weewx will rebuild the stats tables from the archive table when it starts up, if those are missing.

I'm not so sure there is a size limitation thing going on, more like you have an overloaded Pi that you need to do the normal speedups on (minimize skins, don't have a crazy fast archive period, turn turbo on, do whatever you can to minimize SD writes, get a good class-10 SD card, etc.)

That said, 'converting' 800k weewx 2.7 records to weewx-3.x will definitely take a long time and might even run out of memory.   I converted my similarly sized sqlite3 db offline from the command line on a much faster system, then just copied it to my weewx computer (a Seagate Dockstar with a spinning disk, so basically an old pogoplug - but it is faster than a pi model-B for certain).

[Ron Knapp]

Thanks Vince, I tried inserting the records into a clean archive table, but weewx just hangs after starting trying to convert all those records. You say there

is a way to do it command line? Is that using weewx on a faster computer or an sql script? I even tried just inserting the archive records for this year and it couldn't handle that. It

has been running fine for a few years on this pi, I guess I just crossed a threshold in size that that pi can't handle.

Ron

[Chris Thompstone]

is it actually hung or just sitting there working through the daily summaries, which could take hours and hours on a database that big.

Other option is to upgrade to one of those new odroids with 2gb ram emmc memory and a very fast processor.

[vince]

On Tuesday, October 6, 2015 at 10:12:51 AM UTC-7, Ron Knapp wrote:

Thanks Vince, I tried inserting the records into a clean archive table, but weewx just hangs after starting trying to convert all those records. You say there

is a way to do it command line? Is that using weewx on a faster computer or an sql script? I even tried just inserting the archive records for this year and it couldn't handle that. It

has been running fine for a few years on this pi, I guess I just crossed a threshold in size that that pi can't handle.

Weewx probably isn't hung, it's probably converting everything, or generating stats, or generating NOAA files.   Running in debug=1 mode usually helps.
If it's sqlite3, you can do a database dump to a text file, append your lines above the last line where it does the commit, and do a database restore.

Can't speak to the procedure for mysql, sorry.  I stuck with sqlite3 because it was as minimal/fast/light/simple as I needed.

The procedure for how convert v2 to v3 is in the docs or in the google group if you search.   (sorry, can't look it up from here currently)

[Ron Knapp]

Vince, I will search for the conversion, wasn't sure what I was looking for thanks.

Version 2.x db errors, fix db and restart and works fine for a while then another db corruption

****    File "/opt/weewx/bin/weewx/imagegenerator.py", line 230, in _getArchiveInterval

Oct  5 06:30:24 info weewx[23465]:         ****      _row = archive.getSql("SELECT MIN(`interval`) FROM %s" % archive.table)

Oct  5 06:30:24 info weewx[23465]:         ****    File "/opt/weewx/bin/weewx/archive.py", line 286, in getSql

Oct  5 06:30:24 info weewx[23465]:         ****      _cursor.execute(sql, sqlargs)

Oct  5 06:30:24 info weewx[23465]:         ****    File "/opt/weewx/bin/weedb/sqlite.py", line 145, in execute

Oct  5 06:30:24 info weewx[23465]:         ****      return sqlite3.Cursor.execute(self, *args, **kwargs)

Oct  5 06:30:24 info weewx[23465]:         ****  DatabaseError: database disk image is malformed

When I tried with good 2.x db to run 3.x this is all I get. I even turned on write ahead logging (wal) to

try and help the locking no improvement.

Oct  5 19:16:00 info weewx[3140]: engine: Initializing weewx version 3.2.1

Oct  5 19:16:00 info weewx[3140]: engine: Using Python 2.7.3 (default, Mar 18 2014, 05:13:23) #012[GCC 4.6.3]

Oct  5 19:16:00 info weewx[3140]: engine: Platform Linux-4.1.7-v7+-armv7l-with-debian-7.8

Oct  5 19:16:00 info weewx[3140]: engine: pid file is /var/run/weewx.pid

Oct  5 19:16:00 info weewx[3144]: engine: Using configuration file /etc/weewx/weewx.conf

Oct  5 19:16:00 info weewx[3144]: engine: Loading station type Vantage (weewx.drivers.vantage)

Oct  5 19:18:03 info weewx[3342]: engine: Initializing weewx version 3.2.1

Oct  5 19:18:03 info weewx[3342]: engine: Using Python 2.7.3 (default, Mar 18 2014, 05:13:23) #012[GCC 4.6.3]

Oct  5 19:18:03 info weewx[3342]: engine: Platform Linux-4.1.7-v7+-armv7l-with-debian-7.8

Oct  5 19:18:03 info weewx[3342]: engine: pid file is /var/run/weewx.pid

Oct  5 19:18:03 info weewx[3346]: engine: Using configuration file /etc/weewx/weewx.conf

Oct  5 19:18:03 info weewx[3346]: engine: Loading station type Vantage (weewx.drivers.vantage)

Oct  5 19:18:04 info weewx[3346]: engine: StdConvert target unit is 0x1

Oct  5 19:18:04 info weewx[3346]: engine: Archive will use data binding wx_binding

Oct  5 19:18:04 info weewx[3346]: engine: Record generation will be attempted in 'hardware'

Oct  5 19:18:04 info weewx[3346]: engine: Using archive interval of 300 seconds

Oct  5 19:18:04 info weewx[3346]: engine: Using binding 'wx_binding' to database 'weewx.sdb'

Oct  5 19:18:04 info weewx[3346]: engine: Starting backfill of daily summaries

Oct  5 19:18:04 info weewx[3346]: engine: Daily summaries up to date.

When I start ver 2.7 or version 3.x with empty db all works fine.

 I will do the outside weewx conversion and give 3.x a try again.

Ron

[Ron Knapp]

Ok I took an uncorrupted db to a more powerful machine, this is what I get and basically the same error on the pi when trying to run ver 3.x 

At least the dump was able to get logged here.

Oct  6 18:01:09 flux weewx[894]: engine: Starting backfill of daily summaries

Oct  6 18:01:09 flux weewx[894]: engine: Caught unrecoverable exception in engine:

Oct  6 18:01:09 flux weewx[894]:     ****  unsupported operand type(s) for +=: 'float' and 'unicode'

Oct  6 18:01:09 flux weewx[894]:     ****  Traceback (most recent call last):

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/engine.py", line 836, in main

Oct  6 18:01:09 flux weewx[894]:     ****      engine = EngineClass(config_dict)

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/engine.py", line 75, in __init__

Oct  6 18:01:09 flux weewx[894]:     ****      self.loadServices(config_dict)

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/engine.py", line 136, in loadServices

Oct  6 18:01:09 flux weewx[894]:     ****      self.service_obj.append(weeutil.weeutil._get_object(svc)(self, config_dict))

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/engine.py", line 504, in __init__

Oct  6 18:01:09 flux weewx[894]:     ****      self.setup_database(config_dict)

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/engine.py", line 608, in setup_database

Oct  6 18:01:09 flux weewx[894]:     ****      nrecs, ndays = dbmanager.backfill_day_summary()

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/manager.py", line 1347, in backfill_day_summary

Oct  6 18:01:09 flux weewx[894]:     ****      _day_accum.addRecord(_rec)

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/accum.py", line 240, in addRecord

Oct  6 18:01:09 flux weewx[894]:     ****      func(self, record, obs_type, add_hilo)

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/accum.py", line 310, in add_value

Oct  6 18:01:09 flux weewx[894]:     ****      self[obs_type].addSum(val)

Oct  6 18:01:09 flux weewx[894]:     ****    File "/usr/share/weewx/weewx/accum.py", line 87, in addSum

Oct  6 18:01:09 flux weewx[894]:     ****      self.sum     += val

Oct  6 18:01:09 flux weewx[894]:     ****  TypeError: unsupported operand type(s) for +=: 'float' and 'unicode'

Oct  6 18:01:09 flux weewx[894]:     ****  Exiting.

May try to track it down if find some time.

[gjr80]

Just wondering, is there any chance with the database manipulation you seem to have been doing that you may have ended up with a string in your database instead of a numeric? The 'Unicode' error made me wonder. Running the command line tool wee_database with the --string-check option will do the check and fix any issues. Sorry but a bit difficult to give the exact syntax/detailed instructions on this device, a bit of searching should find what you need though. Of course I could be completely wrong....

Gary

[Ron Knapp]

Gary you were dead on. I found that option and ran it, I had  75000 records where the wind gust direction was > 1000000, Not sure what it

really was, it was not null, it was not printable. The tool said it was u'' . I set all windgustdir value to NULL that were > 10000 and it passed the

string check. The dates were long ago, before weewx so not sure how they got in there.

Once I did that I ran the utility to backfill and this is where I am. Once it is complete I will see if it will run on the pi. Thanks for everyone help

and pointing me to the db tools. It really just took running it on a fast machine so the error could trap out instead of just crashing on the rPi.

Using configuration file /etc/weewx/weewx.conf

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

Backfilling daily summaries in database 'weewx.sdb'

Records processed: 94000; Last date: 2009-06-15 14:10:00 EDT (1245089400)

[Ron Knapp]

Ok backfill completed

Backfilled 'weewx.sdb' with 741510 records over 2914 days in 2538.25 seconds

Seems to be running correctly, It may be time to archive a few years out of the DB, might be reaching limit

that pi can handle.

Thanks again all,

Ron