I'm install 2.015 SMF at my local machine (Windows 10-64, Openserver (Apache+php7.3 + MySql8)
It's fresh install - no mods - one custome theme "woodtools"
Next I'm try to upgared to SMF2.1-rc2
I'm start upgrade.php and wait for a long time in step:
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: "Changing column names." (1 of 42)
I'm enable query log for all queries and see infinite loop :
Connect root@localhost on using TCP/IP
Init DB woodtools
Query SET NAMES utf8
Query SELECT variable, value FROM smf_settings
Query SELECT variable, value
FROM smf_themes
WHERE id_theme = 1
AND variable IN ('theme_url', 'theme_dir', 'images_url')
This queryes repeated one by one and nothing more is happened.
May be problem in smf_settings table ?
I don't believe we have done full testing with MySql8 yet you may need to try an older version of MySQL
I don't think that we got here a mysql 8.0 issue.
True might not be. I would suggest Ulibka to grab the latest github version as there might have been fixes made.
Yes, latest GitHub is lightyears ahead of the posted RC2. Try that first.
But it's also true that we don't have a lot of mysql8 experience to-date, and the very little we've seen has issues.
I see this repo:
https://github.com/SimpleMachines/SMF2.1/tree/release-2.1
But this repo don't have upgrade.php - I think it's for fresh installation.
I can try MySQL 5-7
But installation isn't easy becuse my db size is about 5GB
Do you know - is it possible manualy import from old database to new fresh installation ?
What table I don't to import ? (for example smf_log_*** ?)
And I don't know - what records to leave in smf_settings ?
Quote from: Ulibka on April 23, 2020, 01:18:46 PM
I see this repo:
https://github.com/SimpleMachines/SMF2.1/tree/release-2.1
But this repo don't have upgrade.php - I think it's for fresh installation.
Did you look inside the folder named "other" ?
Yes, to do the upgrade, you need to copy the 8 files from /other to the forum root.
Thank you Shambles, shawnb61.
I'm try to upgrade using git version, but unsuccesfuly
upgrade.php craches at step4:
Fatal error: Uncaught Error: Call to undefined function log_error() in
Y:\OSPanel5-3-5\domains\woodtools\Sources\Subs.php:6422
Stack trace: #0
Y:\OSPanel5-3-5\domains\woodtools\Sources\Load.php(69): smf_json_decode('a:5:{i:1;s:45:"...', true) #1
Y:\OSPanel5-3-5\domains\woodtools\install.php(1535): reloadSettings() #2
Y:\OSPanel5-3-5\domains\woodtools\install.php(145): AdminAccount() #3
{main} thrown in Y:\OSPanel5-3-5\domains\woodtools\Sources\Subs.php on line 6422
In smf_setting I'm have record:
attachmentUploadDir
a:5:{i:1;s:45:"Y:\OSPanel5-3-5\domains\woodtools/attachments";i:2;s:46:"Y:\OSPanel5-3-5\domains\woodtools/attachments1";i:3;s:46:"Y:\OSPanel5-3-5\domains\woodtools/attachments2";i:4;s:46:"Y:\OSPanel5-3-5\domains\woodtools/attachments3";i:5;s:45:"Y:\OSPanel5-3-5\domains\woodtools/attachments";}
smf_attachments has 386 000 records and attachments placed in 4 directories:
attachments,attachments1,attachments2,attachments3
Are you running the upgrade via browser? - try upgrading SMF via CLI (5gb worth table is not small and you might have timeouts due to limitations of php)
You're running the installer, but you need to be running the upgrader. Sorry, I guess I needed to be more specific... To upgrade, you need to copy the 8 upgrade files from other to the root. (upgrade*.*)
I would:
- Restore your settings file from before the upgrade (it may have been stepped on)
- Delete the install*.* files from your forum root
- Copy the 8 upgrade*.* files from /other to your forum root
- Run the upgrader
On large forums like this, the browser will run OK, but some tasks need to run many minutes/hours. The browser is impatient and gives you a wtf prompt after not hearing from the server in 30 secs... If the server is still chugging along, just ignore the warning & let it run. DO NOT NAVIGATE AWAY from the upgrader tab while it is running.
Antes is correct, the CLI is a viable option here. CLI is good at shorter jumps, & you're only going from 2.0 to 2.1 here. It's a little more clear when there is a hard error. I am not as familiar with the CLI, there may be more manual steps post-upgrade.
Yes, I'm run http://woodtools/upgrade.php
At php.ini I'm set
max_execution_time = 60000
Antes, how I can upgrading SMF via CLI ?
I can't insert any image to this forum.
Is any way to do it ?
My top records in database :
smf_log_search_words 57 000 000 records
smf_log_topics 14 000 000 records
smf_log_boards 9 000 000 records
smf_log_mark_read 8 000 000 records
Is it possible to clear all smf_log tables ?
Quote from: shawnb61 on April 24, 2020, 02:34:52 AM
I would:
- Restore your settings file from before the upgrade (it may have been stepped on)
How I can do it ?
Unless there is a bug (which is not unusual for 2.1 at this stage), CLI upgrade & Browser should result same output, upgrade.php designed to handle both ways... How to upgrade via CLI is actually simple, you just need a command-line access to the server and navigate to your SMF folder (say like yours is under /var/www/html/) and run;
php upgrade.php
Upgrade process requires same files regardless of the way you run it, so for CLI or for Browser you still need to have upgrade.php / upgrade-helper.php & required SQL file(s) -in this case 2.1-
I'm run php upgrade.php
And process start work!!!!
I'm see many screen messages and many different queryes in log.!
(process still working)
upgrade.php was ended sucessfuly!
Is I need some manual steps?
I can't run index.php at my forum.
It produce error and write : run upgrade.php
upgrade.php start from step 4 - Database Changes
Once I'm see error - "Server does not respond for 30 second, try again" and after some seconds error was gone.
I'm see this select queryes in infinite loop - one by one:
SELECT variable, value FROM smf_themes WHERE id_theme = 1 AND variable IN ('theme_url', 'theme_dir', 'images_url')
Nothing is doing else :'(
I'm run php upgrade.php again
It was work succesfully.
Next I delete upgrade.php and run index.php in browser and recieve error:
Copy following files and run upgrade.php again (via CLI)
> upgrade.php
> upgrade-helper.php
> upgrade_2-0_mysql.sql
> upgrade_2-1_mysql.sql
if this does not work, please wait a developer to help you further.
copy all files from "Other" directory and rewrite setting.php
all stop working :)
Ok, import database again and start from scratch
Quote from: Ulibka on April 24, 2020, 06:05:26 AM
copy all files from "Other" directory and rewrite setting.php
all stop working :)
Ok, import database again and start from scratch
What do you mean by rewrite setting.php?
What is your forum url btw?
we did not say "copy all files"
we said copy SPECIFIC files.
Yes. By doing what you did, you overwrote the Settings.php file with blank data.
Restore your previous Settings.php file and follow the actual instructions.
I'm working at my local machine (Windows, OpenServer, Apache, MySQL, PHP 7.3)
I'm start process from blank - import database (about 2hr)
run php upgrade.php
copy four files
> upgrade.php
> upgrade-helper.php
> upgrade_2-0_mysql.sql
> upgrade_2-1_mysql.sql
and run php upgrade.php again
All processed sucessfully.
I'm delete upgrade.php
I'm try to run SMF and see error:
Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, bool given in Y:\OSPanel5-3-5\domains\woodtools\Sources\Subs-Db-mysql.php on line 1019
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, bool given in Y:\OSPanel5-3-5\domains\woodtools\Sources\Subs-Db-mysql.php on line 1020
SMF file version (2.1 RC2) does not match SMF database version (2.1 dev0).
Run the SMF upgrader to fix this.
It looks like the db is not en par with the forum files from looking at the error.
I'm run next step later.
I'm found and correct data eror in smf_member birthday field.
The birthday type is data and some values in this field was not data type.
But upgrade stop working at this step:
CREATE INDEX idx_likes ON smf_messages (likes DESC)
MySQL said:Documentation
#1178 - The storage engine for the table doesn't support descending indexes
Is anybody know is MySQL 5.7 can create descendand index?
Or I'm need to switch to MaraDB ?
MySQL 8.0 is the first mysql database how support this feature,
but only with innodb: https://github.com/SimpleMachines/SMF2.1/pull/6069
Thank you very march to all!
I can upgrade database to 2.1 version!
I see my forum again!
What was the problems in my case:
1. Error in data in birfday field in smf_members (for example I'm see 1961-00-00)
2. program don't work with many records (in my case 300 000) in table smf_attachments - I'm temporary delete 230 000 records
3. My smf_message was have MyISAM engine, I'm need to convert it to InnoDB engine
Solution:
run upgrade.php in cli mode (php upgrade.php)
Use answer from Antes:
Copy following files and run upgrade.php again (via CLI)
> upgrade.php
> upgrade-helper.php
> upgrade_2-0_mysql.sql
> upgrade_2-1_mysql.sql
I'm repeat step above about 20 times
For stupit reason,
smf team decide to keep the myisam engine instead of using modern db engine.
Quote from: albertlast on April 24, 2020, 03:01:06 PM
For stupit reason,
smf team decide to keep the myisam engine instead of using modern db engine.
In my case upgrade.php don't process next steps and stop for error.
I'm think is to see most liked post - if I convert my table to myisam engine - index dropped and first I'm see posts with 0 likes
I'm don't know what doing next....🤷♀️
Quote from: albertlast on April 24, 2020, 03:01:06 PM
For stupit reason,
smf team decide to keep the myisam engine instead of using modern db engine.
Some clarifications - 2.1 installs in InnoDB. The 2.1 Upgrader does not convert MyISAM to InnoDB for a few reasons. Admins are free to convert to InnoDB, & SMF supports that. (I run my 2.0 SMF forum in InnoDB.)
Quote from: Ulibka on April 24, 2020, 03:07:21 PM
I'm don't know what doing next....🤷♀️
I thought your forum was up & running? If you are facing a new error, please share the details. I cannot tell if you are having an issue or not.
Note: 2.1 is not in production at this time, & is available for you to test. This is a test forum, correct?
how do the upgrade realize that the 2.0 is innodb?
Quote from: albertlast on April 24, 2020, 03:33:06 PM
how do the upgrade realize that the 2.0 is innodb?
create new table with same structure (no index)
import all record from MyISAM table
create index
drop old table
rename table to smf_message
[spoiler]
use woodtools;
CREATE TABLE woodtools.`smf_messages1` (
`id_msg` int UNSIGNED NOT NULL,
`id_topic` mediumint UNSIGNED NOT NULL DEFAULT '0',
`id_board` smallint UNSIGNED NOT NULL DEFAULT '0',
`poster_time` int UNSIGNED NOT NULL DEFAULT '0',
`id_member` mediumint UNSIGNED NOT NULL DEFAULT '0',
`id_msg_modified` int UNSIGNED NOT NULL DEFAULT '0',
`subject` varchar(255) NOT NULL DEFAULT '',
`poster_name` varchar(255) NOT NULL DEFAULT '',
`poster_email` varchar(255) NOT NULL DEFAULT '',
`smileys_enabled` tinyint NOT NULL DEFAULT '1',
`modified_time` int UNSIGNED NOT NULL DEFAULT '0',
`modified_name` varchar(255) NOT NULL DEFAULT '',
`body` mediumtext NOT NULL,
`icon` varchar(16) NOT NULL DEFAULT 'xx',
`replyto` int UNSIGNED NOT NULL DEFAULT '0',
`approved` tinyint NOT NULL DEFAULT '1',
`thank_you_post` tinyint NOT NULL DEFAULT '0',
`thank_you_post_counter` smallint NOT NULL DEFAULT '0',
`likes` smallint UNSIGNED NOT NULL DEFAULT '0',
`modified_reason` varchar(255) NOT NULL DEFAULT '',
`poster_ip` varbinary(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO `smf_messages1`(`id_msg`, `id_topic`, `id_board`, `poster_time`, `id_member`, `id_msg_modified`, `subject`, `poster_name`, `poster_email`, `smileys_enabled`, `modified_time`, `modified_name`, `body`, `icon`, `replyto`, `approved`, `thank_you_post`, `thank_you_post_counter`, `likes`, `modified_reason`, `poster_ip`)
select `id_msg`, `id_topic`, `id_board`, `poster_time`, `id_member`, `id_msg_modified`, `subject`, `poster_name`, `poster_email`, `smileys_enabled`, `modified_time`, `modified_name`, `body`, `icon`, `replyto`, `approved`, `thank_you_post`, `thank_you_post_counter`, `likes`, `modified_reason`, `poster_ip`
from smf_messages;
--
-- Indexes for table `smf_messages`
--
ALTER TABLE `smf_messages1`
ADD PRIMARY KEY (`id_msg`),
ADD UNIQUE KEY `topic` (`id_topic`,`id_msg`),
ADD UNIQUE KEY `ID_BOARD` (`id_board`,`id_msg`),
ADD UNIQUE KEY `ID_MEMBER` (`id_member`,`id_msg`),
ADD KEY `ID_TOPIC` (`id_topic`),
ADD KEY `participation` (`id_member`,`id_topic`),
ADD KEY `showPosts` (`id_member`,`id_board`),
ADD KEY `approved` (`approved`),
ADD KEY `id_member_msg` (`id_member`,`approved`,`id_msg`),
ADD KEY `current_topic` (`id_topic`,`id_msg`,`id_member`,`approved`),
ADD KEY `related_ip` (`id_member`,`id_msg`),
ADD KEY `idx_ip_index` (`poster_ip`,`id_topic`),
ADD KEY `idx_related_ip` (`id_member`,`poster_ip`,`id_msg`),
ADD KEY `idx_likes` (`likes` desc );
--
-- AUTO_INCREMENT for table `smf_messages`
--
ALTER TABLE `smf_messages1`
MODIFY `id_msg` int UNSIGNED NOT NULL AUTO_INCREMENT ;
drop table smf_messages;
ALTER TABLE smf_messages1 RENAME TO smf_messages;
[/spoiler]
cp1251 is not a supported character set in SMF,btw. Expect odd bugs.
Quote from: Arantor on April 25, 2020, 04:40:10 AM
cp1251 is not a supported character set in SMF,btw. Expect odd bugs.
may be.
But UTF-8 double data size :'(
So you'd have a 10GB database... how much of that is the messages table? What other tables are big?
Realistically if you're on a 5GB database, you probably should be on at least a VPS server, for which something like Linode is not expensive for running on. Even their $20/month server should be able to deal with a 10GB database without much pain.
Also, cp1251 to UTF-8 doesn't guarantee a doubling unless literally every single character is accented, which I'm a bit skeptical of. Normally I'd expect to see a growth of 1.2x for French, German or Spanish, 1.5x for most eastern European languages, not double.
(For the record, I work on 450GB-2TB sized databases professionally, including on MySQL. Those have challenges. 10GB should not be a problem.)
Quote from: Arantor on April 25, 2020, 06:07:47 AM
how much of that is the messages table?
Thank you for you opinion!
I think it's time to convert to UTF-8
Today forum work on dedicated server
What are you think - how many memory I'm need for MySQL ?
Whats memory setting set in mysql.ini ?
So as suspected, the messages is not the most significant part of the database (and that's the part I'd expect to grow by the aforementioned numbers)
I'd possibly wonder if you want the logs of Aeva Media since that's the second largest part of your data. The search system consumes the largest part of the database.
As for memory, you don't need to put the entire database in memory; you need enough to keep the key parts in memory, but with the size of DB you have there and the way it's spread out, making sure MySQL has a good 1GB wouldn't hurt. 2GB definitely wouldn't hurt.
If you're not already a member of the Big Boards community here, you might want to apply to join - you have enough messages to qualify and there's better guidance in there about how to manage 1.5 million message boards (e.g. Sphinx if you're not already using it for search)
Thank you!
;D
Quote from: Arantor on April 25, 2020, 08:04:30 AM
So as suspected, the messages is not the most significant part of the database (and that's the part I'd expect to grow by the aforementioned numbers)
I'd possibly wonder if you want the logs of Aeva Media since that's the second largest part of your data. The search system consumes the largest part of the database.
As for memory, you don't need to put the entire database in memory; you need enough to keep the key parts in memory, but with the size of DB you have there and the way it's spread out, making sure MySQL has a good 1GB wouldn't hurt. 2GB definitely wouldn't hurt.
If you're not already a member of the Big Boards community here, you might want to apply to join - you have enough messages to qualify and there's better guidance in there about how to manage 1.5 million message boards (e.g. Sphinx if you're not already using it for search)
I never seriously asked this question but what happens if the OP decides to empty those log tables ?
Depends on the log table.
In the case of log_search_words, searching doesn't work properly if you're using a custom index at that point.
In the case of log_topics, log_mark_read and log_boards, read status is messed up.
The Aeva tables I don't 100% remember but I think that one is either an error log or a view log.
Quote from: Arantor on April 25, 2020, 12:26:17 PM
Depends on the log table.
In the case of log_search_words, searching doesn't work properly if you're using a custom index at that point.
In the case of log_topics, log_mark_read and log_boards, read status is messed up.
The Aeva tables I don't 100% remember but I think that one is either an error log or a view log.
Can you cheat your way like, empty log_search_words and perform upgrade (2.0>2.1) and recreate index or makes no difference ?
Sure, that would work.