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 [...]
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, [...]
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 [...]
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 [...]
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 [...]
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, [...]
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 [...]
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})'); $() [...]
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 [...]
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: SELECT `Database` FROM (SHOW DATABASES); One cannot: DECLARE show_cursor CURSOR FOR SHOW [...]