Why is SQL Server consuming more server memory? SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to set the maximum limit for SQL Server. This was done and much improvement is gained. I want to know why if the maximum memory value is not set why SQL Server keeps consuming the resources

SQL Server will consume as much memory as you will allow it.  By default, that number would encompass 100% of your numerical memory on your machine.  That's why you're seeing what you're seeing.  If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory.  Then you have SQL Server and the OS battling for resources, and it'll always result in poor performance.

When you set the max server memory configuration limit, you are limiting how much SQL Server can allocate for the buffer pool (virtually where it stores data pages and the procedure cache).  There are other memory clerks within SQL Server, so for your particular version (2008 R2 and below), max server memory just controls the buffer pool.  But this is always going to be the biggest memory consumer.

TechNet Reference on the Effects of min and max server memory

  The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine.

As far as the question how much memory should I leave for the OS, that is a commonly debated metric.  It really all depends, mostly on what other processes are running on the server.  Provided it's a truly dedicated server (and that's almost never the case).  I like to see at least 4 GB memory for the OS (and even more on big-hardware boxes).  But most importantly, monitor how much unused memory there is.  Be liberal, and give SQL Server more memory if you're noticing a lot of available (can be monitored through perfmon) and unused memory (of course, always leaving a small buffer for those corner situations).


Best way to store data in tables I've been thinking on how to store data efficiently in database tables, I'll give the examples in visual look. See pictures below.

I've come to think to store data in database using compressed values row with the thinking of to save a lot of rows to remove duplicate page_id's

The other one is the usual that I've seen, I separate row_id's per page_id's.

The one I'm concerned is, which is the best way or am I doing it wrong on my first way storing data.

Definitely, in a Relational Data Management System, you SHOULD use relationships between your entities.

A simple example query where you will be stuck by using the first option :

  Select all users and role name with roles 1 or 11

You will have to perform character to character searches and comparisons that are much less efficient than relational integer search and comparisons beacause the latter are indexed, and algorithms to perform them are much more sophisticated than pure string compare.

Unless you are really constrained (and even if so I would say...) use table relations, referential integrity constraints, indexes and so on, they will save your time !


Cancel update if no columns changed How could SP be modified the way it will execute update only in case any updated value is different?

Assume we have a table

CREATE TABLE students
(
    StudentId INT PRIMARY KEY,
    Name nvarchar(255) NULL
 )

|----|------|
| Id | Name |
|----|------|
| 1  | Jane |
|----|------|
| 2  | John |
|----|------|
| 3  | NULL |
|----|------|

and the single student update stored procedure

[dbo].[student_update] (@StudentId int, @Name nvarchar(255)) AS
update [dbo].[students]
set [Name] = @Name
where [StudentId] = @StudentId

So again, how this could SP be modified the way it will execute update only in case @Name does not equal to [Name]?

Please take into account that [Name] could be NULL

DECLARE @areFieldsDifferent = CASE WHEN (
([NAME] is null and  @Name is not null) OR
([NAME] is not null and  @Name is null) OR
([NAME] <> @Name)
)
then 1 ELSE 0 END;

But how could be compared @Name with [Name] from Select * From Students where StudentId = @StudentId?
And how to use it to not call update statement?

Don't think about "not calling the update statement." If you have to check the existing value in the table, you have to read the row, whether you're going to update it or not. Then, if you have to update it, you have to read the row again first. How this could possibly be more efficient than just trying to update the row is beyond me.

You can shorthand all that is null/is not null syntax if you can pick a string that can't possibly be a valid name:

UPDATE dbo.students
  SET [Name] = @Name
  WHERE StudentId = @StudentId
  AND ( COALESCE(Name, N'한') <> COALESCE(@Name, N'한') );
  -----------------------^ obscure character that can't be a valid name

Otherwise:

UPDATE dbo.students
  SET [Name] = @Name
  WHERE StudentId = @StudentId
  AND
  (
        Name <> @Name
        OR ( Name IS NOT NULL AND @Name IS NULL )
        OR ( Name IS NULL AND @Name IS NOT NULL )
  );

If it really is a hard requirement to not fire the UDPATE if it will affect 0 rows, then you need to take a less efficient path and locate the row twice when the update is going to affect a row.

BEGIN TRANSACTION;

IF EXISTS
(
  SELECT 1 FROM dbo.students WITH (UPDLOCK, HOLDLOCK)
  WHERE StudentId = @StudentId
    AND
    (
      Name <> @Name
      OR ( Name IS NOT NULL AND @Name IS NULL )
      OR ( Name IS NULL AND @Name IS NOT NULL )
    )
)
BEGIN
  UPDATE dbo.students
  SET Name = @Name
  WHERE StudentId = @StudentId
    AND
    (
      Name <> @Name
      OR ( Name IS NOT NULL AND @Name IS NULL )
      OR ( Name IS NULL AND @Name IS NOT NULL )
    );
END

COMMIT TRANSACTION;


Postgresql sql import Permission denied! Can't import db from sql file

[user@localhost ~]$ sudo -u postgres psql
[sudo] password for user:
could not change directory to "/home/user"
Password:
psql (9.2.24)
Type "help" for help.

postgres=# \c db
You are now connected to database "db" as user "postgres".
rob=# \i /home/user/Downloads/dump.sql
/home/user/Downloads/dump.sql: `Permission denied`

[root@localhost user]# ls -Z /home/user/Downloads/dump.sql
-rw-rw-rw-. postgres postgres unconfined_u:object_r:user_home_t:s0 dump.sql

Q: Why Permission denied? How can I fix it?

CentOS 7.2.1511

Try to move the file into /tmp folder, I hope this will work.

In PostgreSQL you run into error with having permission on the the file but not all the folders/directories before it.


Upstart script in ubuntu for mongodb I am new to linux and mongodb.
I've already installed mongodb on ubuntu but I want to run one more instance on different port.
As I understood from other questions and forums , there must be upstart script.
https://stackoverflow.com/questions/7300109/ubuntu-start-upstart-second-instance-of-mongodb

But I can not find or locate an upstart script on my server.If I create a upstart script , where should I locate it to start as a service.

or is there any other proper and easy way to start another instance of mongodb on the same server but different port.

Are you running from the 10gen repository or from the default Debian/Ubuntu repo? I recommend using the official 10gen repository.

Check this link out - [10gen MongoDB How-To Install on Ubuntu:] http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. It is best to uninstall the previous mongodb installation prior to this change, which will also require you to modify your repository source (in /etc/apt/source.list) but this is also detailed in the link above. I recommend using upstart over sysvinit and the processed is outlined in the How-To.

With the 10gen Ubuntu set-up, the configuration file is in /etc/mongodb.conf.

There are several ways that you can you can run a separate mongod process, one would simply be running it from the cli -

sudo -u mongodb mongod --dbpath /var/tmp/mongotest --logpath /var/tmp/mongotest_log --port 3001 &

which will produce (using 'ps')

mongodb   2210  3.3  1.5 259012 15300 pts/0    Dl   11:48   0:00 mongod --dbpath /var/tmp/mongotest --logpath /var/tmp/mongotest_log --port 3001

and you can connect via the cli -> mongod --port 3001

Another way is creating a copy of /etc/mongodb.conf -

sudo cp /etc/mongodb.conf /etc/mongodbnew.conf and editing the following lines in the new file -

# mongodbnew.conf

dbpath=/var/lib/mongodbnew

#where to log
logpath=/var/log/mongodb/mongodbnew.log

port = 27018

I have changed the dbpath (where the mongodb files are stored) from /var/lib/mongodb to /var/lib/mongodbnew; the log path has changed from mongodb.log to mongodbnew.log and the port has changed from the default of 27017 to 27018 (you will also have to remove the # to uncomment the line).

I also changed the top line to reflect the new name of this configuration file.

You will also have to create the data directory because it won't exist and without it, the mongod process won't start and ensure that the owner and group is mongodb -

sudo mkdir /var/lib/mongodbnew
sudo chown -R mongodb:mongodb mongodbnew/

Additionally, create and change the persmissions of your log file -

sudo touch /var/log/mongodb/mongodbnew.log && sudo chown mongodb:mongodb /var/log/mongodb/mongodbnew.log

To start your new mongod process (in the background) from the cli, type

sudo -u mongodb /usr/bin/mongod --config /etc/mongodbnew.conf &

which send the following to the screen -

"all output going to: /var/log/mongodb/mongodbnew.log"

Check that the mongod processes are running with "ps" -

sysadmin@ubuntu:/var/lib$ ps auwx | grep mongo | egrep -v 'grep|sudo'
mongodb    921 11.3  1.5 627672 15608 ?        Ssl  10:56   4:30 /usr/bin/mongod --config /etc/mongodb.conf
mongodb   2137  7.8  1.5 627672 15880 pts/0    Sl   11:36   0:00 /usr/bin/mongod --config /etc/mongodbnew.conf

To verify that you can connect, run the Mongo shell -

$ mongo --port 27018
MongoDB shell version: 2.0.5
connecting to: 127.0.0.1:27018/test
>

With lsof, you should now see your two mongod processes, the original bound to 27017 and the new one bound to 27018.

$ sudo lsof -i :27017
COMMAND PID    USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mongod  921 mongodb    6u  IPv4   9066      0t0  TCP *:27017 (LISTEN)
sysadmin@ubuntu:/var/lib$ sudo lsof -i :27018
COMMAND  PID    USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mongod  2137 mongodb    6r  IPv4  11923      0t0  TCP *:27018 (LISTEN)

To ensure that the two processes run at start-up, copy the original init configuration file -

sudo cp /etc/init/mongodb.conf /etc/init/mongodbnew.conf

which should result in two files such as -

$ ls -lart /etc/init/mongo*
-rw-r--r-- 1 root root 536 May  8 15:51 /etc/init/mongodb.conf
-rw-r--r-- 1 root root 554 Jun 11 11:43 /etc/init/mongodbnew.conf

Edit the new mongodbnew.conf file using vi or whatever you prefer to look like this -

## Ubuntu upstart file at /etc/init/mongodbnew.conf

limit nofile 20000 20000

kill timeout 300 # wait 300s between SIGTERM and SIGKILL.

pre-start script
    mkdir -p /var/lib/mongodbnew/
    mkdir -p /var/log/mongodbnew/
end script

start on runlevel [2345]

stop on runlevel [06]

script
  ENABLE_MONGODB="yes"
  if [ -f /etc/default/mongodbnew ]; then . /etc/default/mongodbnew; fi
  if [ "x$ENABLE_MONGODB" = "xyes" ]; then exec start-stop-daemon --start --quiet --chuid mongodb --exec                /usr/bin/mongod -- --config /etc/mongodbnew.conf; fi

    end script

I have tested this on Ubuntu 12.04 and it appears to work well, including after a reboot. I haven't run it in production obviously. Apologies, if there's any typo's above but there's a lot of information and I may have missed something.

Finally, here's a link on Replica Sets that might help you as it has come examples on starting multiple mongod instances from the cli - http://www.mongodb.org/display/DOCS/Replica+Set+Tutorial.


Postgres, levenshtein distance and nested queries I have a DB Table with 3,000,000 rows or more like that:

|ID|T4|T16|T64|TL

where ID =INTEGER (PK)
and T4,T16,T64,TL are text fields. Each one is larger in length than the previous, i.e., LENGTH(T4)

I want to compare one row of this table with a SELF JOIN with all other rows based on the levenshtein distance (fuzzystrmatch extension) between TL of the compared rows and get the best-k results that correspond to smaller levenstein distance.

SELECT n1.*,n2.*
FROM (SELECT * FROM dbtable WHERE ID =110) n1,
dbtable n2
ORDER BY levenshtein_less_equal(n1.TL,n2.TL,LENGTH(n1.TEXT)/4) LIMIT 100;

Of course this would be extremely slow because it will have to do 3,000,000 levenshtein calculations.

In order to accelerate queries, I have created the artificial "hashes" (fields T4,T16,T64). So, I want to check initially levenshtein distance between fields T4. If this distance between T4 is within some limit then I check levenshtein distance between T16 and so-on. This way in each step i would need to calculate fewer levenshtein distances that by the naive approach.

The nested query is like that:

SELECT n5.*,levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
FROM
(SELECT n4.*
FROM
(SELECT n3.*
FROM
(SELECT
n1.id AS n1_id,
n1.t4 AS n1_t4,
n1.t16 AS n1_t16,
n1.t64 AS n1_t64,
n1.tl AS n1_tl,

n2.id AS n2_id,
n2.t4 AS n2_t4,
n2.t16 AS n2_t16,
n2.t64 AS n2_t64,
n2.tl AS n2_tl
FROM (SELECT id,t4,t16,t64,tl FROM dbTable WHERE id=110) AS n1,dbTable AS n2
WHERE
n1.id<>n2.id
AND levenshtein_less_equal(n1.t4,n2.t4,LENGTH(n1.t4)) <= LENGTH(n1.t4)/2
) n3

WHERE levenshtein_less_equal(n3.n1_t16,n3.n2_t16,LENGTH(n3.n1_t16)/2) < LENGTH(n3.n1_t16)/2
) AS n4

WHERE levenshtein_less_equal(n4.n1_t64,n4.n2_t64,LENGTH(n4.n1_t64)/2) < LENGTH(n4.n1_t64)/2)
AS n5
ORDER BY levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
LIMIT 100;

The query is much faster than the original naive since it only needs to access 112851 rows for TL but:

The EXPLAIN PLAN:

Limit  (cost=872281.90..872282.15 rows=100 width=1168)
  ->  Sort  (cost=872281.90..872564.03 rows=112851 width=1168)
        Sort Key: (levenshtein_less_equal(dbTable.tl, n2.tl, (length(dbTable.tl) / 4)))
        ->  Nested Loop  (cost=0.00..867968.81 rows=112851 width=1168)
              Join Filter: ((dbTable.id <> n2.id) AND (levenshtein_less_equal(dbTable.t4, n2.t4, length(dbTable.t4)) <= (length(dbTable.t4) / 2)) AND (levenshtein_less_equal(dbTable.t16, n2.t16, (length(dbTable.t16) / 2)) < (length(dbTable.t16) / 2)) AND (levenshtein_less_equal(dbTable.t64, n2.t64, (length(dbTable.t64) / 2)) < (length(dbTable.t64) / 2)))
              ->  Index Scan using dbTable_pkey on dbTable  (cost=0.00..8.60 rows=1 width=584)
                    Index Cond: (id = 110)
              ->  Seq Scan on dbTable n2  (cost=0.00..699529.82 rows=3046982 width=584)

As you see the problem is that the query optimizer joins / collapses the levenshtein distance calculations between T4, T16, T64, where without collapsing it would be probably faster, because fewer levenshtein distances would need to be made for T64. I used  SET from_collapse_limit=1; to avoid collapsing at the same session but nothing changed. Is there a way to enforce this hierarchical functionality in a Postgres query; Keep in mind that none of the T4...TL are indexed because I do not think there is an index to accelerate levenshtein distances. Any suggestions to further improve performance?

I suggest to use a trigram index provided by the additional module pg_trgm.  Combine that with the length of the string to get a valid pre-selection.

Drop the columns T4,T16 and T64 (faster in a single statement), and run VACUUM FULL or CLUSTER.
Install pg_trgm. Details here:

Full Text Search With PostgreSQL

Create a GiST index on tl and length(tl).

There are a details to be observed. Related answers on dba.SE involving pg_trgm:

https://dba.stackexchange.com/search?q=pg_trgm


Not sure if this is the best solution, but this does the job. The only requirement is that each sensor has a least 2 values.

select *
from sensor_data as t8
inner join (
  select t6.sensor_id, min(t6.date) as date
  from sensor_data t6
  inner join (
     select t4.sensor_id, max(t4.date) as before_last_date
      from sensor_data t4
      left join (
          select t2.sensor_id, t2.val as last_val, t2.date as last_date
          from sensor_data t2
          inner join (
              select sensor_id, max(date) as last_change
              from sensor_data
              where 1
              group by sensor_id
          ) as t1
          ON t2.sensor_id = t1.sensor_id AND t2.date = t1.last_change
      ) as t3
      ON t3.sensor_id = t4.sensor_id
      WHERE t4.val != last_val
      GROUP BY t4.sensor_id
  ) as t5
  ON t5.sensor_id = t6.sensor_id
  WHERE t5.before_last_date < t6.date
  group by t6.sensor_id
) as t7
on t7.sensor_id = t8.sensor_id and t7.date = t8.date


specify delimiter in openrowset bulk insert I'm trying to insert data from CSV files into tables using a cursor and open rowset bulk insert.
this works for me 99% of the times but if one of the fields in the csv files contains a comma( , ) the data import is ruined and all the columns get imported one column after their place.
For example it should look like this:

timestamp   username   name         IP       title
20190331    ABCD12G    david hertz  1.1.1.1   null

But if i have a comma in the name column(for example) it will look like that:

timestamp   username   name      IP      title
20190331    ABCD12G    david    hertz   1.1.1.1

the csv example:

username,name,ip,title
ABCD12G,david,hertz,1.1.1.1,''

This is the syntax i'm using:

insert into [player table]
select 20190331,* FROM  OPENROWSET(BULK  'D:\folder\2019\03-
31\Player_statistics.csv', FIRSTROW = 2,
FORMATFILE='D:\folder\test\xml\Player_statistics.xml')
 as t1

how can i change this to ignore the comma in the middle of a word?(i can't limit the CSV since its taken from another client that will not limit characters.

the xml i'm using for the import with openrowset:

when you perform regular bulk insert you have an option to specify the delimiter
fielterminator = '","'  , I could not find something like that in openrowset.

What can help fix this issue?

but if one of the fields in the csv files contains a comma( , )

If all lines contains a comma, the solution can be

<ROW>
  <COLUMN SOURCE="1" NAME="username" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="name1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="name2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="IP" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="title" xsi:type="SQLNVARCHAR"/>
</ROW>

INSERT INTO [player table]
SELECT 20190331,
       username,
       name1+','+name2,
       IP,
       title
FROM  OPENROWSET( BULK 'D:\folder\2019\03-31\Player_statistics.csv',
                  FIRSTROW = 2,
                  FORMATFILE='D:\folder\test\xml\Player_statistics.xml') t1

If only some lines contains commas, and none title is null/empty, then try

INSERT INTO [player table]
SELECT 20190331,
       `username`,
       CASE WHEN title IS NULL
            THEN name1
            ELSE name1+','+name2 END,
       CASE WHEN title IS NULL
            THEN name2
            ELSE IP END,
       CASE WHEN title IS NULL
            THEN IP
            ELSE title END
       IP,
       title
FROM  OPENROWSET( BULK 'D:\folder\2019\03-31\Player_statistics.csv',
                  FIRSTROW = 2,
                  FORMATFILE='D:\folder\test\xml\Player_statistics.xml') t1

If some title is null/empty, I cannot find simple way to distinguish.

UPDATE

The solution can be: divide your CSV on two fields only - username and the whole slack, the slack then can be divided to separate fields using common string functions in the SELECT part of importing query (maybe with using intermediate variables in chain calculations).


What is the purpose of table partitioning I was advised by a senior DBA that you should never use Partitioning, instead use schema to group data. I am talking on large databases. I am not convinced with this, please advice

An offline application deployed in 350+ locations was converted into an Online Application Single Database (10 Million rows).

They created schema's for 350+ locations and created tables inside these respective schemas.

When I was asked to look into the issues, I wondered why this setup and was because they were advised by Senior DBA to go like this (My Concerns were, How to perform search operations across all schema, how to manage any changes in table 350+ is huge, if it is a single table with partitioning things will become easy)

When I consulted the senior DBA regarding this and why he didnt propose Table Partitioning Instead of different schema, "He said partitioning is logical" and will not work, if you split it into schema the performance will be better because database operations will be limited to that schema.

These statements confused me and thus seeking your views and experiences

When to use many schemas?

In PostgreSQL databases of multi-tenant setup, it is not unusual to see a separate schema for every tenant.  This serves mostly to separate data of the different tenants, as schemas give a very convenient way of setting security/access boundaries.  This means, if I used such a setup, customer A would not have access to Customer B's data.  If necessary, it is easy to set up overarching roles that can work across the different schemas (doing maintenance, producing reports, and so on).

In these scenarios, one might have data that is common across all tenants.  I would store these in a common(ly accessible) schema, to prevent unnecessary duplications, thus making maintenance a bit easier.

Addressing one of your concerns, doing cross-schema queries is easy when you know which schemas to go for.  If not, it might be tricky.  However, maintenance in such cases might need some (self-built) infrastructure (as a script to roll out table changes on all tables named your_table one by one) - but when it's already in place, the single tasks are not so complicated.

Do schemas help with performance?

On the other hand, having 350+ schemas for 10M rows is definitely not about performance.  With 10M (not too wide) rows on a decent machine one would not expect serious performance issues in the first place.  If they still appear, there are other means than partitioning that one should try first (meaning different indexing strategies, usually).  A meaningful partitioning scheme for such data is very hard to come up with: 350+ partitions are just that much a pain in the ass as 350+ schemas.  Not to mention that the per-partition data volume will be small (like 30k rows), and in certain cases planning the queries would take a hit - especially, when you try to have multiple tables partitioned the same way.


How to connect to root with Oracle Developer VM I have the Oracle Database App Development VM (12c) and am trying to create a new container. Specifically:

conn / as sysdba
create pluggable database pdborcl admin user system identified by oracle FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/', '/u01/app/oracle/oradata/cdb1/pdb2'');

However, I cannot connect to the root container. The above conn spits out:

  URL           = jdbc:oracle:oci8:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )
  Error Message = ORA-01017: invalid username/password; logon denied
  USER          =
  URL           = jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )
  Error Message = ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

And sqlplus / as sysdba returns:

ERROR:
ORA-01017: invalid username/password; logon denied

Logging in with sqlplus system as sysdba logs into the connection ORCL.

ALL of the passwords are 'oracle'

That includes:
 OS: root
 OS: oracle
 Database: SYS
 Database: SYSTEM
 Database: HR

oracle@vbgeneric ~]$ sqlplus sys as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri May 11 11:43:25 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

However, you're running into something different.

And it's discussed here.

  Using TWO_TASK. A third way is by defining the TWO_TASK operating
  system variable to point to the PDB you want to connect to:

[oracle@prosrv1 schema]$ export TWO_TASK=PDB1

And, then you can connect as usual without giving a connect string:

[oracle@prosrv1 schema]$ sqlplus system/oracle

I prefer this last approach because it simulates a database connection prior to introduction of the PDB. If you connect to a specific PDB the majority of the time, all you have to do is to set this variable in the shell initialization file (e.g. .profile in case of bourne shell) of the user so that the variable is automatically set when you log in. If you need to know which PDB you are connected right now in SQL*Plus, just use the following command: To show which container (or, the PDB) you are connected to:

SQL> show con_id
CON_ID
------------------------------
5

SQL> show con_name
CON_NAME
------------------------------
PDB1


Selecting with varbinary(max) criteria (in the where clause) Basic info

Database: SQL Server Express 2008 R2
Client: SQL Server Management Studio

Backround (skip if not interested):

A project I'm maintaining uses an ORM, which apparently stored my enum values (which inherit from Byte) into binary serialized .Net objects stored in a varbinary(max) field.
I only found out this was happening after a new requirement emerged dictating my code to run under medium trust. Since the .Net binary formatter needs full trust to be called, it started crashing on the enums.

To clean the mess up I need to create migration scripts that will convert these (varbinary(max)) values back to integer values. There are only a handful of different values so it shouldn't be a big problem (I thought).

The problem:

I am able to get string representations of the blobs when selecting:

SELECT BinValue FROM MyTable where Type = 'Object';

It returns a string '0x...(an array of hexadecimal values)'.

But when I try to select on the column using copy-and-paste making sure I have the exact binary equivalent:

SELECT ItemId FROM MyTable WHERE Value=convert(varbinary(max), '0x...')

it does not return any records.

So is it possible to solve this using a client (such as Management Studio)?

If so, what would be the correct syntax?

Just do

SELECT ItemId
FROM   MyTable
WHERE  Value = 0xAAFF

You are converting the varchar representation to varbinary(max) which is not correct.

SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.


DynamoDB - Multiple range keys Is it possible to have multiple fields as range key?

Say I have a table where each row is uniquely identified by <A,B,C>

 -------------------------------
 A   |   B   |  C  |  D   |  E  |
 -------------------------------

where A is the primary hash key

and I want B and C to be the primary range keys.

  How can I have more than 2 fields as a primary key in DynamoDB?

Put a copy of B and C in the range field (as an extra column) and make that your range key like "B_C" and you can still also have separate columns for B and C if you need.


Starting mongod daemon with a config file for a shard In a Linux machine, I changed the mongod.service file to run a config file I created.

ExecStart=/usr/bin/mongod --config /etc/some_name.conf

When I start mongod with this config file manually, it runs fine. Yet when I use this config file to run the mongod daemon, it fails.

I did run systemctl daemon-reload after the change, yet still failed.

Anybody also dealt with this issue?

Update

I've isolated the error in my log file to this line:

I STORAGE  [initandlisten] exception in initAndListen: IllegalOperation:
Attempted to create a lock file on a read-only directory: /data/db, terminating
I CONTROL  [initandlisten] now exiting

I've tried changing the ownership of the directory /data and /data/db to mongodb:mongodb but doesn't fix this error. Also changed the permissions to the directories with sudo chmod -R 775 /data but still same error when I run:

sudo service mongod restart
sudo service mongod status

Status is still failed and same error in log file.

So, you created mongod.service file, did you remember tell to systemd that you are forking that pid to background? If not, systemd will kill that process soon after start.

Then there is that what @stennie wrote, if you start even once mongod as root user, some of the files are created as root:root and after that, nobody else (like user mongod) can not start mongod-process. So, what are those files and directories, which ownership must be set to mongod (or what ever user you use to start mongod-process)

mongod.log /var/log/mongod/-directory (or what you wrote at config file)
/var/run/mongodb/mongod.pid (or what ever is set at config-file)
directory (and files) where your data is.

Log file is important, because after mongod-process can write to it, it will tell what went wrong if something went wrong...


Using linked server from Windows 10 PC I have been using SQL Server linked servers for years, and they have been working fine. But now when I try to run a query (on a machine with Windows 10) using linked server I get this error:

  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

If I then run the same query on my older machine without Windows 10, I have no problems and never ever had any problems.

Now the funny part begins: If I run the query from my old PC (logged on with the same credentials) and then within 5 minutes run the same query from my Windows 10 machine - there is no problem!

It's like if my old machine establishes a connection that is open for about 5 minutes and then times out, and my Windows 10 machine cannot find out how to establish that connection.

Any ideas?

I am just running a query in SSMS logged onto server A querying data from a database on server B, and doing the same thing on both machines.

The error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" means that SQL Server is not running under a service account on the new PC.  Open the control panel, go to administrative and last open services.  Look for SQL Server and see what it is logging in as.  Create a Active directory account (sort of like an account for a person, except nobody should use it) and put the "domain\serviceaccount" and password into the login as tab.  Be sure to grant "domain\serviceaccount" access to the remote server.


Challenge. Split database 500 records in two smaller datasets, randomly I have a database with 500 records. I want to split these records to 75% and 25% *randomly*in order to use the different datasets for training and testing to machine learning algorithms. Does anyone knows how to do that using an sql query?
ps. I use mysql.

In addition I find this ...
select ...
from ...
where ...
ORDER BY rand() LIMIT 150 but this gives me only the first dataset

Thanks in advance

Easiest solution would be to store the datasets into tables.
After you use your query to get the first dataset:

 INSERT INTO dataset1 SELECT * FROM foo ORDER BY rand() LIMIT 125

then do:

 INSERT INTO dataset2 SELECT foo.* FROM foo
   LEFT JOIN dataset1 d1 ON foo.key=d1.key WHERE d1.key IS NULL


What Happens when Cartesian Product is applied to Relations with same attribute name I understand that the Cartesian product (X) operation on two databases does not need to be UNION compatible. So,if there is a same attribute called name in two relations R and S where  name in R is the first name and name in S is the second name, how can the related values be identified by the following selection operation?

Q=RxS

I want to get the set of tuples whose firstname = lastname. How am I supposed to write the selection statement?

σ Name=Name(Q)

Will there be a problem using the same attribute name in the selection operation?

I'm just referring here to the problem of writing the relational expression (selection) when same attribute name is used.

By way of example, using SQL Server, the simplest answer is an INNER JOIN:

USE tempdb;
GO
CREATE TABLE Names1
(
    FirstName varchar(50)
);
CREATE TABLE Names2
(
    LastName varchar(50)
);
INSERT INTO Names1 (FirstName) VALUES ('Max');
INSERT INTO Names1 (FirstName) VALUES ('Vernon');
INSERT INTO Names2 (LastName) VALUES ('Max');
INSERT INTO Names2 (LastName) VALUES ('Vernon');

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.FirstName = N2.LastName;

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;

The results:

However, if you are prepared to complicate your query unnecessarily, you could do the following:

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.FirstName = n2.LastName;

Results:

In your question, you ask if using the same field names is going to be an issue; plainly it is not:

USE tempdb;
GO

CREATE TABLE Names1
(
    [Name] varchar(50)
);
CREATE TABLE Names2
(
    [Name] varchar(50)
);
INSERT INTO Names1 ([Name]) VALUES ('Max');
INSERT INTO Names1 ([Name]) VALUES ('Vernon');
INSERT INTO Names2 ([Name]) VALUES ('Max');
INSERT INTO Names2 ([Name]) VALUES ('Vernon');

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.[Name] = N2.[Name];

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.[Name] = n2.[Name];

Results:


Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service? Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service !?

I mean the two running as service not application

Thank you for your understanding

Yes,  it is possible and it is covered in documentation and in lot of FAQs like the one at iBase.ru

For the example at my development box I have co-installed FB 2.1.5 Win32 SuperServer (at default port 3050/tcp) and FB 2.5.2 Win64 Super-Classic (at a custom 3064/tcp)

There might be troubles with FB 1.x as it was using registry, but FB 2.x was made isolated and self-dependent.

One option is to download ZIPs and unpack them to different folders.  Then you have to run text window of Windows Command Prompt "As Administrator",  go into "bin"  subfolder and there are all those executables like server itself.  There also is "inst_svc" tool.  Running it with an option like "-?" would show you brief help. Focus on installing main service,  not installing Guardian (only needed on Win98, only shipped for legacy uniformity)  and giving non-default "instance name". Then go outside "bin"  and open "firebird.Conf" with any text editor like notepad. The documentation is within that file how to set non-default TCP port. That's all. Do the same for your another FB folder and you've done it.

Another option is to run two installers.  The 1st one would do all the described above things automatically. The second one would unpack files -  and ask you to do those configuring operations for the second copy manually. Just do it like described above.


Outage due to Replica Set Protocol Version upgrade? My production system has a replica set created in MongoDB v3.0, which still has protocol version undefined (i.e. version 0). It is now running v3.4 and I intend to upgrade to replica set protocol version 1, but I'm wary of what will happen while the replica set reconfigures itself.

Is there any risk of an outage at all? Either if all nodes are running normally, or if any nodes have problems during the change? The documentation warns that reconfiguration can trigger the current primary to step down in some situations but doesn't say which situations.

Apart from the protocol version, I am making no other configuration changes; for example, the member priorities will be unchanged so the same priority node will hopefully remain primary.

The current config is:

rsMyApp:PRIMARY> rs.conf()
{
  "_id" : "rsMyApp",
  "version" : 26139,
  "members" : [
    {
      "_id" : 4,
      "host" : "DB2:27017",
      "arbiterOnly" : false,
      "buildIndexes" : true,
      "hidden" : false,
      "priority" : 1,
      "tags" : {},
      "slaveDelay" : NumberLong(0),
      "votes" : 1
    },
    {
      "_id" : 5,
      "host" : "DB3:27017",
      "arbiterOnly" : false,
      "buildIndexes" : true,
      "hidden" : false,
      "priority" : 1,
      "tags" : {},
      "slaveDelay" : NumberLong(0),
      "votes" : 1
    },
    {
      "_id" : 1,
      "host" : "DB1:27017",
      "arbiterOnly" : false,
      "buildIndexes" : true,
      "hidden" : false,
      "priority" : 2,
      "tags" : {},
      "slaveDelay" : NumberLong(0),
      "votes" : 1
    }
  ],
  "settings" : {
    "chainingAllowed" : true,
    "heartbeatIntervalMillis" : 2000,
    "heartbeatTimeoutSecs" : 10,
    "electionTimeoutMillis" : 10000,
    "catchUpTimeoutMillis" : 60000,
    "getLastErrorModes" : {},
    "getLastErrorDefaults" : {
      "w" : "majority",
      "wtimeout" : 0
    }
  }
}

What that protocol change do, is change decision pattern what nodes do when new primary must be elected. At the situation where all SECONDARY nodes are up-to-date (not falling behind) and your current PRIMARY have higher priority than any of SECONDARY node, nothing will happen. The system continues as it was.

But afterward, if your current PRIMARY is unavailable, next primary is elected faster than it was with protocolVersion:0


SQL Server remote connection to 2nd instance impossible After installation of another SQL Server 2014 instance to a server where a default instance of SQL Server 2008 R2 is present, I cannot remote connect remote to the new named instance.

The default 2008 R2 instance uses TCP port 1433 set for all "IP(n)" settings.

Here is what I read / checked / tried:

Following this question: configure remote connection for multiple instances sql server 2008

I figured several settings and tried the following:

Server Security: Mixed
Server Connections Properties: Allow [checked]
SQL Server Browser started
Protocol Settings for the 2014 instance set as follows:

Shared Memory=Enabled,
Named Pipes=Disabled,
TCP/IP=Enabled

TCP IP Setting of new instance:

Enabled=Yes,
Listen All=YES,

TCP/IP Settings for IP(n):

Dynamic Ports=erased
TCP Port=erased

IPAll Setting:

Dynamic Ports = erased
Port=49172

I tried to remote connect using [IP]:49172\Instance and HostName:49172\Instance and HostName\Instance and [IP]\Instance without success.

I try to use SQL Server authentication, the user is available and is sysadmin and can connect local.

What am I missing here?

The solution was to restart the SQL Server Browser. Looks like my configuration was correct but has to be made before starting this service. After that I could access the 2nd Sql Server without problems, just using the name\instance.


How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema.  So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told MySQL Cluster scales much better.

How much data does it take to show a performance benefit to MySQL Cluster vs. an InnoDB schema?  Or, is there a better way to demonstrate MySQL Cluster's merits?

EDIT

Perhaps an important note:  My cluster is currently a heterogeneous cluster with 4 machines.  On each machine, I have given an equal amount of Data and Index Memory; 4GB, 2GB, 2GB, and 1GB respectively.  The machines are running i7's and are connected over a Gigabit Lan.  NumOfReplicas is set to 2.

EDIT

This application is a low-usage analytics database, which has roughly 3 tables >= 200M rows and 5 tables <= 10K rows.  When we use it, it takes 15 seconds to run our aggregate functions.  My boss asked me to research MySQL Cluster, to see if we could increase performance, since we thought aggregate functions could run pretty well in parallel.

MySQL Cluster stores indexed columns in memory, so the largest practical data size for an in-memory cluster is around 3GB.  If you store tables with non-indexed columns on disk, then you can scale the dataset further

As MySQL Cluster automatically shards your tables across multiple nodes, with each node being a master. This gives very high performance for OLTP workloads, especially those that are write-intensive.  To get the best levels of performance, you need to introduce load to the cluster, ie configure multiple API nodes, with each node configured with multiple threads to access the Cluster.

The latest 7.2GA release of MySQL Cluster includes new functionality called Adaptive Query Localization, which serves to push JOIN queries down to the data nodes, reducing network hops and therefore latency.

To get the best handle on performance, there is a Performance Guide published as follows (note, registration is required):
http://www.mysql.com/why-mysql/white-papers/guide-to-optimizing-performance-of-the-mysql-cluster/


What's the best way to diagram this "relation" looking for performance and|or disk space savings I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to achieve this.

Place a column download_file as VARCHAR(250) and set to NULL by
default (this because not all of the record will have a download
related)

Create a relation between cm_identifier_type and a new table cm_download_file:

Now, regarding performance, disk space savings, query savings and so on, how would you do that? What is your recommendation on this edge case?

Note: for the moment I am using MariaDB 10.1.x but this will be in a MySQL instance, probably 5.x or so I am not sure at all since I didn't got those details yet

If you have a lot of specific statistics about how often your optional columns will be null and how often the non-null columns need to be read (as opposed to your core, mandatory columns) then you could calculate space savings (or not) and at the very least you could derive some performance experiments to test each approach.

There is no rule of thumb for whether it is "better" to segregate optional columns into a separate table.

"Better" is a subjective term.  What is of value?  Disk space, CPU cycles, query response times, simplicity of code? You can't consider the relative merits of one approach over another without first considering what it is that you're trying to optimize.

There are a lot of reasons why you might want to move optional columns to a separate 1:1 related sub-table.  See my answer to this question for more discussion of these reasons.

In your case, since you're concerned about space, you need to keep in mind a few things about how data is physically stored:

Variable size strings don't take up much space if they are empty.
Every nullable column needs a bit to indicate whether it is null or not.
A separate table with optional columns needs to store the primary key of the main table again (as a foreign key) and there may be a separate index on this column as well.

There are a lot of competing influences on the matter of which takes less space and which performs better.  You need to consider:

How often is the optional data going to be missing?  If it's really rare then maybe you will save space by segregating it out.
How often do you need to read the optional data?  If you read it every time (whether it's there or not) then maybe joining for it all the time is less efficient.
On the other hand, if it isn't there very often and if you hardly ever read it, even when it is, then maybe it's more efficient to read shorter records containing only the mandatory columns?

Another thing to consider, perhaps even the most important thing to consider, is whether you are overthinking the issue by trying to pre-optimize.  Disk is pretty cheap.  CPU is pretty cheap.  Programmers are expensive.  Unless you need to be concerned about massive scale, maybe the best answer is the one that leaves you with the simplest (least buggy, easiest to maintain) code.


Mysql Trigger Insert row Another Table using by Barcode I Have 3 Tables:

1) CREATE TABLE `UrunPaketDetay`(
    `UrunPaketDetay` int NOT NULL AUTO_INCREMENT,
    `UrunPaketNo` int NULL,
    `Tarih` date NULL,
    `Barkod` varchar(14) NOT NULL,
    `Urun` varchar(50) NULL,
    `SunumSekli` varchar(50) NULL,
    `IslemeSekli` varchar(50) NULL,
    `PaketlemeSekli` varchar(50) NULL,
    `Kalibre` varchar(50) NULL,
    `Kilo` double NULL,
    `GlazeOran` varchar(50) NULL,
    `Uretici` varchar(190) NULL,
    `PaletKod` varchar(50) NULL,
    PRIMARY KEY (`UrunPaketDetay`)
)CHARACTER SET utf8;

2) CREATE TABLE `CkisEks`(
    `CikId` int NOT NULL AUTO_INCREMENT,
    `Tarih` date NULL,
    `Musteri` varchar(190) NULL,
    `TeslimatYer` varchar(50) NULL,
    `CikisSaati` time NULL,
    `AracPlakasi` varchar(18) NULL,
    `AracTel` varchar(16) NULL,
    `KonteynirNo` varchar(50) NULL,
    `PaletKod` varchar(12) NULL,
    `Kilo` double NULL,
    PRIMARY KEY (`CikId`)
)CHARACTER SET utf8;

3) CREATE TABLE `Ckis_Detay`(
    `CD_Id` int NOT NULL AUTO_INCREMENT,
    `CikId` int NULL,
    `Barkod` varchar(50) NULL,
    `Urun` varchar(50) NULL,
    `Kalibre` varchar(50) NULL,
    `Kilo` double NULL,
    `Uretici` varchar(50) NULL,
    `Musteri` varchar(190) NULL,
    `PaletKod` varchar(50) NULL,
    `Tarih` date NULL,
    PRIMARY KEY (`CD_Id`)
)CHARACTER SET utf8;

My Question:

I fill up my first table. After on 2nd table i call PaletKod row. PaletKod row autofill Kilo Row on 2nd table from 1st Table.

But i need when i fill PaletKod to my 2nd table how can i select Paletkod from first table and fill my 3rd table values (Uretici, Urun, Kilo, Kalibre ) ?

With Trigger ?

create trigger tr_au_CkisEks
after update on CkisEks
for each row
update UrunPaketDetay t1, Ckis_Detay t2
set t2.Uretici = t1.Uretici,
    t2.Urun = t1.Urun,
    t2.Kilo = t1.Kilo,
    t2.Kalibre = t1.Kalibre
where t1.PaletKod = NEW.PaletKod
  and t2.PaletKod = NEW.PaletKod;


Backup/restore using OPENDATASOURCE OR OPENROWSET Has anyone ever successfully taken a backup or restore to another server using OPENDATASOURCE/OPENROWSET? I am not able to use linked servers so I am exploring this idea.

You can automate all of below using SQL Agent.

There are many alternatives that you can use :

SQLCMD ==> sqlcmd -E -S server_name -q "backup command"
 You can use it with dynamic sql to connect to different servers for backup and restores.
PowerShell ==> There are tons of scripts found on internet that will tell you how to do it. SimpleTalk has -- Backup and Restore SQL Server with the SQL Server 2012 PowerShell
SSIS ==> Automate Database Restore to Remote Instance with SSIS

When doing backup - make sure to use compression in sql server 2008 R2 and up which is even supported in standard edition. Also to speed up restore, use Instant File Initialization.


how to pass outer query param into inner in MySQL 5.7 Now I am using this query in MySQL 5.7 to count login count:
select
(
    select sum(login_count)
    from (
        select avg(login_count) as login_count
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
) as login_count
from report_user a
group by user_id

when I calculate the inner login count, I just want to calculate by per user, then the user_id is specified, the result is correct. But when have many users in this table, the result is not correct. How to pass the outer user id into inner query in execute the query? I want this inner sql only calculate each user:
select sum(login_count)
    from (
        select avg(login_count) as login_count
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count

Using this sql solve my problem:
select
(
    select sum(login_count)
    from (
        select avg(login_count) as login_count,user_id
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
    where login_count.user_id = a.user_id
) as login_count
from report_user a
group by user_id


Master-Master on a VIP with a multiple Slave Is it possible to have a MySQL set-up that is running on a 2 Active master running over a VIP/Keep alive. Then having a multiple slaves that connecting to the VIP of the 2 master for the replication?

(The application is only connected to the VIP of that 2 master and any write can accept on any node, if master1 is busy, then master 2 will take the new write to the database and replicate it to the master1).

So, that if ever that there's an issue with the master1 still the master2 is running and the replication is also running smoothly as it is connected with the VIP of the 2 master.

Any inputs are really and highly appreciated. Thank you very much in advance!

Do not connect a Slave to a pair of Masters via a VIP.

There is nothing to keep the binlogs synced up, so a Slave must communicate with only one Master.  (Multi-source Slave is a different topic.)

You could, however, have half your slaves connected to one Master, and half to the other.  Then if either Master goes down, you lose only half the Slaves.

If you are trying to limit the configuration to only 3 servers, then consider Galera (in PXC or MariaDB).  It has a lot more advantages than M<->M->S.

Even with multiple Slaves, consider Galera with 3 nodes, then evenly distribute the slaves off the Cluster nodes.  Now a single Master failure will lead to losing only 1/3 of the Slaves.

(Actually, you don't lose the Slaves of a dead Master, but they do get stale.)


ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES I'm trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the below error:

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

When I open up the backup at line 11084 here is what I get:

LOCK TABLES `sessions` WRITE;
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
INSERT INTO `sessions` VALUES ...
-- MySQL dump 10.11
--
-- Host: localhost    Database: drupal
-- ------------------------------------------------------
-- Server version       5.0.77-log

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `search_index`
--

DROP TABLE IF EXISTS `search_index`;        <=== LINE 11084
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

I checked the other statements and normally INSERT statements are followed by UNLOCK TABLES;. Accordingly if I make the below modifications to the mysqldump file the problem is fixed:

INSERT INTO `sessions` VALUES ...
UNLOCK TABLES;                   <=== ADDED LINE
-- MySQL dump 10.11
[...]
DROP TABLE IF EXISTS `search_index`;

I don't know what's wrong there but the whole comment block starting with -- MySQL dump 10.11 is the same as the one at the beginning of the mysqldump file, and it looks like mysqldump started a new dump while writing the INSERT INTO sessions statements which were not closed properly with UNLOCK TABLES;.

Does someone have any idea as to what's going on here?

My initial guess would be that when the mysqldump file was being created, the option single-transaction was not used.

According to MySQL Documentation on single-transaction

  The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

The fact that you embedded UNLOCK TABLES; into the dump manually shows that neither option was called when the mysqldump was first created. Thus, upon reload, this error reared its ugly head.


can I create an SSRS group without adding the group to the box itself and without using Active Directory? I want to know if it is possible to create a group "internal" to SSRS without having to add the group to the ReportServer box itself.

I do not have permissions to the box to create groups and/or add users to existing groups.  I know that this is a way (the only way?) to have groups that can be assigned permissions in the SSRS interface.

I do have all the permissions I need within the SSRS interface.  I have the System Administrator System Role on the ReportServer instance, as well as full sysadmin rights to the ReportServer database.

I am trying to simplify our security structure and it would be peachy if I could create a group, assign users to that group, and then permissions to the group.  But all I seem to be able to do is assign permissions to the groups set up on the box.

Any ideas?

EDIT: Active Directory groups are not an option for me.  :(

There are a couple of courses of action for you:

If you're friendly with your Windows Admin, I'd thoughtfully think out some AD Groups and then present them to the admin and ask them to be created (in whatever workflow you have).
From there you can assign permissions on the individual folders by these groups. Click on the folder, then click on Properties.

On the left you will see "Security," click that. Then click "Edit
Item Security." You may get a dialog that tells you that the item
currently inherits from parent. You may need to wait for a bit before
this completes, but be patient.

If successful you will have checkboxes next to the principles already
with access. From here choose "New Role Assignment"

Now you can add your AD Group in the next page and assign it the permissions necessary. Try with DOMAIN\GroupName

Obviously this will be much easier if you have AD groups, but if you can't get that, then you can do this with individual domain users. That would be a management nightmare?


How to combine two columns into one? I have a table in PostgreSQL 11 with two varchar columns like this:

 A | B
---+---
 m | n
 x | y

Now I want to add a column C which should contain the contents of A and B (concat(A, B)):

 A | B | C
---+---+---
 m | n | mn
 x | y | xy

After that I want to drop A and B so that I effectively replace the two original columns.

How can I set the value of C to the concatenated values of A and B?

You seem to have already the solution yourself:

UPDATE thetable SET c = CONCAT(a, b)


How to detect whether a key is present in a JSON object using TSQL? This is an interesting one.

Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d.

Imagine this object is passed to a stored procedure and every key is optional (as long as there is at least 1 key in the object). That is, it is valid to pass '{"a":"1"}' even though b, c and d have been excluded.

The question is:

How can I perform a check, in the stored procedure, as to whether a particular key has been passed in?

If I attempt to access a key using json_value, and the key is absent, then in lax mode, I receive a null value; in strict mode, I receive an error. I suppose this is one way I can tell the difference: reference the key in strict mode and catch any errors - but I want to avoid using try-catch logic.

Below are some example calls:

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';
select [value] from openjson(@data);
select 'strict a', json_value(@data, 'strict $.a') union
select 'strict b', json_value(@data, 'strict $.b') union
select 'strict c', json_value(@data, 'strict $.c') union
-- select 'strict d', json_value(@data, 'strict $.d') union
select 'lax a', json_value(@data, 'lax $.a') union
select 'lax b', json_value(@data, 'lax $.b') union
select 'lax c', json_value(@data, 'lax $.c') union
select 'lax d', json_value(@data, 'lax $.d');

This shows:

A non-null string value is always returned "as is" (key 'a')
An empty string is always returned "as is" (key 'b')
A null is always returned "as is" (key 'c')
An absent key is returned as null in lax mode. If you uncomment the relevant line, attempting to reference an absent key in strict mode returns an error.

In other words, I can't distinguish between an absent key, and a key that has been supplied with a null value.

Why do I want to distinguish the difference between null being passed in, and a key being absent? If the key is present, its value should be persisted (whether null or not). I do not want to persist null in the event the key is absent. If the key is absent, I want to simply do nothing relating to that key (or the column where its value would be persisted).

My next approach was to wonder whether I could count the matching rows - perhaps rather than an error, the count function would return '0' as the count of rows matching on key 'd'.

This is where it gets interesting.

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';

with CTE as (select json_value(@data, 'strict $.a') as strictA)
select count(1) from CTE;

with CTE2 as (select json_value(@data, 'strict $.d') as strictD)
select count(1) from CTE2;

I was hoping to see the values 1 and 0. I was thinking it would either be that, or that it throws an error. The actual result is that both queries return a count of 1.

Why does the second query return '1'?

And do I have to resort to try-catch logic to detect whether a given key exists in a JSON fragment?

Try using OPENJSON instead. This returns a Type column that indicates a NULL value for a key. You can LEFT JOIN this to a source list of possible keys and check for a NULL Type value or a NULL return value to determine if the key is present or not. Example below:

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';

SELECT [Value],
    CASE
        WHEN [type] IS NULL THEN 'Not Present'
        WHEN [type] = 0 THEN 'Null Value'
        ELSE 'Non-Null Value'
    END AS [KeyPresent]
FROM
(
    SELECT 'a' AS [key]
    UNION
    SELECT 'b' AS [key]
    UNION
    SELECT 'c' AS [key]
    UNION
    SELECT 'd' AS [key]
) keys
LEFT JOIN OPENJSON(@data) jdata ON keys.[key] = jdata.[key]


What are the risks in running ALTER VIEW in a live production environment? What risks do I need to consider if I want to run ALTER VIEW in a live SQL Server production environment?  Assume lots of long-running queries could be going on, both SELECTS and INSERT/UPDATE/DELETEs, but no ALTER TABLES or other schematic changes.  Is there any risk of disrupting users in the following cases?

Nobody is selecting from the view I'm updating, or anything that depends on it, but they may be updating data in tables the view uses.
Users may be selecting the from the view being updated immediately before or after the statement is run.  For example, if a user starts (A) a long-running SELECT on the view, I run (B) ALTER VIEW and it has to wait for (A) to finish, then a different user starts (C) another SELECT on the view.  Does (B) block (C) until (A) is completed?

If you need to know, the environment is SQL Server 2005 SP4 (9.0.5000), but please indicate if your answer is different for newer versions.

Modifying the underlying table for the view should not make an impact when running alter view. This is not true to users trying to access the view.

  If a view currently used is modified by using ALTER VIEW, the Database Engine takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, the Database Engine deletes all copies of the view from the procedure cache. Existing plans referencing the view remain in the cache but are recompiled when invoked.

Source

Exclusive locks:

  Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Schema locks:

  Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

What it does:

  During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

So it sounds like this:

    User 1 runs a query.

    User 2 alters the view.

    User 3 tries to query the view.

    User 1 finishes query, User 3 finishes query, User 2 now establishes a schema lock
preventing any other transactions until no active users are using the view.

I ran a test on my database with three concurrent windows. One would select * from the view 10,000 times, another would insert into the view 10,000 times, and the other would update the base table 1000 times. While these ran, I would run the alter view script. It behaved in a manner as I described with the scenario above.

None of these procedures were overly heavy or time consuming, basically thousands of micro transactions. The alter wedged itself in as fast as it could and altered the view between transactions when it could establish a lock. Due to this view and tables being small, I could not see the difference. Your environment will vary based on data sets.

I think your primary risk is raising the time it could take to run or complete transactions on the view. The other risk is modifying a view can break other things if they were not written to specify columns and were maintained with * instead. Other thoughts, you could be removing columns in the view in use elsewhere.


if database is not shrinked does it affect backup size? Ok my question is simple.

Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space.

Now when i do a backup of this database, does this space also getting added to the backup file or backup process is somewhat special and it already shrinks it ?

SQL server 2012

The backup process does not back up empty pages, only pages with data, so typically, the backup file will be a lot smaller.

One important thing to note: when using compression, the initial size needed on the destination drive will be the uncompressed size; the backup will only be fully compressed at the end of the process.


Logon failed for login due to trigger execution I have been trying to login using one of my sql server logins but I am getting the error message below:

General Error Message

Copy Message Text

TITLE: Connect to Server
------------------------------

Cannot connect to MY_SERVER.

------------------------------
ADDITIONAL INFORMATION:

Logon failed for login 'cola' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Advanced Information

I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that's why I am putting this question here:

“Logon failed for login 'sa' due to trigger execution.” When No SA Login Triggers Defined

This question here is very similar too:

Login failed for user - Error: 18456, Severity: 14, State: 38

And from this one I got the following info:

Login failed for user - Error 18456 - Severity 14, State 38

1      'Account is locked out'
2      'User id is not valid'
3-4    'Undocumented'
5      'User id is not valid'
6      'Undocumented'
7      'The login being used is disabled'
8      'Incorrect password'
9      'Invalid password'
10     'Related to a SQL login being bound to Windows domain password policy enforcement.
        See KB925744.'
11-12  'Login valid but server access failed'
16     'Login valid, but not permissioned to use the target database'
18     'Password expired'
27     'Initial database could not be found'
38     'Login valid but database unavailable (or login not permissioned)'

According to the information above I have the following issue:

  Login valid but server access failed

Still no joy.

I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.

I used the script below to find out where it is used, by whom, from where:

USE master
go
SELECT
        sdes.session_id
       ,sdes.login_time
       ,sdes.last_request_start_time
       ,sdes.last_request_end_time
       ,sdes.is_user_process
       ,sdes.host_name
       ,sdes.program_name
       ,sdes.login_name
       ,sdes.status

       ,sdec.num_reads
       ,sdec.num_writes
       ,sdec.last_read
       ,sdec.last_write
       ,sdes.reads
       ,sdes.logical_reads
       ,sdes.writes

       ,sdest.DatabaseName
       ,sdest.ObjName
    ,sdes.client_interface_name
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.Query
    ,KillCommand  = 'Kill '+ CAST(sdes.session_id  AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes

INNER JOIN sys.dm_exec_connections AS sdec
        ON sdec.session_id = sdes.session_id

CROSS APPLY (

                SELECT DB_NAME(dbid) AS DatabaseName
                    ,OBJECT_NAME(objectid) AS ObjName
                    ,COALESCE((
                            SELECT TEXT AS [processing-instruction(definition)]
                            FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                            FOR XML PATH('')
                                ,TYPE
                            ), '') AS Query

                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)

    ) sdest
WHERE sdes.session_id <> @@SPID
--ORDER BY sdes.last_request_start_time DESC

I then killed the 2 sessions, re-created the login, tried again, same error.

What am I missing?

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

You might have previously had a logon trigger that evaluated if you were allowed to log in to the SQL Server or not. This trigger may have been removed or might produce wrong results which now does not allow anybody to log in to the SQL Server instance.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd –S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe –c -m –f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.


Do triggers start to degrade in performance (or sometimes fail) on highly transactional tables? How many transactions per second can SQL Server table triggers handle? If I had a highly transactional table that was inserted into 1,000 times a second, are insert triggers on that table reliable to fire every single time?

are insert triggers on that table reliable to fire every single time?

Absolutely. Triggers aren't optional, they WILL fire.

Triggers CAN cause a performance impact on highly transactional tables because the trigger fires synchronously to your DML operation. On an insert trigger, if the trigger execution fails, your insert fails. You won't get confirmation that your insert completed successfully until your trigger has also completed successfully.

In this way, your insert trigger WILL make your insert more expensive, because the trigger is adding work to a previously simple INSERT statement. Whether it's going to be acceptable performance will depend on what your trigger is doing.

The same performance problems that can affect any query or procedure can also affect triggers. You'll need to tune them and pay attention to whether that trigger is doing it's job effectively.

  How many transactions per second can SQL Server table triggers handle?

This isn't a straightforward question. It depends on your schema, your data access patterns, your server hardware, etc. Needless to say, some companies are absolutely running SQL Server at massive scale with transactions per second that are mind boggling. But they have fast hardware, large budgets, and very skilled teams building their systems.

For this part of the question to be answerable, you'd need to propose a specific scenario & problem to see if anyone can help resolve that more specific issue.


psql: FATAL: Peer authentication failed for user I just installed PostgreSQL 9.4 on Ubuntu 15.10.

I created a user with createuser -P myuser
I created a database with createdb -O myuser mydatabase
I edited pg_hba.conf and added local mydatabase myuser md5
I restarted PostgreSQL with sudo service postgresql restart

User myuser is a PostgresSQL user only and has no user account on Ubuntu.

When I try to connect to the database with psql -W mydatabase myuser it fails with psql: FATAL:  Peer authentication failed for user "myuser".

PostgreSQL is running …

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2016-03-03 09:53:00 CET; 9min ago
  Process: 22219 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 22219 (code=exited, status=0/SUCCESS)

Mar 03 09:53:00 SERVER01 systemd[1]: Starting PostgreSQL RDBMS...
Mar 03 09:53:00 SERVER01 systemd[1]: Started PostgreSQL RDBMS.

... and listening.

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:postgresql    *:*                     LISTEN
tcp6       0      0 localhost:postgresql    [::]:*                  LISTEN
Active UNIX domain sockets (only servers)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     151534   /var/run/postgresql/.s.PGSQL.5432

What do I have to do to connect with user myuser to database mydatabase?

In a fresh install from a few days ago, the second line of my pg_hba.conf is

local   all             all              peer

I believe this is the one that makes your connection attempt fail.

The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered.  If it fails, then there is no second try, so the connection attempt will likely fail.  Or, as the documentation states:

  There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

The solution is easy: either remove the above line if you don't plan to use peer authentication, or move your specific rule above this one.


Why Postgresql 9.2 word boundary not working! In my postgresql version 9.1 this query is working fine:

select id,name from flash_card_set where name ~* '\yACT\y'

and matching:
text ACT txt,ACT tests,tests ACT
But in my postgresql version 9.2 it is not matching any of them.Am I missing something!

I'd suspect you have standard_conforming_strings set to OFF on the 9.1 instance (meaning it was changed explicitly, since the default is ON since 9.1), and the opposite in 9.2.

Demo:

test=> select version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

 test=> show standard_conforming_strings ;
 standard_conforming_strings
-----------------------------
 on
(1 row)

test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
test-> SELECT v FROM x WHERE v ~* '\yACT\y';
      v
--------------
 text ACT txt
 ACT tests
 tests ACT
(3 rows)

Now with it to OFF:

test=> set standard_conforming_strings=off;
SET

test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
WARNING:  nonstandard use of escape in a string literal
LINE 2: SELECT v FROM x WHERE v ~* '\yACT\y';
                                   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 v
---
(0 rows)

You'd need also to set escape_string_warning to OFF to mute the warning message and get a final result that plausibly matches your question:

test=> set escape_string_warning to off;
SET
test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
 v
---
(0 rows)


What does RRQMS and WRQMS mean? I found a possiblity to display Rrqms and Wrqms graphs in AWS RDS Enhanced monitoring page (I use Postgres 9.5.19) but I can't find any detailed explenation about these parameters.

By googling I only found these two very vague explenations:

From http://heshen.lofter.com/post/cfa6d_b1b5cb0

rrqms: The number of read requests merged per second that were issued to the device.
wrqms: The number of write requests merged per second that were issued to the device.

From https://www.codetd.com/en/article/7988617

rrqms: the device associated with the read requests per second is the number of the Merge (when the system call requires the read data, VFS requests sent to each FS, FS if found different read requests to read is the same as the Block data, FS will request the merge combined)
wrqm / s: the device associated with the write requests per second is the number of the Merge.

These explenations though are still very blurry for me. Yes one is read and the other is about write per (milli)second but merged into where? Came from where? Issued by whom etc...

RDS instances use Elastic Block Storage (EBS). EBS documentation explains what request merge means:

the underlying drive technology determines the maximum amount of data that a volume type counts as a single I/O. I/O size is capped at 256 KiB for SSD volumes and 1,024 KiB for HDD volumes because SSD volumes handle small or random I/O much more efficiently than HDD volumes.
When small I/O operations are physically contiguous, Amazon EBS attempts to merge them into a single I/O operation up to the maximum size. For example, for SSD volumes, a single 1,024 KiB I/O operation counts as 4 operations (1,024÷256=4), while 8 contiguous I/O operations at 32 KiB each count as 1 operation (8×32=256).

High number of merged I/O requests means your database is requesting a lot of sequential blocks, and EBS is able to translate that into fewer but larger, hence more efficient I/O operations. Low number of merged I/O requests probably means your database is mostly accessing random blocks.
To answer your

Came from where? Issued by whom?

-- by whatever is reading from and writing to EBS volume(s) on your RDS instance, presumably Postgres.


Why is MySQL database data-files deployed under /var/lib/mysql? I was wondering why do we deploy data files under /var/lib/mysql/. Is there a logical reason for that, or is that just a "traditional" place for the data?

I will value more answers based on standards like the LSB or the POSIX, and would appreciate links to the policies of your favourite Linux or Unix implementation that dictate this is the expected place to deploy your data.

Many thanks in advance for your attention and answers.

Kind regards,

-- LMC

In the Filesystem Hierarchy Standard, /var/lib is listed as containing:

  State information. Persistent data modified by programs as they run, e.g., databases, packaging system metadata, etc.

So it is very much because it is the traditional/documented place to put such things. If a database system or similar is installed under /opt it is common for the data files to also be under /opt, though many would argue that /var/lib is still the right default location in that case (with state pertaining to the running of the app in /opt and state/data produced or consumed by the app (such as user databases) under /var/lib).

Of course on a multi-user system, keeping user databases under /home/<user> often makes more sense, though not all database engines support this (or don't support it easily, without some manual jiggery-pokery on the part of your SysAdmin/DBA).


A database for digital assets I work at a print shop that has a decent sized archive of old print jobs. Currently, to find a job we have to search the SMB share (on win2000 server), which looks through a few hundred thousand files.

Our job data is organized by: Year>Month>Customer_Name_Job#  and then the contents of the job are inside the last folder.

Is there a way to create a database so that we can query a job desc. or job number? Basically just a simple search that is faster than searching with windows search would be great. I tried the windows index service but it only finds .doc file, not folder names or PDFs.

In the future we are planning on making a SQL database where complete info will be entered when the job is created so it is easily queried in the future. I was hoping to throw together something that will make it easier to search older orders.

You could use a Document-oriented database for this. You could then create a program in your preferred language to import the existing documents into the db, parsing the folder structure for the metadata (customer, job#, etc).


Stored procedure runs fine when executed manually but fails in scheduled job I have a stored procedure that runs fine when executed manually but fails in scheduled job. The error I receive is:

Executed as user: \USER-a. Login failed for user '\USER-a'. [SQLSTATE 28000] (Error 18456).  The step failed.

My other stored procedures runs fine when scheduled. The only stored procedures that fails are the ones that are written in this form.
SET @QUERY = 'SELECT * from abc'
EXEC(@QUERY)

The probable reason I can think is when u execute manually it might be because you are executing with login with privileges or access to execute that proc.
However sql agent job might be using the account assigned to run sql agent services.
Hence you see the error for login failed.
Probably check the error log when failure happens to get more details on that login failure message


how to prevent any update to my database before a exp operation? I want to migrate my database from AIX to Red Hat vai exp/imp.

how to prevent clients make any update to my database during the migration progress ?

From the command line

from the command line enter lsnrctl and then stop (stops the listener, only connections from the server possible)
connect to sqlplus as sysdba
shutdown immediate  ( makes sure all sessions are terminated)
startup restrict  (only allows users with dba privilege to log on)
as Mindaugas Riauba mentions disable all jobs likely to change data during the export
export your data
alter system disable restricted session
back to command line enter lsnrctl and start


Update query could not be bound I'm trying to do a simple update query and I'm getting this error:

  The multi part identifier "customers.customer_name" could not be bound

Query:

Update Suppliers
Set City = (select s.city
    From suppliers AS s
       Where s.supplier_name = customers.customer_name)
Where Exists (select c.city
    From customers AS c
        Where c.customers_name = Suppliers.supplers_name)

Your syntax is slightly off. In SQL Server, here is how I would perform this update:

UPDATE s SET s.city = c.city
FROM dbo.Suppliers AS s
INNER JOIN dbo.Customers AS c
ON s.supplier_name = c.customer_name;

From my most popular answer on Stack Overflow.

Remember to always use the schema prefix and also it is often easier to just call those columns name instead of prefixing each with the table name.


MySql sort conditional based on column values I have a table with products where there's a column for price and stock.

I want the products to be display sorted by the following rules:

All products with price = 0 go to the bottom;
Then the products with price are sorted by:

if stock is greater or equal to 100 then they are sorted by price ASC;
else they are sorted by stock DESC and if there's a draw then sorted by price ASC.

So far I came up with price = 0, stock >= 100 DESC, price ASC, stock DESC
which seems to work correctly when there's products with stock over 100 but falls apart when there are none.

SELECT *
FROM products
ORDER BY price = 0, stock >= 100 DESC, price ASC, stock DESC

How can I make this work?

I think this would work - although it's rather obfuscated:

ORDER BY
    price = 0,
    LEAST(stock, 100) DESC,
    price ;

More clearly:

ORDER BY
    CASE WHEN price > 0 THEN 1 ELSE 2 END,
    CASE WHEN stock >= 100 THEN 100 ELSE stock END DESC,
    price ;


Create a linked server with SQL Server 2012 x64 How can I create a linked server to a folder having fox pro dbf files?

I can access the dbfs inside Visual Studio 2010, creating a connection with this properties

Connection string

Dsn=Legacy;dbq=\\192.168.48.29\dbf;defaultdir=\\192.168.48.29\dbf;driverid=533;fil=dBase 5.0;maxbuffersize=2048;pagetimeout=5

Provider

.NET Framework Data Provider for ODBC

But I don't know how to use this information with sql server 2012 express x64

I'm trying to use

EXEC master.dbo.sp_addlinkedserver @server = N'LEGACY', @srvproduct=N'Legacy', @provider=N'MSDASQL', @datasrc=N'Dsn=Legacy;dbq=\\192.168.48.29\dbf;defaultdir=\\192.168.48.29\dbf;driverid=533;fil=dBase 5.0;maxbuffersize=2048;pagetimeout=5', @provstr=N'.NET Framework Data Provider for ODBC'

in SQL Server Management Studio

Error message about not finding the dsn ...

Drivers for dBase (or Access or Excel) are not installed as part of the SQL Server install. It is likely that VS 2010 installed on your workstation is connecting through the old Jet drivers, which are installed on developer's machines. The problem with Jet is that it was never ported to 64 bit. I don't think that the old Visual FoxPro drivers were ported to 64 bit, either.

Microsoft replaced Jet with "ACE", which is available in 32 and 64 bit packages. ACE drivers were first released with Office 2007 and supplant the older (and probably deprecated) Jet drivers.

You can download the ACE 2010 drivers here. Since you are using a 64 bit server, you want the 64 bit drivers for linked servers. If you plan on running 32 bit packages on the server, you would need to install the 32 bit ACE as well.

You might be able to find a similar package for 2013 by now. I have not used these more recent drivers, so I don't know if the the older formats (like dBase, Fox, etc) are still supported.

After you install the drivers, they generally need additional configuration inside of SQL Server. IIRC, if you see errors that seem to be security-related, you need this additional configuration. In short:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Note that running drivers in-process could affect the stability of the instance, if those drivers are buggy.

After the drivers are installed, you will need to configure a valid linked server.

You also need to be sure that SQL Server has permission to read (and maybe write) the files.


Do I need full recovery mode when I have multiple daily backup? I have created 5 maintenance plans on my SQL server (say 50 !) which generate databse backup 5 times daily. As I read in this Q/A the full recovery is good when I need Point-in-time recovery.

My exact question: Is generating multiple daily backup with simple recovery model, similar to full recovery backup with longer period?

Setting a database to full recovery is not enough by itself to ensure you have point in time recoverability. You also need to take regular log backups. You should also be taking time regularly to test that you personally know how to execute the required commands to actually perform a restore to an arbitrary point-in-time.

In lieu of running loads of home brewed full backups, the fire-and-forget solution you want is probably to just install Ola Hallengren's backup solution. Running this script installs the required objects and deploys the required jobs to manage all of the full recovery databases on an instance. You will find these jobs 1 under SQL Server Agent > Jobs named by default DatabaseBackup - USER_DATABASES ... and similar. Add a schedule to these jobs that suits you 2 and disable other backup jobs to prevent conflicts.

You should still need to regularly take time in the normal course of business to ensure you and others on your team are able to actually restore the backups you have, regardless of how you take them.

1: assuming you're using SSMS here

2: 5 times daily if you like, but probably a bit less is fine


Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be updated while this task is running so before calling each stored proc I run:

set session transaction isolation level read uncommitted

then call the stored proc which gets a subset of IDs to process. SQLFiddle of the overall operation: http://sqlfiddle.com/#!9/192d62 (somewhat contrived but keeps to the structure of the original queries)

The reason I ask is the deadlocks continue to happen and looking into the Monitor output there's one thread requesting a shared lock while another holds an exclusive lock over the same space (or vice-versa) - shouldn't setting that transaction level prevent the need for a shared lock? Is there a reason to get a shared lock aside from repeatable-read?

Using InnoDB.

Relevant lock information from show engine innodb status (edited to match table name from SQLFiddle):

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 481628 page no 24944 n bits 112 index `PRIMARY` of table `events` trx id 27740892 lock mode S locks rec but not gap waiting

and

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 481628 page no 24944 n bits 112 index `PRIMARY` of table `events` trx id 27740898 lock_mode X locks rec but not gap waiting

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 481628 page no 25267 n bits 112 index `PRIMARY` of table `events` trx id 27740898 lock_mode X locks rec but not gap waiting

Note that query 1 and 2 in the SQLFiddle match the first and second entries in the engine output. The PRIMARY key is only over the id column (as can be seen in the Fiddle)

"Read chunks, but only update a few of them"...

SELECT a chunk -- separate transaction, don't care about locking, etc
munch on that chunk to figure out the 'few' ids to update
BEGIN;
    SELECT ... WHERE id IN (list of the few) FOR UPDATE;
    minimal other work
    UPDATE them;
COMMIT;   -- plus checks for deadlocks, etc, and restart the code.

The idea is to pull as much time-consuming code out of the transaction as possible, thereby making the transaction faster, thereby minimizing the likelihood of conflict.  (No, it probably won't eliminate all deadlocks, but it should help.)

Another note:  If possible, go through the table in the same order in all threads.  Deadlocks happen when one thread plays with id IN (1,2) but another does IN (2,1).  If both do IN (1,2), then you get delays, not deadlocks.

Another tip:  Make the chunks smaller.


When changing startup parameters on a cluster, does each node have to be updated manually? When moving a SQL 2005 instance to new disks on a two-node Windows 2003 cluster, I changed startup params on the active node to reflect the new paths for the master DB files and error logs.

I discovered after attempting a move to the alternate node that the startup params were not synchronized with the passive node and I had to reapply them there. Is that by design or should all nodes in a cluster have SQL Server Configuration Manager settings synchronized automatically?

The registry should have been updated automatically.  Take a look at the BOL reference on configuring server startup parameters:

  On a cluster, changes must be made on the active server while SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.

In order to further troubleshoot why that didn't happen, we would need to know more surrounding information (errors, procedures for changing and failover, etc.).  When you say "after attempting a move to the alternate node that the startup params were not synchronized..." what do you mean by "move to the alternate node"?  Did you failover the instance?


Is it possible to restore SQL Server DB from transaction log (no MDF)? We had a SQL Server 2005 server that housed roughly 200 low/no traffic archive databases, and that server was not backed up to any other device.  The database files (MDF and LDF) were stored on a RAID array, and when the RAID controller failed we were concerned we were going to lose our data.  We sent the RAID controller and array to a data recovery company, and they were able to recover most of the files, but in some cases we have the transaction log but are missing the main data files.

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?

No, restoring a transaction log is sequential. Transaction log relies on LSN (Log Sequence Number)

Also, you cannot restore your database with just transaction log. It requires the main database file (MDF) and NDF (secondary datafiles if there are any).


MYSQL Optimize group by query with aggregators SELECT  id_num
    ,   sum(expired) as expired
    ,   max(`date`) as max_date
FROM accounts
where `date`<=20170505
group by id_num;

The accounts table has a compound index on id_num,date and has about 100mil rows. This query seems pretty basic but it takes forever and I'm not sure how to break it up to speed things up. I thought about first creating a helper table for DISTINCT id_num (~3mil rows) but then I'm not sure how to get the sum(expired) and max(date) columns without joining the helper against the accounts table and doing the same thing as the original query.

CREATE TABLE `accounts` (
    `id_num` int(11) NOT NULL,
    `date` date NOT NULL,
   `time` datetime NOT NULL,
   `price` decimal(10,4) NOT NULL,
   `cost` decimal(10,4) NOT NULL,
   `time_slices` int(11) NOT NULL,
   `sub_expired` tinyint(1) NOT NULL,
   PRIMARY KEY (`id_num`,`date`),
   KEY `date` (`date`),
   CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`id_num`) REFERENCES `cust` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

The date range goes back roughly 18 months and the data is pretty evenly distributed over all dates. The rows are being inserted chronologically and there are rarely updates/deletes.

INDEX(date,             -- to satisfy the WHERE; must be first
      id_num, expired)  -- to complete "covering"; must be last (either order)

Because it is "covering", it will run entirely in the index's BTree.
By putting date first, it looks through the minimal number of rows.

This is a rather unusual query, so don't expect the principles to carry over to other queries.

Even when you are somewhat happy with that, I may tell you how to make it 10 times as fast.  But first, I need to see SHOW CREATE TABLE and provide some clues of the date range and distribution of dates in the table.  Also, are the rows being inserted somewhat chronologically?  And are there any UPDATEs or DELETEs?

Summary Table?

Perhaps the table could be summarized into a Summary Table that has one row per month per id_num.  Then the original query would do most of the work 30x faster from that table, plus still do some work from the huge table.  The summary table would be incrementally augmented.


odd OS memory consumption by two nearly-identical SQL 2008 R2 instances Instances are legacy, something we have to carry for the next 3 years, 2008 R2 64-bit, both SQL Server and Windows Server OS. Two copies on two nodes.

Both servers are set to consume "Maximum server memory" parameter set at MAX.

VM #1 (8GB RAM total) - memory is utilized to the maximum extent and displayed correctly in Task Manager.
VM #2 (32GB RAM total) - memory utilization is shown at ~100MB only, even when I try to force it to eat at least 16GB at startup in the SQL Server properties.

Both task manager and resource monitor on the second VM report barely any RAM consumed by the process.

Is this a SQL Server bug, if the installed OS RAM volume is above certain level? Doubtfully, right?

Or could it be some memory setting on the OS itself?

My bet is #2 has Lock Pages in Memory privileges granted to the SQL Server service account. In this case, Task manager show only a minuscule memory usage (SQL Server uses a different Windows API in this mode). All is normal.

(As an aside, if you by "forcing at startup" mean setting min server memory, then that is not what happens. Min means that if SQL server climbs over it, then it won't go under it. It won't force that allocation at startup. )


Search for nested values in jsonb array with greater operator Here is the table definition (simplified):

CREATE TABLE documents (
    document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    data_block jsonb NULL
);

Sample values:

INSERT INTO documents (document_id, data_block)
VALUES
   (878979,
    '{"COMMONS": {"DATE": {"value": "2017-03-11"}},
     "PAYABLE_INVOICE_LINES": [
         {"AMOUNT": {"value": 52408.53}},
         {"AMOUNT": {"value": 654.23}}
     ]}')
 , (977656,
    '{"COMMONS": {"DATE": {"value": "2018-03-11"}},
     "PAYABLE_INVOICE_LINES": [
         {"AMOUNT": {"value": 555.10}}
     ]}');

I want to search all documents where one of the 'PAYABLE_INVOICE_LINES' elements contains a 'value' greater than 1000.00.

My query is

select *
from documents d
cross join lateral jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') as pil
where (pil->'AMOUNT'->>'value')::decimal > 1000

But, as I want to limit to 50 documents, I have to group on the document_id and limit the result to 50.

With millions of documents, this query is very expensive - 10 seconds with 1 million.

I try to add a GIN index on the array of the jsonb object. But it seems it's applied only while using a jsonb operator like @>.

Do you have some ideas to have better performance?

This is generally hard to optimize: no direct operator or index support for jsonb for this kind of test.

EXISTS should at least be faster than what you have, while also avoiding duplicate rows (where multiple array elements match) and the additional (redundant) column pil in the result:

SELECT *
FROM   documents d
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') pil
   WHERE (pil->'AMOUNT'->>'value')::decimal > 1000
   );

Related:

Indexed range comparison of array key's value in row inside JSONB
Speed up range test for key values nested in jsonb array of objects

To make this faster by orders of magnitude, extract the maximum value per row and save it redundantly or use an IMMUTABLE function in a very small and fast (but also specialized) expression index:

CREATE OR REPLACE FUNCTION f_doc_max_amout(jsonb)
  RETURNS numeric AS
$func$
   SELECT max((a->'AMOUNT'->>'value')::numeric)
   FROM   jsonb_array_elements($1) a
$func$ LANGUAGE sql IMMUTABLE;

CREATE INDEX documents_max_amount_idx
ON documents (f_doc_max_amout(data_block -> 'PAYABLE_INVOICE_LINES'));

Query (must match index expression):

SELECT *
FROM   documents d
WHERE  f_doc_max_amout(data_block -> 'PAYABLE_INVOICE_LINES') > 1000;

dbfiddle here


Why do I get an incorrect error "ORA-01775: looping chain of synonyms", when the base table does not existing? I use Informatica for managing some ETL process that load data into an Oracle 9i Data-warehouse.

Today I got the below error in the Informatica session logs:

Message: Database driver error...
CMN_1022 [DELETE FROM SOME_TABLE
WHERE PERIOD_NAME = 'OCT-12'

ORA-01775: looping chain of synonyms

Database driver error...
Function Name : executeDirect
SQL Stmt : DELETE FROM SOME_TABLE
WHERE PERIOD_NAME = 'OCT-12'
Oracle Fatal Error
Database driver error...
Function Name : ExecuteDirect

Oracle Fatal Error
]

Now it was a typographical error, the table name was misspelt. The Oracle error obviously sent us in the wrong direction... So just wanted to understand why this error was show when there was no issue with synonyms.

This happens when you have a "dangling" synonym, because it points to itself.

For example:

SQL> create table blah ( a number );

Table created.

SQL> create public synonym blah for blah;

Synonym created.

SQL> drop table blah;

Table dropped.

SQL> select * from blah;
select * from blah
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL>