MySQL Bugs: #15690: mysqlimport tries to set options the server doesn't understand

Bug #15690	mysqlimport tries to set options the server doesn't understand
Submitted:	12 Dec 2005 17:53	Modified:	15 Aug 2006 3:42
Reporter:	Anders Henke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0.16 vs. 4.0.25	OS:	Linux (Linux)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
I would've tagged this as "MySQL Client Applications" instead of "MySQL Server", unluckily there's no option to do so; tagged as "serious", as this does affect the interopability between client and server.

Setup:
-a server running MySQL 4.0.25
-a client running with MySQL 5.0.16 client applications

The client runs mysqlimport in order to import a text file (LOAD DATA INFILE ...);
however, before importing the text file, (according to strace) mysqlimport tries to run

"set @@character_set_database=binary;" ... which the 4.0.25-based server 
of course can't handle, so mysqlimport quits using the error message

mysqlimport: Error: Unknown system variable 'character_set_database'

How to repeat:
See above.

I also tried to invoke mysqlimport with the "--force" option set, but mysqlimport
still can't be that easily forced to run as expected (and quits using the same error message).

Suggested fix:
-Note a warning on options the server doesn't understand.
-Only set options the server understands.

Do not use a 4.0 server with a 5.0 client application.

If the mysql server makes it necessary to run different queries per mysqld release, it's just forcing clients to create version-dependent kludges. I see that you don't want to
implement some "kludge for some outdated release", but complain to the ones forcing
you to do so :-)

The mysqlimport-utility is often used as a command-line interface for people who do
have to import some .csv-file without having to "learn" the correct sql syntax. 
It's not some high-tech tool fiddling around with the raw bits in an MyISAM index file,
doesn't create a database file directly within the file system or has some other
technical reason to be release-dependent from the mysqld's version. After all, it's only performing a single SQL query and an command-line interface to "load data".

Teaching "typical" mysqlimport-users to install and use two different releases at the same time because you haven't (or possibly won't ever) upgraded all mysql servers to the same release yet is close to impossible nor feasible.

"Don't bother a 5.x-based server with a 4.x client" also isn't a solution for a networked database system. Especially when migrating to the 5.x-release, it is very common to have a "mixed" setup for quite a long time and as there usually aren't any technical problems to force an upgrade for all clients/servers at the same time.
There are also incompatible changes between 4.x and 5.x which actually may prevent one from upgrading the server, while one or many clients still need to connect to different mysqld releases running on different hosts.

If you seriously think of this as a solution,please change the command line clients to refuse to connect to "unsupported" versions without using some --ignore-version or
--compat option and document why this is a problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9383

Pushed to 5.0.25

Noted in 5.0.25 changelog.

mysqlimport sends a set @@character_set_database=binary statement to
the server, but this is not understood by pre-4.1 servers. Now
mysqlimport encloses the statement within a /*!40101 ... */ comment
so that old servers will ignore it.

Merged into 5.1.12

Noted in 5.1.12 changelog.