0

Mysql experts,

I have a Mysql job that runs perfectly when I run from the mysql workbench GUI.

Now I am trying to automate the job by running it from the command line. It is throwing an error when it gets to a replace function that I have: 

UPDATE  `MyTable`                
SET `Col1` = REPLACE (`Col1`, '˜', '');

So all I am trying to do is strip out some unwanted characters from these columns. The Error thrown is:

ERROR 1270 (HY000) at line 232: Illegal mix of collations (latin1_swedish_ci,IMP LICIT), (cp850_general_ci,COERCIBLE), (cp850_general_ci,COERCIBLE) for operation 'replace'

I'm not understaing why there is a "mix" of collations within one column in one table. How can I remedy this error to run through as it does in the GUI?

TIA! -Jake

EDIT: Thank you all for the replies! Spencer7593's solution of specifying the collation before the strings ( REPLACE(foo,_latin1'a','_latin1'b') ) was the quick workaround I was looking for. The command line runs smoothly now.

Much appreciated Spencer :)

Jake K.
  • 21
  • 1
  • 4
  • every field in mysql can have a different collation, plus a default collation on the table, plus a default collation on your client connection, blah blah blah. for whatever reason, one or both sides of your replace operation and getting converted to a different collation. e.g pull cp850 out of the db, do replace as latin1, and then try to stuff that latin1 back into a cp850 field, and failing. no idea why, but that's what's happening. – Marc B Mar 07 '16 at 21:49
  • As a workaround to the error, I would try prefixing the string literals with **`_latin1`**. The error message makes it look like the column is latin1 (from the column def) , and the string literals are cp850 (from the client). e.g. **`REPLACE(foo,_latin1'a','_latin1'b')`**. That's how I've worked around problems like this. With that special tilde character, if the encoding for that character is different, that may not do what you want it to do. But that's how I workaround that error with the different collation of the literals. – spencer7593 Mar 07 '16 at 21:58
  • MySQL Reference for charactersets of literals: [http://dev.mysql.com/doc/refman/5.7/en/charset-literal.html]. For handling characters that have different encodings in latin1 and cp850, you probably want to use a `CONVERT` function on the literal value. **`CONVERT 'a' USING latin1`** rather than **`_latin1'a'`**. – spencer7593 Mar 07 '16 at 22:21

0 Answers0