search

ferdium / ferdium-server

The server component that can either be self-hosted or hosted for general purpose - for use with the ferdium thick client.
MIT License
159 stars 40 forks source link

Multiple MySQL DB errors after moving to v2 #122

Open tigattack opened 4 months ago

tigattack commented 4 months ago

Hi, after moving from 1.3.16 to 2.0.6 (also seeing the same behaviour with all other 2.0.x versions), I'm receiving a couple of DB errors in the container logs:

❯ error database/migrations/1503250034279_user
❯ migrating database/migrations/1696110557648_jwt_tokens
[ error ]  create table `users` (`id` int unsigned not null auto_increment primary key, `username` varchar(80) not null, `email` varchar(254) not null, `password` varchar(60) not null, `settings` json, `created_at` datetime, `updated_at` datetime) - ER_TABLE_EXISTS_ERROR: Table 'users' already exists
    at Query.Sequence._packetToError (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:335:12)
    --------------------
    at Protocol._enqueue (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:198:25)
    at /app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/dialects/mysql/index.js:132:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/dialects/mysql/index.js:126:12)
    at executeQuery (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/client.js:154:12)
    at /app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/transaction.js:371:24
    at new Promise (<anonymous>)
    at Client_MySQL.trxClient.query (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/transaction.js:366:12)
{"level":30,"time":1710873492772,"pid":74,"hostname":"4ed4afac9397","name":"ferdium-server","msg":"started server on 0.0.0.0:3333"}
{"level":50,"time":1710875134141,"pid":74,"hostname":"4ed4afac9397","name":"ferdium-server","err":{"type":"Error","message":"insert into `jwt_tokens` (`created_at`, `expires_at`, `name`, `refresh_token`, `refresh_token_expires_at`, `token`, `type`, `user_id`) values ('2024-03-19 19:05:34', NULL, 'JWT Access Token', 'REDACTED', '2024-03-29 19:05:34', 'REDACTED', 'jwt_token', 1) - ER_NO_SUCH_TABLE: Table 'ferdium.jwt_tokens' doesn't exist","stack":"Error: ER_NO_SUCH_TABLE: Table 'ferdium.jwt_tokens' doesn't exist\n    at Query.Sequence._packetToError (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)\n    at Query.ErrorPacket (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)\n    at Protocol._parsePacket (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:291:23)\n    at Parser._parsePacket (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Parser.js:433:10)\n    at Parser.write (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Parser.js:43:10)\n    at Protocol.write (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:38:16)\n    at Socket.<anonymous> (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:88:28)\n    at Socket.<anonymous> (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:526:10)\n    at Socket.emit (node:events:517:28)\n    at addChunk (node:internal/streams/readable:335:12)\n    --------------------\n    at Protocol._enqueue (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)\n    at Connection.query (/app/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:198:25)\n    at /app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/dialects/mysql/index.js:132:18\n    at new Promise (<anonymous>)\n    at Client_MySQL._query (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/dialects/mysql/index.js:126:12)\n    at executeQuery (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)\n    at Client_MySQL.query (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/client.js:154:12)\n    at Runner.query (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/runner.js:141:36)\n    at ensureConnectionCallback (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)\n    at Runner.ensureConnection (/app/node_modules/.pnpm/knex@2.5.1_mysql@2.18.1_pg@8.11.1_sqlite3@5.1.6/node_modules/knex/lib/execution/runner.js:300:14)","code":"ER_NO_SUCH_TABLE","errno":1146,"sqlMessage":"Table 'ferdium.jwt_tokens' doesn't exist","sqlState":"42S02","index":0,"sql":"insert into `jwt_tokens` (`created_at`, `expires_at`, `name`, `refresh_token`, `refresh_token_expires_at`, `token`, `type`, `user_id`) values ('2024-03-19 19:05:34', NULL, 'JWT Access Token', 'REDACTED', '2024-03-29 19:05:34', 'REDACTED', 'jwt_token', 1)","status":500},"msg":"insert into `jwt_tokens` (`created_at`, `expires_at`, `name`, `refresh_token`, `refresh_token_expires_at`, `token`, `type`, `user_id`) values ('2024-03-19 19:05:34', NULL, 'JWT Access Token', 'REDACTED', '2024-03-29 19:05:34', 'REDACTED', 'jwt_token', 1) - ER_NO_SUCH_TABLE: Table 'ferdium.jwt_tokens' doesn't exist"}
SpecialAro commented 3 months ago

In fact, before updating the adonisjs dependency I did account for SQLITE databases, but forgot about other databases.

Instead of removing the logic of migrations in your PR I would suggest to adapt the script to fit MYSQL when the DB_CONNECTION != sqlite (for instance)