On 5/12/15, Deepak Hegde <deepakk at allgosystems.com> wrote:
> Hi everyone,
>
> I am facing a issue and details are as below:
>
> 1) Database table have around 15 table. One of the table have around
> 66000 entries and this table have about 25 columns.
> 2) This table with 66000 entry have field called TITLE with TEXT type
> with table name AUDIO.
> 3) When I run a query like: SELECT TITLE FROM AUDIO WHERE VALID!=0 ORDER
> BY TITLE ASC; (here VALID is also a column),
> sqlite3_step() is returning error SQLITE_FULL.
I assume you have no index on AUDIO.TITLE, correct?
What is the result of:
SELECT sum(length(title)) FROM audio WHERE valid!=0;
I'm guessing that result will be more than 2MB. Without an index on
audio.title, SQLite will need to create some temporary files used for
sorting. Probably those temporary files are still in your 2MB tempfs
and are filling it up.
You could create an index on audio.title to avoid the sort.
You might also want to use "PRAGMA temp_store_directory"
(https://www.sqlite.org/pragma.html#pragma_temp_store_directory) to
force SQLite to use your larger temporary storage volume.
> 4) Database is created in a tempfs path with 64MB space (This is tmpfs
> is not a default one it is created only for the database creation).
> And default tempfs have 2MB of space.
> 5) Created database size is at around 7.5MB.
>
> please can anyone help me in understanding how this query works? Is this
> error is due to no enough cache space for database to prepare the query?
>
> Please help me in resolving this problem. Thanks.
>
> Thanks and Regards
> Deepak
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
