Another one tip about this situation: when I make query like
INSERT INTO test_fts(test_fts) VALUES('rebuild');
for the table with 100 000 000 rows - it works fine!
When 1 000 000 000 - out of memory.
So, the table and the query are correct.
> How about trying the sqlite3.exe command line utility. put your
> sql for that operation in a text file, launch the program, open
> the database, then read in the sql file with the .read command.
> If the error occurs, then possibly sqlite3. if not then it is
> probably something else.
I tried it and failed.
Console log:
f:\Suggests\test>sqlite3.exe single.db
SQLite version 3.8.9 2015-04-08 12:16:33
Enter ".help" for usage hints.
sqlite> .read test.sql
Error: near line 1: out of memory
sqlite>
Content of test.sql:
insert into phrases_fts(phrases_fts) values('rebuild');
DDL:
CREATE TABLE phrases ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [phrase] CHAR,
[denial] DOUBLE, [visits] INTEGER, [page_views] INTEGER, [visit_time] INTEGER,
[depth] DOUBLE, [se] CHAR, [date_add] INTEGER, [metrika_id] INTEGER, [period]
CHAR);
CREATE VIRTUAL TABLE phrases_fts using fts4 (content='phrases', phrase, denial,
visits, page_views, visit_time, depth, se, date_add, metrika_id, period);
Database contains about 3.2 billion rows, maximum [phrase] length -
256 symbols.
sqlite3.exe from official site:
https://www.sqlite.org/2015/sqlite-shell-win32-x86-3080900.zip
> ------------
> Scott Doctor
> scott at scottdoctor.com
> ------------------
> On 4/28/2015 6:29 AM, Artem wrote:
>> No, I'm sure that is not a problem in my software, it exactly
>> error of the SQLite library. My software is very simple - it creates
>> simple connection to the database with connection string like
>> "Data Source={0};New=false;Journal
>> Mode=Off;Synchronous=Off;FailIfMissing=True"
>> and executes a query like
>> INSERT INTO test_fts(test_fts) VALUES('rebuild');
>> and that is all.
>> I'm pretty sure because I got exactly the same error in SQLite Expert
>> Professional -
>> popular desktop sqlite-management software, that uses another
>> sqlite driver.
>>
>> P.S. Source code of my function:
>>
>> Using conn As New SQLiteConnection(String.Format("Data
>> Source={0};New=false;Journal Mode=Off;Synchronous=Off;FailIfMissing=True",
>> "f:\Suggests\suggests.db"))
>>
>> conn.Open()
>>
>> Using command = conn.CreateCommand
>> command.CommandText = "INSERT INTO suggests_fts(suggests_fts)
>> VALUES('rebuild');"
>> command.ExecuteNonQuery()
>> command.Dispose()
>> End Using
>>
>> conn.Close()
>>
>> End Using
>>
>> P.S. I can send the database to someone who can try, 19 GB in
>> rar-archive.
>>
>>> Getting "NoMem" sounds very much like a memory leak somewhere, with
>>> the most likely place being your own application, followed by the
>>> wrapper you are using, the FTS code and lastly the SQLite core.
>>> Lastly because the SQLite core is extensively tested with an
>>> explicit emphasis on not leaking memory (or other resources) in the
>>> first place and secondly recovering gracefully from memory allocation
>>> failures.
>>> Also, since you have swapped out SQLite versions and even operating
>>> systems without eliminating the problem, it seems rational to look
>>> into the parts that have remained the same.
>>> Maybe you could run a test on Linux under the control of valgrind
>>> and have its memcheck tool take a look.
>>> -----Urspr?ngliche Nachricht-----
>>> Von: Artem [mailto:devspec at yandex.ru]
>>> Gesendet: Dienstag, 28. April 2015 14:36
>>> An: General Discussion of SQLite Database
>>> Betreff: Re: [sqlite] Segfault during FTS index creation from huge data
>>>> On 04/03/2015 10:16 PM, Artem wrote:
>>>>> Hi!
>>>>>
>>>>> The situation is like that. There?s a SQLite database with around 3
>>>>> billion records. Each record consists of a certain CHAR field and several
>>>>> other additional fields with different types. The file size is approx.
>>>>> 340 gb. The maximum content length in the doc field is 256 symbols, the
>>>>> content is in Russian.
>>>>>
>>>>> I?m trying to create a full-text index, but it results in a Segmentation
>>>>> Fault error. I?ve been trying to create it in different possible ways,
>>>>> both under Windows (with SQLite Expert and my own .NET software,
>>>>> including one with x64 architecture) and Linux (both Ubuntu and Centos).
>>>>> I?ve even compiled sqlite from the sources, having included necessary
>>>>> flags for FTS3 and FTS4, but every time I get one and the same error.
>>>> This does sound like a real problem, but one that might be difficult
>>>> to track down.
>>>> Are you able to get us a stack trace of the crash? Ideally one from a
>>>> build with compiler options "-g -DSQLITE_DEBUG" set.
>>>> Thanks,
>>>> Dan.
>>> Hi, Dan. Now I can't to do this because I haven't Linux on my PC.
>>> But I tried to create FTS table again (now it was another database with
>>> 1 350 000 000 rows, smaller than before). And I got the same error (out of
>>> memory) on function:
>>> internal override SQLiteErrorCode Reset(SQLiteStatement stmt)
>>> in file SQLite3.cs
>>> It returns System.Data.SQLite.SQLiteErrorCode.NoMem.
>>> I home it helps.
>>> P.S. It is latest version of SQLite.Net compiled in Visual Studio 2012.
>>>>> I?ve tried two options:
>>>>> - creating a contentless FTS4, when content is stored in a regular
>>>>> table, and FTS-table contains only index (create virtual table
>>>>> docs_fts using fts4(content='docs'... )
>>>>> - creating a full-fledged FTS table from a regular one (insert into
>>>>> docs_fts select doc... from docs;)
>>>>>
>>>>> SQLite is functioning for about 4 hours, after which Segmentation Fault
>>>>> error occurs inevitably.
>>>>> There?re no NULL fields in the database.
>>>>>
>>>>> I?ve worked with 3 different SQLite versions, including the latest one,
>>>>> available on the website. I started trying to create the full-text index
>>>>> of the base shortly after it was created and filled; no other activity,
>>>>> apart from filling the base with data, was conveyed. It has only one docs
>>>>> table, that for sure doesn?t contain any NULL values.
>>>>> I also had an idea that Reindex and Vacuum might have done something
>>>>> wrong, however Reindex happens instantly and Vacuum works rather slowly,
>>>>> but successfully.
>>>>>
>>>>> In short, my actions are:
>>>>>
>>>>> CREATE TABLE docs ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [doc]
>>>>> CHAR... (other fields here) (here?s the process of filling the base
>>>>> with the data, which are downloaded by means of my own script from
>>>>> text files.)
>>>>>
>>>>> CREATE VIRTUAL TABLE docs_fts using fts4 (content='docs', doc...
>>>>> (other fields here) (here?s an attempt of INSERT INTO
>>>>> docs_fts(docs_fts) VALUES ('rebuild') to rebuild contentless FTS
>>>>> index)
>>>>>
>>>>> or
>>>>>
>>>>> CREATE VIRTUAL TABLE docs_fts using fts4 (doc... (other fields here)
>>>>> (here?s an attempt of INSERT INTO docs_fts SELECT doc... from docs;)
>>>>>
>>>>> or
>>>>>
>>>>> CREATE VIRTUAL TABLE docs_fts using fts3 (doc... (other fields here)
>>>>> (here?s an attempt of INSERT INTO docs_fts SELECT doc... from docs;)
>>>>>
>>>>> For each attempt I?ve been making a new copy of the source file, because
>>>>> I suspected that the base could have got broken after Segmentation Fault.
>>>>> I even changed the ram-cards, in case if memory was the problem.
>>>>>
>>>>> But every time I get one and the same result - Segmentation Fault error.
>>>>>
>>>>> So, can you please pay your attention to this problem and fix it ASAP?
>>>>>
>>>>> I can send you a file if you need.
>>>>>
>>>>> Thank you.
>>>>>
>>>>> _______________________________________________
>>>>> sqlite-users mailing list
>>>>> sqlite-users at mailinglists.sqlite.org
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> sqlite-users at mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>> ___________________________________________
>>> Gunter Hick
>>> Software Engineer
>>> Scientific Games International GmbH
>>> FN 157284 a, HG Wien
>>> Klitschgasse 2-4, A-1130 Vienna, Austria
>>> Tel: +43 1 80100 0
>>> E-Mail: hick at scigames.at
>>> This communication (including any attachments) is intended for the
>>> use of the intended recipient(s) only and may contain information
>>> that is confidential, privileged or legally protected. Any
>>> unauthorized use or dissemination of this communication is strictly
>>> prohibited. If you have received this communication in error, please
>>> immediately notify the sender by return e-mail message and delete
>>> all copies of the original communication. Thank you for your cooperation.
>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
? ?????????,
Artem mailto:devspec at yandex.ru
