sqlite step error

[Markus Spring]

Hi list,
in the past I used the search very rarely, so I don't know when my current
problem with this feature started.

When I want to begin a search, digikam throws an error window with the following
message

sqlite_step error: database disk image is malformed on query: SELECT
Albums.ur||'/'||Images.name FROM Images. Albums LEFT JOIN
ImageProperties on Images.id = ImageProperties.imageid WHERE ((
Images.name LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;

This happens in my 9.3 ubuntu version as well as the latest and greatest svn
version 30 min ago.

I guess it's not a bug in digikam but more a peculiarity of my setup. Any help
how to overcome this will be greatly appreciated

Best regards - Markus
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Arnd Baecker]

On Sun, 6 Jul 2008, Markus Spring wrote:

> Hi list,
> in the past I used the search very rarely, so I don't know when my current
> problem with this feature started.
>
> When I want to begin a search, digikam throws an error window with the following
> message
>
> sqlite_step error: database disk image is malformed on query: SELECT
> Albums.ur||'/'||Images.name FROM Images. Albums LEFT JOIN
> ImageProperties on Images.id = ImageProperties.imageid WHERE ((
> Images.name LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;

Is ther really a space in "Images. Albums" ?
((Hmm, space seems to be irrelevant ...)

> This happens in my 9.3 ubuntu version as well as the latest and greatest svn
> version 30 min ago.
>
> I guess it's not a bug in digikam but more a peculiarity of my setup. Any help
> how to overcome this will be greatly appreciated

No real idea. What happens if you do
  cp digikam3.db /tmp/
  cd /tmp
  sqlite3 digikam3.db
and then paste the above "SELECT ...."

I get SQL error: no such table: Images.Albums

Something like:

SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
ImageProperties on Images.id = ImageProperties.imageid WHERE ((Images.name
LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;

would work.

Somehow the sql request looks odd to me.
Markus: how do you define the search?

Marcel any ideas?

Best, Arnd
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

On Sunday 06 July 2008 23:24:38 Arnd Baecker wrote:

> On Sun, 6 Jul 2008, Markus Spring wrote:
> > Hi list,
> > in the past I used the search very rarely, so I don't know when my
> > current problem with this feature started.
> >
> > When I want to begin a search, digikam throws an error window with the
> > following message
> >
> > sqlite_step error: database disk image is malformed on query: SELECT
> > Albums.ur||'/'||Images.name FROM Images. Albums LEFT JOIN
> > ImageProperties on Images.id = ImageProperties.imageid WHERE ((
> > Images.name LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
>
> Is ther really a space in "Images. Albums" ?
> ((Hmm, space seems to be irrelevant ...)

This query sure looks strange, 1) I guess the dot should really be a comma:
SELECT Albums.ur||'/'||Images.name FROM Images, Albums LEFT JOIN[...]

Hmm I just searched in the kioslave, but I can't find the problem right now...

>
> > This happens in my 9.3 ubuntu version as well as the latest and greatest
> > svn version 30 min ago.
> >
> > I guess it's not a bug in digikam but more a peculiarity of my setup. Any
> > help how to overcome this will be greatly appreciated
>
> No real idea. What happens if you do
>   cp digikam3.db /tmp/
>   cd /tmp
>   sqlite3 digikam3.db
> and then paste the above "SELECT ...."
>
> I get SQL error: no such table: Images.Albums
>
> Something like:
>
> SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
> ImageProperties on Images.id = ImageProperties.imageid WHERE ((Images.name
> LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
>
> would work.
>
> Somehow the sql request looks odd to me.
> Markus: how do you define the search?
>
> Marcel any ideas?
>
> Best, Arnd
> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

I guess we are talking about this line here:

kioslave/digikamsearch.cpp[lines 261-264]:

        // query head
        sqlQuery = "SELECT Albums.url||'/'||Images.name "
                   "FROM Images, Albums LEFT JOIN ImageProperties on Images.id
= ImageProperties.imageid "
                   "WHERE ( ";

Markus, have you played with the sourcecode? Otherwise I can't explain why
this is happening right now.

On Sunday 06 July 2008 23:24:38 Arnd Baecker wrote:

> On Sun, 6 Jul 2008, Markus Spring wrote:
> > Hi list,
> > in the past I used the search very rarely, so I don't know when my
> > current problem with this feature started.
> >
> > When I want to begin a search, digikam throws an error window with the
> > following message
> >
> > sqlite_step error: database disk image is malformed on query: SELECT
> > Albums.ur||'/'||Images.name FROM Images. Albums LEFT JOIN
> > ImageProperties on Images.id = ImageProperties.imageid WHERE ((
> > Images.name LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
>
> Is ther really a space in "Images. Albums" ?
> ((Hmm, space seems to be irrelevant ...)
>
> > This happens in my 9.3 ubuntu version as well as the latest and greatest
> > svn version 30 min ago.
> >
> > I guess it's not a bug in digikam but more a peculiarity of my setup. Any
> > help how to overcome this will be greatly appreciated
>
> No real idea. What happens if you do
>   cp digikam3.db /tmp/
>   cd /tmp
>   sqlite3 digikam3.db
> and then paste the above "SELECT ...."
>
> I get SQL error: no such table: Images.Albums
>
> Something like:
>
> SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
> ImageProperties on Images.id = ImageProperties.imageid WHERE ((Images.name
> LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
>
> would work.
>
> Somehow the sql request looks odd to me.
> Markus: how do you define the search?
>
> Marcel any ideas?
>
> Best, Arnd
> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Markus Spring]

Andi Clemens schrieb:
> Markus, have you played with the sourcecode? Otherwise I can't explain why
> this is happening right now.
No - never. I know my limits...
What might have influenced this error is the fact that I created this digikam
database on a non-utf8 system (debian pre-sarge) and later migrated the whole
thing to ubuntu. As I am ultraconservative with file and directory names I had
no trouble with this side of the migration, but I don't know what happens inside
sqlite.

Markus
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

But is the error message really the one you get? Maybe it was a typo somehow
in the debugging info? I really think there should be a comma, not a dot.
Have you compiled digiKam from source / svn?
If so, do you include debugging symbols?
Is digiKam crashing? If so, can you provide us with a backtrace?

Take a look at
http://www.digikam.org/drupal/contrib
and
http://techbase.kde.org/Contribute/Bugsquad/How_to_create_useful_crash_reports

If you need further help, just send a mail :-)

Andi

On Monday 07 July 2008 11:57:58 Markus Spring wrote:

> Andi Clemens schrieb:
> > Markus, have you played with the sourcecode? Otherwise I can't explain
> > why this is happening right now.
>
> No - never. I know my limits...
> What might have influenced this error is the fact that I created this
> digikam database on a non-utf8 system (debian pre-sarge) and later migrated
> the whole thing to ubuntu. As I am ultraconservative with file and
> directory names I had no trouble with this side of the migration, but I
> don't know what happens inside sqlite.
>
> Markus

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Another node here... this statement is right and should work:

SELECT
Albums.url||'/'||Images.name FROM Images, Albums LEFT JOIN
ImageProperties on Images.id = ImageProperties.imageid WHERE ((
Images.name LIKE '%12345%')) AND (Albums.id=Images.dirid) LIMIT 500;

These are the issues with your version:

1. the dot between Images and Albums in the second line has to be replaced by
a comma
2. in the second line, Albums.ur has to be replaced by Albums.url

I really don't know how it's possible to get this query.
Can you describe exactly what you are searching for? What you selected in the
search dialog? Can you provide a screenshot of that?

Andi

On Monday 07 July 2008 11:57:58 Markus Spring wrote:

> Andi Clemens schrieb:
> > Markus, have you played with the sourcecode? Otherwise I can't explain
> > why this is happening right now.
>
> No - never. I know my limits...
> What might have influenced this error is the fact that I created this
> digikam database on a non-utf8 system (debian pre-sarge) and later migrated
> the whole thing to ubuntu. As I am ultraconservative with file and
> directory names I had no trouble with this side of the migration, but I
> don't know what happens inside sqlite.
>
> Markus

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Markus Spring]

Andi,
thanks for the effort and support. The error message showed up in a window and
it was impossible to copy and paste, but I had to write it down. So that is
probably the reason for the inconsistencies in the syntax. I am sorry for that.

The program did not crash. The bug is reproducable, and attach the screenshot of
the message to this post.

If it helps I will recompile it with debugging symbols enabled.

Best regards - Markus

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Hmm I can't reproduce this... maybe you are right. It seems to be a problem
with utf8 that might be set now on your system. Normally digiKam will warn
you that the encoding has changed and that this might mess up the database.

Have you tried to look at the database, for example with sqlitebrowser? I
attached a screenshot, it should look something like this, especially take a
look at the datetime field. Maybe this is corrupted?

Andi


On Monday 07 July 2008 20:36:36 Markus Spring wrote:

> Andi,
> thanks for the effort and support. The error message showed up in a window
> and it was impossible to copy and paste, but I had to write it down. So
> that is probably the reason for the inconsistencies in the syntax. I am
> sorry for that.
>
> The program did not crash. The bug is reproducable, and attach the
> screenshot of the message to this post.
>
> If it helps I will recompile it with debugging symbols enabled.
>
> Best regards - Markus

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Markus Spring]

Arnd Baecker schrieb:

> No real idea. What happens if you do
>   cp digikam3.db /tmp/
>   cd /tmp
>   sqlite3 digikam3.db
>
>
> SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
> ImageProperties on Images.id = ImageProperties.imageid WHERE ((Images.name
> LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
>

sqlite> SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
ImageProperties on Images.id = ImageProperties.imageid WHERE ((Images.name LIKE
'%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
SQL error: database disk image is malformed

Arnd, I copied your query into the sqlite3 command line and there again I get
this error 'database disk image is malformed'

No idea where this comes from.

Markus

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Can you provide us with your database? Or is the data too sensible?
I'd like to test some things... Maybe you can send me the db to my private
mail address?

Andi

On Friday 11 July 2008 15:19:56 Markus Spring wrote:

> Arnd Baecker schrieb:
> > No real idea. What happens if you do
> >   cp digikam3.db /tmp/
> >   cd /tmp
> >   sqlite3 digikam3.db
> >
> >
> > SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
> > ImageProperties on Images.id = ImageProperties.imageid WHERE
> > ((Images.name LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
>
> sqlite> SELECT Albums.url||'/'||Images.name FROM Albums,Images  LEFT JOIN
> ImageProperties on Images.id = ImageProperties.imageid WHERE ((Images.name
> LIKE '%1372%')) AND (Albums.id=Images.dirid) LIMIT 500;
> SQL error: database disk image is malformed
>
> Arnd, I copied your query into the sqlite3 command line and there again I
> get this error 'database disk image is malformed'
>
> No idea where this comes from.
>
> Markus
>
> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users