Support MySQL

[jprusch]

RIght now, I get:

error [2023-06-23 08:25:18.948 +02:00] Unable to activate plugin caller="app/plugin.go:171" plugin_id=mattermost-ai error="this plugin is only supported on postgres"

[it33]

Hi @jprusch after many years of trying to make things work, we've made the decision to start moving away from MySQL as a Mattermost community, primarily because of reliability and performance issues at scale, and shifting to PostgreSQL.

We're going to be sharing out guidance for existing users and customers around migrating to PostgreSQL as this gets underway, and our new investments will start guiding towards PostgreSQL.

We realize this may narrow the number of people and organizations that participate in our community, however we believe being more streamlined in our support matrix is going to result in stronger results for the community that comes along with us.

[jprusch]

Well that forces customers who have a strong MySQL knowledge to switch to PostgreSQL presumingly leading to more support requests. Performance might be an issue on high-scale implementations for thousands of users. These customers naturally license the enterprise version & got support from Mattermost. Medium & small customers with 10 - 1000 users will not hit any of the performance limits. Running MySQL since 2007 we never had any reliability issues & have we a working replication out of the box. I just came about this article:

https://www.percona.com/blog/mysql-or-postgresql-which-is-better/

The most important part is:

I used to be the Certification Manager for MySQL AB (and Sun Microsystems and Oracle) and
spoke frequently to hiring managers who routinely told me it was very hard to find 
qualified MySQL DBAs. And they said it was impossible to find qualified PostgreSQL DBAs.
If you and your staff have experience and skill in one of the databases, 
then you will probably skew your criteria in that direction 

That about says it all...

I wonder, how we're going to migrate our MySQL Mattermost environment to PostgreSQL without having any knowledge about how to securely run & maintain a PostgreSQL infrastructure. And no, we're not going to use Docker.

[jprusch]

I'm also following the performance channel on Community

https://community.mattermost.com/core/pl/jfbu7hyaffnffjicjo19tuxbfh

It would be more honest to just say that two supported RDBMSs is one too much & there is no manpower to support two.

[it33]

Thanks @jprusch, that's a better way to put it, we don't have the capacity to support two databases.

Most of the support effort happens at scale, and most of it happens with MySQL. By transitioning our community and customers to PostgreSQL we can transfer time and energy from support to platform innovation.

This may result in a smaller community with fewer admins who can run the full system, or it may mean the energy transitioned to innovation brings in a bigger community who enjoy broader and deeper benefits, or the outcome could be something in-between or different.

[it33]

This all said, we're still in the very early days of this project, and the story is just starting to unfold.

When Mattermost itself started, we were in the opposite position--MySQL-only and adding PostgreSQL only after GitLab required it for us to be part of the their omnibus.

[Bolderus]

Hello @it33 , we are using mysql and want to use plugin. if the plugin is not supported with mysql when you are going to be share out guidance for existing users and customers around migrating to PostgreSQL. We need your help and waiting to hearing from you. Best Regards!

[grrinch]

I second for the MySQL support.

Alternatively it would be great to have some guidelines from Mattermost team. Or, ideally, a migrator that can just take in the new Postgres config details and perform the migration MySQL -> PostgreSQL.

It's a pitty this plugin only supports Postgres :(

[it33]

when you are going to be share out guidance for existing users and customers around migrating to PostgreSQL. We need your help and waiting to hearing from you.

Or, ideally, a migrator that can just take in the new Postgres config details and perform the migration MySQL -> PostgreSQL.

Agree, Mattermost needs to provide this--the schemas between MySQL and PostgreSQL are similar but not identical and we need to work with our community to provide migration guidance. Let me circle back with @chenilim and see when we might be able to provide more guidance.

[jprusch]

Pretty sure we've to go thru:

https://docs.mattermost.com/manage/bulk-export-tool.html

[grrinch]

Pretty sure we've to go thru:

https://docs.mattermost.com/manage/bulk-export-tool.html

@jprusch I've found 2 exchanges where people seem to be successfully using pg_loader for this:

• https://forum.mattermost.com/t/migrating-from-mariadb-to-postgresql-db/14194
• https://github.com/mattermost/mattermost/issues/18351#issuecomment-1291635384

I'll be migrating this weekend based on the information from these 2 posts, so hopefully will be able to report back how it's gone.

[it33]

We have deployments that have exported from MySQL and attempted to import into PostgreSQL and the schemas are close but not exact, we could also potentially also see if community has done this before and what issues they hit and fixes from a real world perspective, in addition to working the migration into the product and engineering backlog,

It really depends on if someone from the community has the skill and inclination to make a contribution in this area, after solving the issue for themselves,

Side note: We've been running experiments internally on ChatGPT 4, and something like a migration from MySQL to PostgreSQL schema using the existing export function could be an interesting use case.

UPDATE: It seems--in a very healthy and positive way--our community is a little ahead of our product and engineering backlog looking into MySQL and PostgreSQL migration, as showcased by the post just before this one.

[nab-77]

Pretty sure we've to go thru:

https://docs.mattermost.com/manage/bulk-export-tool.html

@jprusch using mmctl and the export/import workflow would indeed be a valid path but we are focusing on a tool that will migrate directly from mysql' topgsqland so reduce overhead in the task. As @grrinch points out a utility likepg_loader` is in the candidate list today.

Anyone looking to discuss/contribute, take part in early testing or such, please reach out to me over at community.mattermost.com.

[cahaseler]

Also frustrated by this - all the resources I can find that allow me to easily deploy a mattermost server to Azure (the kubernetes guide and the bitnami packaged VM) only support mySQL, but apparently it's essentially deprecated to new features like your AI plugin and there's no migration path provided? Seems like you need to be focusing on critical core features like what database you're using before spending time on AI addons.

[jprusch]

Many customers still use MySQL and as long as there is no clear & easy migration part, there will be no customer-based feedback on the AI plugin. I'm still willing to switch, but I will not do this without a documented path on what I've got to do. Another problem is the non-existing knowledge on PostgreSQL in my company...

[it33]

@cahaseler @jprusch agree with your input. It doesn't make sense to have all the default point to MySQL and yet only support PostgreSQL for AI.

We'll take an action internally to discuss and share back.

[Bolderus]

it33 is there any news? about mysql support?

[it33]

@Bolderus we're working providing more support for migrating from MySQL to PostgreSQL. The community is correct that it's somewhat confusing having a number of default deployment options install MySQL after we're announcing a move away from the platform.

Let us figure out internally who is the right person to speak on this more publicly. To be transparent, there are no plans to support OpenOps on MySQL due to challenges we foresee supporting deployments at scale, which could lower the velocity of the work, as it has in other Mattermost workstreams.

[jprusch]

I can only repeat: As long as there is not clear and easy migration process, you won't get any feedback of customers already on MySQL. Nobody sets up a new installation when Mattermost is already running and implemented in company processes. I follow the discussions around OpenOps, the AI channel & the great YouTube videos on the achievements you make, but I've no time to just set up a new environment to play around with it & check use cases for my coworkers.

[cahaseler]

Entirely reasonable approach - I can see why you want to use PG as the solution, we all just need help getting there!

On Sat, Aug 26, 2023 at 12:11 AM it33 @.***> wrote:

@Bolderus https://github.com/Bolderus we're working providing more support for migrating from MySQL to PostgreSQL. The community is correct that it's somewhat confusing having a number of default deployment options install MySQL after we're announcing a move away from the platform.

Let us figure out internally who is the right person to speak on this more publicly. To be transparent, there are no plans to support OpenOps on MySQL due to challenges we foresee supporting deployments at scale, which could lower the velocity of the work, as it has in other Mattermost workstreams.

— Reply to this email directly, view it on GitHub https://github.com/mattermost/mattermost-plugin-ai/issues/19#issuecomment-1694151301, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2XKRFLL2NV4KDGPYEIYPTXXFZPBANCNFSM6AAAAAAZREOTOE . You are receiving this because you were mentioned.Message ID: @.***>

[it33]

Definitely appreciate all the feedback,

We have @nab-77 who will be the right owner internally at Mattermost to discuss MySQL to PostgreSQL migration, and I think he's posted in different forums on the topic. @nab-77 is on @wiersgallak's team with @chenilim, and I believe he's back tomorrow.

For context, the original intention of OpenOps is to be a "sandbox" to try out LLM and GenAI features outside of production, before flowing confidential data into 3rd party backends, setting policy and governance on acceptable usage, etc.

Original announcement here:

• Open source AI-enhanced chat collaboration sandbox accelerates evaluation of generative AI models and usage policies in real world workflows while maintaining full data control

For this reason, we invested in options like Gitpod, showcased by team members like @azigler, to try out the features of the platform and clear out the trust and safety priorities ahead of rollout.

At the same time, we realize there are many deployments that are further ahead than we anticipated for deploying use cases to production, where PostgreSQL migration is a larger issue than we realized.

[nab-77]

@jprusch @cahaseler We plan to publish the first draft of the mysql-to-pgsql guide later today (on docs.mattermost.com). This first draft focuses on channels data, with playbooks/boards following later. Your thoughts, testing & feedback will be greatly appreciated.

As soon as it publishes I will share here.

[nab-77]

@jprusch @cahaseler as discussed we have published the first draft here. https://docs.mattermost.com/deploy/postgres-migration.html

Any thoughts feedback please share, if possible, at community.mattermost.com (@neil.barnett).

[Bolderus]

Thanks for migration Migration guidelines but ı couldnt start comand morph apply up --driver postgres --dsn "postgres://user:pass@localhost:5432/mydb?sslmode=disable" --path ./db/migrations/postgres --number 1 and I am getting error usage: morph [options]

how can I solve this problem ? also is it possible for someone make a video Migration guidelines from MySQL to PostgreSQL. really I will be so happy!

Best Regards!

[Bolderus]

Anybody can help above the problem ? does anybody managed migration and also is it posible for someone upload video for migration process ? Really I need your help!

Best Regards!

[Bolderus]

is it possible for someone to make a migration video ? I need to migrate but commands not working. anybody please help me ?

[grrinch]

Sorry, I know this is a bit old now, but I tried the migration and failed miserably.

Even though I got through initial issues with go installation and some additional scripts (for some reason morph and dbcmp just did not wanna work out-of-the-box for me), I was never able to get the migration scripts to work.

I don't remember exactly what it was complaining, but something with the syntax in this file:

LOAD DATABASE
     FROM      mysql://{{ .mysql_user }}:{{ .mysql_password }}@mysql:3306/{{ .source_schema }}
     INTO      pgsql://{{ .pg_user }}:{{ .pg_password }}@postgres:5432/{{ .target_schema }}

WITH data only,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000,
     create no tables, create no indexes,
     preserve index names

SET PostgreSQL PARAMETERS
     maintenance_work_mem to '128MB',
     work_mem to '12MB'

SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'

CAST column Channels.Type to "channel_type" drop typemod,
     column Teams.Type to "team_type" drop typemod,
     column UploadSessions.Type to "upload_session_type" drop typemod,
     column Drafts.Priority to text,
     type int when (= precision 11) to integer drop typemod,
     type bigint when (= precision 20) to bigint drop typemod,
     type text to varchar drop typemod,
     type tinyint when (<= precision 4) to boolean using tinyint-to-boolean,
     type json to jsonb drop typemod

EXCLUDING TABLE NAMES MATCHING ~<IR_>, ~<focalboard>, schema_migrations

BEFORE LOAD DO
     $$ ALTER SCHEMA public RENAME TO {{ .source_schema }}; $$,
     $$ DROP INDEX IF EXISTS idx_posts_message_txt; $$,
     $$ DROP INDEX IF EXISTS idx_fileinfo_content_txt; $$

AFTER LOAD DO
     $$ UPDATE {{ .source_schema }}.db_migrations set name='add_createat_to_teamembers' where version=92; $$,
     $$ CREATE INDEX IF NOT EXISTS idx_posts_message_txt ON {{ .source_schema }}.posts USING gin(to_tsvector('english', message)); $$,
     $$ CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON {{ .source_schema }}.fileinfo USING gin(to_tsvector('english', content)); $$,
     $$ ALTER SCHEMA {{ .source_schema }} RENAME TO public; $$,
     $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,
     $$ ALTER USER {{ .pg_user }} SET SEARCH_PATH TO 'public'; $$;

I know this isn't much help, but I think I'll have another attempt once again.

Now, what I tried as an alternative was to also do a full export from the instance with MySQL, copy it over to another server already running Postgres and import the massive zip file.

It also did not work... 😞

This time, however, importer was complaining that it cannot add message reactions for specific users who were not existent.

I'm not sure if the inactive users were not exported or maybe if the code is validating if the users are active for reactions, but this was where it was always failing mid-way-through.

Now, I know it may sound weird that I had inactive users' reactions on messages, but it actually makes sense. When our MySQL server is running for something close to 2 years, some users were deactivated in that time, but their messages and reactions obviously remained on the server.

So, ye, no bueno, unfortunately 👎

I'll try to post an update when I come around to having another go at it.

Cheers

[jprusch]

TBH, without a working migration from MySQL to Postgres, not only for this plugin, all customers/users still on MySQL cannot test anything on the various new features. A working migration must be #1 priority IMHO.

[nab-77]

@grrinch are you signed up at community.mattermost.com? If you are we can look at your migration issue (and then I can bring learnings back here).

[grrinch]

hey @nab-77 - I am although not very active. Look for the same nickname. TBH I don't know what channel I should start the convo about the migration there 😆

[se4n01]

we are also stuck on the seemingly abandoned mysql (I also prefer postgres), I can't do the migration for like reasons. I have succeeded to import the main structure but no messages were imported. +1 for an actual working migration path so we can use these important new features.