Generate GRANT for all tables reference by a procedure I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select statement for all the table that this stored procedure uses. I need this since my procedures use sp_executesql and hence users need explicit permissions on the tables as sp_executesql makes execute permission on the stored procedure not enough (as you might know the user would need select permission on the tables used by that SP as well).

I have so far done the below dynamic SQL statement, however, it only works if the stored procedure only selects from one table. I totally understand that it does not work for more due to the subquery in the where clause.

select 'GRANT SELECT ON [' + schema_name(schema_id) + '].[' + name + '] TO [User1]'
from sys.tables
where name like (SELECT distinct t.name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
where P.NAME LIKE 'myproc')

Error when SP uses more than 1 table:

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My required output for EACH table used by a stored procedure:

GRANT SELECT ON [dbo].[table1] TO [User1]

Is there another way to achieve this?

The error message is correct. You can't say LIKE against the output of a query unless you are somehow explicitly constraining that output to one row (and this is not what you want to do). Just perform a join... you could weed out duplicates but there's no reason to (granting to the same table twice, if possible, does not hurt anything). Also please use QUOTENAME() instead of manually adding [ and ] yourself.

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT SELECT ON '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'] TO [User1];'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.sql_dependencies AS d
ON d.referenced_major_id = t.object_id
INNER JOIN sys.procedures AS p
ON p.[object_id] = d.[object_id]
WHERE p.name = N'myproc';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Keep in mind that sys.sql_dependencies will not necessarily capture everything, for example if the stored procedure uses dynamic SQL, it can't parse the text to find all table references, or explore all possible permutations.


Get distinct count on calculated member? I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has aggregation of Distinct Count.

Everything works great. I need to make the aggregation of 'CustomerID' also to Distinct count, but I can't be able to. It says me error states, "The measure group has more than one distinct count measure."

So, I need to have these two measures's aggregation as DistintCount. How can I? Any help will be appreciated.

I thought to create a calculated member to achieve this but got failed as I don't know what expression I have to give!

The limitation of a single distinct count per measure group is related to performance. To get both distinct count measures to show up in the same measure group, you can use the workaround suggested here by Darren Gosbell.

best of both worlds...performance benefit from each DC-measure residing in its own measure group, UX benefit from having all related measures appearing in same measure group.


Update code works in manual stored procedure but not when run as SQL Server Agent Job I have a stored procedure that deletes all records from an existing table, and then inserts new records. After the insert I have several update operations that change the value of one field.

If I run the SQL Server Agent job, the update does not happen. If I select and execute the update code manually, the update occurs.

If I execute the entire stored procedure manually, the update occurs.

No errors are thrown; everything 'works' but the update does not occur when I kick off the SQL Server job.

I resolved the issue - but do not understand it.

The system has a View corresponding to each Table.  One of the Views was used in my FOR XML PATH view.

I substituted the no-permission Table for the View, and it works.

The View has Public SELECT permissions.  The Table has no named permissions.

I think the SQL Server Agent Job should be able to SELECT from the View because of the public permission, but it cannot.


Query a list of objects in each database I have a list of about 300 Views spread out across several databases.
Some of the tables used in the DDL of the views have been dropped or renamed.
What I want to do is try to query all 300 views in each database.
If a query is successful, I want to output "Query Successful".
If a query fails because the object the view DDL is based on fails, I want to output "Query Failed".
The final output will look something like this
database_name     schema_name     view_name     status
db_1              dbo             view_1        Query Successful
db_2              abc             view_2        Query Failed

I know part of the solution will use the sp_MSforeachdb stored procedure, but I'm not sure what to do after that.
I have a list of all the objects I want to query stored in a table

You can use the following code to list all views against all databases.
It will populate everything inside of #Result. After that, you can use your list to do joins and check whatever status you want.
IF OBJECT_ID('tempdb..#Result') IS NOT NULL  DROP TABLE #Result;
CREATE TABLE #Result
([Database] VARCHAR(100), [schema_name] VARCHAR(100), [view_name] VARCHAR(100))

DECLARE @DB SYSNAME,@cmd NVARCHAR(4000)

SET NOCOUNT ON
SET @cmd = ''

SELECT TOP 1 @DB = D.name
FROM master..sysdatabases AS D
WHERE dbid > 4
    and DATABASEPROPERTYEX(D.name, 'status') = 'ONLINE'
ORDER BY D.name;

WHILE @@ROWCOUNT = 1
BEGIN
      SET @cmd = 'USE ' + @DB + ';
            INSERT INTO #Result
            SELECT  DISTINCT ''' + @DB + ''' AS CustomerNumber ,sh.name, o.name AS ObjectName
            FROM sys.objects o
            INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
            WHERE o.type = ''V''

    ';
      EXEC (@cmd)

      SELECT TOP 1 @DB = NAME
      FROM master..sysdatabases AS D
        WHERE dbid > 4
        AND DATABASEPROPERTYEX(D.name, 'status') = 'ONLINE'
            AND D.NAME > @DB
      ORDER BY D.name
END

SELECT * FROM #Result R


It sounds like the user that's executing the ALTER TABLE and the user who's running SHOW PROCESSLIST are not the same user, and the second user lacks the PROCESS privilege.

If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using).
— http://dev.mysql.com/doc/refman/5.6/en/show-processlist.html


ORA-01017 connecting to example schemas on Oracle VM appliance from Windows host I'm a software developer and am working on getting an Oracle VM Appliance running for testing purposes (the Database App Development VM with Oracle 12c - http://www.oracle.com/technetwork/community/developer-vm/index.html#dbapp). The host OS is Win 8.1 x64. I'm having trouble logging into the example schemas (HR, SH etc) from the host machine, with error ORA-01017. I've set up a tnsnames.ora file on the host and can connect as both SYS (as SYSDBA) and SYSTEM from there, so my basic setup seems fine (e.g. no firewall issues). The service alias is set to ORCL12C (the same as the SID on the VM).

the current situation is as follows:

Port-forwarding and Host-only network both set up in the VM.
From the Win8.1 host I can logon and run queries using both SQL*Plus and SQL Developer as SYS as SYSDBA/SYSTEM using either 0.0.0.0 (port-forwarding) or 192.168.56.101 (host-only) in tnsnames.
When I attempt a login to HR from the host using SQL Developer I get the ORA-01017 error (using "Basic" connection type with default role). This is the case for both SID and Service Name options.
A login attempt to HR from the host using SQL*Plus to HR@orcl12c also gives an ORA-01017 error.
Within the VM I can connect to the HR schema using SQL Developer only if I select the Local/Bequeath connection type, but it fails with the ORA-01017 error if I use the Basic connection type. I can connect to SYSTEM or SYS/SYSDBA using the Basic connection type, however.
Within the VM I can connect to HR, SH etc using SQL*Plus with no problems.
I'm using the instant client on the host.

So there appears to be something I'm missing when using the Basic connection type with standard users in SQL Developer, both within the VM and from the host (possibly related to the SQL*Plus failure from the host?). I've done extensive web searching but can find nothing to solve this. Thanks.

Do you have Oracle XE or any database running in your host and listener is started as a service in your Windows host? Probably it is trying to connect there. If you have mapped 1521 from your host to 1521 to your guest, just make sure in your host there is no listener service running. If it is running change you port forwarding to 1522 ( Recommended )or something or uninstall the listener running in 1521 ( Don't seem like a wise thing to do :-) )


PostgreSQL service start failed on CentOS 7 Installed PostgreSQL using:

sudo yum install postgresql-server postgresql-contrib

PostgreSQL version: 9.2.18

When start service:

sudo service postgresql start

Got error:

Redirecting to /bin/systemctl start  postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

The journalctl -xe detail:

-- Unit postgresql.service has failed.
--
-- The result is failed.
 7月 19 04:17:04 node2 systemd[1]: Unit postgresql.service entered failed state.
 7月 19 04:17:04 node2 systemd[1]: postgresql.service failed.
 7月 19 04:17:04 node2 polkitd[19772]: Unregistered Authentication Agent for unix-process:27133:53830492 (system bus name :1.405, object path /org/freedesktop/PolicyKit1/AuthenticationA
 7月 19 04:19:33 node2 polkitd[19772]: Registered Authentication Agent for unix-process:27165:53845472 (system bus name :1.406 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object pat
 7月 19 04:19:33 node2 systemd[1]: Starting PostgreSQL database server...
-- Subject: Unit postgresql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has begun starting up.
 7月 19 04:19:34 node2 pg_ctl[27187]: pg_ctl: could not start server
 7月 19 04:19:34 node2 pg_ctl[27187]: Examine the log output.
 7月 19 04:19:34 node2 systemd[1]: postgresql.service: control process exited, code=exited status=1
 7月 19 04:19:34 node2 systemd[1]: Failed to start PostgreSQL database server.
-- Subject: Unit postgresql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has failed.
--
-- The result is failed.
 7月 19 04:19:34 node2 systemd[1]: Unit postgresql.service entered failed state.
 7月 19 04:19:34 node2 systemd[1]: postgresql.service failed.
 7月 19 04:19:34 node2 polkitd[19772]: Unregistered Authentication Agent for unix-process:27165:53845472 (system bus name :1.406, object path /org/freedesktop/PolicyKit1/AuthenticationA
lines 2850-2910/2910 (END)

Run systemctl status PostgreSQL.service, got this information:

● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2017-07-19 04:30:26 EDT; 42min ago
  Process: 27285 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=1/FAILURE)
  Process: 27280 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 26640 (code=exited, status=0/SUCCESS)

You have 3 steps before being abble to connect to a PostgreSQL Database:

Install PostgreSQL
Create the data cluster ($PGDATA)
Start the server

You already installed PostgreSQL, you need now to create the cluster. The initdb tool is what you need.

You will find documentation here.

It might be confusing because when you're under a debian based distros, apt and perl wrappers do every steps for you.


PostgreSQL row as jsonb "array", not "object" We are trying to emulate a proprietary DB, which has "global primary keys", with PostgreSQL. That is what we would have liked to do (for many rows of many tables):

select 'some_table' as table_name, t.*
from some_table t
where id = 1

union all

select 'other_table' as table_name, t.*
from other_table t
where id = 2;

But, of course, that doesn't work, because every table schema is different.
Our DBA suggested this instead:

select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t
where id = 1

union all

select 'other_table' as table_name, jsonb_agg(t.*) as content
from other_table t
where id = 2;

Which would do it, but we're worried about the amount of data produced/transfered. Basically, every row of each table is returned as one  JSONB object contained in a single array. Now what bothers us, is the repetition of the column names for each row.

Could we instead somehow return a JSONB array per row, with only the values, based on the order of the columns in each table?

You could extract the values from the JSONB value and aggregate them back into a JSON array

select 'some_table' as table_name,
       id,
       (select jsonb_agg(x.v) from jsonb_each(to_jsonb(t) - 'id') as x(k,v)) as content
from some_table t;

But you can't rely that the order of the array elements corresponds to the order of the columns in the table.

Another alternative (not using JSON) would be to cast the whole row into a text value:

select 'some_table' as table_name,
       id,
       t::text
from some_table t;

Casting a record to text should preserve the order of the columns as they are defined in the table.


Strange colored sqlcl results on Windows I am trying to color the result of SQL statements with SQLcl.

I set sqlformat ansiconsole and execute a select '@|red red |@' red from dual;

The output I get looks like this:

I blame it on the Windows environment. Am I right to blame Windows and what can I do about it?

It's a bug, and it's fixed for the next release.

We're planning on regular, not quite monthly, updates.


Equivalent of CHARINDEX OR IN for ints I want the equivalent of this for ints.

--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0

So something like this.

--Invalid SQL
DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
select * FROM Person AS p
WHERE p.ixPerson IN(@users

You haven't really indicated how this is called.  Do you really have a string full of comma separated ints or is that just an artifact of some other aspect of your system?

If the outer part of the environment around this requirement is changeable, then typically in SQL Server 2008 and up, I pass a table-valued parameter containing a list of IDs (perhaps from ADO.NET or whatever) and then just use it as a table inside the procedure - i.e. WHERE value IN (SELECT id FROM @table) or INNER JOIN @table AS t ON t.id = users.t


ORA-01196: file 1 is inconsistent due to a failed media recovery session I have 3 database 1 primary and two standby, The storage of one the standby was abruptly removed without shutting down the database.

Few Days later the storage was returned, I restart the whole database server, and while trying to open my database instance(alter database open) I get the errors below:

ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/drstby/datafile/system.306.918916853'

I tried to do "recover database;" and I get another error:

RMAN-03002: failure of recover command at 12/15/2017 17:11:50
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 62585 and starting SCN of 16625729961

Please what can I do.

Note:
the database in question has the following
FRA
ASM
RMAN BACKUPS.
THANKS

Looks like you need to restore archivelogs. If you have them backed up to tape, you may need to do something like:

run
{
  allocate channel ch1 type 'sbt_tape' parms
  restore archivelog from scn 16625729961 until scn 999999999;
}

Replace the until scn 999999999 with an appropriate scn from your primary database.


SQL Server Full Text Search Engine is not returning consistent results I'm working on an application that makes use of the Full Text Search Engine from SQL Server. In order to convert the user input into readable parameters for SQL, i'm using This very useful FTS query converter. Now, the issue at hand:

We have a record that has the following set of keywords:

  Bockelman v. MCI Worldcom, Inc.

When the user types that on the application, the converter will work out the logic and send the following parameters to CONTAINSTABLE:

   FORMSOF(INFLECTIONAL, Bockelman) AND FORMSOF(INFLECTIONAL, v) AND FORMSOF(INFLECTIONAL, MCI) AND FORMSOF(INFLECTIONAL, Worldcom) AND FORMSOF(INFLECTIONAL, Inc)

However, the query returned no results. I noticed that the converter removes the periods from both "v." and "Inc.", so I performed a direct query on the database adding those missing periods. Still, no results.

If I remove "v." from the query. It returns a number of results. At this point, I thought that the "v." was being treated as a stopword of some sort. However, when I performed another query...

  John v. Smith

It returned a number of results with the "v." included on the query. This feels a bit inconsistent for me. I am probably not using the FTS Engine correctly, but I'm hoping to find some guidance here.

Thanks!

EDIT:

Here's the fully built query:

    SELECT o.OpinionDocumentId
                                       FROM CONTAINSTABLE(OpinionDocuments, Text, 'FORMSOF(INFLECTIONAL, Bockelman) AND FORMSOF(INFLECTIONAL, v)
    AND FORMSOF(INFLECTIONAL, MCI) AND FORMSOF(INFLECTIONAL, Worldcom) AND FORMSOF(INFLECTIONAL, Inc)') AS KEY_TBL
                                       LEFT JOIN dbo.OpinionDocuments AS o ON o.OpinionDocumentId = KEY_TBL.[KEY]
                                       LEFT JOIN dbo.OpinionDocumentPlaintiffs AS p ON p.OpinionDocument_OpinionDocumentId = KEY_TBL.[KEY]
                                       LEFT JOIN dbo.OpinionDocumentDefendants AS d ON d.OpinionDocument_OpinionDocumentId = KEY_TBL.[KEY]

Took me a little while to see what now seems obvious. I can't seem to replicate your experience with John v. Smith on 2012, but if we check out the default system stopwords...

select * from sys.fulltext_system_stopwords
WHERE stopword = 'v.'

The result is

stopword    language_id
v.          1053

So it seems the stoplist is the problem. Let's try a blank one and see what happens:

CREATE TABLE [dbo].[fts_table](
    [name] [nvarchar](50) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
  CONSTRAINT [PK_fts_table] PRIMARY KEY CLUSTERED
  (
    [id] ASC
  )
GO

INSERT INTO [dbo].[fts_table]
           ([name])
     VALUES
           ('John v. Smith'), ('Bockelman v. MCI Worldcom, Inc.')
GO

--Pretend I scripted out the fulltext index creation here

CREATE FULLTEXT STOPLIST [modified_stoplist]
AUTHORIZATION [dbo];
GO

ALTER FULLTEXT INDEX ON [fts_Table]
SET STOPLIST = modified_stoplist;
GO

Then the query now returns the results desired:

SELECT  *
 FROM CONTAINSTABLE(dbo.fts_Table, name,
'FORMSOF(INFLECTIONAL, ''Bockelman'')
AND FORMSOF(INFLECTIONAL,''v'')
AND FORMSOF(INFLECTIONAL, ''MCI'')
AND FORMSOF(INFLECTIONAL, ''Worldcom'')
AND FORMSOF(INFLECTIONAL, ''Inc'')')
   AS KEY_TBL

Returning...

KEY     RANK
1       32


Postgres Replication Is there any technology to replicate a table from public schema on the master server to a table on the slave server but not in the public schema.

MASTER SERVER                              SLAVE SERVER
pubic.table_name  ----- replicate ----->   schema1.table_name

I tried slony-I but the problem that the table on the slave must in the public schema too.

At least exists three way to do this:

1. REPLICATION + VIEW / FDW

You can create ON MASTER VIEW or FDW for table and VIEW / FDW will be replicate to slave.

Something like :

CREATE VIEW schema1.table_name AS SELECT * FROM public.table_name;

Or

CREATE SERVER srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'db', host 'localhost');
CREATE USER MAPPING FOR postgres SERVER dc OPTIONS (password 'pwd', "user" 'postgres');
CREATE FOREIGN TABLE schema1.table_name (..table description..) SERVER srv OPTIONS (schema_name 'public', table_name 'table_name');

2. TRIGGER + SAVE TO REMOTE

You can create trigger on public.table_name on master to write changes to remote db via dblink or FDW.

3. LOGICAL REPLICATION

The most hard path as for me. You can write your own decoder and/or add trigger to write also/instead data to schema1.table_name


MYSQL 8.0 server not found in Workbench directory in Windows 10? I have upgraded my workbench to 8.0CE and also removed the previous 5.7 MYSQL server as well as Workbench 6.3.

This is the directory:

C:\Program Files\MySQL\MySQL Workbench 8.0 CE

Also when I typed the below, I get error message

C:>cd Program Files\MySQL\MySQL Server 8.0.12.0\bin
The system cannot find the path specified.

Now, there used to be a server in the same directory where MYSQL Workbench reside but I just can't find the server file there.

Another strange thing I just discovered is that even though I have upgraded to version 8, the PorgramData is still containing MYSQLServer5.7 !  It is supposed to be MYSQLServer 8.0.12.0 right ?

Please let me know if there is any steps I have missed.

Tks.

MySQL server and MySQL Workbench both are installed in the default location ("Program Files" for 64bit apps or "Program Files x86" for 32bit apps). MySQL Workbench however only exists as a 64bit arch build.

Did you check the server path actually exists? If it does not you may have forgotten to install the new server. Simply run the MySQL Windows installer again and let it install the server.

About the left behind ProgramData content: that's probably a safety measure to avoid deleting your data unwantedly. If you don't need it anymore then just remove the 5.7 subfolder.


Loading a table with input from two different input files As part of a migration activity i need to load a table in mysql with data from two different input files. As in few fields in the table are loaded from one input file and rest from another. Also the input files are not order wise,what i mean is field 1 in input file 1 might not be the first field in the table. So data needs to be loaded into proper fields in the table from input files.
Hope my question is clear. If anyone can suggest any scripts or anything for this would be helpful
Thanks in advance.

Create a table (t1) with columns matching the columns of one file.  Load that file into it.

Create another table (t2) with columns matching the columns of the other file.  Load that file into it.

Now create the main table (if it does not already exist).  Load it with something like

INSERT INTO main (a,b,c,d,e)
    SELECT t1.x, t2.y, t1.z, t1.w, t2.v  -- in whatever order is correct
        FROM t1
        JOIN t2 ON ... -- explain how the two tables (files) need to be paired up.


Postgresql-Server won't start after moving data directory I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start.
I.e. with
sudo service postgresql restart && sudo service postgresql status

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2017-11-06 10:31:32 CET; 1min 42s ago
  Process: 8962 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 8962 (code=exited, status=0/SUCCESS)

Nov 06 10:31:32 HERCULES systemd[1]: Starting PostgreSQL RDBMS...
Nov 06 10:31:32 HERCULES systemd[1]: Started PostgreSQL RDBMS.

The logs for both versions then contain the following:
pg_ctl: could not access directory "/media/daveknave/BIGFS/PGSQL/9.5/main": Keine Berechtigung

"Keine Berechtigung" translates to "Permission denied".
ls -l /media/daveknave/BIGFS/PGSQL/9.5/main

gives
drwxrwxrwx 5 postgres postgres 4096 Sep 25 21:42 base
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 global
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_clog
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_commit_ts
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_dynshmem
drwxrwxrwx 4 postgres postgres 4096 Sep 25 21:42 pg_logical
drwxrwxrwx 4 postgres postgres 4096 Sep 25 21:42 pg_multixact
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 pg_notify
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_replslot
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_serial
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_snapshots
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 pg_stat
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_stat_tmp
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_subtrans
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_tblspc
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_twophase
-rwxrwxrwx 1 postgres postgres    4 Sep 25 21:42 PG_VERSION
drwxrwxrwx 3 postgres postgres 4096 Nov  2 10:19 pg_xlog
-rwxrwxrwx 1 postgres postgres   88 Sep 25 21:42 postgresql.auto.conf
-rwxrwxrwx 1 postgres postgres  133 Nov  3 21:07 postmaster.opts
-rwxrwxrwx 1 postgres postgres  100 Nov  3 21:07 postmaster.pid

I've set the file permissions in an attempt to solve this, however no result.
Trying to access the server using
sudo -u postgres psql -W

prompts
Password:
psql: could not connect to server: Datei oder Verzeichnis nicht gefunden
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Can anybody please help? Have any of you encountered that same issue? I've searched using google, but couldn't find any hints to solve my problem.

Verify the permissions on /main with

ls -l /media/daveknave/BIGFS/PGSQL/9.5/

Does postgres still have access to main?

Switch user to postgres with su - postgres and then navigate through the directory structure /media/daveknave/BIGFS/PGSQL/9.5/main/. Can you navigate past main into base?

Verify the file permissions beneath ..../base with the following comamnds:

ls -lR /media/daveknave/BIGFS/PGSQL/9.5/main/base > ~/postrgres_main_perm.log

and then

vi ~/postrgres_main_perm.log

If you find that the permissions are not correct in the log file, then run the following command to grant postgres all permissions to the directory ...main/base and all underlying sub-directories:

chown -Rv postgres:postgres /media/daveknave/BIGFS/PGSQL/9.5/main/base/

  Caution:
  Before you modify all permissions ensure the command works by replacing chown -Rv with something less destructive like ls -R or navigate down to the .../main directory and issue the shorter command: chown -Rv postgres:postgres base/. This will ensure you are applying the command to the correct directory.

Try restarting your PostgreSQL instance again.


Oralce: Why Do Triggers on Editioning Views in My Custom Schema Fail? According Tom Kyte on editioning views "you can put triggers on it, do to it things you would do to a table". However, my triggers on my editioning views in my custom schema don't fire. Tom doesn't get things wrong, so I must have something configured incorrectly. Any suggetions?

Create Table, Sequence and Trigger:

CREATE TABLE "MY_SCHEMA"."MY_TABLE"
   ("TABLE_KEY" NUMBER(10,0) NOT NULL ENABLE,
   "FIELD2" VARCHAR2(32) NOT NULL ENABLE,
   "FIELD3" NUMBER NOT NULL ENABLE);

CREATE SEQUENCE  "MY_SCHEMA"."MY_SEQUENCE"  MINVALUE 1
    MAXVALUE 1000000000000000000000000000 INCREMENT BY 1
    START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER "MY_TABLE_TRIGGER"
  BEFORE INSERT
  ON  "MY_SCHEMA"."MY_TABLE"       FOR EACH ROW
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.table_key FROM dual;
END my_table_trigger;

Update Editioning:

exec ad_zd_table.upgrade('MY_SCHEMA', 'MY_TABLE');

That will create an editioned view that looks like this:

CREATE OR REPLACE FORCE EDITIONING VIEW "MY_SCHEMA"."MY_TABLE#" ("TABLE_KEY", "FIELD2", "FIELD3") AS
  select TABLE_KEY TABLE_KEY, FIELD2 FIELD2, FIELD3 FIELD3 from "MY_SCHEMA"."MY_TABLE";

And it will modify the trigger so it looks like this:

CREATE OR REPLACE TRIGGER "MY_SCHEMA"."MY_TABLE_TRIGGER"
  BEFORE INSERT

 ON  "MY_SCHEMA"."MY_TABLE#"        FOR EACH ROW
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.table_key FROM dual;
END my_table_trigger;

Now we test:

INSERT INTO my_table
  (field2,
   field3)
VALUES
  ('TEST', 1234)

Result:
    ORA-01400: cannot insert NULL into (“MY_SCHEMA”.MY_TABLE”.”TABLE_KEY”)

I think I figured it out. I believe this is by design. I tested this and it works.

INSERT INTO my_table#
  (field2,
   field3)
VALUES
  ('TEST', 1234)

I believe the intention is that source code be modified to either directly reference the # view. Or reference a synonym that points to that view. I know, people are saying "reference an apps synonym" all over the web, but I didn't connect why until just now. Hope this is useful to the rest of you all.


Select Statement with Temp Column driven by Bitmask I have a role system setup that checks to see if bits are set for specific roles.

Each user has a specific assigned that is stored in our database and gives them access to different parts of the program. What I'm trying to do is pull every user from the database and add a column beside the role number with what parts they have access to.

I can do this with a case statement but that will only find one of the permissions unless I break down all the combinations of cases.

Database Setup:

Name | Role |
Jon  | 77   |

Permissions:

@ONE       = 1
@TWO       = 2
@THREE     = 4
@FOUR      = 8
@FIVE      = 16
@SIX       = 32
@SEVEN     = 64
@EIGHT     = 128
@NINE      = 256
@TEN       = 1024
@ELEVEN    = 2048
@TWELVE    = 32768
@THIRTEEN  = 65471

So for Jon I want to see:

Name | Role | Roles
Jon  | 77   | One Three Four Seven

SQL Server has bitwise operations that you can use.
The & symbol provides an AND operation comparing bit to bit. Source: https://msdn.microsoft.com/en-us/library/ms174965.aspx

Here is code (not extended to all role cases) that should work:

CREATE TABLE #t
([Name] varchar(30),
[Role] int)

INSERT INTO #t
VALUES ('Jon',77)

SELECT [Name],
    CASE
    WHEN [Role] & 1 = 1 THEN 'One '
    ELSE ''
    END
    +
    CASE
    WHEN [Role] & 2 >= 2 THEN 'Two '
    ELSE ''
    END
    +
    CASE
    WHEN [Role] & 4 >= 4 THEN 'Three '
    ELSE ''
    END
    +
    CASE
    WHEN [Role] & 8 >= 8 THEN 'Four '
    ELSE ''
    END
FROM #t

The comparison only occurs where binary digits exist for both numbers, so we only have to care about the leading 1 of the result, thus the inequality.


SQL server bulk insert fail code 5 [SQLSTATE 42000] (Error 4861) Following error occurs on running sql job.

Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861).  The step failed.

Looks like permission error. Scenario is that Source File is located on Server-A and SQL Server is installed on Server-B. How can I give access to file. SQL User is not showing on below window.

in-order to access a file in another machine using SQL Server you may give permission to the Account which you have used to start your sql-server instance.

if you have active directory on your environment make sure to start sql-server instance with an active directory user and give required permission to to that user on "Server-A".
Change the Service Startup Account for SQL Server

but if you do not have any active directory on your Environment the only way to do this is to have a user with the exact name and password of your sql-server service user on Server-A


MYSQL to other DB migration using MYSQL workbench is it possible to migrate MYSQL to other database platform like MS SQL 2012 using the MYSQL workbench migration wizard?. Everywhere the only destination DB is MYSQL, the reverse process is not explained at all.

MySQL Workbench is primarily a tool to work with the MySQL database server and hence all implemented migrations ultimatively use MySQL as target. No other target is supported.


Multiple group by with inner query in Oracle I have a table Record with three columns: p_id, score, city.

P_id       score      city

12          20       Mumbai
12          80       Delhi
12          50       Pune
13          20       Mumbai
13          30       Delhi
13          50       Pune
13          90       Hyd

Here I want to have a view with p_id, max_score, max_score_city, min_score, min_score_city. Which means for each player, I want max score and the city in which it was scored, min score and the city in which it was scored,

p_id     max_score   max_score_city     min_score   min_score_city
12          80            Delhi             20           Mumbai
13          90            Hyd               30           Mumbai

I know I need to apply group by on p_id for min and max score, but how do I join both group by and how to relate player and city in them.
I am new to SQL, can someone please help?

WITH mins AS
(SELECT P_id
    ,score AS min_score
    ,city AS min_score_city
    ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score) AS rn
)
, maxs AS
(SELECT P_id
    ,score AS max_score
    ,city AS max_score_city
    ,ROW_NUMBER() OVER (PARTITION BY P_id ORDER BY score DESC) AS rn
)
SELECT maxs.P_id
    ,maxs.max_score
    ,maxs.max_score_city
    ,mins.min_score
    ,mins.min_score_city
FROM mins
JOIN maxs ON mins.P_id = maxs.P_id
WHERE mins.rn = 1 AND maxs.rn = 1


What is the performance cost of enabling "log_lock_waits" on Postgres? I am trying to understand what is the performance cost of enabling log_lock_waits on Postgres.

As per this documentation, while log_lock_waits = on:

  Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is off. Only superusers can change this setting.

But it doesn't say how much it hinders performance and I can't find it anywhere else.

This is essentially for free, and you should enable it in all databases unless the workload is designed to have long lock waits regularly (which would be a design bug).

These error messages are written by the deadlock detector, which is expensive to run, which is why PostgreSQL doesn't run it unless someone has to wait deadlock_timeout seconds for a lock. 1 second is very long, so this should not happen normally (database transactions should be way shorter).

But the deadlock detector has to run anyway, and these log messages are just a side effect you get for free.


Can SQL Server 2016 extract node names from JSON? I want to extract a complete list of the node names and their paths from any arbitrary, well-formed JSON document stored as nvarchar value in SQL Server 2016. Is there a reliable way to do this?

For Example, for a json value:

DECLARE @json_doc nvarchar(4000) = '{"Name1":"Value1", "Name2":"Value2"}'

Get this result upon querying @json_doc:

NODE_NAME
$.Name1
$.Name2

Select [key] from default OPENJSON schema.

DECLARE @json_doc nvarchar(4000) = '{"Name1":"Value1", "Name2":"Value2"}';

SELECT [key]
FROM OPENJSON(@json_doc);
GO

| key   |
| :---- |
| Name1 |
| Name2 |

dbfiddle here


How to normalize table with multiple similar columns (day1, day2, day3...)? I have a database named BusinessPass and a website that users can use to create Business Passes. Below is the web form.

Date | Time From | Time To | Location

The problem is the table design. Below is my proposed table design.

BusinessPassId, UserID, Date1, From1, To1, Location1, Date2, From2, To2, Location2.... and so on. There would be a maximum of up to 31 days. So I would have 126 columns total. Is there a better way to design this?

The better way is the original design with 1 column for Date, From and To.
If data is large size than you can partition the table on the basis of date(month wise or day wise).

ID, Date, From, To, Location


SQL Server AG Listener issues in an AWS VPC I'm experimenting with creating a 2-node SQL Server Availability Group following Guy Glantser's excellent guide at MadeiraData where he describes a configuration with no AD domain or internal DNS.  Note that I'm building everything on AWS EC2 instances.  Everything is working well (including the certificates and logins, the AG is fine and can fail over successfully) however I cannot get the AG Listener to function properly.

Guy's instructions do not mention adding a reference to the Listener in the HOSTS files, however, I have done so.

I can connect to the Listener via the name OR its IP address ONLY from whichever is the Primary AG node.  I cannot connect to the Listener (on an unused IP address and listening on port 1433) from another node or another machine - timeout.  When I failover the AG, I can then only connect to the Listener on the "current" Primary, and the "former" Primary cannot reach it anymore.  I've been testing the connections with SSMS, UDL files and telnet to port 1433.

Am I missing something fundamental about AGs, or as I suspect, am I running into an issue with the Listener and DNS/IP addressing inside an AWS VPC?

I'm running Windows Server 2016 Datacenter edition AMI instances and SQL Server 2016 Evaluation Edition SP1.

Can you please review the AWS documentation on how to properly set up AG on their environment?
http://docs.aws.amazon.com/quickstart/latest/sql/part3.html

There are some specific steps that need to be done for this to work.
You may also want to read about the MultisubnetFailover (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery) as without using this feature it may take several minutes (depending on how much you set up your HostRecordTTL setting) for the DNS setting to be updated and the listener name start pointing to the new active node IP. On my tests, without changing the HostRecordTTL and the multisubnetfailover it was taking up to 15 minutes for the listener name to start working again after a failover so I had to play with these parameters to have something that would work for all my client applications


Understanding execution plans using TVPs I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the user chooses and I have an indexed view that accounts for 84% of the cost of the query.

The execution plan is pretty large and can't be uploaded here due to the size so I have uploaded it to here

I've spent quite a lot of time trying to optimise these queries (there are 14 of them but the core of each is much the same) and would appreciate anyone's suggestions or hints in reading them. I have also implemented the query suggested by the actual execution plan and the query was 5 times slower?

I don't think this query is about TVPs so much as complexity.  The plan has an optimizer timeout.  You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml.  These are not always a disaster, it just means the optimizer has run out of time (more iterations) to come up with a plan and picks the lowest cost one it has at that stage.  Optimizer timeouts are often a sign of over-complexity in a query and the common recommendation is to simplify, eg break it up, remove unnecessary parameters and joins etc

Looking at your query, there are 13 tables.  Looking in the plan, many of them just do a "SELECT all records" from the lookup table ( eg all records from dbo.Station for the @from and @to tables, all ticketFilter and ticketClass records ), so actually you don't need these tables and removing them does not change the result.  I understand this is a user-driven query, so you might look at approach where you only join to the tables if the user has actually applied a filter.

I can reproduce the timeout, and remove it by commenting out 4 of the tables, eg:

SELECT d.Month_Year[PeriodName], qg.Title [Service_Area], AVG((CAST(sa.Answer AS smallmoney) * 10.00)) [Average]
FROM [dbo].StaticDataView d WITH (NOEXPAND)
    INNER JOIN [dbo].[Survey] s  ON (s.CustomerJourney = d.Journey_Id)
        INNER JOIN [dbo].[SurveyAnswer] sa   ON (sa.Survey = s.ID)
            INNER JOIN [dbo].[Question] q  ON (q.ID = sa.Question)
                INNER JOIN [dbo].[QuestionGroup] qg  ON (qg.ID = q.QuestionGroupID)

    --INNER JOIN @StationFrom sfl ON (sfl.ID = d.[Source])
    --INNER JOIN @StationTo stl ON (stl.ID = d.[Dest])
    INNER JOIN @InitialScores sc ON (s.Score = sc.ID)
    INNER JOIN @RouteList rl ON (rl.ID = d.Route_ID) -- route list
    --INNER JOIN @TicketClass tc ON (tc.ID = d.Class_ID)
    --INNER JOIN @TicketTypeFilter ttl ON (ttl.ID = d.Ticket_Filter_Type_ID)
    INNER JOIN @TicketDiscount td ON (td.ID = d.Discount_Type_ID)
    INNER JOIN @Days dy ON (dy.ID = d.[DayOfWeek])

WHERE s.DateCompleted IS NOT NULL AND sa.Answer != -1
AND (d.RSID = COALESCE(@RSID, d.RSID))
AND (d.[Date] BETWEEN @Start AND @End) -- between dates
AND (d.[Time] BETWEEN @StartTime AND @EndTime) -- between times
AND ((@Direction IS NULL AND d.Direction IN (0,1)) -- Both
        OR (@Direction = 1 AND d.Direction = 1) -- North
        OR (@Direction = 0 AND d.Direction = 0)) -- South
AND ((@PassengerJourney = 0 AND d.Journey_Type_ID IN (1,2,3)) -- ALL
    OR (@PassengerJourney IN (1,2) AND @PassengerJourney = d.Journey_Type_ID) -- Single or Return
    OR (@PassengerJourney = 3 AND NULLIF(d.RSID_LEG_2,'') IS NOT NULL)) -- multi part
GROUP BY d.Month_Year, qg.Title
OPTION ( RECOMPILE )

Does this make for a better plan?  Hard to say, as in my simple rig, I can't get this query to run in more than half a second, with or without the timeout.  Even SQLFiddle doesn't seem to struggle.  Can you see much difference between the SQLFiddle repro and your setup?


CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE I am facing the following error:

ERROR:  functions in index expression must be marked IMMUTABLE

When trying to create index like this:

CREATE INDEX full_phone_number ON orders_clientphone (concat(area_code, phone));

On the other hand, when using alternative syntax for concatenation:

CREATE INDEX full_phone_number ON orders_clientphone ((area_code || phone));

Postgres is pretty much ok with that.
Both columns are defined as character varying(256).

The deciding factor for Postgres is that the function concat() is defined stable and not immutable in the system catalog pg_proc:

SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc
FROM pg_proc
WHERE proname = 'concat';

proname | provolatile | proargtypes| argtype | prosrc
--------+-------------+------------+---------+-----------
concat  | s           | 2276       | "any"   | text_concat

The manual on pg_proc.provolatile:

  provolatile tells whether the function's result depends only on its
  input arguments, or is affected by outside factors. It is i for
  "immutable" functions, which always deliver the same result for the
  same inputs. It is s for "stable" functions, whose results (for fixed
  inputs) do not change within a scan.

I also added the argument types of the function ("any") to connect to the answers of @dezso and @jjanes, which deliver the rationale behind the decision to make this function only stable. And the name of the internal function (text_concat).

Here is a related question to illustrate why immutability of index expressions is a sine qua non condition:

Intentionally corrupting an index in PostgreSQL

As for using the operator ||:

    SELECT o.oprname, o.oprleft::regtype, o.oprright::regtype, o.oprcode, p.provolatile
    FROM   pg_operator o
    JOIN   pg_proc     p ON p.oid = o.oprcode
    WHERE  oprname = '||';

 oprname |   oprleft   |  oprright   |     oprcode     | provolatile
---------+-------------+-------------+-----------------+-------------
 ||      | anyarray    | anyelement  | array_append    | i
 ||      | anyelement  | anyarray    | array_prepend   | i
 ||      | anyarray    | anyarray    | array_cat       | i
 ||      | text        | text        | textcat         | i
 ||      | bit varying | bit varying | bitcat          | i
 ||      | bytea       | bytea       | byteacat        | i
 ||      | text        | anynonarray | textanycat      | s
 ||      | anynonarray | text        | anytextcat      | s
 ||      | tsvector    | tsvector    | tsvector_concat | i
 ||      | tsquery     | tsquery     | tsquery_or      | i
 ||      | jsonb       | jsonb       | jsonb_concat    | i

The function to use internally depends on actual data type of operands. The definition of an operator includes the operands' data types in Postgres.
All of the functions are different and also different from text_concat above. || is just stable as well, when one of the operators is anynonarray. Things are not so trivial behind the curtains.

character varying(256) (like any varchar variant) is binary-coercible to text so function type resolution defaults to text.


How to do a fast simple query on a large table of postcodes I have a table with 2.5 Millions of Postcodes.

I have to populate a textbox while the user is searching for the Postcode so the query has to be very fast.

The table is composed by:

 ID (Primary key), Postcode (Unique), Population INT(11) NOT NULL

The query is:

SELECT * FROM `postcodes` WHERE substr(Postcode,1,LENGTH_OF_TERM) ='TERM'  order by population desc limit 0,10

TERM is what the user has typed and LENGTH_OF_TERM is the lenght of the TERM .

Despite i have created also an index on  Postcode the query is very slow (3-4 seconds).

How i can improve the speed of the query?

Adding a postcode, population combined index might help with the ORDER BY part of the SQL.

ALTER TABLE postcode ADD KEY postcode_population (postcode, population)

The main problem is that running a function over a column means its unable to use and index. So here should be an equivalent query that has no function over postcode.

SELECT * FROM `postcodes` WHERE Postcode LIKE 'TERM%' order by population desc limit 0,10

You can see which index is used with EXPLAIN {query}. MySQL only used one index per table in the expression.


Alternatives to "OR" in where clause I have a query like this:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '29584'
        OR ord.ClientOrderId = '29584'

The idea is that we are searching using the value '29584' and we don't really know if it is a ClientOrderId or an Identifier, so we want to search both.

This gives really really bad performance.  It pulls millions of rows (125 thousand from one and 20 Million from the other)

But this query:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ord.ClientOrderId = '01193504'

UNION

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '01193504'

has good performance.  Normally I would just do this and call it a day.

But my actual query is very large, and I would rather not call it twice (though it is still better than using the "or".)  So before I just set it up to be called twice, I thought I would ask around and see if I have other options.

It seems I should be to do some kind of left join to make this work.  But I can't seem to wrap my head around it.

Is there a way to do this in one query that has a left join instead of an "OR"?  (Or some other kind of option?)

UPDATE:
OK, I got thinking about it and I realized I could just factor out the OR into a CTE that uses the union and then use the CTE the actual query.

I will leave this open in case there is a better way to deal with it, but a CTE fixes my issue.

I think that you can rewrite that query to use a FULL OUTER JOIN:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        FULL OUTER JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
            AND ( ordIdent.Identifier = '29584'
                    OR ord.ClientOrderId = '29584')
WHERE ordIdent.Identifier IS NOT NULL AND ord.OrderId IS NOT NULL;

But why would you want to? You'll end up reading all rows from both tables with that syntax which causes the performance problem that you're trying to avoid. UNION or UNION ALL is the right way to go here. For filters like you have you can still use UNION ALL like so:

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ord.ClientOrderId = '01193504'

UNION

SELECT  ordIdent.Identifier, ord.OrderId
FROM    OrderIdentifier ordIdent
        JOIN [order] ord
            ON ordIdent.OrderId = ord.OrderId
WHERE   ordIdent.Identifier = '01193504' AND (ord.ClientOrderId
 IS NULL OR ord.ClientOrderId <> '01193504')

Sometimes there's a slight performance benefit in doing so. It depends on your data.


RMAN archives deletion I have a file system for my archives which gets full in a short span of time. I want RMAN to backup all the archives automatically and delete the archives after backing it up when the archives mount-point gets full.

Is that possible?

Yes, it is.

BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

See: Deleting Archived Redo Logs After Backups in non-CDBs

Or you can use FRA and configure an archivelog deletion policy:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK';

This way when the FRA is getting full, the database will automatically delete already backed up archivelogs.

See: CONFIGURE delalConf


How to relate two tables of data Hello, I have this simple diagram showing two tables, event table and guest table.

I created an event which has id of 1 and there are 3 guests for event id of 1. If I want to reuse the same guests for event id 2 and 3, how do I relate them using a foreign key? What if I want to remove Joe from the event 2, how do I do it so that it won't delete Joe from the table because Joe is needed for event 1? I am confused and I don't know if my question is clear enough. Do I need to add some columns to help this out?

uses a third table that will link the Event table to the Guest table :


How do I call a collection of tables with normalized (generic) information In a database design there are many fields with information that needs to be normalized. For instance for an address record countries can be normalized but also the address type (i.e. private address, billing address, business address). This leads to tables with information that, once implemented, only changes on a very occasional basis. What is the generic name to refer to these kind of tables?

These are often referred to as code tables.  It is common that the primary key will be a short code which can be used as a foreign key in tables which use the data.  Theses tables may contain additional information as required.


MySQL ODBC + setting up a DSN on windows + MySQL Workbench Sorry for the vague title, but here's my problem.

I connect to my database in MySQL Workbench using the following method : TCP/IP over SSH

However, when I try to set up the DSN on Windows 7 via:

Control Panel -> Administrative Tools ->Data Sources (ODBC)

It won't connect. My other databases that connect in MySQL Workbench using Standard TCP/IP are able to connect when I set up the DSN on Windows, but I think the SSH is throwing it off.

Could someone please let me know if it's possible to set up a DSN on windows using a connection that uses SSH?

In case anyone else has this question in the future, I just figured out that you need an SSH client like putty to do this.


Order by date then Group By by Name / Type before proceeding with the rest of results I have a data like this that came from two tables.

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/01     2019/01/01
Joe        Armstrong    BVL        2018/12/31     2018/12/31
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
Joe        Armstrong    BVL        2018/12/27     2018/12/27
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27
Joe        Armstrong    BVL        2018/12/26     2018/12/26

The data above is a result of the following query:

SELECT emp.Emp_Fname as FirstName,
emp.Emp_Lname as LastName, lt.LeaveType as LeaveType,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateFrom,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateTo
FROM py_leavetable lt
LEFT JOIN py_emp_master emp ON lt.EmpCd = emp.Emp_Cd
WHERE emp.date_resign IS NULL
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) >= 20181101
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) <= 20190130
ORDER BY lt.TimeIn DESC

I tried adding the name fields in the ORDER BY clause but it didn't change the results. So I tried using GROUP BY. However, I ended up needing to add all the fields because it keeps returning aggregate function error. I tried researching further and it seems I need to do "mathematical operations" for the GROUP BY to work but I don't think it is applicable for the data I have. But, I could be wrong since I'm not really very good at SQL.

Can anyone help me or point me in the right direction on how I can achieve the following results:

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/01     2019/01/01
Joe        Armstrong    BVL        2018/12/31     2018/12/31
Joe        Armstrong    BVL        2018/12/27     2018/12/27
Joe        Armstrong    BVL        2018/12/26     2018/12/26
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27

It would even be better if I'm able to do it like this but having the result above should do:

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2018/12/26     2019/01/02
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27

Am I making sense here? Can anyone help me figure this out? Thanks!

When adding fields to an ORDER BY clause, you must put them in the correct sequence. If your order by clause went from

ORDER BY lt.TimeIn DESC

to

ORDER BY lt.TimeIn DESC, emp.Emp_Lname, emp.Emp_Fname

then the result set will be sorted first by the TimeIn value, then by name for those rows that had matching TimeIn values.

Assuming that you want to sort by Last Name, then First Name, then Time In, you would use the order by:

ORDER BY emp.Emp_Lname, emp.Emp_Fname, lt.TimeIn DESC

This will sort first by the Last Name, then First Name, then within those sorted groups you would sort by Time In, giving you the results you are hoping for.


Database schema for video statistics I'm searching for the best way to store the following information:

There are videos with different length
There are users
Users watch videos and may stop watching videos at some point or only watch a part of the video or skip a part

I want to analyse/store which part of a video is seen more often and which are skipped.

Here are the two ideas I have

Create a table with VideoId and section1, section2, ... Each section could be 10 seconds and in every row I store the videoId and a int for each section representing the number of views.
The problem is, that I don't know which is the longest video I have to store statistics for. If it is 1h, I need 361 columns.
Create a table with VideoId, section and views. This would allow videos with all lengths but will result in a really long table.

Bonus question: How to also store which user saw which video at which positions?
 My idea: Don't store an int for views but an array of userIds?

Which option is better or perhaps my ideas are completely wrong and you have a better idea?

Idea-1 does not sound good as you have realized yourself, as each video will probably have a different length. You would have numerous rows with empty/null columns.

Idea-2 sounds better and is a more appropriate solution for an SQL database. Also regarding your bonus question, this approach would allow you to create a table with many-to-many relations users<->video_sections, thus you could have the per user, per video section data.

Also, for me, a good fit for this problem seems to be using a NoSQL database (for example MondoDB).
NoSQL is better suited to data with non-concrete structure, and also with NoSQL db it would be easier to scale horizontally in case the data grows exponentially (a concern you have identified in your Idea-2).
Here is a quick example structure that came off the top of my head:

videos: [
    {
        id: 1,
        name: Video1,
        length: 238,
        views: 3125,
        sections: [
            {
                seq_id: 1
                views: 10
            },
            {
                seq_id: 2
                views: 5
            },
            { ... },
            { ... }
        ]

    },
    {
        id: 2,
        name: Video2,
        ...
    }
]

users: [
    {
        id: 1,
        name: User1,
        videos_watched: [
            {
                video_id: 1
                sections_watched: [
                    1, 2, 3, ...
                ]
            },
            {
                video_id: 2
                sections_watched: [
                    1, 2, 3, 4, 5, ...
                ]
            }
        ]

    },
    {
        id: 2,
        name: User2,
        videos_watched: [
            ...
        ]

    },
    ...
]

With NoSQL db and this structure you will be able add sections to each specific video dynamically when needed. As each user goes through a video, section, your application logic will just increment the views for each particular section and add the video/section ID to the list of watched videos/sections for that particular user.


OOM in mysql-percona, memory leak? Every second day, I run into an OOM problem.

  Mar 21 17:31:33 iZ23h7fvujwZ kernel: Out of memory: Kill process 60346 (mysqld) score 943 or sacrifice child
  Mar 21 17:31:33 iZ23h7fvujwZ kernel: Killed process 60346, UID 498, (mysqld) total-vm:406039136kB, anon-rss:64936040kB, file-rss:5180kB

Below is my.cnf:

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]>
#pager="less -i -n -S"
no-auto-rehash

#[mysqld_multi]
#mysqld = /usr/local/mysql/bin/mysqld_safe
#mysqladmin = /usr/local/mysql/bin/mysqladmin
#log = /opt/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /opt/mysql_3306
datadir = /data/mysql_3306
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 1
max_allowed_packet = 1073741824
group_concat_max_len = 102400
#thread pool
thread_handling = pool-of-threads

#timeout
interactive_timeout = 3600
wait_timeout = 3600

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 40000
max_connect_errors = 10000

skip-name-resolve = 1
#logs
innodb_print_all_deadlocks = 1
log-output=file
slow_query_log = 1
slow_query_log_file = /data/mysql_3306/log/slow.log
log-error = /data/mysql_3306/error.log
log_warnings = 2
pid-file = /data/mysql_3306/mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog-do-db=db_use
#binlog-ignore-db = mysql
replicate_wild_do_table=db_use.%
replicate_wild_do_table=BBS.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
slave-skip-errors = all
#binlog
binlog_format = mixed
server-id = 1
log-bin = mybinlog
binlog_cache_size = 3072M
max_binlog_size = 2048M
max_binlog_cache_size = 3072M
sync_binlog = 0
expire_logs_days = 10

#relay log
#skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

#buffers & cache
table_open_cache = 4096
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 1073741824
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_repair_threads = 1
log_bin_trust_function_creators = 1

#innodb
innodb_buffer_pool_size = 35G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size=20M

Server details

CPU : 16
MEM : 62G

Server version:  Ver 14.14 Distrib 5.6.20-68.0, for Linux (x86_64) using  EditLine wrapper

Buffer pool and memory

Total memory allocated 38461767680; in additional pool allocated 0
Total memory allocated by read views 5120

We find memory allocated is 38461767680, but MySQL use memory is more and more ,never free. and when MySQL OOMs it uses total-vm:406039136kB, anon-rss:64936040kB, is more than 38461767680. Is there a memory leak? We can use flush tables free 6 g.

Any idea to fix this? Is my buffer_pool_size is too big in my.cnf?

OOM is usually a result of bad my.cnf config and actually your my.cnf overcommits memory big time. Run mysqltuner.pl to get a sense of what parameters needs tweaking. Many of your configuration values are way high and some of them doesn't even make sense.

For example:

1) binlog_cache_size 3Gb? default value is 32768 bytes(!)

According to dev.mysql.org:

  The size of the cache to hold changes to the binary log during a
  transaction. A binary log cache is allocated for each client if the
  server supports any transactional storage engines and if the server
  has the binary log enabled (--log-bin option). If you often use large
  transactions, you can increase this cache size to get better
  performance.

Do you have 3GB big transactions?

2) Per connection buffers:

read_buffer_size = 2M
read_rnd_buffer_size = 16M

Why are you optimizing for random read? These values are way too high even for a very heavily used databases.

You can easily drop these under 1MB. Maybe 256K as a start and increase if necessary.

Same goes for sort_buffer_size and join_buffer_size. 1Mb for the former and 128kb for the latter should be sufficient if your tables are well indexed for the common queries.

Especially with max_connections = 40000 these values can easily run your out of memory.

+) Innodb

innodb_log_buffer_size = 64M

Do you actually need this? You should be able to drop this to a much lower level and like 8MB or 16Mb and monitor the Innodb_log_wait status variable.

innodb_log_files_in_group = 3

You don't get any benefit of having 3. Keep it to 2.


Convert query in words to relational algebra I have a follow-up question to a question I have previously asked on SO.

Instead of the query from my initial question, I want to convert the following into relational algebra:

  List the names and phone numbers of the bidders who are not always affected by
  double-bidding.

Note: Double-bidding occurs when two different bidders bid the same price on the same exact item.

I have some ideas as to how to proceed:

Find all bidders that have double bid (1)
Find all bidders who have a bid that isn't double-bidding (2)
Find all bidders who have never placed a double-bidding bid (3)

From here, I can get the intersection of (1) and (2) and add (3) to this intersection to get the final answer. (That is my thought process, please correct me if I am wrong)

I have no issues in finding all bidders that have placed a double bid, but the consequent ideas are confusing me quite a bit. This is what I have for "all bidders that have double bid":

BID⨝ITEM         - (Q1)
Q1 ⨝ ρbid→bid', iid→iid', price→price'(Q1) - (Q2)
πbid(σbid != bid'(Q2) ∧ σprice = price'(Q2) ∧ σiid = iid'(Q2)) - (Q3)

How do I use this to find the bidders who are not always affected by double bidding?

By the way, the bolded text is merely for labeling purposes and is not part of the answer.

I think the key point here is the fact that relational algebra eliminates duplicates from its result. So if you use a Project operator to get only iid and price, it will give you the list of bids excluding the duplicate bids. If you subtract this list from the whole bids you will get the bids those are duplicates..

I am not sure though.. Would you please comment on how you feel about this approach?


SQL - Sparse data in time series [I wasn't sure if this is the proper place to post SQL questions.] I have a public dataset of pharmaceutical prices.  Any given drug gets a new price on some unpredictable day, and then the price remains that price until the next price change.
E.g.
drug            date          new_price
acetaminophen   2020-01-09    0.25
oxycontin       2020-01-10    1.40
valaxirin       2020-02-10    2.34
oranicin        2020-02-11    1.54
acetaminophen   2020-02-12    1.47

I have to do a variety of analytics e.g. "what was the price of acetaminophen on 2020-02-01?"  Well that would of course be 0.25, but I need a way to figure that out in SQL.  I have a variety of more complex queries, e.g. "list the ten cheapest drugs on a given date".  So a solution I think needs to be generalized.
I realize that one possible solution would be to run a job that populates the database with prices for every day of the year, but I prefer not to solve the problem that way.

This is basic sql
For your second query there is only 1 drug at the given day
for the rest enter Postgre and what you are searching for ijn plain woirds and you usually get a result
The secnd query wrks like this. A subquery grabs with a window functions the last update date for everys drug.
When the originbal table is joined with the drug name and date changed you get the latest price
Which will be sorted by price and grab only the 10 with the lowest prices
Schema (PostgreSQL v12)
CREATE TABLE table1 (
  "drug" VARCHAR(13),
  "date" Date,
  "new_price" FLOAT
);

INSERT INTO table1
  ("drug", "date", "new_price")
VALUES
  ('acetaminophen', '2020-01-09', '0.25'),
  ('oxycontin', '2020-01-10', '1.40'),
  ('valaxirin', '2020-02-10', '2.34'),
  ('oranicin', '2020-02-11', '1.54'),
  ('acetaminophen', '2020-02-12', '1.47');

Query #1
SELECT
     "new_price"
FROM table1
WHERE "drug" = 'acetaminophen'
AND "date"  < '2020-02-01'
ORDER BY "date" DESC
LIMIT 1;

| new_price |
| --------- |
| 0.25      |

Query #2
SELECT
    t1."drug",
    "new_price"
FROM table1 t1
INNER JOIN (SELECT
            "drug",
            MAX(date) OVER (PARTITION BY "drug"  ORDER BY "date")     AS max_date
        FROM table1
        WHERE
    "date"  <= '2020-02-10') t2 ON t1."drug" = t2."drug" AND t1."date" = t2.max_date
ORDER BY "new_price" ASC
LIMIT 10;

| drug          | new_price |
| ------------- | --------- |
| acetaminophen | 0.25      |
| oxycontin     | 1.4       |
| valaxirin     | 2.34      |

Query #3
SELECT
            "drug",
            MAX(date) OVER (PARTITION BY "drug"  ORDER BY "date")     AS max_date
        FROM table1
        WHERE
    "date"  <= '2020-02-10'
;

| drug          | max_date                 |
| ------------- | ------------------------ |
| acetaminophen | 2020-01-09T00:00:00.000Z |
| oxycontin     | 2020-01-10T00:00:00.000Z |
| valaxirin     | 2020-02-10T00:00:00.000Z |

View on DB Fiddle


Return xml sequences where an attribute doesn't contain a specific character Consider the following simple XML:

<xml>
  <customer name="Max">
    <email address="me@you.com" />
  </customer>
  <customer name="Erik">
    <email address="erik@your-mom.com" />
  </customer>
  <customer name="Brent">
    <email address="brentcom" />
  </customer>
</xml>

I want to get a list of <Customer> sequences where the address attribute of the <email> item does not contain an @.

So, I want output that looks like:

<customer name="Brent">
  <email address="brentcom" />
</customer>

mcve:

DECLARE @x XML = '<xml>
<customer name="Max"><email address="me@you.com" /></customer>
<customer name="Erik"><email address="erik@your-mom.com" /></customer>
<customer name="Brent"><email address="brentcom" /></customer>
</xml>';

This query:

SELECT WithValidEmail = @x.query('/xml/customer/email[contains(@address, "@")]')
    , WithInvalidEmail = @x.query('/xml/customer/email[contains(@address, "@")] = False');

Returns:

╔═══════════════════════════════════════╦══════════════════╗
║            WithValidEmail             ║ WithInvalidEmail ║
╠═══════════════════════════════════════╬══════════════════╣
║ <email address="me@you.com" />        ║                  ║
║ <email address="erik@your-mom.com" /> ║ false            ║
╚═══════════════════════════════════════╩══════════════════╝

This query:

SELECT WithInValidEmail = @x.query('/xml/customer/email')
WHERE @x.exist('/xml/customer/email[contains(@address, "@")]') = 0;

Returns:

╔══════════════════╗
║ WithInValidEmail ║
╚══════════════════╝
    (no results)

The WHERE clause in the query above is eliminating the entire set of XML because at least a single sequence exists where the email address contains an "@" sign.

An easy way to do this is to use the nodes method to get right to the address attribute and check for your @ sign.

The problem with the way you're looking now is that it's only checking that any email address has an @ in it. Parsing the XML nodes out lets you check individual emails for it.

DECLARE @x XML
    = '<xml>
<customer name="Max"><email address="me@you.com" /></customer>
<customer name="Erik"><email address="erik@your-mom.com" /></customer>
<customer name="Brent"><email address="brentcom" /></customer>
</xml>';

SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM   @x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;

If you need to query an actual table with an XML column like this, you'd just CROSS APPLY the nodes method like thusly:

SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
FROM @x_table AS xt
CROSS APPLY xt.x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;

If you want to bring all the <customer>...</customer> XML for that "row" back, you can walk the axis back. Just be aware that walking back can make performance a bit woogy for large XML blocks.

SELECT x.c.query('..')
FROM @x_table AS xt
CROSS APPLY xt.x.nodes('/xml/customer/email') AS x(c)
WHERE  x.c.exist('@address[contains(., "@")]') = 0;

Another way of doing it is:

SELECT @x.query('/xml/customer[email/@address[not(contains(., "@"))]]') answer

Moving the square brackets to wrap around the email node effectively make that the WHERE clause applied to the customer node.  Translating this XQuery to English looks like:

  Get me all xml/customer nodes with an email node which has an address attribute
  which does not contain the @ symbol


MongoDB uses only one index on simple query with sorting So the problem is that I'm having a huge collection and I'm trying to run a query that filters and sorts on only fields that have indexes on them.

I have these two indexes:

db.getCollection('product').createIndex({"_type": 1, "_collectivity": 1, "own_risk": 1});
db.getCollection('product').createIndex({"price": 1});

And that's the simple query I am running:

db.getCollection('product').find({
    "_type": "healthcare2",
    "_collectivity": null,
    "own_risk": 375
}).sort({price: 1}).explain()

And here's the winningPlan for this query:

{
  "winningPlan": {
    "stage": "FETCH",
    "filter": {
      "$and": [
        {
          "_collectivity": {
            "$eq": null
          }
        },
        {
          "_type": {
            "$eq": "healthcare2"
          }
        },
        {
          "own_risk": {
            "$eq": 375.0000000000000000
          }
        }
      ]
    },
    "inputStage": {
      "stage": "IXSCAN",
      "keyPattern": {
        "price": 1
      },
      "indexName": "price_1",
      "isMultiKey": false,
      "direction": "forward",
      "indexBounds": {
        "price": [
          "[MinKey, MaxKey]"
        ]
      }
    }
  }
}

So it scans through all the collection ignoring all indexes. But if I try to hint:

db.getCollection('product').find({
    "_type": "healthcare2",
    "_collectivity": null,
    "own_risk": 375
}).sort({price: 1}).hint({"_type": 1, "_collectivity": 1, "own_risk": 1})

Mongo will return me an error...

error: {
    "$err" : "Executor error: Overflow sort stage buffered data usage of 33686190 bytes exceeds internal limit of 33554432 bytes",
    "code" : 17144
}

Mongo will not try to use the price index after it sorts out all documents with the first index.

So... What is my problem? Or Mongo's? How do I make MongoDB use more than one index in one query?

My MongoDB is 3.0.6 and running in Ubuntu within Vagrant.

MongoDB 2.6+ will consider index intersection in cases where multiple indexes might improve the query performance, however for sorting the index has to have keys in common with the query predicate. In your scenario, this means an index on {price:1} cannot be used for index intersection unless price is included in the query.

  So it scans through all the collection ignoring all indexes.

What's actually happening according to the query explain() is that the price:1 index is being used to return the results in sorted order via an index scan (IXSCAN). Your compound index would require an in-memory sort of the result set, which may be larger than the size of the in-memory sort buffer (32MB) as you discovered by trying to hint that index. If you can add a limit to your query so the results are smaller than 32MB, the compound index may be usable but not ideal.

For your query, the ideal index to support the desired sort order (and an unlimited set of results) is a compound index including the price:

db.product.createIndex({"_type": 1, "_collectivity": 1, "own_risk": 1, "price": 1});

For more information, see:

Index Intersection and Sort
Use Indexes to Sort Query Results
Optimizing MongoDB Compound Indexes


Converting MongoDB instance from Standalone to Replica Set and Backing Up I want to convert my MongoDB 3.6 standalone server to a replica set so I can watch a collection using change streams, which requires an oplog. I only want to use change streams so will keep this as a single member replica set.

The problem is that my standalone instance is kind of big (~400 GB) and I do not have a backup of it.

I have a few questions:

Is such conversion considered a safe operation? Are there any caveats considering the time of "conversion", the additional memory needed etc. or does that only apply if I connect more nodes?
What are the ways of backing up such big instances? I have heard of and read about Filesystem Snapshots but I'm not sure what are they and how to handle them.

Is such conversion considered a safe operation?

Converting a standalone node to a replica set is a straightforward procedure, but if you do not have a backup of your deployment (and this data is important) I would definitely prioritise creating and testing a backup. If you only have a single copy of your data (the one being used!) you will have very limited (and possibly painful) recovery options if something unwelcome accidentally (or intentionally) happens to your data.

  Are there any caveats considering the time of "conversion", the additional memory needed etc. or does that only apply if I connect more nodes?

A replica set member will use some extra storage space and I/O for the operation log (oplog) which stores a rolling record of operations for use cases like replication and change streams. The conversion process is essentially restarting mongod with a new replSet option and then initialising the oplog and replica set configuration using rs.initate().

  What are the ways of backing up such big instances? I have heard of and read about Filesystem Snapshots but I'm not sure what are they and how to handle them.

The MongoDB documentation describes supported Backup Methods including Back Up and Restore with Filesystem Snapshots. Filesystem snapshots use system level tools that vary depending on the O/S and filesystem used for your deployment.

For example, Linux has LVM (Logical Volume Manager) which enables taking a consistent backup of a block device. The initial snapshot will have some more noticeable overhead, but subsequent snapshots are generally quick. However, snapshots typically depend on the same storage infrastructure as the original disk so it is essential that you have a plan for archiving snapshots and saving backups elsewhere. If you are using a cloud provider (Amazon, Google Cloud, Azure, ...) with network data volumes, these also have snapshot APIs.


Would appreciate some advice on using external data with SQL Server 2005 I'm a web developer with a bit of DB experience, but this one is beyond my knowledge.

We're trying to set up a new website. The data - products, prices, descriptions etc - are supplied by an external company in either .XLS, .CSV or XML format. This data is updates & distributed regularly, eg each month.

I could simply import all the data each time - the problem is we don't necessarily need all the products, so we would mark them as "unavailable" or something by creating a new field in the DB.

So, next time the data is provided, I need to import it again - but making sure it doesn't overwrite our "available" field.

My initial thought was to create 2 tables - one, "Products" which I would import the data into each time and a new table "availability" just with 2 fields - product ID and an available - yes/no field and join the tables on the product ID.

Is this a sensible approach? can XML files be read directly from SQL Server without importing?

You can import your data into a staging table then update the product reference data from that.  The product table can hold your 'available' flag.  Unfortunately you will have to import the XML data into SQL Server to load it, but SSIS has an XML reader, so you can use that to do the imports.


Do I lose query performance by using same filegroup for all partitions? Let's say the table is partitioned by Month_Key and I execute a simple

SELECT Columns FROM PartitionedTable WHERE Month_Key=201701.

I am wondering whether there are any disadvantages regarding SQL query performance when storing partitions for a partitioned table in one filegroup vs. multiple file groups?

Then, let's say that I execute multiple queries at the same time, querying different Month_Key values. Would the total execution time be lower if the table partitions are stored in multiple file groups vs. one file group?

At least for SQL Server, using multiple files or multiple filegroups on the same set of disks should have no significant impact on performance.

If you have separate drives mounted and want to spread the IO across them, then multiple files or multiple filegroups can help you do that.  But that's a separate issue.


Oracle partition by interval using persian calendar Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say:

select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian')
  from my_table;

I created a table as:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20))
partition by range(xdate)
interval(NUMTOYMINTERVAL(1, 'MONTH'))
(partition p0 values less than (to_date('13920101', 'YYYYMMDD', 'nls_calendar=persian')))
enable row movement;

the table is created normally, but when I add records to it, and oracle creates new partitions, the partitions are:

create table TEMP_TIMES (
  id    NUMBER(18) not null,
  xdate DATE not null,
  str   VARCHAR2(20)
)
partition by range (XDATE)
(
  partition P0 values less than (TO_DATE(' 2013-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P61 values less than (TO_DATE(' 2013-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P62 values less than (TO_DATE(' 2013-05-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P63 values less than (TO_DATE(' 2013-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P64 values less than (TO_DATE(' 2013-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

as you can see, the database changed the NLS_CALENDAR to gregorian (the same day as 13920101) and each partition is created according to gregorian calendar, not persian calendar.

Is there any way to force Oracle to use persian calendars for creating new partitions?

I don't see any way to have intervals that are defined in a different calendar than your database-level NLS_CALENDAR. You could get the same effect by partitioning on a numeric representation of the (Persian) month each date falls in, using a virtual column:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20),
  ydate as (to_number(to_char(xdate, 'YYYYMM', 'nls_calendar=persian')))
)
partition by range(ydate)
interval(1)
(partition p0 values less than (139201))
enable row movement;

If that is populated with a record for every day for the year following your example start date:

insert into test_temp_times (id, xdate, str)
select level, date '2013-03-20' + level, null
from dual
connect by level < 366;

The partitions that are created will look something like:

select table_name, partition_name, high_value
from user_tab_partitions where table_name = 'TEST_TEMP_TIMES';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
TEST_TEMP_TIMES                P0                             139201
TEST_TEMP_TIMES                SYS_P479                       139202
TEST_TEMP_TIMES                SYS_P480                       139203
TEST_TEMP_TIMES                SYS_P481                       139204
TEST_TEMP_TIMES                SYS_P482                       139205
TEST_TEMP_TIMES                SYS_P483                       139206
TEST_TEMP_TIMES                SYS_P484                       139207
TEST_TEMP_TIMES                SYS_P485                       139208
TEST_TEMP_TIMES                SYS_P486                       139209
TEST_TEMP_TIMES                SYS_P487                       139210
TEST_TEMP_TIMES                SYS_P488                       139211
TEST_TEMP_TIMES                SYS_P489                       139212
TEST_TEMP_TIMES                SYS_P490                       139213

 13 rows selected

And you can check which partitions the month boundaries fall in:

select utp.partition_name, min(ttt.xdate), max(ttt.xdate)
from test_temp_times ttt
join user_objects uo on uo.object_id = dbms_rowid.rowid_object(ttt.rowid)
join user_tab_partitions utp on utp.table_name = uo.object_name
and utp.partition_name = uo.subobject_name
group by utp.partition_name
order by partition_name;

PARTITION_NAME                 MIN(TTT.XDATE) MAX(TTT.XDATE)
------------------------------ -------------- --------------
P0                             2013-03-20     2013-03-20
SYS_P479                       2013-03-21     2013-04-20
SYS_P480                       2013-04-21     2013-05-21
SYS_P481                       2013-05-22     2013-06-21
SYS_P482                       2013-06-22     2013-07-22
SYS_P483                       2013-07-23     2013-08-22
SYS_P484                       2013-08-23     2013-09-22
SYS_P485                       2013-09-23     2013-10-22
SYS_P486                       2013-10-23     2013-11-21
SYS_P487                       2013-11-22     2013-12-21
SYS_P488                       2013-12-22     2014-01-20
SYS_P489                       2014-01-21     2014-02-19
SYS_P490                       2014-02-20     2014-03-19

At least, I think that's what you're trying to achieve... Unfortunately I can't add a demo as SQL Fiddle doesn't have the partitioning option, but this is tested against 11.2.0.3.

Of course, you have to make it use the partitions for the query... if I just do:

select * from test_temp_times
where xdate = date '2013-11-01';

It finds the row with plan:

-----------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     1 |    47 |   164   (0)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL|                 |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | TEST_TEMP_TIMES |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
-------------------------------------------------------------------------------------------------------

If I explicitly add the virtual column to the query:

select * from test_temp_times
where xdate = date '2013-11-01'
and ydate = to_number(to_char(date '2013-11-01', 'YYYYMM', 'nls_calendar=persian'));

Then it knows which partition to query:

----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    47 |    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|                 |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
|*  2 |   TABLE ACCESS FULL    | TEST_TEMP_TIMES |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
----------------------------------------------------------------------------------------------------------

Clearly I haven't created any indexes yet. If you are looking for a whole month's worth of data, you would only need to query on a single ydate value, and ignore xdate; but presumably you'd need a mix at least some of the time.


Temporal table syntax in a view Consider the following situation. I have a table "test" which is system versioned. I also have a table containing the last time I checked for modified rows in that table.
CREATE TABLE [Delta]
(
    Tablename NVARCHAR(50) NOT NULL,
    Proccess UNIQUEIDENTIFIER NOT NULL,
    LastDelta DATETIME2 NOT NULL
)

I now want to create a view that does this query
Fake syntax
SELECT *
FROM [test]
FOR SYSTEM_TIME AS OF (select LastDelta from Delta where TableName = 'Test')

Are the only options to supply a datetime a fixed value or parameter? A parameter won't be usefull with a view and a fixed value doesn't help either.
I could emulate the effect of "for system_time as of" with my own query, but I would like to know if there is a way I missed to do it as shown under "fake syntax" or similar.

Use a table valued function (TVF).
CREATE FUNCTION dbo.TVF_func (@_timestamp DATETIME2)
RETURNS TABLE AS
RETURN
SELECT T.*
  FROM [tests]
  FOR SYSTEM_TIME AS OF @_timestamp T;

The TVF can then be used anywhere a view can be used.
SELECT *
  FROM dbo.TVF_func('2020-10-31') T
  JOIN [etc...]

For views with complex logic where you typically select a subset of the output based on a date variable, you can get a massive speed boost from rewriting the view as a TVF instead of SELECT * FROM view V WHERE V.date = @date.


Transactional Replication with Always On via Sql Server 2014 My Scenario is given below...i want to configure transactional replication with alwayson and i know this is possible but my question is "I have two publisher sql server(SQL01_REPLICATION) is running on 32 bit operating system and one subscriber(64 bit) which is work as a primary for alwaysOn".so this is possible or not ?

SQL01_REPLICATION(32 bit os) as a PUBLISHER(sql server 2014 and window server 2008(32bit)).
SQL02_REPLICATION(32 bit os) as a PUBLISHER(sql server 2014 and window server 2008(32bit))..
SQL03_AG(64 bit os) as a PRIMARY REPLICA FOR AG AND SUBSCRIBER FOR                                REPLICATION(sql server 2014 and window server 2012(64bit))..
SQL04_AG(64 bit os) as a SECONDARY REPLICA FOR AG(sql server 2014 and window server 2012(64bit))..

According to above description of my existing server...i have a two publisher with 32 bit OS for replication and two server with 64 bit OS for AlwayOn Availability group.
my question is according to above description can i implement transactional replication with AlwaysOn.

Please advise me which technology is suitable for my current scenario.

Yes it can be done like normal.

32/64 bit can replicate to each other (you wouldn't think so but they do and Microsoft has documented it on MSDN).

Also you can have an AG as a subscriber but you must use the name of the listener when setting it up. There's an MSDN article for this as well.


DR Site availability if primary goes down I have two SQL Servers at a production site (2 node WSFC) in an Availability Group, and one SQL Server at a DR site.

If I add DR site node to existing WSFC to configure AOAG:

If both servers at the primary site go down, will WSFC will be online or offline? Will my secondary replica still be available for read-only workloads?
I am using SQL Server 2014.

I am planning to create a file share witness for quorum configuration.

I just want to make sure if the whole production site is down, will my secondary replica at DR site still be useful for the users? Will it be read-only or read-write? As it is Async, will it require manual or forced failover?

All are SQL Server 2014 instances, and are stand-alones included in AOAG. Production site is synchronous and DR site is asynchronous.

Technically, yes, you should still be able to read from the DR instance assuming it is configured as a Read Only replica and you are connecting to it directly (As read only routing through the primary would not be working).

You should really be concerned in this case though about bringing the DR site online as the new primary replica.  You would need to force quorum on the DR site, and manually failover (with possible data loss) so you can then accept reads and writes at the DR site.

Perform a Forced Manual Failover of an Availability Group (SQL Server)


How can I query a remote server using a local function? I am querying a remote server for which I only have read permissions. I set up a local server with a UDF (since I cannot create one on the remote) that I would like to utilize for the query. When I try executing the query, I receive the error

  Msg 344, Level 16, State 1Remote function reference 'localhost\SQLDEV.db.dbo.udf_StripHTML' is not allowed, and the column name 'localhost\SQLDEV' could not be found or is ambiguous.

I tried looking for solutions, but nothing seemed remotely (haha) close to my situation as all other solutions were for executing a remote function against a local query, or trying to pass variables with OPENQUERY, etc. Is there a simple way to achieve this query with only read access to the remote, master db?

SELECT [localhost\SQLDEV].[db].[dbo].[udf_StripHTML](notes.note_text)
FROM event_log AS el
INNER JOIN dbo.progress_note as notes
on notes.event_log_id = el.event_log_id

Seems (based on the name, anyway) like a pretty basic scalar UDF that just massages strings, won't be subject to a lot of changes over time, and doesn't rely on any data. So, I suggest you make a copy of the function on the source server, and fight whatever rule is preventing you from doing so (it shouldn't be hard if you can justify the use of the function, and describe what kind of hack you're trying to use as a workaround to the rule). Note that the function doesn't have to live in the same database, it just needs to be on the same server.

(Even if the function does rely on data, and it's clear in your case it doesn't, the local copy of the function could reference the data in the other server, using 4-part name(s), without referencing any functions.)

DRY is not always worth pursuing.


Stored Procedure with HINT "WITH RECOMPILE" loses performance, solved with DBCC FLUSHPROCINDB I have a Stored Procedure with the hint WITH RECOMPILE, so the Stored Procedure do not store in the cache a Query Plan for the Stored Procedure. The average time the Stored Procedure resolve the Query is between 1 and 3 second, but from time to time the Stored Procedures takes > 30 seconds and sometimes it gets timeout.
The strange thing here is that I run DBCC FLUSHPROCINDB and the Stored Procedure returns to its normal time (1 to 3 seconds).

Do you have any idea of what could be happening?

The hint WITH RECOMPLILE is telling your database to rebuild the execution plan every time. This rebuild requires some overhead, and can explain why it takes an extended period of time in some circumstances. Determining exactly why and when the recompile takes longer will require more information from the system (ambient load, how much change there is in the tables being analyzed, table contention, etc).

A more detailed explanation can be found here.
https://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why


Spatial index not used I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting in bad performance.

Example script:

--Create the table
create table Location(
    LocationID int not null identity(1,1) primary key,
    LocationPoint geometry
)

--add records
declare @counter int =0
WHILE @counter<150000
BEGIN
    set nocount on
    --select
    set @counter =@counter +1
    declare @RandomLocation geometry=geometry::Point(RAND() *1000, RAND() *1000, 0)
    insert into Location(LocationPoint) values  (@RandomLocation)
END

--create index
CREATE SPATIAL INDEX SPATIAL_StructureBE ON dbo.Location(LocationPoint)
USING GEOMETRY_GRID WITH (
    BOUNDING_BOX  = (xmin  = 0.0, ymin  = 0.0, xmax  = 1000, ymax  = 1000),
    GRIDS = ( LEVEL_1  = MEDIUM, LEVEL_2  = MEDIUM, LEVEL_3  = MEDIUM, LEVEL_4  = MEDIUM),
    CELLS_PER_OBJECT  = 16,
    STATISTICS_NORECOMPUTE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)

--Search, this query should use the index but it doesn't
declare @CurrentLocation geometry=geometry::Point(24,50, 0)
select top 1 *
from Location
order by LocationPoint.STDistance(@CurrentLocation) asc

Correct. Spatial indexes don't get leveraged in that situation, sadly.

Spatial indexes provide a set of grids, allow the system to identify geometries (or geographies) that overlap these grids.

Your best bet is to set a threshold of acceptable closeness, and try that, using something like STBuffer. STIntersects works well, and you can increase this threshold if nothing is found (eg, using a second OUTER APPLY if nothing is found.

Edit: They do now! Check my post on it... http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/

In summary, to get your index to be used, you need to make sure that your ORDER BY value can't be NULL - just add a WHERE...IS NOT NULL clause and it should work.


Use table in database like in excel I'm writing a program for my client in C# Winform. And the client gave this excel sheet.

Process

I have a table that used to calculate BULK amount of alcohol by "DIP" and "SLIDE".
In excel I need to select the specific DIP and SLIDE to get the BULK. I need to do the same thing in the database.

I exported a table from excel to MySQL table.

I can get the BULK amount by this query

SELECT SLIDE 0.8 FROM tbl_vat_01_alcohol_esa WHERE DIP = 212;

Requirement

I need to get the DIP and/or SLIDE when I enter BULK amount. I wanna know how to do it? or Is it possible to do that kind of work in this table? or do I need to change the table structure or something.

OR

How to access the excel sheet from C# winform. I mean, If I make a form that contain 3 textbox for DIP, SLIDE and BULK.
When I enter DIP and SLIDE I get BULK and when I enter BULK I get DIP and SLIDE...

Is it possible? If it is, How to achieve that? OR Is there any other method.

I'm new to StackExchange and Database!

what you have is a cross-table you need to uncross it.

create a table with three columns bulk,dip,and slide, put each cell of the excel table into a row of this table.

then you can query like

select bulk from bdstable where slide=0.8 and dip=212