Topic: 1.1.x > 2.0.2 upgrade problems

[SnowmanDK]

Hi

I have been using SMF for quite a few years. Greatest board out there 

But now I have a problem (or two).

My forum have not been updated for quite a while, so I decided to do something about it.
I made it all to 1.1.16 without a problem.

After that I wanted to sort the boards.
It SEEMS to work but get an error that my board is 1.1.16 but the database is only 1.1.9.

I hoped I can fix this issue by upgrading directly to 2.02 so I got the upgrade package and unpacked it to the directory.
This doesn't work as I now get this message:

Code: [Select]

ORDER BY ignored as there is a user-defined clustered index in the table 'tsssmf_boards'Anyone know how to fix this?

[CapadY]

If you want to upgrade to 2.0.2 that's the most easiest way I think.

First take a backup of all files and the database.

Downlad the large upgrade for 2.0.2, unzip the package, upload all files to the server and run upgrade.php.

After that your error will be gone and your forum is on 2.0.2

Remind, all install MOD's are undone after upgrading.

[SnowmanDK]

I did a verify to check that all files on my installation was ok.

Then I followed the guide on http://wiki.simplemachines.org/smf/Upgrading.

Reason for table name change below, is I made a full backup and installed a copy of the old forum to a new path.
All paths WAS adjusted before importing database as new forum, and it also works flawlessly (as 1.1.16).

Sadly I just get the error (almost instantly):

Code: [Select]

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

[CapadY]

Can you also tell in what part of the forumsoftware this error excist ?

I think there is nobody here who can guess such things.

[SnowmanDK]

Can you also tell in what part of the forumsoftware this error excist ?

It pops up on the browser right after I press enter to start the upgrade.php

[K@]

What mods do you have, installed?

[SnowmanDK]

What mods do you have, installed?

As I can't access the forum atm, I looked in the packages folder.
It contains these (besides updates):
nCode_Image_Resizer
Aeva-6.9.99
Enhanced_Dropdown

There is more in the backup subfolder, but I THINK I removed them as they were not compatible any more.

I'll try and restore the forum, then delete all mods and try upgrading again... Will be back with result.

UPDATE: I restored the old 1.1.16 backup I had and accessed the forum.
It DOES only contain the 3 mods mentioned above, but gives me an "Error in package installation" when I wanna uninstall them. From the warning it seems like I risk crashing the entire forum if I uninstall... What to do now?

[K@]

If you try the upgrade with it as it is, now, do you still get the error?

[SnowmanDK]

If you try the upgrade with it as it is, now, do you still get the error?

Unfortunately yes... Exactly the same error:

Code: [Select]

ORDER BY ignored as there is a user-defined clustered index in the table 'tss_boards'
But after restoring the old forum once more I started to go through everything.
I noticed I get almost the same error when moving tables around.
It refers to THIS problem:

Code: [Select]

It appears that your database may require an upgrade. Your forum's files are currently at version SMF 1.1.16, while your database is at version 1.1.9.
I just thought, AH, then I get the 1.1.16 upgrade package and then we are running.
But NO... When I try to run THAT upgrade then I get THIS error:

Code: [Select]

The MySQL user you have set in Settings.php does not have proper privileges.
Please ask your host to give this user the ALTER, CREATE, and DROP privileges.This does NOT make sense. I set it to use "root" which have FULL access and it always worked before.
So why this error?

[K@]

Ah... Humour me, here, would you?

Give your host a slap and ask them if you have full ownership of the files on your site, would you?

Whilst you're there, to save future hassles, can you ask them to disable mod_security?

[SnowmanDK]

Ah... Humour me, here, would you?

Give your host a slap and ask them if you have full ownership of the files on your site, would you?

Whilst you're there, to save future hassles, can you ask them to disable mod_security?

Then I would have to slap myself... It's running on my own rig (Winslows) from home
I really don't get this.
"IIS_IUSRS" have full access to the required subfolders.
"root" have full access to the database.

What am I missing?

[K@]

Which version of MySQL do you have?

I seem to remember a bug in that giving that error.

[SnowmanDK]

Which version of MySQL do you have?

I seem to remember a bug in that giving that error.

MySQL is running at 5.5.25 (think it's the newest version).

[K@]

I'm at a loss with this self-hosting stuff, I'm afraid.

It's screaming "File permissions/ownership", to me.

But, it seems you have that covered.

My brain hurts.

[SnowmanDK]

My brain hurts.

Mine too

[emanuele]

Please go to phpmyadmin, select the table tsssmf_boards, select "structure", then go down and you should see "Indexes" and a table with the following headers: action, keyname, type, unique, Packed, Field, Cardinality, Collation, Null, Comment.

Can you take a screenshot of this table and post it here?

Also, please check if the table is MyISAM or InnoDB.

[SnowmanDK]

I hope this screenshot is the one you look for:



I can tell that the entire database is running as InnoDB.

[SnowmanDK]

Any news on my problem?
It all seems to relate to my database, that for some reason wont update.
According to upgrades then the database is v1.9 while the forum it self is 1.16.
Re-upgrade fails with the errors mentioned earlier.

Please help.

[emanuele]

Sorry for the late reply...

Searching a bit around it seems that it's a problem related to InnoDB and ORDER BY.
I'm not an expert of MySQL, but probably the easiest workaround is to remove the order from the upgrade script...
Open upgrade_1-1.sql and remove these lines:

Code: [Select]

ALTER TABLE {$db_prefix}boards
ORDER BY boardOrder;
And remove these from upgrade_2-0.sql:

Code: [Select]

ALTER TABLE {$db_prefix}boards
ORDER BY board_order;

The moment you finish the upgrade you may see the boards in a wrong order, go to the admin panel and move one board (and move it back afterwards), that should fix the order.

[SnowmanDK]

I restored my backup and did some testing on the clean 1.1.16 forum.
Users are deleted without any errors at all.
When trying to move a board then it DOES move, but I get this error:

Code: [Select]

ORDER BY ignored as there is a user-defined clustered index in the table 'tss_boards'
File: D:\WWW\forum\Sources\Subs-Boards.php
Line: 1426

Note: It appears that your database may require an upgrade.
Your forum's files are currently at version SMF 1.1.16, while your database is at version 1.1.9.
The above error might possibly go away if you execute the latest version of upgrade.php.
I then copied the content of the 1.1.16 upgrade package into the forum folder.
After that I edited the upgrade_1-1.sql, but it seems like the upgrade never gets to that point.

I just get this error:

Code: [Select]

The MySQL user you have set in Settings.php does not have proper privileges.

Please ask your host to give this user the ALTER, CREATE, and DROP privileges.

Click here to try again.which makes no sense to me, as I have full rights when messing around in the forum itself.
Also when I go into the administration of the database, then I can see the user set in settings.php have FULL access to the database.