search

OAGi / Score

Score
MIT License
9 stars 6 forks source link

Convert Database to MariaDB #1526

Closed lmkennethburnett closed 1 year ago

lmkennethburnett commented 1 year ago

Newer versions of mysql offers features such as the ability to accomplish recursive queries that might be useful, for example, when querying a chain of self-referencing manifest tables.

dubnemo commented 1 year ago

@lmkennethburnett what are your thoughts on MariaDB in lieu of MySQL? Our infrastructure team has a bullseye on MySQL, and wants it to go away. ;-)

lmkennethburnett commented 1 year ago

MariaDB is preferable to me. However, I just checked and the newer version of both MySQL and MariaDB are not passing our vulnerability scans, so I'll need to look into that. The last version of MariaDB approved is 10.3. Postgres is an approved alternative, if that is even a possibility.

hakjuoh commented 1 year ago

@lmkennethburnett Although all mentioned vendors support the CTE through the WITH query, I'm not sure whether there's a difference among them or not. Do you have any example query?

hakjuoh commented 1 year ago

@lmkennethburnett We found out that MariaDB uses LONGTEXT data type as a substitute of JSON data type in MySQL. It forces to change log.snapshot column data type to LONGTEXT. Fortunately, it supports mysql_json plugin from MariaDB 10.5.7. Thus, we tested all functions in Score using the official MariaDB 11.0.2 docker image. It ran through the following command.

docker run -p 3306:3306 -d mariadb:11.0.2 --plugin-load-add=type_mysql_json

As a result, we found no issues and we are ready to move. The remaining issue is how we migrate data from MySQL. We will go over this issue.