How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA? I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go.

The application currently consists of a command to update the data; and one to query it. Naturally, I'll also need to maintain the database in other ways (create new tables, views, triggers, etc).

While this application will be the only one hosted on the server at first, I'd prefer to bake in the assumption that it might be hosted on a server with other databases in the future, rather than having to scramble later if that becomes necessary in the future.

I would think that these would be a fairly common set of requirements, but I'm having trouble finding a simple tutorial explaining how to set up a new database in PostgreSQL, with this sort of user/privilege separation. The references go on at length about groups, users, roles, databases,
schemas and domain; but I find them confusing.

Here's what I've tried so far (from within psql as 'postgres'):

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr   WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';

GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;

But I'm not getting the intended semantics. I want to have it configured so only the hostdb_admin can create (and drop and alter) tables; the hostdb_mgr can read, insert, update and delete on all tables by default; and the hostdb_usr can only read all tables (and views).

When I tried this I found that I was able to create tables in hostdb as any of these users; but, for each user, I could only read or modify tables created by that user - unless I use an explicit GRANT.

I'm guessing that there's something missing between CREATE DATABASE and CREATE SCHEMA, something to apply the SCHEMA to the DATABASE?

(As things get more advanced I'll also have questions to apply similar restrictions on TRIGGERS, stored procedures, VIEWS and perhaps other objects).

Where can I find a decent guide, tutorial or video series on this?

Where can I find a decent guide, tutorial or video series on this?

You'll find everything in the manual. Links below.
Granted, the matter is not trivial and sometimes confusing. Here is a recipe for the use case:
Recipe

I want to have it configured so only the hostdb_admin can create
(and drop and alter) tables;
the hostdb_mgr can read, insert, update and delete on all tables by default;
and the hostdb_usr can only read all tables (and views).

As superuser postgres:
CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

If you want a more powerful admin that can also manage databases and roles, add the role attributes CREATEDB and CREATEROLE above.
Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):
GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

I am naming the schema schma (not hostdb which would be confusing). Pick any name. Optionally make schma_admin the owner of the schema:
CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional

For and drop and alter see notes below.
Postgres 14 adds the predefined, non-login roles pg_read_all_data and pg_write_all_data to give read-only / write-only access to all objects in all schemas. That goes beyond what's being asked here, but may be useful. See:

PostgreSQL: Give all permissions to a user on a PostgreSQL database

As things get more advanced I'll also have questions to apply similar
restrictions on TRIGGERS, stored procedures, VIEWS and perhaps other objects.

Views are special. For one:

... (but note that ALL TABLES is considered to include views and foreign tables).

And for Updatable Views:

Note that the user performing the insert, update or delete on the view
must have the corresponding insert, update or delete privilege on the
view. In addition the view's owner must have the relevant privileges
on the underlying base relations, but the user performing the update
does not need any permissions on the underlying base relations (see
Section 38.5).

Triggers are special, too. You need the TRIGGER privilege on the table, and:

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

But we are already over-expanding the scope of this question ...
Important Notes
Ownership
If you want to allow schma_admin (alone) to drop and alter tables, make the role own all objects. The documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.
(However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.)
The owner implicitly has all grant options for the object, too.

ALTER TABLE some_tbl OWNER TO schma_admin;

Or create all objects with the role schma_admin to begin with, then you need not set the owner explicitly. It also simplifies default privileges, which you then only have to set for the one role:
Pre-existing objects
Default privileges only apply for newly created objects and only for the particular role they are created with. You'll want to adapt permissions for existing objects, too:

Permission denied for relation <table>

The same applies if you create objects with a role that does not have DEFAULT PRIVILEGES set, like the superuser postgres.  Reassign ownership to schma_admin and set privileges manually - or set DEFAULT PRIVILEGES for postgres as well (while connected to the right DB!):
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

Default privileges
You were missing an important aspect of the ALTER DEFAULT PRIVILEGES command. It applies to the current role unless specified otherwise:

Can't alter default privileges

Default privileges only apply to the current database. So you don't mess with other databases in the DB cluster. The documentation:

for all objects created in the current database

You may also want to set default privileges for FUNCTIONS and TYPES (not just TABLES and SEQUENCES), but those might not be needed.
Default privileges for PUBLIC
Default privileges granted to PUBLIC are rudimentary and overestimated by some. The documentation:

PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default
privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE
privilege for languages.

Bold emphasis mine. typically the one command above is enough to cover everything:
REVOKE ALL ON DATABASE hostdb FROM public;

In particular, no default privileges are granted to PUBLIC for new schemas. It may be confusing that the default schema named "public" starts with ALL privileges for PUBLIC. That's just a convenience feature to ease the start with newly created databases. It does not affect other schemas in any way. You can revoke these privileges in the template database template1, then all newly created databases in this cluster start without them:
\connect template1
REVOKE ALL ON SCHEMA public FROM public;

The privilege TEMP
Since we revoked all privileges on hostdb from PUBLIC, regular users cannot create temporary tables unless we explicitly allow it. You may or may not want to add this:
GRANT TEMP ON DATABASE hostdb TO schma_mgr;

search_path
Don't forget to set the search_path. If you only got the one database in the cluster you can just set the global default in postgresql.conf.
Else (more likely) set it as property of the database, or just for involved roles or even the combination of both. Details:

How does the search_path influence identifier resolution and the “current schema”

You may want to set it to schma, public if you use the public schema as well, or even (less likely) $user, schma, public ...
An alternative would be to use the default schema "public" which should work with default settings for search_path unless you changed that. Remember to revoke privileges for PUBLIC in this case.
Related

Grant privileges for a particular database in PostgreSQL
PostgreSQL - DB user should only be allowed to call functions


Create a Table only Schema We use JPA to create the objects that map to our database tables, however whenever it starts looking at our views the entire entity generation process grinds to a halt.

Is there a way in SQL Server to create a schema that only has access to the tables in our database?

Ok so this isn't perfect put it works well enough for me.
Note this is a development database where we're trying to make life easier for the developers rather than a production database.

What i did was create a TablesOnly user that people must login with when they're generating entities.
I had 2 scenarios that I had to deal with - Current tables and tables that will be created in the future.

First I ran this cursor to allocate the current tables to my 'TablesOnly' user

DECLARE @username VARCHAR(50) -- username
DECLARE @tablename VARCHAR(50) -- database name

SELECT @username = 'tablesOnly'

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.tables

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

        EXECUTE ('GRANT SELECT ON '+@tablename+' TO '+@username)
        EXECUTE ('GRANT VIEW DEFINITION ON '+@tablename+' TO '+@username)

        FETCH NEXT FROM db_cursor INTO @tablename
END

CLOSE db_cursor
DEALLOCATE db_cursor

Then I created this trigger on a table create that automatically allocated the permissions that I need to the user

CREATE TRIGGER UpdateTablesOnlyUser
ON DATABASE

    FOR CREATE_TABLE
    AS
        set nocount on

        DECLARE @data XML, @ObjectName sysname
        set @data = EVENTDATA()
        SET @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

        PRINT 'GRANT SELECT ON '+@objectName+' TO tablesOnly'
        PRINT 'GRANT VIEW DEFINITION ON '+@objectName+' TO tablesOnly'
        EXECUTE ('GRANT SELECT ON '+@objectName+' TO tablesOnly')
        EXECUTE ('GRANT VIEW DEFINITION ON '+@objectName+' TO tablesOnly')


MySQL Dump all rountines except one We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was wondering, is there a way to exclude only procedure from the dump?

For tables there is an "ignore-table" option in MySQL. Is there something similar or a workaround to do this for one procedures in particular?

There is no built-in way to do so with mysqldump. However, all is not lost, you should be able to get away with it using sed.

Let's say your routine is a function, which is called split_token. The following code:

mysqldump --all-databases ... | sed "/DROP FUNCTION IF EXISTS \`split_token\`/,/^DELIMITER ;$/d"

will disregard the lines starting with DROP FUNCTION... and until the DELIMITER statement which ends the function's definition.

The above does not verify:

A function of the same name may exist in multiple databases
The text may appear within a string -- need better regexp matching (try and match the code starting the line using ^ -- look at the following line:

/*!50003 DROP FUNCTION IF EXISTS split_token */;

Leaving this up to you to find the best regular expression.


OUTER JOIN with join condition causes unexpected results SQL Server 2008 R2, but likely behaviour is found on all other versions of SQL Server as well.

This may seem obvious, but to me it seems like a bug.

The following query gives unexpected results, here is the setup:

CREATE TABLE #Base (
Key1 int,
RefDate date
)

CREATE TABLE #JoinTable (
    Key1 int,
    RefDate date
    )

INSERT INTO #Base
SELECT 1, '2012-05-05'
UNION
SELECT 2, '2013-06-06'
UNION
SELECT 3, '2014-07-07'
UNION
SELECT 4, '2015-08-08'
UNION
SELECT 5, '2016-09-09'

INSERT INTO #JoinTable
SELECT 4, '2012-05-05'
UNION
SELECT 5, '2013-06-06'
UNION
SELECT 6, '2014-07-07'
UNION
SELECT 7, '2015-08-08'
UNION
SELECT 8, '2016-09-09'

The following query executes as I would expect, returning the 3 rows that only occur in the base table:

SELECT *
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1
WHERE j.Key1 IS NULL

Now I would like to restrict the rows to those before 1st of Jan 2014. As I understand it, I can take the two approaches below, both should be perfectly viable:

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'
WHERE j.Key1 IS NULL

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1
WHERE j.Key1 IS NULL AND b.RefDate < '2014-01-01'

However, only the 2nd query returns the data that I want. The 1st appears to ignore the added join condition after the AND in the join line.

Why?

(sql fiddle link here)

It is not a bug, it is how LEFT JOIN works.

b LEFT JOIN j ON <SomeCondition>

would return all rows from b regardless of the join condition. Join condition defines/limits only what rows would be returned from j.

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'
WHERE j.Key1 IS NULL

You can think of it as follows:

Go through all rows of #Base.
For each row in #Base find all rows from #JoinTable that satisfy the join criteria (b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'). There could be no such rows in #JoinTable, in this case return NULLs. Note, that join criteria doesn't filter #Base, it filters only #JoinTable.

When you put b.RefDate < '2014-01-01' into WHERE, then you are filtering #Base.


How does Postgres make its B-tree index? I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size.
PostgreSQL manual about the index here and here about the block size, which is 100 for aux so having 3 reads.

The default memory taken by the block size is 8 KB i.e. 1 block normally but I am not sure if this can be possible because log_1(2) is infinity.
I got the idea that the number of reads is dynamically calculated possibly also in PostgreSQL here by considering B-tree where the block size determines the number of reads.
I would like to know how much this is in the block size: log_b n where b is the block size and n is the number of events.
I think it cannot be one mathematically.
I think Postgres B-tree is implemented the standard way as described in the Wiki page, also described by Cormen et al.

B-tree index contains only keys in PostgreSQL, based on this answer.
Data is then again in tables which are logical heaps.
The point of indexes is to store keys to begin with. Data lies in tables, which are logical heaps, based on here.
I am interested in how PostgresSQL make the entity called B-tree.
Based on here, the physical storage of B-tree indexes and tables are using the same data pages with mostly the same page-layout.
However, I am interested in how this entity works together.
Probably, the functioning of the index and data can be described by this:

  B-tree grows from root, not from leaves.

but more precisely from Sumathi about Fundamentals of Relational Database Management Systems (Studies in Computational Intelligence):

  In B-tree, nonleaf nodes are larger than leaf nodes.  Pointers to data
  records exist at all level of the tree.

In B+tree, pointers exists only at the leaves.
How can you evalute the pointer system of B-tree?
How can you describe the big-O space taken by PostgreSQL B-tree?
How does Postgres make its B-tree index?

Masi,

The PostgreSQL B-Tree index is very strongly based on the implementation by Lehman and Yao, which includes a lot of work oriented around multi-version concurrency control, but there's still great info in this paper.

Of course, PostgreSQL doesn't make a 100% accurate replica of the method in the paper, and to find the differences, there will be almost no way to do it other than to (1) find someone who understands the PostgreSQL B-Tree, and has the time to go through the intricate explanation, or (2) dig through the source code yourself.

Another possibility is for you to visit Bruce Momjian's excellent reference website, where he discusses PostgreSQL internals in more detail.

In this case, however, based on the nature of your questions, I feel like you may have a fundamental misunderstanding about how B-Tree indexes work. In this case, I think a little Google searching, or maybe reading through a portion of a textbook like Fundamentals of Database Systems by Elmasri & Navathe would do you some good.


Prevent Subscription to report from running on a holiday I have a SQL Server that I am using for building and distributing reports.  I have built all the packages for importing data as needed as SSIS packages.  These reports only need to be run after the data has been imported.  I have built my SSIS packages to check and make sure that the day isn't a holiday by comparing the date to a table that contains holidays.  This way my data is only imported on business days.  The problem is, my report subscriptions are set to run at a specific time Monday - Friday.  How can I programmatically run the subscription at the end of my import, so that it only runs on the days that the import runs and not on the holidays?

have built my SSIS packages to check and make sure that the day isn't a holiday by comparing the date to a table that contains holidays.

You can have your 1st job step as

IF EXISTS (SELECT 1 FROM HolidayTable WHERE Date = GETDATE())
  begin
   RAISERROR ('There is nothing to run as today is holiday !', 16, 1)
  end
 else
   print 'The job can run .. as its a working day !'

The second step is to run the SSIS package only if the above condition is FALSE. If the above step is true meaning if it is a holiday as per your HOLIDAY Table, then it should silently fail.


SQL Join vs NoSQL Wide column If we need to have 2 tables, one storing the author info(author_info), & another storing all the books info(book_info).

author_info has authorId as it's primary key, whereas the book_info table has bookId has it's primary key.

Now to answer a question like fetch all books written by an author in an OLTP system (latency is key), which of the following 2 will be better:

1) RDBMS: Having authorId as a foreign key in the book_info table, and joining the two tables based on the authorId.

2) Wide Column Store: Modifying the second table to be like a wide-column DB, wherein we author_id as the primary key, and simply query the second table based in author_id.

I would go with option 3, and have a many to many mapping table to associate Books and Authors.  What if you have a book with more than one author? I've included an example for you below.

Your second approach could work for you.  But the answer as to what's best is how will you be querying for the information?  If you are going to query for data inside the Wide Column Store, that's going to be slow through SQL.

CREATE TABLE dbo.Author
    (
    AuthorID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AuthorName NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Book
    (
    BookID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , BookName NVARCHAR(100) NOT NULL
    , ISBN NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.AuthorBook
    (
    AuthorBookID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AuthorID INT NOT NULL REFERENCES dbo.Author (AuthorID)
    , BookID INT NOT NULL REFERENCES dbo.Book (BookID)
    )


Query to select max value on join I have a table of Users:

|Username|UserType|Points|
|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

and Levels

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

And I'm looking for a query to get the level for each user. Something along the lines of:

SELECT *
FROM Users U
INNER JOIN (
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

Such that the results would be:

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

Does anyone have any ideas or suggestions on how could I do this without resorting to cursors?

Your existing query is close to something that you could use but you can get the result easily by making a few changes.  By altering your query to use the APPLY operator and implementing CROSS APPLY.  This will return the row that meets your requirements.  Here's a version that you could use:

SELECT
  u.Username,
  u.UserType,
  u.Points,
  lv.Level
FROM Users u
CROSS APPLY
(
  SELECT TOP 1 Level
  FROM Levels l
  WHERE u.UserType = l.UserType
     and l.MinPoints < u.Points
  ORDER BY l.MinPoints desc
) lv;

Here's a SQL Fiddle with a demo.  This produces a result:

| Username | UserType | Points |  Level |
|----------|----------|--------|--------|
|     John |        A |    250 | Silver |
|     Mary |        A |    150 | Bronze |
|     Anna |        B |    600 | Bronze |


Select only those records that have same multiple values for a particular column if it's existing? Below is an example of my pricing_supports table have more than 3 milions rows.
There some rows having same values in the first_origin_id, delivery_id fields.
If there're records with same first_origin_id, delivery_id but different source, I only want to select those records source = 0

ID   code first_origin_id    delivery_id    source
1      A       10                20              0
2      B       10                20              1
3      C       11                21              1
4      D       12                22              0
5      E       12                22              1

I would like result like that:

ID   code first_origin_id    delivery_id    source
1      A       10                20              0
3      C       11                21              1
4      D       12                22              0

How can i do for good performace?

By using ROW_NUMBER() and PARTITION BY first_origin_id, delivery_id then ORDER BY source, you can achieve your expected result.

So the query will be:

SELECT ID, code, first_origin_id, delivery_id, source FROM (
  SELECT ID, code, first_origin_id, delivery_id, source,
  ROW_NUMBER() OVER (PARTITION BY first_origin_id, delivery_id ORDER BY source, ID) AS RowNumber
  FROM pricing_supports
) Q
WHERE Q.RowNumber = 1

Please find the Live Demo


Slide Partition from Rowstore to Columnstore I have a rowstore table which contains ~10 billion records.  The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same Partition Scheme / Function. Can I switch an active partition from the Rowstore table to the Columnstore table?

ALTER TABLE [dbo].[RowstoreTable] SWITCH PARTITION 741 TO [dbo].[ColumnstoreTable] PARTITION 741

This database resides on SQL Server 2014 Enterprise Edition.

No, this is not possible. ALTER TABLE... SWITCH is a metadata operation and both tables need to have identical schemas. There's no such thing as a partitioned table that has some columnstore partitions and some rowstore partitions.

The best you can do is switch the rowstore partition out, build a clustered columnstore index on the switched out table, and then switch that table into the columnstore table.


installing SQL SERVER 2016 side-by-side SQL Server 2012 I am trying to install SQL Server 2016 on a server that already has SQL Server 2012 instances installed - all of the shared and instance features of the 2012 version have been installed also. I do not have an option of uninstalling these and I just need to install a separate, independent SQL SERVER 2016 instance.

According to MS, side-by-side is supported. But then it says: "you cannot prepare a SQL Server 2016 instance side by side with a prepared instance of SQL Server 2012"

What does this mean? So can I have them side-by-side without any issues or not?

Thank you very much

Installing SQL Server 2016 side by side with an existing SQL Server 2012 can (usually) be installed without issues and is supported as they state in that article.

  SQL Server supports multiple instances of the Database Engine,
  Analysis Services, and Reporting Services on the same computer.

What you can't do however is using SQL Server Sysprep to install different versions side by side.

SQL Server Sysprep is basically an image based deployment solution like you have Sysprep for windows images.

So as long as you manually run through the installation wizard you are fine, but you wouldn't be able to install a SQL Server image using a prepared image by selecting this option on the advanced tab:


PostgreSQL, finding elements by value in numeric JSON arrays I have a table defined as:

create table dummy (jdata jsonb);

I inserted the following two rows:

insert into dummy values ('["dog","cat","elephant","waffle"]');
insert into dummy values ('[1,2,3,4]');

I am trying to make use of the jsonb ?& operator which lets you ask the question "Do all of these key/element strings exist?"

An example using the string fields works:

select * from dummy where jdata ?& array['cat','dog'];
            jdata
--------------------------------------
["dog", "cat", "elephant", "waffle"]
(1 row)

But, trying to do this with an array that contains numbers does not work:

select * from dummy where jdata ?& array['1','2'];
  jdata
  -------
(0 rows)

select * from dummy where jdata ?& array['1','2'];
 jdata
 -------
 (0 rows)

select * from dummy where jdata ?& array[1,2];
ERROR:  operator does not exist: jsonb ?& integer[]
LINE 1: select * from dummy where jdata ?& array[1,2];
                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I know that the ?& operator works on text arrays, but therein lies the problem.  Does anyone know how to get the json operators to work on numeric arrays?

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT
  j,
  j @> '"dog"'::jsonb AS hasDog,
  j @> '["dog","waffle"]' AS hasDogAndWaffle,
  j @> '5'   AS has5,
  j @> '42'  AS has42
FROM ( VALUES
  ('[5,2,3]'::jsonb),
  ('["dog","cat","elephant","waffle"]'::jsonb)
)
  AS t(j);

                  j                   | hasdog | hasdogandwaffle | has5 | has42
--------------------------------------+--------+-----------------+------+-------
 [5, 2, 3]                            | f      | f               | t    | f
 ["dog", "cat", "elephant", "waffle"] | t      | t               | f    | f
(2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.


Database connections and related issues? If my tomcat is configured to accept 100 incoming requests concurrently, how many connections should be present in DB connection pool ? In other words, if there a known optimal ratio of number of connections / number of incoming requests ?
Is it possible that application servers have made more requests / transactions per second than the db is configured to handle ? If yes, then what happens ? Are transactions queued ? dropped ?

In general the max number of running sessions has a relation with the number of CPU cores on the database server. If you have 32 cores it does not make sense to allow more connections and expect them all to run concurrently.
Because in most of the times there will be some waiting, you could allow a few more but the database system itself also needs some CPU to work for you.
So your database connection pool should not have many more connections than there are available CPU cores to run them on.
When all CPU's are loaded, the application will have to wait for connections to return results and putting more stress on the database will slowdown the application more.
The optimal connection pool size can be smaller but depends on the transaction length and the number of concurrent transactions. If every transaction returns it's connection to the pool the system can handle a lot of work.


What is the relationship between the SSIS Package Store and Catalog? I have a server with a working installation of the Sql Server 2012 SSIS Catalog. I need to set up an additional instance including the SSIS Package Store service as an interim step while the packages are being re-written. The Package Store is a per-server feature, not a per-instance feature. Can these two features operate side-by-side?

There are two choices where to store SSIS Packages: the file system or the database. Within each of those, there are two variants.

The File System supports anywhere you want to put packages. The Package Store is just a well known location of %Program Files%\Microsoft SQL Server\NNN\DTS where NNN matches your version. The challenge with the Package Store is that for multi-instanced servers, everything is mixed together. SSMS, I would specify I want to connect to Server\DEV or Server\TEST. The Integration Services connector though, I'm just connecting to Server. You can create subfolders to attempt to isolate packages for environments but it's clunky. Not as clunky as the other approach of editing/swapping in different XML files to redirect where the Integration Services process defines the Package Store...

The Database only option pre-2012 was going to be tables in the msdb (and all the dba's rejoiced at the added backup bloat and testing of restores for msdb too). Depending on 2005 vs 2008 will the table will be called dbo.sysdtspackages90 or dbo.sysssispackage and they may also live in folders. Handy SSIS Package query

2012+ forward, we have a new choice and that is the SSISDB. There's a one-time creation required but it is per instance so you get proper isolation. This new SSIS repository though is only for packages that have been built under the Project Deployment Model. Before this, packages were a cohesive solution in Visual Studio/BIDS/SSDT but that was only for the development experience. After that, the packages could be spread to the four corners of the globe which is unfortunate if one package depended on another being in the same folder. The Project Deployment Model attempts to address this shortcoming by "compiling" all the packages in an SSIS project into a .ispac file. By compiling, it's really just zipping them together with a manifest file but now we have a deployable quantum and that's all you need for this to successfully run on a new environment *.

* As long as you have already installed the drivers, third party applications, set file system permissions, created users, applied configurations, etc but it is an improvement.

Back to the original question

Can the Package Store, a server wide feature, coexist with the new instance scoped SSISDB? Sure, why not?


Slow Connection to Azure SQL Database using SQL Server Authentication In SSMS, when connecting to any of our Azure SQL Databases using service accounts and SQL server authentication, the connecting itself can take between 10 to 15 seconds to establish.

Once connected, right click on any table and click on "select top 1000 rows" this also takes between 10 and 15 seconds to respond and present the rows in SSMS.

None of this delay/lag occurs when using "Active Directory - Universal with MFA Supprt" authentication - however, this isn't using the same service accounts.

How can the SQL Server Auth lag be resolved/investigated?

Here is the Microsoft explanation of this issue:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-132-delays-connecting-to-azure-sql-database-from/ba-p/1502030
They describe two types of authenticated users.  Ones with instance LOGIN's and ones that have only access to a single database ("contained" database users).
The problem with the slow SSMS connectivity only affects the first type of authentication (instance LOGIN's when they have access to a database but have no rights to the "master" database).  If you are able to switch to "contained" database users then you may avoid the problem altogether.


Oracle 12 set user password Expiration date or EXPIRED(Grace) Is it possible to set a future expiration date on a user account in Oracle?  Or to set a account status to EXPIRE(GRACE)

We want to implement a more strict password policy, and I've created the new Profile, but would like to use Oracle's password grace period function to warn users (instead of just running a script to immediately expire the user accounts).

(In short, yes I can and will send out an email telling everyone their password will expire in 10 days, but I'd rather have Oracle tell them when they log in so they can't deny seeing the warning)

I don't see an option for ALTER USER... for expiration dates.

Also when I added some users to the new profile (which has a 90 day Password Life Time and a 30 day Password Grace Time for some reason it automatically set their expiration date to 51 days and 6 hours from now, which I thought was rather odd.

You have to use a profile and a combination of password_life_time and password_grace_time; Lifetime will set the expiry time of password and grace_time will warn them (after expiry) as you'd expect. All you then need to do is assign the profile to user or users (using alter user).

A quick demo?

SQL> set echo on
SQL> drop user testa cascade;
User TESTA dropped.
SQL> create user testa identified by testa;
User TESTA created.
SQL> grant create session to testa;
Grant succeeded.
SQL> prompt this connection should be successful as there is no profile attached.
this connection should be successful as there is no profile attached.
SQL> conn testa/testa
Connected.
SQL> connect as a dba user
Connected.
SQL> drop profile test_profile;
Profile TEST_PROFILE dropped.
SQL> -- using profile i am setting password to expire in about 10 seconds useful for our testing
SQL> -- 0.0001158 is about 10 (10/86400)
SQL> -- grace time is set to 1 day
SQL> create profile test_profile limit password_life_time 0.0001158 password_grace_time 1;
Profile TEST_PROFILE created.
SQL> -- now you assign the profile to the user who should get warning .
SQL> alter user testa profile test_profile;
User TESTA altered.
SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
SQL> prompt this connection should show a warning
this connection should show a warning
SQL> conn testa/testa
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> select account_status ,expiry_date, PROFILE, sysdate from user_users;
ACCOUNT_STATUS   EXPIRY_DATE         PROFILE        SYSDATE
---------------- ------------------- -------------- -------------------
EXPIRED(GRACE)   03/21/2017 14:56:00 TEST_PROFILE   03/20/2017 14:56:00


How to return the difference (columns) between two databases or tables I have a database that has been updated, many columns have been added. Currently the only way of returning the differences between the two databases is by going through the database.cs class that is autogenerated and comparing it with the old database.cs file. I have both database on Sql Server Management Studio (the new structure, and the old structure of the databsae) and want to see all the differences. How can I return the differences between the databases and tables.

E.g.

OLD Db  New Db
TabA    TabA
Col1    Col1
Col2    Col2
Col3    Col3
        Col4

Output
Col4 int not null

You can use the applications listed in comments, or use SQL Server Data Tools. What I like about SSDT is it is a native SQL Server development environment, works well with source control, and has a really good set of features for database development.

You should be able to install from your SQL Server 2014 media or just download it. You can then use Schema Compare between database projects or deployed databases. In the example image I have compared two dev servers, and after selecting a Procedure that has a detected difference, I can inspect what has changed. You can script out the update and inspect or edit it or simply push the change to the target server. You could also import either into a database project and put it under source control.


High RAM memory usage I'm currently testing a new MySQL server, but I'm new in this so I'm not sure about my parameters, because the RAM is always above 90% of usage.

The server will be setup for a CRM environment of 100 people.

Server specs:

MySQL version 5.7.28-0ubuntu0.18.04.4-log
16GB of RAM

Changes to mysqld.conf:

key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size   = 8

# My changes

innodb_buffer_pool_instances = 12
innodb_buffer_pool_size     = 12G
innodb_flush_method         = O_DIRECT
innodb_log_file_size        = 1G
innodb_page_cleaners        = 4
innodb_purge_threads        = 4
max_connections             = 151
open_files_limit            = 112274
table_open_cache            = 56137

log_output                  = FILE
default-storage-engine      = INNODB
character-set-server        = utf8
innodb_lock_wait_timeout    = 300
read_buffer_size            = 64K

# Recommendations [mysqltuner]

long_query_time         = 10
query_cache_size        = 0
query_cache_type        = 0
query_cache_limit       = 2M
join_buffer_size        = 512K
table_definition_cache  = 28468
log_bin                 = /var/log/mysql/mysql-bin.log

What mysqltuner says in InnoDB Metrics:

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 12.0G/22.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (66.6666666666667 %): 4.0G * 2/12.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 96 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (5004553901 hits/ 5004951598 total)
[OK] InnoDB Write log efficiency: 96.96% (8250637 hits/ 8509152 total)
[OK] InnoDB log waits: 0.00% (0 waits / 258515 writes)

Can you help me with this? Should I change anything else in the conf file? Should I upgrade the hardware?

EDIT:

GLOBAL STATUS: https://pastebin.com/1kbHTGkL

GLOBAL VARIABLES: https://pastebin.com/KUrBh9wr

FULL PROCESSLIST: https://pastebin.com/39GZdeWB

MySQLTuner report: https://pastebin.com/Khdgf7GP

EDIT 2:

I'm using SSD Storage

ulimit: https://pastebin.com/aUAtjnUN

iostat: https://pastebin.com/kjr9GaLB

In general, you want to utilize as much RAM as possible (leaving enough for the O/S, etc), since that means data is in-memory instead of being on disk.

For a CRM system serving 100 people, you may find 16GB of RAM dedicated to the server is not enough.  My phone has 8GB of RAM, for instance, and it's just a phone.

In-memory access times for data is a couple of orders of magnitude faster than accessing data on disk.


Never Ending OLEDB Wait for Linked Server We created a linked server to connect SQL Server to our ERP (running UniVerse not SQL Server). It has worked fine in the past, but now any queries to the linked server just get stuck (for days) with an OLEDB wait type.

This has happened on rare occasions previously, and restarting the SQL Server has always resolved the issue. Now however, restarting the server either does not solve the issue or it fixes it for at the most an hour or two.

We are experiencing the issue in both SQL Server 2016 and 2008 R2.

We are able to query the data source using other tools on the same server and with the same driver as the SQL Server. On one server, we are running two instances of SQL Server 2008 R2. One is able to query the data source, while other gets stuck with the OLEDB wait type. The linked server on both is set up exactly the same way, and they are using the same driver.

We hare tried:

Restarting SQL Server
Restarting the server the data source is on
Different drivers
Recreating the linked server
Querying the data source using a different tool using the same driver (it was successful)
We are able to ping the data source server from the sever with the affected SQL Server
We are able to connect to the data source using Telnet from the affected server

For SQL Server targets:
On the target server (the one that's receiving the linked server query), run sp_WhoIsActive and look at the linked server query.

If the query doesn't show up, something's going awry with the linked server query and it's never reaching its destination.

If it does show up, look at the Wait column, and see what the wait type is. (It won't be OLEDB.) That'll help you troubleshoot the reason it's not completing. Feel free to post that in with your question, and I can elaborate more on that specific wait type.

For non-SQL Server targets like the IBM/Rocket one in this case: you'll want to work with the sysadmin/DBA on that platform, and have them check the progress of the query. When you see OLEDB on the SQL Server sending side, you just can't tell anything about what the holdup is on the other end.


Does Galera Cluster rsync only updated files or all files We're currently setting up a galera cluster using rsync for sst.

The datadir is currently 20Gig and right now we're only updating about 4 (very large) tables.

I know that rsync normally only transfers file changes also that galera has rsync setup to ignore timestamps.

If we start sst, does rsync transfer the whole 20gig or only the files for the tables that have been updated?

Thanks

From what I can tell it looks like it will usually transfer the whole file instead of using the delta-xfer algorithm unless you set the wsrep_sst_rsync_wan option.  I assume this is because on a local network transferring the full file is close enough or as fast as calculating the deltas between the files.  The relevant lines in the SST script for MariaDB 10.0.14 galera is below.

# Use deltaxfer only for WAN
inv=$(basename $0)
[ "$inv" = "wsrep_sst_rsync_wan" ] && WHOLE_FILE_OPT="" \
                                       || WHOLE_FILE_OPT="--whole-file"


MySQL: Load csv file to mysql I'm having problems when loading a csv file into MySQL 5.7 with MySQL Workbench.

Example Data

ESH13   1/2/2013    10:04:35    1451    1
ESH13   1/2/2013    10:04:35    1451.25 2
ESH13   1/2/2013    10:04:35    1451    1
ESH13   1/2/2013    10:04:35    1451    1
ESH13   1/2/2013    10:04:36    1451    1
ESH13   1/2/2013    10:04:36    1451    2

Originally I tried using this [below] method to start:

SHOW VARIABLES LIKE "secure_file_priv";

CREATE TABLE es (
    id INT NOT NULL AUTO_INCREMENT,
    ticker VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    time DECIMAL(10 , 2 ) NULL,
    price DECIMAL(10 , 2 ) NULL,
    vol DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);

LOAD DATA INFILE 'C:\Users\YCM LLC\Desktop\HFT\ES_test.csv'
INTO TABLE es_test1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This error occurs when the above is run:

  Error Code: 1290. The MySQL server is running with the
  --secure-file-priv option so it cannot execute this statement.

I tried to modify C:\ProgramData\MySQL\MySQL Server 5.7\my.ini by blocking the statement as shown below, but I get an "Access is denied" when I try to save the changes.

# Secure File Priv.
# secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

I then tried to use of "local" keyword in the LOAD DATA INFILE statement:

    LOAD DATA local INFILE 'C:\Users\YCM LLC\Desktop\HFT\ES_test.csv'
    INTO TABLE es_test1
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

This error occurs when the above is run:

  Error Code: 2. File 'C:UsersYCM LLCDesktopHFTES_test.csv' not found
  (Errcode: 2 - No such file or directory)

I was able to get data to load after getting adjusting per the advice from [a] the person that answered my question in a comment, but I started having trouble loading time string data to a table

CREATE TABLE es (
    ticker VARCHAR(255) NOT NULL,
    xdate DATE NOT NULL,
    time Time NULL,
    price DECIMAL(10 , 2 ) NULL,
    vol DECIMAL(10 , 2 ) NULL
   );

LOAD DATA local INFILE 'C:\\Users\\YCM LLC\\Desktop\\HFT\\ES_test.csv'
INTO TABLE es
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(ticker, @var1, @var2, price, vol)
SET xdate = STR_TO_DATE(@var1, '%m/%d/%Y'),
    time = STR_TO_DATE(@var2, '%hh:%mm:%ss');

This error occurs when the above is run:

  6 row(s) affected, 6 warning(s): 1411 Incorrect datetime value:
  '10:04:35' for function str_to_date 1411 Incorrect datetime value:
  '10:04:35' for function str_to_date 1411 Incorrect datetime value:
  '10:04:35' for function str_to_date 1411 Incorrect datetime value:
  '10:04:35' for function

Issues when Loading Data with MySQL LOAD DATA INFILE

You appear to have a few issues going on here and to resolve you can make a few adjustments to get your data to load without error.

I've source referenced and quoted the items in more detail below so you can read up on each for a more thorough explanation.

In short though essentially you can:

Use the LOCAL keyword with the LOAD DATA INFILE statement to get the data to load with MySQL without needing to adjust the my.ini file parameters

LOAD DATA LOCAL INFILE '<file>'

Use double backslashes [\\] in the folder path to separate folders rather than just one to escape the first backslash since by default a single backslash is used as a special escape character and ignored when used alone

C:\\Users\\YCM LLC\\Desktop\\HFT\\ES_test.csv

With the STR_TO_DATE function for the second argument you pass it telling it the format for the time value in the first argument, you should use the %h:%i:%s or %H:%i:%s format

time = STR_TO_DATE(@var2, '%h:%i:%s');

 Further References

  The LOCAL keyword affects expected location of the file and error
  handling, as described later. LOCAL works only if your server and
  your client both have been configured to permit it. For example, if
  mysqld was
  started with the local_infile system variable disabled, LOCAL
  does not work. See Section 6.1.6, “Security Issues with LOAD DATA
  LOCAL”.

  The LOCAL keyword affects where the file is expected to be found:

  If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a
  full path name to specify its exact location. If given as a relative
  path name, the name is interpreted relative to the directory in which
  the client program was started.

  When using LOCAL with LOAD DATA, a copy of the file is
  created in the server's temporary directory. This is not the directory
  determined by the value of tmpdir or slave_load_tmpdir,
  but rather the operating system's temporary directory, and is not
  configurable in the MySQL Server. (Typically the system temporary
  directory is /tmp on Linux systems and C:\WINDOWS\TEMP on
  Windows.) Lack of sufficient space for the copy in this directory can
  cause the LOAD DATA LOCAL statement to fail.

  source

  Within a string, certain sequences have special meaning unless the
  NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (\), known as the escape
  character. MySQL recognizes the escape sequences shown in Table 9.1,
  “Special Character Escape
  Sequences”.
  For all other escape sequences, backslash is ignored. That is, the
  escaped character is interpreted as if it was not escaped.

    Table 9.1 Special Character Escape Sequences

    Escape Sequence: \\

    Character Represented by Sequence: A backslash (\) character

  source

  Description

  The MySQL STR_TO_DATE function takes a string and returns a date
  specified by a format mask.

  Syntax

  The syntax for the STR_TO_DATE function in MySQL is:

    STR_TO_DATE( string, format_mask )

  Parameters or Arguments

  string

  The string value to format as a date.

  format_mask

  The format to apply to string. The following is a list of options for
  the format_mask parameter. These parameters can be used in many
  combinations.

  Short List

    Value: %H

    Description: Hour (00 to 23)

    Value: %h

    Description: Hour (00 to 12)

    Value: %i

    Description: Minutes (00 to 59)

    Value: %s

    Description: Seconds (00 to 59)

  source


Sql Server 2008 Replication "Generate Scripts" option not available All,

On Sql Server 2008, I have Transaction Replication going from Server1 to Server3 (with Server2 as distribution).

I have an option to "Generate Scripts" on Publication on Server1. But I do not have that option to "Generate Scripts" for Subscription on Server3.

But on a different set of servers (Server6, Server7, Server8) with replication I have options to "Generate Scripts" on both Publication and Subscription.

Both are Sql Server 2008, but the later has Sql Serve 2008 R2 on it.

Could that be the reason? How do I get around it?

Thanks,
_UB

Edit:Grammar, and more grammar.

Are these push subscriptions?  If so, the Generate Scripts option will not be available on the subscribers.  To generate scripts for push subscriptions, right-click on the Publication on Server 1 -> Generate Scripts...  The generated script will contain T-SQL necessary to create the publication and the push subscriptions.

Pull subscriptions should have Generate Scripts option at the subscriber.


How to login into mysql shell from localhost instead of '%'? I need to grant a permission to a newly created user. I have access to root. But, i can't grant a permission to a new user. When I run:

grant all privileges on 'newuser'.* to 'newuser'@localhost;

I got this:

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'newuser'

The part that I don't understand is why do I logged in as 'root'@'%'? Since I logged in with this command:

mysql --protocol TCP -h localhost -u root -p

But this:

select user(), current_user();

gives me this:

+----------+----------------+
| user()   | current_user() |
+----------+----------------+
| root@::1 | root@%         |
+----------+----------------+

I know only 'root'@'localhost' has grant privilege. Then I check mysql.user table which gives me output (only different columns shown here):

+-----------+---------+------------+-----------------------+
| Host      | User    | Grant_priv | authentication_string |
+-----------+---------+------------+-----------------------+
| localhost | root    | Y          |                       |
| %         | root    | N          | NULL                  |
+-----------+---------+------------+-----------------------+

Other columns that is not included above for 'root'@'localhost' and 'root'@'%' are identical.

So, back to my question: How to login into mysql shell from localhost instead of '%'?

NOTE: I run mysql version 5.5.21 on windows 10 machine.

user() function shows what have your client answered when server asks "Who are you?".

Query shows that your client answers using IPv6 local address.

There is no such record in your mysql.user table. But server finds the record which client's information is matched to. current_user() function shows what account was applied by server for the authentication based on the client's info.

Simply tell your client that it must connect to server using IPv4 - in command line or in configuration file, or disable IPv6 somewhere - on the client, or on the server, or on the workstation. Or add one more user with grant privileges - 'root'@'::1' (recommended).

I don't know why your server cannot convert '::1' adddress to 'localhost' name. Maybe you have no proper record ::1 localhost in your HOSTS file?


One call to `decryptbypassphrase` for decrypting all columns If I use ENCRYPTBYPASSPHRASE to encrypt the full table then how can I decrypt full table easily by using DECRYPTBYPASSPHRASE?

Actually I have to use DECRYPTBYPASSPHRASE ('passphrase', column_name) for every column to decrypt it. So it becomes so lengthy command. If I want to decrypt all columns then I have to specify this statement for all columns. Instead of that, can I use DECRYPTBYPASSPHRASE in queries so that I can use it only once and it decrypts all the columns?

No. Scalar operators cannot magically transform themselves into relational operators. You use the ENCRYPTBYPASSPHRASE to encrypt a column (there is no way to 'encrypt a table') and you use DECRYPTBYPASSPHRASE to decrypt a column.

Using passphrases to encrypt the data directly is a very very poor choice. A proper key hierarchy is needed where data is encrypted with a set of symmetric keys and the symmetric keys are in turn encrypted with a certificate. Encrypting the data directly with a passphrase requires expensive size-of-data operation to rotate the keys.


Is it possible to have 1 slave server with multiple master? Here is my set of master server:
DbServer1 - 10.1.0.1
DbServer2 - 10.1.0.2
DbServer3 - 10.1.0.3
DbServer4 - 10.1.0.4
and my replica server would be on 10.1.0.5
Is this doable if I create 4 instances of MySQL on a single server with different ports? But keeping in mind that all of my Master are using the default port which is 3306

Yes, you can run on different port and configure replication through master.
If you have different table name or db name in all master, you can for with multi source replica in that case you just need to install single instance for replica node of all masters. You can go through here and comment if have any doubt and need more clarification.


Most efficient way to cascade delete in MySQL I have several 1-to-many FK relationships in my MySQL schema, with tables as follows:

AGENCYS  1-*>  ROUTES  1-*>  TRIPS  1-*>  STOPTIMES

At present, there are roughly 1300 agencys, 16000 routes, 1734765 trips and 69,000,000 stoptimes - and we'd like to scale much larger than this.  Each FK relationship has a CASCADE deletion rule set.

Each week, I need to delete all the agencys that have a particular agency_import_source, and delete all their descendants too (i.e. routes, trips and stoptimes).

The obvious query, to a non-expert such as myself, is this:

DELETE FROM agencies
WHERE agencys.agency_import_source = "UK"

However, this is timing out after ten minutes, I imagine because of the huge number of foreign key indexes.  Each table also has multiple additional indexes that perhaps need to be re-built each time too -- I'm not an expert in MySQL unfortunately.

What is a sensible way of tackling this problem?  Ideally, this task needs to complete quickly with many, many more records than this.

FOOTNOTES:
1. The context of this question:  we store transit data from around the world (schedules, etc.)  The data is updated from various sources at different times, so I need to remove the old records related to a certain source and replace them with the newer data, while preserving records from other sources.

We're using MySQL 5.7 to get InnoDB spatial index support, although I hope this is not relevant - the problem persists in MySQL 5.6 too.

While you are using foreign keys in the way that they were designed for, in reality disks can put a limit to the speed of certain operations.

There are certain configuration options that I would advise to check for performance first (innodb_flush_log_at_trx_commit, innodb_log_file_size, etc.) but I am going to assume that there is no performance problems because misconfiguration, and that proper indexes are in place. Profiling your query can help make sure that that is correct.

I would advise you against large transactions modifying lots of rows. Your server, specially under high load can end up impacted quite a lot by such operations.  I would recommend using the JOIN syntax to extract the id's of all rows to be deleted (could be millions?) and delete them in batches. A tool like pt-archiver can help you automatize that, making sure that you are doing things in small transactions, minimizing impact on the server by large write operations.

Another option that may help you with maintenance is mysql PARTITIONING. When a table is paritioned, instead of deleting every row one by one, you can delete whole partitions, which are on different physical locations, resulting in faster delete operations.

I know these suggestions are a bit general, but that is what I can do for you with the information provided.


Is there an elegant way to send query results directly to a physical printer? I've got this weird request to have a report be scheduled and printed to a printer.

The report itself is pretty simple, and will fit on a single page. I can print it to a txt and that will be just fine (although I'm open to suggestions on a better product).

Here's the code. It works. But I don't like it. Specifically, Id like to do away with the nested @bcp and @SQL.

Question: Is there a more elegant way to do this?

declare @filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@sql nvarchar(max),
@coverpage_text nvarchar(max)

set @filepath = 'C:\users\jmay\documents\'
SET @filename = 'TestFile'
set @filetype = '.txt'

--output to txt
set @sql = N'declare @bcp varchar(4000)
set @bcp = ''bcp " select * from test_data " queryout '
+ @filepath +  @filename + @filetype + ' -t " - " -c -T -d DBA''
print @bcp

EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
 exec sp_executesql @sql

--print data
 set @sql = N'declare @bcp varchar(4000)
set @bcp = ''START /MIN NOTEPAD /P ' + @filepath +  @filename +  @filetype + '''
print @bcp

EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
exec sp_executesql @sql

Execute the query using POWERSHELL and OUT-PRINTER

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance .   | Out-Printer

  The Out-Printer cmdlet sends output to the default printer or to an
  alternate printer, if one is specified.


If the login credentials are in the ODBC connection, which security connection setting to use? We have a linked PostgreSQL server and the remote login and password are parsed using the ODBC settings on the Windows server.

Under the Security tab in Linked Server Properties within SQL, which connection setting should be used please?

Not be made
Be made without using a security context
Be made using the login's current security context
Be made using this security context

If the last one, why would you want to pass the login credentials twice?

I have a linked MySQL server set up through an ODBC connector, so these settings may help you get somewhere. I found the setting that worked 100% of the time for me was selecting:
Be made using the login's current security context
Then in the box above, click Add, then as your local login choose your default local SQL server user, or a specific SQL Server user, and un-check impersonate, then in the Remote User and Remote Password field enter your PostgreSQL credentials. I am not 100% sure of the actual logic behind this, but the way I see it is it's linking up your local SQL server user with your remote user.

If that doesn't work, then you could also try selecting Be made using this security context and entering your Remote password and Username there, but I found my previous suggestion to always yield the best results for me. Good luck!


Most performant design for each member to manage his/her own news list? Say a news website has news categories such as Politics, Business, Society, Education, Health, Tech, Celebrity, Lifestyle, ... etc. The number of categories may be more than 20. New categories may be added; existing categories may be deleted.

If a registered member has no desire to see the news of a certain categories, he/she should be able to block those news so that those news don't show up in the news list on the homepage. This will only affect himself/herself, of course. Other members have their own preferences.

Each member can visit the Manage News Categories page to block/unblock news categories. The page will contain a HTML <form> with <input type="checkbox">s of news categories.

What's the most performant database design for such a feature?

I'm currently not an expert of database design. My initial thought is creating an associative table called "member_category_map" and associate it with the "members" table and the "categories" table.

Not sure if I'm being naive, but I'm worrying that the "member_category_map" table might end up containing too much data. Assuming that there are 20 categories and each member only blocks 1 category. If there are 2,000,000 members, then the "member_category_map" would contain 2,000,000 * 19 = 38,000,000 data. I don't know if that's good for performance.

Advices are much appreciated. Thanks.

You need four tables:

User            Category           Article                  Pivot
+----+-----     +----+-------+     +----+--------+-----     +---------+--------+
| id | ...      | id | title |     | id | cat_id | ...      | user_id | cat_id |
+----+-----     +----+-------+     +----+--------+-----     +---------+--------+

Table Pivot is standing for M:N relation and contains the subscription pairs user:category. Each user can have 0..n categories subscribed and each category can be subscribed by 0..m users. To fetch articles accordingly to the user's subscriptions you need to use JOINs like that:

SELECT u.name  AS UserName
     , c.title AS Category
     , a.id    AS ArticleID
     , a.title AS ArticleTitle
     , a.date  AS ArticleDate
     , a.url   AS ArticleLink
     -- everything you need
  FROM user      AS u
  JOIN pivot     AS p  ON p.user_id = u.id     -- pivot to user first
  JOIN articles  AS a  ON a.cat_id = p.cat_id  -- articles and
  JOIN category  AS c  ON c.id = p.cat_id      -- categories to pivot then
 WHERE u.id = 12345
 ORDER BY a.date DESC
 LIMIT 10
;

For the best performance you need to index tables by the columns used by the query:
user should have the primary Key (id)
article should have the multicolumn key (id, cat_id, date)
pivot should have the multicolumn primary key (user_id, cat_id) with FKeys to the corresponding tables.


Query against a view vs. query against the underlying base table I was told to query against views in our ERP system because this would prevent any locks put on the actual tables thus blocking actual work being done. After learning a bit more about SQL Server, I'm not sure this is accurate but wanted to get clarification.

Knowing that a view is just a “saved query”, it appears I'm querying the base table via the view so it would seem this wouldn't accomplish anything as far as preventing blocking.

In other words if I do the following and leave these open, am I understanding that either one would block sales table writes?

begin trans
select * from dbo.view_sales

versus

begin trans
select * from dbo.sales

Thanks!

It depends on the construction of the view. SELECTing from a view with NOLOCK or READUNCOMMITTED table hints should avoid blocking the underlying tables (with the risks that entails).

Indexed views (SQL Server term - more generally called materialized views) actually are stored on disk separately from their underlying tables, and are updated as the underlying tables are updated. This not only consumes more disk space, but can lead to contentions and delays if the underlying tables are frequently modified. SELECTing from the view can still block updates to the tables underneath, since when those tables are updated, the index for the view is also updated.

And, as you thought, SELECTing from a normal view can temporarily block queries that are changing the underlying tables, just like SELECTing from the tbles directly can.


How do I organize tables for simple lottery game? mysql I am building a simple lottery web game that will let users pick a number that is in between that weeks given range. A number will be randomly select to determine the winner. I have a table for users (id, username, password, pin, funds) but as I go to setup the games table I get kinda stumped. How do I keep track of what numbers have been picked so far and who they belong to? game (id, date, winner...)? How do I handle the fact that the number range can change from week to week?

users
id
username
password
pin
funds

games
id
date
winner

number_range
date
range

selected_numbers
date
user_id
number

Is this heading in the right direction?

I'd create another table to store the number range each week (store the date and range) and then another table to store the user's selection (store the user id, date, and chosen number).


How can I model this relationship? My database design skills are a bit rusty so I was hoping to get some help from y'all =)

I have a 'user' table that contains all the typical fields (id,name,email,etc.). Users can have many 'invitations'. (many to one) Each 'invitation' has a single owner (user). However, a single 'invitation' can have many 'invitees'.

My confusion is this: If users can have many invitations and invitations can only have one owner (user) but that single invitation can contain many invitees (these are users) how should this be modeled? Do I need a new table?? ('invite')

I hope my question makes sense.

If an invitations is just an invitation, then it seems simplest to store all of the users for an invite in their own row in a separate table.

Users
 - UserID, PK
 - other details about the users

Invitations
 - InvitationID, PK
 - OwnerID or OwningUserID or just UserID, FK to Users
 - other details about the invitations

InvitationUsers
 - InvitationID, FK to Invitations
 - UserID FK to Users
   [PK on (InvitationID, UserID)]

A lot of people are tempted to store all of the users of an invitation in a comma-separated list or as XML. Resist this temptation; it leads to nothing but trouble.

If there is only a single type of invitation (like "be my friend on Facebook and we can hug and all that") then a simpler model might be:

Users
 - UserID, PK
 - other details about the users

Invites
 - SenderID, FK to Users(UserID)
 - RecipientID, FK to Users(UserID)
   [PK on (SenderID, RecipientID)]

If an owner can send an invitation to various things (a dating service, a boat show and a movie) then we'll need more details about what those relevant things are.


Slow lookup of part of primary key I am struggling with query performance.  I'm working with historical records of who has voted (or not) in an election.  I have data for nearly 40 million ballots over 22 election.  When I get additional data in the future, the oldest elections will drop off of the new data set, but I want to retain that information in my own database.  So I need a system that will let me merge new data into the existing data.

I have a table 'elections' with 22 rows:

CREATE TABLE `elections` (
  `election_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `election_type` enum('primary','general','special','board') NOT NULL,
  `whose` enum('state','other') NOT NULL,
  PRIMARY KEY (`election_id`),
  UNIQUE KEY `date` (`date`,`whose`),
  KEY `whose` (`whose`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

And a table 'ballots' which is initially empty:

CREATE TABLE `ballots` (
  `voter_id` int(10) unsigned NOT NULL,
  `election_id` int(10) unsigned NOT NULL,
  `status` enum('unreturned','invalid','valid') NOT NULL,
  PRIMARY KEY (`voter_id`,`election_id`),
  KEY `election_id` (`election_id`),
  KEY `status` (`status`),
  CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (`election_id`) REFERENCES `elections` (`election_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My raw data obviously does not contain an election_id.  I need to look up the election_id based on the date.  I load my raw data initially into a 'temp_ballots' table which has ~40M rows:

CREATE TABLE `temp_ballots` (
  `voter_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `status` enum('unreturned','invalid','valid') NOT NULL,
  KEY `date` (`date`),
  KEY `voter_id` (`voter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And now, the part I struggle with.  I want to take the data from temp_ballots, look up the correct election_id, and insert it into ballots.  (Then I will drop temp_ballots.)  The following operation is taking hours, and I don't know when it might finish, and I don't understand why this is slow.  I think I've indexed the appropriate columns.  What am I doing wrong?

INSERT INTO `ballots` (voter_id, election_id, status)
  SELECT voter_id, election_id, status
  FROM elections NATURAL JOIN temp_ballots
  WHERE whose = 'state'
  ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;

I am using MariaDB 10.0.36, so I do not have the ANALYZE statement.  But here is the output from EXPLAIN:

+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
| id   | select_type | table        | type | possible_keys | key   | key_len | ref                   | rows | Extra                 |
+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
|    1 | SIMPLE      | elections    | ref  | date,whose    | whose | 1       | const                 |   22 | Using index condition |
|    1 | SIMPLE      | temp_ballots | ref  | date          | date  | 3       | voters.elections.date | 1123 |                       |
+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+

Instead of depending on IODKU to update rows, do the messy work in the SELECT:

INSERT INTO `ballots` (voter_id, election_id, status)
    SELECT t.voter_id, e.election_id, MAX(t.status)
        FROM elections AS e
        JOIN temp_ballots AS t  USING(date)
        WHERE e.whose = 'state'
        GROUP BY t.voter_id, e.election_id

Or...

INSERT INTO `ballots` (voter_id, election_id, status)
    SELECT  voter_id,
            ( SELECT election_id FROM elections
                     WHERE date = t.date
                       AND whose = 'state' ) AS election_id,
            MAX(status)
        FROM temp_ballots AS t
        GROUP BY voter_id, election_id

Notes:

I think the first one might be better.
The first one designed to produce the rows in PK order.
I picked MAX to choose among the status values, since you did not have any way of picking among them.


How to deal with finding nearby locations when given a zip code or city? I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and populated it.

Now, what I'd like to do is given a zip code, I want to find all zip codes within 20 miles of the radius. Here's the structure of my geoname table:

CREATE TABLE public.geoname
(
  geoname_id integer NOT NULL,
  name text,
  ascii_name text,
  alternate_names text,
  latitude double precision,
  longitude double precision,
  fclass text,
  fcode text,
  country text,
  cc2 text,
  admin1 text,
  admin2 text,
  admin3 text,
  admin4 text,
  population bigint,
  elevation integer,
  gtopo30 integer,
  timezone text,
  mod_date date,
  the_geom geometry(Point,4326),
  CONSTRAINT geoname_id_pk PRIMARY KEY (geoname_id)
)

CREATE INDEX idx_geoname_admin1
  ON public.geoname
  USING btree
  (admin1 COLLATE pg_catalog."default");

CREATE INDEX idx_geoname_ascii_name
  ON public.geoname
  USING btree
  (ascii_name COLLATE pg_catalog."default");

CREATE INDEX idx_geoname_the_geom
  ON public.geoname
  USING gist
  (the_geom);

Here's the query I am using (it never completes, takes forever) and the EXPLAIN output:

SELECT ascii_name
FROM geoname
WHERE
ST_distance(
ST_PointFromText('POINT(-122.67621 45.52345)', 4326),
the_geom) < 16093

"Seq Scan on geoname  (cost=0.00..2729028.01 rows=3179247 width=14)"
"  Filter:   (st_distance('0101000020E61000009ACE4E0647AB5EC0AC8BDB6800C34640'::geometry, the_geom) < 16093::double precision)"

I was thinking about using ST_DWithin, but from what I can tell it requires two locations.

This is more-or-less what I am trying to accomplish.

What you want is ST_DWithin. That will use an index.

SELECT ascii_name
FROM geoname
WHERE
ST_DWithin(
  ST_SetSRID(ST_MakePoint(-122.67621 45.52345), 4326),
  the_geom,
  16093
);

You probably want to use geography rather than geometry.


What is the correct term to describe a "persistent transaction"? I'm kind of a the administrator of a zero-administration OODBMS, but mostly I do programming.

I remember reading years ago about some SQL database that had a concept of something like a permanent transaction. It might have been Sybase. Anyway, what that "thing" was, was like a transaction that you start, and that persist between connections, and withing which you can run "normal" transactions. It basically gave you a "view" of that database that could diverge from the main database, but running on the same server, and therefore sharing most of the resource instead of having to have it's "own" server. I hope you understand what I mean now.

Anyway, what is that kind of thing called, and is it still available in modern databases. In particular, is there any of the fancy distributed NoSQL databases that can do that?

The reason I am asking, is because I could use something like that to run the staging of the next version of my software on the main database, saving me the need to have a staging system that is as powerful as the main one, and the trouble/bandwidth of synchronizing the changes from the main to the test. I would just start the "permanent transaction", test the new software for a few days, possibly letting some users get a preview, and throw away the changes when I'm done. Then I make the real deployment, and start a new "permanent transaction" for the next version...

NB: I couldn't put meaningful tags because it's my first post and I haven't got the right to invent tags, and there was nothing like "persistent", or "permanent" or even "long running" ...

I know sort-of what you mean, we do it on the storage using NetApp FlexClones. A FlexClone "looks" just like a copy of a filesystem, but the underlying blocks on the disks are copy-on-write, so the only space it actually occupies is that of the changes you make.

However, the clone is not a stream of changes, like a redo log. The way NetApp works is that each block you see at the filesystem level is really a just pointer to a block on the "real" storage. Creating a FlexClone is merely a matter of copying the list of pointers, so it is a very lightweight operation. When you change a block on a FlexClone the pointer is moved to a fresh block, and the original is copied, then it proceeds as normal. This has two interesting implications:

Once you have changed a block once, you can go on changing it as many times as you like - it will occupy no more storage
Once the initial COW is done, speed of access to the FlexClone is identical to that of a real volume.

We would typically put the primary into hotbackup mode (for Oracle, but since it's on the storage this will work with any database that has an equivalent mode), FlexClone the storage, mount the clone with NFS onto the (much smaller) test server, then create a new controlfile, recover the database from archived redo logs as necessary and open it up with resetlogs. We allow a "snap reserve" of 10% by default (i.e. you can change up to 10% of the clone) but this is fully configurable - you can make it 100% and do a "split" which creates an independent copy too. This technology (with some clever shell scripting) has brought creating test environments down from 3+ days to a matter of minutes at my site, I am a huge fan.


change column type from string to numeric so I have a table with 5.2 million records. 3 columns in this table are for salaries. Those column are currently stored as string and I'd like to change the data type to numeric (I understood while researching it that numeric is better than float).

I'm trying to run this command:

ALTER TABLE lca_test ALTER COLUMN prevailing_wage TYPE numeric(10,0)
USING prevailing_wage::numeric;

But I'm getting the error

  ERROR:  invalid input syntax for type numeric: "40768,43"

I'm not sure how I can make it work. Any idea?

I'm ignorant of postgres SQL syntax, however this appears to be an issue with either the TYPE numeric(10,0) not containing decimal granularity (numeric(10,2)) would work, or the fact that the currency is in a european format that utilizes commas instead of decimals.


How do I model the following requirement in ER-Diagram? I'm making a story-composer application which allows users to write stories and other users can contribute to it. A story can have multiple storylines and multiple levels(think like a tree or thread structure). Following are the constraints :

When a user creates a story, they need to give it a title to the story and write a storyline.
Creating user can make the story public or private, if public, anyone can contribute to it, if private, the creator needs to invite specific users to contribute to it.
Bookmark Feature : There will be a checkbox besides each thread(storyline) and title.

If user checks the checkbox besides story title then the entire story is bookmarked or
User can bookmark specific storyline(s) by checking checkbox besides it.

I think you could do this with three tables and a view, User, Bookmark, Story_View and Story. User would contain all the users (log in information and an ID).

Story would contain a field in which you could put whatever the story is then when you want another user to modify that story you could just select that story and make a new record adding on whatever the next user adds on. Also it would have a number primary key as well as a foreign key to user ID so we can select the original owner of the story.

Story View would be what the users actually see. Users would only be able to see the data for stories they have made or are able to write on. There's definitely a better way of doing this using something outside of SQL.

Bookmarks table would simply store a foreign key to both user and story every time a user adds something to their bookmarks a record would be added to the bookmark table containing the user Id and story id so you could easily use a user ID to select all that users bookmarks.

I'm not going to draw a diagram because I think it's pretty easy best on this design.


Can we copy data from 1 text file to 2 postgresql tables? I have a text file with some data and I have two similar tables in my postgresql database. Is it possible to copy a part of the data present in the file, say first 10 lines, to the first table and the remaining in the second table?

The SQL COPY command itself has no provision for that. But you can specify PROGRAM as input. The manual:

  When PROGRAM is specified, the server executes the given command and
  reads from the standard output of the program, or writes to the
  standard input of the program. The command must be specified from the
  viewpoint of the server, and be executable by the PostgreSQL user.

Then, assuming you are on a Linux system, you can COPY the first 10 lines with:

COPY tbl1 FROM PROGRAM 'head -n10 /path/to/file/my.csv';

To COPY the rest, starting with line 11:

COPY tbl2 FROM PROGRAM 'sed -n 11,\$p /path/to/file/my.csv';

Or, to COPY without comments with leading # / only comments (asked in comment):

COPY tbl3 FROM PROGRAM 'awk ''!/^#/'' /path/to/file/my.csv';
COPY tbl4 FROM PROGRAM 'awk ''/#/ { sub(/^#/, ""); print }'' /path/to/file/my.csv';

The last command removes the leading # from comments (but not additional whitespace).

Consult the man page or your preferred online source about the shell commands head, sed and awk ...

As always, you need the necessary privileges. Consider the psql meta command \copy if you are not superuser nor member of the pg_execute_server_program role.

Related:

Using Postgres COPY TO for data export without overwriting file


Restore database stuck at 100% progress We are seeing restore taking more than 25 minutes for a simple 4 mb file .Restore progresses to 100%,but it wont update any status from there

When checked in error log ,we could see below error...

  Process 0:0:0 (0x12c8) Worker 0x000000CB5481A160 appears to be non-yielding on Scheduler 0. Thread creation time: 13223894899341. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 2%. System Idle 92%. Interval: 70083 ms.

We are able to match the worker address with session id of restore as shown in below screenshot

further we could see wait type of the spid for restore is 'PREEMPTIVE_OS_CREATEFILE'

Below are some more details

Version:

  Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)
      Jul 20 2019 21:42:29
      Copyright (c) Microsoft Corporation
      Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: )

windows details :

  windows serrver 2012 R2 standard

actual file sizes of this mdf,ndf ,ldf are 0.5,0.4 and 1 GB respectively.backup file is 4 MB

File speeds during restore are below 50 MS.

Any ideas on what may cause this behaviour ..?

The size of the backup file is only partly relevant. The restore process first have to create the database files (the "containers" as I like to think of them) and they need to have the same size as when the backup was produced.

So you have have a database with only small amount of information, but sitting in large database files.

Instant File Initialization can help to some extent, but only for database files. I.e., not for transaction log files. See for instant this for more info: https://www.brentozar.com/blitz/instant-file-initialization/

If you want to know the size of the file to be created, you can use RESTORE FILELISTONLY:

RESTORE FILELISTONLY
FROM DISK = 'R:\mybackup.bak'

You might need a FILE option if you have more than one backup in the file (just like in the RESTORE command you are running).


mongodb shard chunk migration 500GB takes 13 days - Is this slow or normal? I have mongodb shard cluster, shard key is hashed. It has 2 shard replica sets. Each replica set has 2 machines.

I did an experiment by adding another 2 shard replica sets, and it starts to rebalance.

However, after a while I find out that chunk migration is rather to be slow. It takes 1 hour to move 1.4GB of data.

It makes me worry, it means I have to wait for 13 days to complete 500GB of chunk migration!

I am new on this stuff and I don't have a god feel whether it is slow, fast, or normal. But still, those number does not convince me.

Additional notes on experiment:
- using m3 medium aws machines
- no other process running, only chunk migration
- default mongodb sharding installation with no further configuration
- shardkey is using hashed at object id (_id)
- max chunk size 64MB

Update: April 2018

This answer was correct at the time of the question, but things have moved on  since then. Since version 3.4 parallelism has been introduced, and the ticket I referenced originally has been closed. For more information I cover some of the details in this more recent answer. I will leave the rest of the answer as-is because it remains a good reference for general issues/constraints as well as valid for anyone on an older version.

Original Answer

I give a full explanation of what happens with a chunk migration in the M202 Advanced course if you are interested.  In general terms, let's just say that migrations are not very fast, even for empty chunks, because of the housekeeping being performed to make sure migrations work in an active system (these still happen even if nothing but balancing is happening).

Additionally, there is only one migration happening at a time on the entire cluster - there is no parallelism.  So, despite the fact that you have two "full" nodes and two "empty" nodes, at any given time there is at most one migration happening (between the shard with the most chunks and the shard with the least).  Hence, having added 2 shards gains you nothing in terms of balancing speed and just increases the number of chunks which have to be moved.

For the migrations themselves, the chunks are likely ~30MiB in size (depends on how you populated data, but generally this will be your average with the default max chunk size).  You can run db.collection.getShardDistribution() for some of that information, and see my answer here for ways to get even more information about your chunks.

Since there is no other activity going on, for a migration to happen the target shard (one of the newly added shards) will need to read ~30MiB of data from the source shards (one of the original 2) and update the config servers to reflect the new chunk location once it is done.  Moving 30MiB of data should not be much of a bottleneck for a normal system without load.

If it is slow, there are a number of possible reasons why that is the case, but the most common for a system that is not busy are:

Source Disk I/O - if the data is not in active memory when it is read, it must be paged in from disk
Network - if there is latency, rate limiting, packet loss etc. then the read may take quite a while
Target Disk I/O - the data and indexes have to be written to disk, lots of indexes can make this worse, but usually this is not a problem on a lightly loaded system
Problems with migrations causing aborts and failed migrations (issues with config servers, issues with deletes on primaries)
Replication lag - for migrations to replica sets, write concern w:2 or w:majority is used by default and requires up to date secondaries to satisfy it.

If the system was busy then memory contention, lock contention would usually be suspects here too.

To get more information about how long migrations are taking, if they are failing etc., take a look at the entries in your config.changelog:

// connect to mongos
use config
db.changelog.find()

As you have seen, and as I generally tell people when I do training/education, if you know you will need 4 shards, then it's usually better to start with 4 rather than ramp up.  If you do, then you need to be aware that adding a shard can take a long time, and initially is a net negative on resources rather than a gain (see part II of my sharding pitfalls series for a more detailed discussion of that).

Finally, to track/upvote/comment on the feature request to improve the parallelism of chunk migrations, check out SERVER-4355


Oracle CTE to SQL Server Using SSIS OLE DB Source I wrote a cte against an Oracle database and the query executes without issue when working in Oracle SQL Developer. I need to use ssis to transport the results of that query to a SQL Server database.

In ssis I'm using a data flow task, with an OLE DB Source and an OLE DB Destination component. In my source component I'm using the SQL Command data access mode.

When I paste my query into the command text window and click preview I see the error No column information was returned by the SQL command.

Other threads I've seen seem to indicate that this is possible, so I'm wondering if something in my configuration is incorrect. All fields have names, and I can execute other queries against this data source. Is there something I'm overlooking here?

For clarification purposes, my query basically looks like this:

WITH CTE(column1,column2) AS (
    SELECT field1 --window function
           ,field2
    FROM table1
)

SELECT column1
       ,column2
FROM CTE
WHERE condition1 is true

I had the same problem, which I managed to resolve by updating the Oracle driver.

My environment:

SSDT 11.1.20627.00 running on Visual Studio 2012 Update 4
Oracle OLEDB driver 11.2.0.1 - this version was not working with CTE

I updated the Oracle OLEDB driver to 12.2.0.1 and the error disappeared. I can now use CTE's in the packages:

The Oracle OLEDB driver is part of Oracle Data Access Components package, which can be downloaded from Oracle (32-bit version, 64-bit version).

Tip 1: To find your current Oracle OLEDB driver version, locate the OraOLEDBnn.dll (where nn is a number, eg 11, or 12) file in the Oracle directory. Open file properties and look for File version under Details tab.

Tip 2: If you have a mess in the Oracle directory (eg multiple directories; old versions present), use the NirSoft RegDllView tool to show a list registered DLL's. The separate 32 and 64 build versions of the tool will show 32 and 64 bit DLL's, respectively.


MySQL: How to fetch nth level child menus of a parent menu I have a menu which is parent of multiple menu items and each child menu has their own child menus. This pattern is continued till 5-6 vertical hierarchy level.
I don't want to write a query which contains nth level subquery as below:

select id from jos_menu where parent in(select id from jos_menu where parent in(select id from jos_menu  where parent=385 and published=1))

Also, I don't want to write a script for this. There should be something in MySQL to fetch such result.

Can someone guide me about how can I achieve my result in the best way?

A long, long time ago (Oct 24, 2011), in a galaxy far away, someone boldly asked

Find highest level of a hierarchical field: with vs without CTEs

In my answer to that post, I wrote three stored procedures to find specific relationships

GetParentIDByID
GetAncestry
GetFamilyTree

If you use the code in GetFamilyTree, just pick the nth member of the output.

I have referred others to that post

Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
Jul 11, 2013 : Recursive self joins
Dec 10, 2012 : MySQL: Tree-Hierarchical query

Give it a Try !!!


Adding hstore entries to an uninitialized (NULL) column I got bitten by this "feature" recently.

If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error.

Is this expected?

 create table test_hstore(id int, map hstore);
 insert into test_hstore(id,map) values(0, '');
 INSERT 0 1

 select * from test_hstore ;
  id | map
 ----+-----
   0 |

 update test_hstore set map = map || hstore('key1', 'value1') where id = 0;
 UPDATE 1

 select * from test_hstore;
  id |       map
 ----+------------------
   0 | "key1"=>"value1"

 update test_hstore set map = null where id = 0;
 UPDATE 1

 select * from test_hstore;
  id |  map
 ----+--------
   0 | (null)

 update test_hstore set map = map || hstore('key1', 'value1') where id = 0;
 UPDATE 1

 select * from test_hstore;
  id |  map
 ----+--------
   0 | (null)

If I cannot have a not null constraint on the column, can I safeguard myself by doing something like that(this doesn't actually work):

UPDATE test_hstore SET map = (IF map IS NULL
                                THEN  '' || hstore('key1', 'value1')
                                ELSE map || hstore('key1', 'value1'))
WHERE id = 0;

In SQL, NULL (operator) (value) is generally NULL.

This is not unique to hstore, and is the norm for everything.

The empty string '' is different to NULL. '' || 'somestring' is 'somestring', wheras NULL || 'somestring' is NULL.

The same is true for hstore. Just like NULL + 1 is NULL.

If this is a problem for you, you should probably store empty hstore values instead of NULL and assign a NOT NULL constraint on the column.


How assigning a value to Variable is working in SSRS Recently started learning Reporting Services using Microsoft SQL Server 2016 Reporting Services, Fifth Edition, Brian Larson.
I am creating this one report which uses UNION to create a dataset, FuelType, that will be used for assigning values to a dropdown list.

SELECT 'All' AS Fueltype, '_All' AS SortField
UNION
SELECT Description, Description FROM Propulsion ORDER BY SortField

Now there exists another dataset that will help generate the chart for me and there.

SELECT Propulsion.Description AS Fueltype, FuelPrice.PriceStartDate, FuelPrice.Price
FROM  FuelPrice
INNER JOIN Propulsion ON FuelPrice.PropulsionID = Propulsion.PropulsionID
WHERE        (YEAR(FuelPrice.PriceStartDate) = @year) AND  (Propulsion.Description = @PropulsionType)
OR  (YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All')
ORDER BY Fueltype, FuelPrice.PriceStartDate

I do not understand, how by using "@PropulsionType = 'All'" I am able to pass in all available values to the parameter.

For someone who has the book, this is under Chapter 6 :Fuel Price Chart, Version 2

SSRS isn't doing anything special.  It's actually the query that's making the distinction to either pull all fuel types or just the one specified, though I suspect the book has a typo because it looks to be missing an extra pair of parenthesis.  The real magic is supposed to be happening in the predicate, here:

WHERE  (YEAR(FuelPrice.PriceStartDate) = @year) AND (Propulsion.Description = @PropulsionType)
   OR  (YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All')

This really should be written in one of the two following ways:

WHERE  ((YEAR(FuelPrice.PriceStartDate) = @year) AND (Propulsion.Description = @PropulsionType))
   OR  ((YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All'))

Or, another way:

WHERE (YEAR(FuelPrice.PriceStartDate) = @year)
 AND  ((Propulsion.Description = @PropulsionType)
        OR  (@PropulsionType = 'All'))

Basically what this is doing is in the event  your report is passed the 'All' parameter, don't filter on Propulsion.Description.  If a parameter is passed that is not 'All', you want to filter on that specific value.  Taking SSRS out of it, you can see the working example via this dbfiddle.uk link.


PowerPivot doesn't install in SharePoint mode on an Active/Passive Cluster We a provisioning a new SQL Failover Cluster. We have successfully configured the SQL instance, and have moved on to configuration of the PowerPivot for SharePoint instance. We run the configuration wizard, and create a new failover cluster. However when the install completes, the resultant Analysis Services instance is in Multidimensional Mode (Compatibility level 1100) rather than in SharePoint integrated mode.

After the configuration had completed, the SSAS instance is in the wrong mode.

This works fine on a non-clustered instance in a VM lab, anyone else experienced a similar issue and hopefully has a fix?

We just got off the phone with Microsoft about this very same question this morning.  According to MS, there is an internal document from the product team stating that this is an unsupported scenario.  Which really stinks because we wanted to leverage our existing cluster licensing for SSAS rather than paying for an additional stand alone instance.  At any rate, hope this helps.  I have requested additional guidance if this is going to be tested and supported in a future CU or SP or if MS is going to remove the capability of allowing someone to install a clustered instance of SSAS in SharePoint Mode through the installer.


Does an OFFLINE Database use space? I took my SQL Server Database OFFLINE. Is it still occupying the same space as when it was ONLINE?

Should I keep it OFFLINE or is it better to BACKUP the database and DROP it?

An OFFLINE database is indeed still occupying space on disk. You can verify this by finding the location of the database files with a quick peek at sys.master_files and then navigating in windows explorer to the directory location.

select *
from sys.master_files
where database_id = db_id('my_offline_db');

If it's not bothering you, then there's no need to drop the database. You might run into some maintenance processes that don't play nice with an offline database, but it's a matter of preference whether you modify these processes to gracefully handle and offline database or you remove the offline db from your instance.

If you choose to drop the database to free up disk space, you will need to remember to manually delete the files afterwards. If you want to quickly browse some reasons why you might keep the offline database around, this ServerFault question is a good read.