2022-09-10, 01:13 AM
Mods, if this is inappropriate please direct me otherwise.
On the MyBB Discord I've been discussing the migration in the title, and I've found a number of things that are weird. I'm looking to make documentation about doing it anyways, so I wanted to discuss both methods I've tried, and also source from people smarter than me how to fix issues here:
Methods that didn't work
Merge System. See this thread: https://community.mybb.com/thread-236487-lastpost.html
python/ruby mysql2postgresql. Didn't get off the ground due to not being updated in years.
mysqldump --compatible=postgresql
Why do this?
A bunch of reasons:
MariaDB/MySQL has made a serious number of crufty errors that were not smart:
See UTF8/UTF8MB4 debacle, which continues to break things
InnoDB is unreliable in crash recovery. MyISAM is obsolete, Aria is mehhhhhh.
Out of the box, a requirement to dump a db to get a backup
Galera is not stock with MariaDB but a separate addon, otherwise you have standard master/slave replication.
Lots of other annoying things. That's enough for me, at least to consider postgres, take a small performance hit, lose native fulltext search, etc.
What has worked (partially)?
pgloader3, suggested by Euan worked partially. The general method to get a partial working forum is:
That cast is needed to prevent an erroneous tinyint - > bool type which will break everything.
You'll need to update the database driver, make sure that the utf8mb4 strings in config.php are instead utf8, and probably tweak other stuff
What is still not working, for us?
1. Plugins with single quotes in messages and such I can't figure out how to escape properly. \' makes an error. I'm familiar with admining postgres, but not with development of it so this simple issue is still being researched in my spare time.
2. Optimize Databases, Convert UTF8, and numerous other things are broken.
3. Cache reload is broken. I don't know why, the error is that this query is improper, and I can tell it is:
"select count() as count from mybb_datacache where = '' limit 1" It's not a table thing AFAICT? I'm still looking into it.
I've still got a lot to review and test.
Approaches?
1. I could use some help coming up with other cast rules for mybb -- I'm not 100% sure what type conversions could be causing other internal issues?
2. I'm coming up with tables that can be dropped and recreated that only hold transient data, e.g. data that is mostly written to and then read but that can be truncated without issues. Thus far, my list includes:
Obviously purge your mail queue, lock down your site, backup logs etc. But AFAICT, these all contain nonessential data that can be essentially done without losing posts or PMs or anything.
I'm looking for help in documenting this and making it better, if you guys care enough to help that would be grand! Eventual goal is a postgres migration guide!
On the MyBB Discord I've been discussing the migration in the title, and I've found a number of things that are weird. I'm looking to make documentation about doing it anyways, so I wanted to discuss both methods I've tried, and also source from people smarter than me how to fix issues here:
Methods that didn't work
Merge System. See this thread: https://community.mybb.com/thread-236487-lastpost.html
python/ruby mysql2postgresql. Didn't get off the ground due to not being updated in years.
mysqldump --compatible=postgresql
Why do this?
A bunch of reasons:
MariaDB/MySQL has made a serious number of crufty errors that were not smart:
See UTF8/UTF8MB4 debacle, which continues to break things
InnoDB is unreliable in crash recovery. MyISAM is obsolete, Aria is mehhhhhh.
Out of the box, a requirement to dump a db to get a backup
Galera is not stock with MariaDB but a separate addon, otherwise you have standard master/slave replication.
Lots of other annoying things. That's enough for me, at least to consider postgres, take a small performance hit, lose native fulltext search, etc.
What has worked (partially)?
pgloader3, suggested by Euan worked partially. The general method to get a partial working forum is:
pgloader --cast 'type tinyint to smallint drop typemod' mysql://user:password@localhost/mybb pgsql://user:password@localhost/mybbThat cast is needed to prevent an erroneous tinyint - > bool type which will break everything.
You'll need to update the database driver, make sure that the utf8mb4 strings in config.php are instead utf8, and probably tweak other stuff
What is still not working, for us?
1. Plugins with single quotes in messages and such I can't figure out how to escape properly. \' makes an error. I'm familiar with admining postgres, but not with development of it so this simple issue is still being researched in my spare time.
2. Optimize Databases, Convert UTF8, and numerous other things are broken.
3. Cache reload is broken. I don't know why, the error is that this query is improper, and I can tell it is:
"select count() as count from mybb_datacache where = '' limit 1" It's not a table thing AFAICT? I'm still looking into it.
I've still got a lot to review and test.
Approaches?
1. I could use some help coming up with other cast rules for mybb -- I'm not 100% sure what type conversions could be causing other internal issues?
2. I'm coming up with tables that can be dropped and recreated that only hold transient data, e.g. data that is mostly written to and then read but that can be truncated without issues. Thus far, my list includes:
mybb_adminlog
mybb_adminsessions
mybb_datacache
mybb_debuglogs
mybb_mailerrors
mybb_maillogs
mybb_mailqueue
mybb_moderatorlog
mybb_questionsessions
mybb_searchlog
mybb_sessions
mybb_spamlog
mybb_tasklogObviously purge your mail queue, lock down your site, backup logs etc. But AFAICT, these all contain nonessential data that can be essentially done without losing posts or PMs or anything.
I'm looking for help in documenting this and making it better, if you guys care enough to help that would be grand! Eventual goal is a postgres migration guide!