Bug #5002 mysqldump where option not working with string criteria

• Submitted: 11 Aug 2004 20:46
• Modified: 13 Aug 2004 18:53
• Reporter: Dennis Mitchell
• Status: Not a Bug
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S2 (Serious)
• Version: 4.1.3b-beta
• OS: Windows (Windows Server 2003)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[11 Aug 2004 20:46] Dennis Mitchell

Description:
I created a Windows Script Host file to launch mysqldump with various command-line parameters.  The parameters work perfectly with the 4.0.20a version of mysqldump on Windows Server 2003; however, the same script fails to work with the 4.1.3b-beta version of mysqldump.

I attempted to isolate the problem by systematically manipulating each of my mysqldump parameters.  It appears as though the 4.1.3b-beta mysqldump cannot process the where parameter (whether the parameter is supplied as --where or -w) when the SQL where clause includes string criteria (e.g., "-wname BETWEEN 'ADAM' AND 'EVE'").  From the MS-DOS prompt, a where clause with string criteria generates an "illegal mix of collations" (latin1_swedish_ci, implicit vs. utf_general_ci, coercible).  The where clause appears to work with numeric criteria.

As a workaround, I renamed the 4.1.3b-beta mysqldump and copied an old 4.0.20a mysqldump into the mysql\bin directory.  The 4.0.20a mysqldump appears to be working fine with my 4.1.3b-beta database (multiple-tablespace INNODB tables). 

How to repeat:
Run the 4.1.3b-beta version of mysqldump with a where clause specifying string criteria.  

Examples:
mysqldump "-wname BETWEEN 'ADAM' AND 'EVE'" --tab=c:\ database1 table1
mysqldump "-wname > 'ADAM'" --tab=c:\ database1 table1
mysqldump "-wname <>'EVE'" --tab=c:\ database1 table1

Suggested fix:
Since the mysqldump where parameter works fine in 4.0.20a, but not in 4.1.3b-beta, one should compare the relevant sections of source code from these files.

[13 Aug 2004 18:53] Dean Ellis

This is expected behavior when character sets conflict; the character set mysqldump is using (which you can change with --default-character-set, or with explicit character set qualifiers on the string constants) conflicts with the character set used by the table(s).

[18 Aug 2004 17:41] Dennis Mitchell

Thank you for looking into my reported bug.  As you suggested, 4.1.3b-beta mysqldump works fine with the option "--default-character-set=latin1".  Perhaps, it would be worthwhile to include a note in the mysqldump documentation that the initial default character set for 4.1.3b-beta mysqldump can conflict with the initial default character for 4.1.3b-beta INNODB tables (or perhaps only with multiple tablespaces?).  I didn't make any configuration changes to either program, other than specifying multiple tablespaces in my.ini.