Abstract - As described by Walter Heck, MySQL database size can be visualized using Google Charts. With a minor code improvement the URL for the chart can be obtained twice as fast. With some more modification, the number of lines can be cut down resulting in a function that is half as long.

Hi!It's been a while since I posted - I admit I'm struggling for a bit to balance time and attention to the day job, writing a book, preparing my talks for the MySQL user's conference and of course family life.

A month ago or so I …

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.

In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.

CREATE DATABASE IF NOT EXISTS dba;
USE dba;

DROP PROCEDURE IF EXISTS `dba`.`get_objects`;
DELIMITER $$
CREATE …

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides …

During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:

SELECT table_name article_attachment,
engine,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;

A generally  lite version is:

select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’  from tables order by 3 desc;

You can add or remove columns etc and but this query shows the table size (data wise) index size, approx number of rows, size in MB etc. If you would like to know what else is available to add to this query, just do a “desc tables” while using the …

I’m in the process of converting some very large data tables to partitioned tables. By “In the process” I mean “scripts are running as we speak and I’m monitoring what’s going on.” When I did this in our test environment (2 or 3 times to be sure), I got familiar with the information_schema.partitions table. 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 …

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 and deploy it. If all goes well, you can do …

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();
+—————————+
| VERSION()                             |
…

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.pdf

Diagram of the information schema

I_S_VC_Slides.pdf

Slides for the UC presentation, "Grand Tour of the Information Schema"

I_S_INDEXES2.sql

script, returns one row for each index (rollup of information_schema.STATISTICS)

I_S_REDUNDANT_INDEXES2.sql

script, 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 …

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 memcache_servers plugin):

mysql> INSTALL PLUGIN memcache_servers SONAME …