This has taken some detective work, but I’ve found the answer.
First, I checked the MySQL versions on both remote and local servers:
Remote: MySQL 5.1.38
Local: MariaDb 5.5.34
I had forgotten that the local server was running MariaDb but supposedly
it’s compatible with MySQL so this shouldn’t matter.
Then I wrote the SQL statements to a log file to check that they’re
identical, which they are.
The next step requires a short explanation. The program involves
filling in a very large form in which many fields are optional.
If a field isn’t filled in, its value in the SQL INSERT statement
is ‘’ (two single inverted commas with nothing between them).
In the databases, these fields are defined as INT(11). MySQL
interprets the ‘’ as zero and places a zero in the relevant
I copied the SQL statement, pasted it into MySQL-Front and ran
it on the MariaDb. Lo and behold, it was choking on the ‘’
and the insert failed.
I made a quick change to the program to make sure it wrote ‘0’ (zero)
instead of ‘’ and now everything works just fine – the statement
runs on both databases.
So, the problem is an incompatibility between MySQL and MariaDb. Also,
when the statement was run programmatically, MariaDb produced no
error message but did when the statement was run manually.
Something to be remembered if you’re using MariaDb – don’t assume
it’s 100% compatible.
[quote=321322:@Eli Ott]You must check for the error before committing:
if db.error then
[post a message with db.errormessage]
You’re right! Change made and thank you.