A great MySQL session

I found this great MySQL session over here. It has a clever approach for creating a big table, and shows how to invoke shell commands from the `mysql` client.

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

SHOW CREATE TABLE big_table\G

select count(id) from big_table;

-- Check how much space is needed for the uncompressed table.
\! ls -l data/test/big_table.ibd

CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- Check how much space is needed for a compressed table
-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd

MySQL admin with Ian Gilfillan

I was doing some reading today and I came upon an old series of articles over on www.databasejournal.com by a dude called Ian Gilfillan:

For the ‘type’ in SQL ‘EXPLAIN’ Ian says: from best to worst the types are: system, const, eq_ref, ref, range, index, all.
It seems this Ian Gilfillan fellow has been rather prolific.

Error in rsync protocol data stream

So I was running a backup with rsync and I saw this:

lib/mysql/ibdata1
        437.40M  33%    4.53MB/s    0:03:10  
inflate returned -3 (0 bytes)
rsync error: error in rsync protocol data stream (code 12) at token.c(557) [receiver=3.1.2]
rsync: connection unexpectedly closed (155602 bytes received so far) [generator]
rsync error: error in rsync protocol data stream (code 12) at io.c(235) [generator=3.1.2]

The issue seems to be that if you’re using rsync compression and the remote file gets changed while the rsync copy is in progress then shit gets corrupted. My solution was to handle error level ’12’ and retry without compression. If the file changes while the rsync is in progress the file will be corrupt, so you shouldn’t rely on the integrity of such files.