bizarre error trying to add UUID to a new table column

[phillip.s.powell@gmail.com]

mysql> update student set uuid = concat(UUID(), '_',
'asdfasdfasdfas df') where uuid is null or uuid = '' limit 1;
ERROR 1270 (HY000): Illegal mix of collations
(utf8_general_c i,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@gmail.com]

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

UPDATE:

mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
SUBSTRING(GROUP _CONCAT(SUBSTRI NG('abcdefghijk lmnopqrstuvwxyz ABCDEFGHIJKLMNO PQRSTUVWXYZ0123 456789',
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@gmail.com]

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


phillip.s.powel l@gmail.com wrote:[color=blue]
> UPDATE:
>
> mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
> SUBSTRING(GROUP _CONCAT(SUBSTRI NG('abcdefghijk lmnopqrstuvwxyz ABCDEFGHIJKLMNO PQRSTUVWXYZ0123 456789',
> 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[/color]

Ok I guessed and came up with a horrible hack:

$alphaNum = 'abcdefghijklmn opqrstuvwxyz';
$alphaNum .= strtoupper($alp haNum) . '0123456789';
$evilSubstring = "concat(UUI D(), concat('_',
SUBSTRING(GROUP _CONCAT(SUBSTRI NG('$alphaNum', round(rand() * " .
strlen($alphaNu m) . ") + 1, 1) SEPARATOR ''), 1, 16)))";
$query = new MySQLQuery("UPD ATE student SET uuid = (SELECT
$evilSubstring FROM student_ethnici ty_interest_ass oc) WHERE uuid IS
NULL OR uuid = '' LIMIT 1",
$dbAP->getDBConn()
);

Problem is, this will only work if student_ethnici ty_interest_ass oc 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]

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

>mysql> update student set uuid = concat(UUID(), '_',[color=blue]
>'asdfasdfasdfa sdf') where uuid is null or uuid = '' limit 1;
>ERROR 1270 (HY000): Illegal mix of collations
>(utf8_general_ ci,COERCIBLE), (latin1_swedish _ci,COERCIBLE),
>(latin1_swedis h_ci,COERCIBLE) for operation 'concat'
>
>
>I have no idea what any of this means, what on earth did I do wrong???[/color]

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]

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

>UPDATE:[color=blue]
>
>mysql> UPDATE student SET uuid = concat(UUID(), concat('_',
>SUBSTRING(GROU P_CONCAT(SUBSTR ING('abcdefghij klmnopqrstuvwxy zABCDEFGHIJKLMN OPQRSTUVWXYZ012 3456789',
>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![/color]

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@gmail.com]

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


Gordon Burditt wrote:[color=blue][color=green]
> >mysql> update student set uuid = concat(UUID(), '_',
> >'asdfasdfasdfa sdf') where uuid is null or uuid = '' limit 1;
> >ERROR 1270 (HY000): Illegal mix of collations
> >(utf8_general_ ci,COERCIBLE), (latin1_swedish _ci,COERCIBLE),
> >(latin1_swedis h_ci,COERCIBLE) for operation 'concat'
> >
> >
> >I have no idea what any of this means, what on earth did I do wrong???[/color]
>
> 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.[/color]

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

$alphaNum = 'abcdefghijklmn opqrstuvwxyz';
$alphaNum .= strtoupper($alp haNum) . '0123456789';
$evilSubstring = "concat(UUI D(), concat('_',
SUBSTRING(GROUP _CONCAT(SUBSTRI NG('$alphaNum', round(rand() * " .
strlen($alphaNu m) . ") + 1, 1) SEPARATOR ''), 1, 16)))";
$query = new MySQLQuery("UPD ATE student SET uuid = (SELECT
$evilSubstring FROM student_ethnici ty_interest_ass oc) 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
[color=blue]
>
> Gordon L. Burditt[/color]