MySQL Bugs: #92239: Foxpro having troubles querying table with TEXT field in MySQL 8.0.

Bug #92239	Foxpro having troubles querying table with TEXT field in MySQL 8.0.
Submitted:	29 Aug 2018 23:54	Modified:	13 Oct 2018 12:40
Reporter:	John Peter Paredes	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	8.0.12	OS:	Windows (7 x64)
Assigned to:		CPU Architecture:	Other (64)

Description:
Im having issues with MySQL 8.0 and Visual Foxpro 9.0 SP2

before i had MySQL 5.0 and when i query a table that has a TEXT field i get
results.
Given : d_date is datetime d_remarks is TEXT

=sqlexec(myhandle, "Select d_date,d_remarks from SampleTable"
,"SampleCursor")

Now on MySQL 8.0 the same query above would return EMPTY result. i had to
remove d_remarks from the field list so
that it would return the actual rows. 

=sqlexec(myhandle, "Select d_date from SampleTable" ,"SampleCursor")

but i needed that field column :(

Our discussions are here
https://forums.mysql.com/read.php?10,668614,668614#msg-668614

How to repeat:
INSTALL VISUAL FOXPRO 9.0 SP2
INSTALL MySQL 8.0.12 x86 / x64
      using NATIVE PASSWORDS. (NO SHA)
      using InnoDB
INSTALL MySQL ODBC 8.0 ANSI Driver x86

in MySQL ;

CREATE A TABLE in MySQL with a TEXT field.

CREATE TABLE `dmcm` (
  `d_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `d_ref` int(11) NOT NULL default '0',
  `d_remarks` text NOT NULL,
  `d_ctrl` varchar(10) NOT NULL,
  `d_uid` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`d_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=latin1

POPULATE the table. add some data in the TEXT fields.

in FOXPRO :

nStringConn="Driver={MySQL ODBC 8.0 ANSI Driver}"+;
             ";Port=3306";
             ";Server="+nServer+;
             ";Database="+nDatabase+;
             ";Uid="+nUser+;
             ";Pwd="+nPassword+;
             ";OPTION=3"
	lnHandle = SQLSTRINGCONNECT(nStringConn)

lcSQLStmt="Select d_date,d_ref,d_remarks ";
	+" from dmcm "
=sqlexec(lnHandle,lcSQLStmt,"SourceDMCM")

browse SourceDMCM <--- this is empty because we included "d_remarks" in our sql query above. removing the memo field from the SELECT statement "d_remarks" fixes it. 

downgrade to MySQL 5.0
install MySQL ODBC 5.1 Driver
do the same table creation and population above
run the same foxpro codes and the issue does not appear.

Suggested fix:
N/A

Hi John,

The best way to find out what went wrong would be to try ODBC tracing.
You will need to enable odbc tracing BEFORE running FoxPro or your program.
Here are the steps to do it:

1. Run the 64-bit ODBC Data Source Administrator from 
   %WINDIR%\system32\odbcad32.exe
   Don't run it from Control Panel because it might be the 32-bit version.
2. Click the "Tracing" tab and tick the "Machine-Wide tracing for all user identities"
3. Specity the log file path (make sure the directory exists and you have write access to it)
4. Click "Start Tracing Now"
5. Run FoxPro and in minimum amount of steps try to reproduce the issue.
   (You might notice serious performance drop, this is normal for ODBC tracing)
6. When the issue is repeated go back to ODBC Data Source Administrator and click "Stop Tracing".
7. Attach the log file to this bug report (if it is bigger than 1MB it should be compressed with ZIP or some other program at your choice)

Thanks.

One more thing: when adding the file please make sure you select the privacy setting "Only developers will be able to see the file" to prevent your trace log information from being accessible to the public.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".