User: swanhart Date: 2008-05-13 21:00 Subject: Using a DELETE w/ a JOIN and LIMITing the number of rows deleted. Security: Public Tags: delete, mysql, session, variables, workaround

I've come up with some interesting workarounds for missing features using @session variables and I'd like to share one with you today: DELETE ... JOIN ... LIMIT N;

okay, so we've got two tables, one with many duplicates and one with no duplicates.
We want to join the tables using 'id', but only want to delete N duplicates from t1.
MySQL's DELETE does not include LIMIT support when JOIN is used, so we need to work around
that using some fancy footwork:


mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
8 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | xyz |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)

mysql> set @limit=2; set @rowCount=0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE t1.*
-> FROM t1
-> JOIN t2 on (t1.id = t2.id)
-> WHERE t2.id = 1
-> AND CONCAT(t1.id, t1.abc, @rowCount +1)
-> = CONCAT(t1.id, t1.abc,
-> CASE @rowCount
-> WHEN @LIMIT THEN @rowCount:=NULL
-> ELSE @rowCount := @rowCount+1
-> END
-> );
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
6 rows in set (0.00 sec)

how it works:
When MySQL first evaluates the expression, @rowCount is zero. The CONCAT in the where clause concatenates all the values given together, so at first we get CONCAT(1, 1, 1) = CONCAT(1, 1, 1). The next time through, we get CONCAT(1, 1, 2) = CONCAT(1, 1, 2). At the third execution we get CONCAT(1, 1, 3) = CONCAT(1, 1, NULL), which does not evaluate true. At this time @rowCount is now NULL, so subsequent scanned rows evaluate to
CONCAT(1, 1, NULL) = CONCAT(1, 1, NULL), which can never evaluate true (NULL != NULL).

So, what interesting ways have you used @session variables to come up with solutions to intractable problems?

User: tcpip Date: 2008-05-14 13:09 Subject: Horror (Fictional and Real), Nasty Conservatives, Gaming Updates Security: Public

Recently had the opportunity at Splodge with caseopaya, severina_242, _zombiemonkey, and cvisors to see the classic Hammer film Horror of Dracula (with Peter Cushing and Christopher Lee). A good bit of cheesy horror (the working class Londoner accent complete with Gordon's gin at a Kluj inn would horrify Transylvanians). Later in the week watched the classic Vincent Price flick House on Haunted Hill which was quite clever and finally, wonder of wonders, found the memory card for the Playstation 2 which had been missing for several months and contained four hours of a saved game for Project Zero, certainly one of the best horror games that has ever been produced.

In real world horrors, there has been both the earthquake in Chengdu and a rising death toll (approximately 10,000 mostly in Mianyang) and the effects of Cyclone Nargis in Burma, where the number of dead or missing is over 100,000. The military junta that rules Burma deserves a special award for disgusting behaviour for not only blocking aid workers, but for also holding a particularly rigged constitutional referendum days after the event. An interesting comparison can be drawn between how the contemporary Chinese government is reacting to the disaster compared to their government during the Tangshan earthquake which killed approximately a quarter of a million. Communications technology is a bedrock of an open society.

On topic, there was the kerfuffle over a local Liberal Party 'blog, managed and operated by insiders, criticising leader Ted Baillieu. Although now closed it has been mirrored with history all the way to December. Not only is it amazing to think that it took that long to go through the IP logs, but in the fall-out the conservative's hatred of Jews is once again exposed. An isolated incident? Perhaps not - consider just prior Republican Congress hopefull Tony Zirkle (who, not surprisingly campaigns primarily against pornography and prostitution) decided to attend an Adolph Hitler birthday celebration sponsored by the American Nazi Party. If one was to give a broad summary it would seem that "the left" has a problem with Israel, whereas the "the right" has a problem with Jews.

Apropos there recently has been "some discussion" over an online Harry Potter RPG which called itself "Kristallnacht", which has since changed its name. Personally, I thought it was a great name for the story with enormous potential for thematic references and an oppurtunity to teach people some history via analogy (which is one of fantasy's better uses). In my own gaming worlds, I am pleased to see that a DragonQuest PBeM I am involved in has just restarted, had a good session of Bushido last Sunday and most recently I've started a Powers & Perils PBeM which has developed very quickly. Finally, one of the players in my HeroQuest PBeM has culled the archives into a story representation; it currently runs at 185 pages.

User: krow Date: 2008-05-13 14:42 Subject: Fedora, MySQL DBD, Finally Security: Public

From the release notes:
http://docs.fedoraproject.org/release-notes/f9/en_US/sn-DatabaseServers.html#sn-MySQL-DBD


The MySQL DBD driver has been dual-licensed and the related licensing issues have been resolved (https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=222237). The resulting apr-util-mysql package is now included in the Fedora software repositories.


I had wondered if they would ever get that in.

User: swanhart Date: 2008-05-12 15:17 Subject: Tools to generate large synthetic data sets for testing? Security: Public Tags: data warehousing, dataset, mysql, testing

I need to generate large (1TB-3TB) synthetic MySQL datasets for testing, with a number of requirements:

a) custom output formatting (SQL, CSV, fixed-len row, etc)
b) referential integrity support (ie, child tables should reference PK values, no orphans,etc)
c) able to generate multiple tables in parallel
d) preferably able to operate without a GUI and/or manual intervention
e) uses a well defined templating construct for data generation
f) preferably open source

Does anyone out there know of a product that meets at least most of these requirements?

*edit*
I found a PHP based data generation script (www.generatedata.com) that is extensible in its output formatting, so it should do everything I need it to do.

User: awfief Date: 2008-05-10 15:55 Subject: Damned if you do, damned if you don't..... Security: Public

After 2 elections where we got pissed that the Democratic candidate conceded to Bush way too early, and didn't challenge the results when we so righteously wanted him to....

Why are we all pissed that Hillary's staying on? Oh sure, I think she's lost it (pun/double meaning intended) but I have to say that she's got a tough backbone, and that's admirable and novel.

User: awfief Date: 2008-05-10 15:42 Subject: Mostly for my brothers.... Security: Public

Driving home from Providence at 1:30 in the morning, I came upon a radio station playing FunkyTown.

It took all I had not to call my father at that precise moment and blast the radio exceptionally loud.

Even more bizarrely, when the song finished I channel-surfed among my 6 preset stations, and the very next song I came across was Michael Jackson's "Bad".

My restraint is extraordinary.

User: swanhart Date: 2008-05-09 15:53 Subject: Kickfire makes it easy to be green (and to save some too)! Security: Public Tags: appliance, green, kdb, kickfire, sql, work

Kickfire has announced (as of April 14th, 2008) record breaking results in the TPC-H(tm) Price/Performance category at 300GB and also in overall performance in the non-clustered category at 300GB.
You can find the official results here on the TPC(tm) (Transaction Processing Performance Council) website:
http://www.tpc.org/tpch/results/tpch_price_perf_results.asp
http://www.tpc.org/tpch/results/tpch_price_perf_results.asp?resulttype=noncluster

While the amazingly low price of the Kickfire Database Appliance 2400 will grab you (only about twice the price of a typical 4U MySQL database server) -- the amazing performance per watt is truly incredible. The 3RU Kickfire appliance used in the 300GB volume test consumes just 3RU of rack space and a measly 650watts of power.

To give an appropriate comparison, my home gaming PC contains an 800W power supply. Compared to other high performance database systems (comparison by wattage, not necessarily performance) that use tens (even hundreds) of thousands of watts, the Kickfire appliance is orders of magnitude more environmentally friendly.

Not only does it reduce the amount of physical space required, but it also reduces the costs of environmental control (cooling). It uses less manufacturing resources, is more portable between data centers and it requires less excess inventory (like spare hard disks) to maintain, all of which result in significant cost of maintenance savings.

This 'green' aspect is one of the major factors in my decision to join Kickfire, and I'm proud to be working for a company making such huge strides toward sustainable computing practices.

Full disclosure: I am a paid employee of Kickfire, Inc. The opinions expressed in my personal blog posts are my own and may not reflect those of the company. All facts and figures may be validated via the TPC website, and this blog does not represent an official posting of TPC-H data, or any other TPC related information.

Kickfire is a trademark of Kickfire, Inc.
TPCH, QphH and $/QphH are trademarks of the TPC. For additional information on the TPCH benchmark, please visit the Transaction Processing Performance Council's Web site at http://www.tpc.org/.

User: krow Date: 2008-05-09 11:17 Subject: Proxy, Proxy, on the Wall, who is the fairest of them all... Security: Public

Dear Lazyweb,

I am tracking three four five lots of different proxies for MySQL today:

http://forge.mysql.com/wiki/MySQL_Proxy
http://consoleninja.net/code/dpm/README.html
http://spockproxy.sourceforge.net/
http://sqlrelay.sourceforge.net/
http://myosotis.continuent.org/HomePage
Proximo

Would someone please do a write up on which is best, which is moving forward, and synopsis the differences? I don't expect a front runner just yet, but it would be nice to see a comparison about how their development is going.

Thanks!
-Brian

BTW I am sorry if I missed a proxy, if you have another one email me or leave a comment.
BBTW I don't consider HScale a proxy, but yes it is damn neat.
BBBTW I think the creation of proxies, points to a trend ;)

User: krow Date: 2008-05-09 08:44 Subject: Social Graphs, Portable Neighborhoods Security: Public

Here is the thing.

Not everyone whom I marked as a friend in social graph games version 1, do I still hang out with. Some moved, others I have nothing in common with any longer. Hobbies change, etc.

Just as I consider Linkedin and Facebook to be two wildly different networks, I pretty much find sites in general to be this way. Sure, some friends are portable... but in many ways social sites are sort of an archive for "this is who I was hanging out with then".

Friendster is sort of 1.0 (ok, ISCA would be 0.1, and Livejournal something like a 0.5).
Tribe 2.0
Facebook 3.0 (though Facebook is filled with friends 2.0 (aka high school))

Twitter, well I don't know what Twitter is.

But do I really want these to be portable? Not really.

Do I want to keep up to date with my friend's contact information?

Sure, that sounds excellent.

But the rest?

Not so much.

If Apple did a better job with Addressbook I would keep more information there (and keep it updated via some system (plaxo++)).

So what does all of this mean?

I am really bored with social sites, social graphs, and how many degrees I am away from anyone.

User: krow Date: 2008-05-08 18:37 Subject: BBC Headlines, Who would have thought... Security: Public

I kid you not:

Great tits cope well with warming

User: swanhart Date: 2008-05-07 12:51 Subject: Sample my.cnf file for InnoDB databases Security: Public Tags: community, config, innodb, mycnf, mysql, parameters

Brian Moon suggest that community provided example my.cnf files would be a great thing to have on MySQLforge in this recent post: http://doughboy.wordpress.com/2008/05/06/example-mycnf-files/

I pulled out the "innodb heavy" config sample file and modified it with the standard settings that I typically start with when setting up a new InnoDB master. I've also modified the comments in the file a bit and have added some of my own too. I removed the sample slave configuration parameters (master-host, etc) because you should be using 'CHANGE MASTER TO'.

He suggested tagging such files with a 'mycnf' tag and very kindly tagged mine after I posted it :)

Feel free to share yours too and please feel free to make any comments about my configuration choices.

You can find it here (along with any other mycnf tagged files):
http://forge.mysql.com/tools/search.php?t=tag&k=mycnf

User: swanhart Date: 2008-05-05 16:03 Subject: I'm now with Kickfire Security: Public Tags: job, kickfire, mysql

Today is my first official day with Kickfire. I've spent most of the day reading up about how the appliance works and trying to wrap my head around some of the finer details.

My starting role here is essentially as an internal consultant, which means that I'll be the one that gets the MySQL server related questions from the development team. This is going to allow me a chance to really sink my teeth further into the source code and help implement some really cool tech, which has me quite excited.

Later I will be doing some external consulting (sales, implementation, etc) that will allow me to travel a bit.

Overall, I think this position is a great fit for me and I'm really psyched.

User: krow Date: 2008-05-05 12:03 Subject: CrippleWare, World of Open Source Security: Public Tags: mysql, open source

Ever since I did my original post on Crippleware I have been getting a lot of feedback from individuals about how the intersection of open source works with closed source extensions.

Open Source that is not crippleware but allows for third party extensions allows for the following:

User: krow Date: 2008-05-05 11:56 Subject: Open Source, Table Manners Security: Public

One of the questions I receive a lot is "how does my company work with an open source project?".

Working with open source projects is all about having table manners. There is an expected behavior for individuals and companies to have.

User: imajica_lj Date: 2008-05-05 20:50 Subject: GOLD! Security: Public Mood: amused Music: Mission of Burma, That's when I reach for my revolver Tags: math, ppl, quote

"Yes Andrew, but you represent the dark side of mathematics".

Fucking gold.

User: krow Date: 2008-05-04 21:50 Subject: Seattle Cardboard Fight Club! Security: Public

Carboard Fight Club, originally uploaded by krow.

The only rule about Cardboard Fight Club?

Invite everyone you know :)

User: tcpip Date: 2008-05-05 13:39 Subject: Liberal Religion and Neo-Astrology, Champagne Breakfasts, Clusters and CMSs Security: Public

Gave an address at the Melbourne Unitarian Church on Sunday on Reverend Stephen Fritchman, a radical liberal in the U.S. who caused a great deal of trouble for the political establishment during the 1940s, 50s and 60s. On a vaguely related topic I found myself debating on convert_me last week with astrologers, of all people. Intrigued to discover that some of that ilk now claim (or rather, admit as the statistics and science is beyond reasonable doubt) that it has no predictive value whatsoever, but rather can only be used as a metaphorical and instrospective approach. So, after all those centuries it is finally acknowledged to be fiction.

Last Saturday the deco-apartment of Das Hoehnhauss/Casa di Lafayette held a champagne breakfast with various teas, Timorese coffee, lots of pancakes, almond shortbread, silverware and fine china. In attendence were caseopaya's new workmate and partner along with imajica_lj. We're hoping to turn this into a semi-regular event as it's a delightful way to waste away a Saturday morning and most of an afternoon and catch up with a small group of people as well. We're gradually building a list of people to come along, but if anyone is particularly keen to attend in the next couple, please drop a line below.

Work-related matters have been quite busy of late and we've been somewhat short-staffed. Upgrading the dual-core Opteron systems to quad-cores on Tango is a necessity if we wish to reach at least somewhere close to the c7 Teraflop peak but (as one would expect) it hasn't entirely been smooth sailing. On a completely different tangent, I've been investigating the relative competitive benefits of Drupal vs Plone CMSs for ARCS. Having some experience with both does lead me towards the former for the sake of simplicity, but I'm interested to hear contrary views.