Re: Taking export from SECONDARY_INSTANCE from 02 node RAC
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 27 Feb 2009 16:40:21 -0600
Message-ID: <11_pl.11548$pr6.4807_at_flpi149.ffdc.sbc.com>
jgar the jorrible wrote:
>> Mladen Gogala wrote:
>> And there are many other performance issues as well as standards issues..
>> Try this in mysql and oracle: (and unless they fixed it- which they have
>> not in my server...) will be very troubling to REAL database folks...
>> Especially if you need to move data from MySQL to say a mainframe or
>> COBOL app :-( And BTW per their crack[ed?] developer staff - this is
>> NOT a BUG because it is "documented behavior".. How whacked is that!!!
>> create table mytest (a char(10));
>> insert into mytest values ('ABCDE');
>> select '|'||a||'|' from mytest;
>> select '|'||cast(a as CHAR(20))||'|' from mytest;
>> ORACLE: (correct result!!)
>> SQL> create table mytest (a char(10));
>> insert into mytest values ('ABCDE');
>> select '|'||a||'|' from mytest;
>> select '|'||cast(a as CHAR(20))||'|' from mytest;
>> Table created.
>> SQL>
>> 1 row created.
>> SQL>
>> '|'||A||'|'
>> ------------
>> |ABCDE | <<--- note there are 5 chars+5spaces as per the data type.
>> SQL>
>> '|'||CAST(AASCHAR(20))
>> ----------------------
>> |ABCDE |
>> ==============================================================================
>> MySQL - the INCORRECT RESULT
>> mysql> create table mytest (a char(10));
>> Query OK, 0 rows affected (1.62 sec)
>> mysql> insert into mytest values ('ABCDE');
>> Query OK, 1 row affected (0.38 sec)
>> mysql> select '|'||a||'|' from mytest;
>> +-------------+
>> | '|'||a||'|' |
>> +-------------+
>> | |ABCDE| | <<--- note there are 5 chars only - no spaces.
>> +-------------+
>> 1 row in set (0.24 sec)
>> mysql> select '|'||cast(a as char(20))||'|' from mytest;
>> +-------------------------------+
>> | '|'||cast(a as char(20))||'|' |
>> +-------------------------------+
>> | |ABCDE| |
>> +-------------------------------+
>> 1 row in set (0.11 sec)
Date: Fri, 27 Feb 2009 16:40:21 -0600
Message-ID: <11_pl.11548$pr6.4807_at_flpi149.ffdc.sbc.com>
jgar the jorrible wrote:
> On Feb 27, 10:35 am, Michael Austin <maus..._at_firstdbasource.com> > wrote:
>> Mladen Gogala wrote:
>>> On Thu, 26 Feb 2009 19:29:07 -0600, Michael Austin wrote: >>>> I would caveat any discussion about licensing with "it all depends". >>> Yes, of course. Oracle sales force is not unlike the car dealership. >>> What, you think that $15,000 is too much for this only 4 years old >>> Ford Taurus? No problem, I will lower the price, just for you, it's >>> only $12,000 and I will throw in new tires, too. That is especially >>> true if you are a big shop with a big name. I've seen some deals with >>> cutting the prices in half - literally. Unfortunately, these days >>> everybody is in dire need of money, especially the banks. Oracle cannot >>> afford slashing the prices by much without layoffs. >>> I do predict, however, that, during this crisis, many shops will turn to >>> cheaper alternatives like PostgreSQL and MySQL, thus eventually forcing >>> Oracle to cut the prices. My boss, an avid Oracle fan, started a pilot >>> project for an office database with MySQL. I am an old oracle hack with >>> many years of experience but I did accept the task to learn and manage >>> MySQL. Should that go well, I am sure that we will see those projects >>> multiply like rabbits. That is the thing that will eventually force Oracle >>> to lower the prices and give some things like AWR report for free. > > Many jobs available for many projects that will fail, I'm sure. > > When most of Oracle's gross profit percentage comes from apps, Oracle > will... make AWR an app. > >>> I must say, though, that Oracle plans look so much nicer then this: >>> mysql> explain select ar.artist_name,al.album_name >>> -> from artist ar,album al >>> -> where ar.artist_id=al.artist_id; >>> +----+-------------+-------+------+---------------+---------+--------- >>> +--------- >>> -----------+------+-------+ >>> | id | select_type | table | type | possible_keys | key | key_len | >>> ref >>> | rows | Extra | >>> +----+-------------+-------+------+---------------+---------+--------- >>> +--------- >>> -----------+------+-------+ >>> | 1 | SIMPLE | ar | ALL | PRIMARY | NULL | NULL | >>> NULL >>> | 6 | | >>> | 1 | SIMPLE | al | ref | PRIMARY | PRIMARY | 2 | >>> music.ar >>> .artist_id | 1 | | >>> +----+-------------+-------+------+---------------+---------+--------- >>> +--------- >>> -----------+------+-------+ >>> 2 rows in set (0.20 sec) >>> mysql> >>> This is a simple NESTED LOOPS join.
>> And there are many other performance issues as well as standards issues..
>>
>> Try this in mysql and oracle: (and unless they fixed it- which they have
>> not in my server...) will be very troubling to REAL database folks...
>> Especially if you need to move data from MySQL to say a mainframe or
>> COBOL app :-( And BTW per their crack[ed?] developer staff - this is
>> NOT a BUG because it is "documented behavior".. How whacked is that!!!
>>
>> create table mytest (a char(10));
>> insert into mytest values ('ABCDE');
>> select '|'||a||'|' from mytest;
>> select '|'||cast(a as CHAR(20))||'|' from mytest;
>>
>> ORACLE: (correct result!!)
>> SQL> create table mytest (a char(10));
>> insert into mytest values ('ABCDE');
>> select '|'||a||'|' from mytest;
>> select '|'||cast(a as CHAR(20))||'|' from mytest;
>>
>> Table created.
>>
>> SQL>
>> 1 row created.
>>
>> SQL>
>> '|'||A||'|'
>> ------------
>> |ABCDE | <<--- note there are 5 chars+5spaces as per the data type.
>>
>> SQL>
>> '|'||CAST(AASCHAR(20))
>> ----------------------
>> |ABCDE |
>> ==============================================================================
>>
>> MySQL - the INCORRECT RESULT
>>
>> mysql> create table mytest (a char(10));
>> Query OK, 0 rows affected (1.62 sec)
>>
>> mysql> insert into mytest values ('ABCDE');
>> Query OK, 1 row affected (0.38 sec)
>>
>> mysql> select '|'||a||'|' from mytest;
>> +-------------+
>> | '|'||a||'|' |
>> +-------------+
>> | |ABCDE| | <<--- note there are 5 chars only - no spaces.
>> +-------------+
>> 1 row in set (0.24 sec)
>>
>> mysql> select '|'||cast(a as char(20))||'|' from mytest;
>> +-------------------------------+
>> | '|'||cast(a as char(20))||'|' |
>> +-------------------------------+
>> | |ABCDE| |
>> +-------------------------------+
>> 1 row in set (0.11 sec)
> > Preaching to the choir here. Try complaining to this guy: > http://monty-says.blogspot.com/ >
[thought I would go visit the bug database to see the status before commenting further...]
It was logged as a bug... #24424 [19 Nov 2006 5:28] - apparently they added a parameter to change this behavior Oct 10th 2008 to versions >=5.1 - the last time I responded to the bug was Jan 2008.
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
So it took them almost 2 years to figure out how what a data type is...
> jg > -- > _at_home.com is bogus. > If at first you don't succeed... write a book, be profiled in > newpapers and Reader's Digest, get on tv... > http://www3.signonsandiego.com/stories/2009/feb/27/1m27silver235851-familys-sailing-adventure-now-sub/?uniontribReceived on Fri Feb 27 2009 - 16:40:21 CST