I have been suffering from a random issue with some signatures i store in MYSQL blob fields for the past 6-9 moths.
I have two blob fields that store simple 10-20K jpg images in my solution, every couple of weeks they seem to suddenly corrupt and be filled with numeric rubbish like
I think this is normally related to when i have been in developer, and i thought might be occuring after an occasional crash when i have left dveloper unattended but runnig for a few hours on my mac and then come back to it and it sort of works for a minute and then freezes. (not 100% sure), but it seems not to happen when i leave my solution for a few weeks without developing.
The two blob fields are only updated by physically picking a new image using the file insertion plugin, so this is not being initiated wrongly.
The other interesting thing is that both signature fields always corrupt together, never one and not the other.
Has anyone else had any similar issues with blob fields?
I have updated my mysql drivers, but interestingly enough i left my machine running a windows version of servoy developer this afternoon for a couple of hours and returned to it. I closed it down and then restarted it. 5 minutes later the same thing happened.
It is an interesting pattern. Note this is my standalone solution so is running Sybase not Mysql. Same table structure.
I did an upload which is a few key output fields back to the main database.
I then ran my main client solution to test the script where i email the pdfs that are uploaded of my whole report.
At this point i noticed on my emailed reports that the signature was missing.
I went back into my standalone developer to check the reports and recreate them. At this point it was not creating them in sybase with signatures in my pdf output layout.
I went into the screen with my preferences where the signature field is stored and it definitely showed my signature.
Ok i thought this is stranger now, in that my signature is there, but not appearing on the layout.
I restarted logged out of and back into my solution and low and behold the signature field had corrupted:!:
There is nothing in my script that touches this blob field except when you specifically run a method to insert from a file. There are also no if statements looking at it (no way it is doing an = instead of a ==.
Its as if the backend database corrputs these blob fields and i am sure it is related to times when i have left servoy standing for a prolonged time.
But now i know its not mac or windows specific and it isnt Sybase or Mysql dependant
It is not easy to reproduce, but i wonder what type of logging i could try to use to catch whats happening.
What version(s) of Servoy and Java are you using ?
Also when you use a transactional database your data shouldn’t get corrupted when Servoy crashes or hangs so that rules that out.
we had a similar-sounding problem in an earlier version of Servoy 3.1.x. In a table of books we were storing 2 blobs, a full-size cover and a thumbnail cover. For some reason a small number of the thumbnails vanished. Since we had the full-size image we could detect problem in a script and auto-fix.
Database mySQL. The images were copied into the mySQL database from a PostgreSQL database with similar structure. Otherwise there was no obvious code to modify the records.
Since our upgrade to 3.5.x and switch to PostgreSQL we have not noticed this problem.
my Java versions are the up to date leopard version on my mac and version 6 update 3 on windows.
The really odd thing is that this is now happening on a local developer version using sybase on windows, as well as my served version that is on MySql.
There are absolutely no scripts altering these images except one to insert the image. But both images go at the same time, they turn into a load of numbers rather than the weird JPG type of ascii that you normally see in a blob field when looked at in a text editor.
Historically it didnt seem to happen when i didn’t use developer much.
It feels as if Servoy is resaving the data and almost doing another form of conversion on it!?!?
I can only hope that when i take off the developer privileges and roll my offline solution out that it stops happening.
dpearce:
There are absolutely no scripts altering these images except one to insert the image. But both images go at the same time, they turn into a load of numbers rather than the weird JPG type of ascii that you normally see in a blob field when looked at in a text editor.
In Servoy 2.2.7 I had an issue of where in certain circumstances a calc field would cause the BLOB to be updated with nothing, effectively emptying the BLOB in the database (which was a bug of course).
This BLOB field was only used in an IF statement in that calc (!).
So do you have any calculations that use that BLOB field in some way?
they are just two blob fields in my customer file, that are used as signatures on either the doctors report or the secretaries appointment letter.
They appear on two layouts.
in the preference section where they are non editable fields with a button next to each to allow the field to be updated:
rw_prefs_expsig=plugins.file.readFile()
and a similar one for the secretary
rw_prefs_secretarysig=plugins.file.readFile()
then they appear as labels with the field as a datasource on what i refer to as my print preview pdf layouts.
Thats it really. Also the fields dont go blank, they merely turn to have a little icon with two boxed representing pages in them and when i use mysqladmin to look at the content it has these 0-9 numeric characters.
in fact i hadn’t realised, but i read it into a global variable based on that customer records on login and that is what is displayed on my pdf preview screen.
The only place it exists in my solution was on the customer preferences panel where it has a button to insert it.
I think what i might do is to actually take this one stage further.
Read them both into global variables on startup. Display that global variable on the preferences pane and then when you insert an image (which technically will only be done once per new customer), to then write the global variable to the database using the raw sql plugin.
This was i can then not display the blob field on any layout in my solution.
If that works then i will conclude it is something weird in servoy. If it doesn’t then G.D knows!
Can you upload the app as a case in our support system? I doubt it’s something in Servoy we’d be seeing it more often then as thousands of users store images and pdf’s in blobs. But who knows maybe it is and that’s why we need your app (or even better a mini app that reproduces the problem) to see what’s wrong.
Can you upload the app as a case in our support system? I doubt it’s something in Servoy we’d be seeing it more often then as thousands of users store images and pdf’s in blobs. But who knows maybe it is and that’s why we need your app (or even better a mini app that reproduces the problem) to see what’s wrong.
First i will test the workaround and make sure it gets rid of the problem.
If by taking all instances of the blob objects of my servoy forms directly and displaying them through reading scripted global variable solves the issue then I would conclude it is a servoy issue and then i will post it or try to recreate it in a simple form first and get it to reproduce the issue.
It is very hard with issues like this in a big solution which only happen every few days or so!
Firstly, I have eliminated the problem by making all reference to these blob signatures in my solution done on forms by displaying a global field into which i load the jpg from the blob when the user logs in. Since i did this one month ago, i have had no further corrupted signatures in my backend MYSQL database.
Secondly, I sent the numeric contents of the corrupted blob field to one of my techies who noticed the following:
O.K.
I ‘unpacked’ the file sigdp that you sent to me on December 27th, twice, and got a jpeg 115304 bytes long which was your signature. Do you need a copy of this file and/or the intermediate ‘packed’ file to see what I mean?
"I can reproduce with perfection the packing process with:
It appears that somehow the contents of the field is being randomly saved to the database when it doesnt need to be and converted twice. The numeric data that represented the corrupted field converted back into the original JPG image.
All very odd, i still believe this was only happening occasionally and anecdotally seemed to occur when i left developer running on my Mac and came back to it after a couple of hours it needed restarting.
Anyway, problem worked around, but maybe the techies on your side may wish to look at it further, or as you say it may be a bug in the MYSQL jdbc driver.