> But why would the primary key specification have been dropped? I can't say for certain, but I think I can reconstruct what happened. I mentioned earlier that I migrated the database from the remote host where it lived to the system where I run weewx--I did a mysqldump there, then mysql weewx < weewx.sql on the other end. When the latter finished, it gave a "temporary file write failure." But since all the tables were there, and archive seemed to have the right number of rows, I figured it wasn't a problem. But I got the same error just now when running "ALTER TABLE archive ADD PRIMARY KEY (dateTime);" which led to some digging, determining that /tmp is a RAMdisk in Debian 13, adding RAM to the VM, rebooting, and rerunning that command--which completed successfully.
So it seems likely that the failure when I initially imported this database was in setting the primary key. And now the database calc-missing operation--which had stalled, not indicating any progress at all before setting the primary key--is running quite speedily, though it still (naturally) takes a while to get through 5M rows. Once that finished, restarted weewx, and unsurprisingly Seasons completed much faster--including generating all the NOAA data, about 8 minutes. Running Me.teo from scratch, 12 minutes. A second run, 10 seconds. So while I think there's still some testing I'll need to do, I think I'll call this solved. I don't think the primary key was the sole issue, because I was having performance problems when the database was on the remote host, and the key was present there. But it was definitely an issue once I moved the database to the weewx host, and apparently the lack of the appTemp column was the rest of it. On Sunday, June 28, 2026 at 2:56:09 PM UTC-4 Tom Keffer wrote: > Absolutely. It's critical that dateTime be set as the primary key. Under > MySQL the primary key is not only indexed, but it sets how data is > organized. Without it, any query would pretty much require scanning the > whole database. > > But why would the primary key specification have been dropped? Do you have > old backups to examine when it was dropped? > > -tk > > On Sun, Jun 28, 2026 at 11:44 AM [email protected] <[email protected]> > wrote: > >> Got it, and I'm in the middle of running the calc-missing step right >> now. But in reviewing the describe archive outputs from the old and the >> new database, I see that dateTime is set as a primary key in the new >> table and not in the old one. Am I wrong in thinking that's also likely to >> impact performance. >> >> On Sunday, June 28, 2026 at 2:42:09 PM UTC-4 Tom Keffer wrote: >> >>> There is no reason to migrate to the new schema. It is not an "upgrade." >>> It just offers more types. In your case, because you need only a single new >>> type (appTemp) it would be much more efficient to just add it using the >>> tool "weectl database add-column >>> <https://www.weewx.com/docs/5.4/utilities/weectl-database/#add-a-new-observation-type-to-the-database> >>> ": >>> >>> *weectl database add-column appTemp* >>> >>> >>> However, while that will add a new column, it will not populate it with >>> anything. To do that, use weectl database calc-missing >>> <https://www.weewx.com/docs/5.4/utilities/weectl-database/#calculate-missing-derived-variables> >>> : >>> >>> *weectl database calc-missing* >>> >>> >>> I would advise doing a backup first. >>> >>> -tk >>> >>> On Sun, Jun 28, 2026 at 11:25 AM [email protected] <[email protected]> >>> wrote: >>> >>>> Hmmm. My MySQL database is using the old schema, and it seems there's >>>> a good likelihood that's at least contributing to the performance problems >>>> I'm seeing. And while the link Tom gave describes adding a specific >>>> column >>>> to the table, >>>> https://weewx.com/docs/5.4/custom/database/#reconfigure-using-new-schema >>>> provides instructions that would seem to address migrating to the new >>>> schema completely. So I tried them. Stopped weewx, made a backup of the >>>> mysql database using mysqldump, and edited weewx.conf to revert to using >>>> the mysql database, and to use the wview_extended schema. Then ran weectl >>>> database reconfigure--which died after just under a minute: >>>> dan@wxbox ➜ ~ time sudo -u weewx weectl database reconfigure >>>> Using configuration file /etc/weewx/weewx.conf >>>> Copying database 'weewx' to 'weewx_new' >>>> The new database will use the same unit system as the old ('US'). >>>> Are you sure you wish to proceed (y/n)? y >>>> [2] 7144 killed sudo -u weewx weectl database reconfigure >>>> sudo -u weewx weectl database reconfigure 0.01s user 0.01s system 0% >>>> cpu 53.676 total >>>> >>>> The new database is created, the new archive table is created in it, >>>> and it appears to have all the necessary fields (describe archive returns >>>> 115 rows), but it's empty. The system log doesn't report any errors: >>>> 2026-06-28T14:16:26.783311-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Initializing weectl version 5.4.0 >>>> 2026-06-28T14:16:26.787432-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Command line: /usr/share/weewx/weectl.py database reconfigure >>>> 2026-06-28T14:16:26.788797-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Using Python: 3.13.5 (main, May 5 2026, 21:05:52) [GCC 14.2.0] >>>> 2026-06-28T14:16:26.788976-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Located at: /usr/bin/python3 >>>> 2026-06-28T14:16:26.789025-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Platform: Linux-6.12.94+deb13-amd64-x86_64-with-glibc2.41 >>>> 2026-06-28T14:16:26.789078-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Locale: 'en_US.UTF-8' >>>> 2026-06-28T14:16:26.789130-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Entry path: /usr/share/weewx/weectl.py >>>> 2026-06-28T14:16:26.789303-04:00 wxbox weectl[7147]: INFO weectllib: >>>> WEEWX_ROOT: /etc/weewx >>>> 2026-06-28T14:16:26.789712-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Config file: /etc/weewx/weewx.conf >>>> 2026-06-28T14:16:26.789764-04:00 wxbox weectl[7147]: INFO weectllib: >>>> User module: /etc/weewx/bin/user >>>> 2026-06-28T14:16:26.789957-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Debug: 1 >>>> 2026-06-28T14:16:26.790009-04:00 wxbox weectl[7147]: INFO weectllib: >>>> User: weewx >>>> 2026-06-28T14:16:26.790050-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Group: weewx >>>> 2026-06-28T14:16:26.790186-04:00 wxbox weectl[7147]: INFO weectllib: >>>> Groups: dialout weewx >>>> 2026-06-28T14:16:37.260384-04:00 wxbox weectl[7147]: INFO >>>> weewx.manager: Created and initialized table 'archive' in database >>>> 'weewx_new' >>>> >>>> Am I just misunderstanding what this tool does? If so, is there a way >>>> to migrate an existing database to a new schema? >>>> >>>> On Saturday, June 27, 2026 at 9:54:37 PM UTC-4 John Smith wrote: >>>> >>>>> I've never heard of a V5 problem that bad, but I suppose it's possible. >>>>> >>>>> >>>>> Considering they have a smaller number of columns, it could be a case >>>>> of taking a long time to calculate the missing ones. >>>>> >>>>> On Sun, 28 Jun 2026 at 06:53, Tom Keffer <[email protected]> wrote: >>>>> >>>>>> This is smelling like a V5 performance issue. See the Wiki article >>>>>> *Troubleshooting >>>>>> V5.x performance >>>>>> <https://github.com/weewx/weewx/wiki/v5-performance-troubleshooting>. >>>>>> *What >>>>>> gives me pause is that you said that sometimes the reports don't finish >>>>>> even after several days of running. I've never heard of a V5 problem >>>>>> that >>>>>> bad, but I suppose it's possible. >>>>>> >>>>>> Try putting appTemp in the database and see what happens. >>>>>> >>>>>> -tk >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Sat, Jun 27, 2026 at 1:41 PM [email protected] <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> >>>>>>> > Struggles to create the charts ? Lets see some logs showing that. >>>>>>> You did not post any debug=1 logs. >>>>>>> >>>>>>> I did, in fact, 400+ lines' worth. Nothing that I saw there about >>>>>>> image generation, but it's certainly possible I overlooked it. I >>>>>>> inferred >>>>>>> the "struggle" from the fact that, at that time, index.html had updated >>>>>>> but >>>>>>> none of the charts had. Over time (30+ minutes), the charts gradually >>>>>>> updated. >>>>>>> >>>>>>> > The only thing I can think of along those lines is weewx being >>>>>>> slow calculating things you refer to in your skin that are not elements >>>>>>> the >>>>>>> db but that shouldn’t happen with a clean v5 installation tunning >>>>>>> unaltered >>>>>>> Seasons >>>>>>> >>>>>>> If it's still using the existing database, which it was, that seems >>>>>>> plausible. Rich also mentioned something similar. >>>>>>> >>>>>>> > I’m thinking you have the old wview not wview-extended db perhaps. >>>>>>> You should have around 113 elements in the archive table not the small >>>>>>> old >>>>>>> schema with 52 or so. >>>>>>> >>>>>>> I think you're right. `describe archive` lists 53 rows. Looks like >>>>>>> some reading at >>>>>>> https://weewx.com/docs/5.1/custom/database/#modify-the-schema-of-an-existing-database >>>>>>> >>>>>>> is in order. >>>>>>> >>>>>>> On Saturday, June 27, 2026 at 4:31:07 PM UTC-4 Vince Skahan wrote: >>>>>>> >>>>>>>> Struggles to create the charts ? Lets see some logs showing that. >>>>>>>> You did not post any debug=1 logs. >>>>>>>> >>>>>>>> The only thing I can think of along those lines is weewx being slow >>>>>>>> calculating things you refer to in your skin that are not elements the >>>>>>>> db >>>>>>>> but that shouldn’t happen with a clean v5 installation tunning >>>>>>>> unaltered >>>>>>>> Seasons. >>>>>>>> >>>>>>>> On Saturday, June 27, 2026 at 12:42:32 PM UTC-7 Tom Keffer wrote: >>>>>>>> >>>>>>>>> On Sat, Jun 27, 2026 at 12:03 PM [email protected] < >>>>>>>>> [email protected]> wrote: >>>>>>>>> >>>>>>>>>> > MySQL server. You had a "Cannot connect" error on startup, so >>>>>>>>>> there is something flaky about the connection. >>>>>>>>>> >>>>>>>>>> It seems weewx starts up earlier than mysql, and thus fails to >>>>>>>>>> connect. It also seems to shut down earlier (on system shutdown or >>>>>>>>>> reboot) >>>>>>>>>> than mysql, leading to errors then. >>>>>>>>>> >>>>>>>>>> Enabling the general query log and following it doesn't show >>>>>>>>>> anything obvious. Nothing gets logged to the slow query log. >>>>>>>>>> >>>>>>>>> >>>>>>>>> It should show the types of queries running and how long they >>>>>>>>> take. Are you saying they were all quick? The time *must* be >>>>>>>>> going somewhere! >>>>>>>>> >>>>>>>>> Trying to rule out sources of a slowdown, I moved the VM storage >>>>>>>>>> to a local disk (it had been over the network), and again rebooted. >>>>>>>>>> And it >>>>>>>>>> now quickly generates the index.html page for the Seasons report, >>>>>>>>>> but it >>>>>>>>>> still seems to be struggling to create the -- >>>>>>>>>> >>>>>>>>> >>>>>>>>> Good idea. Try using sqlite and see if that's faster. >>>>>>>>> >>>>>>>>> -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 [email protected]. >>>>>>> To view this discussion visit >>>>>>> https://groups.google.com/d/msgid/weewx-user/367d2d60-042b-4ac0-a5e2-b836b7d0e6b0n%40googlegroups.com >>>>>>> >>>>>>> <https://groups.google.com/d/msgid/weewx-user/367d2d60-042b-4ac0-a5e2-b836b7d0e6b0n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> -- >>>>>> 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 [email protected]. >>>>>> >>>>> To view this discussion visit >>>>>> https://groups.google.com/d/msgid/weewx-user/CAPq0zEC1O3r3B_ckvhXr%2Ba%3Dm3YY1gKKxJQFrhs9DtLs6j6GmNw%40mail.gmail.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/weewx-user/CAPq0zEC1O3r3B_ckvhXr%2Ba%3Dm3YY1gKKxJQFrhs9DtLs6j6GmNw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- >>>> 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 [email protected]. >>>> >>> To view this discussion visit >>>> https://groups.google.com/d/msgid/weewx-user/f352da7b-efd3-4e9d-a98e-a01c5dd5c88bn%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/weewx-user/f352da7b-efd3-4e9d-a98e-a01c5dd5c88bn%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- >> 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 [email protected]. >> > To view this discussion visit >> https://groups.google.com/d/msgid/weewx-user/fad514fd-0b39-441d-9e6d-3bc962945cb0n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/weewx-user/fad514fd-0b39-441d-9e6d-3bc962945cb0n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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 [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/4af2d653-dc4e-4598-85dc-dd32fb97a1a0n%40googlegroups.com.
