Way down in the FM documentation, they say the odbc connection is for MySQL 5.5 databases, and the version of MySQL on my webhost is 5.1. Is that the problem? The database version, seems to me, would be isolated from FM by the ODBC driver.
To test this, I installed MySQL 5.5.4 on a local linux pc I have here. I set up connections to my test database with the MySQL ODBC 5.3 Unicode Driver. Same deal, in Filemaker, Convert an Existing file lets me browse to my ODBC data connections, but still wants a user name and password for the ODBC data source, even though that is set up in Window's ODBC Data Source Administer. This test database blasts right into MS Access through its External Data>ODBC Database>Machine Data Source steps.
So what does FileMaker want?
I found this page in help and it doesn't look anything like the screens I'm seeing. Different version? http://help.filemaker.com/app/answers/detail/a_id/6528/kw/connecting%20to%20mysql
you have to enter the same user and password as in the odbc settings. the filemaker file does not have access to the credentials stored in the odbc settings, so you have to re-enter both. but you can store user and password in the "external data source" for all accounts within the filemaker file.
As I said in the original post, entering the odbc username and password doesn't work. Tried this with my MySQL 5.1, ODBC 5.3 ANSI connects and my MySQL 5.5 ODBC 5.3 Unicode connects. All I get back is "The account and password you entered cannot be used to access this file. Please try again."
Still haven't figured this out.
I tried a different approach. I went File>Manage>External Data Sources>New, clicked the OBDC radio button > DSN Specify, and I get a window empty of any of the ODBC data sources that I have set up, seen in the window lower right. Is that a clue?
For further testing, I read at http://help.filemaker.com/app/answers/detail/a_id/6420 that the driver should be MySQL/ODBC 5.25 64 bit. Seems like rolling back to that driver negates security and other enhancements, but I got the driver from http://downloads.mysql.com/archives/c-odbc/ and installed it, set up a machine data source to my local install of MySQL 5.5 with the 5.2.5 driver, but still have a blank ODBC data sources window in FileMaker. What shall I try now? Think it will make any difference if I run MySQL 5.6? Is 5.6 even available for linux?
at http://help.filemaker.com/app/answers/detail/a_id/6420 Now I'm reading that the MySQL version is 5.6, while earlier in FM docs I read it was MySQL 5.5. Which is it? Is the MySQL version going to make much difference?
To eliminate any potential problems caused by the versions, I installed MySQL 5.624 Community on localhost which is Windows 7 64 bit. Used the older 5.2.5 MySQL connector to make a machine data source with Windows ODBC Data Source Administrator. The new data source tests good with other Windows applications. However, with File Maker Pro 13, I still get the same behavior as mentioned above.
FileMaker tech support suggested that I try installing the 32 bit version of the MySql ODBC driver 5.25 because FM is a 32 bit application. The closest I could find still available was 32 bit 5.27. I installed it and it wanted to uninstall my old 5.2.5 versions. Now I have 5.2 data sources that don't work any more. When I try to remove them, I get "The specified DSN contains an architecture mismatch between the Driver and Application" from Windows. When I try to create new ODBC connections, Add doesn't show this new 5.2.7 driver.
Working with FileMaker technical support, I learned that the 32 bit ODBC drivers data sources can't be added or configured with Windows 64bit ODBC Data Administrator, but there is a 32 bit ODBC Administrator at C:/Windows/SysWOW64/odbcad32.exe that looks and acts just like the 64 bit Administrator. I ran some of the ODBC instatllation msi files to remove conflicting ODBC drivers, then reinstalled 5.3 64 bit and 5.2.4 32 bit, the one that FM documentation mentions. Then I used odbcad32.exe. to configure 32 bit 5.2.4 driver, and import records from a small test MySQL 5.6 database. And I was also able to make a connection to a large MySQL 5.1.56 database I have on the web, so perhaps the MySQL version is not as critical as the ODBC driver version.
The 32 bit driver connections show up in DS Administrator as MYSQL OBDC 5.2a Driver, can coexist with my 5.3 64 bit driver connections, but can be configured only with odbcad32.exe.
I build a new FM database and table to receive the data import from one of my big MySQL 5.1 tables on the web. I also wasn't sure how I would handle the UNIX timestamp date in MySQL table. During the FM import, I lifted a SQL statement from one of my stored queries:
Flow.SITE, FROM_UNIXTIME(Flow.DATE, '%Y %m %d') AS Date, (Flow.QD)
SITE = 'YRPW'
AND YEAR(FROM_UNIXTIME(DATE)) > '2014'
ORDER BY DATE
and quickly got 105 records from a much larger table, and dates look good. This works better than MS Access because MS Access doesn't have FROM_UNIXTIME in its SQL.