texts: Modelling document sharing permissions with NoSQL

texts:
I'd like to use NoSQL databases for the first time and was going to use MongoDB.  I have a project where an employee with @employeed_id can go to a web app and get a list of documents that he can read.  If this were a relational database, I might begin my SQL statement like this to fetch all the documents he has read access to (I'll explain in plain english what I'm trying to achieve next):

SELECT * FROM t_document
WHERE EXISTS (
    SELECT 1 FROM t_user
    WHERE t_user.user_id = t_document.author_id
    AND t_user.user_id = @employee_id
) OR EXISTS (
    SELECT 1 FROM t_user
    WHERE EXISTS (
        SELECT 1 FROM t_document_share
        WHERE t_document_share.user_id = t_user.user_id
        AND t_document_share.read_access = 1
    )
    AND t_document.company_id = t_user.company_id
    AND t_user.user_id = @employee_id
) OR EXISTS (
    SELECT 1 FROM t_user
    WHERE EXISTS (
        SELECT 1 FROM t_document_group_share
        WHERE t_document_group_share.read_access = 1
        AND EXISTS (
            SELECT 1 FROM t_user_group
            WHERE EXISTS (
                SELECT 1 FROM t_user
                WHERE t_user.user_id = t_user_group.user_id
                AND t_user.user_id = @employee_id
                ...
                ...
            )
        )

    )
)

Essentially, what I'm saying is get me all documents where at least one of these conditions apply:

I am the author of the document
the document has been shared with me, I have read access, and the document is owned by a company that I am employed at
the document has been shared with a group that I belong to.  The group has read access to the document.  Both I, the group, and the document belongs to the same company

After some quick online research, I read opinions that my use case is not meant for NoSQL.  All these sub queries (and potentially joins) to investigate relationships between different entities is cumbersome to do in NoSQL.  So my question is, can someone confirm if using NoSQL to solve my problem is like trying to make a fish climb a tree?  If so, then I will look for other projects to practice NoSQL on


texts: Logical reads different when accessing the same LOB data

texts:
Here are three simple tests that read the same data, yet report very different logical reads:

Setup

The following script creates a test table with 100 identical rows, each containing an xml column with enough data to ensure it is stored off row. In my test database, the length of the xml generated is 20,204 bytes for each row.

-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
    DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
    ID integer IDENTITY PRIMARY KEY,
    X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;

SET @X =
(
    SELECT TOP (100) *
    FROM  sys.columns AS C
    FOR XML
        PATH ('row'),
        ROOT ('root'),
        TYPE
);

INSERT dbo.XMLTest
    (X)
SELECT TOP (100)
    @X
FROM  sys.columns AS C;

-- Flush dirty buffers
CHECKPOINT;

Tests

The following three tests read the xml column with:

A plain SELECT statement
Assigning the xml to a variable
Using SELECT INTO to create a temporary table

-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DECLARE @X xml;

SELECT
    @X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
    DROP TABLE #T;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT
    XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;

Results

The output is:

=== Plain SELECT ====
Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0,
    lob logical reads 795, lob physical reads 37, lob read-ahead reads 796.

=== Assign to a variable ====
Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0,
    lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

=== SELECT INTO ====
Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0,
    lob logical reads 300, lob physical reads 37, lob read-ahead reads 400.

Questions

Why are the LOB reads so different?
Surely the exact same data was read in each test?


texts:
I have column with strings of dates with format: "dd.mm.yyyy".
I need to convert the date format contained in the strings to ISO8601 format: "YYYY-MM-DD" for the whole column.


texts: Store time taken to run query in a table

texts:
I am trying to gauge performance across about 300 views with and without some changes. I can write a script easily enough to run every view, but ideally, I wanted to get the time it took for each view to run individually.

I know that you can use SET STATISTICS TIME ON, to get the time, but this would be per-query and would not be easily searchable. Ideally, I wasn't sure if the STATISTICS TIME ON has an output parameter or something, or additionally - if there is a known way to manually time a query's execution so that I can insert it somewhere?


texts:
I've just finished setting up the 3 nodes mariadb (galera) cluster (let's call the nodes node1, node2 and node3). In order to have the cluster up to date with currently working mariadb database (let's call it dev_db) we created a replication of this dev_db to node1 so dev_db is a master and node1 is a slave. Replication is working properly so node1 is up to date with dev_db. I thought that replicated data to node1 cluster will be able  also transfered to other nodes so that whole cluster will be 'consistent'.
Unfortunately what is happening is that replicated data from dev_db to node1 is not propagated to the rest of the nodes.

When I add some sample data to node1 I can see that this data is propagated to other nodes (node2 and node3) so cluster is fine.

The question is how to configure this setup so that dev_db will be feeding the cluster with new data so that whole cluster will be up to date.

Many thanks.


texts: How to aggregate transactional records by month year but based on subscription period not calendar months?

texts:
I have a table containing transactional events for customers:

table: events
| id   | cust_id  | date                       | amount |
|------|----------|----------------------------|--------|
| 78   | 211      | 2010-06-18 20:10:00.000000 | 20     |
| 84   | 211      | 2010-07-18 05:53:00.000000 | 35     |
| 91   | 211      | 2010-07-27 05:45:00.000000 | 25     |
| 2136 | 211      | 2011-02-14 20:07:00.000000 | 5000   |
| 2947 | 211      | 2011-02-21 20:29:25.000000 | 22     |
| 2945 | 211      | 2011-03-14 20:48:26.000000 | 22     |

I want to produce a year and month aggregation of the sum of the amounts, but where the month periods correspond to the customer's subscription period. This period has a specific start date, for example the 20th of the month.

The query can take the inputs:

customer id - the customer to generate the report for
start day of period - the day of the month on which the "month" should start

Here is a query that naively groups by month without the custom period:

SELECT
  extract(YEAR FROM date)  AS year,
  extract(MONTH FROM date) AS month,
  sum(amount)              AS amount
FROM events
WHERE cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

Example output of this naive query:

| year | month | amount |
|------|-------|--------|
| 2010 | 6     | 20     |
| 2010 | 7     | 60     |
| 2011 | 2     | 5022   |
| 2011 | 3     | 22     |

But, given that start day for customer 211 is the 20th of the month, I want the actual output to be:

| year | month | amount |
|------|-------|--------|
| 2010 | 5     | 20     |  from May 20 - June 19
| 2010 | 6     | 35     |  from June 20 - July 19
| 2010 | 7     | 25     |  from July 20 - Aug 19
| 2011 | 1     | 5000   |  from Jan 20 - Feb 19
| 2011 | 2     | 44     |  from Feb 20 - March 19

I'm at a loss for how to go from my naive query to the query I need to generate this output.


texts:
I have a MariaDB table

id | key  | year | .... | position
1  | key1 | 2004 | ... |  3
2  | key1 | 2011 | ... |  2
3  | key1 | 2017 | ... |  1
4  | key2 | 1994 | ... |  2
5  | key2 | 2004 | ... |  1
6  | key3 | 2004 | ... |  1
7  | key4 | 1980 | ... |  2
8  | key4 | 2011 | ... |  1

Position is the row number within they Key field order by year descending

How can I create a trigger to autofill position when I insert, delete or update any row. thanks


texts: Create GIN index in a schema with the pg_trgm extension

texts:
I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn gin_trgm_ops);

To do the same within myschema I modified the migration to look like this:
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA myschema;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn gin_trgm_ops);

However, index creation fails with the following error:

operator class "gin_trgm_ops" does not exist for access method "gin"

I tried namespacing the operator class:
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA myschema;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn myschema.gin_trgm_ops);

but got the same error:

operator class "myschema.gin_trgm_ops" does not exist for access method "gin"

I also tried to install the extension without specifying the schema, which yields the same error.
What is the right way to use the pg_trgm extension in a schema other than public?


texts: How can I get the list of tables in all the stored procedure

texts:
I am trying to get the information about all the tables belongs to the stored procedure. I was able to get this information from below query. But, this query only gives information about the tables who are in the same database. What if my procedure is using the tables from other database separated by different schema? How can I modify my query to give information about Database name, schema name, table name of all the stored procedures in the given database. I would like to list the table who belongs to other database as well.

Query (SSMS - 2016)

  SELECT DISTINCT s.name,p.name AS proc_name, t.name AS table_name
  FROM sys.sql_dependencies d
  INNER JOIN sys.procedures p ON p.object_id = d.object_id
  INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
  INNER JOIN Sys.schemas s on s.schema_id=p.schema_id
  ORDER BY proc_name, table_name

Can anyone please help?


texts: Which columns to place the clustered index

texts:
I have a system that has been up-and-running for almost 3 years now. Previously (and currently) the front-end was using NHibernate for all of the database access.

Right now, I'm in the process of converting over to Dapper and utilizing stored procedures for EVERYTHING (reads, writes, etc).

Now that NHibernate is gone, I don't believe the current structure of tablename, tablenameid (clustered primary key) is optimal anymore for every table. Lots of my tables will NEVER be accessed via the primary key, UNLESS it's for a delete (even that can be avoided).

So, here are a couple examples of table structures, and which fields I believe should be the clustered index.

Example 1:

Event table
------------------------------------------
EventId int primary key clustered,
Season int not null,
EventDate datetime not null,
EventName varchar(100) not null,
EventType varchar(10) not null,-- (soon to be it's own table, but not right now...there are only 2 possible values, Tournament or Dual)
SchoolId int not null,
OpponentId int NULL,
etc...

So currently, I have the clustered index on the primary key. This is one table that is accessed 99% of the time by Season, and SchoolId. (Very rare that the EventId is used, when it is, it's for a delete)

Here's where the trickiness comes in. If the EventType is 'Dual', then uniqueness is by Season, EventDate, SchoolId, OpponentId. If the EventType is 'Tournament', Then uniqueness is on Season, EventDate, SchoolId, EventName (OpponentId will be null).

Under this architecture, I don't believe I can/should have a unique key, which isn't really my issue...

Am I safe to assume that the clustered index for this table should be on the Season and SchoolId columns?

Example 2:

WrestlerRanking table
-----------------------------
WrestlerRankingId int primary key clustered,
Season int not null,
Week int not null,
IsCurrent bit not null,
WrestlerId int not null,
etc...

In this scenario, similar to the one above, 99% of the this table is accessed by Season, WrestlerId, and IsCurrent. Uniqueness can be set by Season, Week, and WrestlerId.

Would/should the clustered index for this table be on Season and WrestlerId, even though the majority of joins to this table will include the IsCurrent column? This is definitely not unique as there could be around 20 records for the Season and WrestlerId combination (20 different weeks of rankings).


texts:
If I have inherited db that has grown to 400GB 1 MB chunk at at time. Is an offline defrag the only way to remove all the physical fragmentation. Another issue I have is that the data and log files are mixed and on the wrong RAID drives.

Can I kill 2 birds with 1 stone by backing up the db, delete it and restore it using WITH MOVE to put the files on the correct RAID drives. Will this eliminate the physical fragmentation?


texts: What is the right tool to process big .xel files (SQL Server Extended Events logs)?

texts:
My aim is to audit all queries on an instance, who's running them and from where, how many reads, in order to consolidate/migrate on another instance.

Figured out the best way is via Extended Events to .xel files, over 2 weeks, copy those and analyse on my well-specd desktop. Set up the capture with the minimal number of details (fields), so really can't make it smaller than this - aprox 12GB in 12 files. Used "Merge Extended Event Files" in SSMS to load the .xel files and process as detailed in https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/advanced-viewing-of-target-data-from-extended-events-in-sql-server?view=sql-server-2017

Is this really the best way to do it ? Is there a better tool to load the files ? It's really slow with SSMS 17.7 - example: with only 4 filters so far (before getting into grouping and aggregates) it took over 12h to get to crunch through 50% of the logs.


texts: Understanding Database Query Cost Optimzation

texts:
I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in the equation.

Textbook mentions a line where :

The number of levels (x) of each multilevel index (primary, secondary, or
clustering) is needed for cost functions that estimate the number of block
accesses that occur during query execution. In some cost functions the
number of first-level index blocks (bI1) is needed.

and the formula given for Secondary Index(B+ trees) is :

 Cost(C) = x+s;(on equality, =) and
 Cost(C) = x + (bI1/2) + r/2
 (for conditions <,<=,>,>=)
 where r = number of records (tuples)

My first question is, What does x actually refer to?

Because if you have a block containing the information which is available for identifying the root node of a secondary Index (B+ trees) in this case..why is there a need to take into number of levels as number of levels on first-level index is understandable as on might have to find all possible first level internal nodes to identify the position of the leaf node containing the data pointer.

My Second Question is regarding the join operation formulas, where

A and B are domain-compatible attributes of R and S, respectively. Assume
that R has bR blocks and that S has bS blocks:

■ J1—Nested-loop join. Suppose that we use R for the outer loop; then we get
the following cost function to estimate the number of block accesses for
this
method, assuming three memory buffers.We assume that the blocking factor
for the resulting file is bfrRS and that the join selectivity is known:

        CJ1 = bR + (bR * bS) + (( js * |R| * |S|)/bfrRS)
The last part of the formula is the cost of writing the resulting file to disk.
This cost formula can be modified to take into account different numbers of
memory buffers, as presented in Section 19.3.2. If nB main memory buffers
are available to perform the join, the cost formula becomes:

     CJ1 = bR + ( ⎡bR/(nB – 2)⎤ * bS) + ((js * |R| * |S|)/bfrRS)

■ J2—Single-loop join (using an access structure to retrieve the matching
record(s)). If an index exists for the join attribute B of S with index levels xB,
we can retrieve each record s in R and then use the index to retrieve all the
matching records t from S that satisfy t[B] = s[A]. The cost depends on the
type of index. For a secondary index where sB is the selection cardinality for
the join attribute B of S,21 we get:

     CJ2a = bR + (|R| * (xB + 1 + sB)) + (( js * |R| * |S|)/bfrRS)

For a clustering index where sB is the selection cardinality of B, we get

     CJ2b = bR + (|R| * (xB + (sB/bfrB))) + (( js * |R| * |S|)/bfrRS)

Taking the above formulas and the assumptions,

On basis of my understanding, If there are three memory buffers, why is the initial term br in the nested join loop being given when each time the comparison is being made with a tuple in the second buffer .... why read all the br values again for the join operation being performed with relation s which is what happens to be going on in the second memory buffer mentioned in the textbook?

lets suppose if there are six blocks of relation R and there are five memory buffers, wont it be better to take the tuples of R from the first buffer, perform the join operation in the second with tuples of S and save the resulting tuples in the third memory buffer in case of Single- loop Join ? because the cost would be then increased because the buffer cannot accommodate the whole relation itself and you might need to load unnecessary data multiple times?

Pardon me if my basic understanding of things is flawed to begin with.


texts: What exactly does the last_value() window function do?

texts:
While testing my answer to SQL - Getting data from first and last row of each group I noticed something strange.  The first_value and last_value window functions appear to behave differently.

As expected, first_value(col1) over (order by col2) finds the value of col1 from the row with the minimum value for col2.  But last_value(col1) over (order by col2) seems to find the value for the current row.  last_value seems unrelated to the group or partition it's operating on.

For the query below:

id  Session ID  bal
0   00000002    100
1   00000002    120
2   00000002    140
3   00000001    900
4   00000001    800
5   00000001    500

The expression:

last_value(bal) over (partition by [Session ID] order by id) as lv

Returns something different than:

first_value(bal) over (partition by [Session ID] order by id DESC) as fv_desc

Here's the result (note that the value of lv changes within one group):

lv       fv_desc
500      500
800      500
900      500
140      140
120      140
100      140

Example at SQL Fiddle with some extra columns.  Reversing the order by or omitting partition by doesn't seem to impact what last_value() returns.

If I read the MSDN page correctly, it suggests that last_value() should work as the opposite of first_value(), which is different from what I observe during testing.

Does last_value() do what it is supposed to do?  Why does last_value() allow you to specify a partition and/or order by clause when it doesn't seem to use either?


texts:
How to use multiple columns with a single COUNT?

Assume that there is a table demo with these data:

id |  col1  |  col2  |
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
1  |'alice' | 'book1'|
2  |'bob'   | 'book1'|
3  |'alice' | 'book2'|
4  |'alice' | 'book3'|

I want to find the count of all the combination of user and book name.

I have tried that if i run with distinct, everything is alright.
select count(distinct col1, col2) from demo

However, when i remove distinct, then the sql statement will not be passed in mysql console.
select count(col1, col2) from demo

Question is how to use COUNT with multiple columns in MySql?


texts: SQL Server: filegroup for system tables only?

texts:
One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements.

So it looks like this

fileid 1 = system tables, MDF
fileid 2 = t-log = LDF
fileid 3 = user stuff = NDF

Can anyone here help me understand the original justification why this was mandated?

I'll come clean and state I think it's voodoo. Am I wrong...?

Edit: I am aware of how to use filegroups for separation of indexes/partitions/archives, as well as how to restore piecemeal. This question is about the use of a separate filegroup on the same volume for system tables only.


texts: What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice

texts:
Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have columns of int type as well):

declare @test as table(
    col1 int
)

insert into @test (col1)
values(30500600)

select LEFT(test.col1, 2)
from @test as test

30 is returned as expected, but upon examining the Execution Plan I see a warning for the SELECT statement that reads:

  Type conversion in expression
  (CONVERT_IMPLICIT(varchar(12),[test].[col1],0)) may affect
  "CardinalityEstimate" in query plan choice

If I run the same above without pulling from a table:

select LEFT(30500600, 2)

30 is returned as expected and there is no warning in the Execution Plan.

Both statements should be performing LEFT() on int type so I don't understand why one returns a warning and the other doesn't. What's going on here?

Edit:

I've tried changing the col1 datatype declaration to varchar and that clears up the error. But that still doesn't explain to me why select LEFT(30500600, 2) by itself doesn't cause a warning if I'm passing an int directly to the LEFT() function.

I've tried cast to varchar in the original query: LEFT(cast(test.col1 as varchar(12)), 2) which returns the same warning.

Possible Duplicate

The linked duplicate states that computed columns were the issue for that user. There are no computed columns in my example, so that isn't the issue. Which leads me to the second part of the answer regarding implicit conversion to string types using the CONCAT function which is also the same for the LEFT function. In the second paragraph of my edit above I used CAST to explicitly convert to varchar so I wouldn't think that a conversion would be taking place? I'm obviously missing something here.


texts:
I am a programmer, dealing with a big table which the following scheme:

UpdateTime, PK, datetime, notnull
Name, PK, char(14), notnull
TheData, float

There is a clustered index on Name, UpdateTime

I was wondering what should be faster:

SELECT MAX(UpdateTime)
FROM [MyTable]

or

SELECT MAX([UpdateTime]) AS value
from
   (
    SELECT [UpdateTime]
    FROM [MyTable]
    group by [UpdateTime]
   ) as t

The inserts to this table are in chunks of 50,000 rows with the same date.
So I thought grouping by might ease the MAX calculation.

Instead of trying to find max of 150,000 rows, grouping by to 3 rows, and then calculation of MAX would be faster ? Is my assumption correct or group by is also costly ?


texts:
I’m attempting my first upgrade of Postgres from 9.3 to 9.5. I’ve run into two roadblocks but not sure how to get around them. I have my 9.3 PGDATA set to /data/postgres, which is a separate filesystem on my RHEL 6 server. After installing 9.5 and running initdb, I attempted to run pg_upgrade using this command line:

/usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.3/bin -B /usr/pgsql-9.5/bin
  -d /data/postgres -D /var/lib/pgsql/9.5/data --link -p 5432 -P 5433 -c

This command failed the link parameter test, because the 9.5 PGDATA value is not on the same filesystem as 9.3. Roadblock #1.

I then tried to re-initialize 9.5 using this command:

initdb -D /data/postgres95

It errored saying "Data directory is not empty!". Roadblock #2.

How do I proceed with this upgrade? I’d rather not move to a new server.


texts: Postgresql return results of dynamic SQL

texts:
I'm using Postgresql, version 12.

My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that the form of the table which is returned is not known in advance.

This is a follow up question to a previous thread , in which I learned how to dynamically create a table within a function with a lot of help from the StackExchange community. But now I realize it would be much nicer, instead of creating a new table inside the function, to just return the result of the select.

The actual problem I need to solve is as follows (this is repeated from the previous thread):

Say I have a table with the following information:

Table nominal_dollars:

year       GDP        Dividends
2000      100              20
2001      110              30
2002      120              40

In the above table, the numbers are not adjusted for inflation. A common procedure I want to do is create a new table, in which the numbers are adjusted for inflation. This will mean I need to join the above table with a new table, the consumer price inflation (CPI), which has the following form

Table cpi:

year       cpi_adjustment
2000      1
2001      2
2002      3

This will allow the creation of a new table, in which all of the numbers are adjusted for inflation, i.e. the original numbers are multiplied by the cpi adjustment:

Table nominal_dollars_adj:

year       GDP        Dividends
-----------------------------------------
2000      100              20
2001      220              60
2002      360              120

Where GDP  for the new table equals GDP from the original table * cpi_adjustment, and the same for dividends.

Now, I want to do this CPI adjustment procedure for many different tables --- which may have different numbers of columns.

The answer which was given by is Erwin is really nice (see below), and generates a new table dynamically. But my question remains --- instead of creating the new table, can I return the results in a select?

CREATE OR REPLACE FUNCTION f_results_inflation_adjusted(_tbl text)
  RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE for debugging
   EXECUTE (
   SELECT format('DROP TABLE IF EXISTS public.%1$I;
                  CREATE TABLE public.%1$I AS SELECT %3$s
                  FROM public.%2$I t JOIN public.cpi c USING (year)'
               , _tbl || '_adj'     -- %1
               , _tbl               -- %2
               , string_agg(        -- %3
                  CASE a.attname
                     WHEN 'id'   THEN 't.id'    -- case sensitive!
                     WHEN 'year' THEN 't.year'
                     ELSE format('t.%1$I * c.cpi_adjustment AS %1$I', a.attname)
                  END, ', ' ORDER BY a.attnum
                 )
          )
   FROM   pg_catalog.pg_attribute a
   JOIN   pg_catalog.pg_class c ON c.oid = a.attrelid
   WHERE  c.relname = _tbl
   AND    c.relnamespace = 'public'::regnamespace
   AND    NOT a.attisdropped
   AND    a.attnum > 0
   );
END
$func$;


texts: SQL Server: How do you prevent data being modified through an exposed view?

texts:
I have a database: DB 1

In a different database DB 2, I want to create a bunch of views that reference data in DB 1. These views will be relatively complex, with lots of joins, aggregations, sub-queries, etc.

I don't want users of DB 2 to be able to modify the data in DB 1. i.e. I don't want users to be able to be able to execute INSERT, UPDATE, DELETE queries on the view.

This blog post: http://sqlhint.com/sqlserver/views/creating-readonly-views

suggests I can use an INSTEAD clause. I would prefer to just dissalow certain query clauses on the view itself except for a particular user. Is this possible?


texts: /usr/sbin/mysqld was deleted and replaced by another mysqld with different inode

texts:
This happened on three of our mysql server running on RHEL. While checking for open and deleted files, I found that mysqld in use is deleted (as seen in lsof) and was replaced by a similar mysqld (in /usr/sbin/mysqld) with a different inode. Size and blocks of both the files (deleted and current) are same. new mysqld (not in use) seems to be of the same version as the deleted one. I am trying to figure out what could have caused this (there are no cronjobs running on the system). I checked system logs, database logs and yum logs and found nothing relevant. Any input is appreciated.

Thanks!


texts: Database Design - different objects with shared tagging

texts:
My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here.  I'm trying to figure out the best way to design the database for an application I'll be coding.

The situation: I've got Reports in one table and Recommendations in another table.  Each Report can have many Recommendations.  I also have a separate table for Keywords (to implement tagging).  However, I want to have just one set of keywords that gets applied to both Reports and Recommendations so that searching on keywords gives you Reports and Recommendations as results.

Here's the structure I started out with:

Reports
----------
ReportID
ReportName

Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)

Keywords
----------
KeywordID
KeywordName

ObjectKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)
RecommendationID (foreign key)

Instinctively, I feel like this isn't optimal and that I should have my taggable objects inherit from a common parent, and have that comment parent be tagged, which would give the following structure:

BaseObjects
----------
ObjectID (primary key)
ObjectType

Reports
----------
ObjectID_Report (foreign key)
ReportName

Recommendations
----------
ObjectID_Recommendation (foreign key)
RecommendationName
ObjectID_Report (foreign key)

Keywords
----------
KeywordID (primary key)
KeywordName

ObjectKeywords
----------
ObjectID (foreign key)
KeywordID (foreign key)

Should I go with this second structure?  Am I missing any important concerns here?  Also, if I do go with the second, what should I use as a non-generic name to replace "Object"?

Update:

I'm using SQL Server for this project.  It's an internal application with a small number of non-concurrent users, so I don't anticipate a high load.  In terms of usage, the keywords will likely be used sparingly.  It's pretty much just for statistical reporting purposes.  In that sense, whatever solution I go with will probably only affect any developers that will need to maintain this system down the line... but I figured it's good to implement good practices whenever I can.  Thanks for all the insight!


texts: SQL Server Transactional Replication snapshot delivery loop

texts:
I have configured transactional replication from SQL Server 2008 to SQL Server 2014. And everything is OK, except one thing: after the initial snapshot is delivered to subscriber, distribution agent starts to deliver it again, the same articles in the same order, instead of applying undistribution transactions, pending delivery.

I tried to reinitialize subscription, re-create subscription and generate new snapshot, but it doesn't help.
There are no any errors in any of agents history, but snapshot delivery is just looped.

How can it be fixed?


texts: Full Back up over to Network failed due to unknown network error 59

texts:
I am recently seeing the Unknown Netowrk Error 59 which saves the full  back ups to a file share on some of the servers . The back ups happens at different times.

Ola Halengren scripts are being used currently and the sql server edition used are 2012, 2014 enterprise edition.

I see there are suggestions for to create a DWORD with name ‘Sesstimeout’ and value 360 in  HKLM\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters.

My question is should we create this on the target server (file share) or the server from where the sql server resides


texts:
So, I've ran the BrentOzar script which has identified 10195 plans for the same query!!!  The query is below:

SELECT *
FROM [table1]
INNER JOIN [table2]
  ON [table1].[versionId] = [table2].[VersionId]
INNER JOIN [table3]
  ON [table2].[ContentId] = [table3].[nodeId]
INNER JOIN [table4]
  ON [table3].[nodeId] = [table4].[id]
WHERE ([table4].[nodeObjectType] = 'abcde123-fgh3-4ijk-8lmn-424f222332ff')
  AND ([table1].[published] = 0
  AND [table1].[releaseDate] <= '2017-07-22 17:43:47')
  AND ([table1].[newest]=1)
ORDER BY [table2].[VersionDate] DESC, [table4].[sortOrder]

The only difference between all 10195 of them is the date field (releasedate). value which has a different date for each plan.

Regarding indexes, the following applies:

Table1: NC on nodeid and versionid, C on versionId
Table2: NC on versionID, C on ID
Table3: NC on nodeid, C on pk
Table4: NC on nodeObjectType, NC on parentID, C on ID

Anyone any ideas what's the best way to resolve this as it's getting a big ridiculous!

Thanks in advance!


texts:
I found some column values have been changed on a table, I need to view when and who made these changes.

I tried to read the transaction log backup file, but I didn't understand it and cannot find the information that I look for.

Any help please. If there is a way, if no for the future which is the best way to audit database transactions without impact the performance, SQL audit, or trace or custom triggers?


texts: Difference between worker threads and sessions in SQL Server

texts:
I'm looking at Azure Premium/P3 database offering under "Overview of the Performance Model" on http://msdn.microsoft.com/en-US/library/azure/dn741336.aspx

What's the difference between worker threads and sessions? Am I getting it right when I think there could be at most 16000 client machines opening a concurrent session to the same database but only 1600 queries can be executed concurrently?


texts: Why is the value of NUMERIC_SCALE column in INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE='double' NULL?

texts:
I have created a column whose data_type is DOUBLE.

alter table my_table add column my_column double;

When I queried about this column in information_schema.COLUMNS table
I found that the NUMERIC_SCALE for this column is NULL?

select DATA_TYPE,
NUMERIC_PRECISION,
NUMERIC_SCALE

from information_schema.columns

where table_schema = 'my_schema'
and table_name = 'my_table'
and column_name = 'my_column'

+-----------+-------------------+---------------+
| DATA_TYPE | NUMERIC_PRECISION | NUMERIC_SCALE |
+-----------+-------------------+---------------+
| double    |                22 |          NULL |
+-----------+-------------------+---------------+

Can someone explain this behavior?


texts:
I want to change the datadir path in the my.ini file.

The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/

The size of my C: drive is 30 GB and the MySQL data folder is occupying 25 GB. So I want to change the datadir to F:/naveen/data.

Is this possible?

My machine is running Windows Server 2003.


texts: What is the best way to migrate multiple SQL Server databases?

texts:
I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012.

There are around 50 to 100 databases.

What is the best way to migrate the database server without affecting clients (meaning no downtime)?


texts: sync MS SQL Server 2014 and Express 2014 Database using Stored Procedure

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

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

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


texts: What mongodb database layout would fit better

texts:
I'm currently writing an application using the MEAN stack, pretty much only for fun and because MEAN is "cool" at the moment.

Therefore I'm thinking about the database layout. The data needed to be saved represents a music collection. So I had two ideas. But because I'm quite new to NoSQL, I don't know which one I should use or if both of them are rubbish.

First idea:

One big collection "artists" with an array "albums". This array also has an array called "songs". Here I don't know what to do about playlists!

Second idea:

Four collections "artists", "albums" ,"songs" and "playlists" all linked using the _id   field - This is how I would do it in a traditional database, like MySQL.

Which one would in your opinion fit better and - that's what I'm even more interested in - why?


texts: ORA-01031 Insufficient Privileges Raised From Within Function

texts: When I run a CREATE USER statement within Sql Developer, the user is created just fine.  When I try to call a function which performs CREATE USER (followed by a bunch of other statements) I get the ORA-01031 error when that statement is executed.  On another machine this function is apparently completing successfully.  I have checked all the roles and system privileges but nothing stands out.  What could it be?


texts: Why is this query throwing an "Invalid Column Name" error?

texts:
Trying to run a simple UPDATE query as follows:

UPDATE [TILL].[dbo].[appParameters]
SET [TILL].[dbo].[appParameters].[ParameterValue] = "1"
WHERE [TILL].[dbo].[appParameters].[ParameterName] = "StaffTablesRefreshed"

If the field [ParameterName] is the value "StaffTablesRefreshed", then set the value of [ParameterValue] to "1".

When executed, SQL Server throws an error:

  Invalid column name 'StaffTablesRefreshed'

Both [ParameterName] and [ParameterValue] are nvarchar(255).

Am I missing something obvious here?

Thanks,
Ken


texts: Why T-log is not truncated after T-log backup?

texts:
I have a database myDB in full recovery model. I took a full backup and restored it on the test server. Afterwards, I checked the size of T-log. The size of t-log was 15600 MB and used space out of total space was 351 MB. I decided to truncate those 351 MB and ran T-log backup. Unfortunately, the size does not change at all. It again showed 351 MB. Then I took other subsequent t-log backups. The size still 351. Why t-log backup does not truncate log file? I am not asking about shrinking, I just want to know why t-log does not truncate t-log.

Here are some details regarding t-log:

From DBCC LOGINFO

From querying log_reuse_wait_desc:

About the size of T-log:


texts: TSQL trigger on actual changes - update field

texts:
Background -
Table that looks like this:
country_id
product_id
flow_date
update_date
amount

country_id, product_id, flow_date, and update_date are a composite PK, because I need to track the history of updates.
However, many times, data is inserted to the table where the amount hasn't changed. (I.e. country, product, flow date and amount are the same, but update_date is new.)  In those cases, I just want to update the update_date of the existing entry, rather than insert a new field.  Otherwise, I want it to insert as usual.  (I'm really not sure that the below will do this later part.)
This is what I have so far:
CREATE TRIGGER history_archive_changes_only
ON history_archive
AFTER INSERT
AS
BEGIN
    UPDATE history_archive
    SET update_date = i.update_date
    from history_archive
    INNER JOIN inserted i on (i.country_id = history_archive.country_id
                            AND i.product_id = history_archive.product_id
                            AND i.flow_date = history_archive.flow_date
    WHERE abs(history_archive.amount - i.amount) < 0.01

END;

Thoughts?


texts: Best way to handle dates prior to 1000 A.D. in MySQL?

texts:
I am creating a database for records that extend prior to 1000 AD, but MySQL Date  and DateTime fields only support dates starting at 1000.

Is there a way that would be more convenient than either using a bigint type to count seconds before/after 1/1/1970 using a Unix timestamp, or switching to a database software that supports larger date ranges?


texts: Error while setting up Master Slave Replication on Ubuntu

texts:
I am trying to setup a Master slave Replication on Ubuntu. I have 2 VM's one master and one slave. I have connected to Ubuntu via Putty.
I am Following this link to configure : https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
Installed MySQL server on both the Server's and while configuring the Master server in my.cnf file with the help of "sudo nano /etc/mysql/my.cnf" Inserted the lines given in the blog and replaced them with my values.
It is throwing an error stating

Job for mysql.service failed because the control process exited with
error code.    See "systemctl status mysql.service" and "journalctl
-xe" for details.

Tried multiple times resolving the error but was unable to. I would really appreciate if any one could help ne with this.
Thanks.


texts: Should I switch my database LOG volumes from IO1 to ST1

texts:
I've got a SQL 2014 database that's been running in AWS for a couple of years now, in a dual-server mirrored configuration, using separate EBS volumes for data, logs, etc.  When the system was created, the performance characteristics of the various EBS volume seemed to indicate that IO1 was the safest option for the kind of latency and throughput we needed for the log volume (it's a pretty busy database and at the time we were willing to pay extra to make sure the transition from self-hosted when smoothly).  Since then, AWS has added a new much cheaper volume type, ST1, that's backed by HDD instead of SSD (see AWS EBS volume types).  We're also now interested in cutting costs if we can do so without a major impact on performance.  Amazon specifically lists 'Log Processing' as one of the use cases for this type of volume.  The performance characteristics of ST1 allow for better throughput (MB/sec) but fewer IOPS than IO1 or GP2 type volumes, and given my understanding of database logs, that would seem to be perfect, so I'd like to make a switch.

However, in checking the CloudWatch metrics for these volumes (we actually have two databases on two servers with each server as principal for one of the database and as mirror for the other database), the 'Write Bandwidth (KiB/s) is FAR below the 500 MiB/s limit (generally <3 MiB/s), but the 'Write Throughput (Ops/s)' metric frequently goes up to about 2000.  The docs say that for ST1 the Max. IOPS/Volume is 500, assuming a 1Mib I/O size, but it's not clear whether the 500MiB/s limit comes from the IOPS limit or vice-versa.  It's also not clear whether this Ops/s is the same as IOPS.  Although logic would seem to indicate that they are, I have other volumes on those systems that I recently switched to ST1, and CloudWatch shows up to 10,000 Write Ops/s for those, so maybe not.

So, my questions are:

Would it be prudent to switch the log volumes to ST1?
If yes, what is the best way to go about doing this?

Taking the databases offline, even for a few minutes, to make the switch is NOT an option.

AWS may provide a 'seamless' 'online' conversion of these volumes (there is a warning that it may or may not be available for these volumes, possibly because they were created before that features was guaranteed to be available).  I converted the EBS volumes we use for backups on the same servers from GP2 to ST1 this week and had no problems at all, but if those volumes had performance issues during the conversion process, it wouldn't have affected the system anyway, as they only get used at night when we do the database backups.  There is no indication I can find about the performance impact of the conversion process itself, nor whether or not it can be cancelled should it cause issues.

The other two possible migration methods I can think of are:

Add new ST1 volumes to both the principal and mirror, add a new log file and set the log file parameters such that it will attempt to put all the log data into the new file.  Then run log backups, delete the old log file, and then remove the old log volume.
Add a new ST1 volume to the mirror, take the mirror offline, move the log file from the old volume to the new one, then bring the mirror back up, failover, and repeat with the principal.

Any advice or insight into the inner workings of EBS or SQL 2014 would be greatly appreciated.  For example, how exactly does EBS enforce performance limits on the various types of volumes?  Will SQL 2014 automatically increase the size of units it writes to the log in response to an increase in log file latency?  (If it automatically flushes to the log every time data is pending and the previous flush completes, for example, such a change might simply increase how much is written each time and have little impact on overall throughput, and I'm much more concerned with throughput than latency).


texts:
I have a mysql master/master setup with tungsten replicator. My server is reaching out of space, occupied around 30G in 6 hours. I'm not sure how to handle this. Would be great if some one can help me out of this.

Thanks,
Swaroop.


texts: What is the meaning of MariaDB `mysql`.`servers` table constraint?

texts:
I am using SPIDER engine to access a remote table from within my MariaDB database, which requires a server definition in mysql.servers table created via CREATE SERVER statement.

I noticed that this table has a column named Db of type char(64) with a constraint hitting information_schema.SCHEMATA.SCHEMA_NAME field.

I was wondering what is the meaning of this, since the database referred by this field might be on a different server? I feel I am missing something but I can't guess what.


texts: Does a mirroring configuration requires the database do stop receiving requests?

texts:
I want to configure a SQL Server 2012 Mirroring. I know that I have to create a full backup of the Principal server and restore it at the Mirror server. After that, I will backup the transaction log of the Principal and restore it at the Mirror server.

My doubt is: if my Principal database is online (in production) and receiving many insert/delete/update requests, is it a problem? The mirroring and sync can be done without the need of stop my Web application and the Principal Database will be 100% available during this process? I'm worried because the transaction log that will be restore at the Mirror will not represent all transactions because some of then will happen between the backup-restore and the start of the mirroring sync.


texts: strange optimization of query results in phantom value conversions

texts:
-- DB collation is German_PhoneBook_BIN
CREATE TABLE [dbo].[partition_tbl](
    [tbl_name] [varchar](255) NOT NULL,
) ON [PRIMARY]
INSERT INTO partition_tbl VALUES ('some_table_{num}')
INSERT INTO partition_tbl VALUES ('other_table_{num}')

assuming in your database are the following tables

some_table_123
some_table_444
some_table_hist
some_table_555
other_table_100
other_table_4321

the following statement

SELECT a.tbl_name, REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS number
FROM partition_tbl a
INNER JOIN dbo.sysobjects b
ON (
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
    OR
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
) AND b.xtype='U'

should return

tbl_name          | number
------------------|-------
some_table_{num}  | 123
some_table_{num}  | 444
some_table_{num}  | 555
other_table_{num} | 100
other_table_{num} | 4321

you can see, that some_table_hist is omitted. But why can't I convert the column number (which is a NVARCHAR at this time) to type INT with both following statements?

SELECT a.tbl_name, CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS INT) AS number
FROM partition_tbl a
INNER JOIN dbo.sysobjects b
ON (
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
    OR
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
) AND b.xtype='U'

and

SELECT tbl_name,CAST(number AS INT) AS number FROM (
    SELECT a.tbl_name, REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS number
    FROM partition_tbl a
    INNER JOIN dbo.sysobjects b
    ON (
        b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
        OR
        b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
    ) AND b.xtype='U'
) x

Both statement results in an error: Failure on converting the nvarchar value hist to int data type (real error in german says: Fehler beim Konvertieren des nvarchar-Wertes 'hist' in den int-Datentyp). Huh? I can see no hist value in resultset above!? Does MSSQL doing a wrong statement optimization?

(BTW: I'm using now another statement which fixes this issue. I'm only interested in the reason why this strange effect occurs)


texts:
I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP.

Is there any simple way to do that? Does, SQL Server execute any SP or anything after job completion?


texts: Full outer join does not work for two small tables, error 1064

texts:
folks.

I am currently exploring the capabilities of MySQL while doing simple exercises with diverse queries.

I was reading the following article that explains how table JOIN works:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

However, I am stuck at the FULL OUTER JOIN example (comprised of tableA and tableB):

tableA:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> select * from tablea;

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Pirate    |
|  2 | Monkey    |
|  3 | Ninja     |
|  4 | Spaghetti |
+----+-----------+

tableB:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> select * from tab

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Rutabanga   |
|  2 | Pirate      |
|  3 | Darth Vader |
|  4 | Ninja       |
+----+-------------+

I am trying the same as proposed in the website:

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

and receive error 1064.

Does anyone have an idea what the reason could be?

Thank in advance for the assistance.


texts:
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.

This application will have 1M users, each user will have their own dashboard where they can create their own users/roles, pages and posts.

I am thinking about two possible solutions

Use only 1 table for all users, pages and posts and they will have a foreign key of the owner
Use separate tables for all users, like user1_pages, user1_posts, user1_users

I Think with #1 the query will be slow as all users will be sharing one table, second one also don't seems a perfect solution as number of tables will increase exponentially.

Please suggest me a best possible solution and share tips to design an optimized MySQL schema.


texts: Getting an error "ORA-00093: pga_aggregate_target must be between 10M and 4096G-1" on Oracle DB startup

texts:
My Oracle DB 12c instance was not starting up. While connected as an idle instance ( / as sysdba ) and executing startup; command, I was getting an error:

  ORA-00093: pga_aggregate_target must be between 10M and 4096G-1


texts: Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?

texts:
When I try to create indexes on a temporary table in SQL Server when using the SNAPSHOT transaction isolation, I get this error:

  Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.

Why is it not allowed to create indexes on temporary tables in SQL Server when using snapshots?

I don't understand this, if I'm allowed to create temporary tables, why am I not allowed to add indexes to them?


texts:
I came across 1 query which is

Select * from R Natural Outer Join S

Where R=(A,B) has tuples {(1,2),(1,2),(3,4)} and S=(B,C) has tuples {(2,5),(2,5),(4,6),(7,10)}.

To implement this I created 2 relations named R and S.

create table R
(
A number(5),
B number(5)
)

create table S
(
B number(5),
C number(5)
)

And I inserted the provided tuples in it.

Now while implementing this I came to know that "Natural Outer Join" is not supported by the Database tool that I am using (Oracle) so I used the following query

select *
from R
natural full outer join S

output

2 1 5
2 1 5
2 1 5
2 1 5
4 3 6
7   10

Now coming to my question

Is "Natural outer join" same as "Natural full outer join"?
How the matching of records are being done here?

Because there is no primary key defined in any of the tables I think it should do cross join and display 16 records which is not the case.

It would be very helpful if anyone can explain this behavior to me.


texts:
I wonder if you could make the upgrade of the procs easier and faster by providing a sqlcmd-skript or something which executes all sp_blitz[xxx].sql files.
I first did a little sqlcmd-script myself (but failed because of quoted_identifiers). It would be great if something like this could be a part of the package? Or is there already a easy way to implement new sp_blitz[xxx] versions on a server?


texts: Bypass stored procedure execution CPU consumption

texts:
We are using Microsoft SQL Server Agent Jobs to execute stored procedures on the other database systems (like MySQL or Oracle).

Usually we run procedures via linked servers (over OLEDB and ODBC drivers) and queries like:

EXECUTE ('CALL DB.sp_MyProcedure') AT OTHER_DBS;

(Not very good method, but I don't have access to schedule other DBS.)

Well, I observe queries with sp_whoisactive and have seen high summary CPU consumption for these jobs — i.e. 2,000,000 in 30m query duration as OLEDB waiting.

What is the reason of high CPU consumption?


texts: Connecting to an external database with pgAdmin III

texts:
I'm trying to connect to an external database from pgAdmin III (which is installed on both machines).
The client complains:

  Access to database denied

  The server doesn't grant access to the database: the server reports

FATAL: no pg_hba.conf entry for host "192.168.108.161",
                                user "postgres",
                            database "postgres",
                                 SSL off

  To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication).
  PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

  The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings.
  You'll probably want to add something like

host all all 192.168.0.0/24 md5

  This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.
  You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file.

  After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

Where the server explicitly states that all connections from the internal network are accepted.

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             192.168.0.0/24          md5

I have already restarted the postmaster for the changes to take effect, and have gone so far as to restart the entire machine.
What else could be going wrong here?


texts: I use a slow.log logging on my MySQL server, to catch bottlenecks in my script, but at the same time, i use phpmyadmin on this server. My script and pma has different MySQL user accounts and now, when i analyzing the slow.log file, i see a lot of spam from pma queries, is it possible, to configure the MySQL to logging a slow queries only from specific users?


texts: Move local SQL Server DB to hosted SQL Server

texts:
I have a SQL Server database here that contains the info for the Gemini Issue Tracking software. It is running on a Windows XP system and I have SQL Server Management Studio (SSMS) on that system. I can successfully back it up to a xxx.bak file.

Now I want to bring that database up on an instance of SQL Server at a paid hosting site. I do not have shell access. I can log SSMS into the hosted database. That one uses SQL Server username/password foo\bar. My local database uses Windows Authentication HOST\USERNAME. I cannot replicate that Windows user onto the hosted database. I can transfer the xxx.bak backup to the hosted site through their Plesk control panel and I see it in my "files". I found out the physical path to it C:\Inetpub\vhosts\xxx.yy.com\, so I can construct a TRANSACT SQL statement

RESTORE DATABASE new_db_name
FROM DISK="C:\Inetpub\vhosts\xxx.yy.com\xxx.bak"
WITH RESTORE

which forces it to wipe out the one they created with the info in the .bak.

  Msg 3110, Level 14, State 1, Line 1
  User does not have permission to RESTORE database 'new_db_name'.

Also the original backup is from a database with a different name. So I have a source database with Windows Login and one name, and I want to restore the data to another with a different SQL Server username/password and a different name (the first part of the name is fixed).

I am certain that SSMS is logged in under the SQL username/password as I can see the tables in the empty database. My SQL Server user has read/write permissions.

What  issues am I going to face assuming I can figure out how to get the RESTORE DATABASE to work? What is the best way to do this?


texts: Sql Server rollback to some point in time

texts:
This seems like a fairly common scenario, yet I'm not sure how to do this.

I have a very large database (think a lot of terabytes), which makes it impossible to create a full backup. At some point, I want to run an update script on the database that can do some schema modifications, alter some data, etc.

To be on the safe site, I would like to be able to undo these operations if something goes wrong. It's okay if no-one can access the database during the update process.

To make it a bit more complex, I don't want to ruin the daily maintenance / backup process.

I've been thinking about putting the database in single server mode and then rolling back the transaction log for a specified period of time, but I'm not really sure how to do this (and time doesn't feel really reliable). I've looked at the 'checkpoint' function, but am unsure how to use this. Anyone?


texts:
My goal is to select rows from a mysql table, but according to certain column. Normal SELECT would be:

        SELECT
        ti.id, ti.userid, ti.date, ti.duedate, ti.datepaid, ti.subtotal, ti.taxrate, ti.paymentmethod, ti.notes AS invoicenotes,
        tii.invoiceid, tii.type, tii.description, tii.amount,
        tc.id, tc.firstname, tc.lastname, tc.companyname, tc.email, tc.address1, tc.address2, tc.city, tc.state,
        tc.postcode, tc.country, tc.phonenumber, tc.currency, tc.defaultgateway, tc.credit, tc.taxexempt,
        tc.latefeeoveride, tc.overideduenotices, tc.separateinvoices, tc.disableautocc, tc.datecreated,
        tc.notes AS tcnotes,
          (SELECT GROUP_CONCAT(value) FROM tblcustomfieldsvalues WHERE relid=tc.id) AS vatid
        FROM tblinvoices ti
        LEFT JOIN tblinvoiceitems tii
        ON tii.invoiceid=ti.id
        LEFT JOIN tblclients tc
        ON tc.id=tii.userid
        WHERE ti.status='Paid'
        AND ti.infakt_no IS NULL
        AND ti.paymentmethod='transferuj'
        ORDER BY ti.userid AND tii.id

But that kind of SELECT would split rows with same userid value. How to SELECT certain amount of rows and keep from separating userid?


texts: Oracle: Resetting an identity column vs resetting a sequence

texts:
An Oracle Identity column can be reset by simply changing its start value with an alter table... modify statement. Given that identity column internally uses a sequence, why is it not possible to do the same with a sequence i.e. change its start value to a specific number with a single alter statement? So far the only way I've seen to do this is through a procedure involving multiple steps.

Or, to put it in another way, how does Oracle internally handle the altering of start value of an identity column? Does it drop and recreate the backing implicit sequence? Or is there a way to reset a sequence which is just not accessible to users?


texts: Can any database table name support double quotes "?

texts: I need to know whether any database supports double quotes as a part of their table name. I am especially interested in JDBC compliant databases. And if they do, can I please know how?


texts:
The Query:

select JOIN_RESULT.batch_id as BATCH_ID,
       JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
       JOIN_RESULT.brand as BRAND,
       JOIN_RESULT.message_date_time as UPLOAD_DATE,
       decode(JOIN_RESULT.recordstatus, '5', 'SUCCESS', 'FAILED') as UPLOAD_STATUS,
       decode(JOIN_RESULT.recordstatus,
              '5',
              null,
              (decode(STATUS_RESULT.vendor_unique_doc_id,
                      JOIN_RESULT.unique_doc_id,
                      STATUS_RESULT.comments,
                      JOIN_RESULT.comments))) as FAILURE_REASON
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d
         inner join doc_recon_record_w r on d.vendor_unique_doc_id =
                                                      r.unique_doc_id) STATUS_RESULT
  full join (select r.batch_id,
                    r.unique_doc_id,
                    r.brand,
                    h.message_date_time,
                    r.recordstatus,
                    r.comments
               from doc_recon_record_w r
              inner join doc_recon_header_w h on r.fileid =
                                                           h.fileid) JOIN_RESULT on STATUS_RESULT.vendor_unique_doc_id =
                                                                                JOIN_RESULT.unique_doc_id
 order by JOIN_RESULT.batch_id

  The Question:

How can I optimize the performance of this query?

Using joins good or is it better to do a Cartesian Product?


texts: How to view contraints of a cloumn in postgresSql?

texts:
I applied a constraint but forgot its name. Now I want to drop it using ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME] . If there is a way to drop all constraint from a column that should work too.
I cannot use psql command.


texts: Splitting Comma Separated string into columns by using REGEXP_SUBSTR

texts:
Here is my example string:

select 'po1,qty1,po2,qty2,..,POn,QTYn' str from dual.

The string length is dynamic. It may have more than one PO and QTY.

Now, I want the output displayed as all the POs in first column and quantities in
second column.


texts:
Let us say I have a master(m) slave(s) replication. Now, in the scheme of things, I introduce another database/schema(foo) not associated with the master in any way and I want this schema to be replicated into the slave(s). Can I do this?

AFAIK, this cannot be done. What is the best way to pull this off? The reason I want to do this is I want to join tables from foo with s/m.

The data replication need not happen in real time, can be a daily cron job too. Is mysqldump the way to go? Is there some hack that I can pull off?


texts:
It is possible to do Unpivot operation, where column names are done via select, without using dynamic SQL ?

I tried this, but it fails.

SELECT *
FROM [dbo].[Report]
UNPIVOT
(
  Value
  FOR WeekName IN
  (
      SELECT COLUMN_NAME FROM information_schema.columns c
      WHERE c.TABLE_NAME = 'Report'
      AND ORDINAL_POSITION > 1
      ORDER BY ORDINAL_POSITION
  )
) u


texts: ORA-01652: Extend a tablespace in Oracle 12c, no DBA_DATA_FILES table

texts:
I know that there are other questions about this topic but reading and trying them they cannot solve my problem. Mainly because select * from DBA_DATA_FILES; shows me an error when executing, because the table doesn't exist in the database.

I know the different tablespaces using the following query, also looking not standard :

SELECT * FROM USER_TABLESPACES;

The ORA error ORA-01652 was thrown when executing a request which should use many temporary space.

The tablespace which have the problem is R1XQDTM1. Below the tablespace info :

The problem is that i don't know the datafiles location for this tablespace to execute the solution request ALTER TABLESPACE ADD DATAFILE


texts: How do I restore 3 tables into a MySQL database from a single Windows dump file?

texts:
I want to import 3 specific tables from a dump file that contains 270 tables.

The dump file, naveen_ifs_db.sql, is 20 GB.

How do I restore 3 tables from that dump-file using the command line in windows?


texts:
There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in parallel and restoring them in parallel too.

Btw, SET FOREIGN_KEY_CHECKS=0 is not a solution, becase standart mysql dump creates dump file with that statement.


texts: PostgreSQL 9.3 Query Design Optimisation - Improving speed

texts:
I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user experience as it powers a set of visualisations in the UI.

There are three tables in use in the query (see diagram):

Simulation
- simulation_id serial (Primary Key),
- probability double precision,
- ... other columns ...

Result_Data
- result_data_id bigserial, -- primary key
- simulation_id integer foreign key references simulation(simulation_id),
- result_variable_id integer foreign key references result_variable(result_variable_id),
- time double precision,
- value double precision

Result_Variable
- result_variable_id serial, -- primary key
- ... other columns...

There are indexes on the primary keys, and an unique index on result_data(simulation_id, result_variable_id) as result_data_result_variable_id_simulation_id_idx.

The table sizes are as follows:

Simulation: ~500 rows
Result_Data: ~70,000,000 rows
Result_Variable: ~544 rows.

The data stored in result_data is per simulation, for each result variable, store the time series data. There are ~240 time steps per variable.

The query currently being run is getting data across all simulations (with the simulation's given probability) for a given result_variable. The reason the query is run multiple times is that the user is given a chart per variable to navigate.

Query:

SELECT
    s.probability,
    r.time,
    r.value
FROM simulation s
JOIN result_data r USING (simulation_id)
WHERE
    s.run_id = 2197 AND
    r.result_variable_id = 228433
ORDER BY r.time

This query returns ~130,000 rows in ~2500ms.

The query analyze returns:

Sort  (cost=334630.68..334949.52 rows=127535 width=24)
  Sort Key: r.time
    ->  Hash Join  (cost=25007.56..323815.38 rows=127535 width=24)
          Hash Cond: (r.simulation_id = s.simulation_id)
          ->  Bitmap Heap Scan on result_data r  (cost=2388.96..298167.82 rows=127535 width=20)
                Recheck Cond: (result_variable_id = 228433)
                ->  Bitmap Index Scan on result_data_result_variable_id_simulation_id_idx  (cost=0.00..2357.08 rows=127535 width=0)
                      Index Cond: (result_variable_id = 228433)
          ->  Hash  (cost=15161.60..15161.60 rows=596560 width=12)
                ->  Seq Scan on simulation s  (cost=0.00..15161.60 rows=596560 width=12)

Any help in optimising this further would be most appreciated. I've simplified everything as much as possible, the server itself is tuned for the PostgreSQL 9.3 installation.

EDIT to include EXPLAIN(ANALYZE, BUFFERS)

Sort  (cost=113825.62..113825.77 rows=60 width=24) (actual time=308.862..338.584 rows=128821 loops=1)
  Sort Key: r.time
  Sort Method: quicksort  Memory: 13137kB
  Buffers: shared hit=131844
  ->  Nested Loop  (cost=0.99..113823.85 rows=60 width=24) (actual time=0.070..242.060 rows=128821 loops=1)
        Buffers: shared hit=131844
        ->  Index Scan using simulation_run_id_idx on simulation s  (cost=0.42..22.62 rows=280 width=12) (actual time=0.043..0.835 rows=539 loops=1)
              Index Cond: (run_id = 2197)
              Buffers: shared hit=514
        ->  Index Scan using result_data_result_variable_id_simulation_id_idx on result_data r  (cost=0.57..404.06 rows=237 width=20) (actual time=0.015..0.350 rows=239 loops=539)
              Index Cond: ((simulation_id = s.simulation_id) AND (result_variable_id = 228433))
              Buffers: shared hit=131330
Total runtime: 356.395 ms

There are slight changes as I have added an index to Simulation(simulation_id) - the query still runs at ~2500ms.


texts: How to get value by key from unstructured json column in Postgres 11

texts:
I have jsonb column with unstructured json. It can contain any combination of keys and arrays.

I need to find if the value of key "Grade" contains the specified string. I managed to do that in Postgres 12.1 using jsonb_path_query_first. Unfortunately I have no access to Postgres 12 in production and the highest I can go is the version 11.

I tried everything I could think of to solve this in Postgres 11, but nothing worked. Basically I need to translate this to work with Postgres 11:

select "Id" from (
  select (jsonb_path_query_first("Data", '$.**."Grade" ? (@ <> null)')) as grade, "Id" from "Product"
  where "GroupId" = '8435c76f-f7ab-44d7-b72f-b2a6f0465df5' or "GroupId" = '71010f76-fd97-4aa4-99d1-dadc3a5f9675'
)
as test where grade is not null and grade::text like '%test%';

Example data:

{
"general": [{"Grade": "aaaaaaaaaa"}, {"DesignationAnsi": "xxxxxxxx"}],
"details": [{"Name": "bbb", "Unit": null, "Value": "cccc", "Description": "ddd"}, {"Name": "eee", "Unit": null, "Value": "fff", "Description": "ggg"}]
}

Note: Grade can be null, may not be present at all, and can be present at multiple places at once (any value is ok)


texts:
I want to get the count of all rows in a single collection into an output file. What is the correct way of doing this
Here is what I did but I am encountering an error.
mongoexport --host 10.1.0.1:27017 --db proddb --collection  UserColl --query "{db.getCollection('UserColl').count()}" --type=csv --out=output.csv


texts: Is there any open source GUI tool to monitor cassandra?

texts:
I would like to monitor cassandra database as well as need to check the values in the database.

Could any one suggest me best open source GUI tool ?

Regards,

Arun Raj.


texts: Chunk error when upgraded to mongodb 2.6 while uploading doc

texts:
I was trying to upgrade mongodb 2.2 to 2.6 and upon testing 2.6 received chunk out of order error..
I recieve an unexpected error from the server: exception: chunks out of order at /home/ps203k/.perlbrew/libs/perl-5.20.2@oasis-gsp_oss/lib/perl5/x86_64-linux-thread-multi/MongoDB/GridFS.pm line 236.
it is standalone.. and not sharded..
only once instance of mongodb running in one sever..
the server has old mongodb version : 2.2 and may be old perl version..
package MongoDB;
{
$MongoDB::VERSION = '0.46.3';
}
so we have 0.46.3 driver version of perl..
so do you think that this chunk out of order we are getting because of old driver version...? we need to upgrade perl driver and perl versions..


texts: I installed an Oracle OraOLEDB.Oracle provider. Now I want to remove that provider from the list. How can I do that?


texts: distributed queries are configured to run in single-threaded apartment mode

texts:
I have sql server 2008 R2 64bit Developer's Edition Installed on my machine.

And Microsoft Office 2010 Professional 32bit.

I have been trying to import some Excel data from an Excel sheet into a sql server database. I have used the following query to do this:

Query

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=D:\Files\BlueFile.xlsx;IMEX=1',
                'SELECT * FROM [Sheet1$]')

I understand sql server has disabled this feature by default as a security measure, however I executed the following statements to enable it.

Configuration

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Error Message

Yet every time I execute the select statement to get data from Excel sheet I get the following error.

  Msg 7308, Level 16, State 1, Line 1 OLE DB provider
  'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
  because the provider is configured to run in single-threaded apartment
  mode.


texts: How to run a SELECT query within while loop in PHP?

texts:
Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as

$result1 = $mysqli->query("SELECT * FROM table1");

while ( $row = $result->fetch_assoc() ) {

if ( $row['X'] == 'X' ) {
$result2 = $mysqli->query("SELECT * FROM table2");
.....
}

}

but this does not work. I cannot JOIN the tables, as the if statement is complicated to perform SELECT from different tables.


texts:
There is a reserved keyword DOUBLE in SQL Server, but I know that it's not a data type (other languages' double is float or real in SQL Server); does anyone know why this is a keyword?

I've tried to use it in SQL Server 2008 and SQL Server 2012 and I cannot use it as a data type nor a function and I cannot find any info in MSDN pages; only that it is a reserved keyword and it isn't a data type.


texts: MySQL - Turkey/Istanbul Daylight Saving Time Change

texts:
Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3.

Our DateTime's are persisted on MySQL (5.26.34) db (on AWS) was using UTC timezone.

When i try to use CONVERT_TZ(DateTime, UTC, EUROPE/ISTANBUL) for recent datetime -lets say- 2016-11-21 00:00 it tries to convert according to UTC+2, whereas it suppose to be UTC+3.

When i would tried to convert 2015-11-21 00:00 -last year- it suppose to convert according to UTC+2, since Turkey was in UTC+2 at that time.

I Found an announcement on AWS forum[1], advicing to use Moscow timezone, which is not an option for us since Turkey's timezone situation is unique and our DateTime's are saved in the form of UTC0.

https://forums.aws.amazon.com/ann.jspa?annID=4115


texts: two-table-over  functional relationship: maintaining referential integrity

texts:
I have three tables, the last two having a many to one relationship with the first, written here in SQL:

create table study (
    id bigint primary key,
    name text
);

create table treatment (
    id bigint primary key,
    name text,
    study_id bigint references study
);

create table visit (
    id bigint primary key,
    name text,
    study_id bigint references study
);

These are brought together into a session which ties a person assigned a treatment on some visit in some study like so:

create table session (
    id bigint primary key,
    person_id bigint references person,
    study_id bigint references study,
    visit_id bigint references visit,
    treatment_id bigint references treatment
);

What is the best way to make sure that for any row in the session table, the study_id, the visit_id, and the treatment_id all belong to the same study? I am working in a Postgres 9.x database. Check constraints can't refer to other tables, so maybe a trigger before insert and update?


texts: How to get the current (default) transaction isolation level in oracle?


texts:
I need to execute a dynamic query.
I need to extract some values of a table. With these a values I can construct the table names where I must do another query and check every value that it gives me with a if clause.

I'm trying to make with cursor, but it seems don't let me put a variable in the second cursor.

What is the best method to do this. Could you help me please?

DECLARE
    CURSOR c_outer IS SELECT ACTIVE FROM COMM;
    CURSOR c_inner(p_name VARCHAR2) IS SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 HOURS FROM p_name WHERE  UPPER(STATUS) = 'EXECUTING';
    HOURS NUMBER;
    TAB VARCHAR2(30);

BEGIN

    FOR r IN c_outer LOOP
       TAB :='A'||c_outer||'JOB';

        OPEN  c_inner(TAB);

        FETCH c_inner INTO HOURS;
                IF HOURS > 24 THEN
                        DBMS_OUTPUT.PUT_LINE('MAXIMMO');
                END IF;

        CLOSE c_inner;

    END LOOP;

END;


texts:
I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.)
But still there are times when saving user's data, there may be null values or empty strings.

Can you please guide me to get the effect of having NULL values or empty strings in a database, where SELECT is most used?

NOTE: Specifically for MySQL 5.5 or later.


texts: How to debug connection between ec2 and rds in aws?

texts:
In AWS, created 2 accounts A and B.

A: EC2 instance -> Installed a postgres client
B: RDS postgres cluster

These 2 accounts were set transit gateway from each VPC. So they should be connect each other.
But from A's EC2, in the instance, use psql
psql -h [Acount B's DB instance endpoint] -U postgres

Got connect error:
psql: error: could not connect to server: Connection timed out
        Is the server running on host "xxx.yyy.rds.amazonaws.com" (12.32.123.10) and accepting
        TCP/IP connections on port 5432?

From B's RDS subnet, already allowed A's instance IP.
How to debug the connection from A to B? If there is a way to find RDS endpoint's IP, maybe a little helpful. Since there isn't many methods to test except by client tool.


texts: delete WSFC 2012 cluster role/group fails

texts:
PS C:\Windows\system32> get-cluster

Name
----
sql2012poc_cl1

PS C:\Windows\system32> get-clustergroup

Name                                    OwnerNode                               State
----                                    ---------                               -----
AG1                                     srvgnpw049                              Failed
Available Storage                       srvgnpw049                              Offline
Cluster Group                           srvgnpw049                              Online

PS C:\Windows\system32> remove-clustergroup AG1
remove-clustergroup : The object has been deleted from the cluster.
At line:1 char:1
+ remove-clustergroup AG1
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (:) [Remove-ClusterGroup], ClusterCmdletException
    + FullyQualifiedErrorId : ClusterObjectNotFound,Microsoft.FailoverClusters.PowerShell.RemoveClusterGroupCommand
PS C:\Windows\system32> get-clusterresource
get-clusterresource : The object has been deleted from the cluster.
At line:1 char:1
+ get-clusterresource
+ ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (:) [Get-ClusterResource], ClusterCmdletException
    + FullyQualifiedErrorId : ClusterObjectNotFound,Microsoft.FailoverClusters.PowerShell.GetResourceCommand
PS C:\Windows\system32>

I am trying to delete Cluster Role from Windows 2012 Failover Cluster but get above error in both WFC Manager GUI and PS.
AG1 cluster role is still visible in GUI and Cluster Core Resources is empty in GUI.

Please advise how AG1 role can be removed and cluster core resources be visible again.


texts:
I am trying to add a new key value pair to a jsonb array and I am having some difficulties in doing it.
What I am trying to add is a key id with a uuid value
This is how my data looks right now:
{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

And this is the result I am looking to achieve:
{
    "id": "12c78bb5-8f81-49c0-b226-feb0164573c3",
    "type": "imageGalleryModule",
    "content": {
        "images": [{
            "id": "6a1d15ad-468b-4714-81f8-dccffdea3021",
            "title": "Mollit anim id est",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }, {
            "id": "8099c2cf-2ff1-4855-9b54-aa0e62d4bfa5",
            "title": "Officia Deserunt",
            "description": "Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. "
        }]
    }
}

So far I've only managed to add the id key with a static value using this query
select  jsonb(
                COALESCE(
                        jsonb_set(
                                imageElements,
                                '{id}',
                                jsonb '3',
                                true
                            ), imageElements
                    )
            )
from  jsonb_array_elements(
    (
        select entitiesArray->'content' -> 'images' from (
                select jsonb_array_elements(column) as entitiesArray from table
        ) as column
            where entitiesArray->> 'type' = 'imageGalleryModule'
    )
    ) as imageElements;

My question is, how can I generate a uuid for each key I am trying to add? Instead of my current static value 3 I want to add a uuid that must not be the same for all the elements in the array


texts: Using the Schema Generation Wizard to regenerate a previously-generated schema

texts:
Lately, I've been trying to clean up a group of projects that my company received from an outside contractor.  Among them is an SSAS cube, with an associated data warehouse.

The contractors generated the cube top-down, using the Schema Generation Wizard to generate the schema for the data warehouse from the cube.  Normally, based on what I've read about the SSAS tools in Visual Studio, you make changes to the data warehouse by changing the cube, and then re-running the Schema Generation Wizard to update the data warehouse.  I'd like to maintain the database using the SSAS tools so that we have one centralized place to manage both the cube and data warehouse's schema.

My goal with cleaning up these projects is to get them to run on developers' local machines.  That will require standing up a full new set of independent databases on each developer's machine.  I want to manage the data warehouse schema from the SSAS tools, so I'd like to fully regenerate the schema each time we stand up a new developer.

I'm not concerned with data or stored procedures in the data warehouse at the moment.  I'd be fine with completely deleting the data warehouse and regenerating it when needed.  Deleting the .dsv file will not be an option, however, because it contains queries against the table structure defined by the cube.

Is there any way to regenerate a previously-generated schema using the Schema Generation Wizard, without deleting the .dsv?


texts:
I have a 225GB DB with a 390GB log file, which has only recently exploded in size. I am trying to shrink it - per these instructions: https://www.brentozar.com/blitz/transaction-log-larger-than-data-file/

I am not able to Shrink it on SSMS or by the DBCC SHRINKFILE command. I tried waiting until it was not in use, and also tried a Log backup then running the DBCC SHRINKFILE again, but to no avail. Any ideas on what I can do?

I saw in this article (https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/) that it says I could try flipping the recovery model over to Simple, run the shrink, then flip it back to Full, but that sounds risky to me so I am cautious to try that.

Also worth noting is that my database is Mirroring and currently stuck in a Suspended state.

Wondering if you guys could lend any advice. We are your typical DBA-less shop, so I appreciate any novice-conscience pointers or suggestions! Thanks!


texts: Warning in query plan "Cardinality Estimate"

texts:
create table T(ID int identity primary key)
insert into T default values
insert into T default values

go

select cast(ID as varchar(10)) as ID
from T
where ID = 1

The query above has a warning in the query plan.

<Warnings>
  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[xx].[dbo].[T].[ID],0)" />
</Warnings>

Why does it have the warning?

How could a cast in the field list affect the cardinality estimate?


texts: How can I alter the type of a money-column to decimal in PostgreSQL?

texts:
I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead.

How can I do it?

I tried with:

alter table my_table alter column vat type decimal;

But I get this error:

ERROR:  column "vat" cannot be cast to type numeric

Any suggestions?


texts: Converting SQL Server queries to PostgreSQL

texts:
I am trying to convert the following SQL Server T-SQL block to a PostgreSQL compatible one using, preferably CTE or a function. The issue with converting is I am not able to figure out how I return the last inserted id (scope_identity() in SQL Server returns the last inserted id in the identity column).
 IF (
        NOT EXISTS (
            SELECT p_id
            FROM qr_request
            WHERE q_ota = 'Done'
                            )
        )
BEGIN
    INSERT INTO qr_request (
        u_id
        ,c_id
        ,r_id
        ,q_ota
        )
    VALUES (
        1
        ,2
        ,3
        ,'P'
        )

    COMMIT

    SELECT Scope_Identity() AS id
        ,p.email AS n
    FROM person p
    WHERE p.uid = 33
        AND deleted = 0

END
ELSE
    SELECT NULL AS id
        ,p.email AS n
    FROM person p
    WHERE p.uid = 33
        AND deleted = 0

Any help would be appreciated.


texts:
I have two tables: orders and order_lines. I need a fast, inexpensive way to display a count for orders that are going to be shipping today.
The order_lines table is associated with the orders table view a foreign key based on order id. A line is shippable if the shipped field > 0.
An order is "shippable" if any of its lines have a qty of shipped.

Right now I basically have a query that reads through the orders, then through the lines (via sqlalchemy in python), and then if any of the lines can be shipped a counter is incremented. This works but it is very slow.

Is there some way on the database side or can create something to speed this up?


texts: When range partitioning, should I drop primary UUID-like key?

texts:
I'm aware of the limitation "every unique key on the table must use every column in the table's partitioning expression". So given that my primary key is a UUID, it's going to require  a change.

Background: I have a transaction history table that grows at about 1.5G per month (more so than I can keep upgrading server memory). The table has a PRIMARY key (uuid_short) and a datetime column, among others. There are two types of queries hitting the table: those that return a list of transactions in a date-range, and those that refer to a specific transaction through its primary key.

Question: Since vast majority of queries don't care for transactions more than 18 months old I thought datetime could be a candidate for range partitioning. Does this mean I'm better off dropping the PRIMARY key and converting it to a non-unique key? (dups are not a problem, the app servers guarantee there cannot be any).

The system is currently a hybrid of OLTP and OLAP scenarios which is probably why it doesn't scale.

Is partitioning going to make a positive impact on memory footprint?

If not, then I may be better off reorganizing the system by moving historical transactions to a data warehouse like AWS Redshift. It would be quite a refactoring effort, but if that's the preferred solution then I'd like to plan early.

This is MySQL 5.6.


texts: Table Partitioning - database file increased after partitioning

texts:
I have a table that is 100 GB in size. I forgot the exact blog which I read when partitioning tables but when I ran the partition query, my primary table file increased while being able to partition it into a new drive.
The steps I took were:

Create file group

ALTER DATABASE MY_DB
ADD FILEGROUP MY_FILEGROUP
GO

Create secondary file for the filegroup

ALTER DATABASE MY_DB
ADD FILE
(
NAME = my_partition,
FILENAME = 'D:\my_partition.ndf',
size = 3072 KB
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
TO FILEGROUP MY_FILEGROUP

Create partition function

CREATE PARTITION FUNCTION myPartitionFunc (date)
AS RANGE RIGHT FOR VALUES ('20200101')

Create partition scheme

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myPartitionFunc
TO (PRIMARY, my_partition)

Partition the table

ALTER TABLE my_schema.my_table DROP CONSTRAINT PK_MY_TABLE
ALTER TABLE my_schema.my_table ADD CONSTRAINT PK_MY_TABLE PRIMARY KEY CLUSTERED
(
DATE ASC,
ID ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON myPartitionSceme(DATE)

After step 5, I was able to partition my database to my two separate drives. My only problem was my primary database file increased after partition.
IE
My initial file is C:\my_initial_file.mdf is 100 GB and will be partitioned to D:\my_partition.ndf
Once partitioned, my D:\my_partition.ndf is 10 GB and my C:\my_initial_file.mdf is 94 GB which still consumed an excess of 4 GB.
My reason for partitioning is because my drive C is getting full and would like to keep the server running until we migrate to elsewhere.


texts: Create table group sub-folders in Management Studio

texts:
When I was exploring the master database in SSMS I noticed that under the Tables folder there is another folder called Systems Tables that houses a bunch of tables. Is it possible for us to create a structure akin to Systems Tables within our database?

I am looking to organize tables and stored procedures into project specific folders. Under the new setup - when I am referring to my table object I would have to use the following syntax (I am guessing here):

[dbName].[projectName].[dbo].[tableName]

Also, apart from clearing up the clutter, do anybody foresee any performance improvement/degradation because of this re-organization? I use Microsoft SQL Server 2008 R2.


texts: Using a PostgreSQL tablespace from two different servers

texts:
I have a PostgreSQL database living on an external hard disk (RAID0 via Thunderbolt). So far I accessed it from a PostgreSQL server running on my MacBook Pro. As my work on this database is getting more and more intensive the queries are getting more and more complex, too. Therefore I'd like to use my brand spanking new iMac from now on.

My question is: Can I somehow tell the new PostgreSQL server (running on the iMac) to use the data that is already living in the tablespace on the external disk? Or will I have to export & import all the data manually (pg_dump, pg_restore)?


texts: Where can I get error messages from SQL Server Auditing?

texts:
Where can I get information about the audit process failing to start?

I'm setting up auditing on my MS SQL 2014 server.  I've created an AUDIT and an associated AUDIT SPECIFICATION, but when I try to enable the audit, I get this message:

Msg 33222, Level 16, State 1, Line 13
Audit 'X' failed to start. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

Checking the error log, I see:

Audit: Server Audit: 65552, Initialized and Assigned State: START_FAILED

That's about as vague as one can get.  The 65552 number is just an auto-incrementing value, not an error code.

OK, how about that dm_os_ring_buffers table?  When filtered for the ring_buffer_type mentioned in the original error, I get nothing.  Well, there are a couple hundred messages, but none are new and there's no change after each attempt to start auditing.  Without a filter, I get an XML message, with these salient points:

Record type: RING_BUFFER_EXCEPTION
Task address: 0x000000189D6244E8
Error: 33222
Severity: 16
State: 1
UserDefined: 0
Origin: 0

According to sysmessages, this error code means:

Audit '%.*ls' failed to %.*ls. For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

There's a joke in here somewhere about ring buffers and going in circles.

It's possible that the service account doesn't have privs to write to the system event log, but I've also tried setting up my audit to use the application log or a folder, and there's been no change.  Also, according to another question, if that was the case it should be mentioned in the error log.

The file path I selected is a subdirectory of the database's log folder, and the service has full rights by inheritance, so it should have no problem reading or writing.

Here's the script for the AUDIT alone; the error occurs when trying to activate the audit.

CREATE SERVER AUDIT X
    TO FILE (FILEPATH = 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\', MAXSIZE = UNLIMITED)
    WITH    (ON_FAILURE = CONTINUE)
GO
ALTER SERVER AUDIT X WITH (STATE = ON)


texts: Wildcard search using parameters in function with dynamic SQL

texts:
What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL?

As a starting point, here is an example from Erwin Brandstetter answering a different question on stackoverflow:

https://stackoverflow.com/a/12047277/538962

CREATE OR REPLACE FUNCTION report_get_countries_new (starts_with text
                                                   , ends_with   text = NULL)
RETURNS SETOF lookups.countries AS
$func$
DECLARE
   sql text := 'SELECT * FROM lookups.countries WHERE country_name >= $1';
BEGIN
   IF ends_with IS NOT NULL THEN
      sql := sql || ' AND country_name <= $2';
   END IF;

   RETURN QUERY EXECUTE sql
   USING starts_with, ends_with;
END
$func$ LANGUAGE plpgsql;

Let's suppose for country_name you wanted to do a leading and trailing wildcard search.

E.g., without using a parameter, AND country_name LIKE '%ic%'.

What is the best way to implement the wildcard search be in this scenario with respect to negating SQL injection risk?


texts: MySQL - INSERT ... SELECT .. FROM .. - Single transaction or transaction per row?

texts:
Question:

I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup).
I am using the following syntax to do that:

INSERT `new_table` (`new_column`,`more_columns`)
SELECT `old_column`,`more_old_columns` FROM `old_table`;

I am wondering if MySQL/InnoDB will perform this as one giant transaction, or will MySQL/InnoDB create a transaction per row it encounters from the select? (I would basically would like to know if it will perform a COMMIT on each row).


texts: How to detect level in nested MySQL table?

texts:
I have simple child-parent relation by saving parent_id. Is it possible to count which level each entry?

My DB looks like this:

As You can see from the image, for example the first item relation is:

Aston Martin

Vanquish

10

V8...
DB9
Rapide

When I selecting Aston Martin I need to know that it is first record, when Vanquish is the second, and so on.


texts: I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the MySQL ODBC driver, and it correctly accesses both databases, but the xml files containing the specifications for type conversion did not exist and the specifications were too limited for me to correctly create them. Does anyone know either how to create the type conversion files or where to get a better tool for transferring this data?