MySQL 5.0.37 syntax error upgrading to 1.1.2

[bweisz]

SMF Version: SMF 1.1.2
Attempting to upgrade to 1.1.2 from 1.1.1 using upgrade.php.  I get this error during the upgrade:

Upgrading...
Updating and creating indexes... Unsuccessful!
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

Running this query from mysql client results in the same error:
mysql> ALTER TABLE smf_smileys ORDER BY LENGTH(code) DESC;
ERROR 1064 (42000): 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 1
mysql>


MySQL is 5.0.37

I wonder if hxxp:bugs.mysql.com/bug.php?id=24562 [nonactive] is related?

Any help appreciated.

Note:  I "thought" I had completed this upgrade weeks ago with the package manager.  When a user brought another issue to my attention (with Source/ManageSmileys.php) I found that the upgrade likely didn't complete as I expected, the smf_settings.smfVersion still shows as 1.1.1, even tho everywhere else it shows 1.1.2.

The mysql query that gave me problems also exists in ManageSmileys.php

[bweisz]

It should be noted this query is successful on MySQL 5.0.26, i suspect more people will see this as they upgrade to MySQL 5.0.37:

mysql> ALTER TABLE smf_smileys ORDER BY LENGTH(code) DESC;
Query OK, 28 rows affected (0.19 sec)
Records: 28  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.26    |
+-----------+
1 row in set (0.00 sec)

mysql>

[bweisz]

A quick fix for anyone else with this problem is to alter the query to remove the LENGTH() function:

ALTER TABLE smf_smileys ORDER BY code DESC;

Heres a patch against smf_1-1-2_upgrade.tar.gz archive:

Code Select Expand


diff -Nur smf-orig/Sources/ManageSmileys.php smf/Sources/ManageSmileys.php
--- smf-orig/Sources/ManageSmileys.php  2007-02-11 05:32:34.000000000 -0600
+++ smf/Sources/ManageSmileys.php       2007-03-30 18:57:41.000000000 -0500
@@ -651,7 +651,7 @@
                        // 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__);
+                               ORDER BY code DESC", __FILE__, __LINE__);
                }

                cache_put_data('parsing_smileys', null, 480);
@@ -987,7 +987,7 @@
                // Make sure the smiley codes are still in the right order.
                db_query("
                        ALTER TABLE {$db_prefix}smileys
-                       ORDER BY LENGTH(code) DESC", __FILE__, __LINE__);
+                       ORDER BY code DESC", __FILE__, __LINE__);

                cache_put_data('parsing_smileys', null, 480);
                cache_put_data('posting_smileys', null, 480);
diff -Nur smf-orig/upgrade_1-1.sql smf/upgrade_1-1.sql
--- smf-orig/upgrade_1-1.sql    2007-02-11 05:32:05.000000000 -0600
+++ smf/upgrade_1-1.sql 2007-03-30 18:57:17.000000000 -0500
@@ -178,7 +178,7 @@
CHANGE COLUMN smileyOrder smileyOrder smallint(5) unsigned NOT NULL default '0';

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

UPDATE {$db_prefix}smileys
SET filename = 'embarrassed.gif'


[metius84]

excuse me,

i've the same problem.

Where have I to put the patch? (i'm a little bit ignorant )

How can i solve the problem easily? Could you explain me easily?

Thanks

[bweisz]

Edit Sources/ManageSmileys.php

Change Line 654:
ORDER BY LENGTH(code) DESC", __FILE__, __LINE__);
To:
ORDER BY code DESC", __FILE__, __LINE__);

Change Line 990:
ORDER BY LENGTH(code) DESC", __FILE__, __LINE__);
To:
ORDER BY code DESC", __FILE__, __LINE__);

Edit upgrade_1-1.sql

Change Line 181:
ORDER BY LENGTH(code) DESC;
To:
ORDER BY code DESC;

[OzExcalibur]

Thank you very much, had the same error, applied the fixes listed in Reply 4 and all is working again.

SQL server version is 5.0.41

[marestare]

I just had this problem on an upgrade from 1.1.4 to 1.1.6, and I'm using MySQL 5.0.45.

bweisz's fix worked perfectly for me as well.