When tempdb allocations are released? In a Service Broker queue, when one message is proceed over time, we are executing a stored procedure which is performing some heavy operations using temporary tables.
I guess that the sp_WhoIsActive is showing summarized values for tempdb allocations and CPU usage, as the queue is using one session.
The question is, are the allocations released when the current stored procedure completes?

Please see the Microsoft BOL for Temporary Tables:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Once the TempTable is dropped, the space it was consuming (and any resources reliant on it) is released (internally). If the TempTable caused your TempDB data file to grow on disk, even though the space is released internally in SQL Server, it won't be released back to the disk in the OS (just like any other database's data file). It will be re-used internally by SQL Server for other TempDB operations though.
Restarting SQL Server or running a SHRINK command are ways to release the internal unused space back to the disk for the OS, if that is of concern.


Marking duplicate records I have a list of things, some of which are duplicates.

create table things (
  id int primary key,
  title text not null,
  author text not null,
  duplicate_of int null references things(id)
);

I'd like to mark duplicates as such (can't delete them). The first item (defined by the lowest id) should never be marked as a duplicate. Its duplicates should have their duplicate_of value set to its id.

This is what I have so far:

update things a set duplicate_of = (
  select
    id
  from things b
  where
    b.title = a.title
    and b.author = a.author
    and b.id < a.id
  order by
    id asc
  limit 1
  );

Is there a more efficient way to accomplish this?

you can make use of the so called ctid (which is basically an internal thing but for a lousy hack it should do. here is an example:
'

test=# CREATE TABLE x (id int);
CREATE TABLE
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# SELECT ctid, * FROM x;
 ctid  | id
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
 (0,5) |  1
 (0,6) |  2
 (0,7) |  3
 (0,8) |  4
(8 rows)

test=# SELECT id, min(ctid) FROM x GROUP BY 1;
 id |  min
----+-------
  4 | (0,4)
  1 | (0,1)
  3 | (0,3)
  2 | (0,2)
(4 rows)

test=# DELETE FROM x WHERE ctid NOT IN (SELECT min(ctid) FROM x GROUP BY id);
DELETE 4
test=# SELECT ctid, * FROM x;
 ctid  | id
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
(4 rows)
`

keep in mind; the ctid is an internal thing so be careful.


How to trace a MySQL session like in Oracle I have an application running in MySQL 5.1.58 community edition.

My application is working fine, but suddenly any query stuck and then after all queries is getting logged in slow query log. I am curious to know whether which query is the real culprit.

In Oracle, we can easily trace the session to find all queries executed in a user session. Is there feature in MySQL through which I can find the list of all queries during a single session.

MySQL has "General Query Log". This logs everything that is going on MySQL server: users connecting, disconnecting, queries etc.

This query log is a file on your filesystem or (from 5.1.6 versions) table

Control the general query log at server startup as follows:

Before 5.1.6, the general query log destination is always a file. To enable the log, start mysqld with the --log[=file_name] or -l [file_name] option.
As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the --log[=file_name] or -l [file_name] option to enable the general query log, and optionally use --log-output to specify the log destination
As of MySQL 5.1.12, as an alternative to --log or -l, use --general_log[={0|1}] to specify the initial general query log state. In this case, the default general query log file name is used. With no argument or an argument of 1, --general_log enables the log. With an argument of 0, this option disables the log.
As of MySQL 5.1.29, use --general_log[={0|1}] to enable or disable the general query log, and optionally --general_log_file=file_name to specify a log file name. The --log and -l options are deprecated.

After starting query log, investigate the file (or table) for further information.

MySQL Query Log Documentation: http://dev.mysql.com/doc/refman/5.1/en/query-log.html


Do postgres autovacuum properties persist for DB replications? I would like to understand postgres replication options more, and as I am doing my research, I have come across a concept of autovacuuming in efforts to create routine DB maintenance.  My question is whether or not autovacuum settings persist across replications? My initial thought is no, because some of these parameters are set at a server level? But also there are table and DB specific autovacuum options.  If I set certain autovacuum properties on a specific postgres table or DB, would these properties translate across replicas of the DBs? Thanks in advance for the help.

VACUUM (both manual and auto) do not run on a physical replica.  The results of a VACUUM running on the master get replicated over, just like other changes do.  Per-table vacuum settings are stored in database catalog tables, and so get replicated over, but they have no effect unless/until the replica gets promoted.

For logical replica, its VACUUM settings are completely separate from the publisher.  Both the server-level and table-level settings are just whatever you set them to.


Subselect of MAX(Version) takes many minutes, though there are only ~20K records I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain.

Mainlines -> Releases -> Overlays -> Calibrations <- Parameters

Calibrations, then, is the child of the the Mainline-Release-Overlay chain, and Parameters. This is the set I want to return.

Now, the complexity is that -- to save space -- we are storing the base Mainline's calibrations, and then just the DIFFERENCES when changes happen at the Release and Overlay levels. This produces a "base" set of Calibrations of about 16K rows, and then a few hundred changes each Release, and perhaps just a few changes each Overlay. Deletions of parameters happen. To keep track of this, Calibrations has a Status field (tinyint) which is set to 1 for a deletion.

Overlays get versioned successively. So, to get a complete "calibration," we need to query the Calibrations table for the latest version of a Parameter's data, up to a particular Overlay version number. Parameter metadata may change, but the names stay the same, so these Calibrations may refer to differing versions of Parameters, albeit with the same name.

Up till now, the following has worked perfectly, and instantaneously (with the following caveats):

SELECT c.Parameter_ParameterID, p.Designation AS Name, c.Data, o.Version
 FROM Calibrations c, Parameters p, Overlays o, Releases r
 WHERE r.Mainline_MainlineID = 9
    AND o.Release_ReleaseID = r.ReleaseID
    AND c.Overlay_OverlayID = o.OverlayID
    AND c.Parameter_ParameterID = p.ParameterID
    AND o.Version =
        (SELECT MAX(o1.Version)
            FROM Parameters p1, Calibrations c1, Overlays o1, Releases r1
            WHERE r1.Mainline_MainlineID = 9
            AND o1.Release_ReleaseID = r1.ReleaseID
            AND c1.Overlay_OverlayID = o1.OverlayID
            AND c1.Parameter_ParameterID = p1.ParameterID
            AND p1.Designation = p.Designation  -- New condition
            AND o1.Version <= 68)
    AND c.Status != 1 -- Changed from boolean to tinyint
ORDER BY Version DESC, Name

The problem has come in since we have started (properly) tracking Parameter versions, so the inner match for the Parameter can no longer by on the ParameterID, but rather the Designation (i.e., Name). At first this wasn't a problem, but it is now that Calibration Status is no longer a boolean for deletion. Now it can also be set to 2 for newly introduced, and 3 for reinserted.

Adding these two changes have caused this query to take 10-15 minutes to run! How is that even possible? Even if I contorted the query to connect every row to every other row, it shouldn't take this long!

There are two maddening things here. One is that, when this initially became a problem, I put an index on the Parameters Designation field, and it made it work again. Now, deleting and recreating it doesn't make a difference. Also, there's still a special case, in that, if I have exactly two Mainlines in the database, it still runs instantaneously. If I have only one or more than two, it takes forever.

I've tried variously commenting out sections, like the o1.Version <= condition and the c.Status != condition, and sometimes, it works again. I can't get a handle on the differences in the various cases, and I'm at the end of my rope. I know I'm a tiny trick away from getting it to run fast again, but it's apparently beyond me, and this is a show-stopper for my beta plans next month.

I've been programming for 35 years, but I've never had to deal with SQL like this before, and SSMS's tools for showing explain plans mean nothing to me. I would like to understand the fundamental issues and get a fix.

Execution plan for the long query.
Execution plan for the query when commenting out c.Status != 1

A full analysis would require access to the execution plans, table and index definitions, and database statistics (or a copy of the database itself). That's possibly unrealistic, so here's some general observations, and a possible solution for you to try. (Strictly, this question is probably beyond this site's remit.)

General background

The SQL Server query optimizer is the component responsible for choosing an execution plan for the logical results specification represented by your query. For even modestly complex queries, there are an enormous number of possible physical plans. The optimizer uses heuristics and cost estimation to choose from the limited plan space it explores.

The quality of information provided to the optimizer (including database design, indexing, and the accuracy of the current statistics) all have a great impact on the likelihood of the plan chosen by the optimizer performing well in practice. Where the design is relational, well-indexed, and with representative statistics, good plan choices will be the norm. Otherwise, all bets are off.

The query in question is moderately complex, and while its intent will be reasonably clear to most humans, the optimizer sees it as a SQL query like any other. The correlated sub-query with multiple joins and a MAX aggregate means the potential plan search space will be large, and estimates may not be accurate (due to accumulated errors, if nothing else). The optimizer may well end up choosing a plan that would work well if its assumptions were borne out, but which may perform horribly in reality.

The query and a potential rewrite

In broad terms, it appears you are asking SQL Server for the highest version per designation, with a limit on the version number. An alternative way to express this query (that is likely to have more predictable performance) is to number the rows (partitioned and ordered appropriately) then return the single row per group that is ranked #1:

WITH MaxVersionPerDesignation AS
(
    -- Do the joins and number the rows
    -- per designation, in descending
    -- Version order
    SELECT
        c.Parameter_ParameterID,
        p.Designation AS Name,
        c.Data,
        o.[Version],
        rn = ROW_NUMBER() OVER (
            PARTITION BY p.Designation
            ORDER BY o.[Version] DESC)
    FROM dbo.Releases AS r
    JOIN dbo.Overlays AS o
        ON o.Release_ReleaseID = r.ReleaseID
    JOIN dbo.Calibrations AS c
        ON c.Overlay_OverlayID = o.OverlayID
    JOIN dbo.[Parameters] AS p
        ON p.ParameterID = c.Parameter_ParameterID
    WHERE
        r.Mainline_MainlineID = 9
        AND c.[Status] <> 1
        AND o.[Version] <= 68
)
-- Return the row with the highest Version per designation
SELECT
    MVPD.Parameter_ParameterID,
    MVPD.Name,
    MVPD.Data,
    MVPD.[Version]
FROM MaxVersionPerDesignation AS MVPD
WHERE
    -- Row #1 per Designation
    MVPD.rn = 1
ORDER BY
    MVPD.[Version] DESC,
    MVPD.Designation;

Hopefully, the logic in the SQL above is reasonably easy to understand.

Analysis of the uploaded plan

The query optimizer produces an execution plan that attempts the execute the subquery once per outer row. This would be a good idea if the outer query did only produce one row, but sadly it qualifies 16,794 rows at runtime (execution plans captured using SQL Sentry Plan Explorer):

This mis-estimation is the root cause of your problem. Most probably, statistics on the base tables are not representative of the current data. You should refresh these statistics and have an ongoing plan to ensure statistics stay reasonably current. Another side-effect of the erroneous 1-row estimation is that the optimizer considers the whole query to be very low cost, almost trivial, and so does not spend long examining alternatives.

Anyway, the subquery (which is very similar to the outer query aside from the correlation and an extra predicate) is fully executed 16,794 times (with a different value for Designation and Version each time). This is an awful strategy of course (but it would have been just fine for 1 outer row).

The cumulative effect is to generate over 282 million rows (over all 16k iterations). This results in 282 million executions of the Key Lookup in the plan fragment shown above. This latter fact is likely the primary cause of the poor performance, though executing the correlated subquery 16,794 times would never be quick in any case (and the optimizer would not choose this strategy if it knew 16,794 rows would be driving it).

With optimal indexing (following a more detailed analysis than I can do right now), it would be possible to rewrite the query using CROSS APPLY and TOP 1 instead of MAX (see this related question) for possibly-optimal performance, but you may find the ROW_NUMBER alternative (again, see the related question) performs well enough, with good plan-choice stability.

Key Lookups:

You can eliminate the Key Lookups (in the long plan) by including the Parameter_ParameterID, Data, and Status columns in the IX_Overlay_OverlayID index.  For example:

CREATE INDEX [IX_Overlay_OverlayID]
ON dbo.Callibrations (Overlay_OverlayID)
INCLUDE (Parameter_ParameterID, Data, Status)
WITH (DROP_EXISTING = ON);

This will likely not cause the optimizer to choose a better or more stable plan without updating statistics, but it would be remiss of me not to mention it.


High CPU usage, low memory usage on Mariadb 10.5.8 -- SQL tuner complaining about memory, performance seems fine I'm hoping to get some friendly advice how to proceed with optimizing this server.
VM:
8vCPU - 2.6GHz
32GB mem
Disk speed
~3Gbps (380MBps)

Application is Librenms
my.cnf.d/server.cnf  -- Built from template somewhere on github
mysqltuner.pl output
The server currently idles at around 500% cpu with Mariadb being the culprit
sar -q output
free -m output

Increase innodb_buffer_pool_size to be about 70% of available RAM.
High CPU implies poor indexing and/or poor query formulation.  Let's see some queries and the matching SHOW CREATE TABLE.
 INDEX(`rule_id`,`device_id`,`state`)  -- handles the following:
 INDEX(`rule_id`,`device_id`)   -- DROP
 INDEX(`rule_id`)   -- DROP

Add
 INDEX(state, time_logged)

Shrink INTs to some smaller datatype.  Saving space speeds up the query a little.
Invert the query -- Find the 50 rows, then do the JOINs:
SELECT ...
    FROM (
        SELECT id FROM log
            WHERE ...
            ORDER BY  time_logged DESC
            LIMIT  0,50
         )
    JOIN log AS e USING(id)  -- to get other columns from log
    JOIN the other tables for the other columns
    ORDER BY  time_logged DESC  -- repeat if ORDER matters

Use pt-query-digest to summarize the slowlog; it will be easier to spot the "worst" queries.
Other issues...

max_connections = 200000 is ridiculously high, even dangerously high.  That is the number of "connections" to allow at any one time.  RAM is consumed for each connection.  Do SHOW GLOBAL STATUS LIKE 'Max_used_connections'; to see that you have not come anywhere near that value.  100 is a safe starting value until you have a reason to increase it.
Notice (in slowlog) Rows_examined: 973090.  How many rows in each of the tables?  The changes I suggest will probably bring that number down, though probably not down to the optimal of 50 (as in Rows_sent: 50).
An owl can turn its head almost all the way around.  That is how my head feels when I see a LEFT JOIN and RIGHT JOIN in the same statement.  My head would appreciate it if you could turn that RIGHT JOIN into a suitable LEFT JOIN or, if appropriate, INNER JOIN.  In particular, in the queries with AND  R.severity >= 2, it really is an INNER JOIN.


When to add a new table or new column I have looked at previous questions dealing with New table or new column and When should I use a new table? and a few others. I'm a little confused, not sure if my problem has a different need/solution; so here goes:
I have a table for users with a userid, name, email, etc.  This is used for both employees and for customers (at around 800 entries, it is split nearly 50-50).
I have a need to incorporate about 4-6 more columns for just the employees; such as manager, citizenship, office, etc.  These would only be used for the employees and not for the customers.
My question is: do I create another table for the employees with those extra columns or do I add those columns to the 'users' table and default to NULL (for customers) in each one?
I thought this was a different situation than the other questions, which is why I posted it.  If not, please let me know.
Thank you

For proper normalization, I would split them up into two tables. It makes for a more logical design, and easier to maintain down the line if you want to make additional modifications.


How should I be using Users and Access Roles in MongoDB? The documents are confusing me a little bit and I'm not finding any answers on here or google. I'm using my own auth via passport-local-mongoose, so I don't need to use mongo's auth at all.

The docs say that I should create a new user for each user/application. Does this mean I create a user for each entry point, ie: one for mobile app, one for web service api, and then an admin for management?

Or does this mean I need to make a new user for every single person who signs up on my site/app?

Every interaction with entry points (other than user registration) requires auth already, so creating a separate mongo user for every registered user seems redundant, however this is my first database so I'm not sure how things work as far as logging or security best practices.

It is worth noting that I am currently storing users as collections with embedded documents for their data for easy lookup per user.

Because user security is handled with your application and it's application what contacts the database (not user), you don't need to create every user to the database as a database user.

However, it is still good practice to create a user for every application that uses the database system, so that application can only use those databases what that application needs to access and do only those operations (insert, delete, update,...) what that application needs to do. Nothing else.


Why is it important to carefully determine and select statistics for the query optimization process? It's a general question rather than a db engine specific one. In popular db engines, we see the query optimization process taking statistics into consideration. I'm trying to understand what types of statistics the optimization function is using and on what basis these statistics are selected and used.

The type of statistics are cardinalities i.e. a count of the number of rows that have a certain value for a particular attribute.

To take a simple example, say I have a table with one column that is one character long:

create table T(c1 char(1));

This table contains the values A, A, A, B, C, F, K, X, X, Z. The cardinality will then be a count of the occurrences of each value:

A 3; B 1; C 1; F 1; K 1; X 2; Z 1

Now when the optimizer sees the query select * from T where c1 = 'A' it knows to expect three rows returned because the statistics tells it that's how many exist for that value.

In general tables can be large with many, many different key values. Storing complete statistics as I have shown would be impractical. What actually happens is that a statistically believable summary of the actual table is created. (There are many ways to do this; here's one approach; this answer works through the nitty-gritty.) This summary is compact enough to be traversed quickly during optimization to give good-enough results.

The statistics are used by the optimizer to choose between different physical implementations of the query. My single-table query above does not illustrate this well so let's say we have the query

select *
from TableA
inner join TableB
  on TableB.CustId = TableA.CustId
where TableA.SomeColumn = 42;

There are many possible implementations. It could apply the WHERE then do the join, or it could do the join then apply the WHERE. The physical join implementation could be a nested loop, merge or hash join. To read the data from disk it could do table scans, index look-ups or some combination. How is the optimizer to choose between these alternatives?

Most modern DBMS will use a cost-based approach based on the Cascades framework. Each alternative is given a cost (in some arbitrary units that only make sense within the optimizer itself) and the lowest cost option is chosen for execution. These costs are calculated based (amongst other things) on the number of rows expected which, of course, come from the statistics.


How to get 0 as array_length() result when there are no elements I have a query like this:
select id, array_length(users_who_like_ids,1) as ct
from queryables
order by 2 desc;

But empty arrays (with no elements) sort on top. I'd rather have array_length() return 0 for that case so that this would get sorted to the bottom.
I'm probably not understanding array_length() (ok, definitely) but:
embers_dev2=# select array_length(array[1,2], 1), array_length(ARRAY[]::integer[],1);

should return 0 rather than nothing (NULL), right?
Could I do an if statement like inline on it?
Edit
According to this related answer on SO, it looks like COALESCE might be what I want - but I am open to better ideas:

postgresql return 0 if returned value is null

should return 0 rather than nothing (NULL), right?

It's up for debate what this should return. But the way Postgres defines it, the result for any dimension that does not exist is NULL.
COALESCE is the tool to fix your query, but the better solution is not to break it to begin with.
Alternative in Postgres 9.4
Postgres 9.4 or later provides a separate function cardinality() that per documentation:

returns the total number of elements in the array, or 0 if the array is empty

Bold emphasis mine.
Seems like you deal with 1-dimensional arrays exclusively and just want to use that.
test=# SELECT cardinality('{}'::int[]);
 cardinality
-------------
           0

However, this is not the proper fix for your problem, yet. The whole array might be NULL, which returns NULL either way and still sorts on top.
Fix query with NULLS LAST
SELECT id, array_length(users_who_like_ids, 1) AS ct
FROM   queryables
ORDER  BY 2 DESC NULLS LAST;

Why do NULL values come first when ordering DESC in a PostgreSQL query?

This always deals with NULL values properly. You might still want to use cardinality() to sort empty arrays before NULL. But be aware of the difference when dealing with multi-dimensional arrays.


Combining data of multiple rows in view I have a table that holds timetracking data of users and looks somewhat like this:

projectid activityid worklocationid year  week  day         hours
1         22         21             2014  37    09/16/2014  8
2         22         4              2014  37    09/15/2014  8.5

So if a user saves hours on one project for every day of the week, there are 5 rows in the database that only differ in the "day" and "hours" columns.
I need to display this data on a webpage in a timetable format, without having to query the database for each cell to get the hours for a specific day. I'd like to generate a view that has one row per week for one project/activity/worklocation combination:

projectid activityid worklocationid year  week mon tue wed thu fri sat sun total
1         22         21             2014  37   8   8.5 9   8   8           41.5

I tried solving this with subselects and putting the days and hours values into one column respectively, separated by semicolons, but failed miserably because the table has over one million records.

My current approach would be using an OLAP cube, but I'm facing an issue:

There might be one entry for one week, e.g. on Monday, or one each for Monday and Thursday, or five for each day of the week, or none at all.
So if I want to save them in an ordered format, I have to determine which row belongs to which day of the week. Is there a way to do this that scales up to this large amount of records?

We're using Oracle 11g.

Please feel free to point out if I missed to write down additional information.

Look up PIVOT command in Oracle SQL reference manual, it is what you need to solve this problem. Over a million records shouldn't be a problem. Since you want day list as static, you can "pivot max(hours) as hrs for to_char(day,'DY') in ('SUN','MON'))" etc. Once you have the query, adjust for column heading appropriately, and generate a totals column. If this igoing to be regularly used, you can generate a MV using this logic and update it appropriately.

Here is a simple example (I just used a row generator for sample data)

with x as (select 'Test' typ, to_char(sysdate+level,'DY') dt
from dual connect by 1=1  and level <= 366)
select * from x pivot (count(*) as cnt
for dt in ('SUN' as sun,'MON' as mon,'TUE' as tue, 'WED' as wed,
'THU' as thu, 'FRI' as fri, 'SAT' as sat))


MariaDB, Triggers and last_insert_id I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information).

CREATE TABLE `sample` (
  `sample_id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sample_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sample_name` (
  `sample_name_id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `project` varchar(255) NOT NULL,
  `sample_id` int unsigned NOT NULL,
  PRIMARY KEY (`sample_name_id`),
  FOREIGN KEY `fk_sample` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,
  UNIQUE KEY `un_sample_name` (`name`,`project`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `run` (
  `run_id` int unsigned NOT NULL AUTO_INCREMENT,
  `sample_id` int unsigned NOT NULL,
  PRIMARY KEY (`run_id`),
  FOREIGN KEY `fk_run1` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I would like to check if a sample (based on sample_name) exists or if not, then insert both and use the sample_id to insert run entry as well. To do this I planned first to check if the unique name (name + project) exists and if so use it. However if it doesn't, then use trigger to do double insertion (I have a perl script to do this logic; I tried also the commented update version).

delimiter |
CREATE TRIGGER tr_new_sample BEFORE INSERT ON sample_name FOR EACH ROW BEGIN
  IF NEW.sample_id IS NULL THEN
    INSERT INTO sample () VALUES ();
    -- UPDATE sample_name SET NEW.sample_id = LAST_INSERT_ID();
    SET NEW.sample_id := (SELECT LAST_INSERT_ID());
  END IF;
END |
delimiter ;

So when sample name does not exists, I try to insert a record with null sample_id and thus the trigger reacts. However this doesn't work and I get a following error. Can someone suggest on how to fix this or make it work.

The database is actually MariaDB 5.2.12.

Thanks,

mysql> INSERT INTO sample_name (name,project) VALUES ('a','a_project');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pipeline_runs`.`sample_name`, CONSTRAINT `sample_name_ibfk_1` FOREIGN KEY (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE)

I try to insert a record with null sample_id

`sample_id` int unsigned NOT NULL,

The sample_id can't be null.  You will probably find NEW.sample_id = 0 inside the trigger if you don't specify a value on insert, since you have declared the column as NOT NULL... so you need to test for that instead:
IF NEW.sample_id = 0 THEN
...

...or change your table definition to allow null values.

Update (for the benefit of future visitors):
MySQL Bug #6295 (yes, from 9 years ago) is resolved in MySQL 5.7.1 and apparently changes this behavior so that NOT NULL validation is not imposed until after the BEFORE trigger is processed.  This behavior will likely make its way into MariaDB at some point also.  If the original poster of this question is using the = 0 test that currently works, it's possible that this behavior change could cause that logic to fail since the timing (or occurrence, at all) of the coercion to from NULL to 0 might also change.

If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug #6295, Bug #11744964)
— http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html

See also: http://dev.mysql.com/worklog/task/?id=6030


Manual Failover with AlwaysOn availability group We are planning to upgrade our SQL 2012 to SQL 2014. We currently use a mirror for our production databases but are planning to move to availability groups. We are going to be running 2 SQL Servers on our primary site and a single asynchronous replica at DR.

Our link to DR is sometimes quite patchy and so we don't want to rely on DR to have a quorum, which is also the advice from Microsoft: https://msdn.microsoft.com/en-us/library/hh270280.aspx. To provide an odd number of votes we are planning to have a node and file share majority (2 nodes + 1 file share at the primary site = 3 votes).

A couple of times a year we need to fail over to DR, either to test or because of maintenance (Electrical/Connectivity/etc) work at our primary site.

To failover to DR, we can set the DR replica to be synchronous, wait for it to catch up and then failover either using a script or the failover wizard. I've setup a test using 4 servers in Hyper-v (3x SQL + 1x AD). Once I do the failover to DR and then shutdown the SQL Servers the cluster goes offline.

I notice that the same msdn article I mentioned above says

  • Re-assess vote assignments post-failover. You do not want to fail
  over into a cluster configuration that does not support a healthy
  quorum.

What would be the best adjustments to make to maintain quorum after a planned failover to DR and the Primary site being completely offline (including the 2 primary SQL nodes and file share)?

It looks like the answer is Server 2012 R2. 2012 R2 only includes the file share when there are an even number of nodes. So if DR goes offline then the file share will be included in the quorum until such time that DR comes back online.

It also looks like from testing that if you gracefully shutdown connections on secondary's then the primary stays up. So to run from DR for a planned outage means we could failover gracefully and then shut down the servers at the primary site. Because DR became the primary, it will remain up as the last node standing until such time that quorum is restored (and synchronization is complete)


SQL Server trigger (update or insert with inner join problem) ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
IF (UPDATE([Kill]))
BEGIN
  SET NOCOUNT ON;

  INSERT  killer(serial,[Kill])
    SELECT i.serial,i.[Kill] FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.serial = d.serial
    AND i.[Kill] <> d.[Kill] AND i.[Kill] > 0;
END

However how would I go if I would want it to update if the serial already exist in the killer table. Could do update first and if it returns rowcount 0/null then do insert but I got stuck   WHERE - since I can't use i.serial = d.serial to compare there.

How about changing the trigger to a BEFORE UPDATE? That way you can check the values before any action has taken place.

EDIT

OK so I think I understand what you are trying to do. How about this:

ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  Merge killer as k
  Using (select serial, [kill] from inserted) as I (serial, [kill])
  On (k.serial = I.serial)
  When matched then
   Update set [kill] = I.[kill]
   When not matched then
   Insert (serial, [kill]) values (I.serial, I.[kill]);
END


SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture) I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a lot of transactions. I only need to audit the DML statements and not the values (e.g. I would need to know about an UPDATE statement and who performed it and on which table but I do not need to know what the value of the column was before the update).
From my research I have excluded traces and extended events due to their performance impact.
I have also excluded change tracking since it cannot be used with partial containment and I have some DBs which are set to partial containment.
I am currently mostly between using SQL Audit feature (i.e. using server and database audit specifications) and triggers.
I do understand that both the above methods will cause some overhead and that the performance impact depends on how much activity I am auditing, but given the same activity being audited (e.g. selects, inserts and updates on 10 tables), which one will cause the least performance impact between these two? I am planning to use an audit file (locally or on a separate fileshare) in case I use the SQL Audit feature (and not in the windows log file).
From my research I have also concluded that triggers have to be maintained with every schema change as well, so if SQL Audit feature does not cause a massive difference in performance (compared with triggers) I will go with it.
Also I am not sure whether change data capture or temporal tables will be a better solution compared to SQL Audit feature/triggers.
Any experience with these auditing methods and their performance impact would be greatly appreciated as information about this is highly limited.

CDC and temporal doesn't say who did the modification.
Audit has lower overhead than triggers. Audit uses extended events under the covers.
Audit might not give you which rows where read or modified (when parameterized sql is submitted), but that wasn't a prerequisite. I. e, I'd look at Server Audit first.


Unable to check is schema is present first I've created a SQL  script that will release a process to numerous SQL  Server instances so I want to check if a paticular schema is present before creating the schema.

I'm using the below and it creates an error but I don't understand why?

IF NOT EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'SQLInstance_Baseline')
    CREATE SCHEMA [SQLInstance_Baseline];

If I wrap the CREATE SCHEMA [SQLInstance_Baseline] in an EXEC i.e. EXEC('CREATE SCHEMA [SQLInstance_Baseline]') it works fine, but I don't really like doing this for best practice reasons.

The schema creation has to be the only statement within it's own batch, with no statements preceeding or following it.

As the IF statement is considered to be in the same batch, the statement is syntactically incorrect.

You have to wrap the schema creation within an EXEC, so it is considered to be in a separate batch.

Therefore the correct syntax for what you are doing is:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'SQLInstance_Baseline'))
BEGIN
    EXEC ('CREATE SCHEMA [SQLInstance_Baseline] AUTHORIZATION [dbo]')
END


Is there a performance consideration if stored procedures are in a separate database than the source data? This applies to a SQL Server 2012 instance.  We are considering structuring things to where we have a database that contains our source data, and then separate database (on the same instance) that would contain any stored procedures that query that data.  The stored procedures would then explicitly reference the "data" database when pulling any data.

Would there be any query performance problems in structuring things this way?

Edit: I'm coming at this from a developer's perspective; this is being proposed from the DBA level and I couldn't tell you what might be behind it. Best I can tell it's some sort of separation of interests - the data database is loading data from a 3rd party app so we can more easily report on it. I think they want to separate out stored procs we create to not clutter the "source data" db. This db is not a vendor database per se, but the application's data is unavailable in its native format. So there are daily processes that import the vendor data via ODBC into SQL Server so we can more easily get at it.

I've seen this type of thing done before.  Particularly where the data & possibly some code is coming from an outside location and may be overwritten with restores (for example).  It also allows the code to be backed up separately from the data (yes I know you could do that with filegroups also).

In general as long as your code is on the same instance as the data then there are no performance issues.

However you will likely run into some permissions issues.  There is something called ownership chaining that allows someone to successfully execute a stored procedure that updates a table even though they don't have permissions to the table directly.  This does not work cross-database.

Unless of course you turn on something called cross database ownership chaining.  This is not turned on initially and there are of course security implications of turning it on.  It basically allows for exactly what it says.  Ownership chaining will work across databases.  It can be turned on at a server or database level, although it is highly recommended that you not turn it on at a server level unless required for some reason.  You can however turn it on for your two databases and you should be fine.

I recommend reading the links above.  They appear to be quite good and will give you a better understanding of what I'm talking about.


MySQL 8.0 Replication filters lost between restarts I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information.

I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). I'll name these SRV1 and SRV2.

Out of 5 databases on SRV1, I want to replicate only 3 of them on SRV2.

I setup the replication filter with the following query, made to ignore the two remaining databases, so that in the future the replication process will pickup any new database created.

CHANGE REPLICATION FILTER
     REPLICATE_IGNORE_DB = (ignore1,ignored2);

The replication process worked as expected and I saw the three databases created and fully replicated on SRV2. So far so good.

The result of SHOW SLAVE STATUS \G; was correct, showing

[...]
    Replicate_Do_DB:
Replicate_Ignore_DB: ignore1,ignore2
[...]

But a wild Windows Update appeared. And made SRV2 restart during the night. I detected today that the replication process stopped because the replication user tried to do a query on ignore1.

One more SHOW SLAVE STATUS \G; confirmed it, my replication filters are not here anymore.

[...]
    Replicate_Do_DB:
Replicate_Ignore_DB:
[...]

Wanting to troubleshooting it, I put again my replication filters, restarted the slave process, waited that Seconds_Behind_Master went to 0. I then asked Windows to stop the MySQL service, and start it again, as it would do on a normal OS restart.

Once again the replication filters got lost, and the slave process stopped because it's trying to replicate a database that doesn't exist on SRV2

Before going on 8.0.15, I went on 5.7 during 3 years with the exact same setup, but never encountered a single issue like that during server restarts. Is it something intended (that I would have missed in the 8.0 release notes?) or a bug in my configuration?

I know that I can fix it by adding the --replicate-ignore-db argument to the MySQL service binary path, but I still want to know if there is another way.

The short answer is: this is a feature.

The CREATE REPLICATION FILTER syntax was created as a way to dynamically modify replication filters on the slave without a restart. (bug report)

You are right, that the documentation is a bit lean on this, but I found this snippet from FAQs in the MySQL Release announcement of the feature in 5.7:

11) Are these changes made through the new command persistent upon restarting the server?
A) No, the changes are not persistent. You have to change the values again after restarting the server. If you want these changes to be persistent, you can put them in my.cnf file.

[src]

What is interesting is that you claim this is the same setup on 5.7. It would be good if you could get that behavior reproducible (5.7 setup without options in my.cnf survive restarts).

I'd also recommend opening a bug report for 8.0/5.7 to at the very least get this behavior documented on the official documentation.

Immediate fix is of course to set the config options.


Where do I find the meaning of TdsInputBufferError numbers? We are having some sporadic connection errors on some of our SQL servers. I did some searching and found out about the Ring Buffer in SQL 2008 R2 and found some information, but I am not entirely sure how to figure it out.

Running the query

SELECT  CAST(record AS XML)
FROM    sys.dm_os_ring_buffers
WHERE   ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

Gives me some results, on of which is interesting

<Record id="4" type="RING_BUFFER_CONNECTIVITY" time="461419385">
  <ConnectivityTraceRecord>
    <RecordType>LoginTimers</RecordType>
    <Spid>0</Spid>
    <SniConnectionId>40A403B2-B860-4877-A1D3-1FA7024356B7</SniConnectionId>
    <SniConsumerError>17830</SniConsumerError>
    <SniProvider>4</SniProvider>
    <State>11</State>
    <RemoteHost>&lt;local machine&gt;</RemoteHost>
    <RemotePort>0</RemotePort>
    <LocalHost />
    <LocalPort>0</LocalPort>
    <RecordTime>5/19/2012 16:29:23.831</RecordTime>
    <TdsBuffersInformation>
      <TdsInputBufferError>109</TdsInputBufferError>
      <TdsOutputBufferError>0</TdsOutputBufferError>
      <TdsInputBufferBytes>0</TdsInputBufferBytes>
    </TdsBuffersInformation>
    <LoginTimers>
      <TotalLoginTimeInMilliseconds>18010</TotalLoginTimeInMilliseconds>
      <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>
      <NetworkWritesInMilliseconds>0</NetworkWritesInMilliseconds>
      <NetworkReadsInMilliseconds>18010</NetworkReadsInMilliseconds>
      <SslProcessingInMilliseconds>0</SslProcessingInMilliseconds>
      <SspiProcessingInMilliseconds>0</SspiProcessingInMilliseconds>
      <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>
    </LoginTimers>
  </ConnectivityTraceRecord>
  <Stack>...</Stack>
</Record>

So, my question is, where do I find the meaning for the TDS error numbers. In this case <TdsInputBufferError>109</TdsInputBufferError>

I'm not sure that this is an exhaustive list but both Error 109 and Error 10054 (from this similar question) are detailed in the SQL communication errors KB article.


Too many open connections with MongoDB 2.6 + PyMongo 2.7.2 I was running MongoDB 2.4 with a replica set which contains 1 primary, 1 secondary and an arbiter. We were using PyMongo 2.6 and MongoEngine 0.8.2.

Recently, we performed an upgrade to MongoDB 2.6, and also upgraded PyMongo to 2.7.2 and MongoEngine to 0.8.7. This setup worked fine for almost 12 hours, after which we started getting the below error:

[initandlisten] connection refused because too many open connections: 819

The ulimit is 1024 which worked perfectly with MongoDB 2.4* and hence we have not increased it. Increasing it might solve the problem temporarily but we will hit it again in the future. Somehow the root cause seems to be the inability of PyMongo to close the connections. Any pointers why this happens?

*When using MongoDB 2.4 with PyMongo 2.7.2 and MongoEngine 0.8.7, everything works well and the max connections are about 250. It is only with MongoDB 2.6, PyMongo 2.7.2 and MongoEngine 0.8.7 that the number of connections shoots up to 819.

So after a lot of struggle and a lot of load testing, we solved the problem by upgrading PyMongo to 2.8.1. PyMongo 2.7.2 is the first version to support MongoDB 2.6 and it sure does have some problems handling connections. Upgrading to PyMongo 2.8.1 helped us resolve the issue. With the same load, the connections do not exceed 250-300.


MsMerge_genhistory has alot of rows with pubid = null I have a merge replication and I am worried that the cleanup of metadata might not be enough. I have a retention period of 60 days and I can see thet the metadatacleanup-job do remove rows i msmege_genhistory that are older but only for rows that has the right guid in pubid. most of the rows, about 1,6 million, has the value NULL in pubid and I cannot figure out why.
does anybody know why there is so many null-values?

I have found out that since i use anonymous subscribers the publication will take ownership of all changes and it will not set a pubid for its changes. the only row that the subscriber adds is a row with artnick = 0. So if the subscriber adds changes the subscriber will only have one row and it will have a valid guid for the publication but the row does not point to any changes. instead the publication will add the changes as if the originated there.

The problem with the metadata cleanup was not related to the null values, it was orphaned subscriptions. If an anonymous subscription is created from one of our handheld devices and is not synced, it caused the cleanup to exclude generations. This is added with sp2 in sql2012 and the cleanup creates a list of excluded generations that are "active" the problem is that if a subscription in sysmergesubscriptions has null in last_local_sentgen it will add all generations between 1 and sentgen to the exclude-list and the metadata does not get cleaned. I have tried to remove these subscriptions in my test environment and now i can see the cleaning is being run properly. I can also see that this can also happen if a subscription-creation does timeout. The subscription can be created but the database in the handheld is not downloaded the subscription becomes orphaned and never synced.

Hope this helps someone.


is it possible to create a dynamic website without a database?(Only use a File to save Data) I learn PHP from now. And  for create a dynamic website usually use PHP + MYSQL(For database).

In PHP have function like fopen() to create, write result from form to a file like txt or other.

My Question is,  is it possible to create a dynamic website without a database?
(For very small Website maybe)?

Im very Appreciated your advice

Thanks.

It depends on how complex your web is.

I assume your web is contact mangager (create,update,delete) for 1 user only, you can storage records in a csv file on web server, it's not too difficult.

But if your contact manager serve many users, you have to implement concurrency, transaction for you text file. Its not easy.


Avoiding a divide by zero error in a computed column How do you avoid divide by zero error in the below table?

CREATE TABLE [dbo].[TblDivision]
(
    [Numerator] int NOT NULL,
    [Denominator] int NOT NULL,
    [Result] AS (Numerator/ Denominator)
)
GO
Insert into (Numerator, Denominator) TblDivision values (3,0)
GO

Just add a special case for division by 0:

CREATE TABLE [dbo].[TblDivision]
  (
  [Numerator] int NOT NULL,
  [Denominator] int NOT NULL,
  [Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end
  );


Merging multiple databases as separate schemas in PostgreSQL I have to combine data from 4 different databases. Each having its own design.

What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database

Then export tables from all other databases and import this data into a unique corresponding schemas schema inside my final database.

How do i export table data from Postgresql Databases.

After having one database with multiple schemas i will use cross schema queries to read and write data to target schema.

Thanks in anticipation.

I would assume that the other databases are Postgres as well? And all of them have only one schema? If you only have to do it once, then the easiest way is to use pg_dump -Fc to export the database and then edit the resulting SQL file by some regexp script.

In detail, you will have a

SET search_path = public;

row near the top of the file.  Rewrite 'public' to the desired schema name.  Other places to check for are where a stored procedure returns a user-defined type, view or table rowtype, here you will see something similar to

CREATE OR REPLACE FUNCTION f()
RETURNS public.my_view AS
...

Rewrite 'public' in such places as well.


Join 3 tables and return only distinct values for max datetime I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it must return all.

SELECT DISTINCT A.CASENO,A.DATE,A.TIME,A.TRANNO,B.PCODE,C.PDESC,A.USER
FROM tableA A
RIGHT JOIN tableB B ON A.CASENO=B.CASENO
RIGHT JOIN tableC C ON C.PCODE=B.PCODE
WHERE A.DATE between 140124 and 140331

Result still have duplicates because not same time and tranno. See result on CASENO.= 08088040

A.CASENO   A.DATE   A.TIME   A.TRANNO   B.PCODE   C.PDESC    A.USER
08088040   140124   182516   321         TYQ      PREPLAN1   ANTHONY
08088040   140124   182131   318         TYQ      PREPLAN1   ANTHONY
36360569   140128   111056   431         CVB      POSTT1     MARIA
36360569   140310   113221   433         CVB      POSTT1     MARIA
37386911   140213   150240   230         M2P      PLANAVG    FELISE
37386911   140213   135220   223         M2P      PLANAVG    HECTOR
39222881   140128    94122   104         TYQ      PREPLAN1   ELLA
40895213   140213   164409   104         CVB      POSTT1     WINNIE
51311866   140124   103203   319         M2P      PLANAVG    BRATT

For same date, same user and same pcode and pdesc, caseno. 08088040 came out twice because of the time and tranno difference. here the max tranno would indicate the latest transaction done on the same case no. the max time also shows that the latest datetime action done for that particular case no. was on 24/01/2014 @ 18:25:16. For the same date and user, I just want the latest transaction details.

I've tried added the below script as the first condition but result was 0. It has also caused long delays before I could get the output.

WHERE A.TRANNO=(SELECT MAX(TRANNO) FROM tableA A where A.CASENO=B.CASENO)

FYI, the rests, eg. case no. 37386911 is considered unique because of different user, so I will want that to be returned. Meanwhile, case no.36360569 is also unique record because different date. although same user.

So, how should I achieve this please? Much of the examples I found was only showing for 1 table and 1 criteria to get distinct values.

The script should be those used for SQL DB2. I actually no background at all in SQL. Most of the query I've done earlier was based on trial and error but it was successful because of no complicated conditions like this.

Hopefully, somebody expert here can share the knowledge.

This is a typical "greatest N per group" problem which is usually solved using window functions:

select caseno, date, time, tranno, pcode, pdesc, user
from (
  select a.caseno,a.date,a.time,a.tranno,b.pcode,c.pdesc,a.user,
         row_number() over (partition by a.caseno order by a.tranno desc) as rn
  from tablea a
    right join tableb b on a.caseno=b.caseno
    right join tablec c on c.pcode=b.pcode
  where a.date between 140124 and 140331
) t
where rn = 1;

You could also use your obfuscated timestamp information to do the sorting:

row_number() over (partition by a.caseno order by a.date desc, a.time desc) as rn

Given your example it won't probably make a difference.


Run sp_BlitzCache an agent job to assist performance tuning efforts / baselining query performance? Just getting into using various Brent Ozar tools like sp_BlitzCache and sp_BlitzIndex to identify and correct SQL Server performance issues. Been experimenting with running sp_AskBrent (now BlitzFirst...) and sp_WhoIsActive as agent jobs as a way to baseline query performance.

Do any of you run sp_BlitzCache periodically as an agent job to capture history / provide a baseline and/or evidence for your SQL Server performance tuning efforts? If so:

How specifically has this helped you in your environment?
Which sp_BlitzCache sort_order do you find most helpful to log? I'm considering separate output tables by cpu, reads, duration, and executions per minute. But wondering if I should be using "avg X" for these sorts.
Should I stick with the default of @top = 10? Or go for 20, 30, etc?

Here's an example where I'm using total reads and top 20:

EXEC DbaTools.dbo.sp_BlitzCache
 @top='20', @sort_order = 'reads', @output_database_name = 'DbaTools', @output_schema_name = 'dbo', @output_table_name = 'BlitzCache_by_reads'

How frequently would you run such a job? Weekly? Since the plan cache doesn't get wiped out until a host / service restart, an explicit command to do so (dbcc freeproccache), or in some other perhaps unexpected way (to some) such as changing MAXDOP (our Team learned the latter the hard way).
This is more in line with with another StackExchange question just I posted about sp_AskBrent and sp_WhoIsActive, but how resource-intensive is it to run these SPs on a periodic basis?

That other StackExchange question is here:

sp_AskBrent not logging queries when saving to table vs. sp_WhoIsActive

How specifically has this helped you in your environment?

My laptop instances are running great these days!

But seriously, if you lack a monitoring tool, this is a good way to analyze your plan cache for all sorts of problems, and opportunities for tuning. We warn you about a lot of stuff that may not be obvious just looking at an execution plan.

  Which sp_BlitzCache sort_order do you find most helpful to log? I'm
  considering separate output tables by cpu, reads, duration, and
  executions per minute. But wondering if I should be using "avg X" for
  these sorts.

You may find some overlap in the different sort orders. For instance, CPU and Reads often have a lot of overlap.

As for average vs total, that mostly depends on plan cache stability. If it doesn't change much, averages can be more interesting than totals. Sometimes small, not necessarily tune-able queries rack up a lot of total CPU time because they're run fairly frequently, as well as maintenance tasks. This is something you'll have to figure out for each environment.

  Should I stick with the default of @top = 10? Or go for 20, 30, etc?

This is also subjective, but lends itself a bit to another question of yours. The more plans you choose to bring in to analyze, the more resource intensive it is. Analyzing XML in SQL Server is... challenging.

The default is 10 to keep the footprint small, but also give you some meaningful results back. Of course, if you're not finding anything of interest in the top 10, feel free to experiment with different values to see how long it takes to run, and even if it helps you find anything interesting.

  How frequently would you run such a job? Weekly? Since the plan cache
  doesn't get wiped out until a host / service restart, an explicit
  command to do so (dbcc freeproccache), or in some other perhaps
  unexpected way (to some) such as changing MAXDOP (our Team learned the
  latter the hard way).

This depends a bit on plan cache stability. If the same stored procedures run over and over again, daily or weekly is fine. If your server gets a lot of ad hoc user queries pushed through, you may want to run it hourly, or at some other interval your server can handle.

You can also use the @IgnoreQueryHashes parameter to keep some of the noise out.

  This is more in line with with another StackExchange question just I
  posted about sp_AskBrent and sp_WhoIsActive, but how
  resource-intensive is it to run these SPs on a periodic basis?

We aim to be as resource un-intensive as possible, but the number of plans you choose to analyze, and the size of the plans can cause spikes. Like I said, analyzing XML in SQL Server ain't fun. It's single threaded, and can use a bit of CPU. You can use the @DatabaseName parameter if you're only interested in what one database is doing, which can help focus tuning efforts.

Thanks!


MySQL query performance of Date sorted data I found some articles and questions on this issue but not sure if I got the answer. very new to db modeling so please bear with me
My use case is:

Save user activities: I'm doing this by creating a one table and saving all user activities. The table has a ID, user_id(foreign key), time(Datetime), type(varchar), description
Query the table for activities for a user for a time frame and potentially for a activity type.

My concern is the performance when the table has a large number of rows. My steps that I think will help improve is

index the user_id
index the time

Then I can query for "user_is = 'abc' and time > somedate" or "user_is = 'abc' and time > somedate and type = xyz"

should I index type too? will having too many index have performance issues (when inserting new row or querying)?
What else can I do. Any advise will be greatly appreciated.
Thanks in advance

Your query is presumably something like:

select a.*
from activities a
where user_is = 'abc' and time > somedate

or

where user_is = 'abc' and time > somedate and type = xyz

The best index for the first query is activities(user_is, time) and for the second activities(user_id, type, time).


Hexadeicimal data and corresponding MySQL field type I used some Node.js code to stream a file image before inserting it to the database. I have a problem inserting the data into the related MySQL table field. So I run console.log(streamedFileContent) on the server to see what is this data about and I got this:

<Buffer 89 50 4e 47 0d 0a 1a 0a 00 00 00 0d 49 48 44 52 00 00 03 9e 00 00 03 52 08 06 00 00 00 0d a5 11 28 00 00 00 04 73 42 49 54 08 08 08 08 7\
c 08 64 88 00 ... 150808 more bytes>

What should be the appropriate MySQL field type for this data?

SQL is textual language. So you must convert your binary data to its textual representation (solid hexadecimal codes, for example, 'MySQL' -> '4D7953514C') and use it while building query text or attaching text parameter, and use UNHEX() function over this value in query text for to reconstruct binary value on the server side.

Avoid exceeding the max_packet_size value for your query text, increase it if needed in MySQL configuration file. Remember - textual representation is 2 times longer than binary one.

If the file which you want to insert into the table is local for your MySQL server you can use LOAD_FILE() function.


Full text search over multiple related tables: indices and performance We have the following database structure

CREATE TABLE objects (
    id       int   PRIMARY KEY,
    name     text,
    address  text
);

CREATE TABLE tasks (
    id int           PRIMARY KEY,
    object_id int    NOT NULL,
    actor_id int     NOT NULL,
    description text
);

CREATE TABLE actors (
    id   int  PRIMARY KEY,
    name text
);

The user enters a whitespace-separated list of words (search terms, basically) and we have to search for tasks, that satisfy the following: the task is a "match" if each search term occurs at least once in concatenation of task's description, the name and address of its associated object and the name of its associated actor.

Now, if we are not concerned about performance, we can just do something like this (given query "foo bar"):

SELECT t.id, t.description
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id
WHERE to_tsvector(concat_ws(' ', t.description, o.name, o.address, a.name)) @@
    plainto_tsquery('foo bar');

Unfortunately, we are concerned about performance. The dataset will, probably, be as follows (and it is expected to grow):

about 10000 objects
about 1000 actors
about 100000 tasks evenly distributed between objects

What I've considered:

Make a denormalized table like this:

CREATE TABLE task_documents (
    id int PRIMARY KEY,
    doc tsvector
)

The field "doc" will contain the concatenation of task's description, associated object's name and address and actor's name. We will have to create an index over this field and it will be used in full text search queries. This table will be updated in update / insert triggers on tasks, actors, objects.

Drawbacks: tons of duplicated data (this one I am not quite concerned about), and updates to the master tables will become unpredictable in terms of number of rows updated (say, you update a name of some object and now suddenly you must update thousands of rows in task_documents).

Honestly, I don't have any more (good) ideas. It is obviously impossible to create an index, spanning 3 tables, so that it will be used for the WHERE clause in the original query.

UPD

Here's a sqlfiddle with DB schema and some data. I had to make it up, because we have no real data at the moment.

Optimization

You're going down the right track.

You either need to

Denormalize
Cache

Caching the results

What you probably want is a MATERIALIZED VIEW. This is easy and works reasonably well.

CREATE MATERIALIZED VIEW foo
AS
SELECT t.id, to_tsvector(concat_ws(' ',a.name, o.address, t.description, a.name)) AS tsv
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id
;

Then just

SELECT * FROM foo WHERE tsv @@ plainto_tsquery('foo bar');

Denormalizing the table

This can take a lot of forms, you've got this right though..

Redesign

Searching everything in a fuzzy fashion like this is a losing game. Even this knock off of Dungeon and Dragons meets Yahoo Answers has rules.

It becomes a lot easier to generate a query when you introduce syntax likes [text] for tagging, and is:answer to search just answers, rather than rebuilding Google and normalized indexes.


Postgres how to update first row in the child table for all parent entries I have a table parent table workflow and child table workflow_task having one to many mapping.

workflow`

workID
workTitle

1800
vpInv1231

1801
vpInv1231

workflow_task

id
task_Type
workflow_id(fk)

1
null
1800

2
null
1800

Is there any way I can update all the first row in the child table with taskType as 'POC' and rest(from second to so on) as 'Appr'?.

Possible question interpretation:
UPDATE workflow_task
SET task_Type = CASE WHEN workflow_task.id = first_row.id
                     THEN 'POC'
                     ELSE 'Appr'
                     END
FROM ( SELECT MIN(id) id, workflow_id
       FROM workflow_task
       GROUP BY workflow_id ) first_row
WHERE workflow_task.workflow_id = first_row.workflow_id;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=08c60035f735ae557100dde8a7623008
workflow table not needed for this operation.


Shared emails in a database We lost our database administrator over a year ago - and even then she was promoted into the job internally and sort of learned on the job. Basically I say this to establish that strong data standards have not been enforced for a long time. We have a company that set up and administers our database - but they are particularly unhelpful and different people give different answers so I am really just looking for some best practices and maybe some key things that I should be asking for so that I get their attention and we are on the same page.

Our biggest problem is, in my opinion, that we have many many duplicate records. People make new records when they donate, they make new records when they sign up for email newsletters, they make new records when they sign up on our new app...etc etc. This causes problems down the line because then our website returns errors when it finds multiple profiles with the same email and won't let people buy things/register etc. - and we waste money mailing to the same people several times. We really need to tackle this problem - especially for our certified tree farm members because they are getting tired of getting multiple emails and mailings plus we need an accurate count for real programmatic reasons. I know I'm asking a big question - but how SHOULD these things be set up? I know there must be a better way because other websites seem capable of handling these issues fine. For instance - if we have a husband and wife - should they each have their own profile or share one? What if they share an email address (our population is a bit older so many do) right now we can create a 'shared email' relationship - is that a real thing or is there another way to do this?

I know it would be impossible to give a really specific answer - I'm just looking for a gold standard of functionality so I know what we should be aiming for and what we should be able to do. I don't really trust this company because they tell us things are impossible, but I see them working well all around me.

Thank you for any advice!

Many sites have taken to using an email address as the login id.  This works well for me as I don't have to remember the user id that goes with the email address used by the site.  This solution would require the couple to share a profile, if they use the same email address.

Some mail sites allow adding a prefix following a punctuation character.  This gives email addresses like me.site@example.com or me+site@example.com.  This can be used to separate customers in the case like a husband and wife sharing an account.

Getting your data cleaned up will be a problem.  Instead of blocking users where there are multiple records with the same email address, you may want to offer to provide the user id(s) matching the email address.  I would send the id or list by email with a link that allows them to switch their id to the email address.

Some sites allow or require an alternate email address.  It may not be appropriate to make that address unique.  However, you may want to monitor the number of accounts linked to the same alternate address.


Unsuccessfully granting privileges I'm currently attempting to define a new user using the following:

mysql> GRANT ALL PRIVILEGES ON simple_cms_development.* TO 'br'@'localhost' IDENTIFIED BY 'password';

However, it is unsuccessful and outputting:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password'' at line 1

I am running MySQL 8.0.11.

According to the MySQL Documentation, this feature was deprecated: https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations

Changes to account-management statements make the following features obsolete. They are now deprecated:

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is deprecated.

Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

The solution is to use two separate statements. One to ALTER USER, then a second to GRANT privs.
ALTER USER 'br' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON simple_cms_development.* TO 'br'@'localhost';


How can I speed up an sql query that round trips between two tables I first posted this question on stackoverflow, and now I see this site, seems more relevant.

These tables are not that big, but the time taken to process this query is long.

What can I do with the following queries to speed up the process taken to return results?

$chat_alg = mysqli_query($con, "
    SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY && sid != ''
    GROUP BY sid
    ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));

while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
    $chart_songs[] = $row_chat_alg['sid'];
}

$imploded_songs = implode($chart_songs, ',');

$projects = $dbh->query("
       SELECT * FROM music
       WHERE perms != 'c' && sid IN($imploded_songs)
       ORDER BY FIELD(sid,$imploded_songs)
       limit 50
");

...

This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.

From my (rather limited) knowledge of php, I assume you want to combine the two queries into one:

SELECT m.*, p.frequency
FROM
  ( SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY
      AND sid <> ''
    GROUP BY sid
    ORDER BY COUNT(*) DESC
    LIMIT 50
  ) AS p
  JOIN music AS m
    ON m.sid = p.sid
WHERE m.perms <> 'c'
ORDER BY m.frequency DESC ;

As for efficiency, it's better to tune the first query separately and then this combined one.

An index on plays (sid, time) should help for the first query. For the second, if there is an index on music (sid), it should be quite efficient as it's a join to only 50 rows of music.


What is hot spotting in the context of adding files to tempdb? I'm trying to find out whether it is possible to add tempdb files to a SQL Server without having to restart the SQL Server service. I saw this answer here on Database Administrators:

Tempdb Add files require Restart

And one answer states:

  ADD - no outage required. Although as Sean from Microsoft pointed out,
  SQL will prefer to use the lower filled files. If you are going from 1
  data file and adding more, then SQL will use the new ones for a while,
  but your performance won't be worse than only having one file.
  However, if you have 2+ already and add one more, then it will hotspot
  on the new one and decrease performance.

However, a comment cautions the following:

  I would put an addendum on the "Add" part: "Add: No, but you'll most
  likely be imbalanced so you'll be hot spotting which could make things
  much worse."

I have the following questions about that comment but was instructed to ask those questions in a new question of my own (this one) rather than asking the commenter via comment in that question's answers.

Specifically:

What is hot spotting? (I got some info via Google but not in detail
what happens with hotspotting on tempdb after adding files)
What about hot spotting makes things much worse in tempdb?
What specific things in the DB would get much worse?

What is hot spotting?

"Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my experience, anyway.
What about hot spotting makes things much worse in tempdb?

I think it is deemed to be worse in tempdb because it takes a brunt of the write activity in most workloads. You can certainly suffer similar problems in user databases, but since you're already trying to solve a problem in tempdb...
What specific things in the DB would get much worse?

Write times, mostly. Imagine everyone trying to use the same ATM, even when there are 7 other ATMs nearby. Only so much can be written at any point in time; everything else has to wait. With more files (and enough cores to schedule the work), the I/O can be spread more evenly.

Just make sure:

you have an even number of files
all the files are the same size
all the files have the same autogrow settings
the number of files is somehow driven by number of cores
depending on version (these are enabled by default in more modern versions of SQL Server), you are using trace flags 1117 (grow all data files at once) and 1118 (don't use mixed allocation extents).


How to install SQL Server 2017 Express in "Quiet Simple" mode without an extraction folder? I need to install SQL Server 2017 Express with as little user interaction as possible. I am using the SQLEXPR_x64_ENU.exe setup file that I found a Microsoft download for. The installer I am currently using is the 2017 CORE one linked to from here (I have no idea how people find official Microsoft downloads for anything!)

Currently I can do exactly what I want with 2012 by using the following parameters:

/FEATURES="SQL, Tools" /QS /IACCEPTSQLSERVERLICENSETERMS /ADDCURRENTUSERASSQLADMIN=1 /ACTION="Install" /ERRORREPORTING=0 /INSTANCENAME="MyDB"

In 2012 this works great, however I am trying to now do the same in 2017. The problem I have is that it creates an extraction folder called "SQLEXPR_x64_ENU" in the same directory as the install. This is not desirable.

Question: Is there anyway to prevent this extraction folder from being used, so that it functions that same as the 2012 setup?

Alternatively, one of the following solutions would be acceptable:

Allow the user to select the extraction location - like it does with default install (non-quiet) - but still ensure /QS mode for the rest of the setup
Have the extraction folder automatically deleted after setup (it wouldn't be so bad if it cleaned up it's mess after it was finished)

Why not create a PowerShell script to start the install. After the install is finished:

verify success (possibly with the return code or parsing the output log)
verify the existence of the extraction folder
if there are no errors delete the extraction folder


Backup and Restore of Development I am wondering if anyone can provide information on taking a differential backup and then restoring that differential backup to another SQL Server database?

I have a production environment that is connected to a SQLVault backup system that takes nightly backups.  I also have a development environment that is not connected to the SQLVault backup system.

The production environment is in a remote datacenter.  The development environment is hosted locally.  The WAN linked between the datacenter and the local office is slow, when I try to copy over the 400 GB of backup to my development server.

Currently I need to take a laptop with external HDD to the datacenter and execute the backup process.  Then pickup the laptop and external HDD and bring them back to the office.  Followed by executing the restore of the backup to the development server.

I would like to be able to perform a nightly differential backup and ship that across the WAN and then restore it on top of the previous full backup.  So, when I want to bring my development environment into sync with my production environment...I execute the full restore with differentials from the previous several days/weeks.

I tried taking a full backup yesterday.  I then took a differential backup today (appending it to the backup I took yesterday).  I tried to restore both, but get an invalid state error message when it moved on the second backup file.

I have also looked into log shipping, but that looks like it is configured more for a standby server and not another environment.

Thanks
Jlimited.

Whenever you restore a backup file with the intention to restore more backup files, you need to include with norecovery. In your scenario, it would look something like:

restore database [yourDB] from disk = 'backup_file.bak' with norecovery, file=1
restore database [yourDB] from disk = 'backup_file.bak' with recovery, file=2

Note, once you run a restore with recovery, no further restores are possible. So, in your scenario where you want to restore from production every day, you'd need to restore the full backup every time. Not that that's a bad thing; I do it in my shop and it seems to work out alright.


Get rows in a range of time I have a table with two columns that stores TIME type values. This is the CREATE statement:

CREATE TABLE `sdriving_turno` (
    `idturno` bigint(20) NOT NULL AUTO_INCREMENT,
    `idempresa` int(10) unsigned NOT NULL,
    `tipo` varchar(30) NOT NULL,
    `hora_inicio` time DEFAULT NULL,
    `hora_fin` time DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`idturno` , `idempresa`),
    KEY `fk_sdriving_turno_sdriving_empresa1_idx` (`idempresa`),
    CONSTRAINT `sdriving_turno_ibfk_1` FOREIGN KEY (`idempresa`)
        REFERENCES `sdriving_empresa` (`idempresa`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

I need to get the records in a range of TIME, lets say I need to get all the records BETWEEN 14:00 and 17:00 and for that I made this query (it has some JOINS but it's not relevant on this topic):

SELECT
    s.idregistros AS s__idregistros,
    s.maquinaemisorid AS s__maquinaemisorid,
    s.idoperador AS s__idoperador,
    s.idturno AS s__idturno,
    s.created_at AS s__created_at,
    s.updated_at AS s__updated_at,
    s2.idturno AS s2__idturno,
    s2.idempresa AS s2__idempresa,
    s2.tipo AS s2__tipo,
    s2.hora_inicio AS s2__hora_inicio,
    s2.hora_fin AS s2__hora_fin,
    s2.created_at AS s2__created_at,
    s2.updated_at AS s2__updated_at,
    s3.idmaquinaemisor AS s3__idmaquinaemisor,
    s3.idmaquina AS s3__idmaquina,
    s3.idemisor AS s3__idemisor,
    s3.created_at AS s3__created_at,
    s3.updated_at AS s3__updated_at,
    s4.idmaquina AS s4__idmaquina,
    s4.idempresa AS s4__idempresa,
    s4.patente AS s4__patente,
    s4.created_at AS s4__created_at,
    s4.updated_at AS s4__updated_at,
    s5.idoperador AS s5__idoperador,
    s5.idempresa AS s5__idempresa,
    s5.nombre AS s5__nombre,
    s5.rut AS s5__rut,
    s5.created_at AS s5__created_at,
    s5.updated_at AS s5__updated_at,
    s6.iddetalle_emisores AS s6__iddetalle_emisores,
    s6.idregistros AS s6__idregistros,
    s6.fecha_registro AS s6__fecha_registro,
    s6.dertalle_mensaje AS s6__dertalle_mensaje,
    s6.estado AS s6__estado,
    s6.revisado AS s6__revisado,
    s6.modo AS s6__modo,
    s6.remitente AS s6__remitente,
    s6.created_at AS s6__created_at,
    s6.updated_at AS s6__updated_at
FROM
    sdriving_registros_emisores s
        LEFT JOIN
    sdriving_turno s2 ON s.idturno = s2.idturno
        LEFT JOIN
    sdriving_maquina_emisor s3 ON s.maquinaemisorid = s3.idmaquinaemisor
        LEFT JOIN
    sdriving_maquina s4 ON s3.idmaquina = s4.idmaquina
        LEFT JOIN
    sdriving_operador s5 ON s.idoperador = s5.idoperador
        LEFT JOIN
    sdriving_detalle_emisores s6 ON s.idregistros = s6.idregistros
WHERE
    (s5.idempresa = '12'
        AND (s2.hora_inicio < '14:47'
        AND '14:47' > s2.hora_fin))

But when record has hora_inicio = 00:00:00 and hora_fin = 17:00:00 I get no results, why? What's wrong in my query?

Note 14:47 is the server data build through PHP if there is any way to get the records according to the current time in server just using SQL way let me know.

SELECT *
FROM `sdriving_turno`
WHERE hora_inicio < NOW() AND hora_fin < '17:00:00'

you could also do something like

SELECT *
FROM `sdriving_turno`
WHERE hora_fin BETWEEN NOW() AND '18:00:00'


Designing Database for Longitudinal Survey Data I'm trying to design a relational database based on a bunch of CSV data that I already have.  It's mostly for the purposes of organization and querying subsets, and I won't be adding any new records via transactions.  My data describes around 60 attributes of businesses, and it is longitudinal as they are surveyed each year (25 years).

My problem is that some attributes will see many duplicates over the life of a business, and others will see very few.  Here's a dumbed down example:

id, year, company_name, type, num_employees, total_sales,
056, 2000, papa johns, fast food, 11, 45000
056, 2001, papa johns, fast food, 11, 45557
056, 2002, papa johns, fast food, 14, 50000
056, 2003, papa johns, pizza, 17, 55000
056, 2004, papa johns, pizza, 17, 55456
063, 1998, pops barbershop, barber shops, 3, 15000
063, 1999, fresh cutz, barber shops, 3, 15023
063, 2000, fresh cutz, barber shops, 3, 15000

I'm looking at around 60 million unique ids, most with a good number of years as well.  If the database was in a form similar to this it would be massive and full of duplicates.

Any suggestions on a manner of organizing it?

According to Dr. E. F. Codd, the originator of the Relational Model of Data:

Duplicate values are permitted within columns of a relation, but duplicate rows are prohibited.1

Therefore, at the logical level —and taking into account the stated intention to create a relational database—, the column values duplicates presented in your sample table are not harmful.
If the relevant database has to contain large volumes of information, then it consequently must be managed as such; however, there are some aspects that can be optimized conceptually, logically, physically and pragmatically speaking.
Reorganization suggestions
Expository IDEF1X diagram
Having the referred sample table and data as a reference, it appears that the combination of the columns (year, company_name) is sufficient in ensuring row uniqueness (so it should be declared as a KEY, be it PRIMARY or ALTERNATE); therefore, the column labeled as id would be superfluous (assuming that it is a special addendum meant to contain surrogate key2 values, which lack business meaning, and, quite probably, would require handling an extra INDEX at the physical level).
Nevertheless, company_name and (company_?) type seem to be columns that hold data that is physically “heavy” (in terms of bytes, perhaps of type CHAR(n) or VARCHAR(n)), so I consider convenient to make some structural reorganizations, as depicted in the IDEF1X3 diagram contained in Figure 1:

Sample SQL-DDL logical-level design
Then, in accordance with said IDEF1X diagram, we can derive the following logical-level DDL layout:
-- As these are only illustrative examples,
-- you should determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient INDEX strategies.

CREATE TABLE company (
    company_id       INT      NOT NULL,
    name             CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT company_PK PRIMARY KEY (company_id),
    CONSTRAINT company_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE year ( -- “Look-up” table.
    year             SMALLINT NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT year_PK PRIMARY KEY (year)
);

CREATE TABLE company_type ( -- “Look-up” table.
    company_type_code CHAR(2)  NOT NULL,
    name              CHAR(30) NOT NULL,
    --
    CONSTRAINT company_type_PK PRIMARY KEY (company_type_code),
    CONSTRAINT company_type_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE organizational_register (
    company_id          INT           NOT NULL,
    year                SMALLINT      NOT NULL,
    company_type_code   CHAR(2)       NOT NULL,
    number_of_employees INT           NOT NULL,
    total_sales         PERTINENTTYPE NOT NULL
    created_datetime    DATETIME      NOT NULL,
    --
    CONSTRAINT organizational_register_PK                 PRIMARY KEY (company_id, year), -- Composite PRIMARY KEY.
    CONSTRAINT organizational_register_TO_company_FK      FOREIGN KEY (company_id)
        REFERENCES company (company_id),
    CONSTRAINT organizational_register_TO_year_FK         FOREIGN KEY (year)
        REFERENCES year    (year),
    CONSTRAINT organizational_register_TO_company_type_FK FOREIGN KEY (company_type_code)
        REFERENCES company (company_type_code)
);

Company
The company_id column, of type INT (or something similar, depending on the platform of choice), would decidedly be much “lighter” than company.name (helping to, e.g., speed up data retrieval) that is why I added and fixed it as the PRIMARY KEY (PK) of this table.
The name of each company would only be retained once in the company.name column, optimizing disk space usage if that is one of the (physical) concerns. Said column should be established as an ALTERNATE KEY (AK) by means of UNIQUE and NOT NULL constraints, which prevents the duplication of the same name value (thus, protecting data integrity and consistency).
Company Type
The company_type table would carry out a “look-up” role.
Its PK, called company_type_code, being of type CHAR(2) can retain values with business meaning and is far smaller than the column that encloses the full company_type.name, points that enhance data retrieval quickness and maintain readability (quite useful when, e.g., the end-users are interpreting result sets, the data is analyzed with a reporting tool, at the application program code debugging phase, etc.).
This table may hold the data shown below:
 +-—————————————————-+-————————————-+
 | company_type_code | name         |
 +-—————————————————-+-————————————-+
 | FF                | Fast food    |
 +-------------------+--------------+
 | PI                | Pizza        |
 +-------------------+--------------+
 | BS                | Barber shops |
 +-------------------+--------------+
Since the company_type.name values would be kept exclusively in this table, this rearrangement would as well permit exploiting disk space in a more advantageous manner. This column, being an AK, also demands UNIQUE and NOT NULL constraints.
There might well be some other columns that serve “look-up” usage in your database, so a comparable approach can be followed.
Year
I integrated a year table because I esteem it can be helpful to enforce the exclusive retention of data that is associated with years that have already been INSERTed in this table.
Organizational Register
All the the modifications discussed above will have the more substantial impacts on the processes that pertain to the organizational_register table.
Since, comparatively, it is very likely that this table is the one that will keep the larger quantity of rows, having two FK constraints of reduced size, i.e., company_id and company_type_code, the disk space consumption would be smaller.
Moreover, as the “reduced” FK values would very likely be involved in SELECT operations as search conditions or filters, they would accelerate data retrieval speed at the physical tier.
Optional columns
As you mentioned that the real table has more than 60 columns —which, in my experience, is considerably wide— and also that you will usually be querying subsets of them, it would be as well favourable to identify which columns are optional, and then move them to separate tables, grouping the ones that bear a conceptual-level association. This procedure would naturally yield several tables that are narrower (in the order of columns) and, being of course “lighter” compared with a wide one, behave generally faster with respect to retrieval and/or computing processes (e.g., the calculation of an average) at the physical level.
In this way, let us suppose that company_type_code and number_of_employees are optional columns (generally handled via NULL marks). Hence, the expository IDEF1X Diagram may evolve as shown in Figure 2:

And a the tentative logical arrangement would be modified as follows:
CREATE TABLE company (
    company_id       INT      NOT NULL,
    name             CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT company_PK PRIMARY KEY (company_id),
    CONSTRAINT company_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE year (
    year             SMALLINT NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT year_PK PRIMARY KEY (year)
);

CREATE TABLE sales (
    company_id       INT           NOT NULL,
    year             SMALLINT      NOT NULL,
    total_sales      PERTINENTTYPE NOT NULL
    column_x         FOO           NOT NULL,
    column_y         BAR           NOT NULL,
    created_datetime DATETIME      NOT NULL,
    --
    CONSTRAINT yearly_sales_PK     PRIMARY KEY (company_id, year), -- Composite PRIMARY KEY.
    CONSTRAINT sales_TO_company_FK FOREIGN KEY (company_id)
        REFERENCES company (company_id),
    CONSTRAINT sales_TO_year_FK    FOREIGN KEY (year)
        REFERENCES year    (year),
);

CREATE TABLE company_type (
    company_type_code CHAR(2)  NOT NULL,
    name              CHAR(30) NOT NULL,
    --
    CONSTRAINT company_type_PK PRIMARY KEY (company_type_code),
    CONSTRAINT company_type_AK UNIQUE      (name) -- ALTERNATE KEY.
);

CREATE TABLE organizational_register (
    company_id          INT      NOT NULL,
    year                SMALLINT NOT NULL,
    company_type_code   CHAR(2)  NOT NULL,
    number_of_employees INT      NOT NULL,
    created_datetime    DATETIME NOT NULL,
    --
    CONSTRAINT organizational_register_PK                 PRIMARY KEY (company_id, year), -- Composite PRIMARY KEY.
    CONSTRAINT organizational_register_TO_company_FK      FOREIGN KEY (company_id)
        REFERENCES company (company_id),
    CONSTRAINT organizational_register_TO_year_FK         FOREIGN KEY (year)
        REFERENCES year    (year),
    CONSTRAINT organizational_register_TO_company_type_FK FOREIGN KEY (company_type_code)
        REFERENCES company (company_type_code)
);

So, organizational_register would be split into two tables:

sales, that would hold the mandatory columns, and
organizational_register, that will incorporate the optional ones.

Using ACID Transactions
The discussed reorganizations would require multiple INSERTs in order to manipulate and retain all the relevant data; in this respect, all the pertinent operations might require to be executed within the same ACID TRANSACTION, so that they either succeed or fail as a single Unit of Work. Perhaps, some kind of batch process would be worth investigating and (or) testing.
JOINs and views
Then in order to derive all the columns of the table as presented in your question, you would have to create a SELECT statement that JOINs the necessary tables.
Certainly, defining one or more views based on the queries that include the JOINs discussed above would be very practical in specific cases, as you can SELECT information directly from the pertinent view with ease.
Summary
In general, my recommendations are:

setting up PK constraints on columns with data types and sizes that “behave” (physically) faster;
retaining the business meaning of (sometimes multicolumn) PKs and FKs when possible;
identifying what are the columns that can be moved to new tables, and place them accordingly based on conceptual-level associations; and
defining (physical) indexing strategies based on the retrieval tendencies that apply.

Endnotes
1 Codd, E. F. (Jan. 1990). Preface. In The Relational Model for Database Management: Version 2 (p. viii). Boston, MA, USA: Addison-Wesley.
2 E.g., a column with the IDENTITY property in Microsoft SQL Server, or with the AUTO_INCREMENT attribute in MySQL, etc.
3 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


How can I improve my query, using joins & improving performance? I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query.  I have tried various union and inner joins in an attempt to simplify it and my biggest problem is getting around the following error:

  Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

My query below solves this but it is not very efficient.

What am I trying to achieve?

When a media item has been deleted, I wish to soft delete all products where that media is associated providing that product does not have any other media files associated.

Schema

+-----------+----------+
| entity_id | media_id |
+-----------+----------+
|       192 |        1 |
|       193 |        1 |
|       193 |        2 |
+-----------+----------+

I have a composite Primary Key index on entity_id and media_id
I have a Foreign Key constraint on entity_id to product table
I have a Foreign Key constraint on media_id to product_media table

When I delete media #1 I would like to be able to update entity_id #192 to set deleted_at to the current timestamp.  I DO NOT want to update entity_id #193 because another media is associated to it.

UPDATE product
SET deleted_at = NOW()
WHERE id IN (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    AND entity_id NOT IN (
        SELECT entity_id
        FROM product_media
        GROUP BY entity_id HAVING COUNT(media_id) > 1
    )
)

As you can see there are two sub-queries, the fact I have 1 sub-query is somewhat of a concern but having two I know i'm doing something wrong but I cannot think for the life of my how to get around this?

You have an extra unnecessary subquery check, and your subquery seems to only return a rowset if there is a single value in it, so you may want to use = instead of in. You can achieve the same by doing this:

UPDATE product
  SET deleted_at = NOW()
WHERE id = (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    GROUP BY entity_id
    HAVING COUNT(media_id) = 1
)


Upgrading a big database inside a transaction Assuming a big database, where normal upgrade (structural changes and data upgrade) currently takes about 2-3 days (not a issue, is not done during production and about 1-2 times per year.

Currently, a backup is done, and if anything fails, backup is restored and upgrade scripts are checked for reason.
We are looking into the possibility of upgrading the database inside a transaction, rollbacking the changes if anything happens.

Are there any best-practices, reasons, knowledge and/or experience that would say if this is a good idea?

Thank you

Reverting to a valid backup is the most common and safest approach. You can reduce risk and likelihood of needing to restore from backup by versioning the changes.

For instance, say your upgrade process involves 50 steps that have some sequence/order requirements. Any step or small group of steps that can survive a reboot after they are applied should safely rev a version number in your DB so you know exactly what the last successful upgrade step was. You can then resume the process instead of having to restart.

Add this to putting each step or collection of steps in transactions will help ensure your upgrade steps comply with ACID requirements and make your upgrade process more robust. We do this with some of our major upgrades and rarely ever have to restore when something breaks in the middle unless there's a bug in the script of one of the upgrade steps.


postgresql 9.3: two instances on same port and two different ip adresses don't work i'm setting up multiple instances of postgres 9.3 on the same machine (centos 7). I have 2 virtual ip addresses, one instance is bound to first address and second is bound to second one. Same configuration is working on postgres 8.4 and very old red hat server.

I have configured 2 .service files in /usr/lib/systemd/system and i have done the setup initdb servicename commands.

the problem is: if i starts first service it goes well, but the second says that /tmp/.s.PGSQL.5432.lock file already exists.

in previuos db and os version i could set two different /var/run path in order to manage two instances,but now i cannot do it beacause i don't find any setting to write lock file where i want.

obviously if i change listening port on second instance all works well beacuse it create another lock file with different name,but i would to mantian same configuration. I don't know if in new version they delete the possibility to have 2 instances on same port and different ips but it seems strange to me.

how can i do?

thanks

You must also set separate unix_socket_directories.


Can't restore SQL Server 2016 backup to SQL Server 2014 We are having an issue at trying to restore a database:

  System.Data.SqlClient.SqlError: The database was backed up on a server
  running version 13.00.4001. That version is incompatible with this
  server, which is running version 12.00.2269. Either restore the
  database on a server that supports the backup, or use a backup that is
  compatible with this server.

Install sql server 2016 developer edition, its free for dev purpose only. Restore the database on 2016 instance and then use this method to move your data back to older version.

Note: be mindful of enterprise features vs standard edition feature incompatiblity and limitations.