July 19, 2008

MySQL 5.1 is to reach GA state - all to arms !

Posted by peter

MySQL 5.1 was in RC stage for around 10 months now finally planned to be released as GA soon. As Monty Says MySQL 5.1.26 will be renamed as GA if no serious bugs are discovered.

Lets help MySQL to ensure GA release will be indeed GA quality and support Monty in his call to ensure this RC candidate works well for you.
[read more...]

May 20, 2008

Dangerous command

Posted by Vadim

Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with “Syntax error”.
So RTMF and documentation says
“This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.”

For me term ‘dangerous’ is interesting there , as I’d expect really dangerous is DROP DATABASE (which I hope will not be removed in next release) , and RENAME DATABASE is supposed to be kind of safe - just replaces old name to new one.

I guess there are some related bugs - and there are:
Bug#28360: RENAME DATABASE destroys routines
Bug#17565: RENAME DATABASE destroys events
so basically RENAME DATABASE destroyed all events and routines related to database.

And solution of bug is:

Removed the
RENAME DATABASE db1 TO db2
statement.

So it seems MySQL took an interesting practice to get GA released as soon as possible - just remove features that does not work instead of fix it.
The similar was with FEDERATED storage engine, which was disabled by default in 5.1.23 binaries, in contrast to previous version (but should be enabled again in 5.1.24).

That probably helps to get ‘Zero P1 bugs reported’

April 4, 2008

Multi-Column IN clause - Unexpected MySQL Issue

Posted by peter

We have an application which stores massive amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to find matching urls quickly. There is a bit of chance there would be some false positives but these are filtered out after reading the data so it works all pretty well.

If we just process urls one by one it works great:

SQL:
  1. mysql> EXPLAIN SELECT url FROM 124pages.124pages WHERE url_crc=484036220 AND url="http://www.dell.com/";
  2. +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+
  3. | id | select_type | TABLE    | type | possible_keys | KEY     | key_len | ref   | rows | Extra       |
  4. +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+
  5. 1 | SIMPLE      | 124pages | ref  | url_crc       | url_crc | 4       | const |    1 | USING WHERE |
  6. +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+
  7. 1 row IN SET (0.02 sec)


[read more...]

March 16, 2008

MySQL Error Message Nonsenses

Posted by peter

What MySQL honestly was never good at is giving good helpful error messages. Start with basics for example - The error message in case of syntax error gives you information about tokens near by but little details:

SQL:
  1. mysql> SELECT * FROM  user oder BY pwd;
  2. ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near 'by pwd' at line 1

It would be much better if MySQL would give error give exact position of error (with complex auto generated queries line number is often not good enough) as well as give some better explanation on what is wrong.

The new parser for MySQL was spoke since 5.0 times but it never took off and I'm not seeing it on public road map either.

Though this is just tip of the iceberg of not so helpful or misleading error messages.
[read more...]

January 29, 2008

No more MySQL Crash Safe Replication in 5.0 ?

Posted by peter

As you might know even if you're only using Innodb tables your replication is not completely crash safe - if Slave MySQL Server crashes/power goes down it is likely for relay logs to run out of sync (they are not synced to the disk) plus position on the master which slave remembers becomes stale.

During MySQL 4.0 and 4.1 series there was a great workaround if you're using only Innodb tables - Innodb when Innodb does crash recovery it would print position in master log files up to which replication was done:
[read more...]

January 10, 2008

PHP vs. BIGINT vs. float conversion caveat

Posted by shodan

Sometimes you need to work with big numbers in PHP (gulp). For example, sometimes 32-bit identifiers are not enough and you have to use BIGINT 64-bit ids; e.g. if you are encoding additional information like the server ID into high bits of the ID.

I had already written about the mess that 64-bit integers are in PHP. But if the numbers you use do not cover 64-bit range fully, floats might save the day. The trick is that PHP floats are in fact doubles, i.e. double-precision 64-bit numbers. They have 52 bits for mantissa, and integer values up to 2^53-1 can be stored exactly. So if you're using up to 53 bits, you're OK with floats.

However, there's a conversion caveat you should be aware of.
[read more...]

November 13, 2007

10+ Ways to Crash or Overload MySQL

Posted by peter

People are sometimes contacting me and asking about bugs like this which provide a trivial way to crash MySQL to the user with basic privileges and asking me what to do.

My answer to them is - there is nothing new to it and they just sit should back and relax :)

Really - there are many ways to crash or otherwise made unavailable server with any MySQL version if you have access to it with normal privileges. We're constantly helping people to fix mistakes in the applications which make MySQL Server useless (though few of them cause crashes to be honest) so obviously it is even easier if you have intent.
[read more...]

October 22, 2007

Making bugs public - good job MySQL

Posted by peter

If you have been MySQL User for many years you might remember the times when MySQL had "zero bugs policy", this is when all known bugs really were fixed before release was made. To be honest at that time bugs were reported via bugs mailing list not via bugs database as they are now so they were not tracked so accurately but still there was intention and all known serious bugs were fixed before release was made.

Over years this policy had few changes, transforming to something like "no critical bugs in production releases" and in practice releases moved to predictive schedule rather than based on the moment when all bugs were fixed.
[read more...]

October 12, 2007

MyISAM Scalability and Innodb, Falcon Benchmarks

Posted by Vadim

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that's why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.
[read more...]

October 4, 2007

MySQL Quality of old and new features

Posted by peter

Recent couple of days our team was pointed to number of bugs in MySQL 5.0 which again seriously shakes the confidence in both MySQL Quality Control and bug fix promptness.

Let me just take couple of bugs as examples:

Triggers broken with auto-increment columns for Innodb tables (bug 26316). As you can see this bug is reported in February - over 6 months ago and it is still in verified state even though it has "serious" severity.

ORDER By DESC broken for Innodb tables (bug 31001) This is also very interesting - the VERY basic functionality gets broken and passes quality control. And why it is broken ? Because minor optimization was implemented in MySQL 5.0.
[read more...]


This page was found by: error 1064 42000 ...