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 http://bugs.mysql.com/bug.php?id=24562 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
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>
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:
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'
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
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;
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
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.