mysql: how to copy database and required dependencies I need to set up an automated copy of a single mysql database (not the whole DBMS). If I use mysqldump to dump and recreate a DB, the copy may be unusable because the user accounts need to also be copied. Triggers in the source DB for instance may be copied, but there's no guarantee that they will execute correctly in the new DB because the original triggers were designed to run as some particular user.

A couple of questions:

how can I automate copying of the user accounts and permissions?
is there a way, when copying a db to check whether all of its dependencies are met? (E.g. are all of the databases referred to by the stored procedures present?)

I understand that I can replicate the whole DBMS - this is overkill (and often not practical for hourly regressions)

Users and grants are stored in the mysql database, so you could insert rows directly. MySQL Workbench includes the mysqluserclone utility, which provides a safer way to copy user accounts and permissions from one server to another.

I don't know of any direct way to check dependencies, but INFORMATION_SCHEMA contains all the necessary metadata. Other utilities from MySQL Workbench such as mysqldiff and mysqldbcompare would probably help in determining when something is missing.


If calculation is negative it shows NULL, how to fix this? So I am validating count between source and target tables, if count is not the same it can go in positive or negative number, however when is negative it would show NULL. I used to know this but seems to me I have short memory. My code is below.
with cte as (
            select 'source' [object],count(contract_id) as total_count
            FROM [account].[dbo].[account] act
            left join [account].[dbo].[contract] cont
            on act.account_id = cont.account_id)
,cte1 as(
          select 'target' [object], count(contract_id) as total_count
          from [account].[dbo].[action]
          union
          select * from cte)
          select * from cte1
          union
          select 'diff' [object],
                 (select total_count from cte where [object] = 'source')
                -
                 (select total_count from cte where [object] = 'target')

Results is as follow
object  total_count
target  28402
source  28401
diff    NULL -- here it should be negative 1 (-1)

The problem is here:

(select total_count from cte where [object] = 'target')

There is not object = 'target' in the "cte" cte.
As the select returned null, the calculation also have to return null.
Try this: (changing cte for cte1)
with cte as (
            select 'source' [object],count(contract_id) as total_count
            FROM [account].[dbo].[account] act
            left join [account].[dbo].[contract] cont
            on act.account_id = cont.account_id)
,cte1 as(
          select 'target' [object], count(contract_id) as total_count
          from [account].[dbo].[action]
          union
          select * from cte)
          select * from cte1
          union
          select 'diff' [object],
                 (select total_count from cte where [object] = 'source')
                -
                 (select total_count from cte1 where [object] = 'target')


user security inheritance in SQL Server 2016 I'd like to build a bunch of new databases (several dozen) in SQL Server 2016.  However, I'd like all users to access a single database (called SECURITY for now), and apply security to that one database, where each user would be given access to a single schema for their department.  Does SQL Server 2016 allow inheritance of security, so you don't have to build those users in each database, and apply the same security in each?  Some of these schemas will just have views in which the definitions that read schemas and tables in other databases.

A few of these schemas in DEV_SECURITY will allow read/write and drop/create on views, tables, and stored procedures, for SECURITY, but also one other database.

Does this require me to build out these users, and their security in all databases, or can I simplify by just building them in SECURITY?

Does SQL Server 2016 allow inheritance of security, so you don't have
  to build those users in each database, and apply the same security in
  each? Some of these schemas will just have views in which the
  definitions that read schemas and tables in other databases.

What you are asking about is Ownership Chains.

In the simple case when we are within one database, when you give permissions on a view and the view is owned by the same principal that owns all the underlying tables, you just do nothing: a user will access the view without any permission on the underying tables.

In your case there are more than one database, and we are talking about
Cross-Database Ownership Chaining

You should read this paragraph in the article above to understand Potential Threats in case youenable crossdb ownership chains.

You can read more on it here: Enabling Cross-Database Access in SQL Server.

In brief, to make it work, in addition to the requirement to have the same owner you should enable both database to db chainig: ALTER DATABASE ... SET DB_CHAINING ON.

In this case you still can grant permissions only on your views without granting them on other database's tables, but you still should map your users in all databases of interest. Otherwise when accessing tables from other databases through view ownership chain breaks when you are leaving the database and you should give the permissions to all the underlying tables.


mysql - find duplicate sets of data grouped by foreign key I have a large mysql table (500,000 records). I need to find sets of data that have all the same attributes (KEY, NAME AND VALUE). Some sets have 20 attributes others have 397 per each KEY.
The table looks like this. KEY is a foreign key.

ID, KEY, NAME, VALUE
1   87   Color  Red
2   87   Size   Big
3   87   Weight 6

4   85   Color  Red
5   85   Size   Big
6   85   Weight 6

7   96   Color  Red
8   96   Size   Small
8   96   Weight 7

I'm trying to write a query where if KEY=87 it finds KEY 85 matches all attributes and values like KEY 87. There are 397 different attributes rather than just the 3 I show here. I can do this in php but it's sloppy and I want to learn more about mysql but I can't get my head around it. Appreciate any help in advance.

In a subquery, fetch all the attributes (I cringe at EAV, too).
GROUP_CONCAT(... ORDER BY ...) as foo to combine them into a single string.
In the outer query, SELECT GROUP_CONCAT(ky) ... GROUP BY foo HAVING COUNT(*) > 1 to get the duplicate kys.  (Please don't use the keyword "KEY".)

Give that a try.  If you get in trouble, show us SHOW CREATE TABLE and your failed attempt.  (Or abandon EAV.)

Some code (not quite mimicking the above prose):

SELECT `key`, CONCAT(`name`, '=', `value`) AS kv FROM tbl

should give you

key kv
87  Color=Red
87  Size=Big
...

Then...

SELECT `key`,
       GROUP_CONCAT(kv SEPARATOR '|' ORDER BY kv) AS kvs
    FROM ( -- the above query -- ) AS t1

should give you

key kvs
87  Color=Red|Size=Big|...
85  ...

Now...

SELECT kvs,
       GROUP_CONCAT(`key`) AS keys
    FROM ( -- second select -- ) AS t2
    GROUP BY kvs
    HAVING COUNT(*) > 1

should give you

kvs                           keys
Color=Red|Size=Big|Weight=6   87,85
...  (perhaps not including 96)

I suggest you debug my code the way I wrote it -- test the first query to see if it gives you the first set of output, then second, then third.


Show Dollar Amount By Name On A Month To Month Basis I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount.  Below is what I get from my query (subset of data)

Name  Value   Month
A     200     2016-01-01 00:00:00-05
A     400     2016-01-03 00:00:00-05

However, this is what I want (ONE entry for salesman and dollar amount for each month)

Name  Value   Month
A     600     2016-01-01 00:00:00-05

What syntax will get me these results?
This is my non-working syntax:

SELECT
na.name As "Name"
,SUM(jl.dollaramt) As "Income"
,DATE_TRUNC('MONTH',jl.dateofwork) As "Month"
from workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
WHERE CAST(jl.dateofwork As Date) BETWEEN '01/01/2016' AND '04/09/2016'
GROUP BY na.name, jl.dateofwork
ORDER BY na.name ASC

To tackle this problem, I did the following.

Created my tables (workprovided and people):

CREATE TABLE workprovided(wpid VARCHAR(20), dollaramount INTEGER, dateofwork timestamp);

CREATE TABLE people (personid VARCHAR(20), name VARCHAR(20));

Populated my tables:

INSERT INTO workprovided VALUES('A', 200, '2016-01-01 00:00:00-05');
INSERT INTO workprovided VALUES('A', 200, '2016-01-13 00:00:00-05');
INSERT INTO workprovided VALUES('A', 400, '2016-12-01 00:00:00-05');
INSERT INTO workprovided VALUES('A', 500, '2016-12-07 00:00:00-05');

and

INSERT INTO people VALUES ('A', 'Billy');

And then ran the following query, largely based on yours. You were close, just not grouping by the DATE_TRUNC - which means that your results were inconsistent.

SELECT
  na.name,
  SUM(jl.dollaramount),
  DATE_TRUNC('MONTH', jl.dateofwork)
FROM workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
GROUP BY na.name, DATE_TRUNC('MONTH', jl.dateofwork)

Result:

name;     sum;           date_trunc
-----     ----           -----------
Billy;    400;           2016-01-01 00:00:00
Billy;    900;           2016-12-01 00:00:00

Note that this sums them by the DATE_TRUNC('MONTH'...) part - which is, if I've understood you correctly, what you're looking for?

For extra credit, I came up with this - nicer formatting and sorting:

SELECT
  na.name,
  SUM(jl.dollaramount),
  CASE
    WHEN EXTRACT(MONTH FROM jl.dateofwork) = 1 THEN 'January'
    -- fill in the rest here...
    WHEN EXTRACT(MONTH FROM jl.dateofwork) = 12 THEN 'December'
  END AS Month
FROM workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
GROUP BY na.name, Month, EXTRACT(MONTH FROM jl.dateofwork)
ORDER BY na.name, EXTRACT(MONTH FROM jl.dateofwork) ASC

Result:

name;     sum;    month
-----     ---     -------
Billy;    400;    January
Billy;    900;    December


Slow Postgres Aggregate Query I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical input sets will contain between 10,000 to 1,000,000 rows. The following solution is the best idea i could think of. It works fine, but execution is rather slow. Because one of my requirements is to trigger the calculation quite often i need to optimize for execution time. Unfortunately it’s not an option to precalculate the values for the individual groups because every new measurement row affects the group size.

Schema Setup

create table devices
(
    id varchar not null
        constraint devices_pkey
            primary key
);

create table processes
(
    id        integer not null,

    constraint processes_pkey
        primary key (id, device_id),

    device_id varchar not null
        constraint fk_processes_devices
            references devices
            on delete cascade
);

create index processes_device_id_idx on processes (device_id);

create table measurements
(
    timestamp  timestamp with time zone not null,
    current    real                     not null,
    process_id integer                  not null,
    device_id  varchar                  not null,

    constraint measurements_pkey
        primary key (timestamp, process_id, device_id),

    constraint fk_measurements_processes
        foreign key (process_id, device_id) references processes
            on delete cascade
);

create index measurements_process_id_device_id_idx on measurements (device_id, process_id);

INSERT INTO devices (id) VALUES ('123');

INSERT INTO processes (id, device_id) VALUES (456, '123');

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 1000000)
)

INSERT INTO measurements (timestamp, current, process_id, device_id)
SELECT NOW() + (generate_series * interval '1 second'), generate_series * random(), 456, '123'
FROM numbers;

Query

select min(timestamp) as timestamp,
       case when sum(current) < 0 then -SQRT(AVG(POWER(current, 2))) else SQRT(AVG(POWER(current, 2))) end,
       456            as process_id,
       '123'          as device_id,
       index / 1000   as group_index
from (select timestamp,
             current,
             row_number() over (order by timestamp) as index
      from measurements
      where device_id = '123'
        and process_id = 456
      order by timestamp) as subquery
group by group_index
order by group_index;

db-fiddle: https://www.db-fiddle.com/f/uVTcf9Q2JDEkPf3S5hgvfB/2

Query plan visualization: http://tatiyants.com/pev/#/plans/plan_1569689658707

How to optimize the query?

Query Plan

Sort  (cost=100157.88..100158.38 rows=200 width=60) (actual time=927.340..927.402 rows=1001 loops=1)
  Sort Key: ((subquery.index / 1000))
  Sort Method: quicksort  Memory: 103kB
  ->  HashAggregate  (cost=100144.74..100150.24 rows=200 width=60) (actual time=926.828..927.036 rows=1001 loops=1)
        Group Key: (subquery.index / 1000)
        ->  Subquery Scan on subquery  (cost=0.42..77644.74 rows=1000000 width=20) (actual time=0.049..704.478 rows=1000000 loops=1)
              ->  WindowAgg  (cost=0.42..65144.74 rows=1000000 width=20) (actual time=0.046..576.692 rows=1000000 loops=1)
                    ->  Index Scan using measurements_pkey on measurements  (cost=0.42..50144.74 rows=1000000 width=12) (actual time=0.029..219.951 rows=1000000 loops=1)
                          Index Cond: ((process_id = 456) AND ((device_id)::text = '123'::text))
Planning Time: 0.378 ms
Execution Time: 927.591 ms

There is nothing obviously wrong with the plan, so there is no obvious large optimization to be done. You are fundamentally doing a lot of work, and it takes a lot of time.

I would probably start by taking a step back and looking at your business case.  Why do you need exactly this output?  Could you perhaps "need" something easier to optimize?  Like, something where chunks slide past in their entirety, rather than individual rows slowly passing from chunk to chunk on their way oblivion?  Or maybe where partitions are defined by predictable timestamp slices, rather than as having exactly 1000 rows per partition?

You didn't give your version, but your fiddle uses 9.5.  If I use the latest version and turn on JIT (just-in-time) compilation, I get about a 15% improvement over JIT off.

If I build an index suitable for index only scans, I don't get much improvement, it is sometimes faster and sometimes slower (it also interacts with JIT).  But if everything were not in already memory, it might give a huge improvement:

create index  on measurements (device_id, process_id, timestamp, current);

If you are looking for more complex solutions, you could write a process which will connect to the database and LISTENs for new INSERTs, then updates the data by sliding rows out of one partition and into the next one using incremental calculations, rather than recomputing each partition from scratch.  This is similar to how moving aggregate works, except that that only works between frames within a statement, not between statements like you need. This should be very fast.  For strict accuracy, this would depend on each inserted row committing in the same order of their timestamp column--that requirement is hard to guarantee in general, but might be easy for your exact situation.


Attributes from an hstore-column as separate columns in a view? Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs.

You could query that column as such:

SELECT name, attributes->'device' as device
FROM products
WHERE attributes->'edition'= 'ebook'

Can you create a view for this table where the keys/values appear as columns in the view instead of all in the hstore column?

Example: I have an hstore key where it might have the key of amenity.  I want to create a view showing the the ID and the amenity values in a new view.

Sure you can.

CREATE VIEW v_ebooks AS
SELECT products_id, attributes->'device' AS device
FROM   products
WHERE  attributes->'edition'= 'ebook';

SQL Fiddle demo.

But only for a static set of columns. Dynamic output of all possible attributes in an hstore column conflicts with the requirement that the return type has to be declared at creation time.

This later answer provides a completely dynamic solution (in two steps):

Dynamically convert hstore keys into columns for an unknown set of keys


Implications of setting READ_Write I am looking to update several users on READ_ONLY databases.
As the databases are used 24/7 globally, and maintenance windows have just past, I would like to do this during a low-activity moment (to be able to adhere to deadlines).

I'm currently considering using:

USE MASTER
GO
ALTER DATABASE SQL SET READ_WRITE
GO
USE SQL
GO
EXEC sp_addrolemember N'db_datareader', N'USER'
GO
USE MASTER
GO
ALTER DATABASE SQL SET READ_ONLY
GO

However I want to:

Guarantee this doesn't hang indefinitely
Be sure nothing can be written to the database during this action

As I can't put these commands in a transaction, and I don't have an application server handy on the DEV or STG environments to test the changes beforehand, is there any documentation about changing read_only states for single actions? (I did test the T-SQL statements, and they work)

Also, as this requires an exclusive lock on the database, am I even likely to get my statement through?

I'm inclined to just put forward that this will have to wait until the next maintenance window, period. But I'm hoping someone here can prove me wrong.

You can set the database to SINGLE_USER so that no other session can come in while you are performing your changes. Also, sp_addrolemember has been deprecated; you should be using ALTER ROLE instead. Finally, please get in the habit of using statement terminators.

USE master;
GO

ALTER DATABASE SQL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SQL SET READ_WRITE;
GO

USE SQL;
GO

ALTER ROLE db_datareader ADD MEMBER [USER];
GO

USE master;
GO

ALTER DATABASE SQL SET READ_ONLY;
ALTER DATABASE SQL SET MULTI_USER;
GO


ORA-04031: unable to allocate 32 bytes of shared memory I want to flush shared pool because of error like

ORA-04031: unable to allocate 32 bytes of shared memory

So I want to flush shared pool using

alter system flush shared_pool;

Connected as sysdba using

hwflow08:flman800 > sqlplus "conn as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 28 05:39:56 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: Connected.

While executing alter system command we faced ORA-01012

SQL> alter system flush buffer_cache;
alter system flush buffer_cache
*
ERROR at line 1:
ORA-01012: not logged on

Can anyone please guide how can I flush the Shared Pool ? (I dont want to shutdown the DB nor want to increase size of shared pool)

Previously Same issue Came :

I left with option to SHUTDOWN DB. So after STARTUP , when i connected through SYSDBA and executed same query alter system flush shared_pool; That time it works perfectlly fine. So question remains as why ORACLE is not allowing flush shared pool when it is out of memory ? What is efficient way to use alter system flush shared_pool;

EDIT :
Seems like its a 10g BUG

check SGA region sizes - buffer pool, dictionary cache, library cache.

Also check the dynamic view v$sga_resize_ops. There you might find a reason.
Also these version of Oracle had a bug, which caused that Oracle could not get rid of the old exec plans if there were too many of them. You should upgrade to terminal patchset 10.2.0.5.

You should also consider setting parameter cursor_sharing, but this might not help in some cases. 1st you should find a culprit, typically this is caused by some ORM framework like Hibernate.

Update:
There is also memory leak in early release 12c RAC cluser. So when you execute this:

SQL> select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <11;

NAME                       BYTES/1024/1024/1024
-------------------------- --------------------
ges resource dynamic                 7.42374295
value block free list                4.61375274
free memory                          2.66423168
gcs resources                        1.78276435
ges enqueues                         1.45691999
gcs shadows                          .990424648
db_block_hash_buckets                .343753815
gcs res hash bucket                        .125
gc name table                            .09375
KTSL subheap                         .086000413

So you can see that 7GB of SGAs RAM are occupied by some clusterware buffers.

But most common source of these errors is caused by enormous amount of hard parsing causing library cache growth.


What settings should I use in my.cnf We have about 5-6 different internal webapps (staff use only) that we will be transferring across to a new virtual server, however I need to setup our my.cnf settings first and I am lost (I am web designer - so this is a little out of my league!).

Our internal systems have about 100 tables apiece, using primarily InnoDB but in some cases using Myisam. Also, 1 of our webapps has quite a lot of data (100,000 records+) and the SQLs used to select the data are quite inefficient (few indexes used, inner SELECTs etc). Roughly about 50 users at any one time.

Our new server will be the HS-250 from HeartInternet.

Many thanks for the help,

InnoDB
You need to run this query:
SELECT
    CONCAT(IB_BB/POWER(1024,1),'K') IB_KB,
    CONCAT(IB_BB/POWER(1024,2),'M') IB_MB,
    CONCAT(IB_BB/POWER(1024,3),'G') IB_GB
FROM (SELECT SUM(data_length+index_length) IB_BB
FROM information_schema.tables WHERE engine='InnoDB') A;

This will tell you what to set for innodb_buffer_pool_size or 75% of the DB Server's total memory, whichever is smaller.
See also

Jan 13, 2012 : What to set innodb_buffer_pool and why..?

MyISAM
You need to run this query:
SELECT
    CONCAT(KeyBuffer_BB/POWER(1024,1),'K') KeyBuffer_KB,
    CONCAT(KeyBuffer_BB/POWER(1024,2),'M') KeyBuffer_MB,
    CONCAT(KeyBuffer_BB/POWER(1024,3),'G') KeyBuffer_GB
FROM (SELECT SUM(index_length) KeyBuffer_BB FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

This will tell you what to set for key_buffer_size.
CAVEAT #1
Use common sense about using both buffers in relation to the total installed RAM (See my answer to What are the main differences between InnoDB and MyISAM?)
CAVEAT #2
Once you set these values and start mysql, please download mysqltuner.pl and run it. It will forecast how much RAM is need for all buffers and for connections (it terms of buffers joins, sort, reads)
CAVEAT #3
As for InnoDB, there are other settings you need to set for getting InnoDB to use
Multiple CPUs/cores:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

InnoDB Tablespaces for Individual Tables

Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Feb 22, 2012 : Ibdata usage and Recommendations?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Apr 19, 2012 : MySql - Clean ibdata1
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

and so much more


Multiple results from a single query into different columns I have 2 tables that look something like this:

Table A:

CREATE TEMP TABLE table_a (
  ID_number_X text,
  ID_number_Y int
);
INSERT INTO table_a VALUES
  ('HT59452', 123),
  ('HT59453', 123),
  ('HT59454', 123),
  ('HT59455', 789),
  ('HT59456', 101);

Table B:

CREATE TEMP TABLE table_b (
  Column_1 text,
  ID_number_Y int,
  ID_number_X text
);
INSERT INTO table_b VALUES
  ('foo', 123, null),
  ('bar', 789, null),
  ('baz', 101, null);

I am running the following query to bring ID_number_X across to Table_B:

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM table_a AS A
WHERE B.id_number_y = A.id_number_y

This works fine, however, there are cases when there can be multiple results i.e. id_number_y can have more than one id_number_x associated with it.

How can I put those multiple results (when they occur) into additional columns?

So the result I want from the above example tables would be this:

Table B
column_1    id_number_y     id_number_x1   id_number_x2   id_number_x3
foo            123            HT59452        HT59453        HT59454
bar            789            HT59455
baz            101            HT59456

I think there could be up to 5 id_number_x for each id_number_y, so I can happily create the extra columns needed to store these.

A few things table_b's schema must be fixed. You can't have an update that updates variable columns, some of which may not even be created. Because you don't know how many matches you'll have, what you likely want here is an array type.

ALTER TABLE table_b
  DROP COLUMN ID_number_X,
  ADD COLUMN ID_number_X text[];

Now you have an array type of text[] on the table.

      Table "pg_temp_6.table_b"
   Column    |   Type    | Modifiers
-------------+-----------+-----------
 column_1    | text      |
 id_number_y | integer   |
 id_number_x | integer[] |

And you can proceed creating UPDATE. Next, you need a simply query from which to do the update.

SELECT id_number_y, array_agg(id_number_x) AS id_number_x
FROM table_a
GROUP BY id_number_y;

 id_number_y |        id_number_x
-------------+---------------------------
         123 | {HT59452,HT59453,HT59454}
         101 | {HT59456}
         789 | {HT59455}

With this you can wrap the query in the UPDATE and get what you want.

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM (
    SELECT id_number_y, array_agg(id_number_x) AS id_number_x
    FROM table_a
    GROUP BY id_number_y
) AS A
WHERE B.id_number_y = A.id_number_y;

Now you have..

# TABLE TABLE_b;
 column_1 | id_number_y |        id_number_x
----------+-------------+---------------------------
 foo      |         123 | {HT59452,HT59453,HT59454}
 bar      |         789 | {HT59455}
 baz      |         101 | {HT59456}

That said, this is rarely necessary and there are two other ways to do this which may better fit your needs,

MATERIALIZED VIEW
VIEW

Normally, this isn't needed because you simply use a JOIN.


How do you clear out all old query plans from within Microsoft SQL Server? We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these reports.

I believe we have a query plan issue. The first report we run each day, runs very fast 7 minutes. Any report we run after the first report takes over an hour.

Each night we run a scheduled task that stops and starts SQL Server Agent and SQL Server. There are approximately 25 other databases within this one instance of SQL Server. No other databases have performance issues, only the one off the shelf product I mentioned earlier.

Is there a way to clear out all query plans that SQL Server currently has in memory?

How can I do this without impacting 30 or so users that rely on other databases on the same server?

You asked two questions here. First, you want to know if you can remove all plans stored in memory for an instance of SQL. That is done with DBCC FREEPROCCACHE as Matt M suggested.

The second question you asked is "How can I do this without impacting 30 or so users that rely on other databases on the same server?". The short answer is "you can't". If you remove all plans than the other users that are relying on plans being in memory will likely suffer a performance hit.

The workaround for this requires some manual intervention. You can use DBCC FREEPROCCACHE to remove specific plans providing you have the plan_handle.

From what you are describing above it does sound like a plan issue, but I am not certain that removing plans is the answer. I would point you in the direction of parameter sniffing before thinking about removing plans:

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/08/11/conor-vs-misbehaving-parameterized-queries-optimize-for-hints.aspx

You should be able to optimize the queries rather than fooling around with DBCC FREEPROCCACHE on a scheduled basis. I would also advise that you spend time analyzing the wait events for your instance.


Can you direct Availability Groups to fail over to a particular node? Can you direct Availability Groups to fail over to a specific node in the cluster?

Windows Server Failover Clustering (WSFC) with SQL Server does have the option to failover to a particular node and Availability Groups seem to build on WSFC.

Taken form the MSDN:

  Preferred owner A node on which a resource group prefers to run.
  Each resource group is associated with a list of preferred owners
  sorted in order of preference. During automatic failover, the resource
  group is moved to the next preferred node in the preferred owner list.

http://msdn.microsoft.com/en-us/library/hh270278.aspx


MySQL UTF-8 Encoding Problem I have two MySQL databases that are for Wordpress websites. One db is for a live version of a site, the second is a copy of the original that is used in conjunction with a copy of the original site. We use the copies for development.

The original site is having character encoding issues, showing the black diamonds with question marks in them. I've gone all over the web looking into this, but nothing is fixing anything. The strange thing is that the second database, which is a copy of the original, displays characters correctly. How could this be? Its nothing with my site files, as I've redirected the original site to use the copy of the database temporarily and the characters display correct on the live site.

Both databases have "latin_swedish_ci" as the database collation and the wp_posts table that fetches the content I'm having problems with is set to a "utf_8" charset with "utf8_general_ci" as the collation. I've tried setting the whole database collation to utf8 (general and unicode), as well as changing the specific database table collations to utf8_general, unicode and tried a few others. It seems like nothing changes at all, I even chose a few arbitrary charsets and the characters just appeared exactly the same. Could this be related to user permissions perhaps, if thats the case?

Thanks for the help in advance.

Not an "answer", but steps for debugging...

All of these need to agree:

 tag on HTML
character_set_client, character_set_connection, and character_set_results
the WP setting (I don't know what is, try this?)
the CHARACTER SET on the columns of the relevant tables, or if nothing mentioned, then on the table, from which the columns inherit.
And the connection user should not be root.

If, however, the data is already stored in the wrong encoding, the problem is messier.  Do analyze this, do SELECT HEX(col), col ... to look at some particular thing that is causing the black diamonds (etc).  Show that to us, plus list the other things; then we can proceed.


Redo Logs Mysql Cluster The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles.
But while inserting into table only one redo directory is used.
I ran following command to get redo information "all dump 2398" and i get:

REDO part: 0 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 12 free: 12 (mb)
REDO part: 1 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 12 free: 12 (mb)
REDO part: 2 HEAD: file: 1 mbyte: 0 TAIL: file: 2 mbyte: 2 total: 12 free: 6 (mb)
REDO part: 3 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 12 free: 12 (mb)

It doesn't uses REDO part: 0 REDO part: 1 REDO part: 4 . It only uses REDO part: 3
It will give "ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER" inspite of using rest of redo files.
Why should we allocate 4 times space if it only uses 1 of 4 redo files?

Each data node's redo log has a number of 'parts' - currently 4 by default.  Each redo log part is like a mini redo log, with 1 / Num_parts of the capacity defined for 'Redo log'.  This design increases file system and lower layers parallelism when writing the redo log, but requires balance across the parts to be reasonable.

When a table is created, table fragments are created on each data node.  Each fragment is assigned a redo log part which will be used to log all of the activity on the fragment - e.g. inserts, updates, deletes.

A table with only a single fragment on a particular data node will use only one redo log part, and you will see an imbalance in the use of the redo log parts as you have shown.

Where a table has multiple fragments stored on a data node, they will use different redo log parts, and the use of the different parts is balanced.

Where you have more than one table, the fragments of other tables will use different redo log parts, and the use of different parts will be statistically balanced.

So try creating the single table with more fragments (increase MAX_ROWS, or specify PARTITIONS=X in the CREATE TABLE statement), and see if the redo log part usage is more balanced.


SQL Server 2012 Error 9512 when modifying user login We are attempting to grant access to a database to one of our employees. We open up his user properties in Security -> Logins, navigate to the User Mappings page, select the database, select db_owner in the options below, set the default schema to dbo, then when we click OK to save the changes, we get the following error:

  Create failed for User 'domain\username'. (Microsoft.SqlServer.Smo)
  Xml data type is not supported as a parameter to remote calls. (Microsoft SQL Server, Error: 9512)

Any thoughts on why we may be getting this error and what we can do to resolve it?

Sounds like you have a DDL trigger that is set to listen for ALTER_LOGIN events, and is trying to pass EVENTDATA() directly to some remote procedure.

Check for this using:

USE master;
GO
SELECT OBJECT_DEFINITION([object_id])
  FROM sys.server_triggers
  WHERE OBJECT_DEFINITION([object_id]) LIKE N'%ALTER[_]LOGIN%';

It could also be a database-level DDL trigger that listens for ALTER_USER:

USE the_relevant_database;
GO
SELECT OBJECT_DEFINITION([object_id])
  FROM sys.triggers
  WHERE parent_class = 0
  AND OBJECT_DEFINITION([object_id]) LIKE N'%ALTER[_]USER%';


Why do I see sp_OACreate, sp_OASetProperty in sp_WhoIsActive? I've seen this happen a few times: sp_WhoIsActive shows various sp_OA% sprocs, with my user name, but I'm certainly not running them directly.  Does this reflect some kind of query rewrite process?  It seems to be correlated with queries across remote servers.

Did you search your system catalogs for stored procedures that might be calling them? In DBs where you see this:

SELECT OBJECT_NAME([object_id]), definition
   FROM sys.sql_modules
   WHERE definition LIKE '%sp[_]OA%';

To do this for all databases at once:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10)
   + 'SELECT ''' + name + ''', o.name, m.definition
   FROM ' + QUOTENAME(name) + '.sys.objects AS o
   INNER JOIN ' + QUOTENAME(name) + '.sys.sql_modules AS m
   ON o.[object_id] = m.[object_id]
   WHERE m.definition LIKE ''%sp[_]OA%'';'
FROM sys.databases;

EXEC sp_executesql @sql;

You might also check job steps.

SELECT j.name, s.step_name, command
   FROM msdb.dbo.sysjobsteps AS s
   INNER JOIN msdb.dbo.sysjobs AS j
   ON s.job_id = j.job_id
   WHERE s.command LIKE '%sp_OA%';

If you don't find anything there, you will want to run a server-side trace because it must be coming from ad hoc batches.


Null Values in a CASE statement I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to alter/delete any of the already created tables in the AdventureWorks or TSQL2012 databases. I've created a temp table to test my code before I actually create a table to play with and this is the code I am using to insert values into my table:

DECLARE @i INT = 1
 WHILE @i < 10
    BEGIN
    INSERT INTO #TestEmployeeCountry
    VALUES ( SUBSTRING('ABCDEFGHIJKLMNOP', @i, 1),
        CASE (SELECT ABS(CHECKSUM(NEWID()))%10 +1)
            WHEN 1 THEN 'USA'
            WHEN 2 THEN 'CANADA'
            WHEN 3 THEN 'MEXICO'
            WHEN 4 THEN 'UK'
            WHEN 5 THEN 'FRANCE'
            WHEN 6 THEN 'SPAIN'
            WHEN 7 THEN 'RUSSIA'
            WHEN 8 THEN 'CHINA'
            WHEN 9 THEN 'JAPAN'
            WHEN 10 THEN 'INDIA'
        END)
    SET @i = @i + 1
    END;

The problem I am having is I keep getting an error saying "Cannot insert the value NULL into column 'Country', table 'tempdb.dbo.#TestEmployeeCountry" The reason this is is because I have the Country column set to NOT NULL, and my code does work for some of the inserts, the problem is I randomly get NULL values out of my case statement.

I know that to fix this I can easily add another line that says "DEFAULT xxxxxx" however I want to understand what is going on because based on what I see I shouldn't have to do that, should I? I thought I wrote my case statement correctly, giving me a number between 1-10 only and upon testing just that specific select statement over 1000 tries, I always get a random number between 1-10, nothing larger or smaller. Can anyone help me to understand why this code tries to enter a NULL value into that column?

Why this happens has already been answered by @PaulWhite in the SO question: How does this CASE expression reach the ELSE clause?

To solve it, you should calculate the ABS(CHECKSUM(NEWID()))%10 +1 outside/before the INSERT statement so it is calculated once. Something like:

DECLARE @i INT = 1 ;
DECLARE @rand INT ;
 WHILE @i <= 10
   BEGIN
    SET @rand = ABS(CHECKSUM(NEWID()))%10 +1 ;
    INSERT INTO TestEmployeeCountry
    VALUES ( SUBSTRING('ABCDEFGHIJKLMNOP', @i, 1),
        CASE @rand
            WHEN 1 THEN 'USA'
            WHEN 2 THEN 'CANADA'
            WHEN 3 THEN 'MEXICO'
            WHEN 4 THEN 'UK'
            WHEN 5 THEN 'FRANCE'
            WHEN 6 THEN 'SPAIN'
            WHEN 7 THEN 'RUSSIA'
            WHEN 8 THEN 'CHINA'
            WHEN 9 THEN 'JAPAN'
            WHEN 10 THEN 'INDIA'
        END) ;
    SET @i = @i + 1 ;
   END ;

Also notice that with your code, the 10 countries will not be placed in the table with equal probability! The first country (USA) will have 10% chance, the second will have 9% ( (100%-10%)*10% ), the third 8.1%, ((100%-19%)*10%), etc. That leaves a not so small chance (around 1/e) that none of the 10 is chosen and the CASE expression goes to the default ELSE NULL and you get the error. (You can check the probabilities if you allow nulls in the column and run the SQLfiddle script.)

According to the above, another way to solve it would be to change the expressions to comply with how SQL-Server executes the CASE and all 10 cases have the same probability:

    CASE 0
        WHEN ABS(CHECKSUM(NEWID()))%10 THEN 'USA'
        WHEN ABS(CHECKSUM(NEWID()))%9 THEN 'CANADA'
        WHEN ABS(CHECKSUM(NEWID()))%8 THEN 'MEXICO'
        WHEN ABS(CHECKSUM(NEWID()))%7 THEN 'UK'
        WHEN ABS(CHECKSUM(NEWID()))%6 THEN 'FRANCE'
        WHEN ABS(CHECKSUM(NEWID()))%5 THEN 'SPAIN'
        WHEN ABS(CHECKSUM(NEWID()))%4 THEN 'RUSSIA'
        WHEN ABS(CHECKSUM(NEWID()))%3 THEN 'CHINA'
        WHEN ABS(CHECKSUM(NEWID()))%2 THEN 'JAPAN'
        ELSE 'INDIA'
    END


Lost persistance in long term in redis without TTL Our keys are lost in some time and we cannot find the problem.

We are using AOF and RDB at the sametime with configs below:

RDB:

save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb

AOF:

appendonly yes
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes

As you can see there is no fancy... all are redis defaults.

I have tried to create keys and reboot the server. At this circumstances the keys still. I couldn't determine when keys are lost or why? The log files are not helpful cause I don't know what to query in logs or when.

All I know is my keys are lost at a time interval about one month.

How can I debug this kind of problem in redis?

Preface: keys in Redis do not get deleted, unless explicitly requested or when the server is configured with an eviction policy (and RAM is pressured).

Regrettably, the most common cause for missing keys is security, or rather the lack of it. Script kids, leet haxors and regular bad guys take advantage of unprotected servers and cause all kinds of havoc. The first thing you need to do is ensure that the server is protected properly (i.e. is configured with a password and is not exposed to the world). For more details refer to https://redis.io/topics/security

After ruling out attacks and eviction policy configuration, the only remaining explanation is a rogue piece of code. From the server's side, you can try tracking this behavior using either the MONITOR command or by configuring keyspace events and subscribing to the relevant messages.


How big of an impact does NUMA really have? I was reading up on Non-Uniform Memory Access (NUMA) and I was wondering how much of a difference it can really make?  Another way to word that, what level of throughput is required before this becomes beneficial?

Also, what type of server do you need to look for when you want a system with multiple system busses?

Jonathan Kehayias did a good blog post on NUMA at SQLSkills.com.

I believe server wise most of the vendors out there note if they are NUMA capable servers or not. From reading Jonathan's post it seems the focus is on the motherboard and processor capabilities. Then I would imagine how they interconnect would be an important thing to note as well, since that would be the bottleneck in most cases.

I have not really seen anyone write up on when NUMA becomes beneficial. I think it would just be a analysis on what your system is going to need. If one server maxed out to the nines is not going to cut it for the workload, then look at the alternatives.


How is an idle SQL Server connection causing blocking? I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but these show that the blocking-process seems to be idle, with the input buffer being use [_dummy].

The application connecting to this server uses multiple databases, so _dummy is a blank database which connection pools open connections to; the database context is switched as required.

What I thought was happening was that a query was timing out after obtaining locks, not rolling back the transaction and the connection was being released by the application back to the connection pool, which was resetting the database context before problems occurred due to the open transaction. To try and resolve that I changed the user connection settings so that XACT_ABORT is on by default, thinking that this would cause timed out transaction to rollback automatically, but this doesn't seem to have resolved the problem.

Is there anything else which could cause an idle connection (part of a .NET connection pool) to block other queries? Is there any way I can capture more information about the last query run on the blocking-process?

Note: I'm also monitoring deadlocks and the blocked-processes are sometimes blocked for several minutes but do not become deadlocks.

If you're comfortable with Data Collector, you could download and install ExtendedTSQLCollector and set up a collection set to capture Blocking and Deadlocking events with XE. I have a blog post that explains how to do that.

In order to collect sleeping SPIDs that cause blocking, you could set up another collection set (or an additional collection item in the same collection set) that captures information about those SPIDs. You can use the query found in this answer. You can also tweak the query to extract information about the objects the sleeping SPID is holding locks on.

With this setup you should easily identify blocking SPIDs. What you will not identify easily is what was the sleeping SPID doing, except for the last command issued. With the information collected you can however set up something more specific like a XE session filtered for application name and/or hostname (or whatever makes sense) using a ring buffer target and dig through the batches it ran before becoming idle.

Good luck!


Query problem no product name I have a query for a custom opencart store and I am doing something wrong with the LEFT join as because of the subquery on some products I am loosing the Product Name. I am not sure why. Any suggestions?

SELECT
    w0.name AS 'Product Name',
    p.sku AS 'Product(sku)',
    p.product_id AS 'product_id',
    p.parent_id AS 'parent_id',
    COALESCE(w0.quantity, 0) AS 'Denmark - main'
FROM
    oc_product p
        LEFT JOIN
    oc_warehouse_product wp ON (p.product_id = wp.product_id)
        LEFT JOIN
    oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
        LEFT JOIN
    (SELECT
        pd.name, wp.quantity, p.sku
    FROM
        oc_product p
    LEFT JOIN oc_warehouse_product wp ON (p.product_id = wp.product_id)
    INNER JOIN oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
    LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
    WHERE
        wl.warehouse_name = 'Denmark - main') AS w0 ON p.sku = w0.sku
GROUP BY p.sku
ORDER BY 'Product Name'
LIMIT 0 , 20

Rick James was right the problem was the left join that did not worked there. I replaced it with an INNER join and it works perfect.

The final query working and optimised looks like this:

SELECT
    w0.name AS 'Product Name',
    p.sku AS 'Product(sku)',
    p.product_id AS 'product_id',
    p.parent_id AS 'parent_id',
    COALESCE(w0.quantity, 0) AS 'Denmark - main'
FROM
    (SELECT
        *
    FROM
        oc_product p
    ORDER BY 'Product Name'
    LIMIT 0 , 20) p
        LEFT JOIN
    oc_warehouse_product wp ON (p.product_id = wp.product_id)
        INNER JOIN
    oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
        LEFT JOIN
    (SELECT
        pd.name, wp.quantity, p.sku
    FROM
        oc_product p
    LEFT JOIN oc_warehouse_product wp ON (p.product_id = wp.product_id)
    INNER JOIN oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
    LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
    WHERE
        wl.warehouse_name = 'Denmark - main') AS w0 ON p.sku = w0.sku
GROUP BY p.sku
ORDER BY 'Product Name'
LIMIT 0 , 20;


What is the better way to handle a different types of measurement in grocery store items database While designing a DB for grocery shop, I came across a requirement of "same product available in different measurements" - e.g., Lets say, Green Peas available in 250gms, 500gms, 1Kg and so.

My question is :
Is it good practice to have duplicate entry of product with different measurement in products table or keeping the measurements in different table with product id?

Using postgresql-unit

I'm big on types. I would use an extension for this. If you're not in the US and working with Imperial Units, take a look at Christoph Berg's brainchild postgresql-unit. First build the extension

sudo apt-get install bison flex build-essentials libpq-dev
git clone "https://github.com/ChristophBerg/postgresql-unit"
cd postgresql-unit
make
sudo make install

Then install the extension on your DB and configure your table.

CREATE EXTENSION unit;

CREATE TABLE products (
  id     serial        PRIMARY KEY,
  name   text          NOT NULL,
  unit   unit          NOT NULL,
  price  numeric(7,2)
);

INSERT INTO products ( name, unit, price )
VALUES ( 'Green Peas', '250 g', '2.99' );

# SELECT name, unit, price FROM products;
    name    | unit  | price
------------+-------+-------
 Green Peas | 250 g |  2.99

# SELECT name, unit @ 'kg' AS unit, price FROM products;
    name    |  unit   | price
------------+---------+-------
 Green Peas | 0.25 kg |  2.99

Likewise you can INSERT in other units if need be. They'll get stored in those units, but you can select in any unit you want.

INSERT INTO products ( name, unit, price )
VALUES ( 'KY Jelly', '1 kg', '8.00' );

# TABLE products;
 id |    name    | unit  | price
----+------------+-------+-------
  2 | Green Peas | 250 g |  2.99
  3 | KY Jelly   | 1 kg  |  8.00

# SELECT id, name, unit @ 'g' AS unit, price FROM products;
 id |    name    | unit   | price
----+------------+--------+-------
  2 | Green Peas | 250 g  |  2.99
  3 | KY Jelly   | 1000 g |  8.00


Minimum and Maximum Number of Tuples Consider Relations R and S where are R is having m tuples and S is having n tuples . m<=n . What would be the minimum and maximum number of tuples in each of the following cases (Assume that nothing is mentioned about key constraints)

R union S
R intersection S
R-S
S-R
R NATURAL JOIN S
R Left OUTER Join S
R/S

My Work

R UNION S

max : n+m ( union we add all the tuples from both relations)

min: 0 ( taking m=n=null )

R INTERSECTION S

max : m ( m < n both relation contains same keys then we may get maximum m keys )

min: 0 ( taking m=n=null if no common keys in both relations)

R - S

max : m ( if they are disjoint then in R-S we will get all tuples of R )

min: 0 ( if all tuples in R is also present in S)

S-R

max : n ( as explained above )

min: 0 ( as explained above )

R natural join S

max : n*m ( if no matching key constraints natural join will produce Cartesian product )

min: m ( m < n  when key constraints are taken into consideration )

R LEFT OUTER JOIN S

max : m ( everything from left table will be output even if no match)

min: 0 ( when m=0 )

R/S

max : m ( when n=0 )

min: Im Not able to make a  conclusion

Yes, your answers are mostly right, except a few mistakes:

1. R UNION S

max : n+m ( union we add all the tuples from both relations)
Correct, when R and S have no common tuple.
min: 0 ( taking m=n=null )
Wrong, the minimum is n (the greatest of the two sizes, m and n). When all the tuples of R also exist in S.
And m and n cannot be null, they are the sizes of the relations, they are numbers (integers).

2. R INTERSECTION S

max : m ( m<n both relation contains same keys then we may get maximum m keys )
Correct but the reasoning is wrong. You compare tuples of the two relations, not keys.
min: 0 ( taking m=n=null if no common keys in both relations)
Correct but the reasoning is wrong. The result can be 0 because the two relations may have no common tuples).

3. R - S

max : m ( if they are disjoint then in R-S we will get all tuples of R )
Correct
min: 0 ( if all tuples in R is also present in S)
Correct

4. S - R

max : n ( as explained above )
Correct
min: 0 ( as explained above )
Wrong, the minimum is n - m.

5. R natural join S

max : n*m ( if no matching key constraints natural join will produce Cartesian product )
Correct
min: m ( m < n  when key constraints are taken into consideration )
Wrong, the minimum is 0. You can easily find an example, identical with case 2 (INTERSECTION).

6. R LEFT OUTER JOIN S

max : m ( everything from left table will be output even if no match)
Wrong, the maximum is m * n, the same as for natural join. Or just take ON TRUE.
min: 0 ( when m=0 )
Wrong, the minimum is m. Example can be the same as for NATURAL join above (or just take ON FALSE) but it cannot give as a result lees than the number of tuples in R (the left relation in the join).

7. R / S

max : m ( when n=0 )
Correct but it doesn't have to be n=0 or m=0. You can find another example.
min: I'm not able to make a conclusion.
Minimum is 0 Consider that relational division is similar to integer division. 3 / 7 gives 0 in integer division for example. Try to convert this into relational division.


Metric to standard unit conversion in SQL Server I am giving simplified version of my two table which are as follow.

tblRecords

ID  unit  val
--- ---   ---
1   KV    3
1   BAR   4
2   KV    7
2   KG    5
3   C     37

tblUnit

unit    convUnit    formula
---     ---         ---
KV      CV          @val * .865
KG      lbs         @val / 2.205
BAR     PSI         @val / 14.504
C       F           @val * 9/5 + 32

I will not be able to change table structure.

I know which unit will be converted in which unit (i.e. I know KV will be converted to CV, KG to lbs etc..).

Please note that formula is a varchar column and I have reference from table tblRecords.unit to tblUnit.unit

But my question is how to calculate the formulas on the fly in single query. i.e. I want following output.

ID  unit    val     convVal(column will be counted on the fly using formula stored in tblUnit.formula field)
--- ---     ---     ---
1   KV      3       2.595
1   BAR     4       0.27578599
2   KV      7       6.055
2   KG      5       2.267573696
3   C       37      98.6

There is no direct way to implement this in SQL Server, as dynamic SQL in functions is not permitted. However, as your tblUnit will most likely rarely change you can go an indirect way:

What we need is a function that can handle the conversion based on the formulas in the table. If we just could hard-code those formulas in the function and somehow update the function every time the table changes, the problem would be solved. So, can we?

Yes, we can:SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.tblRecords
    ([ID] int, [unit] varchar(3), [val] DECIMAL(10,4))
;

INSERT INTO dbo.tblRecords
    ([ID], [unit], [val])
VALUES
    (1, 'KV', 3),
    (1, 'BAR', 4),
    (2, 'KV', 7),
    (2, 'KG', 5),
    (3, 'C', 37)
;

CREATE TABLE dbo.tblUnit
    ([unit] varchar(3), [convUnit] varchar(3), [formula] varchar(15))
;
GO
CREATE FUNCTION dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))
RETURNS TABLE
AS
RETURN
 SELECT @val convVal;
GO
CREATE TRIGGER updateFnConvert ON dbo.tblUnit AFTER INSERT,UPDATE,DELETE
AS
BEGIN
  DECLARE @cmd NVARCHAR(MAX);
  SELECT @cmd = 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
  ' RETURNS TABLE AS'+
  ' RETURN SELECT CASE @unit'+
  (SELECT ' WHEN '''+unit+''' THEN '+formula
     FROM dbo.tblUnit
      FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
  ' END convVal;'
  EXEC(@cmd);
END

GO

This creates the two tables, a fnConvert function stub and a trigger on the tblUnit table.

When we now alter the contents of the table with INSERT, UPDATE or DELETE like this:

Query 1:

INSERT INTO dbo.tblUnit
    ([unit], [convUnit], [formula])
VALUES
    ('KV', 'CV', '@val * .865'),
    ('KG', 'lbs', '@val / 2.205'),
    ('BAR', 'PSI', '@val / 14.504'),
    ('C', 'F', '@val * 9/5 + 32')
;

the trigger fires and generates a dynamic sql statement like this:

Query 2:

SELECT 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
' RETURNS TABLE AS'+
' RETURN SELECT CASE @unit'+
(SELECT ' WHEN '''+unit+''' THEN '+formula
   FROM dbo.tblUnit
    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
' END val;'

Results:

|                                                                                COLUMN_0 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4)) RETURNS TABLE AS RETURN SELECT CASE @unit WHEN 'KV' THEN @val * .865 WHEN 'KG' THEN @val / 2.205 WHEN 'BAR' THEN @val / 14.504 WHEN 'C' THEN @val * 9/5 + 32 END val; |

and then executes that statement. That statement replaces the function with a version containing all the current formulas.

Now we can just use the function in any query like this:

Query 3:

SELECT *
  FROM dbo.tblRecords AS TR
  JOIN dbo.tblUnit AS TU
    ON TR.unit = TU.unit
  CROSS APPLY dbo.fnConvert(TU.unit,TR.val) AS FC

Results:

| ID | UNIT | VAL | CONVUNIT |         FORMULA |     CONVVAL |
|----|------|-----|----------|-----------------|-------------|
|  1 |   KV |   3 |       CV |     @val * .865 |       2.595 |
|  1 |  BAR |   4 |      PSI |   @val / 14.504 |  0.27578599 |
|  2 |   KV |   7 |       CV |     @val * .865 |       6.055 |
|  2 |   KG |   5 |      lbs |    @val / 2.205 | 2.267573696 |
|  3 |    C |  37 |        F | @val * 9/5 + 32 |        98.6 |


Escaping T-SQL Keywords $sql = "SELECT Kill FROM tbl_pvporderview";

Problem is that I end up with: Incorrect syntax near the keyword 'Kill'.

Because kill is a T-SQL command... any way to bypass it?

I can't change the column name because it's used by the software a lot and I cant change the software that's using the database.

So it simply fails if I use sqlserv to select data from that column. '' or "" wont help.

The complete statement would be:

$sql = "SELECT serial,Kill FROM tbl_pvporderview WHERE Kill > (?) ORDER BY Kill DESC ";

If you want to use reserved words as table or column names, you have 2 options:

use brackets (the SQL-Server's way): SELECT [Kill]

or double-quotes* (the ANSI/ISO standard): SELECT "Kill"

Your whole statement would become:

SELECT [serial], [Kill]
FROM tbl_pvporderview
WHERE [Kill] > (?)
ORDER BY [Kill] DESC ;

*: Of course, using double quotes (SELECT "Kill") would necessitate that QUOTED_IDENTIFIER is ON. You may encounter databases that the setting is still SET OFF.


How to force a delayed member to become primary in case of failure in mongodb I have three member set [primary,secondary & arbiter] in my data center 1 [DC1]. Now i want a delayed member in data center 2 [DR]. In case DC 1 is completely down then my sub net application pointed will be redirected to DR. In that case :

a. How i will use DR mongodb as its a delayed member.

b. When the DC come back online then how i will sync the database from DR to DC.

Firstly, it is not recommended to have a replica set with even number of members. Hence, it will be better to add an arbiter or another data bearing node in the DC2

Coming to your question, the DR servers are not supposed to be used for ensuring availability they are meant to provide limited functionality during recovery from a disaster. Assuming the configuration that you have provided:

a.) How i will use DR mongodb as its a delayed member.

For your configuration assuming 1 vote per server, the majority will be 3. If DC1 goes down then no server will be able to get majority votes. Hence there will be no primary. Thus for the duration of the downtime your application will be able to service only read requests for the time up-to which the data has been synched on the delayed node.

b. When the DC come back online then how i will sync the database from DR to DC.

Since the application is in read only mode, hence once the DC1 comes up, there will be a primary election and server in DC1 with the latest data will be elected primary and the server in DC2 will be relegated to the secondary and the replication would take care of the synch


Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060 I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. This is happening for a table having lots of varchar 250 to 500, numerics and datetime columns. I was trying to reproduce this same scenario in my test environment but unable to do so.

Do you guys have any idea how to reproduce it in Test environment? Any examples would be great for explanation.

My environment is SQL server 2005 Ent. Edition. My table has ~470 columns. I know its a bad design but its not in my control.

I tried below but it doesn't work:

create table test (col1 varchar(5000), col2 varchar(5000))
insert into test values (replicate('A', 5000), replicate('B', 5000))

Even though record length is greater than 8060, it inserts successfully.

SQL Server has a limitation of 8060 bytes per row. See "Bytes per row" in Maximum Capacity Specifications for SQL Server. Additional information for this line has this phrase:

  SQL Server supports row-overflow storage which enables variable length
  columns to be pushed off-row. Only a 24-byte root is stored in the
  main record for variable length columns pushed out of row; because of
  this, the effective row limit is higher than in previous releases of
  SQL Server. For more information, see the "Row-Overflow Data Exceeding
  8 KB" topic in SQL Server Books Online.

So, when you insert two varchar values each 5000 bytes long in one row, one or both of them would be stored off-row and occupy only 24 bytes instead of 5000+2.

But, if you have 470 varchar columns in a table, you can insert only 8060/24=335 non-null varchar values in the same row of such table. In fact, even slightly less than 335, because there are other structures in a row that take some space.

MSDN: Row-Overflow Data Exceeding 8 KB

So, to reproduce it in test environment, create a table with at least 336 varchar columns and try to insert 336 non-null values that are longer than 24 characters in the same row.


Postgres - Importing multiple rows with Foreign key constraint I can't seem to find an answer for my problem, maybe I'm not asking the right question but here it goes:

I have basically two tables in pgAdmin, let's say student and grades, eventually with a foreign key constraint (student_id referencing student(id)

CREATE TABLE student (
    id CHAR(2),
    name CHAR(4),
PRIMARY KEY (id));

CREATE TABLE grade (
    student_id CHAR(2),
    grade INT,
FOREIGN KEY (student_id) REFERENCES student(id));

What I'd like to do is copy/import two files into the databases. All good for students but my grade.csv files will contain non-existing student IDs and I want the import to ignore and not insert them. Instead of that, the command fails, nothing is imported.

What would be a correct and efficient way to do this?

The solution to this problem is the one that one uses quite often when importing data: to use a staging table.

Let's say you have a CSV with two fields (student_id and grade).  This maps to the grade table nicely, and the only problem is the nonexistent students.  So, first create a temporary table and then import the data there:

CREATE TEMPORARY TABLE tmp_grade AS
    SELECT *
      FROM grade WHERE FALSE;

Then you have multiple options, but the gist of it is only picking the rows from the temp table that have a matching student:

INSERT INTO grade
SELECT *
  FROM tmp_grade
 WHERE EXISTS (SELECT 1 FROM student WHERE id = student_id);

And done.  The performance of this depends on the size of the data (not surprisingly).  The definitions of the unique student.id suggests this won't be the case.  However, if it happens, you can still play around with other variations to the same theme.

Notes:

choosing char(2) as the student ID is interesting.
pgAdmin is a client to PostgreSQL, it does not have tables by itself.


Can a connection's prepared statements be altered by Postgres's admin functionality? I am trying to tamper proof as best as possible a peer to peer application.

I am concerned that the prepared statements will be altered.

Can the Postgres admin alter a connection's prepared statements with built-in functionality?

If so, how best can this be managed?  Randomly looping to unprepare and reprepare?

If the user has:

Physical hardware access;
Root / "Administrator" access to the system; or
PostgreSQL superuser rights;

then they effectively have total control and can do whatever they feel like.

It's a little trickier when starting with superuser access, but the PostgreSQL superuser is not isolated from the underlying OS user that PostgreSQL its self runs as. They can modify the data directory contents in-place, change views, install extensions, etc.

If you're running a database on someone else's hardware, they control the database - not you. You must design with that in mind, allowing for the idea that they can modify the data, queries, etc in any way they feel like.

It's pretty simple to recompile PostgreSQL with whatever modifications you feel like, or add extensions to do so. Feel like matching query text and replacing it with a different query? Easy, that's just a parser or executor hook.

Or you can just attach a debugger and do it live.

So, while the user can't just go modifying prepared statements directly from SQL, if they have control of the system they can still do it with varying degrees of difficulty. Whether it's "hard enough" for your purposes depends mainly on what the incentives for breaking the system are, the skill level of the attacker(s), whether attackers communicate with each other, etc.

There are some strategies you can use to make untrusted local storage better protected - for example, signing data with a key known only to the entity that generates it, so others can verify its authenticity but not modify it.


Calculate column running total Given the following schema and dummy data:

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  memo VARCHAR(255) NOT NULL,
  amount INT NOT NULL,
  tx_date DATE NOT NULL
);

INSERT INTO transactions (memo, amount, tx_date) VALUES
  ('Initial balance', 10000, '2015-08-17'),
  (     'Eating out', -1000, '2015-08-19'),
  (            'Gas', -7000, '2015-08-19'),
  (          'Wages', 75000, '2015-08-31');

I'm trying to generate a report of the running balance ordered by the transaction date such as:

 id |      memo       | amount |  tx_date   | running_balance
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000

Ideally I'd like to sum each amount to the running_balance of the following row, but it seems that the lead() window function can't reference the very same column you are trying to create:

SELECT *, amount + lead(running_balance, 1, 0) OVER (ORDER BY tx_date DESC) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

ERROR:  column "running_balance" does not exist
LINE 1: SELECT *, amount + lead(running_balance, 1, 0) OVER (ORDER B...

Another option would be to make partial column sums from the current row to the last one, but I am also unable to come up with the correct syntax:

SELECT *, SUM(amount) OVER (ORDER BY tx_date DESC ROWS UNBOUNDED FOLLOWING) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

ERROR:  frame start cannot be UNBOUNDED FOLLOWING
LINE 1: ...T *, SUM(amount) OVER (ORDER BY tx_date DESC ROWS UNBOUNDED ...

Can any of these queries/approaches be made to work? Or is there any other way to achieve the desired result?

Well, apparently this is it:

SELECT
    *,
    SUM(amount) OVER (
        ORDER BY tx_date DESC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

Output:

 id |      memo       | amount |  tx_date   | running_balance
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000


How can I setup a Login which can run an automated Database Refresh without giving away too much priv? I would like to re-ask and extend the question posed in this thread if I may:

SQL Server permissions to create, restore, delete only some databases

I am also attempting to setup an 'isolated' Login-A on a server I'll call Target (SQL Server 2012), with just enough priv to run a stored procedure (with a @dbname argument) which does the following:

runs a Backup for @dbname on a different linked server (Source)
Alters @dbname offline and Drops it on the Target server
then Restores new @dbname on Target

I played with "Grant Create Any Database To Login-A", which does appear to fence Login-A from neighboring databases. But when I tried to run Alter as part of some Create Database DDL, it failed, so this doesn't seem to help the stored procedure sequence.

How do we give enough priv to Login-A to be able to Alter and Drop without it being turned on beyond the current DB being created? I want my users to be able to refresh their databases by themselves (via the proc) while protecting all the other databases on the server which they don't own and shouldn't be able to touch (and without creating an audit violation).

You need to use impersonation.

Let's assume that loginB has elevated privileges.  Your stored procedure will have an "Execute as" line:

execute as login = 'yourdomain\loginB';
backup database @dbname to disk.......
revert;

You must grant impersonation on loginB to loginA.  Also, loginA must have execute permissions on the stored procedure.
MSDN reference for EXECUTE AS:  https://technet.microsoft.com/en-us/library/ms188304(v=sql.105).aspx


Should I put OLAP and realtime web DBs on the same server? I have several different workloads I'd like to consolidate into the same Sql 2012/2014 instance.

Cognos/OLAP is burst oriented and users are accustomed to a response delay
Web databases that require immediate response (user authentication)

Is there any way to leverage SQL to partition these workloads?

You can install both on the same instance, If the performance is an issue you can use the resource govenor to make sure that the web database gets the lion's share of the resouces.

Security wise I would reccomend to separate the two by using different instances but if you are careful in setting up security this should be no issue.


Can this query be optimised with indexes, or should I change my schema? I have a MySQL table that stores each time a user has downloaded a photo that they've bought.  I want to be able to retrieve the first time each photo was downloaded:

SELECT MIN(downloaded) AS downloaded
     , photoId
     , size
     , transactionId

FROM ORDER_DOWNLOADS

GROUP BY photoId, size, transactionId

I've got an index on photoId, size, transactionId, downloaded, as well as single-column indexes on each column.

With 50,000 rows this takes around a second.

Can I simply improve it substantially with a rewrite or a better index, or is my best bet to add a "firstDownload" bit column that I set to 1 upon an initial download and 0 on any subsequent downloads of the same photo/size/transaction?

Thanks!

explain:

id 1
select type SIMPLE
table ORDER_DOWNLOADS
partitons null
type index
possible keys photoId, size, transactionId, downloaded, covering
key covering
key_len 87
ref null
rows 43878 (I was being approximate when I said 50,000 earlier, this is the whole table)
filtered 100
Extra Using index

I think this request is fully optimized on a SQL point of view. Meanwhile, you may can increase performance by choosing a particular type of index depending on your data (for example BINARY for IDs), check this documentation for more details.

Moreover you have to keep in mind that the innoDB engine in MySQL (if you're using it) may be slow on a big amout of data.


APEX 5: Horizontal Scollbar on page instead of region I'll generate in APEX 5 (Universal Theme) a large table with PL/SQL Dynamic Content.

To scroll down, there is a scrollbar on the page. To scroll to the right, there is only a scrollbar in the region/div.
So if user want to see the values in the most right column in the first rows, they have to scroll all down, move to the right and scoll all up.

Thats very anoying.

Any suggestions?

I solved this issue by enabling the "Maximize Button" for the Region.


Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow We have one table that has clustered index on two columns: first one is NVARCHAR(50) and second one is on DATETIME2(3) type.

There are few other columns. When we query the table with WHERE clause on the clustered index columns, the result is received immediately, even when the result set is tens of thousands of rows.

However, if we add one more predicate on the where clause, the query is super slow. After ten seconds I just stop it, because that is not useful to us.

The table has 200+ million rows.

This is the query that is super fast:

SELECT *
FROM Table
WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2'

This is the query that is super slow:

SELECT *
FROM Table
WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2' AND BooleanColumn = 1

In my mind, the second query should use the clustered index to search for rows and then simply scan the result set to filter out what's needed.

Is it possible to (somehow) make the second query work fast without creating additional non clustered index that would include other columns that we need to filter on?

Here is the query plan for the second:

https://www.brentozar.com/pastetheplan/?id=BJj28mjvL

After the first run, it works fast now for different combination of values for the WHERE part, which means probably it's not that results are cached, but the query plan is generated and optimized.

Every time you add a new column predicate, the optimizer builds (sampled) statistics on that column to compute selectivity. This is where the time goes - building the statistics. On a large table, that can take a while, even with a low sampling rate.


Dealing with disk space full in postgresql I have a Django web application with postgresql 9.3.10 backend (sitting in a Linux OS). I ran into disk full error, such that even if I try to truncate a table, I get errors of the sort:

ERROR:  could not extend file "base/30137/33186048": No space left on device
HINT:  Check free disk space.

I can't easily add more disk space to the server, nor can I delete stuff on this VM. However there are several tables that are candidates for truncation, but seems I can't truncate them now either.

Can anyone give me advice on what I can do here? This is hitting my production server hard, and I'm a bit of an accidental DBA here, so totally stumped.

Because PostgreSQL must write WAL before making any changes to tables, it needs free disk space in order to delete things and release more disk space.

If you let the disk fill up, you can't recover from within PostgreSQL. Even TRUNCATE still has to write to WAL.

So you must free some space on the volume, or expand the volume. If your PostgreSQL log files are in pg_log in the data directory, you can safely remove some of those and restart Pg.

Do not delete pg_xlog or pg_clog. These are not server error logs, they're critical parts of the database, the transaction log and commit log.


Advanced CSV export formatting in MS Access I am trying to create a CSV file with the following format (for import into MYOB AccountRight financial software):

InvoiceNum1,Line1,...
InvoiceNum1,Line2,...

InvoiceNum2,Line1,...
InvoiceNum2,Line2,...

InvoiceNum3,Line1,...
....

So basically each invoice is separated by a blank line, and there can be one or more invoice items per invoice. I currently have a query written that does the following format (i.e. without blank lines):

InvoiceNum1,Line1,...
InvoiceNum1,Line2,...
InvoiceNum2,Line1,...
InvoiceNum2,Line2,...
InvoiceNum3,Line1,...

This query is a union query that brings together two normal sub-queries.

Unfortunately the software I am importing into is extremely stupid and treats this all as one invoice. So I need a way to insert the blank lines between each invoice. Before I export the CSV I am applying filters to the query (so I can select a date range) which means that any blank lines I manage to insert in my query get filtered out because they don't contain a date.

The solution I am currently planning is to write a separate program that the user can use to format the CSV file correctly before importing it into MYOB. This is obviously a huge hack and I would prefer a native Access solution if possible.

I'm using Access 2007, but can upgrade/downgrade to a different version if it gets me the functionality I need.

If you're just trying to put a line between every line after you've exported your file then one solution would be to create macros in Excel. By tweaking your auto-generated macros code with VBA in Excel you can automate all the process and use the same solution in the future with a click of a button.

But if you're familiar with programming languages then it might be much more easier to manipulate your files in scripting languages such as Python etc..

*CSV files are seperated by commas hence "comma seperated values."


Geo-clustering using SQL Server 2012 Enterprise with AlwaysOn We are trying to take our current SQL 2012 Enterprise edition 3 node cluster (OS 2012) and add a 4th node to the cluster that will be at another location.  And everything I find is telling me to use SAN replication to handle the data movement from local cluster to geo-cluster node.  WHy?  Doesn't AlwaysOn handle that data movement piece?  Any insight would be appreciated.  Thanks.

You are essentially trying to create a DR node. Availability Groups in SQL Server 2012 and above supports this scenario. Your node has to be part of the same windows cluster. The SQL Server instance on the secondary site can be configured as a secondary replica for the existing availability group. Once that is done, you will be able to sync changes from primary replica to the secondary replica. Your scenario is documented here: https://msdn.microsoft.com/library/hh781257.aspx


Postgres restore from WAL-E backup never finishes Doing a restore on a WAL-E backup to S3, postgres never seems to complete the restore process. The log repeats the following:

< 2016-04-08 13:41:55.801 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:55.801 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:56.809 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:56.809 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:57.812 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:57.812 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:58.815 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:58.815 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:41:59.817 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:41:59.818 PDT >FATAL:  the database system is starting up
< 2016-04-08 13:42:00.820 PDT >LOG:  connection received: host=[local]
< 2016-04-08 13:42:00.820 PDT >FATAL:  the database system is starting up
lzop: <stdin>: not a lzop file
wal_e.blobstore.s3.s3_util WARNING  MSG: could no longer locate object while performing wal restore
        DETAIL: The absolute URI that could not be located is s3://groupsiopostgres/main/wal_005/0000000100000A8000000071.lzo.
        HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration.
        STRUCTURED: time=2016-04-08T20:42:01.071643-00 pid=28784

The URI does increase approximately every minute. I understand these aren't errors. But psql continues to in this loop until systemctl kills it for taking too long.

Here's my recovery.conf file:

restore_command = '/usr/local/bin/envdir /etc/wal-e.d/env /usr/bin/wal-e --terse wal-fetch %f %p'
standby_mode = 'on'
trigger_file = '/var/lib/pgsql/9.3/data/failover'

How can I get postgres to complete before systemctl kills it?

standby_mode = on

Tharrr's yer problem. You've configured it as a continuous (warm or hot) standby.

standby_mode in the manual:

  standby_mode (boolean)

  Specifies whether to start the PostgreSQL server as a standby. If this parameter is on, the server will not stop recovery when the end of archived WAL is reached, but will keep trying to continue recovery by fetching new WAL segments using restore_command and/or by connecting to the primary server as specified by the primary_conninfo setting.


RDS Postgres chicken-and-egg problem altering roles I just created an RDS Postgres instance, the user postgres (and it's master password), and connected via psql as user postgres.
Then I ran the script generated from the current system using pg_dumpall --schema-only, and it immediately failed on the ALTER ROLE commands:
ERROR:  must be superuser to alter superusers

Ok, but how do I grant superuser to postgres?  All of the documentation I've found "conveniently" skips that part...
EDIT: Here are the exact commands and error message:
postgres=> CREATE ROLE "READONLY";
CREATE ROLE
postgres=> ALTER ROLE "READONLY" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'mdXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
ERROR:  must be superuser to alter superusers

Your RDS master user is not a superuser.

ALTER ROLE changes the attributes of a PostgreSQL role. [...]
Roles having CREATEROLE privilege can change any of these settings except SUPERUSER, REPLICATION, and BYPASSRLS...

Omit incompatible settings from your ALTER ROLE command.


Which one for MySql Bind addresss 127.0.0.1 (in apache & ubuntu) Should I set the bind address 127.0.0.1 in

etc/mysql/my.cnf

or

/etc/mysql/mysql.conf.d/mysqld.cnf

I am using

mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64)

mysqladmin Ver 8.42 Distrib 5.7.20, for Linux on x86_64

Many Linux distributions source the contents of /etc/mysql/conf.d after reading /etc/mysql/my.cnf, so that the package maintainer can make whatever changes they want to my.cnf and the system administrator can keep only their customizations in the conf.d directory, which makes upgrades much easier.

However, quoting straight out of my /etc/mysql/my.cnf file provided by Debian Linux (I've got MariaDB, but Debian policy handles it the same way as they would have MySQL):

  The MariaDB/MySQL tools read configuration files in the following order:

  "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
  "/etc/mysql/conf.d/*.cnf" to set global options.
  "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
  "~/.my.cnf" to set user-specific options.

On my system, the default bind-address is set in /etc/mysql/mariadb.conf.d/50-server.cnf which would be read after my customized setting in /etc/mysq/conf.d/isaacs_totally_great_customizations.cnf, and override my setting from there — I'd have to set it in the mariadb.conf.d folder. You don't say which distribution you're using, but you should look in /etc/mysq/my.cnf for some hints about the order of loading. At the end, you'll probably see a line like !includedir /etc/mysql/mysql.conf.d/ which will show you which folder is loaded last.

My opinion is that, if the conf.d directory is loaded last, that's the "best" place to put it and least likely to be affected by your next upgrade. Of course, the simplest solution would be to just make your bind-address statement the last line of my.cnf which would pretty simply solve the problem without any lingering doubts.


What is best for a primary key? Bigint of 20 numbers or varchar / varbinary of 15 characters? I have already seen some posts making comparisons of varchar with numeric fields, but most only compare with equal values and do not talk about performance in primary keys.
I have a user registration table where each user needs to have a unique code and the auto-incrementing sequential numbers (1,2,3 ...) are not a good idea.
So I'm using PHP's uniquid() command to generate user codes.
Something like this: it5f29b715cff42 containing on average 13 to 15 characters in varchar type fields
I use this code in the primary key because it is the main code of the user's registration, and there will always be a user search for this code in the table.
Some people have suggested switching to a varbinary type field to gain performance. And yet I still don't know if it's the best idea.
So I thought of turning this code into numbers using PHP's hexdec() command. Leaving the code more or less like this: 10001674124319326018 containing an average of 16 to 20 characters. And storing that code in a bigint type field.
I know that the bigint field will always be 8 bytes, but how many bytes would varchar and varbinary in that number of characters occupy? What is the best option to use as a primary key in a table with an average of 2 billion records?

Bignint is much faster than cvarchar or varbinary. at anbay time..
And so you can have 18446744073709551615 (unsigned) Rows.
You would use uuids, when you enter data in different databases and try to unite them.
but if you only need unique ids, use bigint.


Change of index key columns order affects performance I have the following table in my database:

create table [dbo].[tb_StatusLog](
[RequestID] [int] not null,
[statusID] [int] not null,
[startTime] [datetime] not null,
[endTime] [datetime] null,
)

with the following non-clustered index

create nonclusteredindex [index1] on [dbo].[tb_StatusLog]
(
requestID asc,
statusID asc
)
include ([startTime])

and the following table-valued function fn_getTable

select requestID, max(startTime)
from tb_StatusLog
where statusID=2
group by requestID, statusID

after running profiler trace and running the results by db engine tuning advisor, it proposed that I create the following index (which is the same index I have but with the key columns reversed):

create nonclusteredindex [index2] on [dbo].[tb_StatusLog]
(
statusID asc,
requestID asc
)
include ([startTime])

Now, when running fn_getTable, the execution plan uses index2 instead of index1 and the performance improved.

Why is that?

Because you have an equality predicate on where statusID=2.

With the reversed order it is able to seek into exactly the rows matching the status and these can then feed into a stream aggregate to do the grouping.

Your original index supports the group by requestID, statusID but not the WHERE - meaning the whole index will need to be scanned and only rows matching the statusID=2 retained.


Want to know whether an 'UPSERT' operation performed INSERT or UPDATE I have a query where I try to insert a record and ready to update in case the record exists.

The question is that I would like to know, once the query is executed, if the operation has been an update or an insert.

Any idea?

According to MySQL documentation:

  With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

So, if your statement would affect just one row, if the number of affected rows is 1, then it is an INSERT; otherwise, an UPDATE. To retrieve the number of affected rows using PDO, use rowCount method.


Rebuild index online without risk to other requests We have a SQL Server 2012 Enterprise Edition database whose indices fragment very quickly.

We use a maintenance plan that reorganizes those indexes daily, and rebuilds them weekly. The rebuild happens online and uses tempdb (Enterprise Edition SQL Server feature).

However during the 2 minutes of the rebuild, other connections with the database suffer. We get several timeouts or deadlocks. Sometimes the rebuild itself fails with a deadlock as well.

The rebuild failing is no problem, we can just try again. However other connections timing out or even taking very long is very troublesome.

Is there a way to run the rebuild with a lower priority, or automatically fail if the current processing resources will not suffice for an online rebuild?

There is no window of downtime for this database, so we cannot rebuild indices offline. Is there any way we can rebuild the indices online without significant disturbance of other requests?

There are several options available to help reduce the problems you're seeing:

/* set the LOCK_TIMEOUT value to the maximum number of milliseconds
   you want to wait for the ALTER INDEX REBUILD to start
*/
SET LOCK_TIMEOUT 100;

/* this tells SQL Server our ALTER INDEX REBUILD statement should
   be the deadlock victim
*/
SET DEADLOCK_PRIORITY LOW;

/* Rebuild the index */
ALTER INDEX MyIndex
ON MySchema.MyTable
REBUILD WITH (
    ONLINE = ON
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

I know you have SQL Server 2012, however with SQL Server 2014+, you can use the WAIT_AT_LOW_PRIORITY option to tell SQL Server to allow other sessions to proceed while the online index build operation is waiting to obtain its schema modification locks.

ALTER INDEX MyIndex
ON MySchema.MyTable
REBUILD WITH (
    ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

From the MSDN SQL Server Books Online:

  In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. In all 3 cases, if during the wait time ( (MAX_DURATION = n [minutes]) ) there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.


SELECT all rows with sequential id after a specific id Is it possible to query all rows that their ids are sequential after a specific id? (id is an AUTO_INCREMENT column.)
For example, suppose my specific id is 5. Then I want all rows with these ids:
6, 7, 8, 9, ... until this sequence exists. E.g. if there is no row with id=12 then the sequence must be stopped at 11. So desired result would be rows with these ids:
6, 7, 8, 9, 10, 11.
I use MySQL.

EDIT:
An example:
This is my table:
mysql> SELECT * FROM Inheritance ORDER BY id ASC;
+-------------+-------------------+------------+-------------+-------+
| classNS     | class             | super      | superNS     | id    |
+-------------+-------------------+------------+-------------+-------+
|             | Data              | Object     |             | 00002 |
|             | Thing             | Object     |             | 00003 |
| BUILD       | DATA              | Data       |             | 00004 |
|             | Dialable          | Data       |             | 00005 |
|             | Phone             | Data       |             | 00006 |
|             | Email             | Data       |             | 00007 |
|             | Place             | Data       |             | 00008 |
|             | Website           | Data       |             | 00009 |
|             | SocialNetworkLink | Data       |             | 00011 |
|             | Password          | Data       |             | 00012 |
|             | User              | Thing      |             | 00013 |
|             | Asset             | Thing      |             | 00014 |
|             | Collection        | Asset      |             | 00015 |
|             | Product           | Asset      |             | 00016 |
| collections | School            | Collection |             | 00017 |
| collections | LangSchool        | Collection |             | 00018 |
| BUILD       | OWNER             | DATA       | BUILD       | 00022 |
| BUILD       | OWNER_OF_PRODUCT  | DATA       | BUILD       | 00023 |
| BUILD       | OWNER_OF_EMAIL    | DATA       | BUILD       | 00024 |
| BUILD       | COUNTRY           | DATA       | BUILD       | 00025 |
| BUILD       | SOCIAL_NETWORK    | DATA       | BUILD       | 00026 |
| BUILD       | PROVINCE_IR       | DATA       | BUILD       | 00027 |
| collections | SchoolPlus        | School     | collections | 00028 |
+-------------+-------------------+------------+-------------+-------+
23 rows in set (0.00 sec)

Then I want this result (when my specific id is 5):
+---------+----------+-------+---------+-------+
| classNS | class    | super | superNS | id    |
+---------+----------+-------+---------+-------+
|         | Phone    | Data  |         | 00006 |
|         | Email    | Data  |         | 00007 |
|         | Place    | Data  |         | 00008 |
|         | Website  | Data  |         | 00009 |
+---------+----------+-------+---------+-------+

Notice that the row with id=10 is absent in the source table.

A query that doesn't use recursive CTEs or window functions or variables:

SELECT i.*
FROM Inheritance AS i
    JOIN
    ( SELECT ui.id
      FROM Inheritance AS ui
      WHERE ui.id > 5
        AND NOT EXISTS
            ( SELECT 1
              FROM Inheritance AS pi
              WHERE pi.id = ui.id - 1
            )
      ORDER BY ui.id
      LIMIT 1
    ) AS up
    ON  i.id < up.id
WHERE i.id > 5 ;


Big Data storage, for relative data storage I have 2 tables in MySQL DB.

account - Around million records and increasing

id
name

proposal - 5,000 records and increasing

id
name

I have some algorithm that runs and find fit between account and proposal I want save the matches in DB, that will be filled from some queue manager, etc. So I can fetch the proposals much faster.

So that table will look like

account_proposal

account_id
proposal_id

As I understand I need to save at most 1M X 5K records I think it will be too heavy for MySQL and I want to solve the problem for future, when I will have 30M Accounts and 0.5M proposals.

So my question is which db engine fits more for saving such data?

This is not a database question, this is a mathematical question.  Mathematicians love problems like that -- find the best match between these 30M things and those 500K things.  It's sort of like matching your picture against all images in Facebook.

The mathematicians will (you can hope) come up with a way of chopping the problem up into pieces and coming up with an approximate best fit in a few million operations instead of 15 trillion operations.  Once you find that algorithm, then database gurus can discuss how to best store and fetch the data involved.

You will need to give the math guys the details of the function for metric of "closeness".  Part of the answer will probably take advantage of those details.


SQL Server: Stored Procedure to add parent & child records My SQL is pretty good, and I have programming experience in a number of languages. However, I have only dabbled in stored procedures for SQL Server.

I would like to write a procedure to add a parent record with child records in a related table. This is the sort of thing you would do to add an invoice in an invoice table and invoice items to an invoiceitems table.

I know the process would be as follows:

Create the invoice record
Get the primary key of the invoice record (presuming it’s auto-numbered)
Loop: add multiple invoiceitem records, with the foreign key set to the primary key in step 2 above.
(Possibly) update the parent invoice parent record with anything derived from the child records (such as a total).

I can handle most of that with what I already know. However:

how can I send the procedure the data to be processed?
inside the procedure how do I loop through the data?

You could use Table-Valued Parameters to pass a set of InvoiceItems to a procedure that will insert a new Invoice and also insert the InvoiceItems to the InvoiceItems table.

Without knowing your table schema, I am just going to throw in a few columns.

Example user-defined table type for use as a table-valued parameter:

create type dbo.InvoiceItems_udt as table (
    ProductId int
  , Quantity decimal(9,2)
  , LineTotal decimal(19,2)
    );
go

Example procedure using scope_identity() to get the InvoiceId after inserting a row into dbo.Invoice:

create procedure dbo.Invoice_Insert_WithItems (
    @InvoiceId int output
  , @CustomerId int not null
  , @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
  set nocount on, xact_abort on;
  begin tran;
    insert into dbo.Invoice (CustomerId, InvoiceTotal)
      select @CustomerId, InvoiceTotal = sum(LineTotal)
        from @InvoiceItems i;

    select @InvoiceId = convert(int,scope_identity());

    insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
      select @InvoiceId, ProductId, Quantity, LineTotal
        from @InvoiceItems i;
  commit tran;
end;
go

Table-valued parameter reference:

SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven! - Leonard Lobel
Using Table-Valued Parameters in SQL Server and .NET - Erland Sommarskog
Maximizing Performance with Table-Valued Parameters - Dan Guzman
User-Defined Table Types - msdn
Table-Valued Parameters - msdn

In SQL Server 2012+, an alternative to using an auto-numbered identity() for InvoiceId, you could use a sequence.

Example of how to create and use a sequence as the primary key on dbo.Invoice:

create sequence dbo.InvoiceIdSequence as int
  start with 1
  increment by 1;

create table dbo.Invoice (
    InvoiceId  int not null default next value for dbo.InvoiceIdSequence
  , CustomerId int not null
  , InvoiceTotal decimal(19,2)
  , constraint pk_Invoice primary key clustered (InvoiceId)
  , constraint fk_Invoice_Customer foreign key (CustomerId)
      references dbo.Customer(CustomerId)
  );

In this example, instead of using scope_identity() after inserting a row into dbo.Invoice,
we would use next value for dbo.InvoiceIdSequence before inserting a row into dbo.Invoice.

create procedure dbo.Invoice_Insert_WithItems (
    @InvoiceId int output
  , @CustomerId int not null
  , @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
  set nocount on, xact_abort on;
  begin tran;
    set @InvoiceId = next value for dbo.InvoiceIdSequence;

    insert into dbo.Invoice (CustomerId, InvoiceId, InvoiceTotal)
      select @CustomerId, @InvoiceId, InvoiceTotal = sum(LineTotal)
        from @InvoiceItems i;

    insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
      select @InvoiceId, ProductId, Quantity, LineTotal
        from @InvoiceItems i;
  commit tran;
end;
go

Sequence reference:

Simple Talk - Sql Server sequence basics - Joe Celko
sequence - msdn
next value for - msdn