Archive for the ‘mysql’ Category
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 ...
Posted in mysql, rant | 5 Comments »
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 ...
Posted in mysql, stored procedure | 3 Comments »
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;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| ...
Posted in innodb, mysql | 1 Comment »
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 ...
Posted in linux, mysql, tips | No Comments »
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)
Posted in mysql | 5 Comments »
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. ...
Posted in mysql | 5 Comments »
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()
: ...
Posted in mysql, vim | 1 Comment »
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.
Posted in mysql | 6 Comments »
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 ...
Posted in db2, mssql, mysql, oracle | 10 Comments »
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 ...
Posted in mysql | 6 Comments »