ValueError: Cannot find a UNIQUE constraint on table IosCommand
Article ID: 163913
Updated On:
Products
Issue/Introduction
While performing an upgrade of Mobility Suite the message that "Load settings failed to complete" in the "/var/log/nukona/appcenter-setup.log" due to a "ValueError: Cannot find a UNIQUE constraint on table IosCommand" occurs.
"ValueError: Cannot find a UNIQUE constraint on table IosCommand"
Environment
App Center or Mobility Suite upgrade of any version.
Cause
This error occurs when using a Windows version of MySQL. The error displayed below confirms this:
"Cannot find a UNIQUE constraint on table IosCommand".
This stopped the ALTER TABLE command from being able to run will using ADD CONSTRAINT for the UDID's listed in that table. Since this happened, the full "mdmcore" migration was unable to succeed.
There is a readonly MySQL variable set that forces all tables to be stored in lower case. This is set to 1 (ON) within case sensitive filesystems, like Windows. However, this difference in table case entirely trips up "south" because the table names are mandated by the core schema to be named using "CamelNotation". Items function as expected during most operations, but when there is a "delete_unique()", it searches for constraints attached to "IosCommand", finding nothing because it's all under "ioscommand".
This error does not exist in "appstore_cu" tables because "django" and "south" deal with table names. By default everything is all lower case, so there would be no problem on that database.
Resolution
Verify that the affected environment is experiencing this same problem by gathering the output of the following mysql query:
SHOW VARIABLES LIKE "%lower%";
If it matches the following result, then there would be case sensitivity issues:
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 1 |
+------------------------+-------+
By running the "SHOW TABLES;" command, the case of the characters used in the database's table names will be displayed:
+---------------------------+
| Tables_in_mdmcore |
+---------------------------+
| androidcommand |
| appconfigurations |
...
The output should be as follows on a supported Linux version of MySQL:
mysql> SHOW VARIABLES LIKE "%lower%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
The problem is that turning off "lower_case_table_names" in this state for a functional environment would be catastrophic. If there was ever a collision of
naming on any tables in any other databases, there would be data corruption. The affected server could be patched by changing the migrations and using all lower case table names. That would work to a point, but any subsequent upgrades would have the same issue again.
The server can be patched to become functional again. The "mdmcore" database can be changed to have all of its tables switched to lowercase which should permanently resolve the issue.
A Windows MySQL database is a configuration that was never tested for use with Mobility Suite and is not supported.
However, below is an attached file that should resolve this issue for the current version of Mobility Suite so long as no further upgrades are performed. Otherwise it will need to be run again each time an upgrade occurs.
The following syntax for extracting and running the attached patch is as follows (From the directory where the .tar file is located):
# tar xvfz migrationfix.tar.tar
# cd migrationfix
# bash runme.sh
The following output should be shown if it is successful (This has been tested on 4.4 and 5.4.1 App Center and Mobility Suite environments):
`0010_add_ioscommand_and_devic
`0009_modify_ioscommand_unique
Patch successful
Once this patch is successful the upgrade of Mobility Suite can be re-run. The "mdmcore" migration should be successful on the next attempt.
Attachments
Feedback
