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?
-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