Currently viewing the category: "mysql"

The Lack of Flexibility of Stored Procedures in MySQL

By On January 6, 2010 · 6 Comments

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 to generate. Some of [...]

Continue Reading →

Stored Procedure For Finding Columns In MySQL

By On December 10, 2009 · 4 Comments

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 easy to use stored [...]

Continue Reading →

MySQL 5.1.34 and Innodb

By On June 18, 2009 · 1 Comment

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;
+————+———+———————————————————–+————–+——+————+
| [...]

Continue Reading →

Interesting Programmer Links

By On April 21, 2009 · Leave a Comment

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 the Twitter libmemcached [...]

Continue Reading →

MySQL: Innodb Memory Usage Formula

By On December 23, 2008 · 5 Comments

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)

Continue Reading →

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

By On August 7, 2008 · 5 Comments

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. This probably wouldn’t have been a problem [...]

Continue Reading →

Executing MySQL queries within VIM

By On August 1, 2008 · 1 Comment

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()
: let g:current_db = input(“Database > [...]

Continue Reading →

MySQL ALTER table Progress Bar?

By On May 15, 2008 · 6 Comments

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.

Continue Reading →

MySQL and Materialized Views

By On May 13, 2008 · 12 Comments

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 a [...]

Continue Reading →

MySQL: Time Delayed Replication

By On May 7, 2008 · 7 Comments

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 always
[...]

Continue Reading →