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 { …
  [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? …

  [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 …

  [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 …

  [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 …

  [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 …

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

  [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 …

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