PRIMARY_KEY is missing in multiple tables in mysql

Describe the bug

Multiple tables have the PRIMARY_KEY missing in the mysql driver, which makes memos incompatible with DigitalOceans managed database. This is due to these databases having the variable sql_require_primary_key set to ON for replication purposes (so it's not just limited to DigitalOcean databases, but to all mysql databases that have this setting on).

The "correct" fix would probably be to just give out primary keys to all tables; a workaround might be to use

SET SESSION sql_require_primary_key = 'OFF'

before creating the tables.

Log excerpt from the docker compose:

memos  | mode: prod
memos  | driver: mysql
memos  | ---
memos  | 2024/07/21 13:53:10 ERROR failed to migrate database !BADKEY="failed to exec latest schema: Error 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.\ngithub.com/usememos/memos/store/db/mysql.(*DB).prodMigrate\n\t/backend-build/store/db/mysql/migrator.go:79\ngithub.com/usememos/memos/store/db/mysql.(*DB).Migrate\n\t/backend-build/store/db/mysql/migrator.go:30\nmain.init.func1\n\t/backend-build/bin/memos/main.go:53\ngithub.com/spf13/cobra.(*Command).execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.1/command.go:989\ngithub.com/spf13/cobra.(*Command).ExecuteC\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.1/command.go:1117\ngithub.com/spf13/cobra.(*Command).Execute\n\t/go/pkg/mod/github.com/spf13/cobra@v1.8.1/command.go:1041\nmain.Execute\n\t/backend-build/bin/memos/main.go:101\nmain.main\n\t/backend-build/bin/memos/main.go:190\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:271\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1695"
memos exited with code 0

Steps to reproduce

Any mysql database with sql_require_primary_key set to ON
Simply start the Docker compose (without any previous data)

The version of Memos you're using.

latest

Screenshots or additional context

No response

This seems to fix the issue of starting up:

 git diff store/
diff --git a/store/db/mysql/migration/prod/LATEST__SCHEMA.sql b/store/db/mysql/migration/prod/LATEST__SCHEMA.sql
index 3a3fb7d0..7e21eec3 100644
--- a/store/db/mysql/migration/prod/LATEST__SCHEMA.sql
+++ b/store/db/mysql/migration/prod/LATEST__SCHEMA.sql
@@ -1,4 +1,5 @@
 -- migration_history
+SET SESSION sql_require_primary_key = 'OFF';
 CREATE TABLE `migration_history` (
   `version` VARCHAR(256) NOT NULL PRIMARY KEY,
   `created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
@@ -130,3 +131,5 @@ CREATE TABLE `reaction` (
   `reaction_type` VARCHAR(256) NOT NULL,
   UNIQUE(`creator_id`,`content_id`,`reaction_type`)  
 );
+
+SET SESSION sql_require_primary_key = 'ON';
\ No newline at end of file

But I get into trouble when trying to add a new memo, not sure if it is related though:

2024/07/21 15:30:03 ERROR server error method=/memos.api.v1.MemoService/CreateMemo error="Error 1054 (42S22): Unknown column 'COMMENT' in 'on clause'"

usememos / memos