Category Archives: Database

ORA-12514 and ORA-27101

Posted on by
Reply

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.

SQL Update in One Table Based on Values in Another Table

Posted on by
Reply

Dolphin Crest on Flickr

(Photo: Dolphin Crest by jurvetson)

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 name =
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id)
WHERE EXISTS
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

Oracle TNS Listener service not starting

Posted on by
13

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))
    )
  )

‘Error in my_thread_global_end()’ when running rake db:migrate

Posted on by
2

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.

Listing All Tables

Posted on by
Reply

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;

phpMyAdmin not starting

Posted on by
Reply

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

phpMyAdmin – Error

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.

phpMyAdmin – Error
Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly.

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.

phpMyAdmin – Error

 Cannot load mysql extension. Please check your PHP configuration. – Documentation

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.

View that Calculates using Previous Date

Posted on by
Reply

I have a table of data that tracks the mileage of my beloved 2005 Toyota Prius.

+----+------------+---------+---------+-------+ 
| id | date       | mileage | gallons | cost  | 
+----+------------+---------+---------+-------+ 
|  1 | 2005-02-14 |     280 |   8.615 | 16.03 | 
|  2 | 2005-02-27 |     480 |   4.775 |  8.59 | 
|  3 | 2005-03-19 |     713 |   7.213 | 14.27 | 
|  4 | 2005-04-09 |     999 |    7.86 | 16.81 | 
|  5 | 2005-04-11 |    1172 |   3.174 |  6.63 | 
|  6 | 2005-05-05 |    1560 |   8.889 | 18.66 | 
|  7 | 2005-06-07 |    1985 |   9.815 | 20.01 | 
|  8 | 2005-07-03 |    2444 |   9.868 |  21.7 | 
|  9 | 2005-08-13 |    2762 |   7.728 |    20 | 
| 10 | 2005-09-11 |    3271 |  10.072 | 30.11 | 
| 11 | 2005-10-24 |    3646 |   8.953 | 22.19 | 
| 12 | 2005-10-30 |    3959 |   6.583 | 17.11 | 
| 13 | 2005-11-04 |    4184 |   4.538 | 11.25 | 
| 14 | 2005-11-21 |    4631 |   8.742 |    18 | 
| 15 | 2005-12-31 |    4897 |   8.511 | 18.64 | 
| 16 | 2006-02-02 |    5609 |    7.83 |    18 | 
| 17 | 2006-03-01 |    6064 |    3.38 |  7.16 | 
| 18 | 2006-03-13 |    6400 |   7.171 |  16.2 | 
| 19 | 2006-03-22 |    6605 |   5.399 | 13.17 | 
| 22 | 2006-02-16 |    5944 |   7.957 |  17.5 | 
| 20 | 2006-04-06 |    6974 |   7.774 | 19.43 | 
| 21 | 2006-04-23 |    7316 |   7.102 | 20.45 | 
+----+------------+---------+---------+-------+

I wanted a way to calculate how many miles were driven for each trip, i.e. between consecutive dates, and what was the miles per gallon (MPG) for that trip.  To do this I modified what I learned in this post, unique ID field, getting next and previous existing ID from table to create a view that does all these calculations.  The advantage of this view is that it does not matter what order I enter the data, it will always calculate correctly.

CREATE VIEW v_prius_mpg AS 
  SELECT id, date, mileage, gallons, cost, mileage as trip_mileage,
         mileage / gallons AS mpg, cost / gallons AS price_per_gallon 
    FROM prius_mpg 
    WHERE id = 1 
  UNION 
  SELECT New.id AS id, New.date, New.mileage, New.gallons, New.cost,
        (New.mileage - Old.mileage) AS trip_mileage,
        (New.mileage - Old.mileage)/ New.gallons AS mpg,
        (New.cost / New.gallons) AS price_per_gallon 
    FROM prius_mpg New, prius_mpg Old 
    WHERE New.id > 1 
      AND Old.id = 
        (SELECT id 
           FROM prius_mpg 
           WHERE date < New.date 
           ORDER BY date DESC 
           LIMIT 1);

And here are the results of the view.  Note how row 20 is out of order but it still calculates correctly the trip mileage and MPG.

+----+------------+---------+---------+--------------+------------------+ 
| id | date       | mileage | gallons | trip_mileage | mpg              | 
+----+------------+---------+---------+--------------+------------------+ 
|  1 | 2005-02-14 |     280 |   8.615 |          280 |  32.501450957632 | 
|  2 | 2005-02-27 |     480 |   4.775 |          200 | 41.8848167539267 | 
|  3 | 2005-03-19 |     713 |   7.213 |          233 | 32.3027866352419 | 
|  4 | 2005-04-09 |     999 |    7.86 |          286 | 36.3867684478371 | 
|  5 | 2005-04-11 |    1172 |   3.174 |          173 | 54.5053560176434 | 
|  6 | 2005-05-05 |    1560 |   8.889 |          388 | 43.6494543818202 | 
|  7 | 2005-06-07 |    1985 |   9.815 |          425 |  43.301069791136 | 
|  8 | 2005-07-03 |    2444 |   9.868 |          459 | 46.5139845966761 | 
|  9 | 2005-08-13 |    2762 |   7.728 |          318 | 41.1490683229814 | 
| 10 | 2005-09-11 |    3271 |  10.072 |          509 | 50.5361397934869 | 
| 11 | 2005-10-24 |    3646 |   8.953 |          375 | 41.8854015413828 | 
| 12 | 2005-10-30 |    3959 |   6.583 |          313 | 47.5467112258849 | 
| 13 | 2005-11-04 |    4184 |   4.538 |          225 | 49.5813133539004 | 
| 14 | 2005-11-21 |    4631 |   8.742 |          447 | 51.1324639670556 | 
| 15 | 2005-12-31 |    4897 |   8.511 |          266 | 31.2536717189519 | 
| 16 | 2006-02-02 |    5609 |    7.83 |          712 | 90.9323116219668 | 
| 17 | 2006-03-01 |    6064 |    3.38 |          120 | 35.5029585798817 | 
| 18 | 2006-03-13 |    6400 |   7.171 |          336 | 46.8553897643285 | 
| 19 | 2006-03-22 |    6605 |   5.399 |          205 | 37.9699944434154 | 
| 20 | 2006-02-16 |    5944 |   7.957 |          335 | 42.1012944577102 | 
| 21 | 2006-04-06 |    6974 |   7.774 |          369 |  47.465912014407 | 
| 22 | 2006-04-23 |    7316 |   7.102 |          342 | 48.1554491692481 | 
+----+------------+---------+---------+--------------+------------------+

I always wanted to appear on every row how many gallons of gas had been consumed up to that day and how much had spent on gas up to date but I could not figure out how.  The only thing I could do was determine the sums up until now.

mysql> select sum(gallons) as gallons_to_date,
       sum(cost) as cost_to_date from prius_mpg where date <= current_date;
+-----------------+--------------+
| gallons_to_date | cost_to_date |
+-----------------+--------------+
|         161.949 |       371.91 |
+-----------------+--------------+
1 row in set (0.01 sec)

Debugging a Category’s Bad Child Products

Posted on by
Reply

Today I was using the CategoryLookup droplet to find a category’s child products. However when I accessed the child products I would get this JDBC error.

java.lang.NullPointerException
at java.lang.String.(String.java:166)
at oracle.sql.CharacterSet.AL32UTF8ToString(CharacterSet.java:1517)

I realized my product data was corrupted. However this category had over 70 child products. The stack trace wasn’t telling me which one was corrupt and going through each child product to find out which one was corrupt was too painful.

I first queried the database to find all the child product ID’s.

SQL> select child_prd_id from dcs_cat_chldprd where category_id='cat101' order by sequence_num;

I then created a simple JHTML page which would query each child product and output which ones were corrupted.

<java>
final String [] product_ids = { "prod101", "prod102", "prod103" };

for (int ii = 0; ii < product_ids.length; ii++) {
out.print(ii + ". [" + product_ids[ii] + "] ");
try {
</java>
<droplet name="/atg/commerce/catalog/ProductLookup">
<param name="id" value="`product_ids[ii]`">
<oparam name="output">
<valueof param="element.displayName"/><br/>
</oparam>
</droplet>
<java>
} catch (RuntimeException exc) {
out.println(exc + "<br>");
}
}
</java>

Once I knew which child products were bad I removed their mappings to the category in dcs_cat_chldprd.

SQL> delete from dcs_cat_childprd where child_prd_id = 'prod102' and category_id='cat101';

Then I updated the sequence numbers so that they are all consecutive by moving the ones at the end to fill the holes created by the previous deletes.

SQL> update dcs_cat_childprd set sequence_num = 1 where child_prd_id = 'prod103' and category_id='cat101';

Retrieving the Source from the Database

Posted on by
Reply

Sometimes I am faced with a situation where I need to know the source for a view or a procedure or a function. Fortunately Oracle stores the source in the database.

First I check to see what kind of object whose source I need is.

SQL> select object_name,object_type,status from user_objects where object_name = 'FOO';

If the object is a table I can simply recreate the table by doing a desc on that object.

SQL> desc FOO;

If the object is a function or stored procedure you can find its source in the user_source table.

SQL> select text from user_source where name = 'FOO' order by line;

If the object is a view you can find its source in the user_views table.

SQL> select text from user_views where view_name = 'FOO';

If the output seems to be truncated it is because text is of type LONG which is a big binary field. To allow more output do this.

SQL> set long 4000

Thanks to this article and this forum for helping me figure this out.