[Bug 271924] New: digikam fail to create/update mysql database on startup [PATCH]

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924

           Summary: digikam fail to create/update mysql database on
                    startup [PATCH]
           Product: digikam
           Version: 2.0.0
          Platform: Compiled Sources
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Database
        AssignedTo: [hidden email]
        ReportedBy: [hidden email]


Created an attachment (id=59392)
 --> (http://bugs.kde.org/attachment.cgi?id=59392)
easy-digikam-dbupdate.patch resilient db update

Version:           2.0.0 (using KDE 4.6.2)
OS:                Linux

since some time digikam tell me "Failed to create tables in database."
then it start with empty albums. Going into setup and telling it to add a new
root album make it work but with outdated database schema.

The database is stored into a system mysql install, with user "digikam" which
have full privilege on database "digikam".

the problem possibly is generated initially by a broken update, not sure of it.

The query are not executed because elements of the update are already in place,
making the transaction fail altoghether.

my solution is to make update more resilient (even if redundant) to already in
place tables/indexes/triggers.

attached patch for dbconfig.xml.cmake does:
- add a new stored procedure `create_index_if_not_exists` which is able to
create conditionally indexes on mysql 5.1+ (unsure about 5.0)
- move all "CREATE TABLE" to "CREATE TABLE IF NOT EXISTS"
- drop triggers and recreate them


Reproducible: Always

Steps to Reproduce:
start digikam

Actual Results:  
no more photo

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=271924


Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email],
                   |                            |[hidden email]




--- Comment #1 from Gilles Caulier <caulier gilles gmail com>  2011-04-28 16:34:58 ---
Francesco,

Thanks for your patch. It very appreciate...

Holger,

Can you review this patch. It touch DB XML config file...

Thanks in advance

Gilles Caulier

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924


Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #59392|0                           |1
        is obsolete|                            |




--- Comment #2 from Francesco Riosa <francesco pnpitalia it>  2011-04-29 11:12:03 ---
Created an attachment (id=59413)
 --> (http://bugs.kde.org/attachment.cgi?id=59413)
/home/vivo/tmp/digikam-easy-dbupdate.patch

now I've applied it directly to dbconfig.xml.cmake from core with:
patch --verbose -p1 < digikam-easy-dbupdate.patch

emptied digikam-sc/build and recompiled, it start ok but the database is
already updated, so no way to really test it.

initial debug log is:
digikam(11548)/digikam (core) Digikam::AlbumManager::setDatabase:
DatabaseParameters: [ Type "QMYSQL", Name "digikam" (Thumbnails Name
"digikam"); Host Name and Port: "localhost" 0; Username and Password:
"digikam", "digikam"]
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in
use, all queries will cease to work.
digikam(11548)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig:
Loading SQL code from config file
"/home/vivo/usr/share/apps/digikam/database/dbconfig.xml"
digikam(11548)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig:
false "1" 1 1
digikam(11548)/digikam (core) Digikam::SchemaUpdater::update: SchemaUpdater
update
digikam(11548)/digikam (core) Digikam::SchemaUpdater::startUpdates: No database
file available
digikam(11548)/digikam (core) Digikam::AlbumRootLocation::AlbumRootLocation:
Creating new Location  "/vivo/docs/NIKON"  uuid
"volumeid:?uuid=800b21c2-dadc-4930-829e-a96b04ce26fa"
digikam(11548)/digikam (core) Digikam::AlbumRootLocation::AlbumRootLocation:
Creating new Location  "/vivo/Immagini"  uuid
"volumeid:?uuid=800b21c2-dadc-4930-829e-a96b04ce26fa"
digikam(11548)/digikam (core) Digikam::CollectionManager::updateLocations:
location for  "/home/vivo/docs/NIKON"  is available  true
digikam(11548)/digikam (core) Digikam::CollectionManager::updateLocations:
location for  "/home/vivo/Immagini"  is available  true
digikam(11548)/digikam (core) Digikam::LoadingCache::setCacheSize: Allowing a
cache size of 200 MB
digikam(11548)/digikam (core)
Digikam::ThumbnailLoadThread::initializeThumbnailDatabase: Thumbnail db ready
for use
digikam(11548)/digikam (core) Digikam::AlbumManager::setDatabase: nepomuk
service available true
digikam(11548)/KEXIV2 KExiv2Iface::KExiv2::sidecarFilePathForFile: File path:
"/home/vivo/docs/NIKON/BLACK/200i10s.nef"  =>  XMP sidecar path:
"/home/vivo/docs/NIKON/BLACK/200i10s.nef.xmp"
digikam(11548)/digikam (core) Digikam::DImg::load:
"/home/vivo/docs/NIKON/BLACK/200i10s.nef"  : RAW file identified
digikam(11548)/digikam (core) Digikam::ImageScanner::addImage: Adding new item
"/home/vivo/docs/NIKON/BLACK/200i10s.nef"
[...]

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #3 from Gilles Caulier <caulier gilles gmail com>  2011-04-29 11:54:13 ---
Francesco,

What about these entries ? That can be fixed with your patch ?

https://bugs.kde.org/show_bug.cgi?id=258409
https://bugs.kde.org/show_bug.cgi?id=257183
https://bugs.kde.org/show_bug.cgi?id=233763
https://bugs.kde.org/show_bug.cgi?id=267733
https://bugs.kde.org/show_bug.cgi?id=262231
https://bugs.kde.org/show_bug.cgi?id=233770

Gilles Caulier

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #4 from Francesco Riosa <francesco pnpitalia it>  2011-04-29 13:34:30 ---
(In reply to comment #3)
> Francesco,
>
> What about these entries ? That can be fixed with your patch ?
>
> https://bugs.kde.org/show_bug.cgi?id=258409
not this one, seem related to this error message:
QSqlQuery::seek: cannot seek backwards in a forward only query
Dunno what exactly can be, maybe a server side cursor where a client side one
should have been used


> https://bugs.kde.org/show_bug.cgi?id=257183
yes if the triggers for the relevant table are wrong and not deleted and
re-created then this bug could be fixed

> https://bugs.kde.org/show_bug.cgi?id=233763
IMHO when when one of ip,server,port change in the dialog the database should
be checked (including DBVersion in digikam.settings). If not existant it should
be created, or if current db is valid a warning telling to use the database
migration tool.


> https://bugs.kde.org/show_bug.cgi?id=267733
"The Internal MySQL box in option menu is blank. "
In my case it was populated with correct values both in 1.9.0 and 2.0
would tell the user to re-test and close if not confirmed

> https://bugs.kde.org/show_bug.cgi?id=262231
mmh, may be best to disable triggers during mass migration, then do a cleanup
following the same rules i.e.
delete a.* from a LEFT JOIN b on a.id = b.id where ISNULL(b.id)
then reenable them
mysqldump tool may be inspirational here

> https://bugs.kde.org/show_bug.cgi?id=233770
yes should be fixed

P.S. I do grok enough of mysql sql language and behaviour, and very pleased to
use digikam, feel free to contact me whenever you want also by private email

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #5 from Francesco Riosa <francesco pnpitalia it>  2011-04-29 13:36:39 ---
> https://bugs.kde.org/show_bug.cgi?id=262231
also both database should be write-locked

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #6 from Gilles Caulier <caulier gilles gmail com>  2011-05-02 14:37:21 ---
Francesco,

We will be very happy if you can help use to hack as developer MySQL database
support in digiKam. Holger who have implemented this is not available, and
Marcel and me we are very busy (GSOC 2011 just started)...

Are you developer ?

Gilles Caulier

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #7 from Francesco Riosa <francesco pnpitalia it>  2011-05-02 15:10:48 ---
My skills are very oriented at database management and interpreted languages, I
do kinda suck at c++ but can read it somewhat.
Other than this to give some kind of help you should tell me what to fix, and
where to search in the code to be able to do some useful work i.e. this may
need some input and time from your side. Plus my english suck ;)

If these characteristics meet your needs you have my email, I'm on pc from 10
a.m. to 8 p.m most working days and some weekend at your disposal

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #8 from Quallenauge <Hamsi2k freenet de>  2011-05-02 21:37:01 ---
Hi Francesco,

as Guilles wrotes, I'm very busy with other things than coding for
digikam :-(
If you want to go into the code and you do need some help or some
explanations
about the mysql database part (also the xml based database configuration
script),
feel free to send me an eMail. I will try to give (soon ;) ) some answers ;)

--
Holger

Am 02.05.2011 15:10, schrieb Francesco Riosa:

> https://bugs.kde.org/show_bug.cgi?id=271924
>
>
>
>
>
> --- Comment #7 from Francesco Riosa<francesco pnpitalia it>   2011-05-02 15:10:48 ---
> My skills are very oriented at database management and interpreted languages, I
> do kinda suck at c++ but can read it somewhat.
> Other than this to give some kind of help you should tell me what to fix, and
> where to search in the code to be able to do some useful work i.e. this may
> need some input and time from your side. Plus my english suck ;)
>
> If these characteristics meet your needs you have my email, I'm on pc from 10
> a.m. to 8 p.m most working days and some weekend at your disposal
>

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924


Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #59413|0                           |1
        is obsolete|                            |




--- Comment #9 from Francesco Riosa <francesco pnpitalia it>  2011-05-06 00:55:57 ---
Created an attachment (id=59678)
 --> (http://bugs.kde.org/attachment.cgi?id=59678)
digikam-easy-dbupdate.patch

working on bug #258409 I've noticed a bug in stored procedure
create_index_if_not_exists (my patch).

dunno where the collation of the connection is set to:
SET collation_connection = 'utf8_general_ci'
But default for database is 'utf8_unicode_ci', so when migrating it result in
the error:
#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and
(utf8_unicode_ci,IMPLICIT) for operation '='

the procedure should set explicitly collations where needed, empiric results
show it's needed only in the first query of the S.P.

CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50),
index_name_vc varchar(50), field_list_vc varchar(1024))
SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
    SELECT COUNT(1) cnt
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_name = table_name_vc COLLATE utf8_general_ci
      AND index_name = index_name_vc COLLATE utf8_general_ci
);

IF IFNULL(@Index_cnt, 0) = 0 THEN
    set @index_sql = concat('ALTER TABLE ',table_name_vc,' ADD INDEX
',index_name_vc,'(',field_list_vc,');');
    PREPARE stmt FROM @index_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;
END;

the new stored procedure has been tested, the patch instead is manually
modified and never applied

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924





--- Comment #10 from Francesco Riosa <francesco pnpitalia it>  2011-05-09 01:28:18 ---
-- Humans should have only one language and one alphabet really --
-- to set collation is not enough, for various reason strings may be of another
charachter set (probably latin1)


DROP PROCEDURE `create_index_if_not_exists`;;

CREATE DEFINER=`digikam`@`%` PROCEDURE
`create_index_if_not_exists`(table_name_vc varchar(50), index_name_vc
varchar(50), field_list_vc varchar(1024))
    SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
    SELECT COUNT(1) cnt
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING
latin1)
      AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING
latin1)
);

IF IFNULL(@Index_cnt, 0) = 0 THEN
    set @index_sql = concat(
      CONVERT( 'ALTER TABLE ' USING latin1),
      CONVERT( table_name_vc USING latin1),
      CONVERT( ' ADD INDEX ' USING latin1),
      CONVERT( index_name_vc USING latin1),
      CONVERT( '(' USING latin1),
      CONVERT( field_list_vc USING latin1),
      CONVERT( ');' USING latin1)
    );
    PREPARE stmt FROM @index_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;
END ;;

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=271924


Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|UNCONFIRMED                 |RESOLVED
         Resolution|                            |FIXED




--- Comment #11 from Francesco Riosa <francesco pnpitalia it>  2011-05-09 16:58:07 ---
@bug_id = 271924
@bug_status = RESOLVED
@resolution = FIXED

Git commit ec7ceeea0d2587abb9f1bdd17109d4903998e943 by Francesco Riosa.
Committed on 09/05/2011 at 14:20.
Pushed by riosa into branch 'master'.

more resilient database upgrade and startup

All statements are made resilient to a partially upgraded database.
- CREATE TABLE are done with IF NOT EXISTS
- TRIGGERs are dropped and re-created
- a new stored procedure is added "create_index_if_not_exists"
  the procedure check in information_schema if the index exists,
  if not does create it
- all INDEXes are created using "create_index_if_not_exists"

BUG: 271924
BUG: 258409
BUG: 257183
BUG: 267733
BUG: 262231
BUG: 233770

M  +90   -56   data/database/dbconfig.xml.cmake    

http://commits.kde.org/digikam/ec7ceeea0d2587abb9f1bdd17109d4903998e943

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=271924

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
   Version Fixed In|                            |2.0.0

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=271924

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Mysql

--
You are receiving this mail because:
You are the assignee for the bug.