MySQL 5.5.8 GA and PHP 5.3.4 don't get along with libmysql

Today I discovered that you are unable to compile the current stable PHP version 5.3.4 with yesterday’s MySQL 5.5.8 GA release. I was able to download the current MySQL 5.1.54 and compile without issue.

You can find all the gory details in Bug #58987 however I was able to edit a number of MySQL include file to get a build. Does this mean it’s a MySQL packaging problem or a PHP problem I don’t know, but I would hope that Oracle in the testing phase of a GA release test this against popular programming languages starting with the LAMP stack to ensure compatibility such as what I uncovered.

Five reasons to upgrade to MySQL 5.5

Updated Nov 2011. Check out my Reasons to use MySQL 5.5 Presentation for more in-depth information about installing/configuring and using MySQL 5.5

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary configuration changes is a great simplification. The introduction in the InnoDB plugin 1.0.x of a new file format (known as Barracuda) and additional new row formats such as dynamic and compressed should also be considered for improved I/O performance. Greater eduction and customer comparison results are still needed here.

2. Improved performance

“Improved scalability on multi-core CPUs” is the tag line Oracle touts and while the marketing graphs do show improved performance with huge numbers of threads, it is the continued work at addressing the support for large cores that is the key to have MySQL perform on newer H/W. MySQL was always known as the software that runs on low cost commodity H/W, however it also needs to work well on new hardware architectures.

InnoDB has many improvements, you can see a long list at 13.7.7. Performance and Scalability Enhancements. I have seen an upgrade from 5.0 to 5.5 significantly reduce contention of queries. The help of multiple buffer pool instances, multiple rollback segments, additional threads and functional mutexes have all reduced high throughput mutex contention.

3. Improved recoverability

While I have yet to use Semisynchronous replication in a production situation I see this as a continued progression to improving the recoverability and resilience of MySQL. This was work first seen by the Google Patch in MySQL 5.0 several years ago. The lack of a single unbreakable backup strategy is the number 1 overlooked feature in MySQL as reported by customer advisory groups in the past. The move to making InnoDB the default storage engine is another small step to improved recoverability in a default MySQL setting.

4. Improved instrumentation

A lack of detailed instrumentation rates number 2 on my list of areas of improvement needed with MySQL. With 5.5 there are more continued improvements. While some of these are very technical for high end analysis (e.g. the performance_schema), simple improvements in common instrumentation can help you identify triggers for poor performance. There is a lot more information available in the INFORMATION_SCHEMA. Here is a great example via SHOW PROCESSLIST that shows a 4 second lock in the Query Cache (QC) that until now was not possible to summarize easily what happens in the Query Cache black box.

... | Query   |    4 | Waiting for query cache lock | REPLACE INTO statistics_w SET IP = ...

Identification is the first necessary step to continued improvement.

My work with the PERFORMANCE_SCHEMA has been limited to experimenting such as described by Tracking IO with performance_schema however a recent discussion with Mark Leith highlighted that with the performance_schema it will be possible to determine the exact size of disk based temporary tables created by the MySQL kernel for example.

5. Improved development methodology

We have heard a lot of noise from a vocal few that Oracle will not do right by MySQL, the product, the community, the OEM providers. While I believe it is too early to tell the long term plans of MySQL under Oracle, I believe that action speaks louder than words. Oracle has made a number of commitments publically, Oracle are investing in resources to improve the development of the product and It’s clear the targeting of the Microsoft OS highlights MySQL has a future as a SQL Server competitor. Oracle brings a great wealth of experience, resources and processes and hopefully that will be invested into MySQL.

There is a good list of What Is New in MySQL 5.5 as part of the current development, features I’ve not mention include SIGNAL/RESIGNAL, change in the default storage engine, partitioning additions and pluggable MySQL authentication capability.

The MySQL documentation team also do a great job. An example of new work is a great comparison of Options and Variables from all 5.x versions shown on a single page for comparison.

The way forward with MySQL

Oracle as steward of MySQL is here to stay. While people still rumble about it, everybody has to get over it. For the end consumer little will change, for clients that use MySQL Enterprise Support that are affected by the changes in policy, many other viable support options exist. What is unknown and my single point of issue with the EU 10 point Statement is what will happen with OEM providers. This affects the financial viability of a number of providers and while I’m not directly involved I do not want to see this good work lost to the MySQL community.

Points of uncertainty include what features will be developed next, will they be pay only features, will they be available to enterprise customers and not community customers, what is the frequency of community releases etc. These still need good PR from Oracle for the MySQL community. For those concerned, Oracle has made continued investment in acquired RDBMS products including BerkeleyDB and InnoDB and RDB. Oracle RDB for example was acquired in 2004 and is still being actively developed and supported. As long as MySQL is profitable, we will see continued releases.

About the Author

Ronald Bradford is a well respected expert in the MySQL community. Ronald is internationally recognized as an Oracle ACE Director in MySQL, the highest industry recognition and is also a published author of Expert PHP and MySQL. He is available for MySQL consulting work now.

Wish list for MySQL thread polling events

It is great to draw inspiration from other Open Source communities. Brad Fitzpatrick recently wrote about Android Strict Mode. His twitter tag line for this post was “I see you were doing 120 ms in a 16 ms zone” which is all I needed to hear from somebody who also worries unreasonably about responsiveness (Web site quote).

How would I apply this to a MySQL context? This is what happens in Android. “Strict Mode lets you set a policy on a thread declaring what you’re not allowed to do on that thread, and what the penalty is if you violate the policy. Implementation-wise, this policy is simply a thread-local integer bitmask. By default everything is allowed and it won’t get in your way unless you want it to”

In a MySQL thread I would like to know certain things every time they occurs. Some events I’d consider relevant are:

  • Any disk writes (e.g. Created_tmp_disk_tables) occured
  • Any disk reads (e.g. data was not in buffer_pool for example)
  • Exceeded execution time obviously
  • Internal memory buffer usage, size/amount of read/read_rnd/join/sort buffer
  • Other paths triggered, e.g. Sort_merges, read from disk for table_open_cache etc

Also with this mode is not just the instrumentation of the thread, but the penalty if violated. Do you log, kill, change priority, report an annoying message (difficult in SQL world).
I feel it would be unwise to consider this on a per thread request due to the complexity of SQL, so the next consideration is how to you categorize or identify given SQL query paths to a given polling policy.

While I have already raised more questions then answers for a possible solution the principle is important in what can we do to make our job easier and more simpler. These appproaches are the next generation of requirements in performance tuning, where you are becoming proactive rather then reactive in identification, analysis and correction.

Unexpected mysqld crashing in 5.5

An update of MySQL from 5.0 to 5.5 on CentOS 5.5 64bit has not resulted in a good experience. The mysqld process would then crash every few minutes with the following message.

101120 8:29:27 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: File name /tmpfs/#sql6cf3_5c_0.ibd
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.

The work around was to change the tmpdir=/tmpfs (which was a 16G tmpfs filesystem) to a physical disk.

The referenced URL didn’t provide any more information of help. Unlike Bug #26662 O_DIRECT is not specified as the flush method.

Damm you Peformance Schema

One significant new feature of MySQL 5.5 is the Performance Schema. I recently performed an upgrade from 5.0 to 5.5, however my check of differences in the MySQL variables via mysqladmin variables failed because we now have a new record long variable name “performance_schema_events_waits_history_long_size”.

The full list of new performance_schema variables are:

> | performance_schema_events_waits_history_long_size | 10000                                                                                                                  |
> | performance_schema_events_waits_history_size      | 10                                                                                                                     |
> | performance_schema_max_cond_classes               | 80                                                                                                                     |
> | performance_schema_max_cond_instances             | 1000                                                                                                                   |
> | performance_schema_max_file_classes               | 50                                                                                                                     |
> | performance_schema_max_file_handles               | 32768                                                                                                                  |
> | performance_schema_max_file_instances             | 10000                                                                                                                  |
> | performance_schema_max_mutex_classes              | 200                                                                                                                    |
> | performance_schema_max_mutex_instances            | 1000000                                                                                                                |
> | performance_schema_max_rwlock_classes             | 30

Not really a big problem, and not a complaint, just no longer a simple diff.

The Casual MySQL DBA – Operational Basics

So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

  1. Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
  2. Can you connect locally via cli?
  3. What’s in the MySQL error log?
  4. What are current MySQL threads doing? Locked? long running? how many? idle sources?
  5. Can you connect remotely via cli?
  6. Verify free diskspace?
  7. Verify system physical resources?
  8. If this is a slave, is MySQL replication running? Is it up to date?
  9. What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
  10. What is the current InnoDB state and load? (based on if your using InnoDB)

After you do this manually more then once you should be scripting these commands to be productive for future analysis and proactive monitoring?

Is a problem obvious? Does the output look different to what a normal environment looks like? (HINT: This list is not just for when there is a problem)

So moving forward?

  1. Is disk/memory/cpu/network bottleneck an issue you can resolve?
  2. Can you improving locking statements (if applicable)?
  3. Can you identify, analyse and tune long running statements?
  4. Do you know how to restart MySQL?
  5. Do you know who to call when you have a non working environment?
  6. When did your backup last run?
  7. Does your last backup work?

In order to support any level of production MySQL environment you need to know the answers to these questions? If you don’t, then this is your homework checklist for MySQL DBA operations 101. There a number of resources where you can find the answers, and this help can be available online, however never assume the timeliness of responses, especially if your expecting if for FREE! Open source software can be free, open source support rarely is.

MySQL HandlerSocket under Ubuntu

Starting with the great work of Yoshinori-san Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server and Golan Zakai who posted Installing Dena’s HandlerSocket NoSQL plugin for MySQL on Centos I configured and tested HandlerSocket under Ubuntu 10.04 64bit.

NOTE: This machine already compiles MySQL and Drizzle. You should refer to appropriate source compile instructions for necessary dependencies.

# Get Software
cd /some/path
export DIR=`pwd`

# Install MySQL Binaries
tar xvfz mysql-5.1.52-linux-x86_64-glibc23.tar.gz
ln -s mysql-5.1.52-linux-x86_64-glibc23 mysql5152
rm -rf mysql5152/mysql-test
rm -rf mysql5152/sql-bench
cd mysql5152
bin/mysqld_safe &

# Install MySQL Source
tar xvfz mysql-5.1.52.tar.gz

# Install HandlerSocket
tar xvfz ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
cd ahiguti-HandlerSocket-Plugin-for-MySQL-d032ec0/
./configure --with-mysql-source=${DIR}/mysql-5.1.51 --with-mysql-bindir=${DIR}/mysql5152/bin
sudo make install

# libtool: install: /usr/bin/install -c .libs/handlersocket.lai /home/rbradfor/projects/handlersocket/mysql5152/lib/plugin/

# Install the Perl dependency
cd perl-Net-HandlerSocket
perl Makefile.PL
Checking if your kit is complete...
Warning: the following files are missing in your kit:
Please inform the author.
Writing Makefile for Net::HandlerSocket
# For some reason I had to run this twice, after error I check, saw nothing wrong, ran a second time and it worked ???
perl Makefile.PL
Writing Makefile for Net::HandlerSocket
sudo make install

Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/
Installing /usr/local/lib/perl/5.10.1/Net/
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket/
Installing /usr/local/man/man3/Net::HandlerSocket.3pm
Appending installation info to /usr/local/lib/perl/5.10.1/perllocal.pod

cp lib/Net/ blib/lib/Net/
cp lib/Net/HandlerSocket/ blib/lib/Net/HandlerSocket/
/usr/bin/perl /usr/share/perl/5.10/ExtUtils/xsubpp  -typemap /usr/share/perl/5.10/ExtUtils/typemap  HandlerSocket.xs > HandlerSocket.xsc && mv HandlerSocket.xsc HandlerSocket.c
Please specify prototyping behavior for HandlerSocket.xs (see perlxs manual)
g++ -fPIC -c  -I. -I../libhsclient -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -O3 -Wall -Wno-unused   -DVERSION="0.01" -DXS_VERSION="0.01" -fPIC "-I/usr/lib/perl/5.10/CORE"   HandlerSocket.c
Running Mkbootstrap for Net::HandlerSocket ()
chmod 644
rm -f blib/arch/auto/Net/HandlerSocket/
LD_RUN_PATH="/usr/local/lib" g++ -fPIC  -shared -O2 -g -L/usr/local/lib -fstack-protector HandlerSocket.o  -o blib/arch/auto/Net/HandlerSocket/ 	

chmod 755 blib/arch/auto/Net/HandlerSocket/
cp blib/arch/auto/Net/HandlerSocket/
chmod 644 blib/arch/auto/Net/HandlerSocket/
Manifying blib/man3/Net::HandlerSocket.3pm

Now all built, it’s as simple as the following to install:

${DIR}/mysql5152/bin/mysql -uroot
mysql> INSTALL PLUGIN HandlerSocket SONAME '';
| Name          | Status   | Type           | Library          | License |
| binlog        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| partition     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| ARCHIVE       | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| BLACKHOLE     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| CSV           | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| FEDERATED     | DISABLED | STORAGE ENGINE | NULL             | GPL     |
| MEMORY        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| InnoDB        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| MyISAM        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| MRG_MYISAM    | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| handlersocket | ACTIVE   | DAEMON         | | BSD     |
11 rows in set (0.00 sec)

However, I found it not actually running even after restarting MySQL. You need the following configuration

cd ${DIR}/mysql5152
echo "[mysqld]
loose_handlersocket_port = 9998 # the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999 # the port number to bind to (for write requests)
loose_handlersocket_threads = 16 # the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1 # the number of worker threads (for write requests)" >> my.cnf

After restarting you get the following confirmation.

| Id | User        | Host            | db            | Command | Time | State                                     | Info             |
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |
| 18 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | SHOW PROCESSLIST |
18 rows in set (0.00 sec)

My Test Data

USE test
  name VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY(user_id)

INSERT INTO user (user_id,name, email) VALUES
(100,'Mickey Mouse','[email protected]'),
(101,'Marvin the Martian','[email protected]'),
(102,'Spiderman','[email protected]');

SELECT user_id,name,email FROM user WHERE user_id=101;
| user_id | name               | email               |
|     101 | Marvin the Martian | [email protected] |

My test retrieval program is basically unchanged from the original post.

$ cat

use strict;
use warnings;
use Net::HandlerSocket;

#1. establishing a connection
my $args = { host => '', port => 9998 };
my $hs = new Net::HandlerSocket($args);

#2. initializing an index so that we can use in main logics.
 # MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
die $hs->get_error() if $res != 0;

#3. main logic
 #fetching rows by id
 #execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
for (my $row = 0; $row < 1; ++$row) {
  my $user_name= $res->[$row + 0];
  my $user_email= $res->[$row + 1];
  my $created= $res->[$row + 2];
  print "$user_namet$user_emailt$createdn";

#4. closing the connection

If your HandlerSocket is not running you will see:

$ perl
FATAL_EXIT: getaddrinfo failed: ip_to_remote_host:9998
$ telnet 9998
telnet: Unable to connect to remote host: Connection refused

When working

$ telnet 9998
Connected to
Escape character is '^]'.
P	0	test	user	PRIMARY	name,email,created
0	1
0	=	1	101
0	3	Marvin the Martian	[email protected]	2010-11-05 13:11:34

$ perl
Marvin the Martian	[email protected]	2010-11-05 13:11:34

Again, great work by Yoshinori-san to have this code as production deployable. Now to run some real benchmarks.

Improving MySQL Insert thoughput

There are three simple practices that can improve general INSERT throughput. Each requires consideration on how the data is collected and what is acceptable data loss in a disaster.

General inserting of rows can be performed as single INSERT’s for example.

INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);

While this works, there are two scalability limitations. First is the network overhead of the back and forth of each SQL statement, the second is the synchronous nature, that is your code can not continue until your INSERT is successfully completed.

The first improvement is to use MySQL’s multi values capability with INSERT. That is you can insert multiple rows with a single INSERT statement. For example:

INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?),  (?, ?, ?), (?, ?, ?);

Depending on how you collect the information to be inserted, you may be able to easily implement this. The benefit, as previously mentioned in The value of multi insert values shows an 88% improvement in performance.

One system variable to be aware of is max_allowed_packet. You may have to adjust this for larger INSERT statements.

Next is the ability to INSERT data based on information already in another table. You can also leverage for example another storage engine like MEMORY to batch up data to be inserted via this approach.

INSERT INTO table (col1, col2, col3) SELECT col1, col2, col3 FROM anothertable

The third option is to batch load your data from a flat file. Depending on how you source the information you are inserting, that may also be a significant improvement in throughput in bulk inserting data.

INTO TABLE (col1, col2, col3)

On a closing note, the choice of storage engine can also have a significant effect on INSERT throughput. MySQL also offers other non ANSI options including DELAYED, IGNORE and LOW_PRIORITY. These should definitely be avoided.

OTN MySQL conference slides

2010 has been the first year I have re-presented any of my developed MySQL presentations. Historically I have always created new presentations, however Paul Vallee gave me some valuable advice at UC 2010. In the past two weeks I’ve traveled to seven countries in South America on the OTN LA tour where I have been speaking about and promoting MySQL.

My three current presentations have been improved and even simplified, more future improvements are planned. There is definitely a benefit in repeating a good presentation multiple times.

MySQL Support Options

Oracle has released news about changing policies of MySQL Enterprise Support effectively dropping annual support for Basic and Silver. The entry level support is now $3000 per server per year. The MySQL support team now part of Oracle has great resources however Oracle is in the business of making money. When a general company question for OOW is company income, and the first option is < $50 million it highlights that startups, and smaller companies are clearly not a focus.

The success of MySQL as an open source company has lead to other leading providers that now can provide enterprise level support. More importantly many organizations over per-incident support which is more cost effect. News in the past week has included Percona - New Support Option for MySQL, and today SkySQL with Kaj Arno’s Joining SkySQL Ab, back in the startup business. (Motivation for me to publish this draft)

For reference, this is what I received from Oracle prompting this post.

For those of you using Basic and Silver support we’re being told those options will no longer be available. If you wish to continue with Basic or Silver you will need to sign a multi-year agreement and you would be able to keep using Basic or Silver for up to another 3 years.

MySQL Best Practices for DBAs and Developers

This is one of the MySQL presentations I’m doing on the OTN LAD Tour in South America, starting today in Lima, Peru.

MySQL Best Practices for DBAs and Developers

Learn the right techniques to maximize your investment in MySQL by knowing the best practices for DBAs and Developers. Understand what subtle differences between MySQL and other RDBMS products are essential to understand in order to maximize the benefits and strengths of MySQL. We will be covering areas including the minimum MySQL configuration, ideal SQL, MySQL security and schema optimizations.

  • MySQL Configuration default settings including SQL_MODE
  • Documenting, formatting and future proofing your SQL
  • Developing and reviewing all SQL paths
  • MySQL physical and user security
  • The best schema optimizations
  • Essential Monitoring and Instrumentation
  • The locking essentials for different storage engines
  • Managing your Disk I/O with optimal storage and access

MySQL 5.5 and transaction management

Announced at MySQL Sunday was the Release Candidate edition of MySQL 5.5.6. Also noted by Geert where he points out the default storage engine is now InnoDB.

However, for those from a background other then MySQL there is still a gotcha.

mysql> show global variables like 'autocommit';
| Variable_name | Value |
| autocommit    | ON    |

Unlike Oracle for example, the default autocommit is on.

Doing some other boundary conditions, it is no longer possible to disable InnoDB on startup which you would of course expect.

$ bin/mysqld_safe --skip-innodb &
$ tail error.log

101003 15:33:32 [Note] Plugin 'InnoDB' is disabled.
101003 15:33:32 [ERROR] Unknown/unsupported storage engine: InnoDB
101003 15:33:32 [ERROR] Aborting

MyISAM however can’t be removed and can’t be disabled. This is a question I’ve been asked by Oracle resources.

101003 15:34:55 [ERROR] /Users/rbradfor/mysql/mysql-5.5.6-rc-osx10.5-x86_64/bin/mysqld: ambiguous option '--skip-myisam' (--skip-myisam-block-size)
101003 15:34:55 [ERROR] Parsing options for plugin 'MyISAM' failed.
101003 15:34:55 [ERROR] Failed to initialize plugins.
101003 15:34:55 [ERROR] Aborting
mysql> select table_schema, table_name from information_schema.tables where engine='myisam';
| table_schema       | table_name                |
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | columns_priv              |
| mysql              | db                        |
| mysql              | event                     |
| mysql              | func                      |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | host                      |
| mysql              | ndb_binlog_index          |
| mysql              | plugin                    |
| mysql              | proc                      |
| mysql              | procs_priv                |
| mysql              | servers                   |
| mysql              | tables_priv               |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| mysql              | user                      |
30 rows in set (0.06 sec)

Common MySQL Scalability Mistakes

This week I was one of the presenters at the first Surge Scalability Conference in Baltimore. An event that focused not just on one technology but on what essential tools, technologies and practices system architects need to know about for successfully scaling web applications.

While MySQL is an important product in many environments, it is only one component for a successful technology stack and for many organizations is one of several products that manage your data.

My presentation was on the common MySQL scalability mistakes and how to avoid them. This is a problem/solution approach and is a companion talk with Successful MySQL Scalability which describes design for successfully scalability from the ground up.

OTN Interview about MySQL

I was interviewed by Justin Kestelyn the OTN Senior Director about MySQL at Oracle Open World this week.

Some highlights of the questions asked:

  • 0:55 Since the close of acquisition has there been any change in direction?
  • 2:23 How have your clients and customers responded to the acquisition?
  • 3:53 You mentioned that Oracle will bring added advantages and could infuse invocation.
  • 5:15 InnoDB and MySQL are now both owned by Oracle. What do you see as the development advantages?
  • 6:47 What were your thoughts on the first MySQL Sunday?
  • 8:58 Forks?
  • 11:04 Contact Details

You can view the Video online or play below.

Successful MySQL Scalability Presentation

Last night I was the invited guest at the SF MySQL Meetup. In my presentation “Successful MySQL Scalability” I talked about a set of principles to ensure appropriate system architecture, data availability and best practices to build an ideal solution for your business. The presentation was also live streamed and is available online.

MySQL South America tour

DISCLAIMER: This post contains no technical MySQL content however it is good news for the MySQL Community.

MySQL content will be included for the first time with the LAOUC (Latin American Oracle Usergroups Council) Oracle tour that is being organized in conjunction with OTN (Oracle Technology Network).

I have no idea what MySQL user communities are in South America however if you live in any of the following cities, please feel free to contact me. I am happy to have additional discussion regarding MySQL or help in some way if there is interest in any cities.

This seven country tour includes:

  • Oct 12 – Lima, Peru
  • Oct 14 – Santiago, Chile
  • Oct 16 – Montevideo, Uruguay
  • Oct 18 – São Paulo, Brazil
  • Oct 20 – Bogota, Colombia
  • Oct 22 – Quito, Ecuador
  • Oct 25 – San Jose, Costa Rica

More details on the specific locations in each city will be available when finalized.

I would be very happy if anybody wants to translate this to Spanish or Portuguese for readers in South America.

View OTN Latin America in a larger map

2011 MySQL Conferences

Next year will mark a significant change for the MySQL community. At least three major conferences will have dedicated MySQL content that is great for attendees getting the best information on how to use MySQL from the experts in the field.

O’Reilly MySQL Conference & Expo

The 9th Annual MySQL conference will be held at is usual home of recent years. Colin will again be back as committee chair for a 3rd year and this will be my 6th straight MySQL conference.

Date: April 11 – 14, 2011
Location: Hyatt Regency, Santa Clara, California
Website: There is no website at this time
Call for Papers: There are no details for call for papers
Program Chairs: Colin Charles from Monty Program AB and Brian Aker.

Collaborate 11

Collaborate is a larger conference (4,000-5,000 attendees) that is actually three separate conferences in one run by the IOUG, OAUG and Quest. The IOUG content is generally a focus for Oracle DBA’s. Last year marked the first year with any MySQL sessions, and this year Collaborate will have dedicated MySQL tracks chaired by fellow ACE director Sheeri Cabral who is well known for her work in the MySQL community.

Date: April 10 – 14, 2011
Location: Orange County Convention Center West, Orlando, Florida
Call for Papers: Now open. Closes Friday October 1, 2010
Program Chair: Sheeri Cabral

KScope 11

ODTUG Kaleidoscope (Kscope for short) is a conference (1500 attendees) that is very focused on delivering the best content from the top community contributors for the communities benefit. 2010 was my first Kaleidoscope conference and I felt completely at home. Great people, great events and the best conference food I’ve had in many years.

With a dedicated MySQL track in 2010 for the first time I will again be the MySQL Program Chair in 2011 with an extended format for the MySQL developer and DBA. The focus will be the best way to develop successful applications with MySQL and will include Architecture, Performance Tuning, Best Practices, Case Studies and Hands-On streams.

Date: June 26 – 30, 2011
Location: Long Beach, California
Call for Papers: Closes Tuesday October 26, 2010
Program Chair: Ronald Bradford – Independent Consultant


2010 is also not over. MySQL Sunday at OOW promises to be a great event in San Francisco in under 2 weeks. You can still register at a very cheap price of $75 for 4 dedicated tracks of MySQL content. Open SQL Camp being organized also by Sheeri in Boston in October will continue the tradition of a small but focused and free event for the MySQL community.

The case against using rpm packaging for MySQL

In some environments using a distro package management system may* provide benefits including handling dependencies and providing a simpler approach when there are no dedicated DBA or SA resources.

However, the incorrect use can result in pain and in this instance production downtime. Even with dedicated resources at an unnamed premium managed hosting provider, the simple mistake of assumption resulted in over 30 minutes of unplanned downtime during peak time.

One of the disadvantages of using a system such as rpm is the lack of control in managing the starting and stopping of your MySQL instance, and the second is unanticipated package dependency upgrades.

So what happened with this client. When attempting to use the MySQL client on the production server, I got the following error.

$ mysql -uxxx -p
error while loading shared libraries: cannot open shared object file: No such file or directory

The server was running MySQL 5.0.27 via an rpm install.

$ rpm -qa | grep -i mysql

With no access to this managed server the information was relayed to the hosting provider and some time later we found the production website down. Some 30 minutes later we found that to fix the rpm problem, a dependency upgrade has also caused an automatic upgrade from 5.0.27 to 5.0.88.

While upgrading is not necessarily a bad thing, the lack of planning including a backup, a scheduled window of downtime and any level of testing is simply a poor cowboy approach to DBA management.

Upcoming MySQL Conferences

Unlike previous years when the number of conferences with MySQL content diminishes after the O’Reilly MySQL and OSCON conferences (Open SQL Camp excluded), this year has a lot on offer.

This month:

Upcoming next month in September:

  • MySQL Sunday at Oracle Open World on September 18 in San Francisco includes 4 tracks and around 15 quality speakers. (Big numbers of attendees also rumored but yet unconfirmed).
  • The inaugural Surge Scalability conference in Baltimore will include presentations by myself and Baron Schwartz (Percona being sponsors) as well as talks from other popular sites using MySQL.

If your in SF for the MySQL Sunday you may also want to come for the SF MySQL Meetup on the preceeding Thursday night where I’ll be giving my talk on “Common MySQL Scalability problems, and how to fix them”.

In October:

  • Open SQL Camp in Boston from Friday, Oct 15th in the evening, ending Sunday Oct 17th

Europeans will be busy in November where you will find dedicated MySQL tracks with multiple speakers at DOAG and UKOUG. Other MySQL talks can be found at SAPO Codebits 2010 and BGOUG.

And for South America, stay tuned. October will be your month!

There is also a great event calendar maintained by the MySQL community team on the Forge.

Why GRANT ALL is bad

A common observation for many LAMP stack products is the use of poor MySQL security practices. Even for more established products such as WordPress don’t always assume that the provided documentation does what it best for you. As per my earlier posts where I detailed installation instructions and optimal permissions for both WordPress and Mediawiki, and not just directed readers to online documentation.

In this post I will detail why GRANT ALL is bad.

Let’s start with what GRANT ALL [PRIVILEGES] provides. As per the MySQL 5.1 Reference Manual you get the following:


I am going to focus on just one privilege that is included with ALL, and that is SUPER. This privilege can do the following which can be destructive for an application level user:

  • Bypasses read_only
  • Bypasses init_connect
  • Can Disable binary logging
  • Change configuration dynamically
  • No reserved connection

User Permissions

This is how a user should be created, granting only the required permissions to a given schema.

CREATE USER goodguy@localhost IDENTIFIED BY 'sakila';

This is what is commonly seen.

CREATE USER superman@'%';
GRANT ALL ON *.* TO superman@'%';

Bypasses read_only

Many MySQL replication environments rely on ensuring the MySQL slave is consistent with the master. Did you know that an application can bypass this security when read_only=true is used?

$ mysql -ugoodguy -psakila odtug
mysql> insert into test1(id) values(1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
$ mysql -usuperman odtug
mysql> insert into test1(id) values(1);
Query OK, 1 row affected (0.01 sec)

GRANT ALL is bad for data consistency.

Bybasses init_connect

A common practices used for UTF8 communications is to use the init_connect configuration variable.

init_connect=SET NAMES utf8
$ mysql -ugoodguy -psakila odtug

| Variable_name            | Value    |
| character_set_client     | utf8     |
| character_set_connection | utf8     |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results    | utf8     |
| character_set_server     | latin1   |
| character_set_system     | utf8     |
$ mysql -usuperman odtug

mysql> SHOW SESSION VARIABLES LIKE 'character%';
| Variable_name            | Value    |
| character_set_client     | latin1   |
| character_set_connection | latin1   |
| character_set_database   | latin1   |
| character_set_filesystem | binary   |
| character_set_results    | latin1   |
| character_set_server     | latin1   |
| character_set_system     | utf8     |

GRANT ALL is bad for data integrity.

Disables Binary Logging.

$ mysql -usuperman odtug

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| binary-log.000001 |      354 |              |                  |

mysql> DROP TABLE time_zone_leap_second;
mysql> SET SQL_LOG_BIN=0;
mysql> DROP TABLE time_zone_name;
mysql> SET SQL_LOG_BIN=1;
mysql> DROP TABLE time_zone_transition;
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| binary-log.000001 |      674 |              |                  |
$ mysqlbinlog binary-log.000001 --start-position=354 --stop-position=674

# at 354
#100604 18:00:08 server id 1  end_log_pos 450 Query thread_id=1 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1275688808/*!*/;
DROP TABLE time_zone_leap_second
# at 579
#100604 18:04:31 server id 1  end_log_pos 674 Query thread_id=2 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1275689071/*!*/;
DROP TABLE time_zone_transition
# End of log file
ROLLBACK /* added by mysqlbinlog */;

Should that statement be run on MySQL Slaves?
Is the binary log used for any level of auditing?

GRANT ALL is bad for slave consistency.

The reserved connection

MySQL reserved one connection for an administrator to be able to login to a server. For example.

$ mysql -uroot

mysql> show global variables like 'max_connections';
| Variable_name   | Value |
| max_connections | 3     |
1 row in set (0.07 sec)

mysql> show global status like 'threads_connected';
| Variable_name     | Value |
| Threads_connected | 4     |

| Id | User | Host      | db    | Command | Time | State      | Info
| 13 | good | localhost | odtug | Query   |  144 | User sleep | UPDATE test1 ...
| 14 | good | localhost | odtug | Query   |  116 | Locked     | select * from test1
| 15 | good | localhost | odtug | Query   |   89 | Locked     | select * from test1
| 15 | root | localhost | odtug | Query   |   89 | Locked     | SHOW PROCESSLIST

However if all application users are already using the SUPER privilege, the administrator will get.

$ mysql -uroot
ERROR 1040 (HY000): Too many connections

There is no way to be able to login and see what’s happening, or kill threads for example. In this case you either wait, or you are required to kill the mysqld process, or fine the client threads to kill. The result of the former may lead to a corrupt database requiring additional recovery.

GRANT ALL is bad for system administration and monitoring.


Don’t use GRANT ALL for application users. For more information, including why I only listed just 5 issues, check out my MySQL Idiosyncrasies that BITE presentation. I will also be presenting this talk at MySQL Sunday at Oracle Open World in September.

You have to love the Planet MySQL voting system

Within a few hours my post Installing Mediawiki on Oracle Enterprise Linux LAMP stack got 5 negative votes.

Wow, I’d be glad if these people could felt so passionately about all the other CRUD on Planet MySQL that has ZERO to do actually do with MySQL.

Using a LAMP product, and providing instructions for operation can’t be a negative voting offense. So it can only be the words “Oracle Enterprise Linux”.

For those negative people out there that care enough to physically mark blogs let me share some facts with you. RedHat Enterprise Linux (RHEL) is the most widely used and support platform for production MySQL environments. CentOS and Oracle Enterprise Linux (OEL) provide via the freedom of Open Source, their own offerings of RHEL with various other features including support or specific additional features for other products. Oracle also provides and commits work to the Linux Kernel, so they are just as much an allies to open source as other companies.

So are you bagging the operating system or just the word Oracle. Oracle is hear to stay, in fact those attending the MySQL Sunday event at Oracle Open World may get a welcome wake up shock. And for reference these articles are being written for a published Oracle Magazine article which meets the comfort level of the reader and introduces MySQL in a positive way. Your actions show just how much you are unwilling to embrace the larger community.

Installing Mediawiki on Oracle Enterprise Linux LAMP stack

A company wiki can be easily configured in under 10 minutes using Mediawiki the open source LAMP software that powers the top 10 website Wikipedia.

A company wiki is an ideal means for a centralized and user contributed documentation system. The following steps show you how to download, configure and get your Mediawiki site operational.

Software Pre-Requisites

Software Installation

su -
cd /tmp
cd /var/www/html
tar xfz /tmp/mediawiki*.tar.gz
mv mediawiki* wiki
chmod 777 wiki/config

NOTE: You should check the Mediawiki Downloads page for the latest version.

You can now visit http://localhost/wiki and you will be presented with a message of an un-configured Mediawiki environment. You can streamline the MySQL portion of this configuration with the following commands.

mysql -uroot -p -e "DROP SCHEMA IF EXISTS wikidb;CREATE SCHEMA wikidb"
mysql -uroot -p -e "CREATE USER wikiuser @localhost IDENTIFIED BY 'sakila'"
mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX on wikidb.* TO wikiuser@localhost"

You can now complete the configuration on the Configuration Page. You will need to enter the following information.

  • Wiki name = Example Wiki
  • Contact e-mail = your email address
  • Admin user password = enter password
  • DB password = sakila

After successful installation, one additional step is needed.

mv wiki/config/LocalSettings.php wiki/
chmod 400 wiki/LocalSettings.php
chmod 500 wiki/config

You can now start using your new Wiki at http://localhost/wiki. You will find documentation at the links provided on the displayed home page and also at

MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -uwikiuser -psakila blog

mysql> show tables;
| Tables_in_wikidb  |
| archive           |
| category          |
| categorylinks     |
| change_tag        |
| external_user     |
| externallinks     |
| filearchive       |
| hitcounter        |
| image             |
| imagelinks        |
| interwiki         |
| ipblocks          |
| job               |
| l10n_cache        |
| langlinks         |
| log_search        |
| logging           |
| math              |
| objectcache       |
| oldimage          |
| page              |
| page_props        |
| page_restrictions |
| pagelinks         |
| protected_titles  |
| querycache        |
| querycache_info   |
| querycachetwo     |
| recentchanges     |
| redirect          |
| revision          |
| searchindex       |
| site_stats        |
| tag_summary       |
| templatelinks     |
| text              |
| trackbacks        |
| transcache        |
| updatelog         |
| user              |
| user_groups       |
| user_newtalk      |
| user_properties   |
| valid_tag         |
| watchlist         |
45 rows in set (0.00 sec)

mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'wikidb';
| table_name        | engine | table_rows |
| archive           | InnoDB |          0 |
| category          | InnoDB |          0 |
| categorylinks     | InnoDB |          0 |
| change_tag        | InnoDB |          0 |
| external_user     | InnoDB |          0 |
| externallinks     | InnoDB |          0 |
| filearchive       | InnoDB |          0 |
| hitcounter        | MEMORY |          0 |
| image             | InnoDB |          0 |
| imagelinks        | InnoDB |          0 |
| interwiki         | InnoDB |         95 |
| ipblocks          | InnoDB |          0 |
| job               | InnoDB |          0 |
| l10n_cache        | InnoDB |       3686 |
| langlinks         | InnoDB |          0 |
| log_search        | InnoDB |          0 |
| logging           | InnoDB |          0 |
| math              | InnoDB |          0 |
| objectcache       | InnoDB |          2 |
| oldimage          | InnoDB |          0 |
| page              | InnoDB |          1 |
| page_props        | InnoDB |          0 |
| page_restrictions | InnoDB |          0 |
| pagelinks         | InnoDB |          0 |
| protected_titles  | InnoDB |          0 |
| querycache        | InnoDB |          0 |
| querycache_info   | InnoDB |          0 |
| querycachetwo     | InnoDB |          0 |
| recentchanges     | InnoDB |          0 |
| redirect          | InnoDB |          0 |
| revision          | InnoDB |          1 |
| searchindex       | MyISAM |          0 |
| site_stats        | InnoDB |          1 |
| tag_summary       | InnoDB |          0 |
| templatelinks     | InnoDB |          0 |
| text              | InnoDB |          1 |
| trackbacks        | InnoDB |          0 |
| transcache        | InnoDB |          0 |
| updatelog         | InnoDB |          0 |
| user              | InnoDB |          1 |
| user_groups       | InnoDB |          2 |
| user_newtalk      | InnoDB |          0 |
| user_properties   | InnoDB |          0 |
| valid_tag         | InnoDB |          0 |
| watchlist         | InnoDB |          0 |
45 rows in set (0.01 sec)

Sort URL for this post

First thoughts of Augen Android Internet Tablet

In the last few days there has been some press of the Augen gentouch 7″ Tablet. A new cheap tablet that is running Android 2.1.

Image from

There were a few primary motivations for getting one, the first being price, at $150 I consider cheap for a small tablet, and second it runs Android, something I’m wanting to play more with. I have an original Google G1, and I also purchased one recently for my fiance.

So what are my first impressions.

  1. First it was difficult to get. It is only available at KMart it seems. My local KMart in New York City didn’t have any. Apparently they sold out very quickly with stock on Wednesday. 10 KMart stores later (via phoning them) I found just 1 more that had even received stock, and they had 1 left. Lucky last. It was only that we had a car on Saturday it was even possible to get to this store. (-1 for ease of acquisition via company distribution)
  2. KMart claimed the price was $165, but if you checked the online magazine it was $149.99. I had to prove that first to get the price via using the web on my phone. (-1 for Kmart customer service)
  3. I wanted to buy and additional memory card as I knew from online review this was available however the box (which was still unopened before purchase only mentions Expansion Memory Card Slot, it doesn’t mention the actually size format. Even reading the manual after purchase talks about a SD card or a TF card, but is not specific. I ended up purchasing a Sandisk Mobile microSDHC 16GB card. Even that packaging was confusing as it clearly states “For Mobile Phones” twice on the package. (-1 for ease of information/packaging)
  4. Turning on the first time was a pleasant surprise because the item was fully charged. (+2 there). With my MiFi I was immediately able to connect to the web while still in the car (as a passenger). (+1 for access to primary use)
  5. The touch screen is clearly not as responsive as an Apple or smart phone, it’s mixed sometimes it only requires a light touch, sometimes a heavy touch. Early reports mentioned this, so it was not unexpected. Again for the price and proposed uses I have for it, it wasn’t a deal breaker. I saw one online video that included a stylus, and another mention online, however the manual gives no indication, and I don’t seem to have one.
  6. Downloading the 2012 HD trailer via YouTube worked promptly and without issues and picture was good (+1) but there is no external controls for audio. (-1 for that).
  7. There is a headphone jack however I find out later that this is a 2.5mm jack, and standard headphones used by everybody on the planet is 3.5mm. (-1 for that). The website claims they will supply people at no cost a set of 2.5mm headphones. Again useless as I don’t want to have to carry those around as well. What I want is a 2.5mm to 3.5mm converter, actually two because I’m sure I’ll lose it easily. (-1 for that)
  8. The main buttons for “back, menu, home” are actually on the back of the device. Once you know that it’s not that bad, but it is a little odd. Of course for right handed person. If they were on the side it’s possible any type of “death grip” may accidentally press them.
  9. Unit comes with a handy and practical leather case (+1) with adequate access for side controls, however it’s only good for holding not using because the buttons are on the back (-1).
  10. By purchasing a 16GB at $99, the price is now $250, it’s not as impressive a cost product.
  11. The purchased SDCard was not easy to install, there is no clear instruction on right side up and certainly nothing in the docs, and there are reports online that people misplace in the slot and the card gets lost inside of unit. I didn’t have that problem and I don’t really see that would happen for me. The manual indicates you will get an icon about it, however that wasn’t the case so I really didn’t know if it worked or not. (-1) Using the AndExplorer indicates a /sdcard so I assume it’s operational.
  12. There is reference to a U Disk (who knows what that is). with a supplied cable I assume it’s a means of adding a USB thumbdrive to the mini USB slot. Trying this however didn’t seem to work so I don’t know if that’s the intended use.
  13. The power supply is yet another plug I have to now carry (along with one for laptop, phone, mifi). I was really hoping the power adapter would have been a mini or micro USB. (-1)
  14. The first real use after web, video, google maps, email is to download other stuff however it seems the Android Market is broken. The official website (which I now can’t find, -1 for poor SEO and google searchability) makes a note this is broken and is expecting a patch (-2 for poor testing there.
  15. The manual was not proof read by an English person. The title cover states “Table” not “Tablet” however the funniest part was reading some the manual. I will not type what I found, but you can see the image below. It so made me laugh.
  16. There is no video output, which is really annoying because the side panel actually states HDMI in printing, but nothing physical.
  17. The screen res is 800×480. Ok, so it’s small but of the sites I’ve initially visited the horizontal bar has not been an issue. I suspect it may be in the future, but this is not my primary development machine.
  18. No webcam
  19. I wanted to download some of my ebooks, however this seems to be broken and related to the market problem. Will see after patch.

I’ve yet to really test it out, these are just my first impressions. For the price I consider it a worthwhile investment for the purposes I want it for. That is some browsing, (ideal for bedroom), I can see it a wicked 7″ GPS unit with turn by turn controls for driving, but without GPS it will be a bit manual. Reading email, or an ebook, and even use as a large digital frame, especially for my photos.

This is a gen 1 product, so you have to accept the shortcomings. You are either an alpha adopter that is willing to accept limitations and accept the benefits it does have or your not.

Other References:

Augen’s $150 Android tablet hits Kmart circular, coming to stores later this week (what first caught my eye). KMart update. First impressions of the Augen GenTouch78 Android tablet (with second mention of a stylus).

Will Oracle kill MySQL?

I get asked this question often. It was mentioned again recently in a NYTECH executive breakfast with RedHat CIO Lee Congdon.

The short answer is No.

There is clear evidence that in the short to medium term Oracle will continue to promote and enhance MySQL. Some of these indicators include:

It is clear from these sources that Oracle intends to incorporate MySQL into Oracle Backup and Security Vault products. Both a practical and necessary step. There is also a clear mention of focusing on the Microsoft platform, a clear indicator that SQL Server is in their sights without actually saying it.

What is unknown is exact how and when features will be implemented. Also important is how much these may cost the end user. Oracle is in the business of selling, now an entire H/W and S/W stack. They also have a complicated pricing model of different components with product offerings. I assume this will continue. There are already two indications, InnoDBbackup included for Enterprise Backup (from April Keynote) and 5.1 enterprise split. (Note: while this split may have existed prior to Oracle, it is now more clearly obvious).

MySQL can never be seen as drawing away from any Oracle sales of the core entry level database product. It is likely Oracle will provide a SQL Syntax compatibility layer for MySQL within 2 years, however it will I’m sure be a commercial add-on. Likewise, I would suspect a PL/SQL lite layer within 5 years, but again at a significant cost to offset the potential loss of sales in the low end of the server market. There continues to be active development in the MySQL Enterprise Monitor, MySQL Workbench and MySQL Connectors which is all excellent news for users.

Moving forward, how long will this ancillary development of free tools continue? What will happen to the commercial storage engine, OEM and licensing model after the 5 year commitment? How will the MySQL ecosystem survive.? There is active development in Percona, MariaDB and Drizzle forks, however unless all players that want to provide a close MySQL compatible solution work together, progress will continue to be a disappointing disjointed approach. The 2011 conference season will also see a clear line with competing MySQL conferences in April scheduled at the same time, the O’Reilly MySQL conference in Santa Clara California and the Oracle supported(*) Collaborate 2011 in Orlando, Florida.

I have a number of predictions on what Oracle ME MySQL may look like in 5 years however this is a topic for a personal discussion.

Speaking at Surge Scalability 2010 – Baltimore, MD

I will be joining a great list of quality speakers including John Allspaw, Theo Schlossnagle, Rasmus Lerdorf and Tom Cook at Surge 2010 in Baltimore, Maryland on Thu 30 Sep, and Fri Oct 1st 2010. Surge 2010 Speaker - Baltimore, MD

My presentation on “The most common MySQL scalability mistakes, and how to avoid them.” will include discussing various experiences observed in the field as a MySQL Consultant and MySQL Performance Tuning expert.


The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environments to provide a suitable architecture and application design to support these essential needs.


Some details of the presentation would include:

  • The different types of accessible data  (e.g. R/W, R, none)
  • What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
  • The three components of scalability – Read Scalability/Write Scalability/Caching
  • Design practices for increasing scalability and not physical resources
  • Disaster is inevitable. Having a tested and functional failover strategy
  • When other products are better (e.g. Static files, Session management via Key/Value store)
  • What a lack of accurate monitoring causes
  • What a lack of breakability testing causes
  • What does “No Downtime” mean to your organization.
  • Implementing a successful “failed whale” approach with preemptive analysis
  • Identifying when MySQL is not your bottleneck

If you missed MySQL Idiosyncrasies that BITE

I recently gave a webinar to the LAOUC and NZOUG user groups on MySQL Idiosyncrasies that BITE.

For the benefit of many viewers that do not use English as a first language my slides include some additional information from my ODTUG Kaleidoscope presentation in June.

Thanks to Francisco Munoz Alvarez for organizing.

3 webinars on Upgrading MySQL

The IOUG Online Education Series: Get Real with Upgrades will include next week 3 different MySQL webinars. These are:

  • MySQL 5.1: Why and How to Upgrade by Sheeri Cabral on Tuesday, July 27, 12:00 p.m. – 1:00 p.m. CT
  • MySQL Upgrades With No Downtime by Sean Hull on Wednesday, July 28, 12:00 p.m. – 1:00 p.m. CT
  • MySQL Upgrade Best Practices by Matt Yonkovit on Thursday, July 29, 12:00 p.m. – 1:00 p.m. CT

Each speaker will be covering different areas and reasons for considering and implementing a successful MySQL Upgrade.

Installing WordPress on Oracle Enterprise Linux LAMP stack

A company blog can be easily configured in under 10 minutes using WordPress, a popular open source LAMP product that runs a reported 12+ million blogs including those found at CNN, NY Times, Wall Street Journal (WSJ), ZDNet, MTV, People Magazine, Playstation and eBay.

A company blog is a great way for the dissemination of information to your user base as well as enabling a means of user feedback via comments.

The following steps show you how to download, configure and get your WordPress blog operational.

Software Pre-Requisites

Software Installation

su -
cd /tmp
cd /var/www/html
tar xfz /tmp/latest.tar.gz
mv wordpress blog

You can now visit http://localhost/blog and you will be presented with a message of an un-configured WordPress environment. You can streamline the MySQL portion of this configuration with the following commands.

cd blog
sed -e "s/database_name_here/blog/;s/username_here/blog_user/;s/password_here/sakila/" wp-config-sample.php > wp-config.php
mysql -uroot -p -e "CREATE SCHEMA blog"
mysql -uroot -p -e "CREATE USER blog_user @localhost IDENTIFIED BY 'sakila'"
mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE on blog.* TO blog_user@localhost"

Returning now to http://localhost/blog you simply only need to specify a Title, password and email address, click Save and your Blog at http://localhost/blog is complete and operational.

MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -ublog_user -psakila blog

mysql> show tables;
| Tables_in_blog        |
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
11 rows in set (0.00 sec)

mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'blog';
| table_name            | engine | table_rows |
| wp_commentmeta        | MyISAM |          0 |
| wp_comments           | MyISAM |          1 |
| wp_links              | MyISAM |          7 |
| wp_options            | MyISAM |        109 |
| wp_postmeta           | MyISAM |          1 |
| wp_posts              | MyISAM |          2 |
| wp_term_relationships | MyISAM |          8 |
| wp_term_taxonomy      | MyISAM |          2 |
| wp_terms              | MyISAM |          2 |
| wp_usermeta           | MyISAM |         13 |
| wp_users              | MyISAM |          1 |
11 rows in set (0.00 sec)

Additional References

Short URL for this post