betweenGo

1. Get the Ruby OCI8 driver.  Download the file that ends with “mswin32.rb” and install like this:

   E:\ruby>ruby ruby-oci8-1.0.3-mswin32.rb
   Copy OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8
   Copy oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8
   Copy oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt
   OK?
   Enter Yes/No: Yes
   Copying OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done
   Copying oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8 ... done
   Copying oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done
   OK

   You can test the driver by running a query using Ruby.

   E:\>ruby -r oci8 -e "OCI8.new('foo','12345','sid').exec(
   'SELECT * from users') do |r| puts r.join(' | ') ; end"

2. Install the ActiveRecord Oracle adapter.gem

   E:\ruby>install activerecord-oracle-adapter --source http://gems.rubyonrails.org

3. Update config/database.yml to connect to Oracle

   development:
     adapter: oracle
     database: sid
     username: foo
     password: 12345
     timeout: 5000

4. Test by doing a rake db:migrate.
5. Test by running the Ruby on Rails server and making sure there are no errors upon startup.

This article is based on these articles.

• Oracle in Ruby on Rails
• Ruby on Rails with Oracle FAQ
• Connecting Ruby on Rails to Oracle on an Intel Mac in Leopard (Mac OSX 10.5)

To list all the users in a database log in as the sysadmin and query the dba_users table.

$ sqlplus sysman/123456@dev01

SQL> select username from dba_users where username like 'Frank%';

Recently our DBA recommended the following for creating tables.

1. All constraints (primary keys, unique keys, foreign keys, etc….) should be declared outside of the CREATE TABLE …. statements, and instead done as ALTER TABLE statements.
2. All tables must have table and column comments to provide information for the data dictionary/schema metadata.

Not Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL,
  PRIMARY KEY (id)
);

Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (id);
COMMENT ON TABLE items IS 'repository items';
COMMENT ON COLUMN items.id IS 'primary key (repository id)';
COMMENT ON COLUMN items.type IS 'item type';

I asked the DBA why this is considered best practice and this is what he said.

The DBA’s put indexes into a different tablespace than the table itself for storage, admin, and somewhat performance reasons (NetApp spreads out the I/O so does not quite apply to Upromise environment).

If the PK is part of the table create statement, they have to break out the statement in order to put the PK into a different tablespace or different storage parameters than the table. Having the statements separate from the start makes things smoother.

- Jeff Janousek, Upromise DBA

This morning I was unable to access my Oracle database which runs on my laptop. It’s the first time I have had this problem.

When I tried to login I saw this.

$ sqlplus foo/foo@dev01

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I tried to login without the TNS alias I saw this:

$ sqlplus foo/foo

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

On the internet I saw recommendations to set your ORACLE_SID and ORACLE_HOME environment variables. But on my system ORACLE_SID is not set and ORACLE_HOME is blank. Finally I just tried restarting the OracleServiceDEV01 service and that worked.

I was wondering how to do this and thankfully someone wrote up a nice article on the SQL update statement.

Here is their example of how to do this.

UPDATE suppliers
SET supplier_name =
  ( SELECT customers.name
  FROM customers
  WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
  ( SELECT customers.name
  FROM customers
  WHERE customers.customer_id = suppliers.supplier_id);

Thanks to this article, Oracle/PLSQL: Change a user’s password in Oracle, it was quite simple to change a user’s password.

SQL> alter user frank identified by password;

Whenever I tried to start my local TNS Listener service, OracleOraDb10g_home1TNSListener, I would get an error like this.

The OracleOraDb10g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

This forum thread, Can’t start Oracle service, suggested I do the following to diagnose the problem.

1. cmd
2. lsnrctl
3. start

This worked and I saw the following error which showed my Oracle install had not been properly done, the listener was pointing to the wrong server.

LSNRCTL> start
System parameter file is
  C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Error listening on:
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bad.betweengo.com)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
    32-bit Windows Error: 1004: Unknown error

In C:\oracle\product\10.2.0\db_1\network\admin\listener.ora the listener is improperly configured for bad.betweengo.com.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bad.betweengo.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

I ran Oracle’s Net Configuration Assistant and updated the Listener configuration which fixed the above listener to point to my server. Now C:\oracle\product\10.2.0\db_1\network\admin\listener.ora points correctly to good.betweengo.com.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = good.betweengo.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

When I run rake db:migrate I get this error at the end.

Error in my_thread_global_end(): 1 threads didn't exit

I pinned it down to this code in one of my migration files.

# create admin user
user = User.new
user.login = 'admin'
user.password = 'password'
user.save(false)

If I don’t run user.save(false) I don’t get the error. I am not sure why, the user does get saved properly to the database.

I saw this post on the MySQL forums that seemed to indicate it was an issue with libmySQL.dll. So I upgraded my MySQL instance from 5.0.27 to 5.0.51a. Of course this did not go smoothly, I got this error when trying to reconfigure the MySQL server instance “MySQL service could not be started error 0″. Fortunately another post on the MySQL forums, Could not start service : Error 2003, solved this problem for me. I just had to remove the following files from the mysql/data directory.

• ib_logfile0
• ib_logfile1
• ibdata1

Unfortunately when I then did a rake db:migrate I saw this error.

Mysql::Error: Table 'prayer.schema_info' doesn't exist:
SELECT version FROM schema_info

After deleting and recreating the database I was finally able to run rake db:migrate. Unfortunately I still got the same error that inspired this post.

Googling some more I saw a MySQL bug report, MySQL Bugs: #25621: Error in my_thread_global_end(): 1 threads didn’t exit. Apparently this is a client side issue and I think I can safely ignore it though it is quite annoying.

Strictly speaking, this is not MySQL bug. This is a client bug - a client application (PHP, probably) linked with libmysqlclient calls my_thread_init() but does not call my_thread_end() as appropriate (doesn’t call at all or calls too late). MySQL client library detects this and issues a warning.

On the other hand, we can just remove the check and let buggy applications to fail some other way. Not calling my_thread_end() is a guaranteed memory leak. Calling it too late could easily crash the application.

With MySQL listing all the tables in a database is easy enough.

mysql> show tables;

With Oracle it’s a little less straight-forward.

> select table_name from user_tables;

OR

> select * from user_objects where object_type = 'TABLE'; 

OR

> select table_name from tabs;

OR

> select table_name from all_all_tables;

Today when I started up phpMyAdmin (version 2.10.0.2) by going to http://localhost/phpMyAdmin I saw this error screen.

I googled around but could not find any solutions.

So I went and tried to install the latest version of phpMyAdmin, 2.11.5. When I tried to run setup I saw this error screen.

Now that I had an error message to work with I googled around and found this post, Xampp phpMyAdmin Problem, which helped me diagnose the problem.

It turns out during one of my cleaning sessions I wiped out the session directory in, “C:\DOCUME~1\fkim\LOCALS~1\Temp\php\session.” After restoring it I ran into my next problem.

I upgraded to PHP 5.2.5 from 5.2.1 but that did not help. I installed the mbstring module but that did not help.

Finally I added the PHP extension directory to the path (I had already added the PHP directory to the path) and it finally worked! My path now includes the following two directories, E:\Program Files\PHP and E:\Program Files\PHP\ext.