View Issue Details

IDProjectCategoryView StatusLast Update
0035996FPCDatabasepublic2019-08-26 15:55
ReporterZdravko GabrovskiAssigned ToMichael Van Canneyt 
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionwon't fix 
Product VersionProduct Buildtrunk 
Target VersionFixed in Version 
Summary0035996: Issue with String field size in MySQL/Maria DB utf8mb4_general_ci encodind, which cause SIGKILL
Description

I have a MySQL/Maria DB Database, the a table with the following structure:

CREATE TABLE `master_video_data` (
  `vd_id` int(11) NOT NULL,
  `id` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `title` varchar(2048) CHARACTER SET utf8mb4 NOT NULL,
  `channelTitle` varchar(2048) CHARACTER SET utf8mb4 NOT NULL,
  `channelId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `duration` varchar(255) NOT NULL,
  `embeddable` tinyint(1) NOT NULL,
  `viewCount` int(11) NOT NULL,
  `likeCount` int(11) NOT NULL DEFAULT 0,
  `dislikeCount` int(11) NOT NULL DEFAULT 0,
  `commentCount` int(11) NOT NULL DEFAULT 0,
  `embedHtml` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='General video data table';

I have a simple lazarus application with a form with one edit field, button, and DBGrid.
I have a datamodule with TMySQL57Connection, which points to the Maria DB database.
I have also TQuery component, named qSeacrh, datasouce dsSearch, and transaction trnSearch, which is connected to qSearch.

On ButtonOnClieck event handler, I have the following code:

  dmvjpmain.qSearch.Close;
  dmvjpmain.trnSearch.Rollback;
  dmvjpmain.qSearch.ParamByName('SEARCH_TAB').AsString:=lblEdtSearchText.Text;
  dmvjpmain.trnSearch.StartTransaction;
  dmvjpmain.qSearch.Open;

Where dmvjpmain points to datamodule name dmvjpmain.

the qSearch SQL is:

SELECT vd_id, id, title, channelTitle, channelId, duration, viewCount, likeCount, dislikeCount, commentCount FROM `master_video_data` WHERE
title like :SEARCH_TAB

The query has added in design-time TFiedDefs and TFields, which is with correct field sizes for string fieldas, 2048 for 'title' field,(see attachments) which I will give as example of the problem.

So, if I put some value into edit field and press a button, if the result of the query is non-empty, application hangs or quit unexpectly with SIGKILL event.

After some research and debug, I fount the problem.

The SIGKILL is result of
Move(CurrBuff^, Buffer^, GetFieldSize(FieldDefs[Field.FieldNo-1]));

at line 2573 in bufdataset.pas unit.

Why?

The reason is result of function call GetFieldSize(FieldDefs[Field.FieldNo-1]), at the same line, which will retun how many bytes to move.
This function calculates number of bytes in case of TString field type, as
result := FieldDef.Size*FieldDef.CharSize + 1;
Where fieldDef.Size if the size into fielddef property, CharSize = 4 + 1 byte.
At design time for TFieldDef 'title', size is = 2048,(see attachment) and the result of the function call must be 8193, which is the allocated buffer size, and everithing must be OK.

But I was surprised, that inrun time, after query open, this size become = 2730, which cause this wrong buffer size!

I add the follwing code to button push handler:

  dmvjpmain.qSearch.Close;
  dmvjpmain.trnSearch.Rollback;
  dmvjpmain.qSearch.ParamByName('SEARCH_TAB').AsString:=lblEdtSearchText.Text;
  dmvjpmain.trnSearch.StartTransaction;

  for i := 0 to qSearch.FieldDefs.Count - 1 do begin
    fName_ := qSearch.FieldDefs[ i ].Name;
    if fName_ = 'title' then
      fSize_ := qSearch.FieldDefs[ i ].Size; // <---- HERE SIZE IS =2048
    end;


  dmvjpmain.qSearch.Open;
  S := dmvjpmain.qSearch.FieldByName('title').AsString;
  for i := 0 to qSearch.FieldDefs.Count - 1 do begin
    fName_ := qSearch.FieldDefs[ i ].Name;
    if fName_ = 'title' then begin
        fSize_ := qSearch.FieldDefs[ i ].Size; // <----- But after open this is = 2730
        fSize1 := qSearch.FieldByName(fName_).Size; // <----- TField size is still 2048
        end;
    end;

Temporary solution: At the moment, simple set TField size for all varchar(2048) to 3000, this prevents SIGKILL.
But will be better to debug and fix the issue.



Steps To ReproduceAs described above.
TagsNo tags attached.
Fixed in Revision
FPCOldBugId
FPCTarget-
Attached Files

Activities

Zdravko Gabrovski

2019-08-23 11:41

reporter  

Screenshot at 2019-08-23 11-07-26.png (294,298 bytes)

Zdravko Gabrovski

2019-08-23 13:10

reporter   ~0117792

You can not even add a fields in design time, if does not put where clause in SQL, which will restrict query to return empty recordset.
For example in my case: When I click "Active" to true to the query with SQL

SELECT Distinct channelTitle, channelId FROM `master_video_data`

where channelTitle field is VARCHAR (2048), it immidiately raises AV and restart the IDE.

But if i put SELECT Distinct channelTitle, channelId FROM `master_video_data` where 1=0

It will give me a chanse to add fiends and after that to set size to channelTitle to 3000, which prevents AV.

Maxim Ganetsky

2019-08-23 13:58

reporter   ~0117793

Bufdataset.pas unit belongs to FCL. Moving to FPC project.

Michael Van Canneyt

2019-08-24 11:05

administrator   ~0117816

This is a problem of MariaDB.

TDataset works as follows:
When the query is opened, the field size is stored in TFieldDef.
If there are no fields, the fields are created with the size in TFieldDefs.
You can make these fields persistent.

If there are persistent fields, their size is not updated, because the theoretical size of a field must be the same no matter how often you run the query:
The field size must be the same every time you open the query.

If MariaDB reports different result field sizes depending on the query result set, this is not consistent and is simply not compatible with the design of TDataset.


Zdravko Gabrovski

2019-08-26 15:55

reporter   ~0117848

I am verry sorry, it was my fault!
I override connection charset utf8mb4 with utf8, which causes wrong buffer size.

Many thanks for Michael Van Canneyt, for him cooperation, which help me a lot to resolve the issue!

Issue History

Date Modified Username Field Change
2019-08-23 11:41 Zdravko Gabrovski New Issue
2019-08-23 11:41 Zdravko Gabrovski File Added: Screenshot at 2019-08-23 12-02-33.png
2019-08-23 11:41 Zdravko Gabrovski File Added: Screenshot at 2019-08-23 11-23-56.png
2019-08-23 11:41 Zdravko Gabrovski File Added: Screenshot at 2019-08-23 11-07-26.png
2019-08-23 13:10 Zdravko Gabrovski Note Added: 0117792
2019-08-23 13:58 Maxim Ganetsky Note Added: 0117793
2019-08-23 13:59 Maxim Ganetsky Project Packages => FPC
2019-08-24 11:05 Michael Van Canneyt Assigned To => Michael Van Canneyt
2019-08-24 11:05 Michael Van Canneyt Status new => resolved
2019-08-24 11:05 Michael Van Canneyt Resolution open => won't fix
2019-08-24 11:05 Michael Van Canneyt FPCTarget => -
2019-08-24 11:05 Michael Van Canneyt Note Added: 0117816
2019-08-26 15:55 Zdravko Gabrovski Status resolved => closed
2019-08-26 15:55 Zdravko Gabrovski Note Added: 0117848