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 …

With 130 entries in the “MySQL” category and no MySQL-related subcategories, my blog had become impossible to search and navigate easily.

And thus I created a number of new categories for the MySQL entries within my blog. They’re listed in the left navigation bar, below the months, as well as below:

• MySQL Server, MySQL Cluster, Falcon
• Connectors: PHP, Ruby on Rails
…

I've been talking to some Montrealers about using Amazon EC2. One of the small issues with EC2 is that you have no fixed IP addresses for your instances. This means that people are having to use DNS, which works well 99% of the time[1].

One of the problems for me, is that I can't as easily have a virtual interface, which is the IP address of the current master database server - I have to have a hostname like mysql-master.mydomain.com. Which means that if I switch masters, I have to wait for the TTL length for old clients to stop connecting to the previous master (In talking to Rightscale, they set it to 45 seconds, PeterZ has blogged about with not to use 0 second TTLs here).

The possible solution - when you switch masters, the previous master shuts …

I figure while I'm in the blogging spirit, I should announce where you can catch me presenting early next year:

March 12-14 - PHP Quebec
* Security from the Database Perspective
* Breaking the Rules

April 14-17th - MySQL Conference
* Exploring Amazon EC2 for Scale Out Applications

May 21st-23rd php|tek
* Performance Tuning MySQL
* Designing for High Availability

I decided to do something different and submit about 10 outlines for possible talks, so it's interesting to see what each of the organizers eventually arrive at.