tag:blogger.com,1999:blog-204349082024-02-28T15:42:42.416-08:00Rational RelationalUnknownnoreply@blogger.comBlogger44125tag:blogger.com,1999:blog-20434908.post-68178028413459488222023-07-03T12:17:00.006-07:002023-07-03T12:25:57.844-07:00Is blogging dead?<p>It's clear to whoever stumbles on this page today, that I've not touched it in well over a decade. Blogger seems to have not changed much since, it only feels a bit more broken and weathered, like patio furniture left out for a few seasons.</p><p>If you wonder why the site is up after all these years, it comes down to a few things. </p><p>Mostly, I've figured I can keep this page up for the hundreds of people who search for some of the MySQL related posts and are perhaps even finding them usable, so many years after posting. If you're one of those, I hope it helped you. In the case it was one of those search dead ends and I wasted you a browser tab and a click... that's unfortunate. The rest of the content is a trip down memory lane to a time before social media, and it's personally amusing to keep around.</p><p>Another more subtle reason is the optimism and hope that perhaps I'll get back to publishing things at some point. It's kind of like keeping a motorcycle you don't have time to ride in our garage, just to feel you have your options open and identify yourself as a rider. As they say, hope dies last.</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-88398678557763671512013-10-22T12:22:00.001-07:002013-11-30T12:00:45.427-08:00Under the Hood at Facebook: MySQL Pool Scanner (MPS)My <a href="https://www.facebook.com/notes/facebook-engineering/under-the-hood-mysql-pool-scanner-mps/10151750529723920">blog post about MPS</a> on the Facebook Engineering blog has been published today!<br />
<br />
This is a pretty amazing piece of automation that we've been building at Facebook for the past few years, and I'm excited to be able to speak about it in public. <br /><br />Update: It is now also available at the <a href="https://code.facebook.com/posts/180455938822278/under-the-hood-mysql-pool-scanner-mps-/">new Facebook Code site</a>.<br />
<br />
<div id="fb-root">
</div>
<script>(function(d, s, id) { var js, fjs = d.getElementsByTagName(s)[0]; if (d.getElementById(id)) return; js = d.createElement(s); js.id = id; js.src = "//connect.facebook.net/en_US/all.js#xfbml=1"; fjs.parentNode.insertBefore(js, fjs); }(document, 'script', 'facebook-jssdk'));</script>
<br />
<div class="fb-post" data-href="https://www.facebook.com/notes/facebook-engineering/under-the-hood-mysql-pool-scanner-mps/10151750529723920" data-width="550">
<div class="fb-xfbml-parse-ignore">
<a href="https://www.facebook.com/notes/facebook-engineering/under-the-hood-mysql-pool-scanner-mps/10151750529723920">Post</a> by <a href="https://www.facebook.com/Engineering">Facebook Engineering</a>.
</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-78120831088088710422011-08-16T09:01:00.000-07:002011-08-16T09:01:49.356-07:00How to Quickly Create a Histogram in MySQLThis is a post about a super quick-and-dirty way to create a histogram in MySQL for numeric values.<br />
<div>
There are multiple other ways to create histograms that are better and more flexible, using <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CASE</span> statements and other types of complex logic. This method wins me over time and time again since it's just so easy to modify for each use case, and so short and concise.</div>
<div>
This is how you do it:<br />
<pre><span style="font-family: 'Courier New';"><span style="color: blue;">SELECT</span> <span style="color: #ff0080;"><b>ROUND</b></span><span style="color: maroon;">(</span><span style="color: maroon;">numeric_value</span><span style="color: silver;">,</span> <span style="color: silver;">-</span><span style="color: black;">2</span><span style="color: maroon;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">bucket</span><span style="color: silver;">,</span>
<span style="color: #ff0080;"><b>COUNT</b></span><span style="color: maroon;">(</span><span style="color: silver;">*</span><span style="color: maroon;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">COUNT</span><span style="color: silver;">,</span>
<span style="color: #ff0080;"><b>RPAD</b></span><span style="color: maroon;">(</span><span style="color: red;">''</span><span style="color: silver;">,</span> <span style="color: #ff0080;"><b>LN</b></span><span style="color: maroon;">(</span><span style="color: #ff0080;"><b>COUNT</b></span><span style="color: maroon;">(</span><span style="color: silver;">*</span><span style="color: maroon;">)</span><span style="color: maroon;">)</span><span style="color: silver;">,</span> <span style="color: red;">'*'</span><span style="color: maroon;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">bar</span>
<span style="color: blue;">FROM</span> <span style="color: maroon;">my_table</span>
<span style="color: blue;">GROUP</span> <span style="color: blue;">BY</span> <span style="color: maroon;">bucket</span><span style="color: silver;">;</span></span></pre>
Just change <span class="Apple-style-span" style="color: maroon; font-family: 'Courier New'; white-space: pre;">numeric_value</span> to whatever your column is, change the rounding increment, and that's it. I've made the bars to be in logarithmic scale, so that they don't grow too much when you have large values.<br />
<div>
This is an example of such query on some random data that looks pretty sweet. Good enough for a quick evaluation of the data.</div>
<div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+--------+----------+-----------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| bucket | count | bar |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+--------+----------+-----------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| -500 | 1 | |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| -400 | 2 | * |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| -300 | 2 | * |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| -200 | 9 | ** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| -100 | 52 | **** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 0 | 5310766 | *************** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 100 | 20779 | ********** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 200 | 1865 | ******** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 300 | 527 | ****** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 400 | 170 | ***** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 500 | 79 | **** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 600 | 63 | **** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 700 | 35 | **** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 800 | 14 | *** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 900 | 15 | *** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 1000 | 6 | ** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 1100 | 7 | ** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 1200 | 8 | ** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 1300 | 5 | ** |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 1400 | 2 | * |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 1500 | 4 | * |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+--------+----------+-----------------+</span></div>
</div>
Some notes:<br />
Ranges that have no match will not appear in the count - you will not have a zero in the count column. Also, I'm using the <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ROUND</span> function here. You can just as easily replace it with <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">TRUNCATE</span> if you feel it makes more sense to you.</div>
Unknownnoreply@blogger.com8tag:blogger.com,1999:blog-20434908.post-57270090113586202912011-08-10T09:28:00.005-07:002011-08-10T10:30:04.676-07:00How to Kill All Connections to MongoDBThis post is similar to the previous one I have <a href="http://blog.shlomoid.com/2008/06/how-to-perform-connection-massacre.html">posted before for MySQL</a>. Only this time, it's for MongoDB. <br />
What's so nice about MongoDB is that the command line is actually a fully fledged javascript console. So you can do <i>this</i>:<br />
<script src="http://gist-it.appspot.com/github/shlomoid/mongodb-snippets/raw/master/killall.js">
</script>
(the embedded script is not visible from the RSS feed though, so click through to the post itself)<br />
The function accepts two variables, both optional. <br />
The first one is the namespace for which you'd like the connections to be working with. It's optional, but you'd usually want to specify it. If you don't, you'll kill all the connections, including the ones that perform replication duties.<br />
The second one is a "wet" run parameter, that if set to true actually does the killing. The default is false, thus not doing anything but print the connections to be killed to your console.
<br />
<br />
The whole thing is a work in progress, plus my javascript skills are <i>far</i> from perfect, so comments here or on <a href="https://github.com/shlomoid/mongodb-snippets">github</a> are welcome.
<br />
A potential issue I've already noticed is that when some operations are in a <i>yeild</i> state, the namespace's first letter is <a href="http://www.mongodb.org/display/DOCS/Viewing+and+Terminating+Current+Operation">prefixed with a question mark</a> "?". In that case, the simple namespace filter will probably miss it. But, I didn't want to make the function any more complex than it already is.Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-20434908.post-63806540018664720032011-08-08T01:53:00.004-07:002011-08-09T01:19:05.204-07:00How to Easily See Who's Connected to Your MySQL ServerI'm posting this here since it has been useful for me, and the blog is a nice place to keep public notes.<br />
If you have servers which have multiple application servers connected to them, you often need to see things like who's connected, how many connections they have, and which users. Using <span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px;"><span style="color: blue;">SHOW</span> <span style="color: maroon;">PROCESSLIST</span></span> doesn't work that well, since it gives you a row for each server.<br />
<div>
<br /></div>
What we want is an output similar to this:<br />
<blockquote>
<pre>+-----------------+-----------------+----------+
| host_short | users | count(*) |
+-----------------+-----------------+----------+
| slave1 | repl | 1 |
| slave2 | repl | 1 |
| localhost | event_scheduler | 1 |
| 111.111.222.111 | root, foo | 2 |
| 111.111.222.222 | appuser, bar | 3 |
| 111.111.222.333 | appuser, moshe | 9 |
+-----------------+-----------------+----------+</pre>
</blockquote>
And it is achieved using a simple query such as this one:<br />
<div>
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="color: black;"><span style="color: blue;">SELECT</span> <span style="color: #ff0080; font-weight: bold;">SUBSTRING_INDEX</span><span style="color: maroon;">(</span><span style="color: maroon;">host</span><span style="color: silver;">,</span> <span style="color: red;">':'</span><span style="color: silver;">,</span> <span style="color: black;">1</span><span style="color: maroon;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">host_short</span><span style="color: silver;">,</span><br /> <span style="color: #ff0080; font-weight: bold;">GROUP_CONCAT</span><span style="color: maroon;">(</span><span style="color: blue;">DISTINCT</span> <span style="color: blue;">USER</span><span style="color: maroon;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">users</span><span style="color: silver;">,</span><br /> <span style="color: #ff0080; font-weight: bold;">COUNT</span><span style="color: maroon;">(</span><span style="color: silver;">*</span><span style="color: maroon;">)</span><br /><span style="color: blue;">FROM</span> <span style="color: maroon;">information_schema</span><span style="color: silver;">.</span><span style="color: maroon;">processlist</span><br /><span style="color: blue;">GROUP</span> <span style="color: blue;">BY</span> <span style="color: maroon;">host_short</span><br /><span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> <span style="color: #ff0080; font-weight: bold;">COUNT</span><span style="color: maroon;">(</span><span style="color: silver;">*</span><span style="color: maroon;">)</span><span style="color: silver;">,</span><br /> <span style="color: maroon;">host_short</span><span style="color: silver;">;<br /></span></span></span></span><br />
A final note: I'm not sure what version of MySQL this query needs to function, but it works great on MySQL 5.5, and should work just as well on MySQL 5.1.</div>
Unknownnoreply@blogger.com7tag:blogger.com,1999:blog-20434908.post-50909807122398452122011-08-06T23:10:00.001-07:002011-08-06T23:10:55.978-07:00How to Move a MongoDB Collection Between DatabasesIf you need to move a collection between two MongoDB databases, there is no need to dump and restore your data using <span class="Apple-style-span" style="font-family: monospace; white-space: pre;">mongodump / mongorestore.</span><br />
<br />
In a way, it's pretty similar to what we can do with MySQL's "<a href="http://dev.mysql.com/doc/refman/5.5/en/rename-table.html">RENAME TABLE</a>" command:<br />
<blockquote>
<pre>db.runCommand({renameCollection:"<b>sourcedb</b>.mycol",to:"<b>targetdb</b>.mycol"})</pre>
</blockquote>
In the background, MongoDB will dump and restore it automatically. There is no metadata "magic" for such a rename since databases reside in different files on disk. It just saves a bit of work, and it's worth knowing about.Unknownnoreply@blogger.com8tag:blogger.com,1999:blog-20434908.post-20429224320252573822011-08-06T05:15:00.003-07:002011-08-06T23:11:07.459-07:00How to Fix Erroneously Named MongoDB CollectionsIn MongoDB, collections are not supposed to include certain characters like, for example, a hyphen "-" or a slash "/". The manual <a href="http://www.mongodb.org/display/DOCS/Collections">has this to say</a> on the matter:<br />
<blockquote>
Collection names <b>should</b> begin with letters or an underscore and may include numbers; $ is reserved. Collections can be organized in namespaces; these are named groups of collections defined using a dot notation.</blockquote>
The keyword here is "should". <br />
It is possible to create collations with names that do not adhere to these rules. This is not enforced at all. When you try to access such a collection from the shell, things don't quite work as expected:<br />
<blockquote>
<pre>>db.test-col.find()
Sat Aug 6 14:54:06 ReferenceError: col is not defined (shell):1</pre>
</blockquote>
This is because the javascript console tries to subtract "col" from "test". I've tried different escaping methods for the rogue hyphen, but couldn't find one that works.<br />
Eventually, I've reverted to using the more "internal" renameCollection command, to rename the collection into something manageable:<br />
<blockquote>
<pre>>db.runCommand({renameCollection:"test.test-col",to:"test.test_col"})</pre>
</blockquote>
<i>Which solves the problem completely.</i><br />
<br />
Now you might be wondering: how was this created in the first place? Because if you can't read from this collection, you can't "save" into it from the shell as well.<br />
We have a nice list of commands <a href="http://www.mongodb.org/display/DOCS/List+of+Database+Commands">here</a> which I recommend to anyone who's working with MongoDB to be familiar with. One of these commands is the aptly named "create", which will obediently create a collection with such a name:<br />
<blockquote>
<pre>>db.runCommand({"create":"test-col"});
{ "ok" : 1 }</pre>
</blockquote>
I can only assume drivers use this same command to create the collections, which was how it was created in the first place.Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-20434908.post-79698812025701543922011-01-23T14:07:00.001-08:002011-01-24T00:02:41.596-08:00More Optimizer Bugs in MySQLI'm proud to report of two bugs I've submitted to the MySQL bug tracker.<br />
Both are seemingly simple and vaguely related, but have grave consequences when combined with large amounts of data.<br />
<br />
The first one, <a href="http://bugs.mysql.com/bug.php?id=50212">Bug #50212</a> is a regression performance problem in 5.1, dealing with MySQL not using a primary key when it can, in order to avoid a file sort. The problem is made worse by the fact that even using a <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">FORCE INDEX</span> hint does not help things. The bug requires a join condition to be present to reproduce.<br />
So if you have a large table, and you're expecting MySQL to select data from it ordered by the primary key and avoid a file sort, you're out of luck.<br />
Of course, you can remove the <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORDER BY</span> and hope it's going to return in order, but it's not guaranteed.<br />
There is no workaround, and this bug is in a verified state for a year now.<br />
<br />
A more recent bug, <a href="http://bugs.mysql.com/bug.php?id=59557">Bug #59557</a> deals with another optimization issue, that takes place when MySQL is not willing to perform an index scan to complete a <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">GROUP BY</span> statement, when you reverse the order of the fields compared the their order in the index.<br />
<br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Since a <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">GROUP BY</span> statement does not care about the order of fields, it should not matter: the fields in the <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">GROUP BY</span> clause are essentially a set, and not a ordered list, as they are in <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORDER BY</span>.</div><div><br />
</div>An example to demonstrate the problem (taken from the bug report itself):<br />
For this table:<br />
<span class="Apple-style-span" style="font-family: Verdana, 'Lucida Grande', 'Lucida Sans Unicode', Tahoma, Arial, sans-serif; font-size: 12px; line-height: 19px;"></span><br />
<blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CREATE TABLE `t` (<br />
`a` INT(11) NOT NULL,<br />
`b` INT(11) NOT NULL,<br />
PRIMARY KEY (`a`,`b`)<br />
) ENGINE=INNODB;</span></blockquote>Run this:<br />
<blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a\G<br />
*************************** 1. row ***************************<br />
id: 1<br />
select_type: SIMPLE<br />
table: t<br />
type: index<br />
possible_keys: NULL<br />
key: PRIMARY<br />
key_len: 8<br />
ref: NULL<br />
rows: 1<br />
Extra: Using index; Using temporary; Using filesort</span></blockquote><br />
What you get is: <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><b>Using index; Using temporary; Using filesort</b></span><br />
If you're a MySQL veteran, you know that any <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">GROUP BY</span> is implicitly a <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORDER BY</span> as well - and you'd guess that causes the issue.<br />
Unfortunately, even if we add a <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">ORDER BY NULL</span> clause, the optimizer would still choose a wrong plan, like so:<br />
<blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a ORDER BY NULL\G<br />
*************************** 1. row ***************************<br />
id: 1<br />
select_type: SIMPLE<br />
table: t<br />
type: index<br />
possible_keys: NULL<br />
key: PRIMARY<br />
key_len: 8<br />
ref: NULL<br />
rows: 1<br />
Extra: Using index; Using temporary</span></blockquote>What you get now is: <span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><b>Using index; Using temporary</b></span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"></span>A bit <b>less</b> <b>wrong</b>, but still... wrong. If someone knows what the database tried to accomplish by using a temporary table here, I'd be happy to hear an explanation... :)<br />
<br />
As I've said, I am proud to submit bugs and help the community, but I would be a bit more proud if bugs I've submitted a year ago would have been fixed by now. Just saying.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-51239276062922174002010-11-18T03:34:00.002-08:002010-11-24T04:10:30.275-08:00Which SQL Mode Should I Use with MySQL?Dealing with sql_mode can be a tricky business, and many people have blogged about it before (<a href="http://rpbouman.blogspot.com/2009/01/mysqls-sqlmode-my-suggestions.html">here </a>and <a href="http://code.openark.org/blog/mysql/do-we-need-sql_mode">here</a>). My intent is not to explain the details of what each option means (since it's covered in the <a href="http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html">manual</a>), but rather to answer this simple and common question:<br />
Which SQL mode should I use with MySQL? / What is the recommended SQL mode for MySQL?<br />
<br />
My general purpose recommendation is this one:<br />
<pre><b>sql-mode="TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"</b></pre>This "traditional" setting includes most of what you'd want your database to behave like, namely:<br />
<pre>STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER</pre>This has a nice benefit of returning an <b>error</b> instead of a <b>warning</b> on truncating a varchar column, not let you inserts completely broken date values, and give out division by zero errors. That's pretty traditional as I see it.<br />
<br />
The other two settings make sure you don't create InnoDB tables as MyISAM by mistake, and that you include all the columns in the GROUP BY clause, like in other databases.<br />
<br />
That's my choice for a reasonable all-around setting - for different applications you might need different ones (see excellent comment by Roland Bouman below).Unknownnoreply@blogger.com6tag:blogger.com,1999:blog-20434908.post-2312147432696897552010-11-09T01:41:00.001-08:002011-04-07T07:06:14.294-07:00How to Install innotop 1.8.0 on CentOS 5<a href="http://innotop.sourceforge.net/">innotop</a> is a fine tool that every MySQL DBA should be familiar with. Although it takes a while to get used to, it's worth it. It's less than intuitive for most people, unless you grew up in Linux.<br />
The other problematic part except the learning curve is the installation. It took me a while last time I had to install it, and this time re-installing it I've decided to write down the steps needed.<br />
<br />
Most of the trouble I went through the first time around were with CPAN, and finding out that those Perl modules were available in RPM form saved me lots of time. Avoid CPAN if you can for this purpose.<br />
Also, these steps should work on RedHat Linux as well, of course, but I didn't test it.<br />
<ol><li><a href="http://dev.mysql.com/downloads/mysql/5.1.html">Download </a>the compatibility version of the shared MySQL libraries. The compatibility package will save you a lot of trouble due to dependencies. The rest won't go smoothly if you don't get this one. <br />
It should be named something like:<pre>MySQL-shared-compat-5.1.xx.distribution.platform.rpm</pre></li>
<li>Install it:<br />
<pre>rpm -Uvh MySQL-shared-compat-5.1.52.rhel5.x86_64.rpm</pre></li>
<li>Install these packages:<br />
<pre>yum install perl-DBI perl-DBD-mysql perl-TermReadKey</pre></li>
<li>Download latest Innotop, and extract it:<br />
<pre>wget http://innotop.googlecode.com/files/innotop-1.8.0.tar.gz
tar zfx innotop-1.8.0.tar.gz</pre></li>
<li>We're done with the prerequisites. Go into the directory and install innotop (from now on it's the same as in the INSTALL file):<br />
<pre>perl Makefile.pl
make install</pre><pre></pre></li>
</ol>That's it. We're done.<br />
Hope this saves someone some time.<br />
<br />
<b>Update</b>:<br />
On Debian, this is also simple due to this little trick (if you have the right sources defined):<br />
<pre>sudo aptitude install libdbd-mysql-perl</pre>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-20434908.post-35816777681283708842010-07-27T06:33:00.000-07:002010-07-27T06:33:31.242-07:00Recommended Google Chrome ExtensionsNot all great Chrome Extensions get to the <a href="https://chrome.google.com/extensions/list/featured?type=ext">Featured</a> or <a href="https://chrome.google.com/extensions/list/popular?type=ext">Popular</a> lists on the <a href="https://chrome.google.com/extensions">Google Chrome Extensions website</a>. These are 3 extensions I really like, and are not as widely known.<br />
<ul><li><a href="https://chrome.google.com/extensions/detail/acgimceffoceigocablmjdpebeodphgc">Chromey Calculator</a> - super useful for quick money/distance conversions</li>
<li><a href="https://chrome.google.com/extensions/detail/binjiceocgbfooocmheaenmmcominbpe">JoinTabs</a> - unites all the open chrome windows into one window as tabs</li>
<li><a href="https://chrome.google.com/extensions/detail/elioihkkcdgakfbahdoddophfngopipi">Facebook Photo Zoom</a> - does exactly what the name says, highly recommended</li>
</ul>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-48738740635935525432010-07-26T06:00:00.002-07:002010-07-26T06:01:00.949-07:00UNHEX for Integers in MySQLThe following is a <b>very, very</b> specific subject matter, but I wish this blog post had existed when I was looking for the solution to a problem I was having. Most likely it has no interest to anyone who is not specifically looking for it, so feel free to stop reading...<br /><br />MySQL has a few built-in functions for handling binary data. One of them is <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_hex">HEX </a>which converts any data into hexadecimal representation. The function which you would expect to do the opposite (as the manual states) is <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_unhex">UNHEX</a> which takes a hexadecimal representation and turns it into <i>characters</i>.<br /><br />So, if you try to do:<br /><blockquote><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: blue;">SELECT</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"> </span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: #ff0080;"><b>UNHEX</b></span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">(</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: #ff0080;"><b>HEX</b></span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">(</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: red;">'a'</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">)</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">)</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: silver;">;</span></span></blockquote>You get an "a" back. But if you try that on a integer:<br /><blockquote><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: blue;">SELECT</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"> </span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: #ff0080;"><b>UNHEX</b></span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">(</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: #ff0080;"><b>HEX</b></span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">(</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: black;">1</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">)</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">)</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: silver;">;</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"> </span></blockquote>You get back the char corresponding to the ASCII value of 1, which is not what was intended.<br />The correct way to do it, and the real opposite of HEX is the <a href="http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_conv">CONV</a> function which converts betweens bases:<br /><blockquote><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: blue;">SELECT</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"> </span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: #ff0080;"><b>CONV</b></span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">(</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: #ff0080;"><b>HEX</b></span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">(</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: black;">1</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">)</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: silver;">,</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"> </span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: black;">16</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: silver;">,</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"> </span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: black;">10</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: maroon;">)</span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: small;"><span style="color: silver;">;</span></span></blockquote>This time the result is the number "1" as expected.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-46555832008800388152010-02-17T05:57:00.001-08:002010-02-17T06:09:18.318-08:00Emulating The Missing RENAME DATABASE Command in MySQLI'll approach this topic from another direction.<br /><div>I had to perform a task which required moving tables between schemas on the same MySQL server.</div><div>One way of doing this would be:</div><div><ol><li>Dumping the original table to disk.</li><li>Creating new empty table on the target schema.</li><li>Importing table from disk into the target schema.</li></ol></div><div>But, a simpler way exists!</div><div>The <a href="http://dev.mysql.com/doc/refman/5.1/en/rename-table.html">manual entry for RENAME TABLE</a> casually describes this feature:</div><blockquote><span class="Apple-style-span" style=" line-height: 23px;font-family:verdana, arial, helvetica, sans-serif;font-size:13px;color:#555555;"><div style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; display: block; font-family: sans-serif; font-size: 14px; line-height: 25px; margin-bottom: 10px; margin-left: 0px; margin-right: 0px; margin-top: 0px; max-width: 720px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">As long as two databases are on the same file system, you can use <a href="http://dev.mysql.com/doc/refman/5.1/en/rename-table.html" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #015a84; font-size: 14px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-decoration: underline; vertical-align: baseline;" title="12.1.33. RENAME TABLE Syntax"><code class="literal" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #026789; font-family: monospace, fixed; font-size: 13px; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">RENAME TABLE</code></a> to move a table from one database to another:</div><pre class="programlisting" style="background-attachment: initial; background-clip: initial; background-color: #eeeeee; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: black; display: block; font-family: monospace, fixed; font-size: 13px; line-height: 20px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; max-width: 720px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 2px; padding-left: 2px; padding-right: 2px; padding-top: 2px; vertical-align: baseline;">RENAME TABLE <em class="replaceable" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-size: 13px; font-style: italic; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;"><code style="background-attachment: initial; background-clip: initial; background-color: #cccccc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #026789; font-family: monospace, fixed; font-size: 12px; font-weight: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 1px; padding-left: 1px; padding-right: 1px; padding-top: 1px; vertical-align: baseline;">current_db.tbl_name</code></em> TO <em class="replaceable" style="background-attachment: initial; background-clip: initial; background-color: transparent; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-size: 13px; font-style: italic; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;"><code style="background-attachment: initial; background-clip: initial; background-color: #cccccc; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; color: #026789; font-family: monospace, fixed; font-size: 12px; font-weight: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 1px; padding-left: 1px; padding-right: 1px; padding-top: 1px; vertical-align: baseline;">other_db.tbl_name;</code></em></pre></span></blockquote><div>Which works almost instantaneously and does what it is supposed to do. This command could also be called MOVE TABLE.</div><div><br /></div><div>Using this command, it is easy to emulate the missing RENAME DATABASE command.</div><div><ol><li>Create the new schema.</li><li>Perform a query to generate the move commands from the source to the target schema:<br /><blockquote><blockquote><span class="Apple-style-span" style=" ;font-family:'Courier New';font-size:13px;"><span style="color:blue;">SELECT</span> <span style=" font-weight: bold;color:#ff0080;">CONCAT</span><span style="color:maroon;">(</span><span style="color:red;">'RENAME TABLE '</span><span style="color:silver;">,</span><span style="color:maroon;">table_schema</span><span style="color:silver;">,</span><span style="color:red;">'.'</span><span style="color:silver;">,</span><span style="color:maroon;">table_name</span><span style="color:silver;">,</span><br /> <span style="color:red;">' TO '</span><span style="color:silver;">,</span><span style="color:red;">'new_schema.'</span><span style="color:silver;">,</span><span style="color:maroon;">table_name</span><span style="color:silver;">,</span><span style="color:red;">';'</span><span style="color:maroon;">)</span><br /><span style="color:blue;">FROM</span> <span style="color:maroon;">information_schema</span><span style="color:silver;">.</span><span style="color:blue;">TABLES</span><br /><span style="color:blue;">WHERE</span> <span style="color:maroon;">table_schema</span> <span style="color:blue;">LIKE</span> <span style="color:red;">'old_schema'</span><span style="color:silver;">;</span></span></blockquote></blockquote>Run those by copying them from your editor, or whatnot. If you're using SQLyog, CTRL-L switches the results tab to text mode for easy copying.</li><li>Drop the old schema</li></ol><div>Some notes:</div><div>If you're using InnoDB foreign keys, worry not. The constraints are kept across databases, and moving the tables does not affect them - no need to move the tables in any specific order.</div><div>The move command also moves the .idb files between the database directories, if you're using innodb_file_per_table.</div><div><br /></div><div>Hope this helps.</div></div>Unknownnoreply@blogger.com7tag:blogger.com,1999:blog-20434908.post-77609005359820461942009-08-12T12:40:00.002-07:002009-08-12T12:41:20.701-07:00A Free Text Editor for Very Large / Huge Files on WindowsEvery once in a while a programmer finds himself in need of a tool that allows him to edit very large text files. By large, I mean several gigabytes. For DBAs it is common, especially if you’re using MySQL dumps a lot. What do you do if you’re doing this on Windows? <br />
If you’re using <a href="http://notepad-plus.sourceforge.net/" target="_blank">Notepad++</a> or any other <a href="http://www.scintilla.org/" target="_blank">Scintilla</a> derivatives, you’re out of luck - those editors are not cut out for this kind of work. Using Visual Studio also won’t work. There are some partial solutions just for viewing, like <a href="http://www.swiftgear.com/ltfviewer/features.html" target="_blank">LTFViewer</a> – but it cannot handle large files without line breaks, something common in MySQL dumps. So what do you do?<br />
The answer is simple and somewhat unexpected – use <a href="http://www.vim.org/" target="_blank">Vim</a> for Windows. It opens a file of any size effortlessly and almost instantly – then you can browse through the file like it was a small script - <i>it just works</i>. <br />
Few things to notice:<br />
For files with very long lines, like dumps, consider using “set nowrap”.<br />
Another thing is the fact that Vim keeps a temporary copy of the file for backup when saving an edited file, so not only it takes a while to save since it writes the entire buffer to the disk, but you also need to delete the temp file afterwards. To disable this behavior, use “set nobackup”.Unknownnoreply@blogger.com11tag:blogger.com,1999:blog-20434908.post-83255454246201292162008-11-03T05:07:00.007-08:002009-01-29T03:57:30.752-08:00Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005Sometimes, the small things make all the difference. Last month, a friend of mine that works at Intel asked me how to do something seeming easy – concatenate the rows eliminated during a GROUP BY and present them in a column of their own. This is actually something typical when trying to do all sorts of reports, and just makes thing easier for everyone. <br />
The answer? GROUP_CONCAT of course. This could have been easy. Alas, they were working with SQL Server 2005, which does not have such a function. <br />
Now, all of the above isn’t exactly news – there are tons of articles and blog posts about it on the web. Some give you the code for special UDFs and some give you solutions that use cursors. A 3 year old <a href="http://www.xaprb.com/blog/2005/09/28/simulating-the-group-concat-function/" target="_blank">post by Xaprb</a> suggests using local variables.<br />
I suggest a different approach based on a “hack” which utilizes the new xml functions in SQL Server 2005 to concatenate the values in a column. <br />
It goes like this:<br />
<blockquote><code><span style="font-family: Courier New; font-size: 10pt;"><code><span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: maroon;">my_column</span> <span style="color: blue;">AS</span> <span style="color: maroon;">[text()]</span> <br />
<span style="color: blue;">FROM</span> <span style="color: maroon;">my_table</span> <br />
<span style="color: blue;">FOR</span> <span style="color: blue;">XML PATH</span><span style="color: silver;">(</span><span style="color: red;">''</span><span style="color: silver;">)</span></span></code></span></code></blockquote>This gives you the concatenated values of all of the values of this column in the table. From here on, it’s all just simple SQL. I have made two complete examples, in the order I’ve tried them. I’ve used the information_schema tables, since they exists everywhere.<br />
First intuitive version:<br />
<blockquote><code><span style="font-family: Courier New; font-size: 10pt;"><code><span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: maroon;">table_name</span><span style="color: silver;">,</span> <br />
<span style="color: magenta; font-weight: bold;">LEFT</span><span style="color: silver;">(</span><span style="color: maroon;">column_names</span><span style="color: silver;">,</span><span style="color: magenta; font-weight: bold;">LEN</span><span style="color: silver;">(</span><span style="color: maroon;">column_names</span><span style="color: silver;">)</span> <span style="color: silver;">-</span> <span style="color: black;">1</span><span style="color: silver;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">column_names</span> <br />
<span style="color: blue;">FROM</span> <span style="color: silver;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">table_name</span><span style="color: silver;">,</span> <br />
<span style="color: silver;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">column_name</span> <span style="color: silver;">+</span> <span style="color: red;">','</span> <span style="color: blue;">AS</span> <span style="color: maroon;">[text()]</span> <br />
<span style="color: blue;">FROM</span> <span style="color: maroon;">information_schema</span><span style="color: silver;">.</span><span style="color: maroon;">columns</span> <span style="color: blue;">AS</span> <span style="color: maroon;">internal</span> <br />
<span style="color: blue;">WHERE</span> <span style="color: maroon;">internal</span><span style="color: silver;">.</span><span style="color: maroon;">table_name</span> <span style="color: silver;">=</span> <span style="color: maroon;">table_names</span><span style="color: silver;">.</span><span style="color: maroon;">table_name</span> <br />
<span style="color: blue;">FOR</span> <span style="color: blue;">xml</span> <span style="color: blue;">PATH</span> <span style="color: silver;">(</span><span style="color: red;">''</span><span style="color: silver;">)</span> <br />
<span style="color: silver;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">column_names</span> <br />
<span style="color: blue;">FROM</span> <span style="color: silver;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">table_name</span> <br />
<span style="color: blue;">FROM</span> <span style="color: maroon;">information_schema</span><span style="color: silver;">.</span><span style="color: maroon;">columns</span> <br />
<span style="color: blue;">GROUP</span> <span style="color: blue;">BY</span> <span style="color: maroon;">table_name</span><span style="color: silver;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">table_names</span><span style="color: silver;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">pre_trimmed</span><span style="color: silver;">;</span></span></code></span></code></blockquote>Second version (admittedly inspired by <a href="http://anthony-yio.blogspot.com/2007/12/mssql-groupconcat.html" target="_blank">this post</a>, which I stumbled on after writing the first version):<br />
<blockquote><code><span style="font-family: Courier New; font-size: 10pt;"><code><span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: maroon;">table_name</span><span style="color: silver;">,</span> <br />
<span style="color: magenta; font-weight: bold;">LEFT</span><span style="color: silver;">(</span><span style="color: maroon;">column_names</span><span style="color: silver;">,</span><span style="color: magenta; font-weight: bold;">LEN</span><span style="color: silver;">(</span><span style="color: maroon;">column_names</span><span style="color: silver;">)</span> <span style="color: silver;">-</span> <span style="color: black;">1</span><span style="color: silver;">)</span> <span style="color: blue;">AS</span> <span style="color: maroon;">column_names</span> <br />
<span style="color: blue;">FROM</span> <span style="color: maroon;">information_schema</span><span style="color: silver;">.</span><span style="color: maroon;">columns</span> <span style="color: blue;">AS</span> <span style="color: maroon;">extern</span> <br />
<span style="color: blue;">CROSS</span> <span style="color: blue;">APPLY</span> <span style="color: silver;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">column_name</span> <span style="color: silver;">+</span> <span style="color: red;">','</span> <br />
<span style="color: blue;">FROM</span> <span style="color: maroon;">information_schema</span><span style="color: silver;">.</span><span style="color: maroon;">columns</span> <span style="color: blue;">AS</span> <span style="color: maroon;">intern</span> <br />
<span style="color: blue;">WHERE</span> <span style="color: maroon;">extern</span><span style="color: silver;">.</span><span style="color: maroon;">table_name</span> <span style="color: silver;">=</span> <span style="color: maroon;">intern</span><span style="color: silver;">.</span><span style="color: maroon;">table_name</span> <br />
<span style="color: blue;">FOR</span> <span style="color: blue;">XML</span> <span style="color: blue;">PATH</span><span style="color: silver;">(</span><span style="color: red;">''</span><span style="color: silver;">)</span> <br />
<span style="color: silver;">)</span> <span style="color: maroon;">pre_trimmed </span><span style="color: silver;">(</span><span style="color: maroon;">column_names</span><span style="color: silver;">)</span><span style="color: blue;"> <br />
GROUP</span> <span style="color: blue;">BY</span> <span style="color: maroon;">table_name</span><span style="color: silver;">,</span><span style="color: maroon;">column_names</span><span style="color: silver;">;</span></span></code></span></code></blockquote>The second CROSS APPLY version is supposedly slower according to the execution plan, but I didn’t conduct any benchmarks at all.<br />
<br />
I hope this saves someone several hours of work :)Unknownnoreply@blogger.com9tag:blogger.com,1999:blog-20434908.post-44605262423388623692008-10-19T07:19:00.004-07:002008-10-28T03:25:38.969-07:00Problems Uninstalling MySQL Connector .NETThis is a short post that might save someone some valuable time, if Google decides to rank it high enough.<br /><br />I've tried to install a newer version of the MySQL Connector .NET, namely 5.2.3 instead of the old 5.1.3 I had installed.<br />When trying to install 5.2.3, I got this error message:<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEX3uwkDfZuVhmY1OEOfPNvYvCzDpgrosJtvJmsV-WgxpiE8ykUqzBVUtYvi9f_wAhWS5FsIf5-wWh4UcgG1ZvC2LdhQB5_6LcUUa3NAZWjHD4nHwPi7BKbCOi8f9ngxwlF9I0/s1600-h/Untitled.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbXKdeHqrdHwnYCKFj8Pmzp55tCpq8WlIEVbwgXmRUSJnqrdEe2ZiXKH2xKSm-qNosZzPn8mVo9J-PHLMpCCEaUmH0PrqCrf-s4PGNytFGZQY7VUZNmt1OEwElehK5he93kwq8/s400-r/Untitled.jpg" border="0" /></a></div><br />Apparently the connector does not support upgrades from 5.1.x to 5.2.x. We should just remove the old one.<br /><br />Here lies the problem: when I tried removing the old 5.1.3, I got a weird error <strike>of which I took no screenshot</strike>. It consisted of a <b>blank</b> error message showing a computer screen with a icon of a moon on it. Something resembling a "sleep mode" icon. Huh?<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEic8lo21n5rTwMlIVDSwIeq20xcItyHZqmDQTvTM8n6YtlGlelJh_kxjGnSom7qZ34G7DJCgbsF_j6EmIiJEbW-vwgjPAG0hR6242vQJIZ2VOlonDszXI-U-mDkwKM-40OzNJwN/s1600-h/screenshot+for+shlomo.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3IWMQ__AzXCf-KP9Du-RkATclOXMuKC8SCHggRjf8s1BqcWbmw1ksEwnjUoDu8X6CYwKpEmBLOoo009ldWRlRJQCg8lgY_Y1CWMIS_gplyvkkKPJIXqa5mcsRFBiO3Sp3jfND/s400-r/screenshot+for+shlomo.jpg" border="0" /></a></div><br />Anyway, to make a long story short, nothing I did to remove the 5.1.3 <i>gracefully </i>worked. I tried using the old .MSI file, tried reinstalling, changing the install configuration, editing registry keys... I also tried using <a href="http://msdn.microsoft.com/en-us/library/aa370523.aspx">msizap.exe</a>. Nothing worked.<br /><br />The solution was to use <a href="http://support.microsoft.com/default.aspx?scid=kb;en-us;290301">this</a> tool from Microsoft - called "Windows Installer CleanUp Utility". It's supposed to be a wrapper for msizap, but it did the job, while msizap did not.<br /><br />Regardless of MySQL, this is a good tool to be familiar with. It removed 3 other entries in my "Programs and Features" list that refused to leave by themselves.<br /><br /><span style="font-weight: bold;">Update:</span> I've had to solve this problem a few more times since posting. As it seems, this "Windows Installer CleanUp Utility" is basically a wrapper for msizap, so it probably used some other parameters that made it work. In addition, I've encountered a time where none of the tools worked, but a registry "Seek & Destroy" for "MySQL Connector" did the job. Go figure...Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-20434908.post-5173306490013185962008-07-25T09:19:00.021-07:002008-08-06T08:35:06.428-07:00Recommendation: PuTTY Connection Manager<b>Linux desktop users: </b>this post is probably not for you. You've got like a gazillion of these tools available.<br />
<br />
<b>Windows users: </b>If you're like me, and use PuTTY all the time to manage your Linux MySQL servers, you'll appreciate <a href="http://puttycm.free.fr/">this</a> gem. It's called PuTTY Connection Manager and it, obviously, manages PuTTY connections. Not a very creative name, but a fine piece of software. <br />
<br />
It's actively developed (latest alpha version out about two months ago), and boasts these features (taken from the <a href="http://puttycm.free.fr/">developer website</a>):<br />
<ul><li><b>Tabs and dockable windows </b>for PuTTY instances.</li>
<li>Fully compatible with PuTTY configuration (using registry).</li>
<li>Easily customizable to optimize workspace (fullscreen, minimze to tray, add/remove toolbar, etc...).</li>
<li><b>Automatic login feature</b> regardless to protocol restrictions (user keyboard simulation).</li>
<li>Post-login commands (execute any shell command when logged).</li>
<li> <b>Connection Manager</b> : Manage a large number of connections with specific configuration (auto-login, specific PuTTY Session, post-command, etc...).</li>
<li><b>Quick connect</b> toolbar to quickly launch a PuTTY connection.</li>
<li><b>Import/Export</b> whole connections informations to XML format (generate your configuration automatically from another tool and import it, or export your configuration for backup purpose).</li>
<li><b>Encrypted configuration database</b> option available to store connections informations safely (external library supporting AES algorithm used with key sizes of 128, 192 and 256 bits, please refer for the legal status of encryption software in your country).</li>
<li><b>Standalone executable</b>, no setup required.</li>
<li><b>Completely free for commercial and personal use</b> : PuTTY Connection Manager is freeware.</li>
</ul><b> Things that I personally liked about it:</b><br />
<ol><li>It's fast, since it's only a reasonably lightweight wrapper for the already fast PuTTY.</li>
<li>It can save a portable connection database in a file, unlike PuTTY, which uses the registry for it's connections.<br />
</li>
<li>It looks and feels like the Visual Studio window manager, so you can arrange and dock your PuTTY windows in all sorts of creative rectangular ways.</li>
<li>It is relatively feature rich, but it doesn't get in your way, and just works most of the time.</li>
<li>It's <i>completely free</i>.<br />
</li>
</ol>Get it <a href="http://puttycm.free.fr/">here</a>.<br />
<br />
<b>Alternatives:</b><br />
<br />
If you don't like it, there is another alternative that I personally like less for SSH-only work, which is called <a href="http://www.mremote.org/">mRemote</a>. It is much more advanced, has support for <a href="http://en.wikipedia.org/wiki/Vnc">VNC</a> and <a href="http://en.wikipedia.org/wiki/Remote_Desktop_Protocol">RDP</a>, but I prefer the simpler and more flexible interface of PuTTY Connection Manager.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-19045925808568036942008-06-17T11:17:00.003-07:002008-06-17T11:25:15.285-07:00How to Truncate All or Some of the Tables in a MySQL Database<p>Truncating all tables in a database is a common problem which arises a lot during testing or debugging. <br />One of the most common answers to this question is to drop & recreate the database, most likely utilizing the shell. For example, something like this:</p> <blockquote><code>mysqldump --add-drop-table --no-data [dbname] | mysql [dbname]</code> </blockquote> <p>This dumps the entire schema structure to disk, without dumping any data, and with commands for dropping existing tables. When loading it back into mysql, it essentially truncates all the tables in the database. Basically, this is a decent solution for many uses.</p> <p>We had a requirement for a solution that needed these additional features:</p> <ol> <li>Does not require shell (We work with both Linux and Windows) </li> <li>Resides inside the MySQL Server (To minimize outside dependencies - for example - the mysql command line client) </li> <li>Can truncate only specified tables using a some sort of filter </li> </ol> <p>In other words, the main requirement here is encapsulation and simplicity of use for the developers.<br />Basically, this means I've got no other choice except writing a stored procedure for this purpose, ugly as it may be. Specifically, this will require using the pseudo-dynamic SQL in MySQL (more about this later) <em>and</em> a server-side cursor.</p> <p>The code of the Stored Procedure:</p> <blockquote> <pre><code>DELIMITER $$<br />CREATE PROCEDURE TruncateTables()<br />BEGIN<br />DECLARE done BOOL DEFAULT FALSE;<br />DECLARE truncate_command VARCHAR(512);<br />DECLARE truncate_cur<br /> CURSOR FOR /*This is the query which selects the tables we want to truncate*/<br /> SELECT CONCAT('TRUNCATE TABLE ',table_name)<br /> FROM INFORMATION_SCHEMA.TABLES<br /> WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'prefix_%';<br />DECLARE<br /> CONTINUE HANDLER FOR<br /> SQLSTATE '02000'<br /> SET done = TRUE;<br /><br />OPEN truncate_cur;<br /><br />truncate_loop: LOOP<br /> FETCH truncate_cur INTO truncate_command;<br /> SET @truncate_command = truncate_command;<br /><br /> IF done THEN<br /> CLOSE truncate_cur;<br /> LEAVE truncate_loop;<br /> END IF;<br /><br /> /*Main part - preparing and executing the statement*/<br /> PREPARE truncate_command_stmt FROM @truncate_command;<br /> EXECUTE truncate_command_stmt;<br /><br />END LOOP;<br />END$$<br /><br />DELIMITER ;</code></pre></blockquote><p>This is a generalized version, you can (and should) modify it to suit your needs.</p><p>Regarding dynamic SQL. Initially I've tried to make this query a lot simpler, and use something along the lines of the <a href="http://blog.shlomoid.com/2008/06/how-to-perform-connection-massacre.html" target="_blank">previous blog post</a>. This would look something like:</p><blockquote><pre><code>DELIMITER $$<br />CREATE PROCEDURE TruncateTables()<br />BEGIN /*Build a long user variable containing the varchars*/<br /> SELECT GROUP_CONCAT('TRUNCATE TABLE ',table_name SEPARATOR ';')<br /> INTO @truncate_command<br /> FROM INFORMATION_SCHEMA.TABLES<br /> WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'prefix_%';<br /><br /> SET @truncate_command = CONCAT(@truncate_command,';');<br /><br /> PREPARE truncate_command_stmt FROM @truncate_command;<br /> EXECUTE truncate_command_stmt;<br /><br />END$$<br /><br />DELIMITER ;</code></pre></blockquote><p>The main (and unsolvable) problem with this code is that the PREPARE cannot prepare more than one statement. In other words, while in other databases you could have sent an entire batch (real dynamic SQL), in MySQL the only way to do this is to run statement by statement. On the other hand, there is no GROUP_CONCAT() in other databases (even though it can apparently be <a href="http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html#c113634158846161647" target="_blank">created with ease</a> in PostgreSQL). </p><p>The second and less worrying problem is the fact that by default, GROUP_CONCAT() result tends to be limited to a certain length. This is easily solved by setting <code>group_concat_max_len</code> to something higher than default.</p><em>End Transmission.</em>Unknownnoreply@blogger.com11tag:blogger.com,1999:blog-20434908.post-22776766149145609212008-06-12T06:35:00.009-07:002008-06-17T05:57:22.204-07:00How To Perform a Connection Massacre<p>Occasionally, you find yourself in the need to kill problematic connections to the database.<br />Usually if it's only one or two connections, you can use the combination of "SHOW PROCESSLIST" command to identify the problematic connection ID, and run a "KILL ID" command. </p> <p>What do you do if you need to kill 10 connections? Or 56? I wouldn't want to type in all those kill commands, it's just dirty work. What we need is a more <a href="http://en.wikipedia.org/wiki/Dexter_%28TV_series%29" target="_blank">neat</a> manner to perform those kills. Mass kill, if you wish.</p> <p>Alternative way: use the INFORMATION_SCHEMA's PROCESSLIST table, to construct the kill statements semi-automatically.</p><blockquote><code>SELECT CONCAT('kill ',id,';') AS kill_list<br />FROM INFORMATION_SCHEMA.PROCESSLIST<br />WHERE command='Sleep';<br /></code></blockquote> <p>This select will return something like this (when using the command line client):<br /></p><blockquote><pre>+-----------+<br />| kill_list |<br />+-----------+<br />| kill 28; |<br />| kill 1; |<br />+-----------+<br />2 rows in set (0.04 sec)<br /></pre></blockquote> <p>You can also use GROUP_CONCAT() to get the kill commands in the same line, which may be more useful when you can't copy&paste easily:</p> <blockquote><code>SELECT GROUP_CONCAT('kill ',id SEPARATOR '; ') AS kill_list<br />FROM INFORMATION_SCHEMA.PROCESSLIST<br />WHERE command='Sleep';</code></blockquote> <p>Returns:<br /></p><blockquote><pre>+------------------+<br />| kill_list |<br />+------------------+<br />| kill 28;,kill 1; |<br />+------------------+<br />1 row in set (0.03 sec)</pre></blockquote> <p>Note, that you can use any filter in the WHERE clause, such as "WHERE db IN('dbname1','dbname2')", "WHERE user = 'appuser'" or "WHERE time>600". If you got any more clever uses, feel free to post them in the comments. </p> <p>Now, all needed is to copy&paste those kill commands into the mysql command console (whichever you're using: GUI, command line, etc) and you're done.</p> <p>Since most programs are kind enough to format the output in some way that prevents convenient copying/pasting, here's another tip. The mysql command line client can be asked to strip some of the output by using the "-s" optional parameter, which stands for "silent". You can use it once, and remove the ASCII art, timing and row count, and if you use it again (-s -s), you only get the actual rows.</p> <p>An example doing just this (copy&paste friendly!):</p> <blockquote><code>mysql -s -s -e "SELECT CONCAT('kill ',id,';') AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';"</code></blockquote> <p>Gets you this (with different numbers of course):</p> <blockquote><code>kill 28;<br />kill 1;</code></blockquote> <p>Another way to do this, is to use SQLyog or any other <a href="http://blog.shlomoid.com/2007/12/mysql-development-tools-good-bad-and.html" target="_blank">GUI for MySQL</a>, which can output the results in text. Most of them have this as a configurable option (Ctrl-L keyboard shortcut in SQLyog).</p> <p>Hope this is will save someone some precious time. </p><span style="font-weight: bold;">Update:</span><br />I didn't include a suggestion on doing this automatically, since I personally prefer to run kill commands manually most of the time. Plus, some of the comments gave me an idea. Why not just pipe the stripped kill_list into mysql?<br /><blockquote><code>mysql -s -s -e "SELECT </code><code>GROUP_CONCAT('kill ',id SEPARATOR '; ')</code><code> AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';" | mysql</code></blockquote>Unknownnoreply@blogger.com9tag:blogger.com,1999:blog-20434908.post-23180068976285141602008-05-31T11:46:00.001-07:002008-05-31T11:46:33.300-07:00Taking Cache Warm-up Time Into Consideration<p>Last week there there was a short scheduled downtime for the system, and I've used the chance to upgrade the MySQL servers to a newer version. We've tested it for more than a month before deploying to production, so I wasn't worried about any potential problems. </p> <p>The upgrade itself went smoothly and was complete in about less than 10 minutes. Speaking of which, this is one of the things I like in MySQL upgrades as opposed to SQL Server. </p> <ol> <li>Copy new version</li> <li>Shut down the server </li> <li>Rename directories </li> <li>Start server </li> <li>Run mysql_upgrade </li> <li>Restart server</li> </ol> <p>All of the above usually takes about 2 minutes, and can be reverted quite easily, if you have backup for the data files. This is compared to a SQL Server upgrade, or even a Service Pack install which includes running an installer for what can be 20 minutes or more. Uninstalling is equality lengthy, though both are accompanied by nice and useless progress bars.</p> <p>After the upgrade was complete, we started the application and <em>it wouldn't work </em>- it was reporting timeouts. Why? Was there a problem with the upgrade? Some bug we haven't found out about? The circumstantial evidence pointed directly to the upgrade as the problem. After some digging by <a href="http://www.pashabitz.com" target="_blank">Pasha</a>, the problematic query was found - it was a group by query updating some counter table. It ran in under 5 seconds before, but now it just wouldn't finish in the 30sec timeframe before timing out.</p> <p>This immediately pointer to the real cause of the problem: the <i>server restart</i>. The buffer cache was flushed, and the index scanned by this query was not in memory. Fixing was easy, I ran the problematic query manually (it took almost 2 minutes to run). After this, the application worked again. Problem solved? Not really.</p> <p>Queries of this sort should almost <strong>never </strong>exist in a system. This specific query was long since been noticed due to proactive slow-log monitoring. This problem was (ironically) fixed on the day of this incident, but the newer version was not yet deployed.</p> <p><strong>Conclusion</strong>: Think of how your application works with a cold database cache. If you need to warm it up, do so. A better path is to design the database more efficiently and avoid certain types of queries. In this case, the counter table was now updated on batches of inserts and deletes - instead of periodically.</p> Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-20434908.post-23712026158158993822008-04-26T06:03:00.002-07:002008-04-27T01:17:56.729-07:00Using the MySQL Event Scheduler to Emulate Threads<p>The MySQL Event Schedule is one of the new features in MySQL 5.1. It is explained well in <a href="http://dev.mysql.com/tech-resources/articles/event-feature.html" target="_blank">this article</a> from the MySQL site, and in <a href="http://dev.mysql.com/doc/refman/5.1/en/events.html" target="_blank">the manual</a> itself. I'd like to demonstrate an interesting way to use it. What gave me this idea is actually a feature in SQL Server 2005, called <a href="http://msdn2.microsoft.com/en-us/library/ms345108.aspx" target="_blank">Service Broker</a>. It's a sort of queue manager / messaging platform, which can also be exploited in the same way. </p> <p>Lets say you are writing a stored procedure, that loads data into the database. Benchmarks show that loading data in parallel from several threads increases overall throughput, as MySQL is unable to utilize several CPUs to process a single SELECT query, moreover a bulk load operation. To work in parallel, you'd probably need to write a script that uses threads, open several connections to the database, and perform those queries in parallel.</p> <p>I propose an alternative.</p> <p>The MySQL Event Scheduler is capable of performing several tasks at once, since it's highly probable that several events might need to run at the same time. Why not use it? <br />What we will create, is a "self-destructible" event, that performs a task once and disappears. The syntax that allows this is self explanatory:</p> <blockquote> <p>CREATE EVENT IF NOT EXISTS event_name ON SCHEDULE AT NOW() ON COMPLETION NOT PRESERVE <br />DO statement_or_batch;</p> </blockquote> <p>I don't think this is the place to descend into the implementation detail of using it for data loads, so I won't. Note, however, how flexible it is.</p> <ol> <li>You could use the event scheduler to perform several SELECT statements in parallel into some summary or union table, and collect the returned result afterwards.</li> <li>To see if an event is still running, you can poll the mysql.event system table for the event name, or names (when waiting for several events to complete).</li> <li>Combine it with the <a href="http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html" target="_blank">FEDERATED engine</a>. That is one feature which is seriously in need of more development efforts. It is not even included in 5.1.24 due to severe problems. When it goes back to a stable status, you could use events to execute remote queries in your cluster in parallel - with a single command, from a single server.</li> <li>Use it for an unordered set of commands that your application shouldn't wait for at all, or it doesn't care when it gets done. </li> <li>Use it to delay execution, just replace NOW() with NOW() + INTERVAL 5 MINUTE, as <a href="http://blog.shlomoid.com/2008/02/mysql-date-and-time-arithmetic.html" target="_blank">demonstrated earlier</a>.</li> </ol> <p>The possibilities are endless. Well, not <em>endless</em>, but I'm sure that the event schedule can be tricked into doing a lot more cool things than these... If you can think of anything of that sort, I'd love to hear it.</p> <p>Some points:</p> <ol> <li>As the mysql.events table is using the MyISAM engine, I'm not sure how transactional adding an event is. Most likely, <em>it isn't</em>.</li> <li>If you want to test the scheduler, make sure it's turned on (SET GLOBAL EVENT_SCHEDULER = ON).</li><li>Each event run adds two lines to the error log (hostname.err) file reporting start and end of the event. This might be a problem similar to leaving the general log on, when using events extensively.<br /></li> </ol>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-20434908.post-80499836143518873022008-03-20T09:48:00.001-07:002008-03-20T09:48:33.398-07:00Two MySQL Monitoring Tools and a Hidden Knowledge Base<p>This post is incomplete without mentioning the <a href="http://blog.shlomoid.com/2007/12/mysql-development-tools-good-bad-and.html" target="_blank">previous one</a> about development tools, so make sure you read it before/after this. <br />In this post, I'd like to mention two monitoring tools, and one knowledge base.</p> <p>Two of the tools are made by <a href="http://www.quest.com/" target="_blank">Quest Software</a>/<a href="http://www.toadsoft.com/" target="_blank">ToadSoft</a> that make Toad for MySQL. Both of them are Freeware. <br />A side note: TOAD was born as "Tool for Oracle Application Developers". Because "Tool for Oracle Application Developers for MySQL" sounds weird, TOAD is known officially as "Tool For Application Developers".</p> <p><strong><a href="http://www.quest.com/spotlight-on-mysql/" target="_blank">Spotlight for MySQL</a></strong> <br />Spotlight is a tool perhaps more known among SQL Server and Oracle users. It shows you the entire flow of data throughout your database in a nicely laid out executive-summary kind of way. It has lots of colors and it's impressive to look at. When it works. Seriously, I've had a lot of trouble with it, to make it work the way it should too many times. It's also slow as hell, and tends to hang or crash, like other tools by Quest. But, when it does work - it's very nice. Quest released this tool for MySQL as well, and since it's free it doesn't hurt to try it out. </p> <p><strong><a href="http://www.quest.com/knowledge-xpert-for-mysql/" target="_blank">Knowledge Xpert for MySQL</a> <br /></strong>This is basically a well laid-out knowledge base, of what Quest Software thinks you should know about MySQL. Full of useful examples, and clear explanations for things that the MySQL manual doesn't mention. I recommend installing it and taking a look, you might find something interesting. It was helpful to me when I started learning MySQL. <br /><em>Warning:</em> practically this is just one big help file, but Quest decided to bundle an annoying interface, a slow installer and one of those load-on-startup-take-memory-do-nothing-system-tray-icon which you need to get rid off after installation.</p> <p>The next one is made by <a href="http://www.webyog.com/" target="_blank">Webyog</a>, (the same guys that make SQLyog which I mentioned <a href="http://blog.shlomoid.com/2007/12/mysql-development-tools-good-bad-and.html" target="_blank">before</a>).</p> <p><strong><a href="http://www.webyog.com/" target="_blank">MONyog</a> <br /></strong>MONyog is a small program with a web front-end which can monitor several MySQL servers and supports some customizable metrics. I won't go into detail on the good and bad things in MONyog here (I plan to write a review of it later on), but I do suggest you try the trial version. MONyog is not free, and doesn't have a free or community "lite" version. <br />At the time of writing the latest stable version is 1.52, but 2.0 beta 2 is already available and brings additional features like historical reports and better graphs.</p> Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-20434908.post-84219469423852610842008-03-09T00:22:00.003-08:002008-03-09T00:35:55.551-08:00SQL Server Data Services Coming SoonMicrosoft decided to jump into <a href="http://blog.shlomoid.com/2007/12/amazon-announced-simpledb.html">Amazon's territory</a> and provide a <a href="http://www.microsoft.com/sql/dataservices/default.mspx">data storage web service</a> accessible from anywhere on the internet.<br />It's not live yet, and you have to register to see the beta when it comes - but it looks interesting.<br /><br />Take a look at the <a href="http://www.microsoft.com/sql/dataservices/faq.mspx">FAQ</a>. Looks interesting. Note the following points (taken from the FAQ):<br /><ul><li>Benefit: Scale, with virtually no restriction on storage</li><li>Target Customers: Running applications that have limited or batched data access.</li><li>Typical scenarios include business solutions like HR services, healthcare records management, data archiving and internet facing applications like social networking and picture sharing.</li><li>The SQL Server Data Storage Service can store multiple types of data, from birth to archival. Users can upload and query structured data, semi-structured data, stored in flexible entities, and unstructured data. Customers will be able to associate entities with large unstructured data objects (blobs) which could be accessed as URL addressable resources.</li><li>Data is stored in large storage clusters in various Microsoft data centers located across North America. We are planning to offer the service from international locations such as Europe and Asia. Users can group their data into authorities, which are affiliated with specific data centers and therefore provide control over the geographic location of the data.</li><li>Easy access with REST and SOAP protocols</li></ul>There is not much information available about it yet, so I'll leave my speculations for later.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-20434908.post-30345323623343998832008-03-07T07:33:00.008-08:002008-03-08T03:01:29.313-08:00Formatting Large Volumes Using FAT32<span style="font-weight: bold;">First, some background </span><span>(Executive Summary below)</span><br /><br />During this weekend I purchased a <a href="http://www.wdc.com/en/products/Products.asp?DriveID=357">Western Digital My Book Home Edition 500GB</a>. This is the triple-interface model that includes FireWire 400 and eSATA in addition to USB 2.0.<br />I wanted the FireWire model since it's faster than USB 2.0, and connecting a FireWire drive in a Mac is the same as connecting a USB one. Just perform these two easy steps - connect cable, wait 3 seconds.<br />Anyway, this is just a prelude to the story, since this drive came pre-formatted with FAT32 to suit both Mac and Windows computers, so I didn't have to format it. Western Digital also sells some drives pre-formatted with HFS+ and some with NTFS, by the way.<br /><br />The next thing I wanted to do after setting up the My Book was to format my old 60GB NTFS external drive using FAT32, so that I could use it with both computers.<br />I copied it's contents over to the 500GB drive (OS X has read-only access to mounted NTFS volumes) and plugged it into a Vista machine to format it as one big FAT32 volume.<br />This is when I found out that Windows Disk Management allows you to format drives larger than 32GB using <span style="font-weight: bold;">only</span> NTFS.<br />So I can <span style="font-style: italic;">use</span> a 500GB FAT32 drive in Windows, but I can't format a 60GB one. This also means by the way, that if you re-partition a 500GB drive you bought, and you don't know how to bypass this limitation, you're basically doomed to creating ~15 32GB partitions to use your drive, unless of course you use NTFS.<br /><br />I've done my research+trial+error and eventually used my Macintosh to format the drive for Windows. Sort of poetic justice.<br />The 500GB monster eventually got converted to HFS+ since it's about 2 times faster in writes compared to FAT32. This is no official benchmark: I just monitored the HD read/write sustained speeds while copying files during setup.<br /><br /><span style="font-weight: bold;">Executive Summary</span><br /><br />Windows XP/2000/Vista <a href="http://en.wikipedia.org/wiki/File_Allocation_Table#FAT32">don't allow creating FAT32 partitions larger than 32GB</a>. This is to promote NTFS, and because FAT32 becomes slower as volume size increases. Reading/Writing of any volume size is possible.<br /><br />If you want to format a drive larger than 32GB with FAT32, you have the following choices:<br /><ol><li>Run the Windows XP installer from the CD - just make sure you quit it in time just after "preparing" the drive for installation.</li><li>Use any Linux bootable CD - fdisk can do it. If you can boot into Linux, you can also Google for instructions about using fdisk so I'll stop here.<br /> </li><li>Use the free <a href="http://www.ridgecrop.demon.co.uk/index.htm?fat32format.htm">FAT32 Formatter</a>. Small windows utility that does what its name implies. I didn't use it myself, but it should do the trick.<br /></li><li>Use your Mac. The Mac "Disk Utility" can format FAT32 of any size.<br />Open Disk Utility, select the volume, click erase, and select "MS-DOS (FAT)" as the file system.<br />Leopard also includes the fdisk utility so you can use that one if you are some kind of a masochist.</li></ol>I didn't want to include detailed instructions, since I assume people should know how to use Google and common sense, but if someone has questions about anything, the comments are open as always.<br /><br />P.S. I published the previous post by a clicking mistake, deleted it right away, checked that it wasn't there - only to find out that blogger entered 3 identical posts, marked 2 as drafts and one got published anyway.Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-20434908.post-30077457262382721542008-03-06T00:42:00.003-08:002008-03-06T00:56:55.393-08:00Misleading Error Message When Running 32bit Applications on 64bit Ubuntu/Linux<p><strong>Short version (aka, Executive Summary)</strong></p> <p>If you try to run a 32bit program under 64bit Ubuntu, there is a chance you might see this misleading error message:</p> <blockquote> <p>-bash: ./executable_filename: No such file or directory</p> </blockquote> <p>What the shell is <em>actually</em> trying to say is "You can't run 32 bit applications here without installing support for 32 applications!".</p> <p><strong>Longer version</strong></p> <p>We purchased the <a href="http://www.webyog.com/" target="_blank">Webyog's</a> MONyog MySQL monitoring tool, after some trial version testing in a Windows environment. When trying to run it on one of our Ubuntu servers, I got this:</p> <blockquote> <p>myuser@myserver:~/MONyog/bin$ ./MONyog-bin <br />-bash: ./MONyog-bin: No such file or directory</p> </blockquote> <p>I checked again: the file was there, permissions were okay. Hmm. Why was bash saying the file wasn't there? Here it is! It's not like the error came from the application, it was bash itself saying that it's not found. <br />I googled for a while till I opened a support ticket at Webyog, and this is how I found out the real reason. The solution is to install support for 32 bit applications which is package called <a href="http://packages.ubuntu.com/feisty/libs/ia32-libs-sdl" target="_blank">ia32-libs-sdl</a> (which also installs a number of prerequisites). Once it's installed, the program works.</p> <p>I'll use this opportunity to praise Webyog's support, which was fast and to the point. I got my initial reply with an explanation after few hours, and the solution the next day - without having to chase them for it.</p>Unknownnoreply@blogger.com1