Obviously in table `piwigo_image_category` there is already an entry where `image_id` = 2 and `category_id` = 1115, which apparently was to be added for the IMG_2650.JPG of table `piwigo_images` with `id` = 2 and `storage_category_id` = 1115. However those image id and category id pair duplicates were calculated first hand (the following images entries also seem to be duplicates). The piwigo_images table, where the next id value is calculated from, may had been cleared without clearing also piwigo_image_category. Probably best to check database integrity, [extension by rvelices] RV DB Integrity might help to detect odds.

In site_update.php there is:

    $insert = array(
      'id'             => $next_element_id++,
      'file'           => $filename,
      'name'           => get_name_from_file($filename),
      'date_available' => CURRENT_DATE,
      'path'           => str_replace("\'", "\'\'", $fulldir),
      'representative_ext'  => $fs[$path]['representative_ext'],
      'storage_category_id' => $db_fulldirs[$dirname],
      'added_by'       => $user['id'],
      );


By adding the str_replace call to '$fulldir' the query succeeds. Ta da!

memecode wrote:

In further investigations to this issue, it seems that piwigo is completely unprepared for the amount of data I'm throwing at it:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4929792 bytes) in /var/www/html/piwigo/include/functions_html.inc.php on line 380

And also it's hitting the default 30 second script timeout too. This in trying to sync a large folder of photos and videos. I've increase both the memory limit and the script timeout to try and work around it, but it seems to me that some sort of chunking of the sync algorithm is needed in the long term.

Have the sync script run for 20seconds and then update the user's display with a ajax message or something. Then run the script again with the next batch of data... and keep the numbers of photos processed at once to a reasonable size... rather than just run out of memory.

Honestly I though this software was going to be a bit better than this.

Back to the SQL error itself. Seems it's not properly handling quotes. e.g.:

  , ('24498','104-0437_IMG.JPG','104-0437 IMG','2020-11-13 09:15:46','./galleries/Photos/Image Library One/Mags' piccies/Album/104-0437_IMG.JPG',NULL,'20470','1')
  , ('24499','104-0438_IMG.JPG','104-0438 IMG','2020-11-13 09:15:46','./galleries/Photos/Image Library One/Mags' piccies/Album/104-0438_IMG.JPG',NULL,'20470','1')

When the folder path has a single quote in the name, it needs to escape that correctly. Rookie error. *shakes head slowly*

Faced same problems.
I've solved issues related to memory exhaust and long running operations by adjusting following parameters in php.ini:
max_execution_time = 1200 (10minutes)
max_input_time = 1200 (same)
memory_limit = 2048M (it is good idea to turn swap on if you have a virtual server with ssd and limited ram)

Found VERY strange thing. During synchronization, if a filename contains a 'rank' substring (i.e battleship-rank-1.jpg)  then filename, picture name and picture path will be written in database if following manner: battleship-`rank`-1. This leads to checksum misses, and various upleasant things.

Can someone point where to dig for solving this issue?

memecode wrote:

When the folder path has a single quote in the name, it needs to escape that correctly.

Which running into should be possible only if the apostrophe was explicitly added to $conf['sync_chars_regex'].

'path'           => str_replace("\'", "\'\'", $fulldir),

Though storing that form may lead to trouble later if the value is read from the database and used as is.

Aest wrote:

if a filename contains a 'rank' substring (i.e battleship-rank-1.jpg)  then filename, picture name and picture path will be written in database if following manner: battleship-`rank`-1

Not any substring 'rank', but a word 'rank', due to include/dblayer/functions_mysqli.inc.php function pwg_query() line 132.

Please submit bug reports at https://github.com/Piwigo/Piwigo/issues, or even better pull requests with proper fixes..

I've read some discussions at piwigo's gihtub and other places, so have a couple ideas about that 'rank' issue.
As mentioned by @memecode, exactly separate word "rank", not any substring leads to probems.
That happens because rank is reserved keyword in MySQL.
BUT, i suppose it have meaning to field names of a table, or to parts of SQL clauses, but not to value of a field.
So, we need to add some distinction between "rank" used in a sql syntax and "rank" used as a value of a field.

UPD. made a quick and dirty workaround:

  if (!preg_match('/(?<=\s|^|\W)values(?=\s|$|\W).*(?=\b)rank(?=\b)/i', $query))
  {
    // starting with MySQL 8, rank becomes a reserved keyword, we need to escape it
    if (preg_match('/\brank\b/', $query))
    {
      // first we unescape what's already escaped (to avoid double escaping)
      $query = preg_replace('/`rank`/', 'rank', $query);
      // then we escape the keyword
      $query = preg_replace('/\brank\b/', '`rank`', $query);
    }
  }

  if (!preg_match('/(?<=\s|^|\W)values(?=\s|$|\W).*(?=\b)groups(?=\b)/i', $query))
  {
    if (preg_match('/\bgroups\b/', $query))
    {
      // first we unescape what's already escaped (to avoid double escaping)
      $query = preg_replace('/`groups`/', 'groups', $query);
      // then we escape the keyword
      $query = preg_replace('/\bgroups\b/', '`groups`', $query);
    }
  }

Opinions?

Last edited by Aest (2020-11-13 13:39:06)

Don't forget to remove the database as well!