Posts Tagged ‘MySQL

Why use MyISAM?


So far, this has read somewhat like a paid advertisement for InnoDB. However, MyISAM has some very real advantages. One of these is the simplicity of the engine, it is very well understood and it’s easy to write third party tools to interact with it. There are very high quality free tools, such as mysql hot copy available for MyISAM. It is much more difficult to write tools for an engine as complicated as InnoDB and this can be easily seen in the number of them available. Also, this simplicity allows for an ease of administration that is not there with InnoDB.


MyISAM’s other main advantage is how long it has been around. There are many systems, Drupal for example, that are very much optimized for that particular engine. This is not to say that they perform poorly on InnoDB, but they are not optimized for it. For example,while many of Drupal’s core queries are well indexed and use the primary key (thus benefiting from InnoDB’s primary key clustering), some could be improved. The node table has a primary key on (nid, vid). Having this index is a good idea, but it is a two integer index and there are eleven secondary indexes based on it. This doesn’t mean much when you use MyISAM, but under InnoDB it means each of those secondary indexes has two integer sized leaves identifying the primary key.

Another fact, is that there are some workloads MyISAM is better suited for. For example,Drupal’s built in search functionality performs horribly on InnoDB for very large datasets, for example 100k+ rows. These tables are best left MyISAM. Fortunately, MySQL allows for mixing engines like this.

Resource Usage

It is readily accepted in computer science that there is often a trade off  between speed and memory footprint. We have seen through the above benchmarks that InnoDB has some fast algorithms, however, this comes at a price. Not only does InnoDB use more memory than MyISAM, but the actual data files are often quite a bit larger. Add to this the fact that InnoDB has at least one quite large log file and you have a significant increase in resource use. This makes MyISAM a good fit for a resource limited server. However, if you’re concerned at all with high levels of concurrency, it is likely you have the funds to buy a server that can handle these increased resource demands.

Tags : , , , , , , , , ,

Flaws in Authentication Protocols

Several database systems have plain text authentication protocols, by which we mean authentication protocols in which the password is passed “on the wire” in a plain text or easily decrypted format. In a default configuration (that Sybase warns against, but which we have still seen in use) Sybase passes passwords in plaintext. By default, Microsoft SQL Server obfuscates passwords by swapping the nibbles (4-bit halves of a byte) and XORing with 0xA5. In both ofthese cases, the vendors warn against using the plain text versions of their authentication protocols and provide strong, encrypted mechanisms that are relatively easy to deploy — but the defaults are still there, and still dangerous.

MySQL has historically had a number of serious problems with its authentication protocol. Although the protocol isn’t plaintext, the mathematical basis of the authentication algorithm prior to version 4.1 was called into question by Ariel Waissbein, Emiliano Kargieman, Carlos Sarraute, Gerardo Richarte, and Agustin Azubel of CORE SDI (CVE-2000-0981). This describes an attack in which an attacker that can observe multiple authentications is quickly ableto determine the password hash.

A further conceptual problem with the authentication protocol in MySQL prior to version 4.1 is that the protocol only tests knowledge of the password hash, not the password itself. This leads to serious problems if a user is able to somehow determine another user’s password hash — and MySQL has been subject to a number of issues in which that was possible. Robert van der Meulen found an issue (CVE-2000-0148) in MySQL versions prior to 3.23.11 whereby an attacker could authenticate using only a single byte of the expected response to the server’s challenge, leading to a situation whereby if you knew a user’s username, you could authenticate as that user in around 32 attempts.

Chris Anley recently found a very similar problem in MySQL (CAN-2004-0627) whereby a user could authenticate using an empty response to the server’s challenge, provided he or she passed certain flags to the remote server.This category of bugs is almost as dangerous as the “unauthenticated flaws in network protocols” category, because in many cases the traffic simply looks like a normal authentication. Attackers don’t need to exploit an overflow or do anything clever, they simply authenticate without necessarily needing the password—or if they’ve been able to sniff the password, they just authenticate.The best defense against this kind of bug is to ensure that your database patches are up-to-date, and that you don’t have any plaintext authentication mechanisms exposed on your databases. If your DBMS cannot support encrypted authentication in your environment, you could use IPSec or SSH to provide an encrypted tunnel. MySQL provides explicit guidelines on how to do this in its documentation, though recent versions of MySQL allow authentication to take place over an SSL-encrypted channel.


Tags : , , , , , , ,