-
Row to Column in MySQL
Say you have two tables like these: And you have these values: id name 1 Kesavan 2 Madhavan user_id attribute_name attribute_value 1 GENDER M 1 PREMIUM_USER N 1 SUBSCRIBED_TO_NEWSLETTER Y 2 GENDER M 2 PREMIUM_USER Y To get a result like this: id name GENDER PREMIUM_USER SUBSCRIBED_TO_NEWSLETTER 1 Kesavan M N Y 2 Madhavan M…
-
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…
-
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`…
-
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…
-
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 |…