Many-to-many MySQL relationship giving erroneous "Ensure non-key columns are nullable or auto-generated" error

[rlad]

Checklist

• [x ] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method:
  • Budibase Version: <2.26.4>
  • App Version:
• Cloud
  • Tenant ID:

Describe the bug
Following the tutorial at:
https://docs.budibase.com/docs/mysql-mariadb

I get the error:

"Ensure non-key columns are nullable or auto-generated"

when attempting to define the many-to-many relationship using dept_emp, even after having changed the from_date and to_date fields to nullable using:

ALTER TABLE employees.dept_emp
CHANGE COLUMN from_date from_date DATE NULL ,
CHANGE COLUMN to_date to_date DATE NULL ;

I verified that the table was correctly changed using DESCRIBE.

Additionally, the tutorial says to define the:
One Employee -> Many Titles (Historical)
relationship first, then update the dept_emp table to have nullable fields, then has the note:
"Make sure to re-fetch the tables to pull down the new schema information!"

Following this advice deletes the "One Employee -> Many Titles (Historical)" relationship that was just defined. Deleting all relationships whenever the schema needs to be updated is extremely inconvenient and appears not to be the intended behavior.

To Reproduce
Follow the steps as described above.

Expected behavior
Expected is that steps described in the tutorial should work!

[linear]

BUDI-8258 Many-to-many MySQL relationship giving erroneous "Ensure non-key columns are nullable or auto-generated" error

[rlad]

Hosting method is Digital Ocean btw.

[ConorWebb96]

Ok so I've had a look into this and was able to replicate.

You are right, for whatever reason the schema doesn't seem to be updated after running the query then refetching the tables.

ALTER TABLE `employees`.`dept_emp` 
CHANGE COLUMN `from_date` `from_date` DATE NULL ,
CHANGE COLUMN `to_date` `to_date` DATE NULL ;

It seems you have to remove the tables and completely refetch them in order to get passed this error. However, doing this will remove any previously setup Budibase relationships.

Thanks for bringing this to our attention.