When I try to import one of my table into the relationship graph from mysql, weird things happen. I get a message stating "sql statement is too long" no matter what I do. I can't add records nor even see the current data.
This happens on a couple of my tables, but not on other ones in the same database. I think it might be because some tables are larger than others, but I'm not sure.
This happens in Filemaker 16 on Windows. Anyone else have this issue?
Could you possibly post the SQL query and/or screenshot the exact error message?
(in addition to what TSGal asks)
beverly
Hi guys,
I'm using the ODBC connector 5.3 for windows from here:
MySQL :: Download Connector/ODBC
One table that doesn't work has 628 fields and 13565 records. Another one that doesn't work is 697 fields and 0 records.
The tables from the same database that do work: 242 fields, 1 record. 220 fields, 405 records.
That's why I think it might be the number of fields that are making the sql fail.
I don't know what the actual sql call is because it's an ess connection (live link to table in relationship graph). The error message is an error dialog box that simply says: "SQL statement is too long". That's all.
Notice how the records are there, but no fields are populated. When I click on an editable field, I get this.
I should clarify that the connection is using the Windows driver in Filemaker Server - the error message happens on both mac and windows Filemaker Pro. (Only with those two longer tables - the other 20+ shorter tables seem fine.)
fubizitch:
The query limit for MySQL is 8,192 characters. If the query exceeds 8,192 characters, then the error message "SQL statement is too long" is displayed.
As a workaround, reduce your query to limit the number of fields and/or records.
TSGal
FileMaker, Inc.
Thank you for the update that you are using ESS. There should be a log of the queries (if you set up your ODBC to log them). The logs may tell you what query is being made by FileMaker.
For queries (ESS or your own SQL statements) that run into problems such as you have (too many columns, rows or length of data in columns), I tend to recommend that pre-queries be set up as Views (on the MySQL server). These can narrow down what you get with the ESS and that can be a real advantage!
If you are admin on the MySQL, then you may be able to set these up. If not, you'll need to contact DB admin/IT to do this for you.
MySQL :: MySQL 5.7 Reference Manual :: 23.5 Using Views
Another tip (whether you use Views or not), is to
1. enter find mode (from another layout not tied to ESS tables)
2. go to a layout you need to search (this should be a form view layout for your data)
3. narrow your found set
4. perform find & enter browse mode
5. then and only then go to a list or table view if your found set is not so large.
This may or may not help you as you import from ESS into your table. Just some suggestions that have helped me in the past.
beverly
Thanks for the info and for possible workarounds. I think I should state our goal:
I'm trying to move some of our Filemaker tables over to MySQL. The reasons are for portability (syncing with several other buildings), prevention of record locking (since Filemaker records are automatically placed into a LOCKED state as soon as a user begins typing, which is hardly ever the desired behavior), high availability, and better interoperability with web sites.
We have many, many layouts and reports that already access these large tables, so would be nice to more easily switch over to an SQL-based back-end. Going forward, we'll be making calls from PHP directly and from FileMaker, and of course our PHP calls would be optimized, but we don't want to break our current layouts and reports.
An 8,192 character SQL statement call limit totally explains why our larger tables are failing even when few or no records exist. Why does this limit exist? It isn't specified in any of the documentation, and we've been preparing this move for months. Is there a setting to maybe increase this limit to something higher?
fubizitch:
These are the limits on the drivers that FileMaker can access:
MySQL for Windows: 8,192 characters
Oracle for Windows: 8,000 characters
SQLServer for Windows: 524,288 characters
Actual Technologies MySQL for Mac: 8,192 characters
Actual Technologies Oracle for Mac: 8,000 characters
Actual Technologies SQLServer for Mac: 8,000 characters
These values cannot be changed.
TSGal
FileMaker, Inc.
if this is not already in a KB, it would be handy to have them there.
thank you!
beverly
OK, thanks for the info. Looks like we might have to start programming completely outside of Filemaker and perhaps use some way of syncing the databases together.
ESS would be very useful if it didn't have these limitations!
Yah, we could try that - but I don't think the fields will line up the same if we were to do that, right? From my experimentation, the names are irrelevant when switching from an FM table to a MySQL one, so there have to be the same number of fields / order of creation for an easy switchover...
If you can query the MySQL directly, you can get the "field names" (columns) should that be your aim.
An export as csv (from MySQL) would give you the columns as well and be able to "map" to your FileMaker fields upon import there.
If your FileMaker field names are the same as the MySQL column names than 'matching' upon import will align the correct fields and columns.
If your FileMaker field names are not the same, then there is more work to do. Typically I use a temp table (as many as you need) to import from SQL and then change the field names (in the temp table in FileMaker) after import just for easier mapping to your fields.
Are you able to export from MySQL as .csv? and if so, does that import into FileMaker?
I don't mean to step into your problem with the limitations of the drivers. I just have run into these kinds of things. I also work with MS SQL and MySQL in my business (as a SQL db admin). There may be a few options for the SQL-FileMaker bridge. Let me know if you want to close the conversation on the reported issue.
beverly
Not sure this applies but I have always been able to get around the "too long" error by putting the full query in a text field and then set a variable to the text field and using the calculate query option to call the variable.
Another thing that works is query everything with an * and then only import what you want by de-selecting unwanted fields in the second set-up window
Great suggestions, and thank you Beverly for the various ideas that you've given.
What I wanted was just to replace a few tables in the relationship graph with their ESS MySQL equivalents. I've already written some PHP that converts a DDR into MySQL tables, and I was hoping it would be a quicker switchover. Sounds like I'm going to have to go layout by layout (and script by script probably) to get things working with MySQL
OK, So you want the layout to point to the ESS instead of local (and in all your scripts, etc.)
Hmm, not so easy.
Beverly
Hello fubizitch,
Sorry, i re-red multiple times this thread but because of my bad english, i am not sure to understand :
Is it an issue with Import records script step using MySQL driver OR accessing a layout based on a ESS TO ?
8192 chars sounds like very low if we are talking about the amount of data returned by the query / records to display thru the layout, but sounds more correct if we are talking about the length of the query itself.
In the first case, it would be fired by the number of records or chars in the fields, whereas in the second case, it would be fired by the number of fields or query complexity.
Sorry to be so perplexed. I never experienced a such issue with large ESS MySQL databases where a huge amount of records were displayed in the layout with, for sure, more that 8192 chars ???
Thank for your help.
Hi Fred,
This is an issue with ESS MySQL (not importing). The limit is the SQL statement length, so depending on the number of fields (no records) in the system.
The query itself fails, so you get no record data back. I am not aware of any limit with the number of records nor the size of data in the fields when returning results.
fubizitch:
Thank you for your post.
What is the driver being used to access the MySQL database?
Can you describe the tables the don't work? How many fields? How many records?
TSGal
FileMaker, Inc.