'Hack' Tag

  • Making UUID() and RAND() replication safe

    October 20, 2014

    MySQL's UUID() and RAND() functions both provide with (pseudo) indeterministic result. UUID()'s result is moreover bound to the host on which it executes. For this reason, both are unsafe to replicate with STATEMENT binlog format. As an example, consider: master> create table test.uuid_test (id int, u varchar(64)); master> insert into test.uuid_test values (1, UUID()); Query […]

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

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

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

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

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

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

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

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

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

Powered by Wordpress and MySQL. Theme by openark.org