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
