Error running upgrade.php

Started by lukeren, November 27, 2014, 09:49:29 AM

Previous topic - Next topic

lukeren

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.

Arantor

What table type and what indexes are in use on that table?

lukeren

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

Arantor

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.

lukeren


Oldiesmann

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.');
Michael Eshom
Christian Metal Fans

Arantor

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.

Oldiesmann

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.
Michael Eshom
Christian Metal Fans

lukeren

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.

Oldiesmann

Remove this from the end of upgrade_2-0_mysql.sql:

ALTER TABLE {$db_prefix}boards
ORDER BY board_order;
Michael Eshom
Christian Metal Fans

lukeren

Do i run the upgrade from the start (restore database) or just run it again?

Oldiesmann

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.
Michael Eshom
Christian Metal Fans

lukeren

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.

Oldiesmann

Run upgrade.php again from the start. That means the upgrade didn't run all the way through.
Michael Eshom
Christian Metal Fans

lukeren

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

Oldiesmann

Delete those lines entirely. Since that's raw SQL, putting "//" in front of them won't work.
Michael Eshom
Christian Metal Fans

lukeren

Deleted those, now I get this:

ORDER BY ignored as there is a user-defined clustered index in the table 'smf_categories'

Oldiesmann

Remove this from the end of upgrade_2-0_mysql.sql:

ALTER TABLE {$db_prefix}categories
ORDER BY cat_order;
Michael Eshom
Christian Metal Fans

lukeren


Advertisement: