• Baffling 5.7 global/status variables issues, unclean migration path

    August 7, 2015

    MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables. But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time: […]

  • common_schema & openark-kit in the media: #DBHangOps, OurSQL

    June 26, 2013

    #DBHangOps I had the pleasure of joining into @DBHangOps today, and speak about common_schema and openark-kit. What was meant to be a 15 minute session turned to be 50 -- sorry, people, I don't talk as much at home, but when it comes to my pet projects... I also realized I was missing on a […]

  • Hierarchical data in INFORMATION_SCHEMA and derivatives

    January 8, 2013

    Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table: CREATE TABLE employee (   employee_id INT UNSIGNED PRIMARY KEY,   employee_name VARCHAR(100),   manager_id INT UNSIGNED,   CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ) engine=innodb ; +-------------+---------------+------------+ | employee_id | employee_name […]

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

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

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

  • Auto caching INFORMATION_SCHEMA tables: seeking input

    March 8, 2012

    The short version I have it all working. It's kind of magic. But there are issues, and I'm not sure it should even exist, and am looking for input. The long version In Auto caching tables I presented with a hack which allows getting cached or fresh results via a simple SELECT queries. The drive […]

  • INFORMATION_SCHEMA Optimizations: still crashing my servers

    December 12, 2011

    [Update: need to take more breaks: now NOT crashing my servers! See clarifications below] INFORMATION_SCHEMA Optimizations are meant to make your INFORMATION_SCHEMA queries lighter and safer. For example, if you're going to query the COLUMNS table for just the columns of a single table, then the following: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='rental' […]

  • More MySQL foreach()

    December 2, 2011

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

  • Reading results of SHOW statements, on server side

    November 25, 2011

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

Powered by Wordpress and MySQL. Theme by openark.org