I was chatting with Brian yesterday about my displeasure with
Ubuntu's idea of requiring you have to apt-get every package
including the kitchen sink in order to have a development box
where things like autoconf Just Work (TM). I mentioned my
problems and had a Freudian slip of the tongue and said
"Unbuntu". I rather like this, because as nice as Ubuntu is in
some ways, it's incredibly frustrating that you can't just
specify "I WANT A DEVELOPMENT BOX" when you install the OS. I
don't like having to figure out what to install when I'm busying
hacking code. I hate having to mess around with OS installs. Back
in 1993, I put in my time installing Slackware on 30 something
odd floppies as well as trying to get X to run on my Packard
Hells video card (headland technologies 5650 - something I'll
never forget)-- point being, my days of tinkering with OS-isms
are past.
Also, why the hell do you have to have multiple versions of
automake and …
Sometimes MySQL Replication may run out of sync - because of its own buts or operational limitations or because of application mistake, such as writing to the slave when you should be only writing to the master. In any case you need slave to be synced with Master.
To discover the difference between Master and Slave you can use excellent Maatkit tool though if you you just need to resync small single table it may be easy to do it other way:
Assuming your table schema does not change you can do something like this on the master:
LOCK TABLE tbl WRITE;
SELECT * FROM table INTO OUTFILE '/tmp/tbl.txt';
DELETE FROM tbl;
LOAD DATA INFILE 'tmp/tbl.txt' INTO TABLE tbl;
UNLOCK TABLES;
This will dump master's table content to the file clean the table on master and slave and refresh it on both of them.
This obviously makes table …
[Read more]I read Baron's blog about this challenge on how to limit rows accessed, not rows returned with a query. He had another requirement, no subqueries, so it would work on pre MySQL 4.x. At first, I started down the same path he did, looking for a way to limit the number of rows accessed. The only way I could do it was to either create 4 seperte queries directly spelling out which actor_id I was looking for. I could also use an IN clause as well, but that wasn't generic enough for me. There's got to be a way to avoid a full table scan and only access a minimum number of rows to return the result he was looking for.
In researching LIMIT optimization, the MySQL 5.0 reference manual states:
If you use LIMIT row_count with …
[Read more]I read Baron's blog about this challenge on how to limit rows accessed, not rows returned with a query. He had another requirement, no subqueries, so it would work on pre MySQL 4.x. At first, I started down the same path he did, looking for a way to limit the number of rows accessed. The only way I could do it was to either create 4 seperte queries directly spelling out which actor_id I was looking for. I could also use an IN clause as well, but that wasn't generic enough for me. There's got to be a way to avoid a full table scan and only access a minimum number of rows to return the result he was looking for.
In researching LIMIT optimization, the MySQL 5.0 reference manual states:
If you use LIMIT row_count with …
[Read more]I'll say beforehand that the following is not very clean - for a number of reasons. I'll discuss some issues after the query. The query returns all indexes in a db where the cardinality is lower than 30% of the rows, thus making it unlikely that the server will ever use that index.
SELECT s.table_name,
concat(s.index_name,'(',group_concat(s.column_name order by s.seq_in_index),')') as idx,
GROUP_CONCAT(s.cardinality ORDER BY s.seq_in_index) AS card,
t.table_rows
FROM information_schema.tables t
JOIN information_schema.statistics s USING (table_schema,table_name)
WHERE t.table_schema='dbname'
AND t.table_rows > 1000
AND s.non_unique
GROUP BY s.table_name,s.index_name
HAVING (card + 0) < (t.table_rows / 3);
Let's discuss...
The number of rows in a table used here will be accurate for
MyISAM; for InnoDB it's essentiall a rough guess.
Cardinality in this context is the number of distinct values …
Vervante Books Etc — MySQL 5.1 Cluster DBA Certification Study Guide
Only $40. Written by absolute brilliant people (and me). Needing to learn MySQL Cluster so you can go and use it? test it out? work out if it’s for you? Get this book!
Sun Microsystems Inc. today announced the immediate availability of a new offering to help customers achieve greater return on investment (ROI) and significantly reduce the costs of deploying and managing database and application server software. Sun GlassFish and MySQL Unlimited enables companies of all sizes to deploy the software on unlimited servers across their entire organization for a flat annual subscription. For more details, visit http://www.sun.com/mysql/glassfish.
Bazaar Branches of MySQL 6.0
Now that MySQL has switched to using bazaar for source code repositories, an in place to go for 6.0 public code is Canonical’s “launchpad” site, specifically “Bazaar Branches related to Sun/MySQL Engineering” at https://code.launchpad.net/~mysql. The Bazaar Branches page has a list. Each row in the list is for a source-code package with all the MySQL files necessary to build the server, as described in the MySQL Reference Manual chapter MySQL Installation Using a Source Distribution. For 6.0 lovers, the interesting entries in the Bazaar Branches list are mysql-6.0, mysql-6.0-backup, mysql-6.0-falcon, mysql-6.0-rpl, mysql-6.0-wl3220, mysql-maria.
how to read the list
The Bazaar Branches page’s instructions look good enough …
At last we are code complete with Maria 1.5, the so called crash
safe version of MyISAM. Note that code complete means that all
features are coded, not that the code is 100 % bug free!
The code should be available soon in a bzr repository near you.
We will also shortly (as soon as we pass all build tests) make a
normal source and binary release of MySQL-5.1-Maria from here.
There is still a couple of minor bugs that we know about and we
will fix them shortly, but nothing that should stop you from
testing/using Maria. See the file KNOWN_BUGS.txt in the source
release for details.
The current release plan for MySQL-5.1-Maria is as follows:
- The current release will be an alpha release. (Technically it
could have been a beta release but because we have added so much
new code we wanted to signal this by making another …
You’ve probably seen the MySQL Reference Manual page “What’s New in MySQL 6.0″ and so you’ve seen this description of a new feature: “Support for extended comments for tables, columns, and indexes.” Here’s what “extended” means.
You can do a little more with COMMENTs in ALTER or CREATE statements. The easy way to see the difference is by running this statement in both MySQL 5.1 and MySQL 6.0:
SELECT table_schema, table_name, column_name, character_maximum_length FROM information_schema.columns WHERE column_name LIKE '%comment';
From that, you’ll see that the differences are:
TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH
in MySQL 5.1 in MySQL 6.0
COLUMNS COLUMN_COMMENT 255 1024
PARTITIONS …[Read more]