Hi All!

This year, the International Free Software Forum celebrated its 10^th anniversary. It happened last week in Porto Alegre.

Pythian presented a session on Thursday called 8 Rules for Designing More Secure Applications with MySQL.

As promised, here are the slides we used on that session: 8 Simple Rules to Design Secure Apps with MySQL (PDF).

Cheers!

Welcome, readers! It’s time for another update to our series of posts on installing Oracle on Ubuntu Linux. In this edition, we’ll be installing Oracle 11g R1 on Ubuntu 9.04, both 32-bit.

This time, I’ve used VirtualBox to run a virtual machine (VM) to perform our work. (Virtualization has a number of advantages; in this case, I made several trial installs, trying different combinations and configurations. Having a pristine, basic set-up accelerated the whole process, since I didn’t had to reinstall from scratch on every new attempt.)

You might want to review the previous editions of this series, as there are technical references on this text fully detailed on previous posts. See these HOWTOs for Ubuntu:

• 8.10 - Intrepid Ibex
• 8.04 - Hardy Heron
• 7.10 - Gutsy Gibbon
• 7.04 - Feisty Fawn

Since we’re installing on a VM, we’ll be using Ubuntu 9.04 32-bit Server edition, , so let’s download it and check the MD5sum:

user@jackalope:/media/trezentos/downloads$ md5sum ubuntu-9.04-server-i386.iso
20480057590ff8b80ad9094f40698030  ubuntu-9.04-server-i386.iso
user@jackalope:/media/trezentos/downloads$

Download Oracle Database 11g Release 1 (11.1.0.6.0), and verify the provided cksum:

Read the rest of this entry »

I’m very proud to share with you a few things: Sheeri K. Cabral, Nick Westerlund, Paul Vallée, Peter Ling, and I (Augusto Bott) will be in Santa Clara, CA for the MySQL Conference and Expo, MySQL Camp, and the Percona Performance Conference, next week.

Nick and I will be presenting a session called Proactive Operational Measures on the Percona Conference, and another session called 8 Rules for Designing More Secure Applications at the MySQL Camp.

Sheeri will be presenting Understanding How MySQL Works by Understanding Metadata with Patrick Galbraith ; Agile Environments and DBAs with Laine Campbell; and Connect and Replicate Securely: How to Use MySQL with SSL.

Last but not least, Sheeri will be presenting a keynote on Wednesday: How to be a MySQL Community Superhero.

Please introduce yourself when you see us—we’d love to meet you!

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.

In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.

Read the rest of this entry »

The other day, a client mentioned they were getting strange results when running ALTER TABLE. The episode involved modifying an existing primary key to add an auto_increment primary key: it was “shifting” values. Say what?!

As it turns out, it was a very special value getting changed: zero. Some fiddling revealed the underlying reason. Care to join me?

To understand what’s going on, follow the example below as we start with an empty database, create a table and insert a few rows:

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table test_table (id int not null primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test_table (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id) values (0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

mysql>

Now let’s change our PK and make it auto_increment Read the rest of this entry »

Hello, there! With another Ubuntu release, it has come the time to update our series of posts on how to install Oracle 11g on Ubuntu. If you’ve been following, we’ve been publishing updated howtos since Ubuntu 7.04:

• Installing Oracle 11g on Ubuntu 8.04 Hardy Heron LTS
• Installing Oracle 11g on Ubuntu 7.10 Gutsy Gibbon
• Installing Oracle 11g on Ubuntu 7.04 Feisty Fawn

In fact, in this article I refer several times to previous posts regarding some configuration aspects and why I chose particular values. Also, note that this series of posts is a work in progress and we were able to improve this series with your help. So please do post comment below as your collaboration is very much appreciated.

In this post, we’ll see the steps needed to install Oracle 11gR1 on an Ubuntu 8.10 Intrepid Ibex box all the way to creating your very first database. I’ve been working very hard to ensure that at every new post, the results you get when executing this procedure are as deterministic as possible, leading to a successful setup.

Please keep in mind that this is not a supported architecture, so pay special attention to the order in which I do things. Don’t rush and try to merge steps, as it took me a lot of attempts to make this setup work properly. There are some reboots and also there’s a specific order you need to do things, so please follow the instructions step-by-step and verify the the results of every single command.

Preparation

Let’s get down to it, shall we? The first thing to do is to get some files. We need an ISO image of Ubuntu (a CD/DVD will do) and one of Oracle 11gR1. Get Ubuntu 8.10 Server here; and Oracle 11gR1 here. (It’s free, but you have to register on the Oracle website to download it.)

It’s a good idea to check the md5sum of each image after downloading from the Internet.

Read the rest of this entry »

The other day we began to encounter weird and random errors on small and innocent queries that shouldn’t give any errors at all. It all lead to one of our most basic health checks failing for no apparent reason.

The first clue that popped into our minds was related to case-sensitivity, since the failing check was looking for the column names of the only table in the schema with UPPER CASE name. This symptom was especially weird since all of this was happening on MySQL setups running on Windows, and so we’re not sure if this was being caused by some internal code library, MySQL, or Windows itself.

Since that was the only clue we then had, it seemed obvious that we should start fiddling with the lower_case_table_names system variable. After a couple of restarts, this approach was leading us nowhere, so we finally gave up on it.

Then, we had the brilliant idea of actually executing that statement by hand on the command line to see what happened:

C:\pythian>mysql -uXXXX -p XXXX -e "desc TABLE_NAME"
Enter password: *****
ERROR 1 (HY000) at line 1: Can't create/write to file 'C:\MySQL      mp\#sql_634_0.MYI' (Errcode: 22)

C:\pythian>perror 22
OS error code  22:  Invalid argument

C:\pythian>

None of us remembered seeing this one before, so we stared at the monitor for a few moments, not realizing the meaning of this message. I guess our focus on the case-sensitivity was driving us away a from the real cause. So . . .  time to check the my.ini file. Read the rest of this entry »

So you want to store URLs in MySQL, and the URLs have those annoying %20%27%7C%26%5E%2B%2D%25 symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:

%20 - space
%27 - '
%7C - |
%26 - &
%5E - ^
%2B - +
%2D - -
%25 - %

So, how about we do some search’n'replace on that?

mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @url as original,
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        @test,'%20',' '),
    ->        '%27','\''),
    ->        '%7C','|'),  -- REPLACE() is case sensitive
    ->        '%7c','|'),  -- so we have
    ->        '%26','&'),
    ->        '%5E','^'),
    ->        '%5e','^'),  -- to repeat
    ->        '%2D','-'),
    ->        '%2d','-'),  -- ourselves
    ->        '%2B','+'),
    ->        '%2b','+'),  -- sometimes
    ->        '%25','%') as replaced;
+--------------------------+----------+
| original                 | replaced |
+--------------------------+----------+
| %20%27%7C%26%5E%2B%2D%25 |  '|&^+-% |
+--------------------------+----------+
1 row in set (0.01 sec)

mysql>

We can easily turn this into a function: Read the rest of this entry »

Note: Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex is now published.

After our last post about installing Oracle 11g on Ubuntu 7.10 (November, 6^th), and considering Ubuntu 8.04 LTS was released on April 21^st, I spent some time reviewing and putting together this new HOWTO for the installation.

Please note: I’ve used the x86 server version of Ubuntu 8.04, but the same steps should work without any problems for the Desktop version. Also notice that this whole procedure can easily take over six hours to complete, so don’t complain I didn’t warn you!

So, let’s get started, shall we?

Step One

Get the Ubuntu Linux 8.04 Hardy Heron (x86, 32-bit) image here, burn it, and install on any box you like. The only remark on the installation is that you should ask the installer to install an OpenSSH server at the end of the installation, since we’ll perform all the steps on this procedure remotely.

I’m not sure about the minimum requirements for the server, as, the last time I checked, running Oracle on Ubuntu is not officially supported by Oracle. In case you’re wondering, however, I’m using an x86 Pentium-like machine with 512M of RAM.

Step Two

Download Oracle 11g for Linux (x86, 32-bit).

Read the rest of this entry »

Hi there!

I arrived at Santa Clara yesterday, just in time for the MySQL Community dinner (check this post on Arjen’s blog). It was a lot of fun, not to mention the opportunity to talk to developers and other folks from MySQL/Sun.

Unfortunately, I forgot to pack in my luggage a card reader or a proper cable for my camera, so pictures will have to wait a few days. To mitigate this problem, I’ll try to hunt down more posts from other guys, but in the meantime, you might want to check the 2008 MySQL Users Conference & Expo group at Flickr.

Yesterday was the “Tutorials Day”. I’ve been to many of them. The subjects were so interesting I just couldn’t stay put for the whole session so, I kept jumping from one to the other. A few quite interesting sessions: MySQL Proxy, the complete tutorial. A very interesting session on hacking and adjusting it to fit your needs. It was held by Jan Kneschke (main author and developer) and Giuseppe Maxia (testing).

MySQL Cluster, with Stewart Smith. An amazing and thorough session on MySQL Cluster - all day long. There’s nothing better than learning from the guys that actually are writing the code, right? If you are curious, his blog can be found here.

In the afternoon, there were two very interesting sessions, but I had to pick only one of them. SQL Antipatterns (by Bill Karwin) was quite interesting, but I ended up at the Ask Bjorn Hansen’s session, called Real World Web: Performance & Scalability, MySQL Edition. This must have been one of the best sessions, ever (mostly because we’ve been giving the exact same advice to our clients for some time now).

This morning started with a keynote from Mårten Mickos (who used to be MySQL’s CEO, but his “job title” must have changed after the acquisition by Sun). His speech was followed by Jonathan Schwartz’s (CEO of Sun) and after that, we heard from Werner Vogels (CTO of Amazon.com). Quite interesting, but I’m not about to repeat everything they’ve said. Instead, I’m linking Colin Charles’s post, Ahead in the Cloud by Verner Vogels.

I’m running to another session now, so… keep tuned: more news will follow!