473,241 Members | 1,637 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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 2590
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Ethan | last post by:
I've been having some really weird problems with a very simple PHP app. I'm wondering if anyone can help me sort this out. I have a page that prints out the results of a MySQL query as an HTML...
5
by: Moshe | last post by:
Scenario - IIS 5.0 WIN2K latest SP Application ASP. An ASP Page has a text area. When submitted calls SQL server 2K Stored Procedure to update a record. The field in the data base is of type...
7
by: Spacen Jasset | last post by:
The main two desirable things I feel error handling should provide are these: 1) Debugging and diagnostic aid 2) User feedback One method that is used a fair amount it to 'say' that all...
2
by: Joel Whitehouse | last post by:
Hey guys, I was trying to add typelib functionality to a VS2003 multithreaded .dll that I'm writing. I read some instructions on how to do this, so I created a .odl file and added it to the...
5
by: Ron St-Pierre | last post by:
I found this error in /var/log/messages yesterday after a cron job wouldn't complete: STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate; ERROR: tables can have at most 1600 columns...
1
by: Xeth Waxman | last post by:
Good afternoon, I have a bizarre question. When running the following query: select SomeColumnName from TableA where PK_TableA in (select PK_TableA from TableB) I get results. This should...
0
by: bolyogesh | last post by:
Hi, I am trying to load some java files to oracle server. I could do it properly here on one database (Windows machine). But failing to work on another database (unix system). I don't know what...
2
ak1dnar
by: ak1dnar | last post by:
Hi, I am getting some sql errors when trying to insert a record to mysql table using asp.net application. Let me explain the scenario for your reference. This asp.net web form is executing...
12
by: Gerhard | last post by:
This is bizarre... Im having problems with the combobox AfterUpdate event: Im running Access 2003. I created an unbound combobox with 3 columns on a form. The Row Source is from a table....
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.