Show only duplicate values How to select rows with duplicate values (non-distinct rows) and summarize with a count of the number of duplicate values in order of the most frequent duplicates?

Given a table of:

person_

name_
favorite_color_

…with this data:

Bob       Brown
Lucy      Lavender
Orin      Orange
Liam      Lavender
Betty     Brown
Blair     Brown

…show:

3         Brown
2         Lavender

We omit “Orange” from the list as it is distinct, with no duplicate siblings.

I am most interested in Postgres, but I expect this can be done in standard SQL.

That can be done with a simple SELECT statement.

SELECT count(*) AS count_ , favorite_color_
FROM person_
GROUP BY favorite_color_
HAVING count(*) > 1
ORDER BY count_ DESC
;

The count(*) command is an aggregate function supported by Postgres and gives the number of rows with duplicate values in a column specified by the GROUP BY clause.

To omit the values without any duplicates, add the HAVING clause. A HAVING is similar to a WHERE but applies to each row being generated to represent the grouping.

To get results to sort with most frequent duplicate values at the top, use the ORDER BY clause. We reference the column alias count_ that we created in the first line with the AS clause.


What is the most efficient way of inserting 199 millon rows of data? I have a 2.2 GB text file in the format of:

1234567890
1234567890
etc.......

I need to insert each line into a table (field 1) such as:

CREATE TABLE [dbo].[table](
    [field1] [varchar](10) NOT NULL,
    [field2] [varchar](3) NOT NULL

What is the most efficient way of inserting this data?
Is there a premade program that can be used? (i.e can it be done from the command line externally)

I've created a script and I select 1,000 records create a sql query around them and then insert. This takes a while, is there a better way? (Better as in easier, faster, less overhead)

Note: I've tried 1M, 100,000, 10,000 batch sizes, but the query is long at those sizes, so am stuck with 1k for now.

(query used)

INSERT INTO table (field1, field2)
SELECT '1234567890','123'
UNION ALL
SELECT '1234567890','123'
UNION ALL
SELECT '1234567890','123'
// etc....

BCP is likely to be the best tool for the task. SQLFool has a good BCP basics article on its usage.

@Marian posted a comprehensive list of BCP optimisation references in response to a question I asked, which you may find useful.


Why are transactions running as distributed (DTCXact)? I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask.

I repetitively run the following query against production servers:

SELECT  DTAT.transaction_id ,
    DTAT.[name] ,
    DTAT.transaction_begin_time ,
    CASE DTAT.transaction_type
      WHEN 1 THEN 'Read/write'
      WHEN 2 THEN 'Read-only'
      WHEN 3 THEN 'System'
      WHEN 4 THEN 'Distributed'
    END AS transaction_type ,
    CASE DTAT.transaction_state
      WHEN 0 THEN 'Not fully initialized'
      WHEN 1 THEN 'Initialized, not started'
      WHEN 2 THEN 'Active'
      WHEN 3 THEN 'Ended' -- only applies to read-only transactions
      WHEN 4 THEN 'Commit initiated'-- distributed transactions only
      WHEN 5 THEN 'Prepared, awaiting resolution'
      WHEN 6 THEN 'Committed'
      WHEN 7 THEN 'Rolling back'
      WHEN 8 THEN 'Rolled back'
    END AS transaction_state ,
    CASE DTAT.dtc_state
      WHEN 1 THEN 'Active'
      WHEN 2 THEN 'Prepared'
      WHEN 3 THEN 'Committed'
      WHEN 4 THEN 'Aborted'
      WHEN 5 THEN 'Recovered'
    END AS dtc_state
FROM    sys.dm_tran_active_transactions DTAT
        INNER JOIN sys.dm_tran_session_transactions DTST
                         ON DTAT.transaction_id = DTST.transaction_id
WHERE   [DTST].[is_user_transaction] = 1
ORDER BY DTAT.transaction_begin_time

It always shows results similar to this sample:

  2414848764;DTCXact;2016-01-28
  10:24:41.983;Distributed;Committed;Committed
  2414896908;DTCXact;2016-01-28
  10:26:05.847;Distributed;Committed;Committed
  2414903917;DTCXact;2016-01-28
  10:26:29.017;Distributed;Committed;Committed
  2414918503;user_transaction;2016-01-28
  10:27:06.823;Read/write;Active;NULL 2414918551;DTCXact;2016-01-28
  10:27:06.973;Distributed;Committed;Committed

This result shows that most transactions run as distributed. I really don't know why at all. Some of the servers are clustered with two nodes. Others are not. Their results are similar.

Facts:

The MSDTC Service is running on all SQL-Servers. None of the SQL-Servers has any Linked Servers.
remote trans proc in sys.configurations is set to 0.
I found no explicit Begin DISTRIBUTED Transaction or SET REMOTE_PROC_TRANSACTIONS usage in code
no definition / usage of TransactionScope in Code
Found a class described as follows: //COM+ Transaction mgr using "Service without Components" model built according to System.Transactions.TransactionScope (NET 2). I don't know what this means.

The asp.net web application causing those transactions usually always reads and writes data from one and the same database on one and the same SQL-Server. There MAYBE some very few exceptions, especially running during nighttime copying some data around, but I would not expect to see the MAJORITY of transactions being distributed the whole day long.

I traced the actios of a SPID that I found was running distributed transactions:

Here is a sample result of tracing: I see dozends of RPC:Completed Events in a row followed by 2 DTCTransaction Events (1st: EventSubClass="Enlisting in a DTC transaction", 2nd EventSubclass= "Propagate Transaction").
All target the same DB. No TM:Promote Tran xxx Events happen.

I found out that those queries are running on the IIS (where the Web Application is hosted) in a .net Windows Service, that uses the same database as well and executes jobs. May this 2nd application cause DTC??

I searched around the web without finding similar questions or any answers. So I wonder what could be the reason for this? Or what knowledge do I miss to understand this?

Have you tried profiling a single session to see if/when its transaction is promoted to distributed? For example, using a server side trace using e.g. the DTCTransaction (and maybe TM: Promote Tran xxx) event classes. You can also monitor detailed DTC activity in Windows.

I would profile all the likely looking connection, execution, and transaction-related events for a single SPID while it did some representative work. Note: profiling is best done on a test system due to overheads, using a server-side traces or the Extended Events equivalent. Do not use the Profiler UI except on a local test instance if you really have no other option.

If I were unable to see the application source code or talk to the developers about the use of transactions, this is likely how I would begin to isolate the cause.

It may also be that the application, its data access layer, or client driver is set to promote transactions when a second local database is accessed or a second connection used in the same transaction. There might even be an explicit BEGIN DISTRIBUTED TRANSACTION. Perhaps TransactionScope (or similar) is being used in ADO.NET with multiple connections. See:

Using System.Transactions
System.Transactions Integration with SQL Server
Distributed Transactions (.NET Framework)

There are many possibilities, but tracing/profiling will at least give you some clues about where to focus your investigations.

My guess is that it's most likely to be an unintended side-effect of the way your application developers are using .NET connections and transactions, perhaps combined with the way IIS is configured.

It does seem likely the distributed transactions are associated with your "Transaction Mgr" class in some way. There doesn't have to be an explicit TransactionScope in your code, that's just one common vector.

Stepping through the application code (and data access components) while watching the effect on DTC and SQL Server may be the best way to determine exactly where, when, and why distributed transactions are being invoked or enlisted to.

Obsolete now the question has been edited, but other causes include:

Linked servers with remote proc transaction promotion set true. This can cause a local transaction to be promoted to a distributed transaction.
The legacy configuration setting remote trans proc in sys.configurations, when used with 'remote servers'. Also see the SET REMOTE_PROC_TRANSACTIONS session option.


Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ) I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue.

issue is: when I wanted to integrate OWASP WebGoat application with oracle XE 11g , I receive the above mentioned error.

connection URL in web.xml is:

jdbc:oracle:thin:@//localhost:1521/XE/${USER}

=================================================================
listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

=============================================================
tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

===================================================================
java version:

java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) Client VM (build 24.55-b03, mixed mode, sharing)

===================================================================
output of the lsnrctl services:

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2014 16:34
:07

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: AMIR-PC, pid: 5740>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Amir-PC)(PORT=2403))
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

=====================================================================
output of the tnsping xe :

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2
014 16:35:19

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

could some one please help me resolve this issue? I have been struggling more than 3 days and not found any useful answer yet.

In a comment you said your web.xml contains:

<init-param>
 <param-name>DatabaseConnectionString</param-name>
 <!-- The string "${USER}" in the connection string will be replaced
      by the active username when making a connection. -->
 <param-value>
  <!--jdbc:hsqldb:mem:${USER}-->
  <!--jdbc:oracle:thin:@127.0.0.1:1521:xe/${USER}-->
  jdbc:oracle:thin:@localhost:1521
 </param-value>
</init-param>

The connection string you showed at the top of the question is commented out, so you're actually connecting with jdbc:oracle:thin:@localhost:1521. That isn't supplying a service name; but your listener.ora does have DEFAULT_SERVICE_LISTENER = (XE), so this should be the equivalent of jdbc:oracle:thin:@localhost:1521/XE. (I've tested this with 11gR2 EE and the parentheses around the XE in the listener.ora don't seem to matter).

But that doesn't quite seem to work as expected. When you changed that and set the user name explicitly:

jdbc:oracle:thin:system/system@//localhost:1521/XE

... the TNS-12514 went away and was replaced with an ORA-01017, invalid username/password. That means the connection string is now working and your system account's password is something other than system. With a valid username and password it looks like it should now work.

If the ${USER} substitution works as the comment in the web.xml suggests, and contains both the username and password, then making your connection string look like this ought to work:

jdbc:oracle:thin:${USER}@//localhost:1521/XE

But only if ${USER} is actually a valid username/password string.


Split read/write requests over different read/write datasources Recently I have run some performance tests on the application I work on, and it turns out that it didn't do really well ( the problem is mainly between the back and the DB). So as investigating the problem\solution, we have found out that using read/write datasources ( read/write master 1 or multiple reads slaves) could be a good way to go. As I found in those sources: http://fedulov.website/2015/10/14/dynamic-datasource-routing-with-spring/

To sum up the solution consists of defining the datasources, and before each transaction ( @transaction ) define which datasource should we use. But with already having a huge number of defined services and transactions ( my case) it seems too much time consuming to choose at every step which datasource to use.

Is there an automated way to split (select vs /post/update ) opreations ? or a project that serves as a proxy to route the queries. ( I have seen this question that was asked 9 years ago but I think certainly there are new solutions How to setup Hibernate to read/write to different datasources?).

Also I have read about latency problems between writing and reading, ( are we talking about ms, s latency ?) does the number of read instances influence the latency? what to do to prevent such behavior before staring to code. ( an architecture to adopt maybe ? a design pattern? )

Ps: I am using spring, spring data jpa, hibernate, postgresql, hikari connection pool. Thank your for time.

There is no automatic way to tell a data modifying query from a reading one.

Imagine the following SQL statement performing a function call:

SELECT delete_all();

There is no way of telling what the function does without analyzing its source code.

So you have to make the decision in your code that (hopefully) knows which queries modify data and which don't.

Using streaming replication in PostgreSQL to off-load read workload to standby servers works, but there is one caveat:

By default, replication is asynchronous, so there is a delay between the transaction commit on the primary server and the time the changes become visible on the standby. Your application has to be able to deal with this (or perform the queries that cannot on the replication primary).

The only way around that would be to use synchronous replication with synchronous_commit = remote_apply, but that will drastically increase the time a transaction commit takes, because it has to wait for the change to be replayed on a standby before it can report success.


How can I stop script execution if insert value is null and set value if not null? I have tried to use Case and SET NOEXEC ON but get the following errors:

  Syntax Error: unexpected 'SET' (set)
  Syntax Error: missing 'closing parenthesis'

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
           @Id AS repr,
           'product_code' AS tag,
           CASE @Code
             WHEN NOT null THEN @Code
             ELSE SET NOEXEC ON
           END AS value`

@Id and @Code are declared parameters in the script

SET NOEXEC ON is not MySQL syntax.

The thing to do is to avoid selecting it:

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
            @Id AS repr,
            'product_code' AS tag,
            @Code AS value`        -- The WHERE will prevent nulls
        WHERE @Code IS NOT NULL;   -- avoid generating anything to insert when NULL


What's the best database design for implementing categories and tags? For example, a news website might have "categories" such as Politics, Business, Entertainment, Sports, Lifestyle, Tech, Science, Health, ... etc. Each article belongs to only one category, but can have multiple "tags". Tags are just like hashtags we see on social media and can be very detailed.

This StackOverflow question shows the best approach to design a database for "tags". What if I need to add "categories"?

I'm not sure if it's okay to simply record each article's category in its table row. Maybe adding a column named "Category"? If so, is the performance still good when visitors want to search, say, all articles which belong to "Business" category and have "Facebook" tag?

If each article has at most one (or one and only one) category, then you would include the category information in the article table.

I would not include the text name of the category in the table. I would have a separate category table with the category name and an integer ID value. As there are presumably a relatively small number of possible categories, this shouldn't have a major impact on performance; and it would prove its value the first time someone had you change the name of a category.

You'll also probably want an index on article.category_id, if searches by tag and category will be very common (as I imagine they would be).


Constraint for a unique column if other column has same value I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do.

Here's the table:

CREATE TABLE Example (
    id INT,
    name VARCHAR(100) NOT NULL
);

The data would be similar to this:

INSERT INTO Example (id, name) VALUES(1, 'Peter Parker');

The following would work:

INSERT INTO Example (id, name) VALUES(1, 'Bruce Wayne');
INSERT INTO Example (id, name) VALUES(2, 'Peter Parker');

The following would not work (as there's the same name with that id):

INSERT INTO Example (id, name) VALUES(1, 'Peter Parker');

Hopefully this can help clear things up for what I want:

There can be multiple rows with the same ID
There can be multiple rows with the same name
There cannot be multiple rows with the same ID and name

If I've understood you correctly, what you want is something like this:

CREATE TABLE Example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uix_name_example (name)
);

The id field is the PRIMARY KEY and its value is taken care of by the server. A UNIQUE KEY on name ensures no duplicates are allowed - and NOT NULL ensures (hopefully) meaningful values.

INSERT INTO Example (name) VALUES ('Peter Parker'); -- specify name, but not id - it's automatic
INSERT INTO Example (name) VALUES ('Billy Joe McAlister');
INSERT INTO Example (name) VALUES ('Bill Clinton');

Check your table:

mysql> SELECT * FROM Example;
+----+---------------------+
| id | name                |
+----+---------------------+
|  3 | Bill Clinton        |
|  2 | Billy Joe McAlister |
|  1 | Peter Parker        |
+----+---------------------+
3 rows in set (0.03 sec)

A subsequent attempt to INSERT the same name (Peter Parker) fails.

mysql> INSERT INTO Example (name) VALUES('Peter Parker');
ERROR 1062 (23000): Duplicate entry 'Peter Parker' for key 'uix_name_example'
mysql>

If you want to allow people with the same name, but different ids, then you just leave out the UNIQUE KEY uix_name_example (name) line in the table definition.

mysql> INSERT INTO Example VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

If this isn't a satisfactory explanation, please expand your question.

(EDIT in response to OP's comment and clarification)

What you want is a UNIQUE KEY (or INDEX) on the combined id and name fields - also known as a composite index (or key). The syntax for this is as follows:

CREATE TABLE Example2 (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uix_example (id, name)
);

Duplicate ids with different names allowed to be inserted.

mysql> INSERT INTO Example2 VALUES(1, 'Peter Parker');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO Example2 VALUES(1, 'Billy Power');
Query OK, 1 row affected (0.08 sec)

Same name allowed to be inserted with different ids

mysql> INSERT INTO Example2 VALUES(1, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO Example2 VALUES(2, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

But, what is not allowed is to insert same id and same name.

mysql> INSERT INTO Example2 VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1-Peter Parker' for key 'uix_example'
mysql>

Hopefully this resolves your question.


postgresql 9.1 Multiple Cluster on same host I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok with local connections, I cannot connect using any of my tools to the second database with port 5433, including pgAdmin. Please help. Any parameter that I need to modify for the new database with port 5433?

netstat -an | grep 5433 shows,

tcp 0 0 0.0.0.0:5433 0.0.0.0:* LISTEN
tcp6 0 0 :::5433 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING 72842 /var/run/postgresql/.s.PGSQL.5433

iptables -L shows,

Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

First, please post error messages.  Otherwise this is shooting in the dark.

You could have a bunch of problems.  I would suggest the following:

If you simply get a connection refused, try netstat -p | grep 5433 so you can see what programs are listening on the port.  Is it something unexpected?
If you are getting pg_hba.conf errors, double check your settings there.
Please check again your listen settings in your postgresql.conf file.
Check your postgresql log on the cluster you are having trouble with.  Please post relevant sections if this doesn't get you to your answer.


SQL Server 2005 encryption - What Grants should I give I use Mixed mode Authentication and I want my database users (in the Public role membership) to have the ability to JUST execute procedures that encrypt/decrypt data (using Certificate and Symmteric keys).

I have already given them Grant Execute on the procedures. What other Grants should I apply?

Thanx!

The specific permissions required are:

GRANT CONTROL ON CERTIFICATE :: [cert] TO [user]
GRANT VIEW DEFINITION ON SYMMETRIC KEY :: [key] TO [user]

However, rather than grant permissions directly on the Certificate and Key, I like to have two stored procedures - one for encryption and one for decryption. We generally have different users that perform the encryption and decryption. Generally, the application is encrypting records as they are inserted into the database. Then it's a different user that needs to decrypt them for reporting purposes. Both procedures are created WITH EXECUTE AS OWNER and the users are granted EXECUTE on the procedures.

This way no users have direct permissions on the Certificate and Key and no user can both encrypt and decrypt.


"Flexible" foreign key constraint We have a Postgres setup with multiple tables representing different parts and stages of a project, 4 of them at the moment but potentially more in the future. We want to make a Task table with a foreign key to any of these tables. Preferably like this:

CREATE TABLE stage1(
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage2 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage4 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE task (
id SERIAL PRIMARY KEY,
description TEXT,
foreignkey_table VARCHAR(20),
foreignkey_id INTEGER);

where the column named foreignkey_table can indicate the "parent" table and the foreignkey_id will indicate the "parent id".

These stages 1 to 3 are to make the example simple but they can in no way be joined as one. Think of them as "preparation stage", "execution stage" and "evaluation stage". Or as completely different tables like "users", "companies" and "activities"  each with its own specific not overlapping content.

How can I do this?

I have read some posts telling it is not possible but they were quite old so I'm hoping a solution is implemented after these posts. I also found a potential solution on Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL), and this is my current best fallback option.

I don't see the need for multiple stage tables at all (tip: if you have increasing numbers on columns or tables you probably violated normalization rules).

I would create a single stage table that contains the information which stage that is:

CREATE TABLE stage
(
  id SERIAL PRIMARY KEY,
  level integer not null check (level between 1 and 4),
  description TEXT
);

CREATE TABLE task
(
  id SERIAL PRIMARY KEY,
  description TEXT,
  stage_id integer references stage
);

If you get new stages in the future, all you need to do is add new rows to the stage table.


User specific default database in Hive I am running a Oracle Big Data Appliance platform that has Cloudera EDH 5.9.x running under the hood. My users are mainly using beeswax, which is the Hive query editor app within Hue.

When a user logs into Hue and then opens beeswax, the default Hive database "default" is preselected. I want to change this so that the first database they see is their own sandbox database. Currently, the user has to manually select the database from beeswax or run the use DATABASE command in the editor.

Is there a configuration item I can change within any of the CDH software modules that will help me do this? Or is there a concept of a Hive startup script where I can run the use DATABASE command?

In bypass mode Hive clients directly access the metastore database instead of using the Hive Metastore Server for metastore information.

Go to the Hive service.
Click the Configuration tab.
Select Scope > HIVE service_name (Service-Wide)
Select Category > Advanced.
Deselect the Bypass Hive Metastore Server property.
Click Save Changes to commit the changes.
Re-deploy Hive client configurations.
Restart Hive and any Hue or Impala services configured to use that Hive service.

This might work for you.


Finding the last record within three years of the first record with subqueries I have the following table with student enrollment data:

CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

I want to

Find the first year that the student was enrolled in a program.
Find the latest enrollment from all the enrollments occurring within 3 years of the first year.
Get the DepartureCode for the enrollment in 2.

Output for sample data above would be:

+--------------+--------------+--------------+--------------+---------------+
| SequenceID   | SchoolYear   | StudentID    | ProgramID    | DepartureCode |
+--------------+--------------+--------------+--------------+---------------+
|     37105    |         2014 |     9800384  |         5331 |           01  |
|     37320    |         2014 |     9100926  |         5729 |            01 |
+--------------+--------------+--------------+--------------+---------------+

This is the full record for the last enrollment within 3 years of the first enrollment.

I'm doing this with subqueries as below, but wondering if there is another way that is easier to read?

SELECT
    MainQueryEnrollments .StudentID,
    MainQueryEnrollments .ProgramID,
    MainQueryEnrollments .SchoolYear,
    MainQueryEnrollments .DepartureCode
FROM
    Enrollments AS MainQueryEnrollments
JOIN
    ( -- find SequenceID of the last enrollment within
      -- three years of the first enrollment
    SELECT
        SubQueryEnrollments.StudentID,
        SubQueryEnrollments.ProgramID,
        MAX(SubQueryEnrollments SequenceID) AS SequenceID
    FROM
        Enrollments AS SubQueryEnrollments
    JOIN
        ( -- find the SchoolYear of the first enrollment
          SELECT
            StudentID,
            ProgramID,
            MIN(SchoolYear) AS SchoolYear
        FROM Enrollments
        GROUP BY
            StudentID,
            ProgramID
        ) FirstEnrollment
    ON FirstEnrollment.StudentID = SubQueryEnrollments.StudentID
    AND FirstEnrollment.ProgramID = SubQueryEnrollments.ProgramID
    AND SubQueryEnrollments.SchoolYear - FirstEnrollment.SchoolYear <= 2
      -- enrollments within three years of the first enrollment
    GROUP BY
        SubQueryEnrollments .StudentID,
        SubQueryEnrollments .ProgramID
) AS LastEnrollment3Years
ON MainQueryEnrollments.SequenceID = LastEnrollment3Years.SequenceID

My solution makes the following assumptions (please let me know if I have misunderstood your requirements)

SequenceId is like an Identity value and represents the chronological order of student changes.
You want to find the enrollment row for each student where the SequenceId is the MAX(SequenceId) for each student where the SchoolYear is within 3 years of the initial enrollment year for that student.

SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[Enrollments];
CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

;

WITH YearFirstEnrolled
AS (
    SELECT StudentID
        ,min(SchoolYear) AS YearFirstEnrolled
    FROM Enrollments
    GROUP BY StudentID
    )
SELECT *
FROM YearFirstEnrolled fe
JOIN Enrollments e
    ON e.StudentID = fe.StudentID
        AND e.SequenceID = (
            SELECT max(SequenceID)
            FROM Enrollments
            WHERE StudentID = e.StudentID
                AND SchoolYear < fe.YearFirstEnrolled + 3
            )
order by e.SequenceID

| StudentID | YearFirstEnrolled | SequenceID | SchoolYear | StudentID | ProgramID | DepartureCode |
|-----------|-------------------|------------|------------|-----------|-----------|---------------|
| 9800384   | 2012              | 37105      | 2014       | 9800384   | 5331      | 01            |
| 9100926   | 2012              | 37320      | 2014       | 9100926   | 5729      | 01            |

Select your desired columns from the result.


SQL Server seperate data files When splitting out a data file into separate files is there any benefit in having tables usually queried together in the same file?

I'm against it but a vendor has suggested it as part of setup. May help with performance, the DB is currently 400 GB. The files will be on the same drive as well. This will just be a test, so I will split the file on a similar server and then run some tests to see if any performance gain is there. But when splitting the file should tables queried together be kept in (if possible) the same file?

This won't be different disks so not sure if we'll see any benefit from this, the data file is on 4 SSD drives in RAID 10 . I just need to split the file and as I have no idea how the vendors DB is queried without doing extensive monitoring not sure how to group the tables in different files.

I am referring to .ndf files (filegroups). Is there a best practise in allocating tables to filegroups?

NDF are not file groups. NDF files are additional data files. You may achieve better performance by adding additional files or you may not, it depends.

Read Benchmarking: do multiple data files make a difference? by Paul Randal.

What I think you had in mind are additional file groups, whereby you create an object in a new file group and put that group into separate file (NDF). To ease I/O, large, frequently accessed tables will achieve better performance in separate file groups, providing those file groups sit in separate files and those files are on different LUNs, and LUNs are on separate spindles, and so on.

If you only have one disk the difference may not be noticeable.

You may also take a look at Using Files and Filegroups from Microsoft.

With SSDs, it depends on your array config and class of the storage. SSDs have no more throughput but lower latency, so you should do some performance testing first, and see if is good enough to satisfy SLA with your production workload. At the end of the day, only thorough performance tests can clear your doubts.


Why are my SQL Server 2008R2 databases not mirroring? I'm trying to set up database mirroring in SQL Server 2008R2. I've taken a full backup and a transactional backup from my principal database and I've restored both WITH NORECOVERY.

However, my mirror database is now stuck in RECOVERING mode and when I hit "start mirroring" on my principal, it says it can't connect.

What am I doing wrong?

Edit: I should probably state that my database is rather large (the mdf file is about 4.8GB) so that could be why.

Edit2: I've also tried doing this with both firewalls turned off so I know it's not a firewall issue.

Edit3: I've run the SQL that Mark suggested. The principal results are here: http://piersonthe.net/principal.xls and mirror are here: http://piersonthe.net/mirror.xls

It's worth noting that I got the following error when I ran the query on the mirror:
Msg 927, Level 14, State 2, Line 1
Database 'RHSCMSSites' cannot be opened. It is in the middle of a restore.

After all that one of the servers didn't have the SQL Server service running under the same user. I'm an idiot.


Drop database owned by specific user in PostgreSQL I have created database with bad symbols at the end of its name.
Now I want to drop it but I don't know how to type its name in the console.
Can I remove databases owned by this user? I tried to DROP OWNED BY devuser but it does not worked. I see just DROP OWNED in the console.

You could also try this from your shell:

-bash-4.1$ bad_dbname=$(psql -t -q -c "select datname from pg_database where datname like 'bsw_ui%' limit 1")
-bash-4.1$ echo "the next command will drop ${bad_dbname}"
-bash-4.1$ psql -c "drop database ${bad_dbname}"

I verified this works using bash4.1 on centos6.3 with posgresql8.4 for a database using this unicode character. YMMV


Scaling Percona datacenters: setup and replication Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.

We have already scaled alot of our application stack: we offloaded our high read/write tables for temporary information to a separate Percona server where the tables are running with "Engine=MEMORY", as well as migrated other sections to a cassandra cluster.

Now we are left with a "lean" database where we have a read/write load of 88%/12%. At this point i have a few questions i would like to get some feedback on:

1. Read Slaves

With our read/write setup, a number of (e.g. 2-3) read-slaves should reduce the read load to a minimum on our write master. How scaleable is a read-slave solution: if we double/tripple our load is adding additional read slaves going to continue to offer enough capacity for reads? I read up on this post: What are the limits on the amount of slaves per master? however, not coming out of a scaleablility background this this may seem foolish, but is this a freezable solution? There are alot of people pushing sharding rather than a read-slave solution, however, i really dont see a need at the moment with our read/write load to rewrite large sections of our application... any thoughts?

2. Multiple datacenters and replication

Furthermore, we are looking at serving different geographic locations with datacenters near by to reduce network lag (we deal with mobile applications which dont really like lag). The plan would be to use the much mentioned semisync. replication (see: Is it a good idea to split MySQL DB into two servers and Is MySQL Replication Affected by a High-Latency Interconnect? ) for a master-master replication, where each datacenter has a single master, and multiple read slaves. Again, in my naivety, i am very interested in knowing if this would be within the bounds of "best practices" when scaling.

3. Hardware and Config

I have been busy the past few weeks benchmarking our live system, and i have come to the conclusion that, regardless of the solution we choose for Point 1 and 2, the server we are currently using will not make it for very much longer, could i get some thoughts on our setup:

CPU: Intel(R) Xeon(R) CPU E31275 @ 3.40GHz mit 8 cores (hyperthreading)
RAM: 16GB
Raid 10 with a strip size of 64 KB and controller cache enabled
Software: Percona 5.5
Database size: 83.7GB
Top 5 Tables:
 21302MB  table1
 7656MB  table2
 5477MB  table3
 4352MB  table4
 3663MB  table5

my.cnf settings:

 max_heap_table_size=64M
 tmp_table_size=64M
 default_storage_engine = InnoDB
 innodb_buffer_pool_size = 10G
 innodb_file_per_table   = 1
 innodb_old_blocks_time=1000
 innodb_buffer_pool_instances=10
 innodb_log_file_size=256M
 innodb_flush_method=O_DIRECT
 innodb_read_io_threads=10
 innodb_write_io_threads=10
 join_buffer_size = 67108864 #64M
 expand_fast_index_creation=ON

Will moving to a Percona XtraDB Cluster solution solve some of our issues, e.g. replication stability?

I know that these are alot of questions which are very theoretical, and i appreciate anyone who takes the time to read and comment on my thoughts. As a small startup in Europe we really dont have the venture capital to just "go to the cloud", and we prefer to have more control ourselves. While we are looking into consultants, etc. I thought that stackexchange was the right place to bounce some ideas off.

In this instance, you actually have two choices
CHOICE #1 : Percona XtraDB Cluster
I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).
You ultimately become responsible for

sizing memory requirements and disk configuration for InnoDB
remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
adding provisioning new Cluster Nodes for Reads/Writes

CHOICE #2 : Amazon RDS
Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired  Options. Then, you must do this to import the new options:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.
Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.


What function to use with this timestamp? I have timestamp (stored as BIGINT) format I cannot turn into a human-readable format:

1462975819250

It points to a date in 2016-may-11th. However function to_timestamp() turns this into:

48329-11-10 13:00:49.999872+01

I'd like to create a VIEW where I could display these values, don't know what function to use.

The field is written by a Java program (EclipseLink I presume). I don't have the source-code of the program.

to_timestamp() expects the parameter to be given in seconds. Your value is in miliseconds. Just divide it by 1000:

SELECT to_timestamp(1462975819.250);

gives 2016-05-11 16:10:19.25+02


Select longest continuous sequence I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column.

Consider the following table:

lap_id (serial), lap_no (int), car_type (enum), race_id (int FK)

Where lap_no is unique for each (race_id, car_type).

I would like the query to produce the longest sequence for a given race_id and car_type, so it would return an int (or long) that is highest.

With the following data:

1, 1, red, 1
2, 2, red, 1
3, 3, red, 1
4, 4, red, 1
5, 1, blue, 1
6, 5, red, 1
7, 2, blue, 1
8, 1, green, 1

For car_type = red and race_id = 1 the query would return 5 as the longest sequence of the lap_no field.

I found a similar question here however my situation is a bit more straightforward.

(I would also like to know the longest sequence for a given car_type for all races, but was planning to work that out myself.)

Your description results in a table definition like this:

CREATE TABLE tbl (
   lap_id   serial PRIMARY KEY
 , lap_no   int NOT NULL
 , car_type enum NOT NULL
 , race_id  int NOT NULL  -- REFERENCES ...
 , UNIQUE(race_id, car_type, lap_no)
);

General solution for this class of problems

To get the longest sequence (1 result, longest of all, arbitrary pick if there are ties):

SELECT race_id, car_type, count(*) AS seq_len
FROM  (
   SELECT *, count(*) FILTER (WHERE step)
                      OVER (ORDER BY race_id, car_type, lap_no) AS grp
   FROM  (
      SELECT *, (lag(lap_no) OVER (PARTITION BY race_id, car_type ORDER BY lap_no) + 1)
                 IS DISTINCT FROM lap_no AS step
      FROM   tbl
      ) x
   ) y
GROUP  BY race_id, car_type, grp
ORDER  BY seq_len DESC
LIMIT  1;

count(*) FILTER (WHERE step) only counts TRUE (= step to next group), which results in a new number for every new group.

Related question on SO, one answer featuring a procedural solution with plpgsql:

GROUP BY and aggregate sequential numeric values

If the top requirement is performance, the plpgsql function is typically faster in this particular case because it can calculate the result in a single scan.

Faster for consecutive numbers

We can capitalize on the fact that consecutive lap_no define a sequence, for a much simpler and faster version:

SELECT race_id, car_type, count(*) AS seq_len
FROM  (
   SELECT race_id, car_type
        , row_number() OVER (PARTITION BY race_id, car_type ORDER BY lap_no) - lap_no AS grp
   FROM   tbl
   ) x
GROUP  BY race_id, car_type, grp
ORDER  BY seq_len DESC
LIMIT  1;

Consecutive laps end up in the same grp. Every missing lap results in a lower grp per partition.

This relies on (race_id, car_type, lap_no) being UNIQUE NOT NULL. NULL values or duplicates could break the logic.

Discussion of Jack's simpler alternative

@Jack's version effectively counts all laps (rows) where the previous lap_no in this race_id had the same car_type. That's simpler and faster and correct - as long as each car_type can only have one sequence per race_id.

But for a task that simple the query could be simpler, yet. It would follow logically that all lap_no per (car_type, race_id) must be in sequence, and we could just count the laps:

SELECT race_id, car_type, count(*) AS seq_len
FROM   tbl
GROUP  BY race_id, car_type
ORDER  BY seq_len DESC
LIMIT  1;

If, on the other hand, one car_type can have multiple separate sequences per race_id (and the question does not specify otherwise), Jack's version will fail.

Faster for a given race / car type

In reply to the comment / clarifications in the question: restricting the query to one given (race_id, car_type) will make it much faster, of course:

SELECT count(*) AS seq_len
FROM  (
   SELECT row_number() OVER (ORDER BY lap_no) - lap_no AS grp
   FROM   tbl
   WHERE  race_id = 1
   AND    car_type = 'red'
   ) x
GROUP  BY grp
ORDER  BY seq_len DESC
LIMIT  1;

db<>fiddle here
Old SQL Fiddle

Index

Key to top performance is a fitting index (except for the mentioned procedural solution working with a single sequential scan). A multicolumn index like this serves best:

CREATE INDEX tbl_mult_idx ON tbl (race_id, car_type, lap_no);

If your table has the UNIQUE constraint I assumed at the top, that is implemented with just this (unique) index internally, and you do not need to create another index.


How to Test Log Shipping I am evaluating log shipping as a solution to keep a permanent failover copy of our live database. It looks ideal.

We are using Filestream which is part of the reason to use it over mirroring.

I have a secondary database in the (Standby / Read-Only) state. In my tests which are on a small database I have been able to take it offline, copy the mdf and ldf files, create a new database and replace the mdf and ldf files.

This then gives me a copy of the database to test that the log shipping is working as expected, and the idea is that I want to test that the database will be in a state that come go back live straight away.

The problem comes when I go back to our live database and use log shipping. To test that it can failover I cannot copy away the mdf and ldf files because it is using Filestream, and those files won't include the Filestream data.

Suggestions for the testing? I would ideally like to keep the secondary database connected to log shipping because it is big and rebuilding it again wouldn't be ideal.

I'm not sure why your copying the mdf and ldf files but I'm assuming that you want a copy of the database without breaking the log chain.

This can be done by taking a backup of the database and specifying the COPY_ONLY option which will not effect the log chain http://msdn.microsoft.com/en-us/library/ms191495.aspx


SELECT *
FROM customer c
LEFT JOIN customer_orders co
    ON c.customer_number = co.customer_number
   AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid'
  AND c.order_status = 'unserved'
  AND co.cust_ord_number IS NULL


As mustaccio states, partitioning the data may help, though that might not be practical as a quick solution and you'd still have to optimise such statements a bit.

mysql is said to be particularly inefficient with IN clauses, in this case it may be running that inner query once for every row in mytable which is not going to be efficient. Better but still far from optimal, it may be running the inner query and spooling the results into a temporary table on disk then joining on that.

To avoid IN, you can rearrange actions of the pattern:

DELETE FROM mytable1 WHERE value IN (SELECT key FROM mytable2 WHERE <filtering_condition>)

into

DELETE t1
FROM   mytable1 t1
INNER JOIN
       mytable2 t2
ON     t2.key = ti.id
WHERE  <filtering_condition>

(in your case both mytable1 and mytable2 are the same table, and that works just as well)

I'm not sure how this will react to the counting-in-a-variable syntax you have there though (I'm not a mysql person specifically and that is not something seen in other DBs I do work with regularly).

If you add an integer identity column (I assume your PK here is id, time hence the current integer ID is not unique) then simply checking it against modulo 5 may be an acceptable approximation of "delete 80% evenly" like so:

DELETE t1
FROM   mytable t1
WHERE  t1.time < "2013-08-08 00:00:00"
AND    t1.counter MOD 5 != 0

Adding that column initially will be a time consuming process but maintinaing it afterwards should not be a problem (the DB will generate a number for you on each insert, just make sure you don't include it in the VALUES list of any INSERT operation), but you don't need to use IN or JOIN at all. An index over time, counter (instead of just time) may help performance a bit more. I would be tempted to make counter the primary key and id, time a seperate index (as well as an index over time or time, counter), but that would depend a lot on your other operations on the data.

Of course once you are altering the table structure like this, do give consideration the the partitioning option too. It will be more complicated but may have significant beneficial performance impact elsewhere too.


Changing database context before creating new database I have noticed on many examples that before CREATE DATABASE statement, there is part that changes database context.

USE master;
GO

CREATE DATABASE Sample;
GO

I wonder why is that because with or without it, in SQL Server Management Studio 17, database creates right where it is supposed to be.

Often these examples are accompanied by (or are expected to be copied and pasted to a script with) other commands, such as a DROP DATABASE first (which can’t happen if you’re in the context of that database that you’re trying to drop and re-create).

There are a few commands that need to be run in master, but not a whole ton. This may just be a convention by habit rather than by reason, and in all honesty, it probably is the source of many mistaken deployments to master instead of a user database. I wrote a tip about this situation:

Cleaning up the SQL Server master database


MySQL reads / writes per table I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives.

Is there a way of following each tables activity ?
As in follow IOPS, writes, reads per table ?

Method 1

If you are using Percona Server or MariaDB (>= 5.2), you can simply set the userstat/userstat_running variable to enable a bunch of new INFORMATION_SCHEMA tables including one called TABLE_STATISTICS that provides exactly this information.

For example:

mysql> SELECT TABLE_NAME, ROWS_READ, ROWS_CHANGED, ROWS_CHANGED_X_INDEXES FROM TABLE_STATISTICS ORDER BY ROWS_CHANGED DESC LIMIT 5;
+-------------------+------------+--------------+------------------------+
| TABLE_NAME        | ROWS_READ  | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+-------------------+------------+--------------+------------------------+
| user              |   21122527 |      5989231 |               23956924 |
| audit             |       1208 |      5020929 |               20083716 |
| sometemp          |   13995426 |      3182150 |                9546450 |
| creditcards       |    3566482 |      2998976 |               11995904 |
| order             | 2147483647 |      2662606 |               53252120 |
+-------------------+------------+--------------+------------------------+

ROWS_CHANGED would correspond to the most written to tables and ROWS_READ would be the most read from.  You should also look at INDEX_STATISTICS to find your most and least used indexes.

See also the MariaDB user statistics documentation.

Method 2

If you are not using Percona Server, you could use pt-query-digest to capture a sample of your queries and then filter out only INSERT/UPDATE/DELETEs.  That would look something like this:

mysql> SELECT @@GLOBAL.slow_query_log_file;
+------------------------------------------+
| @@GLOBAL.slow_query_log_file             |
+------------------------------------------+
| /var/logs/mysql/slowquery.log            |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL slow_query_log_file='/tmp/allqueries.log';
mysql> SELECT @@GLOBAL.long_query_time;
+--------------------------+
| @@GLOBAL.long_query_time |
+--------------------------+
|                 0.250000 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL long_query_time = 0;
mysql> FLUSH LOGS;
mysql> SLEEP 600; SET GLOBAL long_query_time = 0.25; SET GLOBAL slow_query_log_file='/var/logs/mysql/slowquery.log'; FLUSH LOGS;

Now you have a file, /tmp/allqueries.log that contains every query executed on your server for ~10 minutes.

Next, analyze it with pt-query-digest to get the most frequently written to tables:

pt-query-digest /tmp/allqueries.log --group-by=distill --filter '$event->{arg} =~ m/^(update|delete|insert)/i' --limit 5 > /tmp/writes.txt

If you examine /tmp/writes.txt, you will see a section near the top that looks like this:

# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M   Item
# ==== ======== ============= ===== ====== ==== ===== ====================
#    1 0x        0.0558 26.8%   282 0.0002 1.00  0.00 INSERT UPDATE user
#    2 0x        0.0448 21.5%   246 0.0002 1.00  0.00 UPDATE audit
#    3 0x        0.0228 10.9%    11 0.0021 1.00  0.00 UPDATE sometemp
#    4 0x        0.0108  5.2%    16 0.0007 1.00  0.00 UPDATE creditcards
#    5 0x        0.0103  4.9%    43 0.0002 1.00  0.00 UPDATE order

Roughly, these are your most written to tables for the duration of the sample you chose.  To get the most read from tables (roughly), you can change the --filter parameter to --filter '$event->{arg} =~ m/^select/i' and you will see similar output.

If you are only interested in writes, you can pass a binary log into pt-query-digest and get similar results:

mysqlbinlog mysql-bin.000511 | pt-query-digest --type=binlog --group-by=distill > /tmp/writes.txt

You can also get the same data with tcpdump and pt-query-digest --type=tcpdump

So, this being said, assuming that you are using InnoDB tables, I highly doubt that you will see much performance benefit from doing this at all.  Because of the way data is buffered to the InnoDB log and then written to disk, I wouldn't expect much or any performance gain from moving individual tables around like this.  You might see some benefit from moving the InnoDB log files themselves to separate, faster disk to separate the log read/writes from the tablespace read/writes, but even that is questionable.  Investing in fast, high quality RAID arrays with a battery backed cache (or better yet, SSD) will be a better use of your resources.


Is it possible to drop export some shema without data and the other with data I have oracle 11G R2 under RHEL 6.3 test is the name of the database, the database contain 12 custom schema, sch1,sch2,...,sch12.  is it possible to create database backup using datapump method that contain all of the schema with no data except the first three one, the data is needed.

You can export specific schema using the SCHEMAS parameter:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007509

You can export the definitions of data alone using the CONTENT parameter:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL838

Combine the two:
        expdp user/pass SCHEMAS=a,b,c CONTENT=METADATA_ONLY [........]

This will achieve the aim of just doing the schema.  You'll need to do the 3 schema where you want the data as a separate job.


RANGE clause error within window function I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it:

SELECT date("aa0"."created_at") AS "Day of Created At",
       "aa0"."user_id" AS "User Id",
       count (*) activities_today,
       count(*) over (PARTITION BY "aa0"."user_id"
                      ORDER BY date("aa0"."created_at") ASC
                      RANGE BETWEEN date("aa0"."created_at" - interval '4 weeks')
                                    AND
                                    date("aa0"."created_at" - interval '1 day')
                ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id"
ORDER BY "Day of Created At" ASC LIMIT 1000;

However, I get the following error:

ERROR: syntax error at or near ")" Position: 459

If I use ROWS instead of RANGE it works but this is not the correct logic since it's not guaranteed that there will be a row for every day:

SELECT date("aa0"."created_at") AS "Day of Created At",
       "aa0"."user_id" AS "User Id",
       count(*) activities_today,
       count(*) over (PARTITION BY "aa0"."user_id"
                      ORDER BY date("aa0"."created_at") ASC
                      ROWS BETWEEN 28 PRECEEDING AND 1 PRECEEDING
                ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id"
ORDER BY "Day of Created At" ASC LIMIT 1000;

Schema:

CREATE TABLE activity_activity (
  user_id int,
  created_at timestamp
)

Version:

select version()

PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit

PostgreSQL doesn't support the RANGE clause yet. So even if you'd got the syntax right, it would've just failed with:

regress=> SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 FOLLOWING);
ERROR:  RANGE FOLLOWING is only supported with UNBOUNDED
LINE 1: SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 F...

To work around this, you can do a left outer join on generate_series(first_day, last_day, INTERVAL '1' DAY) then use a ROWS based window. That way a row exists for every day. It's a whole lot less efficient but unless you feel like getting your hands dirty in PostgreSQL's source code, it's probably the only option.


Where does MySQL's "show slave status" information come from? two files "master.info" and "relay-log.info" were deleted after I "reset slave"

I want to know why slave information still exists ~ confused >_<

I think it shouldn't display any message about slave ~ thanks ^_^

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: centos7
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: Centos6-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 126
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

That slave info is in RAM.
If you are running MySQL 5.6, run RESET SLAVE ALL;
If you are running MySQL 5.5, restart MySQL
If you are running MySQL 5.0/5.1, run the following:
STOP SLAVE;
CHANGE MASTER TO master_host='';

These are the ways to make the SHOW SLAVE STATUS\G disappear from view
GIVE IT A TRY !!!


Visual Explain Not working in MySQL Workbench Is there some trick to getting the Visual Explain feature to work in MySQL Workbench?

I am running version 6.3.6 on Windows 7.
I've tried simple queries with just one join. I've tried it with complex queries with 12 Joins.
I've tried it with MySQL 5.5 and 5.7.

But every time I just get Explain Data Not Available For Statement

I've looked on the Bugs for MySQL Workbench > Visual Explain, but there is no recent bugs, making me think it is something I am doing.
But I can't see what. Does anyone have some suggestions?

The user raising this question got an answer in the MySQL Workbench Forum:

  Visual Explain is only available if your server supports the JSON explain output (MySQL 5.6.5 or newer).

The reason VISUAL EXPLAIN didn't work for the user with 5.7, was the query being already prepended with explain at input time. Workbench transformed this into explain explain format=json - what wouldn't work.


How do I normalize and transform field text algorithmically I've got names and emails exported from one sales system that I am trying to move into my email service provider.  I have to normalize the data.

The email field has data like this:

John Smith <jsmith@gmail.com>

I want to algorithmically turn all email field entries into this:

jsmith@gmail.com

Do you know the best tool, script, process or excel function to do this?  It's racking my brain, because I don't want to do it manually.

Example SQL Script
For the specific example of parsing a name into it's separate elements see (SO question):
How can I parse the first, middle and last name from a full name field in SQL?

The accepted answer is a fantastic example of how to roll your own algorithm to separate out your names from emails.

Tools
There are some out there, I'll update this answer later when I can have some time to find (or please edit this answer if you have found some)

Note: No matter what method you use, do a sanity check validation of your results, most good methods will be able to accurately parse 90+% of your data, the real trick is how to identify the <10% that have not been correctly parsed  (that can be as simple as doing a sanity check and scanning the results)


Find who and when changed a specific value in the database We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake.

we want to find out who and when has done this?

could someone give some tips?

thanks in advanced.

If you didn't enable auditing in your database, you usually cannot spot who and when queried and/or modified the database, at least as easily as with auditing enabled.

You can use various types of auditing for these tasks:

Standard auditing
Fine-Grained Auditing (FGA)
Triggers

With standard auditing, you can audit object and system privileges, such as SELECT * FROM HR.EMLOYEES and CREATE ROLE. Audit records can be written in database (the default) or in the files outside the database. You enable auditing with AUDIT statement, for example, AUDIT SELECT ON HR.EMPLOYEES, and disable it with NOAUDIT statement, for example, NOAUDIT CREATE ROLE. Standard auditing allows you to capture the user name which invokes the statement, the timestamp, the text of the executed SQL, and bind variables. The standard auditing records can be queried from the DBA_AUDIT_TRAIL view if auditing is configured to store them in database (which is default).

With FGA, you can audit specific columns and rows of the table based on audit condition, you can audit actions in specific time intervals (say, from 5 PM to 8 AM), you can even audit actions from specific IP addresses. You implement FGA with DBMS_FGA PL/SQL package. For example, you can audit queries and updates on the column SALARY of the table HR.EMPLOYEES with this FGA policy:

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'chk_hr_employees_salary',
   audit_column       => 'SALARY',
   enable             =>  TRUE,
   statement_types    => 'SELECT, UPDATE',
   audit_trail        =>  DBMS_FGA.DB);
END;
/

The FGA audit records can then be queried from the DBA_FGA_AUDIT_TRAIL view if policy is configured to store them in database (audit_trail parameter in the code above).

With triggers, you can capture the previous and new values for the interesting tables. However auditing implemented with triggers is superseded by FGA. The audit records are inserted in the tables specifically created by you. As far as table auditing is concerned, you can use BEFORE STATEMENT and AFTER STATEMENT triggers to audit statements executing on the tables, or BEFORE ROW and AFTER ROW to audit the previous and new values for the columns.

The other facility I would give a shot is LogMiner. It allows you to analyze the online and archived redo logs and reconstruct the exact DML and DDL that were executed in the database. In Database Control (the web interface to control the database), take to the Availability tab, then click View and Manage Transactions). Here, you can specify the time range and objects you're interested in, and LogMiner will provide you the transactions list based on the parameters you specified. If you cannot determine the actual user, I would try to correlate the time of transaction with AUDIT SESSION records which are usually collected as part of mandatory auditing and can be retrieved from DBA_AUDIT_TRAIL view.


How to store immutable, unique, ordered, lists? I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists and entity instances are immutable. There will be far more entity instances than lists, and the database has to support fast insertions of entities.
So what's an insert-efficient, robust, way of storing immutable, unique, ordered lists?

Edit: The list items are simple integers, and the typical length is about 5 items. Long lists of, say 10 or 20 items are very unlikely but possible.

Edit: So far, I've considered these approaches:
1)
lists table has these columns: <list_id> <order> <item> so if list #5 contains the elements [10,20,30] the table will contain:
5   1   10
5   2   20
5   3   30

The entity table will have a item_list_id column that references the lists table (it's not a foreign key since list_id is not a unique column in the lists table - The this can be solved by adding another table with a single column that which contains all valid list_ids
).

This solution makes inserts a bit tricky
It also places the responsibility for enforcing the uniqueness of lists on the application, which isn't great.

2)
lists table has these columns: <list_id> <item1> <item2> <item3> ... <itemN> so if list #5 contains the elements [10,20,30] the table will contain:
5   10   20   30

The entity table will have a item_list_id column that references the lists table.

This solution is less robust since list lengths are limited (although this isn't a huge problem for me since my lists are VERY unlikely to contain more than 10 or 20 elements)
This approach is quite horrible to query ("find all lists in which a particular item appears" has to specify each and every column), and a nightmare to map using an ORM.
Insertions of new entities is not too bad since my lists are typically 4-5 items long, so I can probably index the first few columns.
Enforcement of uniqueness is still in the hands of the application.

3)
Use solution #1, but replace the meaningless list_id with a hash (say SHA-1) on the list in serialized form.

This way uniqueness is more strictly enforced since lists will have unique hashes
Insertions are simpler and quicker(?)
The data integrity enforcement is still in the hands of the application.

I would go with a traditional set-based approach for this.  Create entity, list, entityList, and listItem tables to store your data.  Individual list and entity tables allow you to create lists without entities and vice versa.  Constraints on these tables such as unique constraints and foreign keys will help preserve the integrity of the data.

For the list, create a single stored procedure which is the single point for inserting lists and listItems.  This proc has logic to detect if a list already exists and serves as a gateway.  This is straightforward to do using set operators like EXCEPT.

Here's a simple demo which shows how something like this might work.  I'm using a table-valued parameter (TVP) to handle passing around lists and a scalar function to determine if a list already exists.  Scalar functions are evil when used in resultsets (eg SELECT, WHERE clauses, JOIN ) but are ok when used for what they are meant for, returning single values.  Spend some time working through it and see if it could work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Reset
IF OBJECT_ID('dbo.entityLists') IS NOT NULL DROP TABLE dbo.entityLists
IF OBJECT_ID('dbo.listItems') IS NOT NULL DROP TABLE dbo.listItems
IF OBJECT_ID('dbo.entities') IS NOT NULL DROP TABLE dbo.entities
IF OBJECT_ID('dbo.lists') IS NOT NULL DROP TABLE dbo.lists
IF OBJECT_ID('dbo.usp_addList') IS NOT NULL DROP PROC dbo.usp_addList
IF OBJECT_ID('dbo.usf_listExists') IS NOT NULL DROP FUNCTION dbo.usf_listExists
IF EXISTS ( SELECT * FROM sys.types st INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'tvp_myList' AND ss.name = N'dbo')
DROP TYPE dbo.tvp_myList
GO

CREATE TABLE dbo.entities
(
    entityId        INT IDENTITY,
    entityName      VARCHAR(50) NOT NULL,

    dateAdded       DATETIME    NOT NULL DEFAULT GETDATE(),
    addedBy         VARCHAR(30) NOT NULL DEFAULT SUSER_NAME(),
    dateModified    DATETIME    NULL,
    modifiedBy      VARCHAR(30) NULL,

    CONSTRAINT PK_entities PRIMARY KEY ( entityId ),
    CONSTRAINT UK_entities__entityName UNIQUE ( entityName )

)
GO

CREATE TABLE dbo.lists
(
    listId          INT IDENTITY(1000,1),
    listName        VARCHAR(50) NOT NULL,

    dateAdded       DATETIME    NOT NULL DEFAULT GETDATE(),
    addedBy         VARCHAR(30) NOT NULL DEFAULT SUSER_NAME(),
    dateModified    DATETIME    NULL,
    modifiedBy      VARCHAR(30) NULL,

    CONSTRAINT PK_lists PRIMARY KEY ( listId ),
    CONSTRAINT UK_lists__listName UNIQUE ( listName )

)
GO

-- Although constraints might appear a little excessive, they prevent 'bad data' getting into the table,
-- whereas lesser constraints would not, eg prevent list with duplicate order
CREATE TABLE dbo.listItems
(
    listItemId      INT IDENTITY(100000,1),
    listId          INT NOT NULL,
    itemOrderId     INT NOT NULL,
    itemValue       INT NOT NULL,

    CONSTRAINT PK_listItems PRIMARY KEY ( listItemId ),         -- prevent list with duplicate order, value
    CONSTRAINT UK_listItems__listId__itemValue UNIQUE ( listId, itemValue ),        -- prevent list with duplicate value
    CONSTRAINT UK_listItems__listId__itemOrderId UNIQUE ( listId, itemOrderId ),    -- prevent list with duplicate order
    CONSTRAINT UK_listItems UNIQUE ( listId, itemOrderId, itemValue ),

    CONSTRAINT FK_listItems__listId FOREIGN KEY ( listId ) REFERENCES  dbo.lists ( listId )

)
GO

CREATE INDEX _idx_listItems__itemOrderId__itemValue ON dbo.listItems ( itemOrderId, itemValue ) -- to be used in list existence check
GO

CREATE TABLE dbo.entityLists
(
    entityId        INT NOT NULL,
    listId          INT NOT NULL,

    CONSTRAINT PK_entityLists PRIMARY KEY ( entityId, listId ),
    CONSTRAINT UK_entityLists__entityId UNIQUE ( entityId ),
    CONSTRAINT UK_entityLists__listId UNIQUE ( listId ),
    CONSTRAINT FK_entityLists__entityId FOREIGN KEY ( entityId ) REFERENCES  dbo.entities ( entityId ),
    CONSTRAINT FK_entityLists__listId FOREIGN KEY ( listId ) REFERENCES  dbo.lists ( listId )

)
GO

-- Create a type for passing lists around; TVP types and constraints mirror target table to act as a quality control
CREATE TYPE dbo.tvp_myList AS TABLE
(
    itemOrderId INT NOT NULL UNIQUE,    -- you could make this an identity column if you want to make sure order is sequential
    itemValue   INT NOT NULL UNIQUE,

    PRIMARY KEY ( itemOrderId, itemValue )

)
GO

-- Scalar function to determine if list already exists
CREATE FUNCTION dbo.usf_listExists
(
    @tvp        AS dbo.tvp_myList   READONLY
)

RETURNS BIT
AS
BEGIN
    DECLARE @listExists_yn  BIT

    IF EXISTS (
        SELECT COUNT(*) OVER() totalItems, itemOrderId, itemValue
        FROM @tvp
        EXCEPT
        SELECT COUNT(*) OVER( PARTITION BY listId ) totalItems, itemOrderId, itemValue
        FROM dbo.listItems
        )
        SET @listExists_yn = 0
    ELSE
        SET @listExists_yn = 1

    RETURN @listExists_yn

END
GO

-- Create a proc to add lists, report if list already exists
CREATE PROC dbo.usp_addList

    @listName   VARCHAR(50) = NULL,
    @tvp        AS dbo.tvp_myList   READONLY,
    @listId     INT OUTPUT

AS

SET NOCOUNT ON

    BEGIN TRY

        -- Check if list name already exists
        IF @listName IS NULL
            SET @listName = NEWID()
        ELSE
        IF EXISTS ( SELECT * FROM dbo.lists WHERE listName = @listName )
            RAISERROR ( 'List name ''%s'' already exists id %s', 16, 1, @listName );

        -- Check if list content already exists
        IF dbo.usf_listExists(@tvp) = 1
        BEGIN
            -- Get list
            --!!TODO add 'get list id' proc
            RAISERROR ( 'List already exists id %i', 16, 1, @listId );
        END
        ELSE
        BEGIN

            BEGIN TRAN

                -- Add the list
                INSERT INTO dbo.lists ( listName ) VALUES ( @listName )
                SET @listId = SCOPE_IDENTITY()

                -- Add the list items
                INSERT INTO dbo.listItems ( listId, itemOrderId, itemValue )
                SELECT @listId, itemOrderId, itemValue
                FROM @tvp

            COMMIT TRAN

        END

    END TRY
    BEGIN CATCH

        DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

        IF @@TRANCOUNT > 0 ROLLBACK
        RAISERROR( @errorMessage, 16, 1 )

    END CATCH

    RETURN

GO

-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Initial Data START
-- Add some data; actually entities are irrelevant to this demo
------------------------------------------------------------------------------------------------

;WITH cte AS (
SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.entities ( entityName )
SELECT 'Entity ' + CAST ( rn AS VARCHAR(10) )
FROM cte
GO

---- Add some simple lists
DECLARE @listId INT, @tvp AS dbo.tvp_myList

INSERT INTO @tvp VALUES ( 1, 10 )   -- Single Item List ( 10 )

EXEC dbo.usp_addList 'my New List 1', @tvp, @listId OUT

INSERT INTO @tvp VALUES ( 2, 20 )   -- Two Item List ( 10, 20 )
EXEC dbo.usp_addList 'my New List 2', @tvp, @listId OUT

INSERT INTO @tvp VALUES ( 3, 30 )   -- Three Item List ( 10, 20, 30 )
EXEC dbo.usp_addList 'my New List 3', @tvp, @listId OUT
GO

-- Initial lists
select * from dbo.lists
select * from dbo.listItems

-- Initial Data END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Test Scenarios START
------------------------------------------------------------------------------------------------

-- Try and add any of those lists again
BEGIN TRY

    -- Single item list already exists
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 )
    EXEC dbo.usp_addList 'my New List 4', @tvp, @listId OUT

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Two item list already exists
    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 )
    EXEC dbo.usp_addList 'my New List 5', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
    EXEC dbo.usp_addList 'my New List 6', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    -- New four-item list
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, 40 )
    EXEC dbo.usp_addList 'my New List 7', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    -- New four-item list
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, 41 )
    EXEC dbo.usp_addList 'my New List 8', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Two item list does not exist, but three-item list with same two components does exist; both get added
    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 21 ), ( 3, 30 )
    EXEC dbo.usp_addList 'my New List 9', @tvp, @listId OUT
    PRINT @listId

    DELETE @tvp WHERE itemValue = 30
    EXEC dbo.usp_addList 'my New List 10', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

-- Final lists
SELECT * FROM dbo.lists
SELECT * FROM dbo.listItems
GO

-- Test Scenarios END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Load Tests START
-- For 1,000 random entities, create a randomn-size list and add; report errors
------------------------------------------------------------------------------------------------

-- Load test the addList proc
DECLARE @i INT = 0

WHILE @i < 1000
BEGIN

    DECLARE @item INT, @items INT
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Initialise
    SELECT @items = ( RAND() * 9 ) + 1, @item = 1   -- random number between 1 and 10
    DELETE @tvp

    PRINT @items

    WHILE @item <= @items
    BEGIN

        PRINT CHAR(9) + CAST( @item AS VARCHAR(5) )

        INSERT INTO @tvp VALUES ( @item, RAND() * 10000 )

        SET @item += 1

    END

    EXEC dbo.usp_addList NULL, @tvp, NULL

    SET @i += 1

END
GO

-- Load Tests END
------------------------------------------------------------------------------------------------

-- Visualise your lists
SELECT
    l.listId,
    l.listName,
    l.dateAdded,
    (
    SELECT
        li1.listId  "@listId",
        COUNT(*) AS "@count",
        (
        SELECT
            itemOrderId AS "@itemOrderId",
            itemValue AS "@itemValue"
        FROM dbo.listItems li2
        WHERE li1.listId = li2.listId
        ORDER BY itemOrderId
        FOR XML PATH('listItem'), TYPE
        )
    FROM dbo.listItems li1
    WHERE l.listId = li1.listId
    GROUP BY li1.listId
    FOR XML PATH('listItems'), TYPE
    ) listItems
FROM dbo.lists l
GO

-- END
------------------------------------------------------------------------------------------------

I'm a big proponent of XML but it is the wrong choice for this design.  XML can be appropriate for semi-structured, but your design is actually really structured.  The overhead of constantly shredding XML for comparison would be too much, plus you can't directly compare once piece of XML with another easily in SQL Server.  Where XML would be useful here, is visualising your lists, as I've done at the end of my demo.

HTH


How make recursive json on mysql my table looks like

CREATE TABLE `test_comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `test_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `comment` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `rate` mediumint(9) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_comments_user_id_foreign` (`user_id`),
  KEY `test_comments_test_id_foreign` (`test_id`),
  KEY `test_comments_parent_id_foreign` (`parent_id`),
  CONSTRAINT `test_comments_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `test_comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_comments_test_id_foreign` FOREIGN KEY (`test_id`) REFERENCES `tests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_comments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

how can i retrieve comments like this structure:

[
    {
        "id": 3,
        "comment": "...",
        "comments": [
            {
                "id": 12,
                "comment": "answer for #3",
                "comments": []
            },
            {
                "id": 14,
                "comment": "answer for #3",
                "comments": [
                    {
                        "id": 22,
                        "comment": "sub comment for #14",
                        "comments": [
                            {
                                "id": 23,
                                "comment": "discussion continues..."
                            }
                        ]
                    }
                ]
            },
        ]
    },
    {
        "id": 44,
        "comment": "wow so many comments!",
        "comments": []
    }
]

I had an idea to form this structure in the application layer, but I think that this will negatively affect the memory consumption and the load as a whole.

I do not know how others solve such problems and i have not yet found a solution. I will be grateful if the community will tell me the right way :)

MySQL 8+

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

Recursive Common Table Expression Examples

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

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

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

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

The docs even explain it,

  How the CTE works:

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

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


Resetting MySQL Password Please see the following link from Super User. I cannot get anything working:

  https://superuser.com/q/342609/93569

Update: This link has been dramatically changed! Please see the link.

On Debian or Ubuntu you can also do this on the command line to login as the administrator:

sudo mysql --defaults-file=/etc/mysql/debian.cnf

Then you reset the password as randy has indicated.


Can I create a new column with default and a foreign key as a metadata-only operation? It is possible to create a new column with a default value as a metadata-only operation (not a size-of-data operation):

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL
    DEFAULT 0;

But is it possible to also create such a column that references another table as a metadata-only operation?

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL
    DEFAULT 0
    REFERENCES dbo.MyThing(MyThingId);

In theory it's possible because there should only be one value to check in the referenced table. However, in my example, the foreign key creation seems to be a size-of-data operation.

You can do this, but only if you don't check the FK on creation.  The Foreign Key will be enforced for subsequent INSERT and UPDATEs, but won't be trusted by the Query Optimizer.  EG:

drop table if exists MyReallyBigTable
drop table if exists MyThing
go
select o.* into dbo.MyReallyBigTable
from sys.objects o, sys.columns c

go
create table MyThing(MyThingId int primary key )
insert into MyThing(MyThingId) values (0)

set statistics io on
go
ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL
    DEFAULT 0
    --REFERENCES dbo.MyThing(MyThingId);
go
alter table MyReallyBigTable
with nocheck
add constraint  fk_MyReallyBigTable_MyThing
foreign key (MyThingId) references MyThing(MyThingId)
set statistics io off

You can come back later when you have time for a table scan and check all the rows to make the FK trusted:

select name, is_not_trusted
from sys.foreign_keys
go
alter table MyReallyBigTable with check check constraint fk_MyReallyBigTable_MyThing
go
select name, is_not_trusted
from sys.foreign_keys


SSIS Failed foreign key constraint I'm loading 2 csv files into 2 tables in SQL Server Data Tools using SSIS, and the 2nd one is connected to the 1st via a foreign key constraint. The problem is that a row of the 2nd table has a key that is missing from the 1st table, so the foreign key constraint fails and an error message is presented.

Is there a way to check the foreign key constraint before insertion and catch the error so that it doesn't interrupt the insertion of the rest of the rows?

IN SSIS, you can probably set up a lookup step with the foreign key data from table 1; rows that don't match could be routed into an error file, and the rest routed into the table.

Alternately, load the data into a staging table without the foreign key constraint, and run a script/stored procedure to validate the data in the staging table, including making sure the foreign keys are present.


Msg 5145: Autogrow of database log file took 1010871 milliseconds In the past, I had an update query that ran for 7 days. I couldn't find a solution, so I created a new database with indexes. The update query has now been running for 26 hours.
I am seeing messages like the following:

Autogrow of file database1_log file in database 'database1' took 1010871 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
Autogrow of file 'database1.log' in database 'database1' took 79342 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

The data file is 40GB
The log file is 50 GB, with autogrowth set to 10%

I really don't understand what to do.

The error is caused by how long it's taking SQL Server to grow your transaction log.

Assuming the error message you've quoted is for the last grow, SQL Server is claiming that it's taking about 17 minutes for SQL Server to add just under 5GB to your transaction log file.

This can be caused by a number of issues:

Slow disk: If your disks are particularly old and slow it will take SQL Server longer to extend the files
Disk Contention: If SQL Server is having to compete for access to the disks, then it will be slow to extend them. Common causes of this could be having only 1 disk for OS, Data and Logs, or another application on the machine accessing the same disk as SQL Server

There are a number of things you can try to alleviate the problem:

Move your files onto seperate physical disks if possible. Typically
you'd like to have 3 disks for OS, Data and Logs.
Use faster storage. If you can upgrade your disks then this will alleviate the wait for autogrowth to complete
Modify your autogrowth setting to be a fixed size. That way you'll get consistent growth rather than exponential as you are with your current percentage setting. Pick a value that is an acceptable compromise between lots of file growth events and waiting for the growth to happen.
Recreate your database, and preallocate the log file required to accommodate the queries you plan to run. This will remove the autogrowth events (unless you exceed that size).

Hope that helps.


Selecting the correct row given multiple input value combinations I have a table that stores part numbers for various items like so:

+------+-------------+-------------+-------------+
| Item |    Color    |    Type     | Part Number |
+------+-------------+-------------+-------------+
| Pen  |             |             |         001 |
| Pen  |       red   |    felt     |         002 |
| Pen  |      blue   |    felt     |         003 |
| Pen  |             |    felt     |         004 |
| Pen  |     green   |    felt     |         005 |
| Pen  |             |     gel     |         006 |
| Pen  |     green   |     gel     |         007 |
+------+-------------+-------------+-------------+

I need to get the part numbers for items that were ordered in the past. Is there a way to match the "correct" row with a single query knowing the Item, Color and Type?

Given {Item = Pen, Color = orange, Type = felt} the result should be 004

Given {Item = Pen, Color = blue, Type = felt} the result should be 003

Given {Item = Pen, Color = blue, Type = gel} the result should be 006

I'd like to avoid populating every possible combination into the table if possible.

Short and dirty answer. Not efficient, it will have to scan the whole table. Will give only 1 row as result, if there are more than one rows with same (maximal) number of matches ("wrong" data in your terminology), the choice is arbitrary:

SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC
LIMIT 1 ;

Slightly better than the above but not more efficient. If there are more than one rows with same (maximal) number of matches, the choice is not arbitrary but based on the assumption that a row with "item and type" is preferred over a row with "item and colour" which is preferred over a row with "type and colour":

SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC,
         (item='pen')+(type='gel') DESC,
         (item='pen') DESC
LIMIT 1 ;


Database Performance Tuning I have a MySQL backend, MS Access front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. What are the recommended tools and tuning methods that I should be using?

Recently, I discovered that MySQL keeps a 'General Query Log'. Maybe I can monitor the changes in this log file to see what requests are actually being made to the server. Is this a good method for fine tuning?

A good starting point is the MySQL Slow Query Log instead of the general query log. You can set the

You'll want to log queries that aren't using indexes

Update
In your question, you state that the system is 'nice and responsive' over local network, but that you haven't done any performance tuning. The slow query log I pointed out will help you identify queries that are taking a long time to run (over 1 second, if configured that way). IMO, this is a great starting point.
The longer a query takes, it is much worse when the response has to be transmitted over a WAN.

One tool I've recently discovered is mk-tcp-model that analyzes output from tcpdump to help measure how long a request takes to respond. You can see how many request/responses are coming in and how long each takes. The best tuning over a WAN is to reduce the amount of requests you need to make.


Invalid sync source in mongodb config server I have 3 config servers 1 primary and 2 secondaries i have received Waring alerts
in secondary servers as

  Log File Monitoring : key = error, File = /var/log/mongodb/mongod.log
  (mgdb09:log[/var/log/mongodb/mongod.log,@mongodb_error,UTF-8]):
  2018-05-05T05:42:08.913+0900 W REPL     [rsBackgroundSync] Fetcher
  stopped querying remote oplog with error: InvalidSyncSource: sync
  source mgdb08:27019 (last visible optime: { ts: Timestamp
  1525466619000|1, t: 24 }; config version: 1; sync source index: -1;
  primary index: 1) is no longer valid

  Log File Monitoring : key = error, File = /var/log/mongodb/mongod.log
  (mgdb07:log[/var/log/mongodb/mongod.log,@mongodb_error,UTF-8]):
  2018-05-05T05:37:19.219+0900 W REPL     [rsBackgroundSync] Fetcher
  stopped querying remote oplog with error: InvalidSyncSource: sync
  source mgdb08:27019 (last visible optime: { ts: Timestamp
  1525466619000|1, t: 24 }; config version: 1; sync source index: -1;
  primary index: 2) is no longer valid

  . Log File Monitoring : key = error, File =
  /var/log/mongodb/mongod.log
  (mgdb09:log[/var/log/mongodb/mongod.log,@mongodb_error,UTF-8]):
  2018-05-05T05:42:13.911+0900 I REPL     [SyncSourceFeedback]
  SyncSourceFeedback error sending update to mgdb08:27019:
  InvalidSyncSource: Sync source was cleared. Was mgdb08:27019

and later on one of the secondary became primary

what cause this kind of issue what action do i need

As I am able to see in your error log file such as error Fetcher stopped querying remote oplog with error: InvalidSyncSource: sync source

As per MongoDB jira documentation here It seems that the common cause for the remote replSetUpdatePosition failing with those error codes NodeNotFound,NotMasterOrSecondary and InvalidReplicaSetConfig would be a change in config. Config changes currently will cause the oplog fetcher to fail because it will detect a change in the config version contained in the oplog query metadata. Subsequently, after exiting the oplog fetcher, we will go back to the sync source resolver which has its own blacklisting logic.

For your further ref here