What's up with Metalink?

REAL SLOW today. Maybe they upgraded to Oracle Linux?

Year Six

Disclaimer:
I have the unfortunate luck of being hired on the same date that Tom Kyte started his blog. Honestly, I've been working on this a couple days so I'm posting it anyway, even if you think I am a copycat.

Six years ago today I started with my current company. This is the longest I've ever had the same title, although my responsibilities have changed over the years. It's also the most time I've spent at the same place.

It was the tail end of the dotCom boom and I had over 40 interviews with companies in the Tri-State area. Most of them didn't pan out, but when it came time to choose, I had three offers to consider. When I accepted this job, I took a chance because I liked the people but didn't think they had enough for me to do. I was the sole DBA for one production database that ran on an Ultra 2 (2 CPUs, 54G of disk, 512M). The telecom company I came from had three E5500's (six CPUs, 2G RAM, 600G disk). In fact, they had more than enough for me to do.

My first project at the new company was to move approximately half of the schemas in the production db to a new server. By the end of the first year, I was managing four db servers. Today, my team of three manages about a dozen db servers and almost two dozen instances. We've gone from a little six-pack of disks to a 3+TB SAN.

My second project was to setup a backup & recovery plan. Good thing, too, because about 6 months later we did a full recovery of one of our major production systems. Fortunately, we only had 3 un-planned recoveries until the blackout in August 2003. Recovered 12 databases that night.

Now MySQL and Linux are at the forefront of of my knowlege adventure. Maybe six years from now our MySQL databases will outnumber the Oracle ones...

Using vacation

I try to be a good corporate citizen. When I'm out of the office I use the vacation program to automatically reply and let people know I'm out of the office. Somehow, I think the 9.2.0.7 patchset has figured out how to look at the DBA's .forward file and know when to have problems. No matter what you think of me, I don't relish the idea of recovering a 600G database using Juno free dialup from Aunt Sally's house.

Using MySQL's LOAD DATA LOCAL

We're starting on a new project using MySQL that will bulk load CSV data once a day and then users can report on it whenever they want. In the days of old (ie. Oracle), we'd simply setup a load job using SQL*Loader or use external tables. In MySQL, loading data is just an extension of SQL using the LOAD DATA command. Since my data was going to be distributed, I wanted
to explore Mike Hillyer's suggestion of using the LOCAL option of LOAD DATA.

First, I created a small table called “testload” :

mysql> create table testload (empid integer, name varchar(20),
bonus integer);
Query OK, 0 rows affected (0.02 sec)

I then decided to try it from the server to verify everything worked as expected:

mysql> -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.18

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

mysql> use user1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> load data infile '/home/users/jeff/foo.txt' into table
testload fields terminated by '|';
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testload;
+------+-------+------+
| id   | name  |bonus |
+------+-------+------+
|    1 | jeff  |   30 |
|    2 | user1 |   20 |
|    3 | gail  |   60 |
|    4 | bob   |   40 |
|    5 | john  |   70 |
|    6 | jim   |  100 |
+------+-------+------+
6 rows in set (0.00 sec)

mysql> delete from testload;
Query OK, 6 rows affected (0.00 sec)

Now I know it works as the root user. Lets try as somebody else on the server. First, I check to make sure I have FILE privilege:

mysql> select host, user, password, file_priv from user
> where user = 'user1';
+------+-------+-------------------------------------------+-----------+
| host | user  | password                                  | file_priv |
+------+-------+-------------------------------------------+-----------+
| %    | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
+------+-------+-------------------------------------------+-----------+
1 row in set (0.00 sec)

mysql@sql1 $ mysql -u user1 -h sql1 -P3322 -p user1
Enter password:
ERROR 1045 (28000): Access denied for user 'user1'@'sql1' (using
password: YES)

Hmmm, I don't really get this one since I should be covered by the '%'. Have to investigate that later, but lets grant permission on this host anyway.

mysql@sql1 $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.18

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

mysql> grant file on *.* to 'user1'@'sql1' identified by 'nopassword';
Query OK, 0 rows affected (0.00 sec)

Let's check the privs and try again from the same user:

mysql@sql1 $ mysql -u user1 -h sql1 -P3322 -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.18


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


mysql> select host, user, password, file_priv from user
> where user = 'user1';
+------+-------+-------------------------------------------+-----------+
| host | user  | password                                  | file_priv |
+------+-------+-------------------------------------------+-----------+
| %    | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
| sql1 | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
+------+-------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)

Now that I have given myself privileges on the server, it should
work, right?

mysql> load data infile '/home/users/jeff/foo.txt' into table
testload fields terminated by '|';
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


mysql> select * from testload;
+------+-------+------+
| id   | name  |bonus |
+------+-------+------+
| 1    | jeff  |  30  |
| 2    | user1 |  20  |
| 3    | gail  |  60  |
| 4    | bob   |  40  |
| 5    | john  |  70  |
| 6    | jim   | 100  |
+------+-------+------+
6 rows in set (0.00 sec)

Sure enough, that did the trick. On to loading from a client:

host1:/home/users/user1/tmp $ mysql -u user1 -h sql1 -P3321 -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.18


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


mysql> load data local infile '/home/users/jeff/foo.txt' into
table testload fields terminated by '|';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> quit

Now what? I go back to the documentation and re-read about the
parameter local_infile. I'm pretty sure I set it, but lets check
anyway:

mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

That's what I thought. I went over the docs once again and saw a
mention of the local-infile argument to the mysql client. So I tried
that:

host1:/home/users/user1/tmp $ mysql -u user1 -h sql1 -P3321 -p
user1 --local-infile -p user1

Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 5.0.18


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


mysql> load data local infile '/home/users/jeff/foo.txt' into
table testload fields terminated by '|';
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

Nice. That is exactly what I am looking for. Knowing that you can set
preferences in your .my.cnf file, I setup the local-infile option in
my .my.cnf.

host1:/home/users/user1 $ more .my.cnf
[client]
loose-local-infile=1

As long as I'm at it, why not setup the host, port, and user in my
.my.cnf.

[client]
loose-local-infile=1
host=sql1
port=3321
user=user1

Then, it's a simple command to login.

user1@host1 13> mysql -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 5.0.18

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

mysql> 

I learned a lot about LOAD DATA during this exercise.

1. The local_infile parameter must be set to 1 in the my.cnf
   file on the server.


2. By default, the mysql client doesn't allow you to load data from the client. You must use the local-client flag or set the loose-local-client flag in your .my.cnf file.


3. You must have the FILE privilege.

How to break the Unbreakable, by Oracle

Beware of those patches you're applying.

Using Resource Profiles

I never had the need to use Resource Profiles extensively. Recently, though, I've had the opportunity to investigate this feature.

First things first, the resouce_limit parameter must be set to TRUE. You can either set it in the init.ora or via ALTER SYSTEM.

Next, you create the profile and assign limits to it. Read the descriptions carefully, though, some of the resource parameters may sound self-explanatory, but aren't. For example, you would think SESSIONS_PER_USER would mean the number of times a particular user can login. In fact, it's the number of concurrent sessions that can run at one time.

SQL> create profile really_small limit
2  sessions_per_user 1
3  cpu_per_session   100
4  cpu_per_call      100
5  connect_time       5
6  /

Profile created.

Then you assign the profile to a particular user:

SQL> alter user jh profile really_small;

User altered.

Just for kicks, you can check that your profile is assigned to your user.

SQL> select username, profile from dba_users where username = 'JH';

USERNAME     PROFILE
------------ ---------------
JH           REALLY_SMALL

SQL> select resource_name, resource_type, limit
2  from dba_profiles
3  where profile = 'REALLY_SMALL';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ------------------
COMPOSITE_LIMIT                  KERNEL   DEFAULT
SESSIONS_PER_USER                KERNEL   1
CPU_PER_SESSION                  KERNEL   100
CPU_PER_CALL                     KERNEL   100
LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
IDLE_TIME                        KERNEL   DEFAULT
CONNECT_TIME                     KERNEL   5
PRIVATE_SGA                      KERNEL   DEFAULT
FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
PASSWORD_LIFE_TIME               PASSWORD DEFAULT
PASSWORD_REUSE_TIME              PASSWORD DEFAULT
PASSWORD_REUSE_MAX               PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
PASSWORD_LOCK_TIME               PASSWORD DEFAULT
PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16 rows selected.

Your user connects to the database, starts running his monster query, and is promptly disconnected:

$ sqlplus jh/jh@mydb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Apr 4 20:34:23 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> select count(*) from all_objects, all_objects, all_objects;
select count(*) from all_objects, all_objects, all_objects
                 *
ERROR at line 1:
ORA-02392: exceeded session limit on CPU usage, you are being logged off

Sweet.