MySQL group_concat Character Limit

GROUP_CONCAT  in MySQL is quite a handy function and I have used it lots of times to solve problems in my professional life. And today I just used it to solve a memory issue I have in my PHP script which requires grouping of data.

It all worked OK if you work with very small data set, but problems will arise if you need to process hundreds of millions of rows of data – MySQL does not return all data set required in the GROUP BY and will simply truncate the data based on the group_concat_max_len configuration in MySQL, and the default value is only 1024.

If you are working with a big data set, it is advised to set this config at run time so that you won’t get the unexpected behaviour:


SET GROUP_CONCAT_MAX_LEN = 100000;

Be mindful that although you can change the limit for GROUP_CONCAT, the upper limit is still controlled by the max_allowed_packet, please refer to MySQL documentation for more details.

MAMP MySQL Access Denied Error

After installing MAMP 2.0.5 and changed MySQL’s default root password, I kept getting the following error messages:

The phpMyAdmin still works, because I have already updated the config.inc.php, but this popup every time I start MAMP server is just annoying. And I have found a way to fix this.

To fix the first error Open file “/Applications/MAMP/bin/quickCheckMysqlUpgrade.sh” and update the password in it

/Applications/MAMP/Library/bin/mysqlcheck --quick --check-upgrade -u root -p{changeme} --socket=/Applications/MAMP/tmp/mysql/mysql.sock mysql

Do the same for the following files too:

“/Applications/MAMP/bin/upgradeMysql.sh”
“/Applications/MAMP/bin/checkMysql.sh”

To fix the second error update the password as well in file: “/Applications/MAMP/bin/mamp/index.php”


<?php
include_once 'php/functions.php';

$port = '3306';
$link = @mysql_connect(':/Applications/MAMP/tmp/mysql/mysql.sock', 'root', '{changeme}');

if (!$link) {
exit('Error: Could not connect to MySQL server!');
}
mysql_close($link);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>MAMP</title>
</head>
<frameset rows="39,*" frameborder="no" border="0" framespacing="0">
<frame src="<?php echo $language; ?>/navigation.php?language=<?php print $language; ?>" name="navigationFrame" id="navigationFrame" scrolling="No" noresize="noresize" />
<frame src="<?php echo $language; ?>/index.php?language=<?php print $language; ?>" name="contentFrame" id="contentFrame" />
</frameset>
</html>

After the changes, both errors should just disappear.

Hive Query Column Type Comparison

Hive query is very close to MySQL in many ways, and it has lots of other features that MySQL does not have. I have been playing with it for quite a few months and I quite enjoy using it so far.

There are also lots of problems that I have encountered and here is one of them.

Because Hive is build from JAVA, which is a strong typed language, when doing query on Hive tables, you should be careful about column types and the value you are comparing with, for example:

CREATE TABLE hive_table (hive_string_column string, hive_int_column int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- INSERT SOME DATA HERE

SELECT * FROM hive_table WHERE hive_string_column != 0;
SELECT * FROM hive_table WHERE hive_int_column != '';

Both of the queries above will return no results regardless, simply because the types don’t match. The correct query should be:

SELECT * FROM hive_table WHERE hive_string_column != '' OR hive_string_column != '0';
SELECT * FROM hive_table WHERE hive_int_column != 0;

It is not an easy pickup initially, but now you know!

How To Order Data Inside GROUP_CONCAT Using MySQL

I use GROUP_CONCAT function in MySQL quite often, but today I encountered a situation where by I need to order the data returned from the GROUP_CONCAT function call. Simply having “ORDER BY” clause at the end of the query does not help as it orders the final output rows, not the data inside the GROUP.

I have found the way to do it, very simple:

SELECT `column`, GROUP_CONCAT(DISTINCT choice_id ORDER BY bit_index DESC) AS choice_ids FROM table_bit_index GROUP BY `column`

The stuff inside the GROUP_CONCAT does the trick “DISTINCT choice_id ORDER BY bit_index DESC“.

Now I know..

Order By Values Within IN() Under MySQL

I have encountered this problem before and I had found the solution, but now I have completed forgotten how to do it again. Here is the question:

I have a query like

SELECT id, name FROM table WHERE id IN (2,4,3,1);

And I want the result to be sorted by the order specified in the IN clause.

In order to do this, simply modify the query like the following:

SELECT id, name FROM table WHERE id IN (2,4,3,1) ORDER BY FIELD (id, 2,4,3,1);

The FIELD function returns the position of the first string in the remaining list of strings.

Hope this would help someone like me.