Endpoint Security Complete

 View Only

ValueError: Cannot find a UNIQUE constraint on table IosCommand 

Nov 14, 2016 10:37 AM

When does this happen?

This issue can occur while performing an upgrade of Mobility Suite and 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" entry.

It can affect App Center or Mobility Suite upgrade of any version.

This error occurs when using a Windows version of MySQL. The error displayed below confirms this:

"Cannot find a UNIQUE constraint on table IosCommand".

 

Why does this happen?

This error shows that the ALTER TABLE command was stopped 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 the "south" migration because the table names are mandated by the core schema to be named using "CamelNotation". Items work as expected during most operations, but when there is a "delete_unique()" function, 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" work with table names. By default everything is all lower case, so this issue does not occur on that database.

 

How is this verified to be the cause?

In order to verify that the affected environment is experiencing this same problem the output of the following mysql query can be collected:

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 below command, the case of the characters used in the database's table names will be displayed:

SHOW TABLES;

They will be displayed in the following format:

+---------------------------+
| Tables_in_mdmcore         |
+---------------------------+
| androidcommand            |
| appconfigurations         |
...

The output of the same "SHOW" command should be as follows on a supported Linux version of MySQL:

SHOW VARIABLES LIKE "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

 

Is there a path to recovery?

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.

 

What is the fix?

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 as the "root" user (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/Mobility Suite environments):

`0010_add_ioscommand_and_deviceios_index.py' -> `/usr/local/nukona/mdmcore_migration/apps/mdmcore/migrations/0010_add_ioscommand_and_deviceios_index.py'
`0009_modify_ioscommand_unique_constraint.py' -> `/usr/local/nukona/mdmcore_migration/apps/mdmcore/migrations/0009_modify_ioscommand_unique_constraint.py'
Patch successful

Once this patching has been completed the upgrade of Mobility Suite can be re-run. The "mdmcore" migration should be successful on the next attempt.

Statistics
0 Favorited
0 Views
1 Files
0 Shares
0 Downloads
Attachment(s)
tar file
migrationfix.tar.tar   3 KB   1 version
Uploaded - Mar 11, 2020

Tags and Keywords

Related Entries and Links

No Related Resource entered.