The latest from the MySQL community

This release fixes a few bugs, adds minor features, and adds some debugging support to shared code. I'm working on the Nibble sync algorithm for mk-table-sync, and someone has found a few more bugs with mk-parallel-dump, but those might take me a while to complete.

With InnoDB, I was trying to figure out if ANALYZE TABLE blocks other clients or if it uses MVCC ("in theory" it probably could). The answer is that it does seem to lock for a small amount of time, but the results are not as expected. A second execution of ANALYZE TABLE takes considerably longer than the first:


mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| 1 | root | localhost | test | Query | 235 | Sending data | select count(*) from …

I was coincidentally doing a show table status on couple of tables when I saw that one of the MyISAM tables has reach the 4 GB default limit. The table stores all my show processlist parsed data and apparently my parsing and importing script did not notify me of any errors like the table was full or something. Anyway, I solved the problem by running the command below.

ALTER TABLE MAX_ROWS = 1000000000;

A good discussion on MyISAM 4 GB default limit can be found on this link:

http://jeremy.zawodny.com/blog/archives/000796.html
http://dev.mysql.com/doc/refman/5.0/en/full-table.html

We (me coding, Igor and Evgen reviewing) have fixed BUG#32198. According to Support/Bugs team, the bug was causing pain to a number of people, so I thought it deserved a post. The problem was as follows:

• Before MySQL 5.0.42, comparisons in form

    date_column CMP datetime_const

  were interpreted as comparisons of DATEs (CMP is one of =, <, > , <=, or >=). The time part of datetime_const was ignored.

• In 5.0.42 we've fixed it to perform in a way that is closer to the SQL standard: the comparisons are now resolved by comparing values as DATETIMEs. Our fault was that "date_column CMP datetime_const" stopped being sargable. It turned out there quite a few people who had queries like

        SELECT ... WHERE date_column < NOW() ... 

  and those queries became awfully slow.

  …

Let's say you have a pool of ~100 connections to your database. A thread in your application does something like this:


START TRANSACTION;
INSERT INTO my_table (col1) VALUES ('val1');
COMMIT;
START TRANSACTION;


That last transaction was never committed and remains open. This means that InnoDB (which keeps multiple versions of each row) can not clean up and free space:


mysql> SHOW INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 981654
Purge done for trx's n:o < 0 979531 undo n:o < 0 0
....


One 'symptom' of this problem may be if the user complains that their database is much faster after they restart, since …

I published slides from my two talks from our Egypt company meeting.
First one is about Sphinx search engine. The originally presentation was prepared by Andrew Aksenoff, author of Sphinx for HighLoad.ru conference, I translated it on English for our meeting.
And second about Mercurial version system, the tool we decided to use for our internal and web projects.

Entry posted by Vadim | 2 comments

Add to: …

There is a going trend among companies not just supporting and appreciating open source, but also ‘’going open’’ by releasing their new development efforts as open source or converting their formerly proprietary offerings open source by releasing the source code under some open source license such as GPL.

I believe there are many misconceptions among organizations as to what can be gained by this and even more misconceptions as to how to manage the process.

Some Myths:

- Open Source is hostile to intellectual property.
- If I give away my software to the Open Source community, thousands of developers will suddenly start working for me for nothing.
- The Open Source movement isn’t sustainable, since people will stop developing free software once they see others making lots of money from their efforts.

(Free/Libre Open Source Software: a guide for SMEs 2007)

Some reasons why select …

Okay, so you have used some mysqldump to backup some of your data. But have you wondered how to test your backup? What if mysqldump does not really 'backup' your data? It is actually quite simple to test your backup and that is to verify the restore of your backup.

Here are a couple of ways to test your backup recovery (assuming that you have restored your backup already).

1. Do a select count(*) on all your tables before and after you restore your data.
2. Do a md5sum on your MySQL files before and after you restore your data.

Obviously when you do test your recovery process, you need to make sure that no data is being changed while you are doing it.

I had some free time this weekend, so I thought I would give DRBD a look on EC2... It wasn't long in, and I made a discovery:

etch:~# ifconfig
eth0      Link encap:Ethernet  HWaddr 12:31:38:00:35:37  
          inet addr:10.252.58.197  Bcast:10.252.59.255  Mask:255.255.254.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:82547 errors:0 dropped:0 overruns:0 frame:0
          TX packets:41818 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:121402208 (115.7 MiB)  TX bytes:2645395 (2.5 MiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

At least …

Today's entry, provided by Paul Reinheimer, is entitled Channels and Output.

Name

Paul Reinheimer

Blog

blog.preinheimer.com

Biography

Born in Vancouver, raised in Ontario, educated in Windsor, currently roaming the streets of beautiful Montreal. When not fighting off crazy Internet vixens, Paul pays his hosting and Internet bills by taking care of training for php|architect, launching his own projects like funcaday, and speaking at various conferences.

Location

Montreal, Canada

When getting started with PHP programming, we memorize rules that those …