database migration and datetime-related columns

Description / Beschreibung

We are currently in preparation for upgrading/migrating our instance from 1.10.0 (running on an Rocky 8 Machine with Python 3.6 and MariaDB 10.3) to 2.3.0 (new machine with python 3.11 and mariadb 11.4)

This instance was initially created in 2018 on a CentOS 7 Machine.
I do not know the exact version, but I assume we created the database with MariaDB 5.5.

I dumped/restored the productive database to a fresh 11.4.5 database, installed rdmo + deps and migrated our rdmo-app + implemented the changes.
After "python manage.py upgrade", it looks that everything is fine.
But there was one thing:

• the first time we insert a value into a question, it is fine (in the browser "developer tools" you see a PUT, POST,GET)
• now, we edit the value again -> PUT with 400 and on the page "An Error occured. Please contact support" appears.
• in the PUT response, you get 'conflict: ["A newer version of this value was found."]'

with a fresh database and a small imported catalogue, this does not happen.

I compared both old and new database and saw the reason.
In our old 1.10.0 database all time-related columns were created with the "datetime" data type, where new databases use "datetime(6)" for storing microseconds.
(The support for datetime(6) came with MySQL 5.6)
Even after migration/upgrading to 2.3.0, our database still use datetime.
This lead to a conflict during updating fields, as both "created" and "updated" in the database have the same value.
With some "ALTER TABLE" and "UPDATE" statements I converted our database to datetime(6) and everything works now.
I also changed the collation from utf8mb3_bin to utf8mb4_uca1400_ai_ci as I imported the dump, (some of our old database tables/columns used utf8mb3_bin, where as new tables and a complete new database use utf8mb4_uca1400_ai_ci )

Expected behaviour / Erwartetes Verhalten

During Migration of existing databases, the datetime type should be converted to datetime(6).
Or atleast a warning should be given, as microsecond support is crucial for the new responsive design.
Maybe it should also warn about collations utf8 not utf8mb4_uca1400_ai_ci (if there is something related to this in the future)

Steps to reproduce / Schritte zum Reproduzieren

1. Create an (ancient) rdmo instance with MariaDB 5.5
2. Try to upgrade to 2.3.0 with the same database content, dumped/restored to a more recent mariadb database
3. Try to change values twice in an interview
4. See error

Context / Kontext

productive instance: Rocky 8, Python 3.6.8, RDMO 1.10.0, MariaDB 10.3, migrated somewhere in time from CentOS7 with MariaDB 5.5
development instance: Container with Python 3.11, RDMO 2.3.0, MariaDB 11.4.5

I upload my "ALTER TABLE" + "UPDATE" statements, if somebody has the same problem

rdmo_alter_datetime.txt

Best Regards

Start with the migration path invoked by python manage.py upgrade and compare it with the attached rdmo_alter_datetime.txt statements. Reproduce the upgrade from an old MariaDB database, then verify that datetime columns retain microsecond precision or that a clear warning is emitted and repeated value updates no longer produce the conflict.