DB errors during migration from DWS

[sse450]

Hello,
I am trying to migrate from DWS to Tekki version. But, I receive lots of db tables errors. What is the proper way of upgrading db tables?
Thank you.

PS:I have been using SL/DWS for more than 20 years. Also, I am one of the translators. Currently, my setup is stuck with CentOS 7 due to unicode problems with newer OSs. One of the SQL-Ledger forum members put your diagnosis into our attention: "SQL-Ledger working with binary instead of Perl characters." This is spot on and your version of SL is working without any unicode issue. Thank you for this.

[Tekki]

Hello there
Please give more information about the system you want to upgrade (OS, Perl version, Postgres version, SQL-Ledger version). And describe how you installed SQL-Ledger from this repo (because there is a pending change that doesn't have a version yet).

[sse450]

My current setup is SL files on CentOS 7, DB is on AlmaLinux 9 as SL files on AL9 creates unicode problems. I am trying to relocate SL files also to AL9 and get rid of CentOS 7.

Here is the info you asked:

Database:
OS: Alma Linux 9.3
Perl Version: v5.32.1
PostgreSQL: 15.6

SL files:
SQL-Ledger: Versiyon 3.2.12 (these are on a different CentOS 7 VM. Unicode problems happens if I take them to Alma Linux 9.3)

I always install SQL-Ledger by downloading new copy on to the web server root. Copy members and users from the old version of SL to the new one. Change permissions and file owners accordingly. perl-DBI, perl-DBD-Pg packages are already installed.

I have no idea about pending change without a version. I just downloaded what was released.

Thank you.

[Tekki]

With how you installed SQL-Ledger I meant how you downloaded the program from this repo here on Github. But before you try again give me some time to publish a new version, so we are sure we speak of the same code.

When you move the data to the new server, do you use the SQL-Ledger backup function or pgdump? And is your database in UTF-8 already?

And the last question for the moment: What translation do you use?

[sse450]

Sorry. I downloaded your version from the releases page as zipped source code (3.2.12.039) file.

1. I used pgdump while migration. Checked pgdumped files using a text editor. The entries are OK. Also checked the dumped files with "file -bi dumped_file" command to verify that all dumped files are UTF-8.
2. After restore, all data table entries look OK when viewed using Adminer. Also, All databases, collations, ctypes are UTF-8 in PostgreSQL.
3. I use tr_TR.UTF-8 encoding. I am the translator of all the static files in locale/tr directory of SL.

All the old entries entered from CentOS 7 look nice on database tables and SL pages residing on CentOS7. But they look garbage when viewed inside SL pages residing Alma Linux 9.3.

New entries entered through SL residing on Alma Linux 9.3 look nice in SL. But look garbage on database tables.

I tested your version while SL files are on AL9.3. All the new and old entries look OK (in tables and in SL pages).

Thank you.

[Tekki]

If you are sure that your database is UTF-8 then all is fine on this side. You are already on 3.2 from DWS, so the upgrade won't be too difficult.
You can now download version 3.2.12.040 from this repo. There should be no more errors when you upgrade from a DWS backup.
Next you have to set the translation to "Turkish (UTF-8)" in the user settings. That's the one from directory locale/tr_utf. The files in locale/tr don't work because they have wrong metadata. I've updated the UTF-8 translation so all backreferences work. I'm sure you know that this translation is only at 29 %, don't you?

[sse450]

Thank you.

I will try to install and inform back you here.

Translation files were almost 100%. Already submitted. But, somehow, not integrated the release. Will check it too.

[Tekki]

Translation files were almost 100%. Already submitted. But, somehow, not integrated the release. Will check it too.

You could create another issue for the translation.

[sse450]

Installed Tekki 3.2.12.40. It upgraded all the databases (3.2.12DWS) without any problem. Encoding is correct when viewed directly on the db tables and inside SL pages. No problem with the existing and new entries.
I really appreciate your efforts on unicode support. We don't have to use old OS or old perl-DBD-Pg any more.
Now, trying to install latex.

[Tekki]

I really appreciate your efforts on unicode support.

You're welcome.

Did you notice that my version needs some additional Perl modules? You should install Excel::Writer::XLSX and Mojolicious.

[sse450]

Dear Tekki, I installed Excel::Writer::XLSX and Mojolicious perl modules. But when I click spreadsheet button, I get 3 lines of error and garbage characters below these lines.
Why is that?

Here is an excerpt:
Argument "#8EA9DB" isn't numeric in numeric ne (!=) at /usr/share/perl5/vendor_perl/Excel/Writer/XLSX/Package/Styles.pm line 797. Argument "#4472C4" isn't numeric in numeric ne (!=) at /usr/share/perl5/vendor_perl/Excel/Writer/XLSX/Package/Styles.pm line 797. Argument "#4472C4" isn't numeric in numeric ne (!=) at /usr/share/perl5/vendor_perl/Excel/Writer/XLSX/Package/Styles.pm line 797. Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet Content-Disposition: attachment; filename*=UTF-8''Bor%c3%a7%20%c4%b0%c5%9flemleri%20%2f%20Onart%20Yap%c4%b1%20Sistemleri%20Ltd.%20%c5%9eti..xlsx PK����!a]I:O����[Content_Types].xml���n�0�E�����*1tQU��E������\{B,��������P[Q��M�d��sǎ<�-��- � ����'2�:�맥x�<�w"CR�(�<�b�(Fë�d��3n�X��(�K���Fa�"x�T!5��5MeTz�� oz�[��'�S�!��G���Q����� ���a-lY�P1:��q].��E�7��;; �6�5���Kh+��6}��3����*ыjX%M���"J���]��Ue5�Ǽ���@�L����Y�e>��!����=j�O$.�DZ9��GŘ@����q�����������6��9��i�����ök�(�O�wb��r��?�����y���7J| \��{os��>�~�PK����!�I��K��

[Tekki]

Never seen this before, but it seems this is an error produced by a newer version of Excel::Writer::XLSX installed from CPAN. I'll look into it as soon as I have time (give me 2 or 3 days).

[sse450]

Just FYI.
I installed Excel::Writer::XLSX module as compiled package (perl-Excel-Writer-XLSX-1.12-1.el9.noarch), not from CPAN.

[Tekki]

But when I click spreadsheet button, I get 3 lines of error and garbage characters below these lines. Why is that?

New issue for the spreadsheet error created: #64

[sse450]

As a new issue #64 is opened, this one is closed.