[bug_fixed] VarChar/Char fields last chartacter chopped off

TheEdge · Post by **TheEdge** » 23.12.2008, 11:03

G'Day,

- I snapshotted the latest from SVN repo and built and successfully installed into my Delphi 2009 Architect Upd1 installation.
- Connecting to an existing MySQL (Version : 5.1.22-rc) database I did a SELECT * on my table and displayed this in a DevExpress CX grid. Some of the fields have their last character truncated for instance:

State char(3) utf8_general_ci
PostCode varchar(4) utf8_general_ci

Testing with the dbExpress driver against the same does not have the character chopped off. Any ideas or thoughts?

If I then attempt to edit the record I get the following exception:

exception class : EZDatabaseError
exception message : 0 record(s) updated. Only one record should have been updated.

main thread ($19eec):
008cdfc5 My.exe ZAbstractDataset 383 TZAbstractDataset.InternalUpdate
008ce2af My.exe ZAbstractDataset 470 TZAbstractDataset.InternalPost
004b138d My.exe DB TDataSet.CheckOperation
004b1040 My.exe DB TDataSet.Post

All help and pointers greatly appreciated. I include below my full table definition if it is helpful:

MySQL Version : 5.1.22-rc
Server Default Charset : latin1

Code: Select all

Field              Type         Collation        Null    Key     Default  Extra   Privileges                       Comment                                                      
-----------------  -----------  ---------------  ------  ------  -------  ------  -------------------------------  -------------------------------------------------------------
Code               char(38)     utf8_general_ci  NO      PRI                      select,insert,update,references  Unique Identifier                                            
Name               varchar(45)  utf8_general_ci  YES             (NULL)           select,insert,update,references  Human readable unique identifier                             
Address1           varchar(45)  utf8_general_ci  YES             (NULL)           select,insert,update,references  General number and street info line 1                        
Address2           varchar(45)  utf8_general_ci  YES             (NULL)           select,insert,update,references  General number and street info line 2                        
City               varchar(45)  utf8_general_ci  YES             (NULL)           select,insert,update,references  City/Town of the address                                     
Suburb             varchar(45)  utf8_general_ci  YES             (NULL)           select,insert,update,references  Suburb within the city/town.                                 
State              char(3)      utf8_general_ci  YES             (NULL)           select,insert,update,references  State that the city/town (and suburb if entered) reside.     
PostCode           varchar(4)   utf8_general_ci  YES             (NULL)           select,insert,update,references  Post code that which is defined by the city/town and suburb. 
ContactsGroupCode  char(38)     utf8_general_ci  YES             (NULL)           select,insert,update,references  The code for the group of contacst for this client.          
UserCode           char(38)     utf8_general_ci  YES             (NULL)           select,insert,update,references  The user code that the client logs into the system with

Post by **mdaems** » 23.12.2008, 11:22

exception message : 0 record(s) updated. Only one record should have been updated.

What's your setting of the Wheremode of the query? wmWhereKeyOnly? This exception means the update statement created did not find the record to update. Usually this is an bad situation. (Unless it's possible that other processes are updating the table simultaneously, then you can set a query property to disable the check)
There are multiple possible reasons in your situation
- Wheremode : when all fields are added to the where clause of the update statement and some fields are truncated this must fail, of course.
- Truncated primary key field in the pudate statement. Even with wmWhereKeyOnly you're in the situation above, then.
- Some conversion error between the internal Delphi Unicode strings and the UTF-8 encoded ANSI-strings needed for mysql.
- ????

You could start checking the cause of this error by adding a TZSQLMonitor to your project. Configure it to write a logfile. Then you can see what's sent to the server exactly. (I hope this internally doesn't convert too much as that could also hide some thing)

Mark

BTW, I hope you understood I don't know the source of the truncation at the moment. If you can debug that, fine, let us know. If not, we'll investigate, but I don't know when yet. When the test data you have is not confidential, can you dump the table using the mysql command line tools and attach it to this thread? If it's not that public you may send it to the zeoslib mailbox at reverse('moc.liamg@bilsoez'). That one's only readable by me and Michael.

TheEdge · Post by **TheEdge** » 23.12.2008, 12:26

Hi Mark,

Thanks for the quick reply. I added logging as you suggested and received the following:

Code: Select all

2008-12-23 22:21:09 cat: Execute, proto: mysql-5, msg: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
2008-12-23 22:21:10 cat: Execute, proto: mysql-5, msg: SELECT * FROM tblClients

2008-12-23 22:21:10 cat: Execute, proto: mysql-5, msg: SHOW TABLES FROM spysee_mystery LIKE 'tblClients'
2008-12-23 22:21:10 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM spysee_mystery.tblClients LIKE '%'
2008-12-23 22:21:12 cat: Execute, proto: mysql-5, msg: SELECT * FROM tblGroupItems WHERE GroupCode = "{2A8554AD-DD5E-474A-89FA-88234545270E"

2008-12-23 22:21:12 cat: Execute, proto: mysql-5, msg: SHOW TABLES FROM spysee_mystery LIKE 'tblGroupItems'
2008-12-23 22:21:12 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM spysee_mystery.tblGroupItems LIKE '%'
2008-12-23 22:21:12 cat: Execute, proto: mysql-5, msg: SELECT * FROM tblPersons WHERE Code="!!"

2008-12-23 22:21:12 cat: Execute, proto: mysql-5, msg: SHOW TABLES FROM spysee_mystery LIKE 'tblPersons'
2008-12-23 22:21:12 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM spysee_mystery.tblPersons LIKE '%'
2008-12-23 22:21:18 cat: Execute, proto: mysql-5, msg: SHOW KEYS FROM spysee_mystery.tblClients
2008-12-23 22:21:18 cat: Execute, proto: mysql-5, msg: UPDATE spysee_mystery.tblClients SET Name='Test Client ' WHERE Code='{740976EE-103E-46AF-848D-048F85FC599D'

Now as the primary keys for my data are GUID's and the last } is being chopped off I now understand why I am getting the "EZDatabaseError 0 records exception". So basically this is all about the string being chopped (which is good).

I will see what I can figure out from my end and let you know what I find. Seems rather odd that no-one else has seen this (all those that say mySQL is working) as this is a pretty obvious error.

Post by **mdaems** » 23.12.2008, 13:46

You would think so... But even the test suite didn't get that and I run it against mysql5.0, 5.1 and 6.0. These tests include insert+retrieve+compare situations!

It may have something to do with the combination of database charsets/collations used.
Some extra questions :
- what dll are you using? I see you're using a 5.1.22 server. That dll should work fine, but you never know.
- did this program work on other delphi versions before with zeoslib 6.6?

If you can't figure out what's wrong yourself, please send me a minimal failing project. (=1 form + 1 dataset + supporting components + table dump) That's the only way we can see it is (or isn't) a reproducible problem in all environments.

A hint : if you want to put a debug point to start searching, you could try to see what's returned in the TZMySQLResultSet.GetString function (ZDbcMySqlResultSet.pas). If there the string is still intact you could try to step through till you find where the truncation happens.

Mark

TheEdge · Post by **TheEdge** » 24.12.2008, 00:52

Hi Mark,

- I have yet to trace into and see if I can debug the issue locally, but I have gathered some more information for you:

- what dll are you using?

It appeared that I had some old version of libmysql.dll in System32 so to be on the safe side I used the mysql51.dll from the lib folder. No difference.

did this program work on other delphi versions before with zeoslib 6.6?

Yes:

- Delphi 2006 with ZEOS 6.x
- Delphi 2009 with dbExpress driver - At least for display

I attach a zip that contains:

- Screenshot of behaviour (Occurs both at designtime and runtime)
- madSHI stack trace so you can see all the DLL's loaded and my environment
- DB Dump of my database with some sample data so you can see collation etc.
- Sample simple application

I will now have a look at tracing through to see if I can find the truncation.

--Donovan

TheEdge · Post by **TheEdge** » 24.12.2008, 01:24

Mark,

Everything is correct until we ultimately get to:

Code: Select all

procedure TZRowAccessor.SetUnicodeString(ColumnIndex: Integer; Value: WideString);
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnConvertion(ColumnIndex, stString);
{$ENDIF}
  case FColumnTypes[ColumnIndex - 1] of
    stUnicodeString{$IFDEF DELPHI12_UP},stString{$ENDIF}:
      begin
        FBuffer.Columns[FColumnOffsets[ColumnIndex - 1]] := 0;
        Value := System.Copy(Value, 1, FColumnLengths[ColumnIndex - 1] div 2 - 1);
        
        if Length(Value) > 0 then
               System.Move(PWideString(Value)^,
                  Pointer(@FBuffer.Columns[FColumnOffsets[ColumnIndex - 1] + 1])^,
                  Length(Value) * 2 + 2)
        else
          PWideChar(@FBuffer.Columns[FColumnOffsets[ColumnIndex - 1] + 1])^ := #0;
      end;
    else
      SetString(ColumnIndex, Value);
  end;
end;

That is Value is the expected '{740976EE-103E-46AF-848D-048F85FC599D}'

But then we make the assignment:

Code: Select all

Value := System.Copy(Value, 1, FColumnLengths[ColumnIndex - 1] div 2 - 1);

I see no reason for the -1 as you are not dealing with a null terminated string due to the parameter being a WideString and that is what is causing the truncation.

Post by **mdaems** » 25.12.2008, 22:15

I changed the test suite test for string fields to fill the string fields until the last byte. This test did fail indeed ... on D2009 only. Removing the '- 1' you showed fixed the bug.

SVN rev. 555

Thanks,

Mark

TheEdge · Post by **TheEdge** » 26.12.2008, 08:28

Hi Mark,

mdaems wrote:Removing the '- 1' you showed fixed the bug.

SVN rev. 555

I am using rev 556 and your fix appears to have fixed what I reported but.... there does appear to be some pointers that are pointing off into hyperspace - see attached screen shot.

This is with the same test application and sample data. There is clearly an issue with the termination of the ContactGroupCode data but why this is duplicated within the "State" data I cannot fathom.

Tracing the code here locally briefly does not elucidate anything from what I can see.

Any thoughts?

Post by **mdaems** » 26.12.2008, 09:23

Looks like I should rollback rev 555. Or did this issue also happen before?
Seems like somewhere there's a buffer that should be zero-terminated so that the TDataset/Datagrid/Whatever knows where to stop reading a value from the internal buffer. Or maybe we should set the right string length value somewhere. Please send me a project I can test with. I'm not going to try to set this up myself. D2009 is not my main compiler, so I'm not used to using it.

Seems like the data in your test app is not sensitive, so don't be shy and package the thing from the screenshot and a table dump. That should be enough.

Mark

TheEdge · Post by **TheEdge** » 26.12.2008, 09:48

Hi Mark,

I don't believe this issue was there before. See my ZeosBug.zip attachment from a few posts back. That has all you requested in this post.

Let me know if you need anything else and I will provide it no worries.

--Donovan

Post by **mdaems** » 31.12.2008, 11:38

Sorry, didn't see the project before as I wasn't logged on when reading your post. (I only logon when I have time to read/reply all new stuff)

Please check again with last svn version (Rev 557)

Mark

UPDATE : Got confirmation in a post that I've split to a new thread (Memory leaks)