Odd MySQL behaviour

Databases
1

I have a program that works on two identical MySQL databases. One database is on a
remote server and the other is on the same computer on which the program runs.
The program first updates one table in both databases then inserts a new record
in another table in both. Identical SQL statements are used for both databases.

The problem is that while it carries out the updating of both databases
correctly, it inserts the new record in the remote database only but not on
the local one.

I repeat: the databases are identical and the SQL statements are identical.
The program has the proper permissions for inserting on both databases.
I can’t figure out why it’s failing on the local db (without any error
message from the SQL server). And yes, the statements are followed by
db.commit on both cases.

2

are you explictily checking for an error? or assuming because it didn’t “bomb” that there was no error?

3

Try to insert the record manually with the console client (and the same connection parameters) and see if you get some error there.

4

Yes, it checks for errors and displays a message if any occur - none reported.

I can insert records manually (using MySQL-Front) using exactly the same parameters and with no errors.

5

If you reverse the order - local first and then remote - any difference?
I’m thinking timing in the MySQL library here…

6

Without posting any code guesswork is all you’ll get.

7

Do you assume the SQL code to be identical or is the same SQL string you re-use for both SQL commands?

8

The code is:

if db.Connect then
  db.SQLExecute(s)
  db.Commit
  if db.error then
      [post a message with db.errormessage]
  end if
  db.Close
else
  [post an error message]
end if

where “s” is the SQL statement “INSERT INTO [table name] VALUES (” followed by the values of all the 40 fields to be inserted, in the correct order.

The exact same code is run twice, once for each database with the relevant database parameters (name, id, pw, etc) being the same for both databases, only the host name being changed. Before this code segment runs, db is declared as a new MySQLCommunityServer.

Makes no difference.

It’s the same string - no assumptions made!

9

You must check for the error before committing:

db.SQLExecute(s) if db.error then db.Rollback [post a message with db.errormessage] else db.Commit end if

10

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
db field.

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:

db.SQLExecute(s) if db.error then db.Rollback [post a message with db.errormessage] else db.Commit end if[/quote]

You’re right! Change made and thank you.