Re: [sqlite] playing with sqlite3
On 31/01/2009 8:20 AM, Mike Eggleston wrote:
> Wait. I ran the sqlite3 under script during lunch. I have the same
> behavior. The script also captured the first line of output from killing
> sqlite3. The output shows binary characters in an INSERT statement. The
> bad line is (characters coverted):
>
> (oops deleted the line, a few ^P, ^G, and ^0 characters)
> (found another)
> INSERT INTO File VALUES
> (3555,55862,1093685304,1817326624,1799833888,1092633120,'A A Bey BAA Y BGmuHb
> BFi8Ob BGfR','I A A C^V+I2SKHGKJVuCfW9BcGPWLQ\0\0^C\0g^A
> ø~^~G§\0^G;^C\0~J\0\0\0ã\r\0\05Ú\0\09P0A 4Rj IGk B A A A');
>
> In vi I finally used :g/[^A-^Z]/d to remove all binary characters.
>
> After removing that line I was able to load the entire file without
> errors. Thanks for the help.
Earlier question:
> 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')?
Your answer:
This box is fedora core 5 with sqlite3 3.3.3.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with sqlite3
On Fri, 30 Jan 2009, D. Richard Hipp might have said: > > On Jan 30, 2009, at 8:42 AM, Mike Eggleston wrote: > > > This box is fedora core 5 with sqlite3 3.3.3. > > Version 3.3.3 will be three years old tomorrow. From this I'm > guessing you didn't compile SQLite yourself but are using whatever > happen to come with fedora core 5. And there is no telling how they > compiled it. > > I'd suggest you download the latest sqlite3 command-line shell from > http://www.sqlite.org/download.html > - precompiled and ready to run, and try again using that. > > FWIW, SQLite does not use mmap(), at least not directly. (Maybe the > system malloc() is calling mmap().) So I do not know what is causing > all of those mremap() calls you are seeing in strace. > > D. Richard Hipp > d...@hwaci.com I had thought of that also, but wanted to wait to see if there is some already known reason this is happening. So, I pulled the source for sqlite-3.6.10.tar.gz, compiled it on the fedora core 5 box, and ran the command again. The data loading is stopping at exactly the same place. Short answer: no change. What's next? Wait. I ran the sqlite3 under script during lunch. I have the same behavior. The script also captured the first line of output from killing sqlite3. The output shows binary characters in an INSERT statement. The bad line is (characters coverted): (oops deleted the line, a few ^P, ^G, and ^0 characters) (found another) INSERT INTO File VALUES (3555,55862,1093685304,1817326624,1799833888,1092633120,'A A Bey BAA Y BGmuHb BFi8Ob BGfR','I A A C^V+I2SKHGKJVuCfW9BcGPWLQ\0\0^C\0g^A ø~^~G§\0^G;^C\0~J\0\0\0ã\r\0\05Ú\0\09P0A 4Rj IGk B A A A'); In vi I finally used :g/[^A-^Z]/d to remove all binary characters. After removing that line I was able to load the entire file without errors. Thanks for the help. Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with sqlite3
On Jan 30, 2009, at 8:42 AM, Mike Eggleston wrote: > This box is fedora core 5 with sqlite3 3.3.3. Version 3.3.3 will be three years old tomorrow. From this I'm guessing you didn't compile SQLite yourself but are using whatever happen to come with fedora core 5. And there is no telling how they compiled it. I'd suggest you download the latest sqlite3 command-line shell from http://www.sqlite.org/download.html - precompiled and ready to run, and try again using that. FWIW, SQLite does not use mmap(), at least not directly. (Maybe the system malloc() is calling mmap().) So I do not know what is causing all of those mremap() calls you are seeing in strace. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with sqlite3
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 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 geDomain 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 Users969 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 geDomain 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 Users969 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("
Re: [sqlite] playing with sqlite3
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 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')?
[If you were on Windows I'd bet you had Ctrl-Z aka '\x1a' aka CPMEOF
bytes in there]
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?
(2) Also, have you done a line count on the file so you know exactly how
many rows it should load?
Also while you are getting the line count from wc, get the character
count and compare it with the file size from ls.
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.
HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with sqlite3
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 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? Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with sqlite3
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 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 Duh, I should have tried that first. I've removed three lines around line 55035 and will try the inserts again. Inspecting the file of lines I see no difference other than the values for the columns. The File table should have 2671022 rows (that count is from mysql and from last night's backup, so the count is bound to be off some, but sqlite3 should be close). Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with sqlite3
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 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 > ___ > 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
