On Fri, 30 Jan 2009, John Machin might have said:
> On 30/01/2009 2:27 AM, Mike Eggleston wrote:
> > On Thu, 29 Jan 2009, Thomas Briggs might have said:
> >
> >> When you say the load "stops", what do you mean? Does the sqlite3
> >> process end? Does it sit there doing nothing?
> >>
> >> The first thing I would do is look at line 55035 of the source file
> >> and see if there's something weird about it.
> >>
> >> Also, have you done a line count on the file so you know exactly
> >> how many rows it should load?
> >>
> >> -T
> >>
> >> On Wed, Jan 28, 2009 at 5:33 PM, Mike Eggleston <mikee...@me.com> wrote:
> >>> Hi,
> >>>
> >>> I'm curious how sqlite3 may perform for some of my applications that
> >>> really don't need things like MySQL or larger. I am using bacula
> >>> (http://www.bacula.org) at work so I dumped the bacula data from MySQL
> >>> (mysqldump bacula > bacula.sql), wrote a perl script to massage the data,
> >>> and now I'm trying to load that data into a sqlite3 file.
> >>>
> >>> I don't see any errors on stdout, but the loading of rows stops after
> >>> 55034 rows (file size is 6338560 bytes). I know in MySQL this table
> >>> has rows.
> >>>
> >>> Where can I look and what might be the error that the rows are not
> >>> loading?
> >>>
> >>> Mike
> >>>
> >>> Fedora Core 5
> >>> sqlite3 3.3.3
> >
> > After deleting the three rows in my previous message (that has not yet
> > made it through moderation), the load now stops at 6337536 bytes when
> > using the command 'time sqlite3 x.db < x.sql ; date'. Still no messages
> > nor errors from sqlite to stdout nor stderr from the above command.
> >
> > What to try next?
>
> Have you looked at the stoppage points in your file(s) with a hex
> editor? Any non-printable non-ASCII characters other than newline ('\n')?
This box is fedora core 5 with sqlite3 3.3.3.
> [If you were on Windows I'd bet you had Ctrl-Z aka '\x1a' aka CPMEOF
> bytes in there]
[Right, this is not windows.]
> You could also try answering Thomas Briggs's questions:
>
> (1) When you say the load "stops", what do you mean? Does the sqlite3
> process end? Does it sit there doing nothing?
I start sqlite3, several tables are created, data for the largest table
(File) starts loading, after 55000+ rows the loading stops, no further
rows are loaded nor are the rest of the tables later in the x.sql file
created.
> (2) Also, have you done a line count on the file so you know exactly how
> many rows it should load?
[mi...@zurich tmp]$ grep -ci 'insert into file ' x.sql
6093439
[mi...@zurich tmp]$ mysql --user=root --exec='select count(*) from File' bacula
+----------+
| count(*) |
+----------+
| 2289331 |
+----------+
My test with sqlite3 so far is only loading 55034 rows of the 6093439
in the x.sql file.
> Also while you are getting the line count from wc, get the character
> count and compare it with the file size from ls.
Below.
> And another thought, bit of a long shot, try running it without the
> "time" and "; date".
>
> Oh, and try running it with only the 3 lines that you cut out plus a
> couple more on the end. If that reproduces the problem, then at least
> you don't have to wait around while experimenting. The next experiment
> would be to try to reproduce the problem with a non-confidential set of
> 5 or so lines so that you could post it here for scrutiny.
>
> And double-check the SQL syntax in the 3 lines that you cut out.
I did.
> HTH,
> John
$ ls -l
total 2012088
drwxr-xr-x 20 ge Domain Users 4096 Sep 17 2007 acads
-rw-r--r-- 1 mikee Domain Users 944217701 Jan 28 14:21 bacula.sql
-rw-r--r-- 1 mikee Domain Users 969 Jan 28 15:22 x.pl
-rw-r--r-- 1 mikee Domain Users 1114109293 Jan 29 08:52 x.sql
$ cp x.sql y.sql
$ ls -l
total 3101160
drwxr-xr-x 20 ge Domain Users 4096 Sep 17 2007 acads
-rw-r--r-- 1 mikee Domain Users 944217701 Jan 28 14:21 bacula.sql
-rw-r--r-- 1 mikee Domain Users 969 Jan 28 15:22 x.pl
-rw-r--r-- 1 mikee Domain Users 1114109293 Jan 29 08:52 x.sql
-rw-r--r-- 1 mikee Domain Users 1114109293 Jan 30 06:59 y.sql
$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-datalv
1887255336 1780873492 12026644 100% /opt/data
$ wc x.sql
9459865 138924803 1114109293 x.sql
$
Running the command:
$ strace -o /opt/data/tmp/sqlite3.strace sqlite3 x.db < x.sql
The strace output when the error starts is:
fcntl64(4, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=1073741824, len=1},
0xbfbc8a94) = 0
fcntl64(4, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=1073741826,
len=510}, 0xbfbc8a94) = 0
_llseek(4, 0, [0], SEEK_SET) = 0
write(4, "SQLite format 3\0\4\0\1\1\0@ \0\0\327\16\0\0\0\0"..., 1024) = 1024
_llseek(4, 6336512, [6336512], SEEK_SET) = 0
write(4, "\r\0\0\0\n\0^\0\3\225\3*\2\326\2\202\2\32\1\306\1q\1\34"..., 1024) =
1024
fsync(4) = 0
close(5) = 0
unlink("/opt/data/tmp/x.db-journal") = 0
fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741826,
len=510}, 0xbfbc8b54) = 0
fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=1073741824, len=2},
0xbfbc8b54) = 0
fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0xbfbc8b54) = 0
read(0, "77,0,\'kI dUAg IH/ B w w A Dx BAA"..., 4096) = 4096
read(0, "C\',\'0\');\nINSERT INTO File VALUES"..., 4096) = 4096
read(0, "7633,126428,442434,0,\'kI TIAu IH"..., 4096) = 4096
read(0, " BB6T6J A A C\',\'J3VBu6i/9a4BrTAE"..., 4096) = 4096
read(0, "545226,7633,126437,442471,0,\'kI "..., 4096) = 4096
read(0, ";\nINSERT INTO File VALUES (22086"..., 4096) = 4096
read(0, "SYAV IHt B RW U A 6y BAA I BJW4b"..., 4096) = 4096
brk(0x9cce000) = 0x9cce000
brk(0x9cc7000) = 0x9cc7000
read(0, "126451,438819,0,\'kI KMBM IHt B R"..., 4096) = 4096
read(0, "3,126455,442519,0,\'kI GkA2 IHt B"..., 4096) = 4096
read(0, " w A Fk BAA I BJW4br +6imw BB6+l"..., 4096) = 4096
read(0, ",7633,126475,102,0,\'kI O8AR EH/ "..., 4096) = 4096
read(0, "BAA I BJW4bs /KShJ BB6+lO A A C\'"..., 4096) = 4096
read(0, "A A C\',\'n+ge6+awO8Ve9zX/3eRRLA\')"..., 4096) = 4096
read(0, "2,442587,0,\'kI Y0BP IG2 B w w A "..., 4096) = 4096
read(0, " O8AX EHt C RW U DTc BAA BAA I B"..., 4096) = 4096
read(0, "t B RW U A F6q BAA w BJW4bs x0w7"..., 4096) = 4096
read(0, "INSERT INTO File VALUES (2208678"..., 4096) = 4096
read(0, "ig\');\nINSERT INTO File VALUES (2"..., 4096) = 4096
read(0, "208678934,545624,7633,126529,442"..., 4096) = 4096
read(0, "A\');\nINSERT INTO File VALUES (22"..., 4096) = 4096
read(0, "\'kI fEAj EHt C RW U A BAA BAA I "..., 4096) = 4096
read(0, "neHV A A C\',\'0\');\nINSERT INTO Fi"..., 4096) = 4096
read(0, "679051,545741,7633,126575,442698"..., 4096) = 4096
read(0, "633,126579,442716,0,\'kI ScAu IHt"..., 4096) = 4096
read(0, "/xy2Npw\');\nINSERT INTO File VALU"..., 4096) = 4096
read(0, " A C\',\'5gAErq1/QMHMUzDDNLuIrQ\');"..., 4096) = 4096
read(0, " C RW U A BAA BAA I BJYIrf y/3Oq"..., 4096) = 4096
read(0, "\',\'0\');\nINSERT INTO File VALUES "..., 4096) = 4096
read(0, "VkYttahf2KfmrENSg\');\nINSERT INTO"..., 4096) = 4096
read(0, "kI bMBm IH/ B w w A Fi BAA I BJW"..., 4096) = 4096
read(0, "\nINSERT INTO File VALUES (220867"..., 4096) = 4096
read(0, " F1T BAA w BJW4bx 13r8Y BB6T6l A"..., 4096) = 4096
read(0, "\',\'RsxMhUa91DtdLzT1lJQfcQ\');\nINS"..., 4096) = 4096
read(0, "ES (2208679360,546050,7633,12662"..., 4096) = 4096
read(0, "e VALUES (2208679389,546079,7633"..., 4096) = 4096
read(0, "7633,126644,102,0,\'kI SsBN EH/ D"..., 4096) = 4096
read(0, "37,7633,126654,104603,0,\'kI a4BR"..., 4096) = 4096
read(0, "21Al4AEcnpljSkuhhl1Ew\');\nINSERT "..., 4096) = 4096
read(0, "126667,25687,0,\'kI FMA6 IH/ B w "..., 4096) = 4096
read(0, "UES (2208679533,546223,7633,1266"..., 4096) = 4096
mmap2(NULL, 167936, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0xb7f90000
brk(0x9cbf000) = 0x9cbf000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
read(0, "Aj EH/ C w w A BAA BAA I BJYIrf "..., 4096) = 4096
mremap(0xb7f90000, 167936, 167936, MREMAP_MAYMOVE) = 0xb7f90000
mremap(0xb7f90000, 167936, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
mremap(0xb7f66000, 172032, 172032, MREMAP_MAYMOVE) = 0xb7f66000
read(0, "7633,126406,442415,0,\'kI BwFM IH"..., 4096) = 4096
mremap(0xb7f66000, 172032, 176128, MREMAP_MAYMOVE) = 0xb7f66000
... and repeats for hours...
Is there a pragma or something that I need to issue at CREATE TABLE time
to say how many rows are going into a table?
I know the first row below is one of the last rows written to the
table. The INSERT fails somewhere in the next few rows.
INSERT INTO File VALUES (2208679533,546223,7633,126675,442964,0,'kI SEAr IH/ B
w w A D6 BAA I BJW4b6 +OXEX BB6+lP A A C','o8jhRLbxeDVc15uzsbjowg');
INSERT INTO File VALUES (2208679534,546224,7633,126675,442965,0,'kI SEAs IH/ B
w w A Fy BAA I BJW4b6 /ZhDW BB6+lP A A C','0vIxIZP8MpVZDS7MNJZBZg');
INSERT INTO File VALUES (2208679535,546225,7633,126675,438758,0,'kI SEAt IH/ B
w w A Np BAA I BJW4b6 8A+wk BB6+lP A A C','vnltPK6SDl2OHf1mz55DYA');
INSERT INTO File VALUES (2208679536,546226,7633,126675,442966,0,'kI SEAu IH/ B
w w A Fy BAA I BJW4b6 BB5qRu BB6+lP A A C','Xn47L0XGSB3fsvhpcTSdYg');
INSERT INTO File VALUES (2208679537,546227,7633,126675,442967,0,'kI SEAv IH/ B
w w A E2 BAA I BJW4b6 9ARUZ BB6+lP A A C','SFSi5H5H7KYlJht1kLoPvA');
INSERT INTO File VALUES (2208679538,546228,7633,126675,442968,0,'kI SEAw IH/ B
w w FVq D6 BAA I BJW4b6 +OVpH BB6+lP A A C','aYq9esJ2ehyiMtzGq4LYtA');
INSERT INTO File VALUES (2208679539,546229,7633,126675,104602,0,'kI SEAx IH/ B
w w FVt Fy BAA I BJW4b6 BB5qRu BB6+lP A A C','Xn47L0XGSB3fsvhpcTSdYg');
INSERT INTO File VALUES (2208678471,545161,7633,126426,442429,0,'kI WEG3 IGk B
w w A Fp BAA I BJW4bn BCEiIi BCEiIi A A C','wHsPB95w75r5aOwqetuFrw');
INSERT INTO File VALUES (2208678472,545162,7633,126426,442430,0,'kI WEG4 IGk B
w w A Fo BAA I BJW4bn BCNbyB BCNbyB A A C','CPHb8hUM4AyMv/NBKl9LIw');
INSERT INTO File VALUES (2208678473,545163,7633,126426,442431,0,'kI WEG5 IGk B
w w A Fo BAA I BJW4bn BCXUu6 BCXUu6 A A C','BZBhV25IPhP8cy38JU0/8Q');
INSERT INTO File VALUES (2208678470,545160,7633,126426,104602,0,'kI WEAa IH/ B
w w A Fo BAA I BJW4bn BCXUu6 BCXUu6 A A C','BZBhV25IPhP8cy38JU0/8Q');
INSERT INTO File VALUES (2208678469,545159,7633,126426,442428,0,'kI WEAZ IH/ B
w w A HY BAA I BJW4bn BBd34U BB6+lN A A C','jujrRwhNpA7y42H7CRlhcw');
INSERT INTO File VALUES (2208678468,545158,7633,126426,104603,0,'kI WEAY IH/ B
w w A HY BAA I BJW4bn BBd34U BB6+lN A A C','jujrRwhNpA7y42H7CRlhcw');
INSERT INTO File VALUES (2208678460,545150,7633,126424,442425,0,'kI VAA+ IH/ B
w w A HN BAA I BJW4bn BA2vgc BB6+lO A A C','/QhUW69w2P4jLj2zCp3HuQ');
INSERT INTO File VALUES (2208678467,545157,7633,126426,442427,0,'kI WEAX IH/ B
w w A JA BAA I BJW4bn BBd8L4 BB6+lN A A C','THt7ssRAIYUijPVth9dEeg');
INSERT INTO File VALUES (2208678466,545156,7633,126426,25687,0,'kI WEAW IH/ B w
w A t3 BAA I BJW4bn BCXUu6 BCXUu6 A A C','tLNzSMxLsSQX7EGYLObqew');
INSERT INTO File VALUES (2208678465,545155,7633,126425,102,0,'kI VAA7 EH/ D w w
A BAA BAA I BJYIrf BA2vfU BCneHV A A C','0');
Mike
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
