MySQL 5.0 DBA - Review

Today, I launched this blog and became aware of MySQL 5.0 being released. I have decided to upgrade MySQL from 3.23.56 to 5.0. This post will contain my review and the next post will include my upgrade to MySQL 5.0 details.

First some exciting stuff about MySQL

QUICK LOOKS: (MySQL 5.0 Whitepaper)

• MySQL has six million active installations worldwide
• 40,000 MySQL downloads occur every day.
  

Countless corporations, educational facilities, and government agencies rely on MySQL to manage their critical data assets and service anywhere from hundreds to billions of requests per day. With over six million active installations worldwide and 40,000 downloads occurring every day, the popularity and prominence of MySQL continue to rise.

reliability, performance and ease-of-use are "the three priorities of the MySQL database server."

ACID Transactions – MySQL supports ACID (Atomic, Consistent, Isolated, Durable) transaction model that ensures the integrity and validity of every entered transaction. Full COMMIT and ROLLBACK, with redo logs and undo (rollback) segment areas to ensure data consistency during crash recovery operations.
• Server-Based Data Integrity – Invalid data (bad dates, invalid numbers, etc.) can be automatically rejected at the server, with column-level rules enforcement being possible. In addition, full foreign-key support is provided so that complete data referential integrity is guaranteed.
• Low Contention Issues – Unlimited row-level locking is provided within InnoDB as is automatic deadlock detection, custom isolation levels (READ COMMITTED, etc.), to virtually eliminate locking issues. In addition, InnoDB supports multi-version concurrency control so that those reading data see a snapshot of the requested information as it existed at the moment their query was issued. This ability ensures that readers don’t block writers and vice-versa, and leads to extremely high levels of data availability.

• Support for stored procedures
• Fast performance
• Easier Security Administration
• Fast Performance
• Reduced Network Traffic

Using MySQL 5.0 Stored procedures

• very easy to use
• adhere to the ANSI SQL 2003 specification
• standoutput ability for SELECT statements
• 
  

Creating MySQL Stored Procedure

delimiter //
create procedure myproc()
select * .....;
//
delimiter;
call top_broker();

delimiter //
create function broker_name(id int)
returns varchar(50)
deterministic
begin
declare broker_name(VARCHAR (50)
select * into broker_name from broker .....;
return broker_name;
end
//
delimiter;
select top_broker(1);

• "SHOW PROCEDURE STATUS " is now possible
• mysql.proc can be queried
• 5.0 INFORMATION_SCHEMA data dictionary
• SHOW CREATE PROCEDURE function (to create DDL)