SQL Error in MairList Library

Hi Guys,

When opening a folder in my Mairlist DB Library I get this error:

SQL Error: ERROR: character 0xe28093 of encoding “UTF8” has no equivalant in “LATIN1”

Has anyone has this before and know a potential fix?


That means that a dataset contains a character (stored in UTF-8 format inside PostgreSQL) that cannot be converted into the local character set of the application (LATIN1).

It is rather unlikely that this dataset was created with mAirList itself (because it can only create and use LATIN1 compatible characters - a full unicode version is still to be released) - do you use any external tool to populate the mAirListDB with data?

In case you are using v4.3 beta, there were a couple of faulty builds (around b1830) that possibly saved corrupted characters that lead to the same message. In that case, you can only search and correct/delete the datasets manually in PostgreSQL, or restore an earlier backup.

Thanks for the prompt reply.
Ahhh yes running am running 4.3.1834 beta. It seems if I downgrade to 4.3.1817 it works. But not on the latest vuild.

I have PGAdmin 3 installed but no other software using it.

Is there any tips you can give me on how to go in and correct the datasets?

Learning for future :slight_smile:


Hello Ethan, long time no see!

I think that sums it up, really: it’s annoying but it is the only way to fix it.

Moral of the story: don’t use beta builds with your main live database: always COPY your database first and point the beta/test version at the COPY of the database. They are called ‘beta’ versions for a reason, and I’m afraid it’s up to the individual user to protect their live system against any potential damage which a beta version could cause.

Obviously, I’m still sorry to hear about your misfortune; If you don’t have a database backup you can restore, maybe a local PostGRE guru could help you track down and fix the corrupted data? And also help you set up a regular backup, of course. :wink:


When trying to repair manually, step #1 is to determine the ID of the corrupted item. The following script will help:

  i: integer;

  for i := 1 to 5000 do 
      SystemLog(IntToStr(i) + ': ' + ExceptionParam);

Replace 5000 by what you think is the highest item ID in your mAirListDB; choose a larger value if unsure.

The script will try to retrieve all items from 1 to 5000 from the database (be patient, it will take a while), and output an error message to the system log if it fails. You will probably see a lot of “Invalid item ID” messages for IDs that don’t exist in the database. But you may also come across the UTF8 error message…

Once the item ID is known, you can use pgAdmin in order to inspect and possibly edit the “items”, “items_attributes” etc. tables for that item.

Thanks guys will have a play :).

It was found that there was another bug in mAirList 4.3 (actually in the ZeosLib database library) that may cause the above mentioned error under certain circumstances.

Please try the latest snapshot build 1845 that was just uploaded.

That fixed it! Thankyou!

Turned out that the ZeosLib team thinks WIN1252 is the same as LATIN1, which is not…

We had the SQL Error: ERROR: character 0xc292 of encoding “UTF8” has no equivalant in “WIN1252”

Our database was populated with the 4.3.0 release. After the correction in 4.3.1 we had this error.
The solution was - at last - quite simpel.

The 0xc292 should have translated to chr(39) (’), but instead in some cases it is coded to chr(146)
to correct this I used the next SQL in the Postges query tool (direct on the database):

update items
set title = replace(title, chr(146), chr(39))
where strpos(title, chr(146)) > 0;

– ran this on title, artist and filename. It was not necessary to run it on the item_attributes table in our case

To explore wich character to change in what, you can use the script provided by Torben.
with the found index items.idx display the fields and look for a weird icon / char in that record:
select * from items where idx = numberofrecord

count the position in say title, and show ascii:
select ascii(substring(title from position for 1)) from items where idx = numberofrecord

find out what ascii to change to, put the character you want to see on the faulty one between ‘’ s:
select ascii(‘x’)

update the sql:
update items
set fieldname = replace(fieldname, chr(numeric code from), chr(numeric code to))
where strpos(fieldname, chr(numeric code from)) > 0

Same here . Today i did not recieve the error message: One tip though . You can skip this errror by selecing two records of the music libary , this case it helps with me