'scripts' Tag

• common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum

  January 14, 2013

  common_schema 1.3 is released and is available for download. New and noteworthy in this version: Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed duplicate_grantee(): copy+paste existing accounts along with their full set of privileges similar_grants: find which accounts share the exact same set [...]

  posted in MySQL by shlomi

• common_schema over traditional scripts

  December 12, 2012

  If you are familiar with both openark kit and common_schema, you'll notice I've incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript. What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples. I'm generally [...]

  posted in MySQL by shlomi

• Killing InnoDB idle transactions

  December 4, 2012

  The issue of terminating long-time idle open InnoDB transaction has been discussed recently by many. I wish to add my share, by proposing a quick and clean solution via common_schema. common_schema 1.2 provides with the innodb_transactions view, which relies on INNODB_TRX - one of the InnoDB Plugin views in INFORMATION_SCHEMA - as well as on [...]

  posted in MySQL by shlomi

• Purging old rows with QueryScript: three use cases

  November 14, 2012

  Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy. You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server [...]

  posted in MySQL by shlomi

• Experimenting with 5.6 InnoDB Online DDL (bugs included)

  October 18, 2012

  MySQL 5.6 offers the groundbreaking online DDL operations for InnoDB. Most common use cases will enjoy this feature, and the need for online alter table scripts will decrease. This is a killer feature! I've put this new feature to the usability test. How did it go? Not too well, I'm afraid. [Updates to this text [...]

  posted in MySQL by shlomi

• How common_schema split()s tables - internals

  September 6, 2012

  This post exposes some of the internals, and the SQL behind QueryScript's split. common_schema/QueryScript 1.1 introduces the split statement, which auto-breaks a "large" query (one which operates on large tables as a whole or without keys) into smaller queries, and executes them in sequence. This makes for easier transactions, less locks held, potentially (depending on [...]

  posted in MySQL by shlomi

• Table split(...) for the masses

  September 5, 2012

  (pun intended) common_schema's new split statement (see release announcement) auto-splits complex queries over large tables into smaller ones: instead of issuing one huge query, split breaks one's query into smaller queries, each working on a different set of rows (a chunk). Thus, it is possible to avoid holding locks for long times, allowing for smaller [...]

  posted in MySQL by shlomi

• common_schema 1.1 released: split(), try-catch, killall(), profiling

  September 4, 2012

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

  posted in MySQL by shlomi

• MySQL/QueryScript use case: DELETE all but top N records per group

  February 9, 2012

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

  posted in MySQL by shlomi

• common_schema rev. 218: QueryScript, throttling, processes, documentation

  February 8, 2012

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

  posted in MySQL by shlomi