Every few years, a wave of people claim that the relational model falls short of modeling data structures of modern applications. This includes some really smart people recently such as:- Jim Starkey- Brian Aker- MIT researchersThis trend of criticizing the relational model started almost immediately after E. F. Codd published his seminal paper, "A Relational Model of Data for Large Shared Data

Today I saw a case where MySQL server is writing its binlog entries to the error log file ‘mysqld.err’ instead of writing it to regular binlog. This is happening with MySQL version 4.0.26 and not sure how it was triggered.

I am guessing it could be because the file handles got swapped, it can not be a memory overrun or anything else as I can see the error log is getting the right binlog entries. I am still trying to find what could have caused this to happen. This happened twise so far from the same property with the same setup; even though there is more than an year in between. If you restart the server or upon issuing FLUSH LOGS, everything will be back to normal.

I had a interesting problem from Yahoo! mail team that they were not able to start the 5.1 server at all on couple of their replication setup boxes. They tried 5.1.20 , 21 and 22 and all are behaving in the same way.

After digging into the box, the mysql error log has the following info that it was failed to open event table.

1
2
3
4
5
6
7
8
9

      071113 10:09:28 [ERROR] Event Scheduler: Failed to open table mysql.event
071113 10:09:28 [ERROR] Event Scheduler: Error while loading FROM disk.
071113 10:09:28 [Note] Event Scheduler: Purging the queue. 0 events
071113 10:09:28 [ERROR] Aborting
 
Creating new mysql …

I often noticed that people complain about the LOAD DATA performance when loading the table with large number of rows of data. Even today I saw a case where the LOAD DATA on a simple 3 column table with about 5 million rows taking ~15 minutes of time. This is because the server did not had any tuning in regards to bulk insertion.

Consider the following simple MyISAM table on Redhat Linux 32-bit.

1
2
3
4
5
6

CREATE TABLE load1 (
  `col1` varchar(100) NOT NULL DEFAULT '',
  `col2` int(11) DEFAULT NULL,
  `col3` char(1) DEFAULT NULL,
  PRIMARY KEY  (`col1`)
) TYPE=MyISAM;

The table has a string key column. Here is the data file( …

Today I had an interesting issue from one of the internal property within Yahoo!, where they were not able to start the MySQL server after the server has been upgraded to 5.1 from 4.1.

Here is the error log when the 5.1.22 server has started on 32-bit RHEL:

1
2
3
4
5
6
7
8

      071105 12:25:50  INNODB: Operating system error number 27 IN a file operation.
INNODB: Error number 27 means 'File too large'.
INNODB: SOME operating system error numbers are described at
INNODB: http://dev.mysql.com/doc/refmastartn/5.1/en/operating-system-error-codes.html
INNODB: File name /mysql/var/mysql/ibdata/ibdata1
INNODB: File …

Hello everyone, I think this will be a great log buffer. Dave has been sick these past two days and as a result, we do not have a comprehensive log buffer ready the way we or a volunteer usually do. This was bound to happen to log buffer at some point and today it has happened. So I [...]

One of the key features MySQL often uses to advertise for their database is the modular architecture that allows them to have different storage engines below the same SQL layer. In practice the application and/or database designer can choose from a variety of low level data storage implementations that each offer different characteristics and may be chosen on a per table basis. (Even though I personally believe most designs will use one type of table for all tables of a particular schema).

The idea behind this is that for example people who do not need transactions should not have to worry about them at all ? maybe there is performance impact involved which they cannot afford to take. Moreover some specialized types of index or column might not be available on all engines. Basically the concept is very interesting and can be really useful for developers.

However there is a weakness that in my opinion needs some severe work to be …

Solid has released 5.0.45-0077. New features include full support for prefix indexes and support for multiple NULLs on unique indexes. This release also has a number of bug fixes. For the full detailed list of bug fixes and enhancements, please see the ChangeLog. Check it out at http://dev.soliddb.com/download

By now, word has gotten around that MySQL has made some more changes to their process around MySQL Community Server. The changes primarily focus on:

• acceptance of community contributions (5.0 and 5.1 are now locked for contributions, so any contributions won’t be available until 5.2 or later)
• release schedule of community server (”Mature GA”, meaning 5.0, will have approximately 4 source builds per year and 2 binary builds; new GA, meaning 5.1, will have a binary build once per month)

These refinements of the community server [process] are detailed in Kaj Arnö’s blog. Not everyone is happy about the new plan for acceptance of community contributions, because it means that community enhancements have to wait a long time to see the light of day. Jeremy Cole has talked about the frustrations that this causes and calls the …

After several months I have again spent a little work in the MySQL Index Analyzer I first published back in August of 2006.

I added a feature that will find duplicate columns inside an index, caused by the internal appending of the InnoDb primary key columns to each secondary index.

To get the code and read more about the new feature, including an example, go to the MySQL Index Analyzer Blog.