Unable to change database server - Got critical error

[meteo-melin]

Hello,

I am currently running weewx 4.8 on a raspberry pi with a remote mysql database server.

It is running smoothly except I need to change my database server and I can't do it.

Here is what I did:

- Stopped weewx

- Dumped all mysql database from server using mysqldump

- Cloned users settings from old database server to the new server

- Imported all data and structure from the dump I made previously

- Changed server address in the weewx.conf (all other settings keept uncahnged).

- Tried to start weewx and got:

Oct 11 15:17:45 meteo.botrange systemd[1]: Starting LSB: weewx weather system...
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: Initializing weewx version 4.8.0
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: Using Python 3.7.3 (default, Jul 25 2020, 13:03:44)
                                              [GCC 8.3.0]
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: Platform Linux-5.10.17+-armv6l-with-debian-10.8
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: Locale is 'en_GB.UTF-8'
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: Using configuration file /home/weewx/weewx.conf
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: Debug is 0
Oct 11 15:17:48 meteo.botrange weewxd[24921]: weewx[24921] INFO __main__: PID file is /var/run/weewx.pid
Oct 11 15:17:48 meteo.botrange weewxd[24925]: weewx[24925] INFO weewx.engine: Loading station type Vantage (weewx.drivers.vantage)
Oct 11 15:17:48 meteo.botrange weewx[24911]: Starting weewx weather system: weewx.
Oct 11 15:17:48 meteo.botrange systemd[1]: Started LSB: weewx weather system.
Oct 11 15:17:49 meteo.botrange weewxd[24925]: weewx[24925] INFO weewx.engine: StdConvert target unit is 0x10
Oct 11 15:17:49 meteo.botrange weewxd[24925]: weewx[24925] INFO weewx.wxservices: StdWXCalculate will use data binding wx_binding
Oct 11 15:17:49 meteo.botrange sudo[24899]: pam_unix(sudo:session): session closed for user root
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__: Caught unrecoverable exception:
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****  [<class 'decimal.ConversionSyntax'>]
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****  Traceback (most recent call last):
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewxd", line 147, in main
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      engine = weewx.engine.StdEngine(config_dict)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/engine.py", line 93, in __init__
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      self.loadServices(config_dict)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/engine.py", line 161, in loadServices
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      obj = weeutil.weeutil.get_object(svc)(self, config_dict)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/wxservices.py", line 105, in __init__
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      initialize=True)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/manager.py", line 597, in get_manager
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      self.manager_cache[data_binding] = open_manager(manager_dict, initialize)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/manager.py", line 747, in open_manager
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      manager_dict['schema'])
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/manager.py", line 164, in open_with_create
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      dbmanager = cls(connection, table_name=table_name, schema=schema)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/manager.py", line 884, in __init__
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      super(DaySummaryManager, self).__init__(connection, table_name, schema)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weewx/manager.py", line 83, in __init__
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      self.sqlkeys = self.connection.columnsOf(self.table_name)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weedb/mysql.py", line 54, in guarded_fn
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      return fn(*args, **kwargs)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weedb/mysql.py", line 210, in columnsOf
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      column_list = [row[1] for row in self.genSchemaOf(table)]
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weedb/mysql.py", line 210, in <listcomp>
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      column_list = [row[1] for row in self.genSchemaOf(table)]
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/home/weewx/bin/weedb/mysql.py", line 183, in genSchemaOf
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      cursor.execute("""SHOW COLUMNS IN %s;""" % table)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 250, in execute
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      self.errorhandler(self, exc, value)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      raise errorvalue
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 247, in execute
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      res = self._query(query)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 412, in _query
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      self._post_get_result()
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 416, in _post_get_result
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      self._rows = self._fetch_row(0)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 384, in _fetch_row
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****      return self._result.fetch_row(size, self._fetch_type)
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****  decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>]
Oct 11 15:17:50 meteo.botrange weewxd[24925]: weewx[24925] CRITICAL __main__:     ****  Exiting.

When I roll back to the other server (change server address) all works good.

How should I fix those errors?

Old sql server:  10.3.39-MariaDB-0+deb10u2 - Debian 10

New sql server:  11.8.3-MariaDB-0+deb13u1 from Debia

Thanks

[Tom Keffer]

Most likely some change in MariaDB V11.8 vs 10.3.

Using the command line interface, what do you get when you run the query

show columns in weewx.archive;

?

--
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 visit https://groups.google.com/d/msgid/weewx-user/1af74556-c95d-4c53-b912-76277ec21877n%40googlegroups.com.

[Cameron D]

I recently upgrade two systems from Deb 12 to 13, one of them has two weewx MariaDB DBs. While I did have various other things that needed fixing, the DBs themselves were fine.

In my case the DB upgrade was in-place, but I cannot think of a situation where a dump/restore would fail.

Are you using the default schema?