Archive for the ‘mysql’ Category

The Lack of Flexibility of Stored Procedures in MySQL

Wednesday, January 6th, 2010

Over three years ago I wrote about how you cannot use a stored procedure in a subquery. Well, it's 2010, and I'm still annoyed by this and a handful of other things. I was just working today on a report consisting of a series of queries, taking about a minute ...

Stored Procedure For Finding Columns In MySQL

Thursday, December 10th, 2009

Looking for instances particular column in a large schema can be a pain. Fortunately the information schema makes this pretty easy, if your columns have a consistent naming convention. SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%some_name%'; Now, if we want to wrap this up into an ...

MySQL 5.1.34 and Innodb

Thursday, June 18th, 2009

I don't remember having to do this before, but I had compiled MySQL 5.1.34 recently on my dev box (os x) and I saw a warning on a create table statement. It turns out InnoDB was not enabled (or even listed in the list of storage engines. Before: mysql> show engines; +------------+---------+-----------------------------------------------------------+--------------+------+------------+ | ...

Interesting Programmer Links

Tuesday, April 21st, 2009

Peeping into memcached. Really interesting read about how to examine what's stored in memcached. Peep uses ptrace to freeze a running memcached server, dump the internal key metadata, and return the server to a running state. If you have a good host ejection mechanism in your client, such as in ...

MySQL: Innodb Memory Usage Formula

Tuesday, December 23rd, 2008

I hate looking for this.... This will give you a rough idea of your innodb memory usage. I know it's in a hundred spots, but i hate looking for it when i double check things. innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size +2MB)

MySQL: ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

Thursday, August 7th, 2008

I have never, ever seen this before. I don't even know how the table was created with a different collation. However, I had all my other tables created with the character set utf8, no collation specified. I had to convert the second table to match the character set. ...

Executing MySQL queries within VIM

Friday, August 1st, 2008

I haven't been using vim for very long, but I've gotten over the initial learning curve of getting used to the different editing modes. With some help from the guys in #vim on irc.freenode.net, I managed to get this gem: map <C-d> :call SwitchDB()<CR> :function SwitchDB() : ...

MySQL ALTER table Progress Bar?

Thursday, May 15th, 2008

Altering a big table sucks, and to make it worse you have no idea what's happening or how long it will take. I'd like a progress bar, or some status output, or something that gives me the feeling like my server didn't die.

MySQL and Materialized Views

Tuesday, May 13th, 2008

I was poking around the MySQL Worklog again over the weekend, and found a request for materialized views for MySQL. This feature has existed in Oracle for a while, in DB2 as a materialized query table, and appeared in MS SQL Server 2000 and 2005 as indexed views. What is ...

MySQL: Time Delayed Replication

Wednesday, May 7th, 2008

I was cruising the MySQL Forge Worklog when I came across the idea of Time Delayed Replication. I had never considered the benefits of deliberately keeping a slave server behind a master. Kristian Koehntopp gives a good example: Kristian Koehntopp writes: TDS: Time delayed SQL_THREAD (Have a replication slave that is ...