better approach to design company hierarchies in table not sure if I'm in the correct forums, but what I wanted to ask is how would you implement a hierarchies in database terms?

for example, a person belongs to a service, and then to a team, and then to a section, and then to a channel, and then to a division.

so to summarize, a person will have these values associated to him/her:
service > team > group > domain > company.

I think the better approach is to have different tables for each category, so there will be 5 tables for the categories and 1 table for the person entity that will hold the relationship to the appropriate categories to a person. so the person table will have the columns serviceId, teamId, groupId, domainId, companyId which are all FK to the category tables.

or do you think this approach is better. have all the categories be placed in one table, and be differentiated by a TYPE field which will identify what type of category it is. also the hierarchy needs to be declared as well. so there would a level column that will identify which is the parent of which category.

which design do you think is better, in terms of optimization, operationability and complexity?

If your hierarchy is rigid (i.e. the levels are consistent and mandatory) then there is nothing wrong with representing each level as it's own table.  This is your first option, except that instead of having foreign keys on the person table to each of the organization levels, you should have a single foreign key on the person table to the leaf level of your organization, and then each level in turn refers to it's parent/container.  i.e.:

Person => Service => Team => Group => Domain => Company

With this modification, your tables will be in third normal form (3NF) which is always a good starting point from a design perspective.

If, on the other hand, your hierarchy might be at all fluid, then keeping a hierarchy in a single table with a self-referencing (involuted) foreign key is a better option from a code stability perspective.  This is your second option.

One issue with this second option is that relational databases are not brilliant at handling unleveled trees in this form.  There are, however, a couple of pretty useful techniques for managing this type of data in this format a little more easily.  You can Google around for adjacency lists and visitation numbers to learn more.


Getting SELECT to return a constant value even if zero rows match Consider this select statement:

SELECT *,
       1 AS query_id
FROM players
WHERE username='foobar';

It returns the column query_id with value 1 along with a player's other columns.

How would one make the above SQL return at least the query_id of 1 even if the select finds no rows that match?

BTW, it's PostgreSQL 8.4.

SELECT col1,
       col2,
       col3,
       1 AS query_id
FROM players
WHERE username='foobar'
union all
select null,
       null,
       null,
       1
where not exists (select 1 from players where username = 'foobar');

Or as an alternative (might be faster as no second subselect is required):

with qid (query_id) as (
   values (1)
)
select p.*,
       qid.query_id
from qid
  left join players as p on (p.useranme = 'foobar');

You can re-write the above to a more "compact" representation:

select p.*,
       qid.query_id
from (values (1)) as qid (query_id)
  left join players as p on (p.useranme = 'foobar');

But I think the explicit CTE (with...) is more readable (although that is always in the eyes of the beholder).


Entering multiple entries in SQL where the count increases by one I do apologize for the uninspired title, I'm not sure how best to put it.

Another person helped me massively in another question I posted regarding the structuring of data.

The solution has left me with another bit of a brain ache. I will paste some of that users response to me as it reflects what I currently have, I do apologize if that is poor etiquette.

Basically I'm allowing users to store a certain amount of text information each day, for example, 7 entries daily. Entries referenced as things.

The current structure works as follows (UID is Users ID + Date):

Things
PK          PK
UID         thing_id  thing
71420150404 0000001   First thing
71420150404 0000002   Second thing

The user that helped me before came up with the following way to enter data:

INSERT INTO test.Things(UID,thing_id,thing,thing_date)
SELECT
    :uid,
    MAX(IFNULL(thing_id,0))+1,
    :thing1,
    NOW()
FROM test.Things
WHERE UID=:uid
ON DUPLICATE KEY UPDATE thing=:thing1,thing_date=NOW();

This works perfectly, however the problem is only one entry can be added at a time. A workaround I've found is to take all the entries (things) as an array in my PHP and execute the SQL statement over and over for each entry. This seems highly inefficient.

Though I have been getting the basics of SQL through online courses I haven't been able to work out how to a better solution to this through SQL itself.

As I'd suggested on comments, this is a trigger in action BEFORE INSERT to autogenerate a thing_id with the UID given.

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.Things_BEFORE_INSERT$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`Things_BEFORE_INSERT` BEFORE INSERT ON `Things` FOR EACH ROW
BEGIN
SET NEW.thing_id=IFNULL((SELECT MAX(thing_id)+1 FROM test.Things WHERE UID=NEW.UID),1); -- If NULL set it to 1 as first thing_id for this UID
END
$$
DELIMITER ;

Note: Change the database and table to yours.

Test:

mysql> SELECT * FROM test.Things;
Empty set (0.00 sec)

mysql>
mysql> USE `test`;
Database changed
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP TRIGGER IF EXISTS test.Things_BEFORE_INSERT$$
Query OK, 0 rows affected (0.00 sec)

mysql> USE `test`$$
Database changed
mysql> CREATE DEFINER = CURRENT_USER TRIGGER `test`.`Things_BEFORE_INSERT` BEFORE INSERT ON `Things` FOR EACH ROW
    -> BEGIN
    -> SET NEW.thing_id=IFNULL((SELECT MAX(thing_id)+1 FROM test.Things WHERE UID=NEW.UID),1); -- If NULL set it to 1 as first thing_id for this UID
    -> END
    -> $$
Query OK, 0 rows affected (0.12 sec)

mysql> DELIMITER ;
mysql>
mysql> SET @UID='71420150404',@thing1='First Thing.',@thing2='Second Thing.',@thing3='Third Thing.';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO test.Things(UID,thing)
    -> VALUES (@UID,@thing1),(@UID,@thing2),(@UID,@thing3);
Query OK, 3 rows affected, 1 warning (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql>
mysql> SELECT * FROM test.Things;
+-------------+----------+---------------+------------+
| UID         | thing_id | thing         | thing_date |
+-------------+----------+---------------+------------+
| 71420150404 |  0000001 | First Thing.  | NULL       |
| 71420150404 |  0000002 | Second Thing. | NULL       |
| 71420150404 |  0000003 | Third Thing.  | NULL       |
+-------------+----------+---------------+------------+
3 rows in set (0.00 sec)

mysql>


Will MongoDB exceed the 64000 ulimit threshold? We are using MongoDB as a newbie and today we have exceeded the ulimit of the system causing chaos and panic all around.

However I have fixed it by increasing the hard and soft limits like below.

mongouser soft nofile 65535
mongouser hard nofile 65535

From what I understand, these are the limits for opened files.

My question is, will mongodb ever exceed this limit? If so how do I prevent it?
Does MongoDB have a mechanism to open/close files or will they always stay open and the journal files or db files always increase in size or do I have to implement some mechanism for it.

P.S. We are also using MMS to monitor the health of the DB.

Without knowing more about your database and its usage, I can't say for sure, but in general, the high number of open files found in most busy MongoDB instances is caused by connections (one open file per connection) rather than the data files themselves.

What does MMS have to say about your concurrent connection count?

You need to have huge number of databases/collections, and they need to be quite large before you start to hit this purely from data files themselves.

Hence, the usual method to limit how many open files MongoDB will create is to limit the inbound connections. The most common way to do that is to limit/control the number of client connections from the drivers (connection pool size) and in a sharded environment the number of mongos processes is also important. You can usually work out your theoretical maximum for connections to a MongoDB instance if you know the max number of clients, their max connection pool sizes, how many mongos processes you have (if sharded). There are a couple of other minor factors (replication, chunk migrations etc.) but that calculation will get you in the right ballpark.

If fixing the client side turns out to be an issue, then you can look at limiting incoming connections on the mongod too with the net.maxIncomingConnections option. However, that will just mean that rather than ulimit issues you will be rejecting client connections due to a configuration limit.


How can one make a partial index to optimize a query for a single value? Let's say I have table foos with column thing_id.
What's the smallest index I could make for these queries?
select count(*) from foos where thing_id is null;
select id from foos where thing_id is null;

Conceptually, we just need the index to keep track of a list of primary keys which match the criteria. Is it possible/useful to create a partial index on... no columns?
I tried this and postgres considered it a syntax error:
create index on foos where thing_id is null;

This did work
create index on foos (thing_id) where thing_id is null;

But will this result in unnecessarily writing the value of thing_id (always NULL) for each row?

You always need a column in an index.
You could try to index a constant that occupies only one byte:
SELECT typname FROM pg_type WHERE typlen = 1;

 typname
---------
 bool
 char
(2 rows)

If you choose boolean, that would be
CREATE INDEX ON foos ((TRUE)) WHERE thing_id IS NULL;

But my recommendation is to index id. Sure, that would make the index somewhat bigger, but your second query could use a much faster index-only scan.


Multiple aggregate operations via a pivot table My goal is to obtain for each user a total lifetime achievement points as well as points obtained in ranges, i.e. for the last three months.
I have this working but I'm curious if my solution will scale or if theirs a better way of thinking about the problem, in particular, I'm using Postgres so particular features in that DB might be a good solution for this.
Table Structure
Users:
------
id: int

AchievementsUser
----------------
id: int
user_id: int
achievement_id: int
inserted_at: DateTime

Achievement
----------------
id: int
value: int

Solution description

Left join on the pivot table to get the achievements per user. This is built as a CTE (base in the SQL)
Produce a further CTE for each date range I'm interested in. These provide the filters, and sum operations (i.e. later, I could do averages if I wanted)

SQL
WITH base AS (
  SELECT
    users.id AS user_id,
    achievements.value AS value,
    achievements_users.inserted_at AS inserted_at
  FROM
    users,
    achievements_users,
    achievements
  WHERE
    users.id = achievements_users.user_id
    AND achievements.id = achievements_users.achievement_id
),
all_time AS (
  SELECT
    base.user_id,
    SUM(base.value)
  FROM
    base
  GROUP BY
    base.user_id
),
last_three_months AS (
  SELECT
    base.user_id,
    SUM(base.value)
  FROM
    base
  WHERE
    base.inserted_at > CURRENT_DATE - INTERVAL '3 months'
  GROUP BY
    base.user_id
)
SELECT
  all_time.user_id,
  all_time.sum AS all_time,
  last_three_months.sum AS last_three_months
FROM
  all_time,
  last_three_months
WHERE
  all_time.user_id = last_three_months.user_id;

Use conditional aggregation:
SELECT u.id AS user_id,
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at >= current_date - interval '3 months') as last_three_months
FROM users u
  JOIN achievements_users au ON u.id = au.user_id
  JOIN achievements a ON a.id = au.achievement_id
GROUP BY u.id;

You don't really need the users table though:
SELECT au.user_id,
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at >= current_date - interval '3 months') as last_three_months
FROM achievements_users au
  JOIN achievements a ON a.id = au.achievement_id
GROUP BY u.id;


mongorestore failure: error writing file "/var/lib/mongodb" I want sync my server db with my local db;

dump db

  mongodump --db [db name]

copy dump folder into my local
restore dump on my local

  mongorestore --db [db name] [dump folder path]

I have 4 dbs I can do this successfully for 3 of them but I got

mongorestore error: failure writing file /var/lib/mongodb

when I restore 4th db

There is enough room on the file system. I restarted the server and repeated the process and I don't get this error anymore. However, sometimes I get:

failed: restore error: cohortV5.orders:
error restoring from dump/cohortV5/orders.bson: insertion error: EOF

and sometimes I get:

Failed: restore error: cohortV5.orders: error creating indexes for cohortV5.orders:
createIndex error: no reachable servers

Finally after two days, I found the answer, I get this error because of lack of memory. You need to add more memory if you work with big collection.


extproc env variables oracle 11g I have oracle 11g with extproc separately configured in listener.ora.

Users report some environmental variables that should be exported are not set.

From where does extproc gets it environment besides ENV in its definition in listener.ora? They come from shell that started listener? Why variables included in ENV do not appear?

How could I efficiently check what env variabls extproc has set?

11g
Oracle recommends that we configure the EXTPROC by altering the extproc.ora file rather than configure the listener for Oracle 11g.
 The extproc.ora file is located in the ORACLE_HOME\hs\admin directory.


SQL Server: Determine backup location from physical_device_name field I am working on a SQL Server database and ran the following command to figure out where the database is backing up to:
SELECT TOP 1 M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = 'MyDB'

The physical_device_name says: HTSQLServerMyDB
I don't see any maintenance plans or jobs that I can figure out that do this backup. I am trying to figure out where it is doing this backup. If I go into Server Objects then Backup Devices it is empty.
How can I determine where are my backups?
I am running SQL Server 2016 (13.0.4001.0).

Does this StackOverflow answer point you in the right direction? Specifically if you look at the device_type column from the backupmediafamily table, what do you see? You can find the list of types in the API doc:

2 = Disk
5 = Tape
7 = Virtual device
9 = Azure Storage
105 = A permanent backup device.

I'm almost wondering if that's a CNAME or some sort of name reference to the actual path, but it may be dependent on the above.
This is likely the relevant part from the above answer to what you're seeing:

You should notice that these backups have value 7 in the column "device_type" (7 means Virtual device). These rows are actually very useful, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance.

There's no trace of these backup files since they are above the scope of the virtual machine (above the level of your machine) - these are triggered by the host (Hyper-V or VMware for example).


Circular dependency in schema or boolean with CHECK? Let's say I have the following tables:

create table repositories (
    id serial primary key,
    name varchar(255) not null
);

create table remotes (
    id serial primary key,
    url varchar(255) unique not null,
    repository_id integer references repositories(id) not null
);

I want to have an "origin" remote for a repository.

I have 2 solutions:

Create a circular dependency, which means using ALTER TABLE in the schema:

alter table repositories add origin_remote_id integer references remotes(id);

Add a flag on remotes with a custom CHECK constraint:

create table remotes (
    id serial primary key,
    url varchar(255) unique not null,
    repository_id integer references repositories(id) not null,
    is_origin boolean not null constraint is_only_origin check (is_only_origin(repository_id))
);

The problem is that the 1st approach makes relationally more sense, but it also introduces a circular dependency.

Is it fine? I'm not sure what I should do.

Having the origin_remote column in the repositories table allows for an intrinsic consistency, even without check, and you'll keep this consistency when changing DBMS and, more importantly, in the application code.

This also allows for a single query when you want to change the origin of a repository, while having your is_origin boolean would imply two queries which would have to be wrapped in a transaction.

In this specific case I'm of the opinion you should really use the standard relational link from repositories to origin_remote_id. The need to use alter table is just as a minor inconvenience.


Login to SQL Server 2008 R2 remotely It seems this is a common problem over the internet so let me give you details on my environment and the steps I did:

Client - Windows 7 32-bit with SQL Management Studio
Server - Windows Server 2008 R2 Standard with SQL Server 2008 R2 [Default not named instance] and SQL Management Studio
For the server I added Application server role. I think I also added Web server role. The server and client are on the same LAN.

After installation and testing windows authentication to the SQL server, this is -in short words- what I did to enable SQL server remote login:

Enabled and started all SQL Server services.
Enabled TCP/IP for SQL Server. [I restarted the server to be sure after this step]
Enabled TCP port access on the firewall.
Allowed sqlservr.exe and sqlbrowser.exe on the firewall.
Created another user rather than sa and give it sysadmin role

On the client machine, when I open SQL management studio, I tried to login with server IP address, server name, server name or IP with port [192.168.1.25,1443]. Username and password are correct.

After the previous explanation, I can't access the server's SQL Server remotely, I get the famous error "A network-related or instance specific". Notice that the same error message appear, but with different error codes after performing some steps from the above. I can't provide you with the error codes since I'm not with the machine right now. Any help is really appreciated! Thanks!

SSMS uses port 1434 and the UDP protocol.  This has to be allowed on the firewall, too.

Quote:

  When SQL Server clients request SQL Server resources, the client
  network library sends a UDP message to the server using port 1434. SQL
  Server Browser responds with the TCP/IP port or named pipe of the
  requested instance. The network library on the client application then
  completes the connection by sending a request to the server using the
  port or named pipe of the desired instance.

From here.


Tracking Down Application Timeout Errors in SQL Server SQL Server 2008 SP3

How do I track down these timeout errors ?

The errors are displayed on an intranet dashboard used specifically for error reporting in IIS.  My suspicion is that there is a default timeout of 30 seconds in the web application and if a query takes more than thirty seconds, an exception is thrown. As there are many queries that take longer than 30 seconds on these SQL servers, I can't just filter in profiler based on duration.

Serving up the website being monitored by this dashboard are two IIS servers retrieving data from seven SQL Server instances.

Could I use the "User Error Message Event" and the "OLEDB Errors Event" to track these errors in SQL Server Profiler?

Aaron Bertrand put me on the right track with his comment

  And I believe you should be able to filter on duration and error <> 0.

Created a server side trace using the tsql_duration profile template

Added the User Error Message Event
Added the following filters

error <> 0

error <> 1

severity <> 10

This avoided capturing the USE DATABASE commands

The error message captured by profiler was 2 - Abort and the Event Class was 10 RPC:Completed.


Are "out of the row" fields read when a clustered index is used? I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored.

I have the following questions:

Is each field stored out of the row or only the max ones?
If you are using the clustered index of the table to read the whole record, are fields that are stored out of the row read, too?

  VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of  row'. It means that the ...

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row...

Actually, that depends on the setting of the large value types out of row option, which can be set using sp_tableoption. From the documentation:

The default is for MAX values to be stored in-row, up to 8000 bytes, if they fit. Unless you have used sp_tableoption to change the default, your MAX data will most likely be stored in-row.

That said, it is poor practice to use MAX data types for values that will never exceed 8000 bytes - use a non-MAX type instead. Aside from anything else, performance is often significantly poorer when dealing with MAX types, because SQL Server must be prepared to cope with data that might be up to 2GB in size.

  Is each field stored out of the row or only the max ones?

Only the MAX ones. In addition, if a previously in-row MAX column is moved off-row, only that column in that row is affected. It is replaced in-row by a pointer to the off-row LOB structure. There are also circumstances where non-MAX columns may be moved off-row.

  If you are using the clustered index of the table to read the whole record, are fields that are stored out of the row read, too?

Scanning the clustered index traverses only in-row data. If off-row data is needed for the query, it is looked up using the in-row pointer.


Innobackupex apply-log should be done before emergency restore or after each backup? Basically Percona Innobackupex consist with three steps:
1. Backup
2. Apply log
3. Copy back restore during emergency

My question:
When should I do apply log? Is it after each backup or before emergency restore?

If you apply the redo log right after a backup copy is taken you can greatly reduce time to restore.

Sometimes --apply-log step fails. Due to a bug or tablespace corruption. In either case you don't want to learn that in the emergency. That's one more argument to do the --apply-log after the backup.

People don't apply the redo log because it brings in more complexity to the backup process. For example, you cannot stream and apply the log.  If you take incremental backups the --apply-log makes it even more complex and you loose any intermediate recovery points (at best you'll have a state as of full backup time and the most current state. It may be OK though).


A low cardinality column index and SELECT Suppose that a long (1000000 entries for example) MySQL table t has a column c with less than 10 distinct TINYINT values.

Suppose also that most (say 99% or even 99.9%) of these values are equal to zero.

Will adding an index on this column speed up queries like the following?

SELECT * FROM t WHERE c > 1

The answer is "It Depends, you didn't provide enough information".

Put yourself in the shoes of the optimizer.
You see this query that does a SELECT *. C is most likely not the only column in the table, let's say you have both columns A and B as well.
That means that an index seek on C will require a lookup operation for each row to bring back A and B from the table.
Now it boils down to the cost - If you have 1M rows, and IF the optimizer can realize that your query will return 1% = 10,000 rows. That means the cost of using the index is performing the index seek, getting 10K rows, and then performing 10K lookups to bring back columns A and B.

Unfortunately, MySQL does not maintain histograms like some other engines, but only the density vector... So it may estimate that ~10% of the rows will be returned, and with that info the estimated cost will be much higher.

The alternative, is to scan 1M rows and filter 'on the fly' without using the index.
Which is cheaper? I don't know - that depends on the size of the table. If A and B are both BOOLEAN columns which take up very little space, it might think that it will be cheaper to scan the table. If A and B are huge BLOBS, it most likely will increase the estimated cost.

BTW.. if instead of using * (which I assume you only gave as an example), you would list only the minimal set of columns that you need, let's say A and C, then a composite index on (C,A) will always be the cheapest choice as you just saved yourself the lookups..

HTH


Comparing values between two tables How to compare values between two different tables?

I have two tables as shown below:

Table 'A'
sql_id       OPERATION       OPTIONS
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc TABLE ACCESS FULL

Table 'B'
sql_id       OPERATION     OPTIONS
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc INDEX UNIQUE SCAN IDX_THU

I need to compare the values in Table 'A' and Table 'B' for the given sql_id. My question is how to find the changed values.

In this case in Table 'A' the second line is TABLE ACCESS FULL. Table 'B' the the second line is INDEX UNIQUE SCAN IDX_THU. So here the value has changed. How can I find that using query? Something like when matched then "matched" when not changed "value changed".

I'm looking for output something like below:

 sql_id           comaparion outcome
dmqb1hzrf5uxh   No change in the plan
74anujtt8zw4h   plan changed

Can I do something like this?

select sql_id,case when ( select sql_id,OPERATION,options  from  a where sql_id='dmqb1hzrf5uxh'
minus
 select sql_id,OPERATION,options from  b where sql_id='dmqb1hzrf5uxh') is null then 'matched'  else 'changed' end as status;

I am comparing the explain plans before and after patch. The table 'A' is before patch result of the sqlid and table 'B' is after patch result. Now I want to compare the plans in both the tables. I want to compare the plan by using a query.

Assuming that sql_id has a unique or primary key constraint, you can do this with FULL JOIN (or LEFT JOIN if there are no new "inserts" in table B, or just JOIN if there are no inserts and no deletions):

SELECT
    COALESCE(a.sql_id,b.sql_id) AS sql_id,
    CASE WHEN a.operation = b.operation THEN 'matched'
         WHEN a.operation <> b.operation THEN 'value changed'
         WHEN b.operation IS NULL THEN 'a deleted'
         WHEN a.operation IS NULL THEN 'b inserted'
    END AS status,
    a.operation AS a_operation,
    b.operation AS b_operation
FROM
  a FULL JOIN b
      ON a.sql_id = b.sql_id ;


SQL SERVER Database IO performance impact of splitting database tables into FileGroups and multiple Files per filegroup SAN LUN ambiguity wrt performance

MSDN says to split read/write operations into a drive, sequential writes to another drive. (PRIMARY filegroup use typically data with read, update, write activity while LOG filegroup is sequential write mainly)
Documentation predates Storage Area Networks.

I get that grouping storage to read/write activity would optimise the heads on disk - minimise disk thrashing, sort of thing... but how is this dealt with when a "Disk" on the OS is an allocation to a storage pool derived from many disks?

The "bottleneck" of IO is then the IO controller on the SAN, and it controls the data flow from the data on striped groups of disks?
Meaning, LUN1 exposed to Server1 OS as 2 500GB drives, each split into 2 logical drives D:, E:; F:,G: of 250GB each: A database having a 750GB table created on a a filegroup of 4 files (D:\f1.mdf, E:\f2.ndf, F:\f3.ndf, G:\f4.ndf) will have parrallelism across all the logical drives (OS file driver optimised?) and SQL can use more file handler threads? but all the IO still comes through the LUN controller maxing at 1GB/sec.

Or is the DISK seperation then implied to be LUN level splits?
Meaning LUN1 250GB, LUN2 250GB, LUN3 250, LUN4 250... each exposed to the OS as a seperate physical drive, mapped to a logical drive each, and the SAME FILEGROUP split would ONLY then realise the benefit of IO optimised throughput?
I guess my question is then dependent on whether the SAN storage controller can sustain a 1GB/sec throughput on each LUN.

Or does it mean a storage controller for each LUN?

Can someone more experienced advise whether the gotcha of aligning the striping to the NTFS blocksize is participant in this discussion? OS NTFS allocation size blocks... oh! and the network packet size - is this also an influencing factor in terms of data throughput to the client? IPv6 vs IPv4 - is this a factor also?

Talk with your SAN administrator. That's the source that should be able to explain you with detailed information what kind of SAN you've got and what it is capable of. Have you got MPIO? What's the structure of your fabric?

Sometimes it makes sense to use multiple LUNs for different files. Behind the scenes, there might be different types of storage available.

A SAN with some SSD capacity might offer tiering. That is, the hottest data is elevated to SSD -backed part of the system whilst colder data is persisted on HDDs. Some models provide autotiering, in which the system decides what's hot and what's not. Some models require administrative action.

HP EVAs provide VRAID levels 0, 1 and 5. Using those will spread a LUN on all the disks in an array. This increases reliability, but the cost with VRAID 5 is increased controller CPU usage. The SAN admin needs to balance increased storage costs against increased CPU usage.

Aligning NTFS blocks with storage is dependent upon the SAN. If you are in addition using virtualization (Hyper-V, VMWare, whatever), that's additional complexity introducec. Your storage vendor is likely to offer white papers, best practices and consulting services about how to configure the storage for optimal performance.

Network packet size is relevant - if you use iSCSI. For FibreChannel, IP settings are not relevant.


How to partition an existing non-partitioned table I have an existing table with data:

dbo.Test (col1,col2,col3....) ON [PRIMARY]

I need to change this table to be partitioned like this:

dbo.Test(col1,col2,col3....) ON Ps_Date(Col2)

How I can I achieve this without dropping and recreating the table?

You do not specify if your table has a clustered index or not, so let's walk through all options.

I am going to use this example partition function, partition scheme and table:

CREATE PARTITION FUNCTION pf1(INT) AS RANGE LEFT FOR VALUES(10,20,30,40);
GO
CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([PRIMARY])
GO
CREATE TABLE dbo.pt(pc INT NOT NULL, id INT NOT NULL) ON [PRIMARY];
GO

1. Your table has a clustered index that was not created by a constraint.

This is the easiest case. You can just use the CREATE INDEX statement with the DROP_EXISTING clause to move the table to the partition scheme.

Assume for the example that this clustered index had been created:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(Id) ON [PRIMARY];

To partition this table, the clustered index has include the partition column (pt in our case) as part of the key. This statement changes the clustered index to include the partition column and partitions it at the same time:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(pc, Id) WITH(DROP_EXISTING = ON)ON ps1(pc) ;

The DROP_Existing clause automatically removes the existing index before creating the new one. This is preferred over a separate DROP INDEX as it causes the nonclustered indexes to be rebuild only once.

2. Your table has a clustered index that is part of a PRIMARY KEY or UNIQUE constraint and it contains the partition column as part of the key

This one is still easy and very similar to the previous one.

Assume this PRIMARY KEY constraint had been created on the table:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY CLUSTERED (pc, Id) ON [PRIMARY];

Now you can just run the same re-creation script that we used in 1:

CREATE UNIQUE CLUSTERED INDEX ptc ON dbo.pt(pc, Id) WITH(DROP_EXISTING = ON)ON ps1(pc) ;

3. The table has a clustered index that does not include the partition column but was created as part of a PRIMARY KEY or UNIQUE constraint

Tough luck. You cannot change the definition of a PRIMARY KEY or UNIQUE constraint after the fact. Your only option is to drop the constraint and then either recreate it including the partition column or create a clustered index independent of the constraint that includes the partition column. In the second case you can re-create the constraint NONCLUSTERED without including the partition column. Because now this constraint is not aligned (meaning its supporting index is not partitioned) you have to specify where to place it on disk.

Assume the table had a primary key like this:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY CLUSTERED (Id) ON [PRIMARY];

To partition this table you have to drop the constraint first:

ALTER TABLE dbo.pt DROP CONSTRAINT ptc;

Then you need to create the partitioned clustered index:

CREATE UNIQUE CLUSTERED INDEX ptci ON dbo.pt(pc, Id) ON ps1(pc) ;

If you choose to re-create the PRIMARY KEY constraint non-aligned you can do so like this:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

4. Your table does not have a clustered index

In this case it is recommended in most cases to just create a clustered index to establish the partitioning. You can use the previously seen create index statement for that:

CREATE UNIQUE CLUSTERED INDEX ptci ON dbo.pt(pc, Id) ON ps1(pc) ;

However, if you have a good reason to not create a clustered index you can get away with the following two-step approach. Sadly there is no direct way to make this change.

Assume your table does not have a clustered index. To partition the table you need to first create a CLUSTERED UNIQUE constraint. (You could also use a CLUSTERED PRIMARY KEY constraint). If you have a column combination that is unique that is a simple step:

ALTER TABLE dbo.pt ADD CONSTRAINT ptc UNIQUE CLUSTERED(pc,id);

After the constraint is created you can drop it again and "move" the table to the new partition scheme at the same time:

ALTER TABLE dbo.pt DROP CONSTRAINT ptc WITH(MOVE TO ps1(pc));

If you do not have a column combination that is unique, you are out of luck. In this case your only option is to add a new column and fill it with unique values. If the table is reasonably small you could do something like this:

ALTER TABLE dbo.pt ADD tmp_id INT IDENTITY(1,1);

However, that will take an exclusive table lock until all rows are valued. Depending on the table size this can be for quite a while. After that column is created follow the above two steps to first create the UNIQUE constraint and then drop it again right away. Afterwards you can also drop the column again. All these steps are fairly intrusive, so you are probably better of to just create a clustered index on the table. That does not even have to be unique.

If you have Enterprise Edition, you can use the WITH(ONLINE=ON) clause on most of the statements above. That will keep your table available to other connections. However, there will be a performance impact during that time.


how can i write a trigger that compare between two tables in phpmyadmin mysql I have two tables that has rows that have same values.

I want to write a trigger that will check for mypin in debtors tables and if mypin exists in debtors it fills the debtor row in the users table to have a value of 1 and if mypin does not exist in debtors table it fill the debtor row in users table to be 0.

debtors table
`debtor`, `myPIN`,

Users table
 `debtor`,`myPIN`

A db<>fiddle can be found here

Here's one example. You should make deptor and mypin
columns not null, or you will have to deal with possible nulls there:

delimiter //
create trigger before_insert_users
before insert on users
for each row
begin
    IF ( EXISTS (
            SELECT 1 FROM debtors
            WHERE debtors.myPIN = NEW.myPIN
         )
    )
    THEN
        SET NEW.debtor = 1;
    ELSE
        SET NEW.debtor = 0;
    END IF;
end //

Note that you will need a delete trigger on debtor as well (if myPIN can change you need an update trigger in each table as well.


sequence keys for static data In my data model I have some static data that is added to over time as the system changes.  For instance:

create table region (id SERIAL,crname TEXT);

customer has a link to the region, my system adds regions over time.  new regions are manually added by the dba executing an insert statement provided by the dev team for a release.  I'm looking for a Chris Date style perspective on how this should be modeled and implemented.  Should the key be managed by a database sequence where each database instance can have different keys for region "South West Denver" depending on when they are added, or is it acceptable to have key 32 for "South West Denver" in every instance of the region table for dev/test/qa/pre-prod etc? Effectively not using a sequence or database managed key.

If the databases are independent from each other, there is no problem if the internal, automatically generated primary keys are different in different databases.

If you want to exchange data, it could be an advantage to use the same keys. One way of doing this is to use the name itself as the primary key, but that means that these names cannot be changed.

Other than that, you'd need a central service that deals out id numbers for regions.


Changing IP address from existing Mariadb10.1 Galera Cluster I have a multimaster cluster with MariaDB Galera working properly, it is set to perform the replica through its WAN interface. This configuration was a result of a previous migration from another datacenter, but now I need to take advantage from 10Gbps internal network which gives me the new datacenter and for this I need to reconfigure the cluster IPs. That is changing the WAN IPs to LAN IPs.
There is a specific procedure for this? Or I should just shut down gracefully the nodes, change the IP and start again?

The nodes are not registered by their IP addresses, but by a UUID and server ID. Just ensure that wsrep_cluster_address reflects the nodes properly, and do a rolling restart.


How can I Export Warnings generated by HeidiSQL I am trying to do a bulk data upload into a MariaDB database.  The data file has over 3M records.  When the SQL runs the LOAD DATA INFILE statement, I get over 9600 warnings where there were problems when the data was inserted.  In order to chase down why there were problems, I need to get the row numbers of each record where an error was encountered so I can filter the source file and examine the problem columns.

I'm using HeidiSQL as my front-end, and there doesn't appear to be a way to export the list of warnings.  I tried to direct the output of the SHOW WARNINGS statement to an external file like you would a query, but that doesn't work (probably because the warnings are not persistent data like data in a table would be).

Does anyone know how I can save the list of Warnings to an external file?


best strategy to update from invalid date formats vs allow_invalid_dates Here is the create table syntax from a legacy table:

CREATE TABLE `bais_logs` (
`lg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
`lg_machines_id` int(11) unsigned NOT NULL COMMENT 'unique machine id from which they are logged in',
`lg_editdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'this will not be changed with a ping; only a logout or other edit',
`lg_acctname` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'admin1' COMMENT 'as of 2012-04-01 this has not been used',
`lg_stusername` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT 'the logged in user; note st_ prefix is inaccurate as it may be a contact or other object',
`lg_masterlogin` tinyint(1) unsigned NOT NULL DEFAULT '0',
`lg_stemail` char(85) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT 'email address at time of login',
`lg_action` mediumint(5) NOT NULL DEFAULT '0',
`lg_requesttype` mediumint(5) NOT NULL DEFAULT '0',
`lg_sessionkey` char(32) CHARACTER SET latin1 NOT NULL DEFAULT '',
`lg_ipaddress` char(16) CHARACTER SET latin1 NOT NULL DEFAULT '0.0.0.0',
`lg_referrer` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '',
`lg_feed` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
`lg_lastping` datetime NOT NULL COMMENT 'last ajax ping to determine still logged in',
`lg_entertime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lg_exittime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lg_logouttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`lg_id`),
KEY `lg_machines_id` (`lg_machines_id`),
KEY `lg_acctname` (`lg_acctname`),
KEY `lg_stusername` (`lg_stusername`),
KEY `lg_masterlogin` (`lg_masterlogin`),
KEY `lg_lastping` (`lg_lastping`)
) ENGINE=MyISAM AUTO_INCREMENT=3074 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

You will notice a whole lot of 0000-00-00 00:00:00 in there; that is no longer a valid format and entries into this table are failing.  Even trying to change the field definitions is problematic with lots of "0" dates in there.

I am trying to update the dates to 1000-01-01 00:00:00 which is supposed to be valid, but I am getting the message:

update bais_logs set
lg_editdate = IF(lg_editdate=0, '1000-01-01 00:00:00', lg_editdate),

lg_lastping = IF(lg_lastping=0, '1000-01-01 00:00:00', lg_lastping),

lg_entertime = IF(lg_entertime=0, '1000-01-01 00:00:00', lg_entertime),

lg_exittime = IF(lg_exittime=0, '1000-01-01 00:00:00', lg_exittime),

lg_logouttime = IF(lg_logouttime=0, '1000-01-01 00:00:00', lg_logouttime)
----------------
MySQL said:

#1292 - Incorrect datetime value: '1000-01-01 00:00:00' for column 'lg_editdate' at row 373

What am I doing wrong on this? Also, how would I set ALLOW_INVALID_DATES for now?  Is this something that is done in the mysql.ini file?

NOTE: from the command line I am logging into mysql as root and entering:

SET GLOBAL sql_mode = 'traditional,allow_invalid_dates';

and it shows as set, but it doesn't make any difference; I still get the behavior above.

Quick Solution as this is a very small Table(3078). I suggest to create a table with the correct default values and copy the records from the existing table and then rename the table. Hope this helps.


Sql Server Replication - Modify Statement Delivery Stored Procedures before the replication starts I want to create a subscriber (replica) where fields like Username, Email, and other personal customer info is masked (I'm creating a replica of production to help developers troubleshoot issues).

So I know that I should modify the sp_MSins_... and sp_MSupd_... procedures. But the problem is that these procedures exist in the subscriber (right?) so whenever I modify them and then reinitialize the replication to apply their logic to the existing data, the procedures get reverted to their initial state.

I couldn't find anything online about how to handle this.
What are my options?

From docs:

  Default and custom stored procedures

  The three procedures that
  replication creates by default for each table article are:

  sp_MSins_< tablename >, which handles inserts.

  sp_MSupd_< tablename >, which handles updates.

  sp_MSdel_< tablename >, which handles deletes.

  The  used in the procedure depends on how the article was
  added to the publication and whether the subscription database
  contains a table of the same name with a different owner.

  Any of these procedures can be replaced with a custom procedure that
  you specify when adding an article to a publication. Custom procedures
  are used if an application requires custom logic, such as inserting
  data into an audit table when a row is updated at a Subscriber. For
  more information about specifying custom stored procedures, see the
  how to topics listed above.

Default and custom stored procedures

So you create the custom stored procedures in the subscriber database and configure the article to use them in sp_addarticle.


How do MySQL 5.7 virtual columns differ from views? Starting reading point: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/

Running some tests with the most recent percona 5.7  they seem to work well. Explain plans and wall time look pretty good but I'm not seeing any difference in show table status index_length or data_length.

How do you find the true footprint or overhead for using these?

Unlike views they do seem to be faster than bookmarked selects.

Views and virtual columns are similar, in that neither are materialized.  i.e. neither store any data or indexes, hence why your index_length+data_length remain the same.

Where they differ is:

Virtual columns are created on base tables, and will co-exist with columns which will be material.  This can be useful as it has more of a mixing/matching behavior than views.
Virtual columns permit indexes (which are materialized).  Views do not.

As a side note, another commonly used feature with some overlap here is triggers.  It is often better to have a virtual column than a trigger updating legacy columns on insert/update.

In response to this specific question:

  How do you find the true footprint or overhead for using these?

There is no footprint, and you will likely not see overhead.  Sure, there are hypothetical cases: If you have many virtual columns and SELECT * from a client then more data will be sent across the network (since rows are sent in full; clients don't know they can reconstruct certain data from virtual column definitions).


Is there a common approach to encrypting data at the database level? Is there a common approach to encrypting data at the database level?  The application I am working on will be storing sensitive data that only users of the system themselves will be able to view.

Depending on your security restrictions you could:

use cell-level encryption (basically column-level encryption), which decrypts the data once it hits the buffer pool; it's available in all SQL Server versions, including Express
encrypt/decrypt data in your own application, so unencrypted data is never present in the database server's memory


Alter table statement waiting (for hours, on dev machine), but no locks shown I have been trying to issue a simple:

ALTER TABLE tablename ADD COLUMN id_col character varying (30)

type statement on a Postgres 9.1.13 build on Debian. The application is still in private beta, so the volume is low, and yet something is blocking this statement. Following this Postgres lock monitoring post, and running the query,

SELECT bl.pid        AS blocked_pid,
     a.usename       AS blocked_user,
     kl.pid          AS blocking_pid,
     ka.usename      AS blocking_user,
     a.current_query AS blocked_statement
 FROM  pg_catalog.pg_locks  bl
 JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
 JOIN pg_catalog.pg_locks    kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
 WHERE NOT bl.granted;

returns no results.

If I run,

SELECT * FROM pg_stat_activity

all I see is the ALTER TABLE statement with waiting = t, and a couple of other queries in an IDLE state.

I am not a DBA, more of a database developer, and so apologies if I am missing something really obvious, but I have never seen a situation like this on a low volume dev box, so am at a loss as to how to proceed.

Per the notes given on the wiki for that query, it only shows row level locks.

ALTER TABLE takes a table level ACCESS EXCLUSIVE lock. One or more transactions will be holding weaker locks on the table that prevent ALTER TABLE from acquiring its lock.

You can identify the lock being waited on with something like:

select *
from pg_stat_activity sa
inner join pg_locks lwait on (sa.pid = lwait.pid)
where sa.waiting = 't'
  and sa.query like '%ALTER%'
  and not lwait.granted;

(change "sa.pid" to "sa.procpid" on 9.1 and older).


Joining 3 tables using SQL Query? I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three tables.

This is my query

SELECT A.Name, B.Quantity, C.Quantity
FROM A
LEFT OUTER JOIN B
    ON A.ID = B.ID
LEFT OUTER JOIN C
    ON B.No = C.No;

MS-Access is rather picky at how it wants the joins to be formed. Add parentheses:

SELECT A.Name, B.Quantity, C.Quantity
FROM
  ( A LEFT OUTER JOIN B ON A.ID = B.ID )
  LEFT OUTER JOIN C ON B.No = C.No ;

Standard SQL syntax - and most other DBMS - do not need require parentheses in the FROM clause, although you are allowed to use them for clarity.


jdbc connection string ORACLE , integrated security we are trying to configure Spotfire to use JDBC connection to ORACLE. We would like it to use integrated security (I call it Active Directory, TIBCO seems to like integrated security).

Here is the current connection string which required username, pwd

jdbc:oracle:thin:@WARHAWK.conocophillips.net:1521:OWVCORP3

can anyone point me to, or tell me how to modify this to use integrated security.

When you use Kerberos authentication (for example Active Directory) with JDBC, you have to prepare your code for it, and not just simply change the connection string.

The Database JDBC Developer's Guide and Reference covers this topic:
http://docs.oracle.com/cd/E11882_01/java.112/e16548/clntsec.htm#JJDBC28344

Essential parts from the example of the above URL:

Example connection string and declarations:

  String url ="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)"+
    "(HOST=oracleserver.mydomain.com)(PORT=5561))(CONNECT_DATA=" +
    "(SERVICE_NAME=mydatabaseinstance)))";

   OracleDriver driver = new OracleDriver();
    Properties prop = new Properties();

Specifying the kerberos specific options:

prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_SERVICES, "("+AnoServices.AUTHENTICATION_KERBEROS5+")");
prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_MUTUAL, "true");

Specifying the credential cache location:

prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_CC_NAME, "/tmp/krb5cc_5088");

Finally connecting:

Connection conn  = driver.connect(url,prop);
String auth = ((OracleConnection)conn).getAuthenticationAdaptorName();
System.out.println("Authentication adaptor="+auth);
printUserName(conn);
conn.close();


Get the quantity of sold products on a specific day, keeping all products I'm building a database for daily sales report, for example:
2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
0 pears sold on 2021-02-12
Notice the "0 pears sold"

I have these tables:
Products

id (PK)
name
deleted_at

2
Apple
null

5
Banana
null

6
Pear
null

Orders

id (PK)
sale_date (DATE)

1
2021-02-12

2
2021-02-12

ProductOrders
|order_id (FK)|product_id (FK)|quantity|price|
|1 | 2 (it is "Apple") | 2 | 2.50 |
|1 | 5 (it is "Banana") | 3 | 3.50 |
|2 | 2 (it is "Apple") | 1 | 2.00 |
|2 | 5 (it is "Banana") | 3 | 3.50 |
I would like to get the products sold on 2021-02-12 grouped by price, keeping products that have not been sold so that I can write "0", as in the example.
I tried this query:
SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold,
    po.price AS price
FROM products p
    JOIN ProductOrders AS po ON p.id = po.product_id
    JOIN Orders AS o ON po.order_id  = o.id
WHERE o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price

With this query I get:
2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
Without the "0 pears sold", because the query discards the products that have not been ordered in "that date"
How can I to edit this query to get all products, also if they has not been sold in the date? Because I need to write 0 for the products has not been sold in "that date"
Thank you in advance and sorry for my bad english
EDIT
I forgot to say that I tried to use LEFT JOIN, but the problem is that in this way, it will keep all orders
SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold,
    po.price AS price
FROM products p
    LEFT JOIN ProductOrders AS po ON p.id = po.product_id
    LEFT JOIN Orders AS o ON po.order_id  = o.id AND o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price

This query will keep all "ProductOrders" so it will returns many many rows, so I can't use this solution
SOLUTION
Thanks to the user nbk for this solution (I just added the WHERE clause to discard deleted products that have not orders on "THE DATE"):
SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    p.deleted_at,
    sum(po.quantity) AS quantity_sold,
    po.price as price
FROM products p
    LEFT JOIN (ProductOrders AS po
        INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id = o.id
    ) ON p.id = po.product_id
WHERE (p.deleted_at IS NULL OR (p.deleted_at IS NOT NULL AND po.product_id IS NOT NULL))
GROUP BY p.id, p.name, p.deleted_at, po.price
ORDER BY p.name, po.price;

If you do a ddl you get quicker a good result.
you have to LEFT JOIN the tables to get the Rows with no partners.
Also add the where clause directly to the ON Clause or else you loose the gained rows.

CREATE TABLE products (
  `id` INTEGER Primary Key,
  `name` VARCHAR(6),
  `deleted_at` VARCHAR(4)
);

INSERT INTO products
  (`id`, `name`, `deleted_at`)
VALUES
  ('2', 'Apple', 'null'),
  ('5', 'Banana', 'null'),
  ('6', 'Pear', 'null');

CREATE TABLE Orders (
  `id` INTEGER PRIMARY KEY,
  `sale_date` DATE
);

INSERT INTO Orders
  (`id`, `sale_date`)
VALUES
  ('1', '2021-02-12'),
  ('2', '2021-02-12');

CREATE TABLE ProductOrders (
  `order_id` INTEGER,
  `product_id` VARCHAR(18),
  `quantity` INTEGER,
  `price` DECIMAL(10,2)
);

INSERT INTO ProductOrders
  (`order_id`, `product_id`, `quantity`, `price`)
VALUES
  ('1', '2', '2', '2.50'),
  ('1', '5', '3', '3.50'),
  ('2', '2', '1', '2.00'),
  ('2', '5', '3', '3.50');

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold
FROM products p
    LEFT JOIN (ProductOrders AS po
    INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id  = o.id)
    ON p.id = po.product_id
GROUP BY p.id, p.name,po.price
ORDER BY p.name;

prod_id | prod_name | quantity_sold
------: | :-------- | ------------:
      2 | Apple     |             1
      2 | Apple     |             2
      5 | Banana    |             6
      6 | Pear      |          null

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold
FROM products p
    LEFT JOIN (ProductOrders AS po
    INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-13') AS o ON po.order_id  = o.id)
    ON p.id = po.product_id
GROUP BY p.id, p.name,po.price
ORDER BY p.name;

prod_id | prod_name | quantity_sold
------: | :-------- | ------------:
      2 | Apple     |          null
      5 | Banana    |          null
      6 | Pear      |          null

db<>fiddle here


Mysql synchronization databases on one machine I have MySQL databases. Database A and Database B. Running on Mysql 5.5 and Ubuntu 12.04.

Into Database A i am doing INSERTs, UPDATEs every minute (cron) (The whole database has over 15 millions rows and growing).

I want to synchronize Database B with Database A use it for SELECT queries (for performance).

So I want to know: How can I synchronize new rows with Database B every 4 hours. I looked into MySQL replication. Is it good for this?

I am thinking think about SQLite3 DB.
All tables are InnoDB
I'm looking for a headless command, not a GUI tool.
Everything is on one machine.
I need a fast utility. I tried Mysqldump, but I am trying to sync, not get the entire database.

Why not Enable / configure mySQL Replication Service? I am not pro at it but I know you can achieve that by using mySQL Replication. See the link for details.
http://dev.mysql.com/doc/refman/5.0/en/replication.html

I am updating my answer here try to guide you to the proper sources or techniques. Try enabling clustering. and try to go through this link to load balance your mySql instances.
https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial

one machine running mySql instance and the same one doing the backup will not solve your issue but can be achieved through virtualization. Consider vmWare for example or try Virtualbox too.. but again if your server fails you will not going have nay means to take advantage of such design. However what will be good you go for another box and deploy the sql node there and then enable/ configure clustering


What is the best sql data type for x.x.x What is the best data type for field with format like this X.X.X.x

Here is the sample field data

15.8.0
15.8.1
15.8.2
15.8.3
15.8.4
1.8.0
2.8.2
9.8.2
9.8.3
9.8.4
10.8.0
13.15.0.1
13.15.1.1
13.15.2.1
13.15.3.1
13.15.4.1
13.0.0
26.0.0.1
26.0.0.2

This field use as primary field of table

Thanks

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c,
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15))
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ;

Tested at rextester.com.

Another option would be to use the HierarchyID type.


Accessing Oracle DB from SQL Server DB I am looking for the best and simplest way for accessing an Oracle db from an SQL Server db. Both are on separate physical servers. I am looking for a one-way access in read-only mode. SQL Server Database would be used by one system for now but would scale to be used by multiple applications in the future.

I currently have three options in mind:

Option 1: Directly accessing Materialised views

Every hour or so, data would be stored into Materialised views in Oracle in order to prevent impacting original DB performance.
SQL Server DB would access the views on-demand, using Linked servers

Option 2: Copy data to SQL Server DB

Every hour or so, replicated tables into SQL Server DB using CDC mechanisms.
(Not so sure how that'll work).

Option 3: Replicate Oracle tables using SQL Server Publishing

Setup the Oracle DB as the distributor.
Setup the SQL Server as the publisher (snapshot mode).

Thank you for your advices.

in order to prevent impacting original DB performance.

All three options you are considering will impact performance on the Oracle DB.

If you don't want to impact performance at all, apply archivelog files from Oracle to a standby DB and query that (you could try Linked Servers first to find out if it's fast enough for you).


Network design considerations for Oracle Database Appliance With the introduction of Oracle Engineered Systems the DBA is moved somewhat closer to infrastructure design decisions, and expected to at least have some opinions of the network design requirements for the database. At least that is the situation I find myself in :)

After deploying an ODA for testing, I find myself with the current setup:

System Controller 0 has the public bonded interface (bond0) connected to a typical edge switch, a Catalyst 2960 series. A management interface (bond1) is connected to a second edge switch of the same type.

System Controller 1 similarly has it's public interface connected to the second switch, while the management interface is connected to the first switch.

This way, if one of the switches goes down, an operator will be able to reach each system controller either via the public or the management interface to facilitate diagnostics.

On the Cisco end of things, EtherChannel groups are configured for the 4 bonded interfaces of the ODA. The two switches are individually wired to the rest of the network, with no direct links between the two.

At first glance this does look like a reasonable design, but the more I think about different fault scenarios the more questions I seem to come up with.

Taking into consideration that these edge-type switches are not in themselves redundant, it seems rather important that the cluster can deal with one switch becoming unavailable due to power supply failure, or one switch failing to forward packages.

The database clients (zend server application servers in this case)  are each similarly connected with a bonded interface to only to one of the two switches. This brings up some questions with regard to load balancing: The way I understand 11gR2 RAC, simply connecting to the SCAN address will quite possibly let the client go the long way to the main network and back through the other switch, which can hardly be considered to be very efficient.

What happens if a switch fails or stops forwarding packets? Will connections find the accessible VIP listener through SCAN? Will RAC somehow detect the network fault and move the SCAN and VIP to the System Controller with a working and accessible public interface? I honestly can't see how it would.

And while clients taking the long way through the core network and back is acceptable during a failover scenario, it sure would be nice to avoid it in normal production.

I'm sure Oracle has a very clear idea of how this should all work together, but I'm afraid I just don't see it all that clearly.

Is it possible to achieve full redundancy with edge-class/non-reduntant switches? Can we somehow add some control on where client connections are routed in production and failover situations? Perhaps there is a good way to interconnect the two switches to allow traffic directly between clients on one switch and database listener on the other?

At this point I'm looking for any best practices and fundamental network design considerations that should be applied to a typical high availability ODA implementation.

Hopefully this will then be of use to any DBA that is faced with making network design decisions for their ODA :)

Update:

The ODA is configured with bonds in active-backup configuration. I think this may allow for a setup where each interface on the bond is connected to a different switch, without any switch side configuration.

Anyone know if this is the case?

[root@oma1 ~]# cat /proc/net/bonding/bond0
Ethernet Channel Bonding Driver: v3.6.0 (September 26, 2009)

Bonding Mode: fault-tolerance (active-backup)
Primary Slave: None
Currently Active Slave: eth2

As it turns out, the ODA is factory configured with active-backup bonds. I've tested this to work well without any switch-side LACP/EtherChannel configuration, and each bonded connection may be split across two switches. In my tests, no simulated failure or network reconfiguration caused more than a a few hundred milliseconds worth of network outage.

This means that one can set up an isolated redundant front network for web applications using any layer two switches that are not inherently redundant.

To avoid client connections taking the long way into the company network and back through the other switch (and thus making production dependent on that equipment), one can have a private VLAN that only lives on the two edge switches and on an EtherChannel trunk between them.

As such, only the application servers and the database appliance will exist on that virtual network segment.

I don't see a way to control which path the connections from the application servers take to the database listeners, so the link between the two switches will have to be redundant, less this link becomes a single point of failure. This rules out using unmanaged switches without support for VLAN and either LACP or STP.

Using Cisco Catalyst 2960-series switches, I believe a combination of EtherChannel and Port Fast would be the better choice for a solid independent connection between the two. I would also use Port Fast on the ports for all the bonded connections to ODA and application servers.

Since the production network is isolated, one would need separate network connections for management, backup and connectivity to the rest of the company network.

Naturally, in order for this front production network to be fully self contained, any dependencies to external resources, such as DNS or authentication services, must also be resolved. Ideally production would be able to continue independently, without regard to any faults, ongoing maintenance or network outages anywhere else in the data center or company network.


Track entity updates (by who and when) I need some advice on how to store information about who created/updated an "entity". I keep going back and forth between Option1, 2 and 3 (see below):
Background

This is a legacy system and there are currently two types of users: e.g. employees and customers.
The users have different userId (saved in different tables...)
The employee key is a char of 50 characters and the customerId is a char of 30 characters.
Also, the employee is working at an office (might be several), so we want to track from which office the update/insert came from
The office key is a char of 50 characters.
The database is quite large: 200+ tables

Option 1
Store the "meta data" together with the entity.
Create table order(
    orderId int not null primary key auto_increment
    ...
    insertedByEmployee CHAR(50),
    insertedFromOffice CHAR(50),
    insertedByCustumer CHAR(30),
    insertedDate dateTime,
    updatedByEmployee CHAR(50),
    updatedFromOffice CHAR(50),
    updatedByCustomer CHAR(30),
    updatedDate timestamp
);

The primary advantage of this method is that it is straight forward fo find the updated field. The downside is that there are redundant columns.
Option 2
In this scenario I've moved the "meta data" to a separate table. Adds the possibility to have history. Don't know how relevant history is in this case though.
Create table order(
    orderId int not null primary key auto_increment
    ...
);

CREATE TABLE order_meta (
    order_metaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    orderId INT,
    customerId CHAR(30),
    employeeId CHAR(50),
    officeId CHAR(50),
    action ENUM('create','update'),
    dateTime` TIMESTAMP
);

What I like about this approach is that some redundancy is removed. Also, if we want to track "reads" we could simply updated the "action" column. This design makes it harder to find the last updated record though. Perhaps there should be updatedDate in the entity table. Also, I'm going to need one meta-table per entity, which i really don't like.
Option 3
Instead of using multiple columns for user id:s, create a "virtual user" and assign an id which will be used regardless of type.
A generic table (meta) is used to track changes to all entities. The downside is that this table might get used A LOT and makes querying for changes more cumbersome. I'm worried it might become a bottleneck.
This design doesn't require creating a separate table for each entity. Finding the actual user performing the update/insert is even more complicated than the other options. On the other hand I think this one might scale better.
Pseudo code (vuserId currently doesn't exist):
Create table order(
    orderId int not null primary key auto_increment,
    updated TIMESTAMP NOT NULL
    ...
);

CREATE TABLE virtual_user(
    vuserId INT NOT NULL PRIMARY KEY AUTO_INCREMENT),
    ...
);

CREATE TABLE customer(
    vuserId INT NOT NULL PRIMARY KEY,
    customerId CHAR(30),
    ...
);

CREATE table employee(
    vuserId INT NOT NULL PRIMARY KEY,
    employeeId char(50)
    ...
);

CREATE TABLE meta (
  metaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  entityName VARCHAR(30),
  entityId VARCHAR(50),
  vuserId INT,
  officeId CHAR(50),
  action ENUM('create','update'),
  dateTime TIMESTAMP NOT NULL
);

Option 1 is a no; this level of un-normalized data will mean many extra data pages occupying more space on disk, more data pages having to be read/written on operations, ...

Between Option 2 and Option 3 I'd go with something closer to Option 3 - In here you will save many bytes related to users (an Int goes for 4 bytes instead of the 30/50 of the Chars). This will save you again considerable disk space (considering users appear more than once) and get you smaller indexes which will mean better performance.

Now, on Option 3, I might be missing something for I don't understand what entityName and entityId stand for. If this is user information, it will better stay on the virtual_user table so you won't get this info repeated in every meta. Also, on the meta table I'd keep the orderId, it will allow you proper and fast joining of the meta and order tables, providing adequate indexing is present. I don't know the specifics of your requirements but consider if the updated column is necessary on the Order table or if for displaying the desired info on the application you'll be joining with the latest registry from the meta table all of the time.

If you want to keep track of order history, depending on requirements you might want to consider having a separate table (orderHistory or 'orderChanges'), or partitioning the order table. Now, especially for this last case, the order table could have a composite key composed by (orderId, metaId), making it Unique and a candidate index for joining with the meta table.

-- After the explaining comment:
The meta table will be the most heavily used one, so keep everything as short as possible on this one:

Clustered index on metaId
Add a table entityTypes which relates entityName CHAR to entityType int. This will also get you a nice list of entities for anyone new looking at the database to know which entities relate to meta
Try having entityId as int for all entities (If you can't change/add column to the existing tables, add an intermediary table) - This will reduce the field to 4bytes and allow one single data type for all entities.
Add an index to meta table on entityType (now int) and entityId (now int). Your rows on this heavily used table are now so short you should be doing everything lightning fast.


Difference between clustered index seek and non clustered index seek What is the difference between Clustered Index (CI) seek and Non Clustered Index (NCI) seek? Does one perform better than the other?

The reason I am asking this is because I have a table with 50 million rows and 150 columns. It has a column named ID defined as the clustered index. There is one more NCI on it with the same index key ID and seven include-d columns. It seems to me that the NC index is a duplicate here and can be safely dropped.

So I want some expert views/advice if it can be dropped safely or should keep it intact?

A nonclustered index that has the same key(s)* as the clustered index, may still be useful, because the nonclustered index will usually be smaller and denser. Remember, a clustered index includes all in-row data, so it is normally the widest (least dense) index possible.

* The same key columns, in the same sequence, sorted the same way (asc/desc).

For a singleton seek (a seek using an equality predicate into a unique index), the main difference is the presumed greater chance of the index page(s) needed to find that single record being already in memory. All things being equal, a nonclustered index with 100 pages has a better chance than a clustered index stored on 10,000 pages.

For any other kind of seek except a singleton, there will be a scanning component as well. The scanning portion will also benefit from the greater density of the nonclustered index (more rows per page). Even if the pages must come in from persistent storage, reading fewer pages is faster.

You should check the system documentation, ask the person that added the index, and/or look for code comments to understand why this index exists. You also need to check the complete index definitions carefully, including compression, and look at index maintenance scripts. There could be other special reasons for having the nonclustered index (e.g. ability to rebuild online).


Is ASYNC_NETWORK_IO wait type anything to worry about? In looking at the list of stored procedures that take long time to execute, one stands out as causing the most wait.  However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the stored procedure transfers roughly 400 MB of information.

In the documentation, it states that the cause of ASYNC_NETWORK_IO is that the client can't keep up with the flood of data and that is probably true.  I am not sure how to make the client keep up since all it does is call the stored procedure via an ADO.NET and then just processes the dataset.

So given this information, should I worry about the ASYNC_NETWORK_IO wait type for this procedure? Does it actually have an effect on the server performance?

Additional pieces of information:

I am on service pack 2 of SQL Server 2005.
The client app is on the same box as SQL Server (I know, i know...but I can't do anything about it).

As you said, this wait type indicates that the application is not keeping up with SQL Server. Now what that really means is, that SQL Server can't send the data over the network as fast as it would like.

There can be two underlying causes:

The app is written inefficiently and does not process the rows fast enough.
The network is maxed out.

If the application itself is too slow there will be no or no significant impact on the performance of other queries. If on the other hand the pipe is too small, other queries can't send their results either and have to wait.

In the latter case however you would have all connections waiting on ASYNC_NETWORK_IO. You should be able to see that impact clearly.


How to properly structure multiple user types? So i'm building a database for an app/website. It's essentially a e-commerce marketplace/platform where a brand can sign up to upload their products and customers can also then sign up to purchase any products from any various brands.
A brand and or employee cannot be a customer
A customer cannot be a brand
This is the structure I have so far, but it seems to me that from this diagram it implies that a user can be a customer, brand and brand employee at the same time.
Is there a better optimal structure for this?

You could turn around the foreign key relationships and add three foreign keys to user, one for each of customer, brand and brand_employee. Then add a CHECK constraint that makes sure that exactly one of the foreign keys is NOT NULL:
CREATE TABLE users (
   id                uuid PRIMARY KEY,
   ...
   customer_id       uuid REFERENCES customer(id),
   brand_id          uuid REFERENCES brand(id),
   brand_employee_id uuid REFERENCES brand_employee(id),
   CHECK (customer_id IS NOT NULL AND brand_id IS     NULL AND brand_employee_id IS     NULL OR
          customer_id IS     NULL AND brand_id IS NOT NULL AND brand_employee_id IS     NULL OR
          customer_id IS     NULL AND brand_id IS     NULL AND brand_employee_id IS NOT NULL)
);


MongoDB Storage Architecture (MySQL innodb_file_per_table equivalent to MongoDB) I am new to MongoDB.I looked inside dbpath I found

users.0 -  16.8MB
users.1 -  33.6MB
users.ns - 16.8MB

Further when I dig deeper I found,

The .0, .1 files are datafiles. Each datafile is preallocated to a particular size. (This is done to prevent file system fragmentation, among other reasons.) The first filename for a database is .0, then .1, etc. .0 will be 64MB, .1 128MB, et cetera, up to 2GB. Once the files reach 2GB in size, each successive file is also 2GB.
The ".ns" files are namespace files. Each collection and index would count as a namespace. Each namespace is 628 bytes, the .ns file is 16MB by default.Thus if every collection had one index, we can create up to 12,000 collections. The --nssize parameter allows you to increase this limit

But I when I started comparing MySQL with I figured out if you specify

innodb_file_per_table = true/1

But I cannot find any option for MongoDB to create separate file to each collection.

Does MongoDB only creates datafiles(.0, .1) which will contain all the collection?

Or

Is there any way to tell MongoDB to create separate files for each collection?

Your kind help and precious time would be highly appreciated

MongoDB only creates discrete file-sets for discrete databases. There isn't an option to split out collections into files like table-files in MySQL.


Microsoft Access Listbox Query I have 2 tables in Microsoft Access.
Table 1:
ID    Val1    Val2    Val3
 1      a       b       c
 2      e       f       g
 3      h       i       j

Table 2:
ID    Val Chosen
 1        a
 2        f
 3        j

In the Val Chosen field, I want Access to create a listbox in Val Chosen that contains the values in Val1, Val2, Val3 for the respective id.  For example, when the user clicks the Val Chosen listbox for the record with ID 1, it should contain "a", "b", and "c".  The Val Chosen listbox for the record with ID 2 should contain "e", "f", and "g".
In design mode for "Table 2", the display control is set to list box. The following query is used as a row source and incorrectly returns all possible values.
SELECT Val1
FROM Table1
UNION
SELECT Val2
FROM Table1
UNION
SELECT Val3
FROM Table1

The actual database has many records so I would prefer to preserve the structure of the database if possible.

I think that in this case you better do this in a form.
The problem with your qeury is that it lacks where clauses. For id 1 for example, the selects to union should be:
SELECT Valx FROM Table1 WHERE ID=1
On a form you can do this for example by setting the row source in the on current event depending on the value of ID.
And yes, the structure of your database could need an update. Wich can be done with one simple query.


Is it possible to have xid before and after wrap around with the same timestamp? I want to create sync API in which users can get the changes since their last sync request, the API exposes cursor-based pagination (sync token)
A possible cursor may be to use updatedAt column along with xmin column (I need the xmin because many rows may have the same timestamp).
My only concern is if it's possible to have rows with same (updatedAt) timestamp and wrapped around xmin:
+---------------------+------------+
| timestamp           | xmin       |
+---------------------+------------+
| 2011-01-01 00:00:00 | 4556455456 | -> first row
+---------------------+------------+
| 2011-01-01 00:00:00 | 6          | -> second row, wrapped around xid
+---------------------+------------+

Within a specific timestamp (moment), I can be sure xmin will behave like a simple sequence, right?

Assuming updatedAt stores the transaction timestamp as reported by now() or CURRENT_TIMESTAMP. See:

Difference between now() and current_timestamp

Then yes, xmin behaves like a simple sequence, basically.
But you may want to look at cmin additionally. The manual:

The command identifier (starting at zero) within the inserting transaction.

Related:

How did this statement update 3 rows in Postgres?

And, subtransactions (incurring a SAVEPOINT) may very well interfere with your plans, as those advance the transaction ID (and hence the xmin of rows written after that) within the same transaction, while the transaction timestamp stays the same.
So, that's a YES to the question in the title. It is possible to have xid before and after wrap around with the same timestamp.
There are various ways subtransactions may get involved:

Calling the SQL commands manually, obviously
A PL/pgSQL block with an EXCEPTION clause
The ON_ERROR_ROLLBACK setting in Postgres
Some replication solutions involve savepoints (I think)

Related:

How to view tuples changed in a PostgreSQL transaction?

Related discussion on ppgsql-general


H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition.
However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the NOT NULL columns and I don't seem to find anything similar to check the constraint on the table columns.

Notice I don't want to check the NOT NULL column values with something like SELECT COLUMN FROM TABLE WHERE COLUMN IS NOT NULL!  I want to check the table definition for that specific column.

thank you

If you have a definition like this:

create table foo
(
   id integer not null
)

The following query will show "NO" in the is_nullable column:

select table_name,
       column_name,
       is_nullable
from information_schema.columns

If the column has a check constraint, like this:

create table foo
(
  id integer,
  constraint id_not_null check (id is not null)
);

then this will be shown as "nullable" with the first query. You would need to check information_schema.constraints and analyze the content of the CHECK_EXPRESSION column to detect this.


The backup set holds a backup of a database other than the existing I'm trying to restore my production .bak file to a new local instance using the below script but I still get this error:

The backup set holds a backup of a database other than the existing MyDatabase database.

I'm using SQL Server 2019.
USE [master]
RESTORE DATABASE [MyDatabase]
  FROM DISK = 'C:\MyDatabase.bak'
  WITH
  MOVE 'MyDatabase' TO 'C:\MyDatabase.mdf',
  MOVE 'MyDatabase_log' TO 'C:\MyDatabase_log.ldf',
  REPLACE
GO

This is a type of safety check done by SQL Server so you don't by mistake overwrite the wrong database.
I believe it includes things such as that the destination database exists and the name of the destination database isn't the same as the name of the database you have in the backup. Possibly a few more things.
So double check and triple check that you connected to the right SQL server etc.
But the REPLACE option should override that safety check, that is why it exists! So make sure that you are 100% certain that you executed with the replace option.
You can of course first delete the destination database since the RESTORE will (internally) do it for you anyhow, and then do the restore.


What is the difference between an INNER  JOIN and an OUTER JOIN ? I am new to SQL and wanted to know what is the difference between those two JOIN types?

SELECT *
FROM user u
INNER JOIN telephone t ON t.user_id = u.id

SELECT *
FROM user u
LEFT OUTER JOIN telephone t ON t.user_id = u.id

When should I use one or the other?

An inner join will only select records where the joined keys are in both specified tables.
A left outer join will select all records from the first table, and any records in the second table that match the joined keys.
A right outer join will select all records from the second table, and any records in the first table that match the joined keys.

In your first example, you will only return a list of users and telephone numbers if at least one telephone record exists for the user.

In your second example, you will return a list of all users, plus any telephone records if they are available (if they aren't available, you'll get NULL for the telephone values).


Is it possible to `DELETE` in a subquery? Is it possible to run a DELETE as a subquery of a SELECT? Eg:

SELECT * FROM posts where title = (DELETE FROM posts returning 'whoops');

When I try this, I get syntax error at or near "FROM", pointing to the FROM of the DELETE, so it doesn't seem possible, but maybe I'm just doing it wrong somehow.

Context: I'm trying to understand the possible damage of some SQL injection attacks. In this case, the overall query will be run in a prepared statement with no variable bindings. This means an attacker can't run multiple top-level statements, but I'm trying to assess the possible damage from subqueries.

Edit: I should have said, the querying code is doing something like:

 query_parameter = blindly_read_from_user_input()

 # this is just interpolated into a string
 db_query = "SELECT * FROM posts WHERE title = '#{query_parameter}'"

 connection.prepare("query_name", db_query)
 connection.exec_prepared("query_name")

So:

It's using a prepared statement, which means an attacker can't cause multiple top-level queries to run
It's not using bound parameters

I'm trying to see whether I can delete records based on SQL injection in this existing query structure.

You can DELETE in a CTE - which is a different kind of subquery.

But you probably don't need it for this simple case, you just need to use RETURNING and fix the syntax errors in your code:

DELETE
FROM posts AS p
WHERE p.title = 'whoops'
RETURNING p.* ;


Database Design: Should I use a Materialized View I am designing a database based off of the NBA stats website (http://stats.nba.com/player/#!/201939/?p=stephen-curry)

If you go to the link posted on the player's stat, you will notice that the player's stats are organized based on different factors (such as the season, month, location, home or away, etc), which you can set based on your filter setting.

The question is, how did the person who designed the site database allow for efficient querying of the each players stats. Each player will have a series of game logs, which will record their stats based on each quarter.

Did they use materialized view to calculate the monthly and seasonal stats for efficient querying?

I try to design the database below:

Unless you work on that database, that's not really something we can answer. However, we can speculate.

It could be a materialized view, but it could also be that a job runs on a schedule to calculate the stats. Maybe computed columns. Maybe just really fast hardware with efficient queries and the perfect indexes. So many possibilities.

My bet would be on the job though.


Why do I need DAC if I am able to connect to the server via sqlcmd? DAC attracted my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned out that the only way to access the Server was to use DAC connection which unfortunately was turned off by default. However, I was able to overcome the problem (I removed trigger) using just sqlcmd.

My question is if I can connect to the SQL Server just using sqlcmd, what is the purpose of DAC then?

Referencing Diagnostic Connection for Database Administrators with highlighting mine:

  SQL Server provides a special diagnostic connection for administrators
  when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to
  execute diagnostic queries and troubleshoot problems even when SQL
  Server is not responding to standard connection requests.


Why Can't I shrink log file in full recovery mode I have a log file that is 302MB.

I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report)

If i try to run

DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0, TRUNCATEONLY)

or

DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 0)

the file is still showing as 302MB.

I know I can change the database to simple recovery, then run one of the commands above and set back to full recovery (and then do a full backup to make sure the database is not in Pseudo-simple recovery mode)

However, why can I not shrink the file in full recovery mode?

I know shrink isn't something you should do, but in my real world database, since no one had ever backed up the transaction log it had grown to 30GB and now regular log backups have been put in place to prevent growth of this level

However, why can I not shrink the file in full recovery mode?

It's working as designed. You just need to understand more about what is going on, so you can decide which is the appropriate action to take.

That, or you're just trying to shrink it at the wrong time (see my note #7 below).

First of all, "log file" is a bit of a misnomer: SQL uses the transaction log as its "working space" for everything it does, so it will always be non-zero in size. In my experience, I normally expect the tran log to be between 10% and 25% the size of the data files, depending on what recovery mode you're using, and the kind of activity on the database. You didn't mention the size of your data file, so I can't judge whether 302MB is big (it frankly doesn't sound very big to me).

Regarding recovery mode, the key question here is: if you have to recover the database (due to an emergency), will you need "point-in-time" recovery? Or is a restore to the latest full backup sufficient?

Putting aside some unusual situations (replication/CDC/mirroring/availability groups), your recovery mode choice is more of a business decision than it is a technical one. In either mode, SQL server uses the transaction log for in-process transactions, the difference is what happens next:

FULL recovery mode: When a transaction completes, it sticks around in the  tran log until you perform a transaction log backup. This tends to make the log larger (depending on how often you run the log backups), but means you can do a "point-in-time" recovery in the event of a disaster: you can restore the latest full backup, then use the tran log backups to "roll forward" to just before the disaster occurred.
SIMPLE recovery mode: When a transaction completes, the space it used in the tran log is immediately available for re-use by later transactions. This keeps the transaction log file (comparatively) small, but means that your only choice in the event of a disaster is to restore from the latest full backup.

In some companies, you'll use FULL for all production databases, and SIMPLE for all QA/DEV environments. Or perhaps some specific databases, like ones used for  reporting and/or data processing don't need full recovery because you can simply re-run the prior day's processing to get back up to date. But like I said, that's a business decision/risk analysis question, not really a technical one.

So, if you don't need point-in-time recovery, then switch it to simple mode, and leave it that way.

If you do need point-in-time recovery, then switch it to full mode, and run regular tran log backups throughout the day, according to your business risk/recovery needs. Hourly is popular, but some people recommend them far more often.

One final point that I alluded to before: once you've selected the correct recovery mode, you might still need to shrink an over-sized log. A few things to keep in mind:

Again, make sure you've chosen the right recovery model
If you're running in FULL mode, make sure you're running frequent tran log backups
Choose a reasonable target size for the log, never try to shrink it to zero. I typically scale it based on the total size of the data files (or at least the used space within the data files)
Read and understand this important advice about VLF size. This might provide more guidance about deciding on a reasonable log size, or give you a better method for getting your log to that size.
Never use SHRINKDATABASE, always use SHRINKFILE
In both simple and full mode, there is a chance that the end of the log file is currently being used by SQL for an active transaction. If that's the case, then it won't shrink as much as you want. Just shrink it again a few minutes later, and if the transaction is complete, it should work better the second time.
With a particularly stubborn tran log in full recovery mode, I've had luck doing SHRINKFILE, immediately followed by a transaction log backup, immediately followed by another SHRINKFILE.


Best Database for range queries on a single column here's my usecase: I have these fields: id(int), frequency(int), created(timestamp).
I need a database that can optimize range queries on created field. Specifically for queries like: SELECT * FROM my_table WHERE created >= val1 AND created <= val2
This db needs to handle millions of queries and also scale properly.
I've looked at Cassandra and understood that it doesn't work well for range splice queries like this since it needs to query all partitions. I suppose I could simply create an index on the created column and query it in a SQL type db but the reason I'm not looking at SQL db is that it won't be able to scale well (like there's no auto-sharding available in SQL dbs for example).
Please let me know of any db suggestions for this usecase. Thank you!

I'll raise a_horse_with_no_name's ante, and tell you I've personally worked with multi-terabyte databases in Microsoft SQL Server that had single tables in the hundreds of billions of rows without any form of Sharding or Partitioning setup, and ran just fine. I've even discussed the topic with Microsoft representatives who've claimed the system can handle tables into the trillions of rows.
Specifically I've worked with range queries (including on fields like a CreatedDate) on tables in the tens of billions of rows, and they were efficient and performed index seeks when appropriate (based on the cardinality of my predicates). The hardware behind the servers these databases ran on were as modest as an average laptop, in some cases.
As far as any modern database system goes (relational or not), you shouldn't choose one based solely on the amount of data you plan to support. Rather you should choose one based on the type of data (inclusive of it being structured, semi-structured, unstructured, or highly variable), and your use-cases for that data such as the type of querying you plan to do.
With such a skinny and structured table of only three columns with lightweight data types, I see no reason why any modern relational database management system couldn't handle your requirements. Proper indexing and architecting will go the longest of ways towards your performance and scalability, as opposed to which database system you actually choose.


MySQL table design of logging tables I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization.

Currently we have a table that logs session data, it writes about 1.2million rows a month.  At the beginning of each month we split off last months rows and move them to a table called "tbl_session_mm_yyyy".  We want to start querying the live table and the older tables and pull out details like last logged in (simple sql query, select maxdate from tbl_live, where accountid = 123 union select etc from tbl_old etc).

Now i'm thinking that the design of our database structure isn't quite right for this.  So where we would have 12 tables for the year, each year, we would have to keep building huge unions to get all those tables.  Thus would it be better to do this:

tbl_sessions: - this is the live table that is written to all the time, indexes on things like userid, accountid. this would only ever contain the current months records.

tbl_old_sessions: - this would replace the monthly table and be one giant table that contains all previous months records. Partioned by month (on a datefield populated by now()) and indexed on accountid, userid etc

Does this design make better sense than my previous one of monthly tables? should i be worried about partioning the tbl_old_sessions if i'm doing very little querying by actual date?

Your new design will make it easier to query across all of the tables without the need to know which tables to union when you write a query.

I'm not sure if you need to worry about the partitioning aspect. It sounds OK, but I've never worked with something like this on this scale (not in MySQL).

If you are worried about the partitioning, you could stay with your current solution, but instead of a new table for every year+month, have 12 tables, one for each month, for all years. At least your unions would be constant.


SSMS clean output window with sql instruction How can i clean output window in SSMS using t-sql syntax? Is it possible ?
Do you know any workarounds ?

This is not possible to do within the context of a single execution of whatever is in the Query pane. T-SQL commands are not interpreted by SSMS. They are sent to SQL Server to be processed. Only a GO [execution_count] statement (batch separator, GO being the default but can be changed) and a subset of the SQLCMD commands -- !!, :exit, :quit, :r, :setvar, :connect, :on error, :error, and :out -- are captured and processed by SSMS (and are not sent to SQL Server).

Also, there aren't even any internal SSMS commands to clear either the Results tab or the Messages tab.

So, if there are any results being returned, they will be in the Results tab at the end of the execution no matter what.

BUT, after that execution ends, you can manually clear both the "Results" and "Messages" tabs by highlighting an empty line (or even just a space on a non-empty line) and hitting F5 or Control + E.

OR, depending on why this is being requested, you can hide the "Results" and "Messages" tabs by hitting Control + R.