Pythian Group

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.

Read the rest of this entry . . .

By now you know that there is a MySQL Track during next week’s ODTUG Kaleidoscope in Washington, DC. Ronald Bradford and I organized the schedule at the last minute (Ronald did a lot of the work!). It was difficult to fill a schedule with 19 sessions that are either 1 hour or 1.5 hours long, and to do it I ended up with three presentations.

At each presentation I will be giving away a copy of The MySQL Administrator’s Bible, so be sure to show up! All MySQL track sessions are in Maryland C, and all times are Eastern.

On Monday, June 28th from 4 pm – 5:30 pm I will be presenting “What do you mean, SQL Syntax Error?”, a presentation about how MySQL’s SQL syntax extends and deviates from the ANSI/ISO SQL:2003 standard. There is an 80-page PDF accompaniment that will be given out for free during this session.

On Tuesday, June 29th from 11 am to 12 noon I will be presenting Importing and Exporting Data with MySQL, about the many tools to load and bulk load data, and how to export data for regular and bulk loads. I will also be going over which storage engines are particularly well-suited for bulk loading, and the caveats to watch out for. This session is useful for those who know MySQL as well as those asking the question, “What’s the equivalent of Oracle’s SQL Loader for MySQL?”

On Wednesday, June 30th from 8:30 am to 9:30 am I will be presenting Navigating MySQL Stored Procedures & Functions, Views and Triggers, which covers all the ways stored procedures, stored functions, views and triggers can be used, including a highlight of Oracle differences.

I hope to see you there!

As I putter around the MySQL INFORMATION_SCHEMA, I am finding lots of undocumented behavior for fields that should be straightforward. For example, the VIEWS table holds information about views, and the VIEW_DEFINITION field contains the view definition, right?

Well, when I was looking at the VIEW_DEFINITION today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the SHOW CREATE VIEW command), the INFORMATION_SCHEMA.VIEWS table sometimes came up blank for the VIEW_DEFINITION. I had to figure out why, and now that I know, I’m not sure if it’s a bug or a feature…..can you figure it out?

mysql> USE INFORMATION_SCHEMA;
Database changed
mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila';

+----------------------------+-----------------+
| TABLE_NAME                 | VIEW_DEFINITION |
+----------------------------+-----------------+
| actor_info                 |                 |
| customer_list              |                 |
| film_list                  |                 |
| nicer_but_slower_film_list |                 |
| sales_by_film_category     |                 |
| sales_by_store             |                 |
| staff_list                 |                 |
+----------------------------+-----------------+
7 rows in set (0.16 sec)

mysql> SHOW CREATE VIEW sakila.actor_info\G
*************************** 1. row ***************************
                View: actor_info
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY INVOKER VIEW `sakila`.`actor_info` AS select `a`.`actor_id` AS `actor_i
d`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(
distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`t
itle` ASC separator ', ') AS `GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ',
 ')` from ((`sakila`.`film` `f` join `sakila`.`film_category` `fc` on((`f`.`film
_id` = `fc`.`film_id`))) join `sakila`.`film_actor` `fa` on((`f`.`film_id` = `fa
`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_
id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info`
from (((`sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor
_id` = `fa`.`actor_id`))) left join `sakila`.`film_category` `fc` on((`fa`.`film
_id` = `fc`.`film_id`))) left join `sakila`.`category` `c` on((`fc`.`category_id
` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name
`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.02 sec)

mysql> SHOW CREATE VIEW sakila.customer_list\G
*************************** 1. row ***************************
                View: customer_list
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `sakila`.`customer_list` AS select `cu`.`customer_id` AS `
ID`,concat(`cu`.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address`
AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`ci
ty`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,if(`cu`.`active`,
_utf8'active',_utf8'') AS `notes`,`cu`.`store_id` AS `SID` from (((`sakila`.`cus
tomer` `cu` join `sakila`.`address` `a` on((`cu`.`address_id` = `a`.`address_id`
))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `
sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country
_id`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

(some people read the dictionary, I read the data dictionary!)