The MySQL optimize and analyze commands must be pretty confusing
if judged by the CMDBA (Certified MySQL Database Administrator)
exams. Hopefully this is because MySQL works wonderfully 'out of
the box' and does not need a great deal of maintenance. I am
looking at the questions on the exams where candidates are not
performing as expected and optimize/analyze is a stumbling
point.
There are several table maintenance operations that you need to
know about to keep your database healthy. Failure to keep your
database healthy can slow down queries and do other nasty things
to your quality of life.
CHECK TABLE performs an integrity check on the structure and
content of your tables. For MyISAM tables, it will also update
the index statistics. If problems appear, proceed to ...
REPAIR TABLE is for MyISAM tables only and will correct corrupted
tables. InnoDB tables should be dumped with mysqldump and …
I've known about the INFORMATION_SCHEMA views (or
system tables) in SQL Server for a while, but I just leared
recently that they are actually part of the SQL-92 standard and
supported on other database platforms.
The INFORMATION_SCHEMA views provide meta data
information about the tables, columns, and other parts of your
database. Because the structure of these tables are standardized
you can write SQL statements that work on various database
platforms.
For example suppose you want to return a resultset with a list of
all columns in a table called employees
SELECT table_name, column_name, is_nullable, data_type, character_maximum_length FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'employees'
Quite a handy feature, but it's hard to find what versions the of various database platforms started supporting this feature, here's a quick list:
- Microsoft …
When traveling to my parents town with my wife, we used to stop
by a coffee shop on the highway, where a charming couple made
good coffee with a smile. They were efficient and friendly. As
soon as we were stepping out of the car, they were already
preparing for our usual order (espresso and cappuccino), and they
had always a joke or a funny comment for us.
Once we saw them dealing with two busloads of tourists, serving
whatever they asked rapidly and cheerfully. This shop was a
popular stop, always crowded.
It ended a few weeks ago. Someone decided that the location was
profitable, and now the small shop has been replaced by a huge
coffee+restaurant+amenities resort.
We stopped there, out of habit, shortly before my world trip. The
crowd was thinner than usual. The charming couple was gone,
replaced by a 4 people team, who sent us to queue at the
cashier's before ordering. The coffee was awful, and the
barista's attitude …
To hear the New York Times’ John Markoff and Matt Richtel describe it in their largely fact-free story on the technical integration that Yahoo! and Microsoft will need to do if the merger goes through, you’d think that a Yahoo-Microsoft integration will amount to a cleaning of the Aegean stables.
The writers did take the time to interview someone who did a Unix-to-Microsoft port of a web site after it was purchased by Microsoft, but that port was done eight years ago. And this wasn’t Microsoft’s 1998 Hotmail acquisition (which some people consider to be the gold standard for Microsoft cocking up an acquisition of a *nix-based web property).
So the question is, from a technical integration perspective, could things have possibly changed in the past eight to ten years?
Well, of course they have. The one guy with …
[Read more]
Since PlanetPlanet aggregation for open source projects
worked so well, why hasn't someone considered to do the same for
the life casting sites? (Twitter and such...).
mysqllife.org? Peel the meme's tagged as MySQL off from twitter
and put them in a stream to watch?
I've started testing the new Release Candidate (5.1.23) today.
This was a version with a huge amount of fixes relative to latest
MySQL releases - but I didn't expect to run into two bugs this
fast.
- Bug #34626 which I just filed in today and was already verified. This is a basic scenario which is used in the deployment scripts we use at Delver. Just try and pipe an empty file into the mysql command line client - and it crashes.
-
Bug #32633 which was apparently fixed in 5.1.24,
and should have also presented itself in 5.1.22, but didn't for
some reason. The upgrade brought it up.
This one basically means that you just can't create …
Master-Master Replication With MySQL 5 On Fedora 8
This document describes how to set up master-master replication with MySQL 5 on Fedora 8. Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.
Master-Master Replication With MySQL 5 On Fedora 8
This document describes how to set up master-master replication with MySQL 5 on Fedora 8. Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.