MySQL Tech-Feed (en)

Hello everybody,

One month earlier than planned we have the great pleasure to announce you that the company called FromDual goes operational today!

We are excited about this step and it is an new era in our personal evolution to get back in full-contact with customers and solve their real life day-to-day MySQL problems.

So we are happy hearing from you and to help you solving your individual MySQL problems…

You can find us at FromDual or you can contact us here.

Regards,
Oli Sennhauser (aka Shinguz) …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Shlomi,

Thanks a lot for your input!

I am not a security specialist so I do not know what those guys who implement audit solutions think about your approach. But I can imagine that moving login information away immediately gives less possibilities to manipulate those data.

But as SUPER users are not logged it is halve baked anyway.

Using your approach in combination with the federated or federatedX Storage Engine would give again …

Problem

A customer recently showed up with the following problem:

*With your guidelines
[ 1
] I am now able to send the MySQL error log to the syslog and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?*

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

What came out is the following:

• We create an UDF which allows an application to …

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

But the nasty thing is, that even …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hello kabel,

Thanks for the flowers!

Anyhow, logical backups with such an amount of data is nearly impossible to restore (in a timely manner)!

But if you rely on physical backup methods you do not get rid of the corruption. So one is trapped in this situation. :-(

Do you have an idea why you got the corruptions? Playing around with DRBD or anything similar on file system level? Or just upgraded to 5.1? How does the corruption …

Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).

When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.

If you are lucky only “normal” tables are concerned. So you can dump, drop, recreate and load them again as described in the InnoDB recovery procedure …

Or an active-active failover cluster à la VMware.

Today I have learned about a totally crazy/cool looking architecture where the expensive VMware ESX server was replace by a free/cheap VMware Workstation version in combination with DRBD.

Basically DRBD we name the poor man’s SAN and that is exactly what this customer is doing. He replaced the SAN with DRBD and now he can easily move one VMware instance to the other host. Possibly it is not that flexible and powerful as an ESX Server but also not so …

According to different sources from the web the decision about the Oracle - Sun merger has been approved by the European commission soon. So at least in the West it is clear what is going on. Let us see what the East decides… [ 1 ], [ 2 ].

Oracles arch-enemy Microsoft has already brought its weapons in position against the target with its: Microsoft offers Oracle-phobes MySQL migration tool" [ 3 ], [ 4 ]. So far so good. Nothing new, nothing special.

What made me a bit edgy was the following …

There are 2 different possible situations you can face when you have to deal with MySQL and syslog:

• MySQL is used as back-end for syslog to store the logging information.
  [ 6
  ]
• MySQL itself should report to the syslog.

In this blog article we look at the second situation: How can you make MySQL reporting to the syslog.

Since the version 5.1.20 MySQL is capable to log to the syslog
[ 1
],
[ 2
]. This is done by the MySQL angel process mysqld_safe.

You can enable the syslog when you add the syslog parameter …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Brian,

Thanks for the hint. Just added it!

HNY, Oli

iostat is a very handy tool to help you investigating what kind of performance problems you have. Especially your databases can cause a lot of troubles to your I/O system and thus it would be very nice if every DBA has installed iostat on all of his MySQL database servers.

Unfortunately most of the Linux distributions do NOT install iostat by default. This causes often unfortunate situations when you are in a MySQL consulting engagement or have a MySQL support case and ask the customer for the output of …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Roland,

Ups! It seems like I did not look into it well enough… :( Thanks for your hint!

Just found the [http://www.mysqludf.org/ UDF repository] on your site. Great stuff…

Windows? What is this? No honestly: I did not try this out. There is no such system close to me nor have I a Win compiler or such stuff… Sorry!

I really like this new toy (for me) called UDF. So I try to provide some more, hopefully useful, functionality.

The newest extension I like is the possibility to write to the MySQL error log through the application. Oracle can do that since long. Now we can do this as well…

A list of what I have done up to now you can find here:

• Query and change InnoDB spin_wait_delay: udf_spin_wait_delay-5.1.30-linux-i686-glibc23.so (md5 807c6bc09b5dc88a8005788519f2483a, BIN … kbyte)
• Send message to the …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

I was told that spin_wait_delay is exposed to public with the InnoDB pluggable storage engine 1.0.4. Seems I have to look for an other hidden variables now. :)

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Papagnome,

You are absolutely right, UDF’s came in in 3.23 already (I was told). What was in my mind was more the pluggable stuff. This feature is very nice an pretty new. But somehow I did not manage to express what I wanted to… :)

Regards, Oli

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Mark,

Thanks a lot for your feedback. And how is the correct way to make it version IN-dependent and thus better portable?

In one of my previous posts I was writing about how to read other processes memory
[ 1
]. As an example I tried to get the value of the hard coded MySQL internal InnoDB variable spin_wait_delay (srv_spin_wait_delay).

In this example we were using gdb or the operating system ptrace function to retrieve this value. This method has the disadvantage that it is pretty invasive.

When I was working on a customer support case I had the idea to solve this by the much less invasive method of User-Defined Functions …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Andrew pointed me to the following blog entry of Brian: [http://krow.livejournal.com/481313.html Daemon Example (deamon plugin)]

Thanks!

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Björn,

Thanks a lot for you post. Much better than mine! :)

Shinguz

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such mathematical calculations is the application code and not the …