Trying to write to mariadb

[Adam Morgan]

Hello, and thank you so much to all of you that work on the software and participate in this forum.   I've been using weewx for a while now, and I hope to be able to contribute to the community going forward.

I am trying to write to a mariadb instance on my network.  I have several other self-hosted apps on my network that write to the database, and there are absolutely no network / port restrictions on the db.   

Maybe its just me, but the instructions are a little murky.   I am not sure that I have the config right.   I can connect to the db with the user and password, so I don't think that is the issue.  

Here are some snippets from my config.  

[Databases]
   
    # A SQLite database is simply a single file.
    [[archive_sqlite]]
        database_name = weewx.sdb
        database_type = SQLite
   
    # MySQL
    [[archive_mysql]]
        database_name = weewx
        database_type = MySQL

   [[MySQL]]
        driver = weedb.mysql
        # The host where the database is located.
        host = 192.168.1.100
        port = 3306
        # The user name for logging in to the host.
        user = weewx
        # Use quotes around the password to guard against parsing errors.
        password = "weewx"


Note:  I tried appending the port to the end of "host" - same error.

here is the error:

admin@WeatherPi:~ $ sudo journalctl -u weewx -f
Jan 08 08:47:00 WeatherPi weewxd[19901]: weewx.UnknownDatabase: Unknown database ''weewx''
Jan 08 08:47:00 WeatherPi weewxd[19901]: CRITICAL __main__:     ****      manager_dict['database_dict'] = get_database_dict_from_config(config_dict, database)
Jan 08 08:47:00 WeatherPi weewxd[19901]: CRITICAL __main__:     **** 

    

[Adam Morgan]

I left out this part of the config:

    [[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 = weewx
        # The name of the table within the database.
        table_name = archive
        # The manager handles aggregation of data for historical summaries.
        manager = weewx.manager.DaySummaryManager
        # The schema defines the structure of the database.
        # It is *only* used when the database is created.
        schema = schemas.wview_extended.schema

[Tom Keffer]

I'm assuming that the section [[MySQL]], which you show as being under [Databases], is actually under [DatabaseTypes].

If that's the case, then the problem is that under [[wx_binding]], the option "database" should be set to "archive_mysql", not "weewx".

But, if [[MySQL]] is in fact under [Databases], then that has to be corrected. If you get confused, look at the original weewx.conf. The comments tell you what to do.

--
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/d5298881-213a-4f70-86d5-09b098677cc5n%40googlegroups.com.

[Adam Morgan]

Thanks Tom!  This is all the part that I find a little confusing - how the sections tie together.  I did see the note about the db name needing to be consistent across sections, but I am unclear if I am missing something beyond that.  

I have stripped out the sections that don't apply to this task.  I have commented out the lines that were causing the program to fail.  

##############################################################################

#   This section binds a data store to a database.

[DataBindings]

   
    [[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 = weewx

        # The name of the table within the database.

        #table_name = archive

        # The manager handles aggregation of data for historical summaries.

        #manager = weewx.manager.DaySummaryManager

        # The schema defines the structure of the database.
        # It is *only* used when the database is created.

        #schema = schemas.wview_extended.schema

##############################################################################

#   This section defines various databases.

[Databases]
   
    # A SQLite database is simply a single file.
    [[archive_sqlite]]
        database_name = weewx.sdb
        database_type = SQLite
   
    # MySQL
    [[archive_mysql]]
        database_name = weewx
        database_type = MySQL

##############################################################################

#   This section defines defaults for the different types of databases.

[DatabaseTypes]
   
    # Defaults for SQLite databases.
    [[SQLite]]
        driver = weedb.sqlite
        # Directory in which database files are located, relative to WEEWX_ROOT
        SQLITE_ROOT = /var/lib/weewx
   
    # Defaults for MySQL databases.
    [[MySQL]]
        #driver = weedb.mysql

        # The host where the database is located.

        #host = 192.168.1.100
        #port = 3306

        # The user name for logging in to the host.

        #user = weewx

        # Use quotes around the password to guard against parsing errors.

        #password = "weewx"

##############################################################################

[Tom Keffer]

Only a few sections need to be changed from the standard weewx.conf file. I've highlighted them below. Everything else should be the same.

#   This section binds a data store to a database.

[DataBindings]
   
    [[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_mysql

        # The name of the table within the database.
        table_name = archive
        # The manager handles aggregation of data for historical summaries.
        manager = weewx.manager.DaySummaryManager
        # The schema defines the structure of the database.
        # It is *only* used when the database is created.
        schema = schemas.wview_extended.schema

##############################################################################

#   This section defines various databases.

[Databases]
   
    # A SQLite database is simply a single file.
    [[archive_sqlite]]
        database_name = weewx.sdb
        database_type = SQLite
   
    # MySQL
    [[archive_mysql]]
        database_name = weewx
        database_type = MySQL

##############################################################################

#   This section defines defaults for the different types of databases.

[DatabaseTypes]
   
    # Defaults for SQLite databases.
    [[SQLite]]
        driver = weedb.sqlite
        # Directory in which database files are located, relative to WEEWX_ROOT

        SQLITE_ROOT = archive

   
    # Defaults for MySQL databases.
    [[MySQL]]

        driver = weedb.mysql
        # The host where the database is located.
        host = 192.168.1.100

        port = 3306

        # The user name for logging in to the host.
        user = weewx
        # Use quotes around the password to guard against parsing errors.
        password = weewx

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/c22faed6-2b1b-49cb-ac31-cbfb13e09476n%40googlegroups.com.

[Adam Morgan]

Thanks again!  Just to be clear, I should make the exact changes that you made?   

Also, can you explain something to me?  The word "archive" is used, and I did see a reference to an archive interval.  I am assuming that the data gets dumped to the SQL db on that schedule?

[vince]

The first one is a 'yes'.

For the second one, look for 'archive_interval' in weewx.conf

[StdArchive]
    # If the station hardware supports data logging then the archive interval
    # will be downloaded from the station. Otherwise, specify it (in seconds).
    archive_interval = 300

[Adam Morgan]

Argh, i don't know if my last update was sent.  I don't see it in the thread.

I am now getting this error:

admin@WeatherPi:~ $ sudo journalctl -u weewx -f

Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****    File "<frozen importlib._bootstrap>", line 690, in _load_unlocked
Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****    File "<frozen importlib._bootstrap_external>", line 940, in exec_module
Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****    File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****    File "/usr/share/weewx/weedb/mysql.py", line 14, in <module>
Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****      import pymysql as MySQLdb
Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****  ModuleNotFoundError: No module named 'pymysql'
Jan 08 14:58:53 WeatherPi weewxd[29087]: CRITICAL __main__:     ****  Exiting.

I went to install the package into the python temp environment, but it was already installed:  

(weewx-venv) admin@WeatherPi:~ $ pip install pymysql
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Requirement already satisfied: pymysql in ./weewx-venv/lib/python3.11/site-packages (1.1.1)

[Tom Keffer]

You're not showing much of the log, but I can see one inconsistency: the log snippet indicates that you did a package install (clue: the path /usr/share/weewx/weedb/mysql.py). But, you installed pymysql into a Python virtual environment (clue: the "weewx-venv" prompt). The latter will not be seen by the system's Python used by the daemon.

If you did indeed do a package install, make sure you follow the instructions in Installing the client libraries for MySQL.

If you did not do a package install and, instead, did a pip install, then something is truly screwed up. Let us know.

Incidentally, unless you really need MySQL, I think you'll find it much easier going to simply use SQLite. 

To view this discussion visit https://groups.google.com/d/msgid/weewx-user/6f5d78df-f1b4-4835-a9f0-546d02071895n%40googlegroups.com.