bizarre error trying to add UUID to a new table column

[phillip.s.powell]

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

[phillip.s.powell]

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

[phillip.s.powell]

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

[Gordon Burditt]

>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

[Gordon Burditt]

>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

[phillip.s.powell]

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