Creating new record in mysql is very slow

[fishtech]

I have fm10 server connected via odbc to a mysql source. There are a dozen mysql tables with shadow tables in FileMaker.

11 of the tables are read only to the filemaker/odbc account.

But 1 table is read/write. It has only 4 fields and 10 records. But when I click "new record" there is a delay of around 30 seconds before the new record shows up. The fields are:

CreationTimestamp - auto-entered by mysql server

ModificationTimestamp - auto-entered by mysql server

RecordID - auto-entered by mysql server

UserName - text field

What could be the reason for this? Does FM not work well with values that are auto-entered by MySQL?

Thanks,

ft.

Thanks,

ft.

[Garry Claridge]

What happens if you do a "Refresh Window" from the "Records" menu? Do this straightaway after the "New Record" action.

This is to check if it is a problem with FM just not re-displaying quickly. If that is the case you may want to create a new record using a Script containing the "Refresh Window" step.

All the best.

Garry

[Trey Caliva]

You'll actually be better served by running a Commit on the records following the New Record creation.

[fishtech]

Thanks for your replies & sorry for delay in response.

I had to work around my problem above using php.

Regards,

ft.

[fishtech]

Another project has landed and I need to export data directly from FileMaker Pro 10 to a new MySQL table that has been created on the same MySQL server as above. So I have to revisit this problem...

My FileMaker files run on a Filemaker Server 10 box running Mac OSX 10.4 and ODBC Open Source Database Drivers by Actual Technologies (10 connection version).

The SQL files are running on an XServe on the same LAN. The SQL admin works remotely and has no experience of FileMaker... he believes things are good at the SQL end.

I have a shadow table occurrence of the MySQL table in my interface file. The MySQL table simply has 5 fields which show as follows in FileMaker:

datemod - Timestamp - Not indexed and indexing cannot be turned on automatically

entity - Text - Not indexed and indexing cannot be turned on automatically, Allow user to override validation, Maximum number of characters = 50

in_db - Number - Not indexed and indexing cannot be turned on automatically, Only allow values of type: “Numeric Only”

sd_status - Text - Not indexed and indexing cannot be turned on automatically, Allow user to override validation, Maximum number of characters = 100

sd_update_id - Number - Not indexed and indexing cannot be turned on automatically, Prevent data that is automatically entered from being

changed., Only allow values of type: “Numeric Only”

If I create a single new record in the MySQL table within filemaker, there is a delay of about 30 seconds (with a spinning pinwheel) as soon as I click out of the record.

If I use Commit script step or Refresh Window it is the same result.

Any ideas what could be causing this delay?

Thanks,

ft.

[fishtech]

So the solution was...

Correcting dns setup on the mysql server.

The mysql server has 2 nics, one in the demilitarized zone and one in the lan. The nic in the dmz did not have a dns server entry for our internal dns server, only external dns servers.

I don't know enough about mysql to know why this matters and how it works... in my case all my FMP <> FMP SERVER <> ODBC<> MYSQL connections are made with IP addresses explicitly to avoid dns issues. But the problem still occurred.

Interestingly this problem only manifested when writing mysql records - reading mysql records was fine, although there was an initial delay of 30 secs when making the first ODBC connection of the day (this delay is now gone also).

Perhaps mysql server itself requires correct reverse dns setup, although other mysql services on the mysql server are running fine... it was just the link with odbc that cause the problem. hmmm...

Hope this helps someone sometime.

ft.

Edited November 18, 2009 by Guest