Best way to delete very large recordset in Oracle

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100)
)
partition   by range  (date_too_old)
(
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('B', date '2008-04-14', date '2000-05-17',
 'B and 2000-05-17 is older than 10 yrs, must be deleted');

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('B', date '1999-09-19', date '2004-02-12',
 'B and 2004-02-12 is younger than 10 yrs, must be kept');

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('A', date '2000-06-16', date '2010-01-01',
 'A and 2000-06-16 is older than 3 yrs, must be deleted');

insert into tq84_partitioned_table (status, date_a, date_b, data) values
('A', date '2009-06-09', date '1999-08-28',
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;


What is the logic behind ISNUMERIC for certain special characters?

The detailed behaviours of ISNUMERIC are not documented, and probably not fully known to anyone without source code access. That said, it may be that interpretation depends on the Unicode categorization (numeric or not). Equally, the weird cases you mention may be bugs that are preserved for backwards compatibility. Yes I know that sounds crazy, but it does happen.

As you are using SQL Server 2012, there is no need to use ISNUMERIC. Use TRY_CONVERT or the synonymous TRY_CAST instead to check if a string is convertible to a given type. Where they provide adequate functionality, these are preferable to TRY_PARSE, because the latter involves more expensive processing via CLR integration.


Is this a good way to model course assignments?

I like the gist of what you're doing. I think the entity names are a bit confusing though, and I would put some fields in different places. From my understanding of the system you're trying to build, here's a diagram I recommend:

In this model, Assignments have one AssignmentOccurrence for every time it occurs. If an Assignment occurs once, it would have one AssignmentOccurrence.

Take the values of the AssignmentOccurrence with a grain of salt, there's not enough information for me to know what data needs to be stored in there.


MySQL: Select two values of two columns and compared their values exchanging them each other

I guess, from the description, that you first need all rows with rows (x,y) where no other row (y,x) exists. Then select a random row from them:

SELECT r.leader, r.subscriber
FROM relations AS r
WHERE NOT EXISTS
      ( SELECT *
        FROM relations AS r2
        WHERE r.subscriber = r2.leader
          AND r.leader = r2.subscriber     -- or perhaps without this
      )
ORDER BY RAND()
LIMIT 1 ;

It would be useful to have two indexes, on (leader, subscriber) and on (subscriber, leader). If the table is very big and performance is still not good, you may have to change how the random row is selected. The ORDER BY RAND LIMIT 1 solution works ok in small tables but it's quite slow in big tables as it has to sort all the matching rows before it chooses one.


You need to update the defaults column by using an insert trigger on this table. The only problem with it would be that your trigger will fail on multirow-insert.
There is a correction in the WHERE clause you are using. Following is the code you should use:

UPDATE FYPDB.dbo.Promotions
SET defaults = 'N'
WHERE ID NOT IN (SELECT ID FROM Inserted) AND membershipType IN (SELECT membershipType FROM Inserted) AND EXISTS (SELECT * FROM Inserted WHERE defaults = 'Y');
UPDATE FYPDB.dbo.Promotions
SET defaults = 'Y'
WHERE ID IN (SELECT ID FROM Inserted) AND NOT EXISTS (SELECT * FROM FYPDB.dbo.Promotions p WHERE p.defaults = 'Y' and p.membershipType in (SELECT membershipType FROM Inserted));


Does avg_query for PgBouncer include update and inserts?

Yes - pgbouncer does not know what type a query is, it just passes it through to the backend. Therefor, avg_query is across all queries, including both INSERT, UPDATE and DDL statements.


What happens in Microsoft SQL Server 2012 .ldf when the database log file if full?

If the log is full, your database will fail any transaction that requires something written to the log (insert,update,delete, schema change, etc.).

From your screenshot, are you really planning to limit it to 15MB? How big is the database? How big is the largest table in the database? You also mention that it is growing very large. How do you define very large?

Generally speaking, you need your log file to be at least as big as your largest index, so that if you rebuild it, it won't fail. If the log has grown, then that's usually because you need a bigger log file. The only time you'd want to shrink the log file is if you know that a 1 off operation happened that will never happen again (e.g. some sort of data load/export). If your log file is growing significantly larger than your data files, you may want to check your recovery model - if it's full then you need to be taking log backups.


Some book suggestions for performance tuning

I like Kalen Delaney's internals book:

Microsoft SQL Server 2008 Internals

I also like the Brent Ozar / Christian Bolton book:

Professional SQL Server 2008 Internals and Troubleshooting

For T-SQL tuning, you can't get much a better T-SQL wizard than Itzik Ben-Gan:

Inside Microsoft SQL Server 2008: T-SQL Programming
Inside Microsoft SQL Server 2008: T-SQL Querying

And query tuning overall, Grant Fritchey and Sajal Dam:

SQL Server 2008 Query Performance Tuning Distilled

There are also some interesting solutions and opinions presented in various chapters in the two MVP Deep Dives titles (from which all proceeds go to two charities, War Child and Operation Smile):

SQL Server MVP Deep Dives Volume 1 (War Child)
SQL Server MVP Deep Dives Volume 2 (Operation Smile)

And a couple of the Red Gate titles @Kev left out, but which are both excellent:

Inside the SQL Server Query Optimizer (Ben Nevarez)
Troubleshooting SQL Server: A Guide for the Accidental DBA (Jonathan Kehayias / Ted Kreuger)


Is there an easy way to update connection strings to a database moved to a different instance

Is there an easy way to update the connection string for each of the datasets?

Connection strings are what client software use to figure out how to connect to the data store. So I would say outside of a programmatic update (search and replace) no there is no straightforward or native way to do this.

  Each customer that purchases the software would have a different connection string. Can you point me to some documentation that would explain how to handle this scenario?

This is really more of a question of software install/deployment/configuration, but typically setting the connection string would be done through the installation process.


A somewhat complicated way could be
WITH new_users AS (
   UPDATE users SET new = TRUE
   WHERE NOT new RETURNING /* all columns except "new" */
)
 SELECT /* all columns except "new" */
 FROM new_users
UNION
 SELECT /* all columns except "new" */
 FROM users;

The UNION will eliminate duplicates.


How to get EstimatedRows values from SQL Server execution plan nodes

The reason is a small typo but I agree that Estimatedrows would seem like the way to go.

In the execution plan's XML it is called Estimaterows instead of the expected EstimatedRows.

An example:

EstimateRows="12"

This should work:

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,total_elapsed_time =   SUM(total_elapsed_time)
            ,execution_count    =   SUM(execution_count)
      FROM  sys.dm_exec_query_stats
     GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle, qp.query_plan, ph.total_elapsed_time, ph.execution_count
      FROM  PlanHandles ph
            OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT  p.total_elapsed_time
        ,p.execution_count
        ,p.plan_handle
        ,p.query_plan
        ,NodeId                 =   s.i.value(N'@NodeId', N'INT')
        ,EstimatedRowsRead      =   s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT')
        ,EstimatedRows          =   s.i.value(N'(@EstimateRows)[1]', N'FLOAT')
  FROM  Plans p
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') q(n)
        CROSS APPLY n.nodes('.//RelOp[IndexScan and IndexScan/Object[@Schema!="[sys]"]]') s(i)
 WHERE  s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT') > 1.0;

Result example

NodeId  EstimatedRowsRead   EstimatedRows
3         530                1
4         1,93078            1
9         12                 12
8         12                 12


How can I get the latest five scores per user per subject?

Do you want the ones with less than 5 scores in mix.  If you want only the 5 scores one, you can add a having to your query:

    SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
    FROM  (
       SELECT *
             ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
      FROM mg.scores
      WHERE score IS NOT NULL) as score
       ) AS x
    WHERE  x.rn <= 5
    GROUP  BY user_id,dept
    HAVING count(*) > 4;


Designing a scenario where a primary key could be of two types

Also search for super type/subtype here and on the SO.


What would cause "SELECT permission denied" for an object?

These symptoms suggest the x schema in DB01 is not owned by dbo, resulting in a broken ownership chain. Permissions are then required on indirectly referenced objects.

To maintain an unbroken chain in DB01 for the query that joins tables in the x and dbo schemas, the x schema must also be owned by dbo. This can be changed with ALTER AUTHORIZATION:

USE DB01;
ALTER AUTHORIZATION ON SCHEMA::x TO dbo;


Data files half empty in SQL Server. Is that a problem?

No. Unless you need the space for something else it is not a problem at all. The unused space won't cause any extra CPU or memory use, empty pages won't consume space in backups, etc... As data grows in future it will use the empty allocated space instead of requesting more from the filesystem.
In fact shrinking can cause issues such as extra fragmentation, that you may end up spending time rebuilding indexes to resolve, so should be avoided unless needed.


SQL Maintenance Tasks on Azure [Ola Hallengren Scripts]

As mentioned on that article, you are expected to create Hallengren schema and its related database objects on each database on the pool, instead of using the Master database to create the schema and objects. Steps 1 to 4 on that article tell you how to perform that setup using jobs.

After that the article shows you how to use Hallengren scripts.


There are two differing "best practices" for using SQL Server Management Studio when managing SQL Server.

Use the same version of SQL Server Management Studio as that of the server you are managing.  In this case that means you should use SSMS that comes with SQL Server 2014; one source would be ENU\x64\SQLManagementStudio_x64_ENU.exe.
Use the latest-and-greatest version of SQL Server Management Studio.  As of now (2018-07-05), that would be SSMS 17.8.1, which is available free from Microsoft here.


Consequences of cascade drop on sequences/functions/views

I am quite certain that no table would be dropped if you remove views, sequences and functions with the CASCADE option.

But parts of a table may vanish:

Indexes, triggers, rules, column defaults, check constraints and in PostgreSQL v12 generated columns may vanish if you drop the functions and operators used in them.
Column defaults will vanish with the sequences used in them.

Dropping views should not affect tables at all.

Disclaimer: I have probably forgotten one or two cases.

Why don't you just selectively dump the parts of the database that you need with the -l and -L options of pg_restore?


Kindly try the following code :

SELECT

            catalog.code,
            catalog.supplier,
            catalog.created_at

       FROM catalog
        WHERE MONTH(catalog.created_at) = month(NOW()- INTERVAL 1 MONTH)
        AND YEAR(catalog.created_at) = year(NOW());

Tried and tested in SQLFiddle: http://sqlfiddle.com/#!9/2f8d3/1/0
Hope this helps !!
Regards.


Oracle pagination with order by field not unique performance

It's also wrong, because now you are getting 500 rows (could be ANY 500 rows) and then sorting them.  If L_DATE is not sufficient to give a deterministic answer, you do need to add more columns.  The most obvious choice would be a primary key column:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' ORDER BY L_DATE, SOME_PK_COL
    ) XX
    WHERE ROWNUM <= 500
) XXX
WHERE RN > 0;

...so that in the absence of good information in L_DATE (or whatever column is nominated by the user), you will be sorting on something deterministic.

Also: If you do select ... from table where rownum <= 10 then the 10 rows you get back is not deterministic over time.  There is no predefined order to the rows.


Why doesn't row data show up in SQL Developer?

I see no COMMIT up there in your code.
By default SQL*Plus autocommit is off.
Data changes are not visible to other sessions until they are not commited.


declare @t table (LEAD_CO_MNE int identity, CURRENCY varchar(10), AMOUNT_FCY int, AMOUNT_LCY int);
insert into @t (CURRENCY, AMOUNT_FCY, AMOUNT_LCY)
values ('KHR', 10, 20), ('KHR', -10, -20), ('KHR', 30, 40), ('other', 1, 2);

with cte as
(
SELECT [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END Amount --I want to split into type of amount debit or crdit
     ,CURRENCY
from @t
)

select [LEAD_CO_MNE],
       CURRENCY,
       case when Amount > 0 then Amount else 0 end as Credit,
       case when Amount < 0 then Amount else 0 end as Debit
from cte;


How to suppress specific warnings in mysql error log when ignoring a table to replicate


ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal

You don't have the create privileges on the object being invalidated. But generally it is a very bad idea to invalidate objects in the sys schema. This will also invalidate all objects that reference them, so you might end up with lots of invalidated objects.


Oracle Top CPU Consuming SQL is "select longdbcs from javasnm$ where short = :1"

From what I remember about javasnm$, it is a table that stores long names of java objects, as oracle objects can only be up to 30 characters (stored in field SHORT). Most java objects are over 30 characters so it constantly has to do these look-ups.

The SQL we have is:

select longname from javasnm$ where short = :1

We had similar performance concerns about the high execution counts of similar SQL when apps made use of the JVM built in to oracle, but nowadays most of our java apps don't use the built-in JVM and that issue isn't there anymore.

Check with your third-party vendor if they can move it off the database and use something like connection pooling e.g. Weblogic to interact with oracle.


Best practice for sharing data over differenet mysql/mariadb databases

If the "common" tables on the secondary "app instances" are being accessed read only, then your easiest option will be doing a traditional master slave(s) configuration with a replicate_ignore_table = ... line put into all slaves for all tables except the "common" ones.

If you need to have identical behavior between all app instances (meaning you're not using app logic to isolate writes to the master only), then I would seriously considering using a galera cluster product of some sort.


Should I stop my PostgreSQL server before reboot?

In theory the system should send it a stop signal and then wait for it to shutdown cleanly. But, I've seen this fail, with the result that the PostgreSQL had to go through crash recovery and any unlogged tables were truncated and had to be regenerated.  So I exercise an abundance of caution and manually shut PostgreSQL down before rebooting.


Which foreign keys do I use if they are repeated

If you go for the Option 1, how do you Identify the Affiliate?
and that's why I think Option 2 is better, unless I am missing something


Database for Orders/Inquiries with "custom" Product Data

Yes, If you create an extra table for storing ordered product information that would be helpful to you.

Make sure you insert all entries in orderedproduct table with reference to order id so it would be easily selected as and when we require all order details with product information.


how to override a table's cardinality or statistics histogram

You can use the UPDATE STATISTICS  WITH ROWCOUNT and PAGECOUNT option

See here some examples :
 https://www.mssqltips.com/sqlservertip/2870/using-with-rowcount-and-pagecount-option-of-sql-server-update-statistics/
or
https://sqlsunday.com/2016/02/27/update-statistics-with-rowcount/


Total number of matches played by player

First this is a horrible idea,

team_a int REFERENCES players (pid),

This will make everyone hate you. If there is no difference between a team and a player -- use the same name. Pick or have a difference.

  I want to make a query so i can also be able to count the total matches played by player id.

You need only query your matches table.

SELECT team, count(*)
FROM (
  SELECT team_a AS team
  FROM matches
  UNION ALL
    SELECT team_b AS team
    FROM matches
) AS t
GROUP BY team;

That is to say, these relationships are bidirectional but the schema isn't. If team foo plays team bar you can write that two separate ways. That is to say both of these are valid,

TeamA | TeamB | Wuteva
foo   | bar   | baz
bar   | foo   | quz

Update

In the future, try to put all of the things you want to do in the first question, but I've updated this to include the matches won

SELECT pid, count(*) AS played, count(is_won) FILTER (WHERE is_won)
FROM (
  SELECT pid_a AS pid, pid_a = winner AS is_won
  FROM matches
  UNION ALL
    SELECT pid_b AS pid, pid_b = winner AS is_won
    FROM matches
) AS t
GROUP BY pid;

As a separate note, good job on changing the names.


When exactly are multiple users unable to simultaneously run a stored procedure with a temp table?

This applies to local temp tables.

The difference between named and unnamed constraints is this:

CREATE TABLE #t1 (c1 INT PRIMARY KEY CLUSTERED)

CREATE TABLE #t2 (c1 INT,
                     CONSTRAINT pk_c1 PRIMARY KEY  CLUSTERED(c1) )

Letting the system name constraints makes it extremely unlikely that there will be a collision. In this example, if you open two windows in SSMS, you'll be able
to create #t1 in both, but not #t2.

Global temporary tables are shared by all users, so you have to handle things differently. They don't get 'destroyed' until the last session is done using them, so you need to make sure that when users access them, they can only access their data. This is sometimes done by SPID, other times by a hash value. It depends on how the global temp table is used.

Typically for global temp tables, stored procedures will check to see if they exist, and then only create them if the OBJECT_ID() is NULL.

Multiple users means multiple sessions. The login name has nothing to do with it. If George runs sp_something @i = 1 and Gina runs sp_something @i = 2, it doesn't matter if they're both logged in as User1, they'll have different SPIDs.


PostgreSQL failsafe start after disk crash

Among the options you give, the first is the only feasible one.  But neither one of them is very good.

The only good solutions is to prevent it from happening.  If your disks randomly eat your data, maybe next time it will be the postgres binaries that get eaten, or the kernel itself, or the init script you wrote to re-init the database when it gets corrupted, or the dump files which contains the schema you want to use to reload.

If your disks are honest and true but your database got corrupted anyway because you were running with fsync=off, then don't do that.  Run with synchronous_commit=off instead, if you must.  That risks losing some transactions, but doesn't corrupt your database.


Why does MySQL allow HAVING to use SELECT aliases?

Well when you have a question of this sort the best source of information IMHO is MySQL documentation. Now to the point. This is the behavior of MySql extension to GROUP BY which is enabled by default.

  MySQL Extensions to GROUP
  BY
  MySQL extends this behavior to permit the use of an alias in the
  HAVING clause for the aggregated column

If you want standard behavior you can disable this extension with sql_mode ONLY_FULL_GROUP_BY

SET [SESSION | GLOBAL] sql_mode = ONLY_FULL_GROUP_BY;

If you try to execute the above-mentioned query in ONLY_FULL_GROUP_BY sql_mode you'll get the following error message:

  Non-grouping field 'Amount' is used in HAVING clause: SELECT
  YEAR(orderdate), COUNT(*) as Amount FROM Orders GROUP BY
  YEAR(orderdate) HAVING Amount > 1

Here is SQLFiddle demo

Therefore it's up to you how to configure and use your instance of MySQL.


your timestamp and logoff_time columns contained time part too, please use trunc if you do not wish to consider the time part and then use the distinct.


rename datafile when file does not exists

You wrote:

  After this rename process , I will try to restore database from rman
  backup. But this backup is not piece by piece copy.

Then this is not the method for doing this. In order to use the ALTER DATABASE RENAME FILE, the file have to already exist at the new location. Instead of this, you can (and should) handle this in RMAN:

sqlplus / as sysdba
startup mount
exit

Which you have already done, then:

rman target /

And run the following:

run
{
  set newname for datafile '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
  set newname for datafile '+NEWDATA/XX/XX02.dbf' to 'M:\oracle\datafiles\XX\XX02.dbf';
  set newname for datafile '+NEWDATA/XX/XX03.dbf' to 'M:\oracle\datafiles\XX\XX03.dbf';
  # ..., list all datafiles this way
  # alternatively you can use the datafile number: set newname for datafile 1 to '.../...dbf';

  restore database;
  switch datafile all;
  recover database;
}


MySQL 8+

Using MySQL 8, you can write a Recursive Common Table Expression.

Recursive Common Table Expression Examples

The example under Hierarchical Data Traversal is pretty much what you're looking for, what you call parent_id they call manager_id

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
  FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  -- Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      -- John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT *
FROM employee_paths
ORDER BY path;

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

The docs even explain it,

  How the CTE works:

  The nonrecursive SELECT produces the row for the CEO (the row with a
  NULL manager ID).
  The path column is widened to CHAR(200) to ensure that there is room
  for the longer path values produced by the recursive SELECT.
  Each row produced by the recursive SELECT finds all employees who
  report directly to an employee produced by a previous row. For each
  such employee, the row includes the employee ID and name, and the
  employee management chain. The chain is the manager's chain, with the
  employee ID added to the end.
  Recursion ends when employees have no others who report to them.

They further have an example wuth specific employee or employees, for more information see "Hierarchical Data Traversal" with MySQL 8


Connections remain active even after terminating the session in Postgres

If you had permission to kill them with pg_cancel_backend, but doing so did not work, then they must be in a state where they are not responding to interrupts.  That is probably a bug, either in PostgreSQL itself, or in your code, or in pldbgapi.  What does wait_event_type and wait_event say?  Can you attach to them with gdb and get backtraces?


SUM() 3 columns with alias into a fourth one

You cannot refer to an alias defined in the same SELECT list. There are various workarounds. Assuming your query is something like this:

select nome,
       -- various complex expression / subqueries
       <complex_expression_or_subquery>
           as Laudos_Expedidos,
       <complex_expression_or_subquery>
           as Laudos_Expedidos2
from <table_name> ;

Duplicate code. As this is the least attractive solution, for various reasons, I won't add a code sample.
a derived table or a CTE allows to use the aliases as they are already defined in a previous level:

with cte as
( select nome,
         -- various complex expression / subqueries
         <complex_expression_or_subquery>
             as Laudos_Expedidos,
         <complex_expression_or_subquery>
             as Laudos_Expedidos2
  from <table_name>
)
select c.*,
       c.Laudos_Expedidos + c.Laudos_Expedidos2
           as Total_Laudos_Expedidos
from cte as c ;

Use CROSS / OUTER APPLY allows also to refer to previously defined aliases. This offers the possibility of using multiple outer/cross apply subqueries, if you want to use the aliases of the first apply in further calculations. (With the derived/CTE solution, that is also possible but the code usually looks more complicated):

select t.nome,
       f.*,
       g.Total_Laudos_Expedidos,
       f.Laudos_Expedidos / g.Total_Laudos_Expedidos
           as Percent_Expedidos
from <table_name> as t
  outer apply
    ( select
          -- various complex expression / subqueries
          <complex_expression_or_subquery>
              as Laudos_Expedidos,
          <complex_expression_or_subquery>
              as Laudos_Expedidos2
    ) as f
  outer apply
    ( select
          f.Laudos_Expedidos + f.Laudos_Expedidos2
              as Total_Laudos_Expedidos
    ) as g ;


It depends on the number of articles you expect to have. If you have indices on the columns you are filtering, it will be pretty fast. Joining is very fast and will surely not be the problem.

But your WHERE clause is very intensive. Because you are basically scanning all rows in the table and therefore loading it all from the disk to compare. This cannot be avoided in your case, but it will be the crucial part. Nevertheless, if you aren't going past 100'000 articles I wouldn't be too concerned. Still - test it, if you want to be sure!

However, your use case seems to be indexing document (articles) and get the top x of them, based on a search query. A way to be faster and more efficient is, to create vectors from documents which contain for each word its occurrence. Your search query will be transformed into one too, and then it's all about comparing vectors. (simplified)

You don't have to implement that of course, there are libraries for document indexing and searching. Maybe you want to have a look at lucene (or maybe you can even use solr which should do everything for you). Just to give you some ideas, with those tools you can build your own little 'google'. Lucene would of course build its own (noSql) database / index. So you would maybe need to separate this functionality from the rest of your application (I don't know the context).


Redis docker container not coming up with custom config file

It turns out that in my custom config file I had the daemonize option enabled and after disabling that my container is working fine.


Incremental backup between now and backup taken one week ago

You cannot go back in time and get an incremental backup as of a certain time.  If the database is in full recovery mode, you can give them the transaction log backups (if you have them) that go from the backup they restored to the time they want to restore to.

However, if they have been using the database, they will have to restore the backup again and leave it in recovery mode so that the transaction logs can be restored into it.  Once a backup is restored and the database is taken out of recovery mode so it can be used, no more backups (differential, incremental, or log) can be restored to it.

You might also check to see what the developer actually wants as I suspect they used the wrong terminology in the request.  Referencing the transformation, etc., it seems that maybe they are expecting to get a backup that will only restore the new records from the time period.  No matter what backup you give them, they are going to get the entire database (unless you are just going to restore partitions, but based on the question I'm guessing that's not really a good option).


too many connection error mysql: how to calculate max_connections

Before deciding on value for max_connections I suggest that you look at WHY it reached the max_connection condition. A bunch of sleeping/idle connection due to loose coding is very common cause. Check if you have proper value for wait_timeout and adjust if this is your symptom.

Besides that, I would check for INSERT/UPDATE query that holds table locks for too long, as well as slow queries that get piled up once in a while.


create a related row if no related row exists

Not sure what is name = 'george' is doing but assume it's part of the filtering in WHERE clause.

INSERT INTO many_table (id, name)
SELECT o.id, o.name
FROM one_table o
LEFT JOIN many_table m ON m.related_id = o.id
WHERE o.name = 'george' AND m.id IS NULL


copy an exported dump file to a target DB instance - LOG

You can see a list of the files in the DATA_PUMP_DIR directory by running

 select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by filename;

Additionally, you can read the contents of the file by using

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','filename.log'));

(Credit for the last script goes to: https://www.experts-exchange.com/questions/28961936/open-a-file-on-a-remote-server-amazon-rds-oracle.html)

I hope it helps


Is it possible to install just the mongo Shell?

You can use HomeBrew, see the official MongoDB HomeBrew page.

Just add the tap:

brew tap mongodb/brew

And then after adding the tap from above, you can then install the Mongo shell with:

brew install mongodb-community-shell


SQL server Profiler can be used any time?

I highly recommend you use Extended Event instead of SQL Profiler trace.  Extended Events can be more targeted (regarding what you collect) and less impact on server resource (Measuring “Observer Overhead” of SQL Trace vs. Extended Events). Few resources below which should give you a good start.

SQL SERVER – Introduction to Extended Events – Finding Long Running Queries
Finding One Problem Query With Extended Events
Three Ways to Identify Long Running Queries - PASS


Is this a bug?

No it is not a bug. Its by design. They are kept for troubleshooting and supportability
purposes.

From the SQL_Server_2014_In-Memory_OLTP White_Paper

  Database administrators do not need to maintain the files that are generated by native compilation. SQL
  Server automatically removes generated files that are no longer needed, for example on table and
  stored procedure deletion, and on drop database, but also on server or database restart.

I tried to repro your scenario on SQL Server 2014 + RTM + (Build12.0.2000.8) - Dev Edition server by creating a test memory optimized table and checking the dll loaded using

     SELECT name, description FROM sys.dm_os_loaded_modules
WHERE description = 'XTP Native DLL'

After I dropped my table, the dll still appears in the output of above select statement and the files are still in the folder and after restart they are still there too.

From Books Online -

  No user interaction is needed to manage these files (.c, .obj, .xml, .pdb., .dll). SQL Server will create and remove the files as necessary.

SO I guess, we just have to follow what Microsoft says - SQL server will manage them for us :-)

ONLY FOR EDUCATIONAL PURPOSE :

I managed to clean up the old files by

Issuing a manual CHECKPOINT on the database.
Taking database offline and then bringing it online.

Ideally, you should not be restarting the server instance, just manual checkpoint and offline/online of the database will clear the files.

e.g. Repro :

USE master
GO
create database db1
GO
ALTER DATABASE db1 ADD FILEGROUP db1_mod CONTAINS memory_optimized_data
GO
-- adapt filename as needed
ALTER DATABASE db1 ADD FILE (name='db1_mod', filename='D:\SQLServer2014\MSSQL12.SQL2014\MSSQL\DATA\db1_mod') -- change here as per your need !!
 TO FILEGROUP db1_mod
GO
USE db1
GO
CREATE TABLE dbo.t1
(c1 int not null primary key nonclustered,
c2 int)
WITH (MEMORY_OPTIMIZED=ON)
GO

--- now check if the dll is loaded or not

SELECT name, description FROM sys.dm_os_loaded_modules
WHERE description = 'XTP Native DLL'

--- now drop table and do a manual checkpoint

use db1;
drop table dbo.t1;
checkpoint

Still the module is loaded in memory (even server restart will load the module sometimes)

The (.c, .obj, .xml, .pdb., .dll) are still present in the folder :

Now take the database offline and then bring it online - the (.c, .obj, .xml, .pdb., .dll) are all gone ...


How to find the time taken to execute a script

Try to use sys.dm_exec_query_stats. Find your query, if it's cached:

set transaction isolation level read uncommitted

select
    top 100
    creation_time,
    last_execution_time,
    execution_count,
    qs.total_elapsed_time/1000 as TotDuration,
    convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
    case
        when sql_handle IS NULL then ' '
        else(substring(st.text,(qs.statement_start_offset+2)/2,(
            case
                when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
                else qs.statement_end_offset
            end - qs.statement_start_offset)/2  ))
    end as query_text
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
order by AvgDur desc


Improving sql query execution time for 3 billion rows table

There is a lot of information not included in your question, which makes providing complete answers rather difficult. However, using just what you've shared:
Is this the point where clustering would solve my issues?
Not likely. Clustering offers a lot of advantages, but it does not seem to be the correct solution for what you're trying to do. With a million rows being added daily, your primary system needs to be optimized for writes. When talking about reporting, you could probably do with a system that is optimized for reads.
Is it unusual that a table this size still runs on a single SQL node?
The level of unusualness here depends greatly on the needs (and expectations) of the business. I would hope that there is a hot spare or replicated instance somewhere that is ready to jump in should the main server suffer a failure. At 11.5 record insertions per second, there's not much room for downtime.
Is it still possible to run queries in such large table in milliseconds?
Given enough hardware, I don't see why not. However, very rarely are people given access to the full computing power of an entire data centre.

Generally when I have had to work with situations like this, I've tried to keep things simple and ask specific questions regarding the types of reports that people are trying to collect from the system. If there are common patterns, then flattened historical tables have saved a great deal of time for everyone. Why query the same data from 2003 a thousand times a week when you can summarize it in a manner that makes long-view reports faster and just as accurate, after all?
However, one of the main ways I've solved this sort of issue — usually for universities that are collecting millions of records daily from seismometers and weather stations spread throughout the country and across the Pacific — is to "cheat" and have a replicated instance that updates its source tables only once or twice a day. This allows for a system that can be optimized for reads with a myriad of indexes, leaving the main server(s) optimized for writes with fewer indexes (if any at all).
For common reports, patterns in the data are found and put into summary tables on an hourly/nightly schedule, allowing for common reports to be quick. Ad hoc or more specific queries can also be run against the replicated instance without worrying about affecting performance on the main system(s). So long as the reports being generated do not need to be "real-time", this method generally works and can be done on a reasonable budget, which management types tend to appreciate.
Mind you, do not take this answer as anything beyond something to think about. As mentioned at the beginning, there is a lot of information that was not included in the original question, such as the target audience for the reports, what other tasks the database(s) are being used for, how often historical data is queried vs. current data, and the like. This is just an option based on past experiences when I have been asked to solve a similar-sounding problem.


Is a single-table relational database similar to a simple NoSQL database?

No - a single table relational database is more akin to a spreadsheet than a NoSQL database. NoSQL is not about having no schema, it's more about having a flexible schema!

Wikipedia puts it very well when it says NoSQL:

  provides a mechanism for storage and retrieval of data that is modeled
  in means other than the tabular relations used in relational
  databases.

The key (pardon the pun) word here is modeled - i.e. there is a model, it's just not a relational one!

As an aside, it's well worth reading the entire article - it's detailed and well written!

IBM says:

  It's important to emphasize that the "No" in "NoSQL" is an
  abbreviation for "not only" and not the actual word "No." This
  distinction is important not only because many NoSQL databases support
  SQL like queries, but because in a world of microservices and polyglot
  persistence, NoSQL and relational databases are now commonly used
  together in a single application.

And indeed many NoSQL systems (it's difficult to say "vendors" since many of these systems are Open Source) are scrambling (or have scrambled) to put SQL interfaces on top of their systems because so many programmers are familiar with that paradigm (and it's one which has worked well over the years).

One could write entire essays (as indeed I have in College) on NoSQL vs. the classic RDBMS, and I'll go no further down this avenue other than to urge you to read around the topic.

It's fascinating to watch the evolution of the database ecosphere - new SSD chip technology, and for me, the most interesting development is the recent focus on LSM (Log Structured Merge tree) which is widely used in NoSQL systems and also NewSQL ones. IMHO, these NewSQL systems are the way of the future and I expect to see Oracle, SQL Server &c. to adopt many of their paradigms (or embrace and extend them to coin a phrase).

Again, it's a complex technology about which reams could be written, but from here:

  At the heart of the LSM algorithm is a rolling merge process:.... LSM trees cascade data over time from smaller, higher performing (but more expensive) stores to larger less performant (and less expensive) stores.

Instead of updating in place, data is "chunked" and from here:

  Once the in-memory tree reaches a threshold size, a new in-memory tree
  is created and the old tree synced to disk. Once written to disk,
  trees are read-only, though they are merged in the background with
  other on-disk trees to reduce the cost of reads.

You mention SQLite in your question - it may surprise you to learn (it did me) that there is a Key-Value "version" of SQLite that was developed between 2012 and 2014. From here:

  Lessons learned from SQLite4 have been folded into SQLite3 which
  continues to be actively maintained and developed. This repository
  exists as an historical record. There are no plans at this time to
  resume development of SQLite4.

I'll leave the last word to Brian Aker, former Director of Architecture for MySQL who presents his amusing take on NoSQL which is available on YouTube here. I think his views can be best summed up by a cartoon which he presented during that talk:

+1 for an interesting question (which I hope won't be closed!) and welcome to the forum!


Optimizing the query with 2 distinct counts in select

There is a many-to-many relationship between messages and order_items through products, as a product can appear in many order_items and many messages and also a message (or an order_item) can have many products.

This results in the query producing a variation of a CROSS JOIN, and thus the need for using DISTINCT in the counts and consequently the poor performance.

I'd suggest you try adding composite indexes on both tables, on (product_id, order_item_id), (product_id, message_id) and see if they are used.

Another approach would be to rewrite the query so it doesn't use DISTINCT:

WITH
  cm AS
    ( SELECT COUNT(*) AS count_messages
      FROM messages AS m
      WHERE EXISTS
            ( SELECT 1
              FROM order_items AS o
              WHERE m.product_id = o.product_id
            )
    ),
  co AS
    ( SELECT COUNT(*) AS count_order_items
      FROM order_items AS o
      WHERE EXISTS
            ( SELECT 1
              FROM messages AS m
              WHERE m.product_id = o.product_id
            )
    )
SELECT cm.count_messages :: DECIMAL / co.count_order_items :: DECIMAL
         AS result
FROM cm, co ;


SQL Server 2008: Measure tps / select statements per second for a specific table?

OK, here is a cheap way, but it is not what I really want (I really want nice charts and graphs!):

-- First PASS
DECLARE @First BIGINT
DECLARE @Second BIGINT
SELECT @First = USER_SEEKS
FROM sys.dm_db_index_usage_stats
where object_id=14473333 AND index_id = 1

WAITFOR DELAY '00:00:15'

SELECT @Second = USER_SEEKS
FROM sys.dm_db_index_usage_stats
where object_id=14473333 AND index_id = 1
SELECT (@Second - @First) 'TableSeeks'
GO


You might look at trying out Luca Canali's PerfSheet4 tool, which helps extract and visualize data from AWR tables in Excel. It comes with a video and should help you get started with some of the necessary data gathering and analysis for troubleshooting.

You probably want to start with Query: Top 3 wait events and CPU option from the sheet which according to the blog post does:

  Extracts data from dba_hist_system_event and dba_hist_sysstat. This query selects the top 3 non-idle wait events and the CPU usage for each instance. It computes delta values and rates (delta value over delta time).

You can also look at the queries in the spreadsheet, and use those as examples for your own queries into the AWR tables, should you need something more customized to your particular problem. Hope that helps. =)


Identical tables, identical query, completely different execution times

...the original table contains more data than the new table, which should theoretically mean that the new table should finish the query faster.

If the execution plans were the same, this would most likely be true, but they're not. The number of rows expected (and the distribution of the data according to the statistics) affects the strategy chosen by the query optimizer.
Small table
When the heap table contains 398,399 rows, the optimizer chooses a serial plan using nested loops for all join operations except those directly affecting the heap table; those joins employ a hash join.

The complexity of the query is such that cardinality (row count) estimates are likely to be inaccurate, so the nested loop strategy ends up being a disaster. The optimizer considered a parallel plan, but rejected it as more expensive than the serial nested loops option.
Larger table
When the heap table contains 1,750,640 rows, changes in cost estimations mean the optimizer assesses that a parallel plan using hash joins and optimized bitmap filters extensively will be a better strategy.

This plan shape is much more resilient to cardinality estimation errors. The hash join build inputs may spill to disk, but the worst case is much better than executing an entire subtree a huge number of times (using nested loops).
Solution 1
If you know that a parallel plan with bitmap filters is the best choice in general, you could use a Plan Guide to enforce this, or encourage such a plan with an OPTION (HASH JOIN, MERGE JOIN) query hint. You may not always get parallelism if the tables are small, but performance should still improve (and be more predictable in general).
Solution 2
You could also explore breaking the query into simpler sections, initially along the lines of the Common Table Expression boundaries. Materializing sensible-sized intermediate results into temporary tables means:

Each part of the query can be optimized separately
The optimizer has statistics and accurate cardinality information
Additional indexes can be added to the temporary tables if proven to be beneficial
Redundant CTE evaluations can be skipped


What is the standard isolation level for a db used for money data?

The write behavior of all transaction isolation levels is the same. However, they are very different when it comes to reads, particularly repeated reads of the same data within a transaction. There are three main behaviors that you need to be aware of:

Dirty Reads
Non-Repeatable Reads
Phantoms

Wikipedia has a good explanation of these behaviors: http://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_Levels_vs_Read_Phenomena

You need to figure out which of these behaviors your application can handle. If it is an absolute requirement, that non of these can occur, than you have to use the Serializable isolation level. However, this is also the level with the least amount of concurrency, as preventing these phenomena requires a great deal of serialization of write processes. (Hence the name.)

In many situations you can get away with less restrictive isolation levels provided you use good coding practices. For example, in general it should be avoided to read the same record twice within the same transaction. That way you don't need to worry about non-repeatable reads.

However, sometimes you need to do things like check if a record exist and create it if necessary. This two step process is only safe when using the serializable isolation level as it is the only one that allows range locks and with that gives a way to prevent inserts into a table area that another transaction has "looked at".

A financial application probably has many situations where one record is read and then based on that one or more records including the original one are altered. Every tim you encounter a situation like that, it is more important that you indicate the write intend on the initial read by requesting a write or update lock. If you do that within the same transaction that executes the writes later on you are not dependent on the isolation level at all. You also will reduce the likelihood of deadlocks.

In the end, the choice of isolation level really depends on your requirements. The type of application is not a good guide to pick an isolation level. Look at each requirement and see if the implementation requires a specific isolation level.

Below is an example of how to use the FOR UPDATE hint in MySQL to request an update lock during a select statement:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE accounts(id INT, balance INT);

INSERT INTO accounts(id,balance) VALUES( 1,42);
INSERT INTO accounts(id,balance) VALUES( 2,16);

GO

CREATE PROCEDURE swap(
  pId1 INT,
  pId2 INT)
BEGIN
  START TRANSACTION;

  SELECT @Balance1 := balance
    FROM accounts
   WHERE id = pId1 FOR UPDATE;

  SELECT @Balance2 := balance
    FROM accounts
   WHERE id = pId2 FOR UPDATE;

  UPDATE accounts SET
    balance = @Balance2 WHERE id = pId1;

  UPDATE accounts SET
    balance = @Balance1 WHERE id = pId2;

  COMMIT;
END;

Query 1:

CALL swap(1,2);

Query 2:

SELECT * FROM accounts

Results:

| ID | BALANCE |
|----|---------|
|  1 |      16 |
|  2 |      42 |

Note, this is an example of how to implement the FOR UPDATE hint. It is not necessarily the best solution to solve the demonstrated problem.


Is it good practice to use non standard schema names in SQL Server?

In my opinion there is nothing wrong with it. In my data warehouse I use different schemas to differentiate between data for departments or in the case that I am copying tables from a third party database, to show where those tables come from.


How to recover an overwritten SQL Server database

As far as SQL native backups are concerned, you can only restore to the last good backup that you have which is 4 days ago.  So, unfortunately, from a SQL server backup perspective you may have lost 4 days worth of data.

That being said, if this is a VM (HyperV, VMWare, etc) and there are snapshots, you may be in luck and your systems guys may be able to get the information back.


postgres: how can I allow index creation but no table mutations or table drops by the same user?

At the SQL level you can't, since all those tasks are governed by table ownership.

The CREATE on a tablespace is required but not sufficient to create an index on a table and store the index in that tablespace. If you don't have the CREATE right on the tablespace you want to put the index in then you cannot CREATE INDEX that index. However, having that right is not enough; otherwise anybody could create indexes on any table if they had the right to create anything in any tablespace, and we don't want that. Indexes have a performance cost, take heavy locks during creation, and perhaps most importantly an expression index can leak data about the table via a malicious function or operator. So you must also own the table the index is to be created on.

Support for a separate INDEX right on a table could be added to PostgreSQL, but has not been, and might not get accepted if submitted. For now, you're stuck with having to own the table.

You could write a C extension that installs a ProcessUtility_hook that checks what operations are being performed and the current user identity, then rejects or permits them as appropriate. You can find examples of ProcessUtility_hook use in contrib/sepgsql and externally in the bdr_commandfilter.c file in the BDR project source code. You have to compile the extension, install it into the file system, then add it to shared_preload_libraries to install it, so you need full filesystem level access to the server, and usually root access.

A more practical approach is to use a SECURITY DEFINER function as a wrapper. Write a PL/PgSQL function that runs as the table owner and accepts as arguments the table to index, the column(s) to index, etc. Have it create the CREATE INDEX expression using format(...) then pass it to EXECUTE. Do not allow the user to pass arbitrary SQL expressions as arguments, or you're basically giving them full access via SQL injection. Want multiple columns? You'll have to accept colname text[] as an argument and quote_ident each one. And so on. Search for "dynamic SQL plpgsql" to learn more about this approach.


mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

innodb_log_buffer_size should be no more than 1% of RAM.

key_buffer_size should be only 30M, assuming you do not have any active MyISAM tables.

Those changes will let you increase innodb_buffer_pool_size to 7G, which might help.

Do you have any FULLTEXT or SPATIAL indexes?  FOREIGN KEYs?  Stored programs (probably not, since you did not ask for them to be dumped)?

If those don't help, file a bug with https://bugs.mysql.com


Best way to ALTER date column to timestamp on a large table?

How to do it efficiently and how to do it without blocking everyone else for the duration are diametrically opposed goals.  The efficient way is to schedule a downtime window in which to do it.

If that can't be done, then:

Add a new column of the desired type, with a NULL default value.
Change all the code which changes that old column or inserts new rows to make sure they correctly populate the new column as well (but make sure they don't depend on the new value already being correct--they just make it be correct)
Create an index to efficiently find rows where the new column is still NULL
Write a script to update rows with NULL values of new column to set them to be correct based on the value of the old column.  Do this in chunks that are as large as feasible, given that the first row in the chunk will be locked for the duration of the processing of the chunk.
Once all chunks of this are done and verified, change all the code to rely on the new column rather than the old column.
Change all the code to stop trying to maintain the old column
Drop the old column
Drop the index that supports finding still-NULL values of the new column (unless it is still useful)


PostgreSQL doesn't track which view column depends on which object. It is the view as a whole that is tracked.

But PostgreSQL tracks on which table columns (and other objects) a view depends: Each view is related to its query rewrite rule in pg_rewrite, and the query rewrite rule has a dependency on the table columns (and other objects) used in the view. These dependencies are tracked in pg_depend.

Putting the pieces of the puzzle together is straightforward, but takes a while. I have written a blog about this recently.


Postgres 9.3 copy data from another table with relation

I think something like this would work -- let me know if it doesn't

WITH forms_data AS (
  SELECT id, calldatetime FROM forms
) UPDATE qa_forms qf
  SET calldatetime = fd.calldatetime
  FROM forms_data fd
  WHERE qf.id = fd.id;

Edit (better version, suggested by dezso):

UPDATE qa_forms qf
SET calldatetime = f.calldatetime
FROM forms f
WHERE qf.id = f.id;


What does SPJ stand for in MySQL parlance?

Apparently it stands for "Select Project Join":

  What’s in a name?

  If some of this seems familiar but you don’t remember hearing the term
  “Adaptive Query Localization” before then you’re not going crazy –
  previous internal names were SPJ (Select Project Join) and Pushed-Down
  Joins. We just figured that Adaptive Query Localization was more
  descriptive.

Source: Dramatically Increased MySQL Cluster JOIN performance with Adaptive Query Localization


Is there a term for a table that has an "effective date" column?

Temporal Table or History Table are probably the terms you are looking for. Often you will see a nullable "end date" column in the table as well.


How to update a sequence column using single update statement

Here is a solution that can help you.

update foo set prio = prio+1 where prio >= 6 order by prio desc;
insert into foo (prio) values(6);


SQL Server - best practice before applying trace flags?

As for Trace Flag 8048, this MSDN blog says:

  HOW DO I KNOW IF I NEED THE TRACE FLAG?

  The issue is commonly identified by looking as the DMVs dm_os_wait_stats and dm_os_spinlock_stats for types (CMEMTHREAD and SOS_SUSPEND_QUEUE).   Microsoft CSS usually sees the spins jump into the trillions and the waits become a hot spot.

  Caution: Use trace flag 8048 as a startup parameter.   It is possible to use the trace flag dynamically but limited to only memory objects that are yet to be created when the trace flag is enabled.  Memory objects already built are not impacted by the trace flag.

Have you identified the need for trace flag 8048 as per the instructions above?

Since 8048 needs to be implemented as a startup trace flag, I'd configure 1118 at the same time.  I'd also consider adding 1117 if you are concerned about tempdb performance.

4199 does not need to be implemented as a startup trace flag, although that is certainly recommended once you've established that you really need it, and your workload will benefit from the great many changes it introduces.

On that note, I'd recommend enabling these trace flags only after extensive testing with representative load on a test server.


MySQL Authentication Error After Re-applying the mysqldump after an upgrade

I needed to run the mysql_upgrade command after re-applying the mysqldump after the update.

mysql_upgrade -u root -p --force


Are database options backed up in a full backup

db options gets restored except the ones like service broker, cdc, as mentioned here which you have to explicitly tell when restoring the db.

I tested the autoupdate stats - setting it to false, backing up db and restoring it as different name.

For new db, you can just set the model db settings and it will get inherited.


Why is my database restore being aborted?

An easy way to execute any ad-hoc query within your container, including RESTORE, is with SQLCMD:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -Usa -P<strong password> -Q"restore database Foobar from disk='/var/opt/mssql/backup/Foobar.bak' with stats=10, replace, move 'Foobar' to '/var/opt/mssql/data/Foobar.mdf', move 'Foobar_log' to '/var/opt/mssql/data/Foobar.ldf';"


How to configure SSIS in SQL Server AlwaysOn

In SQL Server 2014 & 2012, adding the SSISDB as an Availability Database in an Availability Group is not supported. My understanding is that with some workarounds, adding the SSISDB to an AG is possible, but not supported. The workarounds sound error-prone, and I've not attempted it personally.

Rather than adding the SSISDB to the AG, I'd suggest evaluating the business needs (High Availability and/or Disaster Recovery), and see what other options are the best choice.

Depending on your HA/DR needs, consider maintaining two parallel SSISDB catalogs, one on each AG server. From an operational perspective, you will need to ensure that SSIS packages are deployed to both servers, and that they are kept identical (packages, permissions, etc). This would give you the ability to use the locally-installed SSIS service on either machine to run packages for HA/DR. You would also need to make sure that you do not have SSIS packages running on both servers.

In SQL Server 2016, adding the SSISDB to an Availability Group is supported & documented. If upgrading is an option, you may consider that as well.

Within your SSIS package connections, you can use the AG listener to connect to the active node of the AG for data access. SSIS does not need to run on the same server that is hosting your data.


Try making an inventory transaction table.  This table will likely consist of all the columns common to your various transaction type records.  For some record types, this may be all the columns.  Add a transaction type column to differentiate the records.

You can then create an optional relationship to any subtypes which will contain the columns specific to the subtype.

Alternatively, you can add nullable columns for this data.  This will require additional constraints to ensure only the right columns are set.

EDIT: If you have null columns, there will usually be the only table. The shipment column would be required for all types having a shipment id (might include returns), and NULL for other types.  The alternative would be to have rows in InventoryShipments for shipments, and rows in InventoryReturns for returns.  This quickly gets complicated.

If certain (transaction) types have a lot of type specific columns, you could use a hybrid model, with most types entirely in the Inventory table and some types with type specific table.  This may occur when you use sub-type tables, as there may be types which only have columns which are common to all types.

Using either approach requires effort to ensure data integrity.  Null columns should always be null for columns, and not null when required for the records type.  If there are subtype records, there usually must be only one per inventory record.


You can do this with a TVF, when you migrate to SQL Server 2016 or higher you can use STRING_SPLIT.

Function source : https://stackoverflow.com/questions/10914576/t-sql-split-string

(slightly modified to use ';' instead of ',' )

CREATE FUNCTION

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @stringToSplit)
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

TEST DATA

CREATE TABLE test(id int, pseudoid varchar(255))
insert into test
values(1 ,'ABC'),
(2 ,'DEF'),
(3 ,'GHI;JKL'),
(4, 'MNO'),
(5 ,'PQR;STU;VWX')

TEST

   select id , s.Name as PseudoID from
    test
    cross apply dbo.splitstring(pseudoid) as s

RESULT

id  PseudoID
1   ABC
2   DEF
3   GHI
3   JKL
4   MNO
5   PQR
5   STU
5   VWX


MySQL upgrading from 5.7 to 8 - Throwing strange error and won't start up

I suspect this may be caused by a bug that happens when in-line upgrades go from 5.5 to 5.6 to 5.7 then finally 8.0.

The suggestion to deal with this is to run optomize table on every single table while in version 5.7. Once the 8.0 upgrade is attmpted, it is too late because you cannot go back.

I am giving up, rebuilding this server from scratch, then install 5.7 and perform the optomize table then try the upgrade to 8.0 again.

Good thing I have another 5.7 server running and a full backup of all the data.


Is the BACPAC from a TDE encrypted DB also encrypted?

No, the BACPAC is not encrypted. BACPAC is an export of schema and data.
TDE means that decryption is done when SQL Server reads the data pages from disk. I.e., the client tool that generates the BACPAC (sqlpackage.exe, for example) asks SQL Server to read the data, and that tool will never see the encrypted data.
So you'd have to post-encrypt the BACPAC file by some tool, if you have such a requirement.


Obtaining a MIN() value in a column in a subquery

From your post:

INSERT INTO PC
(model, speed, ram)
VALUES
(1070, 2.00, 1024, --- Missing closing parenthesis ')'
(1071, 2.10, 512),
(1050, 1.90, 512);

Once that was corrected, your query worked for me.


Is bulk-logged recovery model ever a good choice if point-in-time restore is not needed?

No, use simple

Bulk-Logged is a special case of FULL, not an enhanced version of SIMPLE.

Bulk logged requires log backups otherwise the transaction log grows (as per full). This takes more space.
Simple does not require log backups: the log will (normally) be cleared by the DB Engine.

Bulk logged with minimally logged operations also requires the data files to be present. Full does not because each row changes is logged. This introduces another complexity.

So use SIMPLE if you can restore to the last full database backup

If you have a nightly load, then using DIFF backups is probably enough to give you extra restore capabilities.

Note, I don't think I've ever used bulk logged recovery since SQL Server 6.5


Truncating a table with a foreign key constraint - SQL Server

The documentation for TRUNCATE TABLE (Transact-SQL) is fairly clear on this topic.

Referencing the Restrictions:

  You cannot use TRUNCATE TABLE on tables that:

  Are referenced by a FOREIGN KEY constraint. (You can truncate a table
  that has a foreign key that references itself.)

Regarding your comment

  I don't want this operation to be logged in transaction log that's why
  I wanted to use truncate instead of delete operation.

The TRUNCATE TABLE command is fully logged, just not at the row level.  Within a transaction, it can be rolled back.

Referencing the REMARKS section of the documentation.

  Less transaction log space is used.

  The DELETE statement removes rows one at a time and records an entry
  in the transaction log for each deleted row. TRUNCATE TABLE removes
  the data by deallocating the data pages used to store the table data
  and records only the page deallocations in the transaction log.


what is the difference between first and second execution of a query?

As others have mentioned in the comments, likely the performance improvement on subsequent runs that you saw in your first example is due to the data being cached in memory after your first run. There are multiple types of caches in MySQL, so even if you're not using the Query Cache, your MySQL instance uses the Table Cache and the buffer_pool which means the data from your query is actually cached in memory so that subsequent runs of that query don't need to fetch the data from disk again. This is beneficial if the size of the tables in your query are large.
The reason your second query didn't see much of a difference in performance on subsequent runs (though I would assume the raw table data was still cached) was because most of the runtime you're experiencing is probably due to the complexity of the query itself. Some of those complexities are from the EXISTS & NOT EXISTS that can be written in a more relationally efficient way, multiple inequality predicates, and the fact you're doing a COUNT() which may result in scans instead of seek operations, for example. The EXPLAIN ANALYZE / query plan for your second query would probably give more insight on specifically which part of it is the complexity that is causing most of the runtime.


IS_SRVROLEMEMBER returning NULL for valid non windows user

I think you're confusing logins and users

logins exist in sys.server_principals at the Instance level
users exists in sys.database_principals in each database

Your EXECUTE AS is executed based in info in sys.server_principals but requires an entry in sys.database_principals.

Here, I have a login gbntest that maps to user gbntestU in my GBN database.
My stored procedure works OK

USE master
GO
CREATE LOGIN gbntest WITH PASSWORD = 'pwd'
GO
USE GBN
GO
CREATE USER gbnTestu FROM LOGIN gbntest;
GO

--0, OK
DECLARE @loginuser varchar(100) = 'gbntest';
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO
--NULL, expected
DECLARE @loginuser varchar(100) = 'gbntestu';
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO

CREATE PROC dbo.uspGBNTest @loginuser varchar(100)
AS
SELECT IS_SRVROLEMEMBER('sysadmin'), IS_SRVROLEMEMBER('sysadmin', 'JoeBlow'), IS_SRVROLEMEMBER('sysadmin', @loginuser);
GO
EXEC dbo.uspGBNTest 'gbntest' -- get 0, OK
EXEC dbo.uspGBNTest 'gbntestu'  --get NULL, expected
GO

So, what do these give in the stored proc?

SELECT
  SUSER_ID(@loginUser), -- sys.server_principals
  DATABASE_PRINCIPAL_ID(@loginUser) -- sys.database_principals (for completeness)

This will tell you if

@loginuser is set correctly For example, TheUser is sent when it should be DOMAIN\TheUser
the expected principal (@loginuser) exists at both the Instance and database level for IS_SRVROLEMEMBER to work


SQL Server 2008 Authentication mode changed on its own

The chances that the Authentication Mode changed on it's own is pretty near zero. More likely, it got changed by accident.

This is a change that requires a restart of the SQL Server service in order for it to take effect. What likely happened is that the Authentication Mode was changed sometime since the last restart (which could have been months ago)--then, when the instance restarted, the change "suddenly" took effect seemingly on it's own.

This particular configuration is stored in the registry, so if you have OS backups of your server, you could do some research to go back in time to try to determine when it changed.


Preventing replication of alter table command to change engine

If you want to prevent any ALTER TABLE ... ENGINE=InnoDB; from executing on a Slave, just run this on the Master:

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

What this does is prevent the Master from recording the ALTER TABLE ... ENGINE=InnoDB; in its binary logs. Since the Slave only replicates from the binary logs of the Master, just don't record the commands that you do not want replicated.

If you have multiple tables to convert and not replicate, then you can do this:

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
ALTER TABLE ... ENGINE=InnoDB;
.
.
.
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;


Maximum granularity of user permissions (certain rows within table?)

You can grant access to views and stored procedures without granting direct access to the underlying tables (see https://stackoverflow.com/questions/4134740/grant-select-permission-on-a-view-but-not-on-underlying-objects amongst similar questions for some detail). This will certainly work for reading from views, though I'm not sure about writing to them (it would be difficult for it to know if they should be writing specific things, so you may have to manage this with INSTEAD OF triggers instead of it being done for you).

With the permissions correctly set the users can only access what the procedures/views expose to them, so you can control their access by giving different users different procs/views that each have different filtering clauses. You can even give the different views/procs the same name if you give each user (or class of user) its own schema, so you don't need different code/configuration elsewhere for User1, User2, ..., UserN as they can all run SELECT <something> FROM FilteredView and get results (User1 might see results from the view u1.FilterdeView, User2 and User3 might have reports as their default schema so they get results from reports.FilteredView instead, and so forth).

My knowledge of this is MSSQL specific, but I'm pretty sure other major DMBSs (such as Oracle or postgres) have very similar access granularity controls.


Eager spool for update and delete on partitioned table

The eager spool has to do with the size of data you're deleting.

In this case, you're updating a large quantity of rows on a table with several indexes. You're also updating a field that's covered by a lot of nonclustered indexes, and SQL Server has to update all of those indexes as well.

To avoid this operation, update less rows (as a percentage of the table) or index the table less heavily.


Help combining two MySQL queries to improve performance

I'm assuming that G followed by anything is valid in the second case. If so you sould be able to replace your 3 queries with something like:

SELECT d.field_id_105 AS code
     , t.title AS title,
     , t.entry_date AS entry_date
     , t.entry_id AS entry_id
     , i.title AS installer
     , i.entry_id AS installer_id
     , d.entry_id
FROM exp_channel_data AS d
JOIN exp_relationships AS r
    ON r.parent_id = d.entry_id
   AND r.field_id = 48
JOIN exp_channel_titles AS t
    ON d.entry_id = t.entry_id
   AND t.status = 'open'
JOIN exp_channel_titles AS i
    ON r.child_id = i.entry_id
   AND i.status = 'open'
WHERE SUBSTR(d.field_id_56,1
            ,CASE WHEN SUBSTR(d.field_id_56,1,2) REGEXP '[A-Z][0-9]'
                                    THEN 1
                                    ELSE 2
                               END)
    = SUBSTR(d.field_id_105,1
            ,CASE WHEN SUBSTR(d.field_id_56,1,2) REGEXP '[A-Z][0-9]'
                                    THEN 1
                                    ELSE 2
                               END)
AND d.channel_id = 12;

This is untested and may contain syntax errors since there where no ddl in the question.


I don't really have the possibility to try jMeter now, so I don't know how following the below advice would affect your tests.

Normally benchmarking is done so that after the tests the original database state is restored.  This is achieved by rolling back all changes.

In the case of jMeter you have to set AutoCommit to false in the JDBC configuration, then issue a ROLLBACK as the very last command in your test script.


Clean up orphaned documents in MongoDB error

OK..

Step 1. database and collection must be originally created (or sharded) thru mongoS

Step 2. command db.runCommand({cleanupOrphaned: "test.collection"}) must be ran on individual shard node (if replica set, then primary) at admin database. Not on mongoS, if you run that command on mongoS, you get that error message (and of course that error don't tell you that mongos cannot support that command).

#>mlaunch init --sharded 2 --single
launching: mongod on port 27018
launching: mongod on port 27019
launching: config server on port 27020
replica set 'configRepl' initialized.
launching: mongos on port 27017
adding shards.

#>mongo --port 27017
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
MongoDB Enterprise mongos> use test
switched to db test
MongoDB Enterprise mongos> db.collection.insert({})
WriteResult({ "nInserted" : 1 })

#> mongo --port 27018
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27018/
MongoDB server version: 3.4.6
MongoDB > use admin
switched to db admin
MongoDB > db.runCommand( {cleanupOrphaned: "test.collection"})
{ "ok" : 1 }


Sudden increase in Page Writes/s after patching / rebooting

When you restart the service or reboot, the buffer pool and plan cache get wiped out as a result, and so when the server starts back up again, they don't get loaded until users start executing queries.

What you can do to mitigate this is have a stored procedure aimed at simply running some queries that will load up your most frequently-used data into memory. Call that procedure after a restart, and you will feel that initial delay instead of your users.


A good number is rarely peaking to the available IOPS of the hardware or Cloud provider.

Your graph show rarely getting over 25.  That is a very low number.

AWS carves up machines and sells you different amounts of CPU and IO.  I can't say what your max is.

If you are hitting limits of CPU or IO with MySQL, then the first thing to do is to find the slowest query.  It may need a better index, probably a 'composite' index.  Or it may need to be reformulated in some simple way.  Only after that, I would look at tuning.  Then at hardware or AWS plan.


Complex query with multiple normalized fields

Not many people like them, but I would suggest you use a cursor in a stored procedure to loop though the fields and normalise them into the target table (silly ETL process).

You could probably do better having had separate lookup tables for each type of field, but it depends on you scenario.


Difficulty in moving a SQL Server database

If you wanted to make life easier, according to the Version and Edition Upgrades page, there exists an edition upgrade for SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer.  You could just perform an edition upgrade and call it a day.

Alternatively, if you wanted to keep with your original plan of installing a new instance and migrating the database...

If you can't find files on your system, try installing Everything.  It is an extremely fast indexing application that will run in your notification area.

Once you find the backup file, try running the following command to see if SQL Server can access it:

RESTORE HEADERONLY FROM DISK = 'drive:\path\to\mybackup.bak'

or

RESTORE FILELISTONLY FROM DISK = 'drive:\path\to\mybackup.bak'

If that does not work, then try moving the file to a location that the SQL Server service account has access to.  If you don't plan on accessing any network resources with SQL Server, it would be easiest to run SQL Server as local system on your workstation.

Once your Developer instance can see the .bak file, you can either use the Restore Database task in SSMS (Right-click Databases in Object Explorer and select Restore Database) or you can script it.  Just remember in either option you should change the destination path to the data and log files so they reside in the new default data path of the SQL Developer instance (or some other directory on your workstation).  By default, it'll try putting them back in the original location when the backup was taken.

Here is an example restore script you could modify (use the RESTORE FILELISTONLY details for the logical file names):

RESTORE DATABASE [MyDB] FROM DISK = 'drive:\path\to\mybackup.bak' WITH MOVE 'logicalDataFilename' TO 'drive:\path\to\physicalDataFilename.mdf', MOVE 'logicalLogFilename' TO 'drive:\path\to\physicalLogFilename.ldf'


How do I query for fields that have AT LEAST a given range?

To find those with a "range" of at least 4 for example

SELECT "Key"
FROM   YourTable
GROUP  BY "Key"
HAVING (1 + MAX("Value") - MIN ("Value")) >= 4;

Obviously alter the 4 as appropriate for the other cases.

SQL Fiddle


Oracle Enterprise Manager font problem, some text is displayed in Chinese and with squares

Oracle Enterprise Manager Grid Control Advanced Installation and Configuration Guide 11g has the following section:

  How can you change the language used in Enterprise Manager Grid Console?

  By default, the Enterprise Manager Grid Control Installation Wizard
  installs Enterprise Manager Grid Control console with English
  language. If you want to view them in another language, then select
  the required languages in the Select Languages screen of the
  installation wizard.

    Note: The language you select here changes the language used in the
    Enterprise Manager Grid Control console screens only; it does NOT
    change the language used in the installation wizard screens. The
    language of the installation wizard screens is based on the operating
    system language.

  When you select additional languages to install, the installation
  wizard also installs fonts required to display the languages.

  For some components, languages are installed only if you select them
  during installation. In this case, if you access the application in a
  language that is not available, it reverts to the locale language.

  For other components, available languages are installed regardless of
  what you select during installation. In this case, however, fonts are
  installed only for the languages that are explicitly selected. When
  you access the application, it uses text in your language because the
  language was installed. However, if you do not have the appropriate
  fonts to render the text, the text appears as square boxes. This
  usually applies to the Chinese, Japanese, and Korean languages.


Inserting into Microsoft SQL Server Table

Use single quotes (') instead of double quotes (") for your values, such as:

USE test;
INSERT INTO idk(Id, Name) VALUES ('1','Name');

Using double quotes is one way to let SQL Server know you are specifying a column name instead of a value. Be careful about the data type for the ID. If it is an integer, SQL Server will convert the value in the query from a string to integer for you but it will be better to not have it as a string unless the data type is a string. For the ID, if it's an integer data type (int, bigint, smallint, or tinyint), then change the query as follows to remove the quotes from the integer value:

USE test;
INSERT INTO idk(Id, Name) VALUES (1,'Name');


Insert Into Select multiple columns mysql

Here is a way, using variables:

SET @count1=0;
SET @count2=0;
SELECT COUNT(email) INTO @count1 FROM user;
SELECT COUNT(email) INTO @count2 FROM application;
INSERT INTO subdata (reggeduser,completed) VALUES (@count1, @count2);


Vehicle spare parts inventory datadase model

Great, the part/whole modelling problems.

Here is how LedgerSMB does it. We are likely to change this at some point but this part works well enough.

Basically we have two tables, called parts and assembly.  The exact names/layout will change over time but this basic concept will probably stay the same.

In the parts table, we have parts (goods in inventory), services, and assemblies (as you put it, sets).  In the assembly table we store the assembly to connect with, the part or assembly that is a part of it, and the number required.  This allows sets to be parts of other sets recursively.  This effectively creates a components tree in assembly and a sales component tree in parts.

It works well enough, especially when done on a database which supports WITH RECURSIVE common table expressions (i.e. not MySQL...)

Of course then you have a table or two to manage inventory movements and this would handle the sets and their deconstruction according to your business rules.


Need advises for a new MySQL architecture

This sounds like a good use case for Galera.
http://codership.com/content/using-galera-cluster

You could also use something like this:
Each server is a master for it's copy of the user table (I assume the user table is sharded?). Then there could be a central server with MariaDB or MySQL + Tungsten Replicator which is a slave of all the other servers.

And then you could create another master (extra instance on one of the servers?) which hosts the master copy of the content tables.

CM = Content Master
U  = User Master
UC = User Combined

Server 1: CM + UC (2 Instances)
Server 2-5: U

For the content tables (1 master, 4 slaves):
CM -> U1
   -> U2
   -> U3
   -> U4

For the user tables (4 masters, 1 fan-in slave):
U1 -> UC
U2 ->
U3 ->
U4 ->

Galera and MySQL Cluster provide synchronous cluster where you can write to any server. MySQL Cluster needs 2 data nodes and 2 SQL nodes as a minimal setup (the mgmt node can be combined with a SQL node). MySQL Cluster does not use InnoDB or MyISAM. Galera needs 3 nodes and does support InnoDB.

Investigate Galera, Tungsten (and MySQL Cluster aka NDB?).

Read MySQL High Availability (O'Reilly).


Writing SQL to check for a role if exist and add member

Your basic question boils down to "How can I tell if a given role exists in a database." All of the other code you already know how to do.

The easiest way is to query the sys.database_principals table to see if the role is there. Based on your sample question, here is a good check and update into the different roles:

IF EXISTS (
    SELECT *
    FROM sys.database_principals
    WHERE name = 'role_a'
        and type = 'R'
        )
BEGIN   -- Role_A exists, add to role
    ALTER ROLE [role_a] ADD MEMBER [Domain/SqlAgent]
END
ELSE IF EXISTS (
    SELECT *
    FROM sys.database_principals
    WHERE name = 'role_b'
        and type = 'R'
        )
BEGIN   -- Role_B exists, add to role
    ALTER ROLE [role_b] ADD MEMBER [Domain/SqlAgent]
END


PostgreSQL - getting Too many client repeatedly

Your application has a connection leak.
There is no way to fix this on the database side - you'll have to change the application so that it closes connections again.

I guess you set max_connections so high to work around this problem (1000 is too high).


Finding matching records in a chain to form a parent user

These users form a graph. They are linked by edges to common attributes (email, device etc.). The question boils down to finding disjoint subgraphs within the data, and assigning each a label.
There's an answer on SO which addresses this. The web will have many more if you care to search.
SQL Server 2019 introduced SHORTEST_PATH() which would simplify the query.


PostgreSQL versus MySQL for EAV structures storage

Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling.

In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an "index_merge" feature to substitute for that.

For PostgreSQL you may also be interested in the hstore extension for storing sets of key/value pairs.


What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

The text value shows the procedure in which the statement that is being executed resides. The Command references the action of the executing statement within the procedure

To find the exact statement within the procedure that is being called, you'll need to use the statement_start_offset and statement_end_offset from dm_exec_requests.

Add the following to the list of columns selected:

,SUBSTRING(text, (statement_start_offset/2)+1,
                            ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text)
                                  ELSE statement_end_offset
                                END - statement_start_offset)/2)+1)


do oracle redo logs need to be copied when all other data is copied to a new disks?

If you are using ASM, you could even do this online by first adding the new disks to the current diskgroups, rebalance the disk groups and drop the old SAN disks from the diskgroups. During this drop, the first thing that happens is - again - a rebalance action in which the data that is on the dropping disks is moved from those disks to the new disks. A very powerful feature, not sure if it can help you.

If it all has to be done using FS copies, it still can be done online, if you are running archive log mode. Since 11g RMAN can clone from an active database, also very powerful.

FWIW: some applications check the controlfile and complain if it is changed. This is part of a check for licenses.

Short answer for you question, you need the online redologfiles. The archives are only needed for recovery operations so they should remain accessible, not perse on the new SAN. A regular backup would be ok. Copy the online redo logfiles and use RMAN for the copy of the database since it handles everything that needs to be done in a good way.


I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish:

"I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan.   To accomplish the objective in SQLplus, use "Substitution variables"...excellent background is found here.

My example, doesn't precisely follow your SQL Server T-SQL example, but it's close and is NOT procedural (read: no anonymous PL/SQL block.)

PL/SQL with Substitution variables:

DEFINE min_salary = 1000
DEFINE mgrID = 7

select *
from employees
where
    salary > &min_salary
    and mgrID = &mgrID
;

All at the SQL-Plus prompt....from my fictional employees table, I got this result:

    SALARY      MGRID
---------- ----------
      2000          7
      1001          7
2 row selected.

If you want to make this semi-procedural, then you use BIND Variables and the declarative syntax of PL/SQL similar to T-SQL. (In my opinion this is a HUGE waste of effort, but is included to help clarify variable types.)

SQLPlus using PL/SQL Bind variables:

 VARIABLE   v_in_min_salary number;
 VARIABLE   v_mgrID         number;    /* vars are defined within SQL+ */
 VARIABLE   v_out_salary    number;
 VARIABLE   v_out_mgrID     number;

BEGIN
    :v_in_min_salary    := 1000;   /* vars are assigned values in PL/SQL */
    :v_mgrID            := 7;

 select salary, mgrID
  into :v_out_salary, :v_out_mgrID
  from employees
  where salary > :v_in_min_salary and mgrID = :v_mgrID
 ;

end;
/

PRINT :v_in_min_salary   /* here the in and out vars are displayed  */
PRINT :v_mgrID
PRINT :v_out_salary
PRINT :v_out_mgrID

Fully-procedural approach using PL/SQL:

set serveroutput on;  /* environment setting within SQLplus to see output */
DECLARE
   v_in_min_salary number   := 1000;
   v_mgrID    number        :=7;
   v_out_salary number;
   v_out_mgrID    number;

BEGIN
  SELECT salary, mgrID
    into :v_out_salary, :v_out_mgrID
    from employees
    where salary > :v_in_min_salary and mgrID = :v_mgrID
  ;
  DBMS_OUTPUT.PUT_LINE('Salary Output is= '||:v_out_salary);
  DBMS_OUTPUT.PUT_LINE('Manager ID = '||:v_out_mgrID);
END;
/

So, when do I use each case?  I use SQLplus Substitution variables 75% of the time when I write SQL scripts that I want to parameterize the input for. Executing a script at the SQLplus prompt allows passing inbound arguments, so my favorite Substitution variables are &1 and &2 as in the following example:

   @get_table_size.sql <schema> <table_name>
        which in practice becomes
   @get_table_size jason employees

and executes SQL code similar to

SELECT extents
  FROM all_tables
  WHERE UPPER(table_owner) = upper('&1')  /* inbound first argument */
  and UPPER(table_name) = upper('&2')     /* inbound 2nd argument   */
;

Notice how the inbound arguments are assigned into substitution variables, then used in the SQL statement.  I never use the middle example, and I always use the 3rd examples (procedural PL/SQL) when writing functions, procs, and packages.


When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query?

I have been troubleshooting an issue within a database that has some serious blocking issues. I am just wondering if there is a way to see what variable is associated with the query.

I would suggest, you should use either of below for troubleshooting blocking problems :

You can use beta_lockinfo by Erland Sommarskog

beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

Another method is to use sp_whoIsActive by Adam Machanic with @get_locks = 1 and dump the data into a table for analysis
EXEC sp_WhoIsActive
@filter = '',
@filter_type = 'session',
@not_filter = '',
@not_filter_type = 'session',
@show_own_spid = 0,
@show_system_spids = 0,
@show_sleeping_spids = 0,
@get_full_inner_text = 1, ----------> 1 will get full inner text of query
@get_plans = 1,
@get_outer_command = 1,   ----------> 1 will get outer command of query
@get_transaction_info = 0,
@get_task_info = 1,
@get_locks = 1,   ----------> 1 = ON (get lock info); 0 = OFF
@get_avg_time = 0,
@get_additional_info = 0,
@find_block_leaders = 1,   --> 1 = will find Lead blockers
@delta_interval = 0,
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
@sort_order = '[start_time] ASC',
@format_output = 1,
@destination_table = '',
@return_schema = 0,
@schema = NULL,
@help = 0

There are other ways like Blocked Process Report Event Class or if you are using sql server 2008 and up, then Extended events, but above ones will give you the details that you are looking without much effort.


How to refresh Oracle data dictionary to remove deleted tablespace and datafiles?

There is nothing wrong with the data dictionary.

The moment you overwrote the undo datafile of an active database (which I still wonder how you managed, because file locking does not allow this) you made it impossible to recover the database to a consistent state.

The method you tried would have worked with a regular user tablespace, but undo is different. Your database crashed, it is in inconsistent state. The database can not be recovered to a consistent state, because transactions that were active at the time of the crash must to be rolled back, the rollback needs the undo, but you do not have it anymore. You can not just simply skip undo and remove references to it from the dictionary.

Even if you managed to open an inconsistent database (there are some hidden parameters that allow this in some cases), it may just crash immediately after opened. This scenario is the one before last resort to salvage data from a database that can not be opened anymore and has no backups. (The last resort is contacting 3rd party companies for their data mining tools that can read Oracle datafiles directly and salvage data from them.)

Typically you would just get your RMAN backup, restore and recover the missing file, and you would be over with it - but you have no backup.

You have a dump file, then go ahead, create a new empty database, and import to it. Then schedule a proper backup job.


All in one query for tracking changes in sql server

I would set up an extended events session to monitor these kind of activities and then review the results of the session from time to time.  You can also set up policies that prevent DDL changes.


How can I enforce that my composite key columns do not equal one another in MySQL?

MySQL-8.0.16 enforces check constraints
alter table user_relationships add check (requester_id <> addressee_id)

Afterwards:
insert into user_relationships values (3,3,NOW())

Check constraint 'user_relationships_chk_1' is violated.

ref: fiddle


Ansible: How to change MySQL server root password by reprovisioning the server

The problem you have is that Ansible is trying to use the same root password to login as you want to change it to:

- name: Set root user password
  mysql_user: name=root
              host="{{ item }}"
              password="{{ mysql_root_password }}"
              check_implicit_admin=yes
              login_user="{{ mysql_user }}"
              login_password="{{ mysql_root_password }}"
              state=present

Obviously this is never going to work if you want to use this play to change it.

Instead you should change the above play to be something like:

- name: Set root user password
  mysql_user: name=root
              host="{{ item }}"
              password="{{ mysql_root_password }}"
              check_implicit_admin=yes
              login_user="{{ mysql_user }}"
              login_password="{{ mysql_old_root_password }}"
              state=present

And then update the relevant inventory files to add this new variable.

So your group_vars/production should now contain:

mysql_old_root_password: productionpw
mysql_root_password: newproductionpw

It looks like this playbook uses the root password in both the roles/mariadb/tasks/main.yml playbook and also roles/wordpress-setup/tasks/database.yml so you might want to run the whole server.yml playbook to make sure this is set up properly.


Calculating disk space usage per MySQL DB

There are 3 scenarios.

If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.
If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh.  It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side.  This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.
If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.

In any of the cases above, you can run the following query to get the information that you are looking for.

mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
+--------------------+-----------------+
| table_schema       | MB              |
+--------------------+-----------------+
| prod               | 298025.72448921 |
| information_schema |      0.00781248 |
| maatkit            |     70.77330779 |
| mysql              |      0.66873168 |
| test               |   4752.31449127 |
+--------------------+-----------------+
5 rows in set (0.01 sec)

If you have a very large number of tables, it can be slow, as you have already discovered.