Collation UNICODE_PL for character SET UTF 8 is not installed (after migration from Linux to Windows)

[Paweł Sulkowski]

Hello,

I've Firebird 3.0.5x64 database on Linux which I would like to move to Windows (the same server version; also 64-bit).

Database charset is UTF8 and it has collation:

CREATE COLLATION UNICODE_PL
 FOR UTF8
 FROM UNICODE
 NO PAD 
 CASE SENSITIVE
 ACCENT SENSITIVE
 'LOCALE=pl_PL';

Database works perfectly on Linux but when I try to move it to Windows, although I can connect to it, I got errors while running queries:

COLLATION UNICODE_PL for CHARACTER SET UTF8 is not installed.

When I try to create collation on Windows I got error:

CREATE COLLATION UNICODE_PL failed.
Invalid collation attributes.

The problem is related to   'LOCALE=pl_PL' because when I remove it from "create collation" it works (but collation is obviously not the one I need). I think this problem also applies to other UTF8 collation 

I've compared fbintl.conf files on Linux and Windows and they are the same.

I've searched the Internet but I couln't find solution. I hope you can help me. 

I though I should install another version of ICU*.dll in Firebird directory but it's also not clear to me....

Hope you can help.

Best regards,

Paweł 

[Rustam]

Make DB 'backup' on Linux, then move file *.fbk to Windows and then 'restore' it on Windows.

15 квітня 2021, 02:12:08, від "Paweł Sulkowski" <pawel.s...@gmail.com>:

[Paweł Sulkowski]

Thanks for reply, but when I restore fbk I got similar error:

gbak:committing metadata
gbak: ERROR:unsuccessful metadata update
gbak: ERROR:    CUSTOMERS
gbak: ERROR:    COLLATION UNICODE_PL for CHARACTER SET UTF8 is not installed
gbak:Exiting before completion due to errors

Obviously FBK was made on Linux with -t flag and I run restore on Windows (FB3.0.5 - the same server versions)

I think issue below is related to my problem:

http://tracker.firebirdsql.org/browse/CORE-5391?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel

Solution from ticket:

"The problem has been solved. because under linux the icu data include zh locale. but on windows there is no zh locale. After I recompiled the icu, the problem is solved."

From above post I understand that the same Linux and Windows versions of Firebird may use different ICU libraries. Am I supposed to use self compiled ICU library ? If so has anynone of you have experience in such field ?

I've checked all Windows FB 3.x seems to use the same ICU library. Latest version of Firebird 4 uses newest verssion of ICI but I don't think it's a case...

Regards,
Paweł

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/1618472769.770125000.0islus3d%40frv50.fwdcdn.com.

[Alexey Kovyazin]

Hello,

Copy file restored on Windows and do

gfix -icu databasepathname

Regards,

Alexey Kovyazin 

IBSurgeon 

чт, 15 апр. 2021 г., 11:30 Paweł Sulkowski <pawel.s...@gmail.com>:

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAO8mDFfL2SM%3D0-ijCFQsyXi8%3Dt%3D6p%2BNHiC16qfMsGuf-fS4hvA%40mail.gmail.com.

[Paweł Sulkowski]

Thanks for your hint. 

I run "gfix -icu" on Windows on FDB database I copied (file copy) from Linux  but I still have the same problem. 

Simple "select * from table" produces error: 

COLLATION UNICODE_PL for CHARACTER SET UTF8 is not installed.

I binary compared files before and after "gfix -icu" and they are different but still I cannot run queries....

I've read chapter 16 of https://firebirdsql.org/file/documentation/pdf/en/firebirddocs/gfix/firebird-gfix.pdf but I still I can't find solution.

CREATE COLLATION UNICODE_PL
 FOR UTF8
 FROM UNICODE
 NO PAD 
 CASE SENSITIVE
 ACCENT SENSITIVE

 'LOCALE=pl_PL';-- when I remove this line I can create collation on Windows. WIth this line I can only create collation on Linux.

Regards,

Paweł

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAH0_ZaTifmKgx-U3xzGKO-0OJsEng8MrHjx7nzMoMhs_ft7qwA%40mail.gmail.com.

[Vlad Khorsun]

Paweł,

Firebird 3 on Windows uses relatively old ICU 52. Probably it have no support of pl_PL.

I just checked with Firebird 4 (ICU 63) and it works ok, i.e. collation was created.

So, you should check what ICU version you used on Linux and setup same (or higher) on Windows.

Regards,

Vlad

 

[Mark Rotteveel]

The ICU included with Firebird on Windows is crippled (it doesn't
complete the full ICU locale information). This is - IIRC - fixed for
Firebird 4, but for older versions you will need to obtain the right
version of ICU and compile ICU yourself (or find an already compiled ICU
library to replace the one included with Firebird 3).

Mark

[Mark Rotteveel]

On 2021-04-15 10:32, Alexey Kovyazin wrote:
> Hello,
>
> Copy file restored on Windows and do
> gfix -icu databasepathname

That won't work given the ICU library included with Firebird on Windows
is incomplete.

Mark

[Mark Rotteveel]

The ICU version included with Firebird on Windows was intentionally
crippled in the past to reduce the download size by only including a
limited number of locales and collations that were already explicitly in
use. This is not a limitation of ICU itself, just of the compiled
binaries that Firebird includes.

Mark

[Paweł Sulkowski]

 Thank you all for you replies. They helped me to solve the issue. For those interested in solution please referer to th description below.

1. I've downloaded latest version of ICU Winx64 binary (http://site.icu-project.org/download)

2. Next I've stopped Firbird server and deleted icu* files from Firebird directory (icudt52.dll, icudt52l.dat, icuin52.dll, icuuc52.dll)

3. After that I copied ICU DLL from version 69 (icudt69.dll, icuin69.dll, icuio69.dll, icutu69.dll, icuuc69.dll) to Firebid directory

4. Then I file-copied FDB file from Linux server to Windows one

5. Eventualy I had to run gfix -icu PATH_TO_DATBASE which fixed database (as I understand in RDB$COLLATION) 

Thanks one more time for you support. 

Kindly regards,

Paweł 

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/6ee5046b8fbbd7b4a5e5a0904f088c9e%40lawinegevaar.nl.

[Paweł Sulkowski]

I've just made clean install of Firebird 3.0.5 x64 on another Win10 and copied DLLs from ICU 69 version to Firebird directory.

When I try to connect to any database I've got error:

DESKTOP-1QFKAKT Tue Jul 20 11:25:41 2021
Authentication error
Could not find acceptable ICU library

Oddly enough, it works on some machines.... 

Any thoughts ?

Kindly regards,

Paweł 

[Paweł Sulkowski]

Luckily I found solution which turned out to be very simple. I was missing Visual C++ Rediscributabes. Once installed, everything works like a dream. 

Regards

Paweł