<class 'django.db.utils.DataError'>

[Lewis Bergman]

I received the following error whilst adding a new Device Type:

<class 'django.db.utils.DataError'>
unsupported Unicode escape sequence LINE 1: ...uuid, 'create', 28, 7, NULL, NULL, 'SFP-GB-GE-T', '{"created... ^ 

DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8. 

CONTEXT: JSON data, line 1: ...-GE-T_gRt8jY0.png", "rear_image": "", "comments":... Python version: 3.8.6 NetBox version: 2.10.4

I made sure there are no trailing characters in any of the fields. This seems to be the same as this old thread but that is pretty old and concerns sites not device types. Not sure what I can do to fix this when it seems to be an edge case of formating. I tried changing several of the fields values to no avail. Any ideas?

[Brian Candler]

What version of Postgres are you running? And what operating system?

Please check the encoding of your database, e.g. using "psql --list":

root@netbox:~# sudo -u postgres psql --list

could not change directory to "/root": Permission denied

                              List of databases

   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges

-----------+----------+----------+---------+---------+-----------------------

 netbox    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +

           |          |          |         |         | postgres=CTc/postgres+

           |          |          |         |         | netbox=CTc/postgres

Or this way:

netbox=# SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'netbox';

 pg_encoding_to_char

---------------------

 UTF8

(1 row)

Do you see something other than "UTF8" there?

The Netbox installation instructions don't say to specify the encoding when creating the "netbox" database.  Maybe it picks up the system's default, and if your system is not set to UTF-8, this might have been the problem.

If so, a quick Google found the following (for a different application)

https://github.com/taigaio/taiga-back/issues/1015#issuecomment-329705478

Their solution might work for you - i.e. take a database dump, recreate the database with encoding set to UTF8, and then restore the dump.

[Brian Candler]

Reading documentation further, when you "CREATE DATABASE" but don't specify an encoding, the encoding is copied from the template1 database.  On my system (Ubuntu) this is UTF8.

However I can't see for sure what Postgres ships with for template0 and template1. I can see some postings, even recent ones, which say that the default encoding is SQL_ASCII:

https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/

That's not true for me, but maybe it was an upstream distro decision to make the default UTF8?

In any case, I'd be interested to know what OS you're running and where you got the Postgres package for it, if not from the OS standard package repo.  I've also raised issue #5760 and if you fix your problem you might want to comment there too.

[Lewis Bergman]

Thanks for your responses and the default does appear to be SQL_ASCII on Ubuntu 20.10

OS Version:

root@netBox:/opt/netbox# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.10
Release:        20.10
Codename:       groovy

Postgres Version:

postgres@netBox:/opt/netbox$ psql
psql (12.5 (Ubuntu 12.5-0ubuntu0.20.10.1))

And as you can see, the template is ASCII:

postgres@netBox:/opt/netbox$ psql --list

                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges

-----------+----------+-----------+---------+-------+-----------------------
 netbox    | postgres | SQL_ASCII | C       | C     | =Tc/postgres         +
           |          |           |         |       | postgres=CTc/postgres+
           |          |           |         |       | netbox=CTc/postgres
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres

In addition, it looks like my locale should have set it appropriately.

root@netBox:/opt/netbox# locale -a
C
C.UTF-8
POSIX

Maybe modify the instructions from:

postgres=# CREATE DATABASE netbox;

to 

CREATE DATABASE netbox WITH ENCODING 'UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8';

Admittedly, I know very little about the subject and postgres seems to have messed it up. I would assume if you try to set all that maybe some languages might be mangled as a result.

After following the instructions here I ended up with:

postgres@netBox:~$ psql --list

                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges

-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8      | C       | C     |

And since that didn't match yours exactly I decided to change the collate and Ctype as well. I would assume that they are just contacted with the Encoding so it wouldn't matter but just to be sure. Since this container is only going to be used for netBox I decided to change the default template in case some future process had to create another DB. I found  the same How To article was really helpful. The "AS integer" replacement was mentioned in the How To article so I thought to be safe, I should run that as well. Because mine was slightly different and for future Googlers, below are the steps I used and the output:

postgres@netBox:~$ pg_dump netbox > netbox-dump.sql
postgres@netBox:~$ sed 's/AS integer//' netbox-dump.sql > alt-netbox-dump.sql
postgres@netBox:~$ ls
12  alt-netbox-dump.sql  netbox-dump.sql
postgres@netBox:~$ exit
root@netBox:~# systemctl stop netbox.service netbox-rq.service  nginx.service  
root@netBox:~# su postgres
postgres@netBox:~$ dropdb netbox
postgres@netBox:~$ psql
psql (12.5 (Ubuntu 12.5-0ubuntu0.20.10.1))
Type "help" for help.

UPDATE 1
postgres=# DROP DATABASE template1;
DROP DATABASE
postgres=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8';
CREATE DATABASE
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
UPDATE 1
postgres=# \c template1;
You are now connected to database "template1" as user "postgres".
template1=# VACUUM FREEZE;
VACUUM

postgres@netBox:~$ psql --list

                              List of databases
   Name    |  Owner   | Encoding  | Collate |  Ctype  |   Access privileges

-----------+----------+-----------+---------+---------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C       |
 template0 | postgres | SQL_ASCII | C       | C       | =c/postgres          +
           |          |           |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8      | C.UTF-8 | C.UTF-8 |

postgres@netBox:~$ psql
psql (12.5 (Ubuntu 12.5-0ubuntu0.20.10.1))
Type "help" for help.

postgres=# CREATE DATABASE netbox;
CREATE DATABASE
postgres=# \q
postgres@netBox:~$ psql --list

                              List of databases
   Name    |  Owner   | Encoding  | Collate |  Ctype  |   Access privileges

-----------+----------+-----------+---------+---------+-----------------------

 netbox    | postgres | UTF8      | C.UTF-8 | C.UTF-8 |

postgres@netBox:~$ psql netbox < alt-netbox-dump.sql
postgres@netBox:~$ psql
postgres=# GRANT ALL PRIVILEGES ON DATABASE netbox TO netbox;
GRANT

root@netBox:~# systemctl stop netbox.service netbox-rq.service  nginx.service

I doubt I needed to run the GRANT statement again as I didn't drop the user but I didn't know if dropping the DB would wipe the privileges associated with it so I just ran it to make sure.

Tested and all works. The funny thing is, what I was trying to do I shouldn't have been doing anyway. I was trying to add an SFP as a device type which of course isn't correct.

Thanks a ton for your help.

--
You received this message because you are subscribed to a topic in the Google Groups "NetBox" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/netbox-discuss/TUCsqlSKSLw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to netbox-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/60f15a55-210d-4a48-addd-36294597c7c5n%40googlegroups.com.

--

Lewis Bergman

325-439-0533 Cell

[Brian Candler]

> The funny thing is, what I was trying to do I shouldn't have been doing anyway. I was trying to add an SFP as a device type which of course isn't correct.

If you can reproduce this, it would be good to understand what the underlying issue was.  It seems to be that some non-ASCII data was being saved in a text or json column, but I don't know how you triggered that.  Were you using any accented characters or symbols in your device type?  For example, could the comments field have included quotation marks which were not standard ASCII ones (' and ") but 'smart' quotes (e.g.  ’ “ ”) ?  Do you have custom fields on your device types?

[Lewis Bergman]

I don't recall and the error message provided snipped the full output. I have done a ton of data entry since then, both before and after updating the DB to UTF8, but all without errors. I don't yet have mail setup in configuration.py so I didn't receive an email about it. What I pasted was the entire message printed to screen. I don't know the value snipping error output other than prettying things up.

I did try several different things at the time. Trimming whitespace, deleting the last few chars of each form field, etc. Not sure I ever tried loading it without the image file.

I tried grepping the log files and it appears netbox doesn't keep those things and by the docs it looks like it only prints to console and emails errors.  I didn't set up any debug levels, which I should have.

I was trying to load a fs.com module as a device. https://www.fs.com/products/11773.html . Nothing terribly unusual. Lots of dashes in their names. The pic I was loading was the default pic from that web page. I did rename it from the original name but I can't remember what the original name was. The final name was SFP_CAT6. I doubt that was the cause.

  Sorry i can't provide the useful feedback you are looking for.  

To view this discussion on the web visit https://groups.google.com/d/msgid/netbox-discuss/4d8957fa-708d-4581-b462-c9c96adfdc22n%40googlegroups.com.