Enhancement - Make option to storage data in remote MySQL

[jsiemek]

Storing data in remote MYSQL make many analytics possible.

[OllisGit]

Yes, that the long term goal! It is already in my backlog, see https://github.com/OllisGit/OctoPrint-PrintJobHistory/projects/1

I don't want to implement "1000 analytics-reports", instead the user should use there own tool-stack.

The current "poll" shows a different picture. Only 10% needs an external Database

https://github.com/OllisGit/OctoPrint-PrintJobHistory/issues/6

[rob4226]

An external database option would definitely be great as some people like myself have many different instances of OctoPrint that are often temporary in nature and so local data will be lost. I would love to have all my print history in one central location.

A possible example to use it the FilamentManager plugin which has to option to connect to a PostgreSQL database after installing "pip install psycopg2"

https://github.com/malnvenshorn/OctoPrint-FilamentManager

[taker218]

I just saw the question in your backlog regarding a unique ID. Seems like there is a python package "uuid" which could be used for that.

That's at least the method used in the filamentmanager plugin.

[OllisGit]

Well...I looked into the implementation as well, but IMHO this is not the best solution. Because it doesn't cover all of the following cases in a "single db, multi op-installations"-scenario:

• if you reinstall OP or clean/reinstall the history plugin, the "connection" of all printed jobs with this op-instance is gone. Because uuid-clientID is stored in the plugin-settings. --> solution could be to use the network-mac-address of the RasPi, but what if you upgrade your RasPi from V3 to V4...or do a backup of the clientId and after reinstall put the clientId back into the settings ;-)

• The plugin-version is bind to a specific database-scheme-version (table-structure). E.g. PJH V4 use DB-Scheme V2. If you install PJH V5 and the plugin needs DBS V3, it automatically upgrades the DBS to V3 (e.g. add a new column "roomTemperature"). So, if one OP-instance detects a DB change and upgrade the structure, the other OP-Instance with PJH V4 could not use this DB anymore --> I need a concept how to handle that, maybe Message-Box to User: "PJH disabled, you need to upgrade to PJH V5"

• What about a downtime of the external DB? If the database is not available after the print, should I store the printjob-data locally and after the connection is available I send it to the DB...or just drop the entry.

• What is the "best" database storage? Should I just support a postgres DB, or should I use some of the "fancy" cloud databases, because "most" of the User did not want to maintain/setup a database. --> Well I can just start with a postgres

So, I still want to implement this feature, but I need to do some kind of "external database-concept".

[taker218]

I get your concern regarding the uuid, I don't really know if there is a better way to do it. As far as I can see, the uuid from filament manager was stored in the config.yaml so if you move from one system to the other, it would be consistent.

Sure, if you reinstall everything from scratch, the connection between the old printjobs and the "new" instance is gone since the uuid will be different, but I don't know, if this would be a huge problem.

Regarding the db-version it would be good to have a well thought trough concept from the start. That way you could add changes to the database without breaking older versions (eg. from Version 2.0 to 2.1) Major changes could be done with a new major release (2.0 to 3.0).

I don't know how other people are doing it, but I try to have the same plugin version on my Octoprint instances, just to be sure that everything works and is up to date.

Downtime of the external database is really a problem. I see it with the FilamentManager plugin, when I reboot one of my PIs I need to restart OctoPrint at least once before the connection to the external database is established. I don't really know why, but it could be because OctoPrint starts before the network is ready and the plugin just checks the network connection once on startup, so there should be a way to have a local fallback for that.

Regarding database: I would start with postgres, it's free and super easy to install. And you could use it in an environment without internet access, which you couldn't do with a cloud database.

Just my oppinion on the matter, but hope it helps :)

[jsiemek]

Agree with @taker218.

Some additional thoughts:

Why not use hostname as identifier, it has to be unique in your network anyway. Alternatively maybe there is possibility to read printer servial numer from USB? IDK if all printers reports their S/N, but Prusa definatelly do:

T:  Bus=01 Lev=03 Prnt=03 Port=01 Cnt=02 Dev#=  5 Spd=12  MxCh= 0
D:  Ver= 2.00 Cls=02(commc) Sub=00 Prot=00 MxPS= 8 #Cfgs=  1
P:  Vendor=2c99 ProdID=0002 Rev=01.30
S:  Manufacturer=Prusa Research (prusa3d.com)
S:  Product=Original Prusa i3 MK3
S:  SerialNumber=CZPX2819X004XK35725
C:  #Ifs= 2 Cfg#= 1 Atr=c0 MxPwr=100mA
I:  If#= 0 Alt= 0 #EPs= 1 Cls=02(commc) Sub=02 Prot=01 Driver=cdc_acm
I:  If#= 1 Alt= 0 #EPs= 2 Cls=0a(data ) Sub=00 Prot=00 Driver=cdc_acm

as long you will not use constraints like "not null" adding additional column in newer version will not break anything. There will be simply no information for older version, which is obvious. Message with advise to upgrade is also great idea, with mention, that just some, newer information could be lost.

Regarding database unavailability, I don't know, how plugin is build, but maybe there is possibility to call reconnect procedure in case of connection failure. In case of constant failure you could put into log, failed commands, you know "insert into...." . Then print warning message and point to log. If user cares about, he can manually insert data into db.

I assume, thet is somebody want to user external db, he has at last minimal level of understanding.

[rob4226]

• What is the "best" database storage? Should I just support a postgres DB, or should I use some of the "fancy" cloud databases, because "most" of the User did not want to maintain/setup a database. --> Well I can just start with a postgres

So, I still want to implement this feature, but I need to do some kind of "external database-concept".

@OllisGit I've actually been working on a clone of your repository and got an external PostgreSQL database hooked up to it relatively easily after doing some research. I've never used PeeWee before but it has some support for MySQL and PostgreSQL database drivers. I even added an "External Database" tab in the setting's menu. My version is not completely polished yet but it has been working great so far! I intend to clean it up and post it up here. Edit: Here is the repo I am made: https://github.com/Rob4226/OctoPrint-PrintJobHistory-ExternalDatabase

Console screens from my printjobhistory PostgreSQL database on my internal network but on a different server than OctoPrint: Tables the plugin created:

config.yaml with added database settings:

accessControl:
    enabled: false
api:
    key: FF4782EA88CA49798B2FA931DA598765
devel:
    stylesheet: less
    webassets:
        bundle: true
plugins:
    PrintJobHistory:
        capturePrintJobHistoryMode: always
        externalDatabase:
            database_name: octoprint_print_history
            enabled: true
            host: 192.168.2.30
            password: octoprint_history
            port: '5432'
            username: octoprint_history
        pluginCheckActivated: false
        showPrintJobDialogAfterPrintMode: always
        takePrusaSlicerThumbnailAfterPrint: false
        takeSnapshotAfterPrint: false
        takeUltimakerThumbnailAfterPrint: false
...

[OllisGit]

Hi @Rob4226 look really good!

Do you have also ideas how to modify the upgrade-feature? The easiest solution is, just detecting the database and show the user a popup about what changed and then the user needs to "alter" the tables manually.

I also need to switch from file-based storage of the thumbnails to a database approach.

[tideline3d]

@Rob4226 Love what you've done, this will be huge for the 'print farm' type user. Would love to see this rolled into the main release. I'll give the fork a shot soon thought and share any feedback.

[OllisGit]

fyi: I am started implementing this feature.

Task-Status and addition informations how it is implemented will be found in my wiki.

[tideline3d]

Count me in for testing this, I run production prints on 3 printers and would love to have an accurate accounting of everything across the farm. @ me when you're ready for testers!

[stale[bot]]

This issue has been automatically marked for closing, because it has not had activity in 30 days. It will be closed if no further activity occurs in 10 days.

[Moskito99]

Nice feature! Hope we see it soon! ;-)

[nledenyi]

I for one also hope to see the resurgence of this initiative :) Love your plugin @OllisGit

[OllisGit]

fyi: It is still scheduled and not closed! PS. Sorry, for being late to the party, but I made a "Octoprint sabatical" (no coding, no printing) the last months.

[github-actions[bot]]

This issue has been automatically marked for closing, because it has not had activity in 30 days. It will be closed if no further activity occurs in 10 days.

[TheOneValen]

Still on the roadmap?