With one MySQLCommunityServer connection and one RecordSet open, an Update via SQLExecute to any field value of any one of those records fails silently. Error is false and the MySQL log shows no warning or error. The expected behavior is that the field is updated with a new value.
On physical servers, virtual machines and VPSs with AWS and Digital Ocean, the field value is updated using the very same Xojo executable and nearly identical MySQL configurations. On the GoDaddy VPS, weve tried MySQL 5.6.x and 5.7.x on Ubuntu Server 15.10 and 16.04.1 with the same result, a silent failure.
One workaround is to Close the RecordSet before the Update via SQLExecute.
Has anyone else experienced this unusual behavior with MySQL? Any thoughts on what the cause might be, how to address it or why it is unique to a GoDaddy VPS?
Could it be that AutoCommit is off?
Thanks @David Cox but AutoCommit is set the same on each MySQL Server. We’re drawing at straws too. It’s puzzling.
Hm…and no database errors from the SQLExecute?
Frederick, Can you turn mysql logging on for a bit so you can see the actual sql statement that mysql is executing just to make sure that it’s exactly what you’re expecting?
It fails silently. Very bad. Fortunately, we caught it during development and testing, prior to deployment.
Logging shows the expected SQL statement. Everything indicates it should work, as it does on every other MySQL deployment, but not on that GoDaddy VPS.
The fact that it works when you close the recordset indicates, to me, that it might be locking the record. I agree it’s very weird behavior.
So is this happening on ALL tables or just a single table? If it’s a single table, what field types are you dealing with?
[quote=317154:@Bob Keeney]The fact that it works when you close the recordset indicates, to me, that it might be locking the record. I agree it’s very weird behavior.
So is this happening on ALL tables or just a single table? If it’s a single table, what field types are you dealing with?[/quote]
Weird indeed. All fields of all tables. No indication of a record lock. No indication of anything. Truly silent. Multiple developers and DB admins are unable to duplicate it elsewhere or find an error, warning, etc.