Generating an arithmetic sequence I would like to generate a named table in a WITH clause which will contain this arithmetic progression:

╔══════════╗
║ mynumber ║
╠══════════╣
║        0 ║
║       30 ║
║       60 ║
║       90 ║
║      120 ║
║      150 ║
║      180 ║
║      210 ║
╚══════════╝

...based on variable p_maxnumber (here p_maxnumber = 211 for example).

Ideally it would be in a format like:

WITH sequenceTable AS
(SELECT mynumber
FROM ...),

Within the same WITH clause, I would like to use that sequence in downstream derived tables to get data and to be eventually used as a time axis for chart rendering.  I have a feeling there is a way to do it using create sequence?  Just trying to figure a way to create this still...

You can use a CONNECT BY query to generate an arbitrary sequence:

SQL> variable v_step NUMBER
SQL> variable v_maxnumber NUMBER
SQL> exec :v_step := 30; :v_maxnumber := 211;

PL/SQL procedure successfully completed

SQL> WITH sequenceTable AS (
  2     SELECT (LEVEL - 1) * :v_step myNumber
  3       FROM DUAL
  4     CONNECT BY (LEVEL - 1) * :v_step <= :v_maxnumber)
  5  SELECT * FROM sequenceTable;

  MYNUMBER
----------
         0
        30
        60
        90
       120
       150
       180
       210

8 rows selected

You can also use a pipelined function:

SQL> CREATE OR REPLACE TYPE tab_number IS TABLE OF NUMBER;
  2  /

Type created

SQL> CREATE OR REPLACE FUNCTION arithmetic_sequence (p_step NUMBER, p_maxvalue NUMBER)
  2     RETURN tab_number
  3     PIPELINED
  4  IS
  5  BEGIN
  6     FOR i IN 0..p_maxvalue/p_step LOOP
  7        PIPE ROW (i*p_step);
  8     END LOOP;
  9     RETURN;
 10  END;
 11  /

Function created

SQL> SELECT COLUMN_VALUE myNumber FROM TABLE(arithmetic_sequence(30, 211));

  MYNUMBER
----------
         0
        30
        60
        90
       120
       150
       180
       210

8 rows selected


lock-ddl-per-table option not working as expected in Xtrabackup I've recently been getting the following errors on some of my Percona XtraBackup backups:

InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.

However, it was my understanding that this should be avoided by including the --lock-ddl-per-table option.

The script I use is as follows:

xtrabackup ${MySQLConnectionString} --backup --parallel=${CPUCount} --compress --compress-threads=${CPUCount} --throttle=${throttle} --slave-info --extra-lsndir="${LogsDir}" --target-dir="${CurrentDir}" --lock-ddl-per-table

xtrabackup ${MySQLConnectionString} --backup --parallel=${CPUCount} --compress --compress-threads=${CPUCount} --throttle=${throttle} --slave-info --extra-lsndir="${LogsDir}" --target-dir="${NewDir}" --incremental-basedir="${BaseDir}" --lock-ddl-per-table

My Databases are all MySQL 5.7.28, and they are slave databases. They are all running on Debian 9, and using PXB version 2.4.16.

Have I misunderstood the usage of this option?

How about xtrabackup 2.4.21, which redesigns --lock-ddl-per-table?
See also https://www.percona.com/blog/2020/12/22/redesign-of-lock-ddl-per-table-in-percona-xtrabackup/


What's `sortlen` of mysql collation? Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| utf8_general_ci          | utf8     |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8     |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8     | 192 |         | Yes      |       8 |

What's sortlen of mysql collation?
What's the difference between sortlen of utf8_unicode_ci and utf8_general_ci?

The field Sortlen indicates the bytes needed when collating data:

So the collation is used to sort and compare the strings presented, but as the complexity of the rules for a language increases, you need space to sort the string or compare, because you have to store some information on the way.
Rick James is right you can see them,. because they are used on inside, when sorting or comparing and the number of bytes for sorting are set when the rules are made for a specific.


Is it possible to view the maintenance plans created in SQL Server 2000 in SSMS 2005? Is it possible to view the maintenance plans created in SQL Server 2000 (also running on the same SQL server 2000) in SQL 2005 Server Manangement Studio? I have the server added in my SSMS, but when I expand the Management folder, I don't see the Maintenance Plans folder like what I'd expect from a 2005 server.

For example, we have an optimization plan scheduled to run every week in SQL Server Agent and the command used to run this is: xp_sqlmaint N'-PlanID F7491D04-CDBA-496F-AE57-C2C5A3E3C8F8 (plus a bunch of options). I have no idea how to view this!

Figured it out. It's under Legacy->Database Maintenance Plans. Also all the plans, optimizations, integrity, trans log and complete backup are bunched up under one title.


Using RANK function, how can I return the rank of one record vs the entire table? I have the following info in my database:

+-----------------------+-------------+
| USERNAME        TRUST |             |
+-----------------------+-------------+
| DeaDTerra             |         390 |
| OgNasty               |         322 |
| Blazed                |         303 |
| monbux                |         260 |
| Tomatocage            |         230 |
| Stunna                |         220 |
| philipma1957          |         216 |
| John (John K.)        |         180 |
| CanaryInTheMine       |         173 |
| Mitchell              |         165 |
+-----------------------+-------------+

I want to run a query that will return the following:

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          3    |
+----------------------+

I have tried the following query:

select top 25 username, trust, RANK() over (order by trust desc) AS Rank
from btcProfile
where profile_name='Blazed' order by trust

All I get is this:

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          1    |
+----------------------+

How do I only pull up the one record but calculate the RANK() based on all rows in the table?

Put the window function in a derived table and move the filter outside of the derived table. That way the RANK() function will be calculated before the filter is applied instead of after. Here is one implementation:

CREATE TABLE #btcProfile (
profile_name VARCHAR(20) NOT NULL,
TRUST INT NOT NULL
);

INSERT INTO #btcProfile
VALUES
('DeaDTerra', 390),
('OgNasty', 322),
('Blazed', 303),
('monbux', 260),
('Tomatocage',230),
('Stunna', 220),
('philipma1957', 216),
('John (John K.)', 180),
('CanaryInTheMine', 173),
('Mitchell', 165);

SELECT profile_name, [Rank]
FROM
(
    select profile_name, trust, RANK() over (order by trust desc) AS [Rank]
    from #btcProfile
) t
where profile_name='Blazed';

Be sure that the RANK() function handles ties how you want it to. It wasn't clear how the TOP 25 related to your question. If you let me know I can integrate that into my answer.


mongodb running single instance with --auth flag enabled is it means mongob running autorization enabled? I am actually upgrading 2.2.0 single instance of MongoDB to 3.2 current version.

My MongoDB single instance running with --auth flag enabled so while upgrading from 2.4 to 2.6, do I need to consider authentication and authorization specific instructions given in upgrade notes or can I just simply follow Upgrade Standalone MongoDB Instance to MongoDB instructions just copying the 2.6 binaries?

You should definitely follow the authentication schema upgrades and other procedures as documented in the relevant version upgrade instructions. The documented procedures are the only supported (and tested) upgrade path.

Upgrade steps for authentication schema changes are particularly essential, but skipping any required steps or versions on the upgrade path is very likely to cause issues with your deployment.

You should also take a backup before each major version upgrade. If something goes awry in your upgrade process or some required steps have been skipped, you will then have the option of restoring a previous known-good deployment.


CREATE USER when user already exists I use this code to create a user:

CREATE USER "${domain}"@"localhost" IDENTIFIED BY "${dbuserp}";
CREATE DATABASE ${domain};
GRANT ALL PRIVILEGES ON ${domain}.* TO "${domain}"@"localhost";

But what if a user with that name already exists and I want to replace the existing one, with the new "blankslate" one? I thought of coping with that in the following way, but I'm not sure it's the right way:

DROP USER IF EXISTS "${domain}"@"localhost";
DROP database IF EXISTS ${domain};
CREATE USER "${domain}"@"localhost" IDENTIFIED BY "${dbuserp}";
CREATE DATABASE ${domain};
GRANT ALL PRIVILEGES ON ${domain}.* TO "${domain}"@"localhost";

Your way would seem fine. Older versions of MySQL do not have DROP USER IF EXISTS.

Here is another way that would get around that issue with older versions

DELETE FROM mysql.user WHERE user='${domain}' AND hostname='localhost';
DELETE FROM mysql.db   WHERE user='${domain}' AND hostname='localhost';
FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS '${domain}';
CREATE DATABASE '${domain}';
GRANT ALL PRIVILEGES ON ${domain}.* TO '${domain}'@'localhost';


Is it bad to assign specific processors to SQL Server 2008 R2 cluster instances? I have a SQL Server 2008 R2 failover cluster with a passive node and an active node.  The servers are physically identical.  I have two instances of SQL Server, one set to use all the CPUs in the first 3 NUMA nodes, the other set to use all the CPUs in the 4th NUMA node.

This seems to be working okay, even when failover occurs, but is this a bad idea?

What would happen in failover if the passive server didn't have as many processors?

I would not say that its a bad idea, but its always prefer not to touch the CPU affinity on sql server unless you know for sure that the problem is due to excessive context switching (and this too might be not the actual problem, but just a symptom).

  This seems to be working okay, even when failover occurs, but is this a bad idea?

Forcing affinity means that you take away sql server's ability to move processes between schedulers.

Consider below example : (using coreinfo from sysinternals) On a 2 socket, 12 CPU (24 logical cpu with hyper-threading enabled) with 2 NUMA Node machine, we get 12 CPU per NUMA node.

Logical to Physical Processor Map:
**----------------------  Physical Processor 0 (Hyperthreaded)
--**--------------------  Physical Processor 1 (Hyperthreaded)
----**------------------  Physical Processor 2 (Hyperthreaded)
------**----------------  Physical Processor 3 (Hyperthreaded)
--------**--------------  Physical Processor 4 (Hyperthreaded)
----------**------------  Physical Processor 5 (Hyperthreaded)
------------**----------  Physical Processor 6 (Hyperthreaded)
--------------**--------  Physical Processor 7 (Hyperthreaded)
----------------**------  Physical Processor 8 (Hyperthreaded)
------------------**----  Physical Processor 9 (Hyperthreaded)
--------------------**--  Physical Processor 10 (Hyperthreaded)
----------------------**  Physical Processor 11 (Hyperthreaded)

Logical Processor to Socket Map:
************------------  Socket 0
------------************  Socket 1

Logical Processor to NUMA Node Map:
************------------  NUMA Node 0
------------************  NUMA Node 1

Remember that the schedulers are not bound to cores and SQL Server does its own thread scheduling. For some reason, if a non SQL process maxes out CPU 1 which is running a thread on scheduler 1, then SQL server has the ability to put that scheduler onto any available CPU e.g. CPU 4. SQL Server has its own load balancer that will move a thread from one CPU to another.

If you set processor affinity, then you remove the ability from sql server to switch scheduler. So scheduler 1 is bound to CPU 1 and it has to run there only.

Some Excerpts from my book library ...

From SQL Server Professional Internals and Troubleshooting Book :

  One common use for soft NUMA is when a SQL Server is hosting an application that has several different groups of users with very different query requirements. After conﬁguring your theoretical 16-processor server for soft NUMA, assigning 2 × 4 CPU nodes and one 8-CPU node to a third NUMA node, you would next conﬁ gure connection afﬁnity for the three nodes to different ports, and then change the connection settings for each class of workload, so that workload A is “afﬁnitized” to port x, which connects to the ﬁrst NUMA node; workload B is afﬁ nitized to port y, which connects to the second NUMA node, and all other workloads are afﬁ nitized to port z, which is set to connect to the third NUMA node.

From Microsoft SQL Server 2008 Internals book :

  In some situations, you might want to limit the number of CPUs available but not bind a particular scheduler to a single CPU—for example, if you are using a multiple-CPU machine for server consolidation. Suppose that you have a 64-processor machine on which you are running eight SQL Server instances and you want each instance to use eight of the processors. Each instance has a different afﬁ nity mask that speciﬁes a different subset of the 64 processors, so you might have afﬁ nity mask values 255 (0xFF), 65280 (0xFF00), 16711680 (0xFF0000), and 4278190080 (0xFF000000). Because the afﬁ nity mask is set, each instance has hard binding of scheduler to CPU. If you want to limit the number of CPUs but still not constrain a particular scheduler to running on a speciﬁc CPU, you can start SQL Server with trace ﬂag 8002. This lets you have CPUs mapped to an instance, but within the instance, schedulers are not bound to CPUs.

If you want to limit the CPU that a SQL instance uses on a server, use Windows System Resource Manager instead.

As always TEST, TEST and TEST your entire workload to avoid any serious performance issues if you decide to use "CPU Affinity".

Suggestion : Leave it to default - as if you get it wrong, things will be worse and troubleshooting would be more difficult !

  What would happen in failover if the passive server didn't have as many processors?

performance problems can occur for the instance, due to NUMA node scheduler imbalances

References :

affinity mask Server Configuration Option
“Don’t Touch That Button!” Four Dangerous Settings in SQL Server (video archived - so no longer available)
Changes in affinity settings of SQL Server 2008 R2
SQL Server and Soft NUMA
Setting a Processor Affinity in SQL Server – the (unwanted) Side-Effects


Alter all table's - discard tablespace After having an awful experience with a dedicated server and spending 3 whole days repairing corrupted databases (Yes I know.... backup!).... I have narrowed the issues down to a few websites. The trouble is, these websites are Magento websites.

I am having to import the .ibd files into the /var/lib/mysql folder and DISCARD/IMPORT the tablespace.

Is there a way to alter ALL tables in the database at once, i.e ALTER TABLE * DISCARD TABLESPACE and then move the files across... Then ALTER TABLE * IMPORT TABLESPACE ?

Not as part of MySQL, but tools like Percona Xtrabackup makes the process a bit faster for exporting/backing up the tables, allowing things like using regular expressions or lists for filtering:

Partial backups
Restoring individual tables

For importing a list of tables, you can use some automation oneliners like this one found on Bill Karwin tools:

mysqldump --no-data $schema > schema-ddl.sql
mysql -N -B <<'EOF' > discard-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` DISCARD TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF
mysql -N -B <<'EOF' > import-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` IMPORT TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF

Basically, you can use the information_schema.tables table for listing the tables you want using "dynamic sql". For example, change $schema above for the desired database name.


MS-Access RANK() function I have a table in Access like this:

ID |  Op_ID
1  |  1
1  |  2
2  |  3
3  |  4
3  |  5
3  |  6

Is there a SELECT query that I can run in Access that would give me something like this?:

ID |  Count(ID)
1  |  1
1  |  2
2  |  1
3  |  1
3  |  2
3  |  3

So basically I want a Count of ID that loops over ID every time ID changes.

This query simulates a RANK() function.

SELECT T1.ID, (SELECT COUNT(*)
               FROM YOUR_TABLE T2
               WHERE T1.ID = T2.ID AND T1.OP_ID >= T2.OP_ID) AS Rank
FROM YOUR_TABLE T1
ORDER BY ID, OP_ID

It works only if there is an unique ID field that allow to count records where:

T1.OP_ID >= T2.OP_ID


Database returned to CIRCULAR mode but logs are still being archived to old location my DB2 database (v 9.7) is in Circular mode.

Current Logging Type:Circular

(USEREXIT) = OFF
(LOGRETAIN) = OFF
(LOGARCHMETH1) = OFF
(LOGARCHMETH2) = OFF
(LOGPRIMARY) = 10
(LOGSECOND) = 10
Path to log files: /home/ctginst1/ctginst1/NODE0000/SQL00001/SQLOGDIR/

I had old archive location /opt/IBM/archive
and now my hard disk space is full 100% because I have over 500 logs in that location which I cannot find that is defined somewhere.

What is going on?Why archive logs keep coming to old archive log location?

This is really big problem for us I will be thankful for any help

Run GET DB CFG with the SHOW DETAIL option, which will reveal any settings that are delayed (not yet active). Any difference between the "Current" and "Delayed" value indicates a DB CFG change that was made after the database was last activated.

If the old path isn't showing up in any of the settings in the "Current" column of the database config, then examine the logging settings of other local databases on the system (and in other local instances if any exist) to see if they're the culprit.

It also wouldn't hurt to take a look at the crontab schedules on the server to see if a cron job is responsible for copying the files to an inappropriate path.


Tuning postgresql.conf when benchmark Description:

We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here is our detailed information:

Operating System: Centos 6 x64
RAM: 8 GB
CPU: 2 CPU, Intel(R) Xeon (R) CPU @ 2.50 GHz
User quantity: 1000

Query to benchmark:

select count(distinct(a.id))
from table_a a
join table_b on a.id = b.fk_id  (*)

Running (*) before benchmark : 100 ms -> 200 ms
Running (*) during benchmark : 3000 ms -> 180000 ms (increase 30 -> 900 times)
CPU is used by benchmark: ~ 95 %
RAM is used by benchmark: ~ 3.5 GB

postgresql.conf file:

max_connections = 2000
shared_buffers = 5000MB
temp_buffers = 32MB
work_mem = 5MB
maintenance_work_mem = 1MB
effective_cache_size = 1000MB
checkpoint_segments = 128
edb_enable_icache = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
edb_dynatune = 90
edb_dynatune_profile = oltp

Questions:

If resources do not change, how we can improve our configuration? (can we edit parameters in postgresql.conf?)
Can we use pgbench to benchmark with query (*) above?

Yes, you can run those queries in pgbench. Look for the parts talking about custom scripts:

http://www.postgresql.org/docs/9.4/static/pgbench.html

The max_connections, and shared_buffers seem really high for the server you have. 25% of RAM for shared_buffers would be more reasonable, and getting a connection pooler like pgbouncer to handle lots of connections would be much more productive.

You might find this useful as well:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Hope that helps. =)


SQL Server - Error 3202 during Restore While attempting to restore a 4 TB MDF/data database with a 1.8 TB LDF/log file, I'm receiving the following error:

Write on '???' failed: 1117 (The request could not be performed because of an I/O device error.
Error: 3202, Severity: 16, State: 1

The backup appears to have created successfully on the primary machine, but will not restore to the backup machine. Both servers are running the SQL Server 2012 Enterprise Edition. The location of the .LDF transaction log file is being moved during the restore from the primary's I drive to the backups L drive, but this never posed an issue in the past.

The database is made up of 3 .MDF files: one approximately 2 TB, another at 1.4 TB, and another around 300 GB. The .LDF transaction log file is about 1.8TB. The restore locations have enough space, with the drive for the .mdf files having 6 TB total and the drive for .ldf having 2 TB total.

Any thoughts would be greatly appreciated.

FYI:  This article is also posted in StackOverflow at:
https://stackoverflow.com/questions/27116806/sql-server-restore-error-3202
I'm uncertain which location is best for this type of question.

According the error message posted in the comments this is a faulty disk that cannot serve write requests (at a certain location).


Is it safe to run a Windows Failover Cluster Public and Heartbeat on a Single NIC? I have a 2-Node Windows Failover Cluster (Windows Server 2016, SQL Server 2017) with a Quorum Disk Witness on fibre-connected shared storage.

How important is it that the heartbeat is on its own network with this Quorum setup and why?

Am I right in thinking that if the network goes down, the Quorum Disk Witness will stop the nodes trying to failover, as it will still vote that both nodes are online, even though the nodes themselves are saying the other is offline?

In this scenario I can't see what benefit there is to having the heartbeat on its own network. I completely get that teaming two NIC's gives resiliency, but separating the heartbeat (as far as I can see) doesn't offer much with this quorum setup.

How important is it that the heartbeat is on its own network with this Quorum setup and why?

Well what you have asked is really debatable and I should also add that quorum has little significance in cluster network configuration as such. Starting from Windows Server 2008 and onward Microsoft says that you can go on and configure WSFC without any heartbeat network connection. If you have not configured dedicated network for heartbeat the cluster validation wizard will only give you warning and that means your cluster is still supported. Now, but that does not means this is all good. Allow me quote a reason for dedicated NIC for heatbeat (Source)

  Heartbeat communication is used for the Health monitoring between the
  nodes to detect node failures. Heartbeat packets are Lightweight (134
  bytes) in nature and sensitive to latency. If the cluster heartbeats
  are delayed by a Saturated NIC, blocked due to firewalls, etc, it
  could cause the cluster node to be removed from Cluster membership.
  Intra-Cluster communication is executed to update the cluster database
  across all the nodes any cluster state changes. Clustering is a
  distributed synchronous system. Latency in this network could slow
  down cluster state changes.

So if you read above you can get some fair idea why a heartbeat communication might still be important.

Since you have windows server 2016 you can easily go with heartbeat communication without worrying about network binding order, an order which tell which network/route should be given priority. By default, Windows server 2016 uses the Interface Metric property of a network adapter to determine which route has the highest priority. The lower the Interface Metric property value, the higher the priority. More information in this support article

I also believe it is not too much of overhead to configure heartbeat network, if the heartbeat network goes down the WSFC will start using public network for cluster communication and cluster communication would still go on. I think it is more of segregating things and making cluster communication more secure with heartbeat network. BUT if your public network is teamed up well and has enough bandwidth to accommodate easily both cluster and client communication, by all means go ahead without heartbeat network. Please note if you just have public network all the client and internal cluster communications will go through this link so it has to be strong.

Here is what MVP,MCM Edwin Sarmiento has to say about Heartbeat network (Source)

  But here's my take on it: even if we have multiple NICs per node that
  are teamed up, how sure are we that the network switches are redundant
  and highly available? I've seen DR exercises where only the servers
  are tested but not the underlying network architecture. Only when the
  network switches themselves fail do they realize that they are not at
  all highly available. I still recommend having a dedicated network for
  the heartbeat communication and if the customer can guarantee that the
  network layer - switches, routers, etc. - is highly available, then
  I'll be happy with a NIC teaming implementation (thus, my reason for
  having a dedicated network for the heartbeat.)

I believe he is correct, the focus should be more on making "Complete Network" redundant not just part of it.

  Am I right in thinking that if the network goes down, the Quorum Disk Witness will stop the nodes trying to failover, as it will still vote that both nodes are online, even though the nodes themselves are saying the other is offline?

By network you mean the complete public network is down, in that case this  could be single point of failure bringing down the whole WSFC. And this is what precisely Edwin emphasized on above quote. Now if you are saying due to connecting network issue one of the nodes in cluster was removed from cluster membership forcing cluster to calculate quorum and do failover, in that case, since you have 2 votes of Node and quorum disk(> 50 %) the WSFC will remain online and do failover. This network issue would not affect disk/Storage as they are connected via SAN not via cluster public network.

Additional reading:

Windows Server 2008 networking 3 part series

Disclaimer: I must add I am not an network engineer and the detailed discussion of network configuration for WSFC is not within my knowledge scope and believe a network enginner can definitely add more to this answer, I tried to answer your question to best of my knowledge. Hope this helps.


How to model a Letter Transportation business context in a database diagram? I am trying to crate entity-relationship diagram for a transport company database. This database should store information about letters, deliveries, moving letters from one warehouse to another, and historical information. It should allow tracking letters, e.g., checking what letters were on certain date in a specific warehouse.
The process I want to model looks like this:

letter is collected from sender and it goes to warehouse,
then it could be transferred between warehouses,
at last, letter is delivered to recipient.

Business rules

Letters are sent and received by People, one letter can be sent by one particular Person, and also received by a specific Person.
There can be many People in database, if a Person is stored in the database it must be Sender or Receiver of some Letter.
Transports are made between different Warehouses and must consist of many Letters, Letters can be a part of multiple Transports (on different Dates)
Warehouses store multiple Letters
A Letter can be stored in few different Warehouses on different Dates (on ‘01.11’ it is in ‘Warehouse A’, and on ‘02.11’ in ‘Warehouse B’)
DateStorage - contains information about when a Letter has arrived to certain Warehouse
Transports are being done on certain Date, they depart from one Warehouse and go to another.
Letters can be delivered to recipients. If Delivery Attempt fails - there could be more attempts.

Current diagram
This is the database diagram I have created so far:

Relationship between Letters and Warehouses means that when letter is picked up by courier it is put to that warehouse.
Questions and current considerations
Is this diagram correct, what could I improve?
Is it OK that there are to relationship between transports and warehouses (from could be deduced from previous transports and initial warehouse of letter)?

Although you stated that the structure depicted in your diagram is part of a university project, the objective should be to make it as realistic as possible, so I consider that carrying out an interview (or a series of interviews) with the experts of a correspondence company might be very helpful (perhaps indispensable).

In this way, since creating a full and accurate entity-relationship diagram (or any of its derivatives) for this kind of business domain would require a much more in-depth examination (and, thus, a very long series of to-ings and fro-ings), the goal of this answer is to point you in the right direction with respect to the technical methods you might have to employ, presenting an expository analysis that includes my first draft of an IDEF1X model made up of elements that seem feasible, so that you can use it as a reference to capture the meaning of a real scenario.

Central aspects

The way I see it, the aspects of the business domain analysis that require special attention are the Events in which a Letter might be involved; therefore, I will detail bellow a hypothetical context that consists of some supossed Types of Event that appear of relevance (but, naturally, you should confirm, modify or discard all the discussed points on your own).

Considerations

I have listed the elements (brought up in the talk we had via chat) that I consider particularly helpful to work on forwarding the situation:

  [A]t any time, I would like to know where the letter is - no matter if it is in some warehouse or it is being transported.
  In my design sender is only the original sender of a letter (client of the company) and receiver (recipient) is a person to which letters is addressed - there is always one sender and one recipient for a letter.
  [F]or one transport there can only be one courier.
  One letter can be stored in up to about 10 warehouse. However number of all warehouses is much higher - few hundreds (or maybe even thousands).
  My idea was to store every client (sender / recipient) in database. So if some person have send or received at least one letter it […] is in database (but if person sends / receives more letters it is in database only once).

Business rules

Then, based on (a) such elements, (b) the content of your question, and (c) some estimations and assumptions that appear viable, I wrote the following formulations that describe the tentative interrelationships between the hypothetical entity types of significance (i.e., a substantial part of the business context rules, which serve the purpose of defining the corresponding conceptual model):

Person, Letter and Address

A Letter is sent by exactly one Person (who plays the role of Sender)
A Person sends zero-one-or-many Letters
A Letter addresses exactly one Person (who carries out the role of Addressee)
A Person (performing the role of Addressee) is addressed in zero-one-or-many Letters
A Person keeps zero-one-or-many Addresses
An Address is kept by zero-one-or-many Persons
An Address locates the Sender of zero-one-or-many Letters
An Address locates the Addressee of zero-one-or-many Letters

Letter and Event

A Letter is involved in one-to-many Events
An Event is classified by exactly one EventType
An Event is

either a Dispatch1
or an Exit2
or a Transport3
or a HalfwayStorage4
or a DeliveryAttempt5
or a Receiving6

1 Dispatch. The Event that occurs when a certain Person leaves a specific Letter in a precise Warehouse so that it can take part in a transportation process.

2 Exit. The Event that happens when a particular Letter departs from a given Warehouse to start the transportation.

3 Transport. The Event that occurs when a certain Letter is being moved between distinct Warehouses while the transportation is taking place.

4 Halfway Storage. The Event that happens when a concrete Letter is kept in an exact Warehouse in the middle of the transportation.

5 Delivery Attempt. The Event that occurs when a certain Person (playing the role of Courier) tries to deliver a specific Letter to the Person who was addressed in it.

6 Receiving. The Event that happens when a certain Letter is received in the Address that locates the Person who was addressed in such Letter.

Note: Some of these Types of Event (or Event Types) might come about several times with respect to a certain Letter (e.g., a Letter could be involved in multiple Transport occurrences during a whole transportation process); other Types of Event appear to be constrained to a unique occurrence regarding a concrete transportation process (e.g., Dispatch and Receiving). On the other hand, a real business domain may entail more Event Types, and the ones discussed in this answer might simply not apply or could have distinct characteristics, that is why performing an analysis yourself is paramount. Furthermore, the terms I use here are merely explanatory, as they possibly are not equal to those employed in a real organization.

Warehouse and the different subtypes of Event: Dispatch, Exit and HalfwayStorage

A Warehouse lodges zero-one-or-many Dispatches
A Warehouse is the origin of zero-one-or-many Exits
A Warehouse is the destination of zero-one-or-many Exits
A Warehouse provides zero-one-or-many HalfwayStorages

Vehicule and the distinct subtypes of Event: Exit, Transport and DeliveryAttempt

A Vehicule is utilized in zero-one-or-many Exits
A Vehicule is employed in zero-one-or-many Transports
A Vehicule is used in zero-one-or-many DeliveryAttempts

Person and the subtypes of Event: Transport, DeliveryAttempt and Receiving

A Person (who plays the role of Courier) carries zero-one-or-many Transports
A Person (who plays the role of Courier) conveys zero-one-or-many DeliveryAttempts
A Person (who plays the role of Courier) participates in zero-one-or-many Receivings

Illustrative IDEF1X model

Consequently, I built an IDEF1Xa model in terms of all the points explained above. It is shown in Figure 1 (make sure to click the link so that you can observe it at a higher resolution):

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

Event and its subtypes

As demonstrated in said model, there is a supertype-subtype relationship that comes about

between Event (the supertype), and
Dispatch, Exit, Transport, HalfwayStorage, DeliveryAttempt and Receiving (the subtypes).

The Event superentity type possesses the properties that are common to all its subtypes, i.e., LetterNumber and DateTime (exposed as a composite PRIMARY KEY [PK for brevity]), along with a discriminator, i.e., EventTypeCode, which indicates the exact kind of subtype instance that must be complementing each Event occurrence.

The PKs of Dispatch, Exit, Transport, HalfwayStorage, DeliveryAttempt and Receiving are, at the same time, FOREIGN KEYs (FKs) that point to the Event PK.

Every one of these subtypes displays the properties (or attributes) that apply exclusively to each of them, and some of those properties are FKs that illustrate the relationships that such subtypes have with some of the other entity types included in the model.

In this regard, you might find of help my answers to

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”;
“How to model an entity type that can have different sets of attributes?”;
“Modelling a database structure for multiple user types and their contact information”;
and the answers grouped by the subtypes tag.

Data samples

You did not clarify if the diagram is going to be used as a conceptual platform to construct a logical database structure (with proper tables, columns, column types and constraints) on a particular SQL database management system, but some data samples in tabular form will help to supply a more rounded answer that introduces further possibilities.

An EventType table

A table standing for the EventType entity type would fulfill a “look-up” role, and it may comprise the following rows:

 +-—————————————-+-————————————————-+-———————————————-+
 | EventTypeCode | Name             | Description     |
 +-—————————————-+-————————————————-+-———————————————-+
 | D             | Dispatch         | The event that… |
 +---------------+------------------+-----------------+
 | E             | Exit             | The event that… |
 +---------------+------------------+-----------------+
 | T             | Transport        | The event that… |
 +---------------+------------------+-----------------+
 | H             | Halfway Storage  | The event that… |
 +---------------+------------------+-----------------+
 | D             | Delivery Attempt | The event that… |
 +---------------+------------------+-----------------+
 | R             | Receiving        | The event that… |
 +---------------+------------------+-----------------+

Note that the EventTypeCode column, that has to be constrained as the PK, contains values that are meaningful (and, hence, are highly legible from the end user point of view), which enhances their usage when referenced from columns with FK constraints. At the same time, since the aforementioned values are physically light (as for their size in bytes), they behave quite fast regarding data retrieval processes and optimizes disk space consumption (mainly when pointed to from FKs).

An Event table

Then, let us suposse that a table dennoting the entity type named Event maintains the data points that follow for the Letter that is primarily identified by the Number 1750:

 +-————————————-+-———————————————————————-+-—————————————-+
 | LetterNumber | RegisteredDateTime      | EventTypeCode |
 +-————————————-+-———————————————————————-+-—————————————-+
 | 1750         | 2016-11-12 16:58:12.000 | D             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 09:12:05.000 | E             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 10:12:05.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 11:12:05.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 12:12:05.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 12:46:01.000 | H             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 06:24:07.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 07:24:07.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 08:24:07.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 09:10:13.000 | H             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-18 08:01:01.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-18 09:01:01.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-18 09:27:12.000 | H             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-19 11:16:08.000 | D             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-19 11:48:03.000 | R             |
 +--------------+-------------------------+---------------+

As shown, that table would be retaining a sequence of all the Events in which Letter Number 1750 was involved during a transportation. Of course, it would keep all the Events connected with all the pertinent Letters, but I only depicted a few hypothetical rows related to a supossed Letter Number 1750 in order to simplify the example. The sort of “chronological” data structure held in this table is commonly referred to as a time series.

The Event.EventTypeCode column, which should be constrained as a FK, holds values that indicate the kind of Event that was registered (in a user-friendly manner, in accordance with the points mentioned in the previous section).

Application programs sharing data from tables representing the subtypes

A table with partial Event information (like the one previously deliberated) can be displayed in, e.g., a page of a web application program that provides a link that redirects to subsequent pages based on (a) the Event Type, (b) the Letter Number and (c) the Date and Time of the Event of interest.

In turn, the subsequent pages will exhibit the full Event information. E.g., if

the EventTypeCode is D,
the LetterNumber is 1750, and
the DateTime is 2016-11-12 16:58:12.000,

then the redirection must take to a page that presents the complete Dispatch row, e.g.:

 +-————————————-+-———————————————————————-+-———————————————-+
 | LetterNumber | RegisteredDateTime      | WarehouseNumber |
 +-————————————-+-———————————————————————-+-———————————————-+
 | 1750         | 2016-11-12 16:58:12.000 |               x |
 +--------------+-------------------------+-----------------+

In case that

the EventTypeCode is T,
the LetterNumber is 1750, and
the DateTime is 2016-11-15 10:12:05.000,

the redirection ought to take to a page that sets out the pieces of information of the whole Transport row:

 +-————————————-+-———————————————————————-+-——————————————-+-—————————-+-————————-+-—————————-+
 | LetterNumber | RegisteredDateTime      | VehiculeNumber | CourierId | Latitude | Longitude |
 +-————————————-+-———————————————————————-+-——————————————-+-—————————-+-————————-+-—————————-+
 | 1750         | 2016-11-12 16:58:12.000 |              w |         x |        y |         z |
 +--------------+-------------------------+----------------+-----------+----------+-----------+

Integrity, consistency and derivability

Evidently, a database structure with these characteristics entails a considerable set of constraints that must be put up to protect the integrity of the data, guaranteeing its consistency with the rules determined at the conceptual level (e.g., the cardinalities of the relationships between entity types/entity type instances).

Some of these constraints can be configured declaratively by way of PK and FK definitions, but others require resorting to a procedural approach —due to limitations in the declarative facilities provided by the major SQL database management systems—.

Among the rules that have to be enforced procedurally is the feature that (1) each Event row must be suplemented at all times by (2) the respective counterpart in exactly one of the tables representing the subtypes, which (3) must “comply with” the value enclosed in the Event.EventTypeCode column —standing for the discriminator—, so the utilization of ACID TRANSACTIONS is convenient to ensure that these circumstances are always met. Other possibility would be employing TRIGGERS, but they tend to make things messy.

And, yes, it would be practical to express and fix as VIEWs several queries that “combine” data from some or all the related tables in order to, e.g., simplify the pertinent data derivation.


Database Design Confusion I am making a Phonegap based ERP solution for school in which every school can configure their own school individually and can manage the whole school from that single application, in which a main administrator configures the school,i.e., adds all the subjects, teachers, classes, timetables, result, etc.

The application also has roles for students, teachers, parents, and admins in which the admin can define the permissions that all the different roles have plus can add and delete the permissions as well.

The application manages the attendance, time table, results, profile of all the students and staff as well.

I can think of only two ways of doing the same,

To maintain a column of school_id in every table that i create
and in all the queries that i make i specify the school_id with it.
To create a different database for every school that registers in the application so that all the school data is not in the same table and the single table is not over populated.

I just cant make out with what design I shall go forward with.

Thanks in advance.

Creating independent databases will create the risk that as data structures change with the application, structural changes (and any associated data migrations) must be propagated to all of the databases. This can get tricky. A school_id in the tables that need it will simplify things in the sense that you only need to maintain one database.

Multiple databases would make more sense if you are going to deploy the application to physically separate clients (such as if the application runs independently on a server in the school), but will make maintenance more difficult.

Another place where it might make sense to have a separate database for each client would be if you know there will be slightly different versions of the data structures for each client. Of course, changes to common structures would still have to be propagated to all variant databases, and then you have to ensure that common patches/upgrades to the application work properly with the variants.

In general, I'd try to keep it all in one multi-tenant database, unless there is a very good reason to split it up.


Why do psql queries take longer when inserting many rows. Why is it non linear So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this:

INSERT INTO contacts (numbers)
SELECT  distinct array[
        (random() * 99999999)::integer,
        (random() * 99999999)::integer
    ]
  FROM generate_series(1,4000000) AS x(id);

And I vary the number of rows inserted, the time is nonlinear. Here is the data:

-1 record - 18 ms
-20k records - 36 seconds
-50k records - 151 seconds
-100k records - 750 seconds

Why is this getting exponentially bigger? I need 10 Million records in my database for load testing and it seems to be faster to insert 50k rows and then reinsert the 50k again since 151 + 151 < 750

Any insight on this topic would be appreciated. I assume it is because postgres saves data to rollback in case the query critically fails or is cancelled by the user and postgres does not want to "half insert" the total request.

Leaving aside the fact that the DISTINCT is causing some weird behavior, there are two main reasons why insert times get longer as bulk loads get larger:

B-tree indexes get less efficient to update as they get larger and have more tree levels.  So indexes take longer to insert a the millionth value than they did the 10th.
At certain sizes, you exceed certain thresholds which cause extra IO on the system, resulting in lag while the IO takes place.  These thresholds, which interact in complex ways, include:

the size of the WAL, causing log rotation
the size of the RAID cache, dropping to disk speeds
the size of Postgres' dedicated cache, causing flushing to the FS
the size of the FS cache's dirty block flushing threshold
the size of the entire FS cache, causing emergency flushing


Restricted user on my local SQL Server database? I started working at a new job and when I opened up SSMS and tried connecting to my local DB (by connecting to ".") I got a cannot connect message.

I was finally able to connect to my local DB by connecting to ".\SQLEXPRESS"

The problem I am having now is that I am not able to create any databases, tables etc on my local DB. I get the CREATE DATABASE permission denied in database 'master'

I also tried running the Activity monitor as a test and received the error message "The user does not have permission to perform this action. (Microsoft SQL Server, Error: 297).

I am wondering if this has anything to do with the fact that I am not an "Administrator" on my computer or if it is just the way SQL Server was installed.

When I go into Security > Logins I see BUILTIN\Users and sa (which has a little red arrow pointing down next to it)

When I double click on the sa Login it shows that there is a pasdword for the SA account.

Does this mean that whoever installed SQL Server on my machine created an sa user during installation with a password which is why I have no permissions??

If the instance name is .\SQLEXPRESS you have an instance of SQL Server Express but not localdb.

The default install will give builtin\administrators permissions on the database so you will have to start the Managment Studio (SSMS) by right clicking the shortcut and select run as admin to have an administrative token and be able to create databases.

When running the SSMS as admin you can add your user account to the sysadmin role in the database server and after that run things using your user account without elevation.


MySQL 5.6 InnoDB doesn't work We have been having issues with one of our Windows environments. It uses MySQL and has been running on MyISAM only. We need to create a few InnoDB tables but the engine won't start. We tried changing the my.cnf file and removing the innodb-flush-method, which seems to change the error we get from an invalid flush method (we had 0-Direct) to this (after using the force recovery option):

2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro in the InnoDB data dictionary has tablespace id 6, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-07-19 18:11:00 3416 [Note] InnoDB: 128 rollback segment(s) are active.
2016-07-19 18:11:00 3416 [Note] InnoDB: 5.6.27 started; log sequence number 1656382
2016-07-19 18:11:00 3416 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!!
2016-07-19 18:11:00 3416 [Note] Server hostname (bind-address): '*'; port: 3306
2016-07-19 18:11:00 3416 [Note] IPv6 is available.
2016-07-19 18:11:00 3416 [Note]   - '::' resolves to '::';
2016-07-19 18:11:00 3416 [Note] Server socket created on IP: '::'.
2016-07-19 18:11:00 3416 [Note] Event Scheduler: Loaded 0 events
2016-07-19 18:11:00 3416 [Note] D:\mysql\bin\mysqld: ready for connections.
Version: '5.6.27'  socket: ''  port: 3306  MySQL Community Server (GPL)
2016-07-19 18:11:17 50c InnoDB: Error: page 3 log sequence number 4853983
InnoDB: is in the future! Current system log sequence number 1656392.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2016-07-19 18:11:17 3416 [ERROR] InnoDB:  InnoDB: Unable to allocate memory of size 4294967016.

This is the my.cnf:

[mysql]

# CLIENT #
port                           = 3306
#socket                         = /tmp/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = MyISAM
#socket                         = /tmp/mysql.sock

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# CACHES AND LIMITS #
default_tmp_storage_engine     = MyISAM
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 100

# INNODB #
innodb-flush-method            = async_unbuffered
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1G
innodb_lock_wait_timeout       = 3600

# LOGGING #
#log-error                      = mysql-error.log
#log-queries-not-using-indexes  = 1
#slow-query-log                 = 1
#slow-query-log-file            = mysql-slow.log

# REPLICATION #
#log-bin
#binlog_format                 = MIXED
#expire_logs_days              = 7
#server-id                     = 1

How do we start InnoDB? Note we cannot change the default storage engine. Thanks in advance!

Clearly the data you are trying to start does not belong to the configuration you have in the my.cnf. If you look at the beginning of the log, you will see that there is a mismatch between the files in the system and the InnoDB catalog, meaning that the engine was running before:

2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro
in the InnoDB data dictionary has tablespace id 6, but tablespace with
that id or name does not exist. Have you deleted or moved .ibd files?
This may also be a table created with CREATE TEMPORARY TABLE whose .ibd
and .frm files MySQL automatically removed, but the table still exists in
the InnoDB internal data dictionary.

The innodb-buffer-pool-size is set to 1G, which does not match with the amount of memory is trying to allocate:

2016-07-19 18:11:17 3416 [ERROR] InnoDB:  InnoDB: Unable to allocate
memory of size 4294967016.

Check which configuration is the MySQL loading, inspecting the init file that you are currently using.


Filter by value or show all if no value I have 2 tables: records and access_rights

Access table has list of permission for certain and should be used to filter records based on rules:

function field is mandatory
object field is optional. if it`s empty do not apply filter else filter by value
All rules should work in one query. Please see example of expected output and SQL Fiddle data.

| ID |   FUNCTION |   OBJECT | SUM |
|----|------------|----------|-----|
|  1 | Function 1 | Object 1 | 900 |
|  4 | Function 1 | Object 2 | 750 |
|  5 | Function 2 | Object 2 | 200 |

Thanks in advance!

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE records (
  [id] int NOT NULL PRIMARY KEY,
  [function] varchar(100),
  [object] varchar(100),
  [sum] money
  );

INSERT INTO records VALUES
(1, 'Function 1', 'Object 1', 900),
(2, 'Function 2', 'Object 1', 600),
(3, 'Function 3', 'Object 1', 800),
(4, 'Function 1', 'Object 2', 750),
(5, 'Function 2', 'Object 2', 200),
(6, 'Function 3', 'Object 2', 500);

CREATE TABLE access_rights (
  [id] int NOT NULL PRIMARY KEY,
  [user] varchar(50),
  [function] varchar(100),
  [object] varchar(100)
  );

INSERT INTO access_rights VALUES
(1, 'User', 'Function 1', ''),
(2, 'User', 'Function 2', 'Object 2');

Query 1:

SELECT * FROM records WHERE
[records].[function] in
(SELECT "function" FROM access_rights where "user"='User'  )

Results:

| ID |   FUNCTION |   OBJECT | SUM |
|----|------------|----------|-----|
|  1 | Function 1 | Object 1 | 900 |
|  2 | Function 2 | Object 1 | 600 |
|  4 | Function 1 | Object 2 | 750 |
|  5 | Function 2 | Object 2 | 200 |

I'd recommend reading Erland Sommarskog's "Dynamic Search Conditions" post here

In your case, here's a fiddle with the pattern:
SQL Fiddle

For posterity, here's the code:

SELECT *
FROM records r
WHERE
EXISTS (
  SELECT 1
  FROM access_rights a
  WHERE r.[function] = a.[function]
  and a.[user] = 'user'
  AND (A.[object] = r.[object] or a.[object] = ''))

Also, please stop using reserved words as column names.


Not able to create SSISDB catalog Getting the error below while trying to create a catalog in sql server 2014 integration services.
Any idea what I missed in installation or anywhere else?

  The catalog backup file 'C:\Program Files\Microsoft SQL
  Server\120\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure
  the database file exist, and the SQL Server Service account is able to
  access it(Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)

I had this problem and it wasn't related to user permissions.
I had done an earlier install of SQL server and chose to install many options except for 'Integration Services',
so I ran the SQL Server Installation Centre application again and this time chose to install the 'Integration Services' feature.
I think that the reason I could see the 'Integration Services Catalogs' node in SMSS despite not having installed 'Integration Services', was due to choosing to install 'Master Data Services' during my earlier SQL Server install.


UPDATE to linked server (Pervasive Database) is failing using OPENQUERY I have 2 database servers:

ServerA : Windows Server 2012 R2 Standard, SQL Server 2014 (SP3) (KB4022619) Standard Edition.

ServerB : Windows Server 2016 Datacenter, SQL Server 2016 (SP2-CU6) (KB4488536) Enterprise Edition

Both are x64 servers located at different DC, under different domain.

Purpose:

We are planning to migrate production databases from ServerA to ServerB.

Action Taken:
Prior to migrating production database, I wanted to validate it on development environment first.So,

All of the databases, linked server (with similar settings), logins etc. have been copied properly from ServerA to ServerB.

Installed ODBC driver of Pervasive database on ServerB (similar to ServerA).

Exported all the system DSNs from ServerA to ServerB.

Updated MSDTC settings on ServerB (similer to ServerA).

Note: linked server is referencing to pervasive database.

Problem Facing;

After all these users are able to login, databases are accessible, reports are running and the server (ServerB) is performing well, users are able to SELECT tables from Pervasive database using linked server.

But the problem is, "users are not able to execute update on pervasive database using the linked server on ServerB.

The update queries 1st & 2nd are failing with same error:

--1------------------------------------------------------------
UPDATE OPENQUERY (LinkedServerName, 'SELECT Column_1,Column_2,Column_3,Column_4 FROM TableName WHERE Column_3 = 5910 AND Column_4 = 2')
SET Column_1='2019-02-01',Column_2='2019-07-01';
--2------------------------------------------------------------
UPDATE OQ
SET Column_1='2019-02-01',Column_2='2019-07-01'
FROM OPENQUERY (LinkedServerName, 'SELECT * FROM TableName') OQ
WHERE Column_3 = 5910 AND Column_4 = 2;

Error1:

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 4
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" could not UPDATE table "[MSDASQL]". Unknown provider error.

Error for 3rd query is quit different.

UPDATE LinkedServerName.DBName..TableName
SET Column_1='2019-02-01',Column_2='2019-07-01'
WHERE Column_3 = 5910 AND Column_4 = 2;

Error 2:

Msg 7399, Level 16, State 1, Line 26
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 26
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" could not UPDATE table "[LinkedServerName].[Database]..[TableName]". Unknown provider error.

Same query is executing successfully on ServerA.

Solution will be appreciated.

Thanks!

This issue is caused by upgrading SQL Server from SQL2014 to SQL2016 or later.  The Microsoft OLE DB Provider for ODBC Drivers has changed.  It now has more ISO and other ODBC features support.  This is a breaking change for some ODBC connections.

This driver (MSDASQL) is used to wrap an ODBC connection on the server, in this case, to the pervasive database and allow it to act as a linked server.  The wrapper allows both OPEN Query functionality as well as standard linked query calls.

The issue with the change is that the new wrapper is sending extra data that is not being handled if you are using ODBC driver versions 11 or below for query activity.  It can not handle the additional information and will return an error on execution.  It will be able to connect, however.

Version 12 of the pervasive ODBC driver will work for query only, but Open Query will not work. You will continue to get "unexpected catastrophic failure" on open query updates.

I recommend you upgrade to version 13.3.  This release from Actian will support both selects and updates and will solve the Open Query error. I do not know if you will need  support from Actian for the licensing of the driver.


revoke and apply privileges on current active sessions I am revoking some privileges to a user like this:

REVOKE INSERT, UPDATE, CREATE, ALTER ON `name`.* FROM 'user'@'%'

I verify via:

SHOW GRANTS FOR 'user'@'%';

And see that privileges really have removed, but the problem I am now trying to solve is that even after flushing the privileges FLUSH PRIVILEGES;, if the user still logged in, the user can do inserts therefore wondering how could I force the new permissions to users with a current session open.

If the user reconnects the permissions take place, I can not kill the connection since I have only access remotely, any alternatives, suggestions?

Assume that you revoke a privilege when the user is executing some long-time query which needs in this privilege for successfull execution. This will cause unpredictable break and may result into data unconsistence. So privilege altering cannot be applied to existing session. You can reset the user's connection to force him to reconnect, new privileges will be applied in that case. Of course the best way is to do that only when user's connection is idle. – Akina

If you have suitable permissions, you can kill his connection. Local vs remote is not relevant. Do SHOW GRANTS; for yourself.  From the commandline mysql tool: SHOW PROCESSLIST; to see what the id is, then kill <<that id>>; – Rick James


SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State.  This process blocks our main data entry pipeline and brings the entire database to a screeching halt.

We put a queue table with service broker in front of this proc (as it was initially the data entry proc) and we were losing data as the systems ahead ran out of space in their queues.  This has prevented us from losing data, but hasn't fixed the issue, its simply a band aid.

Is there any way in a procedure to say this proc must be given a higher priority on the CPU, or must be low on the list to take CPU time away from it, it took 7 minutes for the processor to get back around to finishing the task today (has been 12 minutes before, seems to be the higher load we're under the longer it takes as there is more data coming in so there is more processes fighting for CPU time)

It's impossible to do this in the procedure.  This wait type is a function of the scheduler.  Each SPID gets a finite amount of time (4ms) on the processor.  Once that time, or quantum, elapses, the SPID gets put back into the wait list until resources are available again.  This wait type is indicating that the SPID was not able to complete its work on the CPU in the quantum and had to "yield" to another waiting process.

This cannot be changed with any SQL code.  You can specify resources be prioritized for your load with Resource Governor, as Paul White suggested.  Even so, unless, as Kin suggested, you are experiencing a lot of CPU pressure, Resource Governor won't help either.

Check for CPU intensive operations and minimize where you can.  One that bites a lot of people is the Implicit Conversion, that will quickly be discovered by scanning the execution plan.


What is "Init for Update" thread state in mariadb? Our production server gets stuck on Init for update state whenever we start a query like

update
    <some_big_table>
set
    <primary_key> = <some_sequence>.nextval
order by
    <some_indexed_field>

While this the query is stuck are this state, all other queries get stuck at commit or writing to binlog state.

I couldn't find any relevant documentation for the same either.

UPDATEing or DELETEing all of a big table takes a long time.  It is busy copying over every row in case of rollback.  Wait.

Is there some reason for using a sequence instead of an AUTO_INCREMENT?

Is there some reason for renumbering the table?

Neither of those is needed in normal MySQL operation.

To leave the existing column intact, but add a new id that is the PK:

ALTER TABLE t
    DROP PRIMARY KEY,
    ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


UPDATE-SELECT Consistency Issue In Read Committed In Read Committed isolation level, I would expect the following to deadlock since UPDATE statement will mark the row dirty and SELECT should not be able to read the row before transaction is committed or rolled back. But it seems to work without a deadlock. How does this happen?

BEGIN TRAN

UPDATE TableA
SET Col2 = Col2 + 3
WHERE ID = 50

SELECT Col2 from TableA WHERE ID = 50

COMMIT TRAN

Your SELECT is running in the same process (spid) as your UPDATE, so you can freely select the updated rows - as long as you're in the same process.

Here is a classic deadlock example that you can play with:

--Classic deadlock example:

Create table dbo.Test1 (col1 int);
Create table dbo.Test2 (col1 int);

Insert into dbo.test1 values(1)
Insert into dbo.test1 values(2)
Insert into dbo.test1 values(3)

Insert into dbo.test2 values(1)
Insert into dbo.test2 values(2)
Insert Into dbo.test2 values(3)

--Open a SSMS query session 1 and run these commands
begin tran
update  dbo.test1 set col1 = 2

--Open another SSMS query session 2 and run these commands
begin tran
update dbo.test2 set col1 = 1
update  dbo.test1 set col1 = 3

--Go to SSMS query session 1 and run these commands
update dbo.test2 set col1 = 4

rollback


Are sql tuning ways same for different DB engine？ I used Oracle for the half past year and learned some tricks of sql tuning,but now our DB is moving to greenplum and the project manager suggest us to change some of the codes that writted in Oracle sql for their efficiency.

I am curious that Are sql tuning ways same for different DB engine,like oracle,postgresql,mysql and so on？if yes or not,why?Any suggestion are welcomed!

The "tricks" are to convince the DBMS to behave the way you want it to. These are large, complex pieces of software. No two behave exactly the same. So tricks that affect one may or may not work with another.

Good practice, such as only retrieving columns that a actually needed, is applicable everywhere.


Oracle Enterprise Manage "CPU utilization" is confusing When I display the CPU utilization in the Enterprise Manager (in the Performance tab), there is a big discrepancy between the graph that shows the utilization and the table that shows the operating system processes and how much CPU they take.

The graph shows something around 15% utilization (which would be one core at 100% as this is an 8 core server), but the process list shows 100%. Which in turn would mean that the process uses all cores which is not the case. ProcessExplorer indeed shows only ~10% CPU usage for the process oracle.exe.

So the question is: what does the column "CPU Utilization (%)" in the processlist reflect?

That figure is the share of the overall CPU utilization for each process.

In your case, it means that that specific oracle process is responsible for 100% of your current CPU utilization - i.e., you have no other applications that are eating CPU. So 100% of your ~1-core load is generated by that specific Oracle process.

There is an example in the Monitoring Real-Time Database Performance, and they state it like this:

  In this example, the database is consuming 92 percent of CPU utilization.

So the figure in the top graph shows how busy your cores are, and the figure in the table tells you what part of that load is generated by what process.


Firebird2.5-superclassic on Ubuntu 16.04 LTS I installed firebird2.5-superclassic using sudo apt-get install firebird2.5-superclassic and then ran sudo dpkg-reconfigure firebird2.5-superclassic and set the password per https://firebirdsql.org/manual/ubusetup.html. Even after doing this, I cannot gsec SYSDBA. I try running gsec -user SYSDBA -password ********* and i receive  Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
unable to open database Is there a way to ensure this gets created/fix this?  I cannot seem to find a way.

Ok, so I found functions.sh in /usr/share/firebird2.5-common. Running this creates all of the maintenance scripts and logging files that need to be there. It also creates security2.fdb which stores the security info for users and sets up the password according to the SYSDBA.password file.  You will need to set up two environment variables before running this:

FB_VER

and

FB_FLAVOUR

These need to be set to the point release you are on, ex: 2.5 and the flavor you are running, ex: superclassic.

Note: Mark Rotteveel's solution seemed to work as well, but I think this would be deemed as the more acceptable method.


Nested aggregate functions over different dimensions I have a table where I periodically record the throughput of all network interfaces on a set of servers:

create table net (
    ts timestamptz not null,
    host text not null,
    interface text not null,
    recv_bytes bigint not null
);
create index on net (ts);
insert into net (ts, host, interface, recv_bytes) values
    ('2017-01-01 00:00:00+00', 'a', 'eth0',  500),
    ('2017-01-01 00:00:00+00', 'b', 'eth1', 2000),
    ('2017-01-01 00:00:01+00', 'b', 'eth0', 1000),  -- measurements arrive with some jitter, +1s here
    ('2017-01-01 00:00:02+00', 'c', 'eth0',  100),  -- only present in this interval
    ('2017-01-01 00:00:04+00', 'b', 'eth1', 1900),
    ('2017-01-01 00:00:05+00', 'a', 'eth0',  550),
    ('2017-01-01 00:00:05+00', 'b', 'eth0', 1200),

    ('2017-01-01 00:00:10+00', 'a', 'eth0',  600),
    ('2017-01-01 00:00:10+00', 'b', 'eth0', 1500),
    ('2017-01-01 00:00:11+00', 'b', 'eth1', 1900),
    ('2017-01-01 00:00:15+00', 'a', 'eth0',  600),
    ('2017-01-01 00:00:15+00', 'b', 'eth1', 1400),
    ('2017-01-01 00:00:16+00', 'b', 'eth0', 1400),
    ('2017-01-01 00:00:16+00', 'b', 'eth1', 1700);  -- (b,eth1) appears 3 times in this interval

I would like to find the total throughput across all interfaces for a given time range, averaged over 10 second intervals. If a (host, interface) pair is missing data for a given interval, it can simply be omitted. This is what I came up with:

select ts_interval, host, sum(recv_bytes) as recv_bytes
from (
    select
        to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
        host,
        interface,
        avg(recv_bytes) as recv_bytes
    from net
    group by ts_interval, host, interface
) avg_net
where ts_interval >= '2017-01-01 00:00:00+00' and ts_interval < '2017-01-01 00:00:20+00'
group by ts_interval, host
order by ts_interval, host;

Result:

      ts_interval       | host |      recv_bytes
------------------------+------+-----------------------
 2017-01-01 00:00:00+00 | a    |  525.0000000000000000
 2017-01-01 00:00:00+00 | b    | 3050.0000000000000000
 2017-01-01 00:00:00+00 | c    |  100.0000000000000000
 2017-01-01 00:00:10+00 | a    |  600.0000000000000000
 2017-01-01 00:00:10+00 | b    | 3116.6666666666666667

This query seems overly verbose. The essence of that I'm trying to do is to sum over one dimension and average over the other. In pseudo-SQL:

select
    to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
    host,
    sum(avg(recv_bytes OVER ts_interval) OVER host) as recv_bytes
from net
where ts >= '2017-01-01 00:00:00+00' and ts < '2017-01-01 00:00:20+00'
group by ts_interval, host
order by ts_interval, host;

Is there a more concise way to do this?

Query
You can use generate_series() to generate a time raster to join to:
SELECT g AS ts_interval, host, sum(recv_bytes) AS recv_bytes
FROM  (
   SELECT g, host, interface, avg(recv_bytes) AS recv_bytes
   FROM   generate_series(timestamptz '2017-01-01 00:00:00+00'
                        , timestamptz '2017-01-01 00:00:10+00'  -- 10 - only lower bound
                        , interval '10 sec') g
   JOIN   net n ON ts >= g
               AND ts <  g + interval '10 sec'
   GROUP  BY g, host, interface
   ) sub
GROUP  BY g, host
ORDER  BY g, host;

Same result. Not less verbose, but much faster if you have an index on (ts).
Then again, the crucial point is to have "sargable" predicates, which you also get without generate_series() this way:
SELECT ts_interval, host, sum(recv_bytes) as recv_bytes
FROM  (
   SELECT to_timestamp(trunc(extract(epoch from ts) / 10) * 10) AS ts_interval
        , host, interface, avg(recv_bytes) as recv_bytes
   FROM   net
   WHERE  ts >= '2017-01-01 00:00:00+00'
   AND    ts <  '2017-01-01 00:00:20+00' -- just make sure to match bounds
   GROUP  BY 1, 2, 3
   ) avg_net
GROUP  BY 1, 2
ORDER  BY 1, 2;

Minor point: With exclusively positive numbers, trunc() is equivalent to floor() and a bit faster.
I am using ordinal numbers, since you asked for "less verbose" code. But that's not the core of the question here ...
Core question
You can run a window function over an aggregate function in one query level (though it's not typically faster than using a subquery).
But not the other way round: you cannot run an aggregate function over a window function in one query level. Just not possible. Window functions happen after aggregate functions in SQL.
Related:

Best way to get result count before LIMIT was applied
How do I get the aggregate of a window function in Postgres?
Return total number of rows and selected (aggregated) data
How will the order of operations in this query affect my results?


How to compare and MERGE two SQL Server environments (not just databases) Many similar questions were asked before, and mostly about comparing database schemas and data. That's all fine, but what about all the other SQL Server objects in the system overall?

This is the closest answer I've found, but it doesn't answer my query on a tool to be able to also create an SQL script of the variances, that can be easily applied to another server - including new user logins, roles, schemas, linked-servers, maps, maintenance plans, etc.

The other tool I found that does this is TOAD for SQL Server, but it's not cheap. I was hoping for an SQL script solution? Particularly with a mix of SQL Server environments from 2008 R2 to 2017?

Thanks

Thanks to @HandyD, dbaTools does this and more! Cheers


Query that marries data between two databases Database 1 is the master database where data on all domains in our client's industry are stored

Database 2 (or Database N) are the application specific databases of our client. (e.g. Application ABC would have Database ABC, Application DEF would have Database DEF etc.). Common data between applications are stored in Database 1, the master database.

The issue is, for example we have Application ABC. Lets say doing transactions via that application updates data records in tables of Database 1. Doing that also inserts records on Database 2. A record in Database 1 would now have counterpart records of it in Database 2. Now when we do a read of those records we would need a query in both Database 1 and Database 2 then marry both result set programmatically in our application's back end. As queries become more complex and records grow in size, performance issues start to happen when doing reads. Does anyone know the most efficient way to solve this kind of problem? or this this say that we need to re-architect our data?

Having a DB Link is not an option now this that could further degrade performance. Our database by the way is Oracle 12c

If you have doubts / questions about your database design, you can improve your question and continue.

Otherwise, let us assume that the database design is perfect - atleast as databases.

You shall start creating views - per requirement - comprising of both databases. And can make all your other select queries to look at the appropriate view.

They are much faster.

Arriving at a most wanted set of columns of rows matching the where condition through a view is easy.

You shall even think of arranging a view which has everything into it (all rows and all columns - just eliminating duplicates) and use that view in all your queries. This itself will never hinder the performance for sure.


Unable to BackUp Database on SQL2005 I have a database in MS SQL2005 that I can not backup.
Whenever I try to create a backup of the selected database through SQL Mgm studio I get this error:

  The Backup of full-text catalog in not permitted because is not online

This database was crated by detaching another database, copying the data and log files, and attaching the copies as a new database. How I can make my full-text catalog be online again.
I also tried to set USE FULL TEXT SEARCH to false but without success.

Here is my error

There is a MS KB article on it which say it happens because

  The full-text catalog folder is either deleted or corrupted.
  You did not enable the database for full-text indexing.
  The database is restored from a Microsoft SQL Server 2000 database backup. Therefore, the folder of the full-text catalog in the database does not exist on the server where you restore the database.
  The instance of SQL Server 2005 that you are running was upgraded from SQL Server 2000. However, the full-text search service cannot be accessed during the upgrade.
  The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

The article also has a "workaround" section


Simple Implementation of SQL Server 2008 Encryption I am looking at using some of SQL Server 2008's encryption.

I have read the following article on MSDN:

http://msdn.microsoft.com/en-us/library/ms179331.aspx

Q1a: Is the master key password created per DB instance?

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
GO

Q1b: If I create a master key password on Server1 and encrypt a column on a table. When I backup that DB (.bak) will I be able to restore the DB to another Server2 or will I have to create a master key on Server2 with the same password?

Q2: Do I need to backup the certificate, master key or symmetric key if I want to restore to another server?

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

Q3: When should the symmetric key be opened? Should it be done before each select and then closed? i.e. opened at the start of a stored procedure and closed afterwards.

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

Q4: Should I worry about who can open and close the symmetric key? I use SQL Server 2008 for ASP.NET web applications. The DB's are hosted on Windows Server 2008 VPS's. I generally create one DB user of type SysAdmin and supply the connection details from the web.config file. I would be the only person with access to the VPS so I do not need to factor in other developers accessing the DB. However, if the VPS/Windows security was breached and an attacker gained access to the VPS and/or DB. Could they compromise my DB and information?

Q1a: Is the master key password created per DB instance?

A1a: Assuming the question really means "Is the master key created per DB?"" then answer is Yes. Each DB has an different master key. there is also a thing called the service master key, which is per SQL Server instance.

  Q1b: When I backup that DB (.bak) will I be able to restore the DB to
  another Server2?

A1b: Yes. The master key in the restored database can be open using the original password. Then the Server2 service master key encryption can be added to the restored DB master key.

  Q2: Do I need to backup the certificate, master key or symmetric key
  if I want to restore to another server?

A2: No. All these objects are part of the database and they are restored along with the database. Specific needs to backup individual keys may arise from operational requirements (eg. key escrow).

  Q3: When should the symmetric key be opened?

A3: Keys that require opening have to be opened in the session. Once opened, they stay open until explicitly closed or until the session disconnects. An 'open' key is 'open' only in the session that opened it.

  Q4: Should I worry about who can open and close the symmetric key? ...

A4: Now this is the real question. You have two alternatives really, which correspond to two distinct scenarios:

Scenario A: when the service needs to access encrypted data without asking the user for passwords to the data. This is the vast majority of the cases. In this case the service needs to open the keys somehow. the solution is that the SQL Server uses the service master key to encrypt the database master key and the database master key is used to encrypt the certificate's private key and the private key is used to encrypt a symmetric key and the symmetric key encrypts the data. The SQL Server itself can reach any data following this chain, because it has access to the service master key. In this case the data is cryptographically protected only against accidental media loss: a lost laptop with the database on it, or an improperly disposed HDD with database of backup files on it etc. For all other threats, the data is not cryptographically protected, is only protected by the access control: since the SQL Server itself can decrypt the data w/o needing a password from the user, any user with sufficient privileges can access the data w/o knowing the password. In other words, a compromised ASP application may allow access to the encrypted data. As a note, scenarios in which the root of encryption is some secret stored on the ASP web application itself are just a (badly designed) variation on this and do not change anything.

Scenario B: when the service requires the user to provide a password. The password must come from the end user. In a web application, the user must type the password in a form in the browser, it gets sent over SSL channel to the ASP application, which passes it to the SQL Session (again on an SSL channel) and SQL can now decrypt the data using this password. This scenario is typical for multi-tenant applications in which tenants provide the data access password. In this scenarios the data is cryptographically secured against unauthorized access, because the SQL Server itself, nor the intermediate ASP web application, simply do not know the password. Even for a syadmin user with all privileges it would be impossible to read the data. Data can be moved at will and remains just as unscrutable, as it can be, again, only be read by the end-user that has knowledge of the password at the root of the encryption chain. Note that any 'shortcut' deviation in this scenario in which the password is 'saved' somewhere intermediately and not provided by the end-user this scenario degrades immediately to the first Scenario A.

A mandatory read for you: Encryption Hierarchy.


How to merge multiple query results into one 1.

SELECT COUNT( * )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

2.

SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

3.

SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

4.

SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

How To merge above 4 query into one?

I tried below query but it gives Error
"Commands out of sync; you can't run this command now"

SELECT prtner_name, (

SELECT COUNT( * )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS total, (

SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS inscan, (

SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS deliver, (

SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS rto

FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

Since the FROM, WHERE and GROUP BY clause are identical, combine them as follows:

SELECT
    prtner_name,
    COUNT(*)        shipment_count,
    SUM(inscan)     inscan_count,
    SUM(is_deliver) is_deliver_count,
    SUM(is_rto)     is_rto_count
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name;


Moving a Postgres database from standalone local DB to Amazon RDS I have a working Ruby on Rails 4 app running locally on my Mac. I'm running Postgres locally.

I want to move the database (and only the database, not the whole app) to Amazon RDS Postgres.

What is the easiest way to setup all of the tables on the new database and migrate the data over to it?

@joni correctly points out that pg_dump is what you want. However, I recommend not doing a simple SQL dump. Instead, use a PostgreSQL "custom" format dump that can be fed into pg_restore. This allows parallel dump/restore (faster) if you use directory format, but it's mostly useful because it offers control over the details of the restore without taking a new dump.

Custom-format dump/restore

For example, assuming you've already done a CREATE DATABASE on the destination so there's an empty DB named mydb there:

# dump the local db
pg_dump -Fc -o mydb.dump mydb

# restore to Amazon RDS
pg_restore -h my.amazon.host -U rds_superuser -d mydb --no-owner --no-privileges --no-tablespaces

Roles, tablespaces, foreign servers, GRANTs, etc

Note that roles (users and groups) are not included in the dump. Neither are foreign data wrapper servers or some other global objects. Thus, things that depend on them will fail to restore. In the above I omit restoration of the owner and of privileges for that reason - meaning you'll need to redo your GRANTs and REVOKEs if you have non-default privileges in your database.

Similarly, all objects are restored to the default tablespace, because any tablespaces you may have locally won't be on RDS.

No remote access to rds_superuser?

If you don't allow your rds_superuser to log in remotely, but have done a GRANT rds_superuser TO myusername for some other role, then instead of -U rds_superuser above you can use:

-U myusername --role=rds_superuser

to get pg_restore to authenticate as myusername then SET ROLE rds_superuser;.

Replication

Most production PostgreSQL database migrations are performed using replication and failover. This isn't currently possible with Amazon RDS. RDS does WAL archiving and streaming replication internally, but offers no way to get at the WAL archives or to create a replication entry in pg_hba.conf for replication for clients outside RDS.

For that reason, at time of writing you have to dump and reload to move data into or out of RDS. There's no low-downtime option.


Why is WITH NOLOCK running as Serializable I ran the following query:

SELECT session_id,CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
Where transaction_isolation_level = 4

and then:

DBCC INPUTBUFFER(157)

where 157 was one of the prior session_ids, to see the statement of one of the results from query no. 1.

It showed the following:

(@ID uniqueidentifier) SELECT * FROM PS WITH (NOLOCK) WHERE ID = @ID

How can the statement using WITH (NOLOCK) run with isolation level Serializable? Is there anything "overriding" the With (NOLOCK)?

Lock hints are orthogonal to isolation level. While they address similar concerns, adding a lock hint does not change the isolation level. Your transaction will still be a 'serializable' transaction. Of course, the lock hint make the query operation itself violate the transaction serializability, but you are looking at a property of the transaction.


Which is the most efficient way to run a particular select query? If I run the following code:

select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, Risk

I get the following results:

PolicyNumber    decpageid   Risk
AR-0000301132-04    41        1
AR-0000301132-04    41        2
AR-0000301132-04    37        3

All I really want to retrieve though is the policynumber and the maximum decpageid (which in this case would be 41) along with the Risk numbers (Which should be 1 and 2)

The query is also returning decpageid 37 even though it is not the maximum decpageid for the policynumber because it has a different risk.

The results I would like returned are:

PolicyNumber    DecpageID   Risk
AR-0000301132-04    41       1
AR-0000301132-04    41       2

I have figured out 2 different queries I can use to return my desired results but I don't think they are the most efficient. The queries I came up with are:

select PolicyNumber,  MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where
       PolicyNumber = 'AR-0000301132-04'
   and decpageid = (
      select MAX(decpageid)
      from StatRiskDecpages
      where PolicyNumber = 'AR-0000301132-04'
   )
;

This returns the desired results but I don't want to have to specify the policy number more then once in the query. Is there a way to call the policynumber into the sub query from the outer query?

The other query I came up with was:

select  t1.PolicyNumber,t2.DecpageID, t2.Risk
from (
   select PolicyNumber, MAX(decpageid) as decpageid
   from StatRiskDecpages
   where PolicyNumber = 'AR-0000301132-04'
   group by PolicyNumber
) as t1
   left join StatRiskDecpages as t2
      on  t1.PolicyNumber = t2.PolicyNumber
      and t1.decpageid = t2.DecpageID
;

I like this query because I only have to specify the policynumber 1 time and I can also expand the query so I can return the info for multiple policynumbers.

What I need to know is if this is the most efficient way of writing the query? It seems a little redundant. I might be wrong but I think there might be a better more efficient way of writing the query. Any suggestions?

I'd probably use

SELECT TOP (1) WITH TIES PolicyNumber,
                         decpageid,
                         Risk
FROM   StatRiskDecpages
WHERE  PolicyNumber = 'AR-0000301132-04'
ORDER  BY decpageid DESC

Assuming the covering index on (PolicyNumber, decpageid) INCLUDE(Risk) this will give you a plan like


Innodb switch innodb_file_per_table to OFF I want to switch off the inno variable: innodb_file_per_table
I found that this affects only new tables, how to apply this change also to old tables? I tried:

Alter table (ALTER TABLE xxx ENGINES=innodb), but it doesn't work

I may dump everything and restore the entire DB but if possibile I want to avoid this procedure.

What version are you using?  What makes you say it "doesn't work"?

Please note that ibdata1 will not shrink during the process.  Only a dump and restore can fix that -- with a deletion of the ibdata1 file in the middle.  But note...  Unless you tweak the dump, the load will be all-or-none (file_per_table or not).

I recommend changing only "large" files (over a few MB) to file_per_table.  Why?  With "tiny" tables, there is overhead of opening the .ibd files that, though small, might cancel out the benefits of file_per_table.

The 80-20 rule says that 20% of your tables take 80% of the disk space.  So another way to pick "large" is to pick the largest 20% of the tables.

In the future (version 8.0), I am looking toward tablespace-per-database as minor recommendation for tiny tables.


Mysql search varchar column strategy I can't find decent strategy for searching titles.

I have table for movies.
Each movie has 5-10 alternative names that are stored in movie_names (id, name, name_clean, movie_id) table.

To normalize all names I use script that converts all names in ascii, removes whitespace, punctuation, etc. and place it in name_clean.

And on search I use

SELECT DISTINCT(movie_id) FROM movie_names WHERE `name_clean` LIKE '%f%' ORDER BY views DESC

And then I use ids and select with "where in" movies table.

Although this strategy works, but because of my name cleaning the search is very wide. I want to improve it without name cleaning, but wonder if I can improve it better.

For example I was thinking about search LIKE 'f%', but then I will need to explode every movie name in words.
And if I have movie name "My very fat big movie name" I need to make this much of additional rows:

"My very fat big movie name"
"very fat big movie name"
"fat big movie name"
"big movie name"
"movie name"
"name"

Also on people table I use query

 SELECT * FROM people WHERE `name1` LIKE '%f%' OR name2 LIKE '%f%' ORDER BY views DESC

While searching movies the speed is okay, but it is very wide and selects too much results (search column is latin_swedish), with people table it's very slow (columns are utf8) and there are more rows.

How would you organize search in mysql.

thanks all for halp, especially sticky bit.

Here solution worked for me.

New table with id and name column with fulltext. I query with match against in boolean mode "query*". Works very well. No need for elastic.


SQL agent job step's connection managers Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?

Given that SSIS tasks are XML files, I would have thought that it would be possible to write a T-SQL XML query to find the connection objects.

You would need to search the XML for instances of

<DTS:ConnectionManager>

Here's a sample from a project I have:

<DTS:ConnectionManager>
  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
  <DTS:Property DTS:Name="ObjectName">SQLOverview</DTS:Property>
  <DTS:Property DTS:Name="DTSID">{1e0a262f-476a-49d8-873b-acf7e3d7d46e}</DTS:Property>
  <DTS:Property DTS:Name="Description">
</DTS:Property>
  <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property>
  <DTS:ObjectData>
    <DTS:ConnectionManager>
      <DTS:Property DTS:Name="Retain">0</DTS:Property>
      <DTS:Property DTS:Name="ConnectionString">Data Source=MyPC\SQL2008R2Dev;Initial Catalog=SQLOverview;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;</DTS:Property>
    </DTS:ConnectionManager>
  </DTS:ObjectData>
</DTS:ConnectionManager>

A couple of things to note:

The top node is <DTS:Executable>.  These <DTS:ConnectionManager> items are the next level down.
The above snip shows a static / fixed connection.  If you use an expression / dynamic connection, the XML code is a bit different:

  0
  MultiServer
  {02150dee-336e-4750-9159-3c4d3f0c62c3}

  OLEDB
  @[User::DynamicServerName]

      0
      Data Source=MyPC\SQL2008R2;Initial Catalog=master;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;

Please also note that all the code here has been formatted using an on-line XML formatter to aid readability.  In reality, each of these ConnectionManager XML snippets is on a single line of the .DTSX file.


Insert and Update Queries Log in MySQL I have sensitive information in multiple databases on the MySQL Server. I need to record which user have inserted or updated the record and incase of update we should be able to see the previous and the new values.

Is it possible in MySQL?

Usually for this purpose, I add a column in the table saying end user who updated it, and each client populates it when it it inserted/updated.

But if want to track each changes, usually I also add 2 datetime(6) columns in the table called StartDate and EndDate (use DateTime for mysql version before 5.6 not supporting microseconds)

EndDate is added to primary index (preferably at the end).

When row I insert a row into it, I put Sysdate(6) in StartDate and '9999-12-31' in EndDate (year 9999, yes it is hardcoded but if your application is still active at this time, it won't be your problem)

When I want to update an existing row, I close it first with a query like update MyTable set EndDate=sysdate(6), UpdatedBy='currentuser' where key=somekeyvalue and EndDate='9999-12-31' then I insert row with new values as explained earlier

When I look to current values, I always add " and EndDate='9999-12-31') in query

When I look to values applicable on a certain date, ie 2017-02-01
I perform query on a range of dates like this
select * from MyTable where key=somekeyvalue and StartDate<='2017-02-01' and EndDate>'2017-02-01'