Inside Zend Server is a series of blog posts which I intend to write on Zend Server (time permitted). As I pointed out in my previous post on Zend Server there are a lot of different constituencies that Zend Server applies to. In this post I will focus on developers and system administrators who are using Windows either for developing or running PHP–based Web applications.

Running high-performance and stable PHP on Windows has always been a serious challenge. So much of a challenge that in 2004 we announced a Zend product called “Zend WinEnabler” which had the sole purpose of delivering stable PHP on Windows. As part of building that product we built a FastCGI plug-in for IIS and Apache, ported our …

Problem

With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:

• Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
• mysql_upgrade: A much faster method, but it can still be slow for very large data sets.

I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.

Preparation

First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:

• If you haven’t already, enable …

To move data from Sql Server to MySQL, it is certainly possible to use tools that can make connections to both data stores and manipulate data that way, such as Access, Excel, or SSIS. Here I will introduce a process that does not need any special tools or data drivers. Instead, we can use the utilities and methods that come with a standard Sql Server and MySQL install to accomplish that task.

With this approach, it is assumed that matching tables already exist on MySQL. If not, they need to be created first.

This process is comprised of these steps: first bcp command will be generated based on Sql Server database meta data (sysobjects, think information_schema in MySQL); then the generated bcp commands will be executed; the resulting csv files can then be transferred to the MySQL server, optionally it is possible to compress them if the size is big; and finally the csv files will be imported into MySQL with LOAD DATA LOCAL INFILE. …

If:

1. A table t1 is created in database test;
2. A login is granted select permission on t1;
3. t1 is dropped and then recreated.

Then that login would still be able to read the newly recreated table t1, even if t1 has totally different columns. The reason is that the table select privilege is stored in tables_priv in mysql database, and when t1 is dropped, that privilege will not be cleared in tables_priv.

Personally, I think this needs to be changed. In other words, when a drop table command is issued, not only the table needs to be dropped, this command should also go to tables_priv in mysql to remove that select permission. Not too sure how big a task that is, perhaps I should try to see if I can implement it myself, after I get comfortable with the source code and development process of MySQL.

It's really a hard job to decide into which companies to invest. We at Open Ocean, my investment company, have talked with around 60 companies the last few months to find those that we think have a great potential and that would benefit from our involvement.

One company that more than well matched our investment criterias was Ironstar, that makes the MoiPal virtual world. With more than 100,000 registered users they are definitely a company to watch out for.

I have always been interested in virtual worlds and see how the communities around them continue to grow. The thing that really stands out to me with MoiPal, is the way it combines the social networking aspects similar to Facebook, with the caring of your online character, like you do with …

My calendar for the upcoming months is already filling up with conferences, trade fairs and other events at which I'll speak about MySQL. Here's a quick overview:

• This coming Thursday at 15:00 CET, I'll be speaking about "Backing up MySQL using file system snapshots" at the MySQL University. The session will be hosted live using DimDim, which is a great online conferencing and presentation system (Flash required). Attendance is free, so come and join me if you want to learn more about this backup technique!
• On Friday, 6th of March at 15:15 I'll give a presentation about "MySQL Backup and Security" in the …

It is possible to generate SVG graphs directly without requiring access to the Google Chart Servers. Perl provides a wealth of libraries which can create SVG graphs (but most not quite as pretty as the charts which the Google service creates).This could be invaluable for people who want to serve all the information from their own website - for example, SSL encrypted websites - where some 'secured

Last time I demonstrated a case where stored procedures are slow.  There were a few comments that I should include selects in the stored procedure to make the tests  more realistic.  From experience I already knew the answer so I didn’t go into that level of detail, but since stored procedures are all about database access this is a reasonable comment. 

This is a simple stored procedure that selects data and then summarizes it by customer.  No one would actually write this as it is far too easy to use a SQL statement instead.    Assume the logic is more complex and can’t be done easily by the standard SQL techniques of case statements, temp tables, etc, and then this makes more sense.  

The end result is this stored procedure takes 696 seconds to run. 

create procedure slowCounter()
begin

    declare done int …

I'm just back from a little vacation in the winter-wonderland and had some time on the train to spend. Thanks to power-plugs the 8hrs were well spent and ended up in a first, rough cut of a lua-customizable, mysql-shell that is based on the internals of the MySQL Proxy.

Branch

     $ bzr branch lp:~jan-kneschke/mysql-proxy/mysql-shell

if you are interested and want to follow the development.

Everyone already has a mysql-shell, the mysql program that comes with each MySQL installation.

I wanted to take its idea a bit further and make it customizable with our well-known Lua interface. Even if this ongoing work, it already does something useful:

$ mysql-client
(not connected)> .connect
root@127.0.0.1:3306 []> select 1;
{
 ["1"] = 1,
}
OK (warnings: 0, auto-commit: true)
root@127.0.0.1:3306 []> explain extended select 1;
{
 ["id"] = 1,
 ["select_type"] = …

Well… kinda… (and nobody make fun of me for using my MythTV box as a testing ground for SQL against MySQL).

myth@orpheus:~$ mysql -u root test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2496
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a tinytext default 'fail');
ERROR 1101 (42000): BLOB/TEXT column 'a' can't have a default value
mysql> create table t1 (a tinytext default '');
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'a' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)