bizarre error trying to add uuid() to new table column

ppowell

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

bogu

UUID() <-- What does this function do? 🆒

ppowell

from http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html

UUID()

Returns a Universal Unique Identifier (UUID) generated according to “DCE 1.1: Remote Procedure Call” (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 (Document Number C706, http://www.opengroup.org/public/pubs/catalog/c706.htm).

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

A UUID is a 128-bit number represented by a string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:
*

  The first three numbers are generated from a timestamp.
*

  The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
*

  The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.

  Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number.
mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'

Note that UUID() does not yet work with replication.

UUID() was added in MySQL 4.1.2.

Phil

bogu

ppowell wrote:
Note that UUID() does not yet work with replication.

UUID() was added in MySQL 4.1.2.

So I supposed u got that version of mysql?

update student set uuid = concat('UUID()', '_', 'asdfasdfasdfasdf') where uuid is null or uuid = '' limit 1;

maybe u should try it like this, because:

A UUID is a 128-bit number

Sry, I cant help u more because I use a lower version of mysql that dont have that function ...

ppowell

I don't think you can embed mysql functions inside single quote as it turns into a string literal, unless I'm wrong.

Phil

bogu

Yes your right ...

But the UUID() function is the problem, I'm sry but I cant help u ...

ppowell

Update:

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

Yep, still stuck, now GROUP_CONCAT() fails in MySQL!

Phil

ppowell

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