Blacklist module not working (SOLVED)

[rkrenzis (Ryan Krenzischek)]

Same problem here: Blacklist module does not working. The post was older than 30 days so I couldn’t post my fix since the original author didn’t post a fix/resolution.

When opening the blacklist module, you’re greeted with
“SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation ’ IN ’
File:/var/www/html/admin/modules/blacklist/Blacklist.class.php:788”

I’m sharing resolution:

Make sure you have a good backup. We’re going to do a drop operation on the black list to fix it.

1. Goto Bulk Handler
2. Select Export in the Bulk Handler
3. Click Blacklist
4. Export
5. Open with Excel or your choice of CSV editor.
6. There were unreadable characters in the phone number column that were not 0 through 9. Use global find/replace to remove these characters. You may have variations of unreadable characters and may need to do mulitple iterations to clean the list up.
7. Save the CSV
8. Open command line to your Asterisk box.
9. Start the Asterisk CLI (asterisk -rv)
10. Enter the following command to drop the blacklist table:

database deltree blacklist

11. Your old blacklist will be dropped from the database table with the invalid characters. Type “quit” and press enter to leave the CLI. Exit out of the linux shell.
12. Go back into the Bulk Handler and click Import.
13. Select Blacklist (if it is not already selected)
14. Click the ‘Browse’ button and select the modified/updated CSV file
15. Click Submit
16. Click Import in the lower right hand corner (leave all defaults and the read-in blacklist as-is)
17. Data validation will take place. Go get a cup of coffee. This can take some time if you have a large blacklist.
18. Once validation has completed, click the ‘Finished’ button in the lower right hand corner.
19. Navigate to your black list and it will show again without the SQL error as mentioned in the previous post.

[lgaetz (Lorne Gaetz)]

Do you have the original export before removing the non-numeric characters? Do you have any idea how non-numeric characters got in AstDB to begin with? Blacklist entries can be made by user input in the Admin GUI or via dialing the feature code. Doesn’t look like the GUI restricts input when creating a new entry, it will accept alpha chars, but that doesn’t seem to prevent it from loading properly.

[rkrenzis (Ryan Krenzischek)]

Thanks for replying. I deleted the export after I was finished. The characters were umlaut (by itself), umlaut-A, followed by a comma. The characters were either shown at the beginning or end of the DID. I would have not put any of those characters there. My native keyboard (US 104-standard layout) can’t generate those characters by default. Only 13 of the DIDs had this.

I hope this helps.