How do I fix this “ALTER TABLE smf_smileys ORDER BY LENGTH” error I am getting?

While upgrading and possibly editing smileys in the Smileys section of your Administration Panel, you may get an error similar to this:

Quote

This query:
ALTER TABLE smf_smileys
ORDER BY LENGTH(code) DESC;
Caused the error:
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 ‘LENGTH(code) DESC’ at line 2
There are two solutions to resolve this error.

The first solution will fix both the issue with upgrading, as well as an issue with editing smileys in the Smileys and Message Icons section of your admin panel.
All you need to do is update your MySQL version. To update MySQL yourself, you will have to have server access. Otherwise, and more commonly the case, you will have to ask your host to upgrade MySQL on the server for you. Some older versions of MySQL seem to have this issue. At the moment the MySQL versions known with this are:

  • 5.0.37
  • 5.0.41
  • 5.0.42
  • 5.1.17(Unconfirmed)

There may be other versions of MySQL that have this issue as well, but upgrading MySQL to the latest version for the MySQL branch you are using may resolve the issue.

The second way to resolve this for upgrading is to open “upgrade_1-1.sql” with a text editor and find the following:

Code:

ALTER TABLE {$db_prefix}smileys
ORDER BY LENGTH(code) DESC;

Replacing this with:

Code:

ALTER TABLE {$db_prefix}smileys
ORDER BY code DESC;

For editing smileys in the Smileys and Message Icons section of your admin panel, open “/Sources/ManageSmileys.php” with a text editor and find the following code:

Code:

			// Sort all smiley codes for more accurate parsing (longest code first).
			db_query("
				ALTER TABLE {$db_prefix}smileys
				ORDER BY LENGTH(code) DESC", __FILE__, __LINE__);

Replacing this with:

Code:

			// Sort all smiley codes for more accurate parsing (longest code first).
			db_query("
				ALTER TABLE {$db_prefix}smileys
				ORDER BY code DESC", __FILE__, __LINE__);

This section of code will appear twice in “/Sources/ManageSmileys.php“, You will find this approximatively on lines 654 and 990.

Source