RMNOCASE error with Mac OS Monterey

#1 · 2022-03-20, 1:39 pm

I'm running RM8 on an M1 iMac, OS Monterey. Without going into a lot of tedious detail, I'm in a situation where I have more than 4,000 sources imported from an Ancestry Gedcom download that need to be deleted. It will take forever to delete them individually in RM8. I tried using SQLPro for SQLite (v. 2022.12), but received the error message "no such collation sequence: RMNOCASE". Is there a way to do this in Mac OS? Alternatively, since I have Parallels and Win11, could I do it in Windows? I don't see why the database structure would be different, but maybe I'm wrong about that. Which SQL program for Windows would you recommend if I have to go that route? Thanks for any help you can provide.

#2 · 2022-03-20, 10:28 pm

RMNOCASE is a custom collation sequence extension to SQLite embedded in the RM program. Outside SQLite queries either have to use a fake RMNOCASE extension or, in some cases, add COLLATE NOCASE after each fieldname that is indexed on RMNOCASE. Fake RMNOCASE extensions can be found under the tag #rmnocase.

I've no Mac experience but it's my understanding that SQLite is included in MacOS. Other than that, I have used the Windows version of SQLite Studio from https://sqlitestudio.pl/ . Click on the menu item Download link to get to github for the MacOSX version. I don't know if it will run on Monterey.

#3 · 2022-03-21, 3:53 pm

Thank you, Tom! I downloaded the Mac version of SQLite Studio, and it works just fine under Monterey. I was able to delete the 4,000+ unwanted sources with no difficulty. The app seemed to recognize the RMNOCASE extension. At least, when I looked at the Constraints tab, it was named there and I didn't get any error messages.

#4 · 2022-03-29, 9:35 am

Good for you!

I suspect Studio auto-substitutes the default NOCASE collation when it encounters an unrecognised one. Not sure that is right and proper nor safe in all use cases but is what we did explicitly until we came up with the fake RMNOCASE extensions. Remember that after working the database with Studio that, on return to RM, you should use its Rebuild Indexes utility to clear Integrity Check errors caused by using a mismatched collation.

Kevin McLarnon has reacted to this post.

#5 · 2023-11-22, 11:56 am

Quote from Tom Holden on 2023-11-22, 11:56 am
The following was posted as a comment in another discussion but seems to be more appropriately added under this topic. @rvermar My 1st suggestion is to try SQLite Studio, if it can be successfully installed on your config. Please review the discussion above. Another suggestion is below...
Quote from Marilyn Clark on 2023-11-20, 4:26 pm
Quote from Tom Holden on 2023-04-23, 1:26 pm
Are there any of you exchanging databases between MacOS and Windows versions of RM9 noticing indexing errors in Database Tools => Test Integrity?
A RM9 user suffering from the rogue spouse name with a RIN of 0 (a problem that cropped up with much higher frequency in RM8 and continues with less frequency in RM9) sent me a backup to fix. What startled me was that it failed the RM9 Test Integrity tool with a list of indexing errors somewhat similar to what one sees after REINDEXing with SQLite using a fake RMNOCASE collation in SQLiteSpy. I got her to send me another backup of the database that I had fixed for the original problem and had sent to her with Test Integrity passed. She had done some work on it and ran the RM9 database tools before doing the backup. What I restored failed Test Integrity the same as the first!
she uses an iMac running Ventura
she regularly runs the database tools
I'm using Windows 10
we are both on RM 9.0.2
The only explanation that I can think of that would cause this discrepancy in the Test Integrity results is that the Mac version of RM9 does not use the same RMNOCASE collation sequence as the Windows version. Maybe the original RMNOCASE is built on Windows functions that cannot be matched in MacOS and necessitated a MacOS variant, just as we who use SQLite on our RM databases have to substitute a fake RMNOCASE.

UPDATE from the user in question (me): Same problem occurred again: the zero OwnerID. I know diddly about SQL but have been researching enough to:
Find that my Mac (now running Sonoma 14.1.1, Apple M1; RootsMagic 9.1.0) has SQlite 3.39.5 already built-in. So I downloaded/installed SQLite Flow in order to run the recommended query: DELETE FROM NameTable WHERE OwnerID = 0;
I tried to run this on a renamed copy of my current problematic RM db, on which I'd already run the 4 RM tools. The RM db was not open when I ran the query. You can see the results in one of my attached screenshots.
So I have this RMNOCASE problem and IDK how to proceed. I saw somewhere about maybe running REINDEX (in SQL?) before running the DELETE query, then using the RM tool to reindex within RM before using the cleaned file, but IDK the correct SQL syntax for the first Reindex. And the comments about Fuzzy things is way over my head. I don't want to be a chore, but this simple fix is becoming waaaaay too complicated. Thanks for listening.
Marilyn

My 2nd suggestion may not work. Using any SQLite manager with which you can open the database, inspect the NameTable for records where the OwnerID=0. Edit only the OwnerID to the RIN of the person you previously added (using RM) to your database with the name "_ME, _DELETE". Return to RM and open Edit Person on that RIN - you should see the phantom name(s). Then delete the person in RM. The reason it may not work is that even though the OwnerID field is not subject to RMNOCASE, the edit may still invoke an update of the Index and the dreaded error will appear.
My3rd suggestion is - send me your file. We've both used up way more time on this than it takes to fix with the right tool!

The following was posted as a comment in another discussion but seems to be more appropriately added under this topic. @rvermar My 1st suggestion is to try SQLite Studio, if it can be successfully installed on your config. Please review the discussion above. Another suggestion is below...

Quote from Marilyn Clark on 2023-11-20, 4:26 pm
Quote from Tom Holden on 2023-04-23, 1:26 pm
Are there any of you exchanging databases between MacOS and Windows versions of RM9 noticing indexing errors in Database Tools => Test Integrity?
A RM9 user suffering from the rogue spouse name with a RIN of 0 (a problem that cropped up with much higher frequency in RM8 and continues with less frequency in RM9) sent me a backup to fix. What startled me was that it failed the RM9 Test Integrity tool with a list of indexing errors somewhat similar to what one sees after REINDEXing with SQLite using a fake RMNOCASE collation in SQLiteSpy. I got her to send me another backup of the database that I had fixed for the original problem and had sent to her with Test Integrity passed. She had done some work on it and ran the RM9 database tools before doing the backup. What I restored failed Test Integrity the same as the first!
she uses an iMac running Ventura
she regularly runs the database tools
I'm using Windows 10
we are both on RM 9.0.2
The only explanation that I can think of that would cause this discrepancy in the Test Integrity results is that the Mac version of RM9 does not use the same RMNOCASE collation sequence as the Windows version. Maybe the original RMNOCASE is built on Windows functions that cannot be matched in MacOS and necessitated a MacOS variant, just as we who use SQLite on our RM databases have to substitute a fake RMNOCASE.

UPDATE from the user in question (me): Same problem occurred again: the zero OwnerID. I know diddly about SQL but have been researching enough to:
Find that my Mac (now running Sonoma 14.1.1, Apple M1; RootsMagic 9.1.0) has SQlite 3.39.5 already built-in. So I downloaded/installed SQLite Flow in order to run the recommended query: DELETE FROM NameTable WHERE OwnerID = 0;
I tried to run this on a renamed copy of my current problematic RM db, on which I'd already run the 4 RM tools. The RM db was not open when I ran the query. You can see the results in one of my attached screenshots.
So I have this RMNOCASE problem and IDK how to proceed. I saw somewhere about maybe running REINDEX (in SQL?) before running the DELETE query, then using the RM tool to reindex within RM before using the cleaned file, but IDK the correct SQL syntax for the first Reindex. And the comments about Fuzzy things is way over my head. I don't want to be a chore, but this simple fix is becoming waaaaay too complicated. Thanks for listening.
Marilyn

My 2nd suggestion may not work. Using any SQLite manager with which you can open the database, inspect the NameTable for records where the OwnerID=0. Edit only the OwnerID to the RIN of the person you previously added (using RM) to your database with the name "_ME, _DELETE". Return to RM and open Edit Person on that RIN - you should see the phantom name(s). Then delete the person in RM. The reason it may not work is that even though the OwnerID field is not subject to RMNOCASE, the edit may still invoke an update of the Index and the dreaded error will appear.

My3rd suggestion is - send me your file. We've both used up way more time on this than it takes to fix with the right tool!

#6 · 2023-11-22, 1:14 pm

@ve3meo Tom, thanks for answering this. I've spent days researching this...I got SQLiteStudio 3.4.4 installed on my iMac Sonoma. I loaded in my RM db and it sees the structure, but not the data. I got that RMNOCASE error, even tho I didn't even attempt to run a query.

@CherylC above mentioned "constraints" so attached is a screenshot of what I see for NameTable. I can't edit anything here. Next screenshot is of Collations editor. Is there some code I can put here to change RMNOCASE to NOCASE ?

If this is getting ridiculously complicated, I'll do the drag 'n drop and live with the data losses. Actually I already did a DnD to see what would happen. I lost all Groups, individual 'not a problem' overrides, and all media links. On the plus side, my phantom '0' RIN is gone (for now) and my color coding remained.

I am really irritated with RootsMagic at this point. How am I the only user getting these phantom records?

Uploaded files:

#7 · 2023-11-22, 11:09 pm

Quote from Tom Holden on 2023-11-22, 11:09 pm
You haven't been the only one to have this particular phantom name (maybe "rogue name" is more accurate). It's not as frequent in later versions of RM9 as it was earlier and was especially bad in early RM8 which would crash frequently while adding people. It may be that you are more vigilant than others or that you are faster with data entry than most and get too far ahead of the computer. Anyway, I've got some potentially good news. I came up with a hybrid procedure that collects all such rogue names using SQLite without the RMNOCASE collation under a temporary new person and then you find, inspect and delete that person using RM. That gets rid of the rogue names.
I did it using the sqlite3.exe command line version for Windows but it should work for you in Terminal using the native sqlite3. It should also work with one of your GUI SQLite managers. What follows is the dialog in the command line version:
C:\Users\Tom>cd C:\Users\Tom\Documents\FamilyTree\RM9
-- changing directory to where my database file and sqlite3.exe are
C:\Users\Tom\Documents\FamilyTree\RM9>sqlite3 -- start sqlite
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open "PLAY (2).rmtree" -- open my database file
sqlite> DELETE FROM NameTable WHERE OwnerID=0; --test if possible
Error: no such collation sequence: RMNOCASE
-- confirmed it's not possible without the required collation
sqlite> INSERT INTO PersonTable DEFAULT VALUES;
-- add a new, temporary, unnamed person to the database
sqlite> SELECT MAX(PersonID) FROM PersonTable;
1822
-- that's the RIN of the new person
sqlite> UPDATE NameTable SET OwnerID=1822 WHERE OwnerID=0;
sqlite>
-- successfully assigned all the rogue names to the temp person
sqlite> .quit
C:\Users\Tom\Documents\FamilyTree\RM9>
-- can close Terminal
Now open RM on the database, find the temp person (RIN=1822 in my case) and delete the person.
Your filename and path will be different. Perhaps you do not need to have the sqlite3 executable in the same folder as the database because it is already incorporated as a tool in your system (it's not, in Windows) and can be called from anywhere. If you use your GUI tool, you would skip the dot commands.

You haven't been the only one to have this particular phantom name (maybe "rogue name" is more accurate). It's not as frequent in later versions of RM9 as it was earlier and was especially bad in early RM8 which would crash frequently while adding people. It may be that you are more vigilant than others or that you are faster with data entry than most and get too far ahead of the computer. Anyway, I've got some potentially good news. I came up with a hybrid procedure that collects all such rogue names using SQLite without the RMNOCASE collation under a temporary new person and then you find, inspect and delete that person using RM. That gets rid of the rogue names.

I did it using the sqlite3.exe command line version for Windows but it should work for you in Terminal using the native sqlite3. It should also work with one of your GUI SQLite managers. What follows is the dialog in the command line version:

C:\Users\Tom>cd C:\Users\Tom\Documents\FamilyTree\RM9
-- changing directory to where my database file and sqlite3.exe are
C:\Users\Tom\Documents\FamilyTree\RM9>sqlite3 -- start sqlite
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open "PLAY (2).rmtree" -- open my database file
sqlite> DELETE FROM NameTable WHERE OwnerID=0; --test if possible
Error: no such collation sequence: RMNOCASE
-- confirmed it's not possible without the required collation
sqlite> INSERT INTO PersonTable DEFAULT VALUES;
-- add a new, temporary, unnamed person to the database
sqlite> SELECT MAX(PersonID) FROM PersonTable;
1822
-- that's the RIN of the new person
sqlite> UPDATE NameTable SET OwnerID=1822 WHERE OwnerID=0;
sqlite>
-- successfully assigned all the rogue names to the temp person
sqlite> .quit
C:\Users\Tom\Documents\FamilyTree\RM9>
-- can close Terminal
Now open RM on the database, find the temp person (RIN=1822 in my case) and delete the person.

Your filename and path will be different. Perhaps you do not need to have the sqlite3 executable in the same folder as the database because it is already incorporated as a tool in your system (it's not, in Windows) and can be called from anywhere. If you use your GUI tool, you would skip the dot commands.

#8 · 2023-11-23, 4:55 pm

I tried this out today and it WORKED! I used my free 'trial' version of SQLiteStudio and everything went smoothly except that no RIN was returned for MAX(PersonID). So I went back into my original RM db and added an unlinked individual to get the next RIN. Since I'm experimenting on a differently-named but identical copy of my original RM db, I figured I'd just use that next RIN in the rest of the query. Bingo!

Then I closed the SQL editor; deleted the new RIN person, ran tools on my fixed copy, and tested by adding two new spouses in 2 different ways. Still all good. Then I checked that Ancestry TreeShare still worked on my renamed and fixed copy, and it does. So that will be my new database.

Thanks again for solving a really irritating problem. No doubt it will occur again, but at least I have the tools to fix it, unless RM decides to change something...

Uploaded files: