cold dead raw fish (CDRF)

Database GUIs, Free Toad Clone, Oracle DBA GUI, MySQL, SQL Console, etc

Java based Toad clone:
Oracle SQL Developer (ALSO works with OTHER DATABASES as well !!!)
http://www.orafaq.com/wiki/SQL_Developer
http://www.oracle.com/technology/products/database/sql_developer/index.html

MySQL GUI
http://dev.mysql.com/downloads/gui-tools/5.0.html

Facebook Discriminates Against the Elderly!? (Centenarians) - Signup Bug / Workaround

I was creating test accounts today, to use in testing facebook apps.

Though you normally are not allowed to have multiple accounts, it is allowed if you are a FaceBook developer.  You create the initial account, and then convert it to a "test" account by visiting this URL:
http://www.facebook.com/developers/become_test_account.php

Anyway, I was creating test accounts, and to make them easier to remember I was using birthdays of real people who are now deceased.  Some of the people were born in years like 1902 and 1908.

On the initial signup screen, FaceBook DOES have those years listed in its dropdown list of Years, though you have to scroll a bit.  But it will NOT ACCEPT BIRTHDATES from 1902 or 1908.  It complains "please enter a valid date".

Well, this IS a valid date, a 100 year old person could still want to use facebook, not too many, but it *could* happen.  AND it's one of the choices ON THEIR FORM, so presumably somebody at FaceBook thought it was valid at some point.

Fear not centenarians - there is a workaround!  If you add 10 years to the initial birthday on the signup screen, making it 1918 instead of 1908 for example, you CAN then edit it back to 1908 once you've completed the signup process and verified your email, etc.  You do this by editing your profile.  It appears to be the same form, with the same choices, and this time it accepts it.

So is this a big deal?  It's certainly not FB's target demographic, though they are attracting older users these days in the US, and always had older users in some countries.

One criteria for ranking the priority of bugs at a company is the number of people who are affected.  Just considering the US and Japan, Wikipedia puts the combined estimated total at about 85,000 centenarians.  If only 1% of them were online, that would still be 850 potential users, and given the popularity of FaceBook, maybe 1/4 would try to use it, so maybe 200 people.  That might seem small, but equal access is not there to project the majority, and although the numbers are really small, this could result in a high profile news item on a local TV station, the headlines would make for a good sound bite.  So the potential for bad PR disproportionate.

Another criteria for ranking the priority of bugs is whether there is an easy workaround or not.  In this case there is, read below, but the initial signup experience takes on an "unwelcoming" tone.

Also, maybe a 100 year old person wouldn't suddenly wake up and start using the Internet, but imagine somebody who had been in the technology field as a profession, and started using PC's back in the 1980s, whey they were perhaps 75 years old, a much more likely scenario, and had kept using them.  They could still be online.  Also consider that an elderly person might be getting help from a younger person to do the initial setup; senior citizens could certainly have interesting stories to share online, even if they aren't doing the actual typing.  Even younger disabled people sometimes have their caregiver doing the typing and mousing for them.

I think FaceBook could make a couple easy changes to fix this bug:
1: Don't put years in the drop down list on the initial screen that will be rejected.  This seems the most obvious start.
Or better still:
2: In the error message, explain that if they really ARE that old then Welcome to facebook and you can update your age in the Profile page.
Or even:
3.a: Use the Guinness Book of Records to find the oldest person and accept years up to that oldest known living person.  Along with...
3.b: If the age is more than 2 or 3 standard deviations off from the mean, politely ask "are you sure" ?

Just my 2 cents.

HORRIBLE DirecTV Compression Artifacts today

There's been talk about cable and satellite providers over compressing video streams to jam more channels onto their cables and dishes, but I had subconsciously chalked that up to videophile whining and hadn't noticed it too much myself.

But today's running of Star Trek on KBCW channel 44 (Bay Area, 3pm on Sunday 5/11/08) was ABSURD.  They've got the compression set WAY TOO HIGH.  I'm on an analog / standard def setup with an RCA tuner, but other channels look fine.

GIANT compression bands danced around any flat surface, looking sort of like off-color Moire patterns.  And the SOUND was even ringing a bit in places for over compression.

So it's Mother's day, and this isn't sports, so I guess it got flagged as a lower priority (eligible for high compression), but I'm paying for this signal and this is the show I chose to watch.  Though these compression artifacts look different than the old pre-cable pre-satallite analog interference, they were on a par with bad rabbit ears today, which is unacceptable for a paid service.

I wouldn't think channel 44 would have originated the programming with this high of a compression, as they have fixed slots allocated.  This also seems to be a fairly new problem, I haven't noticed it for Star Trek in weeks past.  I did call Direct TV, the person I got wasn't sure about what I was talking about, but she did listen and did check with her supervisor; she took notes and said she would pass it on.

DirecTV should not compress signals to the point where they look worse than the old free rabbit ears days, not for paid service.

8 Gigs in the Palm Treo 700p / Super SD Card with Built in USB Adapter / Hack Required / Bootable Linux?

There are now 8 Gig SD cards on the market, called HDSC.  Even better, you can get them with a BUILT IN USB connector on the other end.  One end has the SD pin contacts, the other end has USB port contacts that are hidden away under a folding plastic flap.

The only trick is getting the Treo 700p to see all 8 Gigs.

Amazon has both the 8 Gig and 4 Gig convertible SD/HDSC/USB cards.  The 8 Gig one needs the hack, the 4 gig  one doesn't!
4 Gig (no hack): http://www.amazon.com/SanDisk-SDSDPH-004G-A11-15MB-Ultra-Plus/dp/B000UZJ0O2
8 Gig (needs hack): http://www.amazon.com/SanDisk-SDSDPH-008G-A11-15MB-Ultra-Plus/dp/B0012W7HGA

This guy has pretty good instructions.
http://blog.treonauts.com/2007/02/treo_8gb_hdsc_c.html
In case his blog goes away, here's the 2 key links, but READ HIS INSTRUCTIONS!!!
ZFile manager: http://software.treonauts.com/product.asp?id=6011
8 Gig driver: http://software.treonauts.com/product.asp?id=10523
Both files are free, and you do not need to even register, pick "download as guest" if you're in a hurry.

The only weird thing is that Palm reports either no memory available, or negative memory available, but applications still seem to work.  The camera and a few of my favorite games still seem to run correctly.  The camera now reports 7000+ available pictures.  I'm now at a bit over 5 Gigs (copied over some test files)

So, you have a  Palm with 8 Gigs of memory.

AND, if you get the dual connector style card, you also have an 8 Gig USB Thumb Drive with you at all times!  Pop it out of the phone, crack the hinge, and pop it into your favorite USB port.  Move files over, pull out, fold back flat, and back into the Palm.

With that much space, I wondering if these SanDisk Ultra II Plus cards would boot a PC?  Modern PC's can boot from external drives on USB ports.  *Some* USB thumb drives will also work... though some don't.

So in addition to having an 8 Gig Palm and 8 Gig thumb drive, you could also potentially have one of those portal virtual Linux boxes with all the portal apps.  Walk up to somebody's computer, pop a chip out of your phone plug it into their computer, reboot, and your phone's memory card is now running Linux on their machine.  Pretty cool, though I have NOT tried this yet.

We're livin' in the future.. future... future.....

Update Mon 5/12/08: Somehow the Treo no longer recognizes the card, it offers to format it when I insert it, but then it gives an error if I say yes.  Inserting it into the PC with the USB end shows the files are still there, looking fine.

I'm not sure of the cause.  I did move the card a bunch of times between PC and Treo.  And I had accidentally downloaded an Audible title to the phone's internal memory because the card wasn't in there.

When I have time I'll try adding the PRC file again, etc.  But this is a bit unsettling.   At least the data is still there.

GotoMeeting Video Codec

When you record meetings or webinars with GotoMeeting (or GotoWebinar) they can use either their own format, or convert to a more general format after the recording is done.

The codec for the native Goto Meeting format is called "G2M2" and be downloaded here:
https://www1.gotomeeting.com/codec

Presumably using this codec you could then load and convert the video after the fact as well, though I have not tried it.

It's better to change it ahead of time in GotoMeeting with Tools / Recording / Settings and then select the second option, the more general format, the radio button "Record to Widnows Media Player file"

Recovering from Oracle ORA-01157 / ORA-01110 (somebody deleted an obsolete data file directly from the file system)

Note: This didn't go very smoothly, so these are RAW NOTES, but in the end I did fix it!!! (scenario 3 below).  The notes from the original thread were a bit vague / wrong details, so this is at least a bit more to go on.

In the course of trying to startup an Oracle database you might get an error like:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:\ORACLE_DATA\CUSTOMER\DB.ORA'

This has to do with Oracle looking for a data file associated with one of its databases that it can't get to any more.  There are 3 general cases:
1: The file is there, but the OS has it locked by some other process, or there's a permission issue, etc.
  Fix: operating system issue, permissions, or reboot to clear processes, etc.
2: The file is not there, it was deleted, but you want to recover it!
  Fix: See this thread and scroll down to Sept 15 2005 7:12 posting.  I did not try this.
3: The file is not there, it was deleted, and you don't care.  It's an old database that somebody knew was obsolete, so they just deleted it.  Oracle doesn't like that!
  Fix: See the same thread but scroll a bit up to the Sept 15 6:46 posting.

# 3 is the case I'm in.  The data was old and was blown away.  I just need to tell Oracle not to worry about it.

This is what I'm trying, from this thread (6:46 posting)

Before you start, you need to know where Oracle stores its control files.  And to find that out, I looked in the ora.ini file, so you'll need to find that.

Oracle ini I found:
D:\oracle\admin\(dbname)\pfile\init.ora.1111200217029
Look in there to find the control_file location, on line 34 I have:
control_files=("D:\oracle_data\stack\CONTROL01.CTL", "D:\oracle_data\stack\CONTR
OL02.CTL", "D:\oracle_data\stack\CONTROL03.CTL")

So the "control files" mentioned in the thread I mentioned above are in D:\oracle_data\stack.  We'll need to know this later.

(from Command Prompt, with sqlplus in path d:\oracle\ora92\bin)
sqlplus /nolog

(then inside SQL)
alter database backup controlfile to trace;

OR is it...

alter database backup controlfile to trace as 'some/path' REUSE;

I think the "reuse" parameter has them take out all the cruft, so that you can rerun it.

(in another command window)
Looked in file system, found D:\oracle\admin\stack\udump\stack_ora_700.trc   Use DIR /OD to see the most recent files, and compare against current system time.

(then in ANOTHER command window)

cd D:\oracle_data\stack
(remember, this was where we found the control files in an earlier step)
mkdir ctrl_bak
copy *.ctl ctrl_bak\
del *.ctl

In the second command window, with the trc file, make a copy of it.  CALL THE ORIGINAL stack_ora_700.trc.BAK.  We will be modifying stack_ora_700.trc

It needs to not have a bunch of cruft comments in it (I think that's what the reuse does).

And then on the first line you need to ADD this:  (see this page, search for controlfile)
connect / as sysdba

BTW If you wanted to do that on the command line it would be:

sqlplus "/ as sysdba"

Now from a command prompt we will restore the control file from this human readable backup:

sqlplus /nolog @stack_ora_700.trc

Not sure you need this next one ....

(now back in SQL)
startup nomount;

I eventually had to reboot.  But after that it worked!

You can also check the control files directory and notice that your 3 .CTL files are back.

JDBC Can't connect to Oracle: ORA-01033: ORACLE initialization or shutdown in progress

The advice on the web is to wait a while until Oracle has time to startup properly, which can take a while.  The admin tools won't connect either.  But if after waiting and waiting it still doesn't work, you may need to get out the crow bar.

I got a bit further with the info from this posting:
(from OS command prompt)
sqlplus /nolog
(then at SQL prompt)
shutdown
(get message saying database is not open)
startup

The last startup command will often get things going.  OR it will tell you why it's so darn unhappy.  In my case there's some old database it's trying to access that is no longer on the disk.

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:\ORACLE_DATA\CUSTOMER\DB.ORA'

For me that's an obsolete database, so now I just need to find the command to tell Oracle to not worry about it.  More later...

Odd behavior when using multiple MySQL TIMESTAMP fields, fixed by changing to type DATETIME

Note 1: I know this sounds too weird to be true, but I saw it consistently.
Note 2: Yes, I'm aware that there are other ways to add an hour to a datetime interval, it's a long story, see prev post.

I had a table with two TIMESTAMP fields.  Adding an hour's worth of seconds to one field would reset the OTHER field to the current date and time.

So I'd do a select on start_time and end_time from my log table.  Both are fine.

Then I would do:
mysql> update log_table set end_time = from_unixtime( unix_timestamp( end_time
) + 3600 );

You'll notice I'm just changing the end_time field.

Then I'd do a select of both values and it would show that START time was now all set to the current date and time.  I recreated this a bunch of times.  I also dropped and recreated the table and indexes.  I also dropped and recreated with no index for either field, just in case it was something weird with indexing.

Changing the field from type TIMESTAMP to type DATETIME fixed the problem.  All of my problems vanished with making that one change.

I really think this is a bug.  I'm running 5.0.51a-community-nt MySQL Community Edition.  I suspect it has something to do with TIMESTAMP's defined behavior when it gets a null value, to return the current date and time.  But it doesn't explain the "cross talk" between fields - I'm guessing that stems from some type of query evaluator/executor and maybe cached/uncached garbage from somewhere... but who know!

Workaround: DATETIME = good, TIMESTAMP = bad (at least when using multiple date / time fields in the same talbe)

Weird Calendar page for September 1752 (the month they dropped 10 days from)

   September 1752
 S  M Tu  W Th  F  S
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Pretty cool.  From PostgreSQL doc page.

Date math in MySQL vs. Oracle, PostgreSQL, Oracle, etc., adding floating point numbers, etc.

In many databases and computer languages dates and times can be treated like floating point numbers.  For example, 3.5 is understood to mean 3 and a half days, or 3 days and 12 hours.

This can be handy in a database, for example to roll all the dates in a table forward by some arbitrary amount of time.  In most databases you can just directly add floating point numbers to date / time values.  Although MySQL has a wealth of date and time arithmetic built in, it doesn't seem to allow for this.  It wants you to use minutes or seconds, etc.

The workaround is to temporarily use the UNIX_TIMESTAMP() and FROM_UNIXTIME() and deal with seconds instead of floating point numbers.  I little extra coding on the Java side as well.

So for example in Oracle I could just say:
UPDATE log_table SET end_time = end_time + 0.506851;

In PostgreSQL it's a bit more complicated, you can't just add a float:
UPDATE log_table SET end_time = end_time + (0.506851 * INTERVAL '1 day')

In MySQL you need different syntax AND you need to recalculate the value beforehand, so I would say:
UPDATE log_table SET end_time = FROM_UNIXTIME( UNIX_TIMESTAMP(end_time)+43792 );

Where 43792 seconds = 0.506851 * 12 * 60 * 60
That is 0.506 days times 12 hours times 60 minutes times 60 seconds
And it's assumed that end_time is of type DATETIME or TIMESTAMP.  This will NOT WORK with just DATE or TIME fields.

I guess you could probably do something like:
UPDATE log_table SET end_time = FROM_UNIXTIME( UNIX_TIMESTAMP(end_time)+ 0.506851 * 24 * 3600);
BUT there's probably some casting needed as well, I doubt that'll work as is.
And I'm not wild about doing the calculation in every line, though maybe it's no big deal, I haven't benchmarked it.

Note that the PostgreSQL trick of multiplying a floating point number  times the INTERVAL of 1 day  doesn't seem to work in MySQL.  I've tried 3 variants with parenthesis, etc.  So these do NOT work:
UPDATE log_table SET end_time = DATE_ADD(start_time,  0.506851 * INTERVAL '1' DAY );
UPDATE log_table SET end_time = DATE_ADD(start_time,  ( 0.506851 * INTERVAL '1' DAY) );
UPDATE log_table SET end_time = DATE_ADD(start_time,  0.506851 * (INTERVAL '1' DAY) );

The only other trick is that if you're building your SQL string in Java, you need to tell it to NOT use scientific / exponential notation.  So you do NOT want 4.3792E4.

To convert a float or double in Java to a String that you can then stuff into SQL you can use:

// Start with approx half of a day (an arbitrary floating point number)
double myDelta = 0.506851;
// Convert to Seconds
double newDelta = myDelta * 3600 * 24;
// Format to a rounded string, with no scientific notation
java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
// Turn off fancy number formatting
nf.setGroupingUsed( false );
nf.setMinimumFractionDigits( 0 );
nf.setMaximumFractionDigits( 0 );
// And now the final String
String deltaStr = nf.format( newDelta );

If somebody comes up with a more direct route, please let me know.  The MySQL INTERVAL values for days seem to only accept integers.