Re: [sqlite] Do I need to migrate to MySQL?

[Darren Duncan]

Mr. Puneet Kishor wrote:
> On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote:
>> MySQL should be avoided like the plague.
> 
> why?
> 
> This is a long standing (un)conventional wisdom to which I too have hewed.
> Now, it so happens, I will be starting work on a project that uses MySQL
> exclusively, and has done so for years. They have been very happy with it.
> And, even though I feel like telling them that they should move to Pg, I
> don't really know what the reasons are. I am not sure if all the reasons that
> might be, are still valid.
> 
> Of course, I don't want this to become a long, religious threat that might be
> inappropriate for this list, or bore most of the other readers to tears. But,
> it merits asking, why should MySQL be avoided like the plague? It is a strong
> statement that requires backing evidence, else it would be construed FUD.

Perhaps my statement was a bit strong, so I will clarify a bit.

*And* I'll give concrete examples.

1.  Firstly, the context for my statement is someone who is not currently using 
MySQL, and so they don't already have an investment in it and codebase designed 
for it.

If one is already using MySQL, then that is the status quo and the question is 
on whether benefits from a change to something else is worth the effort or not. 
  But if one is not already using it, and their current DBMS can't be used how 
they need, then they have to change anyway and the question is between whether 
to move to MySQL or to something else instead; I am addressing this latter 
situation, and you'll notice I also said sticking with SQLite is even better if 
its usage can be fixed.

2.  I consider MySQL to be an 80% solution.

It does the job for which it is used adequately in many cases, and it is 
successfully used in many places, including to drive many businesses and 
organizations for mission-critical purposes.

At the same time, MySQL has a lot of severe flaws, including bugs, 
mis-features, 
and missing useful features.  I won't go into too many details on this here 
because a lot has been written on the subject already that you can reach with 
Google, although I will give some examples.

So, you could do much worse than MySQL, but you could also do much better.

3.  I have many years of personal experience with SQL DBMSs both large and 
small, including many years in using MySQL in production at multiple sites; my 
current main job uses MySQL in fact, so I'm using it day in and out today.  I 
have personally found numerous ways in that MySQL lets me down and I have to 
work around it, where in my usage of Postgres it has not let me down.

Here are a few *current* examples that I discovered (I had previously known of 
many more) because they bit me personally in the last few months (using MySQL 
5.0, though from my reading these are unfixed in the latest versions):

a.  MySQL silently ignores all CHECK constraints in all engines, so for example 
you can't even tell it you want a column to only hold values between 1 and 10.
Its in the MySQL docs:  "The CHECK clause is parsed but ignored by all storage 
engines."

b.  That's just an example of how MySQL silently ignores lots of errors or 
silently changes data on you, such as silently truncating text values that are 
too long for a field when you insert them, so you've lost data without even 
knowing it.  (Okay, I knew about this one previously.)

c.  MySQL treats all subqueries in the WHERE clause as being "dependent 
subquery" even if they are in fact "independent" (have no free variables to be 
filled in by the outer query), so they reexecute the inner query for every row 
in the outer, instead of running the inner just once.  This is a severe 
performance drain, and so an example query that took 1 second if reformatted as 
a FROM subquery plus join would take over 10 minutes (before I killed it) as a 
"IN" subquery.  And this is on tables that are all properly indexed.  The WHERE 
version is much more concise code than the alternative, which is 2-3X as 
verbose.

d.  MySQL seems incapable of using indexes on derived tables to make them 
faster, not automatically nor provides a way to manually specify the use of 
such.  So we use a bunch of explicit temporary tables with explicit indexes.

e.  All MySQL versions have a serious limitation where you can't refer to the 
same temporary table more than once in the same statement or stored function.
See http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html .  So 
then you can't use temporary tables either to refactor common parts of a query.

f.  My understanding is that many MySQL constraints only affect data 
manipulation done after they were defined; adding constraints to a table won't 
catch bad data already in the tables; I haven't personally verified this one.

And those are just the tip of the iceberg.  See Google.  Or MySQL's own manual, 
which spells out many of its deficiencies.

I say avoid MySQL like the plague because it will bite you in so many 

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Keith Christian]

On Fri, Jun 3, 2011 at 3:01 PM, Simon Slavin  wrote:
> However, there are ways to do many things the dot commands do.  For instance,
>
> http://www.sqlite.org/pragma.html#pragma_database_list

Got it, Simon, this works:

set dblist [db1 eval {pragma database_list;}]
puts ""
puts "$dblist"
puts ""


0 main /home/kchristian/./tcl_interface.db



Thank you very much.

==Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Simon Slavin]

On 3 Jun 2011, at 9:58pm, Keith Christian wrote:

> I have another question about the TCL interface:  What is the syntax
> to execute the commands available at the sqlite> prompt?
> 
> Suppose I want to run the ".databases" command, usually executed from
> the sqlite> prompt.

There is none.  The dot commands are implemented by the command-line tool.  
They are not implemented by the SQLite library itself.

However, there are ways to do many things the dot commands do.  For instance,

http://www.sqlite.org/pragma.html#pragma_database_list

shows you how to list the attached databases.  Most of the information commands 
are implemented using the pragmas on that page.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Keith Christian]

On Fri, Jun 3, 2011 at 2:42 PM, Simon Slavin  wrote:
> Use the form
>
> CREATE TABLE IF NOT EXISTS ...
>
> http://www.sqlite.org/lang_createtable.html


Thank you, Simon.

I have another question about the TCL interface:  What is the syntax
to execute the commands available at the sqlite> prompt?

Suppose I want to run the ".databases" command, usually executed from
the sqlite> prompt.

This throws an error:

db1 eval {.databases}

near "".databases"": syntax error
while executing
"db1 eval {".databases"}"
(file "./sqlite_tcl_so_test.tcl" line 22)


Thanks again.

===Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import error: cannot open large file

[Nuno Lucas]

On Fri, Jun 3, 2011 at 18:28, Rense Corten  wrote:
> Thanks for your answer, Nuno.
> However, the system I am using is already 64-bit (I should have
> mentioned that) , and the same binary can do the job on another Ubuntu
> 64-bit  system. I'll try your suggestion nevertheless, but can there
> be other causes?
> Rense

There can always be other causes, like permission problems. But from
what you say I'm tended to believe your problem is the one I
mentioned.
Note that although your system is 64-bits, the pre-compiled sqlite
binary is 32-bits.

But you can check yourself using "strace".
Run the shell as:

$ strace -e open sqlite  ...args...

You will see all open system calls, and for you to be able to open
large files you must see something like:

open("file.csv", O_RDONLY|O_LARGEFILE) = 4

Notice the O_LARGEFILE flag. If the shell is not compiled for large
file access that flag will not be present.
The latter is what happens using the pre-compiled sqlite binary.


Regards,
~Nuno Lucas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Simon Slavin]

On 3 Jun 2011, at 9:26pm, Keith Christian wrote:

> Problem - After the first pass, it obviously attempts to CREATE TABLE
> again, even though the table already exists (Lines 22-25 below.)
> 
> Is there a function provided by libtclslqite3.so that could either
> list the existing tables, or trap this error?

Use the form

CREATE TABLE IF NOT EXISTS ...

http://www.sqlite.org/lang_createtable.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

[Keith Christian]

I've successfully compiled libtclslqite3.so on CentOS 5.5 Linux and
have run the example sqlite3 code from
http://www.sqlite.org/tclsqlite.html.

The TCL script below is almost verbatim from the above web page's examples.

Problem - After the first pass, it obviously attempts to CREATE TABLE
again, even though the table already exists (Lines 22-25 below.)

Is there a function provided by libtclslqite3.so that could either
list the existing tables, or trap this error?  Would it be TCL's
"catch" function?  I'd like the TCL script to continue after throwing
this error.

 1  #!/usr/bin/tclsh
 2  #load ./tclsqlite3.o
 3  load ./libtclsqlite3.so
 4  
 5  sqlite3 db1 ./tcl_interface.db
 6  
 7  db1 eval {CREATE TABLE t1(a int, b text)}
 8  
 9  db1 eval {INSERT INTO t1 VALUES('1','hello')}
10  db1 eval {INSERT INTO t1 VALUES('2','goodbye')}
11  db1 eval {INSERT INTO t1 VALUES('3','howdy!')}
12  
13  set x [db1 eval {SELECT * FROM t1 ORDER BY a}]
14  
15  db1 eval {SELECT * FROM t1 ORDER BY a} values {
16  parray values
17  puts ""
18  }
19  
20  
21  [kchristian@linux1 ~]$ ./sqlite_tcl_so_test.tcl
22  table t1 already exists
23  while executing
24  "db1 eval {CREATE TABLE t1(a int, b text)}"
25  (file "./sqlite_tcl_so_test.tcl" line 8)
26  [kchristian@linux1 ~]$


Thanks!

Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Matthew L. Creech]

On Fri, Jun 3, 2011 at 2:22 PM, Simon Slavin  wrote:
>
> On 3 Jun 2011, at 7:19pm, Darren Duncan wrote:
>
>> MySQL should be avoided like the plague.
>
> Why ?
>

Coincidentally, I happened to be reading over this page just earlier today:

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009

Obviously a bit biased toward PostgreSQL (since it's hosted there),
but it points out some fairly specific differences in features,
performance, etc.  (I have no personal experience either way, but was
just curious myself).

-- 
Matthew L. Creech
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Teg]

We use MySQL on a fairly high end PC server running Linux. The
database is about 300-400 gigs. Front end of PHP and apache. It's been
reliable for a couple years now and seems to have a bunch of headroom.
I wrote a wrapper so my SQLite code (with minor modification) can feed
MySQL instead of SQlite. In fact, the thing that feeds the MySQL DB
reads Sqlite DB's and then feeds this data into MySQL every 15 minutes
or so.

I'd have no problem recommending MySQL for mission critical uses.
Postgres might be better but, MySQL's been good enough.

C



Friday, June 3, 2011, 2:19:44 PM, you wrote:

DD> MySQL should be avoided like the plague.  Use Postgres instead if you have 
to
DD> switch to a larger SQL DBMS.  But hopefully the help you've gotten so far 
will
DD> extend your mileage with SQLite and you won't have to switch to anything 
yet. --
DD> Darren Duncan

DD> Ian Hardingham wrote:
>> Guys, the server for this game -
>> 
>> http://www.frozensynapse.com
>> 
>> uses SQLite.  We've had an unexpectedly successful launch which has 
>> resulted in the server being swamped with players, and I'm trying to 
>> optimise everywhere I can.   I've always been under the impression that 
>> SQLite is pefectly fast and it's the scripting language I wrote the 
>> server in which is too blame.  (Yes, I know writing a back-end in a 
>> single-threaded scripting language is an absolutely terrible idea).  
>> However, everyone in the industry I talk to says that SQLite must be one 
>> of the problems.
>> 
>> I may be looking at a complete re-write.  I may also need to have a 
>> solution which scales beyond one machine.  Can anyone give me advice on 
>> this matter specifically?
>> 
>> (The video on that website at 2.04 gives a good idea of what kind of 
>> functions are being powered by the database).
>> 
>> Thanks,
>> Ian

DD> ___
DD> sqlite-users mailing list
DD> sqlite-users@sqlite.org
DD> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can't download the gzip archives from the list info page

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/03/2011 10:36 AM, Keith Christian wrote:
> What is the procedure to obtain the list archives?

I'd recommend you use Gmane which has messages going back to 2003.  They
provide multiple different ways of viewing the messages as well as searching.

  http://dir.gmane.org/gmane.comp.db.sqlite.general

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3pM2EACgkQmOOfHg372QRW/ACguRzGw7+biaAV3ZhseDYsr5Bt
JCIAoKXMYt9098OkxtZU+AknfQY4vzRd
=P8hJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Mr. Puneet Kishor]

On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote:

> MySQL should be avoided like the plague.

why?

This is a long standing (un)conventional wisdom to which I too have hewed. Now, 
it so happens, I will be starting work on a project that uses MySQL 
exclusively, and has done so for years. They have been very happy with it. And, 
even though I feel like telling them that they should move to Pg, I don't 
really know what the reasons are. I am not sure if all the reasons that might 
be, are still valid.

Of course, I don't want this to become a long, religious threat that might be 
inappropriate for this list, or bore most of the other readers to tears. But, 
it merits asking, why should MySQL be avoided like the plague? It is a strong 
statement that requires backing evidence, else it would be construed FUD.




>  Use Postgres instead if you have to 
> switch to a larger SQL DBMS.  But hopefully the help you've gotten so far 
> will 
> extend your mileage with SQLite and you won't have to switch to anything yet. 
> -- 
> Darren Duncan
> 
> Ian Hardingham wrote:
>> Guys, the server for this game -
>> 
>> http://www.frozensynapse.com
>> 
>> uses SQLite.  We've had an unexpectedly successful launch which has 
>> resulted in the server being swamped with players, and I'm trying to 
>> optimise everywhere I can.   I've always been under the impression that 
>> SQLite is pefectly fast and it's the scripting language I wrote the 
>> server in which is too blame.  (Yes, I know writing a back-end in a 
>> single-threaded scripting language is an absolutely terrible idea).  
>> However, everyone in the industry I talk to says that SQLite must be one 
>> of the problems.
>> 
>> I may be looking at a complete re-write.  I may also need to have a 
>> solution which scales beyond one machine.  Can anyone give me advice on 
>> this matter specifically?
>> 
>> (The video on that website at 2.04 gives a good idea of what kind of 
>> functions are being powered by the database).
>> 
>> Thanks,
>> Ian
> 
> ___
> 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

Re: [sqlite] Do I need to migrate to MySQL?

[Simon Slavin]

On 3 Jun 2011, at 7:19pm, Darren Duncan wrote:

> MySQL should be avoided like the plague.

Why ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Darren Duncan]

MySQL should be avoided like the plague.  Use Postgres instead if you have to 
switch to a larger SQL DBMS.  But hopefully the help you've gotten so far will 
extend your mileage with SQLite and you won't have to switch to anything yet. 
-- 
Darren Duncan

Ian Hardingham wrote:
> Guys, the server for this game -
> 
> http://www.frozensynapse.com
> 
> uses SQLite.  We've had an unexpectedly successful launch which has 
> resulted in the server being swamped with players, and I'm trying to 
> optimise everywhere I can.   I've always been under the impression that 
> SQLite is pefectly fast and it's the scripting language I wrote the 
> server in which is too blame.  (Yes, I know writing a back-end in a 
> single-threaded scripting language is an absolutely terrible idea).  
> However, everyone in the industry I talk to says that SQLite must be one 
> of the problems.
> 
> I may be looking at a complete re-write.  I may also need to have a 
> solution which scales beyond one machine.  Can anyone give me advice on 
> this matter specifically?
> 
> (The video on that website at 2.04 gives a good idea of what kind of 
> functions are being powered by the database).
> 
> Thanks,
> Ian

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can't download the gzip archives from the list info page

[Keith Christian]

Hello Sqlite List,

Clicking a Gzip'd Text xxxKB link on the archives page
(http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users) displays
a page with this message:

No such list 2011-june.txt.gz

What is the procedure to obtain the list archives?

Thanks.

==Keith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import error: cannot open large file

[Rense Corten]

Thanks for your answer, Nuno.
However, the system I am using is already 64-bit (I should have
mentioned that) , and the same binary can do the job on another Ubuntu
64-bit  system. I'll try your suggestion nevertheless, but can there
be other causes?
Rense

> It's what the thread says. The SQLite shell on Ubuntu (on 11.04) isn't
> compiled with large file support on 32-bit systems, so while the
> SQLite library does work with 64-bit database, the shell doesn't.
> The easy solution is to either use a 64-bit Ubuntu system or compile
> the shell yourself with large file support.
>
> To compile it, download the sqlite amalgamation files and run:
>
> gcc -o sqli -O3 -DNDEBUG=1 -D_FILE_OFFSET_BITS=64 sqlite3.c shell.c
> -ldl -pthread
>
> The resulting binary (sqli) will be compiled with large file support
> (I verified it was using strace).
>
>
> Regards,
> ~Nuno Lucas
>
> P.S.- While this could be considered an Ubuntu bug, the truth is that
> the linux shell binary on the sqlite site also isn't compiled with
> large file support, so I would consider this an SQLite bug.
>
>
> --
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Thanks Nico, and everyone else.

A 3x speed bump is still good, so I've set journal_mode to WAL.  The 
docs seem to suggest I only need to do this once, but I presume that 
doing it once every time I open the database is acceptable?

Thanks,
Ian

On 03/06/2011 15:42, Nico Williams wrote:
>
> On Jun 3, 2011 10:04 AM, "Ian Hardingham"  > wrote:
> >
> > Thank you Igor, I'll do some more thorough profiling.
> >
> > When I run the query:
> >
> > UPDATE multiturnTable SET complete=1 WHERE id=-5
> >
> > This takes ~45ms (as reported by SQLite's profile) - is this in the
> > right ballpark?  I'm running a fairly fast modern intel chip here.
>
> In journal mode, yes: that's in the ballpark of what three fsync() 
> calls should take, and that's how many fsync calls SQLite makes at 
> commit time in journal mode.  WAL mode should go faster because most 
> often it does a single fsync().  But even three times faster will seem 
> too slow to you, no doubt.  To go faster consider batching updates 
> into larger transactions (so as to amortize the synchronous I/O cost) 
> or using fast flash devices to hold your DB.  Also, serialize your 
> writing if you can.  Finally, consider some other method of obtaining 
> durability, such as logging updates to a large, widely distributed 
> memcache farm, then disable fsync for most transactions.
>
> Nico
> -- 
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Simon Slavin]

On 3 Jun 2011, at 3:31pm, Ian Hardingham wrote:

> Should really every single INSERT/UPDATE section have a begin/end 
> transaction around it?

That would be the slowest way to do it.  It would make each section an 
independent transaction, and at the end of the transaction all your changes 
must be cleanly written to disk.

This is also what happens if you omit the BEGIN and END SQL commands entirely.  
SQLite notices you're doing an INSERT/UPDATE without having opened a 
transaction for it, and makes that statement its own little transaction.  Then 
it finds your next change and, because it has already closed its transaction, 
has to do the same thing for that one too.  Etc. etc..

To make multiple changes as fast as possible, do a SQL 'BEGIN' command before 
your first change, and an END after the last change.  This will tell SQLite it 
can lump all the changes into one big transaction and do them all in a single 
burst of activity.

[Above explanation simplified a little for brevity.  Handwave.]

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Nico Williams]

On Jun 3, 2011 10:31 AM, "Ian Hardingham"  wrote:
>
> Hey guys, once again thanks for the help.
>
> Should really every single INSERT/UPDATE section have a begin/end
> transaction around it?

There's an implied begin/commit if you don't put them there.  It's when you
can batch lots of INSERT/UPDATE/DELETEs that it really pays to use one large
transaction for them (also, for atomicity and isolation).

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Nico Williams]

Indexes slow down writes somewhat, true, but it sounds like the OP's issue
is with commit latency, the average minimum bound for which is given by the
storage hardware's capabilities.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Nico Williams]

On Jun 3, 2011 10:04 AM, "Ian Hardingham"  wrote:
>
> Thank you Igor, I'll do some more thorough profiling.
>
> When I run the query:
>
> UPDATE multiturnTable SET complete=1 WHERE id=-5
>
> This takes ~45ms (as reported by SQLite's profile) - is this in the
> right ballpark?  I'm running a fairly fast modern intel chip here.

In journal mode, yes: that's in the ballpark of what three fsync() calls
should take, and that's how many fsync calls SQLite makes at commit time in
journal mode.  WAL mode should go faster because most often it does a single
fsync().  But even three times faster will seem too slow to you, no doubt.
To go faster consider batching updates into larger transactions (so as to
amortize the synchronous I/O cost) or using fast flash devices to hold your
DB.  Also, serialize your writing if you can.  Finally, consider some other
method of obtaining durability, such as logging updates to a large, widely
distributed memcache farm, then disable fsync for most transactions.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Black, Michael (IS)]

I should add you only need it around MULTIPLE statements too.

I don't believe there's any benefit around a single statement.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Ian Hardingham [i...@omroth.com]
Sent: Friday, June 03, 2011 9:31 AM
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, once again thanks for the help.

Should really every single INSERT/UPDATE section have a begin/end
transaction around it?


I have posted this code before, so apologies for doing it again - here
is how my scripting language calls a query:

int SQLiteObject::ExecuteSQL(const char* sql, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback,
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");

#ifdef PROFILE_DB
 Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif

 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}

On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> 
>

___
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

Re: [sqlite] Do I need to migrate to MySQL?

[Black, Michael (IS)]

You only need it around INSERT or UPDATE, not SELECT.



The transaction delays the commit to disk so only transactions that write 
benefit from it.



It makes a HUGE difference in speed.



You may also find increasing your cache size could help.  How big is your 
database?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Ian Hardingham [i...@omroth.com]
Sent: Friday, June 03, 2011 9:31 AM
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, once again thanks for the help.

Should really every single INSERT/UPDATE section have a begin/end
transaction around it?


I have posted this code before, so apologies for doing it again - here
is how my scripting language calls a query:

int SQLiteObject::ExecuteSQL(const char* sql, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback,
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");

#ifdef PROFILE_DB
 Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif

 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}

On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> 
>

___
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

[sqlite] HELP: sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program

[Sridhar Polavarapu]

This is not any update or insert statement. This is just a select 
statement. Will that help if i change the driver ? I am currently using 
sqlitejdbc-v056.jar

Thanks
Sridhar

On 03-06-2011 18:59, Sridhar Polavarapu wrote:
> Hi
>
> One of my sqlite query executes faster in sqlite manager( firefox 
> addon ) but take a lot of time to execute using a java program. Here 
> is the snippet
>
> PreparedStatement mStatement;
> ResultSet mResults;
> CachedRowSetImpl impl=null;
> Connection mconn = null;
> try
> {
> Class.forName("org.sqlite.JDBC");
> mconn = DriverManager.getConnection("jdbc:sqlite:"+ 
> "database.db");
> mStatement = mconn.prepareStatement(jobQuery);
> long startTime = System.currentTimeMillis();
> mResults = mStatement.executeQuery();
> long endTime = System.currentTimeMillis();
> System.out.println(jobQuery);
> System.out.println("Time taken to execute query ==>" 
> +(endTime - startTime));
> impl = new CachedRowSetImpl();
> impl.populate(mResults);
> mStatement.close();
> mResults.close();
>
> }
> catch (SQLException e) {
> e.printStackTrace();
> }
>
> Is there anything that I am missing while connecting using java ? Any 
> help appreciated.
>
> Sridhar

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Hey guys, once again thanks for the help.

Should really every single INSERT/UPDATE section have a begin/end 
transaction around it?


I have posted this code before, so apologies for doing it again - here 
is how my scripting language calls a query:

int SQLiteObject::ExecuteSQL(const char* sql, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback, 
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");

#ifdef PROFILE_DB
 Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif

 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}

On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> 
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Black, Michael (IS)]

And do you wrap all your updates inside a transaction?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Ian Hardingham [i...@omroth.com]
Sent: Friday, June 03, 2011 8:40 AM
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, thank you very much for the help so far.

The list of calls which I make during the "end match section", which can
take 2 seconds, is:

SELECT * FROM multiturnTable WHERE id=? LIMIT 1

UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?

UPDATE multiturnTable SET complete=1, score=? WHERE id=?

SELECT * FROM userTable WHERE name='?'  twice

UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice


The setup of the various tables are:

CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER,
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT,
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER,
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0,
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1',
lastP2CommitTime TEXT DEFAULT '-1');";

Multiturn has about 200,000 rows at present

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0,
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";

Usertable has about 40,000 rows at present.

I have the following indexes:

db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable
(player2)", 0);


Thanks,
Ian

On 03/06/2011 13:57, BareFeetWare wrote:
> On 03/06/2011, at 9:47 PM, Ian Hardingham wrote:
>
>> What is basically happening is that we're getting a fairly large number
>> of requests every second.  There is one specific activity which takes
>> about 2 seconds to resolve, which is finishing a match.  This requires
>> an update to three separate tables.
> Send us the schema of the above tables and the SQL that you execute that 
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>

___
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

Re: [sqlite] Do I need to migrate to MySQL?

[Alexey Pechnikov]

I think it's very slow. Update of non-indexed fiels may be faster.
Do you create a new db connection for each update?..
Or may be you have a lot of unused indicies?

2011/6/3 Ian Hardingham :
> Thank you Igor, I'll do some more thorough profiling.
>
> When I run the query:
>
> UPDATE multiturnTable SET complete=1 WHERE id=-5
>
> This takes ~45ms (as reported by SQLite's profile) - is this in the
> right ballpark?  I'm running a fairly fast modern intel chip here.
>
> Thanks,
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Black, Michael (IS)]

My radar says having a TEXT field as a primary key is bad (your userTable).  
String compares are horrendously slow.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Ian Hardingham [i...@omroth.com]
Sent: Friday, June 03, 2011 8:40 AM
Cc: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, thank you very much for the help so far.

The list of calls which I make during the "end match section", which can
take 2 seconds, is:

SELECT * FROM multiturnTable WHERE id=? LIMIT 1

UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?

UPDATE multiturnTable SET complete=1, score=? WHERE id=?

SELECT * FROM userTable WHERE name='?'  twice

UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice


The setup of the various tables are:

CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER,
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT,
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER,
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0,
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1',
lastP2CommitTime TEXT DEFAULT '-1');";

Multiturn has about 200,000 rows at present

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0,
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";

Usertable has about 40,000 rows at present.

I have the following indexes:

db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable
(player2)", 0);


Thanks,
Ian

On 03/06/2011 13:57, BareFeetWare wrote:
> On 03/06/2011, at 9:47 PM, Ian Hardingham wrote:
>
>> What is basically happening is that we're getting a fairly large number
>> of requests every second.  There is one specific activity which takes
>> about 2 seconds to resolve, which is finishing a match.  This requires
>> an update to three separate tables.
> Send us the schema of the above tables and the SQL that you execute that 
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>

___
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

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Thank you Igor, I'll do some more thorough profiling.

When I run the query:

UPDATE multiturnTable SET complete=1 WHERE id=-5

This takes ~45ms (as reported by SQLite's profile) - is this in the 
right ballpark?  I'm running a fairly fast modern intel chip here.

Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Igor Tandetnik]

Ian Hardingham  wrote:
> Hey guys, thank you very much for the help so far.
> 
> The list of calls which I make during the "end match section", which can
> take 2 seconds, is:

The queries you show, on the amount of data you claim, can't possibly take 2 
seconds. They should run nearly instantaneously. The time must be spent 
elsewhere.

> SELECT * FROM multiturnTable WHERE id=? LIMIT 1

Since id is a primary key, you can drop LIMIT 1 clause.

> UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
> UPDATE multiturnTable SET complete=1, score=? WHERE id=?

You could probably combine these two in a single statement.

> SELECT * FROM userTable WHERE name='?'  twice

I assume it's a typo, but just in case it's not, be aware that '?' is *not* a 
parameter placeholder, but a string consisting of a single character '?'. Make 
it

SELECT * FROM userTable WHERE name=?

> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
> scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice

Same here - all instances of '?' should be simply ?.

> 
> 
> The setup of the various tables are:
> 
> CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
> NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date

PRIMARY KEY implies NOT NULL and UNIQUE.

Should probably be "player2 TEXT COLLATE NOCASE" for symmetry. I don't think 
that could cause the slowdown, though.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Hey guys, thank you very much for the help so far.

The list of calls which I make during the "end match section", which can 
take 2 seconds, is:

SELECT * FROM multiturnTable WHERE id=? LIMIT 1

UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?

UPDATE multiturnTable SET complete=1, score=? WHERE id=?

SELECT * FROM userTable WHERE name='?'  twice

UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?, 
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?', 
scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice


The setup of the various tables are:

CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT 
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date 
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER, 
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT, 
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER, 
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT 
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp 
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0, 
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1', 
lastP2CommitTime TEXT DEFAULT '-1');";

Multiturn has about 200,000 rows at present

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL 
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date 
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT 
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT 
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER 
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0, 
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";

Usertable has about 40,000 rows at present.

I have the following indexes:

db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable 
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable 
(player2)", 0);


Thanks,
Ian

On 03/06/2011 13:57, BareFeetWare wrote:
> On 03/06/2011, at 9:47 PM, Ian Hardingham wrote:
>
>> What is basically happening is that we're getting a fairly large number
>> of requests every second.  There is one specific activity which takes
>> about 2 seconds to resolve, which is finishing a match.  This requires
>> an update to three separate tables.
> Send us the schema of the above tables and the SQL that you execute that 
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program

[Sridhar Polavarapu]

Hi

One of my sqlite query executes faster in sqlite manager( firefox addon 
) but take a lot of time to execute using a java program. Here is the 
snippet

 PreparedStatement mStatement;
 ResultSet mResults;
 CachedRowSetImpl impl=null;
 Connection mconn = null;
 try
 {
 Class.forName("org.sqlite.JDBC");
 mconn = DriverManager.getConnection("jdbc:sqlite:"+ 
"database.db");
 mStatement = mconn.prepareStatement(jobQuery);
 long startTime = System.currentTimeMillis();
 mResults = mStatement.executeQuery();
 long endTime = System.currentTimeMillis();
 System.out.println(jobQuery);
 System.out.println("Time taken to execute query ==>" 
+(endTime - startTime));
 impl = new CachedRowSetImpl();
 impl.populate(mResults);
 mStatement.close();
 mResults.close();

 }
 catch (SQLException e) {
 e.printStackTrace();
 }

Is there anything that I am missing while connecting using java ? Any 
help appreciated.

Sridhar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing for a null string

[Nico Williams]

Try "... WHERE f IS NULL".  SQL is a programming language with three-valued
logic, meaning it has truth, falsehood, and null.  NULL != NULL, strangely
enough.

Nico
-- 
On Jun 3, 2011 8:54 AM, "Paul Sanderson" 
wrote:
> I am sure tihs is basic but.
>
> I have a database with a text column and i want to return all rows
> where the column has no value
> I have tried
> select * from db where f = NULL
> select * from db where f = ""
> select * from db where f = ''
>
> all return 0 records when I knopw that most fields are empty - what am
> I missing?
>
> Ta
> ___
> 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

Re: [sqlite] Do I need to migrate to MySQL?

[BareFeetWare]

On 03/06/2011, at 9:47 PM, Ian Hardingham wrote:

> What is basically happening is that we're getting a fairly large number 
> of requests every second.  There is one specific activity which takes 
> about 2 seconds to resolve, which is finishing a match.  This requires 
> an update to three separate tables.

Send us the schema of the above tables and the SQL that you execute that takes 
2 seconds.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Testing for a null string

[Paul Sanderson]

I am sure tihs is basic but.

I have a database with a text column and i want to return all rows
where the column has no value
I have tried
select * from db where f = NULL
select * from db where f = ""
select * from db where f = ''

all return 0 records when I knopw that most fields are empty - what am
I missing?

Ta
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Martin Gadbois]

On Fri, Jun 3, 2011 at 6:58 AM, Ian Hardingham  wrote:

>
> I may be looking at a complete re-write.  I may also need to have a
> solution which scales beyond one machine.  Can anyone give me advice on
> this matter specifically?
>
>
See http://www.sqlite.org/whentouse.html for general usage help.

May I suggest to measure before optimizing? It is a common mistake to
optimize before optimizing in general.

By measuring your various queries, you may find a nice index that would help
your short term issues, or simply adjusting some parameters. I found in the
past that auto-index would make a query a lot slower, just because the SQL
ANALYZE was not run. Your problems may be a simple as this.

That will leave you with more time if you indeed need to go to a
client/server DB setup.

I think I'll give Frozen Synapse a try...


-- 
Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-hot journal removal (remove db locks)

[Simon Slavin]

On 2 Jun 2011, at 12:45pm, Jo Blogs wrote:

> Is there some way to check, using code, if a journal is hot? I'd like to be
> able to do this so I can automatically remove non-hot journals.
> Is there an SQLite api for opening/reading journals etc.? I've looked at the
> source and all the journal related stuff seems to be private.

If the database has no active users, it should not have a journal file.  In 
other words, if you have no active connections to a database file, there should 
be no journal files on disk.

Consequently, the way to remove all journal files is to use the SQLite library 
calls to open the database file then close it again.  The _open() will cause 
SQLite to notice the journal and do whatever's necessary to resolve the crash.  
The _close() will cause SQLite to delete the journal file.

> I'm in a situation where my db is locking (write only) in some rare
> circumstances (I believe after some sort of crash while writing to the db,
> but I'm not sure).
> Others have told me it has also happened to them (on ext3 aswell as nfs).
> I was able to reproduce it once, but on an NFS partition. However I'm not
> entirely sure how, as far as I remember I ran a load of processes writing to
> the db, 50 or so on both cores, and killed them all with a killall command.
> Afterwards the db was write locked, and I had a journal file of size 512kb.
> However, I am able to read from it.

Sorry, I have no experience with NFS.  But having a closed locked journal file 
will certainly present a problem for SQLite.  

> [snip]
> 
> Sometimes I also get a situation after a crash where the db is locked for
> both read and write, in this case I get a journal file with no permissions
> set of size 0kb.

I think you need an NFS expert but a journal file of size 0kb does suggest a 
problem with your file system os ?  hardware ?) rather than a problem with 
SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Simon Slavin]

On 3 Jun 2011, at 12:47pm, Ian Hardingham wrote:

> Common database operations:
> 
> - select all of my non-completed matches
> - select all of my matches
> - select an old match to watch
> - get my current friends

If any of these operations are slow, make sure you have indexes suited to your 
SELECTs.

> - add/remove a friend

Presumably your schema makes this one deletion of one row of one table.  And 
you have a suitable index.

> What is basically happening is that we're getting a fairly large number 
> of requests every second.

Which SQLite library are you using ?  For instance, are you calling SQLite from 
PHP ?  If so, which API are you using ?  Some are faster than others.

> There is one specific activity which takes 
> about 2 seconds to resolve, which is finishing a match.  This requires 
> an update to three separate tables.

Do you do all three updates in a single transaction ?

Oh, and congratulations on the popularity of your project.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

[Richard Hipp]

On Fri, Jun 3, 2011 at 7:47 AM, Ian Hardingham  wrote:

> Thanks Eduardo, I will go into more detail.
>
> The core of the server is the match list.  It is a table with currently
> about 200,000 rows in it.
>
> Two players will start a match, and a new entry is placed in the
> matchTable.  A typical match will last 8 turns - as each player finishes
> a turn, the matchTable entry is updated.  When the match is finished,
> the matchTable is finally updated.
>
> Common database operations:
>
> - select all of my non-completed matches
> - select all of my matches
> - select an old match to watch
> - get my current friends
> - add/remove a friend
>
> What is basically happening is that we're getting a fairly large number
> of requests every second.  There is one specific activity which takes
> about 2 seconds to resolve, which is finishing a match.  This requires
> an update to three separate tables.
>

You have set "PRAGMA journal_mode=WAL" I trust?  If not, do so at once.  It
will make a big difference in your application, I think.

You probably don't need to modify your application to do this.  Just bring
up the database file using the sqlite3 command-line shell and type: "PRAGMA
journal_mode=WAL;"  The WAL mode is persist so the database will continue in
WAL mode until you change it.


>
> Ian
>
>
> > At 12:58 03/06/2011, you wrote:
> >> Guys, the server for this game -
> >>
> >> http://www.frozensynapse.com
> >>
> >> uses SQLite.  We've had an unexpectedly successful launch which has
> >> resulted in the server being swamped with players, and I'm trying to
> >> optimise everywhere I can.   I've always been under the impression that
> >> SQLite is pefectly fast and it's the scripting language I wrote the
> >> server in which is too blame.  (Yes, I know writing a back-end in a
> >> single-threaded scripting language is an absolutely terrible idea).
> >> However, everyone in the industry I talk to says that SQLite must be one
> >> of the problems.
> >>
> >> I may be looking at a complete re-write.  I may also need to have a
> >> solution which scales beyond one machine.  Can anyone give me advice on
> >> this matter specifically?
> >>
> >> (The video on that website at 2.04 gives a good idea of what kind of
> >> functions are being powered by the database).
> >
> > You only uses sqlite for the player lists? What preferences/pragmas do
> > you use for sqlite? Is the server a dedicated server or a shared vps?
> > Which configuration hardware? Tell us more about our configuration and
> > metal so we can help you.
> >
> >> Thanks,
> >> Ian
> >> ___
> >> 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

Re: [sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Thanks Eduardo, I will go into more detail.

The core of the server is the match list.  It is a table with currently 
about 200,000 rows in it.

Two players will start a match, and a new entry is placed in the 
matchTable.  A typical match will last 8 turns - as each player finishes 
a turn, the matchTable entry is updated.  When the match is finished, 
the matchTable is finally updated.

Common database operations:

- select all of my non-completed matches
- select all of my matches
- select an old match to watch
- get my current friends
- add/remove a friend

What is basically happening is that we're getting a fairly large number 
of requests every second.  There is one specific activity which takes 
about 2 seconds to resolve, which is finishing a match.  This requires 
an update to three separate tables.

Ian


> At 12:58 03/06/2011, you wrote:
>> Guys, the server for this game -
>>
>> http://www.frozensynapse.com
>>
>> uses SQLite.  We've had an unexpectedly successful launch which has
>> resulted in the server being swamped with players, and I'm trying to
>> optimise everywhere I can.   I've always been under the impression that
>> SQLite is pefectly fast and it's the scripting language I wrote the
>> server in which is too blame.  (Yes, I know writing a back-end in a
>> single-threaded scripting language is an absolutely terrible idea).
>> However, everyone in the industry I talk to says that SQLite must be one
>> of the problems.
>>
>> I may be looking at a complete re-write.  I may also need to have a
>> solution which scales beyond one machine.  Can anyone give me advice on
>> this matter specifically?
>>
>> (The video on that website at 2.04 gives a good idea of what kind of
>> functions are being powered by the database).
>
> You only uses sqlite for the player lists? What preferences/pragmas do 
> you use for sqlite? Is the server a dedicated server or a shared vps? 
> Which configuration hardware? Tell us more about our configuration and 
> metal so we can help you.
>
>> Thanks,
>> Ian
>> ___
>> 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

Re: [sqlite] Do I need to migrate to MySQL?

[Eduardo Morras]

At 12:58 03/06/2011, you wrote:
>Guys, the server for this game -
>
>http://www.frozensynapse.com
>
>uses SQLite.  We've had an unexpectedly successful launch which has
>resulted in the server being swamped with players, and I'm trying to
>optimise everywhere I can.   I've always been under the impression that
>SQLite is pefectly fast and it's the scripting language I wrote the
>server in which is too blame.  (Yes, I know writing a back-end in a
>single-threaded scripting language is an absolutely terrible idea).
>However, everyone in the industry I talk to says that SQLite must be one
>of the problems.
>
>I may be looking at a complete re-write.  I may also need to have a
>solution which scales beyond one machine.  Can anyone give me advice on
>this matter specifically?
>
>(The video on that website at 2.04 gives a good idea of what kind of
>functions are being powered by the database).

You only uses sqlite for the player lists? What preferences/pragmas 
do you use for sqlite? Is the server a dedicated server or a shared 
vps? Which configuration hardware? Tell us more about our 
configuration and metal so we can help you.

>Thanks,
>Ian
>___
>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

Re: [sqlite] Do I need to migrate to MySQL?

[Alexey Pechnikov]

I start tclsqlite in 16 threads on 2-core Intel Xeon servers and these
work fine.

2011/6/3 Ian Hardingham :
> Guys, the server for this game -
>
> http://www.frozensynapse.com
>
> uses SQLite.  We've had an unexpectedly successful launch which has
> resulted in the server being swamped with players, and I'm trying to
> optimise everywhere I can.   I've always been under the impression that
> SQLite is pefectly fast and it's the scripting language I wrote the
> server in which is too blame.  (Yes, I know writing a back-end in a
> single-threaded scripting language is an absolutely terrible idea).
> However, everyone in the industry I talk to says that SQLite must be one
> of the problems.
>
> I may be looking at a complete re-write.  I may also need to have a
> solution which scales beyond one machine.  Can anyone give me advice on
> this matter specifically?
>
> (The video on that website at 2.04 gives a good idea of what kind of
> functions are being powered by the database).
>
> Thanks,
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Do I need to migrate to MySQL?

[Ian Hardingham]

Guys, the server for this game -

http://www.frozensynapse.com

uses SQLite.  We've had an unexpectedly successful launch which has 
resulted in the server being swamped with players, and I'm trying to 
optimise everywhere I can.   I've always been under the impression that 
SQLite is pefectly fast and it's the scripting language I wrote the 
server in which is too blame.  (Yes, I know writing a back-end in a 
single-threaded scripting language is an absolutely terrible idea).  
However, everyone in the industry I talk to says that SQLite must be one 
of the problems.

I may be looking at a complete re-write.  I may also need to have a 
solution which scales beyond one machine.  Can anyone give me advice on 
this matter specifically?

(The video on that website at 2.04 gives a good idea of what kind of 
functions are being powered by the database).

Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Non-hot journal removal (remove db locks)

[Jo Blogs]

Hey Guys,
Is there some way to check, using code, if a journal is hot? I'd like to be
able to do this so I can automatically remove non-hot journals.
Is there an SQLite api for opening/reading journals etc.? I've looked at the
source and all the journal related stuff seems to be private.

I'm in a situation where my db is locking (write only) in some rare
circumstances (I believe after some sort of crash while writing to the db,
but I'm not sure).
Others have told me it has also happened to them (on ext3 aswell as nfs).
I was able to reproduce it once, but on an NFS partition. However I'm not
entirely sure how, as far as I remember I ran a load of processes writing to
the db, 50 or so on both cores, and killed them all with a killall command.
Afterwards the db was write locked, and I had a journal file of size 512kb.
However, I am able to read from it.
Personally, I believe it may be an nfs lock, coping the sqlite db and db
journal file to another location allows them to be written to. However,
there is an initial read lock which becomes reset by writing to the db.
I have not been able to reproduce the error since on nfs or ext3, the
original db is still locked.

Sometimes I also get a situation after a crash where the db is locked for
both read and write, in this case I get a journal file with no permissions
set of size 0kb.
It happens on both nfs and ext3, removing the journal fixes the problem,
modifying the permissions also fixes the problem.

Thanks,
Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc

[hmas]

Hi,

Thanks a lot for your answer.
What you are poiting out here is indeed very important...

We are new to these "Personal Digital Assistant"/"Pocket PC" programming
"problematics".

We are not targeting so called "general public" devices.
Our customers range from small companies with little budget to
"institutional" entities.
Today, our physical inventory solution relies on barcode terminals which run
a program we've written specifically.

What we want in the future is to be able to sell/propose a program similar
to the one above but which could be downloaded and executed on various
PDAs/Pocket PCs.
We would like to take advantage of the fact that it is sometimes easier for
our customers to use an existing PDA or buy a (new) PDA instead of a barcode
terminal wich is an "industrial" and dedicated device...

So... now, we are trying to decide which devices to target... cleverly... 
Could you recommend good litterature on this topic and point us in the right
direction?

Thanks again for your time. 
Sincerely,
--
hmas
-- 
View this message in context: 
http://old.nabble.com/Error-%28LOCKFILE_EXCLUSIVE_LOCK%29-at-building-sqlite-project-for-pocket-pc-tp31750353p31764126.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making data and tables persistent

[Nick Shaw]

Darren Duncan wrote:
> >> I am using sqlite3 primarily from c++, everything is working fine, 
> >> except when I switch off my computer I loose all data, is there any

> >> setting I need to do to make the data and table object persistent
in the .db file?
> > 
> > Are you correctly closing your connection to the database before
your application quits ?
> > 
> > Does a file with the correct name exist on your disk ?  Does it have
zero length ?
>
> For that matter, maybe hinted from the second point here, are you
using a regular file-based database or a MEMORY one? -- Darren Duncan
___

And are you using autocommit, or are you manually beginning and ending
transactions?  If you have a number of uncommitted transactions prior to
closing the database (or a power failure / app crash / OS crash occurs
before you commit them), the next time you opened the database, sqlite
would rollback the uncommitted changes in the journal file (I assume).

Nick.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

[Sridhar Polavarapu]

Here is the code of my TestStatusDate

public static void main(String[] args) throws Exception
 {
 String jobQuery = "SELECT j.jobId, c.channelName , 
datetime(j.jobCreateDate, 'localtime'), j.jobStatus, j.jobQuality, 
j.jobCompleteDate FROM Job j, Channel c where c.channelId = 
j.jobChannelId ORDER BY jobId DESC";

 long startTime = System.currentTimeMillis();
 Statement mStatement;
 ResultSet mResults;
 CachedRowSetImpl impl=null;
 Connection mconn = null;
 try
 {
 Class.forName("org.sqlite.JDBC");
 mconn = DriverManager.getConnection("jdbc:sqlite:"+ 
"database.db");
 mStatement = mconn.createStatement();
 mResults = mStatement.executeQuery(jobQuery);
 impl = new CachedRowSetImpl();
 impl.populate(mResults);
 mStatement.close();
 mResults.close();

 }
 catch (SQLException e) {
 e.printStackTrace();
 }
 long endTime = System.currentTimeMillis();

 Locale l = Locale.getDefault();
 System.out.println("Today's Locale is " + l);

 System.out.println("timezone ==>" + (TimeZone.getDefault()));

 System.out.println("Time taken to execute query ==>" +(endTime 
- startTime));

 try {
 while(impl.next()){
 StringBuilder sb = new StringBuilder();
 sb.append(impl.getLong(1)+",");
 sb.append(impl.getString(2)+",");
 sb.append(impl.getString(3)+",");
 sb.append(impl.getString(4)+",");
 sb.append(impl.getString(5)+",");
 sb.append(impl.getString(6)+",");

 System.out.println(sb.toString());

 }
 } catch (SQLException e) {
 e.printStackTrace();
 }

 }

On 02-06-2011 22:02, Pavel Ivanov wrote:
> And your TestStatusDate code is?
>
> I'd bet now that the problem is in the way you get that time from
> jdbc. It can convert the time back to utc for you, although it looks a
> little strange that it converts one date and doesn't convert other
> one...
>
>
> Pavel
>
>
> On Thu, Jun 2, 2011 at 11:52 AM, Sridhar Polavarapu
>   wrote:
>> I am on Windows 7 64 bit; I have tested the program as you mentioned, the
>> good point here was the issue still appears please find logs . Let me know
>> if you infer anything from below.
>>
>> C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>java
>> TestStatusDate
>> Today's Locale is en_IN
>> timezone
>> ==>sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=1980,dstSavings=0,useDaylight=false,transitions=6,lastRule=null]
>> Time taken to execute query ==>3623
>> 762,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:53,COMPLETE,md,2011-06-02 13:26:14,
>> 761,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:52,COMPLETE,md,2011-06-02 13:26:07,
>> 760,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:52,COMPLETE,md,2011-06-02 13:25:59,
>> 759,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:51,COMPLETE,md,2011-06-02 13:25:41,
>> 758,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:50,COMPLETE,md,2011-06-02 13:25:36,
>> 757,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:50,COMPLETE,md,2011-06-02 13:25:30,
>> 756,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:49,COMPLETE,md,2011-06-02 13:25:23,
>> 755,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:09,COMPLETE,md,2011-06-02 13:17:30,
>> 754,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:08,COMPLETE,md,2011-06-02 13:17:25,
>> 753,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:07,COMPLETE,md,2011-06-02 13:17:12,
>> 752,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:07,COMPLETE,md,2011-06-02 13:16:51,
>> 751,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:06,COMPLETE,md,2011-06-02 13:16:48,
>> 750,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:05,COMPLETE,md,2011-06-02 13:19:17,
>> 749,automatedTest_1307018964144181250169479584,2011-06-02
>> 12:49:30,COMPLETE,hi,2011-06-02 12:57:58,
>> 748,NaveenTest,2011-06-02 12:49:29,COMPLETE,hi,2011-06-02 12:56:59,
>> 747,automatedTest_1307018192020180478065994419,2011-06-02
>> 12:36:39,COMPLETE,hi,2011-06-02 12:56:30,
>> 746,NaveenTest,2011-06-02 12:36:37,COMPLETE,hi,2011-06-02 12:55:33,
>> 745,automatedTest_130675855965720033590323324,2011-06-02
>> 11:52:21,COMPLETE,md,2011-06-02 11:52:54,
>> 744,automatedTest_1306482594629320685646725968,null,COMPLETE,md,2011-06-02
>> 10:18:47,
>> 743,automatedTest_130675534731016821286104758,2011-06-02
>> 15:15:25,INIT,md,null,
>> 742,automatedTest_130675534731016821286104758,2011-06-02
>> 15:08:15,INIT,md,null,
>> 741,automatedTest_130675534731016821286104758,2011-06-02
>> 09:34:06,COMPLETE,md,2011-06-02 09:34:46,
>>