FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

I won't be surprised if there are also some limit in sql server.

You are sending the data inside a language query string. It's quite
surprising changing log settings solve the issue, perhaps there is a
bug inside Ctlib (DBD::Sybase should use ctlib). You can try using a
packet sniffer to get traffic with the server. Can you post (or send
to me if you prefer) the logs anyway?
Probably you'd better compress them. From the log we can understand
which calls DBD::Sybase does so I can try reproduce with C directly.

Frediano


2015-10-06 1:36 GMT+01:00 Jacopille, David <DJacopille AT mfs.com>:
> DC - Thanks for the quick reply.  Text size is positively set to one 
> million characters.
>
> 1.  The data SELECT of 500,000 characters always works.
> 2.  SELECT @@textsize does confirm the 1,000,000 setting (but the above 
> also proves functionality)
> 3.  'Text size' setting only applies to SELECT statements, not INSERT where 
> we are having the issue.  This is demonstrably true for MS SQL Server, 
> which we are using.  Don't know about other databases.
>
> SQL Server 2012 nvarchar(max) does hold 2 GB theoretical.  This specific 
> table and nvarchar(max) column has proven to handle far more than the 1 MB 
> values I'm testing with.   At least through other software.
>
> I suspect that there is a bug in either FreeTDS 0.95 or DBD::Sybase 1.15 
> that is limiting the text.  However I find FreeTDS more suspicious because 
> turning on logging (which changes something in FreeTDS) fixes the problem.
>
> Unfortunately the thing that would be useful to find the problem is what 
> fixes the problem.
>
> Dave
>
> -----Original Message-----
> From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of David 
> Chang
> Sent: Monday, October 05, 2015 6:54 PM
> To: FreeTDS Development Group
> Subject: Re: [freetds] Using FreeTDS 0.95 INSERTS fail above 66000 
> characters...unless 'dump file' is turned on
>
> Dave,
>
> I see you set the text size in the freetds.conf file to 1000000.  I would 
> confirm it using 'select @@textsize'.
>
> DC
>
> On 10/5/2015 2:07 PM, Jacopille, David wrote:
>> Looking for suggestions or a troubleshooting approach on this 100%
>> reproducible issue where FreeTDS logging seems to be the only variable.
>>
>> Since the issue goes away as soon as I turn on freetds logging I can¹t
>> look at the logs to find the problem.
>>
>> ENVIRONMENT
>>          SQL Server 2012
>>          OS X 10.9.5
>>          FreeTDS 0.95.20
>>          DBD::Sybase 1.15
>>          TDSVER 7.0  or 7.1 (same symptoms)
>>          Perl 5.16.3
>>          FreeTDS text size = 1000000
>>
>> Testing large string INSERTS to a table with a single, nvarchar(max),
>> column
>>
>>
>> WHAT WORKS
>> A. With freetds logging on (via 'dump file = /tmp/freetds.log¹) INSERT
>> values from 0-500,000 characters are successful.  Decreasing
>> reliability above 500,000 characters.
>>
>> B. INSERT statements with values of 0 thru 66,000 characters always
>> work, regardless of freetds logging on/off
>>
>> C. SELECT work correctly for all values 0 thru one million characters,
>> regardless of freetds logging on/off
>>
>>
>> DOES NOT WORK
>> If freetds logging is turned off the following fails consistently:
>>
>> A. 67,000 character INSERT              stalls at INSERT
>>
>> B. 100,000+ character INSERT            doesn¹t stall but doesn¹t INSERT
>>
>> C. Stored Procedures also experiencing failures in same character
>> ranges
>>
>>
>> FREETDS.CONF
>>
>> # In the failure configuration it looks like this.
>> # The ³DOES NOT WORK² symptoms go away if the last two lines are
>> uncommented.
>>
>> [global]
>>          client charset = UTF-8
>>          text size = 1000000
>>
>> [bossqld4]
>>          host = bossqld4.mfs.com
>>          port = 1433
>>          tds version = 7.0
>>          #dump file = /tmp/freetds.log
>>          #dump file append = no
>>
>>
>> PERL SCRIPT REPRODUCING ISSUE (WITH NO FREETDS LOGGING)
>>
>> use DBI qw(:sql_types);
>> use DBD::Sybase;
>> my ($sql, $dbh, $ref, $largetext);
>>
>> $dbh = DBI->connect( 'DBI:Sybase:server=bossqld4', ŒCORP\user',
>> Œpassword', { PrintError => 0 } ) or die; $dbh->do('use
>> testdatabase');
>>
>> $largetext = 'x' x 100000; # one hundred thousand characters
>>
>> $dbh->do(qq|INSERT into TESTTABLE (maxtext) values
>> (N\'$largetext\')|);
>>
>> # SELECT not affected by FreeTDS logging on/off # SELECT always
>> successful on all values tested up to one million characters (and
>> probably much larger, but untested).
>>
>> $sql = q|SELECT TOP 1 [maxtext] FROM TESTTABLE|; $ref =
>> $dbh->selectrow_arrayref($sql); print 'Length of value: '. (length
>> $$ref[0]) ."\n";
>>
>> $dbh->disconnect();
>>
>>
>> Thank you,
>> Dave Jacopille
>> Boston
>>
>> MFS Email system made the following annotation
>> ----------------------------------------------------------------------
>> -----------------------------------------------------------------
>> This email communication and any attachments may contain proprietary, 
>> confidential, or privileged information. If you are not the intended 
>> recipient, you are hereby notified that you have received this email in 
>> error and that any review, disclosure, dissemination, distribution or 
>> copying of it or its contents is prohibited. The sender does not waive 
>> confidentiality or any privilege by mistransmission. If you have received 
>> this email in error, please notify the sender immediately, delete this 
>> email, and destroy all copies and any attachments.
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> MFS Email system made the following annotation
> ---------------------------------------------------------------------------------------------------------------------------------------
> This email communication and any attachments may contain proprietary, 
> confidential, or privileged information. If you are not the intended 
> recipient, you are hereby notified that you have received this email in 
> error and that any review, disclosure, dissemination, distribution or 
> copying of it or its contents is prohibited. The sender does not waive 
> confidentiality or any privilege by mistransmission. If you have received 
> this email in error, please notify the sender immediately, delete this 
> email, and destroy all copies and any attachments.
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds