Great! Thank you for the pointers. -Tod
On Nov 5, 2012, at 3:21 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Nov 5, 2012 at 3:58 PM, Tod Olson <t...@uchicago.edu> wrote: > >> Is there a predicted next release date? Or more to the point, >> does anyone have a guess about when the fix for that 32-bit int >> overflow error will be in general release? >> > > http://www.sqlite.org/draft/ > http://www.sqlite.org/draft/releaselog/3_7_15.html > > > >> >> -Tod >> >> On Nov 3, 2012, at 3:18 PM, Tod Olson <t...@uchicago.edu> wrote: >> >>> >>> On Nov 3, 2012, at 7:32 AM, Richard Hipp <d...@sqlite.org> >>> wrote: >>> >>>> On Fri, Nov 2, 2012 at 6:44 PM, Tod Olson <t...@uchicago.edu> wrote: >>>> >>>>> I'm having a problem with a create … from … order by when my data >> starts >>>>> approaching 2GB. I'm using SQLite 3.7.14 on FreeBSD 8.3-RELEASE-p3. >>>>> >>>>> The processing starts with an unsorted table, created thus: >>>>> >>>>> CREATE TABLE all_headings (key, heading); >>>>> >>>>> Then it creates a sorted version of the table: >>>>> >>>>> create table headings as select * from all_headings order by key; >>>>> >>>>> This is fine on small data, but when I load 1.8GB of data (8.8 million >>>>> rows) the second CREATE fails, reporting a disk I/O error. >>>> >>>> >>>> You might be running out of /tmp space. Do you have plenty of /tmp >> space >>>> available for use by the sorter. >>> >>> Plenty, 14GB of free space available to /tmp (it's all one big >> partition). >>> >>>> You might also be running into the 32-bit integer overflow bug that was >>>> fixed at http://www.sqlite.org/src/info/e24ba5bee4 though normally that >>>> requires a great deal more than 1.8GB of data. >>> >>> Yes! I compiled up that version and it solves the problem. >>> >>> I eagerly await the release of SQLite version 3.7.15. >>> >>> Thank you for your help. >>> >>> -Tod >>> >>>> Please enable error logging using SQLITE_CONFIG_LOG ( >>>> http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog) >> and >>>> rerun your query and see if that provides any additional clues. >>> >>> >>>>> If I remove the "order by" clause, the create succeeds. (SQLite was >>>>> compiled with large file support, and I could create a 4GB database >> using >>>>> .import so it's not a file system limitation, and the /tmp space is >> plenty >>>>> large.) >>>>> >>>>> [At that point it looks like pre-sorting the data before loading has >> some >>>>> appeal, but the code maintainer prefers to treat SQLite as the >> authority on >>>>> sorting rather to mess with the many versions of sort(1) on the various >>>>> UNIXes and Windows. I understand his point.] >>>>> >>>>> So trying to understand the error with the ORDER BY clause, I loaded up >>>>> the unsorted all_headings table and then trussed sqlite3 running the >> CREATE >>>>> TABLE…ORDER BY. Before the error, there's a lot of lseek()/read() of >> the >>>>> .db file, and a lot of lseek()/write() to temp file (in /tmp, I assume >> this >>>>> is the sort space). Then there's a read() of the temp file, which >> returns >>>>> an error. From truss: >>>>> >>>>> read(5,0x800f64108,-1834983915) ERR#22 'Invalid argument' >>>>> >>>>> man 2 read says read's type signature is: >>>>> >>>>> ssize_t read(int d, void *buf, size_t nbytes); >>>>> >>>>> and it says this about read returning errno 22: >>>>> >>>>> [EINVAL] The pointer associated with d was negative. >>>>> [EINVAL] The value nbytes is greater than INT_MAX. >>>>> >>>>> The pointer doesn't look negative, but that nbytes argument looks >> possibly >>>>> a problem. size_t is 64-bit on this system, but INT_MAX = 2147483647, >> or >>>>> the max for a 32-bit signed int. Though truss doesn't know signed from >>>>> unsigned valued, the size_t nbytes value that truss reports is greater >> than >>>>> MAX_INT. So I think that explains the error. >>>>> >>>>> The main question is: is there anything to be done to get that CREATE >>>>> TABLE … ORDER BY to work? Based on the truss output, I suspect not, but >>>>> maybe someone here has run into the problem before. >>>>> >>>>> Thanks, >>>>> >>>>> -Tod >>>>> >>>>> >>>>> Tod Olson <t...@uchicago.edu> >>>>> Systems Librarian >>>>> University of Chicago Library >>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> sqlite-users@sqlite.org >>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>> >>>> >>>> >>>> -- >>>> D. Richard Hipp >>>> d...@sqlite.org >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
