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

Displaying posts with tag: common_schema (reset)

common_schema 1.1 released: split(), try-catch, killall(), profiling
+2 Vote Up -0Vote Down

I'm very happy to announce the release of common_schema, version 1.1 (revision 300).

This version boasts with compelling new features: innovative QueryScript syntax, libraries, views which add to your skills as a DBA, making some maintenance and management tasks a breeze.

  • QueryScript, split statement: automagically break long queries into smaller chunks, avoid long locks and reduce query/transaction overhead
  • QueryScript, try-catch statement: just try { something; } catch { act_on_error; }.
  • killall(): quickly kill connections based on grantee/user/host information.
  • profiling/profiling_last: utility views to assist in query
  [Read more...]
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...]
DELETE, don't INSERT
+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.

Lockdown

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

SELECT * FROM
  [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...

QueryScript

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')
    ALTER TABLE
  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 53 10 Older Entries

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.