Invalid Field Name on Import from mySQL ODBC Connection: Error Code 0X80040204

Question

Receiving Error Code 0X80040204: "an Invalid field name was used in query string" when attempting to view attribute table of table imported from Database connection.

No field names start with numbers or spaces or special characters. Field names only have letters, numbers, and underscores

When we limit the number of fields from ~45 to 5 it works no problem.

Any ideas?

mvolz47 · Answer

Neil:

Had you ever tried this type of connection to MySQL in ArcMap?

I have been using an OLEDB (ODBC) connection to MySQL in ArcMap for years, but the same setup with a 64-bit connection is throwing errors in Pro when trying to access the exact same database tables.

NeilHubbert · Answer

Hi @LanceCole

The problem (we think) has to do with one of our fields using the TIME datatype. Once those fields were removed, I was able to import the data fine. When those fields had their datatype changed to VARCHAR it also worked. The TIME format in our MySQL instance is the standard HH:MM:SS and the variable naming convention doesn't appear to be the issue either. Is there some nuance to converting the MySQL TIME datatype to something ESRI can use?

LanceCole · Answer

Is it possible one of your field names is a reserved word?  Each DBS has there own set of reserved words.

MySQL Reserved Words

ESRI File Geodatabase Reserved Words

MS SQL Reserved Words

ORACLE Reserved Words

PostgreSQL Reserved Words

MS Access Reserved Words

Also make sure none of your field names start with an underscore.  I was not aware of that one until recently.

You note 5 fields of ~45 work, try groups of fields until you get an error.  Then look for possible issues in that group.  Note, there may be more than one field that has an issue.  If you can post a list of the ~45 fields it, may be helpful for for someone to review them.