ZEOSDBO-6.6.6-stable with mysql bug 134 columns varchar 255

Alex17000 · Post by **Alex17000** » 29.11.2010, 14:34

Hi,

I found a bug with Zeos, I have make a table with 134 columns varchar 255,
And when I try to open the table I got a range error.

It's reproductible.

Exemple of table mysql :

Code: Select all

CREATE TABLE IF NOT EXISTS `table_01` (
  `col_1` varchar(255) NOT NULL,
  `col_2` varchar(255) NOT NULL,
  `col_3` varchar(255) NOT NULL,
  `col_4` varchar(255) NOT NULL,
  `col_5` varchar(255) NOT NULL,
  `col_6` varchar(255) NOT NULL,
  `col_7` varchar(255) NOT NULL,
  `col_8` varchar(255) NOT NULL,
  `col_9` varchar(255) NOT NULL,
  `col_10` varchar(255) NOT NULL,
  `col_11` varchar(255) NOT NULL,
  `col_12` varchar(255) NOT NULL,
  `col_13` varchar(255) NOT NULL,
  `col_14` varchar(255) NOT NULL,
  `col_15` varchar(255) NOT NULL,
  `col_16` varchar(255) NOT NULL,
  `col_17` varchar(255) NOT NULL,
  `col_18` varchar(255) NOT NULL,
  `col_19` varchar(255) NOT NULL,
  `col_20` varchar(255) NOT NULL,
  `col_21` varchar(255) NOT NULL,
  `col_22` varchar(255) NOT NULL,
  `col_23` varchar(255) NOT NULL,
  `col_24` varchar(255) NOT NULL,
  `col_25` varchar(255) NOT NULL,
  `col_26` varchar(255) NOT NULL,
  `col_27` varchar(255) NOT NULL,
  `col_28` varchar(255) NOT NULL,
  `col_29` varchar(255) NOT NULL,
  `col_30` varchar(255) NOT NULL,
  `col_31` varchar(255) NOT NULL,
  `col_32` varchar(255) NOT NULL,
  `col_33` varchar(255) NOT NULL,
  `col_34` varchar(255) NOT NULL,
  `col_35` varchar(255) NOT NULL,
  `col_36` varchar(255) NOT NULL,
  `col_37` varchar(255) NOT NULL,
  `col_38` varchar(255) NOT NULL,
  `col_39` varchar(255) NOT NULL,
  `col_40` varchar(255) NOT NULL,
  `col_41` varchar(255) NOT NULL,
  `col_42` varchar(255) NOT NULL,
  `col_43` varchar(255) NOT NULL,
  `col_44` varchar(255) NOT NULL,
  `col_45` varchar(255) NOT NULL,
  `col_46` varchar(255) NOT NULL,
  `col_47` varchar(255) NOT NULL,
  `col_48` varchar(255) NOT NULL,
  `col_49` varchar(255) NOT NULL,
  `col_50` varchar(255) NOT NULL,
  `col_51` varchar(255) NOT NULL,
  `col_52` varchar(255) NOT NULL,
  `col_53` varchar(255) NOT NULL,
  `col_54` varchar(255) NOT NULL,
  `col_55` varchar(255) NOT NULL,
  `col_56` varchar(255) NOT NULL,
  `col_57` varchar(255) NOT NULL,
  `col_58` varchar(255) NOT NULL,
  `col_59` varchar(255) NOT NULL,
  `col_60` varchar(255) NOT NULL,
  `col_61` varchar(255) NOT NULL,
  `col_62` varchar(255) NOT NULL,
  `col_63` varchar(255) NOT NULL,
  `col_64` varchar(255) NOT NULL,
  `col_65` varchar(255) NOT NULL,
  `col_66` varchar(255) NOT NULL,
  `col_67` varchar(255) NOT NULL,
  `col_68` varchar(255) NOT NULL,
  `col_69` varchar(255) NOT NULL,
  `col_70` varchar(255) NOT NULL,
  `col_71` varchar(255) NOT NULL,
  `col_72` varchar(255) NOT NULL,
  `col_73` varchar(255) NOT NULL,
  `col_74` varchar(255) NOT NULL,
  `col_75` varchar(255) NOT NULL,
  `col_76` varchar(255) NOT NULL,
  `col_77` varchar(255) NOT NULL,
  `col_78` varchar(255) NOT NULL,
  `col_79` varchar(255) NOT NULL,
  `col_80` varchar(255) NOT NULL,
  `col_81` varchar(255) NOT NULL,
  `col_82` varchar(255) NOT NULL,
  `col_83` varchar(255) NOT NULL,
  `col_84` varchar(255) NOT NULL,
  `col_85` varchar(255) NOT NULL,
  `col_86` varchar(255) NOT NULL,
  `col_87` varchar(255) NOT NULL,
  `col_88` varchar(255) NOT NULL,
  `col_89` varchar(255) NOT NULL,
  `col_90` varchar(255) NOT NULL,
  `col_91` varchar(255) NOT NULL,
  `col_92` varchar(255) NOT NULL,
  `col_93` varchar(255) NOT NULL,
  `col_94` varchar(255) NOT NULL,
  `col_95` varchar(255) NOT NULL,
  `col_96` varchar(255) NOT NULL,
  `col_97` varchar(255) NOT NULL,
  `col_98` varchar(255) NOT NULL,
  `col_99` varchar(255) NOT NULL,
  `col_100` varchar(255) NOT NULL,
  `col_101` varchar(255) NOT NULL,
  `col_102` varchar(255) NOT NULL,
  `col_103` varchar(255) NOT NULL,
  `col_104` varchar(255) NOT NULL,
  `col_105` varchar(255) NOT NULL,
  `col_106` varchar(255) NOT NULL,
  `col_107` varchar(255) NOT NULL,
  `col_108` varchar(255) NOT NULL,
  `col_109` varchar(255) NOT NULL,
  `col_110` varchar(255) NOT NULL,
  `col_111` varchar(255) NOT NULL,
  `col_112` varchar(255) NOT NULL,
  `col_113` varchar(255) NOT NULL,
  `col_114` varchar(255) NOT NULL,
  `col_115` varchar(255) NOT NULL,
  `col_116` varchar(255) NOT NULL,
  `col_117` varchar(255) NOT NULL,
  `col_118` varchar(255) NOT NULL,
  `col_119` varchar(255) NOT NULL,
  `col_120` varchar(255) NOT NULL,
  `col_121` varchar(255) NOT NULL,
  `col_122` varchar(255) NOT NULL,
  `col_123` varchar(255) NOT NULL,
  `col_124` varchar(255) NOT NULL,
  `col_125` varchar(255) NOT NULL,
  `col_126` varchar(255) NOT NULL,
  `col_127` varchar(255) NOT NULL,
  `col_128` varchar(255) NOT NULL,
  `col_129` varchar(255) NOT NULL,
  `col_130` varchar(255) NOT NULL,
  `col_131` varchar(255) NOT NULL,
  `col_132` varchar(255) NOT NULL,
  `col_133` varchar(255) NOT NULL,
  `col_134` varchar(255) NOT NULL
              ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

You must insert one record. And make :

Code: Select all

var
  Connection_ : Tzconnection;
  ZReadOnlyQuery_ : TZReadOnlyQuery;
begin
  Connection_ := Tzconnection.Create(nil);
  Connection_.Protocol := 'mysql';
  ZReadOnlyQuery_ := TZReadOnlyQuery.create(nil);
  ZReadOnlyQuery_.connection := Connection_;

  Connection_.HostName := 'ip_server';
  Connection_.Database := 'mydatabase';
  Connection_.User     := 'myuser';
  Connection_.Password := 'mypassword';
  Connection_.Connect;

  ZReadOnlyQuery_.close;
  ZReadOnlyQuery_.sql.text := 'select * from mydatabase.mytable;';
  ZReadOnlyQuery_.open;

  ZReadOnlyQuery_.close;

  ZReadOnlyQuery_.free;
  Connection_.free;
end;

And here you got a exception "erangeerror" on zeos 6.6.6 on delphi 5
and "Row buffer width exceeded. Try using fewer or longer columns in SQL query." on zeos 7.0
on delphi 2010.

I have tested with mysql 5.1.52, 5.1.48, 5.1.35, 5.0.45 and 4.0.15.
I try with 134 columns of type integer without any problem.

It's very important to get all the columns with one sql query.

Thanks,

Post by **mdaems** » 05.12.2010, 22:47

Alex17000,

Sorry, but because of the way the string fields are stored internally in the zeoslib cache, this limitation is quite absolute. Problem is the fact that 134*255 bytes (or in delphi2009+ double bytes) are reserved for every record, resulting in huge cache sizes. (Which explains why numbers cause no trouble)
There are a few ways you could try to solve this.
- Use smaller text columns
- Try to fetch the columns as blobs, as they only take a few bytes in the result cache objects. When you do this by typecasting in your query this will result in non-updatable resultsets, however.
- Help gto redesigning the cache layout for strings. He's trying to find a better string storage method, but that will not be available before zeoslib 7, if it will ever be introduced.

Mark