If you are running any GNU/Linux server operating system like RHEL 5 or CentOS 5, you may probably install MySQL server that comes with the operating system packages either during the initial setup or later using yum(8). The advantage being addition/removal of packages either using the GUI package manager or rpm(8), yum(8). Fair enough. But unfortunately the MySQL package (mysql-server) that comes bundled with RHEL 5.5 or CentOS 5.5 is fairly old (5.0.77). What if you want to install the latest stable version of MySQL yet have the advantage of removing/re-installing the software using rpm(8)?

In this blog post, I will guide you with compiling MySQL from source code yet installing the software through rpm(8) so that we tune and configure the software for the target machine and yet uninstall the software using RedHat package manager.

Compiling and Installing MySQL using rpmbuild(8)

First make sure you have sudo(8) access and …

The following quirky dynamic SQL will scan each index of each table so that they’re loaded into the key_buffer (MyISAM) or innodb_buffer_pool (InnoDB). If you also use the PBXT engine which does have a row cache but no clustered primary key, you could also incorporate some full table scans.

To make mysqld execute this on startup, create /var/lib/mysql/initfile.sql and make it be owned by mysql:mysql

SET SESSION group_concat_max_len=100*1024*1024;
SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql') AND seq_in_index = 1;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET SESSION group_concat_max_len=@@group_concat_max_len;

and in my.cnf add a line in the [mysqld] block

init-file = …

With the standard data warehousing queries we have a fact table and dimension tables and we join them.
For example, the fact table (Table size: 5M rows, ~2G in size) from my previous Loose index scan vs. covered indexes in MySQL post:

    CREATE TABLE `ontime_2010` (
      `YearD` int(11) DEFAULT NULL,
      `MonthD` tinyint(4) DEFAULT NULL,
      `DayofMonth` tinyint(4) DEFAULT NULL,
      `DayOfWeek` tinyint(4) DEFAULT NULL,
      `Carrier` char(2) DEFAULT NULL,
      `Origin` char(5) DEFAULT NULL,
      `DepDelayMinutes` int(11) DEFAULT NULL,
      `AirlineID` int(11) DEFAULT NULL,
      `Cancelled` tinyint(4) DEFAULT NULL,
    ... more fields here ...
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

(this is not the best possible fact table as the data is not aggregated by I’ll use it for now).

And we have those …

Before launching into this, I must give due deference to Mogens Nørgaard’s landmark article, You Probably Don’t Need RAC (YPDNR), available here, but originally published Q3 2003 in IOUG Select Journal.  Mogens showed that you can be a friend of Oracle without always agreeing with everything they do.

At Blue Gecko, many of our remote DBA customers have been asking us about Golden Gate.  In July 2009, Oracle bought Golden Gate Software, just one of several companies that have developed log-based replication mechanisms for Oracle and other databases.  This was one of many major acquisitions by Oracle in 2009, including Sun and Relsys. But unlike most of Oracle’s acquisitions, …

Lets say we have a table storing mail messages and we need to show user’s mailbox: messages sent “from” and “to” the specified user.

Here is our table:

CREATE TABLE `internalmail` (
  `mail_id` int(10) NOT NULL AUTO_INCREMENT,
  `senderaddress_id` int(10) NOT NULL,
  `recipientaddress_id` int(10) NOT NULL,
  `mail_timestamp` timestamp NULL DEFAULT NULL,
... message body, etc ...
  PRIMARY KEY (`mail_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And our query:

select * from internalmail
 where (senderaddress_id = 247 or recipientaddress_id = 247 or recipientaddress_id = 0) 
and mail_timestamp > '2010-08-01 12:30:47' 
order by mail_timestamp desc 

In this query we show all messages from and to user_id = 247 plus all messages to system user (user_id=0). We need to show only messages for the last 3 months and show the most recent messages first.

To speed up the query we can try creating …

A very interesting presentation by Kaj Arnö about the State of the MySQL Community. The initial part of the presentation is about the history of how things happened with MySQL, Sun and Oracle, then it describes the current state of our community. The final part of the presentation is fairly enough promotion towards SkySQL for whom Kaj is EVP of Products. I really enjoyed it and would recommend it to all of you interested in past, present and future of “MySQL”.

check it out here

Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information on it. – Samuel Johnson

Sooner or later, a DBA will be asked for some information from their databases that will need to be well formatted and past the limitations of SQL. The requester will also want to run the report on an ad hoc basis.  And the user can not wait for a script or program to be written. Oh, can you set it up so they can add or drop items of data or change it around on the fly too?

There are a number of report writing programs out there and three with active open source communities are BIRT, Jaspersoft, and Pentaho, among others.  They all roughly do the same thing — take data from your instance and turn it into a good looking report.

Calpont has published three guides to help you get started with these three reporting programs and they …

A catchy headline, and I believe more accurate than Oracle Puts the Squeeze on SMBs with MySQL Price Hike (Network World) and MySQL price hikes reveal depth of Oracle’s wallet love [MySQL Jacking up MySQL Prices] (The Register). Slightly more realistic is Oracle kills low-priced MySQL support (again The Register).

First, let’s review what Oracle has actually done: they ditched the MySQL enterprise Basic and Silver offerings. For Oracle, that makes sense. Their intended client base is “enterprise” (high end, think big corporates) and their MySQL sales and cost structure reflects this. It’s not a new thing that came with MySQL at Oracle, because MySQL at Sun …

After this mornings twitteriffic panic where people thought Oracle had removed innodb from the mysql community version, its better to clear some things out.

As a friend explained, mysql and innodb are free as in free speech and free beer (the dream). Hence Oracle model version is only talking about ENTERPRISE versions of mysql NOT the popular COMMUNITY version. All of you who used to go to downloads.mysql.com to get your mysql + innodb fix still can do so for FREE.

Oracle have changed their products page, but haven’t deemed important to explain the changes to the MySQL community. Oversight or intentional, the news that innodb won’t come with MySQL for free anymore with the community version is NOT TRUE.

Because Sharing is Caring

So this morning I was welcomed to work with a “Have you heard the news?” … Good lord I just woke up for God sakes.

Just like the storm back in April 2008 when Sun bought MySQL, another (possibly the perfect storm) happened in April 2009 when Oracle bought Sun, today the latter storm took us be “surprise” to a certain extent when Oracle released their new Editions model showing innodb removed from the classic edition. People are shouting all kinds of things at Oracle and there is a lot of confusion but I think it’s better to wait and see! We are a strong community and we won’t let this storm take us down ….. we are stronger than the Andrea Gail!

If you check out twitter be careful what you believe and don’t …