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!