Big Amount of  in Buffer Pool's Database Pages We were viewing sys.dm_os_buffer_descriptors using below query

select
 d.[name]                                       [Database_Name],
 (count(file_id) * 8) / 1024                    [Buffer_Pool_Size_MB],
 sum(cast(free_space_in_bytes as bigint)) / 1024 / 1024     [Free_Space_MB]
from sys.dm_os_buffer_descriptors b
    join sys.databases d on
        b.database_id = d.database_id
group by d.[name]
order by [Buffer_Pool_Size_MB]

And for one of my databases, it shows [Buffer_Pool_Size_MB] = 77325 Megabytes, and [Free_Space_MB] = 15849 Megabytes

So its about 20% of space in pages in buffer pool is empty. Seems like a waste of resources

Questions:

Is this a problem ?
How can number of free_space_in_bytes be mitigated ?
Any other things to investigate / look at in our situation ?

Is this a problem ?

Maybe, maybe not. If your database(s) only make up 80% of your server's max server memory, it's about what I'd expect to see.

If you have more data than RAM, then the free space is likely due to something else asking for memory. Something else could be memory grants for queries, CHECKDB, index rebuilds, etc.

It could also represent a memory fight between Windows and SQL Server. This usually happens when you don't have max server memory set to give Windows its 10% memory tithe so it can function properly.

After that something else took the memory, it either hasn't been re-used by the buffer pool, or various something elses continue to use memory that could be used by the buffer pool.

  How can number of free_space_in_bytes be mitigated ?

If your database(s) represent ~80% of max server memory, nothing.

If they don't, you can:

Add memory
Try to get something elses to ask for less memory

Adding memory usually helps, if you add enough to both cache your most-queried objects and provide memory grants to processes that need them. Figuring that number out is an exercise left to you. There's no generic calculation, but a good place to start is having RAM at 50% of server data. In simple terms: if you have 10GB of data, have 5GB of RAM.

Getting something elses to ask for less memory comes down to figuring out what else is asking for memory, and tuning those things appropriately.

  Any other things to investigate / look at in our situation ?

Yep! Look at the Stolen Pages counter:

SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE 'Stolen Server Memory%';

That can tell you how much memory SQL Server has taken from the buffer pool and given to something elses.

You can also look to see if SQL Server has had to force any query memory grants:

SELECT *
FROM sys.dm_exec_query_resource_semaphores AS deqrs
WHERE deqrs.forced_grant_count > 0;

Since you're on SQL Server 2017, you can use sp_BlitzCache:

EXEC sp_BlitzCache @SortOrder = 'memory grant';

Which will show you queries that ask for the largest memory grants. Of course, if your server is really low on memory, your plan cache data might stink, because turnover there will likely be really high. If that's the case, stop here and add memory before going any further.

You can try using Query Store, if that's turned on. Proc is available from the same link as above.

EXEC sp_BlitzQueryStore @DatabaseName = 'Your Important Database';

Then look for the "memory grant" pattern in the results. For the queries those bring back, look for Sorts and Hashes in the plans. Those usually represent index or query-tuning opportunities. If you need help tuning those, feel free to post them as new questions. Don't tack them on to this one.

And, of course, it could also be that you just never use that memory. I'd leave it alone, though, because you might grow into that 20% someday.


How do you handle adding nullable columns to each other in a query? Currently whenever I write a query that is adding columns that can contain nulls I resort to wrapping each field in isnull or coalesce, such as coalesce(score1,0) + coalesce(score2,0).
Is there a better way to handle this, or is this the standard practice?

That's pretty much what you have to do. Since NULL + anything else is NULL, you have to wrap each column in its own isnull(), coalesce() or CASE.


Ambiguity in Auto Create\Update Statistics If I set 'Auto Update Statistics' to 'False' and 'Auto Update Statistics Asynchronously' to 'True'

What the DB will do?

Also - If I set 'Auto Create Statistics' to 'True', It will be 'Async'?

No ambiguity there.

If you set 'Auto Update Statistics' to 'False' statistics will not be updated automatically.

If you set 'Auto Update Statistics' to 'True' they will be updated according synchronously or asynchronously according to to the 'Auto Update Statistics Asynchronously' settings.


Query error: there is no matching unique or primary key constraint in the referenced table I am getting an error of

*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

for my code

create table comm_Customer(CustomerID numeric primary key,
                           CustomerName varchar(20),
                           Address varchar(30),
                           PhoneNumber numeric,
                           foreign key(CustomerID)
                               references comm_ShoppingCart(CustomerID));

The error message is clear, you are trying to incorporate a FOREIGN KEY on table comm_Customer that says that the CustomerID (which is it's primary key) is also related to table's comm_ShoppingCart CustomerID column. This would mean that every customer might have a shopping cart (but only 1), since CustomerID has to be a primary key (or unique) on the comm_ShoppingCart to correctly identify a unique row of that table with the foreign key relationship.

I believe the relationship is the other way arround, comm_ShoppingCart CustomerID should reference table's comm_Customer CustomerID which IS a primary key and won't display this error message.

If this is the case, you can add the FOREIGN KEY with an ALTER TABLE if it's already created:

ALTER TABLE comm_ShoppingCart
    ADD CONSTRAINT FK_comm_ShoppingCart_comm_Customer
    FOREIGN KEY (CustomerID)
    REFERENCES comm_Customer (CustomerID)

If you really want the other way around, you have to create a UNIQUE constraint (or index) on CustomerID column on the comm_ShoppingCart table:

ALTER TABLE comm_ShoppingCart
    ADD CONSTRAINT UNI_comm_ShoppingCart_CustomerID
    UNIQUE (CustomerID)

... which will fail if you have more than 1 cart for a particular customer.


MS SQL Creating Tables and Adding Values by a Trigger Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By using triggers I would like to create a new table called Table_B and when I write the following code "INSERT INTO Table_B VALUES(3,5)" a new table will be created and it will have 2 columns ProductId and Quanity. At the same time the value for Stock for Table_A will be decreased by 5. Every time I insert something to Table_B I would like them to get added to Table_B and the stock in Table_A to be decreased by the quantity of that item inserted with the related ProductId. Thanks!

If you have a [Product] table and a [ProductTransaction] table, and your goal is to have [Product].[Quantity] always be the sum of the transaction table, then your trigger will need to take into account INSERT, UPDATE, and DELETE operations. You will also need to ensure that nobody ever directly updates the [Product].[Quantity] value, as it will result in inconsistencies.
One way to get around this without using a trigger would be to use a view. For example:
CREATE OR REPLACE VIEW vw_inventory AS
SELECT p.[ID], p.[Name], SUM(txn.[Quantity]) AS [Quantity]
  FROM [Product] p INNER JOIN [ProductTransaction] txn ON p.[ID] = txn.[ProductID]
 GROUP BY p.[ID], p.[Name]

Then you can query off the view:
SELECT * FROM [vw_inventory]
 WHERE [ID] = 4;

Otherwise, if you really want to use a trigger, then you'll need to set an AFTER trigger on the transaction table:
CREATE TRIGGER [trg_product_quantity]
    ON [ProductTransaction]
 AFTER INSERT, UPDATE, DELETE
    AS
 BEGIN
    SET NOCOUNT ON;
    UPDATE [Product] p
       SET [Quantity] = tmp.[Quantity]
      FROM (SELECT z.[ProductID], SUM(z.[Quantity]) as [Quantity]
              FROM [ProductTransaction] txn
             WHERE txn.[ProductID] IN (inserted.[ProductID], deleted.[ProductID])
             GROUP BY z.[ProductID]) tmp
     WHERE tmp.[ProductID] = p.[ID];
   END

Note: Be sure to double-check and modify this SQL code. I may have mixed in a little MySQL/PostgreSQL by mistake.

Hope this gives you something to think about


How to insert into table multiple geometry rows from function JSON param? My table:

CREATE TABLE Points
(
    id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass),
    info character varying(20) NOT NULL,
    my_point geometry(Point,4326),
    CONSTRAINT points_pkey PRIMARY KEY (id)
)

JSON text:

{
  "points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}
  ]
}

My function:

CREATE OR REPLACE FUNCTION add_multiple_geom ( multiple_geom json )
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point(lng,lat),4326), info;

  RETURN success;
END;

$BODY$;

You can unnest the array values using json_array_elements function and ->> operator:

create table t (data json);

insert into t
values ('{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json);

select points->>'lat' as lat,
       points->>'lng' as lng,
       points->>'info' as info
from
     (select json_array_elements(data->'points') as points
      from t) p;

lat      | lng       | info
:------- | :-------- | :---
45.50884 | -73.58781 | x1;
41.56355 | -81.57327 | x2;
41.52335 | -81.57423 | x3;

db<>fiddle here

Now change your function by:

CREATE OR REPLACE FUNCTION add_multiple_geom (multiple_geom json)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point((points->>'lat')::text::float8,
                                 (points->>'lng')::text::float8),
                                  4326),
                        (points->>'info')::character varying
      FROM (SELECT json_array_elements(multiple_geom ->'points') AS points) p;

    RETURN success;
END;

$BODY$;

And try to use it:

DO $$
DECLARE data JSON;
BEGIN
    data := '{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json;

    PERFORM add_multiple_geom(data);
END $$;

SELECT * FROM Points;

id | info | my_point
-: | :--- | :-------------------------------------------------
 1 | x1   | 0101000020E6100000C5724BAB21C14640C190D5AD9E6552C0
 2 | x2   | 0101000020E610000095D4096822C84440C971A774B06454C0
 3 | x3   | 0101000020E6100000492EFF21FDC2444074982F2FC06454C0

db<>fiddle here


On SQL Server 2012, you can use LAG and SUM with an ORDER BY clause to get what you're after without any self-joins. LAG is used to figure out if the [End Date] value from the previous row for a partition is exactly one day away from the [Start Date] value of the current row. The running total is used to tie the groups together and you get your desired results with a simple aggregate at the end.

SELECT
  Code
, Decri
, MIN([Active Date]) [Active Date]
, MAX([End Date]) [End Date]
FROM
(
    SELECT
      Code
    , Decri
    , [Active Date]
    , [End Date]
    , SUM(start_new_group) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) group_id
    FROM
    (
        SELECT
          Code
        , Decri
        , [Active Date]
        , [End Date]
        , CASE WHEN DATEADD(DAY, -1, [Active Date]) = LAG([End Date]) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) THEN 0 ELSE 1 END start_new_group
        FROM #x
    ) t
) t2
GROUP BY Code, Decri, group_id;

Results:

╔══════╦═══════╦═════════════╦════════════╗
║ Code ║ Decri ║ Active Date ║  End Date  ║
╠══════╬═══════╬═════════════╬════════════╣
║ 1000 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1001 ║ ABC   ║ 2013-07-01  ║ 2014-06-30 ║
║ 1001 ║ ABC   ║ 2015-12-20  ║ 2017-12-20 ║
║ 1003 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1003 ║ ABC   ║ 2014-07-01  ║ 2017-12-20 ║
╚══════╩═══════╩═════════════╩════════════╝


Postgresql - Map a Linux user to login I want to map Linux's root user to Postgres user, like if I enter psql in root user it should go the PostgreSQL cli without asking any authentication.

I tried to add an entry in pg_ident.conf and make the necessary changes in pg_hba.conf, but it didn't work.

psql: FATAL:  role "root" does not exist

Postgresql Version 9.6

pg_ident.conf
# MAPNAME       SYSTEM-USERNAME PG-USERNAME
root                 root            postgres

pg_hba.conf
# TYPE  DATABASE USER   ADDRESS         METHOD
local   all     all                     peer map=root
host    all     all     127.0.0.1/32    ident map=root

Any suggestions to achieve this?

I need to map multiple Linux users. The above one is for who all are using root user.

Finally, it comes to end.

The problem is I need to replace the below line.

local   all             postgres                                peer

 to

local   all             postgres                                peer  map=root

Thats it, no more changes.

Got this from:

This link


Pg_upgrade failed - what does "re-initdb" mean? My pg_upgrade command failed. During the upgrade it told me, that if things were failing after a curtain point (which they did) Then I should:

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

But what does this mean?

EDIT

$> /usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.2/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.2/main/ -D /var/lib/postgresql/9.3/main/ -j 6 -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.2/main/postgresql.conf"
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster
  template1
*failure*

Consult the last few lines of "pg_upgrade_dump_27132.log" for
the probable cause of the failure.
Failure, exiting

child worker exited abnormally: Success
Failure, exiting

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

Log:

2014-02-26 01:05:58 CET ERROR:  function pg_catalog._final_mode(anyarray) does not exist
2014-02-26 01:05:58 CET STATEMENT:  CREATE AGGREGATE "mode"("anyelement") (
        SFUNC = "array_append",
        STYPE = "anyarray",
        INITCOND = '{}',
        FINALFUNC = "pg_catalog"."_final_mode"
    );

The error means that the failed upgrade has left /var/lib/postgresql/9.3/main/ in an unusable state, broken and half upgraded. To attempt to upgrade again you must first remove it, then re-create it by running initdb like you did to create it the first time.

Look at pg_upgrade_dump_27132.log and pg_upgrade_server.log to see why the upgrade failed. Re-running it without changing anything is not likely to make any difference.


Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL Hope you're doing well

I have a table with the structure below :

Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date  datetime)

Some Example Data :

                  "Customer_Channel_Branch"
---------------------------------------------------------
Customer_Num     Channel_Num    Branch_Code    Issue_date
    x                 1             1000         1990
    x                 2             2000         1991
    x                 3             3000         2000

There is another table with this structure below:

 Channel_general_Code:(Channel_Num  int , Channel_gnrl_Code  int)

some example data for this table:

            "Channel_general_Code"
-------------------------------------------
     Channel_Num     Channel_gnrl_Code
        1                 1
        2                 1
        3                 1

As you can see in the second table , all three Channel_Num --> (1,2,3) are grouped as one single channel . So what I want in the result set is this :

                    "result"
----------------------------------------------
Customer_Num     Channel_Num     Branch_Code
    x               1               1000
    x               2               1000
    x               3               1000

for Channel_Nums that are considered as one , (have the same Channel_gnrl_Code in the second table), I need the Branch_Code of the Channel_Num with lowest Issue_date.
I thought that I could use window functions

(`row_number() over(partition by Channel_gnrl_Code order  by Issue_date )`)

to write this query but it gave me a false result.
I was wondering if you could help me with this.

Thanks in advance

Avoid Row_Number because first it process whole table then again you select row
where rn=1 for each group

DECLARE @Customer_Channel_Branch TABLE (
    Customer_Num VARCHAR(10)
    ,Channel_Num INT
    ,Branch_Code INT
    ,Issue_date INT
    )

INSERT INTO @Customer_Channel_Branch
VALUES (
    'x'
    ,1
    ,1000
    ,1990
    )
    ,(
    'x'
    ,2
    ,2000
    ,1991
    )
    ,(
    'x'
    ,3
    ,3000
    ,2000
    )

DECLARE @Channel_general_Code TABLE (
    Channel_Num INT
    ,Channel_gnrl_Code INT
    )

INSERT INTO @Channel_general_Code
VALUES (
    1
    ,1
    )
    ,(
    2
    ,1
    )
    ,(
    3
    ,1
    );

WITH CTE
AS (
    SELECT a.*
        ,b.Channel_gnrl_Code
    FROM @Customer_Channel_Branch A
    INNER JOIN @Channel_general_Code B ON a.Channel_Num = b.Channel_Num
    )
SELECT c.Customer_Num
    ,c.Channel_Num
    ,ca.Branch_Code
FROM CTE C
CROSS APPLY (
    SELECT TOP 1 Branch_Code
    FROM CTE C1
    WHERE c.Customer_Num = c1.Customer_Num
        AND c.Channel_gnrl_Code = c1.Channel_gnrl_Code
    ORDER BY c.Issue_date
    ) ca


A question regarding the performance of normalization I am sorry this is such a newb question, but it is one thing I am having a difficult time wrapping my mind around, and after an evening spent reading I finally just decided to ask for some clarification.

Say I have a table that contains information about servers. As new servers come online, this table is populated with information about the servers. Now, assume that the server name is unique, and server serial number is unique.

Scenario #1:

Server Table
server_name text,
server_serial text,
server_os integer (FK points to os table),
os_version integer (FK points to os_version table),
cpu_type integer (FK points to cpu_type table),

Now, let's assume there are 20 things that will not be unique about this server (e.g., server_os is not unique), thus in Scenario #1 we have 20 columns that contain FKs pointing to PKs of other tables.

Scenario #2:

No normalization. No other tables, just a server table with all the columns related to the server. No FK/PK relationships.

Normally I would go for Scenario #1. However, in scenario #1 I end up with a lot more queries. For scenario #2, I would simply do an insert for each server. In scenario #1, I would need to do a select to grab the PKs for all 20 columns, and then do my insert with the returned values (21 queries assuming all values exist in the 20 other columns). It gets even worse if there's a new server_os, cpu_type, etc.

Any help understanding why scenario #1 (normalized) is better in this situation would be greatly appreciated, or what exactly I am missing here. Thank you!

The relationship between normalization and performance is complicated.  Sometimes the pursuit of normalization and the pursuit of high performance lead in the same direction, sometimes in opposite directions.  You have to look at each case carefully.

More importantly, there is more than one measure of "goodness" in a database.  In addition to performance, there is ease of programming, ease of understanding, adaptability in the face of changing requirements, and so on.

Learning how to normalize is just the beginning.  In addition to learning the basic normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), you should learn what benefit you get out of conformance to each of these normal forms.  In general, conformance to 1NF buys you keyed access to all data.  conformance to 2NF through 5NF buys you freedom from harmful redundancy. Once you know the benefits, you can compare them against the costs.

A database that contains harmful redundancy has the possibility that the database will  contradict itself, by storing contradictory predicates in two different places.  You can exercise discipline in avoiding self contradiction by being very careful when you write the application.  But it's often better to design the database in such a way that it can't contradict itself, and normalization helps here.

Normalization sometimes helps a database to avoid "hot spots" at update time, where the DBMS is forced to serialize update transactions, and thereby creates bottlenecks.  But extracts and reports are often slower and more cumbersome than they are on a well designed database that is not normalized.

If you do decide not to normalize, don't just design at random, composing tables in any way that seems good at first sight.  Adopt some design discipline other than normalization to guide you to a good design.  Star schema is one such alternative design discipline.


Switching to NoSQL for native JSON use? I am rather comfortable writing concrete schemas with explicit primary keys, foreign keys, indexes, views and stored procedures. For queuing I use a NoSQL solution called: Redis.

Recently I heard a web-application development talk where the presenter said he used MongoDB and Node.JS (server-side) with AngularJS on the client-side.

This significantly minimises data conversion; as JSON is used natively across the board.

Are the performance gains from data consistency enough of a reason to develop in NoSQL, rather than relational?

PS: Should note that MongoDB uses BSON, so that could be interchanges for something like CouchDB (correct me if I'm wrong, but that stores as plain JSON)

Are the performance gains from data consistency enough of a reason to develop in NoSQL, rather than relational?

I think your question presupposes a certain way of looking at things.  Your fundamental tradeoff with NoSQL is a tradeoff between declarative ad hoc reporting and fast and loose inputs.  This tradeoff is unacceptable for many (maybe most) businesses, and so "rather than relational" I think is the wrong way to look at it.  Even folks like Martin Fowler advocate something different.

My recommendation is always to start relational and then add NoSQL adjuncts to the rdbms to handle velocity issues when those arise.


Database backup using RMAN We are planning to backup database using RMAN, but since I'm a newbie in DB administration, I don't have much knowledge in this.

I've a few queries though.
Currently we are using Oracle 11g database in noarchivelog mode. As far as I know, running a database in archivelog mode, backup is taken at short intervals, which I don't prefer. I need the backup to be taken only once in a day, that too without shutting down my database.
Is that possible?

Which is better option, database to be run in archive mode or noarchive mode?
Also, if I'm going for noarchive mode, do I need to delete the backups manually or,
does it happen automatically?

As far as I know, running a database in archivelog mode, backup is taken at short intervals, which I don't prefer.

No, archivelog mode and backup frequency has nothing to do with each other. Its you who defines the backup intervals.

  I need the backup to be taken only once in a day, that too without shutting down my database. Is that possible?

No, it's not possible to take hot backup in noarchivelog mode.

  You can only backup a database in NOARCHIVELOG mode when the database is closed and in a consistent state.

-Oracle Documentation.

  Which is better option, database to be run in archive mode or noarchive mode?

Oracle recommends to put your database in archivelog mode. There are chances that you may lose data in case of media or disk failure.

  Also, if I'm going for noarchive mode, do I need to delete the backups manually or, does it happen automatically?

You need to take care of it, no matter whether you are operating on archivelog mode or noarchivelog mode. However, you can define backup retention policy

Documentation:
Configuring the RMAN Environment
Introduction to Backup and Recovery


Avoid using complicated strings in Joins in SQL Server? I will create a couple of tables in SQL Server for a client. Of special interest for this question is the FactStoreSale table to be created and DimProduct in which the first table will include all store's sales and include for instance StoreKey, DateKey, TimeKey, ProductKey and sales data.

The DimProduct table will use complementary data in regards to Products, i.e. ProductName, ProductGroup, ProductCategory etc. This table will be a slowly changing dimension, and store Product history in terms of price etc. for each store. The table will include the columns FromDate and ToDate which describes when the data was entered, and when it was replaced (with default value year 2999 in ToDate until a new update of the Product is inserted) respectively.

I Believe this is very common. However, the ProductKeys from the underlying database are very complex strings, such as 140-xx4449CH-4.44,9.

My alternatives as I see it, is to use Checksum in SQL Server to convert the complex strings to integers, and store these in a separate column ProductKeyInt
and use these columns when doing JOINS. But checksum does not guarantee unique integer values, which would likely cause problems. When I tried Checksum on a Virtual PC and my own PC, doing Checksum  on the same values returned different results which is a concern as well. I Believe these two together rules out Checksum, unless it can be manipulated somehow to make it more unique?

Another  alternative is to use more complex functions to ensure that the string values are converted into integers, such as the one provided on a  question of mine here. A solution such as this however also has issues, the values 100-xx3 and 1003 will for instance get the same result. It is also complicated in the sense that if someone who isn't that savvy in SQL need to try to find any issues with the Query that uses the function, it may be very hard to understand what is happening.

My third option seem to be to use the DimProduct table and update this table first from the SQL Agent, and using an Indexing Key on this table and use this index as the  ProductKeyInt in the FactStoreSale table (where ProductKeyInt will be some kind of subquery in for instance FactStoreSale to fetch the value with the biggest ToDate in DimProduct corresponding to the ProductKey.

Does anyone have any input? Is there a simpler way? I don't want to have strings as JOIN keys because of for example increased CPU time

You don't need to use the ProductKey column as the key column in DimProduct. You can create a surrogate key that is a composite key made up of DimProductID that is an IDENTITY column and increments per each row of the SCD, and a ProductID column that is unique per each ProductKey. Both would be INT (most likely, unless you will never have very many unique products, in which case I might opt for SMALLINT for ProductID which gives you 65k values to use).

This approach allows you to have both the ability to reference any particular version of a product (using the IDENTITY column, or both as both will probably work with indexes better), and the ability to aggregate over all versions of a particular product using just the non-IDENTITY column.

In either case, you generally don't need to be JOINing on the ProductKey column / value.

When you do need to filter or JOIN on ProductKey, assuming that there is no functional difference between upper-case and lower-case letters, you should force everything to be upper-case and use a binary Collation (any Collation ending in _BIN2, not _BIN, though most likely Latin1_General_100_BIN2). Most people do not consider the Collation being used for string columns that contain alpha-numeric codes where there is no purpose, in any way, for having case (or Kana or width or accent insensitivity) and no purpose in evaluating complex linguistic rules, such as which character sequences equate to other characters in other languages. Non-binary Collations have A LOT of complex rules that they apply, and those take time. Using a binary Collation will be much faster as it just compares the bytes, which is why you need to force upper-case just in case someone enters in an alpha character in lower-case.

So for this, make sure to set the column's Collation to use Latin1_General_100_BIN2 when creating the table. If the table has already been created, use ALTER TABLE ... ALTER COLUMN to set the Collation so that it will be used by default.


Full Text Search not searching special character I am working on SQL Server 2008.
I have created full text index on a Table(Column) and I am trying to find.

SELECT *
FROM TblName
WHERE CONTAINS(ColName,'@#');

This is giving no results.

But I have a row in that column which contains value @# in field, so is this the default behavior of SQL Server Full Text Search, or am I doing something wrong and need to do something for this?

I can't verify this right now, sorry, but IIRC you have to quote special characters because SQL Server assumes @ and # are word delimiters

SELECT * FROM TblName WHERE CONTAINS(ColName,'"@#"')

Also see the SQL Server FTS Engineering team blog


SQLite constraint violation - How to insert data to this schema? First of all, sorry for the question but I was hours searching for a solution and the only things that I got are Java exceptions. I'm developing a little web crawler for movies, so I created this two tables.
CREATE TABLE IF NOT EXISTS movies(
    movie_id INTEGER PRIMARY KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    info VARCHAR(250)
);

CREATE TABLE IF NOT EXISTS genres(
    genre_id INTEGER PRIMARY KEY,
    title VARCHAR(50) NOT NULL UNIQUE,
);

Well, as you can see is a many to many relationship, so I made a junction table like this:
CREATE TABLE IF NOT EXISTS movies_genres(
    movie_id INTEGER NOT NULL,
    genre_id INTEGER NOT NULL,
    PRIMARY KEY(movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

And I'm trying to insert data with (arg are the method arguments):
INSERT INTO movies_genres(movie_id, genre_id)
SELECT m.movie_id, g.genre_id FROM
SELECT (movie_id FROM movies WHERE title = arg) AS m
CROSS JOIN SELECT (genre_id FROM genres WHERE title = arg) AS g;

But I get this SQLITE_CONSTRAINT]  Abort due to constraint violation (UNIQUE constraint failed. I don't know where is the error, maybe is in the schema but I can't see the error. Any idea on how fix it?.

First, do you have any data in movies_genres?
SELECT * FROM movies_genres;

If not, you can check if the primary key is missing in any of the sources tables
SELECT movie_id FROM movies GROUP BY movie_id HAVING COUNT(1) > 1;

and the same for genres. If above is ok, the error should not happen, but you can check with:
SELECT m.movie_id, g.genre_id
FROM ( -- I assume the parentheses was a typo and moved it
    SELECT movie_id
    FROM movies
    WHERE title = arg1
) AS m
CROSS JOIN ( -- see comment above
    SELECT genre_id
    FROM genres WHERE title = arg2
) AS g
GROUP BY m.movie_id, g.genre_id
HAVING COUNT(1) > 1;

You can btw simplify your query to:
SELECT m.movie_id, g.genre_id
FROM movies m
JOIN genres g
    ON m.title = arg1
   AND g.title = arg2


Simple recovery mode with mirroring - is that possible? (The usual 'I'm not a real DBA, but I need to handle this somehow' disclamer here)

We have a database with Full recovery model that doesn't really need a Full recovery model... only it does.

Some context first: the database in question contains some data used by the nightly job scheduler (e.g. server connection parameters, queue state etc.). This data doesn't really warrant for the point-in-time recovery or even, for that matter, for backups, because it's much easier to re-create the whole database from scratch. At the same time it's a production database with mirroring enabled, so it can stay online and available if we switch to DR server for any reason.

So we are in a Catch-22 situation: either we switch to Simple and lose the automatic failover setup (which is kind of necessary for us to have), or we keep Full and take regular log backups that we don't actually need, otherwise transaction log file will keep growing (with data that, again, we don't actually need).

The only solution I could come up with is scheduling an automatic job that discards all transaction log data, but I've been wondering if there is a saner way to handle this.

Mirroring uses the transaction log to redo everything at the mirror site.  In simple recovery model, the transaction log is effectively transitory and only enough information is kept in the log for undo/redo to keep the database consistent during crash recovery. So Database mirroring is not possible in Simple Recovery model as it is not supported.


Displaying the list of all tables in all database How can I display a list containing all the tables of all the database in a SQL Server 2008 R2 server?

You need a query for each database against sys.tables.

select 'master' as DatabaseName,
       T.name collate database_default as TableName
from master.sys.tables as T
union all
select 'tempdb' as DatabaseName,
       T.name collate database_default as TableName
from tempdb.sys.tables as T
union all
select 'model' as DatabaseName,
       T.name collate database_default as TableName
from model.sys.tables as T
union all
select 'msdb' as DatabaseName,
       T.name collate database_default as TableName
from msdb.sys.tables as T

You can use sys.databases to build and execute the query dynamically.

declare @SQL nvarchar(max)

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
exec (@SQL)


Query with big IN clause I'm having an performance issue with a query, the query is something like:
Select *
from requests
where start_date > {start_date}
and end_date < {end_date}
and employee in {employee_id_list}

The problem is that this employee list is huge, and come from another service and could contain up to 10.000 employee_id.
Are there any better way of doing this than using the employee_id_list as parameter for this query?

try use "with" clase
with employees as (
--- your employee list in select statement
--- for example:
select 1 emp_id
union
select 2
)

select *
from requests rq
join employees em on (em.emp_id = rq.employee)
where start_date > {start_date} and end_date < {end_date}


How to insert values into a new column which is added into an existing table? Please answer me if you know how to insert values into a new column added into an existing table. Your answer will be highly appreciated

You need to update the existing table.

mysql> create table tbl1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tbl1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> alter table tbl1 add column name varchar(20);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> update tbl1 set name='Jay' where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | Jay  |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> update tbl1 set name='Joe' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | Jay  |
|    2 | Joe  |
+------+------+
2 rows in set (0.00 sec)


How Can I Correctly Pass a Table to a Stored Procedure in Oracle in SQL Plus? Being new to Oracle, I'm almost certain I'm missing something simple here.  This is what I'm trying to run:

declare
type string_table IS TABLE OF VARCHAR2(512);
outuser nvarchar2(512);
outrole nvarchar2(512);
result number(38);
usernames string_table;
rolenames string_table;
begin
usernames(1) := 'Administrator';
rolenames(1) := 'Admins';
result := ASPNETDB.ORA_ASPNET_UIR_ADDUSERSTOROLES('/', usernames, 1, rolenames, 1, outuser, outrole);
END;

This is the error:

ERROR at line 11:
ORA-06550: line 11, column 11:
PLS-00306: wrong number or types of arguments in call to
'ORA_ASPNET_UIR_ADDUSERSTOROLES'

This is the output of DESC for the function:

FUNCTION ASPNETDB.ORA_ASPNET_UIR_ADDUSERSTOROLES RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 APPLICATIONNAME_               NVARCHAR2               IN
 USERNAMES_                     TABLE OF VARCHAR2(512)  IN
 USERNAMES_ARR_SIZE             NUMBER(38)              IN
 ROLENAMES_                     TABLE OF VARCHAR2(512)  IN
 ROLENAMES_ARR_SIZE             NUMBER(38)              IN
 USERNAME                       NVARCHAR2               OUT
 ROLENAME                       NVARCHAR2               OUT

I discovered that collections must have the same data type for an assignment to work. Having the same element type is not enough.

Armed with this information, I set out to figure out what type I needed to use:

SQL> select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = '
PACKAGE' AND OWNER='ASPNETDB' order by OWNER, OBJECT_NAME;

OBJECT_NAME                    OWNER
------------------------------ ------------------------------
ORA_ASPNET_PROF_PKG            ASPNETDB
ORA_ASPNET_ROLES_PKG           ASPNETDB

select line, text from user_source where TYPE = 'PACKAGE'; returned a bunch of garbage which included a line having: 1ASSOCARRAYVARCHAR2_T.  That prompted me to try changing my two variables to:

usernames ora_aspnet_roles_pkg.assocarrayvarchar2_t;
rolenames ora_aspnet_roles_pkg.assocarrayvarchar2_t;

That is the correct solution to my problem.  If someone can suggest a less roundabout way of finding what type to use inside a package, I'll gladly accept that answer.


Postgres 9.2 - Create View Different Column Names I want to create a view of a table with a column renamed.  I do not want to effect the source table, just the view.

I need to rename the column addr:housename to addr_housename.  We have a 3rd party piece of software that does not like ':' in field names.  We cannot just rename the field due to other software we use.

Can someone please provide an example on how to resolve my issue?

Thank you very much.

addr:housename is an illegal column name to begin with. You could have only created that if you used a quoted identifier (see the manual for details)

To create a view and rename the column, simply provide a column alias in the view's query:

create or replace view v_some_view
as
select ... other columns ...
       "addr:housename" as addr_housename
from some_table;


SQL Server Embedded Edition vs SQLite Can anybody shed some light on how these SQL products compare? No need for essays, only highlights such as "X supports this, while Y doesn't".

How is efficiency affected as the database grows larger

Inserts
Updates
Selects

What kind of transactions are supported
What support is there for multiprocessing

Access from concurrent processes
Access from concurrent threads

Data access is in-process or via server process
Key feature of either product that is worth mentioning

Also, any (positive or negative) experiences from people who have used both products (don't want opinions - just observations).

The reason I'm asking is that it's hard to penetrate the marketing fluff surrounding Microsoft's embedded SQL product.

Thank you.

Based on your question "What support is there for multiprocessing":

SQL Server CE and SQLLite are single-user DBs that work in your app and deployed as DLLs
SQL Server (Express and above) (and MySQL, Oracle, Postgres et) are server engines where you submit a request

Either you have proper multiprocessing or you don't: not much grey area in between. The rest of your questions don't matter because of this.

An as example, SQLLite locks tables locking so you don't have much concurrency even if it nominally supports multiple processes

Edit, about SQL Server CE from SO

"SQL Server CE in a multi user scenario - is this really, really stupid?"
"What is the number of maximum concurrent connections for Sql CE 3.5?"

It looks like SQL Server Express is recommended for multi process access


How to JOIN a table with a name that equals to a determined value in MySQL? The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here.

To map this to the database I’m using the Class Table Inheritance design pattern.

I’m cheating a bit and let the “super-entity” (i.e., the supertype) know, which type of “sub-entity” (i.e., the subtype) belongs to it. That means, the “super-entity” has a property (i.e., attribute or column) named type. The value is camel-cased (for an easier usage in the application). The table names are always underscored.

Here is a diagram depicting my database structure

And a diagram displaying class models for my application program objects

The problem is now the retrieving. When I want to get a Car with id=123, I don't know at first, which type it has and hence which car_* table I have to JOIN. Well, I could workaround it, either JOINing  every time all car_* tables or making two request (when the first one is something like a “pre-request” and is used to get the type: SELECT type FROM car;), but I’m sure, there must be a better solution.

How to SELECT/JOIN a TABLE with a variable name, that equals to a selected value in another TABLE?

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out.

Drawing in all the tables as follows would work:

SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2
     , ct.specialAttributeA
     , ca.specialAttributeB
     , cb.specialAttributeC
FROM   AbstactCar ac
LEFT OUTER JOIN
       CarTesla ct ON ct.carID = ac.CarID
LEFT OUTER JOIN
       CarAstonMamn ca ON ca.carID = ac.CarID
LEFT OUTER JOIN
       CarBMW cb ON cb.carID = ac.CarID

but obviously this is limited by needing to know all the types in advance and/or update the query every time a type is added/dropped/changed and possibly hitting some limit on the number of tables in any given SELECT. If a non-common attribute can appear in more than on of the sub-classes then change the definition for that output column to something like ca.specialAttributeD, cb.specialAttributeD AS specialAttributeD.

NOTE: I'm pretty sure the above is mysql compatible, but I'm generally an MS SQL Server fellow and haven't tested it so you may need to tweak the syntax slightly.


Is there a way to have PostgreSQL consider the Interpunct character · as a letter? I'm working on an application storing strings of the Gallo language. In this language the Interpunct character · is considered a letter, not a word divider.

So I want for instance the query

SELECT * FROM records WHERE sentence ~* '\m·r'

to return records where sentence contains word starting with ·r.

Is there a way to force PosgresSQL to consider this Interpunct character ·  as a letter?

Phew... took ages, but I finally figured it out! It's a bit of a hack, but it was the only way I could find to do it.

My train of thought went like this:

SELECT ASCII('·'); Result: 183

After a lot of experimentation (see exhaustive fiddle here), I came up with this:

WITH cte AS
(
  SELECT CHR(112) || CHR(104) || CHR(183) || CHR(97) AS my_string
)
SELECT my_string FROM cte;

Result:

my_string
ph·a

which, as you can see, contains an interpunct character. I was forced to use the ASCII number representations because dbfiddle.uk doesn't appear to treat · - interpunct - as a "proper" letter.

So, then I did this:

WITH cte AS
(
  SELECT CHR(112) || CHR(104) || CHR(183) || CHR(97) AS my_string
)
SELECT 'ph·a' ~* (SELECT my_string FROM cte) AS test_result;

Result:

test_result
t

Which is the correct answer.

I know that it's a lot of trouble to go to (believe me, I was beating my head off the table betimes! :-) ), but it was the only way that I could figure it out. It is possible that with the right character set (utf8mtb4) or similar, interpunct may be treated as a proper letter - but it's the best I could do for the moment! HTH & A+! p.s. +1 for the question BTW, v. interesting and I learnt a lot! :-)


Why does xp_logininfo truncates AD user account names? How to overcome this? As per this question, I use the following SP to see the members within an AD Group that's been added as a "login" in my database server.

EXEC master.dbo.xp_logininfo 'MyDomain\ADGROUPNAME' , 'members'

The issue is that, the resultant data gives me only up to 30 characters of the user's account name. This is because in the Active Directory, there are two sets of account names, one that says pre-windows-2000 , and another is for post-windows 2000. The pre one is truncated and that's the one I get.

For example, if the user account in the AD group is: MyDomain\ShortName, I get MyDomain\ShortName. But if the account name is:
MyDomain\HasOverThirtyCharacters, then I get
MyDomain\HasOverThirtyCharact

How do I overcome this limitation? What can I use instead of xp_logininfo??

the way I get this done is:

            SELECT @@SERVERNAME
            ----myserver

            DECLARE @NTLogin nvarchar(128) ;
            SET @NTLogin = 'mydomain\myuser'

            DECLARE @UserList TABLE (
                     [Account Name] nvarchar(128)collate Latin1_General_CI_AS,
                     [Type] nvarchar(128) collate Latin1_General_CI_AS,
                     [Privilege] nvarchar(128) collate Latin1_General_CI_AS,
                     [Mapped Login Name] nvarchar(128)collate Latin1_General_CI_AS,
                     [Permission Path] nvarchar(128) )

            INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'all' --insert group information

            IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
               INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'members' --insert member information

            SELECT  [Server Name] = @@SERVERNAME,
                    [Account Name],
                     [Type],
                     [Privilege],
                     [Mapped Login Name],
                     [Permission Path]

            FROM @UserList

you can wrap the code inside a stored procedure with @ntLogin as a parameter, that is how I did it here, including the error handling stuff, which is important too.

Another thing is to install the active directory module for windows powershell

also for testing from inside sql server you can run the following script:

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        ,USER
        ,SESSION_USER

this will show your credentials

and if you execute as the login you are testing you can see how it looks like.

EXECUTE AS LOGIN='yourdomain\the_long_login'

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

REVERT


You can find the min value for RecordTime where there are 6 consecutive rows by using a correlated query doing top(6) and then count the number of rows that is greater than or equal to 200.

Use that min value for RecordTime against the table to get the 6 consecutive rows that you want.

-- Setup table to test on
declare @T table
(
  RecordTime datetime primary key,
  ValueAsReal decimal(7, 4)
);

-- Add some data
insert into @T(RecordTime, ValueAsReal)
select '2015-11-30 12:59:00',  195.6907 union all
select '2015-11-30 13:00:00',  203.8667 union all
select '2015-11-30 13:01:00',  198.0907 union all
select '2015-11-30 13:02:00',  198.3573 union all
select '2015-11-30 13:03:00',  208.064  union all
select '2015-11-30 13:04:00',  211.3066 union all
select '2015-11-30 13:05:00',  219.36   union all
select '2015-11-30 13:06:00',  233.4453 union all
select '2015-11-30 13:07:00',  232.8106 union all
select '2015-11-30 13:08:00',  217.872  union all
select '2015-11-30 13:09:00',  214.464  union all
select '2015-11-30 13:10:00',  200.0587 union all
select '2015-11-30 13:11:00',  173.776  union all
select '2015-11-30 13:12:00',  181.1787;

-- Desired number of rows
declare @NumRows int;
set @NumRows = 6;

-- Min value to look for
declare @ValueAsReal decimal(7, 4);
set @ValueAsReal = 200;

-- Get the first @NumRows rows where RecordTime is greater than or equal to
-- the min value for RecordTime where there are 6
-- consecutive values for ValueAsReal greater than or equal to @ValueAsReal
select top(@NumRows)
  T.RecordTime,
  T.ValueAsReal
from @T as T
where T.RecordTime >= (
                      select top(1) T1.RecordTime
                      from @T as T1
                      where (
                            select count(*)
                            from (
                                 select top(@NumRows)
                                   T2.RecordTime,
                                   T2.ValueAsReal
                                 from @T as T2
                                 where T1.RecordTime <= T2.RecordTime
                                 order by T2.RecordTime
                                 ) as T3
                            where T3.ValueAsReal >= @ValueAsReal
                            ) = @NumRows and
                            T1.ValueAsReal >= @ValueAsReal
                      order by T1.RecordTime
                      )
order by T.RecordTime;

Query plan:


Postgres-9.1 having more Idle connections The connections were created 2 days before. Can I kill the process manually by using the below query?

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'mydb'
and procpid <> pg_backend_pid()
and current_query = '<IDLE>'
and query_start < now() - INTERVAL '2880' MINUTE;

The answer is: "Yes, sure you can." But I doubt you wanted to ask that.

Obviously, you need to make sure you only kill processes that need killing.
If possible, it's best done with the same role as the targeted processes to avoid collateral damage - only superusers can terminate processes of other roles.

Related:

How do I detach all other users from a postgres database?


MongoDB Zone Sharding without min & max key I have a database structure that is similar to the following structure.

User collection

Sub collection0, with userId
Sub collection1, with userId
Sub collection2, with userId

and so on

Example:

User: {_id, firstName, lastName, ...}
Blog: {_id, userId, ...}
Post: {_id, userId, ...}

The term sub collection is not appropriate I guess. In the above example, in one zone, I like to have documents related to only one user. For example, If there is an user X, then in a zone named X, there must be only one user document in users collection, which is X's document, the blogs & posts collections in zone X must only have X's blogs & posts. This use case is outlined here.

Based on the above documentation, I've setup zones. For shard tag range, min & max keys must be provided. In my use case(user id, blog/post ids), it is not possible to define range. Also for user collection, there is no range at all, only one user must in one zone.

I am able to think of hacks like, defining non overlap-able ranges in the application, which I tried & works too. But, is there a better way to do this?

Thank you!

Your collections need "key" (like 'zone') what is set when documents need to be "zoned". There will be only so many different key values than you have shards. When user wants to be zoned, you update all users documents (at all sharded collections) with that "key". Those collections are sharded that zone key. You choose one of those shards to be "default" shard so, all those documents what don't have zone key set, goes there. This default shard have minkey and you choose some other shard have that maxkey.

Not the best solution, but...


How to troubleshoot how resource governed query is being throttled in SQL 2012? I have a parallelized query on SQL 2012 SP1 (ent) that is running in a user defined resource governor pool much much slower (2 seconds versus 125 to 140 seconds under user defined pool = ~75 times slower) than the same query that runs in the default resource pool.

The query plan hash is identical between the two executions and statistics io returns same values for all operations. I've confirmed that execution plan does not have additional spill warnings (both have one), and that the memory grant for the queries are identical.

User defined pool workload group

<QueryPlan DegreeOfParallelism="4" MemoryGrant="2384096" CachedPlanSize="384" CompileTime="3608" CompileCPU="2712" CompileMemory="15672">
        <ThreadStat Branches="5" UsedThreads="20">
          <ThreadReservation NodeId="0" ReservedThreads="20" />
        </ThreadStat>
        <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="2360768" RequiredMemory="28960" DesiredMemory="2384096" RequestedMemory="2384096" GrantWaitTime="0" GrantedMemory="2384096" MaxUsedMemory="93872" />
        <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" />

Default pool

<QueryPlan DegreeOfParallelism="4" MemoryGrant="2384096" CachedPlanSize="384" CompileTime="3016" CompileCPU="2660" CompileMemory="15672">
            <ThreadStat Branches="5" UsedThreads="20">
              <ThreadReservation NodeId="0" ReservedThreads="20" />
            </ThreadStat>
            <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="2360768" RequiredMemory="28960" DesiredMemory="2384096" RequestedMemory="2384096" GrantWaitTime="0" GrantedMemory="2384096" MaxUsedMemory="88768" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" />

Total_worker_time is nearly identical between the queries. Only thing that seems different is the total_elapsed_time. Wait is cxpacket. At this point, I must think this is something that resource governor is doing to throttle the CPU and/or memory. I can't find any evidence of the throttling though. CPU throttled counter does not budge, neither does memory suboptimal plans/sec.

For the workload group I've played with increasing memory grant beyond default (25%), and have played with max dop settings. Nothing makes a difference to execution time unless I make the query execute serially. If I change maxdop of workload group to 1 (serial execution) the query runs in ~3 seconds once in cache (1 second slower than default group). All other levels of maxdop result in ~70 and 75 times slower).

Server info:
2 socket x 6 core server. Server MaxDop = 4. CPU is relatively at rest (~12% utilization) when I execute the tests. Memory on server is not under pressure (~6GB free on 24GB box).

Resource pool configuration

100% max memory

50% CPU (increasing to 100% didn't make any difference)

75% CPU CAP

Workload group

MaxDop 0 (and have tried 0,1,4,7)

Memory Grant (25% - and have tried SQL recommended maximum of 70%)

Any ideas on how to narrow down what is doing the throttling?
I do not see anything obvious from the Microsoft link for troubleshooting it

http://technet.microsoft.com/en-us/library/cc627395(v=sql.105).aspx

Full plan for Default Pool (quick) execution - http://pastebin.com/y0P9J61f

Full plan for User Pool (slow) execution - http://pastebin.com/20hnFSTW

The problem query is second statement in the plans (first one is inconsequential and doesn't run parallel anyways...)

An update - I still don't know how best to detect what type of throttling has gone on, but I did find the culprit in my case. It does seem that the CPU CAP to 75% was causing a much higher decrease in performance than a 25% drop should suggest. Raising the CPU Cap to 100% fixed the problem.

A connect article seems to validate this might be a problem with the CPU Cap implementation itself.

Its not the best answer to this question, and if someone still wants to answer the original "how to tell what throttling is going on..." question. I'd be amenable.

If you are struggling to understand why your queries are being throttled so severely, this might be a solution worth trying for it.

http://connect.microsoft.com/SQLServer/feedback/details/781335/resource-governor-feature-cap-cpu-percent-does-not-work-as-expected


error in Execute SQL Task with parameter I have an Execute SQL Task object in the Error handlers section of a SSIS package. What it does is inserting a record to a table using OLE DB connection. There is a value in the insert statement that should be sourced from a user variable.

When I try to pass the query I get the following error, I searched internet a lot and tried different things, but still get the error message:

"The query failed to parse. Parameter Information cannot be derived from SQL Statements. Set parameter information before preparing command."

What I have set to the SQLStatement is:

INSERT INTO ErrorLog(ErrorDateTime, ErrorDescription) VALUES(GETDATE(),?)

What I have in the Parameter Mapping section of the Task:

The SQLSourceType is "Direct input" and as there is no result to this query the ResultSet has been set to "None".

The ErrorDescription column in the target table is varchar(max).

Any idea how fix this issue?

Have you actually tried executing the package or are you just trying to parse the query?

Attempting to parse OLE DB queries with params will fail with the error you've described. Don't parse the query if using OLE DB.

I've created your task and tested. It executes fine, but if you click "Parse query" it returns the same error that you are getting.


"Percona-XtraDB-Cluster prohibits use of ALTER command on a table () that resides in non-transactional storage engine" I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via mysqldump --skip-locks -K and importing them via mysql <database> < <dumpfile> (after creating a blank database using CREATE DATABASE).
One database however is refused the import:

ERROR 1105 (HY000) at line 40: Percona-XtraDB-Cluster prohibits use of ALTER command on a table (example.example) that resides in non-transactional storage engine (except switching to transactional engine) with pxc_strict_mode = ENFORCING or MASTER

Most internet threads simply suggest temporarily disabling pxc_strict_mode. Given however that other databases import without any issues, I assume the issue should be resolvable by altering either the the flags appended to mysqldump, or modifying the statements inside the dumpfile. I attempted removing all ALTER statements in the dump, which changes the above error to ...prohibits the use of DML command....
Both, the source MariaDB instance, as well as the new XtraDB Cluster, are configured with the default InnoDB storage engine.
Would appreciate any input.

The Cluster can properly handle only InnoDB tables.  It sounds like some of your tables have ENGINE=MyISAM?
Plan A:  Edit the dump file to change to ENGINE=InnoDB for each table.
Plan B:  Remove the offending ALTERs; load the data (assuming the tables successfully convert to InnoDB); then manually run the lost ALTERs.
Plan C:  ALTER TABLE t ENGINE=InnoDB; for each table before dumping.
Each has potential hiccups.  Some are listed here:
http://mysql.rjweb.org/doc.php/myisam2innodb


What Permissions Are Needed for Selecting Data and Viewing All Schema? I'm attempting to create a role in Azure SQL such that the user can perform Select operations, view all schema (but not modify schema) but not perform any writes or execute stored procs. Granting a user Select permission gets me close. However, the user cannot see any stored procs or scalar functions.

So, I want to allow the user to see all schema definitions, not modify any schema objects and perform Selects. Is this possible?

Assign the VIEW ANY DEFINITION securable to the user.

That will allow them to see SP and view definitions but not allow them to change it.

https://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx


Many-to-many relationship when one entity is without attributes I'm interesting in mapping many-to-many relationship to relational model.

I understand that the basic approach is to create a third table (associative entity) that connects the entities in many-to-many relationship. But what happens if one of the entities doesn't have attributes? Do we really need a third table?

For example, let's consider entities user and cluster. One user can belong to multiple clusters and one cluster can contain multiple users. And we could create a third table such as "belongs-to". But do we really need that if cluster doesn't have any attributes? Could we, in that case, put the user_id key as foreign key in cluster table and consider that pair (user_id, cluster_id) as a composite key?

I see no gain of creating another table. If cluster had attributes it wouldn't make sense to repeat all the information for each cluster. But if it doesn't have attributes than the same amount of information is saved in both cases, creating a separate table or putting the user_id key in cluster table.

I'm confused, what is the right thing to do?

The 'Right' thing to do depends.

If there's any chance the cluster might end up having attributes in the future, then yes you should have a join table.

Otherwise, if you are trying hard to normalize your data, then you should have a join table, if you are more concerned about minor size/performance boosts then you could skip the extra table. Personally, since I don't have a time machine to see the future, I'd make the extra table anyway.


Return hours, minutes and seconds left if column wasn't updated I currently have this query:

UPDATE MyTable
SET    next_update = now() at time zone 'utc'
WHERE  next_update::timestamp < now() at time zone 'utc' - interval '1 day';

Which updates the column next_update if a day has passed since the last next_update was set. But in the case the value couldn't be updated (a day hasn't passed yet) How can I return the hours, minutes and seconds left for update?

If you want a single query, I suggest a data-modifying CTE like:

WITH cte(bound) AS (SELECT now() AT TIME ZONE 'UTC' - interval '1 day')
   , upd AS (
   UPDATE MyTable
   SET    next_update = now() AT TIME ZONE 'UTC'
   FROM   cte
   WHERE  next_update < cte.bound  -- why cast to timestamp?
   )
SELECT *, next_update - cte.bound AS time_to_update
FROM   MyTable, cte
WHERE  next_update >= cte.bound;

The point being that UPDATE and SELECT see the same snapshot and SELECT won't return the newly updated rows. (The fist CTE named cte is just for convenience, so we don't have to repeat the calculation of the bound.)

time_to_update is an interval of the form 23:53:27.141289 - hours:minutes:seconds:µs. Negative if there can be future timestamps - and possibly with leading days then: '-26 days -23:58:35.25222'. Else it cannot be greater than 24h in this query.

Assuming next_update is type timestamp, though the casts in your UPDATE are inconclusive (contradictory). now() AT TIME ZONE 'UTC' produces a timestamp, makes sense if next_update is of that type. But then why next_update::timestamp? (And why update a column named "next_update" to now()? Does not seem to make sense.)


sqlcmd returning NUL instead of NULL for tinyint column sqlcmd -E -d MyDatabase -Q "sp_helpsubscription" -s "," -o file.csv -W -u

Column 23 which is suppose to be a tinyint (subscriber_type) is output as NUL instead of NULL when the value is NULL.

I cannot figure out why it's doing this or a way to fix it.  When I run sp_helpsubcription in SSMS the column contains the expected full NULL text.

In order to replication this you need a replicated database with a publication in at least one subscription to it.  This particular issue is with a transactional publication.

It does this even if I output the results to the screen instead of to a file.

Edit: The reason I'm doing this is because I can't issue an INSERT #TEMP EXEC sp_helpsubcription because of a NESTED EXEC INSERT inside sp_helppublication.

sqlcmd only displays as many characters in a column as the width of the column data or the width of the column name, whichever is longer. You can expect the same behavior with the following data types:

bit
tinyint
decimal with precision < 2
varchar with length < 4

To work around the issue you can either cast the column to a longer data type or rename the column to a longer name.


Changing PostgreSQL port using command line My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).

I saw this solution before: https://stackoverflow.com/questions/187438/want-to-change-pgsql-port

I tried running the following command, but it failed:

postgres --p 5431

Could someone tell me how to change port in using command line?

Since you're on Windows and are probably starting PostgreSQL as a Windows service, you'll need to edit postgresql.conf (inside your data directory) to set the new port there, then restart the postgresql service using the Services control panel or (as an Administrator) the net service command.

You can use a programmatic text editing tool to change postgresql.conf if you need to automate this. On a Linux/unix box you'd use a tool like sed or awk, but on Windows I imagine you'll be using VBS, powershell, or an add-on tool like Perl.

You'll need to change the connection settings in tools like PgAdmin-III to use the new port.

If you were starting PostgreSQL manually using pg_ctl you could set the PGPORT environment variable or pass a config option on the command line. This won't work when you're launching it as a Windows service.


System-wide startup file for Postgres 10 on Ubuntu 18.04 When I log into postgres under my personal shell account, my .psqlrc file runs a series of meta commands to set up what is for me an optimum working environment. I'd like to set that same environment as the default when I or anyone else logs in as user postgres. I've been searching for a long time for a way to do this. The closest I have come is the following.

Find the system-wide configuration directory:

pg_config --sysconfdir

This returns /etc/postgresql-common. After cd'ing into this directory,  can find no psqlrc file. So I make one, adding a test line to turn off that annoying pager behavior:

\pset pager off

...and save. So far, so good. Permissions and ownership (root) all look good, same as the other files in this directory. Then I try logging into postgres as postgres:

sudo -u postgres psql postgres

and...nothing. No change.

Next, because we have more than one db cluster (currently running pg 10, but also have an inactive pre-upgrade pg 9.3 cluster that I'm not quite ready to get rid of) I get back into /etc/postgresql-common and rename psqlrc to psqlrc-10, following instructions here: http://manpages.ubuntu.com/manpages/bionic/man1/psql.1.html.

Finally, I log back into postgres as postgres and...no change. I've also tried logging out entirely and logging back in. Still no change.

This is the latest of many attempts. I'm sparing you the earlier versions. Is it possible that /etc/postgresql-common/psqlrc only affects interactive terminal behavior for non-postgres users? If so, how do I make changes for user postgres? If not, where on earth is that darn system-wide psqlrc file?

/etc/postgresql-common/psqlrc does work with Ubuntu packages.

Working example with Ubuntu 18.04 and PostgreSQL 11 from apt.postgresql.org.

$ echo "\echo This is the common psql rc file" | sudo tee /etc/postgresql-common/psqlrc
\echo This is the common psql rc file

$ ls -l /etc/postgresql-common/psqlrc
-rw-r--r-- 1 root root 32 mai   23 11:36 /etc/postgresql-common/psqlrc

$ sudo -u postgres psql postgres
This is the common psql rc file
psql (11.3 (Ubuntu 11.3-1.pgdg18.04+1))
Type "help" for help.

postgres=#

When such things don't produce the expected result, a quick way to troubleshoot is to launch the command with strace and examine failures/successes to open files within the captured stderr output.

Example:

$ sudo -u postgres strace psql postgres 2>/tmp/strace-output
This is the common psql rc file
psql (11.3 (Ubuntu 11.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# \q

$ grep psqlrc /tmp/strace-output
access("/etc/postgresql-common/psqlrc-11.3 (Ubuntu 11.3-1.pgdg18.04+1)", R_OK) = -1 ENOENT (No such file or directory)
access("/etc/postgresql-common/psqlrc-11", R_OK) = -1 ENOENT (No such file or directory)
access("/etc/postgresql-common/psqlrc", R_OK) = 0
openat(AT_FDCWD, "/etc/postgresql-common/psqlrc", O_RDONLY) = 4
access("/var/lib/postgresql/.psqlrc-11.3 (Ubuntu 11.3-1.pgdg18.04+1)", R_OK) = -1 ENOENT (No such file or directory)
access("/var/lib/postgresql/.psqlrc-11", R_OK) = -1 ENOENT (No such file or directory)
access("/var/lib/postgresql/.psqlrc", R_OK) = -1 ENOENT (No such file or directory)

In your case if you have a  /etc/postgresql-common/psqlrc you will see what exact errors occur when trying to access it.


Way To Speed Up Query My query returns roughly 590 rows and 8 columns.  The issue that I have is that from start to finish the query takes 2 minutes 30 seconds to complete.  The great group of people on here have taught me a lot on how to write more efficient queries, so here is another!

I am using a date variable  and not a datetime since my variable only contains a date - and I am also using storing my date(s) in the yyyymmdd format as suggested by Aaron Bertrand - Bad Habits To Kick.

Is there anything I can do to optimize this query and have the results return faster?

DECLARE @Startdate date = '20170101',
        @Enddate   date = '20170131';

WITH fc As
(
    Select
    Teacher
    ,Team
    ,fc
    FROM [Helper].[dbo].[fc]
)
,ia As
(
    Select
    Teacher
    ,tia
    FROM dbo.ia
    WHERE [hiredate] >= @Startdate
    AND [hiredate] <  DATEADD(DAY,1,@Enddate)
)
,inb As
(
    Select
    Teacher
    ,tinb
    FROM inb
),
ripcord As
(
    Select
    Teacher
    ,pit
    FROM [homebase].[dbo].[rip]
)
,YRTR As
(
    Select
    Teacher
    ,totamt
    FROM totamt
    WHERE CAST([begindate] As DATE) BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
                                     AND CAST(DateAdd(yy, -1, @enddate) As Date)
)
Select
DISTINCT rost.[Teacher] As Teacher
,[Team Name] = fc.team
,[TIA] = ROUND(SUM(ISNULL(ia.tia,0)),0)
,[SUM] = CAST(ROUND(SUM(ISNULL(inb.tinb,0))+SUM(ISNULL(rip.pit,0)),0) As INT)
,[YR] = ROUND(SUM(ISNULL(tr.totamt,0)),0)
,[fc] = ISNULL(fc,0)
,[1st Count] = COALESCE(b.[students],0)
,[2nd Count] = COALESCE(c.[potstudents],0)
FROM dbo.roster rost
LEFT JOIN fc fc
ON rost.Teacher = fc.Teacher
LEFT JOIN ia ia
ON ia.Teacher = rost.Teacher
LEFT JOIN inb inb
ON rost.Teacher = inb.Teacher
LEFT JOIN ripcord rip
ON rost.Teacher = rip.Teacher
LEFT JOIN YRTR tr
ON rost.Teacher = tr.Teacher
OUTER APPLY
( SELECT
        DISTINCT Teacher [Teacher]
        , COUNT(students) AS students
    FROM students
    WHERE Teacher = rost.Teacher
    AND regdate >= @Startdate
    AND regdate <  DATEADD(DAY,1,@Enddate)
    GROUP BY Teacher
) AS b
OUTER APPLY
(
    Select
    DISTINCT Teacher Teacher
    ,COUNT(potstudents) As potstudents
    FROM dbo.potstudents
    WHERE Teacher = rost.Teacher
    AND returndate >= @Startdate
    AND returndate <  DATEADD(DAY,1,@Enddate)
    GROUP BY Teacher
) AS c
GROUP BY rost.[Teacher],fc.TEAM, fc.fc,b.[students],c.[potstudents]
ORDER BY rost.[Teacher] ASC

EDIT (forgot the link, sorry)
At the requet of @sabin bio - here is a link to the Query Execution Plan

Also, the CTE's query views which have no indexes on them.

Start by understanding the plan that the query optimizer generated by your query. In your question you say that the query returns around 590 rows but the query plan included only returns 18 rows. Did you attach the correct plan? I will walk through it anyway. Reading from right to left:

Do a full table scan of roster and get back 18 rows. This result set is used as the outer part of all of the following nested loop joins. For each row in the outer result set:

Do a full scan on the ia table
Do a full scan on the inb table
Do a full scan on the ripcord table
Do a full scan on the YRTR table
Do a full scan on the students table
Do a full scan on the potstudents table

At some point the number of rows gets bumped up to 19. The GROUP BY is implemented as the SORT with a node id of 4. The DISTINCT is implemented as a Sort (Distinct Sort) with a node id of 0.

As the programmer you do not have direct control over the query plan but there's a lot that you can do to influence it. You said that the query execution time is too slow. Does the above plan sound efficient to you? Is it what you would do if you could choose the query optimizer's actions? You're doing around 140 table scans just to return 18 rows. For a very small number of rows this might be okay but it sounds like you have more data in production.

One way to optimize a query is to reduce the IO requirements of that query. Here you are doing lots of table scans so that should be straightforward to implement. Create indexes so that the query optimizer can get the relevant data more efficiently. Even if you are referencing views you can create indexes on the tables used by the view to improve performance. To give you an example, here is the predicate for the table access on fc:

[Test].[dbo].[roster].[Teacher] as [rost].[Teacher]=[Test].[dbo].[fc].[Teacher]

If you create an index on the Teacher column of fc that may result in a different, and possibly more efficient, table access method for that table. Optionally, you could add all of the columns used by the Teacher table as INCLUDE columns.

Looking at just your code, you should be aware of the impact of using local variables in your query. The query optimizer will not know the values of those local variables when creating a query plan. It will do default cardinality estimates based on hard-coded rules. For some queries, adding an OPTION (RECOMPILE) hint or replacing the local variables with hard-coded values can result in a much better query plan because the query optimizer knows the values of the variables before creating the plan.

Also, do not haphazardly add DISTINCT and GROUP BY to your queries. It looks like the query optimizer optimized away some of them, but most of the time if you have both GROUP BY and DISTINCT in the same query you're doing something wrong. It is not clear how much of a performance impact making that change would have for this query.


multi-master to single-slave replication at table level with PostgreSQL or MySQL Here is my scenario

Master1 hosting DB1
Master2 hosting DB2
...
MasterN hosting DBN

replicate to:

Slave1 hosting DB1,DB2... DBN

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

https://stackoverflow.com/questions/7544241/single-slave-multiple-master-mysql-replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.

the cleanest solution with PostgreSQL is just to do an initdb for each server you will be replicating from, run it on a different port, and have each server replicating to a server software instance on the slave.

If you need something more complex, you should start looking at Slony, Londiste, or Bucardo.  These are trigger-based replication systems which are far more flexible than the built-in replication systems that ship with the database.  You can build amazingly flexible solutions with these, but there is a lot of design overhead.

The simplest approach is that every table on every master gets writes redirected to a partition for the master and then these partitions get replicated into separate partitions on the slave.

That may be more complicated than ETL, which may be a better option.


How to select only members who have not made any deposits this month in mysql I am fetching data from two tables, members and fees .I would like to select only those members who have not made any deposit this month. This is what I have tried but the results are not what I am intending to get . any help will be appreciated . I am using mysql .

SELECT
    members.memberid,
    members.companyid,
    members.name,
    members.initials,
    members.surname,
    members.datejoined,
    members.usercode,
    members.address,
    members.cell,
    members.pic,
    members.idnumber, members.rank,
    members.address2,
    fees.feestype,fees.amountpaid,
    fees.companyid,fees.paiddate,
    fees.usercode
FROM
    members
    INNER JOIN fees
        ON members.memberid = fees.memberid
WHERE
        MONTH(fees.paiddate) < MONTH(CURDATE())
    AND YEAR(fees.paiddate)  < YEAR(CURDATE())

You need to do it in a diffent way: choose all members that are not IN the set of members who paid something this month:

SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    memberid NOT IN
    (
    /* members who paid something this month */
    SELECT
        memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

dbfiddle here

You actual query specified members who paid something in the years previous to the current one, and month previous to the current one.

As a second alternative, you can use NOT EXISTS:

SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    NOT EXISTS
    (
    /* any payment from this specific member this month */
    SELECT
        1
    FROM
        fees
    WHERE
        fees.memberid = members.memberid   AND
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

dbfiddle here

And yet another (not so clear, in my opinion) alternative, do a LEFT JOIN and set a WHERE condition that will filter out the rows actually retrieving something from the right side of the JOIN (i.e.: using only the rows WHERE all columns, and specifically memberid are NULL on the right side):

SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
    LEFT JOIN /* Imperative to be LEFT JOIN */
    (
    /* members who paid this month */
    SELECT
        fees.memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) AS s0
    ON s0.memberid = members.memberid
WHERE
    s0.memberid IS NULL /* The LEFT JOIN produced nothing on the right side */

Check Best practice between using LEFT JOIN or NOT EXISTS

And check all dbfiddle here

This is called, in relational algebra, an antijoin

UPDATE

Taking a step further from Kondybas's solution, and as pointed out in the comments to Kondibas answer by ypercube, this can be further simplified to:

SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees f1 ON fees.memberid = members.memberid */
    LEFT JOIN fees f2 ON
        f2.memberid = members.memberid AND
        f2.paiddate BETWEEN date_format(curdate() ,'%Y-%m-01') AND curdate()
WHERE
    f2.memberid IS NULL ;

Writing the condition like this (i.e.: not having a function applied to paiddate), it will use an index such as:

CREATE INDEX idx_member_date ON fees (memberid, paiddate);

... as can be seen on the execution plan:

id | select_type | table   | type | possible_keys   | key             | key_len | ref                                          | rows | Extra
-: | :---------- | :------ | :--- | :-------------- | :-------------- | :------ | :------------------------------------------- | ---: | :-----------------------
 1 | SIMPLE      | members | ALL  | null            | null            | null    | null                                         |    2 |
 1 | SIMPLE      | fees    | ref  | idx_member_date | idx_member_date | 5       | fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid |    1 | Using where; Using index

dbfiddle here


MySQL Data Files Missing? We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there were the .frm files. I searched the entire HDD for the .MYD files but the only ones are from the old database. It is on a CentOS 6.6 system running MySQL 5.5.

Where would those files be located? At first I was worried that they might just be held in cache, and that if the server lost power all would be lost, but not so sure now.

Wow, awesome commands Rolando, here are the results which looks like the majority is not InnoDB, but I would have assumed it still would need to store the data in the same place, even if it was in a different format. Our old DB is 1.7GB when I use the du -h in the /var/lib/mysql and the directory I care about is less than 1MB

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MEMORY         |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB         |             0.151 GB |             0.059 GB |             0.210 GB |
| MyISAM         |             1.381 GB |             0.227 GB |             1.608 GB |
| Total          |             1.532 GB |             0.286 GB |             1.817 GB |
+----------------+----------------------+----------------------+----------------------+

+-----------------------+------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE         |
+-----------------------+------------------------+
| INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend |
| DATADIR               | /var/lib/mysql/        |
| INNODB_FILE_PER_TABLE | OFF                    |
+-----------------------+------------------------+

OK here is your situation: your INNODB_FILE_PER_TABLE is OFF.

The data and index pages for the InnoDB tables are all inside the system tablespace (ibdata1). For those tables, you only see the .frm files. Therefore, everything is OK.

Here is what InnoDB looks like (image courtesy of Percona CTO Vadim Tkachenko)

These days, many use the default innodb_file_per_table = 1. This used to be 0 for the default back in the days of MySQL 5.1. If you have innodb_file_per_table = 0 in your my.cnf, then MySQL is leaving all InnoDB tables inside ibdata1.

For more info on this, see my posts

Why does InnoDB store all databases in one file?
MySQL dynamically optimize innodb tables without "file per table" setting

You can leave the InnoDB tables inside ibdata1. If you want them stored outside, see the CAVEAT of my post How can I determine which Innodb table is being written?


SORT_SEQUENCE is wrong in as400 library indexes. Can this cause performance issues? I have 4 AS400s running JDEdwards.

Lately a performance issue occured as described here: AS400 Performace issue

Although i am not an expert in AS400 , i tried to find out the cause. I think it came down to this:

I run the following SQL in all my systems:

select sort_sequence,index_schema,count(*) from qsys2.sysindexstat group by sort_sequence,index_schema

In 3 of my 4 systems all of them have sort_sequence="BY HEX VALUE"

In my 4 problematic system many indexes have sort_sequence="*LANGIDSHR"

I start to believe that this can cause performance problems as the query optimizer may leave these indexes unused and create temp indexes.

I wish somebody has an idea on where to go on from now and how.

Is there any specific setting that make all the indexes have default sort_sequence?

All my indexes are created massively so this could not happen by mistake.

Solution: Sort Sequence

In short the problm occured from incorrect client odbc setup, making the indexes not *HEX but langauage specific , rendering the index ussage useless fromn server point of view.


Cassandra nodetool repair scheduling and gc_grace_period I am trying to devise a scheme with minimal repair frequency to not overload the system, and have a problem to understand how can I ensure that the data is not getting resurrected.

I have a cassandra scheme with gc_grace_seconds specification of 48 hrs (172800 seconds)

I know it is recommended that the repair job [must be run before gc_grace expires to ensure deleted data is not resurrected][1]

I run the nodetool repair with the –pr key, so that it take less time to complete (it takes about 1.5 hours per node with the –pr option and around 6-7 hours per node without the –pr option).

Now, I don't see a scenario where I can be sure the data is not becoming visible again after the tombstone is invalidated.

In my understanding, if we create a tombstone at a date 1/1 00:00, it will be gone on 3/1 00:00.

If the repair job is scheduled to start every 2 days at the same times, the repair will start at 3/1 00:00 and until it will take care of the disappeared tombstone, we have a period that the data is resurrected.

If the repair job is scheduled to run, say, every day at the same times, then still we have the same problem - there will be a delay between the tombstone invalidation and nodetool repair action which will start at 00:00 every day.

If we make gc_grace_period more then 48 hours, say, 60 hours, then in my understanding the "resurrection period" will get even longer - the tombstone will get expired at 3/1 12:00 and "wait" till 4/1 00:00, even if we run the repair every day.

So how will the nodetool take care of the tombstones so that there is no period that the data is going back to life?

Found an answer to my own question on SO. There seems to be much more cassandra related questions then on DBA SE

https://stackoverflow.com/questions/32340429/what-does-cassandra-nodetool-repair-exactly-do

The data can become inconsistent whenever a write to a replica is not completed for whatever reason. This can happen if a node is down, if the node is up but the network connection is down, if a queue fills up and the write is dropped, disk failure, etc.

When inconsistent data is detected by comparing the merkle trees, the bad sections of data are repaired by streaming them from the nodes with the newer data. Streaming is a basic mechanism in Cassandra and is also used for bootstrapping empty nodes into the cluster.

The reason you need to run repair within gc grace seconds is so that tombstones will be sync'd to all nodes. If a node is missing a tombstone, then it won't drop that data during compaction. The nodes with the tombstone will drop the data during compaction, and then when they later run repair, the deleted data can be resurrected from the node that was missing the tombstone.

So it's sufficient that the tombstone will be delivered to other nodes before it expires on the original node, and that will complete when the repair runs on this node (as I think). I think because the nodetool takes snapshots before it runs, there is no chance that the tombstone will be gone in the middle of the repair and so will be lost.

It would be nice to test it though.


Table with relationship to multiple tables I want to persist contact details for persons. This contact details can belong to a client, or a next of kin member, or whatever other entity. I'll be using Doctrine2 in a Symfony project, that will handle the data integrity.

It is a good practice to have such contact table which stores contacts for any kind of 'parent' entity or should a I have different tables like client_contact & next of kin?

Your current diagram is great as long as everyone only has one address, and a max of two phone numbers (apart from a fax).

One simple way this schema falls apart is if you have a client with a street address for their location, but a PO Box for their mailing address.  This is a fairly common setup for rural communities in the United States.  When I lived in Jackson, Wyoming I had a street address which had a different zip code from my PO Box. In addition my work address was in another city. The core point is people can easily have more than one address, which conflicts with your schema.

Personally I would break apart the address from the phone number. This gives you more freedom over your data.  Entities can have addresses without phone numbers and phone numbers without addresses without risking a foreign key to a row full of nulls.  Furthermore I would add a foreign key to the address and phone number tables that is just a link to a description.  That way you don't need to remember "PhoneNumber2 is always a cell phone, unless this is a business in which case it is the point of contact's direct line" or other such nonsense. The foreign key to the description table will make it clear that Client X has two home landlines and no cell phones.  You gain flexibility and clarity.

Overall, I still would keep one address and one phone number table that the various entities would link to via foreign keys.  There is no need to duplicate schema.  Keep in mind it might be advantageous to have the foreign key to the address/phone table on the entity for some entities and other entities might use a join table instead.  That can be a per entity judgment call which can be adjusted over time with relatively little pain.

Also you might want to associate the "next_of_kin" table with someone (your clients?), because it seems kind of weird for them not to have kin...  :)


MYSQL, how to build this Trigger I have two tables in mysql

Table: objects

1: ID  (Auto Increment)         INT        eg: 1
2: Object (like "Flower)        TEXT       eg: Flower
3: Rank (like 90, calculated)   FLOAT      eg: 90

Table: Rankings

1: ID  (Auto Incement)          INT        eg: 1,2,3,4,5,
2: Object (like "Flower")       TEXT       eg; Flower,Flower,Flower,Flower,Flower
3: Person (like "Peter")        TEXT       eg: Peter,Hans,Markus,Emil,Egon
4: Ranking ( +/- Points)        FLOAT      eg: 100, -10 , -10 , -10 , 20

So, The Rank from Table1, is a sumary of all Rankings in Table2 where Object = Object.Table1. In this case it would be 90 for the Flower.

How can I build a trigger (on insert) for this? Every time someone posts a ranking the Rank.Table1 gets updated via a trigger.

Sorry for this question, but I'm very new to this.

Though this question is 2 years old, seems like it deserves an answer.

Make sure to look at
MySQL documentation 21.3.1 Trigger Syntax and Examples
in order to understand the importance and context of the delimiter statement.

Create trigger tbi_new_ranking before insert on rankings
for each row

BEGIN
 DECLARE v_ranking INTEGER;
 DECLARE v_aggregate_ranking INTEGER;
 DECLARE v_object VARCHAR(<how many characters>);

 set v_object = NEW.object;
 /*
   Define the code you want to aggregate the ranking numbers.
   The inserting data is addressed by prefixing the triggering table's columns with
   NEW. This can be a little confusing so it is wise to set up variables
   to represent the inserted data rather than mussing about with them directly
   E.G.
     SELECT count(*) INTO v_count from Rankings where Object = NEW.Object;
     SELECT sum(Ranking) INTO v_sum from Rankings where Object = NEW.Object;
     set v_aggregate_ranking = v_sum/v_count;
 */

 update objects set rank = v_aggregate_ranking where object = v_object;
END


Audit all does not capture truncate I want to capture truncate and drop operations on my table.

So i executed the following:

SQL> select name, value from v$parameter where name like '%aud%';

NAME           VALUE
----           -----
audit_trail    DB

SQL> create table audit_test (id number);

Table created.

SQL> audit all on audit_test by access;

Audit succeeded.

SQL> create table audit_test (id number);

Table created.

SQL> audit all on audit_test by access;

Audit succeeded.

SQL> truncate table audit_test;

Table truncated.

And the truncate does not exists in the dba_audit_Trail...

Thanks for help.

2 (+1) possible reasons:

0 - AUDIT ALL does not audit TRUNCATE operations. Use AUDIT TABLE BY ACCESS;

1 - You perform the above as SYS user. Actions performed by SYS are
    audited into files, under the location of audit_file_dest

2 - AUDIT (Traditional Auditing)

  Track the issuance of SQL statements in subsequent user sessions. You
  can track the issuance of a specific SQL statement or of all SQL
  statements authorized by a particular system privilege. Auditing
  operations on SQL statements apply only to subsequent sessions, not to
  current sessions.

Try it in a new session.


How to return ids with specific values in another column I have a table USER_ROLE with the format like this.

+--------+---------+
|USER_ID | ROLE    |
+--------+---------+
|1       | ADD     |
+--------+---------+
|1       | DELETE  |
+--------+---------+
|1       | VIEW    |
+--------+---------+
|1       | TRANSFER|
+--------+---------+
|2       | ADD     |
+--------+---------+
|2       | DELETE  |
+--------+---------+
|2       | VIEW    |
+--------+---------+
|2       | TRANSFER|
+--------+---------+
|3       | ADD     |
+--------+---------+
|3       | DELETE  |
+--------+---------+
|4       | VIEW    |
+--------+---------+
|4       | TRANSFER|
+--------+---------+

I want to find the USER_IDs that have all the below ROLEs.

ADD
DELETE
VIEW

So it should return 1 and 2 from above example.

I managed to get the results with below queries.

SELECT DISTINCT USER_ID
FROM USER_ROLE r1
WHERE EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'ADD' AND r1.USER_ID = r2.USER_ID)
AND EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'DELETE' AND r1.USER_ID = r2.USER_ID)
AND EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'VIEW' AND r1.USER_ID = r2.USER_ID)

and

SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'ADD'
INTERSECT SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'DELETE'
INTERSECT SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'VIEW'

Can you suggest any simple alternatives to these.

SELECT user_id
FROM
user_role
WHERE role IN('ADD','DELETE','VIEW')
GROUP BY user_id
HAVING count(*)=3

Here is the link to SqlFiddle


MySQL Front - Workbench We have MySQL Front installed in our server, I can connect to it with no problems, there is a db with tables, everything fine.

Now I installed MySQL workbench (client) in the same server, but can't connect, I receive a "Malformed packet" error when I test connection. The port is 3306, hostname is localhost (as I'm logged in the server), user and password are the same.

Why shouldn't I be able to connect to the same server with workbench? Actually I need to connect to it using Entity Framework, but I can't due to this error.

I don't know what info would be useful (feel free to ask anything), I can only guarantee the windows service is running, the firewall is not blocking (I even tried disabling it).

If I try to connect with workbench using an "invalid" username (e.g "foobar"), the connection is sucessful (which is strange imo), but then I can only see the "test" database, and I can't see the actual database I want to (as in MySQL Front).

After reseting root password, I could connect with workbench. I still can't connect with old users (which were already created), but connecting with root and creating new users with workbench allows me to connect with these new users.

It looks it has something to do with the password, or the way MySQL Server stores password for users. I don't know the actual reason for the error, but this solved my problem.

Thanks for the suggestions!