I'm using COBOL via Visual COBOL 2013 4.0 to access a MySQL Community 8.0 database. MySQL for Visual Studio 1.2.8, using OpenESQL to connect, Managed COBOL.
The core of my database is a 3-record hierarchy, joined by indexes, the bottom table of which holds variable-length line data, defined as TEXT. My maximum text line is 7060 bytes. I have an application that processes lines of any length perfectly happily through this structure, generating a converted output that clearly 'sees' and processes every byte in every row. However, a parallel process that dumps out the raw data for comparison is losing rows from the cursor where the TEXT length in the row is greater than 1024 bytes.
from program_node pnode
inner join element ele on ele.ele_id = pnode.ele_id
inner join property prop on prop.ele_id = ele.ele_id
inner join property_line pline on pline.ele_id = prop.ele_id and pline.prop_id = prop.prop_id
where program_name = :ws-program-name
order by hierarchy_id,prop.prop_id,prop_line_seq
***** without this 'left', or when changing the 'left' count to 1025 or greater, rows of length > 1024 are completely omitted, with no sqlcode or other indication of an issue.
All the joins and sequences are indexed.
The only solution I have been able to find is to split the query into parts which I knew worked elsewhere, ie get the first 2/3 tables in an outer cursor, and have a simple cursor on the lowest level(s) for each row from the first.
The behaviour suggests there might be a size limit on an intermediate record, such as sortwork, although I tried removing the 'Order By', which removed a filesort according to 'explain', and this made no difference.