Announcement

#1 2019-05-26 18:43:51

John Shaft
Member
2019-05-26
3

MySQL error in metadata sync

Hi,

Haven't been posting photos in my gallery for a while (late 2017) but I kept it updated so I do not know what version introduced this problem.

While uploading new photos yesterday, I got this error during metadata sync :

Warning:  [mysql error 1054] Unknown column 'make' in 'field list'

INSERT  INTO piwi_images_1558887969724342
  (id,filesize,width,height,date_creation,make,model,lens,shutter_speed,iso,apperture,focal_length,latitude,longitude,author,name,comment,date_metadata_update)
  VALUES('1265','6093','4032','3024','2019-05-11 18:27:13','samsung','SM-G930F',NULL,'39981000/1000000000','1000','170/100','4200/1000',NULL,NULL,NULL,NULL,NULL,'2019-05-26 18:26:09')
  , ('1266','4674','3024','4032','2019-05-11 20:26:58','samsung','SM-G930F',NULL,'39981000/1000000000','400','170/100','4200/1000',NULL,NULL,NULL,NULL,NULL,'2019-05-26 18:26:09')
  , ('1267','3613','3024','4032','2019-05-12 11:47:23','samsung','SM-G930F',NULL,'39981000/1000000000','160','170/100','4200/1000',NULL,NULL,NULL,NULL,NULL,'2019-05-26 18:26:09')
  , ('1268','3044','3024','4032','2019-05-12 15:04:05','samsung','SM-G930F',NULL,'380000/1000000000','64','170/100','4200/1000',NULL,NULL,NULL,NULL,NULL,'2019-05-26 18:26:09')
  , ('1269','2955','4032','3024','2019-05-12 17 in /path/to/piwi/photos/include/dblayer/functions_mysqli.inc.php on line 845



Warning:  [mysql error 1054] Unknown column 't1.make' in 'field list'

UPDATE piwi_images AS t1, piwi_images_1558887969724342 AS t2
  SET t1.filesize = IFNULL(t2.filesize, t1.filesize)
    , t1.width = IFNULL(t2.width, t1.width)
    , t1.height = IFNULL(t2.height, t1.height)
    , t1.date_creation = IFNULL(t2.date_creation, t1.date_creation)
    , t1.make = IFNULL(t2.make, t1.make)
    , t1.model = IFNULL(t2.model, t1.model)
    , t1.lens = IFNULL(t2.lens, t1.lens)
    , t1.shutter_speed = IFNULL(t2.shutter_speed, t1.shutter_speed)
    , t1.iso = IFNULL(t2.iso, t1.iso)
    , t1.apperture = IFNULL(t2.apperture, t1.apperture)
    , t1.focal_length = IFNULL(t2.focal_length, t1.focal_length)
    , t1.latitude = IFNULL(t2.latitude, t1.latitude)
    , t1.longitude = IFNULL(t2.longitude, t1.longitude)
    , t1.author = IFNULL(t2.author, t1.author)
    , t1.name = IFNULL(t2.name, t1.name)
    , t1.comment = IFNULL(t2.comment, t1.comment)
    , t1.date_metadata_update = IFNULL(t2.date_metadata_update, t1.date_metadata_update)
  W in /path/to/piwi/photos/include/dblayer/functions_mysqli.inc.php on line 845

upgrade.php tells me everything is OK. So does the repair and optimize database function

Thanks for all the works by the way ;)

Piwigo 2.9.5
PHP: 7.3.4-2
MySQL: 5.5.5-10.3.14-MariaDB-1
Piwigo URL: http://www.shaftinc.fr/photos

Last edited by John Shaft (2019-05-27 11:01:33)

Offline

 

#2 2019-05-27 13:10:14

k5
Member
place
2017-11-05
13

Re: MySQL error in metadata sync

Hi,
Why "make" ?   why "t1" "t2" ?
where does it come from ?
do you have a list of plugins please ?

thanks
k5

Offline

 

#3 2019-05-27 15:18:04

erAck
Piwigo Team
2015-09-06
281

Re: MySQL error in metadata sync

Likely from include/dblayer/functions_mysql.inc.php line 322 with

Code:

    // update of images table by joining with temporary table
    $query = '
UPDATE '.$tablename.' AS t1, '.$temporary_tablename.' AS t2
  SET '.
      implode(
        "\n    , ",
        array_map($func_set,$dbfields['update'])
        ).'

whatever created the dbfields passed down there.. the original piwigo_images table (or piwi_images in this case) does not have a make column, nor model, lens, shutter_speed, iso, apperture or focal_length. If there is an extension that writes these fields it either forgot to modify the table to add the columns, or the table was re-created later with the original columns.


Running Piwigo at https://erack.net/gallery/

Offline

 

#4 2019-05-27 16:21:15

ddtddt
Piwigo Team
Quetigny - France
2007-07-27
6797

Re: MySQL error in metadata sync

Hi :-)

Local config ?


You love Piwigo so don't hesitate to participate, learn more on the "Contribute to Piwigo" page. If you don't have much time for contribution, you can also help the project with a donation.

Offline

 

#5 2019-05-27 17:45:17

John Shaft
Member
2019-05-26
3

Re: MySQL error in metadata sync

ddtddt wrote:

Hi :-)

Local config ?

Here is the custom part of it :

Code:

$conf['show_iptc'] = false;


$conf['show_exif'] = true;
$conf['show_exif_fields'] = array(
  'Artist',
  'Copyright',
  'Model',
  'UndefinedTag:0xA434',
  'DateTimeOriginal',
  'COMPUTED;ApertureFNumber',
  'ExposureTime',
  'ISOSpeedRatings',
  'FNumber'
  );


$conf['use_exif'] = true;
$conf['use_exif_mapping'] = array(
  'date_creation' => 'DateTimeOriginal',
  'make'          => 'Make',
  'model'         => 'Model',
  'lens'          => 'UndefinedTag:0xA434',
  'shutter_speed' => 'ExposureTime',
  'iso'           => 'ISOSpeedRatings',
  'apperture'     => 'FNumber',
  'focal_length'  => 'FocalLength',
);

And indeed, if I remove it, it works. :)

This custom conf is quite recent (made it a year ago or so) and it worked when I re-synced all my photos at that time

Last edited by John Shaft (2019-05-27 18:53:18)

Offline

 

#6 2019-05-27 20:25:42

erAck
Piwigo Team
2015-09-06
281

Re: MySQL error in metadata sync

What made you do that? I mean, something must had told you to add these mappings, only the 'date_creation' => 'DateTimeOriginal' mapping is in include/config_default.inc.php. Note that its comment says

// use_exif_mapping: same behaviour as use_iptc_mapping

for which use_iptc_mapping says

// use_iptc_mapping : in which IPTC fields will Piwigo find image
// information ? This setting is used during metadata synchronisation. It
// associates a piwigo_images column name to a IPTC key

so the behavior it exposed wanting to update such column's field seems quite logical.


Running Piwigo at https://erack.net/gallery/

Offline

 

#7 2019-05-27 23:39:35

John Shaft
Member
2019-05-26
3

Re: MySQL error in metadata sync

I made those change when switching to the Bootstrap Darkroom theme last year.

I simply used Piwigo's wiki and found the mapping I wanted in Bootstrap Darkroom's wiki

It worked alright in Piwigo 2.9.3 (but may be it was the real bug :) )

IPTC headers don't contain the data I need (according to what tools/metadata.php tells me), which is why I don't use it

Offline

 

#8 2019-05-28 14:13:52

erAck
Piwigo Team
2015-09-06
281

Re: MySQL error in metadata sync

That Bootstrap Darkroom wiki says something different though, it's $config['bootstrap_darkroom_ps_exif_mapping'] (note there's the closing ] missing in the wiki), not $conf['use_exif_mapping'].


Running Piwigo at https://erack.net/gallery/

Offline

 

Board footer

Powered by FluxBB

github twitter facebook google+ newsletter Donate Piwigo.org © 2002-2019 · Contact