| Summary: | MYSQL : when a host is set for an user in database, digiKam fail to update | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | e.longuemare |
| Component: | Database-Mysql | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | caulier.gilles |
| Priority: | NOR | ||
| Version: | 4.0.0 | ||
| Target Milestone: | --- | ||
| Platform: | Ubuntu | ||
| OS: | Linux | ||
| Latest Commit: | http://commits.kde.org/digikam/74adf4f5dcdacc4b6574e61f55dac956fa4c7611 | Version Fixed In: | 5.0.0 |
| Sentry Crash Report: | |||
Hello, looking at databases, I found that definer is well set in digikam db (good ip) for Views TagsTree but not for the routine create_index_if_not_exists in digikam thumb db (% all ips). Alter the definer of digikam thumb db create_index_if_not_exists with good ip change nothing. Eric Workaround : Close digikam. Go to mysql, delete all table and routine create_index_if_not_exists from digikam thumb db. Set the user new host permission in mysql. Relaunch digikam, tables are recreated and routine too. No more error message from digikam. PS : I have try to alter routine and change definer, it's apply, but digikam can't use routine. The above trick resolve this. PS2 : I use digikam in network environnement, one NAS for files, one PC for database server (mysql). I have set a mysql user by digikam workstation for digikam both databases (thumb and data) perhaps my _Digikam_root_tags and other problem were related to this. So it's solved. Git commit 74adf4f5dcdacc4b6574e61f55dac956fa4c7611 by Gilles Caulier. Committed on 19/11/2015 at 14:38. Pushed by cgilles into branch 'master'. First stage to fix Mysql support: - For Internal server: Use the current system user to run mysql_installdb program to init local database and server tables. The database is always installed in user account (~/.local/share/digikam/). The goal is to set this install path configurable as with SQlite. Check the value returned to start init and sever program with QProcess. If something is wrong abort the sequence and do not try to populate/use the database. This will fix a crash if DB backend is null at digiKam shutdown. Fix the default embeded mysql server configuration to not need grant privilege with index creation. - For remote server: Fix the index creation procedure SQL statements to not check security rules. No more grant privilege is required, excepted for the DB tables and the standard DB user dedicated for digiKam use. Important : for a remote server, the database creation need to be instanced by administrator, using following SQL statements (which will be put in DB setup page later as helper): * For a common Mysql database storage : CREATE DATABASE digikamdb; GRANT ALL PRIVILEGES ON digikamdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; with: digikamdb : the common database name (storing Core, Thumbnails, and Face databases). digikam/digikam : the name/password of user account used by digiKam to access on mysql server. * For separated Mysql database storages : CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON digikamthumbsdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; with: digikamcoredb : the Core database name. digikamthumbsdb : the Thumbnails database name. digikamfacedb : the Face database name. digikam/digikam : the name/password of user account used by digiKam to access on mysql server. - The Face recognition database is now fully integrated in Mysql support. A new settings have been add to DB setup page. Related: bug 316747, bug 311041, bug 279580, bug 283502 FIXED-IN: 5.0.0 M +29 -15 databaseserver/databaseserver.cpp M +4 -0 databaseserver/databaseserver.h http://commits.kde.org/digikam/74adf4f5dcdacc4b6574e61f55dac956fa4c7611 |
digikam(8807)/digikam (core) Digikam::NewItemsFinder::slotTotalFilesToScan: total scan value : 28165 digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::Private::printExiv2MessageHandler: Exiv2 ( 2 ) : Directory Minolta, entry 0x0088: Data area exceeds data buffer, ignoring it. digikam(8807)/digikam (core) Digikam::DImg::load: "/media/DiskphotosTests/archive materiel peche/aero technium/eclaté_galet-shimano_aero_technium_et_ultegra_modifiee.jpg" : JPEG file identified digikam(8807)/digikam (core) Digikam::ImageScanner::prepareAddImage: Adding new item "/media/DiskphotosTests/archive materiel peche/aero technium/eclaté_galet-shimano_aero_technium_et_ultegra_modifiee.jpg" digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::getImageDateTime: DateTime => Exif.Photo.DateTimeOriginal => QDateTime("jeu. juil. 16 13:25:39 2009") digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::getDigitizationDateTime: DateTime (Exif digitalized): jeu. juil. 16 13:25:39 2009 digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::getImageOrientation: Orientation => Exif.Image.Orientation => 1 digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::Private::printExiv2ExceptionError: Cannot find Xmp key 'Xmp.mwg-rs.Regions/mwg-rs:RegionList[1]/mwg-rs:Name' into image using Exiv2 (Error # 35 : No namespace info available for XMP prefix `mwg-rs' digikam(8807)/digikam (core) Digikam::ImageScanner::commit: Scanning took 213 ms digikam(8807)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "REPLACE INTO Images ( album, name, status, category, modificationDate, fileSize, uniqueHash ) VALUES (?,?,?,?,?,?,?);" Error messages: "QMYSQL3: Unable to execute statement" "The user specified as a definer ('sospcdk'@'%') does not exist" 1449 2 Bound values: (QVariant(int, 66) , QVariant(QString, "eclaté_galet-shimano_aero_technium_et_ultegra_modifiee.jpg") , QVariant(int, 1) , QVariant(int, 1) , QVariant(QString, "2009-08-08T22:52:04") , QVariant(qlonglong, 1215494) , QVariant(QString, "5a5d4cac3bc56330c42876f967ead5f5") ) digikam(8807)/digikam (core) Digikam::ImageScanner::~ImageScanner: Finishing took 37 ms [Thread 0x92efcb40 (LWP 8859) exited] digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::Private::printExiv2MessageHandler: Exiv2 ( 2 ) : Directory Minolta, entry 0x0088: Data area exceeds data buffer, ignoring it. digikam(8807)/digikam (core) Digikam::DImg::load: "/media/DiskphotosTests/vendee 2010/bretignolle juin 2010 immersion/lune_bretignolles.jpg" : JPEG file identified digikam(8807)/digikam (core) Digikam::ImageScanner::prepareAddImage: Adding new item "/media/DiskphotosTests/vendee 2010/bretignolle juin 2010 immersion/lune_bretignolles.jpg" digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::getImageDateTime: DateTime => Exif.Photo.DateTimeOriginal => QDateTime("lun. juin 21 23:55:11 2010") digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::getDigitizationDateTime: DateTime (Exif digitalized): lun. juin 21 23:55:11 2010 digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::getImageOrientation: Orientation => Exif.Image.Orientation => 1 digikam(8807)/digikam (core) Digikam::ImageScanner::scanTags: Pick Label found : 0 digikam(8807)/digikam (core) Digikam::ImageScanner::scanTags: Assigned Pick Label Tag : 12 digikam(8807)/digikam (core) Digikam::ImageScanner::scanTags: Color Label found : 0 digikam(8807)/digikam (core) Digikam::ImageScanner::scanTags: Assigned Color Label Tag : 2 digikam(8807)/KEXIV2 KExiv2Iface::KExiv2::Private::printExiv2ExceptionError: Cannot find Xmp key 'Xmp.mwg-rs.Regions/mwg-rs:RegionList[1]/mwg-rs:Name' into image using Exiv2 (Error # 35 : No namespace info available for XMP prefix `mwg-rs' digikam(8807)/digikam (core) Digikam::DMetadata::getImageHistory: Loading image history "<?xml version="1.0"?> <history version="1"> <file uuid="3b6a2a35ffeb7720318bf7910e7f2265da8767f8944cc68cde090b7fe3abddd3" type="original"> <fileParams fileName="lune_bretignolles.JPG" filePath="/media/Diskphotos/vendee 2010/bretignolle juin 2010 immersion/" fileHash="da8767f8944cc68cde090b7fe3abddd3" fileSize="2159724" creationDate="2010-06-21T23:55:11"/> </file> </history> " digikam(8807)/digikam (core) Digikam::ImageScanner::commit: Scanning took 77 ms digikam(8807)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "REPLACE INTO Images ( album, name, status, category, modificationDate, fileSize, uniqueHash ) VALUES (?,?,?,?,?,?,?);" Error messages: "QMYSQL3: Unable to execute statement" "The user specified as a definer ('sospcdk'@'%') does not exist" 1449 2 Bound values: (QVariant(int, 346) , QVariant(QString, "lune_bretignolles.jpg") , QVariant(int, 1) , QVariant(int, 1) , QVariant(QString, "2013-08-28T14:37:45") , QVariant(qlonglong, 1235703) , QVariant(QString, "43ded3850723a4d62f52315057f927ac") ) digikam(8807)/digikam (core) Digikam::ImageScanner::~ImageScanner: Finishing took 11 ms Reproducible: Always Steps to Reproduce: 1. Set a hosts for a user in mysql 2.start digikam 3.let collection scan go ahead Actual Results: See gdb trace : digikam(8807)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "REPLACE INTO Images ( album, name, status, category, modificationDate, fileSize, uniqueHash ) VALUES (?,?,?,?,?,?,?);" Error messages: "QMYSQL3: Unable to execute statement" "The user specified as a definer ('username'@'%') does not exist" 1449 2 Expected Results: Update is ok with a limited host IP for user in mysql (set to any is unsecure) I use external mysql server workaround : set digikam database use hosts authorization to any (unsecure) reproducible : always