"show create table" statement does not load full result from Database

[zakiwi]

Hi there,

I've got the strangest problem in that I am executing a "show create table TABLENAME;" statement against MySQL, and I am not getting all the data in the result fields.  I thought that the database server was not returning all the data, but I used Wireshark (a packet sniffer) to show me the data coming from the server and it is complete.

As a second point of interest, this problem only occurs when I query remote MySQL servers.  If I query the local MySQL server on my development box, then the result fields do populate correctly.

I have no idea what to try next.  Any advice would be appreciated.

The data appears to be truncated at the same point each time that I run it against any table, but not all tables are truncated at the same point with reference to one another.

ie: The result for the table below is always 341 bytes long, but there is clearly more in the table definition, and Wireshark shows that the database server did return the rest of the definition.  It's like it's not getting loaded into the field, but if it's run against the localhost, then it seems to work.

TABLE: DNS_DOMAIN (341)
-------------------------------------------------------------------------------
CREATE TABLE `DNS_DOMAIN` (
  `domain_name` varchar(255) NOT NULL,
  `primary_name_server` varchar(255) NOT NULL,
  `responsible_person_email` varchar(255) NOT NULL,
  `serial_no` int(11) unsigned NOT NULL,
  `refresh_interval` int(11) unsigned NOT NULL,
  `retry_interval` int(11) unsigned NOT NULL,
  `expire_interval` int(11) unsigned NOT
-------------------------------------------------------------------------------


Kind regards

Zakiwi
Laz: 0.9.28.2-10
FPC: 2.4.0
MySQL 5.1
OS: Linux (Both 32 and 64)

[Lacak2]

Which database access component do you use ?
Can you provide simple test program (which creates temporary table and queries table schema) ?

[zakiwi]

Hi there,

My program is a bit complex to hack apart, but here is the core of the code that you will be interested in.

Thanks for your input.

Cheers

Zakiwi

---------------------------------------------------------------

var
    lcDBConnection :TMySQL50Connection;
    lcDBTransaction :TSQLTransaction;
    lcTableStructureQuery :TSQLQuery;
    lsCreateQuery :string;
begin
{ OK, lets create and initialize the object members }
lcDBConnection := TMySQL50Connection.Create(nil);
lcDBTransaction := TSQLTransaction.Create(nil);

try
  lcDBConnection.HostName := 'HOST-NAME';
  lcDBConnection.DatabaseName := 'DB-NAME';
  lcDBConnection.UserName := 'USER-NAME';
  lcDBConnection.Password := 'PASSWORD';

  lcDBTransaction.DataBase := lcDBConnection;

  try
    { OK, now lets create and initialize the Table Structure Query }
    lcTableStructureQuery := TSQLQuery.Create(nil);

    { OK, lets attach to the primary database connection }
    lcTableStructureQuery.DataBase := lcDBConnection;
    lcTableStructureQuery.Transaction := lcDBTransaction;

    { OK, now lets get the table structure }
    lcTableStructureQuery.SQL.Text := 'show create table ' + TableName + ';';
    lcTableStructureQuery.Open;

    { OK, we should have a record with two fields }
    if lcTableStructureQuery.RecordCount > 0 then
      begin
        { OK, lets make sure that we have the expected number of fields in the result }
        if lcTableStructureQuery.FieldCount = 2 then
          begin
WriteLn('');
WriteLn('');
WriteLn('TABLE: ' + lcTableStructureQuery.Fields[0].AsString + ' (' + IntToStr(Length(lcTableStructureQuery.Fields[1].AsString)) + ')');
WriteLn('-------------------------------------------------------------------------------');
WriteLn(lcTableStructureQuery.Fields[1].AsString);
WriteLn('-------------------------------------------------------------------------------');
WriteLn('');
WriteLn('');

            { OK, now lets process the result into a list that can be processed }
            lsCreateQuery := lcTableStructureQuery.Fields[1].AsString;

            { OK, now, lets clean up the create query string }
            //DoProcessCreateQuery(lsCreateQuery);

            { OK, now lets process the field list }
            //DoProcessFieldList;

            { And now, lets process the index list }
            //DoProcessIndexList;
          end;
      end;
  finally
    { Clean up }
    FreeAndNil(lcTableStructureQuery);
  end;                                 

finally
  { Clean up }
  FreeAndNil(lcDBTransaction);
  FreeAndNil(lcDBConnection);
end;
end

[Lacak2]

For me it works, full length (399) is returned.
In both cases when querying localhost also when querying from remote PC.

[zakiwi]

OK, this has been really odd, but I have managed to get more information.

The query fails to return all the data (as reported above) when executed against a MySQL 5.0 server.

When executed against a MySQL 5.1 Server, then it works reliably.

Lacak2 --- is there any chance that you could confirm this on your side?  That would be good.

Kind regards

Zakiwi

[Lacak2]

Strange  that this error appears on MySQL 5.0, but not MySQL 5.1
(may be, that then it is MySQL bug (although I can not believe it  ), so upgrade MySQL is only solution)
Yes I also use MySQL 5.1 on Windows Vista.
Can not you also upgrade to MySQL 5.1 and leave MySQL 5.0?

[zakiwi]

As I mentioned in my first post, I have checked the response from MySQL, and it is definitely returning all the data.  I think that the fault must exist in Laz/FPC.

We have a large number of production systems that use both MySQL 5.0 and 5.1, and I guess it would be nice to see the bug fixed.

We can probably get away without it being fixed, but it would be better if it was fixed as the fault is entirely reproducible, and may in fact have other knock on effects that non of us have found yet.

Cheers

Zakiwi

[BigChimp]

Zakiwi,

If it is reproducble, that would be pretty serious...

If you haven't already done so, I'd suggest you raise an issue on the bugtracker with your findings, a small example program that reproduces the error...

(Just checked, have a remote 5.1 MySQL server to play with here, so probably only useful for negative confirmation...)

[zakiwi]

Yes, it is reproducible, but just to clarify one point.  It happens on any MySQL 5.0 server local or remote.

Cheers

Zakiwi

[Lacak2]

On FPC side is mysql5.0 = mysql5.1 (at least at TMySQL5xConnection level)
There is equal code, so I do not think, that there is some error.

You wrote, that over network all data arrives, but there is still mysql client library "libmysql.dll", which passes this data to FPC ... so there is another place for bug (but I do not say, that bug must exists there)

Can you try current snapshot with FPC 2.7.1 ?
ftp://ftp.freepascal.org/pub/lazarus/snapshots/

[ludob]

I can confirm the problem. Mysql client version (XP) 5.1.11. fpc 2.7.1 r18901, fcl-db trunk.

Same table definition on server 5.0.51a returns 616 characters (truncated) and on 5.1.41 returns 1848 characters (complete).

[ludob]

Found the cause of the problem. Mysql5.0 returns a field length of 616 and a max_length of 1848. 5.0 returns 1848 for both.  TConnectionName.MySQLDataType  in mysqlconn.inc uses length...

The 5.0 and 5.1 manuals say:
"The server determines the length value before it generates the result set, so this is the minimum length required for a data type capable of holding the largest possible value from the result column, without knowing in advance the actual values that will be produced by the query for the result set. " For real columns this doesn't seem to matter a lot but for calculated columns, "length" doesn't seem to be the correct value to determine the field size.