Blob in MariaDB

Databases
1

I am working with XOJO 2021r2.1 on macOs 11.5.2 .
My app has worked perfectly so far. The app reads und writes tables in a MariaDB 10.3.27 on my Synology NAS.
Now I’ve added a blob column to a table. And when the app tries to write the table with

MyDB.AddRow("MENUE",row)

I get the following DatabaseException:

ErrorNumber 2006
Message MySQL server has gone away
Reason MySQL server has gone away

The exception occurs only when a value is assigned to the blob column. The value is an image.

Does anybody know what the problem is?

2

I’ve tested some pictures.
My first image has a size of 221 KB and leads to a database exception. Then a small image with 13 KB works.
Do I have to increase any parameter value in the MariaDB?

3

intuitive i would use this syntax.

Var row As New DatabaseRow
Var pic As Picture = New Picture(320,240)
row.Column("Pic").PictureValue(Picture.Formats.PNG) = pic

workaround idea

Var row As RowSet
'...
rs.EditRow
rs.Column("Pic").PictureValue(Picture.Formats.PNG) = pic
rs.SaveRow
1 Like
4

some people wrote about
max_allowed_packet
but it seems by default it is 64 MB

https://dev.mysql.com/doc/refman/8.0/en/gone-away.html

5

I used to save images in a blob and buffer the image in the app in a memory block. So I want to do it in this app.
I will add the PictureValue method to the app tomorrow. Maybe it will help.
Until just now I have tested different image formats (JPEG, PNG etc). The problem was always the image size. With a picture with the size of 159 KB I got the error:

ErrorNumber 1406
Message       Data too long for column 'MENBI' at row 1
Reason         Data too long for column 'MENBI' at row 1

Tomorrow I’ll try to work with Pictures instead of MemoryBlock throughout the app (especially PictureValue).

6

Yes I see the comments for max_allowed_packet. But I have no idea what I should change.
If switching to PictureValue doesn’t help, then I limit the image size to a working size.

7

what if you encode binhex the picture into a string and store it as a string in the database ?

8

I will try it

9

I have tried inserting the image as a PictureValue and as a binhex encoded string. Both the same: If the picture is too big (e.g. 221 KB), the AddRow with ErrorNumber 2006 results. If the picture is a little bit smaller (e.g. 159 KB), the AddRow leads to a DatabaseException with ErrorNumber 1406.

My first idea was to save the images in their original state. Now I will scale the images and save only smaller data to avoid the DatabaseException.

10

you could also try :

  • add a big picture to the database using another database software, to see if the problem could come from the synology ?
  • try the mbs database plugins in debug mode (they work like normal) to see if the problem comes from xojo db driver.
1 Like
11

I’ve tried your first idea: I’ve successfully added the 221 KB picture in Safari with phpMyAdmin. My app shows the picture. The problem seems only to be the AddRow command.
I’ve never worked with MBS database plugins - but I think I should make a try in the next days.

12

have you verified the settings values in the mariadb config files?
maybe for safety mania it was reduced.

13

for anything that’s worth, I would never use a sqlite or mariadb in a shared environment, when you can have a very good postgres database on a syno or qnap installed easily.

14

I have tried but I can’t connect via terminal. I always get the response “permission denied” from SSH. I am looking for the solution to the problem.

1 Like
15

I have had a Synology Diskstation for years. I had also tried to install a Postgres on the Synology. I could not get that to work. Since I needed a network solution, I took the existing MariaDB for the sake of simplicity.

16

I’ve created the file my.cnf in the directory /var/packages/MariaDB10/etc with the content

[mysqld]
max_allowed_packet = 1073741824

This seems to be the allowed maximum size of a packet for transfer / receive.

1 Like