Archive for the ‘Oracle’ Category

MySQL conference schedule

Monday, March 14th, 2011

I am one of the crazy individuals(*) that will be speaking at both the regular O’Reilly MySQL Conference and the IOUG Collaborate conference both being held in the second week of April. My 4 presentations are:

Microsoft’s position on MySQL

Monday, January 31st, 2011

While Oracle provides no official information they are planning on improving MySQL and using as a product to compete with Microsoft SQL Server, it is rather obvious from what little information you can glean from public announcements this is a clear business goal.

Microsoft however are publicly seeking a Senior Product Manager, MySQL Compete in the Marketing department. Your goal is nothing technical, it’s all PR to dispel MySQL as a viable product. I quote “you will equip field and partners to win in competitive engagements against MySQL, and you will influence market perception in favor of Microsoft technologies.” Here is the Full job description for those that want an amusing read.

This information came from an Oracle colleague of mine based in Asia.

OTN MySQL conference slides

Wednesday, November 3rd, 2010

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 Best Practices for DBAs and Developers

Tuesday, October 12th, 2010

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

OTN Interview about MySQL

Wednesday, September 22nd, 2010

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.

MySQL South America tour

Monday, September 13th, 2010

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

Tuesday, September 7th, 2010

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
Website: http://collaborate11.ioug.org/
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
Website: http://kscope11.com
Location: Long Beach, California
Call for Papers: Closes Tuesday October 26, 2010
Program Chair: Ronald Bradford – Independent Consultant

Recap

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.

Upcoming MySQL Conferences

Monday, August 9th, 2010

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.

Will Oracle kill MySQL?

Wednesday, July 28th, 2010

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.

Installing WordPress on Oracle Enterprise Linux LAMP stack

Thursday, July 15th, 2010

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
wget  http://wordpress.org/latest.tar.gz
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 rb42.com/oel-install-wordpress

Reviewing your MySQL installation on Oracle Enterprise Linux

Tuesday, July 13th, 2010

After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.

User Management

By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.

$ grep mysql /etc/{passwd,shadow,group}
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/shadow:mysql:!!:14796::::::
/etc/group:mysql:x:27:

Binaries

MySQL binaries are found in /usr/bin.

$ ls -l /usr/bin/mysql*
-rwxr-xr-x 1 root root  314568 Feb 16 17:45 /usr/bin/mysql
-rwxr-xr-x 1 root root  110776 Feb 16 14:39 /usr/bin/mysqlaccess
-rwxr-xr-x 1 root root   35144 Feb 16 17:45 /usr/bin/mysqladmin
-rwxr-xr-x 1 root root  112944 Feb 16 17:45 /usr/bin/mysqlbinlog
-rwxr-xr-x 1 root root    7632 Feb 16 17:45 /usr/bin/mysqlbug
-rwxr-xr-x 1 root root   30576 Feb 16 17:45 /usr/bin/mysqlcheck
-rwxr-xr-x 1 root root    7632 Feb 16 17:45 /usr/bin/mysql_config
-rwxr-xr-x 1 root root    3670 Feb 16 17:44 /usr/bin/mysql_convert_table_format
-rwxr-xr-x 1 root root   22522 Feb 16 17:44 /usr/bin/mysqld_multi
-rwxr-xr-x 1 root root   13073 Feb 16 17:44 /usr/bin/mysqld_safe
-rwxr-xr-x 1 root root   75184 Feb 16 17:45 /usr/bin/mysqldump
-rwxr-xr-x 1 root root    6356 Feb 16 17:44 /usr/bin/mysqldumpslow
-rwxr-xr-x 1 root root   11648 Feb 16 17:44 /usr/bin/mysql_explain_log
-rwxr-xr-x 1 root root    3245 Feb 16 14:39 /usr/bin/mysql_find_rows
-rwxr-xr-x 1 root root     483 Feb 16 17:44 /usr/bin/mysql_fix_extensions
-rwxr-xr-x 1 root root    5834 Feb 16 17:44 /usr/bin/mysql_fix_privilege_tables
-rwxr-xr-x 1 root root   31431 Feb 16 17:44 /usr/bin/mysqlhotcopy
-rwxr-xr-x 1 root root   26160 Feb 16 17:45 /usr/bin/mysqlimport
-rwxr-xr-x 1 root root   13659 Feb 16 17:44 /usr/bin/mysql_install_db
-rwxr-xr-x 1 root root    6586 Feb 16 17:44 /usr/bin/mysql_secure_installation
-rwxr-xr-x 1 root root   16687 Feb 16 17:44 /usr/bin/mysql_setpermission
-rwxr-xr-x 1 root root   28224 Feb 16 17:45 /usr/bin/mysqlshow
-rwxr-xr-x 1 root root   14473 Feb 16 14:39 /usr/bin/mysql_tableinfo
-rwxr-xr-x 1 root root  158192 Feb 16 17:45 /usr/bin/mysqltest
-rwxr-xr-x 1 root root   42360 Feb 16 17:45 /usr/bin/mysqltestmanager
-rwxr-xr-x 1 root root   15464 Feb 16 17:45 /usr/bin/mysqltestmanagerc
-rwxr-xr-x 1 root root   13448 Feb 16 17:45 /usr/bin/mysqltestmanager-pwgen
-rwxr-xr-x 1 root root 1312064 Feb 16 17:45 /usr/bin/mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root   54160 Feb 16 17:45 /usr/bin/mysql_upgrade
-rwxr-xr-x 1 root root    5753 Feb 16 17:44 /usr/bin/mysql_upgrade_shell
-rwxr-xr-x 1 root root  112136 Feb 16 17:45 /usr/bin/mysql_waitpid
-rwxr-xr-x 1 root root    3818 Feb 16 17:44 /usr/bin/mysql_zap

The mysqld binary is found in /usr/libexec

Error Log

The MySQL error log is found in /var/log/mysqld.log

The content after an initial start of MySQL will look similar to:

cat /var/log/mysqld.log
100705 22:09:03  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
100705 22:09:03  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
100705 22:09:03  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
100705 22:09:03  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
100705 22:09:03  InnoDB: Started; log sequence number 0 0
100705 22:09:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

On the first invocation of MySQL, the InnoDB storage engine will create a default tablespace and redo logs. This is the majority of messages in the above log.

Processes

MySQL is a multi-threaded single process called mysqld. A second wrapper process mysqld_safe is generally found. This process logs stderr and also will restart the mysqld process if not found.

ps -ef | grep mysql
root     14733     1  0 Jul05 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
mysql    14783 14733  0 Jul05 pts/1    00:00:10 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Memory Usage

MySQL can have a very low memory footprint. By default the mysqld process has a 175M virtual size.

$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
  PID COMMAND    RSS    VSZ USER     COMMAND
14275 grep       720  61136 root     grep -e RSS -e mysql
14733 mysqld_s  1192  63820 root     /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
14783 mysqld   27004 179496 mysql    /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Disk Usage

The MySQL data files will be stored on a default installation in /var/lib/mysql

$ du -sh /var/lib/mysql
22M     /var/lib/mysql

$ ls -ld /var/lib/mysql
drwxr-xr-x 4 mysql mysql 4096 Jul 13 11:50 /var/lib/mysql

$ ls -l /var/lib/mysql
total 20552
-rw-rw---- 1 mysql mysql 10485760 Jul  5 22:09 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  5 22:09 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jul  5 22:09 ib_logfile1
drwx------ 2 mysql mysql     4096 Jul  5 22:09 mysql
srwxrwxrwx 1 mysql mysql        0 Jul  5 22:09 mysql.sock

The MySQL data directory includes the InnoDB tablespace datafile (ibdata1), redo logs (ib_logfile?), and the mysql directory corresponding to the mysql schema containing instance meta data.

This directory also contains the socket file, which is actually a poor location as this opens the security of this directory for world access. This will be discussed later in securing your installation.

Running MySQL

The best means of controlling the starting and stopping of mysql is to use the provided service init script mysqld

$ ls -l /etc/init.d/mysqld
-rwxr-xr-x 1 root root 4286 Feb 16 17:45 /etc/init.d/mysqld

Configuration

For OEL the MySQL configuration can be found in /etc.
NOTE: MySQL can use multiple configuration files.

$ ls -l /etc/my.cnf
-rw-r--r-- 1 root root 441 Feb 16 14:39 /etc/my.cnf

MySQL includes a minimalistic configuration file by default. The configuration file format is variable=value pairs for a given number of different sections, in this file [mysqld] and [mysqld_safe].

$ cat /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Audit

A full audit of all MySQL related files.

find / -name "*mysql*"
/etc/rc.d/rc3.d/S64mysqld
/etc/rc.d/rc5.d/S64mysqld
/etc/rc.d/rc6.d/K36mysqld
/etc/rc.d/init.d/mysqld
/etc/rc.d/rc0.d/K36mysqld
/etc/rc.d/rc4.d/S64mysqld
/etc/rc.d/rc1.d/K36mysqld
/etc/rc.d/rc2.d/S64mysqld
/etc/php.d/pdo_mysql.ini
/etc/php.d/mysql.ini
/etc/php.d/mysqli.ini
/etc/ld.so.conf.d/mysql-x86_64.conf
/etc/ld.so.conf.d/mysql-i386.conf
/usr/lib64/mysql
/usr/lib64/mysql/mysqlbug
/usr/lib64/mysql/libmysqlclient_r.so.15.0.0
/usr/lib64/mysql/libmysqlclient.so.15
/usr/lib64/mysql/libmysqlclient_r.so.15
/usr/lib64/mysql/mysql_config
/usr/lib64/mysql/libmysqlclient.so.15.0.0
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Bundle/DBD/mysql.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm
/usr/lib64/php/modules/mysql.so
/usr/lib64/php/modules/pdo_mysql.so
/usr/lib64/php/modules/mysqli.so
/usr/libexec/mysqld
/usr/libexec/mysqlmanager
/usr/share/mysql
/usr/share/mysql/mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables_data.sql
/usr/share/mysql/mysql_fix_privilege_tables.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/usr/share/vim/vim70/syntax/mysql.vim
/usr/share/man/man8/mysqld.8.gz
/usr/share/man/man8/mysqlmanager.8.gz
/usr/share/man/man1/mysql.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_tableinfo.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysqlaccess.1.gz
/usr/share/man/man1/mysql_waitpid.1.gz
/usr/share/man/man1/mysql_fix_extensions.1.gz
/usr/share/man/man1/mysqlman.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqladmin.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysql_zap.1.gz
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/mysqlshow.1.gz
/usr/share/man/man1/mysqldump.1.gz
/usr/share/man/man1/safe_mysqld.1.gz
/usr/share/man/man1/mysql_explain_log.1.gz
/usr/share/man/man1/mysql_config.1.gz
/usr/share/man/man1/mysqlbug.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysql_setpermission.1.gz
/usr/share/man/man1/mysqlhotcopy.1.gz
/usr/share/man/man1/mysql_find_rows.1.gz
/usr/share/man/man1/mysql_convert_table_format.1.gz
/usr/share/man/man1/mysql_fix_privilege_tables.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqltest.1.gz
/usr/share/man/man1/mysqlcheck.1.gz
/usr/share/man/man3/Bundle::DBD::mysql.3pm.gz
/usr/share/man/man3/DBD::mysql.3pm.gz
/usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz
/usr/share/doc/mysql-server-5.0.77
/usr/share/doc/mysql-5.0.77
/usr/share/doc/selinux-policy-2.4.6/html/services_mysql.html
/usr/share/pixmaps/comps/mysql.png
/usr/share/info/mysql.info.gz
/usr/share/selinux/devel/include/services/mysql.if
/usr/bin/mysql_fix_extensions
/usr/bin/mysql
/usr/bin/mysqltestmanager
/usr/bin/mysqldumpslow
/usr/bin/mysql_upgrade_shell
/usr/bin/mysql_convert_table_format
/usr/bin/mysqlimport
/usr/bin/mysqldump
/usr/bin/mysqltestmanager-pwgen
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysqlbug
/usr/bin/mysqlhotcopy
/usr/bin/mysqlaccess
/usr/bin/mysqltest
/usr/bin/mysqladmin
/usr/bin/mysql_upgrade
/usr/bin/mysqltestmanagerc
/usr/bin/mysqld_safe
/usr/bin/mysql_zap
/usr/bin/mysql_waitpid
/usr/bin/msql2mysql
/usr/bin/mysql_secure_installation
/usr/bin/mysql_fix_privilege_tables
/usr/bin/mysqlshow
/usr/bin/mysql_config
/usr/bin/mysql_setpermission
/usr/bin/mysql_tableinfo
/usr/bin/mysql_find_rows
/usr/bin/mysqld_multi
/usr/bin/mysqlcheck
/usr/bin/mysqlbinlog
/usr/bin/mysql_install_db
/usr/bin/mysql_explain_log
/usr/lib/mysql
/usr/lib/mysql/mysqlbug
/usr/lib/mysql/libmysqlclient_r.so.15.0.0
/usr/lib/mysql/libmysqlclient.so.15
/usr/lib/mysql/libmysqlclient_r.so.15
/usr/lib/mysql/mysql_config
/usr/lib/mysql/libmysqlclient.so.15.0.0
/usr/lib/python2.4/site-packages/sos/plugins/mysql.pyo
/usr/lib/python2.4/site-packages/sos/plugins/mysql.pyc
/usr/lib/python2.4/site-packages/sos/plugins/mysql.py
/var/log/mysqld.log
/var/run/mysqld
/var/run/mysqld/mysqld.pid
/var/lock/subsys/mysqld
/var/lib/mysql
/var/lib/mysql/mysql
/var/lib/mysql/mysql.sock
/root/.mysql_history
/selinux/booleans/mysqld_disable_trans
/selinux/booleans/allow_user_mysql_connect

Installing a LAMP stack on Oracle Enterprise Linux

Monday, July 12th, 2010

After successfully installing MySQL on Oracle Enterprise Linux installing a LAMP (Linux/Apache/MySQL/PHP) stack can also be performed with a single command:

$ yum install -y httpd php php-mysql
# Start the Apache Httpd Process
$ /etc/init.d/httpd start

To test and confirm Apache Httpd and PHP, we can use the CLI browser lynx:

$ yum install -y lynx
$ echo "<? phpinfo() ?>" > /var/www/html/phpinfo.php
$ lynx http://localhost/phpinfo.php

If successful, you will find a web page that contains the following.

phpinfo() (p1 of 31)

   PHP Logo 

PHP Version 5.1.6

   System Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 04:15:13
   EDT 2009 x86_64
   Build Date Feb 11 2010 19:07:36
   Configure   Command   './configure'  '--build=x86_64-redhat-linux-gnu'
   '--host=x86_64-redhat-linux-gnu'    '--target=x86_64-redhat-linux-gnu'
   '--program-prefix=''--prefix=/usr''--exec-prefix=/usr''--bindir=/usr/bin'
   '--sbindir=/usr/sbin'    '--sysconfdir=/etc'    '--datadir=/usr/share'
   '--includedir=/usr/include'                      '--libdir=/usr/lib64'
   '--libexecdir=/usr/libexec'                     '--localstatedir=/var'
   '--sharedstatedir=/usr/com'                  '--mandir=/usr/share/man'
   '--infodir=/usr/share/info'             '--cache-file=../config.cache'
   '--with-libdir=lib64'                   '--with-config-file-path=/etc'

It is important to note that PHP is also a standalone scripting language that doesn’t require a web browser. You can use PHP on the command line, for example:

$ php --version
PHP 5.1.6 (cli) (built: Feb 11 2010 19:06:40)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies

$ echo "<?phpinfo()?>" | php | grep -i mysql
Configure Command =>  './configure' '--build=x86_64-redhat-linux-gnu' '--host=x86_64-redhat-linux-gnu' '--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib64' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--disable-rpath' '--without-pear' '--with-bz2' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-expat-dir=/usr' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-sysvmsg' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-kerberos' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--with-mime-magic=/usr/share/file/magic.mime' '--without-sqlite' '--with-libxml-dir=/usr' '--with-xml' '--with-system-tzdata' '--enable-force-cgi-redirect' '--enable-pcntl' '--with-imap=shared' '--with-imap-ssl' '--enable-mbstring=shared' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-ncurses=shared' '--with-gd=shared' '--enable-bcmath=shared' '--enable-dba=shared' '--with-db4=/usr' '--with-xmlrpc=shared' '--with-ldap=shared' '--with-ldap-sasl' '--with-mysql=shared,/usr' '--with-mysqli=shared,/usr/lib64/mysql/mysql_config' '--enable-dom=shared' '--with-dom-xslt=/usr' '--with-dom-exslt=/usr' '--with-pgsql=shared' '--with-snmp=shared,/usr' '--enable-soap=shared' '--with-xsl=shared,/usr' '--enable-xmlreader=shared' '--enable-xmlwriter=shared' '--enable-fastcgi' '--enable-pdo=shared' '--with-pdo-odbc=shared,unixODBC,/usr' '--with-pdo-mysql=shared,/usr/lib64/mysql/mysql_config' '--with-pdo-pgsql=shared,/usr' '--with-pdo-sqlite=shared,/usr' '--enable-dbase=shared'
/etc/php.d/mysql.ini,
/etc/php.d/mysqli.ini,
/etc/php.d/pdo_mysql.ini,
mysql
MySQL Support => enabled
MYSQL_MODULE_TYPE => external
MYSQL_SOCKET => /var/lib/mysql/mysql.sock
MYSQL_INCLUDE => -I/usr/include/mysql
MYSQL_LIBS => -L/usr/lib64/mysql -lmysqlclient
mysql.allow_persistent => On => On
mysql.connect_timeout => 60 => 60
mysql.default_host => no value => no value
mysql.default_password => no value => no value
mysql.default_port => no value => no value
mysql.default_socket => no value => no value
mysql.default_user => no value => no value
mysql.max_links => Unlimited => Unlimited
mysql.max_persistent => Unlimited => Unlimited
mysql.trace_mode => Off => Off
mysqli
MysqlI Support => enabled
MYSQLI_SOCKET => /var/lib/mysql/mysql.sock
mysqli.default_host => no value => no value
mysqli.default_port => 3306 => 3306
mysqli.default_pw => no value => no value
mysqli.default_socket => no value => no value
mysqli.default_user => no value => no value
mysqli.max_links => Unlimited => Unlimited
mysqli.reconnect => Off => Off
PDO drivers => mysql, sqlite
pdo_mysql
PDO Driver for MySQL, client library version => 5.0.77

Short URL: rb42.com/oel-install-lamp

Installing MySQL on Oracle Enterprise Linux

Sunday, July 11th, 2010

One of the significant benefits of MySQL is it’s ease of use. Generally already installed on most Linux systems, MySQL can be installed by a single command if not yet present. On Oracle Enterprise Linux 5.4 you can use the following commands to check for MySQL, configure your yum repository and install MySQL.

# Check if already installed
$ rpm -qa | grep -i mysql

# Configure yum repository on new server
$ su -
$ cd /tmp
$ wget http://public-yum.oracle.com/public-yum-el5.repo
$ sed -e "s/enabled=0/enabled=1/" public-yum-el5.repo > /etc/yum.repos.d/public-yum-el5.repo

# Install MySQL
$ yum install -y mysql-server mysql

# Start and test MySQL Instance
$ /etc/init.d/mysqld start
$ mysql -uroot -e "SELECT VERSION()"

+-----------+
| VERSION() |
+-----------+
| 5.0.77    |
+-----------+

You can find more information about the Oracle public yum repository at http://public-yum.oracle.com You will also note that the version installed is 5.0. The current GA version of MySQL is 5.1, however Red Hat is notorious for taking time to update repositories more regularly. You can always use more current rpm files available at the MySQL downloads page.

If you want MySQL to be configured to automatically start and stop on your server you need to run the following command.

$ chkconfig mysqld on
$ chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

You can link to this post with the short url rb42.com/oel-install-mysql

Related Posts

Speaking at Oracle Open World 2010

Saturday, July 10th, 2010


I will be one of 18 MySQL speakers at Oracle Open World 2010 at the first ever MySQL Sunday. With a great diversity of technical, community and product talks this will be a great opportunity to get a cross section of MySQL content. Combined with Java One, this year’s Oracle Open World will include a lot more opportunity of technical and developer content then the more regular marketing material.

As the program chair for the first dedicated MySQL track at last month’s ODTUG Kaleidoscope 2010 our MySQL community now includes a larger number of target people. From the Oracle community come many highly technical and skilled resources, some with an understanding or appreciation of MySQL and many that are new to MySQL.

This is a great opportunity to share our knowledge and experience with MySQL.

References

Upcoming Conferences with dedicated MySQL content

Tuesday, July 6th, 2010

We recently held a dedicated MySQL Track at ODTUG Kaleidoscope 2010 conference for 4 days. This is the first of many Oracle events that will begin to include dedicated MySQL content.

If your attending OSCON 2010 in the next few weeks you will see a number of MySQL presentations.

MySQL will be represented at Open World 2010 in September with MySQL Sunday. Giuseppe has created a great one page summary of speakers. This event is described as technical sessions, an un-conference and an fireside chat with Edward Screven. I’ve seen tickets listed at $50 or $75 for the day.

Open SQL Camp will be held in Germany in August, and Boston in October. This is a great FREE event that includes technical content not just on MySQL but other open source databases and data stores.

You will also find dedicated MySQL tracks in Europe at the German Oracle Users Group (DOAG) conference in November and the United Kingdom Oracle Users Group (UKOUG) in November that I am planning on attending.

In 2011 there is already a lineup of events that will all contain multiple tracks of MySQL content.

For the MySQL community the introduction of various large Oracle conferences may be confusing. From my perspective I describe the big three as.

  • Oracle Open World is targeted towards marketing. This includes product announcements, case studies and first class events.
  • Collaborate is targeted towards deployment and includes 3 different user groups, the IOUG representing the Oracle Database, the Oracle Applications User Group, and the Quest Group.
  • ODTUG Kaleidoscope is targeted towards development. This includes the tools and technologies for developers and DBA’s to do your job.

Having just attended Kaleidoscope 2010, and being a relative unknown I left with a great impression of an open, technical and welcoming event. There was a great atmosphere, great events with excellent food for breakfast, lunch and dinner and I now have a long list of new friends. This conference very much reflected being part of a greater extended family, the experience I have enjoyed at previous MySQL conferences. I’ve already committed to being involved next year.

Improving MySQL Productivity – From Design to Implementation

Thursday, July 1st, 2010

My closing presentation at the dedicated MySQL track at ODTUG Kaleidoscope 2010 discussed various techniques and best practices for improving the ROI of developer resources using MySQL. Included in the sections on Design, Security, Development, Testing, Implementation, Instrumentation and Support were also a number of horror stories of not what to do, combined with practical examples of improving productivity.

MySQL Idiosyncrasies That Bite

Monday, June 28th, 2010

The following are my slides that I presented at ODTUG Kaleidoscope 2010. This presentation talks about the MySQL defaults including a non-transactional state, silent data truncations, date management, and transaction isolation options. These are all critical for data integrity and consistency. I also cover in-depth topics including SQL_MODE, character sets and collations.

Still room at Kaleidoscope for MySQL attendees

Wednesday, June 16th, 2010

Today I received notice that next week’s Velocity conference is at maximum capacity. With just under 2 weeks before the start of ODTUG Kaleidoscope in Washington DC we still have room for late registrations. There is 4 days of MySQL content, free events and also a Sunday Symposium that includes talks on performance and high availability.

Contact any of the MySQL speakers directly and you can receive a special 50% discount code. This is only for MySQL attendees.

If you live in the DC area and only want the FREE option then come along and join use on Monday night for a free session and reception.

ODTUG Kaleidoscope 2010
July 27 – July 1
Marriott Wardman Part Hotel
2660 Woodley Road NW
Washington, District Of Columbia 20008
www.odtugkaleidoscope.com

Conference highlights include

Community Service Day – Saturday, June 26, 8:00 a.m. – 1:00 p.m.
Join ODTUG volunteers and help refurbish a school in D.C.  Under the guidance of Greater DC Cares (GDCC), the leading and largest nonprofit coordinator of volunteerism in the D.C. region, ODTUGgers will: Sort books, beautify school grounds, and paint games on blacktop outside of hte school.

There is still time to sign up!  

Four Full-day Symposia – Sunday, June 27, 8:30 a.m. – 4:00 p.m.
Application Express; Oracle EPM and Essbase; Security, Scalability, and Performance; SOA and BPM. One-day registration available.

Welcome Reception/Battle of the Rock Bands – Sunday, June 27, 6:15 – 8:00 p.m.
Meet the exhibitors and compete in the “Battle of the Rock Bands.” Sign up to play.


Opening General Session – Monday, June 28, 8:30 – 10:00 a.m.
Awards for Best Technical Paper and Best 2009 Presentations
Keynote – “Future of the Internet and its Social Impact” by Lee Rainie, Director of the PEW Research Center’s Internet & American Life Project.
Sundown Sessions with Oracle ACE Directors – Monday, June 28, 5:45 – 6:45 p.m.
Reception to meet the Oracle ACE Directors immediately follows – 6:45 – 7:45 p.m.

Special Event – Wednesday, June 30, 6:30 – 10:00 p.m.
Featuring comedian John Heffron, 2nd season champion of the hit TV show, Last Comic Standing.
Music by live cover band, Right Foot Red

Oracle resources for the MySQL Community

Tuesday, June 8th, 2010

While I have spent a lot of time recently helping the MySQL community interact with and integrate with various Oracle User Groups including ODTUG, IOUG, NoCOUG, NYOUG, DAOG I thought I’d share some resources for the MySQL Community that wanted to know more about Oracle.

The Oracle family of products is huge. You only have to look at the acquisitions via Wikipedia to get an idea. The first thing is to narrow your search, e.g. Database, APEX, Middleware, BI, Hyperion, Financials, development via Java, PHP or Oracle Forms etc.

While Oracle is a commercial product you can download all software for FREE via Oracle Technology Network. There is also documentation, forums, blogs and events.

Some Oracle bloggers I have already been reading however I’m expanding my list. People you may want to consider include:

Cary Millsap,Lewis Cunningham, Debra Lilley, Dimitri Gielis,Duncan Mills, Edward Roske, Mark Rittman, Scott Spendolini, Tim Tow, Tom Kyte

If you want a comparison of the Oracle and MySQL community, be sure to also check out Sheeri Cabral’s keynote address at the 2010 MySQL User Conference for reference.

I’ll have a MySQL shot to go!

Sunday, June 6th, 2010

Wednesday night of the MySQL track of ODTUG Kaleidoscope will include an evening with Last Comic Standing comedian, John Heffron. It should be great way to unwind after day 3 of the conference. Black vodka anybody.

Check out the MySQL Schedule for more information of presentations for the 4 days. More details is also available here.

The code word is?

Friday, May 28th, 2010

For ODTUG readers, the code word is “Wombat”. Hope to meet many of you next month.

Finalized speakers list for Kaleidoscope conference

Monday, May 17th, 2010

We have secured approval for our final two speakers and now have a full schedule for the 4 day MySQL track at ODTUG Kaleidoscope conference. The conference is in Washington DC from Monday June 28th to Thursday July 1st. Welcome to Josh Sled and Craig Sylvester that will be joining our existing list of speakers.

This conference will include 19 sessions of dedicated MySQL content from Monday thru Thursday by well qualified MySQL community members, as well a forums discussion and reception on Monday night. You don’t need to be an Oracle developer to get the benefit of this conference. We will offering a discount code for MySQL attendees in the upcoming days.

If you are in the DC area, the Monday night forum (known as the sundown sessions) as well as the reception are FREE for the MySQL community. This was a great jesture of the Oracle Developer Tools Users Group to openly invite the MySQL community to meet and interact. We ask that you register your name and email for confirmation of numbers.

Speakers List

  • Philip Antoniades, Oracle/MySQL
  • Ronald Bradford, 42SQL
  • Sheeri K. Cabral, The Pythian Group
  • Laine Campbell, PalominoDB
  • Patrick Galbraith, Northscale
  • Sarah Novotny, Blue Gecko
  • Padraig O’Sullivan, Akiba Technologies Inc.
  • Jay Pipes, Rackspace Cloud
  • Dossy Shiobara, Panoptic.com
  • Josh Sled, Oracle/MySQL
  • Craig Sylvester, Oracle/MySQL
  • Matt Yonkovit, Percona

References

Free MySQL Event in Washington DC

Friday, May 7th, 2010

As the program chair for the recently announced MySQL Track at the ODTUG Kaleidoscope conference located in Washington DC we are also looking into an associated free community event for MySQL locals in addition to a dedicated track for 4 days.

Please let us know your name and email via the form at http://ronaldbradford.com/ODTUG/free-event/ so we can provide more details in the coming week as we try to finalize logistics.

Registration will be necessary for attendance however for now we just want to know who is local so we can provide more details soon!

Updated. Full details of the free Monday night sundown sessions and reception can be found at MySQL track with free event at Kaleidoscope 2010

The MySQL community impacting the Oracle community

Monday, May 3rd, 2010

I’m happy to announce that the MySQL community has been given the opportunity to speak at the upcoming Oracle Developer Tools User Group (ODTUG) Kaleidoscope conference in Washington DC. We will be releasing more details this week of the MySQL presentations and topics and we are finalizing details of possible options to include the local MySQL community during the event.

The various independent Oracle User Groups in North America that embody “by the community and for the community” have been very positive with including the MySQL community. With the Sun/MySQL now Oracle community team of Giuseppe Maxia, Lenz Grimmer, Kaj Arnö and Oracle ACE Directors Sheeri K Cabral and myself we have been happy with the openness and willingness to include us in the larger Oracle ecosystem.

We’ll announce the schedule when we finalize it, but we have had a great response from an impressive list of speakers.

Additional References

2010 MySQL Conference Presentations

Monday, April 19th, 2010

I have uploaded my three presentations from the 2010 MySQL Users Conference in Santa Clara, California which was my 5th consecutive year appearing as a speaker.

A full history of my MySQL presentations can be found on the Presenting page.

My acceptance with Oracle as ACE Director

Thursday, April 15th, 2010

I hinted last week of my acceptance with Oracle before the formal announcement this week at the MySQL Users Conference, not for a job but as Oracle ACE Director. In today’s State of the MySQL Community keynote by Kaj Arnö I was one of the first three MySQL nominees that are now part of this program.

What exactly is an ACE Director? Using the description from the Oracle website.

Oracle ACEs and Oracle ACE Directors are known for their strong credentials as Oracle community enthusiasts and advocates, with candidates nominated by anyone in the Oracle Technology and Applications communities. The baseline requirements are the same for both designations; however, Oracle ACE Directors work more closely and formally with Oracle in terms of their community activity.

What does this mean to me?

As a significant contributor to the community I now have the opportunity to continue as well as to contribute to how Oracle continues to interact, promote and involve the MySQL community. As stewards our role as an Oracle ACE Director is to be actively involved. I look forward to the challenge to help shape and improve our State of the MySQL Community.

News and References
Welcome, Oracle ACE Directors for MySQL

My acceptance with Oracle

Thursday, April 1st, 2010

There have been a number of April fools jokes today so I thought I’d add my own to the list. While this sounds unexpected it’s actually no joke.

I just accepted a position with Oracle yesterday but I can’t say any more about the details until the MySQL users conference in a few weeks.

A special thanks to Lenz, Kaj & Giuseppe that championed everything to make it all happen, I really didn’t have to do anything other then accept.

Don’t Assume – Per Session Buffers

Monday, March 8th, 2010

MySQL has a number of global buffers, i.e. your SGA. There are also a number of per session/thread buffers that combined with other memory usage constitutes an unbounded PGA. One of the most common errors in mis-configured MySQL environments is the setting of the 4 primary per session buffers thinking they are global buffers.

Global buffers include:

    The four important per session buffers are:

    I have seen people see these values > 5M. The defaults range from 128K to 256K. My advice for any values above 256K is simple. What proof do you have this works better? When nothing is forthcoming, the first move is to revert to defaults or a maximum of 256K for some benchmarkable results. The primary reason for this is MySQL internally as quoted by Monty Taylor – for values > 256K, it uses mmap() instead of malloc() for memory allocation.

    These are not all the per session buffers you need to be aware of. Others include thread_stack, max_allowed_packet,binlog_cache_size and most importantly max_connections.

    MySQL also uses memory in other areas most noticeably in internal temporary tables and MEMORY based tables.

    As I mentioned, there is no bound for the total process memory allocation for MySQL, so some incorrectly configured variables can easily blow your memory usage.

    References

    About “Don’t Assume”

    “Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.

    For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.

    The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.

Don’t Assume – Data Integrity

Saturday, March 6th, 2010

MySQL has the same level of data integrity for numbers and strings as Oracle; when MySQL is correctly configured. By default (a reason I wish I knew why it is still the default), MySQL performs silent conversions on boundary conditions of data that will result in your data not always being what is specified. Let’s look at the following examples to demonstrate default behavior.

For numbers

mysql> DROP TABLE IF EXISTS example;
mysql> CREATE TABLE example(i1  TINYINT, i2 TINYINT UNSIGNED, c1 VARCHAR(5));
mysql> INSERT INTO example (i1) VALUES (1), (-1), (100), (500);
Query OK, 4 rows affected, 1 warning (0.08 sec)
mysql> SELECT * FROM example;
+------+------+------+
| i1   | i2   | c1   |
+------+------+------+
|    1 | NULL | NULL |
|   -1 | NULL | NULL |
|  100 | NULL | NULL |
|  127 | NULL | NULL |
+------+------+------+
4 rows in set (0.00 sec)

As you can see for one value we inserted 500, yet the value of 127 is stored? For this example I have used the TINYINT numeric data type to demonstrate truncation. TINYINT is a 1 byte integer that stores values from -128 to +127. Unlike Oracle, MySQL has 9 different data types for numeric columns, and using these wisely can improve your database disk footprint, for example BIGINT v INT. Is there a big deal?.

MySQL also has a nice feature for numeric data types, the UNSIGNED attribute that ensures only a positive integer or 0 value. Let’s see what happens with this column.

Unsigned

mysql> TRUNCATE TABLE example;
mysql> INSERT INTO example (i2) VALUES (1), (-1), (100), (500);
Query OK, 4 rows affected, 2 warnings (0.00 sec)
mysql> SELECT * FROM example;
+------+------+------+
| i1   | i2   | c1   |
+------+------+------+
| NULL |    1 | NULL |
| NULL |    0 | NULL |
| NULL |  100 | NULL |
| NULL |  255 | NULL |
+------+------+------+
4 rows in set (0.00 sec)

Now you see that -1 and 500 are now not the expected values, and before while 500 was silently truncated to 127, now it’s truncated to 255.

For Strings

As you can now assume, the following also occurs for strings.

mysql> TRUNCATE TABLE example;
mysql> INSERT INTO example (c1) VALUES (NULL),('a'),('abcde'),('xyz12345');
Query OK, 4 rows affected, 1 warning (0.00 sec)
mysql> SELECT * FROM example;
+------+------+-------+
| i1   | i2   | c1    |
+------+------+-------+
| NULL | NULL | NULL  |
| NULL | NULL | a     |
| NULL | NULL | abcde |
| NULL | NULL | xyz12 |
+------+------+-------+
4 rows in set (0.00 sec)

Show warnings

As you can see here, the mysql client shows that warnings occurred, but if you don’t review the warning you would never know, a situation that is rarely reviewed with development in richer programming languages. Let us look at these actual warnings more closely.

mysql> INSERT INTO example (i1) VALUES (1), (-1), (100), (500);
Query OK, 4 rows affected, 1 warning (0.08 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 4 |
+---------+------+---------------------------------------------+

mysql> INSERT INTO example (i2) VALUES (1), (-1), (100), (500);
Query OK, 4 rows affected, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i2' at row 2 |
| Warning | 1264 | Out of range value for column 'i2' at row 4 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO example (c1) VALUES (NULL),('a'),('abcde'),('xyz12345');
Query OK, 4 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 4 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

Using sql_mode

The solution is the sql_mode configuration variable and at minimum the value of STRICT_ALL_TABLES defined. We can demonstrate the expected behavior with the following syntax.

mysql> set SESSION sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE TABLE example;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO example (i1) VALUES (1), (-1), (100), (500);
ERROR 1264 (22003): Out of range value for column 'i1' at row 4
mysql> SELECT * FROM example;
+------+------+------+
| i1   | i2   | c1   |
+------+------+------+
|    1 | NULL | NULL |
|   -1 | NULL | NULL |
|  100 | NULL | NULL |
+------+------+------+
3 rows in set (0.00 sec)

As you can see, even with an error for a single INSERT statement, some data was actually stored. You should read Don’t Assume – Transactions for some insights here.

When it comes to dates, there is greater complexity and this is grounds for another entry of this series.

References

About “Don’t Assume”

“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.

For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.

The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.

Don’t Assume – Transactions

Thursday, March 4th, 2010

MySQL by default is a NON transactional database. For the hobbyist (See The Hobbyist and the Professional), startup entrepreneur and website developer this may not appear foreign, however to the seasoned Oracle DBA who has only used Oracle the concept is very foreign.

In MySQL you have to be concerned with two situations that will catch the unprepared out. The first is the default autocommit mode. This is TRUE, i.e. all statements are automatically committed on completion.

mysql> SELECT @@autocommit,TRUE;
+--------------+------+
| @@autocommit | TRUE |
+--------------+------+
|            1 |    1 |
+--------------+------+
1 row in set (0.00 sec)

The second is the storage engine used. Again a foreign term for Oracle DBA’s, a storage engine is a technology that stores and retrieves the underlying data from the MySQL database. MySQL has many different storage engines, each with relative strengths and weaknesses and different features. For the purpose of this discussion it is important to know that engines are either non-transactional or transactional. The default storage engine MyISAM is NON transactional. MySQL provides by default the InnoDB storage engine which is transactional. There are distinct advantages of a non transactional environment which I will not go into at this time.

Having recently written about this in my upcoiming book Expert PHP and MySQL I will demonstrate what happens with both MyISAM and InnoDB.

Non-transactional Tables

To show the difference, Listing 6-7 demonstrates that atomicity is not possible with non-transactional tables. The following tables are used in this example.

DROP TABLE IF EXISTS non_trans_parent;
CREATE TABLE non_trans_parent (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  val  VARCHAR(10) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY (val)
) ENGINE=MyISAM DEFAULT CHARSET latin1;
DROP TABLE IF EXISTS non_trans_child;
CREATE TABLE non_trans_child (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NOT NULL,
  created   TIMESTAMP NOT NULL,
PRIMARY KEY (id),
INDEX (parent_id)
) ENGINE=MyISAM DEFAULT CHARSET latin1;

To test things out, perform a sample transaction that inserts records into these two tables:

START TRANSACTION;
INSERT INTO non_trans_parent(val) VALUES(‘a’);
INSERT INTO non_trans_child(parent_id,created) VALUES(LAST_INSERT_ID(),NOW());
INSERT INTO non_trans_parent (val) VALUES(‘a’);
ERROR 1062 (23000): Duplicate entry ‘a’ for key ‘val’
ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+--------+------+--------------------------------------------------------------
| Level  | Code | Message
+--------+------+--------------------------------------------------------------
| Warning| 1196 | Some non-transactional changed tables couldn’t be rolled back
+--------+------+--------------------------------------------------------------
SELECT * FROM non_trans_parent;
+----+-----+
| id | val |
+----+-----+
|  1 | a   |
+----+-----+
SELECT * FROM non_trans_child;
+----+-----------+---------------------+
| id | parent_id | created             |
+----+-----------+---------------------+
|  1 |         1 | 2009–09–21 23:44:25 |
+----+-----------+---------------------+

As you can see, data that you would have expected to not exist from the transaction is present.

Transactional Tables

Repeat these SQL statements using the transactional storage engine InnoDB; you will observe the difference between transactional and non transactional processing. The following tables, shown in Listing 6-8, are used in this example.

DROP TABLE IF EXISTS trans_parent;
CREATE TABLE trans_parent (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  val  VARCHAR(10) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY (val)
) ENGINE=InnoDB DEFAULT CHARSET latin1;
DROP TABLE IF EXISTS trans_child;
CREATE TABLE trans_child (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED NOT NULL,
  created   TIMESTAMP NOT NULL,
PRIMARY KEY (id),
INDEX (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET latin1;

Perform a sample transaction that inserts records into these two tables:

START TRANSACTION;
INSERT INTO trans_parent (val) VALUES(‘a’);
INSERT INTO trans_child (parent_id,created) VALUES(LAST_INSERT_ID(),NOW());
INSERT INTO trans_parent (val) VALUES(‘a’);
ERROR 1062 (23000): Duplicate entry ‘a’ for key ‘val’
ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
SELECT * FROM trans_parent;
Empty set (0.00 sec)
SELECT * FROM trans_child;
Empty set (0.00 sec)

As you can see, no data has been recorded as part of the failing transaction.

About “Don’t Assume”

“Don’t Assume” is a series of posts to help the Oracle DBA understand, use and appreciate the subtle differences and unique characteristics of the MySQL RDBMS in comparison to Oracle. These points as essential to operate MySQL effectively in a production environment and avoid any loss of data or availability.

For more posts in this series be sure to follow the mysql4oracledba tag and also watch out for MySQL for Oracle DBA presentations.

The MySQLCamp for the Oracle DBA is a series of educational talks all Oracle DBA resources should attend. Two presentations from this series IGNITION and LIFTOFF will be presented at the MySQL Users Conference 2010 in Santa Clara, April 2010 This series also includes JUMPSTART and VELOCITY. If you would like to here these presentations in your area, please contact me.