Querying spatial data using spatialite with indexes

Spatialite is an amazing SQLite extension. Using this extension you can query your database using complex spatial functions like ST_Intersection, ST_Difference, ST_Buffer, etc.
Check more at SpatiaLite SQL functions reference list

For those like me who have a MS-SQL background, you should be aware that performing spatial queries
using spatial indexes is a bit different.

After creating your spatial index, you have to change your query to take advantage of it.

Instructions

Adding a spatial column

1
SELECT AddGeometryColumn('MYTable', 'Geometry',4326, 'POLYGON', 'XY');

Creating a spatial index

1
SELECT CreateSpatialIndex('MYTable', 'Geometry');

Querying

Suppose that you want to check if a point is inside a POLYGON among many stored into your database.
If you write a query like the one below, you are not taking advantage of R*Tree spatial indexes.

1
2
3
SELECT *
FROM MYTable
WHERE ST_Contains(Geometry,MakePoint(-46.521291, -23.519995)) = 1

To do so, the first step is to query to match your input data against the R*Tree index of stored data.
This can be done via virtual table SpatialIndex.

1
2
3
4
5
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'MYTable'
AND f_geometry_column = 'Geometry'
AND search_frame = MakePoint(-46.521291, -23.519995, 4326)

So, the final query will look like this:

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM MYTable
WHERE ST_Contains(Geometry,MakePoint(-46.521291, -23.519995)) = 1
AND ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'MYTable'
and f_geometry_column = 'Geometry'
AND search_frame = MakePoint(-46.521291, -23.519995, 4326)
)

This query will fetch a few candidates from the SpatialIndex and then those candidates will be checked by a more complex function like ST_Contains.

Managing IMs over Web

Nowadays, almost all my team communication goes over IM’s. When I’m working at my computer I use the desktop versions
of Whatsapp or Telegram. But, from time to time I have to work at PC where I can’t install any software.

Then I have to use theirs web clients, and they are awesome, but I’m the kind of person who open SEVERAL tabs at once.
I easily forget where I left my IM tab on the way, therefore opening another tab to get access to it.

my tabs

I was looking for better way to deal with this. Thanks to my friend Rafael Leite Antonio I got one!
Using Google Chrome you can open a webpage at new window and place shortcut in your taskbar.
It’s quite easy to do that. You just have to open your IM web client, and to the following steps at Chrome:

  • Add to desktop
    add to desktop

  • Open as new window
    add to desktop

After you get the shortcut in yout desktop, you just have to drag it to your taskbar.

taskbar

Now I can easily use Alt+Tab to get to my IM in it’s own window! Just like in the desktop version.

Fixing spatialite loading problem

A few days ago, I’ve got an issue with spatialite - mod_spatialite-4.3.0a-win-amd64. After googling for a solution, I found some people with the very same issue and no solution.
Then, I decided to try to figure it out.

Symptoms

I was developing a tool to work with spatial data and when I tried to use spatialite from a C# application I got the following error:

1
Unhandled Exception: SQLite.SQLiteException: A dynamic link library (DLL) initialization routine failed.

Debugging

Testing SQLite wrapper

My first approach was to replace System.Data.SQLite Version 1.0.99 SQLite 3.9.2. Since it has a custom interop assembly (SQLite.Interop.dll), I thought it could be something regarding the way it was compiled.

I’ve then tried to load mod_spatialite using sqlite-net. No lucky.
I’ve even used libsqlite3-0 shipped with mod_spatialite-4.3.0a-win-amd64 to be sure to be running correct versions. I’ve got the same error.

Testing mod_spatialite

Since I got two different paths leading to the very same problem, I guessed it was something to do with mod_spatialite it self.
I’ve download mod_spatialite-4.3.0a-win-amd64 and tested it using the bundled sqlite3.exe.

It was able to load mod_spatialite without any problems.

Digging depper with WinDBG

Since everything was working perfecly when separated, I’ve decided to dig deeper with WinDBG.
Then, I got an Access violation - code c0000005 after issuing trying to load mod_spatialite with the command below.

1
SELECT load_extension('mod_spatialite')

Below is call stack when the error occurred. Looks like something went wrong during libstdc++_64-6.dll.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# RetAddr : Args to Child : Call Site
00 00000000`6a5d9c29 : 00000203`b5b80000 0000007d`50000061 0000007d`069fd718 00000000`6a647a00 : libstdc___64_6!_cxa_demangle+0x12610
01 00000000`6a5da052 : 00000000`6a648014 00000000`6a647a00 00000000`6a648014 00000000`00000000 : libstdc___64_6!_cxa_demangle+0xc3a9
02 00000000`6a6475ce : 00000000`00000000 00000000`6a5c117a 00000000`6a6894e0 00007ffd`dd0bb7b0 : libstdc___64_6!_cxa_demangle+0xc7d2
03 00000000`6a5cdf75 : 00000000`00000001 00000000`00000001 00000000`6a5c13e0 00000000`6a648014 : libstdc___64_6!atomic_flag_test_and_set_explicit+0x26e
04 00000000`6a5c137a : 00000000`00000001 00007ffd`dd9a52c8 00000000`00000001 00000000`6a5dc090 : libstdc___64_6!_cxa_demangle+0x6f5
05 00007ffd`dd9a52c8 : 00000000`00000000 00000000`00000001 0000007d`069fdb74 00007ffd`dda021a6 : libstdc___64_6+0x137a
06 00007ffd`dda020ca : 00000203`b5b948a0 00000000`6a5c0000 00000000`00000001 00000203`b5b94f30 : ntdll!LdrpCallInitRoutine+0x4c
07 00007ffd`dda01f0f : 00000203`b5b949f0 00000203`b5b94900 0000007d`069fd9b0 00000000`6a5c13e0 : ntdll!LdrpInitializeNode+0x162
08 00007ffd`dda01f2d : 00000000`00000000 00000203`b5b942d0 0000007d`069fd9f0 00000000`00000020 : ntdll!LdrpInitializeGraphRecurse+0x73
09 00007ffd`dda01f2d : 00000203`b5b91400 00000203`b5b94150 0000007d`069fda30 00000000`69a813e0 : ntdll!LdrpInitializeGraphRecurse+0x91
0a 00007ffd`dda01f2d : 0000007d`069fdc00 00000203`b59d4e70 0000007d`069fda70 00000203`00000000 : ntdll!LdrpInitializeGraphRecurse+0x91
0b 00007ffd`dd99bb85 : 00000000`00000000 00000000`00000000 0000007d`069fdac0 0000007d`069fdb74 : ntdll!LdrpInitializeGraphRecurse+0x91
0c 00007ffd`dd9a1ed4 : 0000007d`069fdb74 00000000`00000000 00000000`00000000 0000007d`069fdb80 : ntdll!LdrpPrepareModuleForExecution+0xc5
0d 00007ffd`dd9a19fe : 0000007d`069fdb80 0000007d`069fdd10 00000000`00000000 00000000`00000001 : ntdll!LdrpLoadDllInternal+0x1a8
0e 00007ffd`dd9a0c44 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`00000001 : ntdll!LdrpLoadDll+0xf2
0f 00007ffd`da82c77f : 00000203`b5b84e18 00007ffd`0000000f 0000000f`00000001 00000203`b5b84e18 : ntdll!LdrLoadDll+0x114
10 00000000`663908e9 : 00000203`00000000 00000203`b75258e8 00000203`b57efb25 00000000`00000000 : KERNELBASE!LoadLibraryExW+0x16f
11 00000000`66384952 : 00000000`66448f40 00000203`cfbdf588 00000203`b59d4e70 00000000`00000001 : sqlite3!sqlite3_win32_set_directory+0x3cba
12 00000000`663eee51 : 00000000`66448f40 00000203`cfbdf588 00000203`b57efb25 0000007d`069fe0c8 : sqlite3!sqlite3_db_status+0x312f
13 00000000`663ef331 : 00000203`b5b847a8 00000203`cfbdf588 00000000`00000000 0000007d`069fdfb0 : sqlite3!sqlite3_exec+0x4b9
14 00000000`663e6fa0 : 00000203`b5b847a8 00000203`cfbdf588 00000000`00000000 0000007d`069fdfb0 : sqlite3!sqlite3_load_extension+0x46
15 00000000`663bc0fa : 00000203`cfbdf408 00000000`00000001 00000203`cfbdf438 00000203`b5b94140 : sqlite3!sqlite3_strglob+0x11d0
16 00000000`663b7f69 : 00000203`cfbe0798 00000000`663851b6 00000203`b5b81db8 0000007d`069fea30 : sqlite3!sqlite3_value_numeric_type+0x1eb8
17 00000000`663b811e : 00000203`cfbe0798 00000203`b75258e8 00140203`b5913950 00000000`00000000 : sqlite3!sqlite3_result_error_nomem+0x2e9
18 00007ffd`5c184244 : 00000203`cfbe0798 00000203`b75258e8 0000007d`069feba0 00000203`b75258e8 : sqlite3!sqlite3_step+0xd2
19 00007ffd`5c1837a8 : 00000203`cfbe0798 00007ffd`5c1e1420 00000203`cfbdfab0 00000203`b5b87101 : 0x00007ffd`5c184244
1a 00007ffd`5c182de6 : 00000203`b75258e8 00000203`b7525360 00000203`b59269b8 00000000`00000000 : 0x00007ffd`5c1837a8
1b 00007ffd`5c181657 : 00000203`b7525640 00000203`b7525360 00000203`b7525868 00007ffd`ba8a8bc0 : 0x00007ffd`5c182de6
1c 00007ffd`bb7c3bd3 : 00000203`b7524508 00007ffd`5c0640c0 00000000`00000000 00000000`00000000 : 0x00007ffd`5c181657
1d 00007ffd`bb7c3a95 : 0000007d`069ff038 00007ffd`bb8fe619 00000000`00000000 00000000`00000000 : clr!CallDescrWorkerInternal+0x83
1e 00007ffd`bb7c44c6 : 00000000`00000001 0000007d`069fefb0 0000007d`069fef80 0000007d`069feef8 : clr!CallDescrWorkerWithHandler+0x4e
1f 00007ffd`bb8bb710 : 0000007d`069fee80 00000000`00000000 00000000`00000000 00000000`00000001 : clr!MethodDescCallSite::CallTargetWorker+0xf8
20 00007ffd`bb8bb8aa : 00000000`00000001 00000000`00000000 00000203`00000000 00000203`b7524508 : clr!RunMain+0x1e7
21 00007ffd`bb8bb7a0 : 00000000`00000000 00000203`b7521628 00000000`00000000 00000203`b59cda80 : clr!Assembly::ExecuteMainMethod+0xb6
22 00007ffd`bb8bba13 : 0000007d`069ff9f8 00000203`b57e0000 00000000`00000000 00000000`00000000 : clr!SystemDomain::ExecuteMainMethod+0x5ea
23 00007ffd`bb8bb9ea : 00000203`b57e0000 00000000`00000000 00000000`00000000 00007ffd`bd254a38 : clr!ExecuteEXE+0x3f
24 00007ffd`bb8cdea0 : ffffffff`ffffffff 00000000`00000000 0000007d`06464000 00000000`00000000 : clr!_CorExeMainInternal+0xb2
25 00007ffd`bd1f802d : 00000000`00000000 00000000`00000091 00000000`00000000 0000007d`069ffab8 : clr!CorExeMain+0x14
26 00007ffd`bd291184 : 00007ffd`bd2910a0 00007ffd`bb8cde8c 00000000`00000000 00007ffd`bd1f7fd0 : mscoreei!CorExeMain+0xe0
27 00007ffd`bd2910ab : 00007ffd`bd1f0000 00007ffd`bd2910a0 00000000`00000000 00000000`00000000 : MSCOREE!ShellShim__CorExeMain+0xb8
28 00007ffd`dd0b8102 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`00000000 : MSCOREE!CorExeMain_Exported+0xb
29 00007ffd`dd9ec5b4 : 00007ffd`dd0b80e0 00000000`00000000 00000000`00000000 00000000`00000000 : KERNEL32!BaseThreadInitThunk+0x22
2a 00000000`00000000 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`00000000 : ntdll!RtlUserThreadStart+0x34

Isolationg the problem

Well, libstdc++_64-6.dll has nothing to do with spatialite it self, it’s just a dependency.
To be sure it was something with lib initialization I’ve tried to load it manually with the code below. As expected it failed.

1
2
3
4
5
6
7
8
9
public class Program {
[DllImport("kernel32", SetLastError = true, CharSet = CharSet.Ansi)]
static extern IntPtr LoadLibrary([MarshalAs(UnmanagedType.LPStr)]string lpFileName);
public static void Main(string[] args) {
var intPtr = LoadLibrary("libstdc++_64-6.dll");
}
}

Digging into libstdc++-6.dll

libstdc++-6.dll is part of MinGW-W64. Since the DLL hasn’t any information regarding it’s version I’ve then download the latest MinGW-W64 x86_64-5.3.0-win32-seh-rt_v4-rev0.
The downloaded version of libstdc++-6.dll loaded perfectly fine using LoadLibrary("libstdc++-6.dll");

Updating libstdc++-6.dll

libstdc++-6.dll has a dependency to libgcc_s_seh-1.dll. So, both DLLs should be updated.
Since mod_spatialite.dll is looking from libstdc++_64-6.dll instead of libstdc++-6.dll you have to rename it to the correct name.
As the new libstdc++-6.dll is looking for libgcc_s_seh-1.dll instead of libgcc_s_seh_64-1.dll, you can delete libgcc_s_seh_64-1.dll shipped mod_spatialite-4.3.0a-win-amd64 and just copy libgcc_s_seh-1.dll.

Conclusion

After upgrading libstdc++-6.dll everything worked as expected. I’ve tested it against the following scenario:

  • Windows 10 x64 - [Version 10.0.10586]
  • .NET Framework v 4.6.1
  • System.Data.SQLite Version 1.0.99 [SQLite 3.9.2]
  • mod_spatialite-4.3.0a-win-amd64.

I’ve also posted to spatialite-users mailist this workaround.
As Sandro Furieri pointed out, it may be not safe to just replace core DLLs in this way because the ABI might have changed. I’ve been using this workaround without any issues so far.

I hope this module gets compiled with a newer version of MinGW-W64 in the next release.

Asymmetric routing

I’ve been working on a OpenVPN deployment that is working with asymmetric routing. I think it is working with a sub-optimal solution. First, let me show how it is deployed and what is bothering me.

Big picture

Big picture
So, lets check the path of a packet going from client A to client B.

Order IP Description
1 10.1.0.92 client site A
2 10.1.0.254 default gw site A
3 10.1.0.45 openvpn gw site A
4 172.16.1.1 wan openvpn site A
5 172.16.1.58 wan openvpn site B
6 10.1.8.254 default gateway site B
7 10.1.8.2 client site B

But when the packet comes from client B to client A the path is a bit different.

Order IP Description
1 10.1.8.2 client site B
2 10.1.8.254 default gateway site B
3 172.16.1.2 wan openvpn site B
4 172.16.1.58 wan openvpn site A
5 10.1.0.45 openvpn gw site A
6 10.1.0.92 client site A

Pay attention that the packet is not going through 10.1.0.254 (default gw site A). So the path is asymmetric.

The problem

As the packet does not go through 10.1.0.254 (default gw site A) in both ways, the firewall is unable to track the connection state and it starts to reject packets from that connection. Let’s take one example of client A trying to connect to client B.

client A -> client B – SYN packet
(this packet is forwarded by firewall in default gw site A)

client B -> client A – SYN/ACK packet
(this packet is delivered to client A without going through default gw site A)

client A -> client B – ACK packet
(this packet is reject by default gw site A)

Alternative #1 – ICMP redirect

As soon as the 10.1.0.254 (default gw site A) detects the next hop is in the same network as the sender, 10.1.0.92 (client site A), it should send an ICMP redirect instructing the client to send packets directly to 10.1.0.45 (openvpn gw site A). In this way you get the optimal path in both ways.

If your router is a linux box remember to enable it to send ICMP redirects by changing yours sysctl.conf

1
net.ipv4.conf.all.send_redirects = 1

Unfortunately, routes created by ICMP redirects are short lived in Windows clients, and when this route gets expired a packet is sent to 10.1.0.254 in the middle of a TCP connection, witch wasn’t tracked, so it rejects the packet and the connection is dropped.

Alternative #2 – ICMP redirect + Stateless firewall

To get alternative #1 fixed you just need to make rules stateless in the firewall (aka don’t use switches -m state –state NEW in iptables). So when the ICMP redirect route gets expired the host will send the packet to it’s default gateway and will receive another ICMP redirect instead of an ICMP port unreachable.

Alternative #3 – Use DHCP to push routes

If all your clients are working with DHCP and both client and servers supports RFC3442 it is a very good alternative. It will make clients always use the best route using the right gateway.

You can check how to configure it ISC-DHCP server here.
You can check how to configure it Microsoft DHCP server here.

If you have a mixed network with DHCP clients and static clients this alternative can be a problem since you won’t get the all routes delivered to all hosts, also I had problems with some SIP phones that doesn’t support RFC3442.

Remember that you can still use both this alternative with alternatives #1 and #2 at same time.

Alternative #4 – Source routing

If you decide that you would like to get the packets going through both gateways to get a symmetric routing you can use this alternative.
Check out LARTC how-to to get more info.

Alternative #5 – Transfer network

This last altervative involves using new network to get packets transferred between networks.
To achieve this you should create a new VLAN to connect the hosts:

  • 10.1.0.254 (default gw site A)
  • 10.1.0.45 (openvpn gw site A)

If you like to use just one NIC in 10.1.0.254 (default gw site A) you can make use of IEEE 802.1q, but your switch must support it.
If you have a spare NIC in 10.1.0.254 (default gw site A) you can just configure the new network using this NIC.

After you get the transfer network configured on 10.1.0.254 (default gw site A), you just need to route the packets addressed to VPN to go through this transfer network. Do the same on your VPN box to the packets addressed to your LAN.
Since they are in separated networks no ICMP redirect will occur.

References

#1 icmp-redirects-are-bad
#2 rfc3442

Exchange 2013 & iPhone Sync

Today I’ve got an iPhone unable to sync with Exchange 2013. Curiously, only the inbox was not syncing. All other folders were working fine.

Checking into event logs I’ve found one error related to the sync problem.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
An exception occurred and was handled by Exchange ActiveSync. This may have been caused by an outdated or corrupted Exchange ActiveSync device partnership. This can occur if a user tries to modify the same item from multiple computers. If this is the case, Exchange ActiveSync will re-create the partnership with the device. Items will be updated at the next synchronization.
URL=
--- Exception start ---
Exception type: Microsoft.Exchange.Data.Storage.SyncStateExistedException
Exception message: The sync state named 2 already exists.
Exception level: 0
Exception stack trace: at Microsoft.Exchange.Data.Storage.SyncState.CreateSyncStateFolderInFolder(SyncStateInfo syncStateInfo, Folder syncStateParentFolder, PropertyDefinition[] properties, Object[] values, ISyncLogger syncLogger)
at Microsoft.Exchange.Data.Storage.SyncState.CreateSyncStateStoreObject(SyncStateStorage syncStateStorage, SyncStateInfo syncStateInfo, Folder syncStateParentFolder, PropertyDefinition[] properties, Object[] values, ISyncLogger syncLogger)
at Microsoft.Exchange.Data.Storage.FolderSyncState.CreateSyncState(SyncStateStorage syncStateStorage, Folder syncStateParentFolder, ISyncProviderFactory syncProviderFactory, String syncFolderId, ISyncLogger syncLogger)
at Microsoft.Exchange.Data.Storage.SyncStateStorage.CreateFolderSyncState(ISyncProviderFactory syncProviderFactory, String syncFolderId)
at Microsoft.Exchange.AirSync.SyncCollection.OpenSyncState(Boolean autoLoadFilterAndSyncKey, SyncStateStorage syncStateStorage)
at Microsoft.Exchange.AirSync.SyncCommand.SyncTheCollection(SyncCollection collection, Boolean createSubscription, Boolean tryNullSync)
at Microsoft.Exchange.AirSync.SyncCommand.OnExecute()
at Microsoft.Exchange.AirSync.SyncCommand.ExecuteCommand()
at Microsoft.Exchange.AirSync.Command.WorkerThread()
Inner exception follows below:
Exception type: Microsoft.Exchange.Data.Storage.ObjectExistedException
Exception message: Could not create folder 2.
Exception level: 1
Exception stack trace: at Microsoft.Exchange.Data.Storage.FolderCreatePropertyBag.CreateMapiFolder()
at Microsoft.Exchange.Data.Storage.FolderCreatePropertyBag.SaveFolderPropertyBag(Boolean needVersionCheck)
at Microsoft.Exchange.Data.Storage.CoreFolder.Save(SaveMode saveMode)
at Microsoft.Exchange.Data.Storage.Folder.Save(SaveMode saveMode)
at Microsoft.Exchange.Data.Storage.SyncState.CreateSyncStateFolderInFolder(SyncStateInfo syncStateInfo, Folder syncStateParentFolder, PropertyDefinition[] properties, Object[] values, ISyncLogger syncLogger)
Inner exception follows below:
Exception type: Microsoft.Mapi.MapiExceptionCollision
Exception message: MapiExceptionCollision: Unable to create folder. (hr=0x80040604, ec=-2147219964)
Diagnostic context:
Lid: 55847 EMSMDBPOOL.EcPoolSessionDoRpc called [length=116]
Lid: 43559 EMSMDBPOOL.EcPoolSessionDoRpc returned [ec=0x0][length=220][latency=0]
Lid: 52176 ClientVersion: 15.0.1076.9
Lid: 50032 ServerVersion: 15.0.1076.6009
Lid: 23226 --- ROP Parse Start ---
Lid: 27962 ROP: ropCreateFolder [28]
Lid: 17082 ROP Error: 0x80040604
Lid: 25953
Lid: 21921 StoreEc: 0x80040604
Lid: 27962 ROP: ropExtendedError [250]
Lid: 1494 ---- Remote Context Beg ----
Lid: 41352 qdwParam: 0x8D29C49F7F66A52
Lid: 55033 dwParam: 0x0
Lid: 10786 dwParam: 0x0 Msg: 15.00.1076.000:LITIO
Lid: 1750 ---- Remote Context End ----
Lid: 31418 --- ROP Parse Done ---
Lid: 22417
Lid: 30609 StoreEc: 0x80040604
Lid: 29073
Lid: 20369 StoreEc: 0x80040604
Lid: 63664
Lid: 40448 StoreEc: 0x80040604
Exception level: 2
Exception stack trace: at Microsoft.Mapi.MapiExceptionHelper.InternalThrowIfErrorOrWarning(String message, Int32 hresult, Boolean allowWarnings, Int32 ec, DiagnosticContext diagCtx, Exception innerException)
at Microsoft.Mapi.MapiExceptionHelper.ThrowIfError(String message, Int32 hresult, IExInterface iUnknown, Exception innerException)
at Microsoft.Mapi.MapiFolder.InternalCreateFolder(String folderName, String folderComment, Boolean openIfExists, Boolean createSearchFolder, Boolean instantSearch, Boolean optimizedConversationSearch, Boolean createPublicFolderDumpster, Byte[] folderId, Boolean createInternalAccess)
at Microsoft.Exchange.Data.Storage.FolderCreatePropertyBag.CreateMapiFolder()
--- Exception end ---.

The basics were already coverd by the help desk team.

First, I’ve tried to check and remove the mobile partnership linked to the Mailbox.

1
Get-MobileDevice -Mailbox johndoe
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
RunspaceId : c42a3abe-e15a-4999-b0d0-8a8aa90a4299
FriendlyName : iPhone 5s
DeviceId : ApplDNPM4HW2FFGD
DeviceImei :
DeviceMobileOperator :
DeviceOS : iOS 8.3 12F70
DeviceOSLanguage : pt
DeviceTelephoneNumber :
DeviceType : iPhone
DeviceUserAgent : Apple-iPhone6C2/1206.70
DeviceModel : iPhone6C2
FirstSyncTime : 26/05/2014 20:13:19
UserDisplayName : mydomain.local/Usuários/John Doe
DeviceAccessState : Allowed
DeviceAccessStateReason : Global
DeviceAccessControlRule :
ClientVersion : 14.1
ClientType : EAS
IsManaged : False
IsCompliant : False
IsDisabled : False
AdminDisplayName :
ExchangeVersion : 0.10 (14.0.100.0)
Name : iPhone§ApplDNPM4HW2FFGD
DistinguishedName : CN=iPhone§ApplDNPM4HW2FFGD,CN=ExchangeActiveSyncDevices,CN=John Doe,OU=Usuários,DC=mydomain,DC=local
Identity : mydomain.local/Usuários/John Doe/ExchangeActiveSyncDevices/iPhone§ApplDNPM4HW2FFGD
Guid : daec6592-9203-4031-b825-628466e311e1
ObjectCategory : myforest.local/Configuration/Schema/ms-Exch-Active-Sync-Device
ObjectClass : {top, msExchActiveSyncDevice}
WhenChanged : 04/06/2015 11:41:00
WhenCreated : 26/05/2014 17:13:20
WhenChangedUTC : 04/06/2015 14:41:00
WhenCreatedUTC : 26/05/2014 20:13:20
OrganizationId :
Id : mydomain.local/Usuários/John Doe /ExchangeActiveSyncDevices/iPhone§ApplDNPM4HW2FFGD
OriginatingServer : TITANIO
IsValid : True
ObjectState : Unchanged
1
Remove-MobileDevice "mydomain.local/Usuários/John Doe/ExchangeActiveSyncDevices/iPhone§ApplDNPM4HW2FFGD"

The command executed without errors. So far things behaved as expected, but when I get mobile partnerships again, I get the same partnership as if it had never been deleted.

After walking in circles I’ve tried a long shoot. Seems that something was corrupted. Let’s check the user mailbox.

1
New-MailboxRepairRequest -Mailbox johndoe -CorruptionType ProvisionedFolder,SearchFolder,AggregateCounts,Folderview
1
Get-MailboxRepairRequest -mailbox johndoe | fl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
RunspaceId : c42a3abe-e15a-4999-b0d0-8a8aa90a4299
Identity : a0598b5b-82db-45a7-8309-4ba865de95e2\6213348d-47c7-4ff5-8a44-9f7151039311\ef8f1654-bfeb-4786-a03f
-2b0b238fdf72
Mailbox : d59341d9-d810-4291-b70e-b6c2ede007c6
Source : OnDemand
Priority : Normal
DetectOnly : False
JobState : Succeeded
Progress : 100
Tasks : {AggregateCounts}
CreationTime : 03/08/2015 18:34:01
FinishTime : 03/08/2015 18:35:05
LastExecutionTime : 03/08/2015 18:35:05
CorruptionsDetected : 4
ErrorCode :
CorruptionsFixed : 4
TimeInServer : 00:00:43.6290000
Corruptions : {Type:AggregateCountMismatch (Fixed:True), Type:AggregateCountMismatch (Fixed:True),
Type:AggregateCountMismatch (Fixed:True), Type:AggregateCountMismatch (Fixed:True)}
IsValid : True
ObjectState : New

Bingo. After the mailbox repair the mobile partnership remove worked fine. Reconfigured the device and everything is working again.