dragffy.com

$ cd /var/lib/mysql

find -type f -name '*.MYI' -exec myisamchk --silent --force --fast --update-state --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M {} \;

Find your debian-sys-maint password in /etc/mysql/debian.cnf.

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY ' ' WITH GRANT OPTION;

Replace with your debian-sys-maint password.

Installing MySQL on OS X has become infinately easier since you can now download an Apple DMG from the MySQL website which takes care of the fine detail.

However, one thing that changed with Leopard is the socket for Mysql. It moved to /private/tmp, so you may need to configure your php.ini file to point it to the new location.

To do so, open the file /private/etc/php.ini, (if no such file exists, then make a copy of /private/etc/php.ini.default and rename it to php.ini) and edit that.

You have two lines to modify:

mysql.default_socket =

becomes:

mysql.default_socket = /private/tmp/mysql.sock

and mysqli.default_socket =

becomes:

mysqli.default_socket = /private/tmp/mysql.sock

Change default password:
$ mysqladmin -u root password NEWPASSWORD

Change existing password:
$ mysqladmin -u root -p'oldpassword' password newpass

I am making a note of this for ease of future reference. I can never quite remember whether a medium blob is 16MB or whatever it is, the capabilities are on the MySQL site but I am going to write it in plain english here so I don’t have to keep getting my calculator out to remind me what 2^24 is in terms I am more familiar with…

TinyInt: -128 to 127 (0 to 255 if unsigned).
SmallInt: -32768 to 32767 (0 to 65535 if unsigned).
MediumInt: -8588608 to 8388607 (0 to 16777215 if unsigned).
Int: -2147483648 to 2147483647 (0 to 4294967295 if unsigned).
BigInt: -9223372936854775808 to 9223372036854775807 (0 to18446744073709551615).

Float: 0 and +-1.175494351E-38 to +-3.402823466E+38.
Double: 0 and +-2.2250738585072014-308 to +-1.7976931348623157E+38.
Decimal[(M,D)]: As for DOUBLE but constrained by M and D.

Char(M): M may take any integer value from 0 to 255, with a CHAR(0) column able to store only two values: NULL and ” (empty string), which occupy a single bit.
VarChar(M): 1 to 255 (number of characters to store). Trailing spaces are stripped before storage.

Text type columns do case insensitive comparisons and sorts, whereas blobs are case sensitive.
TinyBlob/TinyText: Max. length 255 characters. Very similar to VarChar but trailing spaces are not stripped before storage.
Text/Blob: Max. length 65535 characters (65KB).
MediumBlob/MediumText: Max. length 6777215 characters (16.8MB).
LongBlob/LongText: Max. length 4294967295 characters (4.3GB).

Enum: One value chosen from up to 65535 possibilities.
Set: Up to 64 values in a given set column.

Date: ‘1000-01-01′ to ‘9999-12-31′, and ‘0000-00-00′.
Time: ‘-838:59:59′ to ‘838:59:59′.
DateTime: ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′.
Year: 1901 to 2155, and 0000.
TimeStamp: 19700101000000 to sometime in 2037 on current systems.

Last week I setup MySQL replication for a database with two slaves. I wanted to see how resilient it was, and one of the tests I wanted to perform was to create a record on the master and then interrupt the replication of the same record on the slave. This would allow me to see whether MySQL could recover from such an error.

So I thought the easiest way of doing this would be to to insert a large 700mb
file in to MySQL – to give me enough time to interrupt a slave before
replication had completed. Thus being able to test MySQL’s resilience to
loss of connectivity during UPDATE/INSERT queries.

I have never used BLOB columns before so my quest took my on a journey during which I learned a lot about these blobs. Now, even though a MySQL long blob can hold 4GB of data I have encountered
problem after problem dealing with it:

PHP scripts are limited to 32MB memory maximum, this can be upped, but
is not scalable and not a good solution. It means that you cannot simply
read the data of file and perform an INSERT since the file data is
stored in PHP’s memory first. This meant the development of a PHP script
that could read chunks (just a meg or so) from a file and perform UPDATE
queries along with MySQL CONCAT function to effectively append each
chunk.

CONCAT, as I eventually found out, returns NULL if any of it’s arguments
are NULL. In my case when attempting to UPDATE a row with additional
chunks the resulting blob size was always zero. This was because the
very first time CONCAT is used the blob field is empty (NULL), therefore
CONCAT always returned NULL – and so, the data in the blob column never
grew. The query looked like this:
UPDATE `$table` SET `data` = CONCAT(data, '{mysqli_real_escape_string($buffer)}') WHERE `id`=$id";

I then combined CONCAT with COALESCE which will return an alternative
value that you specify in the eventuality the first argument is NULL. By
combining these two I could overcome the above problem.
Resulting query structure:
UPDATE `$table` SET `data` = COALESCE(CONCAT(data, '{mysqli_real_escape_string($buffer)}'), '') WHERE `id`=$id";

Great, now we’re finally adding our chunks on. Then my computer ran out
of disc space – the reason: 7 gigs of MySQL replication logs. Took a
while to find that out. Fixed.

Next problem, although I was certain (via in-depth PHP debugging) that
PHP was correctly reading chunks from the file in a consecutive order,
of the correct size, and the SQL query was being properly executed
without any sly errors the blob column would never end up growing above
12MB (according the PHP MyAdmin). Through even more intensive debugging
I found that after concatenating each chunk the blob would grow by the
correct amount up to 16MB, then it would zero and start again. The
result of pushing a 700MB ISO in to it would always be less 16MB.

Working from a hunch I upped the max_packet_size in my.conf from 16MB to
36MB, the result was as above but this time the blob would grow to 32MB
before zeroing itself.

I then found this MySQL bug report:
http://bugs.mysql.com/bug.php?id=22853

It is considered a bug simply because by upping max_packet_size to
something like 4GB or anything substantial leaves the MySQL server open
to network DoS attacks. There is no work around. A fix is scheduled for
version 6.

Anyway, since we don’t care about security I maxed out the packet_size,
and my chunking script worked, but very quickly I noticed a huge amount of
slowdown once the blob had grown to just a few tens of megs. In fact it
took about 20 minutes to get to 200MB. Without supporting my theory with
any evidence I reckon the CONCAT function reads all the data out of the
database in to memory, does it’s stuff and then stuffs it back in to the
database. The result is an increasingly slow query.

It would seem that although MySQL supports blobs of about 4GB that you
can never get that size unless you:

1. Change the max_packet_size to a stupidly high number, leaving your
server open to DoS attacks so then you can just do a single INSERT. Using CONCAT
is out since it would take forever.

2. Change the memory limit of PHP scripts to be greater than 4GB.

3. Have absolutely bags of RAM since three copies of the file are stored
in RAM (certainly two copies). Since you cannot chunk the file and
perform CONCATS PHP will read the entire file in to it’s memory. This
will all then be buffered by the MySQL driver. Then this is passed to
the database. Therefore if you wish to insert a 4 GB file you will
require >12GB of RAM.

One work around is to chunk the file as before but each chunk is
represented by a single row. These can then be linked together by giving
each row a master file ID, so that all necessary rows can be retrieved.

For my purposes this doesn’t help, I needed queries that took a while,
inserting rows like this will take but a fraction of a second each.

Andrew commissioned me to give his books an on-line presence. He wanted only to list his books and nothing else such as shopping or contact details.This post summarises, and then specifies the project. Designs are presented, and a project plan is demonstrated. Finally, the project is completed and I can make the source code available on request. (more…)