ElkArte Community

Elk Development => Bug Reports => Topic started by: Ruth on April 16, 2023, 02:25:47 am

Title: Editing Smileys is causing errors
Post by: Ruth on April 16, 2023, 02:25:47 am
Hi!

Editing Smileys (by clicking on them) or changing their order (if drag&drop is used) is causing database errors and does not work. This is on test forum, with new installed ElkArte 1.1.9. No smiley addon there yet.

Error log says: "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 'row FROM elkarte_smileys"

The errors are in /sources/subs/Smileys.subs.php on line 200.
Title: Re: Editing Smileys is causing errors
Post by: Burke Knight on April 16, 2023, 11:53:29 am
1. Drag and drop works for me.
2. I've tested editing the smileys and had no issues, so I think this may be a server issue for you.

In closing: Unable to reproduce issue on a fresh clean install of 1.1.9.
Title: Re: Editing Smileys is causing errors
Post by: Ruth on April 16, 2023, 12:19:58 pm
I can edit smileys without any error, if i use the checkboxes and the scroll down menu.
I also can sort them without error, if i click on them, (just drag & drop is not working).

I think, it had been always like this on my forums....but not sure.
For the icons drag & drop is working fine.

Quoteso I think this may be a server issue for you

Yes....but I don't know, how to solve this....a php issue?
Title: Re: Editing Smileys is causing errors
Post by: Spuds on May 13, 2023, 05:04:14 pm
I suspect that is due to a new version of MySQL which added "row" as a reserved name.  Older versions of mysql will not have an issue, nor will any current version of mariadb.

The function getSmiley in Smileys.subs.php (around line 190) looks like this
Code: (find) [Select]
function getSmiley($id)
{
$db = database();

$request = $db->query('', '
SELECT id_smiley AS id, code, filename, description, hidden AS location, 0 AS is_new, smiley_row AS row
FROM {db_prefix}smileys
WHERE id_smiley = {int:current_smiley}',
array(
'current_smiley' => $id,
)
);
if ($db->num_rows($request) != 1)
throw new Elk_Exception('smiley_not_found');
$current_smiley = $db->fetch_assoc($request);
$db->free_result($request);

return $current_smiley;
}
We are going to drop that "AS row" part then add $current_smiley['row'] = $current_smiley['smiley_row']; just to keep it compatible with other existing functions.  When done it will look like this and should work with the latest MySQL
Code: (replace) [Select]
function getSmiley($id)
{
$db = database();

$request = $db->query('', '
SELECT id_smiley AS id, code, filename, description, hidden AS location, 0 AS is_new, smiley_row
FROM {db_prefix}smileys
WHERE id_smiley = {int:current_smiley}',
array(
'current_smiley' => $id,
)
);
if ($db->num_rows($request) != 1)
throw new Elk_Exception('smiley_not_found');
$current_smiley = $db->fetch_assoc($request);
$current_smiley['row'] = $current_smiley['smiley_row'];
$db->free_result($request);

return $current_smiley;
}
Title: Re: Editing Smileys is causing errors
Post by: Ruth on May 14, 2023, 12:38:31 am
It works great now, Spuds!  :smiley: No more errors in the log.

Thank you!
Title: Re: Editing Smileys is causing errors
Post by: Ruth on May 14, 2023, 11:08:55 am
It also works perfect together with the new version of the smiley addon.
I just tried sorting by drag & drop on elk11. Great. :smiley: