About creating a View With additional coumns (that are not in the table)

Can you not just create a new view that is

SELECT *, 0 AS "ABC"
FROM VIEW_IRS1

Or if you mean the text 'False'

SELECT *, 'FALSE' AS "ABC"
FROM VIEW_IRS1

Or to edit the existing view:

SELECT
    MBR.PRSN_MBRSH_ID
    , Atr.Ssn_Tx
    , Names.First_Na
    , Names.Middle_Na
    , Names.Last_Na
    , Reff.Ref_Cd_Desc_Tx Suffix_Na
    , 'FALSE' AS "My Really Long Name"
FROM
    AllYourOtherTables


Two indexes

Simply create two separate indexes. PostgreSQL will use both where appropriate.

CREATE INDEX ON table1 (account);
CREATE INDEX ON table1 USING GIN (json);

Using an extension

Or you can use the btree_gin.

  btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, "char", varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.

It looks like this,

CREATE EXTENSION btree_gin;
CREATE INDEX ON table1 USING gin (account,json);

Under normal circumstances, I'd likely use two indexes.


How to grant SYSDBA access to users on Oracle Cloud v19c

In short, you can't. You need to use PDBADMIN user or PDB_DBA role for such things: https://docs.oracle.com/en-us/iaas/exadata/doc/eccmanagingdatabases.html
Also there is a very useful document:
Known Issues for Oracle Database Exadata Express Cloud Service
It gives some more internal info about cloud access and privileges.


What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data.
A clustered index don't have the concept of included columns since all columns are in the clustered index - the clustered index being the data. So you have the key columns (what you use to "drive the query", like the WHERE clause, so to speak); and the rest of the columns are in the leaf.
You can think of the non-key columns in a clustered index just like included columns in a non-clustered index.
Don't confuse the PK with the clustered index. You decide whether the PK should come with a clustered index or a non-clustered index.
You might have a better candidate for the clustered index than the PK. Fine! Just make the PK non-clustered and create the clustered index on that better candidate.


Cannot access MySQL database after moving files in data directory

Ok, the problem was because I didn't copy the ibdata* file. You may expect this file to be located within the respective database folders just like the other related files, but you would be wrong.

My ibdata file was located at mysql/data/ibdata1; as soon as I copied that to the new location everything was good again.

Note however I have read of this file being located in different locations, so your mileage may vary.


I had the same issue on SQL Server 2017 CU 11 (latest as of today)  with Windows Server 2016. After rebooting the secondary replica, some of the databases in the AG show not synchronizing. I used ALTER database SET HARD RESUME, but it didn't work. The work around is to restart the SQL Server Services on the secondary replica. Then all databases show synchronized again.


Is there a good reason to store big text data outside database?

You might want to consider moving the data outside the database if:

You are having problems with backup durations
It causes disk or network throughput problems on your database server
You have a requirement to access the data without using the database

In general, I'd suggest keeping it in the database unless you can come up with a good reason not to.

There's some overlap between this question and "Files - in the database or not?". You might find some useful thoughts there, though things that are true for binary files don't necessarily hold true for text data.

MySQL offers some useful features where text is stored in the database, such as opportunities to use full-text indexing, and network compression.


How do I add databases from a crashed MySQL 5.1 server to a new MySQL 5.6 server

I have resolved this issue using the instructions posted here: http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html

I was unaware that by default all data in InnoDB tables across all databases on the server are stored in one single file at the root of the data directory: ibdata1.  That file, and I believe the log files, must also be copied along with the folders containing the FRM files (which apparently just store the table structure).  The side effect of this, of course, is that any InnoDB tables already defined on your new server will be rendered unusable.


Assuming 11g.
There are a few ways to do this. Easiest is to use the Oracle Job Scheduler with the Remote Job Agent. That agent can run using credentials for a qualified OS account. You could run a job that selects data from the database and spools it in the required output directory.

You could also copy a file to the Remote Job agent. This is closest to the code flow you described. For docu and examples see the book I wrote about the Oracle Scheduler.

There are no ways to switch OS accounts directly in pl/sql because whatever you do in the database, you are using the credentials of the Oracle Database Instance. The Oracle Remote Job Agent can use own credentials and is accessible from within pl/sql by using the dbms_scheduler package.


Is there a way to run parameterized update statements using only DB2 commandline and bash?

If you are on 10.1 or later you can use INGEST. It is almost as fast as load, more feature rich and has less impact on a running system. First, you need to enable it by creating control tables (if you haven't already):

db2 'call sysproc.sysinstallobjects ( 'INGEST' ,'C' , null, null )';

Now you can ingest the data like:

db2 "ingest from file tab1.del
 format delimited (
     $colb ...,
     $colc ...
 ) update tableA set colA = null
   where (colb, colc) = ($colb, $colc)"

Here is an developerworks article:

https://www.ibm.com/developerworks/data/library/techarticle/dm-1304ingestcmd/index.html

and here is the documentation:

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0057198.html


SUGGESTED QUERY

SELECT
    CONCAT(A.ndx,' - ',A.ndx+9) "score range",
    IFNULL(B.rowcount ,0) "number of occurrences"
FROM
(
    SELECT 0 ndx UNION SELECT 10 UNION SELECT 20 UNION
    SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60
    UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
) A
LEFT JOIN
(
    SELECT ndx,COUNT(1) rowcount FROM
    (SELECT FLOOR(score/10)*10 ndx FROM yoshi_scores) AA
    GROUP BY ndx
) B USING (ndx);

SAMPLE DATA

use test
DROP TABLE IF EXISTS yoshi_scores;
CREATE TABLE yoshi_scores
(id int not null auto_increment,
score int not null,primary key (id), key (score));
INSERT INTO yoshi_scores (score) VALUES
(97),(74),(22),(98),(65),(62),(47),(64),(82),( 8),(60),(12),
(27),(14),(13),(28),(60),(12),(27),(34),(32),(89),(15),( 2);

Data would look like this

mysql> SELECT * FROM yoshi_scores ORDER BY id;
+----+-------+
| id | score |
+----+-------+
|  1 |    97 |
|  2 |    74 |
|  3 |    22 |
|  4 |    98 |
|  5 |    65 |
|  6 |    62 |
|  7 |    47 |
|  8 |    64 |
|  9 |    82 |
| 10 |     8 |
| 11 |    60 |
| 12 |    12 |
| 13 |    27 |
| 14 |    14 |
| 15 |    13 |
| 16 |    28 |
| 17 |    60 |
| 18 |    12 |
| 19 |    27 |
| 20 |    34 |
| 21 |    32 |
| 22 |    89 |
| 23 |    15 |
| 24 |     2 |
+----+-------+
24 rows in set (0.00 sec)

SUGGESTED QUERY EXECUTED

mysql> SELECT
    ->     CONCAT(A.ndx,' - ',A.ndx+9) "score range",
    ->     IFNULL(B.rowcount ,0) "number of occurrences"
    -> FROM
    -> (
    ->     SELECT 0 ndx UNION SELECT 10 UNION SELECT 20 UNION
    ->     SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60
    ->     UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
    -> ) A
    -> LEFT JOIN
    -> (
    ->     SELECT ndx,COUNT(1) rowcount FROM
    ->     (SELECT FLOOR(score/10)*10 ndx FROM yoshi_scores) AA
    ->     GROUP BY ndx
    -> ) B USING (ndx);
+-------------+-----------------------+
| score range | number of occurrences |
+-------------+-----------------------+
| 0 - 9       |                     2 |
| 10 - 19     |                     5 |
| 20 - 29     |                     4 |
| 30 - 39     |                     2 |
| 40 - 49     |                     1 |
| 50 - 59     |                     0 |
| 60 - 69     |                     5 |
| 70 - 79     |                     1 |
| 80 - 89     |                     2 |
| 90 - 99     |                     2 |
+-------------+-----------------------+
10 rows in set (0.00 sec)

mysql>

Why is the Query designed this way ??? Look at the first subquery

(
    SELECT 0 ndx UNION SELECT 10 UNION SELECT 20 UNION
    SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60
    UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
) A

I performed a LEFT JOIN of this to the counts for a reason. Notice that the dataset has nothing in the 50 - 59 range. Its count would not show up in the second query:

mysql> SELECT ndx,COUNT(1) rowcount FROM
    -> (SELECT FLOOR(score/10)*10 ndx FROM yoshi_scores) AA
    -> GROUP BY ndx;
+------+----------+
| ndx  | rowcount |
+------+----------+
|    0 |        2 |
|   10 |        5 |
|   20 |        4 |
|   30 |        2 |
|   40 |        1 |
|   60 |        5 |
|   70 |        1 |
|   80 |        2 |
|   90 |        2 |
+------+----------+
9 rows in set (0.00 sec)

mysql>

I am sure you wanted the range 50 - 59 to show up, so the query is designd to catch all ranges. Any missing range is essentially defaulted to zero.

GIVE IT A TRY !!!

CAVEAT: Unfortunately, the first subquery requires you to hardcode it. It will be the only hardcoding required to make it work.


Database design for holding read/unread content state

It may be better to mark unread entries - read entries will accumulate over time. Unread entries you could kill after let"s say 90 days (show all stuff older than 90 days as read to avoid buildup.

You may also want to just have a top marker per forum - i.e. you can not have a specific post unread. This will seriously cut down on the number of database tnreis.


Please change the directive to one of the following:

replicate_wild_ignore_table=phpmyadmin.%

or

replicate_ignore_db=phpmyadmin.%

mysql database

No need to do mysql. Why? Doing GRANT and REVOKE commands will bypass replicate_wild_ignore_table=mysql.% because the SQL does not explicitly mention mysql schema tables.

This will get by replicate_wild_ignore_table=mysql.%:

GRANT ALL PRIVILEGES ON *.* to rolando@locahost;

This will get caught by replicate_wild_ignore_table=mysql.%:

INSERT INTO mysql.user VALUES (...);

If you want to keep replicate_wild_ignore_table=mysql.%, I suggest the following:

SET sql_log_bin = 0; INSERT INTO mysql.user VALUES (...);

This will prevent the SQL from being recorded in the master's binary logs. Consequently, all SQL executed in the DB Session after SET sql_log_bin = 0; will not replicate.

information_schema database

As far as the information_schema database, mysqld uses it to monitor database metadata. Each is unique to the MySQL instance. They never replicate intrinsically because you have the option to keep different table on Master and Slave. If the information_schema was replicated, then creating replication schemes such as

using the BLACKHOLE storage engine for Star Topologies
using an all-InnoDB Master and an all-MyISAM slave

would be impossible for Slaves to handle.

SUMMARY

Doing

replicate_wild_ignore_table=phpmyadmin.%

or

replicate_ignore_db=phpmyadmin.%

should be all you need. Notwithstanding, make sure any SQL that prepends phpmyadmin to all it table names may still slide by if the default database is not phpmyadmin.


Postgres Performance Over Group by with MAX and MIN

The following indexes may help you:

CREATE INDEX stkx_custloc_ind1
  ON customer_location
  USING btree
  (location_id, customer_id);

CREATE INDEX stkx_custrecpt_ind1
  ON customer_receipt
  USING btree
  (customer_id, receipt_id);

CREATE INDEX stkx_recptprod_ind1
  ON receipt_product
  USING btree
  (receipt_id, status COLLATE pg_catalog."default", is_active, txn_dt_tm);

CREATE INDEX stkx_recptprod_ind2
  ON receipt_product
  USING btree
  (receipt_id, txn_dt_tm, status COLLATE pg_catalog."default", is_active);

Please note, in addition, that your query may be better tuned with an inline view:

SELECT
        cr.customer_id,
        min(min_txn_dt_tm),
        max(max_txn_dt_tm)
FROM customer_location cl
JOIN customer_receipt cr ON cl.customer_id = cr.customer_id AND cl.location_id = 'UUID HERE'
JOIN (
        select re_pr.receipt_id, min(txn_dt_tm) as min_txn_dt_tm, max(txn_dt_tm) as max_txn_dt_tm from receipt_product re_pr where status = 'PURCHASED'
    AND is_active = true group by re_pr.receipt_id) rp ON rp.receipt_id = cr.receipt_id
GROUP BY cr.customer_id
;

Your schema suffers from having the 'id' antipattern.  With this you have made a programmer's life easier until the application allows you to enter unexpected data, such as multiple customers having the same receipts.

I hope this helps!


SSRS 2016 Datasource - SQL Server 2000 database

I just created a data source from SSRS 2016 to a 2000 database so yes it is possible. However, as the commenters above stated it isn't supported.


Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB

A 1 GB database is next to nothing as far as storage is concerned.  If you are really looking to save space at that level, I'd be apt to question the environment specifics.  Even on a small box, 1 GB is negligible.

I recommend you read up on the pit falls of shrinking your database files (more specifically your data file[s]).  Paul Randal wrote a great article on this very topic.  The moral here is to know what is happening and what you are doing to your data.  If that is acceptable, and the business demands the smaller database then proceed cautiously.  I will quote Paul Randal with his recommended practice in a situation like this:

  So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?

  The method I like to recommend is as follows:
   - Create a new filegroup
   - Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON  syntax, to move the tables and remove fragmentation from them at the same time
   - Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

  Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.

Will your database never need to grow to this size again?  This is the main question that should be on the forefront of your mind when you consider shrinking a database.  It's all about proper sizing, not just for today, but for the long-term foreseeable future.

And, most importantly, do not make this part of routine maintenance.  For reasons why, see Paul's blog post.


I'm not sure why bunches wouldn't be its own table (I think that would make this problem easier), but I'll run with it as is. If I'm understanding the problem correctly, assuming you wanted bunch 1, something like this should do the trick:

SELECT p.image
FROM photos p
  JOIN album a
    ON p.album = a.id
WHERE a.bunch = 1
ORDER BY a.id, a.seq, p.id


PostgreSQL batch inserts fail silent randomly

With the help of the author of the script i used (sqlserver2pgsql) we realized it was a (new) issue with how Kettle handled parallel output table truncates.

https://github.com/dalibo/sqlserver2pgsql/issues/57#issuecomment-282675275

What happened was, and the reason i only lacked even hundreds of data, that the output job in some cases did run truncate after one or more workers already had done inserts on the table


how to check and list all the files related to mongodb in ubuntu? I am looking for config files, password informations for mongodb

As noted in the install page, the MongoDB distribution is comprised of several packages:

mongodb-org: This package is a metapackage that will automatically install the four component packages listed below.
mongodb-org-server: This package contains the mongod daemon and associated configuration and init scripts.
mongodb-org-mongos: This package contains the mongos daemon.
mongodb-org-shell: This package contains the mongo shell.
mongodb-org-tools: This package contains the following MongoDB tools: mongoimport, bsondump, mongodump, mongoexport, mongofiles, mongooplog, mongoperf, mongorestore, mongostat, and mongotop.

Typically you would want to install all of these in your development environment, but for production you may only want to have a subset (i.e. perhaps you don't want the tools or shell installed on every server).

To list the contents of an installed package, use dpkg-query --listfiles (you can also use -L as an alias for --listfiles):

dpkg-query --listfiles mongodb-org-server
/.
/usr
/usr/bin
/usr/bin/mongod
/usr/share
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/mongod.1.gz
/usr/share/doc
/usr/share/doc/mongodb-org-server
/usr/share/doc/mongodb-org-server/GNU-AGPL-3.0.gz
/usr/share/doc/mongodb-org-server/THIRD-PARTY-NOTICES.gz
/usr/share/doc/mongodb-org-server/copyright
/usr/share/doc/mongodb-org-server/changelog.gz
/usr/share/doc/mongodb-org-server/README
/usr/share/lintian
/usr/share/lintian/overrides
/usr/share/lintian/overrides/mongodb-org-server
/etc
/etc/mongod.conf
/etc/init
/etc/init/mongod.conf
/etc/init.d
/etc/init.d/mongod

You can also specify multiple packages, for example:

 dpkg-query --listfiles mongodb-org-server mongodb-org-shell mongodb-org-tools


I am getting this error : "Error 102 : non-boolean type specified in a context where a condition is expected" for this request :

You can solve it by putting First name between brackets:
delete from MYDB where [First name]='" + first.Text + "'";

create table t (id int, [First name] varchar(100));
insert into t values (1, 'John Doe');
GO
delete from t where First name = 'John Doe';
GO

Msg 4145 Level 15 State 1 Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'name'.

db<>fiddle here


Deleting the password file itself (without replacing it) and then doing a subsequent lookup of v$pwfile_users removes all of the sys-privileged users from the CBD, but users from my PDB still remain in the lookup. Are PDB passwords stored somewhere other than that file? How are they showing up if the file no longer exists?

The password of PDB users(local users) as well as common users with administrative privileges are stored in data dictionary in encrypted form.

SQL> select password from sys.user$ where name='JAY';

PASSWORD
--------------------------------------------------------------------------------
4FF80DF8D8DBE02E

SQL> select username, common from dba_users where username='JAY';

USERNAME      COM
---------     ---
JAY           NO

If no password file exit common user is unable to get sysdba privilege.

SQL> create user testuser identified by testuser container=all;

User created.

SQL> grant sysdba to testuser;
grant sysdba to testuser
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

Unable to add common users to the password file as I have removed the password file prior to creating this user.

But assigning SYSDBA to local users is possible!

SQL> alter session set container=orapdb1;

Session altered.

SQL> create user newuser identified by newuser container=current;

User created.

SQL> grant sysdba to newuser;

Grant succeeded.

It confirms that the password for PDB administrative users are stored in data dictionary rather password file.

  The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.

Same applies here. As PDB administrative privileged users(local users) are not stored in password file IGNORECASE and ENTRIES do not apply to them.

  In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.

It allows us to store the password of the privileged user until the Operating System block is filled(generally up to 4 passwords per block) as per the Oracle Documentation. I have demonstrated this behavior after the excerpt from Oracle Documentation.

  ENTRIES

  This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

I have created password as:

[oracle@ora12c dbs]$ orapwd file=orapworacdb entries=2

Enter password for SYS:

Then I created four test users and tried to grant the SYSDBA privilege but the grant failed for fourth user. Becuase there were already four users with SYSDBA privilege including SYS.

SQL> select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                TRUE  TRUE  FALSE FALSE FALSE FALSE      0
C##_ABC                TRUE  FALSE FALSE FALSE FALSE FALSE      0
C##_ABC2               TRUE  FALSE FALSE FALSE FALSE FALSE      0
C##_ABC3               TRUE  FALSE FALSE FALSE FALSE FALSE      0

When I tried to grant SYSDBA to fourth user C##_ABC4 I got the following error.

SQL> grant sysdba to c##_abc4 container=all;
grant sysdba to c##_abc4 container=all
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapworacdb' is full

  I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).

It depends upon how you have granted the SYSOPER role. To shutdown the database instance user should have SYSOPER privilege on all containers.

The following demonstrate the scenario.

I have common user call C##_TEST.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:47:05 2016

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select common from dba_users where username='C##_TEST';

COM
---
YES

I have granted SYSOPER to the user for the ORACDB(CDB) only.

SQL> grant sysoper to c##_test container=current;

Grant succeeded.

Then I have connected to container database using C##_TEST user as SYSOPER and tried to shut down the database and got the ORA-01031 error.

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:05 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
ORA-01031: insufficient privileges
SQL>

Now I again granted the SYSOPER privilege with the option container=all. And this time the user can shut down the database.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:36 2016

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant sysoper to c##_test container=all;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:50:06 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


ON CONFLICT on two columns where one can be null

Because PostgreSQL can not infer it from the values, you need the index_predicate

  index_predicate
  Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format. SELECT privilege on any column appearing within index_predicate is required.

Sample Data

CREATE TABLE tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
VALUES (1, 3, false); -- set value to false

Test

INSERT INTO tbl(col1, col2)
VALUES (1, 3)
ON CONFLICT(col1, col2)
WHERE col2 IS NOT NULL  -- predicate here
DO UPDATE SET col3 = true;

TABLE tbl;

 col1 | col2 | col3
------+------+------
    1 |    3 | t
(1 row)


Postgres: What could cause the error "cannot call populate_composite on a scalar"?

Unrelated, but: you can simplify your query to:

SELECT x.*
from the_table,
     jsonb_array_elements(jsonbrecords) AS t(doc),
     jsonb_to_record(t.doc) as x ("grade" text, "userId" text, "endYear" int, "startYear" int);

Now for the actual question. The error can be avoided by not using jsonb_to_record and accessing each key individually instead:

SELECT t.doc ->> 'grade' as "grade",
       t.doc ->> 'endYear' as "endYear",
       t.doc ->> 'startYear' as "startYear",
       t.doc ->> 'userId' as "userId"
from the_table,
     jsonb_array_elements(jsonbrecords) AS t(doc);

You don't really lose flexibility as you need to specify the column list with jsonb_to_record() as well.

Online example: http://rextester.com/VVGJ34083


Trying to disable xp_Cmdshell and rpc_out and and receiving mixed results

You can see the actual value with the following tiny script:

SELECT *
FROM sys.configurations
where name='xp_cmdshell'

You can also do it this way to see all advanced options:

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;


Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance

I think your only option here is to 'roll your own' pseudo-multicolumn stats - as extended statistics cannot be created on Virtual Columns. For example:

testbed:

drop table table1;
create table table1(col1 integer, col2 char(1));
insert into table1(col1,col2) select mod(level,10), '0' from dual connect by level<=1000;
insert into table1(col1,col2) select mod(level,10)+100, '1' from dual connect by level<=1000;
commit;
select count(*) from table1 where col1=1 and col2='0';
/*
COUNT(*)
--------
     100
*/

first try with normal histograms (note 'rows' estimate is 'poor'):

exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col1=1 and col2='0';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    50 |   250 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |    50 |   250 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=1 AND "COL2"='0')
*/

now create virtual concatenated column (note 'rows' estimate is 'good'):

alter table table1 add col21 generated always as (col2||col1);
exec dbms_stats.gather_table_stats(null,'TABLE1');
explain plan for select * from table1 where col21='01';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   100 |   900 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   100 |   900 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL21"='01')
*/

finally repeat with NLS parameters set:

alter session set nls_sort='BINARY_CI';
alter session set nls_comp='LINGUISTIC';
explain plan for select * from table1 where col21='01';
select * from table(dbms_xplan.display);
/*
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   100 |   900 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   100 |   900 |    59   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("COL21",'nls_sort=''BINARY_CI''')=HEXTORAW('303100
              ') )
*/


How I can monitor Amazon RDS Postgresql table size once the `hot_standby_feedback` is on

force manually an autovacumm policy on mergency situations

This is not going to work. hot_standby_feedback doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough".  No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first.

You can use pg_total_relation_size to get the table size.  You can take a query like the below, and just plug it into our favorite monitoring solution.

select pg_total_relation_size('pgbench_accounts');

But that still leaves the question of what to do about it.


implement geo / spatial / proximity search with php and mysql --- what have you done successfully

I think using Latitude and Longitude is the best bet. Because when you go global, then zipcode might not be very accurate search. There is a very good article about using spatial indexes and the Point type database fields. You can read here
http://vinsol.com/blog/2011/08/30/geoproximity-search-with-mysql/


Merge two rows from a table in a single row

@a_horse_with-no_name's query improved:

SELECT "contactId",
        email,
        company
FROM
    ( SELECT "contactId", "value" AS email
      FROM "contactFields"
      WHERE "fieldType" = 'email'
        AND "order" = 0
    ) AS e
  FULL JOIN
    ( SELECT "contactId", "value" AS company
      FROM "contactFields"
      WHERE "fieldType" = 'company'
        AND "order" = 0
    ) AS c
  USING ("contactId") ;


What you expected to have actually exists in Eloquent. In order to attach/detach related models you need to call attach/detach methods on the relations and pass a single ID or array of IDs:

$user->roles()->attach($roleId);
$user->roles()->attach([$roleId1, $roleId2]);
$role->users()->attach($userId);
$role->users()->attach([$userId1, $userId2]);

See some more info at http://laravel.com/docs/5.1/eloquent-relationships#inserting-re lated-models - the examples are pretty close to your case, as it's also about user-role many-to-many relationship.


SQL server not using the memory allocated

You have a odd system. Your database size is 350+ G and you have 32 bit system  I would say this is a system which I would never like to have in my environment. Its very difficult to manage 350 G database on 32 bit SQL Server which has VAS limit(by default) of 2 G. You are bound to face memory pressure going ahead.

AWE in 32 bit system only allows SQL Server to use memory beyond its VAS capabilities and that too only data and index pages can take advantage of extra memory by AWE. Plan cache, procedure cache and other caches cannot utilize this memory.

  I know Task manager is not a best way, but RAMMAP is also not working, but we believe SQL is not using the allocated memory

This is not clear, what do you mean by believe, can you show some facts how you reached to conclusion that SQL Server is not using memory. Please run

DBCC MEMORYSTATUS()

command and share output on some shared link(onedrive,dropbox...) and post link into your question so that we can see how much memory SQL server is using.

Edit:

  But when i looked on task manager and process explorer for private bytes, memory never got exceeded to 300-500 MB.

This is the incorrect thing you are doing. Task manager is not a place to see SQL Server memory utilization we have dbcc memorystatus output and perfmon counters to see SQL Server 2005 memory utilization. If you note task manager show Process private bytes this memory is pageable but memory allocated via AWE API in 32 bit is Locked and non pageable and non shareable. So task manager is not showing you TOTAL memory utilized by SQL Server as it is not tracking memory allocated via AWE. So I guess you got the difference now. Please read AWE how it functions in 32 bit.

Now from dbcc memorystatus dump you posted

Memory Manager   KB
VM Reserved     1632608
VM Committed    169000
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory utilized by SQL Server 2005 would be sum of VM committed and AWE Allocated. This would not give you absolute value but is correct. There are some more allocations which are not included in this but that would be just few MB and would hardly matter

So memory utilized by SQL Server is (169000+13762560)KB which is approx 13G. Which matches with max server limit you have set. So don't worry SQL Server is using memory allocated to it.

Plus whatever may be result please upgrade to 64 bit version of both Windows server and SQL Server


How to get the total days per month between two dates?

Here's my attempt. Still think there are unanswered questions in your sample data, though.

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20120302',  @e = '20120605';

;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

Results:

Month  Days
-----  ----
March    29
April    30
May      31
June      5


What is the locking behavior for Updatable/Insertable Views?

The short answer is that it depends on the isolation level of your connection and/or statement being run.

If you are taking the default isolation level (which is usually Cursor Stability - CS) in DB2, then I would assume it only puts a lock on the actual rows being modified (as an update lock) and it puts a read lock on the row currently being held by the cursor. All other rows are not technically locked in any sort of way.

Now if you go up higher into Read Stability (RS) or Repeatable Read (RR), then you start to get into where it will lock more rows in the result set (and even the whole table in a join case)....

You may wish to check out the Isolation Level settings in the DB2 Information Center, as this will give you a better picture of how it will act.

Also note, even though there is a default isolation level for the connection, this can be overridden in the session, as well as at the statement level, including the underlying statement that builds the view. So if the default is CS, but the view was built with a SELECT that uses RR, then you are using RR and probably locking on all rows in both the result set and all rows utilized by the engine to create the result set. I'm guessing this is not likely (or at least hopefully someone didn't write the view that way....) and you are using CS, in which what I explained at the beginning is more likely how it behaves.


When should a primary key be declared non-clustered?

The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'?

And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index certainly do not need to use lookups to satisfy some of the projected columns and/or predicates.

Another piece of the puzzle is how can an index be used? There are three typical patterns:

probes, when a single key value is seek-ed in the index
range scans, when a range of key values is retrieved
order by requirements, when an index can satisfy an order by w/o requiring a stop-and-go sort

So if you analyze your expected load (the queries) and discover that a large number of queries would use a particular index because they use a certain pattern of access that benefits from an index, it makes sense to propose that index as the clustered index.

Yet another factor is that the clustered index key is the lookup key used by all non-clustered indices and therefore a wide clustered index key creates a ripple effect and widens all the non-clustered indices and wide indices mean more pages, more I/O, more memory, less goodness.

A good clustered index is stable, it does not change during the lifetime of the entity, because a change in the clustered index key values means the row has to be deleted and inserted back.

And a good clustered index grows in order not randomly (each newly inserted key value is larger than the preceding value) as to avoid page splits and fragmentation (without messing around with FILLFACTORs).

So now that we know what a good clustered index key is, does the primary key (which is a data modelling logical property) match the requirements? If yes, then the PK should be clustered. If no, then the PK should be non-clustered.

To give an example, consider a sales facts table. Each entry has an ID that is the primary key. But the vast majority of queries ask for data between a date and another date, therefore the best clustered index key would be the sales date, not the ID. Another example of having a different clustered index from the primary key is a very low selectivity key, like a 'category', or a 'state', a key with only very few distinct values. Having a clustered index key with this low selectivity key as the leftmost key, e.g. (state, id), often makes sense because of ranges scans that look for all entries in a particular 'state'.

One last note about the possibility of a non-clustered primary key over a heap (i.e. there is no clustered index at all). This may be a valid scenario, the typical reason is when bulk insert performance is critical, since heaps have significantly better bulk insert throughput when compared to clustered indices.


CREATE DATABASE vs. CREATE ANY DATABASE permissions

I believe I have found the authoritative answer, helped greatly by the table provided in this article: http://msdn.microsoft.com/en-us/library/ms178569.aspx. It would seem CREATE DATABASE is indeed the ANSI SQL compliant permission, while CREATE ANY DATABASE is an MSSQL proprietary server permission. However, the two are not the same. In fact, there is a rather important distinction between the two, and I found this out by trying it.

They are both permissions, but CREATE DATABASE only consults the security context of the currently used database, while CREATE ANY DATABASE can lookup the list of logins on the SQL Server. This appears to be implemented because the general expectation for an ANSI SQL server is that permissions are conferred in some main system database, with users in that database being granted permissions. Then the default behavior of new databases is to consult master to see what permissions it ought to inherit. (SQL 101, I know, but it’s really exemplified here.)

So, when you grant CREATE DATABASE, you must actually have a user in master to grant this permission to. If you try this, it works (create a user in master, grant it create database, then you can make databases). However, by granting create any database (or granting the role dbcreator), you do not need to be a literal user in master.

To further illustrate the difference between the two permissions, observe the differing messages returned by granting a user CREATE DATABASE on master vs. another database, then rinse and repeat for the permission CREATE ANY DATABASE.

use master; GRANT CREATE DATABASE to TestUser

  Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser',
  because it does not exist or you do not have permission.

This error happens because the user TestUser is not yet in the master database. This is a database-level permission – it has no context to go outside the selected database and look for logins or users. Note that if you create the user in master first, this command succeeds, and you can create databases just as if CREATE ANY DATABASE was granted to you.

use master; GRANT CREATE ANY DATABASE to TestUser

  Command(s) completed successfully.

This succeeds because, as a proprietary Microsoft SQL Server permission, CREATE ANY DATABASE can consult the list of logins for the SQL instance, not just the users in the currently used database.

use test; GRANT CREATE DATABASE to TestUser

  Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser',
  because it does not exist or you do not have permission.

This demonstrates the consistency of my explanation for the first example. Note the below query and its resultant error message. You will not receive this error message if you first create the user in this database (see the last query).

use test; GRANT CREATE ANY DATABASE to TestUser

  Msg 4621, Level 16, State 10, Line 1 Permissions at the server scope
  can only be granted when the current database is master

As shown in the second query, CREATE ANY DATABASE consults the list of logins, where I had already created this login name. So while SQL Server acknowledged the existence of the user, it still errors out because I am attempting to grant a server-level permission somewhere other than master, which is not allowed in MSSQL.

use test; create user TestUser; grant create database to TestUser

  CREATE DATABASE permission can only be granted in the master database.
  Msg 0, Level 11, State 0, Line 0 A severe error occurred on the
  current command.  The results, if any, should be discarded.

Now that there is a user for CREATE DATABASE to apply to, I am given the generic error message that you cannot give server-level permissions anywhere but master, because that is where SQL Server stores these permissions.

Well, this was fun.


AWS RDS responds with (Got an error reading communication packets). Works locally

I encountered this in our QA environment. I spent a great deal of time enabling AWS RDS Audit plugin, improving performance and setting logs everywhere.
The way to ressolve it was to do a proper handling of the JDBC connections from the Java client app.
Please refer to the following question that relates to what you are asking here.
RDS has a lot of messages got an error reading communication packets


integer variable for query string - option(optimize for variables)

The problem is with the line that builds the dynamic query, specifically here:

'... @v3 = '+@instance+'))'

The @instance variable is an int variable. The int type has higher precedence than any string type, and so it changes the meaning of the + operation, which now stands for mathematical addition rather than string concatenation. In other words, you are effectively trying to add a string, which is your query, to an integer, which is the @instance variable. Since the string fails to convert to an integer, you get the error shown in your question.

Solution: change the @instance in that fragment to CAST(@instance AS varchar(11)) (or CAST(@instance AS nvarchar(11))), like this:

'... @v3 = ' + CAST(@instance AS varchar(11)) + '))'


Your cast isn't doing what you think it should.

select cast(cast(100 as binary) as int) equals 100.  In other words, you are casting the decimal value 100 to binary, not taking '100' in base-2 notation.

Your expectation is actually this:

Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARY)
set @a = @a | cast (5 as BINARY)
select cast (@a as integer)

Incidentally, if this is new development, I very highly recommend against bit shifting in SQL Server.  It's slow, difficult to maintain, and violates first normal form.  If it's not, I'll guess that you probably know this already...

To convert binary to decimal, you can create a function.  There are other ways of going from integer to display base-2 notation, but this feels like it would be quite slow.


Introduce partitions into an existing non-partitioned table

I think this document will demonstrate the steps needed. IT is not a non-trivial thing to do.


(Timescale cofounder here)

It sounds like you are trying to perform a "last point" query, e.g., find the latest record submitted by each (device, sensor).  The challenge with the query as above is that the database doesn't know when it has found the last device/sensor, so needs to scan back to the start of time to conclude there are no new devices around.

Much better is the following approach, which performs a LATERAL SCAN from a list of devices against the time-series records:

https://docs.timescale.com/latest/using-timescaledb/reading-data#last-point

Good luck, and you might also find our Slack group at https://slack.timescale.com useful for these types of questions.


This is your query:

Select vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock) join
     ABC [vgender] with (nolock)
     on vname.id = vgender.id join
     ABC [vindustry] with (nolock)
     on vgender.id = vindustry.id
where vname.field = 'Name' and
      vgender.field = 'Gender' and
      vindustry.field = 'Industry';

You can try indexes on this query.  I would try ABC(id, field, value) and ABC(field, id, value).

However, you might also try the following:

select max(case when field = 'Name' then value end) as Name,
       max(case when field = 'Gender' then value end) as Gender,
       max(case when field = 'Industry' then value end) as Industry
where field in ('Name', 'Gender', 'Industry')
from ABC
group by id;


How could I get the value as Y or N in one table if value is exists on another that meets certain condition?

Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list:

SELECT
    c.customer_id,
    p.product_id,
    CASE WHEN EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              )
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c ;

or a LEFT JOIN to a dummy table with one row (the dual does that in Oracle):

SELECT
    c.customer_id,
    p.product_id,
    CASE WHEN d.dummy IS NOT NULL
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c
  LEFT JOIN
    dual  d
      ON EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              ) ;

Random notes:

It's not very good to use reserved keywords in table or column names, like the order. Because you'll have to quote them every time they are used.


If you are on the same system where Oracle database is installed and you have logged in to the system using a correct privileged user then you can bypass the password.

  Please note that the path of the ORACLE_HOME and ORACLE_SID is
  properly configured. Also, TNSLISTNER and SQLNET files are properly
  configured.

You can directly log into DB as a sysdba using the following command:

sqlplus / as sysdba


PL/pgSQL issues when function used twice (caching problem ?)

Explanation:

You declare user_info as record. The manual:

  Record variables are similar to row-type variables, but they have no
  predefined structure. They take on the actual row structure of the row
  they are assigned during a SELECT or FOR command. The substructure of
  a record variable can change each time it is assigned to.

Bold emphasis mine.

You assign the record user_info and then derive a row type (actually called row in your code) from it in the RETURN statement. This is all fine and dandy, until you assign columns of different data types to the record in the next call within the same session. This is incompatible with the cached query plan of the prepared statement and raises an exception.

PL/pgSQL treats all SQL statements in the function body as prepared statements. The query plan for prepared statements is cached for the duration of the session unless any involved objects are changed (including the function itself), which deallocates all depending prepared statements. This explains why the next invocation after ALTER FUNCTION always worked. More explanation:

PostgreSQL Stored Procedure Performance

Solution

There are various ways around this. You found some yourself already. Just avoid feeding parameters of different data type to the same (prepared) statement

The simple solution would be to cast to the same data type. You didn't provide table definitions, I assume users.id and history.id are integer and match just fine. Judging from the error message I assume roles.name is varchar, so I cast the string literal 'Role1' to varchar as well and everything should work. (You might actually mean 'Role2', but that's orthogonal to the problem.)

An untyped string literal is coerced to a matching data type in some types of SQL statements where a data type can be derived from the context. But that's not the case here. Without explicit cast the string literal is type unknown, which is not the same as varchar (or text). This also shows in your error message.

CREATE FUNCTION sp_connect(mail varchar, passwd varchar, role varchar)
  RETURNS json AS
$func$
DECLARE
   user_info record;
BEGIN
  IF role = 'Role1' THEN
    SELECT u.id, r.name INTO user_info
    FROM   users u
    JOIN   users_roles ur ON ur.user_id = u.id
    JOIN   roles r ON ur.role_id = r.id
    WHERE  u.email = mail
    AND    u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex')
    AND    r.name = 'Role1';

    RAISE NOTICE 'Role1: user_info.big_id: %; user_info.name: %'
                , pg_typeof(user_info.big_id), pg_typeof(user_info.name);  -- see data types
  ELSIF role = 'Role2' THEN
    SELECT h.id, 'Role1'::varchar AS name INTO user_info  -- Cast! And did you mean 'Role2'?
    FROM   history h
    WHERE  h.email = mail
    AND    h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex');

    RAISE NOTICE 'Role2: %: user_info.big_id: %; user_info.name: %'
                , pg_typeof(user_info.big_id), pg_typeof(user_info.name);  -- see data types
  END IF;

  IF NOT FOUND THEN
      RAISE 'USER_NOT_FOUND';
  ELSE
      RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
  END IF;
END
$func$  LANGUAGE plpgsql STABLE;

The function can be STABLE, that's the correct volatility.

I also added RAISE NOTICE statements to show data types, which should help you debug. Note that the plan for the RAISE statement itself is cached as well, so a single RAISE after END IF would be subject to the same problem.


Finding the names and the year of the movies that have at least 5 actors

This code works in T-SQL and should work in Ms Access:

SELECT COUNT(M.MovieID) as 'Count', M.MovieName, M.Year FROM Movies M
INNER JOIN ActorsMovies AM ON AM.MovieID = M.MovieID
INNER JOIN Actors A ON AM.ActorID = A.ActorID
GROUP BY M.MovieID, M.MovieName, M.Year
HAVING COUNT(M.MovieID) > 5

I also recommend not to use Year as a field name because it's a reserverd keyword in Ms Access and may cause problems:
https://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx


Creating Report Subscriptions on report that are using a Shared data source that uses Windows Integrated Security

Community wiki answer:

You have two choices:

Change the data sources to use a SQL login and password, then you can use the built in subscription method.
Use a scheduled app to generate the report, and have that do the e-mailing.  The scheduled app would need to run under an account with permissions into SQL/SSRS.

I would only recommend #2 if you don't really need/want the users managing their own subscriptions.  If the users are going to need the ability to generate their own subs then #1 is your only real option.


I suspect that you didn't run this script as user_migration, but as a different user.

Then the ALTER DEFAULT PRIVILEGES statements won't have the desired effect.

You should write:

ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

Without the FOR ROLE user_migration, it will only affect tables created by the user that executed the statement.


Converting SQL Server queries to PostgreSQL

Since PostgreSQL doesn't use the same traditional "ID" concept on Tables like SQL Server, rather it uses Sequences, getting the last value is a little different but there are a multitude of ways to do so as noticed in this well versed StackOverflow answer.
Specifically the functions you can leverage are CURRVAL and LASTVAL.
Relevant info from the aforementioned and linked StackOverflow answer:
CURRVAL():
INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

The name of the sequence must be known. Caveat: currval() only works
after an INSERT (which has executed nextval() ), in the same session.

LASTVAL():

This is similar to the previous, only that you don't need to specify
the sequence name: it looks for the most recent modified sequence
(always inside your session, same caveat as above.)


Extract part of string based on nth instance of character

Well, one way would be to use an ordered split function:

CREATE FUNCTION dbo.SplitStringsOrdered
(
    @List       NVARCHAR(2000),
    @Delimiter  NVARCHAR(32)
)
RETURNS TABLE
AS
    RETURN
    (
      SELECT rn = ROW_NUMBER() OVER (ORDER BY Number), Item
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
          CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_objects) AS n(Number)
        WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
      ) AS y);

Sample usage:

DECLARE @x TABLE(filepath NVARCHAR(2000));

INSERT @x VALUES
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS'),
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerB\Tlog\model_TLOG_20120321_1038.SLS'),
('\\fileServerA\d$\LiteSpeed\Saturday\ServerA\TLog\DBA_2_TLOG_20120811_1538.SLS'),
('\\fileServerA\d$\LiteSpeed\Friday\ServerB\Tlog\DB3_TLOG_20120914_2330.BAK'),
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerC\Tlog\DB44_TLOG_20120815_1445.BAK');

SELECT x.filepath, s.Item FROM @x AS x
  CROSS APPLY dbo.SplitStringsOrdered(x.filepath, '\') AS s
  WHERE s.rn = 7;

Results:

filepath                                     Item
-------------------------------------------  -------
\\file...eed\Wednesday\ServerA\Tlog\DBA1...  ServerA
\\file...eed\Wednesday\ServerB\Tlog\mode...  ServerB
\\file...eed\Saturday\ServerA\TLog\DBA_2...  ServerA
\\file...eed\Friday\ServerB\Tlog\DB3_TLO...  ServerB
\\file...eed\Wednesday\ServerC\Tlog\DB44...  ServerC


You can simple use UNION ALL:

SELECT model, MIN(price) price
FROM YourTable
GROUP BY model
UNION ALL
SELECT model, MAX(price) price
FROM YourTable
GROUP BY model;


How does "OtherServer" in SSDT database references work?

I just tried this locally and it resolves fine and also complains about the one column that doesn't exist.

Check your reference properties I have highlighted the ones used in the four part name.


Adding new data source connection only contains sql server in dropdown in SSRS 2016

It is a limitation that you are hitting for Reporting Services in SQL Server Express

  Report data sources must be SQL Server relational databases that run locally in SQL Server Express.


Strange query results for DELETE with ROW_NUMBER

This is expected behaviour at the moment

  the function gets evaluated on the DELETE stream.

So it actually behaves like this (pseudo code)

DELETE k
OUTPUT Deleted.name,
       ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r
FROM (
    SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #o k
) k
WHERE r <> 1 --OUTPUT returns rows with (r = 1)

Although this is the currently defined expected behaviour it isn't really reasonable and they say

  Long term, we need to actually fix the behavior of OUTPUT clause to
  match that of the ANSI SQL standard which will result in change of
  results. So we will look at the correct semantics for a future version
  of SQL Server since there might be apps that rely on the current
  behavior.

I haven't tested on SQL Server 2014 but on 2012 the plan looks as the below.

After the delete operator (to the left) the column values from the deleted rows are sorted back into object_id order and the row_number re-applied.

It looks like the same is happening in your case from the results. (temp tables have a negative id and are sorted first before sysrscols which has a low positive object_id of 3).

As well as the dubious semantics of the result the second sort by object_id doesn't seem strictly necessary in this plan as it looks likely that they will already be sorted in that order in any event.

Regarding workarounds for this specific case changing the output clause to OUTPUT Deleted.name, 1 + Deleted.r AS r would work.

For more complicated WHERE clauses I think you'd need a pass to calculate the row_number and then a join. e.g.

ALTER TABLE #o
  ADD CONSTRAINT PK PRIMARY KEY (object_id);

WITH k AS
(
 SELECT *,
        ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #o
)
MERGE k AS k1
using k AS k2
ON k1.object_id = k2.object_id
WHEN matched AND k2.r <> 1 THEN
  DELETE
OUTPUT Deleted.name,
       k2.r;

Results


DB2: database hangs while trying to execute simple select query on specific table

I am not sure if I understand your problem. You say that the database hangs on a query, that means that all tables, views, etc, are blocked. However, you say that other tables are accessible, that means that the database is not hung, it is just an access to one table.

You used "db2 restart db", however it is for other things, for example when you need to restart the database once it has crashed, but I think your db has not crashed, just it is blocked.

I think your problem is a concurrency problem, the database has to be processing a big query on the table, putting an exclusive lock (or something similar) and the other queries cannot read the data with the current isolation level.

In order to see if this is the problem, try to get snapshots to see which table are accessed and check the lock list.

You can change the isolation level in the query, for example adding "WITH UR" at the end, if this isolation level is appropriated.

Other problems "could" be related to transaction logs (flush issue), busy or not enough agents, prefetchers issue, small bufferpool, expensive access plan. Well, you have to see the db2diag to have more clues about your problem.


There is no definitive answer here because each solution has its own pros and cons.

CDC

Easier to setup and you don’t need to worry about creating triggers
and storing trigger data
Available only in SQL Server Enterprise edition so price may be an
issue

Triggers

More flexibility to adopt this to your needs (you can audit sql
server users, applications and more…)
More management and planning will be required upfront. You need to design the triggers, figure out how to store history data, how to do
reporting and probably more…

One of these two is enough – you don’t need to implement both.

If you decide to use triggers I’d recommend you find a third party tool that can generate triggers for you, create reports and such. I know that ApexSQL Audit can do this but note that it’s a premium tool (has a free trial though).


Does Microsoft provide paid support for SQL Express?

I can't find any great documentation to back this up other than articles referring to SQL Server 2005 like this one here.

I also just tried to create a ticket for SQL Server Express, and the support site lists Express as an option. You can see for yourself by starting here. I've chosen support, developer or IT support, SQL Server, then SQL Server 2016 and finally the option for edition came up and I chose Express.

Microsoft provides support to customers of SQL Server express in the same manner as SQL Server Standard and Enterprise - if you have a support agreement with hours or pay for hours, you'll receive support. If an item is found to be a bug in the product, typically the case hours or money are refunded/not applied, if it is user error/application issue on your end/etc the hours are used like normal. SQL Server licenses of the paid versions don't automatically give you access to support. Support is a separate offering, effectively.


Optimising query: not sure why LOWER() index isn't used

Two different indexes for your query:
As you found out yourself, you had the column comment_notifications.username indexed, which is irrelevant for the case. You need this one instead:
CREATE INDEX c_lower_commenter_username_idx
ON public.comments (lower(commenter_username));

More importantly, the plan also shows a sequential scan on comment_notifications - the far bigger table. Another index on (comment_id) seems to be missing.
Or, tailored to the query, this partial multicolumn index:
CREATE INDEX cn_comment_id_resolved_at_idx
ON public.comment_notifications(comment_id DESC, resolved_at DESC)
WHERE resolved_at IS NOT NULL;

Run ANALYZE on the tables after creating these (and possibly deleting the ones you had).
Adding WHERE resolved_at IS NOT NULL only makes sense if a sizable proportion of rows with NULL are excluded.
If you can change ORDER BY, the second index can be used much more efficiently, yet:
SELECT cn.id, cn.resolved_at, cn.username, cn.comment_id, cn.inserted_at, cn.updated_at
     , c.id, c.github_id, c.commit_sha, c.body, c.commented_at, c.commenter_username, c.path, c.position, c.payload, c.inserted_at, c.updated_at
FROM   comments              c
JOIN   comment_notifications cn ON cn.comment_id = c.id
WHERE  lower(c.commenter_username) = 'henrik'
AND    cn.resolved_at IS NOT NULL
ORDER  BY cn.comment_id DESC, cn.resolved_at DESC
LIMIT  300  -- or 150 or whatever
If you only added AND cn.resolved_at IS NOT NULL to get rid of the (few) rows with NULL being listed first, there is a more elegant solution with NULLS LAST.
Query:
SELECT ...
...
WHERE  lower(c.commenter_username) = 'henrik'
ORDER  BY cn.comment_id DESC, cn.resolved_at DESC NULLS LAST
LIMIT  300  -- or 150 or whatever
Matching index:
CREATE INDEX cn_comment_id_resolved_at_idx
ON public.comment_notifications(comment_id DESC, resolved_at DESC NULLS LAST);

See:

Sort by column ASC, but NULL values first?

Aside 1: Why the cast to text? What's the actual data type of username and commenter_username? (Should be text to begin with.) And why the need for lower()? User names should not be saved with varying upper/lower case spellings to begin with.
Aside 2: The explain plan shows low cardinalities. Indexes pay for big tables. Not so much for small tables, where queries are fast either way. 2 ms doesn't seem too bad. 150 ms seems to be a misunderstanding, including network latency or display times.


Grouping categorical values in a sequence

Using value_1 to order the row, you can make groups every time name changes.

Then use this groups to get max and min values.

select name, min(v1) v1, max(v2) v2
from (select name, v1, v2,
             sum(rst) over (order by v1) grp
      from (select name, v1, v2,
                   case when coalesce(lag(name) over (order by v1), '') <> name then 1 end rst
            from cat
            ) y
      ) z
group by name, grp
order by v1;

name | v1 | v2
:--- | -: | -:
John |  1 |  9
Mike |  9 | 17
John | 17 | 25
Dave | 25 | 29

dbfiddle here


Search every column in every table in Sybase Database

I was being tasked to do similar task recently. IT is not easy in especially SybaseASE.
Option 1 :
You can write custom tool to BCP out data in files and then grep those files to search for particular text that you want to look for. You can use PERL regex to do as well or use native Unix/Linux commands to parse those files and find the required text that you want to look for.
Option 2 :
Another option (which is not that great - quick and dirty way using TSQL ) that worked for me .
Note: This will take long time (slow and unoptimized) depending on your database size, hardware, workload running on the server as well as database layout.
TEST, TEST and TEST it before hand !!
--- run below script and copy the output and run it in another query window.
set nocount on
declare @searchvalue varchar(255)
select @searchvalue = 'test string to search'---- replace this .. !!
print 'set nocount on'
print 'go'
print 'create table #results (table_name sysname, column_name sysname)'
print 'go"

select
   'insert #results select distinct '''
   + object_name(c.id)
   + ''' as table_name, '''
   + c.name
   + ''' as column_name from '
   + object_name(c.id)
   + ' where '
   + c.name
   + ' LIKE ''%'
   + @searchvalue
   + '%'''
   + char(10)
from
   syscolumns c, sysobjects o
where
   c.usertype in (
-- only look for char, varchar, text etc datatypes as we are not interested in int, datetime etc....
    1
    ,2
    ,18
    ,19
    ,24
    ,25
    ,42
    )
and o.type ='U' -- only user tables we are interested in
and c.id = o.id
and c.length>=datalength(@searchvalue )
--and object_name(c.id) = 'some table if you want to filter'

print 'select * from #results'
print 'go"
go


SSIS Connection String Expression at Run Time and during Execution

When a package receives a signal to start, one of the tasks it performs is to ensure everything it needs is available and has the appropriate signatures. SSIS is all about the metadata. The idea behind this is that a package should blow up before it does any work if it can tell at the start that an error will occur (can't connect to a server, table doesn't exist, data type is incompatible, etc). It's generally a good thing, why start something you know you can't finish and potentially leave yourself in a messy/inconsistent state.

In your scenario, it is validating that this file is there and since it isn't, because it's generated at run-time, it fails. Therefore, you have to flag it such that the validation occurs right at the moment the resource is needed. As @mike Henderson indicated, you'll need to go into the properties of the objects and change the DelayValidation value from False to True. You will probably need to set it on both the Connection Manager and the Data Flow Task that uses it. Click once on the item, hit F4 or right click and select Properties.


how to stop OracleRemExecServiceV2 from cmd?

Use a command line started with administrator privileges.


What is the difference between PAGEIOLATCH_* and IO_COMPLETION?

PAGEIOLATCH_*

I am quoting from Paul Randal's blog post:

  SQL Server has two types of reads: logical I/Os and physical I/Os.
  When the Access Methods portion of the Storage Engine needs to access
  a page, it asks the Buffer Pool for a pointer to the page in memory
  (called a logical I/O) and the Buffer Pool checks through its metadata
  to see if that page is already in memory.

  If the page is in memory, the Buffer Pool gives the Access Methods the
  pointer, and the I/O remains a logical I/O. If the page is not in
  memory, the Buffer Pool issues a "real" I/O (called a physical I/O)
  and the thread has to wait for it to complete – incurring a
  PAGEIOLATCH_XX wait. Once the I/O completes and the pointer is
  available, the thread is notified and can continue running.

IO_COMPLETION

Again quoting from a webpage that belongs to sqlskills.com.

This wait type represents a variety of synchronous read and write operations in data files are not related to tables, plus reads from the transaction log.

What do non-data page IOs mean?

Few examples from Paul Randal's article will make it clear what nod-data page means.

Reading log blocks from the transaction log (during any operation that causes the log to be read from disk – e.g. recovery)
Reading allocation bitmaps from disk (e.g. GAM, SGAM, PFS pages) during many operations (e.g. recovery, DB startup, restore)
Writing intermediate sort buffers to disk (these are called ‘Bobs’)
Reading and writing merge results from/to disk during a merge join
Reading and writing eager spools to disk
Reading VLF headers from the transaction log


DBA_USERS (Table) will give you access to WHEN your user was created, but AFAIK the only way to get WHO created your user is if auditing is turned on.

This also requires you to have access to the DBA_AUDIT_TRAIL table.

SELECT OS_USERNAME,
   USERNAME AS USER_WHO_CREATED_YOU,
   USERHOST,
   TERMINAL,
   TIMESTAMP,
   OBJ_NAME AS USER_CREATED,
   ACTION_NAME
 FROM DBA_AUDIT_TRAIL
 WHERE
TIMESTAMP > SYSDATE-90
AND ACTION_NAME ='CREATE USER'
--AND OBJ_NAME = 'YOURUSERNAMEGOESHERE'

Further Reading: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3056.htm#i1619732


Is there any performance advantage in using CTE for ROW_NUMBER query

The answer is in your question:

  For my current tables, both are giving same execution plan and time.

When two plans are identical, neither of the queries that produced them can possibly have any performance advantage over the other. The times being in your case identical as well only confirms that.

The above should at least be true for actual (as opposed to estimated) plans, but I believe it is perfectly safe to assume that simply rewriting a derived table as a Common Table Expression without incurring any other change is not going to either speed up or slow down your query a tiniest bit in SQL Server.

That is not to say a CTE cannot have advantages of other nature. One of the most commonly (although, perhaps, still not universally) acknowledged advantages of a CTE over a derived table is readability. I will not go into much detail here as this is beyond the scope of the question but basically, a CTE lets you split your query into logical steps more cleanly, as well as enables you to follow the logic of an unfamiliar script more naturally.


This one might work better:

SELECT
*
FROM
items AS i
INNER JOIN (SELECT id_item, COUNT(*) AS count_subs FROM subscriptions GROUP BY id_item) counts
      ON i.id = counts.id_item
WHERE
....

Post the result of the query with EXPLAIN keyword in front of SELECT. Then we can see, if and how indexes are used.


This is the same issue as described here (for MySQL): High CPU usage from MySQL with no queries at all running

sudo date -s now

Caused by leap second and a bug in the kernel.


SQL query to list all student users, add column to show who were logged in at least once

Looks like your trying to see if a record for the userid exists in the mdl_user_lastaccess table, you can achieve that with a left join, try this

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid

Another approach using the exists statement

select u.*,
       1 as loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
 where exists (
    select 1
      from mdl_user_lastaccess ul
     where u.id = ul.userid
 )

union

select u.*,
       0 as loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
 where not exists (
    select 1
      from mdl_user_lastaccess ul
     where u.id = ul.userid
 )


Is mongoexport more optimized for querying than running the same query with a driver like "mongodb" for nodejs?

Mongoexport connects to the MongoDB server using the Go driver.
Any operation it performs can also be performed by any other user.
If there is no filter provided, it will hint to use {$natural:1} to read the documents in the order they appear on disk, which is more efficient than using an index when reading the entire collection.
You should be able to discover the exact queries and options used by any particular run of mongoexport by setting slowms to 0, or by increasing the logging verbosity of the query component.
Once you know what queries it uses for your use cases, you can compare that with what you could do with your driver to determine which is more efficient.


Database transaction consistency on PostgreSQL

serial is defined as such in PostgreSQL documentation:

  The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

They also provide this warning:

  Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

And if you go over to nextval documentation you get:

  Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.

The sequence is a shared object, among all your content. Each call to INSERT will by default call the underlying sequence nextval and will provide one value, irrespective to what happened in the transaction just before. So the value will just depend exactly on the order of operations, and not which transaction started first or things like that.

You could as well get this result (depending on what else is happening elsewhere in your DB at the same time):

 1 | tart     | tart@warm.com
 7 | raspy    | raspy@sun.com
13 | tart123  | tart123@warm123.com
42 | raspy123 | raspy123@sun123.com

Or

 1 | tart     | tart@warm.com
 3 | raspy    | raspy@sun.com
 2 | tart123  | tart123@warm123.com
 4 | raspy123 | raspy123@sun123.com

(which is probably what you expected, but has no specific guarantee to happen).

A sequence just guarantees you to "never" get the same value in two separate places, it does not guarantee lack of holes or monotony in increments, especially accross multiple transactions.


How often does the slow_log table get updated in MySQL?

By default, slow queries are not recorded anywhere.
There are three(3) ways to activate the Slow Query Log
ACTIVATION #1 : Text File
To log slow queries to a text file, please add the following to the /etc/my.cnf
[mysqld]
long-query-time=5
log-output=FILE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log

ACTIVATION #2 : CSV Table (mysql.slow_log)
To log slow queries to mysql.slow_log, please add the following to the /etc/my.cnf
[mysqld]
long-query-time=5
log-output=TABLE
slow-query-log

ACTIVATION #3 : Both Methods (Text File and Table)
[mysqld]
long-query-time=5
log-output=FILE,TABLE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log

SUMMARY
Once you edit /etc/my.cnf (or my.ini for Windows), you must restart mysql

For Linux, service mysql restart
For Windows
net stop mysql
net start mysql

If you would like to test the slow log after the mysql restart, simply run this:
SELECT SLEEP(12);

If long-query-time >= 12, this statement should land the the slow log.
Here are the variables used in the above methods

long-query-time (Set this to 0 so everything and its grandmother lands in the Slow Query Log)
log-output
slow-query-log
slow-query-log-file

For more information on Slow Query Logging, please read the MySQL Documentation

The Slow Query Log
Selecting General Query and Slow Query Log Output Destinations

CAVEAT
If you decide to use mysql.slow_log, keep in mind that the default mysql.slow_log is a CSV table. You can query against mysql.slow_log, but the table has no index.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)

mysql>

If you would like the slow log to be a MyISAM so you can query it like any other table, please run the following:
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Here is what you should see afterwards:
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.slow_log ADD INDEX (start_time);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

You can read my past answers on this:

Nov 18, 2011 : Create a custom log table in MySQL
Mar 30, 2012 : MySQL slow log configuration


Oracle impdb on windows generates a subdirectory

Ask your System admin to grant read write permission on the directory (where dump is located) and run cmd as admin and then try to run impdp command.

For more details :
http://www.dba-oracle.com/t_ora_31640_unable_to_open_dump_file_for_read.htm


SQL Server 2008, switching to simple recovery mode

What exactly do you mean by "remove the transaction log entirely"? I only ask because if you mean that literally the only possible answer is "You can't do that and have a working database".

This is a good article to explain the implications of changes to recovery mode and what it does for you.

Can't remember if all the steps are needed, but what I did last time was change recover mode to simple, carry out a backup then carry out a file shrink operation on the database (Right click on the database in the management centre and choose the option to shrink files that's in there somewhere)


Split comma seperated values and concatenate with other row values in MYSQL

SELECT DISTINCT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers,',',num),',',-1),'_sub_',id) results
FROM `table`, (SELECT 1 num UNION ALL
               SELECT 2     UNION ALL
               SELECT 3     UNION ALL
               SELECT 4) nums

The numbers count in nums subquery is to be equal or greater than max values count in CSV field. If values count is fixed and numbers count is equal to it, you may remove DISTINCT.

If MySQL version is 8+, the numbers pseudotable can be generated in CTE.


How to keep long multiple of questions and answers in database?

Assuming question has just one correct answer and possible answers don't have to be different for each set of tests, you can do something like (it's a rough idea, I might under (or over) engineer some concepts):

Question : id, question_text, correct_answer_id (FK to answer.id)
Answer   : id, answer_text
Question_possible_answer (question_id (FK to question), answer_id(FK to anwer))
Test     : id, test_name
Test_question : id, test_id(FK to Test), Question (FK to Question)
QUESTIONNAIRE (id, customer_id, test_id, questionnaire_date)   -- I assume that a customer may pass test more than once, so no unique key on (customer_id, test_id), but rather on
(customer_id, test_id,questionnaire_date)
QUESTIONNAIRE_Answer (questionnaire_id (FK to QUESTIONNAIRE), test_question_id(FK to Test_question), answer_id(FK to Answer))

Now for each question you add some possible answers in Question_possible_answer table, then you create a new test and populate Test_question table with the questions. You create a new QUESTIONNAIRE by linking test and customer, all answers from this customer go to QUESTIONNAIRE_Answer table.


How to remove temp tables from SQL Server?

Temporary Tables are dropped when the session ends. So, they will be dropped when you close your SSMS window, or your application session ends.

If you are working in SSMS developing code (and keeping the session open) then you'll need to drop them explicitly via...

drop #tempTable

Another common method, to prevent you from attempting to drop a Temporary Table that doesn't exists, is to check if it exists first.

if object_id('tempdb..#tempTable') is not null
drop table #tempTable

create table #tempTable...
or
select into #tempTable

A note on some helper functions from Aaron Bertrand should be mentioned.


Monthly summary of active users based on a start/end date roster

p.s Hoping to get an answer in Postgres (I'm using Redshift)

Solution for PostgreSQL:
WITH cte AS ( SELECT '2021-01-01'::DATE AS month_start, '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-01', '2021-02-28' UNION ALL
              SELECT '2021-03-01', '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date <= month_end
  AND LEAST(end_date, closed_date) >= month_start
GROUP BY cte.month_end
ORDER BY cte.month_end;

or
WITH cte AS ( SELECT '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-28' UNION ALL
              SELECT '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date <= month_end
  AND LEAST(end_date, closed_date) >= DATE_TRUNC('month', month_end)
GROUP BY cte.month_end
ORDER BY cte.month_end;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=710260e9fa44a89cc9c2d536739f7c92
Replace cte code with the code which you need (generate first and last days of months for the months in interest in the first variant or only months last days in the second variant). For example, use dates generator, start from YYYY-MM-01 of the next month for a month in interest, step as a month, and substract - a month for first day of month and a day for the last day.


pgpool load balancing is sending all queries only to master

Initially, I have set sr_check_period in pgpool.conf to 0 (zero) to disable SR checking. I thought of avoiding this check and invariably balance the load. But this has caused the problem.

After I have given a non-zero value to this attribute, load balancing started working.


Very large subpartitions after table redefinition

Yeah, it is recurring problem.Starting with 11.2.0.2, the default segment size of partitions was increased from 64K to 8M. This means, even if your partition is empty, it still consumes 8 MB space right after its creation.

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

You should specify the parameters you mentinoned when you create the new table, for example: INITIAL EXTENT 65536 NEXT EXTENT 65536

Alternatively you could set a hidden parameter, so the database reverts to the old behaviour, session or database level:

alter session set "_partition_large_extents" = false;
alter system set "_partition_large_extents" = false;

But I would suggest specifying the extent sizes manually over modifying a hidden parameter.


Trouble setting up mirroring on SQL Server 2016

For information, I had the same issue.

This is a bug in SSMS 2016, the easiest solution if to connect using an older version of SSMS (Like 2014) to run the mirroring wizard and it works like a charm.

You can also set it up using command line but it's more complicated.


SQL Server 2014 Avaliability Group DbMgrPartnerCommitPolicy

For future readers :
DbMgrPartnerCommitPolicy::SetSyncState:00000003BFC0B0E0:1
The last integer describes the AG state
0 - Not Joined to AG
1 - Not Synchronized
2 - Suspended
4 - Synchronized
8 - Redo (redoing log)

Ref : DbMgrPartnerCommitPolicy::SetSyncState May Seem More Mysterious than It Actually Is


Postgres function index doesn't work correctly with regular expressions

This looks like a live bug in pg_trgm to me.  I can strip away much of the stuff and still reproduce it with this simple test case:

create table foobar (x text);
insert into foobar values ('eldrazi scion'),('eldrazi scio');
create extension pg_trgm ;
create index  on foobar using gin (x gin_trgm_ops);
select * from foobar where x  ~ 'eldrazi (?!scion)';  -- returns 1 row
set enable_seqscan TO off;
select * from foobar where x  ~ 'eldrazi (?!scion)'; -- returns 0 rows

Note that a similar bug was fixed recently (in yet-to-be-released code, commit 16500d2278ab3dd), but that fix does not fix this bug.

I've reported this bug myself, as bug #14623.

And it has now been fixed.  When 9.6.3 is released, it should contain the bug-fix.


Why Table Lock Escalation is happening in my scenario

Lock escalation is not exactly based on 5k rows, but rather a number of locks pr. level in the locking hierarchy. Once you hit the threshold, locks escalate to the next object level, until you eventually reach table-level which usually has a negative impact on the application. To work around this the easiest, consider how many rows you are affecting in  the DELETE TOP() and how many pages that correlates to. The amount of rows in a page is defined by with width of the row. If you have many columns (requiring lots of byte-storage), your row-width is bigger, then a table with fewer columns. A page can contain roughly 8k-bytes of data.

  "Books Online states that the threshold for a lock escalation is reached when the statement has acquired in excess of 5,000 row or page level locks on a single instance of an object. Also some blogs (which I came across in the Internet) just take 5000 value as a threshold point and not consider other parameters. When rows exceed 5000 an attempt is made to convert a higher-level lock on the object (either an IS or an IX lock) to an S lock or an X lock on the table. If this fails, perhaps because the table is being used by someone else, then SQL Server will back off and try again if it acquires another 1,250 locks on the same instance of the object. Remember that all locks attained must originate from the same object within the statement to qualify for exceeding the lock threshold, so 4,000 locks from tableA that is self-joined (i.e., another set of 4,000 locks) would not qualify." [taken from book "Professional SQL Server 2008 Internals and Troubleshooting" by Christian Bolton et al. published by Wrox ]

Good info in the page-object in SQL Server:
https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

There is an option to disable lock escalation as part of your table-definition. Buts it not advisable in most cases. I'd advise to not go this route until you've tried designing with lock escalation in mind. See more here: What is disadvantage of disabling lock escalation in SQL Server 2016


MySQL Exporting a query to text file via Cron Job

After talking to someone more knowledgeable then me I have came up with the following answers to my original questions.
TLDR; Cron should be fine for what your doing but don't write a bash script, write a PHP script instead, it's better.

Q1. Do I have to worry about putting the database username/password into this >cron script?

A1. As long as the script is in a safe spot on the server that is running the >>cron it should be fine. I can also create a new database user with very >>limited access specifically for this script.

Q2. Is there another more efficient way for me to get this information from >the database without logging into MySQL?

A2. There is a more efficient way to get the information, MySQLi or PDO via a >>PHP script, but there is no way to get around logging in.

Q3. Is Cron jobs appropriate for retrieving information from a database?

A3. Cron jobs should work fine for running the script on a regular basis.

Q4. Is there any MySQL specific cron/scripting issues I need to be aware of >while programing this.

A4. There isn't any cron specific issues but Bash or any other *nix based >>scripting language shouldn't be used because of limited access to MySQL. >>Instead a php script should be created utilizing either (PDO or MySQLi).

If anyone can answer the "Why" PHP is better then Bash Scripting in this scenario feel free to leave a post or comment.


SQLcl history (up-arrow) rewrites my queries

Yup, I see that, it's a bug. Will log it and we can hope for a fix in version 19.4.

Just a quick note, you're missing out on features available in versions 19.1, 19.2, and soon to be version 19.3.


Corrupted mdf file causing the backup job to fail

Given that you can connect to the database you may actually be in pretty good shape.  There are a number of methods you can use that might work but here is one.

Create a new database preferably on another disk because the possibility is that your disk is corrupt.
Use generate scripts to generate a script for your existing database.  Include all database objects and permissions.

Run the script on your new database to create all of the structures you need.
Either manually or through a tool (Import/Export Wizard for example) copy the data from each of the tables on your corrupt database into the new database.  Unfortunately this is the step most likely to have problems.  If you do have a problem it will hopefully be with only a few records and you can use the Import/Export Wizard to generate an SSIS package and change your error setting for that particular table to ignore failures.

Either way once you are done take a backup.  (Of course)


SQL Server 2008 T-SQL select hanging, but not dead locked

From the looks of your query I believe that the process is waiting on a spill to tempdb.  You will need to look at the output of

SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = <SPID for the SP>

to get a better idea of whats occuring in the background, Adam Machanic's sp_whoisactive is a great tool for simplifing the collection of details about a queries execution.

Also make sure that autogrowth is set correctly for your database and tempdb with sensible auto growth amounts for the data and log and that the number of VLFs in the log files is under a 100.  Look at his post for details 8 Steps to better Transaction Log throughput


I have a couple of questions:

1) Do you need to keep a detailed history of changes? Or only who/when was the record created and who/when last updated it?

2) Assuming no detailed history is needed, the main question I have is whether your alternate solution, of using a separate tracking table(s), will be any easier to maintain than adding the columns to every table. You will either need to: a) add code into every query/stored procedure that manipulates data to update the tracking table; b) add a trigger to every table that needs to be tracked.

3) How often do you expect to query this data? My experience with this type of auditing is you only need it when something bad has happened.

My impression of your options are:

1) I think using the Object_id for the tables as your FOREIGN TABLE ID is a good idea. Why would this change? Do you expect to drop and recreate tables during deployments? And if you do, you can easily map the old object_id with the newly created one and update the tracking table. You could add this logic to your deployments.

2) Wouldn't this negate your reason for not adding the tracking columns into the main tables?

3) See comment for 2), plus I don't think this would work as well as 2).

As far as performance, if you use a separate tracking table, I think it would be best to keep it as separate from the main tables as possible (option 1).

The biggest potential performance boost I can see by using a tracking table is for querying the data. You will be able to tune the indexes specifically for those queries. Also, all your data will be centralised. If the columns are on the base tables additional indexes will probably be an overall disadvantage. BUT!!! Back to my question 3): How often are you going to need to query this data?

As far as day-to-day performance your real impact will come down to how you implement updates of the tracking table. Changes to the stored procedures would be better than triggers in my opinion.

I'm sorry if I haven't really answered your question, but I hope my comments help.


The commenter was correct -- your kill query 77 was killing your own thread's query... as was indicated by the fact that the row from the processlist indicated that thread 77 (you) was the thread that was currently running the SHOW FULL PROCESSLIST command.
The way you fix your problem is by finding the step you took before running mysqldump, and undoing it.  This does not appear to be a MySQL issue.
From the Mediawiki Manual: Backing Up a Wiki...

Mysqldump from the command line
The most convenient way to create a dump file of the database you want to back up is to use the standard MySQL dump tool mysqldump from the command line. Be sure to get the parameters right or you may have difficulty restoring the database.
First insert the following line into LocalSettings.php
$wgReadOnly = 'Dumping Database, Access will be restored shortly';
this can be removed as soon as the dump is completed.

Your database isn't locked... your wiki is locked, because you locked it before you ran the backup.
If the $wgReadOnly variable in Mediawiki's LocalSettings.php configuration file is set, this locks the wiki against writes and offers the message you put in the string as an explanation to users of the wiki.
This suggests that you added this line while reading the Mediawiki docs but did not remove the line when you were done with the backup, unrelated to mysqldump or MySQL.  When mysqldump (or any other client) disconnects, it is impossible for locks to linger even accidentally inside MySQL because locks can only be held as long as the session/thread holding the locks is still connected.
If your wiki isn't massive, and having your wiki become briefly unresponsive while the dump is running isn't a problem, then you don't need to lock the wiki at all.  I have a scheduled job that runs against the MySQL server that runs my Mediawiki installation and I don't lock the wiki to take backups.  If your wiki is mission-critical and you want zero potential disruption, you could also set up a MySQL replication slave and make backups against the slave instead of the master.  If your server has the resources for it, you could run the slave on the same machine as the master, on a different port other than the default 3306.


Stop SQL job if it runs past a certain time

Add
IF convert(varchar(11),CURRENT_TIMESTAMP,108) >= '06:56:59' BREAK;

Before fetching the new row.
This will stop the cursor after 06:56:59
The current timestamp will be converted in a 24h string and will be compared wuth the choosen time every day.


John M's link in the comments is perfect, but if you want a quick summary:

Covering indices allow the engine to pull all of the relevant data directly from the values in the indices themselves; in this way, the data pages never have to be loaded and parsed to fulfil the query and build the result set.

Example with index on (A,B):

SELECT B FROM table1 WHERE A=9;

For MySQL (let's say InnoDB), for example, on the B-TREE index at values of 9 for A, we can easily gather all of the values of B for the result set right from these same index nodes we're already reading, use of the pointers to the table data pages are not necessary since we have everything we need to fulfil the query already.