Python - Postgresql Invalid Byte Sequence for varchar - Weird Behavior

Question

I have written a script that uses the python threading function. I think the problem has something to do with the threading because when I run the query from outside of the worker thread, it works fine. I am trying to insert some stuff into the database but ran into some REALLY funky behavior.

Let me simplify:

Running this works:

cmd = "INSERT INTO cstanley_temp (device, host, ux, units) VALUES (%s, %s, %s, %s);"
data = ("solaris-cdc", resultHOST[0], "UX10", 1,)
sql.execute(cmd, data)

Running this does not work:

cmd = "INSERT INTO cstanley_temp (device, host, ux, units) VALUES (%s, %s, %s, %s);"
data = ("solaris-cdc", resultHOST[0], "sdsdsdsdsdsd", 1,)
sql.execute(cmd, data)

Here are the field types: device = varchar host = varchar ux = varchar units = int

This is the error I am receiving:

Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/threading.py", line 801, in __bootstrap_inner
    self.run()
  File "/usr/local/lib/python2.7/threading.py", line 754, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/cstanley/scripts/vip/sun_audit.py", line 37, in workon
    sql.execute(cmd, data)
DataError: invalid byte sequence for encoding "UTF8": 0x86
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

Exception in thread Thread-2:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/threading.py", line 801, in __bootstrap_inner
    self.run()
  File "/usr/local/lib/python2.7/threading.py", line 754, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/cstanley/scripts/vip/sun_audit.py", line 37, in workon
    sql.execute(cmd, data)
InternalError: current transaction is aborted, commands ignored until end of transaction block

Here is the full code:

#!/usr/local/bin/python2.7
import sys, os, string, threading
import paramiko
import psycopg2
import time

#paramiko.util.log_to_file("sun_audit.log")

getCPU = "/usr/sbin/psrinfo -p"
getMEM = "/usr/sbin/prtconf | grep \"Memory\" | awk '{ print $3 }'"
getHOST = "hostname"

class bcolors:
    MAGENTA = '\033[95m'
    YELLOW = '\033[93m'
    ENDC = '\033[0m'

def workon(host,sql):

    #Connect to each host
    ssh = paramiko.SSHClient()
    key = paramiko.RSAKey.from_private_key_file("/home/cstanley/scripts/vip/cstanley")
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(host, username='cstanley', pkey=key)

    #Run Commands
    stdinHOST, stdoutHOST, stderrHOST = ssh.exec_command(getHOST)
    stdinCPU, stdoutCPU, stderrCPU = ssh.exec_command(getCPU)
    stdinMEM, stdoutMEM, stderrMEM = ssh.exec_command(getMEM)

    with threading.Lock():

        resultHOST = stdoutHOST.readlines()
        #print "{0} {0} UX10 1".format(resultHOST[0].rstrip())
        cmd = "INSERT INTO cstanley_temp (device, host, ux, units) VALUES (%s, %s, %s, %s);"
        data = ("solaris-cdc", resultHOST[0], "sdsdsdsdsdsd", 1,)
        sql.execute(cmd, data)

        resultCPU = stdoutCPU.readlines()
        ux40 = (int(resultCPU[0].rstrip()) - 1)
        if ux40 != 0:
            #print "{0} {0} UX40 {1}".format(resultHOST[0].rstrip(),ux40)
            cmd = "INSERT INTO cstanley_temp (device, host, ux, units) VALUES (%s, %s, %s, %s);"
            data = ("solaris-cdc", resultHOST[0], "UX40", ux40,)
            sql.execute(cmd, data)

        resultMEM = stdoutMEM.readlines()
        ux30 = (int(resultMEM[0].rstrip()) / 1024 - 2) / 2
        #print "{0} {0} UX30 {1}".format(resultHOST[0].rstrip(),ux30)
        cmd = "INSERT INTO cstanley_temp (device, host, ux, units) VALUES (%s, %s, %s, %s);"
        data = ("solaris-cdc", resultHOST[0], "UX30", ux30,)
        sql.execute(cmd, data)

        ssh.close()

def main():

    #date = (time.strftime("%Y-%m-%d"))

    #Define our connection string
    conn_string = "host='REMOVED' dbname='REMOVED' user='REMOVED' password='REMOVED' connect_timeout=3"

    # print the connection string we will use to connect
    #print bcolors.MAGENTA + 'Connecting to database\n    ->%s' % (conn_string) + bcolors.ENDC + "\n"

    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object, you can use this cursor to perform queries
    sql = conn.cursor()
    print bcolors.YELLOW + "Inserting Solaris information into table.\n" + bcolors.ENDC

    with open('/home/cstanley/scripts/vip/sun_ip') as ip:
        hosts = ip.read().splitlines()

    threads = []
    for h in hosts:
        t = threading.Thread(target=workon, args=(h,sql,))
        t.start()
        threads.append(t)
    for t in threads:
        t.join()

    conn.commit()
    sql.close()
    conn.close()

if __name__ == "__main__":
    main()

Trying to figure out what is going on here. Why does it work when I input UX10 but not when I input sdsdsdsdsdsd? I have even tried replacing it with solaris-cdc just like it is in the first part of the query, but that also fails. What in the world is going on!?

Answer 1

The error seems to be saying: you're trying to insert some Python 2 strs with binary into a varchar column, when PostgreSQL was expecting that proper UTF-8 encoded unicode be given. Either it comes from the resultHOST[0] intermittently, or perhaps you've got some invisible control character in your code in the "sdsdsdsdsdsd" string:

>>> print u"here be ma\x86gic"
here be magic

However the real reason might be that according to psycopg2 documentation,

Cursors are not thread safe: a multithread application can create many cursors from the same connection and should use each cursor from a single thread. See Thread and process safety for details.

Thus you should create a new cursor on each thread. Instead of passing the sql as an argument to workon, just create a new cursor within the workon method with the statement

sql = conn.cursor()

Also, there shouldn't be any need for locking, as using a single connection with multiple cursors is thread-safe as is.