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

Re: bizarre error trying to add UUID to a new table column

UPDATE:

mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
SUBSTRING(GROUP_CONCAT(SUBSTRING('abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
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

Re: bizarre error trying to add UUID to a new table column

phillip.s.powell@gmail.com wrote:
> UPDATE:
>
> mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
> SUBSTRING(GROUP_CONCAT(SUBSTRING('abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
> 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

Re: 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???

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

Re: bizarre error trying to add UUID to a new table column

>UPDATE:
>
>mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
>SUBSTRING(GROUP_CONCAT(SUBSTRING('abcdefghijklmnopqrstuvwxy zABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
>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

Re: bizarre error trying to add UUID to a new table column

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