[Help] Upgrade from 2.0 have infinite loop

[Doug Heffernan]

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?

[Kindred]

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.

[Ulibka]

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.

[Doug Heffernan]

It looks like the db is not en par with the forum files from looking at the error.

[Ulibka]

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 ?

[albertlast]

MySQL 8.0 is the first mysql database how support this feature,
but only with innodb: https://github.com/SimpleMachines/SMF2.1/pull/6069

[Ulibka]

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

[albertlast]

For stupit reason,
smf team decide to keep the myisam engine instead of using modern db engine.

[Ulibka]

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....🤷‍♀️

[shawnb61]

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

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?

[albertlast]

how do the upgrade realize that the 2.0 is innodb?

[Ulibka]

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]

[Arantor]

cp1251 is not a supported character set in SMF,btw. Expect odd bugs.

[Ulibka]

cp1251 is not a supported character set in SMF,btw. Expect odd bugs.

may be.
But UTF-8 double data size 

[Arantor]

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

[Ulibka]

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 ?

[Arantor]

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)

[Ulibka]

Thank you!

[Antes]

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 ?

[Arantor]

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.