Cannot retrieved TEXT from the server

[stevenang]

I am creating an application which will upload an image from iPad to postgres server and also download the image data from database and display in iPad.

In my database, I have a column named image with TEXT data type. In my application, I converted the image to string data using base64EncodedString(options: .lineLength64Characters) method.

The upload of the image data to database works well. However, an error occurred when I tried to select the data (including the image) from the database and here is the error message: "Response truncated; no data available" "no data available from server"

Can anyone help with this issue?

Thank you and good day

[stevenang]

Here is the log output:

2019-07-17 22:19:40.152748-0400 Tello EHR[13454:8562686] PatientMstrDao.searchAllPatient started... 2019-07-17 22:19:40.177372-0400 Tello EHR[13454:8562686] SQL Statement: Optional("SELECT PATIENT_ID, ACCT_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, BIRTHDAY, AGE, GENDER, ADDRESS, APT_UNIT, CITY, ZIP, STATE, HOME_PHONE, OFFICE_PHONE, MOBILE_PHONE, FAX, EMAIL_ADDRESS, LANGUAGE, ETHNICITY, RACE, FACEBOOK, INSTAGRAM, TWITTER, SSN, IMAGE FROM \"PATIENT_MSTR\";") [2019-07-18T02:19:40.261Z Connection-1 warning] Response truncated; no data available [2019-07-18T02:19:40.269Z Connection-1 warning] Response truncated; no data available [2019-07-18T02:19:40.271Z Connection-1 warning] Closing connection due to unrecoverable error: serverError(description: "no data available from server") 2019-07-17 22:19:40.272873-0400 Tello EHR[13454:8562686] serverError(description: "no data available from server") 2019-07-17 22:19:40.273338-0400 Tello EHR[13454:8562686] serverError(description: "no data available from server")

The IMAGE is the one with datatype TEXT and the size of the data stored is 17.82 MB.

[pitfield]

The error indicates the Postgres server returned a response containing fewer bytes than expected. You might want to check the Postgres log file for an error message or other clues.

I wrote a quick command-line test for your scenario. The contents of main.swift are below. This works in my environment.

import Foundation
import PostgresClientKit

do {
    var configuration = PostgresClientKit.ConnectionConfiguration()
    configuration.host = "127.0.0.1"
    configuration.ssl = true
    configuration.database = "example"
    configuration.user = "bob"
    configuration.credential = .md5Password(password: "welcome1")

    let connection = try PostgresClientKit.Connection(configuration: configuration)
    defer { connection.close() }

    var text = "DROP TABLE IF EXISTS foo"
    try connection.prepareStatement(text: text).execute()

    text = "CREATE TABLE foo (id INTEGER, image TEXT)"
    try connection.prepareStatement(text: text).execute()

    let id = 1

    var imageBytes = [UInt8]()

    for _ in 0..<20_000_000 {
        imageBytes.append(UInt8.random(in: 0...255))
    }

    let imageData = Data(imageBytes).base64EncodedData(options: .lineLength64Characters)
    let image = String(data: imageData, encoding: .ascii)!

    text = "INSERT INTO foo VALUES ($1, $2)"
    try connection.prepareStatement(text: text).execute(parameterValues: [ id, image ])

    text = "SELECT * FROM foo"
    let cursor = try connection.prepareStatement(text: text).execute()

    for row in cursor {
        let columns = try row.get().columns
        let selectedId = try columns[0].int()
        let selectedImage = try columns[1].string()

        print("selected id \(selectedId) with a \(selectedImage.count) byte image")

        assert(selectedId == id)
        assert(selectedImage == image)
    }

    print("success!")
} catch {
    print(error)
}

// EOF

By the way, PostgresClientKit supports the Postgres bytea data type, which might be a more direct way to store image data. See the API doc for the PostgresByteA struct.

[stevenang]

Hello.

Thanks for the reply. I checked my postgresql server and found the following log:

2019-07-18 13:25:14.839 UTC [7863] LOG: received fast shutdown request 2019-07-18 13:25:14.842 UTC [7863] LOG: aborting any active transactions 2019-07-18 13:25:14.843 UTC [1788] TelloAdmin@Tello FATAL: terminating connection due to administrator command 2019-07-18 13:25:14.845 UTC [1739] TelloAdmin@Tello FATAL: terminating connection due to administrator command 2019-07-18 13:25:14.851 UTC [7863] LOG: background worker "logical replication launcher" (PID 7870) exited with exit code 1 2019-07-18 13:25:14.851 UTC [7865] LOG: shutting down 2019-07-18 13:25:14.910 UTC [7863] LOG: database system is shut down 2019-07-18 13:25:15.110 UTC [2434] LOG: listening on IPv4 address "0.0.0.0", port 5432 2019-07-18 13:25:15.110 UTC [2434] LOG: listening on IPv6 address "::", port 5432 2019-07-18 13:25:15.111 UTC [2434] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2019-07-18 13:25:15.167 UTC [2435] LOG: database system was shut down at 2019-07-18 13:25:14 UTC 2019-07-18 13:25:15.184 UTC [2434] LOG: database system is ready to accept connections 2019-07-18 13:25:15.782 UTC [2442] [unknown]@[unknown] LOG: incomplete startup packet 2019-07-18 13:25:37.324 UTC [2480] TelloAdmin@Tello WARNING: there is no transaction in progress 2019-07-18 13:25:40.152 UTC [2480] TelloAdmin@Tello LOG: could not send data to client: Broken pipe 2019-07-18 13:25:40.152 UTC [2480] TelloAdmin@Tello STATEMENT: SELECT PATIENT_ID, ACCT_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, BIRTHDAY, AGE, GENDER, ADDRESS, APT_UNIT, CITY, ZIP, STATE, HOME_PHONE, OFFICE_PHONE, MOBILE_PHONE, FAX, EMAIL_ADDRESS, LANGUAGE, ETHNICITY, RACE, FACEBOOK, INSTAGRAM, TWITTER, SSN, IMAGE FROM "PATIENT_MSTR"; 2019-07-18 13:25:40.153 UTC [2480] TelloAdmin@Tello FATAL: connection to client lost 2019-07-18 13:25:40.153 UTC [2480] TelloAdmin@Tello STATEMENT: SELECT PATIENT_ID, ACCT_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, BIRTHDAY, AGE, GENDER, ADDRESS, APT_UNIT, CITY, ZIP, STATE, HOME_PHONE, OFFICE_PHONE, MOBILE_PHONE, FAX, EMAIL_ADDRESS, LANGUAGE, ETHNICITY, RACE, FACEBOOK, INSTAGRAM, TWITTER, SSN, IMAGE FROM "PATIENT_MSTR";

It seems my application was disconnected to the server (connection is closed). Do you have any idea on why this occurred?

I tried to increased tcp_keepalives_idle, but it still not solve the issue.

Thank you and good day.

[stevenang]

I tried to check my application and found out the connection was closed after the query is executed. Here is my code

` do {

        if connection!.isClosed {
            NSLog("\(CommonErrorMessage.DB_COONECTION_IS_CLOSE_ERROR)")
            return result
        }

        NSLog("Is Connection closed?: \(self.connection!.isClosed)")

        // Create prepared statement
        let SQL_STATEMENT = try connection?.prepareStatement(text: SQL_Statement.SELECT_ALL_PATIENT)
        defer { SQL_STATEMENT?.close() }

        NSLog("SQL Statement: \(SQL_STATEMENT!.text)")
        NSLog("Is Connection closed?: \(self.connection!.isClosed)")
        let cursor = try SQL_STATEMENT!.execute(parameterValues: [])
        NSLog("Row count \(String(describing: cursor.rowCount))")
        for row in cursor {
            if result == nil {
                result = []
            }
            let patient = PatientMstr()
            let columns = try row.get().columns
            NSLog("Record found:")
            patient.patinetID = try columns[0].int()
            patient.accountID = try columns[1].int()
            patient.firstname = try columns[2].string()
            patient.middlename = try columns[3].string()
            patient.lastname = try columns[4].string()
            patient.birthday = try columns[5].string()
            patient.age = try columns[6].int()
            patient.gender = try columns[7].string()
            patient.address = try columns[8].string()
            patient.apt = try columns[9].string()
            patient.city = try columns[10].string()
            patient.zipCode = try columns[11].string()
            patient.state = try columns[12].string()
            patient.homePhone = try columns[13].string()
            patient.officePhone = try columns[14].string()
            patient.mobilePhone = try columns[15].string()
            patient.fax = try columns[16].string()
            patient.emailAddress = try columns[17].string()
            patient.language = try columns[18].string()
            patient.ethnicity = try columns[19].string()
            patient.race = try columns[20].string()
            patient.facebook = try columns[21].string()
            patient.instagram = try columns[22].string()
            patient.twitter = try columns[23].string()
            patient.ssn = try columns[24].string()
            //NSLog("\(columns[25].rawValue)")
            //patient.image = columns[25].

            result?.append(patient)
        }

    } catch {
        NSLog("\(error)")
        NSLog("Is Connection closed?: \(self.connection!.isClosed)")
        throw error
    }

`

I tried to print out connection.isClosed before the execute statement and it returned FALSE. However, when I tried to print the connection.isClosed on catch clause and it returned TRUE.

Is there any idea on why this happened?

Thank you and good day.

[stevenang]

Additional Information: The code is working if I removed the image from the select statement. The error only occurred only when I want to retrieved the image from database.

[stevenang]

do {
    var configuration = PostgresClientKit.ConnectionConfiguration()
    configuration.host = "127.0.0.1"
    configuration.ssl = true
    configuration.database = "example"
    configuration.user = "bob"
    configuration.credential = .md5Password(password: "welcome1")

    let connection = try PostgresClientKit.Connection(configuration: configuration)
    defer { connection.close() }

    var text = "DROP TABLE IF EXISTS foo"
    try connection.prepareStatement(text: text).execute()

    text = "CREATE TABLE foo (id INTEGER, image TEXT)"
    try connection.prepareStatement(text: text).execute()

    let id = 1

    var imageBytes = [UInt8]()

    for _ in 0..<20_000_000 {
        imageBytes.append(UInt8.random(in: 0...255))
    }

    let imageData = Data(imageBytes).base64EncodedData(options: .lineLength64Characters)
    let image = String(data: imageData, encoding: .ascii)!

    text = "INSERT INTO foo VALUES ($1, $2)"
    try connection.prepareStatement(text: text).execute(parameterValues: [ id, image ])

    text = "SELECT * FROM foo"
    let cursor = try connection.prepareStatement(text: text).execute()

    for row in cursor {
        let columns = try row.get().columns
        let selectedId = try columns[0].int()
        let selectedImage = try columns[1].string()

        print("selected id \(selectedId) with a \(selectedImage.count) byte image")

        assert(selectedId == id)
        assert(selectedImage == image)
    }

    print("success!")
} catch {
    print(error)
}

I tried your code also in my environment. Similar error occurred. Please see the following log:

[2019-07-18T14:33:09.459Z Connection-2 warning] Response truncated; no data available [2019-07-18T14:33:09.468Z Connection-2 warning] Response truncated; no data available [2019-07-18T14:33:09.470Z Connection-2 warning] Closing connection due to unrecoverable error: serverError(description: "no data available from server") 2019-07-18 10:33:09.477494-0400 Tello EHR[13648:8671290] serverError(description: "no data available from server") 2019-07-18 10:33:09.518700-0400 Tello EHR[13648:8671290] [MC] System group container for systemgroup.com.apple.configurationprofiles path is /private/var/containers/Shared/SystemGroup/systemgroup.com.apple.configurationprofiles 2019-07-18 10:33:09.519022-0400 Tello EHR[13648:8671290] [MC] Reading from public effective user settings.

The only different between your implementation with mine is you are using the local database and I am using the remote database in AWS EC2.

[pitfield]

OK, thanks for the additional information. I am able to reproduce this problem when running an OSX client against a remote Linux server (running on a Linode, in my case).

This appears to be bug. Either in the socket libraries used by PostgresClientKit (Kitura BlueSocket and BlueSSLService), or in how PostgresClientKit uses those libraries. I will continue to investigate this.

I can only reproduce the problem for the SSL case. Could you please try disabling SSL (configuration.ssl = false) and let me know whether you still get the error?

Also, I can only reproduce the problem in SELECTing very long strings from the TEXT column. Could you please try INSERTing a shorter string (say, 1000 bytes) and SELECTing that? Let me know if that still produces an error. (Of course, long strings should work too, but this will help confirm we are looking at the same issue.)

You mentioned the Connection is closed after the error occurs. This is expected behavior. When PostgresClientKit encounters a fatal error in communicating with the Postgres server, the connection is automatically closed. (A "fatal error" is one that prevents further use of the connection -- such as the socket being closed by the Postgres server, or a garbled or incomplete response.) Closing the connection in these cases lets the application know it needs to create a new connection, and lets the ConnectionPool know that the connection should not be returned to the pool.

Thank you for your help in tracking down this issue.

[stevenang]

Hello. Thanks for the reply.

I turned off the SSL on server side. (postgres.conf) `

authentication_timeout = 1min # 1s-600s

ssl = off

ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers

ssl_prefer_server_ciphers = on

ssl_ecdh_curve = 'prime256v1'

ssl_dh_params_file = ''

ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' and also set the configuration.ssl = false. self.configuration = PostgresClientKit.ConnectionConfiguration() self.configuration!.host = DBSettings.DATABASE_HOST self.configuration!.port = DBSettings.DATABASE_PORT self.configuration!.database = DBSettings.DATABASE_NAME self.configuration!.user = DBSettings.DATABASE_USER self.configuration!.credential = .md5Password(password: DBSettings.DATABASE_PASSWORD) self.configuration!.ssl = false `

This time, I can retrieved the long TEXT data from the database. However, I still have some problem to converting it back to image. So I am not sure yet if the operation is success or not.

But turning off the SSL both server and APP side eliminate the issue i reported here.

Hope this help you to debug the issue.

[pitfield]

I have filed #10 to track the underlying problem. That issue links to another issue in the BlueSSLService library. If you are interested, there is a one-line change in BlueSSLService that seems to fix this problem.

Thanks again for your help with this.

[pitfield]

Just released v0.3.2 which should fix this.