Row to Column in MySQL

Say you have two tables like these:

CREATE TABLE user (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
) ENGINE = 'InnoDB';
CREATE TABLE user_attributes(
user_id INT(11) UNSIGNED NOT NULL,
attribute_name VARCHAR(255) NOT NULL,
attribute_value VARCHAR(255) NOT NULL,
UNIQUE KEY (user_id, attribute_name)
) ENGINE = 'InnoDB';

And you have these values:

idname
1Kesavan
2Madhavan
user_idattribute_nameattribute_value
1GENDERM
1PREMIUM_USERN
1SUBSCRIBED_TO_NEWSLETTERY
2GENDERM
2PREMIUM_USERY

To get a result like this:

idnameGENDERPREMIUM_USERSUBSCRIBED_TO_NEWSLETTER
1KesavanMNY
2MadhavanMYNULL

Use this query

SELECT 
u.id,
ANY_VALUE(u.name) as name,
GROUP_CONCAT(IF (ua.attribute_name = 'GENDER', ua.attribute_value, NULL)) AS 'GENDER',
GROUP_CONCAT(IF(ua.attribute_name = 'PREMIUM_USER', ua.attribute_value, NULL)) AS 'PREMIUM_USER',
GROUP_CONCAT(IF(ua.attribute_name = 'SUBSCRIBED_TO_NEWSLETTER', ua.attribute_value, NULL)) AS 'SUBSCRIBED_TO_NEWSLETTER'
FROM user u
JOIN user_attributes ua ON (u.id = ua.user_id)
GROUP BY u.id;

Dbfiddle link: https://www.db-fiddle.com/f/adNt6wy9fMoVudrbeoPtrU/3

myisamcheck running out of tmp space

Recently I was running myisamcheck on an entire database housed inside a vserver. And, by default vserver mounts a 16MB ramfs on /tmp. This obviously is not an ideal solution when you are running a DB server that has a few tables that are around 20GB. So I had to specify a different directory for storing temporary files – that was on a partition with enough space – while running myisamcheck. To do that I had to make use of the –tmpdir parameter defined here.

Also, the way to increase the /tmp size inside a vserver seems to be this. I have not tested that solution yet.

Getting rid of MySQL Warning: Truncated incorrect DOUBLE value

If you come across a cryptic warning like this:

| Warning | 1292 | Truncated incorrect DOUBLE VALUE: 'xxxxxxx'

on running a MySQL query, it could be caused by using a numeric value against a CHAR/VARCHAR column.

---assuming `name` is a CHAR/VARCHAR column
---the following query might cause warnings
SELECT * FROM `t1` WHERE `name` IN (1, 2, 3);
---replace the above with this
SELECT * FROM `t1` WHERE `name` IN ('1', '2', '3');
--and the warnings are quite likely to go away

Munin: Could not open required defaults file: /root/.my.cnf

For some reason I kept getting this error (even when the file was readable by all) in one of the munin (mysql slave) plugins that I had written.

So this was the solution that worked for me;

under the [mysql*] section in /etc/munin/plugin-conf.d/munin-node 
I added this: 
env.mysqlopts -u{user} -p{password}

and in my plugin

I did something like this:

mysql ${mysqlopts} -e'show slave status G'

and that seemed to have picked up the user and password from env.mysqlopts and the plugin started working (ofcourse after I restarted munin).

MySQL KEY, PRIMARY KEY and NOT NULL.

Have a look at this sql statement:

mysql> CREATE TABLE `t1` (`id` INT NULL AUTO_INCREMENT, KEY(`id`));
Query OK, 0 rows affected (0.92 sec)
 
mysql> SHOW CREATE TABLE `t1`;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

See, how MySQL implicitly converts the NULLable column `id` to NOT NULLable because the column `id` was also a key?

Also have a look at the following statements:

mysql> CREATE TABLE `t2` (`id` INT NULL AUTO_INCREMENT KEY);
Query OK, 0 rows affected (0.11 sec)
 
mysql> SHOW CREATE TABLE `t2`;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Having KEY in the column specification makes that column a PRIMARY KEY!

I am going to go through these pages:
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

and try to understand why the context really matters while specifying the key. and also the reasoning behind implicitly changing stuff instead of throwing an error.ATM, it just doesn’t make any sense!