MySQL Bugs: #32058: Truncated data using FileMaker Pro 9 and MySQL ODBC 3.51 Driver

Bug #32058	Truncated data using FileMaker Pro 9 and MySQL ODBC 3.51 Driver
Submitted:	2 Nov 2007 17:06	Modified:	17 Mar 2017 10:26
Reporter:	Theo Hultberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.21	OS:	MacOS (Mac OS X 10.4.10 Intel)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	odbc51_postga

Description:
Using the MySQL ODBC 3.51 Driver (package install) and manually configuring /Library/ODBC/odbc.ini to create a DSN (because the driver crashes ODBC Administrator, see bug #32057).

In FileMaker Pro 9 when you set up a MySQL database as an external data source and edit text data, the text is truncated to the first character. This must be related to the MySQL ODBC driver, because everything works fine using Actual Technologies ODBC driver for Open Source databases.

How to repeat:
1. Install MySQL ODBC 3.51 Driver (package install)
2. Manually configure /Library/ODBC/odbc.ini (or set it up in ODBC Administrator if you're lucky enough that the driver setup won't crash the application)
3. In FileMaker Pro 9 create a new database
4. Go to File > Manage > External data sources
5. Click New
6. Select ODBC
7. Click specify and select the DSN created in step 2
8. Specify name and password for the DSN
9. Click OK and OK in the next window as well
10. Select File > Manage > Database
11. Select the Relationships tab
12. Click the add table button (it's the rightmost button in the buttonbar below the relationship graph, it is a box with a green plus sign in it's upper left corner)
13. Select the DSN as the Data source and select a table that has columns with text data
14. Click OK, then OK to the next window as well
15. Select View > Go to Layout and select the table you added in step 13
16. Change a field containing text to something longer than one character
17. When the field loses focus the text is truncated to one character

Many thanks for reporting a bug.

Please, could you add the trace file here.

I have this same problem on my OSX.4 Mac system, but not the Windows system running via Parallels.  Based on this the problem would seem to lie in the Mac 3.51 driver specifically

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".

This bug was automatically suspended for lack of input. What input is required? The bug is still there (both Mac OS X 10.4 Intel and 10.5 Intel).

I also changed the severity to Critical, as the bug makes the MySQL ODBC driver completely unusable with Filemaker.

We still need the trace for analyzing this problem.

Could you please describe what it is that you want and what I can do to obtain it? I don't know what a "trace file" is.

ODBC trace file

Attachment: odbctrace.txt (text/plain), 87.93 KiB.

OK, I've added what I think you mean by "trace file", I hope it helps.

If the file is not what you were asking fore, please describe in more detail what it is that you need.

Theo, there is nothing alarming in your trace file... I think we will need table structures for tables in question and description of SP's used here.

The table I used when creating the trace file looks like this (SHOW CREATE TABLE `page`):

CREATE TABLE `page` (
  `pageId` mediumint(9) NOT NULL,
  `pageName` varchar(100) default NULL,
  PRIMARY KEY  (`pageId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And that's the only table in the database. No SP's (Filemaker doesn't support calling SP's).

I think I've mentioned this before, but just to be clear: I've the exact same setup but using Actual Technologies ODBC driver, and that works fine. The only difference between the two sessions was the ODBC driver.

Thanks for looking into this.

Theo, no need to thank us, thank you for using MySQL. I have asked my co-worker Sveta to proceed with this report as she has Mac.

Thank you for the report.

Verified as described. In general log file I have:

UPDATE MyDSN60.page SET pageName='s' WHERE pageId=2

I tried to add record like 'something new'

odbc trace file

Attachment: odbc.trace (application/octet-stream, text), 76.71 KiB.

I have a very large MYSQL data base(9gb) where I can hook into via your ODBC but as with Theo it truncates all tables and will skip a hundred or so tables all together when using filemaker pro 9. Is this because I am using a trial version of Filemaker? The filemaker team could not give me an answer...

Chris R

Hi Chris. This has been hard issue for us as Sveta is not an ODBC expert and rest of us, who claim to be ;), do not work on Mac and have no clue as of what FileMaker is... Anyway, I promised to take a look into this trace and I will today. Hopefully I'll know more then but I'm not overly optimistic as it's always hard to analyze black-box.

Sorry I should have said that I am running the Mysql on Windows Server 2003 R2  And tring to connect via Filemaker and your odbc. Mysql is listed as a partner of Filemaker...
FileMaker 
“FileMaker ( www.filemaker.com/mysql )  is excited to join the MySQL MECA partner program.  Connecting FileMaker and MySQL database technologies will allow developers to create a huge variety of working solutions amazingly fast compared to traditional programming tools." 
-- Ryan Rosenberg, vice president, Marketing and Services, FileMaker 

Chris R.

Works well in Windows

Per FileMaker's technical documentation, the MySQL ODBC connector is not currently supported for Mac OS X when talking to MySQL databases.  Instead, you must use the driver from Actual Technologies.  See: http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=6510

Additionally, FileMaker currently only supports working with MySQL 5.0 Community Edition via ODBC.  See: http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=6421

Hopefully, both of these restrictions will be lifted as the relationship between FileMaker and MySQL matures and bugs are fixed.

Eric, are you suggesting that this is not a bug because FileMaker doesn't support the MySQL connector? I would say it's the other way around: FileMaker doesn't support the MySQL connector (partly) because of this bug. The bug has nothing to do with FileMaker as such, as you can see above it's been more or less confirmed to be a bug in the MySQL connector.

The sooner it can be fixed the sooner FileMaker can add it to the list of supported connectors.

I take my hat off to you Theo for your dogged persistence on this matter. If the MySQL worked at all on Mac OS X (see bug ref 32057 which shows that it does not) then maybe Filemaker would support it.

i have the same issue with filemaker 9 os 10.5.4 and i am even using a local filemaker database and mysql database!

d

It sounds like it would be a pretty simple fix to get this sorted out, even though it's "not supported"...

We tried using Actual Technologies' connector, but they charge ridiculous amounts to make multiple connections. We really need this MySQL driver instead, but for the last 2 years have only been able to use it for retrieving information because of this bug.

Please, can the bug be fixed?

Posted by developer:
 
Everything works well with Filemaker 15 and Connector/ODBC 5.3.7.
However, the ANSI version of the driver should be used as FileMaker does not seem to support SQLWCHAR data well, so the strings were truncated indeed.
Closing the bug.