The Ji Village News

季庄新闻–Haidong Ji’s Blog

It was a clear sky in LA, but looking down, the city seemed to be covered in smog. After working on Sql Server 2008 material in LAX airport for a while, I boarded a turbo-prop for San Jose. Landing time was windy, so it was a bit bumpy. The only flight attendant, Cindy K, joked that “United throw in a few rides free of charge”. She was helpful with my luggage, with an easy smile.

So I checked in Radisson Hotel around San Jose airport afterwards. I booked the hotel through HotWire. It was a sweet deal, around 60 dollars per night. HotWire is a great site, especially for hotels. I’ve used it three times, all pretty satisfied.

Google map is awesome. It even provided me with information on how to get to Santa Clara convention center via public transportation. So I walked to the Gish station, saw Mexican, Moroccan, Japanese, and Chinese restaurants along the way, and got to the Santa Clara convention center in a jiffy. This is the first time I stepped on the VTA, and I was impressed: it has bike racks. I also saw signs in English, Spanish, Chinese, and Vietnamese, and people seemingly from all parts of the world. Now that is the America I love!

Oh, I am here for the MySQL conference. Unlike Sql Server conferences I attended in the past, I don’t know many people in this community, so my objectives are to talk to and learn from people, schmooze, and network, and score as many free food and drinks as I can. So don’t disappoint me, MySQL!

I will take MySQL certification exams, therefore I am going through MySQL 5.0 Certification Study Guide. It is a pretty cool book in that it goes to the point right away without many wasted words. Too many technical books dance around the topic, fill the volume with screen shots, but do not deliver the goods in the end. I am reading the Safari online version. With so many pages, I can see the physical book can be bulky.

Anyway, some comments and observations:

1. Sybase, MySQL, and Sql Server’s definition and implementation of database and schema are somewhat close. I dislike Microsoft’s definition and usage of schema, especially in Sql Server 2005. It is very confusing to new or DBAs from other platforms.

Sybase, MySQL, and Sql Server all support the USE Database statement;

2. I like mysql command line tool and how the result set is displayed. It looks clean and neat to me, although, like command line tools from other database platforms, when the result set gets wider, it gets messy;

3. Similar to Sql Server, MySQL also has the system and status variables that starts with @@. And, like Sql Server, you can just do SELECT @@Variable to retrieve its value, for example, SELECT @@sql_mode;

4. Played with MySQL Query Browser a little bit. Ctrl - E is the keyboard shortcut for statement execution, the same as Sql Server. However, F5 does not execute the statement in MySQL Query Browser.

As an independent consultant and trainer , I found myself doing a lot of existing code analysis and enhancement, mostly for stored procedures and ad-hoc SQL statements. I suspect a lot of people do the same thing as well, so I am really interested in learning how you do it. That’s the main purpose of this post. This post is tagged with Oracle, Sql Server, and MySQL, as the principals should be the same for all platforms.

Let me share with you how I do it. Notice that I look at table/column statistics and indexes in almost all the steps below. Therefore I purposely left them out in the discussion.

1. I always talk to the original author or the current owner of the code, asking him/her to walk me through it. I listen mostly, trying to understand why s/he writes code this way. I may ask a few questions, just to help me understand. I almost never do any lecturing at this stage. If the code is a stored procedure, I ask the author or stakeholder to give me sample execution statements, with parameters filled with typical values for production load. Sometime it is not possible to talk to the original author, because s/he may have left the company;

2. After that, I will get the database in question and restore it on my own workstation, with the data as close to production as possible. I will start running the code, and gather IO statistics and execution plan, and save the results for benchmark comparison later on;

3. Here is the raw code analysis stage, where code is put into an editor for analysis. In my case, it is VI or VIM. I find myself using the * command on all variable names and tables (make sure you are doing case insensitive search for Sql Server code), to see where they are used. This is also where I weed out the historical garbage code that is there but never being used anymore, such as unused variables, tables, etc. In the case of Sql Server, I mostly look for cursors, temp tables, and user defined functions;

4. Code simplification stage. I found a lot of bad performing code is unwieldy and unnecessarily complex. At this stage, I look for redundant WHERE clauses, convoluted AND and/or OR operators. This can be a tedious and time-consuming process. At this point, it is important to talk to the author or owner of the code, as a good understanding of data and entity relationship is crucial. Also, since I have gained some understanding from the steps above, it is easier to have an intelligent conversation with the code owner, with fresh perspective and momentum. Usually something good will come out of the conversation, and we will have something concrete to work on next;

5. I will make the changes discussed in step 4, once again, collect statistics and execution plan for comparison;

6. Repeat Step 4 and 5 a few times, when necessary. I usually start talking about better database design, code/design refactoring, set based operations, etc., as hopefully I have some credibility and gained the customer’s trust.

So what do you do to break down complex, low performance SQL code? What methodology, tools, tips, tricks you can share with me?

Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from Oracle, DB2, Sybase, Postgresql, etc., to Sql Server, or the other way around. It might be slightly easier to migrate from Sybase to Sql Server, considering their common root.

1. It is not easy to migrate existing app, unless the app is a simple one. Even for that, there are enough quirks that can throw people off and cause enough frustration to derail the whole projects. I’ve seen that happening twice, having engaged in moving 2 apps from Sql Server to Oracle;

2. Therefore, the best way to migrating to a new database RDBMS, in my opinion, is to start from a new initiative, probably not big initially. When you start things from a clean slate, you don’t have the historical garbage to worry about. Furthermore, you will give the team enough time to learn the new platform, and prepare the team for future migration, if you choose to do so;

3. Having open-minded team members is crucial to a migration project’s success. Too often people have emotional attachments to the platform they are familiar with, possibly out of job security concerns and lack of general curiosity toward new things.

I generally adopt a platform agnostic attitude, and don’t get religious and too carried away on the platform I work on. Having said that, I think these are points that marketing people can spin for persuasion purposes:

1. MySQL has too many storage platforms: MyISAM, InnoDB, MaxDb, and the newly introduced Maria. This can be viewed as a plus, as it provides choice. The downside of it is that it causes confusion for end users;

2. MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc. One could argue that MySQL is not mature in this area since it is new for them, but I think it is difficult to find evidence to substantiate that claim. Also, running the risk of offending some people, I think the importance of relational model got overblown a bit;

3. Sql Server offers the CLR integration. This can be a great selling point;

4. Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.

5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

As far as migrating MySQL to Sql Server in a hosting web environment, my honest opinion is Sql Server will be fighting an uphill battle, because MySQL excels in this arena, especially for small and medium-sized, or departmental organizations, with the proliferation of such LAMP app like blogs, wikies, discussion boards, etc. I believe Microsoft’s weapon of choice in this arena should be SharePoint. Given Microsoft’s clout, it is certainly a battle worth fighting.

I am curious about character sets and collations, especially how they are used in databases. I got some time to play with them recently. I did some testing today on MySQL. I will do the same test on Sql Server, Oracle, and PostgreSql, time permitting. I am only dealing with simplified Chinese at this point. I may take up traditional Chinese too.

Here is my setup:

1. I created a table that stores simplified Chinese characters in different character set, along with collation used, pinyin, number of strokes, and tone value. There are 126 collations in MySQL, only 10 of which are suited for simplified Chinese.

2. I used the Chinese version of the golden rule, 己所不欲, 勿施于人, from Confucius, as my sample characters. Of those 8 characters, 欲 and 于 have the same pinyin. To make the test more interesting, I added one more character, 旗, since it has the same radical as 施.

Here are the findings:

1. I don’t see any difference between the _bin and _chinese_ci collations. In all cases, they produced identical results;

2. Big5 sorts on number of strokes. gb2312 and gbk sort on pinyin. Yes, all of this is stated in the documentation, but I want to see it for myself;

3. ucs2 and utf8 collations generate the same sort results. Interestingly, it is only through those two that characters share the same radicals are sorted next to each other, at least based on my sample. I tend to believe that should be true in general. Another interesting thing is that with ucs2 and utf8, all 独体字 are sorted together, and all 合体字 are sorted together, at least based on my sample.

4. (This is an updated item, thanks to the comment from reader Jedy) However, if you have any English letter as a beginning of the string, regardless of the string is all English letters or English mixed with Chinese characters, the _bin and _CI collation do make a difference. For _bin collations, capital English letters always come first. For _CI collations, non-capitalized English letters come first.

Below is the script I used and the results, if you are interested.

SQL query: SELECT big5_bin , PinYin , StrokeNo , Tone FROM t1 ORDER BY big5_bin

SQL query: SELECT big5_chinese_ci , PinYin , StrokeNo , Tone FROM t1 ORDER BY big5_chinese_ci

SQL query: SELECT gb2312_bin , PinYin , StrokeNo , Tone FROM t1 ORDER BY gb2312_bin

SQL query: SELECT gb2312_chinese_ci , PinYin , StrokeNo , Tone FROM t1 ORDER BY gb2312_chinese_ci

SQL query: SELECT gbk_bin , PinYin , StrokeNo , Tone FROM t1 ORDER BY gbk_bin

SQL query: SELECT gbk_chinese_ci , PinYin , StrokeNo , Tone FROM t1 ORDER BY gbk_chinese_ci

SQL query: SELECT ucs2_bin , PinYin , StrokeNo , Tone FROM t1 ORDER BY ucs2_bin

SQL query: SELECT ucs2_unicode_ci , PinYin , StrokeNo , Tone FROM t1 ORDER BY ucs2_unicode_ci

SQL query: SELECT utf8_bin , PinYin , StrokeNo , Tone FROM t1 ORDER BY utf8_bin

SQL query: SELECT utf8_unicode_ci , PinYin , StrokeNo , Tone FROM t1 ORDER BY utf8_unicode_ci

Here is my notes on setting up replication on MySQL. In a lot of cases, that is not good enough, because it replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.

At first I thought I could just add this to /etc/my.cnf on the slave:
[mysqld]
replicate-do-db=MyDb

That didn’t work very well for statements like this, assuming you are in a database other than MyDb:

insert into MyDb.TableInMyDb values (SomeValue)

Fine, I thought, let me add this to slave’s my.cnf:

replicate-wild-do-table=MyDb.%

It’s not enough. For the Sql statement above, it still couldn’t catch and execute that statement.

After some searching and testing, I found what I was looking for. As far as I can tell, it works beautifully. All you need to do is to modify my.cnf on slave. Just add the databases to ignore in the list. Below is a sample:

replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = testdb
replicate-wild-do-table=MyDb.%

A couple of weeks ago, a friend asked about replication on MySQL 4.1.7. I’ve worked with replication in the past, just a quick and dirty job on MySQL 5, and soon forgot about it. This time, I wanted to do it on MySQL 4, and make sure I take good notes for my own benefit. If it can help somebody else, all the better. The official documentation is here. It took a little time to wade through it.

The process below is used to replicate all databases on the master to the slave(s). I will talk about replicating only certain selected databases in a future post.

1. At the master server, check if binary logging is on. Use show variables like ‘%bin%’ to check if binary logging is on or not. If it is not on, turn it on by adding log-bin under [mysqld] section in /etc/my.cnf. Also remember to add server-id=Number, where the number is a unique number for all MySQL servers within the network. It looks something like this:

[mysqld]
log-bin=mysql-bin
server-id=1

If mysql restart is needed, do:
shell> mysqladmin -u root shutdown
shell> mysqld_safe &

2. Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege. If the account is used only for replication (which is recommended), you don’t need to grant any additional privileges.

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO ‘repl’@'%’ IDENTIFIED BY ’slavepass’;

3. Issue ‘flush tables with read lock’. While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and position on the master. Take note of those 2 values, ’cause they will be used later:

mysql > SHOW MASTER STATUS;
+—————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| mysql-bin.003 | 73 | test | manual,mysql |
+—————+———-+————–+——————+

4. I used mysqldump, documented here, to dump out the database I am interested in. Then do ;

mysql> UNLOCK TABLES;

5. Stop the slave server, add the following lines to its my.cnf file:

[mysqld]
server-id=slave_id

The slave_id value, like the master_id value, must be a positive integer value from 1 to 232 – 1. In addition, it is necessary that the ID of the slave be different from the ID of the master. For example:

[mysqld]
server-id=2

When setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

Now start MySQL on the slave;

6. Create the database with the same name of the database where the dump was taken,

shell> mysql -u root -p -D MyDbNameHere < dump_file.sql

7. Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replication_user_name’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;

8. Start the slave threads:

mysql> START SLAVE;

It should work. One thing that got me during my process was that I had firewall enabled on both the master and slave, therefore I had to open those ports up for MySQL communications. From this point on, EVERYTHING will be replicated. Notes on replicating only selected databases to follow…

In a previous post I mentioned about patches needed to accomadate the new DST changes for Sql Server. (I know, this is one of Dannyman’s favorite subjects )

For MySQL, obviously you need to patch the host server first. Then you need to find out if MySQL needs separate work. Here is a note I gathered. Let me know if I am wrong on this or if there is a better way.

1. Get into mysql and do \s to find out the version of your MySQL.

If it is prior to 4.1.3, don’t worry about it.

Else

2. Do select @@global.time_zone;

If the result is SYSTEM, don’t worry about it.

Else

You need to load time zone info, usually at /usr/share/zoneinfo into your mysql database, by running something like:

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

More information available at MySQL’s documentation site.

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

delete from t1 where c1 = 1;

you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.

Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:

delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount <= 50050;

If the statement returns “one row affected”, then the person would know John Doe’s salary. This person can then issue

rollback

If “no row affected” is returned, the person can continue until s/he can find it out.

I did a quick search on ANSI SQL 92 standard, but didn’t find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.