CODEWITHSUNDEEP
mysqlamazon-web-servicesaws-fargatemetabaseaws-aurora-serverless
jjno91
jjno91

Reputation: 653

All Metabase operations returning "Field 'id' doesn't have a default value"

We're moving our Metabase v0.24.1 deployment from AWS RDS MySQL 5.7 to AWS Aurora MySQL Serverless (5.6 compatible). When we launch Metabase every query the the application tries to run returns will some form of this error:

Field 'id' doesn't have a default value

Full Error:

03-17 16:07:57 [1mERROR metabase.middleware[0m :: [31mPOST /api/database 500 (68 ms) (0 DB calls)[0m
{:message "Field 'id' doesn't have a default value",
 :stacktrace
 ["api.database$fn__29894$fn__29897.invoke(database.clj:233)"
  "api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:229)"
  "api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:224)"
  "api.database$fn__29894.invokeStatic(database.clj:215)"
  "api.database$fn__29894.invoke(database.clj:215)"
  "middleware$enforce_authentication$fn__38784.invoke(middleware.clj:120)"
  "api.routes$fn__38908.invokeStatic(routes.clj:58)"
  "api.routes$fn__38908.invoke(routes.clj:58)"
  "routes$fn__39540$fn__39541.doInvoke(routes.clj:64)"
  "routes$fn__39540.invokeStatic(routes.clj:60)"
  "routes$fn__39540.invoke(routes.clj:60)"
  "middleware$log_api_call$fn__38883$fn__38885.invoke(middleware.clj:329)"
  "middleware$log_api_call$fn__38883.invoke(middleware.clj:328)"
  "middleware$add_security_headers$fn__38833.invoke(middleware.clj:243)"
  "middleware$bind_current_user$fn__38788.invoke(middleware.clj:140)"
  "middleware$maybe_set_site_url$fn__38837.invoke(middleware.clj:266)"],
 :sql-exception-chain ["SQLException:" "Message: Field 'id' doesn't have a default value" "SQLState: HY000" "Error Code: 1364"]}

Attempts to add a new database also return the same error through the Metabase UI.

We have validated that the RDS db parameters are identical between the database we are migrating to and from excluding the engine default configurations that may change between Aurora Serverless and MySQL 5.7 defaults.

Another notable change is that we're moving from normal ECS tasks running on EC2 instances that we own over to Fargate tasks, but the task definitions are identical between the two.

Edit: I've found another configuration inconsistency between Aurora Serverless and MySQL 5.7 in the sql_mode parameter. In 5.7, it is set to NO_ENGINE_SUBSTITUTION by default and in serverless it is set to 0.

After updating sql_mode on my serverless deployment to NO_ENGINE_SUBSTITUTION I've found that the actual configuration remains inconsistent in the database itself. SELECT @@sql_mode; returns NO_ENGINE_SUBSTITUTION on the 5.7 deployment and it returns STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION on the serverless deployment even though the parameter group is not set to this value.

Edit 2: I failed to mention the method that we are using to transfer the data to the new database and that is AWS DMS version 3.3.1. It appears that DMS does not replicate the auto_increment table attribute which needs to be present on the id columns in the Metabase database. This could likely be the root issue.

Upvotes: 0

Views: 388

Answers (1)

jjno91
jjno91

Reputation: 653

The root cause of this issue was that the id columns in the Metabase DB did not have the auto_increment attribute and were being treated as if they did not have values even though they did.

This was because, AWS DMS, which we used to transfer the data, does not replicate all details of the schema:

https://dba.stackexchange.com/questions/138946/aws-dms-creates-tables-with-no-auto-increment

The fix to this issue was to perform a schema migration first using a standard mysqldump --no-data and then we populated the data with DMS.

Upvotes: 1

Related Questions