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 Expand

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 Expand

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

[kat]

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?

[kat]

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 Expand

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 Expand

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 Expand

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?

[kat]

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?

[kat]

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).

[kat]

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 Expand

ALTER TABLE {$db_prefix}boards
ORDER BY boardOrder;

And remove these from upgrade_2-0.sql:

Code Select Expand

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 Expand

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 Expand

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.

[SnowmanDK]

Restored backup yet again...

This time I extracted the 2.0.2 upgrade package into the forum.

Edited the two files suggested and ran the upgrade.php.

Now this error comes up:

Code Select Expand

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

I wonder if these lines should be removed to resolve that error?:

Code Select Expand

ALTER TABLE {$db_prefix}categories
ORDER BY catOrder;

[emanuele]

Do you have some " (double quotes) or ; (semi-colon) or ' (single quote) in the database password or in the database user name?

[SnowmanDK]

Password have a punctuation mark. that's it.
I'll try and remove it and return with result.

[SnowmanDK]

That didn't change anything. Same error.
I'm gonna try something else and return.

[SnowmanDK]

Was hoping I had a glitch in my admin software, but didn't.
BOTH phpMyAdmin AND HeidiSQL agree that the user have FULL rights to the database.

[SnowmanDK]

Update: I even tried making a new account with full privileges. Same sad result as before.

[kat]

I'm not ignoring you, Snowy, honest!

It's just that, as I said, I'm not familiar with the self-hosting thing.

[emanuele]

I'd suggest you to convert the boards table to MyISAM at that point...

[SnowmanDK]

That's ok K@. People have lives I know 

Now... I converted boards as suggested emanuele, and got ONE step further.
But there still seem to be "something rotten in the state of Denmark"  (I live there) 

Code Select Expand

Upgrading...
Updating Settings.php... Successful.
Backing up old table data...
   Backing up "tss_attachments"... Unsuccessful!
This query:
CREATE TABLE backup_tss_attachments (

PRIMARY KEY (`ID_ATTACH`),

UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_ATTACH`),

KEY `ID_MSG` (`ID_MSG`))

TYPE=InnoDB

SELECT *

FROM tss_attachments;
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 'TYPE=InnoDB

SELECT *

FROM tss_attachments' at line 5

I also noticed when converting that all tables are encoded as "latin1_swedish_ci".
Not sure if it will have any effect, but gonna do another restore and convert all tables to "utf8_general_ci" and MyISAM just to test... Will return with result...

[SnowmanDK]

Ahhh... NOW we are getting somewhere 
As said earlier, fresh from backup, and add 2.0.2 upgrade, AND converted all tables to "utf8_general_ci" and MyISAM.

Ran upgrade.php from forum homepage:
+Database backup completed without errors...

Next:

Code Select Expand

Executing database changes
Please be patient - this may take some time on large forums. The time elapsed increments from the server to show progress is being made!
Executing upgrade script 1 of 2.
Executing: "Updating and creating indexes..." (1 of 19 - of this script)

Updating indexes on "messages"...done

and then:

Code Select Expand

!!Error!
Server has not responded for 30 seconds. It may be worth waiting a little longer or otherwise please click here to try this step again

I retried this a few times with the same result.
I even tried waiting for 20 minutes just to see, but again in vain.

[emanuele]

How many posts has your forum?

[SnowmanDK]

Not that many... 1155 messages according to the database.

[emanuele]

(sorry for the late reply)

mmm...can you try disabling javascript in your browser?

[SnowmanDK]

I am affraid the last attempt messed up my database.
I disabled javascript as you suggested, but now I just get this message:

Code Select Expand

Duplicate key name 'IP'
If it's any help then it comes with this message at this point:

Code Select Expand

<my server address>/forum/upgrade.php?step=1&substep=0&data=YTo2OntzOjc6ImN1cnN0ZXAiO2k6MTtzOjQ6ImxhbmciO3M6MTI6ImVuZ2xpc2gtdXRmOCI7czozOiJyaWQiO2k6MTA0MTtzOjQ6InBhc3MiO2k6MTE4MztzOjU6ImRlYnVnIjtpOjA7czoyOiJqcyI7aTowO30=
Also, when I wanted to restore from the database backup, then I couldn't find it.
It DID say that the backup of all tables finished with no errors.
Where are they saved?

[emanuele]

I am affraid the last attempt messed up my database.
I disabled javascript as you suggested, but now I just get this message:

Code Select Expand

Duplicate key name 'IP'

Okay, that's not a big problem.
Are you familiar with phpmyadmin?
If so, enter into it and find the table smf_log_errors (the prefix "smf" could be different), go to "structure" (top menu), at the bottom you'll see a table named "indexes" (it could be collapsed), under "keyname" you should find an entry called "ip", simply remove it, it will be regenerated next time (and if not I'll tell you how to do that at the end of the upgrade).

Also, when I wanted to restore from the database backup, then I couldn't find it.
It DID say that the backup of all tables finished with no errors.
Where are they saved?

Who is "It"?
Usually the backups are files saved on your computer.

[SnowmanDK]

Well the backup I talked about was the one made at the beginning of the upgrade, but I found it 
I thought it made a new database, but I see it just made tables in the existing one

Removing the "ip" entry worked like a charm

Now this happens:

Code Select Expand

Updating indexes on "messages"...done
Updating table indexes......done
Reordering boards and categories...done
Updating indexes and data on "smileys"...done
Updating indexes on "log_boards"...done
Updating indexes on "log_mark_read"...done
and then it does a timeout again:

Code Select Expand

!!Error!
Server has not responded for 30 seconds. It may be worth waiting a little longer or otherwise please click here to try this step again

I am ever greatful for your help so far, expecially as I see progress *thumbs up*
An idea for the next step?

[emanuele]

If you are comfortable with phpmyadmin I'd suggest you to manually update the indexes and drop that part from the upgrade_1-1.sql.

If this is acceptable to you, I'll give you a more detailed answer (I know this is just a workaround... )

[SnowmanDK]

Well, I have actually never used phpmyadmin before you mentioned it 
So far I have always used the program HeidiSQL which have been working fine for me so far.

But I am a spare time .net programmer and have worked quite a lot with databases, so I'll have a go at it 

Hit me 

[emanuele]

Ohh...I suggested phpmyadmin because is one that is usually available, but if you have other tools you are more comfortable with feel free to use them!

[SnowmanDK]

oh ok
I'll try with the tool I know

[kat]

How'd it go?

[SnowmanDK]

Oh I still wait for the instructions

[SnowmanDK]

Still awaiting instructions 

[emanuele]

yups, sorry lost that from the radar...

Okay, now it's a bit late here and I should re-read the topic in order to avoid give you some wrong instruction (I'm even a bit sleepy...), but still there is one that could be easy:
remove this piece from upgrade_1-1.sql

Code Select Expand

---# Updating indexes on "themes"...
ALTER TABLE {$db_prefix}themes
DROP PRIMARY KEY,
ADD PRIMARY KEY (ID_THEME, ID_MEMBER, variable(30)),
ADD INDEX ID_MEMBER (ID_MEMBER);
---#

If the upgrade continues, at the end you will check the themes table and you'll drop the primary key, the re-create the primary key according to the line:
ADD PRIMARY KEY (ID_THEME, ID_MEMBER, variable(30)),
and the index
ADD INDEX ID_MEMBER (ID_MEMBER);

Sorry again...

[SnowmanDK]

Well... Now it does this:

Code Select Expand

Updating and creating indexes... Successful!
Updating indexes on "messages"... Successful!
Updating table indexes... Successful!
Reordering boards and categories... Successful!
Updating indexes and data on "smileys"... Incomplete.
and then it shows the message below and stops:

Code Select Expand

Table 'tss_personal_messages' already exists

[emanuele]

What version of MySQL do you have?
Do you have any particular MySQL setting? (that you are aware of, of course )
Are you upgrading from the browser or from the CLI?

Asking all these because I'm wondering why you are getting all these errors while you shouldn't...

[SnowmanDK]

What version of MySQL do you have?

5.5.25

Do you have any particular MySQL setting? (that you are aware of, of course )

Made no special settings during installation, so I think not.

Are you upgrading from the browser or from the CLI?

Browser.

P.S.: Sorry about all the trouble :/

[emanuele]

P.S.: Sorry about all the trouble :/

Nothing to apologise for.

I just tried an upgrade with a similar version of MySQL (5.5.16), from browser (with or without javascript) and from CLI, interrupting the upgrade and starting again from another browser...nothing.

I'm out of ideas at the moment...
You are using a test site, right?
Have you tried restoring the original database and try again with the changes to the upgrade?

[SnowmanDK]

You're not gonna like this 
I'm running all this on my main, as I am not wealthy enough to do a full test setup also, AND I didn't expect these problems 

Just tried a db restore, but not sure how original it is, as the first run where I choose to do a full backup failed DURING backup 

After backup-restore and so on I tried again WITHOUT javascript active:

Code Select Expand

Updating and creating indexes... Incomplete.

I am getting close to giving up, and just restore my old 1.1.16/1.0.9... 
It popped up with errors once in a while, but at least the forum worked.

...

[Chas Large]

You could try setting up a copy of the forum on a local PC using EasyPHP or XAMPP, either of which allow you to host onto a PC. Create a copy into a designated folder, copy the dB into the MySQL location and then just run repair settings to change the board settings to your local PCs paths.

Once done and the 1.1.x forum is up and running, take another copy of the settings.php file then try upgrading the local forum and see if you still get the same errors. If not it will prove to be a host issue.

I've used EasyPHP quite a bit and SMF works fine on it.

[SnowmanDK]

Hi Chas. Thanks for the suggestion.
I will try it, if the experiments emanuele are doing for me doesn't work 

[SnowmanDK]

Through the huge amount of patience and support by emanuele, it is finally fixed.
Sadly we never found what whas the cause of my problems.

What was done was I made an export of my full database,
which emanuele then imported on his system and ran the upgrade on.

Then I got the upgraded version back, corrected the paths, wiped my old database and imported it.
I installed the FULL v2.0.2 on my site, corrected the settings file and deleted the install files.

It now run without ANY errors at all. Again, thank you so much for the SUPER help and support emanuele

Side note: I can't say it for sure, but it looks to me like it might be a MySQL or PHP setting in my system that prevented the update, although it's pure speculation.

This topic may now be closed

[Chas Large]

Well done emanuele