Skip to content

# Announcing common_schema: common views & routines for MySQL

MySQL Analysis common_schema Data Types Development Indexing INFORMATION_SCHEMA InnoDB Monitoring Open Source Schema Security SQL Stored routines July 13, 2011

Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.

#### What does it do?

There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals… There are basic functions answering for common needs.

Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.

Here are a few highlights:

- Did you know you can work out simple monitoring of your server with a query? There’s a view to do that for you.
- How about showing just the good parts of the processlist?
- Does your schema have redundant keys?
- Or InnoDB tables with no PRIMARY KEY?
- Is AUTO_INCREMENT running out of space?
- Can I get the SQL statements to generate my FOREIGN KEYs? To drop them?
- And can we finally get SHOW GRANTS for all accounts, and as an SQL query?
- Ever needed a 64 bit CRC function?
- And aren’t you tired of writing the cumbersome SUBSTRING_INDEX(SUBSTRING_INDEX(str, ‘,’, 3), ‘,’, -1)? There’s an alternative.

There’s more. Take a look at the common_schema documentation for full listing. And it’s evolving: I’ve got quite a few ideas already for future components.

Some of these views rely on heavyweight INFORMATION_SCHEMA tables. You should be aware of the impact and risks.

#### What do I need to install?

There’s no script or executable file. It’s just a schema. The distribution in an SQL file which generates common_schema. Much like a dump file.

#### What are the system requirements?

It’s just between you and your MySQL. There are currently three distribution files, dedicated for different versions of MySQL (and allowing for increased functionality):

- common_schema_mysql_51: fits all MySQL >= 5.1 distributions
- common_schema_innodb_plugin: fits MySQL >= 5.1, with InnoDB plugin + INFORMATION_SCHEMA tables enabled
- common_schema_percona_server: fits Percona Server >= 5.1

Refer to the documentation for more details.

#### What are the terms of use?

common_schema is released under the BSD license.

#### Where can I download it?

On the common_schema project page. Enjoy it!

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
7
Leave a Reply

7 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Roland Bouman

Hi Shlomi!

great work, looks like a useful toolbox. There’s a few snippets I posted in the way back years to the mysql forge that might be interesting for you. I was thinking about the proc that checks for foreign key violations (to check integrity after disabling foreign key checks) and a proc to create federated tables. I see you already have a redundant key checker.

If you feel like integrating those, drop me a line. If not, that’s ok too.

kind regards,
Roland

Guest
Jonathan Levin

Hey Shlomi

Great idea and great toolkit!

Best Regards
Jonathan

Author
shlomi

@Roland,
Oh, absolutely! I’m looking for contributions, but have failed to mention that. I recall quite a few snippets you wrote at the time; I’d love to have another look.
Let me formalize to myself the criteria for snippets to be in common_schema (licenses aside). Meanwhile, why don’t you send me links to those procs?

Author
shlomi

@Jonathan,
thanks!
(and… congrads? new job?)

Guest
Roland Bouman

Hi Shlomi, here’s a bunch:

mysqldump_partition_backup: generate partition-wise mysqldump commands
http://forge.mysql.com/tools/tool.php?id=258

Obtain the original code for a MySQL view.
http://forge.mysql.com/tools/tool.php?id=246

Creating FEDERATED tables with a stored procedure:
http://forge.mysql.com/tools/tool.php?id=54

Check for violated foreign key constraints
http://forge.mysql.com/tools/tool.php?id=11

Redundant Index Finder
http://forge.mysql.com/tools/tool.php?id=45

(I know you already have a redundant index findex, but this one sports a couple of views to report sane index info that you may find interesting)

Feel free to check it out, and drop me a line if you have a question or need me to fix or modify something.

Guest
Roland Bouman

Oh, there’s also the easter day function, but it didn’t seem to fit directly in the categories you provide.

http://forge.mysql.com/tools/tool.php?id=250

(maybe date/time functions?)

common_schema: looking for contributions | code.openark.org

[…] Announcing common_schema: common views & routines for MySQL […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.