Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 61 to 69

Displaying posts with tag: information_schema (reset)

Blank VIEW_DEFINITION?
+0 Vote Up -0Vote Down

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';
  [Read more...]
Inspect the Query Cache using MySQL Information Schema Plug-ins
+0 Vote Up -0Vote Down
A while ago I wrote about MySQL 5.1 information schema plug-ins.

At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.

I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.

Inside the source file, there's instructions to build





  [Read more...]
Discovering FALCON Metadata in MySQL® v. 6.0.5-alpha
+0 Vote Up -0Vote Down

Introduction
MySQL® 6.0.5-alpha, the latest version of the 6.x branch of the Database Server, is available for download from the SUN|MySQL Web Site.

Metadata (data about the data) are very important, especially for software developers. In this article we will see what’s new in FALCON metadata handling doing some comparison with the old 6.0.4-alpha version.

New tables in the `information_schema` database
As you know, the source for metadata is the database `information_schema`. To start, let’s see which tables related with FALCON metadata are included in that database:

mysql> SELECT VERSION();
+—————————+
|



  [Read more...]
MySQL UC2008 presentation "Grand Tour of the information schema" now online
+0 Vote Up -0Vote Down
Yup, the presentation slides as well as the scripts are now available online on the conference website.

The stuff you will find in there:
Information_schema.pdfDiagram of the information schema
I_S_VC_Slides.pdfSlides for the UC presentation, "Grand Tour of the Information Schema"
I_S_INDEXES2.sqlscript, returns one row for each index (rollup of information_schema.STATISTICS)
I_S_REDUNDANT_INDEXES2.sqlscript, lists all redundant indexes. Redundancy rules:

  • two indexes with the same columns, type and uniqueness are interchangeable. The one with the largest index name is listed as redundant

  • if there is a unique index and a non unique index with the same columns and type exists, the non-unique one is considered redundant








  [Read more...]
Liveblogging: Extending MySQL by Brian ?Krow? Aker
+0 Vote Up -0Vote Down

Liveblogging: Extending MySQL by Brian “Krow” Aker

Brian wins the award for “most frequent great quotes during a talk”.

Before MySQL 5.1 a UDF was the only way to extend MySQL.

All you need in a UDF is: init() execute() deinit()

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args

WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.

“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”

In MySQL 5.1, you can now install plugins (example is



  [Read more...]
"Me Too" MySQL Information Schema popquiz
+0 Vote Up -0Vote Down
A few days ago, I wrote how I will be your guide to the Grand Tour of the Information Schema and its Applications which is one of the two talks I will be doing at the upcoming MySQL User's Conference.

In view of the popularity of "Pop Quiz" format so successfully used by Carsten, I feel compelled to imitation, and as a primer to my talk, I'd like to offer you my "Me Too" MySQL Information Schema popquiz. So, here goes...





  [Read more...]
MySQL Information Schema applications at the UC2008
+0 Vote Up -0Vote Down
Last week I blogged about the upcoming MySQL Users conference, in particular about the Writing MySQL UDFs tutorial that I will be delivering.

I will also be doing the Grand Tour of the Information Schema and its Applications.


I will discuss the elements in the




  [Read more...]
Using information_schema to write code for you
+0 Vote Up -0Vote Down
Back when I was doing a lot of work with Oracle, I learned to lean heavily on the SYSTEM views - Oracle’s equivalent to the INFORMATION_SCHEMA database. These views can really help you when it comes to writing dynamic SQL in stored procedures, or just taking quick shortcuts while you’re writing code. Or, if you’re [...]
INFORMATION_SCHEMA Support in MySQL, PostgreSQL
+0 Vote Up -0Vote Down

I've known about the INFORMATION_SCHEMA views (or system tables) in SQL Server for a while, but I just leared recently that they are actually part of the SQL-92 standard and supported on other database platforms.

The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. Because the structure of these tables are standardized you can write SQL statements that work on various database platforms.

For example suppose you want to return a resultset with a list of all columns in a table called employees

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'employees'

Quite a handy feature, but it's hard to find what versions the of various database platforms started supporting this feature, here's a quick

  [Read more...]
10 Newer Entries Showing entries 61 to 69

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.