update to 2.8.5 with warnings but backup table is empty / old deleted forms are displayed

[Armin83]

Describe the bug
Update with warnings but backup table was empty

To Reproduce
yy@xx:/var/www/glpi/files/_log# cat warning_during_migration_to_2.8.5.log.20191002.bak 2019-10-02 13:19:57 [6@xx] glpi_plugin_formcreator_formanswers Tabelle existiert bereits. Es wurde eine Sicherung durchgeführt, um backup_glpi_plugin_formcreator_formanswers @ 2019-10-02 13:19:57 [6@xx] You can delete backup tables if you have no need of them. @ yy@xx:/var/www/glpi/files/_log#

Expected behavior
Backup table should not be empty - but in my case the production was on a not realeased version a few years ago but i thought now is all good with #1250. but it isn't i have 15 forms that were already declined but after upgrade they are in pending state. the problem the first 2 days we worked on production we did not see this problem. Can i restore these 15 forms from the original backup version on glpi 9.3.3. we changed to 9.4.4

[Armin83]

mysql> select * from backup_glpi_plugin_formcreator_formanswers;
Empty set (0,00 sec)

[btry]

Hi

From which version did you upgrade the plugin ?

[Armin83]

I did not take a screenshot.... but at least we had 2.6.x because we had glpi 9.3.3
-rw-r--r-- 1 www-data www-data 2186 Okt 2 13:20 migration_to_2.8.5.log.20191002.bak
-rw-r--r-- 1 www-data www-data 281 Okt 2 13:19 warning_during_migration_to_2.8.5.log.20191002.bak
-rw-r--r-- 1 www-data www-data 201 Okt 2 13:19 migration_to_2.5.2.log.20191002.bak
-rw-r--r-- 1 www-data www-data 80 Okt 2 13:19 migration_to_2.5.0.log.20191002.bak
-rw-r--r-- 1 www-data www-data 80 Okt 2 13:19 migration_to_2.4.0.log.20191002.bak

[Armin83]

1. I will check some special situation. Maybe the 15 forms are super special at were deleted in the old version but be migrated from an old other table were they don't be deleted from the webinterface ?
2. I will do a restore ot 9.4.4 after upgrade without forms upgrade and check select * from glpi_configs where context='formcreator';

@btry btw are you working for teclib ?

Do you no about the ticket 18072 a real bug and not only "my" personal migration problem or should i open here a issue too?

[btry]

1 - I don't fully understand your assumption

2 - you may try it but you also need to restore the files of Formcreator matching the version found in the backup, table glpi_plugins. If not, after accessing any web page of GLPI, the version in the DB will be replaced by the version if the currently installed version of Formcreator. I suggest you just check the version in the table glpi_plugins in the backup (if it is a SQL dump), or ensure you don't do any access to GLPI before reading the content of this table.

Yes I'm working for Teclib'. I did not received this issue : it is already handled as far I see. As you have subscribed to support (I guess), then prefer opening issues on our support platform and benefit higher priority.

[Armin83]

1. I stopped apache.
2. Restore (Backup from 9.4.4 without forms update)
3. Shema from sql was 2.6
4. I checked table glpi_plugin_formcreator_formanswers (and it's empty)
5. I dropped | glpi_plugin_formcreator_formanswers
6. I startet apache
7. I did forms update
8. Shema from sql is 2.8
9. No warnings or backup messages.

But i got sql error message at begin

[2019-10-03 16:53:24] glpisqllog.ERROR: DBmysql::query() in /var/www/glpi/inc/dbmysql.class.php line 188
*** MySQL query error:
SQL: UPDATE glpi_displaypreferences SET itemtype = 'PluginFormcreatorFormAnswer' WHERE itemtype = 'PluginFormcreatorForm_Answer'
Error: Duplicate entry '0-PluginFormcreatorFormAnswer-2' for key 'unicity'
Backtrace :
inc/dbmysql.class.php:948
plugins/formcreator/install/upgrade_to_2.7.php:45 DBmysql->update()
plugins/formcreator/install/install.php:136 PluginFormcreatorUpgradeTo2_7->upgrade()
plugins/formcreator/install/install.php:104 PluginFormcreatorInstall->upgradeOneStep()
plugins/formcreator/hook.php:50 PluginFormcreatorInstall->upgrade()
inc/plugin.class.php:521 plugin_formcreator_install()
front/plugin.form.php:47 Plugin->install()
{"user":"6@testsystem-glpi"}

[Armin83]

the strange forms have all is_deleted = 1 in the old release ;) the column might be gone through #1269.

possible answer for my problem

1. the sql error i posted or garbage from my old migration problem stop deletion of the forms
2. there should be a deletion function on migration if is_deleted = 1 in the forms. Now our user see wrong forms. they don't see theme before because they was deleted

[btry]

Hi

Your SQL error in the display preferences table might be related to old changed in the life of the plugin.The itemtype "PluginFormcreatorFormAnswer" changed several times with the name PluginFormcreatorForm_Answer (note the underscore between Form and Answer). Moreover, long ago, the plugin did not clean up properly the DB in the uninstallatin process.

I would suspect that those old problems left some inconsistencies in the DB if you used Formcreator for years.

I would suggest that you have a look in the backup before the upgrade. In Formcreator 2.6, the itemtype was named PluginFormcreator_Form_Answer. Search in the table glpi_displaypreferences Find rows having itemtype = "PluginFormcreatorFormAnswer" (without the underscore). It yes, then those rows will cause a failure when renaming display preferences from PluginFormcreator_Form_Answer to PluginFormcreator_FormAnswer.

To avoid that, just delete those lines.

I agree that when the column is_deleted is being dropped, the rows where is_deleted = 1 should be dropped.

[Armin83]

In my case the update is already done. :(

mysql> select * from glpi_configs where context='formcreator';

+-----+-------------+----------------+-------+
| id | context | name | value |
+-----+-------------+----------------+-------+
| 210 | formcreator | schema_version | 2.8 |
+-----+-------------+----------------+-------+
1 row in set (0,00 sec)

And now i have

mysql>
mysql>
mysql> select * from glpi_displaypreferences where itemtype = "PluginFormcreatorFormAnswer";
+------+-----------------------------+-----+------+----------+
| id | itemtype | num | rank | users_id |
+------+-----------------------------+-----+------+----------+
| 743 | PluginFormcreatorFormanswer | 2 | 2 | 0 |
| 744 | PluginFormcreatorFormanswer | 3 | 3 | 0 |
| 745 | PluginFormcreatorFormanswer | 4 | 4 | 0 |
| 746 | PluginFormcreatorFormanswer | 5 | 5 | 0 |
| 747 | PluginFormcreatorFormanswer | 6 | 6 | 0 |
| 1271 | PluginFormcreatorFormAnswer | 60 | 7 | 0 |
| 1273 | PluginFormcreatorFormAnswer | 9 | 9 | 0 |
| 1274 | PluginFormcreatorFormAnswer | 8 | 10 | 0 |
+------+-----------------------------+-----+------+----------+
8 rows in set (0,00 sec)

mysql>
mysql>
mysql> select * from glpi_displaypreferences where itemtype = "PluginFormcreatorForm_Answer";
+------+------------------------------+-----+------+----------+
| id | itemtype | num | rank | users_id |
+------+------------------------------+-----+------+----------+
| 841 | PluginFormcreatorForm_Answer | 2 | 2 | 0 |
| 842 | PluginFormcreatorForm_Answer | 3 | 4 | 0 |
| 843 | PluginFormcreatorForm_Answer | 4 | 5 | 0 |
| 844 | PluginFormcreatorForm_Answer | 5 | 6 | 0 |
| 845 | PluginFormcreatorForm_Answer | 6 | 7 | 0 |
| 1004 | PluginFormcreatorForm_Answer | 8 | 3 | 0 |
| 1005 | PluginFormcreatorForm_Answer | 60 | 8 | 0 |
+------+------------------------------+-----+------+----------+
7 rows in set (0,00 sec)

mysql> select * from glpi_displaypreferences where itemtype = "PluginFormcreator_Form_Answer";
Empty set (0,01 sec)

[btry]

Hi

If you drop the rows whenre itemtype = "PluginFormcreatorFormAnswer" then the upgrade process will no longer fail.

I recommend you check the related view in the plugin to ensure you have all expected columns, and edit your global preferences if needed.

I created a fix to drop the deleted FormAnswers when dropping the is_deleted column. Do you have an opportunity to validate it by running an upgrade from < 2.7 to 2.7 or later ?

[Armin83]

Yes i can test the fix next week on dev system and old backup.

[btry]

Hi

Did you had time to validate the upgrade fix ?

[btry]

Hi

Having no feedback, I assume I did he patch properly, and I close this ticket. Feel free to open a new one if needed