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.

HTTPS+SSLVerifyClient require in <Directory>+big POST = Apache error

I was configuring MediaWiki to allow uploads and was getting an error in the browser about the POST data being too large (“does not allow request data with POST requests, or the amount of data provided in the request exceeds the capacity limit.”). I had a look in the Apache error log and found:

[Thu Feb 23 16:12:45 2012] [error] [client 60.240.67.126] request body exceeds m
aximum size (131072) for SSL buffer, referer: https://www.jj5.net/morpheus/Speci
al:Upload
[Thu Feb 23 16:12:45 2012] [error] [client 60.240.67.126] could not buffer messa
ge body to allow SSL renegotiation to proceed, referer: https://www.jj5.net/morp
heus/Special:Upload

So I did some research. I found this document, File upload size which suggested editing /etc/php5/apache2/php.ini which I did:

upload_max_filesize = 20M
post_max_size = 80M

That didn’t fix the problem though. I found Request entity too large which suggested checking my setting for LimitRequestBody, but that wasn’t the problem either.

Eventually I found Bug 491763 – HTTPS+SSLVerifyClient require in <Directory>+big POST = Apache error which suggested I needed to apply the SSLRenegBufferSize directive which I did like this:

  <Location /morpheus>
    SSLVerifyClient require
    SSLVerifyDepth 1
    SSLRenegBufferSize 20971520
  </Location>

And then after restarting Apache the problem was solved.