Mysql match on two columns and get count How do I get a count of?

+---------+-------------+
|   col1  |   col2      |
+---------+-------------+
|     Joe | April       |
|    Fred | May         |
|     Sam | April       |
|     Joe | April       |
|    Mark | Jan         |
|     Joe | Dec         |
+---------+-------------+

In MYSQL how do I get a count on Names that match only if Month also match?
IE
Joe    April should be only one...

@Abdul's answer just needs HAVING COUNT(*) > 1

The query shold look like this now

SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;

Here is the execution of it:

mysql> create table test(col1 varchar(20),col2 varchar(20));
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO test VALUES('Joe','April'),('Fred','May'),
    -> ('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+----------+------+-------+
| COUNT(*) | col1 | col2  |
+----------+------+-------+
|        2 | Joe  | April |
+----------+------+-------+
1 row in set (0.24 sec)

mysql>

If you do not care for the count, remove it from the SELECT clause

mysql> SELECT col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+------+-------+
| col1 | col2  |
+------+-------+
| Joe  | April |
+------+-------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


How to set max user connection per database in mysql? Hi i am new to MySQL and currently using version 5.5, currently i have one instance for MySQL, with 50 databases hosted on that instance. We have identified that per database there were fix amount of users connection per database is getting used like in my case its 100 users are active on that database. we would like to know is there any specific setting under which this behavior is getting observed?

You may set a cron every minute to trigger below command and pipe it to a logfile and rotate it every day.

select current_timestamp();

select count(*) sumOfUsers , user,db from information_schema.processlist group by user,db;

After a day you may grep for values more than 90 in sumOfUsers column to see which line and then you may get timestamp above it. Thus you may measure time range and do manual monitoring around that time to see how often it takes the connections almost 100. Also you may add Host  in query to see if any particular application host is doing this pile up of connections.


different outcome based on function execution method I am mainlining a legacy system and realize that the answer to this may take a deeper explanation of the code but before I go that far I wanted to make sure the solution wasn't something more simple.

Here is the situation:

the function/s in question just generate and return a new recID in our system, nothing really that complicated at all just some pretty situation-specific business logic. One thing this function (in this case the update I am about to describe happens in another function called by the first. this second function just contains the update statement, that is all.) is supposed to update a status record from active to complete. very simple.

I am seeing a strange difference in this update based on how the function is executed.

In some places in the code the function is executed with: SELECT function(X) FROM DUAL;

In other places in the code the function is executed with:

DECLARE
  RetVal number;
BEGIN
  RetVal := function(X);
  DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));
END;

In places where the first example is used, the status doesn't get changed.

When the second example is executed, the status does change.

Could this be something at a higher level that I don't understand/remember about how these two examples execute? Any Ideas? I can't imagine that it matters but I am running these through Toad.

If you have a function that does an UPDATE and you try to call that from a SELECT statement, you should get an Oracle error saying that you cannot do DML inside a query.  So your SELECT statement should be throwing an error.  My first guess would be that you have an exception handler somewhere that, intentionally or not, is catching and discarding the error.  My next guess would be that the function was declared using an autonomous transaction pragma, which is almost certainly a bug because that would mean that the function was running in a separate transaction scope from the calling code and could not see uncommitted changes made by the caller (such as, for example, inserting the row that the function is trying to update).

In general, if you have a piece of code that is doing an update, that should be a stored procedure not a stored function.  That would mean that it cannot be called from SQL (just like a function that does DML cannot be called in a SQL statement).  But that is exactly what you want.  You don't want a function called in a SQL statement to have a side effect because you cannot control things like how many times the function is called.  Even in a SELECT from dual, the function could be called multiple times by Oracle (though that is unlikely).


How to return a varchar type in a select of a free string. example: Select 'Test' as field from table Unlike other databases, PostgreSQL returns a TEXT type by default every time you run a select with a free string. Example:

SELECT 'Text' as FIELD FROM TABLE

in this case above the field FIELD will be returned with type TEXT

Does anyone know if it is possible to change some parameter in PostgreSQL so that it returns a CHAR or VARCHAR type in the above case?

Firebird and Oracle return a CHAR type.

I know this would be solved with a CAST however for this I would have to replicate the same for all SQL's of the system besides having to make using CAST a standard routine of developing new SQL's.

Anyway, does anyone have any idea of anything that might have changed? I'm studying the files pg_cast.h and pg_type.h

Maybe you just need to cast it.

 SELECT CAST( 'Text' as CHARACTER VARYING(99999) ) as FIELD FROM TABLE;

or using an alternative pg-specific casting syntax.

 SELECT 'Text'::varchar as FIELD FROM TABLE;

If you select a string constant and don't cast it to something it has type unknown.  but I'm guessing your connection library converts this to the preferred postgres string type 'text'


Moving MySQL databases/folder to another disk I want to separate MySQL database from the OS (Ubuntu) disk by moving the entire database to an exclusive HDD (M.2), and update the database folder in my.cnf.

My question is: when the MySQL folder is on a separate disk, can I safely format the OS disk and install a fresh Ubuntu (and MySQL)?

Is it OK to install a fresh MySQL and simply point to the existing MySQL folder on a different disk in the my.cnf of freshly installed MySQL?

OR the best practice is to restore the databases from a dump after a fresh install, though the database is still available in the machine?

Yes, you can here is the steps to do it.
backup:

# service mysqld stop
# cd /var/lib/mysql
# tar -cvzf /tmp/mysql-backup.tar.gz ./*
# service mysqld start

restore:

# service mysqld stop
# cd /data/mysql
# tar -xvzf /tmp/mysql-backup.tar.gz
# service mysqld start

Notes:

Cold backup will not work if you try to restore it in older versions of MySQL it will be better if you use the same version.
You need to make sure new directory is owned by MySQL user and it has the needed permission
New datadir is accurate inside my.cnf


SSIS ODBC: Mapping result set columns to variables returns error "Value does not fall within expected range" In SSIS 2012, I'm using an "Execute SQL Task" to call a stored procedure that returns a single row with two columns.  I'd like to map that result set to two SSIS user variables.  Should be easy, right?  This is works when I use an ADO.NET or OLEDB Connection Manager, but fails when I used an ODBC Connection Manager.

When I map using ODBC, SSIS returns the following runtime exception:

  [Execute SQL Task] Error: An error occurred while assigning a value to variable "MyString": "Value does not fall within the expected range."

The SQL Server columns have a data type of nvarchar(50).  I've also tried varchar(50) and char(1) to no avail.  The SSIS variable "MyString" has a data type of String.

.

The ODBC connection's Code Page is 1252 and the SQL Server 2012 collation is SQL_Latin1_General_CP1_CI_AS.

I'm using a SQL Server 2012 database.

Here's the general setup of the task for reference:

.

Is this possible to do using an ODBC connection?  Or is this yet another type of functionality that just isn't supported for ODBC in SSIS 2012?

With ODBC in SQL task the first position is 1 not 0, this totally cured my problem. Also note that ODBC does not seem to recognize the alias so do not use the alias as the resultName. It seems that the message "expected range" is referring to the result set positioning; i.e. '0' or a name is not seen in the range of variable positions (such as 1 and 2 if you have two selected, 0 or 3 is out of range)


Is catclust.sql really required? What is the importance We are in a process of upgrading RAC Databases, and when validating the installed components, we saw that some of the databases aren't displaying the RAC component, but still the RAC works normally just like the other ones that show RAC component as installed.

Why isn't the component showing up as installed?

And, if the documentation states that catclust.sql create rac additional views, shouldn't it be a mandatory component for RAC dbs?

Thanks in advance.

Let me start with another question.

Is catproc.sql/catalog.sql really required?

Try creating a new database manually:

create database;

And stop here, don't run any scripts. You have a "functioning" database, you can create tablespaces, users, query v$ views, create tables, indexes, insert and query data. You can even create user-managed backups.

Now try to run PL/SQL code, query ALL|DBA|USER_* views, start RMAN, use AWR or jobs. They will not work, because the objects they need were not created.

Can we use the database without running the above scripts? Yes we can, but some feature may not work, and life becomes quite uncomfortable. For example, the view DBA_DATA_FILES is created by the above script, but that view is not mandatory for the operation of the database.

Can we use RAC without running catclust.sql? Yes we can, but some feature may not work.
We can view the content of catclust.sql, but to be honest, I don't know what features depend on the objects created in there, so I just follow the documentation, and run the script.

  Why isn't the component showing up as installed?

Because that is nothing more, than an extra row in a specific table (SYS.REGISTRY$), we can insert anything in there. And the required script was not run.

  And, if the documentation states that catclust.sql create rac
  additional views, shouldn't it be a mandatory component for RAC dbs?

No, as it (in my opinion, based on the content of the script) does not add any additional core feature, just extends the existing ones.


best way to log large amounts of data I'm building a new logging system for our company. I'm wondering about the best way to store about 50-100GB data each year.

I'm looking at both MySQL and MongoDB. I'm considering MongoDB because it looks easy to expand to lots of data and it works pretty fast. I'm considering MySQL because I've used it before.

We have a log database today in MySQL which uses InnoDB and foreign keys. It has over 200GB data. It's not working well and the database crashes whenever I try to remove anything from it. I'm not the one who built it, I'm just trying to fix the problem.

I hope people out there know a good way to log this amount of data. I just need a reliable way to store and a simple way to query. I'm open to other options.

The logs are generated on separate servers, gathered to a central log server and imported. I'm building an API to access them when we need to query them.

ELK Stack is a good choice, even though i use elastic search + gray log for logging purposes. Experience with elastic search, several hundreds of GB are not an issue, u can in any case, easily increase capacity adding new nodes to it.

Graylag has an API which you could use in any other app


Utilizing a Right and Left Outer Joins in same SELECT I am looking to better build my SQL Development skill set.  Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down".  This results in really only needing to use INNER and LEFT OUTER joins for the various queries.

I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.

Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.

EDIT:

I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?.  It would appear that in general there is a consensus that

Most all queries using a RIGHT OUTER JOIN can be converted into an
INNER and LEFT OUTER version.
There are some benefits for
    read-ability formatting or organizational processes which might
    require using a RIGHT OUTER instead of a LEFT OUTER.

There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset.  I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.

In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.

Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.

You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.


Disable Oracle auditing for a particular user? I have one application user in my Oracle 11g DB which does logins/logouts very often.

Since this user is not a human, but an application I would like to exclude the login and logout actions of this particular user from my audit log.

How can I do that?

There is no "audit..." command option to exclude selected users.

you can do it by enabling audit for all other users, example:

audit update table, delete table,
      insert table by TESTUSER by access;


Does Oracle do a native conversion to numbers for varchar2 that are aggregated? I have a table in an Oracle 11g database that's similar to the following:

CREATE TABLE BUDGET_DATA (
  BUDGET_MONTH varchar2(30)
  , BUDGET_CATEGORY varchar2(30)
  , BUDGET_AMOUNT varchar2(30)
)

All the values in BUDGET_AMOUNT are numbers. When I do select SUM, the values come out as I would have expected if BUDGET_AMOUNT was actually stored as a numeric.

SELECT BUDGET_MONTH, SUM(BUDGET_AMOUNT)
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

I'm curious, does Oracle perform a native conversion to number if you put a VARCHAR2 in an aggregate function? I would have expected that I'd have to do the following to get the data to sum correctly:

SELECT BUDGET_MONTH, SUM(TO_NUMBER(BUDGET_AMOUNT))
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

Can someone point me to some documentation about this behavior? I haven't been able to find anything to describe it.

Implicit Data Conversion

  Oracle Database automatically converts a value from one data type to
  another when such a conversion makes sense.

You can find more on the above URL.

After you read it, forget everything written there, and use explicit conversion. Relying on implicit conversion is bad practice.


Can't update "CO2" to "CO₂" in table row Given this table:

CREATE TABLE test (
    id INT NOT NULL,
    description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');

I've realised I can't fix a typographic issue:

SELECT * FROM test WHERE id = 1;
UPDATE test SET description = 'CO₂' WHERE id = 1;
SELECT * FROM test WHERE id = 1;

because the update matches but has no effect:

id          description
----------- -----------
1           CO2

(1 affected rows)

(1 affected rows)

id          description
----------- -----------
1           CO2

(1 affected rows)

It's as if SQL Server determines that, since ₂ is obviously just a tiny 2, the final value won't change so it's not worth changing it.

Could someone shed some light on this and maybe suggest a workaround (other than updating to an intermediary value)?

The subscript 2 is not part of the varchar character set (in any collation, not just Modern_Spanish). So make it a nvarchar constant:

UPDATE test SET description = N'CO₂' WHERE id = 1;


Why does mysqldump require drop command? I am trying to backup a large table and after reading various other answers on this site have tried

mysqldump --user=user --password=password dbname1 tablename | mysql --user=user --password=password dbname2

I get the error

DROP command was denied to user 'user'@'localhost' for table tablename

which scares me. This is the first time I've worked with such a large DB and thus needed to use mysqldump, so I don't know what's going on. Why would it need the DROP command? I want to basically copy the table into another database, not modify (or delete) the existing one in any way.

By default mysqldump will output commands to drop tables, if they exist. This is why you get the error. It doesn't come from the mysqldump command, but from the mysql command you are piping the commands to. The user doesn't have permission to drop the table.

You can append --skip-add-drop-table to the mysqldump command to remove the DROP TABLE commands, but then you will get errors if the tables exist.

mysqldump parameters


What are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN? I am converting an Informix SE to an IDS database.  The programs I have use ODBC System DSNs to communicate with the database.

For SE, we use the sesoctcp protocol.  The program code, would open a Recordset from the DSN, loop through each record and save each record to a local AccessDB (execute INSERT INTO), and then perform a MoveNext on the Recordset.

For IDS, we use the onsoctcp protocol.  With the same code, the program throws an error on MoveNext.

I believe the Recordset is being closed...  I added code to perform a MoveLast/MoveFirst on the Recordset before the loop and the MoveNext does not throw an error.  This change will require me to update potentially 400 programs.

On the other hand, I can forego the code changes and instead change the DSN Cursor Behavior to "1 - Preserve" and the MoveNext does not throw an error.  My limited understanding is that if I leave it on "0 - close", this tells the driver to decide when to close the recordset instead of letting the program decide.

The only documentation I could find for Cursor Behavior doesn't explain the values:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm

So... what are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?

Based on the research below, I feel confident that I can recommend to change the Cursor Behavior to Preserve, though at a potential performance hit.  I believe the performance loss with Preserve is minimal compared to the potential performance hit on a large recordset with code that performs a .MoveLast/.MoveFirst to populate the recordset.

Useless info From IBM:
Flag for cursor behavior when a commit or rollback transaction is called
Possible values are:
•   0=close cursor
•   1=preserve cursor
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm

Performance risks From DataDirect for ODBC:
Purpose
Determines whether cursors will be preserved or closed at the end of transactions.
Valid Values
0 | 1
Behavior
If set to 1 (Enabled), cursors are held at their current position when transactions end. This value may slow the performance of your database operations.
If set to set to 0 (Disabled), cursors are closed at the end of transactions.

http://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/cursor-behavior.html

Performance risks From Pace University for connecting to Informix for administering ColdFusion Server:
Preserve cursors at the end of each transaction -- Determines whether cursors will be preserved or closed at the end of each transaction. Enable this attribute if you want cursors to be held at the current position when the transaction ends. Enabling this option may impact the performance of your database operations.
http://csis.pace.edu/~ctappert/cs615-02/support/Help/Administering_ColdFusion_Server/admin0416.htm

Required Preserve For ArcGIS (popular software):
Cursor Behavior must be set to 1 - Preserve because ArcGIS expects Informix cursors to remain open and active following a database COMMIT operation.
Setting the Cursor Behavior to 0 - Close will have undesirable effects during data insertions and updates.
http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-informix/set-odbc-informix.htm

Required Preserve For TOTVS DBACCESS (unknown software):
… determines the behavior of the database in relation to the automatic closing of cursors when instructions COMMIT WORK or ROLLBACK WORK are submitted.
If the configuration is not proper [during Fetch operations], Cursors may return EOF before the end of data recovery

http://tdn.totvs.com/display/tecen/DBAccess+-+Informix+-+ODBC+-+Cursor+Behavior


How do I know what requires DAC? Playing around with SQL Server, I came across a semi-cryptic error. Essentially, I only need to log into DAC. Is there any general advice for when DAC is needed? How do I know I need it to access sys.sysobjvalues without googling for the error message?

What all does DAC provide access to?

DAC (Dedicated Admin Connection) is (a metadata tool) only use for troubleshooting purposes. This is used by SQL Server DBA as a last resort if you can't access or connect to SQL Server. There are some cases wherein a process or someone tries to use all your system memory and CPU and nobody can use SQL Server (no new processes can connect).
DAC was built in SQL Server 2005 because prior to SQL Server 2005, if we can't access/connect to SQL Server, we either kill all the sessions and restart SQL Server. In SQL Server 2005, Microsoft decided to create DAC for troubleshooting purpose (backdoor).
When we connect as DAC (special connection), we got dedicated processor (scheduler) and a reserved memory to run a simple queries. We need a sysadmin permission to connect. We can only connect locally by logging in to the local server and connect (except when you need to troubleshoot cluster or AGs that needs remote DAC). Only 1 session that can connect as DAC in SQL Server and no one can connect as DAC until we disconnect our DAC session.
We can check if someone is using DAC by querying:SELECT dese.session_id
FROM sys.tcp_endpoints as te
JOIN sys.dm_exec_sessions as dese ON te.endpoint_id = dese.endpoint_id
WHERE te.name ='Dedicated Admin Connection';

What can we do when we are connected as DAC:

access to hidden system tables (like sys.sysobjvalues)
run simple queries for troubleshooting
advanced troubleshooting (like repairing corrupt system table pages)

What we can't do:

run complex queries (parallelism is prohibited)
backup/restore operation

We can't query systems tables in normal connection.
Try SELECT * FROM sys.objects WHERE type_desc='SYSTEM_TABLE';. These are the tables that you can't query in a normal connection.

When we are connected as DAC, you can access systems table (eg. sys.sysrscols)

Lastly, as a best practice always always disconnect the DAC connection after use.
I would recommend using SSMS and read the book about internals of SQL Server and Troubleshooting SQL Server. It's not SQL Server 2017 specific but most of the concepts are still applies to current version of SQL Server.
From Microsoft:

Important:
The system base tables are used only within the SQL Server Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.
Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.


Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables? Scenario

I have a database that contains all of our customer data, which is separated by
Schemas. For example:

Customer1 = Schema1,
Customer2 = Schema2,
Customer3 = Schema3,
etc...

Sometimes we get an issue where a Date value in a table is sometimes NULL. This table is contained in every Schema, but can sometimes be called 'Tasks', 'Tasks_NEW' or 'Task_NEW' (got to love consistency).

I am looking for a way that I can dynamically run a SELECT statement that will return a count of all NULL Date values, across all Tables and all Schemas.

So far, I have done this in order to put the Schema, plus the Table into a TempTable;

CREATE TABLE #TASK_TABLES
(   ID      INT IDENTITY(1,1),
    TenantID        INT,
    TableName       NVARCHAR(255)   )

INSERT INTO #TASK_TABLES
SELECT TABLE_SCHEMA, TABLE_NAME
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

Now that I have this information, I want to cycle through it with a SELECT like this;

SELECT Schema, COUNT(Date)
FROM [Dynamic].[Schema].[Table] (NOLOCK)
WHERE DATE IS NULL

Sadly, I don't fully know how to write Dynamic SQL as I'm still learning, but I was hoping that someone might be able to point me into the right direction?

Below is self explanatory code. Alternatively you can use sp_ineachdb (written by Aaron Bertrand).

 set nocount on
  -- create temp table ..
 create table #commandsToRun (currentDB sysname, schemaName sysname, TableName sysname, columnName sysname, datatype sysname, commandToRun nvarchar(max))
 insert into #commandsToRun
SELECT
        db_name() as CurrentDB, SCHEMA_NAME(tbl.schema_id) as SchemaName, tbl.name AS TableName,
         c.name AS ColumnName, tp.name as DataType,
         N'select count('+c.name+') as TotalDateColumnAsNULL, '
                    + ''''+SCHEMA_NAME(tbl.schema_id)+'''' + ' as  SchemaName, '
                    + ''''+tbl.name + ''''+ ' AS TableName' + ''
                    +' from '+ db_name() +'.' + SCHEMA_NAME(tbl.schema_id)
                    +'.'+ tbl.name +' with (nolock) where '
                    +c.name+' is NULL'as CommandToRun
FROM
  sys.columns c
JOIN sys.tables tbl ON
  c.object_id = tbl.object_id
JOIN sys.types tp on c.user_type_id = tp.user_type_id
WHERE
  ( c.name = 'Date' -- filter the column that you want to check
  or tp.name = 'datetime' -- filter the datatype if you dont know the column names
  )
-- check the contents of the table
select * from #commandsToRun

-- generate exec() to run the sql generated in the CommandToRun

declare @sqltext nvarchar(max) = N''

select  @sqltext += N'exec ('''+replace(CommandToRun,'''','''''')+'''); '
from #commandsToRun

-- uncomment below to run the sql generated.
-- exec(@sqltext)


Providing production MySQL access to outside company We have one main production MySQL database with a read replica. This database has some sensitive data from clients that we need to protect.

My goal is to provide access to an external Business Intelligence reporting tool. As with all tools like this, they all say "we have great security, and we never store anything that we don't need, etc." I am still paranoid.

So what I am considering creating a bunch of views on the replica database (read-only) which leave out sensitive data and provide the minimum information that they need. After that, I will provision a new user on the database that only has access to those views. This way they only see the data that I want to give them and it's already read-only so nothing can be modified.

My question is, is this a good way to go about this considering my database has sensitive client information? In general, is this a relatively secure approach?

Your approach seems to be sound. However, I am unsure of what real business intelligence can be gleaned if the reporting is made off of incomplete data. Create a dedicated user with limited privileges (IE No writing/dropping allowed). There is always a tradeoff of information security and information quality.

Perhaps it would be best to understand what you're hoping to accomplish with this reporting and identify what sensitive info can be removed.


Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837) I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;

alter system set memory_max_target = 512M scope = spfile;

Which I guess it did, since I no longer can start the Oracle instance in question. This is the error I get when trying to connect as a non-DBA-user;

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

When connecting as SYSDBA I get the following;

Connected to an inactive instance.
SQL>startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>alter system set memory_max_target = 2G scope = spfile;
ORA-01034: ORACLE not available

("Connected to an inactive instance." is my translation of a localized error message, might not be exactly correct)
So, I'm in a catch 22 situation, I can't change the memory parameters without starting up, and I can't start up.

Is there a way to change these parameters offline?

If you start your instance using a server parameter file (a binary version of initialization parameter file, spfile), you can extract the initialization parameters to plain-text initialization parameter file (pfile), alter them, and then start your instance with modified memory parameters.

sql> create pfile='myinit.ora' from spfile='spfileORCL.ora';

Modify your memory-related parameters in myinit.ora and start your instance using this new configuration:

sql> startup pfile='myinit.ora';

You can then save your settings back to an spfile with the following command:

sql> create spfile='spfileORCL.ora' from pfile='myinit.ora';

And start your database as usual:

sql> startup;


Max and group by query help We have query as below. What we need is to select particular training with latest date and only single data but now when we run the query below we get more then one results.

SELECT tblTraining.clientTrainingID AS lastID, MAX( tblTraining.trainingDate ) AS lastDate
FROM tblTraining
WHERE tblTraining.trainedID =37
GROUP BY tblTraining.clientTrainingID

Try

SELECT
  tblTraining.clientTrainingID AS lastID,
  MAX( tblTraining.trainingDate ) AS lastDate
 FROM tblTraining
 WHERE tblTraining.trainedID =37
 GROUP BY tblTraining.clientTrainingID
 ORDER BY tblTraining.clientTrainingID desc
 LIMIT 1

This will order the max() and limit the result to the first line being the highest value.


Why can't unused columns and tables be ignored in a recursive CTE Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and joined tables are queried, even if not necessary. Why is that? Can I help SQL Server to ignore unused columns/tables here?
Example:
;WITH cte AS
(
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    WHERE cn.ParentId IS NULL
    UNION ALL
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    JOIN cte ON cte.CatalogNodeId = cn.ParentId
)
SELECT CatalogNodeId FROM cte

This is a simple recursive CTE resolving a hierarchical parent-child structure, adding a localized name to each node.
Even though not necessary, the execution plan shows that column CatalogNodeType is retrieved and  dbo.LocalizedCatalogNodes is joined for each iteration.
Now comment out the UNION ALL part making it a non-recursive CTE, and the execution plan suddenly consists of just two steps, not containing column CatalogNodeType or table dbo.LocalizedCatalogNodes.

It's just a matter of order of operations, which is dependent on what the query optimizer thinks is going to be the most efficient execution plan. Yes, we can obviously see that in the end, the final SELECT list only consists of the CatalogNodeId column, and therefore the other columns of the CTE don't matter, so Microsoft probably could've implemented recursive CTEs to be a little bit better with their available execution plans in this regard. But even easier it is for the developer to recognize the lack of necessity for those columns in the recursive CTE, and therefore can be removed from the query, which is generally best practice anyway.


Create copy of Azure SQL Database using SQL Server Management Studio When running the following query on an Azure SQL Database "Master" in SQL Server Management Studio:

CREATE DATABASE myDbBackup AS COPY OF myDb;

The following error is produced:

Msg 40818, Level 16, State 1, Line 0
Operation on database 'myDb' failed because there are alter operations pending on the database. Try again after the pending operations have completed.

Any guidance on how to resolve this and end all alter operations that are pending so I can go through with the copy? There is only one other person using this DB and neither he nor I are running a query. I even went so far as to revoke the firewall permissions to the server.. not even this worked. Any thoughts?

Try reviewing DMVs sys.dm_database_copies and sys.dm_operations_status which are Azure SQL Database-only, and meant for reviewing progress on these type of operations eg

SELECT *
FROM sys.dm_operation_status

SELECT *
FROM sys.dm_database_copies

Review the state_desc and error_desc columns.  Post the output here if required.


How to move up not null alphabetically sorted values in MySQL? I have a table like this:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Zoe   |NULL  |NULL   |NULL  |
   |Julia |NULL  |NULL   |NULL  |
   |NULL  |Ashley|NULL   |NULL  |
   |NULL  |Belvet|NULL   |NULL  |
   |NULL  |Maria |NULL   |NULL  |
   |NULL  |NULL  |Mark   |NULL  |
   |NULL  |NULL  |Jane   |NULL  |
   |NULL  |NULL  |NULL   |Eve   |
   |NULL  |NULL  |NULL   |Jenny |
   |NULL  |NULL  |NULL   |Ketty |

How can I order alphabetically every single column in a way that the NULL values are pushed on the bottom, like this:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Julia |Ashley|Jane   |Eve   |
   |Zoe   |Belvet|Mark   |Jenny |
   |NULL  |Maria |NULL   |Ketty |

?

What you seem to want is something rather unusuarl (at least from my perspective), where the concept of row is no longer important, and you care only about columns, ignoring from which row its values came from. This is a bit tricky, because SQL understands columns together with rows. So you need, somehow, to deconstruct your tables and reconstruct them later on.

First thing we will do is have a different table (result of a subquery) with two columns: a row_number() and one NameX:

For every column, we will do something like:

-- First column
SELECT
     @rownum := @rownum + 1 AS rank,
     Name1
FROM
    t,  (SELECT @rownum := 0) r
ORDER BY
    -- 'zzzzzzz' = value larger than any realistically one
    -- in column Name1. Change if necessary.
    COALESCE(Name1, 'zzzzzzzzzz'),

... and that would result in:

rank | Name1
---: | :----
   1 | Julia
   2 | Zoe
   3 | null
   4 | null
   5 | null
   6 | null
   7 | null
   8 | null
   9 | null
  10 | null

This is one deconstruction (to put it a name).

NOTE: @rownum := @rownum + 1 is equivalent to using ROW_NUMBER() in stanrdard SQL. See ROW_NUMBER() in MySQL. MySQL doesn't have a row_number() function, but lets you instead use variables in your queries (See MySQL 5.7 Reference Manual  /  Language Structure  /  User-Defined Variables / 9.4 User-Defined Variables)

With this in mind, we then need to do:

Repeat 4 times, for Name1 .. Name4, to get actually 4 (virtual) tables. We will have actually deconstructed the original table.
We have now 4 (virtual) tables with a common column (rank) and with the same number of rows as the original table. We JOIN them together on this common column.
We get rid of the rows that are NULL end to end by adding a WHERE NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)

The full query will then be:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT   @rownum1 := @rownum1 + 1 AS rank, Name1
    FROM      t, (SELECT @rownum1 := 0) AS r1
    ORDER BY  coalesce(Name1, 'zzzzzzzzz')
    )  c1
    JOIN
    (SELECT   @rownum2 := @rownum2 + 1 AS rank, Name2
    FROM      t, (SELECT @rownum2 := 0) AS r2
    ORDER BY  coalesce(Name2, 'zzzzzzzzz')
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT   @rownum3 := @rownum3 + 1 AS rank, Name3
    FROM      t, (SELECT @rownum3 := 0) AS r3
    ORDER BY  coalesce(Name3, 'zzzzzzzzz')
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT   @rownum4 := @rownum4 + 1 AS rank, Name4
    FROM      t, (SELECT @rownum4 := 0) AS r4
    ORDER BY  coalesce(Name4, 'zzzzzzzzz')
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)

And this will give you your intended result:

Name1 | Name2  | Name3 | Name4
:---- | :----- | :---- | :----
Julia | Ashley | Jane  | Eve
Zoe   | Belvet | Mark  | Jenny
null  | Maria  | null  | Ketty

You can check everything together at dbfiddle here

If you're using MariaDB 10.2 or other database that knows of WINDOW functions, you can simplify this query to:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT Name1, row_number() over (order by coalesce(Name1, 'zzzzzzzzz')) AS rank
    FROM t
    )  c1
    JOIN
    (SELECT Name2, row_number() over (order by coalesce(Name2, 'zzzzzzzzz')) AS rank
    FROM t
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT Name3, row_number() over (order by coalesce(Name3, 'zzzzzzzzz')) AS rank
    FROM t
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT Name4, row_number() over (order by coalesce(Name4, 'zzzzzzzzz')) AS rank
    FROM t
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)

and you will get the same result.

dbfiddle here

You can even play to your advantage the fact that MariaDB sorts NULLS LAST, and ignore the COALESCE:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT Name1, row_number() over (order by Name1) AS rank
    FROM t
    )  c1
    JOIN
    (SELECT Name2, row_number() over (order by Name2) AS rank
    FROM t
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT Name3, row_number() over (order by Name3) AS rank
    FROM t
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT Name4, row_number() over (order by Name4) AS rank
    FROM t
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL);

dbfiddle here

NOTE: The execution plans are identical, so the last two queries are expected to take about the same time to perform.


SQL Server Integration Services 2012 - Legacy deployment I'm currently reading a lot about SSIS and I have read that there are now two models for deploying a solution : the one using "Projects" and the one using "Packages".
Starting SQL Server 2012, a database generally called SSISDB can be created as the SSIS catalog.
My question is the following: let's say that I have created the SSISDB but that I will use the legacy deployment model, will the package I deployed be found in the SSISDB ?

Thanks in advance.

Jeff.

No*, it won't.

The Package Deployment Model, if deployed to the database, will store individual packages in msdb.dbo.syspackages90 (SQL Server 2005) and msdb.dbo.sysssispackages (SQL Server 2008+)

The Project Deployment Model, deploys to the SSISDB. There it takes the form of Folders/Projects/Packages which are stored in

SSISDB.catalog.folders
SSISDB.catalog.projects
SSISDB.catalog.packages

*With the 2016 release of SQL Server, we now have incremental package deployment for SSIS projects using the project deployment model. Based on an early release candidate at least, this means you can keep your SSIS projects in the package deployment model yet deploy to the SSISDB to take advantage of the improved management, logging, configuration and execution there.


Solving Long Running Query, number of executions problem I have the following query:

ALTER PROCEDURE [dbo].[spSearchClient]
    @SearchWords NVARCHAR(MAX) = NULL,
    @LowerDate DATE = NULL,
    @UpperDate DATE = NULL,
    @UserCreated nvarchar(450)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserAccountID smallint
    DECLARE @SearchWordCount int

    SELECT @UserAccountID = dbo.fnGetUserAccountID(@UserCreated)

    CREATE TABLE #SearchWords
    (
        ID int IDENTITY(1,1),
        Word NVARCHAR(50)
    )

    INSERT INTO #SearchWords
    (
        Word
    )
    SELECT
        value
    FROM
        STRING_SPLIT(@SearchWords, ' ')
    WHERE
        TRIM(value) <> ''

    SELECT @SearchWordCount = @@ROWCOUNT;

    SELECT
        C.ClientID,
        C.FirstName,
        C.LastName,
        C.FullName,
        C.DateOfBirth,
        G.GenderName,
        G.GenderIcon,
        C.VerificationCode,
        V.LastVisitDate
    FROM
        Client C
    OUTER APPLY (
        SELECT MAX(StartDate) AS LastVisitDate
        FROM Visit AS V
        WHERE C.ClientID = V.ClientID
    ) AS V
    INNER JOIN LookUp.Gender G on
        C.GenderID = G.GenderID
    WHERE
        (
            EXISTS( -- if we have words
                    SELECT *
                    FROM #SearchWords s
                    WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                       OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                       OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
                )
            OR @SearchWordCount = 0 --if we don't have words
        )
        AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)

    INSERT INTO UserSearchLog
    (
        SearchWords,
        LowerDate,
        UpperDate,
        SearchResultsCount,
        UserCreated
    )
    VALUES
    (
        @SearchWords,
        @LowerDate,
        @UpperDate,
        @@ROWCOUNT,
        @UserAccountID
    )

    DROP TABLE #SearchWords
END

The execution plan is https://www.brentozar.com/pastetheplan/?id=BkGzfUeHz

The query works how it should but takes a good 3-7 seconds to run and it seems to all be due to the following which has an execution for every row in the Client table:

WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR @SearchWordCount = 0 --if we don't have words
    )

Wondering if anyone knew of a better more effective way of doing this that would be less time consuming?

If some sample data would be useful, please let me know.

There appears to be a problem with the server that will prevent you from getting good performance with that query, even when @SearchWordCount = 0. Consider the details for node id 15:

This plan executes in row mode and this operator is at the end of the branch, so you can attribute all 20 ms of CPU time and 3962 ms of elapsed time to the index seek on [Visit].[IDX_Visit_ClientID]. When executing this query you spent almost four seconds waiting for something other than CPU work. Looking at the wait stats for the select operator provides a valuable clue:

Almost four seconds waiting on PAGEIOLATCH_SH. We can get more information about the IO that was actually done by looking at the RunTimeInformation in the XML:

<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="8155" ActualRowsRead="8155"
    Batches="0" ActualEndOfScans="211" ActualExecutions="211"
    ActualExecutionMode="Row" ActualElapsedms="3962" ActualCPUms="20"
    ActualScans="211" ActualLogicalReads="726" ActualPhysicalReads="42"
    ActualReadAheads="8" ActualLobLogicalReads="0"
    ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>

SQL Server only needed to do 50 physical reads for the index seek, yet it waited almost four seconds to do them. You're reading data at a rate of 100 KB per second. There could be a problem with the server configuration or perhaps the server is just overloaded. Paul Randal published detailed instructions here for determining the root cause of the problem and how to fix it. Good luck.

On the subject of the pattern matching, SQL Server took about a third of a second to do that with just a single row in the temp table. That part of the query could become a bottleneck as the number of rows increases in the temp table. But you won't get good performance by changing that part of the query without resolving the issue with PAGEIOLATCH_SH waits first.


MySQL Collation utf8_unicode differences But I've been reading up on the importance of MySQL Collation and what I have learned so far regarding compatibility and accuracy is these 4 seem to be my best bet.

utf8_unicode_ci
utf8_unicode_520_ci
utf8mb4_unicode_ci
utf8mb4_unicode_520_ci

From my understanding uft8mb4 would be good for character with mutli language (character) support (japanese for example). uf8 only supports 3 bytes while uf8mb4 supports 4 bytes. So it sounds like the obvious choice would be uf8mb4, but the catch seems to be that you have a length limit (Damn it! I want my cake and eat it too), which is a little concern (I think).

Then you take into account about the 520 standard; which offers more, from what little I could find on it. But that is of course the issue, I could find very little about it. Only that people say it's an improvement, yet being very vague on how that is.

I do want the most I can get with as few restrictions as possible... I figured someone here may know a thing or two. The official MySQL site wasn't as informative as I had hoped it was.

Of the 4 which would offer the most compatibility, accuracy, and the most storage length? Also what truly is the big difference between the 502 standard?

Unicode collation names may include a version number to indicate the version of the Unicode Collation Algorithm (UCA) on which the collation is based. UCA-based collations without a version number in the name use the version-4.0.0 UCA weight keys. A collation name such as utf8_unicode_520_ci is based on UCA 5.2.0 weight keys.

See https://dev.mysql.com/doc/refman/5.6/en/charset-collation-names.html.


Mongos down when I shutdown one of three servers I deploy 3 server of mongodb, layout is

node-01:

mongos
config server 1
replication server 1

node-02:

mongos
config server 2
replication server 2

node-03:

config server 3
arbiter

After deployment I found that if I shutdown node-01 or node-02, mongos fail immediately.

I think it's because replication server down will require metadata change, but I didn't find information from google.

Here is a part of log from mongos:

> 2014-05-21T18:57:03.888+0800 [Balancer] reconnect 10.162.54.97:28002 (10.162.54.97) failed failed couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:08.892+0800 [Balancer] scoped connection to 10.161.236.222:28001,10.162.54.97:28002,10.132.42.79:28003 not being returned to the pool
> 2014-05-21T18:57:08.892+0800 [Balancer] caught exception while doing balance: error checking clock skew of cluster 10.161.236.222:28001,10.162.54.97:28002,10.132.42.79:28003 :: caused by :: 13647 could not get status from server 10.162.54.97:28002 in cluster 10.162.54.97:28002 to check time :: caused by :: 11002 socket exception [CONNECT_ERROR] server [10.162.54.97:28002] connection pool error: couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:10.176+0800 [ReplicaSetMonitorWatcher] warning: Failed to connect to 10.162.54.97:27017, reason: errno:115 Operation now in progress
> 2014-05-21T18:57:12.693+0800 warning:  couldn't check dbhash on config server 10.162.54.97:28002 :: caused by :: 11002 socket exception [CONNECT_ERROR] server [10.162.54.97:28002] connection pool error: couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:19.895+0800 [Balancer] SyncClusterConnection connecting to [10.161.236.222:28001]
> 2014-05-21T18:57:19.895+0800 [Balancer] SyncClusterConnection connecting to [10.162.54.97:28002]
> 2014-05-21T18:57:24.896+0800 [Balancer] SyncClusterConnection connect fail to: 10.162.54.97:28002 errmsg: couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:24.896+0800 [Balancer] SyncClusterConnection connecting to [10.132.42.79:28003]
> 2014-05-21T18:57:24.899+0800 [Balancer] trying reconnect to 10.162.54.97:28002 (10.162.54.97) failed
> 2014-05-21T18:57:25.176+0800 [ReplicaSetMonitorWatcher] warning: Failed to connect to 10.162.54.97:27017, reason: errno:115 Operation now in progress
> 2014-05-21T18:57:29.899+0800 [Balancer] reconnect 10.162.54.97:28002 (10.162.54.97) failed failed couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed

When you say "shutdown node-01 or node-02" what are you actually shutting down? (mongod service, config server service, the host?) Please also state what node you are shutting down in the example you gave, as well as the which node you took the mongos log from.

You can run your config server on the same host as your replica set. However, you may wish to run your config servers on separate hosts. That way, if a replica set host goes down, you don't loose a config server also. If you do loose a config server, your cluster won't be able to migrate chunk data on sharded collections. (this is what is happening in your logs, the balancer will remain disabled until your config server comes back on line.)

Note: I probably would not have apps connecting to the mongos running on the replica sets. It is fine that they are running there, admins have easy access to a mongos to connect to. But, I would run a mongos that sits outside these hosts. Otherwise a host going down in your cluster means your app, or part of it at least, is also down.

Replication is unaffected by a config server going down (just chunk migration). Do you have a sharded collection in this cluster?

Follow up:

Not sure what happened with my comment. I'll try it this way:

You can absolutely run your config servers on the same hosts as your replicas. Here is a quote from Chodorow, Kristina. MongoDB: The Definitive Guide:. Beijing: O'Reilly, 2013. page 243.

"In terms of provisioning, config servers do not need much space or many resources. A generous estimate is 1 KB of config server space per 200 MB of actual data: they really are just tables of contents. As they don't use many resources, you can deploy config servers on machines running other things, like app servers, shard mongods, or mongos processes."

Something else is going on. When you connect your mongos to the config servers, are you adding them using IP addresses or host names? What is the output of rs.conf() and rs.status() before and after shutting down the node? I notice mongos is attempting to connect on port 27017. Typically, in a sharded setup, your mongod port will be 27018 and config port will be 27019. Did you change the port numbers?

I suspect you have something misconfigured. Mongo's fail-over is pretty rock solid.


Best open source DB technology to store and query large, sparse matrices I have a large vector space model, in which each point is a multi-variate observation. In other words, I have a large number of pretty large records, mostly of ints and floats. I started with in-memory matrices, but given a large number of points (1B+) and a large number of dimensions (1K+), I soon realized that that wasn't an option.

I need to

1) make the whole thing persistent and dynamic (I want to add/update/remove points/dimensions easily)

2) query the VSM efficiently, performing similarity queries (given a point, find top K similar points) and range queries (find all points within a subspace).

Based on some reading, it seems that there isn't an off-the-shelf package to do this. What would you use to implement this kind of scenario? Dumping each point as a row in a relational db didn't work because the large number of dimensions. Even after normalization, the relational db was clearly too slow to perform similarity queries.

Moreover, I need to compute the dot product on chunks of the matrix.
What technology would you recommend in this case? For example, I have read about array databases, and OLAP cubes, but I would like to have some advice from real DBAs.

Thanks, Mulone

You should take a look at SciDB - a database designed for precisely this use case. It has as as its major driver Michael Stonebraker, a man who knows a lot about databases - it would be an exaggeration to say that he's been involved with more database systems than most people have had hot dinners, but not by much :-)!

  "including Ingres, Illustra, Cohera, StreamBase Systems, Vertica,
  VoltDB, and Paradigm4. He was previously the Chief Technical Officer
  (CTO) of Informix. He is also an editor for the book Readings in
  Database Systems".

Google "michael stonebraker scidb sparse matrix" and subsets thereof and I think that it might be a good fit for what you want.


Recursive self relational via join column Having this specific simple many to many self referential structure

An item owns other items through the joins table;
trying to retrieve the whole tree structure as json while querying a given ite.

For example, while querying the item with id 1 :

SELECT it.*, fulltree from items where id = 1;

Outputting the following :

{
  id: 1,
  title: "PARENT",
  children: [
   {
     id: 2,
     title: "LEVEL 2",
     children: [
      {
        id: 3,
        title: "LEVEL 3.1",
        children: [
          {
            id: 4,
            title: "LEVEL 4.1"
          },
          {
            id: 5,
            title: "LEVEL 4.2"
          }
        ]
      },
      {
        id: 6,
        title: "LEVEL 3.2"
      }
     ]
   }
  ]
}

I've digged into json capabilities postgres offers, managed such output by repeating nested query (simple but ugly, and limitated to the amount of repeats :/)

I've found out about recursive queries. The examples found here and there not being that simple, I'm having had times finding an entrypoint to understand it correctly and adapt those to my needs

Example Data

CREATE TABLE items (
    id    serial primary key,
    title VARCHAR(255)
);

insert into items (title) values ('PARENT');
insert into items (title) values ('LEVEL 2');
insert into items (title) values ('LEVEL 3.1');
insert into items (title) values ('LEVEL 4.1');
insert into items (title) values ('LEVEL 4.2');
insert into items (title) values ('LEVEL 3.2');

CREATE TABLE joins (
  id serial primary key,
  item_id INT,
  child_id INT
);

insert into joins (item_id, child_id) values (1,2);
insert into joins (item_id, child_id) values (2,3);
insert into joins (item_id, child_id) values (3,4);
insert into joins (item_id, child_id) values (3,5);
insert into joins (item_id, child_id) values (2,6);

I hope the example here will be simple enough to find help from experienced users; in advanced thx a lot

Here is an example query,

WITH RECURSIVE t(item_id, json) AS (
        SELECT item_id, to_jsonb(items)
        FROM items
        WHERE NOT EXISTS (
                SELECT 1
                FROM joins
                WHERE items.item_id = joins.item_id
        )
        UNION ALL
        SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
        FROM t
        JOIN joins AS j
                ON t.item_id = j.child_id
        JOIN items AS parent
                ON j.item_id = parent.item_id
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;
 item_id |             jsonb_pretty
---------+---------------------------------------
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.2",    +
         |             "item_id": 6             +
         |         }                            +
         |     }                                +
         | }
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.1",+
         |                 "item_id": 4         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }
       1 | {                                    +
         |     "title": "PARENT",               +
         |     "item_id": 1,                    +
         |     "children": {                    +
         |         "title": "LEVEL 2",          +
         |         "item_id": 2,                +
         |         "children": {                +
         |             "title": "LEVEL 3.1",    +
         |             "item_id": 3,            +
         |             "children": {            +
         |                 "title": "LEVEL 4.2",+
         |                 "item_id": 5         +
         |             }                        +
         |         }                            +
         |     }                                +
         | }
(3 rows)

Note, we're not actually merging the paths to form a completed tree. You either have to build the tree from the root node down, or the leaf nodes to the top. In this case, you'll have to merge the discrete paths. Look for a deep json merge in Javascript and tie it together with plv8

Test data

I modified your example schema a bit,

DROP TABLE joins;
CREATE TABLE items (
  item_id     serial PRIMARY KEY,
  title text
);
CREATE TABLE joins (
  id          serial PRIMARY KEY,
  item_id     int,
  child_id    int
);
INSERT INTO items (item_id,title) VALUES
  (1,'PARENT'),
  (2,'LEVEL 2'),
  (3,'LEVEL 3.1'),
  (4,'LEVEL 4.1'),
  (5,'LEVEL 4.2'),
  (6,'LEVEL 3.2');
INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,4),
  (3,5),
  (2,6);


How to block user access to perform database application update? Where I work we have a SQL Server database (Microsoft SQL Server 2008 R2) that serves as the back-end with two different user interfaces, a .NET web interface and a FoxPro interface.

Every month we need to apply updates to both the Web and FoxPro clients.  Before doing this, we are advised to make sure that no one is accessing the database during the update process.

What it the easiest way to prevent access to the database while we update it? Follow-up question: What is the best way to prevent access while we update it?

There are couple of ways that you can restrict access to a database :

Using LOGON Trigger - but only temporarily (enable before upgrade and then disable it). Refer to my answer
here for more details including a script.
You can shutdown IIS on the webservers so that no connections are made using the application. This is called "Applicaiton downtime"
Keep database in single user mode using (Note this will be risky as if there is any other connection to the database then you might
end up waiting or refused connection.)

alter database databasename
set single_user with rollback immediately

You are better off using Option 2 as a safe and planned upgrade during your maintenance window.

EDIT:

Restricted user - only users with dbo rights on database allowed (e.g. db_owner,
dbcreator, sysadmin). This means that multiple users can still be logged into the database, as long as they are DBO.

Single user - only one connection allowed i.e. first come, first served.

Due to the fact that Single user will be first come, first served -- it will be more risky in case of error or somehow your connections gets terminated.

When dealing with Logon Trigger, as @AaronBertrand pointed out, that it will not work for existing sessions, but you can over come that by first killing all the sessions and then enabling the trigger so that all the new incoming connections have to go through the trigger.

I can't think of any other way of restricting the connections to the database.


I need to hide an open Access table from users For application startup I had to open a table as part of some DAO.recordSet code.

 DoCmd.OpenTable "User", acNormal, acReadOnly

On running user tests, I want to distribute the desktop application as a run-time file (.accdr) but the access table is visible. All I can find is how to hide navigation area objects.

I, of course, do not want the table visible to users, so my question is how do I hide it?

This article at TechRepublic offers two approaches.
One is to set the table as hidden.

Right-click the object in the Database window.
Choose Properties.
Click the Hidden option.

The other is to rename the table.

Another way to hide a table is to add the prefix Usys to the table's name.

Both aren't really secure, as the object is still accessible, but it's not visible at least.


Rolling SUM with a 30 day range I am using SQL Server 2012 and have a db with around 2 million rows. I need to figure out how to calculate a rolling sum over a 30 day range and return any results over a set amount (amt_pur). These are transaction orders so the id number (ID_NO) is the same but transaction date(TD) varies by minutes to many years at times. So if a customer ordered 10 items within any 30 day period I need those results. I tried something like this and many googled variations:

select id_no, td,
sum(amt_pur) as amtpur from db1 where td between td and dateadd(day,30,td)
group by id_no, td, amt_pur having sum(amt_pur)>10
order by amtpur desc

This does not get me rolling type calculation results. I have a specific ID I can verify this with so I know I'm not getting the right ones. Please help!

I haven't tested this extensively but I think it can do what you need. Here I assume that ID_NO is a customer identifier, TD is the date of the order, and AMT_PUR represents the amount ordered. You want all of the rows for when a customer has ordered 10 or more of anything over a 30 day date range starting with that day.

Here is a little bit of test data:

CREATE TABLE DB1 (
ID_NO VARCHAR(10) NULL,
TD DATETIME NULL,
AMT_PUR INTEGER NULL
);

BEGIN TRANSACTION;
INSERT INTO DB1 VALUES ('A', '01/01/2016', 3);
INSERT INTO DB1 VALUES ('A', '01/05/2016', 1);
INSERT INTO DB1 VALUES ('A', '01/09/2016', 2);
INSERT INTO DB1 VALUES ('A', '02/01/2016', 7);
INSERT INTO DB1 VALUES ('A', '02/02/2016', 2);
INSERT INTO DB1 VALUES ('A', '03/01/2016', 2);
INSERT INTO DB1 VALUES ('A', '03/04/2016', 1);
INSERT INTO DB1 VALUES ('B', '02/01/2016', 3);
INSERT INTO DB1 VALUES ('B', '02/28/2016', 6);
INSERT INTO DB1 VALUES ('B', '03/05/2016', 3);
INSERT INTO DB1 VALUES ('B', '03/28/2016', 6);
COMMIT TRANSACTION;

Suppose that you just looped over each ID_NO in reverse TD order and kept track of the running sum of AMT_PUR. You couldn't just keep the rows with a running sum that's greater or equal to 10 because some of the rows previously added to the total may not be within 30 days of the current row. However, if there was a way to remove rows too far away from the current row then you could just use the running sum and you could do your calculation with a single pass over the data.

The idea behind this approach is to UNION ALL together two copies of the data. The second copy will have the AMT_PUR value reversed and will have 31 days subtracted from the original value. With the data in that format you can perform the calculation with just one pass. We can loop over the data this way using SUM() with the OVER() clause.

SELECT
ID_NO
, TD
, REVERSE_RUNNING_SUM
FROM
(
    SELECT
      ID_NO
    , TD
    , SUM(AMT_PUR) OVER (PARTITION BY ID_NO ORDER BY TD DESC, VALID_FLAG ASC) REVERSE_RUNNING_SUM
    , VALID_FLAG
    FROM
    (
        SELECT ID_NO
        , TD
        , AMT_PUR
        , 1 VALID_FLAG
        FROM db1

        UNION ALL

        SELECT ID_NO
        , DATEADD(DAY, -31, TD) TD
        , -1 * AMT_PUR
        , -1 VALID_FLAG
        FROM db1
    ) t
) tt
WHERE tt.VALID_FLAG = 1
AND tt.REVERSE_RUNNING_SUM >= 10;

Minor update: It would probably be more efficient to CROSS JOIN db1 to a two row table instead of using UNION ALL, but I'm going to keep the UNION ALL approach in my code because I think that's easier to understand.


Quick way to validate two tables against each other We're doing an ETL process.  When all is said and done there are a bunch of tables that should be identical.  What is the quickest way to verify that those tables (on two different servers) are in fact identical.  I'm talking both schema and data.

Can I do a hash on the table it's self like I would be able to on an individual file or filegroup - to compare one to the other.  We have Red-Gate data compare but since the tables in question contain millions of rows each I'd like something a little more performant.

One approach that intrigues me is this creative use of the union statement.  But, I'd like to explore the hash idea a little further if possible.

POST ANSWER UPDATE

For any future vistors... here is the exact approach I ended up taking.  It worked so well we're doing it on every table in each database.  Thanks to answers below for pointing me in the right direction.

CREATE PROCEDURE [dbo].[usp_DatabaseValidation]
    @TableName varchar(50)

AS
BEGIN

    SET NOCOUNT ON;

    -- parameter = if no table name was passed do them all, otherwise just check the one

    -- create a temp table that lists all tables in target database

    CREATE TABLE #ChkSumTargetTables ([fullname] varchar(250), [name] varchar(50), chksum int);
    INSERT INTO #ChkSumTargetTables ([fullname], [name], [chksum])
        SELECT DISTINCT
            '[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM MyDatabase.sys.tables T
            INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id
        WHERE
            T.name like IsNull(@TableName,'%');

    -- create a temp table that lists all tables in source database

    CREATE TABLE #ChkSumSourceTables ([fullname] varchar(250), [name] varchar(50), chksum int)
    INSERT INTO #ChkSumSourceTables ([fullname], [name], [chksum])
        SELECT DISTINCT
            '[MyLinkedServer].[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM [MyLinkedServer].[MyDatabase].sys.tables T
            INNER JOIN [MyLinkedServer].[MyDatabase].sys.schemas S ON
            T.schema_id = S.schema_id
        WHERE
            T.name like IsNull(@TableName,'%');;

    -- build a dynamic sql statement to populate temp tables with the checksums of each table

    DECLARE @TargetStmt VARCHAR(MAX)
    SELECT  @TargetStmt = COALESCE(@TargetStmt + ';', '')
            + 'UPDATE #ChkSumTargetTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + T.FullName + ') WHERE [name] = ''' + T.Name + ''''
    FROM    #ChkSumTargetTables T

    SELECT  @TargetStmt

    DECLARE @SourceStmt VARCHAR(MAX)
    SELECT  @SourceStmt = COALESCE(@SourceStmt + ';', '')
            + 'UPDATE #ChkSumSourceTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + S.FullName + ') WHERE [name] = ''' + S.Name + ''''
    FROM    #ChkSumSourceTables S

    -- execute dynamic statements - populate temp tables with checksums

    EXEC (@TargetStmt);
    EXEC (@SourceStmt);

    --compare the two databases to find any checksums that are different

    SELECT  TT.FullName AS [TABLES WHOSE CHECKSUM DOES NOT MATCH]
    FROM #ChkSumTargetTables TT
    LEFT JOIN #ChkSumSourceTables ST ON TT.Name = ST.Name
    WHERE IsNull(ST.chksum,0) <> IsNull(TT.chksum,0)

    --drop the temp tables from the tempdb

    DROP TABLE #ChkSumTargetTables;
    DROP TABLE #ChkSumSourceTables;

END

Here's what I've done before:

(SELECT 'TableA', * FROM TableA
EXCEPT
SELECT 'TableA', * FROM TableB)
UNION ALL
(SELECT 'TableB', * FROM TableB
EXCEPT
SELECT 'TableB', * FROM TableA)

It's worked well enough on tables that are about 1,000,000 rows, but I'm not sure how well that would work on extremely large tables.

Added:

I've run the query against my system which compares two tables with 21 fields of regular types in two different databases attached to the same server running SQL Server 2005.  The table has about 3 million rows, and there's about 25000 rows different.  The primary key on the table is weird, however, as it's a composite key of 10 fields (it's an audit table).

The execution plans for the queries has a total cost of 184.25879 for UNION and 184.22983 for UNION ALL.  The tree cost only differs on the last step before returning rows, the concatenation.

Actually executing either query takes about 42s plus about 3s to actually transmit the rows. The time between the two queries is identical.

Second Addition:

This is actually extremely fast, each one running against 3 million rows in about 2.5s:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB

If the results of those don't match, you know the tables are different.  However, if the results do match, you're not guaranteed that the tables are identical because of the [highly unlikely] chance of checksum collisions.

I'm not sure how datatype changes between tables would affect this calculation.  I would run the query against the system views or information_schema views.

I tried the query against another table with 5 million rows and that one ran in about 5s, so it appears to be largely O(n).


Multiple THEN outputs on a PostgresQL query Helllo there,
I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis.

As a such, I'd like to use a CASE expression with an input condition and two assignments if the condition is satisfied.

Something like

SELECT
    CASE
        WHEN t."page" ~* '...' THEN t.type = 'Product', t.something = 'something else here'
END AS "page_type"

Reading through the documentation, but also checking for several examples I couldn't see this doable. Am I missing something?

Assuming is not, the only thing I can think of is using a function that returns an array or something similar.
Would that be an a possible approach? If so, any example I can be pointed out to learn a decent DDL pattern?

Thanks

UPDATE To support @a_horse_with_no_name answer

SELECT *
FROM "V_2019-06-10_to_2019-06-20_GSC" t,
            (
                SELECT
                    Regexp_replace(t."page", '...', '\1') as node
                    FROM "V_2019-06-10_to_2019-06-20_GSC" i
                    WHERE i.page = t.page
            ) AS "IFs"

The above generates an error in referencing the t table.
If I go for a CROSS JOIN LATERAL, the query works, but then the GROUP BY I have at the end of the outer query cannot dedup anymore the results

UPDATE 2

Here's a simplification of the query. It's of course much more complex than this, but hopefully it suffice.

SELECT -- t."page",
    "URL".node, t.area, "URL".page_type,
    CASE
        WHEN "URL".page_type = 'ABC' THEN <here I need my value>
        ELSE ''
    END AS "Node_Type",
    FROM "V_2019-06-10_to_2019-06-20_GSC" t
    CROSS JOIN LATERAL (
        SELECT
            Regexp_replace(t."page", '...', '\1\2\3\4\5\6') as node,
            CASE
                WHEN t."page" ~* '...' THEN 'ABC'
                WHEN t."page" ~* 'filter' THEN 'DEF'
                ELSE 'Other'
            END AS "page_type"
    ) AS "URL"
    GROUP BY "URL".node, t.area, "URL".page_type
;

A URL processed by this query could look like this /level1/level2/level3/?filter=123

Idealy I'd like to:

capture level1/level2/level3 with the Regexp_replace, and I have the
regexpr working for this.
I need to identify once again whether the filter is included in the page to populate the page_type, and the case above should be ok.
capturing 123 as part of the outer CASE "Node_Type", but I cannot get this done, hence my first thought of a CASE processing two
outputs.

Is there an effective way to get this out?

A single case expression can only return a single value (=expression). So yes, you are right you can't do that. If you want to have two output columns, you need two case expressions.

SELECT CASE
         WHEN t.page ~* '...' THEN 'Product here'
         ... other outputs ....
        END AS page_type,
        CASE
         WHEN t.page ~* '...' THEN 'something else'
         ... other outputs ....
        END AS something _else
FROM ...

If you don't want to repeat the condition, you could wrap that into a derived table:

SELECT node, area, page_type,
       CASE
          WHEN page_type = 'ABC' THEN 'Product'
          WHEN page_type = 'DEF' THEN 'Something else'
          ELSE ''
       END AS "Node_Type"
from (
    SELECT area,
           Regexp_replace(t."page", '...', '\1\2\3\4\5\6') as node,
           CASE
             WHEN t.page ~* '...' THEN 'ABC'
             WHEN t.page ~* 'filter' THEN 'DEF'
             ELSE 'Other'
           END AS page_type,
           string_to_array(trim(substr(page, 1, strpos(page, '?')-1),'/'), '/') as levels,
           regexp_replace(page, '(^.*\?filter=)([0-9]+)', '\2', 'g') as filter_value
           length(t.page) as string_length
    FROM "V_2019-06-10_to_2019-06-20_GSC"
) t;

The expression string_to_array(trim(substr(page, 1, strpos(page, '?')-1),'/'), '/') will return the individual elements (levels) of the URL as array elements. '/level1/level2/level3/?filter=123' will yield and array {level1,level2,level3) and '/level1/level2/level3/level4/?filter=123' will return 4 elements. If you need to access the number of levels in the outer select, you can use e.g. cardinality(levels)

The expression regexp_replace(page, '(^.*\?filter=)([0-9]+)', '\2', 'g') will return the value of the parameter filter as a single expression (It's probably not 100% fool-proof, but URL parsing with regex is not a simple task).


Why isn't mixed-mode replication default on MySQL It is well known that mixed-mode replication was default from MySQL 5.1.12 to MySQL 5.1.28. However, this was reverted, and at the current moment, statement-based replication is default.

In the announcement that statement-based replication was default, Oracle claimed that this was only because mixed mode replication constituted a behavior change which may catch some users unaware. (See also Bug 39812.) However, MySQL 5.1 has many behavior changes, and it because the benefits of mixed-base replication so clear (I'm sure every DBA has run into a case where replication failed because a user submitted a query which didn't replicate properly and caused their databases to diverge), this doesn't seem like a particularly strong reason.

There are some mutterings that mixed replication "can provide the best of both worlds, but needs testing", but we're already on MySQL 5.1.61, and one would hope all of the bugs would have been ironed out by now. So that leaves us just rather confused. Why isn't mixed mode replication default?

I asked myself this question recently as well. At first, I looked at why row-based replication wasn't enabled by default. Obviously, row-based replication creates the possibility of IO-strain on disks by the amount of data that potentially could be written to the binary log.

Not surprisingly, this is the number one disadvantage of row-based replication the MySQL documentation mentions. A bit further down, another disadvantage would be a potential issue with performance with MyISAM:

  For tables using the MyISAM storage engine, a stronger lock is required on the slave for INSERT statements when applying them as row-based events to the binary log than when applying them as statements. This means that concurrent inserts on MyISAM tables are not supported when using row-based replication.

MyISAM is the default engine of the 5.1 series. It is not until 5.5 that InnoDB is default. This replication impact on MyISAM, to me, is a strong reason not to use RBR by default.

But your question revolves around the MIXED format. Under MIXED format, the default is to use statement-based replication until a statement is called that required row-based replication. The link above gives a nice overview of what type of logging gets performed on certain situations (safe, unsafe, row injection).

I think there are too many potential situations that would cause replication to switch to row-based replication, causing some of the performance issues noted above on non-optimal  hardware. And let's face it, MySQL has a large following of users running it on non-optimal hardware.

This situation stands out to me the most:

  If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.

Note that all statements involving temporary table in row-based logging is considered unsafe, so this could cause issues with your replication setup.

Ultimately, I think it is a good idea to have STATEMENT be the default, having to make a conscious, educated decision to switch to MIXED.


How to know if Instant File Initialization is enabled? Besides creating a new database and timing it, is there a simple way to determine whether or not Instant File Initialization is enabled/functioning?

(on Windows Server 2008 Enterprise)

Using ntrights.exe, check if the SeManageVolumePrivilege priviledge is granted. IF you prefer a 'built in' tool, use secedit /export /areas USER_RIGHTS.


SQL Server - Extremely low avg_page_space_used_in_percent for heap table While analyzing a SQL Server 2012 database bloat issue, I found that fragmentation might be a major contributor to how much space is being taken up by certain tables, so after some Googling, I'm looking into the sys.dm_db_index_physical_stats function to try to understand what's going on.  Here's my query:

SELECT
index_id,
index_type_desc,
alloc_unit_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
record_count,
page_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL , 'SAMPLED')
WHERE OBJECT_ID = 516196889
ORDER BY avg_fragmentation_in_percent DESC;

And my results:

There are 4 non-clustered indexes on the table, so I'm guessing those are what the top 4 result rows represent.  I haven't figured out why the IN_ROW_DATA portion of the heap has such low page usage (6%).  If I'm reading the results correctly, there are more pages for this part than there are rows, but max record size is less than 8kb, so I'm trying to understand how that's even possible.  How do I find out what's causing this low page usage, and what are some things I can do to fix it?

Any help greatly appreciated!

This could be simply because of some delete or update operation removing rows from the page and up till now it was never used to fill additional rows. I do not see anything worrying here.

Go ahead and rebuild the heap to get more page fullness, but please note any nonclustered indexes (NCI) on the heap would also be rebuilt. - Shanky

Using a clustered table instead of a heap will fix likely these symptoms. - Dan Guzman


Summing the count of two columns in postgres that contain the same value I want to sum the total number of occurrences of one value across two columns in the same table.
So, an example would be:

id |node1  |node2
1  |  111  |   123
2  |  122  |   124
3  |  111  |   125
4  |  122  |   111
5  |  124  |   111
6  |  126  |   111

So in this case I want to get the following result:

node   | node_count
111    |     5
122    |     2
123    |     1
124    |     2
125    |     1
126    |     1

Additionally, I want to only include the nodes that have a count > 1 so my final result would be:

node   | node_count
111    |     5
122    |     2
124    |     2

I didn't think this would work but I've tried the following on the table:

SELECT count(node1+node2), node1 as node
FROM table1
WHERE node1 = node2
GROUP BY node1
HAVING count(node1+node2) > 1;

So I then tried creating a temporary table so that I could use the WHERE clause as follows

SELECT count(table1.node1+tableTemp.node2), table1.node1
FROM table1, tableTemp
WHERE table1.node1 = tableTemp.node2
GROUP BY table1.node1
HAVING count(table1.node1+tableTemp.node2) > 1;

But this only seems to return the count of node1. I have also tried the variation of count(table1.node1) + count(tableTemp.node2) but this doesn't work. I've also tried using a combination of SUM and COUNT-sub-queries to no avail.
Can anyone point me in the correct direction? Cheers.

You can get it converting the two columns into a single column.

SELECT node, COUNT(*) as node_count
FROM
(
    SELECT id, node1 as node
    FROM   t1
    UNION
    SELECT id, node2 as node
    FROM   t1
) t2
GROUP BY node
HAVING COUNT(*) > 1
ORDER BY node;

node | node_count
---: | ---------:
 111 |          5
 122 |          2
 124 |          2

db<>fiddle here


Oracle grant select on a table I want to grant select permission on a specific table to a remote ip address I had done it before in Mysql with this:

grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

but I can't do it in Oracle 11G

You can't restrict by IP address. All you can really do is create a new user and only grant them SELECT on that one table.

create user newuser identified by 'p4ssword';
grant connect, resource to newuser;
grant select on yourtable to newuser;

It is actually possible to check the IP address a user is logging in from using a login trigger, and deny the login if the IP address is "wrong", but that is a different question.


Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE Setup:
We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users.
At the start of the process, .NET application creates a set of temp tables
during the process, .NET application calls a set of SQL queries, they look like this:

truncate #QuerySpecificTable

insert into #QuerySpecificTable
select PrimaryKey
  from Production.QuerySpecificTable
  join ...
 where ...

Issue i came across while performance tuning one specific query. ~139000 records get inserted into the temp table. This causes ~300k logical reads on the #QuerySpecificTable.

When i remove the truncate, it drops to ~300 reads on #QuerySpecificTable.
CPU statistics show drop in CPU time (~900->300ms so not sure about precision and reliability.)

The issue is, when trying to do this in SSMS, i cannot 100% reproduce the behaviour and make sure and demonstrate removing the truncate (even through it wouldnt be a problem) is a good thing to do. The biggest issue is NOT KNOWING why and what is happening here

WITH TRUNCATE (NOT ALWAYS, thats the weird part)

truncate table #test
insert into #test select top 150000 PrimKey from dbo.test

Table '#test_______________________________________________________________________ ________________________________________000000000141'. Scan count 0, logical reads 319955, physical reads 0, read-ahead reads 217, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(150000 rows affected)

DROP/CREATE

drop table if exists #test
create table #test (PrimKey integer primary key)
truncate table #test
insert into #test select top 150000 PrimKey from dbo.test

Table 'Test'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 232, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(150000 rows affected)

The data in there is not really dropped, first of all. It has to do with how Temdb is implemented

Also, Microsoft has a great blog on TempDB and contention that addresses the following points:

Tempdb - Files, Trace flags, updates - Microsoft Blogs

  We are continuing to work on improving tempdb performance and metadata
  contention, but in the meantime, there are some best practices you can
  employ in your code that might help avoid the contention:

  Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them
  ends.
  Do not alter temp tables after they have been created.
  Do not truncate temp tables
  Move index creation statements on temp tables to the new inline index creation syntax that was introduced in SQL Server 2014.


Can I tell postgres to use a different "collation" for an integer column? Let's say I'm using an integer column to represent an "enum" in my application code, for a "priority" attribute.

0=low
1=medium
2=high

The app is operating for a while like that, and then I want to add two more

3=medium-low
4=medium-high

Now I want to be able to index this column and do queries on it using the correct semantic ordering. Is there a way to tell postgres, when creating the index and also when doing the query, that the number ordering isn't the semantic ordering?

You can use a CASE expression for the ORDER BY:

order by case the_column
            when 0 then 0
            when 3 then 1
            when 1 then 2
            when 4 then 3
            when 2 then 4
            else 5 --- everything else
         end


Design Sql Server physical configuration from existing VM We have 4 virtual machines that we used to test our BI platform (PoC), the final dimensions are:

SSIS (16 vcpu), SSAS (8 vCPU), SSRS (4 VCPU) et Data Engine (24 vCPU)

What are the recommendations to install these components to a physical server?

For SQL Server performance, VMware recommends (chap. 3.5.2) setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform". That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each.

Can we calculate physical server dimensions using reverse engineering? I mean: 16 vCPU equals 16 physical core?

The dimensions CPU and RAM of these VM are big so we are asking if it's not more interesting to deploy on physical machines. We need 280 GB of RAM.

The same thing that VMware said here the ratio 1:1 or 1:2 is considered for more performance.

That means that a VM with 16 CPUs will have 16 vCPUs with 1 core each.

Not exactly, that means initially a Host with a CPU of 16 cores should only have 1 VM with 16 vCPUs or 2 VMs with 8 vCPUs each instead. But as the chapter 3.5.2 Allocating vCPU says:

  if the physical server that the various SQL Server
  workloads currently run on equates to 16 physical CPU cores, avoid
  allocating more than 16 virtual vCPUs for the VMs on that vSphere host
  during the
  initial virtualization effort.

Notice it says initial virtualization effort. So, after some time, you will probably find out that it's not the best configuration for your environment. And it continues:

  After you have determined that there is excess capacity to be used,
  you can consider increasing density by adding more workloads into the vSphere cluster and allocating virtual vCPUs beyond the available
  physical cores. Consider using monitoring tools capable to collect,
  store and analyze mid- and long-terms data ranges.

It's not that there is a faster ratio then 1:1, it's just that apart from being the fastest it's the most expensive ratio. In a VMware environment you can find performance charts that will give you some information about the workload on a VM.

  Can we calculate physical server dimensions using revese engeneering?
  I mean : 16 vCPU equals 16 physical core?

Yes, but

lets say that VM has been using only 4 vCPUs at peak to process its
workload. When you create the physical server with 16 physical cores,
12 of them will be sitting doing nothing and you paid for that;
Now let's say that VM has been using all 16 cores at peak and you
build a physical server with exactly 16 cores. Next month, when the
workload increases requiring 17 cores to run properly, you'll have to
by new hardware.

So, if you really decide to go for a physical environment and just replicates physical copies of your VMs without the monitoring information of your servers, chances are good of sizing them ineficiently.

If your only motivation to change from VM to physical is the size of your VMs, check the Configuration Maximums of vSphere and you might find out that you still have plenty of room to grow your VMs (provided you have hardware on the host).


cron command running postgres at high cpu I can see that a cron command is running under a postgres user and killing the CPU but I have no idea how it's being executed or how to stop it !

You've been hacked.  There has been a recent trend for crypto miners to hide their mining software by naming it 'cron'.  It is not the actual cron.

You probably had a poor password for your PostgreSQL superuser account (probably named "postgres"), which the hackers then used it to download and launch software as the OS user "postgres".  Or, maybe you had a poor password for the OS user "postgres", and they logged into that directly.

The paranoid approach is to nuke the machine from orbit and rebuild it.  The lax approach is to change your passwords (both OS and internal to PostgreSQL) and reboot, and check ~postgres/.ssh/authorized_keys for entries that shouldn't be there.


Use column values in ORDER BY and LIMIT clauses I have this table in a Postgres 9.3 database:

I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name.

The final result will be as in the following picture:

Note: Sorry if it is simple for you, but I can't solve this. All mail addresses were generated by www.mockaroo.com. If your address is in this list, don't blame me.

order and limit columns will always have same data. order may be asc or desc and limit may be any integer value (but all rows will be the same value. It came from a grouping query.

Query
While your solution is clever, it's poison for performance because the value to order by has to be computed separately for every row. More importantly, your query cannot use a plain index.
I suggest to move the trick to the LIMIT clause and use UNION ALL. This way the query gets cheaper overall and can use an index (which nukes competitors that can't).
WITH l AS (
   SELECT CASE WHEN ordr = 'a' THEN lim ELSE 0 END AS lim_a
        , CASE WHEN ordr = 'd' THEN lim ELSE 0 END AS lim_d
   FROM   test
   LIMIT  1
   )
(SELECT * FROM test ORDER BY first_name      LIMIT (SELECT lim_a FROM l))
UNION ALL
(SELECT * FROM test ORDER BY first_name DESC LIMIT (SELECT lim_d FROM l));

One of both SELECT gets LIMIT 0 and is never executed. You'll see "never executed" in the EXPLAIN ANALYZE output.
About twice as fast without index in a quick test on Postgres 9.4 (35 rows out of 50k), but several orders of magnitude faster with index. The difference grows with the size of the table, obviously.
Table layout
Don't use reserved key words as identifier (as you found yourself already): lim and ordr instead of limit and order.
Don't bloat your table with redundant values. If you cannot avoid storing lim and ordr for every row, at least make it small. Basic layout:
CREATE TABLE test (
  id         int
, lim        int
, ordr       "char" CHECK (ordr IN ('a', 'd'))
, first_name text
, email      text
);

"char" is perfect as a simplistic enumeration type and occupies just 1 byte.
Create an index:
CREATE INDEX test_first_name_idx ON test(first_name);

Details:

Configuring PostgreSQL for read performance
Measure the size of a PostgreSQL table row

SQL Fiddle.


Output a 2 column table to a three column query Hi I have a Table with 2 columns, one column for the employeename and another column for shift,  below is a sample.

EmployeeName          Shift
John                    1
Peter                   1
Mary                    2
Mike                    1
John                    2
Paul                    2
Mary                    1

I want to output it to query with 3 columns and count the number of shifts as show below:

EmployeeName         Shift1            Shift2
John                   1                 1
Peter                  1                 0
Mary                   1                 1
Mike                   1                 0
Paul                   0                 1

In SQL Server 2012 you can use the new IIF() function:

SELECT EmployeeName,
  Shift1 = COUNT(IIF(Shift=1,1,NULL)),
  Shift2 = COUNT(IIF(Shift=2,1,NULL))
FROM dbo.tablename
GROUP BY EmployeeName;

This is just shorthand for a CASE expression, so on older versions you could do it this way:

SELECT EmployeeName,
  Shift1 = COUNT(CASE WHEN Shift = 1 THEN 1 END),
  Shift2 = COUNT(CASE WHEN Shift = 2 THEN 1 END)
FROM dbo.tablename
GROUP BY EmployeeName;

You can also use PIVOT:

SELECT EmployeeName, Shift1 = [1], Shift2 = [2]
FROM dbo.TableName AS t
PIVOT (COUNT(Shift) FOR Shift IN ([1],[2])) AS p;

Now, none of these seems to produce the results in the same order you've defined in the question, and I don't know it's possible to produce that order reliably given the sample data you've shown.

# SQLfiddle demo #


How to investigate memory grant warnings? Saw this warning from query in plan cache
<Warnings>
     <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="2751552" GrantedMemory="2751552" MaxUsedMemory="768" />
</Warnings>

Does RequestedMemory represent bytes or kilobytes?
How do you troubleshoot what caused this warning?

This is usually resultant (though I believe other things can also cause it) from Cardinality Estimate issues. You should look at the Actual Execution Plan and see if you have any mis-estimates by comparing the Estimated Number of Rows total to the Actual Number of Rows. If you update your question with the Execution Plan (you can upload it to Paste The Plan and link it to your question) then we can give you better direction on the source of your excessive memory grant issue.