Parsing a section of a pipe-delimited string I have a pipe-delimited string that ends with a |. I need the information between the second last | and the last |.

Example:

a|b|c|d|e|f|g|h|ijk|

I need to return ijk. The string will always have 9 pipes if that helps.

Call me insane, but this is what I came up with:

CREATE TABLE #SomeTable (
    id INT,
    content VARCHAR(MAX))

INSERT INTO #SomeTable VALUES
    (1, 'a|b|c|d|e|f|Aloha|'),
    (2, 'a|b|c|d|e|f|From|'),
    (3, 'a|b|c|d|e|f|Hawaii|')

SELECT
    REVERSE(SUBSTRING(REVERSE(LEFT(content, LEN(content)-1)), 0,CHARINDEX('|',REVERSE(left(content, LEN(content)-1)),0)))
FROM #SomeTable

Explanation (from the inside out):

Strip the last pipe (|)
Reverse the whole string
Find the next pipe (ex last)
Extract the characters up until the next pipe.
Reverse the string again.

Here's another take:

SELECT
    REPLACE(REVERSE(SUBSTRING(REVERSE(content), 0, CHARINDEX('|',REVERSE(content),2))),'|','')
FROM #SomeTable

Reverse the string.
Get the position of the next pipe (start looking from the second position to skip the first (ex last) pipe.
Reverse the string.
Replace the last pipe.


Where Clause - Two columns of Primary table mapped to single column in Sec. table I am facing a tricky scenario.
We have two tables

Table A

Name            Company
--------------------------
Honda           Accord
Honda           Pilot
Chevy           Impala
Chevy           Corvette
Chevy           Tahoe

Table B

Name            Quantity
--------------------------
Honda_Accord    20
Toyota_Corolla  10
Chevy_Tahoe     15

I want to fetch quantity from Table B based on Table A.
How do I achieve this?

You can join the rows using the following (as suggested by Akina):

WHERE TableA.Name || '_' || TableA.Company = TableB.Name


SSMSBoost intellisense not working? SSMS Version 17.9.1
It looks like the intellisense may not be working? Shouldn't it auto complete Select etc? Also, it looks like the intellisense is from SSMS itself (doesn't work that great)

I've checked the Intellisense setting and it's there just fine, also put the cache size on unlimited

Please, try to reset Intellisense Cache:
CTRL+SHFT+R

But Intellisense completes object names not sql statement like SELECT.


Question About Large InnoDB Table with Deleted Rows Our server has a DB that's over 600 GB - we are deleting older rows that are non-essential to contain the growth. We can't optimize the table due to possible downtown on the table but we are interested to know if queries will be faster with smaller data in the table despite the fact that the table size will not shrink at all.

So basically if the size remains the same, but we are able to delete a significant amount of rows, will our more intensive queries be any faster?

thanks!

If you are worried about table locks caused by DDL operations, like ALTER TABLE... ENGINE=InnoDB (what optimize does for InnodB) you must know that in 5.6, that process can be done fully online, and alternatively, and for lower versions, you can use an online alter table tool like pt-online-schema-change. With proper care, the process can be done fully online and with minimal load increase (although you will need double the table size). Make sure also you are using innodb_file_per_table.

Assuming the table has been optimized and all rows purged, yes, you can obtain an improvement due to the several reasons:

Assuming you do not have 600GB of RAM, a larger part of your InnoDB pages will fit into the buffer pool, reducing IO requests. You can check that before and after by monitoring your physical IO or checking InnoDB buffer pool hit statistics, such as innodb_buffer_pool_read_requests and innodb_buffer_pool_reads
Having more free space on the buffer pool can also smooth the commit writing process by allowing more dirty pages on memory (this depends highly on the load)
If your queries require full table scans, reducing the total number of rows will make those operations faster
Index scans should also be faster, specially Index accesses and large ranges. Also InnoDB takes a huge impact when the hot parts of the indexes do not fit in memory (its trees are designed to be cached)
Temporary tables, sorts, etc. created could potentially be smaller, reducing the latency of its execution

So the actual improvement may depend a lot on the actual queries executed, usage or not of hashed searches, memory state, penalty of random IO reads, etc. But in general, working with smaller data sets is easier.

Beware also of the purge process, which can bite your performance if not done well.

Some of this advantages could be lower if there is a lot of fragmentation, but that depends a lot on the actual state of the table. You can see some thoughts about optimize on InnoDB here (more on comments):

How often should you use OPTIMIZE TABLE? http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
How often should you use OPTIMIZE TABLE? Followup http://code.openark.org/blog/mysql/how-often-should-you-use-optimize-table-followup
Visualizing the impact of ordered vs. random index insertion in InnoDB: http://blog.jcole.us/2014/10/02/visualizing-the-impact-of-ordered-vs-random-index-insertion-in-innodb/


What could be the possible reasons for cardinality estimates being very inaccurate in an execution plan? I have a query that is selecting some data out of a table. The execution plan shows that an INDEX (FAST FULL SCAN) is used and the cardinality is 22.

On the running the query it gives 227,652 rows. I understand that query optimiser relies on row count estimates instead of actually counting rows.

So I run a select table_name, num_rows from dba_tables where table_name = 'reservations';

And it gives 910,087. So why is the cardinality estimate in query plan off by such a large margin?

Query as request in comments:

SELECT COUNT(*) FROM reservations WHERE length(office_number=2) and length(regexp_substr(text, '\d*')) = 3 and indicator_flag is null;

create index res_idx on reservations (office_number, text, indicator_flag);

Giving a complete overview of statistics and how they work feels a bit out of scope for a question here. You can find a ton of information about this by searching with your favorite search engine. This white paper is the first result for me.

However, I can say a little bit about your query, reproduced here:

SELECT COUNT(*)
FROM reservations
WHERE length(office_number)=2
and length(regexp_substr(text, '\d*')) = 3
and indicator_flag is null;

When trying to figure out how Oracle arrived at a cardinality estimate I often find it helpful to break the query into smaller pieces and to get cardinality estimates on those smaller pieces. Consider this piece:

SELECT COUNT(*)
FROM reservations
WHERE indicator_flag is null;

Statistics (if they exist) for the indicator_flag column store how many NULL values are in that column. So Oracle can use the statistics object directly to derive an estimate. This estimate can be off if the statistics object isn't present, if statistics are outdated, or if statistics were gathered with the wrong sample rate. For your query I'm going to guess that around 24% of the rows in the table have a NULL value for indicator_flag, so the estimate here should be around 220k rows.

Consider this piece:

SELECT COUNT(*)
FROM reservations
WHERE length(office_number)=2;

Here a function is applied to the column. That can make things more complicated because Oracle won't store statistics on the length of the office_number column directly. In this situation RDBMs need to make a guess. On the version of Oracle that I'm using the guess appears to be pretty simple: 1% of the rows of the table for integer values of length. Comparisons to NULL or NOT NULL use the statistics on the column. This is very likely to be inaccurate because Oracle is making a guess that isn't based on your data. The estimate as calculated by Oracle should be around 9100 rows.

Consider this piece:

SELECT COUNT(*)
FROM reservations
WHERE
length(regexp_substr(text, '\d*')) = 3;

The analysis above applies to this part as well, so the estimate as calculated by Oracle should be around 9100 rows.

How does Oracle combine the estimates to get a cardinality estimate for the full query? In my experience it tends to assume the predicates are independent unless it has better information. So your final estimate should be 910087 * 0.01 * 0.01 * 0.24 = 22 rows.

If you need the estimate to be better to improve the performance of queries you have a few options depending on where the issue is. You can create virtual columns based on the length, you can create multi column statistics to make the relationship between columns more obvious to Oracle, you can add a dynamic sampling hint to the query, and so on.


Postgres index for aggregate functions Query:

SELECT
  "places_place"."id"
FROM "places_place"
  LEFT OUTER JOIN "units_unit" ON ("places_place"."id" = "units_unit"."place_id")
GROUP BY "places_place"."id"
HAVING SUM("units_unit"."quantity") >= 123

Index attempt:

CREATE INDEX units_quantity_sum ON units_unit (SUM("units_unit"."quantity"));
-- ERROR:  aggregate functions are not allowed in index expressions

Essentially, I want it to index the result of SUM without storing the result in a separate column of the table. How can I create an index to do this (or is there a better way to optimize this query)?

EXPLAIN ANALYZE of query with 10,000 rows in places_place and 25,000 in units_unit:

HashAggregate  (cost=2057.31..2157.33 rows=10002 width=4) (actual time=38.121..41.174 rows=7727 loops=1)
  Group Key: places_place.id
  Filter: (sum(units_unit.quantity) >= 5)
  Rows Removed by Filter: 2275
  ->  Hash Right Join  (cost=594.04..1932.22 rows=25018 width=6) (actual time=6.383..28.578 rows=26727 loops=1)
        Hash Cond: (units_unit.place_id = places_place.id)
        ->  Seq Scan on units_unit  (cost=0.00..994.18 rows=25018 width=6) (actual time=0.003..7.279 rows=25018 loops=1)
        ->  Hash  (cost=469.02..469.02 rows=10002 width=4) (actual time=6.311..6.311 rows=10002 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 480kB
              ->  Seq Scan on places_place  (cost=0.00..469.02 rows=10002 width=4) (actual time=0.007..3.560 rows=10002 loops=1)
Planning time: 0.584 ms
Execution time: 42.643 ms

You have two easy options

You can use a MATERIALIZED VIEW
You can also use a TRIGGER that inserts another table.

Both of these will allow you to cache the SUM(). I would go with the MATERIALIZED VIEW unless you need up to date changes all the time.

Before you go down that route though PostgreSQL 9.6 enables parallel seq scans and aggregation which will increase performance. In fact, it's an ideal use case. If you just need this to be faster try setting max_parallel_workers_per_gather


SQL Query with multiple conditions on date I want to write a query with condition on dates for below table and sometimes it requires great thinking.

StartDtm                             StopDtm
2013-11-03 00:00:00.000               NULL
2013-11-05 08:00:00.000               NULL
2013-11-18 09:00:00.000               NULL
2013-11-18 08:00:00.000               NULL
2013-11-19 08:00:00.000              2013-11-26 07:59:00.000
2013-11-20 08:00:00.000              2013-11-27 07:59:00.000
2013-11-19 08:00:00.000              2013-12-19 07:59:00.000

The table shows the task start date and task end date. Start date can never be null.
Last record shows that the task starts at 19th Nov. and will end at 19th Dec. So if I pass any dates, it should show all the tasks which need to be performed on any date between passed date range.

Conditions are as below:

1.Any date or both date parameters may be null. If so all data should come.

2.If @StartDtm is null and @StopDtm is passed, then it should bring all data based on StopDtm field only but it should not bring those data whose StartDtm is greater than @StopDtm. Same for @StopDtm parameter

3.If StopDtm field is null, It should bring all data based on StartDtm field only

I tried few ways but not succeeded. I to keep condition as short as possible. If possible, then please provide multiple queries with little explanation.

My query is as below:

DECLARE @StartDtm datetime='2013-11-15'
DECLARE @StopDtm datetime='2013-11-21'

select * from #TempTbl
where  ((StartDtm<=@StartDtm OR @StartDtm IS NULL)  AND (StopDtm>=@StopDtm OR StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm>=@StopDtm AND StartDtm<= @StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm<=@StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))

Thanks in advance..
The question is already asked in StackOverflow but I cannot find how to migrate it here. So asked here again.

Created another query which seems working fine:

DECLARE @StartDtm datetime='2013-11-20'
DECLARE @StopDtm datetime='2013-11-30'

IF(@StartDtm is null)
begin
    SET @StartDtm='1900-01-01'
end
IF(@StopDtm IS NULL)
begin
    SET @StopDtm='9999-12-31'
end

select * from #TempTbl
where (startDtm<= @StartDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm )
  OR(StartDtm>=@StartDtm and ISNULL(StopDtm,@StopDtm)<=@StopDtm )
  OR(StartDtm between @StartDtm AND @StopDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm  )
  OR(StartDtm<=@StartDtm AND ISNULL(StopDtm,@StopDtm) between @StartDtm AND  @StopDtm)

Suggest if any logical error/change/improvement.

You can use isnull to take care of the parameters when they are null.

Something like this should do what you want.

select T.*
from #TempTbl as T
where T.StartDtm <= isnull(@StopDtm, '99991231') and
      (T.StopDtm >= isnull(@StartDtm, '17530101') or T.StopDtm is null)


Error 9002 Transaction log full SUSDB I am running a WSUS Server on Windows Server 2016. The WSUS Service stops because transition log is full.

The Event viewer says:

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.

When i start SQL Management Studio and connect to the database i tried to follow the technet article for Troubleshooting a Full Transaction Log. It says that i could Back up the log.
So i select the database SUSDB and try to run a backup by pointing to Tasks and klicking on Back Up.... But as Backup Type i can't select Transition Log, because the Recovery Model is SIMPLE.

Then i tried to change the Recovery Model to Full in Database Properties >> Options. But when i try to do that, it says

Alter failed for Database 'SUSDB'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.
The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.
ALTER DATABASE statement failed.
Could not write a checkpoint record in database SUSDB because the log is out of space.

Also in Database Properties >> Files the SUSDB_log has a Limit to 2.097.152 MB but the actual size of the log file is 220 MB on my disk. The disk free space is 230 GB.

After that i tried to run the query:

ALTER DATABASE susdb SET RECOVERY FULL;

But this also leaded to the error

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.

I'm not able to restart the WSUS Service becaus of this error. Has anyone any suggestions what to do?

First of all you should find out why your log cannot be truncated:

select log_reuse_wait_desc
from sys.databases
where name = 'SUSDB';

It's certainly NOT log backup that it's waiting for, because recovery model of your db is simple.

You should also check free space on the disk where the log file is living, and if someone limited the growth of your log file:

use susdb;
select physical_name,
       size * 8 / 1024 as size_in_Kb,
       max_size * 8 / 1024 as max_size_in_Kb,
       growth,
       is_percent_growth
from sys.database_files
where file_id = 2;

exec xp_fixeddrives;

For now, please update your question with the results of 3 queries above.


CROSS APPLY produces outer join In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A
            ) AS ca;

The query uses CROSS APPLY (not OUTER APPLY) so why is there an outer join in the execution plan instead of an inner join?

Also why does uncommenting the group by clause result in an inner join?

I don't think the data is important but copying from that given by kevinwhat on the other question:

create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)

Summary

SQL Server uses the correct join (inner or outer) and adds projections where necessary to honour all the semantics of the original query when performing internal translations between apply and join.

The differences in the plans can all be explained by the different semantics of aggregates with and without a group by clause in SQL Server.

Details

Join vs Apply

We will need to be able to distinguish between an apply and a join:

Apply

The inner (lower) input of the apply is run for each row of the outer (upper) input, with one or more inner side parameter values provided by the current outer row. The overall result of the apply is the combination (union all) of all the rows produced by the parameterized inner side executions. The presence of parameters means apply is sometimes referred to as a correlated join.

An apply is always implemented in execution plans by the Nested Loops operator. The operator will have an Outer References property rather than join predicates. The outer references are the parameters passed from the outer side to the inner side on each iteration of the loop.
Join

A join evaluates its join predicate at the join operator. The join may generally be implemented by Hash Match, Merge, or Nested Loops operators in SQL Server.

When Nested Loops is chosen, it can be distinguished from an apply by the lack of Outer References (and usually the presence of a join predicate). The inner input of a join never references values from the outer input - the inner side is still executed once for each outer row, but inner side executions do not depend on any values from the current outer row.

For more details see my post Apply versus Nested Loops Join.

  ...why is there an outer join in the execution plan instead of an inner join?

The outer join arises when the optimizer transforms an apply to a join (using a rule called ApplyHandler) to see if it can find a cheaper join-based plan. The join is required to be an outer join for correctness when the apply contains a scalar aggregate. An inner join would not be guaranteed to produce the same results as the original apply as we will see.

Scalar and Vector Aggregates

An aggregate without a corresponding GROUP BY clause is a scalar aggregate.
An aggregate with a corresponding GROUP BY clause is a vector aggregate.

In SQL Server, a scalar aggregate will always produce a row, even if it is given no rows to aggregate. For example, the scalar COUNT aggregate of no rows is zero. A vector COUNT aggregate of no rows is the empty set (no rows at all).

The following toy queries illustrate the difference. You can also read more about scalar and vector aggregates in my article Fun with Scalar and Vector Aggregates.

-- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;

-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();

db<>fiddle demo

Transforming apply to join

I mentioned before that the join is required to be an outer join for correctness when the original apply contains a scalar aggregate. To show why this is the case in detail, I will use a simplified example of the question query:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;

The correct result for column c is zero, because the COUNT_BIG is a scalar aggregate. When translating this apply query to join form, SQL Server generates an internal alternative that would look similar to the following if it were expressed in T-SQL:

SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
    SELECT B.A, c = COUNT_BIG(*)
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

To rewrite the apply as an uncorrelated join, we have to introduce a GROUP BY in the derived table (otherwise there could be no A column to join on). The join has to be an outer join so each row from table @A continues to produce a row in the output. The left join will produce a NULL for column c when the join predicate does not evaluate to true. That NULL needs to be translated to zero by COALESCE to complete a correct transformation from apply.

The demo below shows how both outer join and COALESCE are required to produce the same results using join as the original apply query:

db<>fiddle demo

With the GROUP BY

  ...why does uncommenting the group by clause result in an inner join?

Continuing the simplified example, but adding a GROUP BY:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

-- Original
SELECT * FROM @A AS A
CROSS APPLY
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;

The COUNT_BIG is now a vector aggregate, so the correct result for an empty input set is no longer zero, it is no row at all. In other words, running the statements above produces no output.

These semantics are much easier to honour when translating from apply to join,  since CROSS APPLY naturally rejects any outer row that generates no inner side rows. We can therefore safely use an inner join now, with no extra expression projection:

-- Rewrite
SELECT A.*, J1.c
FROM @A AS A
JOIN
(
    SELECT B.A, c = COUNT_BIG(*)
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

The demo below shows that the inner join rewrite produces the same results as the original apply with vector aggregate:

db<>fiddle demo

The optimizer happens to choose a merge inner join with the small table because it finds a cheap join plan quickly (good enough plan found). The cost based optimizer may go on to rewrite the join back to an apply - perhaps finding a cheaper apply plan, as it will here if a loop join or forceseek hint is used - but it is not worth the effort in this case.

Notes

The simplified examples use different tables with different contents to show the semantic differences more clearly.

One could argue that the optimizer ought to be able to reason about a self-join not being capable generating any mismatched (non-joining) rows, but it does not contain that logic today. Accessing the same table multiple times in a query is not guaranteed to produce the same results in general anyway, depending on isolation level and concurrent activity.

The optimizer worries about these semantics and edge cases so you don't have to.

Bonus: Inner Apply Plan

SQL Server can produce an inner apply plan (not an inner join plan!) for the example query, it just chooses not to for cost reasons. The cost of the outer join plan shown in the question is 0.02898 units on my laptop's SQL Server 2017 instance.

You can force an apply (correlated join) plan using undocumented and unsupported trace flag 9114 (which disables ApplyHandler etc.) just for illustration:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY
(
    SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
    FROM   #MyTable AS mt2
    WHERE  mt2.Col_A = mt.Col_A
    --GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);

This produces an apply nested loops plan with a lazy index spool. The total estimated cost is 0.0463983 (higher than the selected plan):

Note that the execution plan using apply nested loops produces correct results using "inner join" semantics regardless of the presence of the GROUP BY clause.

In the real world, we would typically have an index to support a seek on the inner side of the apply to encourage SQL Server to choose this option naturally, for example:

CREATE INDEX i ON #MyTable (Col_A, Col_B);

db<>fiddle demo


Update a json element in json datatype I can't figure how can I update an element in a PostgreSQL 9.3 datatype.

My example:

CREATE TABLE "user"
(
  id uuid NOT NULL,
  password character varying(255),
  profiles json,
  gender integer NOT NULL DEFAULT 0,
  created timestamp with time zone,
  connected timestamp with time zone,
  modified timestamp with time zone,
  active integer NOT NULL DEFAULT 1,
  settings json,
  seo character varying(255) NOT NULL,
  CONSTRAINT id_1 PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE "user"
  OWNER TO postgres;

The json part in "profiles"

{
    "Facebook": {
        "identifier": "xxxxxxxxxxx",
        "profileURL": "none",
        "webSiteURL": "none",
        "photoURL": "none",
        "displayName": "test2 test2",
        "description": "none",
        "firstName": "test2",
        "lastName": "test2",
        "gender": 2,
        "language": "none",
        "age": "none",
        "birthDay": "none",
        "birthMonth": "none",
        "birthYear": "none",
        "email": "test@test.com",
        "emailVerified": "none",
        "Added": null,
        "phone": "none",
        "address": "none",
        "country": "none",
        "region": "none",
        "city": "none",
        "zip": "none"
    },
    "Google": {
        "identifier": "xxxxxxxxxxxxxxxxxxxxxx",
        "profileURL": "none",
        "webSiteURL": "none",
        "photoURL": "none",
        "displayName": "test2 test2",
        "description": "none",
        "firstName": "test2",
        "lastName": "test2",
        "gender": 2,
        "language": "none",
        "age": "none",
        "birthDay": "none",
        "birthMonth": "none",
        "birthYear": "none",
        "email": "test@test.com",
        "emailVerified": "none",
        "Added": null,
        "phone": "none",
        "address": "none",
        "country": "none",
        "region": "none",
        "city": "none",
        "zip": "none"
    }
}

I'm using x-edit for the frontend, and I was hoping that something like this would work, but it doesn't:

UPDATE public.user
SET "profiles"->'Facebook'->'social'->'facebook' = 'test' WHERE` id='id'

I can't seem to find any information on how to update a json datatype.

Since it's just a string, you might be able to accomplish a simple change/deletion of a node with the regex_replace function.

For example, this is how I recently deleted a certain JSON node in a table (all rows):

UPDATE my_db.my_table
SET my_column = (regexp_replace(my_column::text, ',"some_json_node":(.*),', ','))::json
WHERE NOT my_column IS NULL

Note, for all my JSON data, I keep a "version":"(n).(n)" (i.e. schema version) node in the object. That way I can update objects that comply with a specific version. Your requirements may not be that complex, but if they are, it certainly helps.


How do I develop this data model without a circular reference? I am trying to develop a concept for a new call tracking system for a help desk.

The top 3 levels reference each other.  I am trying to determine a better way to link these tables, and am drawing a blank.

The three levels are Call, Issue and ActionTaken.  Each call can have multiple issues and each issue can have multiple actions taken.  When looking up the call afterwards it needs to only show the actions taken on that call.

For example:

Call - 1, Issue 1 - Outlook not working, ActionTaken - Reset password
          Issue 2 - Ipad keeps crashing, ActionTaken - Restored ipad
Call - 2, Issue 1 - Outlook not working, ActionTaken - Outlook profile reset

We need to be able to look up call 1 and see the two issues and the actions taken during that call, but we also need to be able to look up issue 1 and see the two calls and the corresponding actions taken.

How would I accomplish this?


Percent decrease in value each month with SQL I'm trying to make a field in MS SQL that will serve as a target field, and I would like for it to decrease automatically each month. For instance, starting in April with a target 99.4%, May would decrease to 98.8 and June would be 98.3? Is there an easy way yo do this with SQL that I'm not aware of? I've gone through and manually entered them in with a CASE STATEMENT but I would like for them to not be hard coded.


How can I create a role to create,update,delete stored procedures and views what permissions do I need to give in order to achieve the above task to get users the capacity to create,update,delete stored procedures and views to a database role.

The easiest way is to add a user to the db_ddladmin role.

-- Older method
EXEC sp_addrolemember 'db_ddladmin','username';
-- New method
ALTER ROLE db_ddladmin ADD MEMBER username;

This will give the user the ability to create/modify/drop any object in any schema.  Unfortunately that will include objects you probably don't want them to change (tables for example).

A more precise method is to use the CREATE permissions.  There is a small trick to using them however.  You will also need to grant ALTER on the schema where they can create/alter the objects.  So to grant the ability to create procedures and views in the dbo schema you might do this.

GRANT ALTER ON SCHEMA::[dbo] TO [UserName];
GRANT CREATE PROCEDURE TO [UserName];
GRANT CREATE VIEW TO [UserName];

Same with CREATE TABLE, CREATE FUNCTION etc.  The reason you need both is this: CREATE PROCEDURE gives you the ability to create the procedure, ALTER schema gives you a place to put the procedure as you create it.

I should also point out that the CREATE permissions also grant the ability to alter, and drop.


Percona Node fails to join: Operation not permitted Joining a new Percona node fails with:

130521  7:15:44 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (7ff38373-c1d1-11e2-0800-d595d1800e6a): 1 (Operation not permitted)
     at galera/src/replicator_str.cpp:prepare_for_IST():436. IST will be unavailable.
130521  7:15:44 [Note] WSREP: Node 0 (db-02.arqwifi) requested state transfer from '*any*'. Selected 1 (db-01.arqwifi)(SYNCED) as donor.
130521  7:15:44 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 7101)
130521  7:15:44 [Note] WSREP: Requesting state transfer: success, donor: 1

Full error log:

Host /var/log/mysqld.log, https://gist.github.com/anonymous/1128435a85351a75574e
Host /var/lib/mysql/innobackup.backup.log, https://gist.github.com/anonymous/218f9ff1882bb0f7848a
Joiner /var/log/mysqld.log, https://gist.github.com/anonymous/92b5d29129b6ae72b3cd

Just in case, output of

ls -la /var/mysql/
ls -la /var/log/mysql/
ls -la /var/lib/mysql/
ls -la /var/run/mysqld/
ls -la /etc/my.cnf

Host, https://gist.github.com/anonymous/6bb685ce492a02ead7d4
Joiner, https://gist.github.com/anonymous/2dbf0a13121fd601940c

SELINUX=disabled is on both servers.

Host

[root@db-01 gajus]# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--character_set_server=utf8 --collation_server=utf8_unicode_ci --skip-name-resolve --innodb_file_per_table --log_slave_updates --server_id=155150 --wsrep_provider=/usr/lib64/libgalera_smm.so --wsrep_node_address=10.128.155.150 --wsrep_cluster_address=gcomm:// --wsrep_sst_method=xtrabackup --wsrep_cluster_name=live_cluster --wsrep_sst_auth=percona:[removed] --wsrep_slave_threads=1 --log_bin=/var/log/mysql/mysql-bin.log --binlog_format=ROW --default_storage_engine=InnoDB --innodb_locks_unsafe_for_binlog=1 --innodb_autoinc_lock_mode=2 --innodb_buffer_pool_size=4G --innodb_log_file_size=1G --query_cache_type=0 --query_cache_size=0 --join_buffer_size=4M --max_connections=1000 --table_cache=2000 --table_definition_cache=2000 --key_buffer_size=50M --sort_buffer_size=10M --read_rnd_buffer_size=1M --read_buffer_size=4M --tmp_table_size=500M --max_heap_table_size=1G --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --user=mysql --symbolic_links=0 --default_time_zone=+00:00 --max_allowed_packet=128MB

Joiner

[root@db-02 gajus]# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--character_set_server=utf8 --collation_server=utf8_unicode_ci --skip-name-resolve --innodb_file_per_table --log_slave_updates --server_id=155151 --wsrep_provider=/usr/lib64/libgalera_smm.so --wsrep_node_address=10.128.155.151 --wsrep_cluster_address=gcomm://10.128.155.150 --wsrep_sst_method=xtrabackup --wsrep_cluster_name=live_cluster --wsrep_sst_auth=percona:[removed] --wsrep_slave_threads=1 --log_bin=/var/log/mysql/mysql-bin.log --binlog_format=ROW --default_storage_engine=InnoDB --innodb_locks_unsafe_for_binlog=1 --innodb_autoinc_lock_mode=2 --innodb_buffer_pool_size=4G --innodb_log_file_size=1G --query_cache_type=0 --query_cache_size=0 --join_buffer_size=4M --max_connections=1000 --table_cache=2000 --table_definition_cache=2000 --key_buffer_size=50M --sort_buffer_size=10M --read_rnd_buffer_size=1M --read_buffer_size=4M --tmp_table_size=500M --max_heap_table_size=1G --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --user=mysql --symbolic_links=0 --default_time_zone=+00:00 --max_allowed_packet=128MB

It turned out to be a bug in percona-xtrabackup-2.1.2-611.rhel6.x86_64. Downgrading to percona-xtrabackup-2.0.7-552.rhel6.x86_64 solved the issue.


enabling snapshot isolation level on a database - what to monitor? I am having problems with frequent deadlocks on a subscription database.

I am considering enabling the snapshot isolation level for that database.

I am aware that there is an overhead on the tempDb to maintain row versioning where writes don't block reads.

what should I monitor before and after enabling the snapshot isolation level on a specific database?

There are doubtless many blogs that discuss this, but this post by Kendra Little (at BrentOzar.com) discussed the issues that concern you.  See:

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

Kendra discusses some of the problems that arise and how you can test for potential problems.

After going through what problems SNAPSHOT isolation may cause, you need to also realize that READ COMMITTED isolation level is not as 'simple' as some might think.


Can running a query on a readable secondary slow down (block) the primary in AG Someone told me that running queries on a readable secondary in an AG can slow down (or even block) the primary (no matter if it's in sync or async mode).

I do not understand how this is possible. I'll try to explain why I think this is not possible.

Sync mode:
Log records are sent to the secondary and when those records are hardened on the secondary (written to log file) an ACK is sent to the primary and the transaction is committed on the primary.If there is some latency on sending the ACK this can affect the primary but I think this can't be caused by a query running on the secondary.
Async mode:
Log records are sent to the secondary and the primary isn't waiting for an ACK from the secondary, it is just 'fire and forget'.

What can happen is that the REDO-thread is blocked by a long running query => changes can't be applied to the secondary but I think this doesn't affect the performance on the primary.

Is my assumption correct or am I missing something?

In Simple terms..

  Readers won't be blocked by DML operations,but there is a chance of blocking with DDL operations..

This not blocking  is achieved by mapping isolation levels to snapshot isolation levels.As a result of this,there will be row versioning over head,but there won't be any blocking by DML(Insert/Update/Delete)..

See below for more details on how Isolation levels are mapped in various cases

  What can happen is that the REDO-thread is blocked by a long running query => changes can't be applied to the secondary

When there is a blocking on secondary due to lock conflicts (thread trying to apply DDL to a page generated on primary ) and redo thread is blocked.The only impact i could see is log growth (depending on synchronous commit or asynchronous commit). But there will be blocking irrespective of sync model you choose.

SQL team, even tried with a design  option of killing read only query on secondary automatically,when there is blocking ,but went against the option..

Below is the explanation from Sunil Agarwal on the same

  When we were designing this feature, we thought about killing the redo work -- sorry, killing the reporting work automatically. As soon as redo is blocked, we'll say, okay, I'm going to kill the read workload, and the redo moves forward.
  The problem was if you were running a reporting workload that is very critical for now or your reporting workload was taking half an hour to run and you were almost like 39 minutes done and the blocking happens and if you kill it, it's not a good user story.So what we have done is we have exposed the right knobs and we let the customer decide what to do if the redo thread gets blocked.

so you will have to monitor Xevents to know about blockings and take decision

References  used:
AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas
AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation


The most important oracle database performance metrics Team,

I need to collect the top most 10 important Oracle database related metrics from Oracle OEM.
Can somebody help me define them? I could not find document that list the metrics for oracle database specific. This is for Host, Service and Web app stats Host, Service, Web app metrics from OEM

I'd really appreciate any insight.

Your question is too broad and opinion based for this forum. There are too many permutations and combinations. Start with the Oracle docco. Don't forget OS metrics - sysstat for Linux or see here for ideas about Windows (you didn't even mention your OS).

Are you running an OLTP or an OLAP system? Different answers! Do you have a on-premise or cloud system? Different answers! What version of Oracle are you running? Different answers! How much are you prepared to pay (normally the crunch question!)? Different answers!

There are any number of both free and pay monitoring software systems/frameworks - Nagios (free) and Foglight (Quest) would probably be the best known.

Take the tour and check out this blog post - "Help us to help you". This site is a great resource and you'll get all the more out of it if you follow the guidelines. Give us a specific real-life scenario and then you should be able to get more assistance here. You could also check software recommendations.

[EDIT]

Maybe I missed the original reference to the Oracle Enterprise Manager (OEM) document. What you're really looking for is information on the Oracle Wait Interface (OWI), a complex but truly impressive piece of coding - not a system to be learnt over a weekend! You'll also want to delve into the mysteries of the Oracle Automated Work Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) (may costo mucho bucks!).

The first thing that I would do if I were you is to get Christopher Lawson's "The Art and Science of Oracle Performance Tuning". It is a very (very! surprisingly so) readable introduction to Oracle performance tuning - he doesn't give you a "top 10" - he tells you how to look for one on your system. Then read, read and read some more (preferably till your eyes bleed! :-) ). Oracle is a huge system and, just as Rome wasn't built in a day, neither will a deep understanding of Oracle. But, if you persevere, you'll get here.


What's the default password for SYSTEM in Amazon Oracle RDS? I can only remember setting these parameters and definitely not the password for SYSTEM:

DB Instance Identifier = mydbinstanceidentifier
Master Username = mymasterusername
Master Password = mymasterpassword

The SYS user, SYSTEM user, and other administrative accounts are locked and cannot be used
  More

The Amazon documentation for Oracle on Amazon RDS says you have to use master user which has the DBA privilege and other users like SYS and SYSTEM are locked by default and cannot be used.

SYSDBA privilege is not available for customer in Amazon RDS so Amazon EC2 might be a better choice for you and can have full control over the database and high privileged user like SYS and SYSTEM user.

Useful links:
Best Practices for Running Oracle Database on Amazon Web Services
Common DBA Tasks for Oracle DB Instances
Oracle on Amazon RDS


Compare value from xml node that contains decimal I need to compare the values of two different XML nodes for a record stored on SQL Server 2014.

Here is the contents of my XML column that I will be using:

<Document>
   <PolicyId>0</PolicyId>
   <ChangeAmount>1.4-</ChangeAmount>
   <PolicyTerm>
       <OutstandingBalance>35.04+</OutstandingBalance>
   </PolicyTerm>
   <OriginalCollectionAmount>36.44+</OriginalCollectionAmount>
</Document>

Here is my SQL statement that is not working:

SELECT *
FROM   Table
WHERE  QueueName ='.\private$\CollectionActivityPosted'
AND    Payload.value('(/Document/TransType)[1]', 'nvarchar(20)') = 'C'
AND    (Payload.value('(/Document/OutstandingBalance)[1]', 'nvarchar(20)')
       <>
       (Payload.value('(/Document/OriginalCollectionAmount)[1]', 'nvarchar(20)')))

I am expecting my query to get a hit based on the OutstandingBalance not being equal to the OriginalCollectionAmount but it doesn't.

It looks like you've got the wrong path in your where predicate.  If I do this, it works:

IF OBJECT_ID(N'dbo.x', N'U') IS NOT NULL
DROP TABLE dbo.x;
CREATE TABLE dbo.x
(
    xm xml
);
INSERT INTO dbo.x (xm)
SELECT N'<Document>
   <PolicyId>0</PolicyId>
  <ChangeAmount>1.4-</ChangeAmount>
  <PolicyTerm>
    <OutstandingBalance>35.04+</OutstandingBalance>
  </PolicyTerm>
  <OriginalCollectionAmount>36.44+</OriginalCollectionAmount>
</Document>';

SELECT x.xm.value('(/Document/PolicyTerm/OutstandingBalance)[1]', 'nvarchar(20)')
FROM dbo.x

╔══════════════════╗
║ (No column name) ║
╠══════════════════╣
║ 35.04+           ║
╚══════════════════╝

Testing:

SELECT *
FROM dbo.x
WHERE (x.xm.value('(/Document/PolicyTerm/OutstandingBalance)[1]', 'nvarchar(20)') <>
     (x.xm.value('(/Document/OriginalCollectionAmount)[1]', 'nvarchar(20)')))

╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                xm                                                                                ║
╠═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ <Document><PolicyId>0</PolicyId><ChangeAmount>1.4-</ChangeAmount><PolicyTerm><OutstandingBalance>35.04+</OutstandingBalance></PolicyTerm><OriginalCollectionAmount>36.44+</OriginalCollectionAmount></Document> ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

In your code, Payload.value('(/Document/OutstandingBalance)[1]', 'nvarchar(20)') should be Payload.value('(/Document/PolicyTerm/OutstandingBalance)[1]', 'nvarchar(20)')


Sql Server 2016 uses low RAM I have an SQL server 2016,

  version:
  (SP2-CU4-OD) (KB4482972) - 13.0.5239.0 (X64)

The server is a Win 2016, with 64 GB RAM.
I have configured max memory for SQL to 54 GB, but it uses less than 1 GB.
Data are been migrated from an older SQL version.

Somebody can help me?

Thanks

Like @Shanky mentioned in the comment, it could be due to Express Edition accidently being installed instead of Standard or Enterprise. Due to the Database size size limit, I don't think you could even restore a large database, so you would notice almost immediately.

As an experiement, have you tried caching a lot of data in memory by doing several large SELECTs to see if it changes? SQL Server is fairly memory-hungry, but by default it doesn't use memory until it needs it, so if nothing has happened since the last restart, there's nothing in memory yet.


postgres update parent if entity / foreign key updates I have a table recipes and every recipe can have multiple entries.

CREATE TABLE IF NOT EXISTS "recipes" (
    "ident" SERIAL PRIMARY KEY,
    "identifier" TEXT NOT NULL UNIQUE,
    "modified_on" TIMESTAMP WITH TIME ZONE DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS "entries" (
    "ident" SERIAL PRIMARY KEY,
    "recipe_id" INTEGER NOT NULL,
    "name" TEXT
    FOREIGN KEY ("recipe_id") REFERENCES "recipes"("ident") ON DELETE CASCADE
);

Now when a recipe is updated, the following function is called and updates my timestamp:

CREATE OR REPLACE FUNCTION update_recipe_timestamp_proc()
RETURNS TRIGGER AS $$
BEGIN
    NEW."modified_on" = now();
    PERFORM pg_notify('notify_recipes_update', CAST(NEW.ident AS text));
    RETURN NEW;
END;
$$ language 'plpgsql';

Now I also want my timestamp in the recipe modified_on to get updated whenever an entry which belongs to it is updated.

The following trigger is not working.

DROP TRIGGER IF EXISTS update_recipes_timestamp_e ON entries;
CREATE TRIGGER update_recipes_timestamp_e BEFORE UPDATE ON entries FOR EACH ROW EXECUTE PROCEDURE update_recipe_timestamp_proc();

ERROR: Record "NEW" has no field "modified_on"

Someone can give me a hint what I have to do?

Here is my solution.

CREATE OR REPLACE FUNCTION update_recipe_timestamp_from_entry_proc()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE recipes SET modified_on = now() WHERE ident = NEW."recipe_id";
    PERFORM pg_notify('notify_recipe_update', CAST(NEW."recipe_id" AS text));
    RETURN NEW;
END;
$$ language 'plpgsql';

DROP TRIGGER IF EXISTS update_recipes_timestamp_e ON entries;
CREATE TRIGGER update_recipes_timestamp_e BEFORE UPDATE ON entries FOR EACH ROW EXECUTE PROCEDURE update_recipe_timestamp_from_entry_proc();


Best practices for relating NoSQL data to RDMBS I am looking to use both a NoSQL solution (Redis, Mongo, etc) with a mySQL/Laravel application. My reasons for this is that I need a schemaless key value storage to go with my relational data. I could easily make a key value storage within mySQL, but I think that this is a case where NoSQL could shine and would rather not take the easy way out. Other than coming here for help, that is.

What I am a little foggy is how to tie these two together. An example scenario is I have 2 forms that generate leads. One form has email, best time to call, and date of birth. The other has email and are you wearing pants? Is it as simple as storing data in my NoSQL table as follows:

{ 'lead_id':3829, 'email': '3829@test.com' }
{ 'lead_id':3829, 'best_time': 'evening' }
{ 'lead_id':3829, 'dob': '1980-07-01' }
{ 'lead_id':3830, 'email': '3830@test.com' }
{ 'lead_id':3829, 'pants': 'off' }

Then my mySQL Lead table could look like:

id   | form_id | timestamp
3829 | 1       | 2014-09-18 11:02:32
3830 | 2       | 2014-09-18 09:12:10

Since this will be my first NoSQL excursion, I am also not certain as to the functionality available to my NoSQL data. Would it still be possible to retrieve data based on my lead_id and sort on a particular key? For example, filter form #2 results by email containing 'foo' sorted by the date they were submitted?

There are two ways I would approach this.

1.) Dump your json from Mongo into a tmp_retrieval_table in MySQL, then write corresponding db functions to perform the parsing / ETL to the schemata of choice in what ever fashion you see fit.

2.) Utilize MySQL's partitioning functionality. Where you could in deed sort the records by a specific key or timestamp.

MySQL Partitioning


BIT columns all "1" after a phpMyAdmin export/import I have to import data from a MySQL database using phpMyAdmin because that's the only interface my hosting provider supports.

I have exported my database from my local machine using phpMyAdmin. After that I imported the script file to my host. All of data in the columns that is BIT type are changed to '1'. Hosting database version is 5.5.29.

Is it a phpMyAdmin problem, or MySQL version problem? How can I fix this?

I think we'll need some more information to pin down your problem. I just tried to recreate your issue and I could not.

I created a table named bitTest with two columns:
textName VARCHAR(5)
bitValue BIT(1)

I inserted two rows into the table using phpMyAdmin's "Insert" feature

Then I did a phpMyAdmin "Export" using the "Quick method". The resulting .sql file was

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 01, 2013 at 03:34 PM
-- Server version: 5.5.29
-- PHP Version: 5.3.10-1ubuntu3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `zzz`
--

-- --------------------------------------------------------

--
-- Table structure for table `bitTest`
--

CREATE TABLE IF NOT EXISTS `bitTest` (
  `textName` varchar(5) NOT NULL,
  `bitValue` bit(1) NOT NULL,
  PRIMARY KEY (`textName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bitTest`
--

INSERT INTO `bitTest` (`textName`, `bitValue`) VALUES
('False', b'0'),
('True', b'1');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Then I dropped the table and used phpMyAdmin's "Import" feature ro re-create and re-populate it using that .sql file. The BIT values were (correctly) imported as 0 and 1.

Perhaps you could update your question with some excerpts from the .sql file you exported to see what differences might exist between your export file and mine.


How does clustering on a field other than autoincrement id affect inserts? I have a table with an id column which is auto-incremented, and various other informational columns. Rows are inserted into this table very frequently. When the data is read back, the majority of queries filter by a foreign key and a date range.

Currently, there is a clustered index on the id column, and a non-clustered index on the two columns of importance (TrackerId and DateRecorded). If I were to swap the indexes, our queries will run a lot faster. Would this negatively affect insert times?

In your actual design you have unique, static,narrow, ever-increasing clustered index key that causes no page splits when rows are inserted.

If you create your clustered index on TrackerId and DateRecorded (you want to use these columns in this order, right? because your seek predicate has an equality on TrackerId and range on DateRecorded) you'll get non-increasing clustered key that will cause page splits on INSERT.

It will be wider and maybe not unique.

But final decision is up to you. We don't know if this table has more reads or writes, and page splits can be mitigated by choosing appropriate Fill Factor and regular index rebuild.

Here your can read more on it: Mitigating Index Fragmentation by Paul Randal


RANK used in 'where' returns invalid column, but exists in results set I basically have a table with date, timestamp, DID, coordinates.

I want a query that will return rows with the last coordinate from day X, and the first coord from day X+1, and the coordinates. So it will only return results where there are 2 consecutive dates.

This is what I came up with. Been trying to get this query to work, it's almost perfect, but I just need to add the commented out where conditions and it'll do exactly what I want. But when I uncomment, I get an error "column doesn't exist":

SELECT  a.timestamp_intersecting_date d1,b.timestamp_intersecting_date d2,
        a."DID",
        a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
        RANK () OVER (
          PARTITION BY a.timestamp_intersecting_date
          ORDER BY a.timestamp_intersecting_max DESC
       ) timestamp_d1_rank ,
        RANK () OVER (
          PARTITION BY b.timestamp_intersecting_date
          ORDER BY b.timestamp_intersecting_max ASC
       ) timestamp_d2_rank,
        a.coords_centroid, b.coords_centroid
FROM
    signals a
INNER JOIN signals b ON (a."DID" = b."DID")
WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
AND a."DID" = b."DID"
--AND timestamp_d1_rank = 1
--AND timestamp_d2_rank = 1
ORDER BY a."DID", t1 desc, t2 asc

How to solve this?

You cannot use aliases in WHERE clauses, nor can you use Window functions.

To demonstrate, consider this example (fiddle).

CREATE TABLE payment (amount INTEGER, pay_date DATE);

INSERT INTO payment VALUES (54,  '2019-09-01'), (56,  '2019-09-01'), (154, '2019-09-02'),
(156, '2019-09-02'), (254, '2019-09-03'), (256, '2019-09-03');

Then run the two following queries:

SELECT
  *,
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE the_sums > 200; -- ERROR:  column "the_sums" does not exist

and

SELECT
  *,
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE SUM(amount) OVER (PARTITION BY pay_date) > 200;
-- ERROR:  window functions are not allowed in WHERE

You didn't post your error message or PostgreSQL version, but your problem is that timestamp_d1_rank and timestamp_d2_rank are aliases.

You have two options:

First Option: use the aliases/Window functions within a subquery (also in fiddle)

SELECT * FROM
(
SELECT
  *,
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
) AS tab
WHERE the_sums < 200;

Result:

amount    pay_date  the_sums
    54  2019-09-01       110
    56  2019-09-01       110

Second Option: you can use a CTE (aka the WITH clause as outlined here or as in @ypercubeᵀᴹ 's answer above or also see the fiddle.

For a good explanation of this whole area, see here.


SSIS best practice to allow easily swappable/ secure/ accessible Database Connections? I come from Pentaho PDI.

There, I learned to have scripts load data to a [VariableDB].

That way, I can either initiate that VariableDB at runtime to "ProdDB" or even set the variable to different DBs within the script, temporarily, whatever.

Point is, I don't have to go through tons of files changing database names too often unless some major architecture changes.

Second thing is security. Pentaho's isn't great but you can at least store some of the database credentials is encrypted format, and they are blocked out in GUIs. A 'mastermind' could crack it but they would need a lot of access anyway.

SSIS --- well, it might be the opposite, maybe not. I'd prefer not to store any database passwords in plain text. At the same time, my predecessor left --- and I have no idea where the credentials are. They're "poof" gone -- I don't know. If SSIS scripts and jobs are running on the same SQL DB server, are they stored somewhere? I'm not sure.

Are they encrypted to his username, and now that he's long gone, unrecoverable?

This is not so important, since I can re-enter database credentials, but I'm looking to the future.

I know there are probably a few ways of doing this, but what's a simple way so that if I have 5 packages pointing to "TheFinanceDatabase" I can easily swap out the database host server, name, user, password, etc?

I take it config files and/ or connection managers --- they can be shared between packages within the same "project?". Maybe the "projects" can share connection credentials / pointers within the same "solution"? Just wondering here -- thanks!

I want to avoid re-entering/ losing credentials in the future.

When the packages are deploy to and executed from the SSIS Catalog variables from an Environment can be used to store credentials such as those you mentioned, with these then used by the connection managers of packages.  Once the value of an environment variable is changed, this is propagated to all packages using this variable.  For instance, if you had a variable called "Server" that's used as the ServerName property of a connection manager, and you updated it to hold production server name instead of the test one all packages with this would now be pointed to the prod server.  Additionally, environments can also be created for test, development, prod, etc., with the referenced environment on the package then updated to point to a different environment.  The variables can also be marked as sensitive to protect information such as passwords.  If you haven't installed the catalog (SSISDB), I'd recommend doing so.  You can learn more about the SSIS Catalog here.  A high level overview of configuring a connection manager to reference an environment variable is below.

Right click the package (or project) and select Configure.  Then on the References pane press Add and choose the environment you created.
Still on the Configure page, go back to the Parameters pane, then the Connection Managers tab.  For the connection manager that will be configured, select the property that will be set from an environment variable by clicking the ellipsis next to the property and select the appropriate environment variables on the next window that is displayed.
When running a package that references an environment from SQL Agent make sure to update the job step that calls the package with an environment reference.  This can be done on the Job Step by going to the Configuration tab, checking the Environment check-box and selecting the referenced environment.


Pass a table name as a parameter to a stored procedure I am trying to create a Stored Procedure, and pass the name of a table as a parameter and I get this message:Must declare the table variable "@tblName".
What do I have to change to make it work?
CREATE PROCEDURE [usp_SaveToErrorLog]
    @tblName as nvarchar(50),
    @subject as nvarchar(30)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblName))
        Begin
            Insert Into @tblName
            (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorState, ErrorSeverity, ErrorLine)
            Select
                ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE()
        End
END

Unfortunately, you can't use a variable containing the table name in an INSERT statement. The error message is actually occurring because it is expecting a variable that is a table type to insert rows into, but you're passing a variable with a string value.
You can use Dynamic SQL to execute the command using a variable table name value like this:
CREATE PROCEDURE [usp_SaveToErrorLog]
    @tblName as nvarchar(50),
    @subject as nvarchar(30)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Sql NVARCHAR(MAX)

    -- Insert statements for procedure here
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblName))
        Begin
            SET @Sql = 'Insert Into ' + @tblName + '
            (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorState, ErrorSeverity, ErrorLine)
            Select
                ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE()'

            EXEC sp_executesql @Sql
        End
END

What this does is dynamically create your T-SQL command and store it in the @Sql variable. The stored procedure sp_executesql is then used to execute the T-SQL commands in the @Sql variable, which has the actual table name from the @tblName variable.


MYSQL Patching -Roll back MYSQL patching.

I took all database backups before starting patching.
If we patch mysql 5.6.30 to 5.6.32 and if we want to rollback to 5.6.30 due to some issues. Can we do that? if so should we have to rollback entire server ?Or is there any way just to roll back mysql?

You can have 2 strategies:

Stop MySQL Server and backup (just copy all files to some location) - folder c:/ProgramData/MySQL/data
Make a full dump of all databases
mysqldump -u root -p --all-databases > c:/backups/all_db.sql

p1 - if You plan rollback exactly for 5.6.30,

p2 - if You plan go till the win, best to have both.

then You must have old installer for revert back at any time, You can download it from official archive - https://downloads.mysql.com/archives/community/

Then You if You decide revert back:

Uninstall latest MySQL - by windows tools
Install 5.6.30 from previous step
Copy folder c:/ProgramData/Mysql/data saved on previous step
Start MySQL

If You also make a sql dump - You can use normal restore procedure

mysql -u root -p < c:/backups/all_db.sql

, but it work much slower than simple file copy.


Why did error messages change after upgrading to MySQL 5.7.9? I am running MySQL 5.7.9 on a Windows machine, upgraded recently from a 5.6.17.

I used to get explicit error message with my previous versio, but somehow since the upgrade I get very mysterious error message. For example I get:

SQLSTATE[42S22]: Column not found: 1054 Unknown error 1054

While I used to have something like stated in the doc:

SQLSTATE[42S22]: Unknown column '%s' in '%s'

It's way easier to debug when you know which column was not found, and where (in 'select list' or 'where clause', etc.).

My question is: how to get back to explicit error message? There must be a parameter somewhere but I haven't manage to find it so far.

Thanks!

An off-the-wall guess:  You have a column (or something) that matches a new reserved word.

5.7 adds GENERATED, OPTIMIZER_COSTS, STORED, VIRTUAL as "Reserved" words, plus a number of "non-reserved" words.  Details in keywords.html.

In some contexts, use of a keyword can lead to a cryptic error message.  When you find the context, please file a bug report at http://bugs.mysql.com .

Another tip:  Search for 1054 in your sources, then look at what immediately precedes it.


PostgreSQL - shared buffers expiration I noticed a performance issue with my SQL queries. The same query executed for the first time took ~7 seconds, consecutive executions took around 50 ms.

I tried the same query another day and once again, first one took ~7 seconds and next ones 50-60 ms.

I used EXPLAIN then:

First run:

main=> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM txes INNER JOIN accounts ON txes.account_id = accounts.id WHERE accounts.user_id = 135736;

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=8564.89..8564.90 rows=1 width=8) (actual time=7854.024..7854.024 rows=1 loops=1)
   Buffers: shared hit=3636 read=2052
   ->  Nested Loop  (cost=0.98..8562.64 rows=899 width=0) (actual time=3.901..7852.603 rows=9650 loops=1)
         Buffers: shared hit=3636 read=2052
         ->  Index Scan using index_user_accounts_on_user_id on user_accounts  (cost=0.42..6.03 rows=5 width=4) (actual time=0.016..0.087 rows=31 loops=1)
               Index Cond: (user_id = 135736)
               Buffers: shared hit=29
         ->  Index Only Scan using index_money_transactions_on_user_account_id_and_transaction_on on money_transactions  (cost=0.56..1694.89 rows=1643 width=4) (actual time=1.162..253.254 rows=311 loops=31)
               Index Cond: (user_account_id = user_accounts.id)
               Heap Fetches: 9650
               Buffers: shared hit=3607 read=2052
 Planning time: 0.217 ms
 Execution time: 7854.055 ms
(13 rows)

Next run:

main=> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM txes INNER JOIN accounts ON txes.account_id = accounts.id WHERE accounts.user_id = 135736;

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8564.89..8564.90 rows=1 width=8) (actual time=14.739..14.739 rows=1 loops=1)
   Buffers: shared hit=5688
   ->  Nested Loop  (cost=0.98..8562.64 rows=899 width=0) (actual time=0.068..14.146 rows=9650 loops=1)
         Buffers: shared hit=5688
         ->  Index Scan using index_accounts_on_user_id on accounts  (cost=0.42..6.03 rows=5 width=4) (actual time=0.037..0.077 rows=31 loops=1)
               Index Cond: (user_id = 135736)
               Buffers: shared hit=29
         ->  Index Only Scan using index_txes_on_account_id_and_tx_on on txes  (cost=0.56..1694.89 rows=1643 width=4) (actual time=0.010..0.424 rows=311 loops=31)
               Index Cond: (account_id = accounts.id)
               Heap Fetches: 9650
               Buffers: shared hit=5659
 Planning time: 1.060 ms
 Execution time: 14.805 ms
(13 rows)

So it seems that shared buffers do not contain all the data. And as far as I understand shared buffers represent RAM memory.

So my question is how long the data stays in shared buffers, until the machine is rebooted or is there some kind of validity/expiration period?

Total RAM on machine is 32GB. It is designed to run both web application and database. 12 GB are dedicated for DB.

shared_buffers = 3000MB
effective_cache_size = 9000MB

Data never expires from shared buffers merely due to the passage of time. It will stay in shared buffers until it gets evicted to make room for some other data, until the PostgreSQL server is restarted, or until the object the data belongs to gets dropped (or truncated).  If the data is getting evicted, it is probably because other activity in the database needs to work with a different part of the data and needs to make room for it.

The amount of effective_cache_size which is more than shared_buffers is not dedicated to the DB in any formal sense.  It is an estimate only, it is not reserved or set aside.  That memory is subject to the OS kernel's eviction algorithm, not PostgreSQL's.


Can PostgreSQL create a case sensitive database? I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to dbsqlexec() to PQexec() (for example). That part is mostly working.

It appears that the Sybase database is set up in a case sensitive manner (with respect to database object names). For example, there is both a WIDGET table and a widget table. It looks like the convention in this application is that the all-uppercase names indicate the actual data tables, while the lowercase names are used as temporary tables when running some processing.

According to 4.1 Lexical Structure, "Key words and unquoted identifiers are case insensitive." I know that I can double-quote identifiers to disable automatic folding to lowercase, but I don't want to have to do that manually through the zillions of lines of code that uses this database.

Is there a way to set up PostgreSQL to disable this automatic case folding for database object identifiers?

My alternative will be to write some code that examines each SQL statement and puts double quotes around every identifier (that is not a keyword).

I ended up writing some code that transforms the SQL generated by the application into PostgreSQL-compatible SQL. It's pretty straightforward:

Split the statement into sensible tokens, skipping single-quoted string literals
Double-quote anything that is not a keyword or number

I also took advantage of this layer to transform calls to isnull to coalesce. So far it's working pretty well.


Sync oracle DBs using archive log when not connected to each other I'm trying to synchronize 2 dbs which are not connected to each other, and located in a separated networks.

The 'copy db' (aka: standby) can be read only, but still should be active.

My intuition (which I'm not sure is correct) tells me I can synchronize the both using RMAN backup / restore, then move the archive files from source (production) to copy (standby), open copy (standby) in read only mode and apply changes when still open (similar to active data guard).

What I did is as follows (according this manual):
I configured data guard according the manual, see link.

On source (production):

$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

I moved the files and restored on the copy after configured as standby:

$ rman target=/
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

and got Finished restore at

Now on the copy (after all configurations from the manual) I've tried to open the db:

SQL> shutdown immediate;
...
Oracle instance shut down.
SQL> startup mount;
ORACLE instance started
...
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\PATH\TO\FILE\SYSTEM01.DBF'

I'm using oracle 11g DB on windows, I've tried also on Linux and reach the same results.

both machine are new, oracle 11g SW installed on both (with sample schemes), and DB was dropped on the copy before the restore.

file was copied after DB was dropped, no changes was made on the source DB except the configuration according to the manual.

I would like to ask:

is what I'm trying to achieve (active data guard without direct connection, manually move of archive once in a while, and auto apply of archive) looks possible?
why the database restore or open is failed?
is there simpler way to do so? (i.e. manual backup / restore without configure production / standby architecture, but still apply the archive of the source to the copy.)

Thanks for any reply.

First you need to make sure that the copied database is a standby database. That is not the case in your case. Use duplicate to do that is the easiest way.

Is it possble at all using a not connected database .... yes, but why? To me it looks like a waste of money if there is no decent connectivity.

Restore/open failed because your copy database feels it has the PRIMARY role.
(select database_role from v$database; to check)

There hardly is a simpler way to do this than using rman duplicate. This can be done using a backup, no problem at all. Maybe hiring a real dba can save you lot's of time, money and in the end a disaster.


Need Help With BCNF Decomposition Problem I'll try and keep it as quick and concise as I can: I'm currently a novice developer who's super eager to learn Database Normalization and Optimization so I can up my skill in Full-Stack development, so bare with me please. I'm trying to get good at spotting the different kinds of functional dependencies so I can get better at making optimal/appropriate relational models/schemas, and I'm currently doing an assignment on 3NF and BCNF decomposition (possibly NF4?) that I'm struggling with.

The Assignment has 2 parts and I'm fairly certain that I've properly completed the first Part 1, but I'm completely confused on the Part 2.

Here is the relation we are normalizing called LOTS:

Part One was to break the Relation down into BCNF form if possible, and here is my work for that:

The current R1 relation is the LOTS relation for Part 2.

Here is the prompt for Part2:

It seems to me that, since the AREA attribute has unique sets of values that can determine the COUNTY_NAME that the new introduces functional dependency is AREA -> COUNTY_NAME, which would leave the LOTS relation in 3NF. Thank you for anyone whiling to help a new comer learn. Cheers!

You solved your assignment, so not much more to add. However, because you mentioned that you are

  "super eager to learn database normalization and optimization so I can up my skill in full-stack development,"

and because I am mostly stuck indoors these days -- I will demonstrate a bit different method to achieve the goal. This method will serve you well for your personal and business projects, not so much for db-related exams and textbook assignments.

So here is the task, we have relvar (table) with attributes, and some mythical creature called (probably) database designer has provided us with functional dependencies.

lots { PROPERTY_ID
     , COUNTY_NAME
     , LOT_NO
     , AREA
     , PRICE
     , TAX_RATE
     }

COUNTY_NAME, LOT_NO -> PROPERTY_ID
PROPERTY_ID -> COUNTY_NAME, LOT_NO
PROPERTY_ID -> COUNTY_NAME, LOT_NO, AREA, PRICE, TAX_RATE
COUNTY_NAME -> TAX_RATE
AREA -> PRICE

The idea now is to verbalize this and write down (simple) predicates and associated constraints so that the problem can be reasoned about in a natural language using logic.

A simple predicate can not be decomposed without losing information, its matching relvar is in 6NF.

[p 1] County COUNTY_NAME exists.

(c 1.1) County is identified by COUNTY_NAME.

county {COUNTY_NAME}  -- p 1
   KEY {COUNTY_NAME}  -- c 1.1

[p 2] Lot number LOT_NO in county COUNTY_NAME exists.

(c 2.1) Lot is identified by COUNTY_NAME, LOT_NO pair.

county_lot {COUNTY_NAME, LOT_NO}  -- p 2
       KEY {COUNTY_NAME, LOT_NO}  -- c 2.1

[p 3] Lot number LOT_NO in county COUNTY_NAME is assigned property number PROPERTY_ID.

(c 3.1) For each lot and county pair, that lot lot and county pair is assigned exactly one property number.

(c 3.2) For each property number, that property number is assigned to exactly one lot and county pair.

property {COUNTY_NAME, LOT_NO, PROPERTY_ID} -- p 3
     KEY {COUNTY_NAME, LOT_NO}              -- c 3.1
     KEY {PROPERTY_ID}                      -- c 3.2

-- this one can be decomposed to
-- {PROPERTY_ID, COUNTY_NAME} {PROPERTY_ID, LOT_NO}
-- but leaving it as is to simplify.

[p 4] County COUNTY_NAME has tax rate of TAX_RATE.

(c 4.1) For each county, that county has exactly one tax rate; for each tax rate, it is possible that more than one county has that tax rate.

county_tax {COUNTY_NAME, TAX_RATE} -- p 4
       KEY {COUNTY_NAME}           -- c 4.1

[p 5] Lot area of AREA is priced at PRICE.

(c 5.1) For each lot area, that lot area is priced at exactly one price; for each price, more than one lot area may be priced at that price.

area_price {AREA, PRICE} -- p 5
       KEY {AREA}        -- c 5.1

[p 6] Property PROPERTY_ID has area of AREA.

(c 6.1) Each property is of exactly one area; for each area, it is possible that more than one property has that area.

property_area {PROPERTY_ID, AREA} -- p 6
          KEY {PROPERTY_ID}       -- c 6.1

At this point, all these relvars represent simple predicates and are in 6NF (well p3  in 5NF). Now I can decide to leave them as is (logically ok) or to combine some of them based on keys, but staying in 5NF.

county_tax {COUNTY_NAME, TAX_RATE}  -- p 1,   p 4
       KEY {COUNTY_NAME}            -- c 1.1, c 4.1

area_price {AREA, PRICE}  -- p 5
       KEY {AREA}         -- c 5.1

property_ { COUNTY_NAME   -- p 2, p 3, p 6
          , LOT_NO
          , PROPERTY_ID
          , AREA
          }
      KEY {COUNTY_NAME, LOT_NO}  -- c 2.1, c 3.1
      KEY {PROPERTY_ID}          -- c 3.2, c 6.1

First two are in 6NF, the third one in 5NF.

This method requires understanding of: 1NF, 6NF, 5NF; or 1NF only, as long as redundancy and lossless decomposition join is properly understood. In other words, you can have your DB in 5NF even if you do not know what that means, as long as you can clearly define predicates and constraints.

So how can one know that relvar is 5NF, if one does not know what it means? Simple, it is not abut being in  this-and-that NF, but about removing redundancy.

  The 5NF is final NF as far as removing redundancy is concerned.
  A relvar (table) is in 5NF iff decomposing it would not remove any redundancies.

Or (same meaning) if at least one of these holds:

There is no redundancy.
There is redundancy, but it can not be removed by decomposition.
It is not possible to decompose table without losing information.
It is not possible to decompose table at all.

Just make sure that when you reason abut redundancy, you reason about predicate and constraints, not about few rows of sample data.

To recap, instead of decomposing it by an algorithm or following functional dependencies, the method "explodes" the predicate of the initial relvar into set of simple predicates (6NF relvars) and then composes them, making sure to stay in 5NF.
It uses logic and natural language, a huge advantage when one needs to communicate with analysts and business people.


NoSQL and RDBMS together? I was wondering if there are any good solutions for recording data in a NoSQL  database and then converting them over to an RDBMS?

For example, if you wanted to capture some data quickly, like session logs, but then you want to be able to create reports on them later.

My favorite database is Postgres, so if your answer is relevant in Postgres that would be great.

The Quick Answer - Yes. Happens all the time.

There are plenty of good solutions. What solutions are already in your environment? I am helping one client that takes their web site/session activity information from their web application, they write it to xml then deserialize that xml into Hadoop. They then use Hive on top of Hadoop to create aggregations and facts (by joining this log data to reference data from their website that they store in a Postgres database). These aggregtions and facts are then loaded into an RDBMS (they use SQL Server, because of the rich analytics provided in the SQL Server stack).

I would explore Hadoop, look at Hive which is a query engine on top of Hadoop that allows you to write declarative queries that closely resemble SQL and get translated into MapReduce jobs behind the scenes. Then look at Sqoop which is another tool in the Hadoop family that assists you with exporting/importing data.

http://hadoop.apache.org/
http://www.amazon.com/Hadoop-Definitive-Guide-Tom-White/dp/1449389732/ref=sr_1_1?s=books&ie=UTF8&qid=1319160463&sr=1-1
http://www.cloudera.com/blog/2009/06/introducing-sqoop/
http://hive.apache.org/

There are other options out there too...


Windows SQL Server Express 2012 and LDAP external server integration I have a new installation of Windows Server 2012 with SQL Server Express 2012 with the management tools.

I'm planning to create there a simple synchronization that will make queries twice per day to an external LDAP service (not a Windows active directory server) and fill a table in the local SQL instance. I've seen other questions here about this, and all of them refer to the creation of a Linked Server, but I think that will only work if the remote LDAP service was a Windows AD server.

I'm able to query this server using the LDP tool, but looks like I cannot add it as a linked server. Any ideas?

Do you just want to configure this? Or are you not averse to writing some code?

In the latter case, it should be relatively easy to write a small program that connects to both the LDAP server and the database and synchronizes the data. I've written such a thing once in PL/SQL in Oracle -- if it's possible there it should be possible in most languages and environments!


MySQL column type to boost performance I'll have a table where:

userid (link to user's id in users table)
foldername (a varchar, max. 260 char)
entry (varchar, max. 1024)
data (varchar, max. 20kb)

Single user will have several foldername and entries.
The point is, queries will never ever search anything inside entry or data. Queries ONLY will be like
select * from data where userid  = 1 and foldername = 'first';

That's it.
So I thought about two options:

a. Instead of entry and data columns, storing them inside HDD and only storing filename in DB, so when query returns, PHP app. will read filename, then read the file and send it to user in HTTP response.
b. Store them in DB as I said above

But I'll never run queries on them, so no sorting/indexing will be needed and no full text search will run ever.
QUESTIONS

What's the best possible optimization here?
How can I define a column as a "not-really-a-database-entry" type in MySQL, or is file better?

Having a VARCHAR as a part of an index would be cumbersome to manage although doable.

I would design the tables as follows

CREATE TABLE folderkeys
(
    id INT NOT NULL AUTO_INCREMENT,
    userid INT NOT NULL,
    foldername VARCHAR(260),
    PRIMARY KEY (id),
    UNIQUE KEY (userid,foldername)
) ENGINE=MyISAM;
CREATE TABLE datatable
(
    id INT NOT NULL AUTO_INCREMENT,
    folderkey_id INT NOT NULL,
    filename VARCHAR(1024),
    PRIMARY KEY (id),
    KEY (folderkey_id)
) ENGINE=MyISAM;

This will make the datatables indexes as character-compacted as possible

This will make it as easy doing queries like

INSERT IGNORE INTO folderkeys (userid,foldername) VALUES (1,'first');
SELECT id INTO @fldrkey FROM folderkeys WHERE userid=1 AND foldername='first';
INSERT INTO datatable (folderkey_id,filename) VALUES (@fldrkey,'...');

If you want the filename from userid 1 foldername 'first', you would do queries like this

SET @GivenUserID = 1;
SET @GivenFolder = 'first';
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID AND foldername=@GivenFolder) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

If you want filenames from all folders for userid 27, you would do queries like this

SET @GivenUserID = 27;
SELECT A.userid,A.foldername,B.filename FROM
(SELECT id FROM folderkeys WHERE userid = @GivenUserID) A
INNER JOIN datatable B ON A.id = B.folderkey_id;

Give it a Try !!!

I would normally recommend using InnoDB. Since you have lots of BLOB data, I'd rather not spend time trying to tune your log buffer and log files sizes. I would go with MyISAM for now.

BTW I gave you the tables with Option A in mind


Compare order of columns between two databases I  have two postgres databases which should have an equal db-schema, but have not.

I want to see the difference in the column order.

Columns of table1 in Dev: id, foo, bar

Columns of table1 in Prod: id, bar, foo

Strategy 1: list of columns

This is my current strategy to solve this.

Dump all columns of the database like this:

table1.id
table1.foo
table1.bar
table2.id
table2....

If I have a simple ascii list of both sides, I can use a diff tool to compare both db schemas.

I found ways to output all columns like above but the solutions don't support ordering by the schema of the table.

Question

How to show the difference of the column order in two databases?

Other strategies are welcome.

Implementation: I use postgres 9.3

If I have a simple ascii list of both sides, I can use a diff tool to compare both db schemas.

To get a list of all table columns you can use something like this:

select concat_ws('.', table_schema, table_name, column_name) as column_name
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema, table_name, ordinal_position;

Of course you can limit that to the schema(s) you are interested in.

You can use psql's \copy statement to spool that to a file. Or use whatever export feature the SQL client you are using offers you.


MySQL any way to import a huge (32 GB) sql dump faster? I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:

mysql -uroot dbname < dbname.sql

It is taking too long. There is a table with around 300 million rows, it's gotten to 1.5 million in around 3 hours. So, it seems that the whole thing would take 600 hours (that's 24 days) and is impractical. So my question is, is there a faster way to do this?

Further Info/Findings

The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
I do not have access to the original server and DB so I cannot make a new back up or do a "hot" copy etc.
Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
MySQL version is 5.6.20 community.
innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB

You definitely need to change the following

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ?

innodb_buffer_pool_size will cache frequently read data
innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
innodb_log_file_size : Larger log file reduces checkpointing and write I/O
innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.
innodb_flush_log_at_trx_commit

In the event of a crash, both 0 and 2 can lose once second of data.
The tradeoff is that both 0 and 2 increase write performance.
I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor.

Restart mysql like this

service mysql restart --innodb-doublewrite=0

This disables the InnoDB Double Write Buffer

Import your data. When done, restart mysql normally

service mysql restart

This reenables the InnoDB Double Write Buffer

Give it a Try !!!

SIDE NOTE : You should upgrade to 5.6.21 for latest security patches.


Storing different countries' administrative divisions I'm designing a data base which should relate users to their
administrative division for statistical purposes, considering that
every country has its own type of divisions. Chile, for example,
is divided in regions which are divided in provinces which are divided
in communes, while Angola is divided in provinces which are divided in
municipalities which are divided in communes. I thought a solution
could be to have a column called first_administrative_division
in the user table, which will refer to a
first_administrative_division table, which will have a
second_administrative_division column which will refer to a
third_administrative_division table which will have a column
which will refer to the country table. But reading
this
I didn't only found that there are countries which have only two
administrative divisions, and in other cases, four; some cases
are even more complex, like Argentina and Brazil, which have
different kind of internal divisions, divided in different ways. So,
I'm crying right now.

I don't want to cover the entire world, countries will be added as
they are needed. Is it posible to modelate such thing?

Thanks beforehand.

One way to store different levels of hierarchy is by creating a hierarchical table:

ID    Type
---   ---------------------------
1     Country
2     Province/State
3     County
4     City
5     Regions
6     Communes
7     Municipality

Then you can create your divisions and list them appropriately. This allows you to flexibly assign divisions and the parent divisions.

ID    Division    Parent    Type
----- ----------- --------- -----
1     Chile                 1
2     Region-A    1         5
3     Province-A  2         2
4     Commune-A   3         6
5     Angola                1
6     Province-A  5         2
7     Municip-A   6         7
8     Commune-A   7         6

The benefits is that different levels of hierarchy can be assigned in this design for different top level divisions. People can be associated with any level of hierarchy.

ID    Person      Division
----- ----------- ---------
1     Tom         4 (Commune-A of Province-A of Region-A of Chile)
2     Dick        7 (Municip-A of Province-A of Angola)
3     Harry       1 (Chile)

The challenge is to create a user defined function or stored procedure or utilize hierarchy data structures (e.g. SQL Server's Hierarchy ID) to extract different pieces of information for the statisticians. For example:

  Show sales by highest level of hierarchy of each salesperson and list the level and the name of that level (e.g. Harry sold $45M of widget A in Chile (level 1 of 1) in comparison to Dick who sold $40M of the same widget in Municip-A (level 3 of 4)).

Note that Angola's hierarchy is 4 levels deep and Dick is functioning on 1 level above the bottom-most level. Stats can be generated by different depths.

  Show sales for level 1 of each salesperson (e.g. sales for Chile, Angola etc.). Show sales for level 2 (e.g. sales for Province-A if Chile, or Municip-A if Angola etc.). Show all sales for a level named Province, or even more complex - show all sales for Provinces of countries who have it, or Municipalities of countries who have it.

The denormalized data warehouse structure can also be used, like you mentioned in the question. In a dimensional table, add sale person's name and assign level1, level1type, level2, level2type etc. fields. Tom's level 1 would be Chile and type would be Country. Level 2 would be Region-A and type would be Region etc. This kind of structure can be easy for data extraction if statisticians are looking to hone down on level 1 or 2 or such. We have just added to complexity of adding more columns as we discover hierarchy of different countries but the benefit is that the data is easy to visualize. A simple select statement can help us understand how Tom is functioning.

Sit down with statisticians and find out what they like to see and how they would like to use the data. Based on their ideas you might be able to build a better, perhaps a hybrid system.


SQL Server database backup(2012) restore on lower version(2008) We want to restore 2012 *.bak to 2008 sql server, we don't want to prefer import/export logical backup.

Looking forward to know downgrade procedure.

You CANNOT do this - there is no downgrade procedure - you just cannot attach/detach or backup/restore a database from a newer version (2012) of SQL Server down to an older version (2008) - the internal file structures are just too different to support backwards compatibility.

You can either get around this problem by

using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances
otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool
or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.


Query Joining on Column headings I have Survey results given to me in a massive table. (300+ columns)

A list of questions is provided in a table, and the Survey results in another.

Questions.

+-----+-----+---------------------------------+
| idx | QID |              QDesc              |
+-----+-----+---------------------------------+
|   1 | Q1  | Are you alive?                  |
|   2 | Q2  | Do you like pizza?              |
|   3 | Q3  | Do you like pineapple on pizza? |
+-----+-----+---------------------------------+

The answers from each person surveyed are entered on 1 line, each question/answer in it's own column.

+-----+----+-----+-----+
| idx | Q1 | Q2  | Q3  |
+-----+----+-----+-----+
|   1 |  1 | Yes | No  |
|   2 |  0 | No  | No  |
|   3 |  1 | Yes | Yes |
+-----+----+-----+-----+

I want to create a view on this which will be used in Excel Pivot tables. So I want the quest text in each row. Something like:

+-----+----------------+----------+--------------------+----------+---------------------------------+----------+
| idx |     Q1Desc     | Q1Result |       Q2Desc       | Q2Result |             Q3Desc              | Q3Result |
+-----+----------------+----------+--------------------+----------+---------------------------------+----------+
|   1 | Are you alive? |        1 | Do you like pizza? | Yes      | Do you like pineapple on pizza? | No       |
|   2 | Are you alive? |        0 | Do you like pizza? | No       | Do you like pineapple on pizza? | No       |
|   3 | Are you alive? |        1 | Do you like pizza? | Yes      | Do you like pineapple on pizza? | Yes      |
+-----+----------------+----------+--------------------+----------+---------------------------------+----------+

I feel like the answer should be a unpivot/pivot, but the thought is making my head explode. Over time i'll be loading more results and the questions and result columns could be added to, so i want it to be dynamic.

Is there a simple way to do this?

SELECT a.idx
     , q1.qdesc q1desc, a.q1 q1result
     , q2.qdesc q2desc, a.q2 q2result
     , q3.qdesc q3desc, a.q3 q3result
     , ...
FROM answers a
   , questions q1
   , questions q2
   , questions q3
   , ...
WHERE q1.qid=1
  AND q2.qid=2
  AND q3.qid=3
  AND ...

The number of questions/answers is dynamic, so use dynamic SQL for to build the query text.

PS. Do you really want to output the result table which contains 600 columns?


Why is my plan guide not being used? We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ignores the non clustered index on the search column. An example query below:

select top 100 *
from   [dbo].[t_Call]
where  ID > 0
  and  throwtime between '3/20/2014 7:00:00 AM' and '3/24/2014 6:59:59 AM'
order by id

The ID column is clustered Index and Throwtime has nonclustered index. In this case, we noticed that ordering by throwtime instead of ID changes the query plan and nonclustered index is used.We are also planning to archive some of the old data (it currently has 20 mln rows!!). But making these changes in application is going to take some time and I need to find a way to make reports run reasonably fast, without making changes at the application level (oh well, such is life!).

Enter plan guide. I created the below plan guide with a nonclustered index query hint and for some reason, the nonclustered index is still not used. Am I missing something?

EXEC sp_create_plan_guide
@name = N'[prod2reports_callthrowtime]',
@stmt = N'select top 100 *
          from   [dbo] . [t_Call]
          where  ID > @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID',
@type = N'SQL',
@module_or_batch = N'select top 100 *
                     from   [dbo] . [t_Call]
                     where  ID > @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID',
@params = N'@0 int, @1 datetime, @2 datetime',
@hints = N'OPTION (TABLE HINT( [dbo] . [t_Call],
                   INDEX(IDX_NC_t_call_ThrowtimeProblemCodes)))'
GO

The query must match EXACTLY, including whitespace. I suggest you get the query from the cache, and create it from that rather than entering it any other way.


How does functional index in MySQL 8.0.13+ improve query performance? Quoting from 13.1.15 CREATE INDEX Syntax:

  MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values.

See "Functional Key Parts" section of that manual page for detail. Basically, it enables us to create an index consisting of complex expression values derived from columns rather than mere column values (or their prefixes).

However, on a following part, that page also states:

  Functional indexes are implemented as hidden virtual generated columns [...]

In 13.1.20.8 CREATE TABLE and Generated Columns, it says:

  The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:

  VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.

  [...]
  STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.

So it looks like (at least to me) the functional index is not stored at all, and only built on the fly during executing a query. Is this how functional index is used? If this is true, how could this "virtual" index improve query performance (I mean even the index itself has to be built during query)?

So it looks like (at least to me) the functional index is not stored at all, and only built on the fly during executing a query. Is this how functional index is used?

Please notice that columns are virtual, the indexes are as real ("materialized") as any other index. That means that indeed, if you request data from the table from a virtual column, it is generated at the moment, but if you use the index, it will be pregenerated on creation, and updated on every insert/update/delete.

That means that if you have a query such as this one:

SELECT * FROM revision WHERE substr(rev_timestamp, 5, 2) == '09'

(get all rows generated in september of any year)

You don't need to create a separate real column with substr(rev_timestamp, 5, 2), you can create a virtual one (which will take no more table space) and generate a real index on it, so you can run:

SELECT * FROM revision WHERE virtual_col == '09'

which should use the new index. Of course the index will take some space, but any index does (and modifying non-unique indexes after UPDATEs is very fast on MySQL). Also think that in most cases you don't need to index the whole column- this is very useful and optimized to index optional fields on JSON type columns without duplicating the whole, large content field.