470,580 Members | 2,355 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,580 developers. It's quick & easy.

bizarre error trying to add UUID to a new table column

mysql> update student set uuid = concat(UUID(), '_',
'asdfasdfasdfasdf') where uuid is null or uuid = '' limit 1;
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE),
(latin1_swedish_ci,COERCIBLE) for operation 'concat'
I have no idea what any of this means, what on earth did I do wrong???

Thanx
Phil

Mar 15 '06 #1
5 2489
UPDATE:

mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
SUBSTRING(GROUP_CONCAT(SUBSTRING('abcdefghijklmnop qrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
round(rand() * 62) + 1, 1) SEPARATOR ''), 1, 16))) WHERE uuid IS NULL
OR uuid = '' LIMIT 1;
ERROR 1111 (HY000): Invalid use of group function
mysql>

Now I can't use GROUP_CONCAT() either!

Phil

Mar 16 '06 #2

ph**************@gmail.com wrote:
UPDATE:

mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
SUBSTRING(GROUP_CONCAT(SUBSTRING('abcdefghijklmnop qrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
round(rand() * 62) + 1, 1) SEPARATOR ''), 1, 16))) WHERE uuid IS NULL
OR uuid = '' LIMIT 1;
ERROR 1111 (HY000): Invalid use of group function
mysql>

Now I can't use GROUP_CONCAT() either!

Phil


Ok I guessed and came up with a horrible hack:

$alphaNum = 'abcdefghijklmnopqrstuvwxyz';
$alphaNum .= strtoupper($alphaNum) . '0123456789';
$evilSubstring = "concat(UUID(), concat('_',
SUBSTRING(GROUP_CONCAT(SUBSTRING('$alphaNum', round(rand() * " .
strlen($alphaNum) . ") + 1, 1) SEPARATOR ''), 1, 16)))";
$query = new MySQLQuery("UPDATE student SET uuid = (SELECT
$evilSubstring FROM student_ethnicity_interest_assoc) WHERE uuid IS
NULL OR uuid = '' LIMIT 1",
$dbAP->getDBConn()
);

Problem is, this will only work if student_ethnicity_interest_assoc has
16 or more rows :(

How can I use any table (except for student, can't use that one or get
an error) in the UPDATE's subselect that has 16 or more rows?

Phil

Mar 16 '06 #3
>mysql> update student set uuid = concat(UUID(), '_',
'asdfasdfasdfasdf') where uuid is null or uuid = '' limit 1;
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE),
(latin1_swedish_ci,COERCIBLE) for operation 'concat'
I have no idea what any of this means, what on earth did I do wrong???


You are in a twisty maze of character sets, all different.
I think there's some way to convert character sets, but the
most effective way for my purposes is to make sure that I pick
one character set, make it the DUH FAULT, and use nothing else.

It's telling you that UUID() returns utf8 but everything else is
latin1_swedish_ci.

Gordon L. Burditt
Mar 16 '06 #4
>UPDATE:

mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
SUBSTRING(GROUP_CONCAT(SUBSTRING('abcdefghijklmno pqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
round(rand() * 62) + 1, 1) SEPARATOR ''), 1, 16))) WHERE uuid IS NULL
OR uuid = '' LIMIT 1;
ERROR 1111 (HY000): Invalid use of group function
mysql>

Now I can't use GROUP_CONCAT() either!


It's my understanding that you can never use GROUP_CONCAT() (or, for
that matter, max(), min(), avg(), etc.) without GROUP BY.

Gordon L. Burditt
Mar 16 '06 #5

Gordon Burditt wrote:
mysql> update student set uuid = concat(UUID(), '_',
'asdfasdfasdfasdf') where uuid is null or uuid = '' limit 1;
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE),
(latin1_swedish_ci,COERCIBLE) for operation 'concat'
I have no idea what any of this means, what on earth did I do wrong???
You are in a twisty maze of character sets, all different.
I think there's some way to convert character sets, but the
most effective way for my purposes is to make sure that I pick
one character set, make it the DUH FAULT, and use nothing else.

It's telling you that UUID() returns utf8 but everything else is
latin1_swedish_ci.


Right, and I came up with a hacked MacGyver-like solution, but it's
horrifically ugly, and not very good.

$alphaNum = 'abcdefghijklmnopqrstuvwxyz';
$alphaNum .= strtoupper($alphaNum) . '0123456789';
$evilSubstring = "concat(UUID(), concat('_',
SUBSTRING(GROUP_CONCAT(SUBSTRING('$alphaNum', round(rand() * " .
strlen($alphaNum) . ") + 1, 1) SEPARATOR ''), 1, 16)))";
$query = new MySQLQuery("UPDATE student SET uuid = (SELECT
$evilSubstring FROM student_ethnicity_interest_assoc) WHERE uuid IS
NULL OR uuid = '' LIMIT 1",
$dbAP->getDBConn()
);

But the problem is that the table in the UPDATE's subselect MUST have
16 or more rows else this entire transaction fails!

Phil

Gordon L. Burditt


Mar 16 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Spacen Jasset | last post: by
2 posts views Thread by Joel Whitehouse | last post: by
5 posts views Thread by Ron St-Pierre | last post: by
1 post views Thread by Xeth Waxman | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.