Piwigo.org

vincelight · 2022-01-14 21:27:35

Hi,
I upgrade when I get the email to upgrade so always run the latest software.
I'm getting an error message when I view 3 of my albums (I have dozens of albums). Each album has a similiar error message (only difference is the category_id ):

Warning: [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 7

SELECT DISTINCT(image_id)
FROM photos_image_category
INNER JOIN photos_images ON id = image_id
WHERE
category_id = 82

ORDER BY rank ASC
; in /home/vincelight/vincelight.com/photos/include/dblayer/functions_mysqli.inc.php on line 847

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in /home/vincelight/vincelight.com/photos/include/dblayer/functions_mysqli.inc.php:908 Stack trace: #0 /home/vincelight/vincelight.com/photos/include/section_init.inc.php(317): query2array('\nSELECT DISTINC...', NULL, 'image_id') #1 /home/vincelight/vincelight.com/photos/index.php(12): include('/home/vinceligh...') #2 {main} thrown in /home/vincelight/vincelight.com/photos/include/dblayer/functions_mysqli.inc.php on line 908

Piwigo 12.2.0 Check for upgrade
Operating system: Linux
PHP: 7.4.15 (Show info) [2022-01-14 12:06:43]
MySQL: 8.0.26-0ubuntu0.20.04.2 [2022-01-14 12:06:43]
Graphics Library: ImageMagick 6.9.7-4
Cache size N/A   never calculated Refresh

Piwigo URL: http://vincelight.com/photos
error can be found if clicking one of the albums
2019 Mom Surprise Bday
0 Hattiesburg 131 40th
2017 VT_Dropoff
My album is hosted by DreamHost

erAck · 2022-01-14 23:15:09

Can you try if applying these patches helps?

Code:

--- admin/configuration.php
+++ admin/configuration.php
@@ -178,7 +178,7 @@ if (isset($_POST['submit']))
             $order_by = $order_by_inside_category = array_slice($_POST['order_by'], 0, ceil(count($sort_fields)/2));
 
             // there is no rank outside categories
-            if ( ($i = array_search('rank ASC', $order_by)) !== false)
+            if ( (($i = array_search('`rank` ASC', $order_by)) !== false) || (($i = array_search('rank ASC', $order_by)) !== false) )
             {
               unset($order_by[$i]);
             }
--- admin/element_set_ranks.php
+++ admin/element_set_ranks.php
@@ -198,7 +198,7 @@ $sort_fields = array(
   'hit ASC'             => l10n('Visits, low &rarr; high'),
   'id ASC'              => l10n('Numeric identifier, 1 &rarr; 9'),
   'id DESC'             => l10n('Numeric identifier, 9 &rarr; 1'),
-  'rank ASC'            => l10n('Manual sort order'),
+  '`rank` ASC'          => l10n('Manual sort order'),
   );
 
 $template->assign('image_order_options', $sort_fields);

Update 2022-01-15T11:01+01:00
Corrected change in admin/configuration.php

vincelight · 2022-01-15 01:41:12

Thanks for your help because there is no way I could fix this by myself!
I do not know how to apply those changes you suggested. I've managed to download the files, make copies, open the original with notepad, however I can not locate the lines you want changed. I either don't know what I'm doing (high possibility) or my files do not match your files. For example, in element_set_ranks.php my file has
starting at line 272
// image order management
$sort_fields = array(
'' => '',
'date_creation' => l10n('Creation date'),
'date_available' => l10n('Post date'),
'average_rate' => l10n('Average rate'),
'hit' => l10n('Most visited'),
'file' => l10n('File name'),
'id' => 'Id',
'rank' => l10n('Rank'),
);
and that's the text which appears to be the closest match to yours. And, unfortunately, there is no version number or identifier inside the file which could prove we're looking at the same file. :-(

erAck · 2022-01-15 11:21:07

You are not looking at file admin/element_set_ranks.php of Piwigo 12.2.0 that has only 228 lines, see also
https://github.com/Piwigo/Piwigo/blob/7 … s.php#L184

The code context in the patch starts at line 198, the line to change is 201.

Note that I updated the change of admin/configuration.php

vincelight · 2022-01-15 18:00:19

So somehow my installation has gotten out of sync with the distribution even though I've followed along with the updates. :-( If this one file is out of sync, I have to wonder if other files are also out of sync :-(

Is there an easy way to force a resync?
Or do I need somehow down load the entire distribution and re-install it?

Not being very mysql savy i'm concerned and worried about losing things.
Thanks for your advice....

vincelight · 2022-01-15 18:21:23

I've started a manual update by down loading the 12.2.0 release, unpack locally on my PC...and making the changes before uploading them to my site.

Taking a closer look at the suggested changes to configuration.php
- if ( ($i = array_search('rank ASC', $order_by)) !== false)
+ if ( (($i = array_search('`rank` ASC', $order_by)) !== false) || (($

and it doesn't look right..? is there a missing ")" also the end of the line looks hanging "|| (($"

I'm thinking something got truncated.

erAck · 2022-01-15 20:58:43

No no no, that's wrong. From the original source you have to remove the line that starts with - minus and add the line that starts with + plus, without those minus and plus characters of course. (and note the line is longer, the code window is scrollable, so you'd have to copy the entire line).

However, if your sources are out of sync then rather update with the new original and try that first without modifications. Please follow the Manual Update guide.

vincelight · 2022-01-15 22:04:28

Thanks for the pointer, I didn't realize the adjustments scrolled to the right. silly me.

Just finished manually uploading the entire distribution, with mods to those two files.
Good news is my website still works.
Bad news is the problem still exists for those 3 albums.

albums with the problem: (in vincelight.com/photos)
2019 Mom Surprise Bday
0 Hattiesburg 131 40th
2017 VT_Dropoff

is it a coincidence those 3 albums are next to each other? all my other albums are fine.

vincelight · 2022-01-17 13:44:26

By changing the sort order from Manual to Automatic, the problem went away.
This used to work, but recently broke.

So there must be some bug in the code that sorts manually.

Last edited by vincelight (2022-01-17 13:45:07)

hodgman · 2022-02-09 10:11:51

I tracked down the cause of this error and a developed a patch.

I saw this error on update to Piwigo 12.2.0. It appears because my web hosting service provides MySQL 8 and RANK is now a reserved word as of MySQL 8. The offending query orders by column rank in table image_category. It looks like rank is used to specify manual sort order. However, when a column has the same name as a reserved word, when it is referred to in a query, it needs to be surrounded by backticks. Therefore, "ORDER BY rank ASC" produces an error; "ORDER BY `rank` ASC" does not.

The query is generated in file piwigo/include/section-init.inc.php between lines 309-315 and the order by phrase is specified in $conf['order_by']. My patch cobbles a fix for this error only. It consists of lines of code placed after line 315 and another set placed before line 309.

Before 309:
// 20220208 Patch for MySQL incompatibility
$hold_order_by_patch_20220208 = $conf['order_by'];
// https://www.php.net/manual/en/function.str-contains.php
// based on original work from the PHP Laravel framework
if (!function_exists('str_contains')) {
function str_contains($haystack, $needle) {
return $needle !== '' && mb_strpos($haystack, $needle) !== false;
}
}
if ( !str_contains( $conf['order_by'], '`' ) )
{
$conf['order_by'] = str_replace(
'ORDER BY rank',
'ORDER BY `rank`',
$conf['order_by']
);
}

After line 315:
// 20220208 Patch for MySQL incompatibility
$conf['order_by'] = $hold_order_by_patch_20220208;

Once I added these lines, the error was no longer seen.

vincelight · 2022-02-09 14:44:20

That's great you figured it out and fixed it! Thanks!

erAck · 2022-02-09 16:47:33

I don't get where that rank ASC should originate if not at the place I indicated in my patch earlier, that was said to not help (but then again by someone who's system was completely fouled up anyway and not too experienced applying patches), but I also didn't dig deeper into this.

So if someone else could try and verify the fix or failure of the patch ...

hodgman · 2022-02-10 00:20:25

My patch after piwigo/include/section_init.inc.php adds backticks to ORDER BY rank ASC in $conf['order_by'] after line 309 to fix the resultant query using $conf['order_by'].

$conf['order_by'] is set from a query on table piwigo_categories.image_order = NULL or = 'rank ASC' for the particular category to be shown. I am searching for that query. Directly after that query would be a better place to locate the patch.

File piwigo/include/section_init.inc.php starts with global $conf['order_by'] already set.

File piwigo/include/section_init.inc.php is included in index.php line 12, immediately after file piwigo/include/common.inc.php, which is included in index.php line 11. Array $conf is declared in piwigo/include/common.inc.php at line 48. The query that sets $conf['order_by'] must therefore be executed in the flow in piwigo/include/common.inc.php after line 48.

Element $conf['order_by'] is altogether used 48 times in directory piwigo.
Constant CATEGORIES_TABLE is altogether used 215 times in directory piwigo.
I am searching for the query that determines $conf['order_by'] against table CATEGORIES_TABLE starting with those hits.

When the query is found, its location in the execution sequence should explain why the previous two patches did not add ticks to rank for the query after piwigo/include/section_init.inc.php line 309.

erAck · 2022-02-10 02:55:35

Thanks for shedding some light, appreciated.

hodgman · 2022-02-12 00:08:32

The two patches posted by erAck fix the PHP / MySQL error when choosing Manual Sort Order after a Piwigo 12.2.0 upgrade. The PHP/MySQL error occurs when Manual Sort Order was chosen before the patch or under a previous version of Piwigo. This can be fixed after the two patches are applied, album by album, by choosing Manual Sort Order again for each album showing the error.

The patch I posted earlier will avoid the PHP/MySQL error for albums already configured for manual sort order before the two patches or before Piwigo 12.2.0. I traced the problem creating the PHP/MySQL error to the place the misconfigured parameter is selected from the Piwigo categories table. The following patch fixes the problem at that point:

File include/functions_category.inc.php insert at line 202:

// 20220110 with MySQL 8 'rank' is a reserved word so must be surrounded by backticks for use in a query
if ( is_string( $cat['image_order'] ) &&
FALSE === mb_strpos( $cat['image_order'] , '`rank`') )
$cat['image_order'] = str_replace(
'rank',
'`rank`',
$cat['image_order']
);

The patch I showed earlier is not needed when this new patch is used.

Piwigo.org

Announcement

#1 2022-01-14 21:27:35

Error in functions_mysqli.inc.php on line 847 ?

#2 2022-01-14 23:15:09

Re: Error in functions_mysqli.inc.php on line 847 ?

Code:

#3 2022-01-15 01:41:12

Re: Error in functions_mysqli.inc.php on line 847 ?

#4 2022-01-15 11:21:07

Re: Error in functions_mysqli.inc.php on line 847 ?

#5 2022-01-15 18:00:19

Re: Error in functions_mysqli.inc.php on line 847 ?

#6 2022-01-15 18:21:23

Re: Error in functions_mysqli.inc.php on line 847 ?

#7 2022-01-15 20:58:43

Re: Error in functions_mysqli.inc.php on line 847 ?

#8 2022-01-15 22:04:28

Re: Error in functions_mysqli.inc.php on line 847 ?

#9 2022-01-17 13:44:26

Re: Error in functions_mysqli.inc.php on line 847 ?

#10 2022-02-09 10:11:51

Re: Error in functions_mysqli.inc.php on line 847 ?

#11 2022-02-09 14:44:20

Re: Error in functions_mysqli.inc.php on line 847 ?

#12 2022-02-09 16:47:33

Re: Error in functions_mysqli.inc.php on line 847 ?

#13 2022-02-10 00:20:25

Re: Error in functions_mysqli.inc.php on line 847 ?

#14 2022-02-10 02:55:35

Re: Error in functions_mysqli.inc.php on line 847 ?

#15 2022-02-12 00:08:32

Re: Error in functions_mysqli.inc.php on line 847 ?

Board footer