Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 52

Displaying posts with tag: common_schema (reset)

MySQL error handling on server side: a NO GO!
+3 Vote Up -2Vote Down

There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went wrong.

To illustrate, consider the following query:

INSERT INTO my_table (my_column) VALUES (300);

What could go wrong with this query?

  • We might hit a UNIQUE KEY violation
  • Or a FOREIGN KEY violation
  • my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
  • Or, similarly, it could be an ENUM (2,3,5,8)

Is that it? Not remotely:

  • This could be a read-only MyISAM table
  • We may have issued a LOCK TABLES my_table READ -- this violates our lock
  • Or this could be an
  [Read more...]
+3 Vote Up -0Vote Down

Have just read INSERT, Don’t DELETE by Aaron Brown, and have some lengthy response, which is why I write this post instead of commenting on said post.

I wish to offer my counter thought and suggest that DELETEs are probably the better choice.

Aaron suggests that, when one wishes to purge rows from some table, a trick can be used: instead of DELETEing unwanted rows, one can INSERT "good" rows into a new table, then switch over with RENAME (but please read referenced post for complete details).

I respectfully disagree on several points discussed.


The fact one needs to block writes during the time of creation of new table is problematic: you need to essentially turn off parts of your application. The posts suggests one could

  [Read more...]
Who is hogging my MySQL connections?
+3 Vote Up -0Vote Down

Got "too many connections" this morning. New attempts continuously abort. Every once in a while some slipped through, but overall behavior was unacceptable.

max_connections is set to 500, well above normal requirements.

Immediate move: raise max_connections to 600, some urgent connections must take place. But, this is no solution: if 500 got hogged, so will the extra 100 I've just made available.

So, who's to blame? SHOW PROCESSLIST is so unfriendly at that. Wait. Didn't I create that view in common_schema, called processlist_per_userhost? I wonder what it says...

  [Read more...]
On stored routines and dynamic statements
+4 Vote Up -0Vote Down

I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!

I'm dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.

Still hush hush, the development of that feature touched at the very same issues described in Michael's post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a

  [Read more...]
Impact of foreign keys absence on replicating slaves
+2 Vote Up -0Vote Down

In this post I describe what happens when a slave's Foreign Key setup is different from that of the master. I'm in particular interested in a setup where the slave has a subset of the master's foreign keys, or no foreign keys at all. I wish to observe whether integrity holds.

Making the changes

Which foreign keys do we have and how do we drop them? If you want to do this by hand, well, good luck! Fortunately, common_schema provides with quite a few handy views and routines to assist us. Consider viewing the existing foreign keys on sakila:

master> SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila';
  [Read more...]
common_schema talk at Percona Live
+1 Vote Up -0Vote Down

Are you attending PerconaLive?

Allow me to suggest you attend the Common Schema: a framework for MySQL server administration session on April 12, 14:00 - 14:50 @ Ballroom F.

This talk is by none other than Roland Bouman. Roland co-authored parts of common_schema, and is a great speaker.

I have a personal interest, of course, being the author of most of the components in common_schema. I would like to convert you to a supporter of this project. I know a few very smart people who think this project is an important tool. I would like more people to get to know it. Eventually, I would like developers and DBAs alike to consider it

  [Read more...]
MySQL/QueryScript use case: DELETE all but top N records per group
+2 Vote Up -0Vote Down

Some administrative tasks can be simplified by using common_schema/QueryScript. I'm collecting a bunch of these for documentation. Here's one for example:

The DBA/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, ... most populated counties in each continent.

Is it possible to work out with a single query? Yes. But the query is not pretty. In fact, it is quite complicated, and either involves unintuitive subqueries, or unintuitive hacks. A normal DBA would not want to write, neither maintain this kind of query, unless top-notch-geek, which

  [Read more...]
common_schema rev. 218: QueryScript, throttling, processes, documentation
+0 Vote Up -0Vote Down

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

  • QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
  • Throttling for queries is now made available via the throttle() function.
  • Enhancements to processlist-related views, including the new slave_hosts view.
  • Inline documentation/help is available via the help() routine.
  • more...


common_schema makes for a QueryScript implementation for MySQL. You can run server side

  [Read more...]
QueryScript: SQL scripting language
+2 Vote Up -0Vote Down

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
  throttle 2;

Convert all InnoDB tables in the 'sakila' database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
  if ($engine = 'InnoDB')
  [Read more...]
Documentation in SQL: CALL for help()
+1 Vote Up -0Vote Down

Documentation is an important part of any project. On the projects I maintain I put a lot of effort on documentation, and, frankly, the majority of time spent on my projects is on documentation.

The matter of keeping the documentation faithful is a topic of interest. I'd like to outline a few documentation bundling possibilities, and the present the coming new documentation method for common_schema. I'll talk about any bundling that is NOT man pages.

High level: web docs

This is the initial method of documentation I used for openark kit and mycheckpoint. It's still valid for mycheckpoint. Documentation is web-based. You need Internet access to read it. It's in HTML

  [Read more...]
More MySQL foreach()
+4 Vote Up -0Vote Down

In my previous post I've shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema.

In this part I present DBA's handy syntax for schema and table operations and maintenance.

Confession: while I love INFORMATION_SCHEMA's power, I just hate writing queries against it. It's just so much typing! Just getting the list of tables in a schema makes for this heavy duty query:


When a join is involved this really becomes a nightmare. I think it's

  [Read more...]
MySQL foreach()
+0 Vote Up -0Vote Down

A new routine is now available in common_schema, which makes for an easier execution syntax for some operations:

foreach(collection_to_iterate_over, queries_to_execute_per_iteration_step);

To illustrate what it can do, consider:

call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPACT');

call $('schema like shard_%', 'CREATE TABLE ${schema}.messages (id INT)');

call $('2000:2009', 'INSERT IGNORE INTO report (report_year) VALUES (${1})');

$() stands as a synonym to foreach(). I suspect it should look familiar to web programmers.

The idea for foreach() was introduced by Giuseppe Maxia during a

  [Read more...]
Common Schema: dependencies routines
+3 Vote Up -1Vote Down
Are you a MySQL DBA? Checkout the common_schema project by Oracle Ace Shlomi Noach.

The common_schema is an open source MySQL schema that packs a number of utility views, functions and stored procedures. You can use these utilities to simplify MySQL database administration and development. Shlomi just released revision 178, and I'm happy and proud to be working together with Shlomi on this project.

Among the many cool features created by Shlomi, such as foreach, repeat_exec and exec_file, there are a few %_dependencies procedures I contributed:

      [Read more...]
    common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis
    +1 Vote Up -0Vote Down

    common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.

    common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.

    There's no Perl nor Python, and no dependencies to install. It's just a schema.

    Some highlights for the new revision:

    • foreach(), aka $(): loop through a collection,
      [Read more...]
    Reading results of SHOW statements, on server side
    +5 Vote Up -0Vote Down

    SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side.

    On server side, that is, from within MySQL itself, one cannot:


    One cannot:


    One cannot:

    SHOW TABLES INTO OUTFILE '/tmp/my_file.txt';

    So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading...

    Bwahaha! A hack!

    For some SHOW

      [Read more...]
    Test-driven SQL development
    +1 Vote Up -0Vote Down

    I'm having a lot of fun writing common_schema, an SQL project which includes views, tables and stored routines.

    As the project grows (and it's taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I've turned the development on common_schema to be test driven.

    Now, just how do you test drive an SQL project?

    Well, much like the way you test any other project in your favorite programming language. If its functions you're testing, that's all too familiar: functions get some input and provide some output. Hmmm, they might be changing SQL data during that time. With procedures it's slightly more complex, since they do not directly return output but result sets.

    Here's the testing scheme

      [Read more...]
    common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()
    +3 Vote Up -0Vote Down

    Revision 68 of common_schema is out, and includes some interesting features:

    • eval(): Evaluates the queries generated by a given query
    • match_grantee(): Match an existing account based on user+host
    • processlist_grantees: Assigning of GRANTEEs for connected processes
    • candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
    • easter_day(): Returns DATE of easter day in given DATETIME's year.

    Let's take a slightly closer look at these:


    I've dedicated this blog post on MySQL eval() to describe it. In simple

      [Read more...]
    MySQL eval()
    +4 Vote Up -0Vote Down

    I've just implemented an eval() call for MySQL. It is implemented with SQL, using a stored procedure. So this is not some plugin: you can use it from within your normal database server.

    Just what is an eval() call?

    In some programming languages it would mean: get some text, and execute it as though it were complied code. So, dynamic coding.

    In SQL: get the text of query which generates SQL statements in itself (either DML or DDL), and invoke those implied SQL statements.

    A simple example

    Best if I present Mass killing of MySQL Connections by Peter Zaitsev. The thing is to execute a query, typically on INFORMATION_SCHEMA, which uses metadata so as to generate SQL queries/commands. Peter's example is:

      [Read more...]
    MySQL Global status difference using single query
    +1 Vote Up -0Vote Down

    Have just read MySQL Global status difference using MySQL procedures / functions, by Andres Karlsson. Have commented, but realized I did not provide with a direct answer. In the comment, I suggested checking out a solution based on views, found in common_schema. But the solution in common_schema is split into two views, due to the fact views cannot handle derived tables subqueries.

    Well, here's a single query to do that: it checks GLOBAL_STATUS twice, 10 seconds apart in the following sample. It uses SLEEP() to actually wait between the

      [Read more...]
    Finding CURRENT_USER for any user
    +4 Vote Up -0Vote Down

    A MySQL account is a user/host combination. A MySQL connection is done by a user connecting from some host.

    However, the user/host from which the connection is made are not the same as the user/host as specified in the account. For example, the account may be created thus:

    CREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456';

    The host as specified in the above account is a wildcard host. A connection by the 'temp' user from '' can map into that account. It thus happens that the connected user is 'temp'@'', yet the assigned account is 'temp'@'10.0.0.%'.

    MySQL provides with the USER() and CURRENT_USER() which map to the connected user and the assigned account, respectively, and which lets the current session

      [Read more...]
    common_schema: looking for contributions
    +1 Vote Up -0Vote Down

    In my announcement for common_schema I have failed to deliver the following message:

    I will be happy to receive contributions to common_schema, and I will be happy to have contributors on this project

    What kind of contributions are wanted?

    So, I'm mostly interested right now in:

    • Views: providing more insight on metadata (data types, schemata, volumes, transactions, locks, connections, etc.)
    • Views: SQL code generation (e.g. generate my FOREIGN KEYs)
    • Functions: DETERMINISTIC, NO SQL, preferably no control flow inside, making
      [Read more...]
    Announcing common_schema: common views & routines for MySQL
    +1 Vote Up -0Vote Down

    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
      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 52

    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.