Can't add a spool to inventory/import previous db when using postgres

[reggaemanu]

What were you doing?

I'm trying to add spools or to import previous inventory after switching to postgres. (The connexion to the db works if I click the test connection button.)

What did you expect to happen?

Well, the spool should be added or the inventory imported.

What happened instead?

The plugin throw the error bellow: "Could not add spool There was an unexpected error while saving the filament spool, please consult the logs."

Link to octoprint.log

2018-04-09 02:24:40,334 - octoprint.plugins.filamentmanager - ERROR - Data import failed: (psycopg2.ProgrammingError) syntax error at or near "ON"
LINE 1: ...S (E'1', E'Rigid.ink', E'PETG', E'1.27', E'1.75') ON CONFLIC...
                                                             ^
 [SQL: 'INSERT INTO profiles (id, vendor, material, density, diameter) VALUES (%(id)s, %(vendor)s, %(material)s, %(density)s, %(diameter)s) ON CONFLICT (id) DO UPDATE SET id = %(param_1)s, vendor = %(param_2)s, material = %(param_3)s, density = %(param_4)s, diameter = %(param_5)s'] [parameters: {'diameter': u'1.75', 'vendor': u'Rigid.ink', 'density': u'1.27', 'param_5': u'1.75', 'param_4': u'1.27', 'material': u'PETG', 'param_1': u'1', 'param_3': u'PETG', 'param_2': u'Rigid.ink', 'id': u'1'}]
2018-04-09 02:24:40,346 - tornado.access - ERROR - 500 POST /plugin/filamentmanager/import (127.0.0.1) 61.79ms
2018-04-09 02:27:26,573 - octoprint.plugins.filamentmanager - ERROR - Failed to create spool: 'ascii' codec can't encode character u'\xe9' in position 6: ordinal not in range(128)
2018-04-09 02:27:26,589 - tornado.access - ERROR - 500 POST /plugin/filamentmanager/spools (127.0.0.1) 37.03ms
2018-04-09 02:35:59,578 - octoprint.server.heartbeat - INFO - Server heartbeat <3
2018-04-09 02:39:48,417 - sqlalchemy.pool.QueuePool - ERROR - Exception during reset or similar
Traceback (most recent call last):
  File "/home/pi/oprint/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
    fairy._reset(pool)
  File "/home/pi/oprint/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 829, in _reset
    pool._dialect.do_rollback(self)
  File "/home/pi/oprint/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 440, in do_rollback
    dbapi_connection.rollback()
InterfaceError: connection already closed
2018-04-09 02:41:12,454 - octoprint.plugins.filamentmanager - ERROR - Data import failed: (psycopg2.OperationalError) terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
 [SQL: 'INSERT INTO profiles (id, vendor, material, density, diameter) VALUES (%(id)s, %(vendor)s, %(material)s, %(density)s, %(diameter)s) ON CONFLICT (id) DO UPDATE SET id = %(param_1)s, vendor = %(param_2)s, material = %(param_3)s, density = %(param_4)s, diameter = %(param_5)s'] [parameters: {'diameter': u'1.75', 'vendor': u'Rigid.ink', 'density': u'1.27', 'param_5': u'1.75', 'param_4': u'1.27', 'material': u'PETG', 'param_1': u'1', 'param_3': u'PETG', 'param_2': u'Rigid.ink', 'id': u'1'}]
2018-04-09 02:41:12,466 - tornado.access - ERROR - 500 POST /plugin/filamentmanager/import (127.0.0.1) 52.69ms
2018-04-09 02:50:59,581 - octoprint.server.heartbeat - INFO - Server heartbeat <3
2018-04-09 02:54:53,639 - octoprint.plugins.filamentmanager - ERROR - Data import failed: (psycopg2.ProgrammingError) syntax error at or near "ON"
LINE 1: ...S (E'1', E'Rigid.ink', E'PETG', E'1.27', E'1.75') ON CONFLIC...
                                                             ^
 [SQL: 'INSERT INTO profiles (id, vendor, material, density, diameter) VALUES (%(id)s, %(vendor)s, %(material)s, %(density)s, %(diameter)s) ON CONFLICT (id) DO UPDATE SET id = %(param_1)s, vendor = %(param_2)s, material = %(param_3)s, density = %(param_4)s, diameter = %(param_5)s'] [parameters: {'diameter': u'1.75', 'vendor': u'Rigid.ink', 'density': u'1.27', 'param_5': u'1.75', 'param_4': u'1.27', 'material': u'PETG', 'param_1': u'1', 'param_3': u'PETG', 'param_2': u'Rigid.ink', 'id': u'1'}]
2018-04-09 02:54:53,654 - tornado.access - ERROR - 500 POST /plugin/filamentmanager/import (127.0.0.1) 75.52ms

Link to contents of Javascript console in the browser

Failed to load resource: the server responded with a status of 409 (CONFLICT)
:8576/plugin/filamentmanager/import Failed to load resource: the server responded with a status of 500 (INTERNAL SERVER ERROR)
packed_core.js?ccbe5583:10182 ConfiguredLoggers has not changed. Not saving.
:8576/plugin/filamentmanager/spools Failed to load resource: the server responded with a status of 500 (INTERNAL SERVER ERROR)
2:8576/plugin/filamentmanager/import Failed to load resource: the server responded with a status of 500 (INTERNAL SERVER ERROR)
3:8576/plugin/filamentmanager/spools Failed to load resource: the server responded with a status of 500 (INTERNAL SERVER ERROR)
:8576/plugin/filamentmanager/import Failed to load resource: the server responded with a status of 500 (INTERNAL SERVER ERROR)
2:8576/plugin/filamentmanager/spools Failed to load resource: the server responded with a status of 500 (INTERNAL SERVER ERROR)

Further information

I'm using a prosgres db on my synology nas, I've tried with default db encoding and to force utf-8 encoding at db creation but with no luck. I don't know if the issue is the plugin or the db... I have read the FAQ and provided all required information.

[malnvenshorn]

Can you also provide the CSV files you're trying to import? That would be helpful.

[reggaemanu]

Here you go : filament_export_2018-04-09_02-20-35.zip But I have the same problem trying to manually add a spool.

[malnvenshorn]

I'm not sure that I can help. Just tried to import your data to a postgresql database and it worked fine.

Do you have the same issue when you are using only ascii characters?

[reggaemanu]

Yeah, same issue even if I want to create a spool of PLA named "test". I'm not familiar with postgres (I used to mysql) but I guess this is a db issue then if you can't reproduce the bug. The thing is that I don't see what could be wrong with the db and if I google with the error I have in the log it seems to be a python encoding issue.

[malnvenshorn]

To be honest I have no idea what could cause the issue. Since version 0.4.1 the import/export feature uses unicode encoding and not ascii. The other problem is the strange syntax error issue which I can't explain. What version of the psycopg2 package and postgresql are you using?

[reggaemanu]

psycopg2 2.7.3.2 PostgreSQL 9.3.14

[malnvenshorn]

Okay, that explains the syntax error. The ON CONFLICT clause is only supported since postgresql 9.5. You need to update your installation.

[reggaemanu]

Unfortunately I can't : the synology OS use that db server so I can't mess with it. Anyway I'm happy to have the answer to why it didn't worked :) I guess I'll have to install a server elsewhere but I don't want to put it on one of my raspberry, If for any reason the sdcard get corrupted I'll loose the db, on the NAS it would have been perfect. I'll see if there's a way to install another instance of postgres ...

[malnvenshorn]

You could try to install entware or optware, maybe those repositories provide newer versions for your NAS.

[reggaemanu]

I managed to make it works on postgresql v10 via docker on the nas :) Thanks for your help and sorry for taking your time ! I couldn't live without this plugin anymore and that's getting even better now with the external db (as I've 3 octoprint, it will be even easier to managed my spools) Again, thanks 👍

[malnvenshorn]

You're welcome :wink: