Limiting memory for Sybase SQL Anywhere I have to use Sybase's SQL Anywhere for a project so it has been put on a VM. Memory consumption is particularly high for a not overly taxed database and it's putting a lot of pressure on the VM.

The process is dbsrv12.exe and it's consuming >3GB of memory. Is there a way I can limit that to something more sensible and suitable for a VM?

Sybase's SQL Anywhere has startup parameters to control the min/max cache settings.

  -c  for the initial cache memory setting
  -ch for the maximum cache memory setting
  -ci for the minimum cache memory setting

If you set these appropriately it should constain the memory usage from the cache.

Here is a SQL Anywhere 12.0 page with those settings:
http://dcx.sybase.com/1200/en/dbusage/limiting-using-perform.html


MySQL performance issues with occasional complete freezes we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes are used. Furthermore, once a week or so, MySQL completely stops for no particular reason found in the log files. To be honest, we have not spent much time trying out different settings, but we tried tweaking a few with no success.
After some investigation, a few months ago we had to change some of the default settings, and that looked like it was good, but not for long. Our current my.cnf:

[mysqld]

innodb_buffer_pool_size=12288M # this
innodb_lock_wait_timeout=120 # and this
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # per design

After examining performance_schema, I am pretty sure some of these values are bad, but because of some unclear explanations for on MySQL docs and here, I am not sure which should be changed.
E.g. Created_tmp_tables is currently at 86331 with Created_tmp_disk_tables 25743, which seems very high. MySQL documentation states that increasing tmp_table_size and max_heap_table_size should help, but many answers say that that would only hide other issues behind this.
As we are all new to MySQL optimization, I would be grateful if someone could just give us some points in which direction we should look.
Here are global_status and global_variables entries.
Our current system spec is 8 physical CPUs and 64GB of RAM, with SSD discs. MySQL version is 5.7.33.

Rate Per Second = RPS
From the available (very incomplete) global status and global variables posted, please consider the following suggestions to improve performance.
innodb_log_file_size=512M  # from ~ 50M for log rotation reduction from 6 minutes to about 30 minutes.
innodb_log_buffer_size=256M  # from 16M for ~ 30 minutes RAM storage before write required
innodb_buffer-pool_size=40G  # from 12G to reduce innodb_data_reads RPS of 934
thread_cache_size=100  # from 8 to reduce threads_created count and overhead required
tmp_table_size=128M  # from 16M to expand capacity of in RAM tmp tables
max_heap_table_size=128M  # match tmp_table_size and reduce created_tmp_disk_tables RPhr of 1371

Observation, select_scan RPS of 37 indicates additional indexes would reduce table scans.
Use your slow query log and EXPLAIN SELECT xxxxx to assist with finding table and columns missing indexes.
Posting more complete data to pastebin.com would allow more complete analysis and there are many more Global Variables that could enhance speed of instance.


Right Tool for Data Mapping in Relation to Business Intelligence When you are working as a BI developer you also need to create a data mapping between dimensional modeling and source system. What kinda application to visualize a data mapping?

As with your other questions, this one could be more specific.  However, I will assume that you want to document mappings arising from analysis work.

An ETL tool will actually load the data, and the mappings tend to take the form of wiring loom diagrams that show the mappings graphically.  If you have relatively simple transformations the internal self-documenting capabilities of the ETL tool may suffice for this.

However, this tends to break down on more complex transformations or in situations where not all of the logic is done within the tool.  The more complex the transformation the more likely it is to happen.  In many cases a decisions is made to do all the transformation work in stored procedure code and the ETL tooling is largely used for extraction, logging and load control tasks.

If you are mapping for documentation or specification purposes, people often use spreadsheets if no other tool is available.  However, this really carries an assumption of an isomorphic 1:1 mapping from source to target and does a poor job of capturing and documenting complex logic.

Some CASE tools have meta models for mapping available.  I've done this with Sparx Enterprise Architect, although we had to build a custom metamodel for this.  Some tools have this feature out of the box.

Finally (and this is my preferred approach) a functional specification for the ETL can be written up as a technical document.  This can describe mappings and detailed specifications for the logic.  In the case of a system with complex logic in the ETL a spec document is by far the best way to do this.


How to run recurring tasks on Postgresql without an external cron-like tool? I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent.

Do you know of an "internal" alternative which wouldn't involve the external tool ?

I want to avoid security concerns with the password stored on the command line of the pgAgent.
I want to avoid any additional system configuration for hiding the password (~/.pgpass).

Postgresql 8.3
Linux RedHat 64bit

Even if you were running the soon-to-be-released (at time of writing) PostgreSQL 10 or the current PostgreSQL 9.6 not an ancient release like 8.3, there's still no built-in task scheduler.

Something like PgAgent or external cron jobs is required, there is no convenient workaround.

The background workers feature introduced in 9.3 should hopefully permit a tool like PgAgent to be moved into the PostgreSQL core in a later release, but it hasn't been done yet. Even on 9.3 you still have to run cron or pgagent.

A few people are working on background worker based schedulers, and there are some patches coming that should provide facilities to help with that. But as of PostgreSQL 10 there's still no good quality, widely adopted scheduler, and most people use cron / ms task scheduler / etc.

Please take a look at the version policy, too; you're running an obsolete and unsupported release.


Mysqldump without slow query Situation:

I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query.

Question:

How can I set the system up to prevent the mysqldump from ever being being seen as slow query?

Restrictions given by security team:

The backup user can only have select
The backup can run anywhere from once a day to hourly and the process will be applied to varying sizes of databases (so I can't just speed up the process)
Only the backup user can be used

Environment:

OS: Ubuntu 14.04
MySQL: Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3
MySQLdump: Ver 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64)

The easiest way would be to change the global setting for long_query_time to a ridiculously high number, run the mysqldump, and change long_query_time back to its original value.
Perhaps something like this
MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Record the long_query_time value
#
SQL="SELECT variable_value FROM information_schema.global_variables"
SQL="${SQL} WHERE variable_name='long_query_time'"
ORIGINAL_LONG_QUERY_TIME=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Change long_query_time to 1 year
#
SQL="SET GLOBAL long_query_time = 31536000"
mysql ${MYSQL_CONN} -ANe"${SQL}
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Change long_query_time to original value
#
SQL="SET GLOBAL long_query_time = ${ORIGINAL_LONG_QUERY_TIME}"
mysql ${MYSQL_CONN} -ANe"${SQL}

or disable the slow query log
MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Disble Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'OFF'"
mysql ${MYSQL_CONN} -ANe"${SQL}"
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Enable Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'ON'"
mysql ${MYSQL_CONN} -ANe"${SQL}"

Give it a Try !!!
CAVEAT #1
Your DBA must give the SUPER privilege to backupuser so that backupuser can globally change the long_query_time setting. Perhaps a DBA can have a cronjob give away SUPER privilege to backupuser just before the mysqldump and revoke SUPER privilege when the backup is done.
CAVEAT #2
Unfortunately, any long running queries that occur during the mysqldump might not be recorded


how do you output a plsql array via ref cursor I have a legacy procedure that returns a plsql assoc table to a C++ DLL.  Now I need to call it via C# and ODP.NET.  I have been unable to find any info on how to call a proc that returns a pl/sql assoc array (plenty on how to send data to one that takes as an input).

Thinking that if I can have the proc return a ref cursor then it should not be an issue since I have done it plenty of times

However, have not found examples of doing that and all attempts have failed.

Any help would be welcomed.

code below.  it provides an orderid as input. it calls another proc relOrders  that returns an array that is put into pl sql table.

TYPE tab_number12 IS TABLE OF NUMBER (12)
      INDEX BY BINARY_INTEGER;

PROCEDURE GetOrders (p_Orderid          IN     NUMBER,
                                    p_subOrderIds            OUT tab_number12)
   IS
      allOrder   DBMS_SQL.number_table;

   BEGIN
      allOrder (1) := p_Orderid;

      relOrders (allOrder);

      FOR i IN allOrder.FIRST .. allOrder.LAST
      LOOP
         p_subOrderIds (i) := allOrder (i);
      END LOOP;
   END;

I couldn't find an example either, so I concocted this:

CREATE TYPE t_tf_row AS OBJECT (
  id           varchar2(20),
  description  VARCHAR2(50)
);
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

create or replace package aa2rc as
  FUNCTION get_tab_ptf
    RETURN t_tf_tab PIPELINED;
  PROCEDURE GetOrders (
    p_Orderid     IN  NUMBER,
    p_subOrderIds OUT sys_refcursor);
end aa2rc;
/

create or replace package body aa2rc as
  type allOrder_t is table of varchar2(50) index by varchar2(20);

  allOrder allOrder_t;

  FUNCTION get_tab_ptf
    RETURN t_tf_tab PIPELINED AS
    i varchar2(20);
    BEGIN
          i := allOrder.first;
          while (i is not null) loop
            PIPE ROW(t_tf_row(i, allOrder(i)));
            i := allOrder.next(i);
          END LOOP;

          RETURN;
    END;

  PROCEDURE GetOrders (
    p_Orderid     IN  NUMBER,
    p_subOrderIds OUT sys_refcursor)
    IS

      procedure relorders (p_ord in out allOrder_t) is
        i number;
        begin
          for i in 1 .. 11 loop
            p_ord('Index no. ' || (p_ord('One') + i))
              := 'Value ' || (p_ord('One') + i);
          end loop;
        end;

    BEGIN
      allOrder ('One') := p_Orderid;

      relOrders (allOrder);

      open p_suborderids for
        select * from table(aa2rc.get_tab_ptf);
    END;
end aa2rc;
/

I'm not that sharp with C#, but ran a test with sqlplus:

variable R refcursor
execute aa2rc.getorders(5,:r);
print r

which gave:

ID                   DESCRIPTION
-------------------- --------------------------------------------------
Index no. 10         Value 10
Index no. 11         Value 11
Index no. 12         Value 12
Index no. 13         Value 13
Index no. 14         Value 14
Index no. 15         Value 15
Index no. 16         Value 16
Index no. 6          Value 6
Index no. 7          Value 7
Index no. 8          Value 8
Index no. 9          Value 9
One                  5


Problem with postgres Function I wrote the following function to query data from tables and columns that I give as arguments:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
 returns SETOF anyelement as
 $func$
 BEGIN
 RETURN QUERY EXECUTE
 'select
 time_bucket(' || gap || ', time)::text as hour,
 avg(' || _col ||'),
 min(' || _col ||'),
 max(' || _col ||')
 from ' || pg_typeof(_tbl) || ' d
 where d.device_id in (
     select device_id from sensors)
     group by hour';
 END
 $func$ language sql stable;

The problem is that when call the function like this:
select field_summaries('5minutes', NULL:: m_13, 'temperature');

I receive the following error:
ERROR:  syntax error at or near ":"
LINE 2:  time_bucket(00:05:00, time)::text as hour,                 ...
                       ^
QUERY:  select
time_bucket(00:05:00, time)::text as hour,
avg(temperature),
min(temperature),
max(temperature)
from m_13 d
where d.device_id in (
select device_id from sensors)
group by hour
CONTEXT:  PL/pgSQL function field_summaries(interval,anyelement,text) line 3 at RETURN QUERY

Does anyone have an idea of what this could be?

Change gap datatype from interval to text and enclose it between single quotes in your dynamic query.
create function test (gap text)
returns text
as $ftest$
declare
    cmd text;
begin
    cmd := 'select ''' ||  gap || '''::interval';
    return cmd;
end
$ftest$ language plpgsql

select test('5 minute');

test
:--------------------------
select '5 minute'::interval

select '5 minute'::interval

| interval |
| :------- |
| 00:05:00 |

db<>fiddle here


Per Team - Count Number of Losses in Last N Results I have the following tables:

teams:
-----------------------
| team_id | team_name |
-----------------------

team_results:
----------------------------------------------------------
| team_results_id | team_id | result_date | won (1 OR 0) |
----------------------------------------------------------

For each team, I want to look at the last N results and count the number of losses.

So far, I have the following query:

SELECT *, COUNT(*)
FROM
(
    SELECT teams.team_id, won
    FROM teams
    INNER JOIN
    (
        SELECT team_id, won
        FROM team_results
        ORDER BY result_date DESC
    ) AS team_results ON teams.team_id = team_results.team_id
) AS a
WHERE won = 0
GROUP BY team_id;

This counts the total number of losses per team, but I've been unable to limit the count to the last N results for each team. I've tried placing a limit in the nested subquery without success.

This should do the trick:

select team_id, count(*) from (
    select *, case when @team = team_id then @i:=@i+1 else @i:=0 END as n, @team:=team_id
    from team_results, (select @i:=0, @team_id:=0) init
    order by team_id, result_date desc
) a
where a.n < 10 and won = 0
group by team_id;

You use the inner query to enumerate the results by date. Here you probably want to apply some heuristic where condition to filter the results to optimize your query. For example if they in average play every second day then you can limit for the last one month or so.

Then you can count the lost games with the given conditions per team.

@update to show teams with 0 loss

select team_id, sum(1 - won) -- instead of count rows sum losses (1-won) = loss
from (
    select *, case when @team = team_id then @i:=@i+1 else @i:=0 END as n, @team:=team_id
    from team_results, (select @i:=0, @team_id:=0) init
    order by team_id, result_date desc
) a
where a.n < 10  -- remove the won=0 filter
group by team_id;


Using exists in a query instead of a regular where I saw this query that finds all the employees with more than one degree:

select id, name
from employee E
where exists (
 select *
 from academics A1, academics A2
 where  A1.emp_id = E.id and
        A2.emp_id = E.id and
        A1.discipline != A2.discipline
 )

But why is the exists necessary? Why not just do:

select id, name
from employee E, academics A1, academics A2
where A1.emp_id = E.id and
      A2.emp_id = E.id and
      A1.discipline != A2.discipline

Are the two above equivalent?

I'd suggest you another approach.

COUNT DISTINCT disciplines by employee.

Then use EXISTS to check if one Employee has more than one degree.

select      e.id, e.name
from        Employees e
where exists (select    1
              from      Academics a
              where     a.emp_id = e.id
              having count(distinct a.discipline) > 1);

Or JOIN it with table Employees and show these were NumDegrees > 1

select      e.id, e.name, nd.NumDegrees
from        Employees e
inner join (select    emp_id, count(distinct a.discipline) NumDegrees
            from      Academics a
            group by  a.emp_id) nd
on          e.id = nd.emp_id
where       nd.NumDegrees > 1;


How can I ensure that only one copy of a procedure is running in Oracle? We have the need to ensure that only one copy of a particular procedure is running in Oracle.  If it is already running and a user tries to open another, then it should error.

Whats the best method of doing this?

You can do this with DBMS_LOCK and an exclusive lock.

See the following procedure:

CREATE OR REPLACE PROCEDURE myproc
IS
  lockhandle VARCHAR2(128);
  retcode NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);

  retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode);

  IF retcode<>0
  THEN
    raise_application_error(-20000,'myproc is already running');
  END IF;

  /* sleep so that we can test with a 2nd execution */
  DBMS_LOCK.sleep(1000);

  retcode:=DBMS_LOCK.RELEASE(lockhandle);

END myproc;
/

Test (session 1):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /

(Obviously returns when DBMS_LOCK.sleep() returns).

Test (session 2):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-20000: myproc is already running
ORA-06512: at "PHIL.MYPROC", line 12
ORA-06512: at line 2

SQL>

Obviously you need to GRANT EXECUTE ON DBMS_LOCK TO YOURUSER;.


Geting rows from self-referencing single table using Postgres I am trying to get rows from self-referencing single table in Postgres.

I got table A and I would like to get Table B. For given ProcID using Input_proc_Id I want to get another row until i get capture row 'YES'. So it's kind of loop that will append row to new table until it get 'Yes' in capture filed.

Is it a way to do it in single query or i need to create function loop? If so how to do it?

Setup code:

CREATE TABLE table_A AS
  SELECT 1 AS procid, 3 AS input_proc_id, false AS capture
  UNION ALL SELECT 2 AS procid, 4 AS input_proc_id, false AS capture
  UNION ALL SELECT 3 AS procid, 6 AS input_proc_id, false AS capture
  UNION ALL SELECT 4 AS procid, 6 AS input_proc_id, false AS capture
  UNION ALL SELECT 5 AS procid, 7 AS input_proc_id, false AS capture
  UNION ALL SELECT 6 AS procid, 8 AS input_proc_id, false AS capture
  UNION ALL SELECT 7 AS procid, 8 AS input_proc_id, false AS capture
  UNION ALL SELECT 8 AS procid, 9 AS input_proc_id, false AS capture
  UNION ALL SELECT 9 AS procid, 10 AS input_proc_id, false AS capture
  UNION ALL SELECT 10 AS procid, 12 AS input_proc_id, false AS capture
  UNION ALL SELECT 11 AS procid, 13 AS input_proc_id, false AS capture
  UNION ALL SELECT 12 AS procid, 14 AS input_proc_id, false AS capture
  UNION ALL SELECT 13 AS procid, 15 AS input_proc_id, false AS capture
  UNION ALL SELECT 14 AS procid, null AS input_proc_id, true AS capture
  UNION ALL SELECT 15 AS procid, null AS input_proc_id, true AS capture;

Thank's too @dezso comments i found an answer how to do it!

WITH RECURSIVE temp(procid, input_proc_id, capture) AS (
  SELECT procid, input_proc_id, capture
  FROM table_A
  WHERE procid = 1
  UNION ALL
    SELECT table_A.procid, table_A.input_proc_id, table_A.capture
  FROM temp, table_A
  WHERE  temp.input_proc_id = table_A.procid
)

SELECT * FROM temp;

Which for given "procid" gives correct output, but it works on assumption that input_proc_id is null when "capture" column is "YES" otherwise it is not going to work.


Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home? I have an SSDT project containing tSQLt unit tests.

I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and SQL Server developer edition).

The publish hangs indefinitely and I eventually have to kill visual studio.

The wait type is PREEMPTIVE_OS_AUTHORIZATIONOPS and an example of a statement hung waiting for this (from sys.dm_exec_sql_text) is

(@r BIT OUTPUT)
SELECT @r = CASE
              WHEN I.Version = I.ClrVersion THEN 1
              ELSE 0
            END
FROM   tSQLt.Info() AS I;

I also reproduce this by calling

SELECT tSQLt.Private::Info()

Which is a simple method

public static SqlString Info()
{
  return (SqlString) Assembly.GetExecutingAssembly().GetName().Version.ToString();
}

I assume that it is trying to contact the domain controller to establish that I have some permissions or other. I don't get this with other CLR assemblies and so suspect that this may be something to do with TSQLT not being a SAFE_ACCESS assembly (permission set is EXTERNAL_ACCESS).

Anyone know what's going on here and how I can fix this and work disconnected from my company's network without encountering this?

If you aren't going to be connected to the domain all the time, as it is a development instance, change the db owner from a domain account to a local account or a sql account.

Check this great blog out on the subject:

http://andreas-wolter.com/en/where-is-that-preemptive-wait-coming-from/

To summarise the blog post:

Having a db owner as a domain user causes kerberos ticket granting ticket requests (i.e. sql wants to authenticate using the domain account that is a db owner and kerberos has a thing called ticket granting ticket which lets sql impersonate the db owner)
If you have high latency (or issues connecting, as in this case) to your domain controller, these calls will be slow
SQL caches the calls for 10 minutes so these can appear intermittent

ed


Why does SQL developer suggest a different format for a procedure that works? This is my original procedure that works beautifully:
create or replace PROCEDURE EXTRACT_0_CPP AS
    CURSOR c_data IS
        SELECT cpp,
               rfu1,
               rfu2,
               mean_rfu,
               charge_ph7_4,
               hydropathy
        FROM   cpp
        ORDER BY LENGTH(cpp);
    F1 UTL_FILE.FILE_TYPE;

BEGIN
    F1 := UTL_FILE.FOPEN( location => 'EXTRACT_DIR',
                          filename => '0_cpp.TXT',
                          open_mode => 'w',
                          max_linesize => 32767);
    FOR cur_rec IN c_data LOOP
        UTL_FILE.PUT_LINE (F1,
                            cur_rec.cpp || ':' ||
                            cur_rec.rfu1 || ':' ||
                            cur_rec.rfu2 || ':' ||
                            cur_rec.mean_rfu || ':' ||
                            cur_rec.charge_ph7_4 || ':' ||
                            cur_rec.hydropathy);
    END LOOP;
    UTL_FILE.FCLOSE(F1);
END;

But SQL Developer gives me a squiggly red line under SELECT and suggests I change it to this:
create or replace PROCEDURE EXTRACT_0_CPP AS
    CURSOR c_data IS
        SELECT
    "A1"."CPP"         "CPP",
    "A1"."RFU1"           "RFU1",
    "A1"."RFU2"           "RFU2",
    "A1"."MEAN_RFU"       "MEAN_RFU",
    "A1"."CHARGE_PH7_4"   "CHARGE_PH7_4",
    "A1"."HYDROPATHY"     "HYDROPATHY"
FROM
    "C##ELLIE"."CPP" "A1"
ORDER BY
    length("A1"."CPP");
    F1 UTL_FILE.FILE_TYPE;

BEGIN
    F1 := UTL_FILE.FOPEN( location => 'EXTRACT_DIR',
                          filename => '0_cpp.TXT',
                          open_mode => 'w',
                          max_linesize => 32767);
    FOR cur_rec IN c_data LOOP
        UTL_FILE.PUT_LINE (F1,
                            cur_rec.pk_cpp || ':' ||
                            cur_rec.rfu1 || ':' ||
                            cur_rec.rfu2 || ':' ||
                            cur_rec.mean_rfu || ':' ||
                            cur_rec.charge_ph7_4 || ':' ||
                            cur_rec.hydropathy);
    END LOOP;
    UTL_FILE.FCLOSE(F1);
END;

My question is (why) is this better? What is "A1"?

On the subject of why it would suggest a different format when the code already works, of course it's possible to have poorly formatted code that works, so in principle there is no reason why you shouldn't consider layout or refactoring improvements that might make the code more robust, efficient, or easier to maintain.
However, double-quoted names are poor practice because they hide naming errors and force you to specify upper/lowercase, and the result is less readable than the original. They are really a portability feature for use with third party applications that have weird table names. Code generators tend to slap double quotes around everything because it's easier to do that than to detect whether they are actually needed in each case. There is no way that
from "C##ELLIE"."CPP" "A1"

is any sort of improvement on
from cpp

except perhaps for the use of a table alias. It's a good idea to give the table an alias (without double-quotes!) and use it when referring to columns. But "A1" (or even a1) is the kind of table alias that only a computer would dream up. It would be far better to use an alias that is some kind of abbreviation of the table name, perhaps in this case c. Imagine if you were joining six tables - now which one is a3? Oh right, that's ORDER_DETAILS. (Though in this case the table name is so short that there is no point aliasing it.) So,
SELECT "A1"."CPP" "CPP"
FROM   "C##ELLIE"."CPP" "A1"

would be far better as just
SELECT c.cpp
FROM   cpp c

(I'd also lowercase it because this isn't 1974 and my editor highlights language keywords using colours and bold, but we'll let that go.)
Hardcoding schema names is poor practice because it's at best redundant (the object is in the schema you are already working in so it adds nothing except needless complication) or worse, it limits portability (if you ever rename the schema or move the code you'll have to go through it cleaning up the hardcoded references).
I'm sure this is a clever feature that means well, but in this instance it's not good advice.


SQL Server to MySQL migration, replace '\xA0' with a regular space I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column 'Address1'

As I am trying to automate data migrations that will happen regularly, I need to find a solution I can put into a script. I am using wbcopytables.exe at the moment, but I do not necessarily need to.

In just trying to find an appropriate solution to later implement in the script, I have tried running the below in SQL Server to fix the source data:

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'\xA0', ' ')

and

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'\u00A0', ' ')

but that is not fixing the source data, and my Google-Fu is failing me. Thoughts?

Maybe you can use CHARINDEX casting your Address1 to varbinary(8000) to identify the offending data:

select *
from [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
where charindex(0xA0, cast(Address1 as varbinary(8000))) > 0;

Check this answer: https://dba.stackexchange.com/a/37355/55596


MongoDB presplitting chunks for compound shard key In my mongodb setup I have a compound shard key {"region" : 1, "foo" : 1, "bar" : 1} and I know the values region can be and that each region should be on one chunk.

Therefore I'd like to pre-split based on the region key only. The sharding status should look like that afterwards:

{ "region": { "$minKey" : 1 }, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } -> { "region": region1, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } on: shard1
{ "region": region1, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } -> { "region": region2, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } on: shard2
{ "region": region2, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } -> { "region": { "$maxKey" : 1 }, "foo": { "$minKey" : 1 }, "bar": { "$minKey" : 1 } } on: shard3

I've tried some ways to achieve that, but nothing worked:

db.runCommand( { split : "mydb.mycollection" , middle : { "region" : "region1" } } ); will return an error, because the complete shard key have to be part of the split.
db.runCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : { "$minKey" : 1 }, "bar" : { "$minKey" : 1 } } } ); and db.runCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : "$minKey" , "bar" : "$minKey" } } ); will interpret the minKey as String and split on that base which is wrong.

How can I finally split chunks with a compound shard key on a single field base?!

Cheers.

Just a slight issue with how you are passing the $minKey values in, try this instead:

db.adminCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : MinKey , "bar" : MinKey } } );
db.adminCommand( { split : "mydb.mycollection" , middle : { "region" : "region2", "foo" : MinKey , "bar" : MinKey } } );

This got me the following layout:

sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 4,
    "minCompatibleVersion" : 4,
    "currentVersion" : 5,
    "clusterId" : ObjectId("53a2cd9d98b4ace818666544")
}
  shards:
    {  "_id" : "shard0000",  "host" : "localhost:30000" }
    {  "_id" : "shard0001",  "host" : "localhost:30001" }
    {  "_id" : "shard0002",  "host" : "localhost:30002" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "mydb",  "partitioned" : true,  "primary" : "shard0001" }
        mydb.mycollection
            shard key: { "region" : 1, "foo" : 1, "bar" : 1 }
            chunks:
                shard0000   1
                shard0001   2
            {
    "region" : { "$minKey" : 1 },
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} -->> {
    "region" : "region1",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} on : shard0000 Timestamp(2, 0)
            {
    "region" : "region1",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} -->> {
    "region" : "region2",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} on : shard0001 Timestamp(2, 2)
            {
    "region" : "region2",
    "foo" : { "$minKey" : 1 },
    "bar" : { "$minKey" : 1 }
} -->> {
    "region" : { "$maxKey" : 1 },
    "foo" : { "$maxKey" : 1 },
    "bar" : { "$maxKey" : 1 }
} on : shard0001 Timestamp(2, 3)

The use of the $minKey (MinKey) and $maxKey (MaxKey) values is a bit tough to tease out (they are rarely used except internally), but there is a decent, and illustrative example here in the docs.


AlwaysON AG synchronous commit mode disconnected transaction log effect and mitigation I have an AlwaysON AG with one primary and one secondary replica in synchronous commit mode.  The databases in the AG all have autogrowth disabled (standard policy within organisation).  There is planned hardware maintenance on the server that hosts the secondary replica that will result in the server being offline for 4-8 hours.  My undestanding is that when this happens, the connected state of the secondary replica will change to DISCONNECTED, the send queue of the databases in the primary replica will accumulate unsent transaction log records.  For as long as the secondary replica remains offline, all of the current log records remain active (autogrowth is disabled), transaction log backups will not truncate the log with the potential of the log filling.  Can someone please confirm if this is correct?  Is there any way we can mitigate this, ie. ensuring there's no index maintenance during this window or switching to aynschronous commit mode?

That was my understanding, but after reading this msdn article https://msdn.microsoft.com/en-us/library/ff877931%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396 I am confused.  There is a note that states: "If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode."

Does this pertain to my situation as described above and if so does this mean that the potential for the transaction to fill is mitigated by design? Can this temporary shift in commit mode be seen when querying sys.availabilty_replicas or via any extended event or is it a purely a under the covers change?

Can someone please confirm if this is correct?

Yes, you are 100% correct!

  Is there any way we can mitigate this, ie. ensuring there's no index maintenance during this window or switching to aynschronous commit mode?

The mitigation is not with the mode that the replicas run it, it's removing that replica from the AG.

  Does this pertain to my situation as described above and if so does this mean that the potential for the transaction to fill is mitigated by design?

Yes, that's what will happen if you change nothing. No, the transaction log will still fill up. Regardless of synchronous or asynchronous modes the primary must keep all log blocks that are required for the replica that is the furthest behind.

In your scenario, there are only two replicas a primary and a secondary. The secondary will be offline for a while. You've already correctly stated what will happen, the send queue will get larger as the replica is offline and stays that way. This happens no matter the mode.

The mitigation is to remove the replica that will be down for the extended period of time. This allows the primary to properly truncate the log with proper transaction log backups.

Once the secondary is back up, since it was kicked out of the AG all of the databases participating in that AG will be in a restoring state. To get back to a good steady state and bring the replica back into the AG, take the log backups that were run during the period the secondary replica was offline and apply them to the databases, leaving them in a restoring state (with no recovery). Once you get close, pause all transaction log backups on the primary and join the replica back into the AG. Resume the log backups on the primary.


Is there a way to partially initialize a TYPE? I have created a custom type:

CREATE TYPE my_type as(name text, street text, location text);

Initializing such a type is easy:

SELECT ROW('a', 'b', 'c')::my_type;

I am wondering if there is an easy way to initialize a type partially, meaning that all unmentioned columns are set to null implicitly. In pseudo-code, something like:

SELECT ROW('a', 'c')::my_type(name, location);

In addition, is there also a way to initialize a type when the columns are not mentioned in the correct order? In pseudo-code, something like:

SELECT ROW('c', 'a', 'b')::my_type(location, name, street);

Code examples based on your type definition:

CREATE TYPE my_type AS (name text, street text, location text);

Your syntax is not possible (as you already know). A plain cast does not allow a (partial) list of fields.

SELECT ROW('a', 'c')::my_type(name, location);

In a plain SQL cast expression, you have to provide NULL values (or some other default) for missing fields:

SELECT ROW('a', NULL, 'c')::my_type;

Or, using a row type literal as input:

SELECT '(a,NULL,c)'::my_type;

INSERT or UPDATE statements can target fields of composite types individually. (The underlying type is derived from the target.) Demo:

CREATE TABLE tbl (tbl_id serial, comp my_type);

INSERT INTO tbl (comp.name, comp.location) VALUES ('a', 'c')

UPDATE tbl
SET    comp.name = 'X'
     , comp.street = 'Y'
WHERE  tbl_id = 1;

Fields not filled explicitly default to NULL. (Unless the type has a different default, which would be uncommon.)

Not possible for DELETE, obviously, which always removes the whole row. The logical equivalent would be to set a subfield NULL:

UPDATE tbl
SET    comp.name = NULL
WHERE  tbl_id = 1;

Normally you would use variables in a procedural language function - default PL/pgSQL. There you have SELECT INTO. And you can address fields of a composite type individually. Demo:

DO
$$
DECLARE
    _var my_type;
BEGIN
   SELECT INTO _var.location, _var.name  -- in any chosen order
          'c', 'a';
   RAISE NOTICE '%', _var;
END
$$

db<>fiddle here

Do not confuse PL/pgSQL assignment with SELECT INTO and the SQL command SELECT INTO (which should not be used at all, use CREATE TABLE AS instead). See:

SELECT INTO with regexp_replace() doesn't write changes into newly generated table

Related, more sophisticated tricks:

Casting NULL type when updating multiple rows
How to set value of composite variable field using dynamic SQL
(demonstrating the hstore #= operator)


Table Transformation with SQL? I got a set of tables which have been imported into a database by transforming them like shown in the following picture:

Now it is necessary to get the former tables back. Unfortunately the guy who did the importing finished school and is thus is unavailable. Therefore I was asked to find an SQL-based solution to back-transform the tables. I would be able to write a small tool to solve this problem, but I've got no idea how to solve this with SQL.

With Kind Regards,

Something like this might do the trick (here's the SQLFiddle)

SELECT
Line,
MAX(CASE WHEN `Column` = 'C1' THEN `Value` END) AS C1,
MAX(CASE WHEN `Column` = 'C2' THEN `Value` END) AS C2,
MAX(CASE WHEN `Column` = 'C3' THEN `Value` END) AS C3,
MAX(CASE WHEN `Column` = 'C4' THEN `Value` END) AS C4
FROM YourTable
GROUP BY Line

Like Sascha suggested in their answer, you might want to consider either creating this as a view, or creating a view that looks like Table 2 once you've finished putting the table back to how it was. That should prevent this kind of problem popping up again in the future.


Connecting to Oracle DB with LDAP-Credentials So I would like to connect to a remote Oracle database from my Linux system using the oraclient 19c on the command line, but whenever I try to connect it sees the realmpart of my username as the server.

For example I want to connect with my username "USER@REALM.DOMAIN" to the database identified by "DB" in my tnsnames.ora, i do sqlplus USER@REALM.DOMAIN@DB and of course it complains with ORA-12154: TNS:could not resolve the connect identifier specified, because it thinks "REALM.DOMAIN" is the database.

When I try it without the realmpart like sqlplus USER@DB it returns ORA-01017: invalid username/password; logon denied.

How can I pass my username to the client?

For special usernames, use double quotes.

SQL> create user "USER@REALM.DOMAIN" identified by 1;

User created.

SQL> grant create session to "USER@REALM.DOMAIN";

Grant succeeded.

SQL> connect USER@REALM.DOMAIN/1
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.
SQL> connect "USER@REALM.DOMAIN"/1
Connected.
SQL>


Is there a limit on query time for an Oracle linked server? We've created a stored procedure to pull in full copies of some Oracle tables with a Linked Server.

Over the course of 11 hours, the procedure correctly populated the first 11 tables but failed with this error on table 12:

Msg 7399, Level 16, State 1, Procedure Refresh_From_Oracle, Line 45
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Procedure Refresh_From_Oracle, Line 45
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE".

Is there anything there that gives a clue as to why the procedure failed when it got to table 12? Or is there a limit to how long a procedure can run, or a query can run against a linked server?

All tables except 12 and 13 have <2,000,000 rows, and 12 and 13 have about 70,000,000 each.

Here is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Jack Douglas
-- Create date: 2016-10-07
-- Description: Truncates and re-populates UAT copies of Oracle data
-- =============================================
ALTER PROCEDURE [dbo].[Refresh_From_Oracle]
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE dbo.TABLE1;
    INSERT INTO dbo.TABLE1 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE1;

    …

    TRUNCATE TABLE dbo.TABLE12;
    INSERT INTO dbo.TABLE12 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE12;

    TRUNCATE TABLE dbo.TABLE13;
    INSERT INTO dbo.TABLE13 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE13;

END

The problem turned out to be on the Oracle side:

Tue Oct 18 05:41:17 2016
ORA-01555 caused by SQL statement below (Query Duration=30945 sec, SCN: 0x0aee.4682f3e7):
Tue Oct 18 05:41:17 2016

It's the classic 'Snapshot too old' issue with a long-running transaction and MVCC.


Indexing a view with xQuery in it I'm not sure if this can be done at all but if it can it would be a huge help.

I have a system where I am not allowed to make changes to the database structures.  I have yet to have code changes put in place either for that matter but at least those they will consider (bairly).

What I am dealing with is a table that contains XML data stored in a varchar(max) column.  Then a view that not only references the XML using xQuery but actually has xQuery in the WHERE clause.

Here is the original query.

CREATE VIEW [dbo].[My_View]
AS
   WITH xoutput AS (
          SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
          FROM [dbo].ci
          INNER JOIN [dbo].ct
            ON ci.[ContentType_id] = ct.[Id]
          INNER JOIN [dbo]. cv
            ON ci.ID = cv.[ContentItemRecord_id]
          WHERE ct.[Name] = 'MyImage' and Latest = 1)
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1

What I thought of trying was creating an indexed view out of at least part of it.

The first thing of course was to create a SCHEMABOUND view with COUNT_BIG.  Where I'm running into a problem is the grouping.

When I try it like this:

ALTER VIEW My_XML_Kenneth
WITH SCHEMABINDING
AS
    SELECT ci.[ContentType_id], ci.ID,
        h.ep.value('(IsBlocked)[1]', 'bit') AS IsBlocked,
        h.ep.value('(IsPublished)[1]', 'bit') AS IsPublished,
        h.ep.value('(MyID)[1]','varchar(10)') as MyID,
        h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,
        h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,
        h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
        COUNT_BIG(*) ct_bg
    FROM [dbo].ci
    INNER JOIN [dbo].ct
        ON ci.[ContentType_id] = ct.[Id]
    INNER JOIN [dbo].cv
        ON ci.ID = cv.[ContentItemRecord_id]
    CROSS APPLY (VALUES (CONVERT(xml, ci.[Data]))) x (x)
    CROSS APPLY x.x.nodes('//Data/MyImage') as h(ep)
    WHERE ct.[Name] = 'MyImage'
      AND Latest = 1
    GROUP BY ci.ContentType_id, ci.ID,
        h.ep.value('(IsBlocked)[1]', 'bit'),
        h.ep.value('(IsPublished)[1]', 'bit'),
        h.ep.value('(MyID)[1]','varchar(10)'),
        h.ep.value('(Image)[1]', 'varchar(max)'),
        h.ep.value('(IsBlocked)[1]', 'bit'),
        h.ep.value('(IsPublished)[1]', 'bit')
GO

I get an error

XML methods are not allowed in a GROUP BY clause.

When I try this as the GROUP BY

GROUP BY ci.ContentType_id, ci.ID, h.ep.nodes('//')

I get this error

"nodes" is not a valid function, property, or field.

Is what I'm trying even possible?

Note: last time I tried to come up with a fix to this view I got HEAVY push back that they couldn't possibly make a change.  So if it isn't possible it's not like I'm out much.

You can index a view that contains an XML column. You cannot index a view that contains any of the XML datatype methods ( eg .nodes, .query, .value, .exist ).

I would say your greatest issue is always casting the VARCHAR column to XML.  Is there are a particular reason you are not using the XML datatype?  Although it can alter XML slightly ( eg whitespace ), storing it as VARCHAR means you can't take advantage of the usual performance tuning methods with the XML datatype such as typing the XML ( XML SCHEMA COLLECTION ) and XML Indexes.

In the meantime, add the text() accessor and avoid repeating XML calls ( ie no need to .value IsBlocked as you have it as WHERE clause criteria ) for a performance boost:

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      0 as IsBlocked,
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

These were my results from a simple 100k row test rig I created:

Simple test rig:

-- Simple test rig
USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.ci') IS NOT NULL DROP TABLE dbo.ci
IF OBJECT_ID('dbo.ct') IS NOT NULL DROP TABLE dbo.ct
IF OBJECT_ID('dbo.cv') IS NOT NULL DROP TABLE dbo.cv
IF OBJECT_ID('dbo.My_View') IS NOT NULL DROP VIEW dbo.My_View
IF OBJECT_ID('dbo.My_View2') IS NOT NULL DROP VIEW dbo.My_View2
IF OBJECT_ID('dbo.My_View2_4') IS NOT NULL DROP VIEW dbo.My_View2_4
GO

CREATE TABLE dbo.ci ( Id INT IDENTITY PRIMARY KEY, ContentType_id INT, data VARCHAR(MAX) )
CREATE TABLE dbo.ct ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Latest BIT )
CREATE TABLE dbo.cv ( ContentItemRecord_id INT PRIMARY KEY )
GO

;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.ci ( ContentType_id, data )
SELECT rn, '<Data>
    <MyImage>
        <MyID>' + CAST( rn AS VARCHAR(MAX) ) + '</MyID>
        <Image>' + CAST( NEWID() AS VARCHAR(MAX) ) + '</Image>
        <IsBlocked>0</IsBlocked>
        <IsPublished>1</IsPublished>
    </MyImage>
</Data>'
FROM cte
GO

CHECKPOINT

INSERT INTO dbo.ct ( Name, Latest )
SELECT 'MyImage', ContentType_id
FROM dbo.ci

CHECKPOINT

INSERT INTO dbo.cv ( ContentItemRecord_id )
SELECT Id
FROM dbo.ci
GO

-- Original view
CREATE VIEW [dbo].[My_View]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1
GO

CREATE VIEW [dbo].[My_View2]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      h.ep.value('(IsBlocked/text())[1]', 'bit') as IsBlocked,
      h.ep.value('(IsPublished/text())[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

--... a few other aborted attempts. 2.1 to 2.3

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      0 as IsBlocked,
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

CREATE VIEW [dbo].[My_View2_5]
AS
WITH xoutput AS (
    SELECT ci.[Data],  ci.[ContentType_id], ci.ID
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          )
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,
      0 as IsBlocked,
      1 as IsPublished,
      [ContentType_id], ID
FROM xoutput
    CROSS APPLY (SELECT CONVERT(xml, [Data]).query('.')) AS T(x)
        CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1
GO

/*
SELECT *
FROM [dbo].[My_View]
GO
SELECT *
FROM [dbo].[My_View2_4]
GO
SELECT *
FROM [dbo].[My_View2_5]
GO
*/


Export/import data from Excel into a table with queries I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it.

I've come across some scenario's where I would have an Excel document with 70,000 rows and about 10-12 columns imported into SQL Server (a table), make use of it to compare/insert/modify existing data in other tables. I'm currently using the manual feature available in GUI (right click >> Tasks >> Import) to import and export when needed; however, I've got informed by one of my colleagues that the same procedure can also be done through script/queries.

Can someone let me know what is the correct way of writing a query to import XLS, XLSX, CSV format files into a table?

P.S.: I've also tried reading some posts about OPENDATASOURCE and OPENROWSET where I've not been successful, always getting some errors about which I have no clue. So, looking to see if there are any other ways, and keen to learn their procedure.

I've used Openrowset a number of times for this task.

This code will create a table in SQL.

SELECT * INTO EXCEL_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Sheet1$]');

Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. That way you can control the column data types.


Dimensional Modeling of a Loan Approval Process and Fact Grain(s) Context:

I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out:

Time(day)
Account
Opportunity
Channel

And I and getting to the modeling of fact table(s) around the loan approval and credit decisioning process. An opportunity will get one or more "Credit Reviews" which have a series of steps they go through before arriving at a "Credit Decision". My instinct is to model the fact grain at the credit review step level or maybe the credit review level (or have one fact for each grain).

I'm working with some analysts who are used to using a “canned report”, one flattened table extracted from the source system that is at the opportunity level, and finds a "best credit review", and do a lot of their reporting from that.

I'm trying to explain my credit approval fact and how it would work and they think it's "overly complicated" and seem to want a fact table that is one-to-one with the opportunity, which is a dimension. And contains information on only the "best" credit review. In my mind that seems odd. I haven't run across a fact table that is at the same granularity (one-to-one) with a dimension. Have you ever heard of this? It feels like they are just trying to reproduce what they are used to, a non-dimensionally modeled data mart table at the opportunity level.

I'm trying to explain the utility of having a fact table that is at a higher granularity than they are used to in order to support many questions about the data rather than one, but I seem to be running into a wall getting this concept across. I’m not sure who is right here.

My question is this: Is it every appropriate, say in this situation, to model a fact that has the same cardinality as a dimension (one-to-one) with a core dimension? What would you recommend for this situation?

Dimensions that are 1-1 with facts are not unheard of. The problem with such dimensions is their size, which won't get any smaller just because you introduce a fact table that's even more detailed. Whether you truly need a dimension at the grain of an individual opportunity is a question I'd think about.

You don't mention measures in your question at all, and they're rather important for identifying the fact tables. For instance I'd expect the "Opportunity" (though I'm accustomed to calling it a Loan application) to have the amounts requested and approved. How would you calculate the total amount approved and the number of applications approved/total in the model you're thinking of?

I find it weird that you're attemting to convince the potential users that they need more functionality than they think they do; in my experience it's usually the other way around. How much domain experience do you have? If you're sure the use-cases you've come up with are real, you should be able to find a stakeholder who agrees with you. If you can't, you probably should postpone implementation until someone needs it.

(Unless your architecture is such that adding a more detailed fact table will require unreasonably large effort if not done upfront, e.g. you're planning to ETL source data directly to the dimensional data mart, throwing away all the data that doesn't fit  - but that's an argument against that architecture, not against the "Opportunity" fact table, IMO.)

An "accumulating snapshot" fact table is commonly mentioned when modelling a process in a dimensional paradigm (1, 2).


Mariadb: Cannot decrease max_connections to 1, ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '1' For doing updates on databases I reduce the max_connections variable to 1 (the remaining for root access). I found it the fastest solution to lock the database for other users during updating. This worked fine for me till yesterday. And I cannot find any restriction why it could be forbidden to set the value to 1.

within my update script I used the command:

mysql -u root -p -e "SET @@global.max_connections = 1;"

and I get the errot:

ERROR 1231 (42000) at line 1: Variable 'max_connections' can't be set to the value of '1'

I tried to find the minimum number I'm allowed to set:

MariaDB [(none)]> show variables like 'max_conn%';

-> | max_connections    | 151   |

MariaDB [(none)]> set @@global.max_connections=1;

ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '1'
MariaDB [(none)]> set @@global.max_connections=10;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set @@global.max_connections=9;

ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '9'

MariaDB [(none)]> show variables like 'max_conn%';

-> | max_connections    | 10    |

And I'm the single user on that database:

show status where `variable_name` = 'Threads_connected';

-> | Threads_connected | 1     |

This sounds like a regression as the MariaDB documentation for max_connections says the range is 1 to 100000.

I can do SET GLOBAL max_connections=1; in MySQL 5.7 and 8.0, but not in MariaDB 10.2 and 10.3 (tested on 10.2.21, 10.3.10 and 10.3.12). I get a warning rather than an error, though:

| Warning | 1292 | Truncated incorrect max_connections value: '1' |

And the value gets set to 10:

SHOW GLOBAL VARIABLES LIKE 'max_connections';
-> | max_connections | 10    |

(I've taken the liberty of reporting the issue here.)

EDIT

The documentation has been updated to say the range is now 10 to 100000, and the bug report has a comment from Monty himself explaining the reason for the change - it was deliberate, so not a bug. (Basically, it was done to prevent unexpected behaviour/results.)


How to setup continuous DB replication from on-prem MS SQL server to PostgreSQL on AWS RDS without reconfiguring source machine? I am looking for a way to set up continuous data replication between an on-prem Microsoft SQL Server to AWS RDS PostgreSQL. The source table is updated once every 24 hours. The challenge here is that I don't have any control over the source (on-prem) DB. All I have is a DB user that I can use to query the database. In a nutshell, I am looking for the following requirements:

The source machine should not be modified in any way (i.e. applying configurations to the source machine)
Cheap (do not require a migration server to run 24/7) and easy to manage (set up once and forget it)
Ideally, the solution should only migrate the changes done to the source DB table such as adding/removing records. In other words, avoid emptying the target table and re-filling it with the source table records (that is what the AWS DMS "Full-Load" would do)

I was looking at AWS DMS Ongoing Replication but it requires changes to the source machine so I had to dismiss that solution (see Using a Microsoft SQL Server database as a source for AWS DMS). Also, that solution would require a "Replication Instance" to run 24/7.
Is there anything else I should be looking at that satisfy the above requirements? Or my only option would be to go with "Full-Load" AWS DMS migration which would be potentially costly solution provided the frequency of updates to the source database table?
I would appreciate any thoughts on the problem, please.

This is a very rough thought process on one thing you could do (and I'm sure there's ways to fine tune this idea, which I'll continue to update as I think of them) but you can leverage a couple of the things MS SQL Server and AWS offers to meet most of your needs.
In order of events, this is the steps you can take:

Scale up an additional Microsoft SQL Server possibly using SQL
Server Express Edition: This server will house a job that will
handle pulling the data from your primary MS SQL Server and
comparing it to your AWS PostgreSQL instance, to generate the changes.
There are a lot of limitations with SQL Server Express Edition, so
you'll have to read up on it, but it's a free edition of Microsoft
SQL Server so it checks off the cheap checkbox if you are able to
leverage it. If you can't, then a somewhat cheap alternative would
be to use SQL Server Standard Edition on a two core server. Make sure to install the  Integration Services component during the installation of SQL Server (regardless of which edition you choose).
You can choose to scale this up in AWS, a VM, or however you see
fit. (In AWS you can create a job that turns this server on and off
as needed.)

Create a SQL Server Integration Services project: This will be compiled into an SSIS package that will live on the server job server above. In the SSIS project you'll be able to connect to your primary Microsoft SQL Server, pull in the data from the source table, and then pull in the destination copy of the table on your AWS PostgreSQL instance, and then compare the two datasets for changes. How you implement your comparator is up to you, but one thing in Microsoft SQL Server that can be useful is a function called HASHBYTES(). You can hash the whole row and compare it's hash to your AWS PostgreSQL instance's hashed rows. (I'm not sure if there's an equivalent function in PostgreSQL, but if there's not then you can import your PostgreSQL's table data into your MS SQL job server first and then compare both datasets with HASHBYTES.) Then you'll be able to output the resultant rows that've changed to a destination. Unfortunately I don't think you'll be able to directly output them to your AWS PostgreSQL instance, so the next step will be an intermediary step.

Add a step to your SSIS project to output the rows that changed to either another staging table on your job server or to a raw file like a CSV. (This will be up to you to decide how to implement, as a staging table means you'll need to leverage a more complex process in AWS to pull in the data. I think a CSV is the simpler route, you just need to ensure you setup a file share for the file to be saved to.)

Deploy the SSIS project as a package to your Microsoft SQL job server. Then use the SQL Agent to schedule a job that runs daily to execute the SSIS package.

Create an S3 bucket (or alternative AWS storage solution) in your AWS cloud for where the raw file from step 3 will be copied to.

Create a scheduled job that will migrate your raw file from step 3 to your S3 bucket in step 5. This again will be up to you and can be accomplished many ways such as the following examples:

AWS SFTP Managed Service
AWS REST API
AWS SDK using your programming language of choice
AWS CLI or any other alternative they offer
An alternative non-AWS solution (programmatically, etc)

Leverage the AWS SDK, or alternative services they provide to create an AWS job that uploads the data from the file you imported into your S3 bucket into your PostgreSQL instance. I'd recommend importing it to a separate staging table in PostgreSQL first (but this up to you how you end up implementing it). (You might be able to roll step 6 into the same job you create for this step.) See this AWS doc for more info: Importing data into PostgreSQL on Amazon RDS

After importing the data above into a staging table in PostgreSQL, you can (either in the same job or a new job), delete from your destination table the records with the same primary keys as the records in your staging table. Then insert the records from your staging table into the destination table. (Although this is technically doing a DELETE / INSERT on the records that were updated, it's still better than truncating the entire table and inserting everything, so I think it should suffice your requirement of not doing a full load.)

Implementing this process is going to be a lot of work (more than it took to read :), so you might consider just paying an AWS partnered company to implement a solution for you if it's too much time to implement, but that's for you to decide.
One other thing that might be useful to be aware of in Microsoft SQL Server is Change Data Capture (CDC). This is a process in SQL Server that logs changes to tables (essentially maintains a transaction log table of every table in your database). It might be useful in conjunction or in place of SSIS and is the main component of Microsoft SQL Server that AWS's out of box DMS uses to migrate data from SQL Server.
Another similar component in Microsoft SQL Server is called Temporal Tables which have similar functionality as Change Data Capture (but are probably less used).
If you have any questions, I'm happy to elaborate on any of the steps in my rough solution above.


Bag union operation in relational algebra I have difficulty in understanding how does the bag union operates in relational algebra?

Here, 1) has a schema (X, Y, A)
2) (Y, A)
3) (X, A)
4) (Y, A)
so how will output look like after union operation on 1, 2, 3, and 4?
In my understanding for union operation schema should be the same but here 2 output of 4 has a different schema.

ANS: C

After giving this question little thoughts, I found, I was missing the projection part which means the only attribute A is considered for the union. So, basically all we have to do is grouping using GAMMA and then select attribute A and perform union.

Derivation:

\gamma(x,y,avg(z)) => {(2,6,5),(5,6,3),(9,0,4)}

renaming avg(z) to A and projecting that column results in:

{5,3,4}

Remaining 2,3 and 4 with the same method then becomes:

{11,8}
{5,1,1}
{5,9}

So we end up with the bag:

{5,3,4, 11,8, 5,1,1 ,5,9}

Clearly statement a, b and d is False and statement c is True


Saving your data differently would make that query trivial. Namely: one number per row. Something like:

results(Date, Time, Rank, Number)

where Rank would be 1 (or zero) for the first number, 2 for the second, etc. (Only if the order has importance, drop the rank if it doesn't.)

Then your query boils down to (ignoring reserved identifiers):

select number, count(*)
from results
-- where date = ...
group by number
order by number;

With your current schema, you can still get it fairly easily with a union:

select number, count(*)
from (
  select NumberOne as number from Results
  union all
  select NumberTwo as number from Results
  union all
  select NumberThree as number from Results
) AllNumbers
group by number
order by number;

(Or use a with AllNumbers as (...) CTE.)

When it comes to extending your application to more than three numbers, it's pretty clear which solution wins - you really don't want to add more columns for every type of draw, or produce a huge list of unions.

There's one issue I see though: a lot of data duplication for each row. More so if you need to add a column with the "type" of draw (to distinguish between "pick 3", pick 4" or whatever types of game you want).

You could mitigate that by only storing a "draw id", (optional) rank and number, and have another table where you store the "metadata" for each draw id. There probably are intermediate schemes that would fit your application better - depends on what trade-offs you can/want to make.


Restore Database clarifications I have a 1 TB database. I started restoring the database(full backup) with No recovery and replace .In between there is a windows msi installer which installed .net framework 4.6.1 needed a reboot of machine and server got rebooted. After reboot now the database is in restoring mode. But I feel restore is definitely not successful. So now my plan is :

Restore Database DBNAME with Recovery

does not work in this case.

Take the database offline.
Delete the database and re start the restore of full backup again with no recovery and replace.

Please advice if this approach is correct.Or what I am thinking is wrong.

Since database server was rebooted while restore process was still going on so the restore process did not completed successfully.Restoring database with recovery is not going to work it will flash an error. What you have to do is

Open new query window, connect to master database and drop the database which is in restoring state by drop database db_name;
Now start the restore process again.

You should have waited to restart the machine after it prompted for reboot. I am sure it would have prompted but you would have selected for reboot then only it went ahead and rebooted. Avoid rebooting SQL servers when restore process is going on this would disrupt restore process and you would have to start all over again, which is waste of time.


sql query to compare column name to column data What I am looking for is how I could go about running a query to select

SELECT 'text' FROM 'some_table'
WHERE 'text' = 'some_column_name'

I have looked and searched, and may be searching with the wrong information, but I have been unable to find anything of the sort.

Any help would be greatly appreciated.

The question is somewhat unclear, but I'll assume that you are trying to get the values of one particular column.  And looking at the column names of a particular table, you want a list of all columns whose name is the same as the returned value. Then something like below might be a good start.

SELECT
    T.ColumnName, -- will show the value of the column
    C.Object_name([object_id]), --will show the name of the table
    C.name -- will show the name of the column that matched the column value
FROM
    SchemaName.TableName T
INNER JOIN
    Sys.Columns C
ON
    T.ColumnName= C.name
WHERE
    [object_id] = OBJECT_ID('SchemaName.TableName')

If you want to compare the value of the column to the column names in a different table or any tables, you simply need to change or delete the WHERE clause.


I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea? Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as the job scheduler in pretty much every case, rather than the native Windows Task Scheduler.

From my perspective, the SQL Agent has a number of advantages over the native Windows Task Scheduler:

Remote (from my workstation) start/stop/monitoring of tasks
Shared schedules (rather than each task on its own)
Multiple steps and control flow
Different types of tasks
Alerts on failure/completion
Can be configured to act as different users
(Moderately) descriptive error messages, rather than just an error code

However, I can't escape the feeling that this is bad practice - the SQL Agent should be reserved for just database-related tasks, and I should leave OS level tasks running in the Windows Task Scheduler, despite my dislike of its usability.

Is it okay to rely on the SQL Agent in this way? If not, should I consider a third-party Windows task scheduler to get some of the functionality I'm looking for?

At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers.

While this is largely subjective (and it's going to be hard to derive a "correct" answer in this format), I don't think there's anything inherently wrong with this approach other than it becomes a single point of failure.

That might not be relevant if all of the tasks interact with, or depend on, the database server being up and the SQL Server services running (in our case, it did).

Oh, and you need to add in error handling for cases where the server where the task tries to run is not up - something you wouldn't have to do if the task were set up on that server.


SQL Server user and role backup I'm trying to figure out how to backup the users, roles, and what permissions they have on a SQL Server instance.  I need to be able to restore the users, roles, and permissions without changing anything else (so backing up the entire database is out).  I'm not trying to figure out what permissions are required to be able to make a backup, or restore one.
The reason I'm looking into this:
I need my QA environment to retain its current users, roles, and permissions.  I also need to refresh the QA instance (data and objects) based on my prod instance.  Our QA team has some elevated permissions that we don't want them to lose after the QA environment is refreshed.  The elevated permissions are not documented, and the person who granted them is no longer with the organization.  Nobody is confident that we know what they all are.

The elevated permissions are not documented, and the person who granted them is no longer with the organization. Nobody is confident that we know what they all are.

You'll have to fix that.
Sart with dumping the easy stuff, like the explicit permissions, role memberships, and members of the QA role.  eg
--qa role explicit permissions
select *
from sys.database_permissions p
where grantee_principal_id = user_id('qa')

--roles that the qa role is added to
select r.name role_name
from sys.database_principals r
join sys.database_role_members rm
  on rm.role_principal_id = r.principal_id
where rm.member_principal_id = user_id('qa')

---members of the qa role
select user_name(rm.member_principal_id) qa_member
from sys.database_principals r
join sys.database_role_members rm
  on rm.role_principal_id = r.principal_id
where rm.role_principal_id = user_id('qa')

And use those to generate a permissions script that is applied after restore to QA.  But keep a copy of your old QA database online so you can find stuff you missed.  EG individal team members with directly-granted permissions, etc.


InnoDB - How to get top locked tables and rows which are locked I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?

Just search for 'lock'

mysql> pager grep -C 3 -i lock
PAGER set to 'grep -C 3 -i lock'
mysql> show engine innodb status\G
... results ...
1 row in set (0.00 sec)

mysql> \n
PAGER set to stdout
mysql>


Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table I need to write a stored procedure to receive 4 numbers and insert them into a table. This is what I've developed so far:

Declare
@1  Int = 10,
@2  Int = 20,
@3  Int = 30,
@4  Int = 40

Create table #Temp(Num int)
Declare @I char(1) = 1

 While (@I <= 4)
   Begin

         Insert Into #Temp
         Select @I

     SET @I +=1
   end

Select * from #Temp
Drop table #Temp

I know that I can directly and statically insert the inputs into the table but I just want to know is there any better way to do that? I wanted to use a while statement but the problem is the numbers for (I) variable are being inserted into the table!! I mean the output is 1,2,3,4; what I want is 10,20,30,40.

You could also use a table valued parameter type in the stored procedure and pass numbers through this tvp.

Create the type

CREATE TYPE GetNumbers AS TABLE
( Numbers INT );
GO

Create the procedure

CREATE PROCEDURE dbo.InsertNumbers
@GetNumbers GetNumbers READONLY
AS
SET NOCOUNT ON;

CREATE TABLE #Temp(Num int);
INSERT INTO  #Temp(Num)
SELECT Numbers
FROM  @GetNumbers;
SELECT * FROM #Temp;
DROP TABLE #Temp;
GO

Inserting into temp table is not really needed here, only done to keep it the same as the question.

Fill up a variable with data and call the procedure

/* Declare a variable that references the type. */
DECLARE @GetNumbers AS GetNumbers;

/* Add data to the table variable. */
INSERT INTO @GetNumbers (Numbers)
VALUES(10),(20),(30),(40);

/* Pass the table variable data to a stored procedure. */
EXEC InsertNumbers @GetNumbers;

The example used and more on tvp's here


Slow execution of a subquery with 0 matches In MySQL, I have two basic tables - Posts and Followers:

CREATE TABLE Posts (
  id int(11) NOT NULL AUTO_INCREMENT,
  posted int(11) NOT NULL,
  body varchar(512) NOT NULL,
  authorId int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY posted (posted),
  KEY authorId (authorId,posted)
) ENGINE=InnoDB;

CREATE TABLE Followers (
  userId int(11) NOT NULL,
  followerId int(11) NOT NULL,
  PRIMARY KEY (userId,followerId),
  KEY followerId (followerId)
) ENGINE=InnoDB;

I have the following query, which seems to be optimized enough (based on the EXPLAIN output):

  SELECT p.*
    FROM Posts p
   WHERE p.authorId IN (SELECT f.userId
                          FROM Followers f
                         WHERE f.followerId = 9
                      ORDER BY authorId)
ORDER BY posted
   LIMIT 0, 20

When followerId is a valid id (meaning, it actually exists in both tables), the query execution is almost immediate. However, when the id is not present in the tables, the query only returns results (empty set) after a 7 second delay.

Why is this happening? Is there some way to speed up this query for cases where there are no matches (without having to do a check ahead of time)?

The ORDER BY inside the subquery is rather useless if you are looking for userid.

You should do two things

First rewrite the query (remove the ORDER BY authorId)

SELECT p.*
    FROM Posts p
   WHERE p.authorId IN (SELECT f.userId
                          FROM Followers f
                         WHERE f.followerId = 9
                      )
ORDER BY posted
   LIMIT 0, 20;

Then, add this index

ALTER TABLE followers ADD INDEX followerId_userId_index (followerId,userId);

This should speed things up, even for empty set results.

Give it a Try !!!

You can also do the query using INNER JOIN

SELECT p.*
   FROM (SELECT userId FROM Followers WHERE followerId = 9) f
   INNER JOIN Posts p ON f.userId = p.authorId
ORDER BY p.posted
LIMIT 0, 20;


Mongo E11000 duplicate key error collection after mongo 3.4 to 3.6 upgrade I have a Mongo replica set on Ubuntu with three members configured for my information system and am having issues when I try to upgrade MongoDB from version 3.4 to 3.6

After setting the "featureCompatibilityVersion" to 3.4 on the master node and checking it on the two slaves, I started upgrading the slaves one by one (I gracefully shut the DB down with db.shutdownServer(), update the packages using apt and start the mongod service). That all went fine and the slaves transitioned back to secondary states. I then shut the master down, repeated all the steps for the upgrade from the official guide (https://docs.mongodb.com/manual/release-notes/3.6-upgrade-replica-set/) and tried starting the mongod service again.

However, while I was upgrading the master node, the slave that was elected as a new master crashed with the error:

  [rsSync] Fatal assertion 40454 DuplicateKey: E11000 duplicate key error collection: admin.system.users index: user_1_db_1 dup key: { : "john", : "admin" } at src/mongo/db/system_index.cpp 134

I didn't notice this and when the old master started up again it too crashed with the same error. In my frantic trying to get the members up again, the third member had also been elected as a master for a short time before crashing with the same error.

When I managed to get the first member up again as a standalone, I checked the system.users collection and found that there were indeed two users with the same username for the admin database (I redacted the parts with ...):

{ "_id" : ObjectId("5...8"), "user" : "john", "db" : "admin", "credentials" : { "MONGODB-CR" : "f...c" }, "roles" : [ { "role" : "read", "db" : "admin" } ] }

and

{ "_id" : "admin.john", "user" : "john", "db" : "admin", "credentials" : { "MONGODB-CR" : "f...c" }, "roles" : [ { "role" : "read", "db" : "admin" } ] }

My question is how is that possible? Shouldn't mongo prevent someone from adding an identical user? Or did mongo try to change that collection after the upgrade and failed? I am using authSchema version 3 if it makes any difference. Thanks!

First, per the documentation you should always upgrade the Secondaries first, finishing with the primary node as per MongoDB upgrade process.

Second, since 3.6.13, MongoDB added a field userId adding a UUID into any user created to avoid _id collision such as what you seem to have encountered. See https://jira.mongodb.org/browse/SERVER-38984.


MySQL - How to check whether all values of a given SELECTed row are equals? Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows

COLUMN_A   COLUMN_B   COLUMN_C ... COLUMN_N
       1          2          4            7
       6          3          5            6
       5          5          5            5
       7          1          3            4
       3          3          3            3

I need a SELECT which should return

COLUMN_A   COLUMN_B   COLUMN_C ... COLUMN_N
       5          5          5            5
       3          3          3            3

SELECT
   column_a
  ,column_b
  ,column_c
  ,column_d
FROM mytable
WHERE column_a = column_b
AND   column_a = column_c
AND   column_a = column_d

ETC.

SQL Fiddle Link

My Notes

CREATE TABLE mytable
(
column_a int,
column_b int,
column_c int,
column_d int
);

INSERT INTO mytable VALUES (1,2,4,7);
INSERT INTO mytable VALUES (6,3,5,6);
INSERT INTO mytable VALUES (5,5,5,5);
INSERT INTO mytable VALUES (7,1,3,4);
INSERT INTO mytable VALUES (3,3,3,3);


Positional value issues with PostgreSQL crosstab I am running this query:

select *
              FROM crosstab (
                $$
                  select postcode::text, indicator::int, value::float
                  from indicator_data
                  where
                    (indicator = 35 or indicator = 36 or indicator = 37 or indicator = 38 or indicator = 39 or
                    indicator = 105 or indicator = 106 or indicator = 107 or indicator = 108 or indicator = 108) and date = '2017-04-01'
                  order by 1,2
                $$)
              AS ct (
                "postcode" text,
                "one_y" float,
                "two_y" float,
                "three_y" float,
                "four_y" float,
                "five_y" float,
                "one_l" float,
                "two_l" float,
                "three_l" float,
                "four_l" float,
                "five_l" float
                )

against the table:

indicator   postcode    date        value
35          NW3 3       2017-04-01  1000
105         NW3 3       2017-04-01  2
37          NW3 3       2017-04-01  2000
107         NW3 3       2017-04-01  3

postcode    one_y   two_y   three_y four_y  five_y  one_l   two_l   three_l four_l  five_l
NW3 3       1000    2000    2       3       NULL    NULL    NULL    NULL    NULL    NULL

instead of this expected result:

postcode    one_y   two_y   three_y four_y  five_y  one_l   two_l   three_l four_l  five_l
NW3 3       1000    NULL    2000    NULL    NULL    2       NULL    3       NULL    NULL

How can I fix the query to return the exact positions for the values in the temporary table like in the expected result?

UPDATE
I have added the pivotal query in this change:

select *
                  FROM crosstab (
                    $$
                      select postcode::text, indicator::int, value::float
                      from indicator_data
                      where
                        (indicator = 35 or indicator = 36 or indicator = 37 or indicator = 38 or indicator = 39 or
                        indicator = 105 or indicator = 106 or indicator = 107 or indicator = 108 or indicator = 108) and date = '2017-04-01'
                      order by 1,2
                    $$,
                    $$
                    SELECT g FROM generate_series(1,10) g
                    $$)

but it doesn't help

It's not clear to me what is the relation between one_y and indicator 35 but assuming that they have a 1 to 1 correspondence to the where part of your first query I can came up with a solution.

Clearly you need the updated two parameter version of crosstab function.
What you are missing is the right category values.

Try this as your second parameter...

select * from  (
            values  (35) , (36) ,(37) , (38) , (39) , (105) , (106) ,(107) , (108) , (109)) a

The final query would be...

select *
          FROM crosstab (
            $$
              select postcode::text, indicator::int, value::float
              from indicator_data
              where
                (indicator = 35 or indicator = 36 or indicator = 37 or indicator = 38 or indicator = 39 or
                indicator = 105 or indicator = 106 or indicator = 107 or indicator = 108 or indicator = 108) and date = '2017-04-01'
              order by 1,2
            $$,
            $$
            select * from  (
            values  (35) , (36) ,(37) , (38) , (39) , (105) , (106) ,(107) , (108) , (109)) a
            $$
         )
          AS ct (
            "postcode" text,
            "one_y" float,
            "two_y" float,
            "three_y" float,
            "four_y" float,
            "five_y" float,
            "one_l" float,
            "two_l" float,
            "three_l" float,
            "four_l" float,
            "five_l" float
            )

Resulting in ...

 postcode | one_y | two_y  | three_y | four_y | five_y | one_l | two_l  | three_l | four_l | five_l
----------+-------+--------+---------+--------+--------+-------+--------+---------+--------+--------
 NW3 3    |  1000 | [null] |    2000 | [null] | [null] |     2 | [null] |       3 | [null] | [null]
(1 row)

Regards


Dacpac deployment via sqlpackage, “DropObjectsNotInSource=True” is trying to drop .mdf & .ldf Powershell deployment script:

Executing: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:********.dacpac /Profile:Release.Publish.xml /TargetConnectionString:server=********;database=********;uid==********;pwd=********;app=********;timeout=900 /p:IgnoreAuthorizer=True /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:IgnoreUserSettingsObjects=True /p:BlockOnPossibleDataLoss=False /p:TreatVerificationErrorsAsWarnings=True /p:GenerateSmartDefaults=True
Error *** The object [********] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Error *** The object [********_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Info Initializing deployment (Complete)
Info Analyzing deployment plan (Start)
Info Analyzing deployment plan (Complete)
Info Updating database (Start)
Info Starting ********.sql
Info Update complete.
Info Updating database (Complete)
Info Successfully published database.

Release.Publish.xml:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <CreateNewDatabase>False</CreateNewDatabase>
    <BackupDatabaseBeforeChanges>False</BackupDatabaseBeforeChanges>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <NoAlterStatementsToChangeCLRTypes>False</NoAlterStatementsToChangeCLRTypes>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DeployDatabaseInSingleUserMode>False</DeployDatabaseInSingleUserMode>
    <DeployScriptFileName>Release.sql</DeployScriptFileName>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <UnmodifiableObjectWarnings>False</UnmodifiableObjectWarnings>
  </PropertyGroup>
</Project>

Why is this trying to drop mdf and ldfs?

From Project-Oriented Database Development using Command-Line Tools: I can tell the fix would possibly be to use DoNotDropObjectType to make DropObjectsNotInSource behave more responsibly, if so how?

Honestly I'm confused why this is even an issue in the first place.

If your mdf and ldf files are in a non-default location (or have a non-default prefix / name), you need to add that information to the SSDT project.  This will help the schema compare operation (that occurs when you do a publish) to see that the files are in the right place and don't need to be dropped / recreated.

Right-click your project -> Add New Item -> File Group File

You'll get something like this:

/*
Do not change the database path or name variables.
Any sqlcmd variables will be properly substituted during
build and deployment.
*/
ALTER DATABASE [$(DatabaseName)]
    ADD FILE
    (
        NAME = [SqlFile1],
        FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_SqlFile1.ndf'
    )

Modify that script so that it points to the desired location of your primary data file (mdf) and your deploy should go much more smoothly.

Note that $(DefaultDataPath) is set by the settings of the target instance:

And $(DefaultFilePrefix) is set by the "TargetDatabaseName" in your publish.xml file.


Latitude and Longtitude Distance Calculation I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in select query. There is my stored function in the following segment:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `CALCULATE_DISTANCEE`(Lati varchar(20),Longi varchar(20),MyLat decimal, MyLon decimal) RETURNS int(11)
BEGIN
    declare Lat decimal(12,12);
    declare Lon decimal(12,12);
    declare RadiusOfWorld decimal(12,12);
    declare DistanceLat decimal(12,12);
    declare DistanceLon decimal(12,12);
    declare A decimal(12,12);
    declare R decimal(12,12);
    declare D decimal(12,12);
    declare DistanceAsMeter decimal(12,12);
    select CAST(Lati as Decimal(12,4)) into Lat;
    select CAST(Longi as Decimal(12,4)) into Lon;

    select 6378.137 into RadiusOfWorld;
    set DistanceLat = (MyLat - Lat) * PI() / 180;
    set DistanceLon = (MyLon - Lon) * PI() / 180;

    select SIN(DistanceLat/2) * SIN(DistanceLat/2) + COS(Lat*PI()/180) * COS(MyLat*PI() / 180) * SIN(DistanceLat / 2) * SIN(DistanceLat / 2) into A;
    Select (2*ATAN2(SQRT(A),SQRT(1 - A))) into R;
    select (RadiusOfWorld * R) into D;
    select D*'1000' into DistanceAsMeter;
RETURN DistanceAsMeter;
END

And also I am calling the function like this:

select *,`brain_db`.`CALCULATE_DISTANCEE`(stop.Stop_lat,stop.Stop_lon,'-157.818079','21.260340') AS Distance from stop;

Okay, few notions about your procedure

DECIMAL(12,12) means 12 digits, where ALL 12 are behind a
floating point (so basically, 0.*). To see an example, execute
SELECT CAST(23.45 AS DECIMAL(12,12)); and see what output does it
give you.
I suggest that you change DECIMAL definitions to DECIMAL(9,6)
which means 9 digits, where 6 are after the point. I think that
would suffice for your calculations.
Why are you escaping all your numbers?
Why are you using SELECT xx INTO yy for setting variables? Why
don't you just use SET yy = xx;?
Your function returns INT, which means that it will cancel
everything after the floating point. Is this what you need?

If you change all your decimal definitions properly (even the input ones), I think that your function should return proper result then (altough without the floating point). After that unquote your numbers (there is no need to quote them, even in your select query).

Let me know how it works out.


Selecting the same column from Different rows Based on Different Criteria My table creates a row for each customer name for the unique customer number.

CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.

Given this table and data:

CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

You can apply a simple PIVOT:

SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
  FROM #src AS c
  PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
  ORDER BY Number;

Results:

number   Owner1         Owner2
------   ------------   ------------
12345    Mickey Mouse   Minnie Mouse
45678    Donald Duck    Daphney Duck
245678   Pluto Dog

I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.


SQL Server changes execution plan - Part 2 Previous question: SQL Server changes execution plan
We are using SQL Server 2014 Developer Edition.
SQL Server changes execution plan on identical query and the same database and SQL Server (I checked several times).
If I connect with Management Studio from my dev computer with AD account, it takes 18 seconds to finish the query (most of the time). If I go with remote connection to server and execute query in Management Studio it takes 2 seconds to finish. On my colleague's machine it takes 2 second with Management Studio and 18 if he connects with MVC application (Ado.Net).
Things we did

Restart SQL Server
DBCC FREEPROCCACHE
sp_updatestats
try with different users AD and SQL users

Slow execution plan

Fast execution plan

Query
SET ANSI_NULLS ON
GO

SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @ccode varchar(500)
DECLARE @PageSize int
DECLARE @PageNumber int
SET @ccode = '%skd%'
SET @PageSize = 20
SET @PageNumber = 1

SELECT DISTINCT CLASSIFICATIONS.ABBREV_TEXT,
            CLASSIFICATIONS.TITLE_TEXT,
            CLASSIFICATIONS.CFN_UID
    FROM klasje.dbo.CLASSIFICATIONS
    inner join Klasje.dbo.CFN_VERSIONS on CLASSIFICATIONS.CFN_UID = CFN_VERSIONS.CFN_UID
    inner join Klasje.dbo.VERSION_CATEGORY_XREFS on CFN_VERSIONS.CVN_UID = VERSION_CATEGORY_XREFS.CVN_UID
    left outer join Klasje.dbo.VELJAVNOST on CFN_VERSIONS.LIFE_CYCLE_CODE = VELJAVNOST.RV_LOW_VALUE
    WHERE CATEGORY_CODE like @ccode OR DESCRIPTOR_TEXT like @ccode OR DEFINITION_TEXT like @ccode
    ORDER BY ABBREV_TEXT
    OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY

Both execution plans on gist.
SELECT @@OPTIONS returns 5496 in in both cases (slow and fast).

The most likely explanation is that your sessions have different settings. SQL Server has various session settings that can affect the execution plan selected (and the results!)
The values for these settings can depend on how you connect to SQL Server, since different tools set the options different ways when they connect, and some (like SQL Server Management Studio) allow you to override the defaults as well.
For example:

The image above is reproduced from Erland Sommarskog's definitive article on this topic:
Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
The whole thing is well worth reading, but you should definitely read the section titled, "The Default Settings"
If you make sure all the settings have the same value on all connections, you should get the same execution plans.
For maximum compatibility with features like indexed views, you should ensure your settings are as follows:

Many of these settings are maintained for backward compatibility only. It is strongly recommended you set them as shown in the table above, or use a tool that sets them the right way automatically.
Books Online references:

SET Statements (Transact-SQL)
SET ANSI_NULLS (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)
SET ANSI_WARNINGS (Transact-SQL)
SET ARITHABORT (Transact-SQL)
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
SET NUMERIC_ROUNDABORT (Transact-SQL)
SET QUOTED_IDENTIFIER (Transact-SQL)
Create Indexed Views

Update after plans were provided
The slow plan includes:
CardinalityEstimationModelVersion="70"

...whereas the fast plan says:
CardinalityEstimationModelVersion="120"

So the explanation is that one of you is using the original cardinality estimator, and other is using the new SQL Server 2014 CE. The difference in estimated row counts is enough for the new CE to choose a parallel execution plan. Under the original CE, the estimated cost for the serial plan is below the cost threshold for parallelism.
As to why different estimators are being used, I would guess that you have different context databases when the statements are run. One where the compatibility level of the database defaults to the new CE, and one where the original CE is used. The database you are "in" when the query executes determines the CE model, not the database(s) used in the query.
For example, you may have different default databases associated with your logins. If you USE Klasje; before running the statements, both connections should use the same CE model.
Final update: it turned out the target database was indeed set to an older compatibility level. Running the query with master as the context database produced the better plan. Be aware that changing to use the new CE for all queries may cause regressions. You will need to test your workload before changing the database compatibility level in production.


How to propagate a grandparent relationship throughout a tree? I've got a MySQL table with columns id and parent, wherein each row has another row as a parent, except for some special root rows with parent=0. There are no cycles or broken relations.

In order to cache which root row each row is associated with, I decided to add an ancestor column. My current plan is to build the tree in some other programming language, find all ancestors recursively and then update the table.

However, I'm convinced that there's a clever way to do this in SQL, requiring not more queries than the depth of the three. Could someone help me design this?

There are ~1000 rows and the depth is ~5, so performance is not really an issue.

@Tim you have come to the right place.

It just so happens that I answered a question like this ( Find highest level of a hierarchical field: with vs without CTEs  ) back in Oct 24, 2011.

I wrote Stored Procedures that traverse a table with id and parent fields. I also included sample data.

However, you may not have to resort to the Stored Procedures.

Here is what I mean: Let's start with the sample data

USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;

To reach any grandparent, you could just perform a JOIN of the pctable against itself:

SELECT P.id,GP.parent_id FROM pctable P
INNER JOIN pctable GP ON P.parent_id = GP.id;

To reach any great-grandparent, you could just perform a JOIN of the pctable against itself twice:

SELECT P.id,GGP.parent_id FROM pctable P
INNER JOIN pctable GP ON P.parent_id = GP.id;
INNER JOIN pctable GGP ON GP.parent_id = GGP.id;

Since your depth is no more than three, doing these JOINs should be quite enough.