December 2005’s feature story on MySQL 5 established the foundation of a stock tracking application using stored procedures, views, and triggers (available online at http://www.linux-mag.com/2005-12/mysql.html.) The February 2006 issue of Linux Magazine presented MySQL 5’s stored functions and cursors (see http://www.linux-mag.com/2006-02/mysql.html.) If you didn’t retain the database constructed in those two articles, use Listing One to (re) create the database and then follow along.[ You can download the SQL scripts and code used throughout this article from http://www.linux-mag.com/downloads/2006-06/source.tgz.]
LISTING ONE: A database schema and data for stock tracking
DROP DATABASE IF EXISTS stock_app; CREATE DATABASE stock_app; USE stock_app;
CREATE TABLE IF NOT EXISTS Stock ( stock_symbol CHAR(5) NOT NULL , name VARCHAR(30) NOT NULL , PRIMARY KEY pk_stock (stock_symbol) );
CREATE TABLE IF NOT EXISTS StockPriceHistory ( stock CHAR(5) NOT NULL , time_taken DATETIME NOT NULL , price DECIMAL(9,4) NOT NULL , PRIMARY KEY pk_stock_price_history (stock, time_taken) );
CREATE TABLE IF NOT EXISTS Watchlist ( watchlist_id SMALLINT UNSIGNED NOT NULL , name VARCHAR(30) NOT NULL , PRIMARY KEY pk_watchlist (watchlist_id) );
CREATE TABLE IF NOT EXISTS WatchlistStock ( watchlist SMALLINT UNSIGNED NOT NULL , stock CHAR(5) NOT NULL , quantity_owned INT UNSIGNED NOT NULL , PRIMARY KEY pk_watchlist_stock (watchlist, stock) );
DELIMITER //
CREATE PROCEDURE stock_app.InsertStockPrice ( IN symbol CHAR(5) , IN current_price DECIMAL(9,4) ) DETERMINISTIC BEGIN INSERT INTO StockPriceHistory (stock, price, time_taken) VALUES (symbol, current_price, NOW()); END //
INSERT INTO WatchlistStock (watchlist, stock, quantity_owned) SELECT 1, stock_symbol, 50.00 FROM Stock; INSERT INTO WatchlistStock (watchlist, stock, quantity_owned) VALUES (2, ’AMD’, 300.00) , (2, ’INTC’, 450.00) , (2, ’RHAT’, 50.00);
Fetching Data About Your Data
With the release of MySQL 5, you can now retrieve a wealth of information about your database using the information_schema, a virtual database filled with metadata, or data about your data. (If you have experience with another database system, you may be familiar with the concept of metadata, albeit by a different name, such as” data dictionary” or” system catalog.” Both data dictionary and system catalog are synonymous with information_schema.)
information_schema is virtual, because its data isn’t actually stored anywhere on disk. Much like a database view, information_schema points to information contained elsewhere, both in the MySQL server and in other databases.
If you’re well-versed in MySQL, you may be wondering why you should go to the trouble of learning and using information_schema when you already have a perfectly good set of SHOW commands that retrieve information about your database and tables. While information_schema is like SHOW, it’s much more powerful.
*information_schema is accessed using standard SELECT statements, allowing you to choose specific columns of data, use WHERE clauses, and JOIN multiple metadata tables. Combining metadata tables with the flexibility of SELECT means unlimited output possibilities.
*Inside information_schema, you’ll find structure, privilege, statistics, and usage information about each MySQL database.
*information_schema is in the SQL:2003 standard, which defines how to retrieve metadata from a database.
Using standard SQL to get metadata means you aren’t required to learn and remember the syntax and options of SHOW. Moreover, a standards-based solution can be applied across many database systems.
All metadata is stored in tables in the information_schema database. For example, Figure One demonstrates how to find the names of all your MySQL databases.
FIGURE ONE: The information_schema table SCHEMATA contains the list of available databases
mysql> use information_schema; Database changed mysql> SELECT SCHEMA_NAME FROM SCHEMATA; +——————–+ | SCHEMA_NAME | +——————–+ | information_schema | | mysql | | test | | stock_app | +——————–+ 4 rows in set (0.01 sec)
By default MySQL 5 comes with three pre-installed databases, or SCHEMATA: information_schema, mysql, and test. Assuming you created stock_app using Listing One, the command SELECT SCHEMA_NAME FROM SCHEMATA lists four databases. As with SHOW, MySQL limits what metadata is shown based on your privileges. If you do not have the rights to see a particular database or table, its information won’t appear in your query results.
To see what kind of information is available about your databases and tables, look at all of the tables available in information_schema.Figure Two shows how it’s done in MySQL 5.
FIGURE TWO: Tables in the information_schema database
Each table has a unique set of fields, providing a wide array of information about your database. Table One describes the purpose of each table.
TABLE ONE: The purpose of the information_schema tables
Table name
Contents
CHARACTER_SETS
Details about the available character sets
COLLATIONS
Information about the available collations
COLLATION_CHARACTER_SET_APPLICABILITY
Relationships between collations and character sets
COLUMNS
Cross-database information about table columns
COLUMN_PRIVILEGES
Column-level privilege information from the mysql.columns table
KEY_COLUMN_USAGE
Information about foreign key usage in tables
ROUTINES
Details regarding stored procedures and functions
SCHEMATA
Specifics about databases in this instance of MySQL
SCHEMA_PRIVILEGES
Database-level privilege information from the mysql.db table
STATISTICS
Information about indexes
TABLES
Cross-database information about tables
TABLE_CONSTRAINTS
Information about key usage in tables
TABLE_PRIVILEGES
Table-level privilege information from the mysql.tables_priv table
TRIGGERS
Cross-database specifics about triggers
USER_PRIVILEGES
User privileges from the mysql.user table
VIEWS
Cross-database specifics about views
For any of the tables listed in Table One,DESCRIBE lists information about the columns stored within that virtual table. For example, Figure Three shows detailed information about the TABLES table.
FIGURE THREE: Description of the TABLES table in information_schema
As you can see, a lot of information is available. Any of these columns can be put into a SELECT statement. For instance, suppose you want to see all the columns used in the stock_app database, across all of its tables. Moreover, you would like to see the average record length and the storage engine used for each table to help determine if the database is tuned approriately. You can easily get this information with a join of the TABLES and COLUMNS tables, as shown in Figure Four.
Joining information across multiple tables gives you limitless flexibility in building reports and customizing the output for your particular needs.
The information_schema is exciting, but may be dauting for for folks habituated to using SHOW commands. To jump start usage of information_schema, refer to Table Two for a list of common SHOW commands and the equivalent SQL statements using information_schema.
TABLE TWO: SHOW commands with equivalent information_schema queries
SHOW command
information_schema query
SHOW DATABASES
SELECT SCHEMA_NAME FROM SCHEMATA
SHOW TABLES
SELECT TABLE_NAME FROM TABLES or SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA= ’& lt;database& gt;’
SHOW INDEX FROM& lt;table& gt;
SELECT* FROM STATISTICS WHERE TABLE_NAME= ’& lt;table& gt;’
SHOW CHARACTER SET
SELECT* FROM information_schema. CHARACTER_SETS
SHOW COLUMNS FROM& lt;table& gt;
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM COLUMNS WHERE TABLE_NAME= ’& lt;table& gt;’
The information_schema virtual database provides a rich set of metadata that’s easy to access with standard SQL.
The Future of information_schema
The information_schema database available in MySQL 5 is full of important information, but the work to develop the metadata is not over. Early pre-releases of 5.1 indicate that there is more to come, including five new tables: PROCESSLIST, PARTITIONS, EVENTS, FILES, and PLUGINS.
Performance data available from SHOW STATUS is one thing you cannot find (yet) in the metadata tables. However, it’s likely to be added in a future version.
Getting Strict with SQL Mode
MySQL has a reputation of being soft when handling invalid or improperly formatted data. Perhaps you’ve been bitten by MySQL silently truncating a field that was too long or changing an improperly formatted date to 0000-00-00. To demonstrate the former error, Listing Five inserts data that gets truncated.
FIGURE FIVE: MySQL can often truncate inserted values
mysql> CREATE TABLE customer ( -> id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(5) -> ); Query OK, 0 rows affected (2.13 sec) mysql> INSERT INTO customer (name) -> VALUES (’MichaelJKruckenberg’); Query OK, 1 row affected, 1 warning (0.13 sec) mysql> SHOW WARNINGS; +———+——+——————————————-+ | Level | Code | Message | +———+——+——————————————-+ | Warning | 1265 | Data truncated for column ’name’ at row 1 | +———+——+——————————————-+ 1 row in set (0.00 sec) mysql> SELECT * FROM customer; +—-+——-+ | id | name | +—-+——-+ | 1 | Micha | +—-+——-+ 1 row in set (0.04 sec)
Inserting MichaelJKruckenberg into name doesn’t raise an error but continues with a warning (seen by using the SHOW WARNINGS command).
Failing to raise an error on data truncation can be frustrating and is likely unacceptable to database analysts and programmers. In some cases, you may prefer that MySQL abort an INSERT when data does not match the specified column restraints.
New SQL mode options in MySQL 5 allow you to change such behavior. For example, you can set a mode so that MySQL raises an error when, among other things, data is truncated. SQL modes tweak MySQL to operate in different ways based on your requirements of specific needs of your application.
SQL modes aren’t new to MySQL — the feature has been around since the early days and has been expanded over the years. In MySQL 4.0 and earlier releases, SQL modes were provided in the configuration files and read at startup. MySQL 4.1 made the options dynamic, tunable at runtime and customizable by each individual client. MySQL 5 had added new SQL modes to enforce data checking. Some of the newest options — those that force MySQL to raise errors on certain events — are referred to in a general sense as strict mode.
There are 30 different SQL mode options for changing MySQL behavior. The most prominent additions in 5.0 are STRICT_ALL_TABLES and STRICT_TRANS_TABLES. Set either of these values and MySQL raises an error instead of a warning if data is invalid, that is when data doesn’t match the data type of the column, is out of range, or doesn’t contain a value for a column that has no default in the table definition. By default, the SQL mode is set to an empty string, as shown in Figure Six.
FIGURE SIX: Showing the default SQL_Mode setting
mysql> SELECT @@SESSION.SQL_MODE; +————+ | @@SQL_MODE | +————+ | | +————+ 1 row in set (0.01 sec)
Setting the SQL mode is as simple as issuing a SET. List multiple options in a comma-separated series. Figure Seven show how to enable STRICT_ALL_TABLES mode.
LISTING SEVEN: Setting the SQL mode to” strict”
mysql> SET @@SESSION.SQL_MODE=STRICT_ALL_TABLES; Query OK, 0 rows affected (0.01 sec) mysql> SELECT @@SESSION.SQL_MODE; +——————–+ | @@SESSION.SQL_MODE | +——————–+ | STRICT_ALL_TABLES | +——————–+ 1 row in set (0.00 sec)
With STRICT_ALL_TABLES enabled, any INSERT or UPDATE with invalid data generates an error. For instance, Figure Eight attempts to insert a record with a value that is too long for the customer.name field.
Session and Global Settings
Many MySQL options, including the SQL mode, can be set globally or on a per-session basis. Global settings are changed by using the @@GLOBAL. variable and apply to all connections to the database. Session settings apply only to the current connection and are specified with @@SESSION. variable.
If you don’t specify the scope in the SET statement, MySQL assymes a session scope. Session variables are removed when the application or client disconnects.
LISTING EIGHT: Inserting data with strict mode enabled
mysql> INSERT INTO customer (name) VALUES (’JayPipes’); ERROR 1406 (22001): Data too long for column ’name’ at row 1
Strict Mode and Transactions
Strict mode behaves differently for a transactional and non-transactional table.
A transactional table raises an error and rolls back the transaction if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.
If a table is non-transactional and either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled, an error occurs only if the first row changed incurs the error. If an error occurs after one or more records have been updated and STRICT_ALL_TABLES is enabled, MySQL raises an error, aborting the query. This leaves some records changed and some unchanged, likely not what you want. However, if STRICT_TRANS_TABLES is set and an error occurs after the first row on a non-transactional table, MySQL default to the non-strict behavior, adjusts the data value, and raises a warning.
These two options, STRICT_ALL_TABLES and STRICT_TRANS_TABLES, are the basis for operating in strict mode, but MySQL has a number of other modes that provide additional safety checks. Table Three contains a list of six other modes that enforce particular behaviors in MySQL 5.
TABLE THREE: SQL mode options
SQL mode
Description
ERROR_FOR_DIVISION_BY_ZERO
Raises an error when division by zero occurs in a query. Without this option, MySQL generates a warning and sets the value to NULL.
NO_AUTO_VALUE_ON_ZERO
When inserting into a table with an auto_increment column, MySQL respects a zero value and won’t substitute it with the incremented integer. This is useful if you want to insert a zero into an auto_increment column.
NO_ENGINE_SUBSTITUTION
MySQL raises an error if the storage engine specified in the CREATE statement isn’t available. Without this option, MySQL substitutes the default storage engine.
NO_ZERO_DATE
Don’t allow 0000-00-00 as a valid date. If you don’t enable this mode, MySQL inserts the data and generates a warning. This mode does allow you to insert a portion of that date as zero.
NO_ZERO_IN_DATE
Don’t allow any part of a data to be 00.
REAL_AS_FLOAT
By default, REAL is a synonym for the DOUBLE datatype. With this option MySQL treat REAL as a synonym for FLOAT.
With a combination of these options, there’s a good chance that your complaints about MySQL’s “leniency” you will be resolved. Use these options wisely, though. If your application is built to run in “lax” mode, may have to make some adjustments to deal with a better-behaving MySQL.
Conclusion
MySQL version 5 boasts a huge set of new features. With metadata and strict mode, you can get metadata from your database in a familiar and flexible way, and control some of MySQL’s internal behavior, respectively. The information_schema and strict mode are just two examples that prove MySQL is leaping forward and becoming a more serious contender in the enterprise database market.
Mike Kruckenberg and Jay Pipes are co-authors of Pro MySQL, recently published by Apress (http://www.apress.com/). Written for both current and potential MySQL users, Pro MySQL offers an exclusive, in-depth look at MySQL 5, including design, development, source code, architecture, and administration.
Loading ...
No Comments
No comments yet.