Appointments trying to write illegal characters to database.

[Rebislori]

Hi, i think i broke something.

I created my first page, linked to a calendar, and everything was good. Then i deleted the appointments page, the calendar, and went from scratch trying to create multiple calendars in the directory page.

That never workd, throwing this error in nextcloud log: DriverException An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x81 CONTEXT: unnamed portal parameter $7

The same error shows up, obviously, in the nextcloud database container (in docker). Basically, it cannot write the appt_doc in the database due to that 0x81 character.

I think my best option is to remove completely the plugin from nextcloud, but i have no idea where to find all the files and settings.

Could anyone help me out on this one?

[andreas1107]

I've used appointments for a long time and only today I noticed that I get this error.

As a user, I get the error on the webpage:

Internal Server Error

The server was unable to complete your request.

If this happens again, please send the technical details below to the server administrator.

More details can be found in the server log.
Technical details

    Remote Address: IP
    Request ID: NgvFVoAuLCK8zfcx9AB0

Attaching the detailed logs from admin console.

I can no longer book appointments using the tools so any help would be greatly appreciated!

error-formatted.txt error-raw.txt

Not sure if there has been a recent update @SergeyMosin ? It was working fine last week for me.

[andreas1107]

One more thing, the appointments tool does create a calendar entry like the one attached. Not sure why.

Available.ics.txt

[SergeyMosin]

The appt_doc column was added as a part of latest update here, and in has binary type, which should accept all characters. Do you know what DB (MySQL, PostgreSQL SQLite ) you are using ?

I'll add base64 encoding in the next update avoid this incompatibility.

https://github.com/SergeyMosin/Appointments/blob/d756822c9ffc70b65801569fa234c8065ffea09d/lib/Migration/Version020008Date20240424T001.php#L29-L36

[Rebislori]

I'm using postgreSQL.

Thanks!

[SergeyMosin]

Fixed in v2.1.1

---

The underlying PostgreSQL related issue actually does effect other NC apps/modules like:

• https://github.com/nextcloud/mail/issues/7923
• https://github.com/nextcloud/mail/issues/4320
• https://github.com/nextcloud/recognize/issues/859
• https://github.com/nextcloud/server/issues/41178

If other NC devs encounter similar issues with PostgreSQL's BYTEA columns, the fix I used is this:

When writing binary data using QueryBuilder and createNamedParameter the type must be set to ParameterType::BINARY

Ex:

$query = $this->db->getQueryBuilder();
$query->update(self::HASH_TABLE_NAME)
    ->set('appt_doc', $query->createNamedParameter($docData, ParameterType::BINARY))
    ->where($query->expr()->eq('uid', $query->createNamedParameter($evtUid)))
    ->execute();

And when reading this data, one must do the is_resource check because PostgreSQL returns a resource but MySQL returns a string

Ex:

if (is_resource($row['appt_doc'])) {
    $appt_doc_string = stream_get_contents($row['appt_doc']);
 } else {
    $appt_doc_string = $row['appt_doc'];
}

[andreas1107]

Thank you! For reference I also use postgreSQL, it is what is used in Nextcloud AIO: https://github.com/nextcloud/all-in-one

[Rebislori]

Great, everything working fine 💪