Exception occurred when trying to store large data

[Jaydatt Desai]

Hello Gentlemen,

I am using DerbySQLEntityStore as my database engine, now I am having a case where I want to store a List of email addresses and it may contain 1000-2000 entries.

So, I tried storing a list of 1000 email address and when I do so I got a exception: "http://pastebin.com/HiG9711p". 

From exception I can say that we are restricted to store 10000 characters only. 

One possible problem I can think of is we are using Derby and this is getting stored in memory so this may have some limit due to in memory.

I will try with a persistent engine like MySql, to see if I am thinking is correct.

But if this is not the reason than can any one please let me know what is the solution for this? or is there any way where I can remove this limit?

Thanks in advance,

Jaydatt

[Jaydatt Desai]

Hi Guys, 

More update: I tried with "MySQLEntityStore" but with that as well same result, so with MySQL as well I can store maximum upto 10,000 bytes for the column. 

So, what I am thinking that this may be due to In memory is wrong, there is some other reason or some configuration using which I can store more than 10,000 bytes....

[Jaydatt Desai]

Hi Guys,

One more update: Well this 10000 is the default column varchar size configured in  "DatabaseSQLStringsBuilder.java" as:

        protected SQLDataType getStateType()

        {

            return this.vendor.getDataTypeFactory().sqlVarChar( 10000 );

        }

and even there is no meaning to change it anyway, because there is always a limit, while number of email addresses has no limit.

The other approach I disccused is to use "manyassociation" but with that I will end up with many many number of email entities... for example if I say manyassociation<Emailentity> and if I want to store 5000 email addresses than there will 5000 Email Entities.

So, can anyone suggest me better approach to model this? manyassociation is always there but I don't want to go for it....

[Jaydatt Desai]

Also it will create a problem when I will try to store big binary data: for example if I want to store an "email attachment file data" than in most cases the size of the file bytes exceeds 10000 bytes, so in that case also it will cause the Exception.

[Paul Merlin]

Hello Jaydatt,

Entity payload limit to 10k in SQLEntityStore is hardcoded as you found out.
This should be moved to a Configuration composite.
Please file a JIRA so we can add this: https://ops4j1.jira.com/browse/QI

Cheers

/Paul



Jaydatt Desai a écrit :

--
You received this message because you are subscribed to the Google Groups "qi4j-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to qi4j-dev+u...@googlegroups.com.
To post to this group, send email to qi4j...@googlegroups.com.
Visit this group at http://groups.google.com/group/qi4j-dev.
For more options, visit https://groups.google.com/d/optout.

[Jaydatt Desai]

Hi Paul,

I don't have credentials for JIRA to file a ticket.
However, Can you suggest me what is the best way to model my problem even though its configurable?
I will be having many email addresses when I want to send a "bulk" email (say for example 5000-10000 email addresses). Also the file may contain many bytes (Depends on file size), even if I want to store 5MB of file data than there will be 5*1024*1024 bytes. So, I guess its not suggestible to give a column size this much...
So, what you suggest here, is there any way so that I can store say "infine" bytes. if not than I have to restrict file upload size and number of email addresses.

[Paul Merlin]

Jaydatt Desai a écrit :

> Hi Paul,
>
> I don't have credentials for JIRA to file a ticket.

Can't you create an account?

> However, Can you suggest me what is the best way to model my problem even though its configurable?
> I will be having many email addresses when I want to send a "bulk" email (say for example 5000-10000 email addresses). Also the file may contain many bytes (Depends on file size), even if I want to store 5MB of file data than there will be 5*1024*1024 bytes. So, I guess its not suggestible to give a column size this much...
> So, what you suggest here, is there any way so that I can store say "infine" bytes. if not than I have to restrict file upload size and number of email addresses.

About email adresses, a column size of 8MB should allow you to go up to
10k++ emails. Associations are not a good idea for storing this as this
will lead to too many fetches. SQL is a strict world and your issue show
just this. Maybe another kind of EntityStore would better fit your use case.

About binary attachments, Qi4j has no API for this. You should store
them in a dedicated system. MongoDB EntityStore allow to store
"infinite" binaries alongside Qi4j entities for example. On the other
hand, if storing them on filesystem is acceptable, take a look at the
UoWFile library that bind file operations to the UnitOfWork.

Maybe someone else can chime in with other advices.

Cheers

/Paul

[Stanislav Muhametsin]

On 27.8.2014 11:43, Paul Merlin wrote:
> About email adresses, a column size of 8MB should allow you to go up
> to 10k++ emails. Associations are not a good idea for storing this as
> this will lead to too many fetches. SQL is a strict world and your
> issue show just this. Maybe another kind of EntityStore would better
> fit your use case.
>

SQL is indeed strict, but there are ways to store 'unlimited' data. For
example, in PostreSQL, there is 'text' (
http://www.postgresql.org/docs/9.1/static/datatype-character.html ) data
type for storing unlimited character data. For unlimited binary data,
there is SQL-standard 'BLOB' or in PostgreSQL, once again, 'bytea' (
https://wiki.postgresql.org/wiki/BinaryFilesInDB ).

Of course, making column types as those through Qi4j is currently not
possible. There should be better configuration support in order to make
the SQL entitystore to use customized column types.

Just saying that migrating away from SQL is not necessary, just that
Qi4j currently does not support all of its features.

[Jaydatt Desai]

Hi Paul,

Well I have created an account, but my problem here is I have no rights to file a Jira ticket.

And yes 8Mb of column size is sufficient to store 10k+ email addresses and may be this should be ok at the initial level, and if needed more in future than with Qi4j it will be always configurable.

About Attachment: thanks for the suggestion of "UoWFile", I will think on this that whether this is preferable to store on file system. And if yes than your suggestion will be really help full. However limiting an attachment to a maximum size is always preferable. I mean if for file I will limit it upto 8Mb or 16Mb than it should fine. so, thanks for the suggestion again

[Jaydatt Desai]

Hi Stanislav,

Thanks for PostgreSQL suggestion, there are other databases as well which support storing infinite data, which is not possible with Qi4j. At the other side its also beneficial to restrict column size in many cases, for example when searching or querying if the column data is large than it will always be a time consuming process. So, there are always pros and cons of unlimited column size.

[Paul Merlin]

> Well I have created an account, but my problem here is I have no rights to file a Jira ticket.

Hum ... this is embarassing.
Should be fixed now!

> And yes 8Mb of column size is sufficient to store 10k+ email addresses and
> may be this should be ok at the initial level, and if needed more in future
> than with Qi4j it will be always configurable.

Note that SQLEntityStore has no support for changing the column size afterwards. But this can be done with simple SQL code.

> About Attachment: thanks for the suggestion of "UoWFile", I will think on
> this that whether this is preferable to store on file system. And if yes
> than your suggestion will be really help full. However limiting an
> attachment to a maximum size is always preferable. I mean if for file I will
> limit it upto 8Mb or 16Mb than it should fine. so, thanks for the suggestion
> again

The limit can be in your application, not necessarily in your database
backend.


Jaydatt Desai a écrit :

> Hi Stanislav,
>
> Thanks for PostgreSQL suggestion, there are other databases as well which support storing infinite data, which is not possible with Qi4j. At the other side its also beneficial to restrict column size in many cases, for example when searching or querying if the column data is large than it will always be a time consuming process. So, there are always pros and cons of unlimited column size.

EntityStores are not meant for searching or querying, only for fetches.
Index/Query systems are. And you don't want to index "infinite" binary
data, maybe some meta-data only. But that's another topic.

Cheers

/Paul

[Jaydatt Desai]

Ah Yes EntityStore are just for fetches. 

Cheers

/Paul

[Niclas Hedhman]

Also important to remember, Qi4j is not "streaming" and all content within a UoW needs to fit in memory. This might be a much more troubling limit than that of increasing the email address field size.

I didn't see anyone mentioning it, but you have the choice of using multiple EntityStores, so at least in theory you could use a store that doesn't mind the "many fetches" that a ManyAssocation implies, such as JDBM. I can't recall how to set this up in practice, but it was an initial design criteria of the whole EntityStore system (Users/Groups/Roles in LDAP, Reference data from SQL, Everything else in ABC)

Cheers

--
You received this message because you are subscribed to the Google Groups "qi4j-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to qi4j-dev+u...@googlegroups.com.
To post to this group, send email to qi4j...@googlegroups.com.
Visit this group at http://groups.google.com/group/qi4j-dev.
For more options, visit https://groups.google.com/d/optout.

--
Niclas Hedhman, Software Developer
河南南路555弄15号1901室。
http://www.qi4j.org - New Energy for Java

I live here; http://tinyurl.com/3xugrbk
I work here; http://tinyurl.com/6a2pl4j
I relax here; http://tinyurl.com/2cgsug