'Hack' Tag

• Cheating mysqlsandbox to install MariaDB 10.0

  March 17, 2013

  mysqlsandbox is version-aware. The new 5.6 version, for example, requires special care because of the system InnoDB tables or otherwise modified system tables. At this moment, it will refuse to install MariaDB 10.0 (alpha): bash$ make_sandbox /tmp/mariadb-10.0.1-linux-x86_64.tar.gz unpacking /tmp/mariadb-10.0.1-linux-x86_64.tar.gz unsupported version 10.0 This is perfectly legitimate, and I have no quarrel with this fact. However, [...]

  posted in MySQL by shlomi

• Looking for a hack: share data between MySQL sessions

  January 28, 2013

  I'm looking for a way to share data between two MySQL connections/sessions. Obviously tables are the trivial answer, but for reasons of security (possibly insufficient privileges) I wish to avoid that. The type of data to be passed can vary. Ideally I would be able to pass multiple pieces of information (dates, texts, integers, etc.) [...]

  posted in MySQL by shlomi

• SQL: selecting top N records per group, another solution

  August 21, 2012

  A while back I presented SQL: selecting top N records per group, a "give me the top 5 countries in each continent" type of query, and which used an external numbers table and a lot of tedious casting. Here's another solution I came up with (*). Still using GROUP_CONCAT (how else?), but no external table [...]

  posted in MySQL by shlomi

• Getting rid of huge ibdata file, no dump required, part II

  May 30, 2012

  This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once. In previous part we put aside the issue of foreign keys. We address this issue now. What [...]

  posted in MySQL by shlomi

• Getting rid of huge ibdata file, no dump required

  May 22, 2012

  You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. To quickly reiterate, you can only delete the ibdata1 file when no [...]

  posted in MySQL by shlomi

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

  posted in MySQL by shlomi

• Auto caching tables

  March 6, 2012

  Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated? Hint: yes. But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have [...]

  posted in MySQL by shlomi

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

  posted in Development, MySQL by shlomi

• MySQL foreach()

  December 2, 2011

  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})'); $() [...]

  posted in MySQL by shlomi

• Self throttling MySQL queries

  November 1, 2011

  Recap on the problem: A query takes a long time to complete. During this time it makes for a lot of I/O. Query's I/O overloads the db, making for other queries run slow. I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the [...]

  posted in MySQL by shlomi