SIGSGV when refreshing table with DECIMAL field in MySQL

[waikitlo]

Hello,

I have a table in a mysql database with a field defined as Decimal(10,2). To connect to this table
I have a ZQuery component with the following SQL query:

SELECT DATE(SaleDateTime), SUM(VatLow)
FROM tblsale
GROUP BY DATE(SaleDateTime)

A DBGrid is connected to ZQuery.

I can open the query, but whenever I try to refresh the table an exception is raised of class 'External:SIGSEGV' in file
ZSysUtils.pas at line 7454. This occurs whenever SUM(VatLow) is 0.00.
I also noticed that after opening the query, the value 0.00 is displayed as 0000000.00 and other values are displayed
correctly.

Is there a way solve this?

My environment is:
- Lazarus/FPC in Windows 10 build with fpcupdeluxe
- ZEOS 7.3 installed with fpcdeluxe using testing branch

Thank you in advance.
Wai-kit

[marsupilami]

Hello Wai-kit,

just to be sure - you refresh the query using the Query.Refresh method? Or do you do that in another way? Could you try if this also happens on a select without a sum that only returns a row with 0.0? Something like "select a, b from table testtable" which only returns 1 row with 0.0 as the value?

Best regards,

Jan

[waikitlo]

Hi Jan,

Yes, the query is refreshed by calling ZQuery.Refresh method. The code looks like
if ZQuery.active then
ZQuery.Refresh
else
ZQuery.Open


Since the table has more fields of type Decimal(10,2) I have changed the SQL to:

SELECT SaleAmount, AmountPaid, VatHigh, VatLow
FROM tblSale
WHERE DATE(SaleDateTime)='2020-02-11'

In this case all fields are displayed and ZQuery.refresh does not cause the exception.
The value zero is displayed as 0.

But If I changed the SELECT to
SELECT Sum(SaleAmount), Sum(AmountPaid), SUM(VatHigh), SUM(VatLow)
Then all values are displayed, with value zero displayed as 00000000,00. And the exeception is raised when
ZQuery.refresh is called.

[marsupilami]

Ok - I will try to create a test case based on this information. That can take some days.

Some more questions though:
- Which version of MySQL do you use?
- Which version of MySQL Connector/C do you use? Or do you use the libmysql.dll provided my the server?
- Do you create a 32bits application or a 64 bits application?

Best regards,

Jan

[waikitlo]

Thank you.

I am creating a win32 bit application connecting with libmysql.dll version 6.1.6.0.
I have tested it with MySQL 8.0.18 Community Server x86_64 and MariaDB 5.5.57.

Cheers,
Wai-kit

[waikitlo]

I have downgraded ZEOS from 7.3 to 7.2.6-stable and the exception doesn't get raised at ZQuery.Refresh.

[marsupilami]

I am creating a win32 bit application connecting with libmysql.dll version 6.1.6.0

It seems that MySQL doesn't update the MySQL Connector/C for 32 Bits anymore. We had some serious problems with that version. Could you check, what happens if you use the latest version of MariaDB Connector/C?

[waikitlo]

Unfortunately the SIGSEGV still happens when I replace libmysql.dll with libmariadb.dll.

According to properties of libmariadb.dll I am using is:
- File version 3.0.0.7
- Product version 3.1.9

The code for connection to the database is:
...
if not ZQuery1.Connection.Connected then begin
// liblocation:=ExtractFileDir(Application.ExeName)+'\libmysql.dll';
liblocation:=ExtractFileDir(Application.ExeName)+'\libmariadb.dll';
ZConnection1.LibraryLocation:= liblocation;
ZQuery1.Connection.Connect;
end;
...

[waikitlo]

Ok - I will try to create a test case based on this information. That can take some days.

Some more questions though:
- Which version of MySQL do you use?
- Which version of MySQL Connector/C do you use? Or do you use the libmysql.dll provided my the server?
- Do you create a 32bits application or a 64 bits application?

Best regards,

Jan

The database server is MariaDB server 5.5.57 on a QNAP NAS. But, I get the error even if I connect to MySQL server on localhost.

[marsupilami]

Hello,

EgonHugeist checked in a patch that should solve your issue with revision 6803. Could you please test and report your findings?

Best regards,

Jan

[waikitlo]

I have downloaded and installed zeoslib-code-0-r6803-branches-testing-7.3.zip. It seems to have solved the
problem. No more SIGSEGV with ZQuery.Refresh.
I have tested with aforementioned libmysql.dll and libmariadb.dll.

Thank you for resolving this issue!
Cheers.