data loss upgrading from 2.9.1 to 3.1.0 with database backend

[mfisco]

Describe the problem

Upgrading from 2.9.1 to 3.1.0 and running python manage migrate, both migrations appear successful, but the data isn't transferred, nor is the old table dropped.

Steps to reproduce

1. Install version 2.9.1 and make a couple of sample settings, changing from their defaults.
2. Update to 3.1.0 and run python manage.py migrate
3. Inspect the DB table and notice the new constance_constance table created from 0001_initial is empty, and the old constance_config table still exists.

After some debugging, I think it concerns the SQL in 0002_migrate_from_old_table. Specifically, If I remove the try/except block, I get the following exception:

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax
to use near 'key, value ) SELECT id, key, value FROM constance_config' at line 1")

But by escaping the column names (e.g. ( id, key, value ) → ( `id`, `key`, `value` )), I was able to get the 2nd migration to work (data transferred/old table dropped), leading me to believe it's because of SQL reserved words.

System configuration

• Django version: 4.2.4
• Python version: 3.10.6
• Django-Constance version: 3.1.0
• MySQL version: 8.0.18

[sergei-iurchenko]

It will be fixed soon #531
Sorry, my fault

[mfisco]

No worries and thanks for your help!

[Volody2006]

It seems that fixing this error caused an error for me.

System configuration
Django version: 3.1
Python version: 3.9
Django-Constance version: 3.1.0
MySQL version: 8.0.22

Using connection = schema_editor.connection does not work in my case.

[sergei-iurchenko]

@Volody2006 show traceback please

[jasisz]

I have just experienced the very same issue with MySQL, fixed it on my own and later found this issue...

If anyone lands in here looking for the answer "why": this migration may be indeed fixed, but it is not yet released.

I am not sure if the fix works for me either, I had to run this sqls manually to fix it (state was already messed up a bit):

DELETE FROM constance_constance;
INSERT INTO constance_constance SELECT * FROM constance_config;
DROP TABLE constance_config;

[mfisco]

On that note, would it be possible to generate a new release with the corrected migration file?

[stitch]

Our experience with upgrading from 2.9.1 to 3.1.0 was a bit shocking. The newly created table constance_constance was not filled with existing data. The old table constance_config was preserved (luckily). The incomplete transfer also caused the admin interface not saving things and showing a lot of defaults.

We've fixed it by running the following query in the database:

truncate table constance_constance;
insert into constance_constance select * from constance_config;

Hope this helps someone that is feeling the stress :)

[schefDev]

To pick up on @mfisco's comment again:
#531 is essential as there is a high risk of data loss, but unfortunately it has not yet been released.

Would it be possible to generate a new release? @camilonova or @sergei-iurchenko
Or at least a warning in the README that there is a risk of data loss with version 3.1.0 would certainly not be amiss.

So just a suggestion, don't stress about it. Thanks a lot!

[sergei-iurchenko]

@camilonova please make a new release. I don`t have such permissions