Hi together,
since there doesnt seem to be any support in the german section (last 5 posts no answer at all for the last weeks) I will try to describe my problem and english and hope for some help...
I am the administrator of a small cool board community that has been running for the last 11 years. I guess half a year ago I updated to 1.1.21 and SQL 4, but now my provider (Domainfactory) ends support for this SQL Version so I have to change to SQL 5 (and therefore to SMF 2.0.15).
Well, I was able to create a SQL dump and update it to SQL 5 via BigDump. So far so good.
I am able to install a clean 2.0.15 - that is no problem. But now...
When I try to connect the clean new board to the filled SQL 5 database it doesn´t work (Errors in the last step of installation, something with "1" is not "0" or stuff like that).
When I install the board with a new blank SQL database und then try to import my "old" one I get error messages as well ("Unknown column 'add_deny' in 'field list'").
Where is my mistake? How can I handle this?
Thanks in advance for any help and sorry for my english...
You need to run the upgrader from 2.0 against your 1.1 database, which I don't think you've done based on the error messages.
that´s right - I looked online for a converter of any kind but couldnt find one - can you tell me the direct download link?
So it´s ok if I install a 2.0.15 and connect it to an already filled SQL 5 DB - but his one has to be converted before?
take a look at https://wiki.simplemachines.org/smf/Upgrading
Take the MySQL 5 database, grab the 2.0 upgrade package from the downloads page and take your 1.1 Settings.php file, change the database details to point to the MySQL 5 database.
The upgrade is designed to be run in place.
ok, thanks, this helps.
So to make it clear the way would be:
- Backup my old board
- create a SQL Dump and upgrade it to SQL 5
- change php on the webspace to a current version (7) as the current version is too old to run 2.0.15 oder SQL 5
- then use the "Large Upgrade" for Version 2.0.15
- upload the new SQL 5 DB an connect it to the board
- everything fine
?
No, that's really not what I said but unfortunately I'm at work right now and can't explain in great details, Rhône that you need to dump the database into MySQL 5 then connect that to the 2.0 upgrade package so it can do the various changes it needs (like renaming the column from addDeny to add_deny)
ok, got it (I guess)
Thank you all so much so far for your help! :)
looks good til now... but now I have this error message:
!!
Error!
Table 'db212587_3.smf_log_errors' doesn't exist
That´s right - I killed it one year ago or so... what can I do?
why would you ever delete a table in the database?? ?? ?? ??
NEVER EVER EVER DO THAT!
look at the install sql and then manually recreate the table.
because I was stupid :o
I copied smf113_log_errors to smf_log_errors... seems to work....
well it worked ;D
But one problem is left - I installed the german language, but "ä", "ü", "ö" and "ß" are not available - it only shows things like "rü". But only in the messages, not in the administration - there everything is right.
Does anybody know why? :o
convert to UTF-8
I did (with the possibility within the board) and now it looks like this:
�
confirm that all tables and columns in your database are utf-8
they are not - they are all "latin1_swedish_ci" in phpmyadmin and I don´t know how to change that...
Changing it database wide:
ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Changing it per table:
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Good practice is to change it at table level as it'll change it for columns as well. Changing for specific column is for any specific case.
Changing collation for a specific column:
ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
this is nothing I can do in phpmyadmin, right?
those commands are run from the SQL tab in phpmyadmin
Well it seems that I am fine - so far. Chars wont work but thats ok.
But I dont see any buttons / possibilities to change to administration tools to use another theme - looks strange?
BTW - if anyone wants to help me by just doing it - I dont mind spending 20 Bucks (Dollar? Pounds? Euros?) via paypal if it works afterwards. I don´t think that there is so much left that isn´t correct.
- latin1 to UFT doesnt work (its funny - I put back the old 1.1.14 with SQL 4 and told the installer to "use UFT" - and in 1.1.14 he does it. In 2.0.15 he doesnt with the same command - and it´s the same database, only migrated to SQL5).
- Theme doesnt work
For a newbie I think I did quite well ;D Well, BIG THANKS to all you here - great support!
1.1 themes won't work in 2.0, you'll have to install compatible themes in Admin -> Themes and Layout.
Converting database to utf-8 in phpmyadmin may not solve your character problem. Have you run conversion in Admin -> Forum Maintenance -> Database? If your db is large, may have to run it several times to get it to complete.
I don´t see the Admin Button so I can´t get to the administration panel to change the theme :o I saw it after the first try, but not any more after the second.
I tried conversion - all it did was to make funny signs anywhere ;D
But as I said - don´t mind latin1 and UFT. I can change the topics, and when you write new posts it´s ok. It´s a little nasty, but I COULD live with that. Though I don´t understand why it works in 1.1.14 and not in 2.0.15. Anything to do with the PHP version (if not don´t laugh too loud, I´m not so firm in these things ???)?
Link to your forum?
www.backstage-wrestling.de
Right now it´s back on 1.1.14 and SQL 4. The SQL 5 is ready to be used. If you want me to upgrade I can start right away.
If you want to log in I need Username / PW - because of all the spambots we shut down the registration.
When you upgrade to 2.0.15, your forum is still using a 1.1 theme, that's why you have no Admin button. Do the upgrade, then use http://www.backstage-wrestling.de/index.php?theme=1 to load the forum, it should switch to default Curve theme (what you see here) and you'll have admin access.
oh ok, cool, thanks for this :)
So there´s only the little question of the change from latin1 to UFT8... I will give it a try in the DB any may try conversion one more time. Last time it even changed my theme-panels? :o
Utf-8 conversion shouldn't change anything except collation. If you manually convert tables to utf-8 in phpmyadmin, I don't believe it converts characters in existing posts, only new posts. The admin conversion converts existing posts, so everything should display correctly, but it sometimes doesn't get through a large db in one go, you have to run it several times. If you had converted to utf-8 in 1.1, db should still be utf-8 after upgrade, but iirc (I may not), there was no utf-8 conversion in 1.1.
Quote from: Kindred on January 15, 2019, 05:00:38 PM
those commands are run from the SQL tab in phpmyadmin
I tried - it says: "ALTER DATABASE db212587 CHARACTER SET utf8 COLLATE utf8_unicode_ci;" - Operation succesful, 0,0004 seconds" - but nothing happened. It´s still all latin1
You can change collation in phpmyadmin in Operations tab.
Quote from: Sir Osis of Liver on January 15, 2019, 06:25:14 PM
Utf-8 conversion shouldn't change anything except collation. If you manually convert tables to utf-8 in phpmyadmin, I don't believe it converts characters in existing posts, only new posts. The admin conversion converts existing posts, so everything should display correctly, but it sometimes doesn't get through a large db in one go, you have to run it several times. If you had converted to utf-8 in 1.1, db should still be utf-8 after upgrade, but iirc (I may not), there was no utf-8 conversion in 1.1.
there actually IS a conversion in 1.1.14. I might just try it - if it kills anything I can play back the backup... though it says "this operation might kill all your data" O:)
Quote from: Sir Osis of Liver on January 15, 2019, 06:29:21 PM
You can change collation in phpmyadmin in Operations tab.
yeah, right, just saw it. What of those should be used? UFT8_bin? Is there any danger or risk in doing this?
Yes, a 1.1.x theme will not work on 2.0.x
Go into admin and reset everyone and the default to use the curve/default theme
utf8_general_ci
If you have a good backup, there's no risk. As posted earlier, don't believe phpmyadmin converts existing data, but admin conversion does.
That ALter database command only changes the default collation for new tables, going forward.
I gave you THREE commands to run.... :)
thank you both.
I will give it a try tomorrow night. No Risk no Fun ;D
no luck with no way to change those "ü" to an "ü" for example.
Ain´t there a way to change the language - I mean to tell the board "when you see an "ü" you write an "ü"?
OK, perhaps here is the solution: all my tables in SQL are Latin1. All? No. There are two (that I didn´t change) that are UFT8. Might be the mix that causes the problem? I guess I will change those to Latin1 und see what happens...
Why are you using BigDump? Have you tried exporting with phpmyadmin?
Yeah, sure. phpmyadmin is ok for exporting - but as the dump has about 300 MB I can´t import it with phpmyadim.
So I export with phpmyadmin - but have to import with BigDump.
Quote from: trth on January 17, 2019, 05:01:56 PM
OK, perhaps here is the solution: all my tables in SQL are Latin1. All? No. There are two (that I didn´t change) that are UFT8. Might be the mix that causes the problem? I guess I will change those to Latin1 und see what happens...
That didn´t work - I tried: "ALTER TABLE smf_log_search_messages CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;" - but it didn´t change
Did you zip the dump? I can't import large databases, I have my host do it. If the characters display correctly in 1.1, they should be good in 2.0. Afaik, upgrade does not affect collation, but BigDump may be doing something that does.
I tried to zip to import it, but it´s always too big - so I have to use BigDump.
I found out something quite funny: straight after the setup of the new forum it IS in german. All fine - but it say that my default theme settings are wrong (correct - I import the Dump but for testing it is on another domain - I have to change this in settings.php).
After the change - it´s english.
I´m going slightly mad here... :o
The language setting is in the db, and will carry over from 1.1 to 2.0, but if the language isn't installed it reverts to english. Are the german files in /Themes/default/languages?
yes they are.
I just try the 10th new installation right now...
If you can't get it to work, revert back to a clean 1.1 install with the original database, get it up and running, and start over. Going offline shortly, if you need further assistance pm cpanel access (url/user/pw) and I'll have a look tomorrow.
I have a 1.1.14 still running under backstage-wrestling.de (this board is online), I´m working on my testsystem at bwf.dirklather.de
I made a clean install 2.0.15 with german UFT 8 and it looks good. SQL5 is on UFT 8 as well.
I really think it crashes when BigDump imports the old DB and converts it from SQL 4 to 5.
Thanks for your help, I can pm you what you need (FTP-Access? Board-Access ? I will try to import the DB again now...)
you do realize that the LANGUAGE files also updated for 2.0.x? You can not use the 1.1.x language files, just like you can not use the 1.1.x themes
Quote from: Kindred on January 19, 2019, 11:53:02 AM
you do realize that the LANGUAGE files also updated for 2.0.x? You can not use the 1.1.x language files, just like you can not use the 1.1.x themes
Sure - I installed the new ones (SMF language file for 2.0.15). As I said - the test system is not an Update from 1.1.14 but a complete clear board. Here I wanted to check how I can manage the import of the SQL-4-to-5 DB. And there of course are the new language files installed.
BUT - as I said - after the BigDump-Import the language crashes and can not be chosen anymore (though it´s installed). So there might be something in the DB as well.
Just another thought - maybe the jump from 1.1.13 to 2.0.15 is too huge and the wiser way would be to go to 2.0.1 and then upgrade from step to step?
Ok, patched you up to 1.1.21, upgraded to 2.0.15. Upgrade ran fine, just a few server timeouts. Forum is running, fixed a couple of odds and sods. You tinkered with some things you shouldn't have.
Can't get into admin -
Unknown system variable 'version_comment'
File: /kunden/212587_63762/webseiten/Sources/Subs-Admin.php
Line: 80
version_comment appears only in DbExtra-mysql.php. Not a clue what this is, working on it. >:(
function smf_db_get_engine()
{
global $smcFunc;
static $db_type;
if (!empty($db_type))
return $db_type;
$request = $smcFunc['db_query']('', 'SELECT @@version_comment');
list ($comment) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
Left you running in 2.0.15. Hacked around version_comment error, admin access works. Language is german utf-8, special characters are displayed correctly. Check everything.. Be back online tomorrow.
what the... omg....
I´m speechless... that´s fantastic.... thank you so much... I owe you something! :-*
Some questions...
- saving the log-in won´t work with some users (doens´t work for me as well) -> is this a problem of the board?
- I´d like to use the old Yabb Theme (https://custom.simplemachines.org/themes/index.php?lemma=2603), but it´s not compatible. I mean it works - but the graphics are not there in the "Home - Help-Search and-so-on" bar. Is there a way?
- the "new" Button is missing in every theme I use... try to figure out where I can find it und where I have to save it -> fixed. I had to rename "german-uft8" to "german" in /webseiten/Themes/default/images/
I´m still sitting here being thankful :-* :)
Quote from: trth on January 20, 2019, 04:19:23 AM
Some questions...
- saving the log-in won´t work with some users (doens´t work for me as well) -> is this a problem of the board?
- I´d like to use the old Yabb Theme (https://custom.simplemachines.org/themes/index.php?lemma=2603), but it´s not compatible. I mean it works - but the graphics are not there in the "Home - Help-Search and-so-on" bar. Is there a way? - > fixed
- the "new" Button is missing in every theme I use... try to figure out where I can find it und where I have to save it -> fixed. I had to rename "german-uft8" to "german" in /webseiten/Themes/default/images/
I´m still sitting here being thankful :-* :)
as I can´t edit my postings I have to write a third one, sorry.
I almost died a few minutes ago cause I deleted the SQL 4 DB - thinking the board would be running on SQL 5 now. Good for me - I made a backup 10 seconds before so I was save and could restore everything :D
One of my major problems - and reason why I wanted to update - is the fact that my provider ends support for SQL 4 at the end of February. How can I move the data now to an SQL 5 DB? Would it be enough to bigdump the data in a SQL 5 DB and connect this to the board?
Just got on, will do some cleaning up, but forum seems to be running ok. Your original 1.1.14 install is in /webseiten_sav, I have local backup, and also db dump. Did the upgrade twice, first time ran into problems with utf-8 conversion, login not sticking, and german language not working in acp. Reloaded sql dump with BigDump (works good), had to hack new admin account (Krash - account you set up is not in dump), patched from 1.1.14 -> 1.1.21, upgraded to 2.0.15, reinstalled german utf-8 language pack, manually set all tables to utf8_general_ci.
Several things:
- I've commented out the version_comment query in DbExtra-mysql.php, it causes an error that prevents access to acp. Probably not a good idea to leave it that way, will try to get back to it and see if I can determine the cause.
function smf_db_get_engine()
{
global $smcFunc;
static $db_type;
if (!empty($db_type))
return $db_type;
/* $request = $smcFunc['db_query']('', 'SELECT @@version_comment');
list ($comment) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
*/
// Skip these if we don't have a comment
- Database is at 2.0.15, should run fine in SQL 4 or 5. You should upgrade php to 5.6 min / 7.1 max. I run everything in 5.6.
- I'm not seeing the login problem today.
- You should be able to use Yabb Classic 2.0, it's updated to 2.0. I'll try it.
- new.gif is missing because you renamed the language directory. It's in /Themes/default/languages/images/german/, should be /Themes/default/languages/images/german-utf8/. Why did you rename the directory?
Installed Yabb theme, fixed login and copied new.gif from Curve. It's running. :P
Quote from: Sir Osis of Liver on January 20, 2019, 03:29:18 PM
Several things:
- I've commented out the version_comment query in DbExtra-mysql.php, it causes an error that prevents access to acp. Probably not a good idea to leave it that way, will try to get back to it and see if I can determine the cause.
function smf_db_get_engine()
{
global $smcFunc;
static $db_type;
if (!empty($db_type))
return $db_type;
/* $request = $smcFunc['db_query']('', 'SELECT @@version_comment');
list ($comment) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
*/
// Skip these if we don't have a comment
I don´t know what this is about - I wasn´t the first administrator of this board, I took over the responsibility when our first administrator left the project. As long as everything was running more or less smooth this was no problem...
Quote from: Sir Osis of Liver on January 20, 2019, 03:29:18 PM- Database is at 2.0.15, should run fine in SQL 4 or 5. You should upgrade php to 5.6 min / 7.1 max. I run everything in 5.6.
I will do so after you told me everything is ok and fine.
php is now at 7.028 STABLE. I will then make a new dumb from SQL 4, bigdumb it into SQL 5 and connect this new database to the board. If anything fails I will be fine with the backup - but I guess I can do that.
Quote from: Sir Osis of Liver on January 20, 2019, 03:29:18 PM- I'm not seeing the login problem today.
I still have - I say "always stay logged in" and it won´t work. Everytime I reload the page I have to log in again (though my credentials are saved in the browser). More Users complain about that
Quote from: Sir Osis of Liver on January 20, 2019, 03:29:18 PM- You should be able to use Yabb Classic 2.0, it's updated to 2.0. I'll try it.
It does - I can see it right now :) Welcome to our board - nice to see you there :-*
Quote from: Sir Osis of Liver on January 20, 2019, 03:29:18 PM- new.gif is missing because you renamed the language directory. It's in /Themes/default/languages/images/german/, should be /Themes/default/languages/images/german-utf8/. Why did you rename the directory?
As I said before - I renamed it because in every theme the button was missing. By renaming the directory it worked O:) I´m a bit too pragmatic sometimes I gues...
The version_comment error is nothing to do with anything an admin did. May be due to database running in SQL 4. Don't think you need to export/import the db into SQL 5, should run as is. You may have corrupt cookie. I've changed cookie name, that will log everyone out and set new cookie for all users.
If you haven't already, backup 2.0.15 database before you make any more changes.
Quote from: Sir Osis of Liver on January 20, 2019, 04:06:20 PM
The version_comment error is nothing to do with anything an admin did.
Good to read that this was not my fault ;D
Quote from: Sir Osis of Liver on January 20, 2019, 04:06:20 PMMay be due to database running in SQL 4. Don't think you need to export/import the db into SQL 5, should run as is.
My provider kills all SQL 4 Databases in February / March. I HAVE to switch...
Quote from: Sir Osis of Liver on January 20, 2019, 04:06:20 PMYou may have corrupt cookie. I've changed cookie name, that will log everyone out and set new cookie for all users.
I will give it a try, thank you so much for that. Doesn´t help:
Login is picture 1, then I close IE / Edge / FF and go to the board again - picture 2.
Quote from: Sir Osis of Liver on January 20, 2019, 04:06:20 PM
If you haven't already, backup 2.0.15 database before you make any more changes.
I will backup the DB in a few minutes
You should be able to export SQL 4 database and import to SQL 5, just don't know if it's necessary. I've removed the smf113_ tables.
Quote from: Sir Osis of Liver on January 20, 2019, 04:15:12 PM
You should be able to export SQL 4 database and import to SQL 5, just don't know if it's necessary. I've removed the smf113_ tables.
I´m just starting that and try to re-connect then in the board.
It worked - I´m on SQL5 now :)
Ok, I've reverted DbExtra-mysql.php to core code, admin is working without the hack. Sometimes takes two tries to login, otherwise looks good.
cool 8)
So there´s only one point remaining - that the login is not saved :-\
My login is saved until I close browser (it's configured that way). I'm not seeing the problem. Looking at your screenshots, if you're logged out, login fields should always come up empty. You have to fill them in every time, but once you're logged in, you should stay logged in until you logout or browser clears the cookie.
oh ok I got it.
In the old version (1.1.14) the credentials were saved - when I bootet my PC in the morning and went to the board I was logged in automatically once I allowed cookies to be saved on my computer.
It works now...? :o
You should still be logged in next day if you haven't cleared the browser. Should go directly to main index, not login. If you are using private browsing, cookies will clear when you close browser, and you'll have to login again, but I don't recall 1.1.x ever loading login window with user/pw fields already filled.
I've prettied up urls (again) with repair_settings, double login/logout (referring url error) appears to be fixed. Try not to change anything, let it run for a day or two, see how it is. Old databases can sometimes be a bit squirrelly.
I promise not to change anything but to make a backup of the DB! *swear*
Thank you so much, I learned a lot the last days and I am really, really thankful for you time and your patience! :)
So it´s me again after a couple of days - all runs smooth as far as I can see :)
One question: I always have about 30-40 "guests" sneaking around though I tool all the rights away in the board.
What can I do against this? These are all just spambots from east europe...
you can't do anything... and they are not "sneaking around"
If you have permissions locked down so that guests can not read, then they are not even seeing anything.
The Who's Online list just lists the connections and the URLs that the connection has called... it has no logic to determine if they SEE the contents of the URL.
for example, you might see Guest is viewing admin -- that does nto mean that the guest is SEEING the admin screen... it just means that they attempted to view the url index.php/action=admin
oh ok, thank you - I didn´t know that.
I was just wondering because in "who´s online" they were partially looking at veeery old posts - so I have no idea how they might have gotten the direct URL to try to attempt to look around there.