pt-table-checksum help required I am trying to figure out pt-table-checksum, as I am using it for the first time.
It look likes the documentation is complex and not easy to understand stuff.

I have a complex replication topology setup: I have master-master active topology setup geographically separated and both syncing each other. Further a number of slaves under each master. Also, some of the slaves are using filter table replication under both masters.

The database I am using is around 200GB in size, and slaves are balancing reads. I need to make sure pt-table-checksum will not break replication and will not cause any loads on server which hurts app usage.

My requirements are

Connect on master1 and check master2 and all slaves under both masters and inform for any deficiences in between master slaves data.
Is it possible to check only specific tables' specific slaves ? e.g. check from master1 and all underlying slaves of master1 but do not touch master2 and its underlying slaves?
How to take care of replication filters ? e.g. master1 has slaves slave1 slave2 where slave2 replicating only some of the database tables while slave1 replicates the entire database. I need pt-table-checksum to check for differences on slave1 for entire database and check only tables that are replicating on slave2.

I am running pt-table-checksum (2.0.1) on test machines on an employees database. I inserted some records in employees table with sql_log_bin off to prevent them from replicating so I can test pt-table-checksum to report differences and sync on slave.

Created user on master and on slave with SUPER, REPLICATION SLAVE privilege. The user is testuser with testpass being the password

master$ ./pt-table-checksum --replicate=test.checksum --create-replicate-table --nocheck-replication-filters --databases=employees localhost
Cannot connect to h=slave-ip

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
07-09T13:48:38      0      0        9       1       0   0.082 employees.departments
07-09T13:48:39      0      0   331603       7       0   1.001 employees.dept_emp
07-09T13:48:39      0      0       24       1       0   0.040 employees.dept_manager
07-09T13:48:39      0      0   300028       1       0   0.577 employees.employees
07-09T13:48:48      0      0  2844047      13       0   8.006 employees.salaries
07-09T13:48:49      0      0   443308       1       0   0.989 employees.titles

You can see employees table has some extra records. Secondly I executed the following command.

master$ ./pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=employees --host=slave-ip --user=testuser  --password=testpass

Command executed without any slave. Nothing displayed. When I checked on slave employees count is still old and not updated as master.

You say you are using pt-table-checksum 2.0.1. I would recommend updating to 2.1, as there are many improvements in the tool.

Next, let me address your test. You say the slave was not updated after the first or second  commands that you ran. The second command looks to be trying to connect directly to the slave. pt-table-checksum won't report any differences unless the server you're connecting to has slaves.

Also, the --replicate-check-only option will not do any checksumming. (from the docs):

  If specified, pt-table-checksum doesn’t checksum any tables. It checks replicas for differences found by previous checksumming, and then exits.

Your first command doesn't seem to be able to connect to the slave host, which is why it doesn't report any differences. Make sure the user/pass that is connecting to the master can also connect to the slave.

Now, as for your complex setup, you are right to worry about breaking replication. With some slaves replicating only certain tables, you should heed the warning here:

  If the replicas are configured with any filtering options, you should be careful not to checksum any databases or tables that exist on the master and not the replicas.

You can specify which databases you want to checksum with the --databases option, and give a specific list of tables with the --tables option. Alternatively you can use the --ignore-databases and --ignore-tables options to provide a list of databases/tables to not checksum.

This will probably mean you will want separate pt-table-checksum commands based on which slaves you are trying to checksum. You will probably have to use the 'dsn' --recursion-method to accomplish this (I've never done it, personally)

As for load, pt-table-checksum comes with some options to throttle itself. Namely --max-load and --max-lag.

  The tool keeps track of how quickly the server is able to execute the queries, and adjusts the chunks as it learns more about the server’s performance. It uses an exponentially decaying weighted average to keep the chunk size stable, yet remain responsive if the server’s performance changes during checksumming for any reason. This means that the tool will quickly throttle itself if your server becomes heavily loaded during a traffic spike or a background task, for example.


What should it look like when your SQL servers are properly licensed? New job, going through my server inventory. I've been using this script to get the version & licensing:

select @@version as version,
SERVERPROPERTY('LicenseType') as licenseType,
SERVERPROPERTY('NumLicenses') as NumLicenses

But all my results are returning a disabled license, and null for the number:

version                                 licenseType NumLicenses
Microsoft SQL Server 2008 R2 (SP2) ...  DISABLED    NULL

Which has me questioning myself. Licensing isn't something I've had to deal with before. Is this query correct? Is it even possible for it to function without being properly licensed?

question: What should my query return if my server is properly licensed? Is there a better way to check?

Aha, those will always be disabled and null, according to the SERVERPROPERTY (Transact-SQL) manual page:

LicenseType:

Unused. License information is not preserved or maintained by the SQL Server product. Always returns DISABLED.

NumLicenses:

Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.

The query is not correct.
Instead, I need to download Microsoft's Assessment and Planning Toolkit (MAP).


Who is the person using a SQL login? Is it possible to find out who used a SQL-Server login?

Let's assume we suspect a person to abuse a SQL login that is supposed to be used by an application only to logon to SQL-Server. Is the domain user who used the SQL login logged anywhere, so that we can make a conclusion who used it?

No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server  can only record the information it has been provided, and when you use SQL Authentication, no Windows login / domain / group information is passed to SQL Server.

You can look at DMVs like sys.dm_exec_sessions and sys.dm_exec_connections to get other supporting information, such as IP address and host name, but:

You can only do this going forward, you can't look back in time unless you were specifically collecting this information (you can do this now with a server-side trace, or a logon trigger, or audit, or extended events, or...)
This will only help if the user connects directly to SQL Server from their workstation. If your users use a jump box in the data center or have common access to other machines, you'll be able to tell which machine they came from, but not which Windows user was logged into the box at the time. If you know exactly when the change happened in SQL Server, you might be able to correlate that time with the logs on the machine to determine which user it was.

You can't even prevent users from connecting as this SQL Authentication login unless you change the application to use Windows Auth and disable SQL Auth altogether. Sure, you could set up a logon trigger that prevents logging in unless host_name and/or program_name are within a desired set of values, but these things are very easy to spoof, so if this behavior is malicious, your options are quite limited.


How to increase size of temp tablespace in Oracle 12C RAC with ASM? I have an Oracle 12c RAC consisting of two nodes (OL6) and ASM. I use it for learning. For some reason I want to increase its temporary tablespace size. It's about 50M now. I've considered it using SELECT * FROM dba_temp_free_space; command. I want to increase it to 2048M.
I know, how to increase it in usual configuration:
ALTER DATABASE TEMPFILE '/u02/oradata/my_sid/temp01.dbf' RESIZE 2048M;

knowing temporary tablespace file's path.
I've tried to get information about database files' to see if there are some files with the word "temp" in their names:
select * from dba_data_files;

but I've only got the next files' names:
+DATA/ORCL/DATAFILE/system.287.970304491
+DATA/ORCL/DATAFILE/sysaux.288.970304523
+DATA/ORCL/DATAFILE/users.290.970304549
+DATA/ORCL/DATAFILE/undotbs1.289.970304547
+DATA/ORCL/DATAFILE/undotbs2.295.970304621

So, how to get proper path to a tempXX.dbf file and resize it?

I've created a new temp file using command:
ALTER TABLESPACE temp
   ADD TEMPFILE '+DATA/ORCL/DATAFILE/temp02.dbf' SIZE 2048M REUSE;

After that SELECT * FROM dba_temp_free_space; command showed me much more space available. However I don't know where old temp file is located and what is its name.
I've found old tempfile name and resized it:
SQL> SELECT * FROM dba_temp_files;

SQL> ALTER DATABASE TEMPFILE '+DATA/ORCL/TEMPFILE/temp.XYZ.XXXXXXXXX' RESIZE 500M;


Issue with composite type in an UPSERT function I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.testobj)
  RETURNS boolean AS
$BODY$
DECLARE
tmp_code smallint;
cur_member refcursor;
BEGIN
-- Check if the member exists first
OPEN cur_member FOR
EXECUTE 'SELECT testkey FROM netcen.test WHERE testkey=' || myobj.testkey ;
FETCH cur_member INTO tmp_code;
CLOSE cur_member;
CASE tmp_code
    WHEN COALESCE(tmp_code,0)=0 THEN
    -- Record not found INSERT a new record
    -- will skip user defined validation for now
    insert into netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);

    ELSE
    -- Record found UPDATE the record
    update netcen.test set
    test=myobj.test,
    testname=myobj.testname  WHERE testkey=myobj.testkey;

END CASE;
END;$BODY$
  LANGUAGE plpgsql;

Below is the type testobj

CREATE TYPE netcen.testobj AS
   (testkey smallint,
    tes text,
    testname text);

When I call the function:

SELECT netcen.fun_test('(3,khaendra@me.com,khaendra)':: netcen.testobj);

.. I get the following error message:

ERROR:  operator does not exist: smallint = boolean
LINE 1: SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
                                      ^
HINT:  No operator matches the given name and argument type(s).
       You might need to add explicit type casts.
QUERY:  SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
CONTEXT:  PL/pgSQL function "fun_test" line 11 at CASE

Where should I cast?
Definition of the table netcen.test:

CREATE TABLE netcen.test    (
  testkey smallint NOT NULL DEFAULT 0,
  tes netcen.dom_email_validation,
  testname text,
  CONSTRAINT key PRIMARY KEY (testkey)
)

@ Erwin, thanks for the links. I read and have modified my function to this, please go through it and tell me if it can work well with several clients calling the same function concurrently?

CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
  RETURNS boolean AS
$BODY$
DECLARE
myoutput boolean :=false;
BEGIN
    update netcen.test set
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
        myoutput:= TRUE;
        RETURN myoutput;
    END IF;
    BEGIN
        INSERT INTO netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);
    myoutput:= TRUE;
    EXCEPTION WHEN OTHERS THEN
        update netcen.test set
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
    myoutput:= TRUE;
    END;
    RETURN myoutput;
END;
$BODY$ LANGUAGE plpgsql;

I have done away with the type test and just used the table test! I didn't know that could work!

Answer
The error occurs here:
CASE tmp_code
    WHEN COALESCE(tmp_code,0)=0 THEN
Works like this:
CASE WHEN COALESCE(tmp_code,0)=0 THEN

You mixed two different syntax variants of PL/pgSQL CASE ("simple" vs. "searched") in an incompatible way.
There is another error:
update netcen.test set
test=myobj.test,
testname=myobj.testname  WHERE testkey=myobj.testkey;

Did you mean:
UPDATE test
SET    tes = myobj.tes
     , testname = myobj.testname
WHERE  testkey = myobj.testkey;

There is also no need for CREATE TYPE netcen.testobj .... You can use the table name netcen.test as type name.
You really want to "UPSERT"
In Postgres 9.5 or later use the new UPSERT (INSERT ... ON CONFLICT DO UPDATE). Like:

UPSERT with ON CONFLICT using values from source table in the UPDATE part

For older versions you can emulate an UPSERT. The simple form in plpgsql, without concurrency:
CREATE OR REPLACE FUNCTION fun_test(myobj testobj)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN FALSE;
   ELSE
      INSERT INTO test SELECT (myobj).*;
      RETURN TRUE;
   END IF;
END
$func$;

Could just be plain SQL with a data-modifying CTE:
WITH my_row(testkey, tes, testname) AS (
    SELECT 1::smallint, 'khaendra@me.net', 'khaendra'
    )
, u AS (
    UPDATE test t
    SET    tes = m.tes
         , testname = m.testname
    FROM   my_row m
    WHERE  t.testkey = m.testkey
    RETURNING t.testkey
    )
INSERT INTO test (testkey, tes, testname)
SELECT * FROM my_row
WHERE  NOT EXISTS (SELECT FROM u);

The time window for a possible race condition is extremely tiny with this form (single combined statement). If concurrency is still an issue (heavy concurrent write load), then ...
Your function reviewed
If the function returns, the row has been inserted or updated. The only other way is an EXCEPTION of a different kind. The returned value true is just noise. (Might be more interesting to return true for INSERT and false for UPDATE.) So I simplified:
CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE netcen.test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN true;
   END IF;

   BEGIN
      INSERT INTO netcen.test
      SELECT (myobj).*;  -- simpler form, parenthesis needed.

   EXCEPTION WHEN unique_violation THEN   -- cleaner
      UPDATE netcen.test
      SET    tes = myobj.tes
           , testname = myobj.testname
      WHERE  testkey = myobj.testkey;
   END;
   RETURN true;
END
$func$;

Related answers on SO:

Upsert with a transaction
Is SELECT or INSERT in a function prone to race conditions?

Blog post on UPSERT by Depesz.


Running loadjava with Windows authentication I want to run following command under Windows with Oracle Database 12c installed:

loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar

The problem is that I use Windows authentication and I connect the following way:

sqlplus / as sysdba

and I do not enter any username or password. Could you please tell me how to execute first command under Windows for a pluggable database?

You should not use SYS for this.

With a regular user, you can simply provide the username/password@address.

To do this with SYS, without the password:

Create the class:

C:\Users\Administrator>copy con Hello.java
public class Hello
{
    public static String world()
    {
    return "Hello World";
    }
}
^Z
        1 file(s) copied.

C:\Users\Administrator>type Hello.java
public class Hello
{
    public static String world()
    {
    return "Hello World";
    }
}

C:\Users\Administrator>set JAVA_HOME=O:\oracle\base\product\12.2.0\dbhome_1\jdk

C:\Users\Administrator>set PATH=%JAVA_HOME%\bin;%PATH%

C:\Users\Administrator>javac Hello.java

C:\Users\Administrator>jar cvf Hello.jar Hello.class
added manifest
adding: Hello.class(in = 273) (out= 209)(deflated 23%)

Connect to pluggable database without password:

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 9 16:18:34 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=pdb1;

Session altered.

Load the class:

SQL> exec dbms_java.loadjava('C:\Users\Administrator\Hello.jar');

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type, status, created
     from dba_objects where object_name = 'Hello';

OWNER      OBJECT_NAM OBJECT_TYPE             STATUS  CREATED
---------- ---------- ----------------------- ------- ---------
SYS        Hello      JAVA CLASS              INVALID 09-NOV-18

SQL> alter java class "Hello" compile;

Java altered.

SQL> select owner, object_name, object_type, status, created
     from dba_objects where object_name = 'Hello';

OWNER      OBJECT_NAM OBJECT_TYPE             STATUS  CREATED
---------- ---------- ----------------------- ------- ---------
SYS        Hello      JAVA CLASS              VALID   09-NOV-18

Clean up the mess:

SQL> drop java class "Hello";

Java dropped.


What is the meaning of `(ORDER BY x RANGE BETWEEN n PRECEDING...)` if x is a date? In another thread:

https://stackoverflow.com/questions/37759659/db2-query-to-find-average-sale-for-each-item-1-year-previous

the OP wanted a sliding average for the last 365 days. Using ROWS BETWEEN ... would be fine if it where guaranteed that there where exactly one occurrence per day, but that is not the case here. RANGE BETWEEN ...seems like a good fit, but it is not clear to me what it means in DB2. Not sure if it matters that db2 does not have an INTERVAL type, but mimics it with labled durations.

The documentation says: (https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html)

unsigned-constant PRECEDING

Specifies either the range or number of rows preceding the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or unsigned-constant FOLLOWING.

unsigned-constant FOLLOWING

Specifies either the range or number of rows following the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

DB2 allow constructions like:

values current_date - 1

the default unit date is day so this means:

values current_date - 1 day

Given this I would expect this example to work:

create table test
( d date not null
, x decimal(3,0) not null);

insert into test (d,x)
values ('2016-01-01',10),('2016-01-07',20),('2016-01-12',30);

I would expect the query:

select d, avg(x) over (order by d
                       range between 30 preceding
                                 and current row)
from test
order by d;

to return:

2016-01-01-00.00.00  10
2016-01-07-00.00.00  15
2016-01-12-00.00.00  20

or possibly generate an error, but instead the result is:

2016-01-01-00.00.00  10
2016-01-07-00.00.00  20
2016-01-12-00.00.00  25

I also tried adding day to the query:

select d, avg(x) over (order by d
                       range between 30 days preceding

select d, avg(x) over (order by d
                       range between (cast 30 as day) preceding

just in case, but both of these attempts results in:

 SQL0104N  An unexpected token "day" was found following "y ...
 SQL0104N  An unexpected token "cast(30 as day)" was found following "r ...

First suspicion I had that the unit is something smaller than day, but increasing the preceding part to 300 returns the same result. Perhaps even more surprising is that increasing it to 500 changes the result to:

 select d, avg(x) over (order by d
                        range between 500 preceding
                                  and current row)
 from test
 order by d

2016-01-01-00.00.00  10
2016-01-07-00.00.00  20
2016-01-12-00.00.00  30

Given the query:

 select d, avg(x) over (order by d
                        range between n preceding
                                  and current row)

for 300 <= n <= 399 the result of the last row is 25, for n<300 or n>399 the result of the last row is 30.

I can not figure out which rows that are seen by the avg function, my best guess is that there is some implicit cast of date to something else in the framing clause, but don't have any idea how to prove or disprove this assumption. Can someone shed some light on this?

Firstly, the expression values current_date - 1 would only be valid if Oracle compatibility mode were in effect -- it mimics the Oracle's datetime arithmetic where the default interval is expressed in (potentially fractional) days.

I think that regardless of Oracle compatibility, the range bounds should be comparable as integers, and comparing DATE values with integers might produce unexpected results. If you convert your DATEs to the number of days since some past moment you can use integer comparisons. You could use JULIAN_DAY(), for example:

select d, avg(x) over (order by julian_day(d)
                   range between 30 preceding
                             and current row)
from test
order by d

which produces the result you expect:

D          2
---------- ---------------------------------
01/01/2016   10.0000000000000000000000000000
01/07/2016   15.0000000000000000000000000000
01/12/2016   20.0000000000000000000000000000

  3 record(s) selected.

In the first fixpak of 10.5 it was allowed to use range over dates, but the results where unpredictable. In recent fixpaks this is no longer allowed, so much of the confusion in the question could have been avoided by using a recent fixpak.


Columns as independent lists of items So in my database I have items, and those items have categories.

I've created a small sample data.

I'm creating a report where a user will be able to filter the items by colour, category and some other attributes, and then it should simply list items that fit my parameters in separate category columns.

I also don't know how many categories the user will chose and thus I don't know how many columns my report should have.

This is how a pivot looks like when I apply it to my sample data. This is basically the format that I want, however I want to get rid of the nulls. I need the columns to just 'independently' list the items, like so

Since there was no items from the XX category in my sample dataset, the column is not there. The rest gets 'aggregated'.

Okay so this brings me to my main question. Or questions, rather, I think I have two.

1) I am new to SQL and I'm still learning so correct me if I'm wrong, but am I right to think that this is simply a task that 'should not be performed' in relational databases? I feel like this is going against the core rule, that one row is one piece of data, one entry. Here my one row becomes three (or more/less) pieces of data, and there is no explicit relation to between them.

I previously had to make a report where I displayed two items side by side, the user could pick a shop for the left side, another shop for the right side and see the best selling items from the 'left group' and the best selling items from the 'right group'. So I simply joined the two groups by ROW_NUMBER generated ranking and my data changed from 'one item per entry' to 'Nth posixtion in chosen shops per entry'. That made sense.

This here doesn't, at least to my current understanding. But that brings me to my second question:

2)They still want me to do it. How do I?

So far I have thought of two possible solutions.

One is I pick categories separately, then I join them together based on some kind of ROW_NUMBER, to assure one-to-one relationship (I don't really care about ordering)

So if my desired categories were 'OD', 'AL', 'UT', I'd take take them out with 3 separate queries like so

SELECT
    ROW_NUMBER() OVER (ORDER BY (select null)) AS rn
    ,Item
    ,Category
INTO #OD
FROM
    sth
WHERE
    Category = 'OD'

And so on for every category, and then join them together using 'rn' column. This works, but because I do not know how many categories the report user will choose, it gets problematic. Perhaps that could be done with dynamic SQL? I'm not sure and I'm not really a big fan of using it...

I've also thought of a different solution but I am not sure if that's doable. If it were possible to somehow assign numbers like this:

So the row number 1 is labeled '1' because it's the first value for 'OD' it encountered. Row 2 is labeled '1' as well because it's the first falue for 'AL' it encountered.
That way row number 4 is labeled '2' and row number 5 is labeled '3' because it's the second and third value for 'OD' column respectively. And then row number 6 is labeled '2' again because it's the second value for the 'AL' column.

That way I could use MAX() function and group by those assigned numbers. All the rows assigned 1 will get aggregated to one row, all the rows assigned 2 will be aggregated to another row etc. I am certain that there will aways be one item per row in my pivot, because an item cannot be of two categories.

I also feel like that solution would be much easier to do in a dynamic way (unknown number of columns - categories chosen) but I cannot figure out how to get such a numbering and if that is even possible.

Looking forward to your answers guys, I'm stuck!

I think this is what you are looking for:

test setup: http://rextester.com/SGKW56535

create table t (Item int, Kategoria char(2))
insert into t values
   (1111,'OD')
 , (2222,'AL')
 , (1234,'UT')
 , (1122,'OD')
 , (4321,'OD')
 , (5555,'AL')
 , (6666,'UT')
 , (65577,'AX');

query with row_number() partitioned by Kategoria:

select
    rn=row_number() over (
        partition by Kategoria
        order by Item
        )
    , Item
    , Kategoria
  from t;

results:

+----+-------+-----------+
| rn | Item  | Kategoria |
+----+-------+-----------+
|  1 |  2222 | AL        |
|  2 |  5555 | AL        |
|  1 | 65577 | AX        |
|  1 |  1111 | OD        |
|  2 |  1122 | OD        |
|  3 |  4321 | OD        |
|  1 |  1234 | UT        |
|  2 |  6666 | UT        |
+----+-------+-----------+

hard coded pivot example:

with cte as (
  select
    rn=row_number() over (
        partition by Kategoria
        order by Item
        )
    , Item
    , Kategoria
  from t
  )
  select rn, OD, AL, UT
  from cte
    pivot (max(Item) for Kategoria in (OD, AL, UT) ) p;

results:

+----+------+------+------+
| rn |  OD  |  AL  |  UT  |
+----+------+------+------+
|  1 | 1111 | 2222 | 1234 |
|  2 | 1122 | 5555 | 6666 |
|  3 | 4321 | NULL | NULL |
+----+------+------+------+

dynamic pivot example:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct
      ',' + quotename(Kategoria)
      from t
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'')
select  @sql = '
 select rn, ' + @cols + '
  from  (
    select
        Kategoria
      , Item
      , rn=row_number() over (
          partition by Kategoria
          order by     Item
          )
      from t
      ) as a
 pivot (max([Item]) for [Kategoria] in (' + @cols + ') ) p'
 select @sql
 exec(@sql);

generated sql:

 select rn, [AL],[AX],[OD],[UT]
  from  (
    select
        Kategoria
      , Item
      , rn=row_number() over (
          partition by Kategoria
          order by     Item
          )
      from t
      ) as a
 pivot (max([Item]) for [Kategoria] in ([AL],[AX],[OD],[UT]) ) p

dynamic sql result:

+----+------+-------+------+------+
| rn |  AL  |  AX   |  OD  |  UT  |
+----+------+-------+------+------+
|  1 | 2222 | 65577 | 1111 | 1234 |
|  2 | 5555 | NULL  | 1122 | 6666 |
|  3 | NULL | NULL  | 4321 | NULL |
+----+------+-------+------+------+


RHEL 6: postmaster dead but pid file exists I am using PostgreSQL 9.2 on RedHat 6

I'm a little confused as to why the following is happening:

When postgres is running, performing "service postgresql92-postgresql status" as root results in "postmaster (pid 1379) is running..." (as expected)
As postgres run "pg_ctl stop -D $PGDATA -m s" to stop postgres
As postgres run "pg_ctl -D $PGDATA start" to start postgres (postgres is now running correctly)
Now as root run "service postgresql92-postgresql status", output is now "postmaster dead but pid file exists"

My confusion is why am I getting the message in step 4 when postgres is running correctly? Am I stopping/starting postgres incorrectly?

service postgresql92-postgresql status checks if the file postmaster.5432.pid instead of postmaster.pid.

I'd recommend to use only pg_ctl as postgres and check with ps aux as root.
Or use only service postgresql92-postgresql start/stop/status as root. Mixing the two methods would turn into conflicts.


Selecting dates that are between the previous 12 to 9 month range I need help selecting dates that are between the previous 12 to 9 month range.  As an example if today is 2-22-2017 I need to pull a date range from 2-1-2016 to 3-31-2016.

I can select all dates from the previous 12 months using this:

WHERE READDATE >= DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH,-12,GETDATE())), 0)
AND READDATE <= DATEADD(s,-12,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0))

But I can't figure out how to exclude dates after the previous 9 months giving me that 3 months window.

This should work:

...
WHERE
    -- >= 2016-02-01 00:00:00.000
    READDATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 12, 0)
    -- < 2016-04-01 00:00:00.000
    READDATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 10, 0)
...

Please note that with dates it is usually best to compare it this way:

X >= lower bound
    AND
X < upper bound

This is why I change <= '20160331' to < '20160401'. While it works fine with your sample using what seems to be DATE types, it may give incorrect results using dates with a TIME part.


Using indexes in ORDER BY MySQL Here i have table with indexes and more than 700000 entries.

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `cat_id` int(11) NOT NULL,
  `user_id` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `primary` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_unique` (`id`),
  KEY `user_id_index` (`user_id`),
  KEY `created_index` (`created`),
  KEY `cat_id_index` (`cat_id`),
  KEY `updated_index` (`updated`),
  KEY `visible_index` (`visible`),
  KEY `catid_visible_index` (`cat_id`,`visible`),
  KEY `primary_index` (`primary`),
  KEY `topics_forumid_visible_sticky` (`cat_id`,`visible`,`primary`),
  FULLTEXT KEY `topics_title_fulltext` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I need to do 2 ORDER BY statement. If i use any order by - query runs too long

 SELECT
    `t1`.*
FROM
    `t1`
WHERE
    `t1`.`cat_id` = 24
        AND `t1`.`visible` = 1
ORDER BY `t1`.`primary` DESC , `t1`.`updated` DESC
LIMIT 20

EXPLAIN ordered gives
Extra 'Using index condition; Using where; Using filesort'
Key 'catid_index'

EXPLAIN unordered gives
Key 'catid_index,visible_index'
Extra 'Using intersect(catid_index,visible_index); Using where'

The "best" index for this query seems to be (cat_id, visible, primary, updated). Thanks to jkavalik


procedure is deleting data instead of inserting I have a procedure as the following:

create procedure MoveDailyData
    @date1 bigint
    as
    declare @datenow bigint
    set @datenow = datediff(S,'01/01/1970',getdate())
    if ((@datenow)-@date1)>=86400
    begin
     insert into FinalLogs(problem,channel_number,username,app_id,Is_On,logtype,viewing_channel,playtime,macaddress,wifimacaddress,seen,version,date)
     select problem,channel_number,username,app_id,Is_On,logtype,viewing_channel,playtime,macaddress, wifimacaddress,seen,version,date from DayTempTable
     delete from DayTempTable where (@datenow-@date1)>=86400
     end
     else select 'Not Yet';

The procedure should copy the data from 1 temporary table to the final one (in a daily schedule ) and after that delete that data from the temporary table. Instead, I have been noticing that every couple of days, the data does not get copied, but only deleted from the temporary table, and so I have lost a great amount of relevant data.
the date column is a bigint (dates are saved as unix time). Is there something that I am doing wrong in this procedure?

There are several problems here:

No explicit BEGIN TRAN / COMMIT / ROLLBACK around the INSERT and DELETE to group them as an atomic operation.
No TRY / CATCH structure to manage COMMIT vs ROLLBACK.
Why are you using UNIX time instead of actual DATETIME values?
Why no WHERE condition on the SELECT that feeds the INSERT?
But most directly related to the stated question: the entire DayTempTable table gets deleted because the WHERE condition of the DELETE statement is a simple boolean expression and does not reference any field of that table. Meaning, if (@datenow-@date1)>=86400 evaluates to true, then all rows will be deleted. Else, no rows will be deleted. If you want a subset of rows to be deleted, you need to actually filter the table ;-).
An alternate approach to the INSERT then DELETE within an explicit Transaction is to handle both operations at the same time via the OUTPUT clause:

DELETE tbl
FROM   DayTempTable tbl
OUTPUT DELETED.problem, DELETED.channel_number, DELETED.username, DELETED.app_id,
       DELETED.Is_On, DELETED.logtype, DELETED.viewing_channel, DELETED.playtime,
       DELETED.macaddress, DELETED.wifimacaddress, DELETED.seen, DELETED.version,
       DELETED.date
INTO   FinalLogs (problem, channel_number, username, app_id, Is_On, logtype,
                  viewing_channel, playtime, macaddress, wifimacaddress, seen,
                  version, date)
WHERE  (@datenow - tbl.[date_field]) >= 86400;

And then you don't need the explicit Transaction (i.e. BEGIN TRAN / COMMIT / ROLLBACK) since it is handled automatically. But you should still do the TRY / CATCH.

Even better would be to change the WHERE condition to be a simple, sargable expression:

WHERE  tbl.[date_field] <= (@datenow - 86400);

Also, I wouldn't use the term "temporary table" to refer to the DayTempTable table since it is not, in fact, a temporary table (which is denoted by having either a single or double pound sign # as the leading character(s) of the table name).


Looking for an efficient way to fix "Could not parse relay log event entry..." error I have a corrupt relay log in MySQL 5.0 and looking for the steps to fix replication without having to reprocess all the binary logs from the master again.

Here is the full error message if you are curious:
Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

The normal fix is quite easy, you just obtain the correct binary log name and position from show slave status and run the change master command. But this is not what I would like to do.

I would like to reprocess only one or few binary logs from the master to recover the corrupted relay log and then continue to use the relay logs that were already created. I am looking for someone who had past experience doing this as there is a potential for failures doing the recovery this way.

Conceptually, I am thinking the following steps might be needed (maybe some of them are optional):

Stop replication on the slave.
Shutdown slave instance.
Move relay logs to a safe location.
See if relay-log.info or master.info files need to be updated manually.
Start slave instance.
Run change master command and reprocess binary logs from the failed position.
Wait until next relay log is created.
Stop replication on the slave.
Shutdown slave instance.
Figure out how to replace 2nd relay log file with relay logs files that are in the safe location and move them in.
See if relay-log.info or master.info files need to be updated manually.
Start slave instance.
Hope for the best.

I am thinking of an alternate method to do it, which seems much simpler:

Stop replication on the slave.
Shutdown slave instance.
Move relay logs to a safe location.
Start slave instance.
Run change master command and reprocess binary logs from the failed position.
Wait until next relay log is created.
Stop replication on the slave.
Use mysqlbinlog to process the relay logs that are in the safe location by piping to mysql client. Identify which file and position to start at. (Potential for issues, unless aborting processing upon first error).
Set the slave to replicate from master at the read master log file/position.

There is more stable approach you can try

Here is something to remember

Whenever you run CHANGE MASTER TO, it will erase every relay log you have. You do not want to keep relay logs of commands you have not executed any SQL on as of yet

The following is an excerpt taken from a post I made back on Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication :

  Please notice that there are two sets of replication coordinates from
  the Master

  (Master_Log_File,Read_Master_Log_Pos)
  (Relay_Master_Log_File,Exec_Master_Log_Pos)

  There is a major difference between them

  (Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave
  read from the Master and placed in its Relay Logs.
  (Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the
  Slave read from the Master and placed in its Relay Logs THAT IS NEXT
  TO BE EXECUTED ON THE SLAVE.

What you want are two things:

Erase Every Binary Log You Have
Start Collecting Binary Log Entries From the Last SQL You Successfully Executed.

In your case, you must use the second set of Replication Coordinates

Relay_Master_Log_File
Exec_Master_Log_Pos

It is easy to distrust a corrupt relay log as shown in the error message. The one that hurts the most is a corrupt Master Log. You will have to jump through hoops if that is the case. On the other hand, if one of the other situations was the reason for the corrupt relay log, the simplest and most concise approach is what I stated.

To make sure, whatever is reported for Relay_Master_Log_File, if that particular binary log still exists on the Master, perform a mysqlbinlog on it. If it dumps in its entirety without corrupt characters, go ahead and use the second set of replication coordinates.

From my same earlier post

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

notice that the Replication Coordinates from SHOW SLAVE STATUS\G for what was last executed are (mysql-bin.000254,858190247). The CHANGE MASTER TO command in this case would be:

CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;

Give it a Try !!!

UPDATE 2012-09-14 16:38 EDT

If you worried about the stockpiling relay logs, just throttle the relay logs. In SHOW SLAVE STATUS\G, there is a field called Relay_Log_Space. That gives you the sum of all relay sizes in bytes. Did you know you could put a cap on that number ?

The option is called relay_log_space_limit.

For example, if you want to cap the total number of bytes to 10G, do the following

STEP 01) Add this to /etc/my.cnf on the Slave

[mysqld]
relay_log_space_limit = 10G

STEP 02) Run service mysql restart on the Slave

and that's it !!!

When the oldest relay has all its entries processed, it is deleted and a new relay log is created. That gets filled until all relay logs add up to 10G. That's the only way to control runaway relay log space issues.

UPDATE 2012-09-14 18:10 EDT

SUGGESTION : If you make mysqldump backups of the data on the Slave every midnight, you could set up the following to restrict having 1TB of binary logs:

STEP 01) Add this to /etc/my.cnf on the Master

[mysqld]
expire_logs_days = 14

STEP 02) Run this query on the Master

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 14 DAY;

STEP 03) service mysql restart on the Master

STEP 04) Add a mysqldump backup script to a crontab on the Slave

This will make the Slave more useful and would control having excess binary logs to worry about


SQL Server 2017 login language display is wrong I have just done a clean install of SQL Server 2017 (CU15) on two of my servers, and have noticed that the displayed language for all my logins shows ARABIC instead of '' or 'English'.

Here is the server settings

Here is a login setting

When I run the following SQL code, I get 'us_english' for the default_language_name:

SELECT type_desc,
        default_database_name,
        default_language_name,
FROM master.sys.server_principals
WHERE name = 'Cerberus'

| type_desc        | default_database_name    | default_language_name |
+------------------+--------------------------+-----------------------+
| WINDOWS_LOGIN    | master                   | us_english            |

Did I mess something up on installation?  How do I correct this?

This bug has been fixed as of SSMS version 18.3.


How do I create a table with count of instances of an id before an event date that is individual to that id? I am very new to SQL and have a relatively complex query to write, any help would be appreciated.

I have data with the following columns:

ORDER_ID
CUST_ID
ORDER_DATE
RETURNED(1/0)

I need a table with the following three columns:

CUST_ID
A column with the count of the number of orders a specific customer had before their FIRST returned order.
A column with the count of number of orders a specific customer had after their FIRST returned order.

Each order has a unique id as does each customer, but there are many customers who had multiple orders at different dates. Not all customers had returned orders and I just need the counts for the customers who had a returned order (RETURNED=1).

Would something like this do?

with only_returned as
  (select cust_id,
          min(order_date) min_order_date
   from your_table
   where returned = 1
   group by cust_id
  )
select r.cust_id,
       count(b.order_id) returned_before,
       count(a.order_id) returned_after
from only_returned r
  join your_table b on b.cust_id = r.cust_id
                   and b.order_date < r.min_order_date
  join your_table a on a.cust_id = r.cust_id
                   and a.order_date > r.min_order_date;

ONLY_RETURNED CTE contains customers whose orders were returned; it also selects the first return order date
then, join only_returned with your_table, twice:

once for orders placed before minimum return order date (the b alias)
once for orders placed after minimum return order date (the a alias)


LIMIT on table with 1 to many I have 3 tables (A, B and C) that connect 1:m:1.  I need to JOIN the 3 tables and apply a limit not on the total number of records, but on the number of records used from table A.  This means that more records in total than the limit will be returned.
Here is a fiddle.  This uses MySQL of:
SELECT A.id as Aid, A.name, C.id AS Cid, C.item
FROM A
INNER JOIN B b ON A.id = B.Aid
INNER JOIN C c ON B.Cid = C.id
WHERE A.owner = 1
ORDER BY A.id DESC
LIMIT 3

I would like to limit the number of records used from table A to 3, even though that means that 4 records will result (the ones connected to Aid's of 4, 3 and 2) because there are multiple records in table C that relate to those 3 records in table A.
I am wondering if perhaps this needs to use a SELECT (that has a LIMIT of 3) inside another SELECT that joins to tables B and C, but I don't know how to do this.

Like:
SELECT Al.id AS Aid,
       Al.name,
       C.id AS Cid,
       C.item
FROM
  (SELECT A.id,
          A.name
   FROM A
   WHERE A.owner = 1
   ORDER BY A.id DESC
   LIMIT 3) AS Al
INNER JOIN B b ON Al.id = B.Aid
INNER JOIN C c ON B.Cid = C.id

fiddle


Is there a way to hint to query optimizer to MySQL which constraints should be done first? This is my current query:

SELECT BusinessID as ID,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
    SELECT *
    FROM
      tableauxiliary
    WHERE
      MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1
    AND Prominent >15
) AS TA

    Having Distance <= 18238
    ORDER BY
  Distance
LIMIT
  0, 45

Notice that they I used subquery. The reason why it uses subquery is because I want

      MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1

to be done first. This reduces query time from 19 seconds to .9 seconds.

Is there a way to hint to mysql query optimizer so that I do not need to use subquery

Update:

I tried:

SELECT BusinessID as ID,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
USE Index (LatLong_2,FullTextSearch)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ORDER BY
  Distance

Nothing change. If I use explain FullTextSearch is the only index used.

Note: The question is indeed about speed. What happen is I am sure that the query will run faster if mysql optimizer do spatial first before the fulltextsearch. This is because sometimes we are searching for a* in fulltext search that obviously don't limit by much.

Basically I have businesses database for the whole world and I am only interested in businesses within 500 meter square or 10km square at most. So obviously the spatial part will be far more restrictive.

While I am moving to mongodb, it'll be kind of nice if things work too for mysql.

I have rather bad news for you in this regard
MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange

May 23, 2011 : https://stackoverflow.com/a/6092216/491757
Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

You will have refactor the query
My suggestion would be to do the one of the following
SUGGESTION #1

Retrieve Business IDs suing the FULLTEXT Search
Perform GeoSpatial Search on the Retrieved IDs

SUGGESTION #2

Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds)
Perform the FULLTEXT Search on the GeoSpatial-Retrieved Businesses IDs

SUGGESTION #3

Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds) in Subquery1
Retrieve Business IDs using FULLTEXT Search in Subquery2
INNER JOIN Subquery1 and Subquery2

SUGGESTION #4

Retrieve Business IDs using GeoSpatial Search (Since you said it take 0.9 seconds)
DO NOT USE FullTextSerach. Use REGEXP against GeoSpatial-Retrieved Businesses IDs

Here is your original query:
SELECT BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
from tableauxiliary
USE Index (LatLong_2,FullTextSearch)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ORDER BY
  Distance;

If the MySQL Query Optimizer detects a FULLTEXT search along with the conjunction AND followed by an expression within the same WHERE clause (thus, the same level of evaluation within an EXPLAIN plan), the MySQL Query Optimizer will take a vacation after working with the FULLTEXT search.
Perhaps you could try something like this (from SUGGESTION #1):
SELECT BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID,LatLong
    FROM
    (
        SELECT BusinessID,Prominent,LatLong
        From tableauxiliary WHERE
        MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ) AA
    WHERE Prominent > 15
) A
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
ORDER BY
    Distance
;

Notice you do not see the conjunction AND. Please check the EXPLAIN plan for this. The query should do the following:

Gather BusinessID,Prominent,LatLong That Matched the FULLTEXT search
Remove BusinessID,LatLong Whose Prominent value is < 15
Check Distance of remaining BusinessID values
sort the Check Distance results by Distance

UPDATE 2012-08-08 11:30 EDT
As stated earlier, the FULLTEXT index seems to abandon all hope of using other indexes. Perhaps two subqueries that are JOINed (SUGGESTION #3)
SELECT BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+
    pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID,Prominent From tableauxiliary WHERE
    MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
) A
INNER JOIN
(
    SELECT BusinessID From tableauxiliary WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
) B
USING (BusinessID)
WHERE Prominent > 15
ORDER BY
    Distance
;

You may want to abandon using FULLTEXT searching altogether and try regular expressions (SUGGESTION #4)
SELECT B.BusinessID as ID,
    111151.29341326*SQRT(pow(-6.186751-X(B.LatLong),2)+
    pow(106.772835-Y(B.LatLong),2)*0.98838574205337) AS Distance
FROM
(
    SELECT BusinessID From tableauxiliary WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
) A INNER JOIN tableauxiliary B USING (BusinessID)
WHERE B.Prominent > 15 AND
((B.FullTextSearch REGEXP ' a*') OR (B.FullTextSearch REGEXP '^a*'))
ORDER BY
    Distance
;


Can I have Oracle primary 11.2.0.1 and standby 11.2.0.3? Can I have data guard system like:

Primary: 11.2.0.1
Standby: 11.2.0.3

In logical standby ?

In physical standby versions(releases) must be the same?

The physical standby database must be physically the same as the primary database and this means the versions must be identical.

A logical standby database can be any version, depending on how it is implemented. With 3rd party tools you can go from v10 to v12 and back. The restrictions here are on the data types that are to be replicated.

If this is for an upgrade, you start with a physical standby database, convert that to a logical standby for the upgrade.


Mysqldump tables excluding some fields Is there a way to mysqldump a table without some fields?

Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of table USER, so excluding some fields like mail, ip_login, etc.

How can I do this?

Try this:

SELECT column1, column2, column10
FROM USER
INTO OUTFILE "c:/user.csv"
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY "\n";

For more option read the documentation page.


Database Engine Tuning Advisor I analysed a huge query in DTA. The database is kind of badly designed and does not have primary keys on tables, also they dont have clustered indexes on them. I found around 80 tables as such. Now, without indexes the queries are obviously going to run slow but when I put them through the DTA, it always asked me create a non clustered index (even on heaps). Something like

CREATE NONCLUSTERED INDEX [_dta_index_IO_Status_MAster_10_1899153811__K2_4_5_1912]
  ON [Scorecard].[IO_Status_MAster]
  (
    [WBS] ASC
  )
  INCLUDE ([OrderNumber], [IO_STATUS])
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

My question, does DTA works on the premise that table has a clustered index on it? Why does it not advise to create a Clustered Index?

By default, the DTA doesn't recommend clustered indexes. In addition, check out the documentation for even more things it doesn't do.

If you do want recommendations for clustered indexes, click the radio button for "Do not keep any existing PDS" as shown in the below screenshot:

But be aware that it can produce some stunningly bad clustered index recommendations. The DTA isn't a replacement for basic data modeling for how to store your data.

Generally, your clustered indexes should follow the SUN-E method:

Static - fields that don't change, so you don't have to update your nonclustered indexes
Unique - so SQL Server doesn't have to add a uniqueifier behind the scenes
Narrow - so lots of data isn't copied into your nonclustered indexes
Ever-increasing - creating a hotspot at the end, guaranteeing that where you're inserting will usually be in memory (although this is one rule that folks violate on purpose during extremely heavy concurrency, like tens of thousands of inserts per second)


MySql command line cyrillic Iinsert result is corrupted Have putty connection.
Putty config : window-->translation-->remote character set-->utf-8
Launch command line MySQL client.
Perform insert  with cyrillic like:

CREATE TABLE `category` (
  `catName` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`catName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into category(catName) values ('абвгдйка');

The result is that corrupted cyrillic symbols inserted into DB.

By default MySQL server is using latin1 character set for each incoming connection. Latin1, as you might know, does not support cyrillic symbols.

The simplest solution is to switch so called 'connection character set' by running SET NAMES 'utf8'; in the beginning of each connection.

For example, this query should work:

SET NAMES 'utf8';

CREATE TABLE `category` (
  `catName` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`catName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into category(catName) values ('привет русско-говорящему серверу');

To learn about character sets in MySQL and how to make the above change permament see here.


Rewriting a query and eliminating subqueries I have the following query :

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs,
 num_bases_in_singletons
FROM stats_assembly WHERE ws_id IN (
  SELECT workflow_steps.id
  FROM workflow_steps, workflows
  WHERE workflow_steps.workflow_id = workflows.id
      AND workflows.sample_id = (
            SELECT workflows.sample_id FROM workflows WHERE workflows.id = (
                SELECT workflow_id FROM workflow_steps WHERE id = 462
            )
            AND workflow_steps.submodule_id = 2
      )
)

And I want to rewrite it without using subqueries. So far I have been able to do only this:

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs,
  num_bases_in_singletons
FROM stats_assembly
INNER JOIN workflow_steps ON ( stats_assembly.ws_id = workflow_steps.id )
INNER JOIN workflows ON ( workflow_steps.workflow_id = workflows.id )

And I have no idea how to finish it.

Furthermore, this query potentially returns multiple rows but I want to get the row which has MAX(ws_id). Any tips will be greatly appreciated.

I think this will get you there... cavaet: I'm a SQL Server person...

SELECT
  ws_id
, n50
, num_singletons
, num_segments_less_100
, kmer
, num_all_reads
, number_bases_in_contigs
, num_bases_in_singletons
FROM
 stats_assembly sa
 join workflow_steps ws1
   on sa.ws_id = ws1.id
   and ws1.submodule_id = 2
 join workflows wf1
   on ws1.workflow_id = wf1.id
 join workflows wf2
   on wf1.sample_id = wf2.sample_id
 join workflow_steps ws2
   on wf2.id = ws2.workflow_id
   and ws2.id = 462


get table and field names from union in execution plan I'm trying to find performance issues on query which is based on other queries etc.

I found that I have a union query which is slow, but I have no idea which union query/column it is.

All I get in the execution plan are nodes like:

 <SeekPredicates>
                                              <SeekPredicateNew>
                                                <SeekKeys>
                                                  <Prefix ScanType="EQ">
                                                    <RangeColumns>
                                                      <ColumnReference Database="[mydb]" Schema="[dbo]" Table="[Sales]" Column="SaleID" />
                                                    </RangeColumns>
                                                    <RangeExpressions>
                                                      <ScalarOperator ScalarString="[Union1399]">
                                                        <Identifier>
                                                          <ColumnReference Column="Union1399" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </RangeExpressions>
                                                  </Prefix>
                                                </SeekKeys>
                                              </SeekPredicateNew>
                                            </SeekPredicates>

How can I know which fields/tables make up this "union1399"? So can index it or remove it or otherwise optimize the query.

Thanks!

The answer lies in the system table/views.

  Always refer to the official documentation from MSDN first, and
  search Dave Pinal second, as you will likely find these types of
  questions answered. ~ Helpful Hint #3 ;)

The system table you want is through sys.dm_exec_query_plan

This function stores the execution plans of the caches plans

And you can acquire the needed sql_plan varbinary value from one of three places:

sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests

An example is like the following:

SELECT qt.text, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE 'SELECT %'

I left the predicate in this example, but you can find different methods to achieve the same goal.

Anyways, the XML column query_plan will have all of the tables, columns, and even the values of any variables available in that plan. If you click on it, you get shown the visual query plan, but copy and pasting the field will have the actual XML plan used in the query.

Cheers.


How to create a "match result" data type? I'm building a football match table which contains one column "match result",
which should consist of data like "3-1","0-0"... I'm wondering what kind of data type is best appropriate for it, varchar or else?
I'm thinking of creating a new data type for this column, using CREATE TYPE statement, whose form is like "integer"+"-"+"integer",
is it possible?

Maybe erical already has two columns that keep the "home score and away score as two separate int fields", as Mark and Phil already commented. Then, the remaining part of the question would be whether it is possible to create a user-defined type for a separate string column that stores the match result for faster printing.

So, the answer in this situation would be that yes, it is possible. If unsure about the syntax of the CREATE TYPE statement, it is documented at the following URL:

https://msdn.microsoft.com/en-us/library/ms175007.aspx

The following would be an example of how to do it:

CREATE TYPE MatchType
FROM [VARCHAR](5);

DECLARE @Draw [MatchType];

SET @Draw = '00-00';

DECLARE @Final [MatchType];

SET @Final = '02-01';

SELECT   @Draw  AS [Draw Match]
       , @Final AS [Final Match Sample];

-- Draw Match   Final Match Sample
-- 00-00        02-01

Or, maybe erical was thinking about a composite user defined type. Instead of creating an alias based on an already existing type, what if it were possible to mix two already existing types into a brand new compound?

Microsoft SQL Server does not support that. Instead, Oracle comes with a RECORD type. An example would be the following:

-- Define a user type to hold (HomeScore, AwayScore)
TYPE MatchType IS RECORD
(  HomeScore VARCHAR(2)
 , AwayScore VARCHAR(2));


How do I manage column order when using PL/SQL's %ROWTYPE records? I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:

DECLARE
  TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
  l_data t_toptab_list;
BEGIN
  SELECT ... -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_data
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.

  -- EDIT:
  FORALL i IN 1..l_data.count
    INSERT INTO toptab VALUES l_data(i);
END;

In order for the BULK COLLECT to work properly, I have to list the columns in the SELECT clause in exactly the same order as they are declared in the toptab table. So the problem is that this code would be run in multiple environments. How do I either: (a) guarantee that all environments have the exact same column ordering, or (b) explicitly specify which columns go to which record elements but still use a bulk bind?

I understand that (a) could be done through strict schema management procedures. But let's say that's not an option here. In fact the different schemas may already have different column orderings. Can I reorder the columns without rebuilding the entire table? (Prohibitive.)

I know that (b) can be accomplished without the BULK COLLECT by explicitly selecting into each record element, but I really want to do it in bulk.

Any ideas?

Edit: I want to bulk insert into toptab with the data I've collected. I separate these two steps because I need to batch the inserts into smaller transactions. (We use streams and find smaller transactions keep things moving smoother. However, batching each transaction is still much faster than doing N individual inserts.)

If the definition of toptab will potentially be different in different environments (which is, of course, a separate issue that needs addressing), your options would generally be to either create an object type that does have a defined order and BULK COLLECT into that or to define a different collection for each column that you specify as the targets of your BULK COLLECT.

For the object approach, you'd define an object

CREATE TYPE toptab_like_object
    AS OBJECT (
  col1 number,
  col2 number,
  ...
  colN number
 );

and then use that object rather than the %ROWTYPE in your code

DECLARE
  TYPE t_toptab_list IS TABLE OF toptab_like_object;
  l_data t_toptab_list;
BEGIN
  SELECT toptab_like_object( ... ) -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_data
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.
END;

You could also define a different collection for each column (you could probably define a much smaller number of collection types)

DECLARE
  TYPE t_col1_list IS TABLE OF toptab.col1%TYPE;
  TYPE t_col2_list IS TABLE OF toptab.col2%TYPE;
  ...
  TYPE t_colN_list IS TABLE OF toptab.colN%TYPE;

  l_col1 t_col1_list;
  l_col2 t_col2_list;
  ...
  l_colN t_colN_list;
BEGIN
  SELECT ... -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_col1, l_col2, ... l_colN
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.
END;


Select rows and add their previous rows I have a table structure that holds historical data for another table. Something like

id      | name        | date
----------------------------------------
1       | John        | 2016-01-01 14:38.123123
1       | John Smith  | 2016-01-03 16:46.123123
2       | ...         | 2016-01-01 14:38.123123
3       | ...         | 2016-01-01 14:38.123123

I want to make a query that gets each row, and for each row, also get the previous row (latest date before the rows date) with the same id, e.g. something like

SELECT id, name, date, previous.id, previous.name, previous.date
FROM ..?

Is this possible? This is a pretty general question, but in my specific case, I am using PostgreSQL, so if there is some postgres features that accomplishes this, that is fine.

This is exactly what the LAG() window function does, available since version 8.4:

SELECT id, name, date,
       LAG(name) OVER (PARTITION BY id ORDER BY date) AS previous_name,
       LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date
FROM table_name ;

There is also the option of using a LATERAL join (available from 9.3). Might be better if you want all the columns (of the previous row). This assumes that there is a UNIQUE constraint on (id, date):

SELECT t.id, t.name, t.date,
       p.name AS previous_name, p.date AS previous_date
FROM table_name AS t
  LEFT JOIN LATERAL
    ( SELECT p.*
      FROM table_name AS p
      WHERE p.id = t.id
        AND p.date < t.date
      ORDER BY p.date DESC
      LIMIT 1
    ) AS p ON TRUE ;

An index on (id, date) would be helpful for both queries.


MySQL Using AND Statement I am having issues with this question:

  Find the cities that have direct (non-stop) flights to both Honolulu and Newark.

The answer should be Dallas, since its the only location that has destinations to both Honolulu and Newark. I'm trying to use the AND statement but it wont work.

SELECT FLIGHTS.source_city
FROM FLIGHTS
WHERE (FLIGHTS.destination_city = 'Honolulu') and (FLIGHTS.destination_city = 'Newark')

I am using MySQL.

FLIGHTS.destination_citycannot be the same value at the same time, therefore a join of the same table for each destination reference is required like:

SELECT f1.source_city
FROM FLIGHTS f1
JOIN FLIGHTS f2 ON f1.source_city = f2.source_city
WHERE f1.destination_city = 'Honolulu'
  AND f2.destination_city = 'Newark'
 ;


Are there any tables that I can get all Oracle supported system privileges and object privileges? Are there any tables that I can get all Oracle supported system privileges and object privileges? I know there is a view called all_sys_privs in Oracle. But I can't find more information about it.

Thanks.

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


PostgreSQL : Master-Slave replication strategy and solution for 3 servers. We have currently 3 servers(X,Y,Z) running Debian X64, two of them host projects(Y,Z), which don't require that much database, as they serve more static content, but the third server(X) is largely dependent on database and is a critical component for the project.

All the three servers use PostgreSQL. For this reason, I was thinking of creating a clustering setup, where server X would act like a master, and Y,Z as the slaves.

What I am looking for is, create a setup, where the database from server X is the master. But as the Y,Z host projects as well, the changes done to it, need to be synchronized back to server X.

I presume this technology is called replication & clustering? How can I proceed ahead with this problem for PostgreSQL.

If anyone can help me outline a strategy for this, and how can I proceed ahead. Any help would be nice. Thank you. If you feel, that my understanding of replication and/or clustering is wrong, please feel free to correct me.

You probably need 5+ servers for "real" high availability:

2x pgpool-II proxies (they send writes to master and reads to anyone)

3x postgresql servers (master, slave, witness)

Writes to master get sent to a slave via streaming replication

Use something like repmgr to do automatic failover

You're going to want to read this thoroughly: http://www.postgresql.org/docs/current/static/high-availability.html


Identity column in columnstore index I have an extremely large table IMO (~137 million rows) with lots of repeated data, lots of NULL columns, and such.

I am considering exploring this using a table with a COLUMNSTORE INDEX and I have an IDENTITY column in the original table, which is my only column where every row is unique.

Should I leave this column out or include it?  I have read that you want to include all rows of your table into the COLUMNSTORE INDEX but I have also read that the best candidates are columns with lots of non-unique rows.

Is this just a bad candidate for a COLUMNSTORE INDEX?

I am using SQL Server 2012, so it is a nonclustered columnstore. I am just exploring possible better ways to store this data. Updates are nonexistent, although new rows will periodically be added through an ELT process so I am assuming some work would be done there. Some folks mine this data and generate huge reports, lots of scanning of rows, brings the server to a crawl at times which has forced us to offload a copy daily to a secondary server.

Identity columns are not truly compressed in Columnstore Indexes in SQL Server 2012 or in SQL Server 2014. It will all truly depend on the workload that you are experiencing. If your workload will include the identity column, then you can very beautifully take advantage of Segment elimination.

From the compression point of view - Columnstore will provide you with better compression than page typically does. Typically. Please test it before advancing to production.

Your biggest problem in SQL Server 2012 will be a very weak implementation of the Batch Mode, and there is nothing you can do about it.


sqlcmd to run large SQL file causes high CPU usage I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. This does not look normal to me.

Using SQL Server Management Studio to run the script is not an option, since it won't run very large files.

Note: I know the SQL script is good, since I was able to run it on my local machine with another tool (xSQL data). Unfortunately, I don't have access to that tool on the server.

If the sql script is 156 MB I suppose that you're doing data load using a single statement for each row.

Is that everything in one big batch (all statements one after the other without GO)? If yes, then SQL Server will try to parse that file all at once... on a file that big you're killing the CPU.

Suggestions:

can you split that file in more than one batch? so the parsing will be done batch after batch..
can you load the data from bcp files instead of sql file?


In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account We use Postgres 9.3, we want to split a large database (evaluated final size will be > 100 T) on many servers. As our users already have a set of existing queries, we cannot use solutions like PL/Proxy.  We would like to use the postgres_fdw contrib to externalize some part of the data.

To illustrate our problem, we created 2 foreign tables foreign_table_1 and foreign_table_2. As inheritance seems not available on foreign tables, we created an union view like this :

CREATE VIEW union_table (id, name)
  AS      ( SELECT id, name FROM foreign_table_1 where id BETWEEN 1 AND 100000
  UNION ALL SELECT id, name FROM foreign_table_2 where id > 100000
  ) ;

The problem is that Postgres does not push the constraints into the query plan. For instance here both foreign tables are scanned :

postgres=# explain analyze select * from union_table where id = 1234;
 Append  (cost=100.00..303.35 rows=5 width=8) (actual time=1.113..1.113 rows=0 loops=1)
   ->  Foreign Scan on master_table_1  (cost=100.00..154.82 rows=1 width=8) (actual time=0.567..0.567 rows=0 loops=1)
   ->  Foreign Scan on master_table_2  (cost=100.00..148.48 rows=4 width=8) (actual time=0.538..0.538 rows=0 loops=1)
 Total runtime: 1.876 ms
(4 rows)

Is it possible to push the WHERE clause into the query plan ?
Is it possible to use inheritance with postgres_fdw ?
Is there any other way to obtain a sharded table with Postgres ?

If the view isn't pushing down the WHERE predicates, maybe a db function that dynamically creates the necessary query and only included the Foreign Tables (or shards) that are necessary?

If you need distributed Postgres, have you looked into Postgres-XC? http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki


Eclipse data source explorer not showing Inserted table records (Oracle) After I installed Oracle database 11g, I went to Eclipse Database Development Perspective and successfully created a new database connection for Oracle.

Then I created a new account call "testing" and logged in with this new user "testing" through SQL Plus and created a new table.

Enter user-name: testing
Enter password:

SQL> CREATE TABLE TBLSAMPLE (NAME varchar(50), EMAIL varchar(50), AGE integer);
Table created.

When refresh the Schema in Eclipse's Data source explorer. The table added from SQL Plus shows up. (All is working well so far)

I proceeded to insert some records from SQL Plus with the same user ("testing").
A check on the table shows that the records were indeed inserted.

SQL> SELECT * from tblSample;
...
6 rows selected.

Now the problem is: The inserted records are not showing up in Eclipse's Data Source Explorer despite refreshing the schema & the tables folder.

I created a new SQL file and select the entire tblSample, and it shows 0 records as well. I then inserted 1 row of record from Eclipse SQL file. It does shows up in Eclipse, but it does not affect the 6 rows of records in Oracle SQL Plus.

Why are the inserted records not showing up in Eclipse? To troubleshoot the culprit, I created a NetBeans project using the same Oracle database and all 6 rows of records does show up in NetBeans.

So what could have gone wrong?

Modifications performed by a session are not visible to others until they are commited. Issue a commit after performing these changes.


Can't map invalid columns based on column names in a .dtsx-Package I have the following problem. I currently have to modify a .dtsx-Package which was originally created by a coworker who is not arounf anymore. This package contains some logic but above all it does the following:

The Package contains some reference to xsd-files which provide a structure.
Some XML-files are fetched and read based on the structure provided with the xsd-files.
The process is supposed to write to several ADO destinations witch hold tables according to the data in the xsd-files.
After the package retrieves and reads the xsd + xml files a merge join is applied and the data is supposed to be written to the database.

The whole thing looks something like this:

So the rest of the package is huge and it works fine. But for this one component I have had trouble for several days now. Here everything works fine, except for the Merge-Join node. normally if I open the editor I am always able to map the column names according to their names. In this case this very same window looks as follows:

As you can see there are no mapping possible as this dialog doesn't show any column names, so every time I try it nothing happens.

Any ideas? Note the very same procedure works throughout the entire package, it is only in this one task where it behaves kinda strange.

Something has happened such that the source column doesn't exist any more and the mapping is broken. In the bottom drop down, "spaltzenzuodnungsoption..." there should be 3? options. You want the one that says delete. This will remove all the mappings so you can start over.


sync MS SQL Server 2014 and Express 2014 Database using Stored Procedure I have C# windows form application and I have same database schema on both MS SQL Server(for master user) and on SQL Express(for clients). My database size is around 2 GB for each client.

I want to synchronize this databases once in a day. In MS SQL Server 2008 we have used RMO but RMO API deprecated in SQL Server 2012.

Is there any good suggestion or example then refer it.  I am new to database operations so i don't have much idea bout this.

I fully expect down-votes on this, but deprecated doesn't mean discontinued.  When a feature is deprecated it generally means that said feature won't be expanded and will likely be removed in a future release.  According to MS documentation RMO doesn't show up under features that were discontinued in either SQL 2014 or SQL 2016, but again I wouldn't count on it being available going forward.

The point here is if you've already got something that works using RMO with SQL 2012, see if it works in 2014 and continue using it.  No point to reinvent the wheel if you don't have to.  Who knows, in 5-years-time you may no longer use this application, this back-end, or even this approach.  It's also entirely possible that RMO could turn into something similar to the sysindexes view that was deprecated way back in SQL 2005 but is still in SQL 2017 by the looks of it.

In terms of an official "supported" approach, Merge Replication, as stated by Bartosz X in the comments is your best bet, based on what it sounds like you want to accomplish.  Some additional searching on DBA.SE or Google should get you going.


Importing Oracle database from dump where source db filesystem has changed I'm migrating a database from one server to other. In the migration process  we changed the filesystem structure from :

/appli2/oradata05/HISTO/
/appli2/oradata06/HISTO/
/appli2/oraredolog01/HISTO/
/appli2/oraarch/HISTO/

to

/appli2/oracle/HISTO/oradata05
/appli2/oracle/HISTO/oradata06/HISTO/
/appli2/oracle/HISTO/oraredolog01/HISTO/
/appli2oracle/HISTO/oraarch/HISTO/

And i'm getting error according to this changes:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/******** DIRECTORY=MIG11G DUMPFILE=20131125_full_HISTO.expdp LOGFILE=impdp_dump_HISTO_r7.log parallel=4
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDO" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"TOOLS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf'
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Failing sql is:
CREATE TABLESPACE "TAB_ANC" DATAFILE '/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf' SIZE 1048576000,'/appli2/oradata05/HISTO/HISTO_tab_anc_02.dbf' SIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf'
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Failing sql is:
CREATE TABLESPACE "IDX_ANC" DATAFILE '/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf' SIZE 1048576000,'/appli2/oradata06/HISTO/HISTO_idx_anc_02.dbf' SIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM

Is it possible to import only de data in the current filesystem structure ?
I saw the remap command but i never used it, someone could put an example to use it if it was the answer to the problem.

remap_tablespace:source_tablespace_1:user,source_tablespace_2:user

To respond to your comment, all is the default value of content keyword, so you can ignore this one.

Anyway, I have done some tests.
I don't know how is structured your old database, but from your question I note that you want to import only two tablespaces (TAB_ANC, IDX_ANC) and  with all object/schemas within them. If you want to do this, you can perform this kind of import:

impdp system/passwd@HISTO directory=MIG11G dumpfile=20131125_full_HISTO.expdp  parfile=imp_histo.par

The parameter file is this one:

include=schema:"in ('SCHEMA1', 'SCHEMA2', ... , 'SCHEMAn')"
include=tablespace:"in ('TAB_ANC', 'IDX_ANC')"
remap_datafile=/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf:/appli2/oracle/HISTO/oradata05/HISTO_tab_anc_01.dbf
remap_datafile=/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf:/appli2/oracle/HISTO/oradata06/HISTO/HISTO_idx_anc_01.dbf

As you can see, you need to include all schemas contained in those tablespaces (or you can also create them from scratch in the new database). To verify these schemas you can run a query like the following:

SELECT username, default_tablespace FROM dba_users;

Remapping datafiles is useful when you move databases between platforms that have different file naming conventions.

I think you can use remap_tablespace when you have already created (manually) all the required tablespace in the target database, but with different names.


How currval of sequence being preserved during the backup and restore in postgres If I do backup and restore a huge db (tons of tables in it), will it run without any issues rather rising exception about duplicate key constraints of sequenced column(s).

While taking pg_dump (complete schema / db), how its taking the individual value of a sequence and altering the value with latest value of a table's sequenced column when we do restore?

If you look at the SQL script generated by pg_dump you will see that it generates a setval() call for each sequence exported. So if you have a sequence test_seq and the last value it generated was 42, you will see this line in the SQL script:

SELECT pg_catalog.setval('public.test_seq', 42, true);

this makes the sequence return 43, the next time nextval() is called.

See the manual for details on the setval() function.


When should nonclustered indexes be stored on separate filegroups? I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the index refers.  I have also heard that this is a myth.

When is it advisable to store nonclustered indexes on a separate filegroup and drive?  What perfmon/profiler evidence would lead me to arrive at that conclusion?  Does hardware play a role in the decision (whether a RAID/SAN is used over a single drive)?

The slowest part of a DB system is the disk drives. Eliminating bottlenecks at the disk level will improve performance. When data is being looked up and an index is used, the index is first looked up and then the corresponding data is fetched. If both the index and data are on the same disks, then there is some contention happening. Whereas, if the data were on a different (physical) disk, then there is faster IO happening, thereby increasing performance.
The main part to note is that the data or index are on separate physical disks or LUNs.

You would use such a scenario if you need to get better performance out of your system, provided you have the disks. For your perfmon counters you could use Physical Disk – Avg. Disk sec/Read, Physical Disk – Avg. Disk sec/Write, Physical Disk – Disk Reads/sec, Physical Disk – Disk Writes/sec to have a before and after comparison of your changes.


How can I reduce OLEDB wait type in a SSAS linked server connection? I have recently created a linked server to one of my SSAS servers.

when I go ahead and open the catalogs, to see which ssas databases I have there,
I use the following procedure:

create procedure sys.sp_catalogs
(
    @server_name        sysname
)
as
    select
        CATALOG_NAME    = f_rc.CATALOG_NAME,
        DESCRIPTION     = convert (nvarchar(255), f_rc.DESCRIPTION)
    from
        sys.fn_remote_catalogs (@server_name, NULL) f_rc
    order by CATALOG_NAME

this is how I call it:

sys.sp_catalogs 'sasbidev01'

When I see it takes too long, I check what it is running:

I see the OLEDB wait type.

Is this on its own, indication that I could do something to improve the speed of this connection?

The Linked server script creation:

USE [master]
GO

IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SASBIDEV01')
BEGIN

EXEC master.dbo.sp_addlinkedserver
  @server = N'SASBIDEV01'
, @srvproduct=N''
, @provider=N'MSOLAP'
, @datasrc=N'SASBIDEV01'

EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'SASBIDEV01'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'mycompany.CO.UK\SASBIDEV01_SSAS'
,@rmtpassword='B4l4r4m4__sbidev01'
END
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

The OLEDB wait type is what SQL Server uses to report the amount of time it's waiting for a response from code running outside the control of SQL Server via an OleDB provider.  Typically, this is from a linked server, and in your particular example is the amount of time SQL Server is waiting for SSAS to provide its response to that query.

There is nothing you can do to SQL Server to speed up this query.  You need to ensure SSAS has the dedicated memory required to ensure it never gets paged out of memory to the paging file.  This Microsoft Docs page might be helpful.


restoring a EC2 snapshot on RDS Is it possible to restore our database snapshot on ec2 directly on RDS as our database is very big ?

If not can we restore using csv files. ?

It is not possible to restore an EC2 snapshot to RDS.

It is possible, though not necessarily advisable, depending on the circumstances, to restore an EC2 snapshot to another server.


Query to find circular references I have two tables

ID  Task
1   1
2   2
3   3
4   4

Col1  depend
2     3
2     4
3     1
4     2

ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID and Col1 is just for combining rows from 2 tables, e.g.:

Task 1 can start anytime.
Task 2 can start only after completion of 3, 4 etc

1 –
2 – 3, 4, 1, 2   -- for 2 there is circular dependency
3 – 1
4 – 2, 3, 4      -- also circular dependency

Case 2:

Col1  depend
2     3
2     4
3     1
4     5
5     2

ID  Task
1   1
2   2
3   3
4   4
5   5

1
2 – 3, 4, 1, 5, 2     -- circular reference
3 – 1
4 – 5, 2, 3, 4        -- circular reference
5 – 2, 3, 4, 5        -- circular reference

Circular references may be available at any recursion level. How to find such circular references?
We have tried a recursive query but we went in infinite loop. How to write recursive query for this?

I adapted the example at http://www.postgresql.org/docs/8.4/static/queries-with.html to your case:

WITH RECURSIVE search_graph(id, depends, depth, path, cycle) AS (
        SELECT g.Col1, g.depends, 1,
          ARRAY[g.Col1],
          false
        FROM deps g
      UNION ALL
        SELECT g.Col1, g.depends, sg.depth + 1,
          path || g.Col1,
          g.Col1 = ANY(path)
        FROM deps g, search_graph sg
        WHERE g.Col1 = sg.depends AND NOT cycle
)
SELECT distinct id FROM search_graph where cycle = true;

results:

ID
 4
 2

for the first example,

ID
 4
 2
 5

for the second

You can find the sql fiddle at http://sqlfiddle.com/#!15/87a96/2


Need to suppress rowcount headers when using \G Is there a command to suppress the rowcount headers and asterisks when using '\G' to execute a SQL statement?  I am executing mysql with the -s and --skip-column-name options, but these don't suppress the rowcounts.

I have two suggestions

SUGGESTION #1 : Use grep command

You could use the grep command to filter it out

mysql -u... -p... -ANe"SHOW SLAVE STATUS\G" | grep -v "^\*\*\*\*\*\*\*\*\*"

Here is a quick demonstration:

[root@***** ~]# mysql -uroot -p... -ANe"SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.76.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004474
          Read_Master_Log_Pos: 76212800
               Relay_Log_File: relay-bin.013419
                Relay_Log_Pos: 76212945
        Relay_Master_Log_File: mysql-bin.004474
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 76212800
              Relay_Log_Space: 76213137
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'replicant@10.64.76.250:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@***** ~]# mysql -uroot -p... -ANe"SHOW SLAVE STATUS\G" | grep -v "^\*\*\*\*\*\*\*\*\*"
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.76.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004474
          Read_Master_Log_Pos: 111533085
               Relay_Log_File: relay-bin.013419
                Relay_Log_Pos: 111533230
        Relay_Master_Log_File: mysql-bin.004474
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 111533085
              Relay_Log_Space: 111533422
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'replicant@10.64.76.250:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@***** ~]#

SUGGESTION #2 : Use tail command (if expecting one row of output)

Run the \G command to a text file. Use tail to print everything but the first line:

[root@***** ~]# mysql -uroot -p -ANe"SHOW SLAVE STATUS\G" > junk.txt
[root@***** ~]# LINECOUNT=`wc -l < junk.txt`
[root@***** ~]# (( LINECOUNT-- ))
[root@***** ~]# tail -${LINECOUNT} junk.txt
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.76.250
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004474
          Read_Master_Log_Pos: 411163018
               Relay_Log_File: relay-bin.013419
                Relay_Log_Pos: 411163163
        Relay_Master_Log_File: mysql-bin.004474
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 411163018
              Relay_Log_Space: 411163355
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2013
                Last_IO_Error: error reconnecting to master 'replicant@10.64.76.250:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@***** ~]#

Give it a Try !!!


Installing oracle 9i in RHEL5 ... error during the process Currently I am installing oracle 9i in RHEL5, but I have found that during the installation process at 5% completion it throws this error:

  Error in writing to file /path of oracle home/ctx/lib32/libvs_zip.so

I'm using this guide:
https://sites.google.com/site/bantisandy/oracle/oracle-9i-in-rhel5

Has it happened to someone?

Please someone knows how to fix it?

OK, I'm sorry ... the problem was that the installers were corrupt, just use others and everything went well. Anyway if someone needed it I leave here step by step the installation that worked for me:

http://ivan.kartik.sk/oracle/install_ora9_elinux.html

Thank you very much


Connecting to MySQL 5.1 using IBM Data Studio I am trying to connect to MySQL database version 5.1 hosted on a web server using IBM Data Studio 4.1. I am getting an error

"The JDBC driver class was not found in the specified JAR or .zip file. Click the ellipsis (...) button to update the path to the JDBC driver files.".

I downloaded the connector file from "http://dev.mysql.com/downloads/connector/j/" but IBM Data Studio doesn't recognize this .zip file as the right .jar file.

I was able to connect using SQL Developer but it isn't as friendly as I wished for. So, can someone please help me with the right .jar file and instructions for updating data studio with the jar so that I can connect successfully.

Here is a similar processes to connect to a local derby database

Confirm that your Debrby server is running

I used the startNetworkServer.bat file on Windows 7 to start derby

Open IBM Data Studio
Navigate to Windows>Prefernces>DataManagement>Connectivity>Driver Definitions

Edit the Specific Driver you will use to connect

In my case that was Derby10.2-DerbyClient JBDC Driver Default

On the Jar List tab you will see the default driver files

In my case I updated to c:\db-derby\lib\derbyclient.jar  since my derby is installed in c:\db-derby

I had to save this new location and restart IBM Sata Studio

When I selected this connection parameter in the JDBC driver pulldown I was able to define a successful connection

Hope this helps