I get this error when calling the upgrade.php: ORDER BY ignored as there is a user-defined clustered index in the table 'smf_boards'
There's no styling on the page, I just get this error when I open it,
I've copied my 2.0.7 forum (files and database) from my linux host and set it up on my Windows server, making sure to convert paths to Windows format.
What table type and what indexes are in use on that table?
This is what you need?
Column Type Null Default Comments
id_board smallint(5) No
id_cat tinyint(4) No 0
child_level tinyint(4) No 0
id_parent smallint(5) No 0
board_order smallint(5) No 0
id_last_msg int(10) No 0
id_msg_updated int(10) No 0
lastUpdated int(11) No 0
member_groups varchar(255) No -1,0
id_profile smallint(5) No 1
name varchar(255) No
description text No
num_topics mediumint(8) No 0
num_posts mediumint(8) No 0
count_posts tinyint(4) No 0
id_theme tinyint(4) No 0
override_theme tinyint(4) No 0
allowIgnore tinyint(4) No 1
redirect varchar(255) No
unapproved_posts smallint(5) No 0
unapproved_topics smallint(5) No 0
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No id_board 69 A No
categories BTREE Yes No id_cat 23 A No
id_board 69 A No
children BTREE Yes No child_level 6 A No
id_parent 23 A No
board_order 69 A No
id_board 69 A No
ID_PARENT BTREE No No id_parent 23 A No
ID_MSG_UPDATED BTREE No No id_msg_updated 69 A No
The second one, sure, the first one no. I want to know whether that table is MyISAM, InnoDB or something else.
I see no reason under normal circumstances for any ORDER BY on that table to be ignored.
Gotcha, it's InnoDB
OK. That means you have the index/primary key on two columns, so it can't order by id_board because it thinks that column might not be unique...
Open up upgrade.php and comment out / remove this code (lines 994-996):
// Do they have ALTER privileges?
if (!empty($databases[$db_type]['alter_support']) && $smcFunc['db_query']('alter_boards', 'ALTER TABLE {db_prefix}boards ORDER BY id_board', array()) === false)
return throw_error('The ' . $databases[$db_type]['name'] . ' user you have set in Settings.php does not have proper privileges.<br><br>Please ask your host to give this user the ALTER, CREATE, and DROP privileges.');
Yes but it's been like that for ages to have id_board as the primary key with board_order as a unique.
I know that at some time previously users on InnoDB were instructed to alter the table spec to make id_board a unique and board_order the primary key because of InnoDB table predicating, though that doesn't seem to be the case here.
In any case I'm not sure that query is even necessary any more since Dragooon rewrote the board index code not to depend on forced table ordering.
In upgrade.php it's also used to make sure the user has ALTER privileges so we can throw an error at them before attempting to mess with the database. Not a huge deal though.
Quote from: Oldiesmann on November 27, 2014, 11:41:38 AM
OK. That means you have the index/primary key on two columns, so it can't order by id_board because it thinks that column might not be unique...
Open up upgrade.php and comment out / remove this code (lines 994-996):
// Do they have ALTER privileges?
if (!empty($databases[$db_type]['alter_support']) && $smcFunc['db_query']('alter_boards', 'ALTER TABLE {db_prefix}boards ORDER BY id_board', array()) === false)
return throw_error('The ' . $databases[$db_type]['name'] . ' user you have set in Settings.php does not have proper privileges.<br><br>Please ask your host to give this user the ALTER, CREATE, and DROP privileges.');
This got me to the actual upgrade, but it died on Step 4:
Executing upgrade script 1 of 2.
Executing: "Final clean up..." (42 of 42 - of this script)
Error!
ORDER BY ignored as there is a user-defined clustered index in the table 'smf_boards'
There's a continue button, but nothing happens when I click it.
Remove this from the end of upgrade_2-0_mysql.sql:
ALTER TABLE {$db_prefix}boards
ORDER BY board_order;
Do i run the upgrade from the start (restore database) or just run it again?
You should be able to just run it again without restoring the database. That was the very end of the upgrade_2-0_mysql.sql file so it's already done all that it needs to do there.
That worked (re-open upgrade.php, check 'continue where it left off', click continue), but now I get "Table 'smf.smf_moderator_groups' doesn't exist" after clicking continue on that page.
Run upgrade.php again from the start. That means the upgrade didn't run all the way through.
I restored from the backup and ran the upgrade again.
This time it lands on this at Step 4:
Executing upgrade script 1 of 2.
Executing: "Final clean up..." (42 of 42 - of this script)
!!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 '//ALTER TABLE smf_boards //ORDER BY board_order' at line 1
Delete those lines entirely. Since that's raw SQL, putting "//" in front of them won't work.
Deleted those, now I get this:
ORDER BY ignored as there is a user-defined clustered index in the table 'smf_categories'
Remove this from the end of upgrade_2-0_mysql.sql:
ALTER TABLE {$db_prefix}categories
ORDER BY cat_order;
That did it!
Upgrade completed :)