MySQL Administrator Backups: "Compatibility Mode", What Exactly is this doing?

Question

In Mysql Administrator, when doing backups, what exactly is "Compatibility Mode"?

I'm trying to bridge backups generated by webmin with the upload tool available inside mysql administrator. My data already has a couple of inconsistencies (ticks, commas, etc, I think) I just wont try to kink out (they might just reappear in the future anyways). These kinks generate errors when I try to restore out of my backups.

Now, if I generate backups from webmin, and then use MySQL administrator to restore them, they fail. But if I generate the backups using MySQL Administrator AND tick "Compatibility Mode", then head over to MySQL administrator (another instance) and restore... it works!

According to MySQL, "Compatibility Mode" is;

Compatibility mode creates backup files that are compatible with older versions of MySQL Administrator.

Webmin, on the other hand, gives me the following options for compatibility:

• ANSI
• MySQL 3.2.3
• MySQL 4.0
• PostgreSQL
• Oracle
• Microsoft SQL
• DB2
• MaxDB

Which would you say is a best fit? My data set is very large, so it would take quite some time to experiment one by one (specially whence thinking might beat brute-forcing it).

Edit: seems like it's doing ANSI, but i'm not 100% on it.

Answer 1

Compatibility mode - the mode that helps you create exports compabible with different versions of MYSQL or other databases.

You see, some versions of MySQL had different commands that were used in various versions. So what compatibility mode allows you to do is take a database and export the SQL to be compatible with another version of MySQL. Thus, you may want to upgrade your MySQL 3 server to 4 - this compatibility mode allows for the export your database or individual tables to create a SQL file that can import into a MySQL 4 version server (should work in 5 also).

I use webmin, also, and run MySQL 5. I use compatibility mode for MySQL 4.... I steer clear of any of the other ones, because I'm not running those other databases.

As far as the MySQL commands that were different between MySQL 3.x and 4.x, I believe there were changes in regards to how CURRENT_TIMESTAMP is translated from MySQL 3 to 4, and also MySQL 3 doesn't support charsets, according to this forum post here: http://www.phpbuilder.com/board/showthread.php?t=10330692