I'm working on an article with Peter G. about MySQL partitioning that should be on dev.mysql.com next week sometime (assuming that I pull my finger out and get my bits finished soon). I started working on an example that partitions based on years and quarters and very quickly realised that if you use QUARTER() as part of of the partitioning expression, then that table is going to have all the high-speed performance of the average garden slug.I won't give away everything here (otherwise you'd have no reason to go read the article), but I will say that using TO_DAYS() instead of QUARTER() is part of the solution, and that I came up with a utility of sorts to help in creating such tables. Here's the stored procedure: DELIMITER |CREATE PROCEDURE qdays ( IN y1 INT, IN q1 INT, IN y2 INT, IN q2 INT ) BEGIN DECLARE y INT DEFAULT 2005; DECLARE q INT DEFAULT 0; DECLARE mn INT DEFAULT 1; DECLARE ms CHAR(2) DEFAULT ''; DECLARE s VARCHAR(50) DEFAULT ''; DROP …
[Read more]Flickr: macplusg3’s photos tagged with beijing
There’s some photos I’ve taken around Beijing up there. Will be posting more over the next few days (and until I leave - on the 16th). Enjoy.
A "little-known way," I claim, and yet it happens all the time -- precisely because it's little-known. Experts will quickly recognize where I'm going to go with this article, but I hope many others in my audience will understand deadlocks more deeply after reading it. I'll use MySQL and InnoDB for illustration purposes, but the scenario this article describes (dramatic music, please!) could happen to you, too! And probably will someday, unless you're one of the elite few (ok, enough drama) who know how to avoid it.
In this article I'll briefly introduce deadlocks, give an example of one that happened at my employer recently, analyze and explain it, and then disclose the secret way to avoid cause such deadlocks. Then I'll show you how to reproduce the deadlock and dive into the gory details of what goes on internally with InnoDB. I'll also demonstrate how …
[Read more]
... and most important - good use of SQL!
That doesn't sound surprising, I guess.
Recently, Giuseppe Maxia provided a good hint (An ugly query should be a warning bell) how to
get an indicator that something is wrong with your query. I would
like to add a hint to help you find out that something is wrong
with the way you use your database inside your application. Just
ask yourself the following question:
How much of my client side code is there just to get the data
that I got from the database into the form that I need in my
application?
Your answer should be: very little.
In most cases, SQL provides the capabilities to get out of the
database exactly what you need. Needless to say that it's most …
Stephan Uhrenbacher is the Managing Director of Qype GmbH, the company behind the Qype.com web site, which allows users to recommend places in Germany and has developed into one of Germany?s most interesting web 2.0 applications.
The first beta version of XAMPP for Linux is available for public download in our XAMPP BETA area. New in this beta version are: Apache 2.2.3, MySQL 5.0.22, PHP 4.4.3 and phpMyAdmin 2.8.2.
Beta releases are only for testing purposes. We would very much appreciate it if you could test this release and let us know if you run into any problems.
Jay scooped me on the news but he's right - we're organizing a MySQL camp in the spirit of Barcamp.
One of the great things about living in the bay area is that that there are a lot of smart geeks here. Generally speaking, most of the smart guys in MySQL work either for MySQL AB or one of the big Web 2.0 shops like Facebook or Yahoo. This should give everyone the opportunity to leave their cubicles and share all their tricks with the whole community.
We're also going to be doing this the right way which means that we're going to expect people to lead their own sections and contribute in some way shape or form. This isn't just another top-down conference where sessions are approved by a committee.
One great piece of news is that …
[Read more]Gary Edwards (Open Document Foundation) and I were talking yesterday, and he mentioned the NASCIO (National Association of State CIOs) Conference coming up. I checked out last year's conference and found an interesting set of slides from an open source session they held.
From the slides, some useful data on why state governments are buying into open source. (Note: The survey was to CIO-level IT people within state governments. Pretty credible data, especially since NASCIO gets 350-450 senior government IT folks out to its annual conference, and restricts the survey (unless I'm reading the site wrong) to the most senior IT officials).
Anyway, why are state CIOs buying into open source? Well, for one thing, because it costs less. But also because it works better:
…
[Read more]
In a recent blog entry I discussed some research I had done
into the behavior of MySQL FEDERATED tables. Since then the
question arose in the forums regarding the use of triggers with
FEDERATED tables, so I performed some additional tests to see how
triggers would behave.
The question was asked if triggers could be used with FEDERATED
tables, and I verified that triggers could indeed be created on
FEDERATED tables. With the assertion that the a trigger on a
FEDERATED table did not actually fire, I set up a base table and
an associated FEDERATED tables with triggers attached to both
tables and verified they did in fact get called.
A 'before insert' trigger on a FEDERATED table gets called before
the corresponding 'before insert' trigger on the base table, and
this is rather what I would expect. If the FEDERATED table's
trigger …
When using LOAD DATA especially when importing from one charset
container to utf8. Make sure to issue SHOW WARNINGS after the
statement. Why? Well a cryptic message might occur and lead you
to the true cause of the problem.
For instance if you get a message:
"Data truncated for column 'column' at row 157"
Why was row 157 truncated? The data use to exist before correctly
in the table structure in the old format, what's different?
Well, in the scenario of upgrading to true utf8 column character
sets, MySQL will remove all invalid utf8 chars and produce this
message. You might be lead to believe oh crap maybe I need to
increase the column size, NO that's not it. In MySQL 4.1 varchar,
text, chars size are no longer just a function of bytes. Previous
to 4.1
varchar(255) means that it would take up to 255 bytes, i.e. 1
latin1 character 1 byte. In 4.1+ this means …