MySql Set Column on First Appearance of Value I have a select that returns something that looks like the following:

PeopleId
Timestamp
FavoriteNumber

1
2020-11-04 17:40:33
14

2
2020-12-06 21:19:41
59

3
2020-12-10 18:46:11
14

4
2020-12-15 00:01:53
7

Given this information, I want to, for every row, check if the person's favorite number is the first appearance of that number and if so return 0, if not, return  That would make a table that looks like:

PeopleId
Timestamp
FavoriteNumber
HasOriginalNumber

1
2020-11-04 17:40:33
14
1

2
2020-12-06 21:19:41
59
1

3
2020-12-10 18:46:11
14
0

4
2020-12-15 00:01:53
7
1

Assume the query up to this point has been:
Select p.PeopleId, p.Timestamp, p.FavoriteNumber From people p

The second table could be achieved by doing:
Select p.PeopleId, p.Timestamp, p.FavoriteNumber,
    ((Select Min(x.Timestamp) from people x where x.FavoriteNumber = p.FavoriteNumber) = p.TimeStamp) as
    HasOriginalColor)
    From people p

However, I think that query will be very inefficient for large tables and seems like overkill. Is there an easier and faster way to do this?

This is a classic example of where window functions come in handy! In this case, we shall use the ROW_NUMBER() function.
To answer your question, I did the following (see fiddle here):
The table:
CREATE TABLE people
(
  people_id INT,
  t_timestamp TIMESTAMP,
  favourite_number INT
);

Just a word of advice, you should NEVER call your fields (or your tables) using SQL keywords - it makes debugging more complex and error-prone and your apps less portable and it's bad practice generally.
Populate it:
INSERT INTO people VALUES
(1, '2020-11-04 17:40:33',  14),
(2, '2020-12-06 21:19:41',  59),
(3, '2020-12-10 18:46:11',  14),
(4, '2020-12-15 00:01:53',  7);

Then run the query (I'll go through the logic step-by-step):
SELECT
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
  people_id,
  t_timestamp,
  favourite_number
FROM people
ORDER BY people_id, t_timestamp, favourite_number;

Result:
rn  people_id   t_timestamp favourite_number
1   1   2020-11-04 17:40:33 14
1   2   2020-12-06 21:19:41 59
2   3   2020-12-10 18:46:11 14
1   4   2020-12-15 00:01:53 7

So, we can see by inspection that we want all of the records where rn = 1.
We now have to do a SUBSELECT (you can't put window functions in WHERE clauses):
SELECT * FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
    people_id,
    t_timestamp,
    favourite_number
  FROM people
--  ORDER BY people_id, t_timestamp, favourite_number
) AS tab
WHERE rn = 1
ORDER BY favourite_number, t_timestamp, favourite_number;

Result:
rn  people_id   t_timestamp          favourite_number
1   4           2020-12-15 00:01:53                 7
1   1           2020-11-04 17:40:33                14
1   2           2020-12-06 21:19:41                59

Now, these are the records we're interested in, but it's not quite the result you want.
We obtain this by using a CASE function, and since we're SELECTing all records, we can eliminate the SUBSELECT:
SELECT
  people_id,
  t_timestamp,
  favourite_number,
    CASE
      WHEN
        ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) = 1
        THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn
FROM people
ORDER BY people_id;

Result:
people_id          t_timestamp favourite_number has_original_number rn
        1  2020-11-04 17:40:33               14                   1  1
        2  2020-12-06 21:19:41               59                   1  1
        3  2020-12-10 18:46:11               14                   0  2
        4  2020-12-15 00:01:53                7                   1  1

Which, excepting rn is the result you want!
This is an example of a class of problems known as greatest-n-per-group - it's a dba.stackexchange tag - look at some of the questions in there and I would strongly urge you to get to know window functions - they will repay any effort you put into them many times over!
I modified your query in this way:
SELECT
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p;

The result is the same!
Performance analysis: (MySQL)
I then looked at the performance of both queries using the new EXPLAIN ANALYZE <followed by query text> functionality available in MySQL version >= 8.18 (as explained here and here). For brevity, I'm only showing the plans - see the revised fiddle for the whole thing. I also make use of the MySQL profiling capability as explained here.
(caveat: I am not an expert on query plans - but I think I know a bit - any corrections, references, URLs appreciated):
I turned on SET PROFILING = 1;
Then, after running the two queries, I looked at the profiles (some of the query text is truncated):
SHOW PROFILES;

=================================

Query_ID    Duration    Query

1           0.00078575

SELECT
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p
ORDER BY p.people_id

=======================================

2           0.00062000

SELECT
  p.people_id,
  p.t_timestamp,
  p.favourite_number,
    CASE
      WHEN
        ROW_NUMBER() OVER (PARTITION BY p.favourite_number ORDER BY p.t_timestamp) = 1
      THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp)

_
I ran this several times (typical run shown) and the result consistently shows that the ROW_NUMBER() query is faster (by ~ 20% - frequently more) than the MIN() one - this seems logical (to me) since the MIN() query is SELECTing from the people table twice! The difference is larger if I run the MIN() query second. This effect is also seen if the queries are run in the opposite order.
However, when I ran the query plans, I got a surprise:
-> Sort: people.people_id  (actual time=0.064..0.065 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
        -> Temporary table  (actual time=0.057..0.058 rows=4 loops=1)
            -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.043..0.049 rows=4 loops=1)
                -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
                    -> Temporary table  (actual time=0.040..0.042 rows=4 loops=1)
                        -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.031..0.036 rows=4 loops=1)
                            -> Sort: people.favourite_number, people.t_timestamp  (cost=0.65 rows=4) (actual time=0.027..0.027 rows=4 loops=1)
                                -> Table scan on people  (cost=0.65 rows=4) (actual time=0.013..0.016 rows=4 loops=1)

2nd Query (yours modified):
-> Table scan on p  (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: min(x.t_timestamp)  (actual time=0.036..0.037 rows=1 loops=4)
        -> Filter: (x.favourite_number = p.favourite_number)  (cost=0.35 rows=1) (actual time=0.009..0.033 rows=2 loops=4)
            -> Table scan on x  (cost=0.35 rows=4) (actual time=0.008..0.031 rows=4 loops=4)

The performances are roughly comparable - a bit of variability from run to run.
However, because you're returning the whole table, the optimiser will ignore indexes and scan the whole table - Table Scan on people - note that for your query, it performs 2 Table Scans whereas for mine it only performs 1 for mine (temporary tables excluded - in memory?).
IMHO, this means that as the row-count of the people table grows, the query which performs 2 table scans will slow down as a function of O(n2) but the other query will only slow down as a function of O(n). So, long-term, I think you'll be better off with my query - but, I am open to correction on this!


How to stop all nodes on FCI SQL server? My Setup: We have two nodes SQL setup with Active/Passive Windows Failover Cluster instance.
My Goal: To stop the running SQL Server service on the active node and prevent failover from happening for a period of time and then bring them back online again.
I'm using Windows Failover Cluster Manager and after doing quite a bit of research I see the following options available to me and how I think each of these options mean.

Drain Role - Done at node level to remove all running roles out of a
node, essentially pausing it on a cluster and is useful when you want
to patch or take specific node in a cluster offline.
Stop Role -
Done at Role level to take the FCI server offline and prevent
failover capability on all nodes belong in this specific Role.
Shut
Down Cluster - Done on the cluster group level, it is similar to
Stop Role except it stops all roles.
Stop Cluster Service - Done at
node level, similar to Drain Role????

Can you verify if what I wrote based on my understanding for these are correct?
So if I want to shut off my active/passive FCI SQL Server service, I plan to do the following, are the steps correct?
Bring down FCI SQL Server services:

Stop Role on the role that runs my SQL FCI to prevent failover
between active/passive node.
Shut off SQL Server services using the Windows
services window on the Active Node.

Bring up FCI SQL Server Services:

Turn on SQL Server service using window services on one of the node I want
to be active.
Start Role on the role that run my SQL Server FCI.

Another option I could think off but unsure its validity, basically i'm stopping the SQL service and at the same time refuse it to have the ability to failover to another node.
Stop the cluster service on my "Passive" node.
Stop the SQL Server service on my "Active" node.

So if I want to shut off my active/passive FCI SQL service, I plan to
do the following, are the steps correct?

The only steps you need are these:

Bring down FCI SQL services:

Stop Role on the role that runs my SQL FCI to prevent failover    between active/passive node.

Bring up FCI SQL Services:

Start Role on the role that run my SQL FCI.

With these you can properly manage the role services to be stopped like you need with the least impact. Once the role is stopped you can check on services.msc that SQL Server service stopped if you wanna be sure. It might take some time for that to happen because the service is going to stop gracefully, so wait until the role appears as stopped.

Another option I could think off but unsure its validity, basically
i'm stopping the SQL service and at the same time refuse it to have
the ability to failover to another node.

If you manipulate the services like this from windows services instead of using the Failover Cluster Manager, it's gonna be like fighting against the cluster: you trying to bring the services down and the cluster trying to keep them up.


How to join tables when one of them doesn't have any relationship with the others First of all, I know what I am about to show you is a completely DB mess but I just got it like this.

Tables :

Authors

manufacturers_id manufacturers_name
1                 Name1
2                 Name2
3                 Name3

Authors_to_manufacturers (useless table since there is no authors_id)

authors_id  manufacturers_id
1              100
2              200

manufacturers

manufacturers_id   manufacturers_name
 1                     Name1
 2                     Name2
 3                     Name2
 ...100                Name1,Name2
 101                   Name1,Name2,Name3
 102                    NameX

products

  products_id  products_name manufacturers_id (This is the PK from manufacturers, not authors)
    1         product1         100
    2         product2         300
    3         product3          1

The diference between authors and manufacturers is that authors table are "single" authors while manufacturers can be more than 1 authors together. Also manufacturers table contains all the exactly info of authors but with more rows (because some books have more than 1 author).

Authors table is screwed, since the primary key is the same as the manufacturers (can't change it now, it will mean a lot more of work that I am not authorized to do) and authors_to_manufacturers (It was suppose to be the connection for both tables, but whoever made this put authors_id when there is none as you could see).

Re-formulating my question:

How to make a query than join tables when one of the tables doesn't have any relation with the others (also the primary key is the same as one of the others tables)

This is what i got so far

SELECT count(products_id), authors.manufacturers_name
FROM products
INNER JOIN authors ON authors.manufacturers_id = products.manufacturers_id
WHERE authors.manufacturers_name = (select manufacturers_name from manufacturers) group by manufacturers_name;

of course this doesn't work because "subquery has more than one row", but thats exactly what i want (like if it would be ("...where manufacturers_name = manufacturers.name..."), so it would match all the manufacturers name from one table that match with the other table.

How about this:

SELECT Authors.manufacturers_id AS AuthorID, Authors.manufacturers_name AS AuthorName, count(products.products_id) AS '# of Products'
FROM Authors
LEFT JOIN
(
    SELECT manufacturers_id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.manufacturers_name, ',', n.n), ',', -1) value
      FROM Manufacturers t CROSS JOIN
    (
       SELECT a.N + b.N * 10 + 1 n
         FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        ORDER BY n
    ) n
     WHERE n.n <= 1 + (LENGTH(t.manufacturers_name) - LENGTH(REPLACE(t.manufacturers_name, ',', '')))
     ORDER BY value
) AS manufacturers ON manufacturers.value = Authors.manufacturers_name
LEFT JOIN products ON manufacturers.manufacturers_id = products.manufacturers_id
GROUP BY Authors.manufacturers_id, Authors.manufacturers_name


If I fail over one database, do the others that share the same mirror endpoint fail over as well? We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints.

If I go into the Database Properties for the test database and click the "Failover" button, will it fail over the production database as well since both databases share a mirror endpoint and their Server Network Addresses properties are the same?

I am concerned because when I set up mirroring for the 2nd database, I did not have to configure anything new. It just used all the existing information.

If I use the "Failover" button from the Database Properties, will it result in failing over all databases that use that endpoint, or just the specific database that I am viewing the properties for?

@mrdenny's answer is accurate that failing over one database will not result in all the other databases failing over as well.

However just to give more overview of what a database mirroring Endpoint is:

From BOL,

Connection management in Microsoft SQL Server 2005 and later versions is based on endpoints. An endpoint is a SQL Server object that enables SQL Server to communicate over the network. For database mirroring, a server instance requires its own, dedicated database mirroring endpoint. All mirroring connections on a server instance use a single database mirroring endpoint. This endpoint is a special-purpose endpoint used exclusively to receive database mirroring connections from other server instances.

To be more flexible, you can use T-sql for doing manual failover:

Turn mirroring off (break mirroring) on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the recover mod the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Fix and orphaned users if you are using SQL authentication

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’

EDIT:

Since you are using high safety mode (as per your screenshot), you can use the following from the principal server

ALTER DATABASE <<DB NAME>> SET PARTNER FAILOVER

Doing so, only the role reversal happens and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN.

Note that per this MSDN article

  Manual failover can be initiated only from the principal server.

If you want to automate the entire failover then refer Database Mirroring Automation


Optimize mysql database performance I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this:

Id: (int 11, primary key)
Codigo: (longtext)
Act_mas: (double)
Act_menos: (double)
Ind_mas: (double)
Ind_menos: (double)
Cap_mas: (double)
Cap_menos: (double)
Mensaje: (longtext)
Fecha: (datetime)
SuministrosId (int 11, this is a foreign key of a table with 62 rows, 21 columns that has another 2 foreign key, one with 44 rows and 11 columns and the other with 2 rows and 2 columns)

I guess I could change the datatype of Codigo and Mensaje to something like VARCHAR(10), since normally the value has less than 10 characters and probably LONGTEXT is an overkill.

I could also change column datatype, from Act_mas to Cap_menos, to INT, since all the values should be smaller than 1.000.000 (at the moment the biggest value is 795.600, and it's very strange to have such a value).

The database and the table is using 'InnoDB' engine.

Actually a query like SELECT * FROM table WHERE SuministrosId = 4 and Fecha >= "2015-01-01" takes about 3-5 seconds to load. Maybe its not too much but I think it could be lot less.
I tried to change the datatype of some columns but it was terribly slow and I had to stop it, should I give another try? Also I added an index to SuministrosId but it seemed to be the same.

If other data is needed feel free to ask. Thank you in advance! (And sorry for my English :c)

EDIT

Computer info:

CPU: Intel(R) Xeon(R) CPU E5-2609 v4 @ 1.70 GHz

RAM: 32 GB

OS: Windows Server 2012 R2 Standard

MySQL Version: 5.7.11

The database is used by an external java program that typically uses queries like the described above: SELECT * FROM consumos WHERE SuministrosId = 4 and Fecha >= "2015-01-01", this is the most used query since it's the "core" for the job done here. Some other queries are used to list clients like: SELECT * FROM clientes, or SELECT * FROM suministros. But those tables are really small and they are not a problem.

SHOW CREATE TABLE for the big one:

CREATE TABLE `consumos` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Codigo` longtext NOT NULL,
`Act_mas` double NOT NULL,
`Act_menos` double NOT NULL,
`Ind_mas` double NOT NULL,
`Ind_menos` double NOT NULL,
`Cap_mas` double NOT NULL,
`Cap_menos` double NOT NULL,
`Mensaje` longtext NOT NULL,
`Fecha` datetime NOT NULL,
`SuministrosId` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `IX_FK_Contadorsuministrosset` (`SuministrosId`),
KEY `Fecha` (`Fecha`),
CONSTRAINT `FK_Contadorsuministrosset` FOREIGN KEY (`SuministrosId`) REFERENCES `suministros` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13002432 DEFAULT CHARSET=latin1

Query explain:

Profile:

That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.)

Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.


Removing per column and table collation and charset I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their respective charsets and collation properties.

Now that the transition has been completed and all of the servers and the database itself have now been set to use utf8mb4 and utf8mb4_unicode_ci, how would I go about removing those properties from the columns and tables so they inherit the default value set on the server?

For instance presently if I execute a SHOW FULL COLUMNS FROM table; Collation for most columns is set to utf8mb4_unicode_ci rather than NULL.

Converting columns:

ALTER TABLE table_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8;

Converting tables:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;


Permission to view execution report in SSIS Catalog We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin?

This is for production environment and we don't want people to manipulate SSIS Catalog projects.

Thanks!

Here is our solution (believe me it will work perfectly!)

After investigating the procedure about how the execution reports are stored, we found that every time a job runs, the internal.executions table in SSISDB will be updated. And in order to view the execution report of this run, we need to run something like below:

EXEC SSISDB.catalog.grant_permission
    @object_type = 4,
    @object_id = @execution_id,
    @principal_ID =  13,
    @permission_type = 1;

This stored procedure will grant a role/user a certain access to an object in database. @object_type means on which type of object you need permissions on (4 means operation); @object_id means the specific object we want to access to; @principal_ID means who want to get the access; permission_type means what kind of access we want to have (1 means read only). For more information, please refer to catalog.grant_permission (SSISDB Database)

Our target is to create a trigger that every time a job runs -- meaning the internal.executions table gets inserted -- using the above SP to grant a role the permission to that operation information.

Then, let's follow the below steps to setup execution report viewing permissions:

Create a user the trigger will be executed as. This user should be able to execute a trigger in SSISDB and have access to SSIS Catalog. In our case, we give it db_owner role and ssis_admin under SSISDB.

USE [master]
GO
CREATE LOGIN [ssis_job_viewer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [SSISDB]
GO
CREATE USER [ssis_job_viewer] FOR LOGIN [ssis_job_viewer]
GO
USE [SSISDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssis_job_viewer]
GO
USE [SSISDB]
GO
ALTER ROLE [ssis_admin] ADD MEMBER [ssis_job_viewer]
GO

Create a role [package_execution_viewer]. This role will be used in the stored procedure we mentioned above.

USE [SSISDB]
GO
CREATE ROLE [package_execution_viewer]
GO

Add users to [package_execution_viewer]

USE [SSISDB]
GO
ALTER ROLE [package_execution_viewer] ADD MEMBER [user1]
GO
USE [SSISDB]
GO
ALTER ROLE [package_execution_viewer] ADD MEMBER [user2]
GO

Get the principle_id of package_execution_viewer role. This id will be used in above SP also.

SELECT * from sys.database_principals
GO

Create trigger to grant permission for package_execution_viewer

USE [SSISDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [internal].[update_viewer_perms]
ON [internal].[executions]
WITH EXECUTE AS 'ssis_job_viewer'
AFTER INSERT
AS
declare @execution_id bigint
BEGIN
select @execution_id = execution_id from inserted
EXEC SSISDB.catalog.grant_permission
    @object_type = 4,
    @object_id = @execution_id,
    @principal_ID =  13,
    @permission_type = 1   **--Note the principal_id needs to be changed**
END
GO

All set. This way we can let people access to the execution reports without making them as ssis_admin. Try it out and share any thoughts to this post!


Database setup/design for multiple services I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few of the 7 services requiring an additional piece of information. There other 7-8 services are similar to each other as well but not as similar the the first 7. So my question is how should I break this down into a MySQL database?

Should each service have its own table? If this is true, what is the best way to link tables? Any advice is greatly appreciated!

Also, here is a sample of the type of info that will be stored:

good lord, no.

read up on multi-tenancy http://msdn.microsoft.com/en-us/library/aa479086.aspx
read up on the Party Model http://www.tdan.com/view-articles/5014/
read up on Table Inheritance  http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

Then go read the data modeling books by Fowler, Silverston, or Hay.


Can an Oracle IDENTITY column be nullable? I want to create an ID column that automatically generates unique ID numbers by default.

Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box application (using the attribute editing environment).

I've heard that auto-generated IDs can be achieved by creating an Oracle IDENTITY column
 (hint: to find the relevant section in the linked page, search for this text: Use this clause to specify an identity column.).

And so, I have successfully created a table with an IDENTITY column:

CREATE TABLE A_TEST_TABLE (ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

However, I see that the field is not nullabe:

SELECT ID FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'A_TEST_TABLE'

NULLABLE = N

This is a problem because my application tries to "do users a favor". If a field is not nullable, the application automatically inserts a zero as a dummy value. This overwrites the auto-generated ID (which of course, is not what I want). I'm guessing that it does this to avoid getting an error message from the database if a null value is committed.

To avoid this issue with my application, is it possible to make the field nullabe?

When I read the documentation, it says:

  If you specify ON NULL, then Oracle Database uses the sequence
  generator to assign a value to the column when a subsequent INSERT
  statement attempts to assign a value that evaluates to NULL.

Does this imply that an IDENTITY field can be nullable? If so, how?

I recognize that I have no idea what I'm doing. I don't even think I would ultimately want an ID field that is nullable. I'm just trying to make sense of all this, and I'm failing.

You could make the column NULLable, but not make it an IDENTITY column; instead, create a sequence, and an INSERT trigger that sets the ID value from the sequence. Prior to version 12.1, IDENTITY columns weren't available, and using a sequence was the workaround.


Migration of create statement from SQL Server to MySQL I have a table that have the following table structure

CREATE TABLE [dbo].[ASAT](
  [ID] [bigint] IDENTITY(1,1) NOT NULL,
  [ASATID]  AS ('ASAT'+right('00000000000000000000'
  +CONVERT([varchar](19),[ID],0),(20))) PERSISTED NOT NULL,
  [Created] [nvarchar](8) NOT NULL DEFAULT (CONVERT([varchar],dateadd(year,
  (0),getdate()),(112)))
) ON [PRIMARY]

When I used the MySQL Migration Wizard to convert the above Microsoft database table structure, I got errors that both datatype for my fields ASATID and Created was not supported (in MySQL).

What are the recommended ways to proceed such data type differences when performing a database migration from SQL Server to MySQL?

This answer will apply to your question : Insert Value based on One Column based on Value on another Column

AFAIK, and based on the link posted, you cannot create a computed column in MySQL.  You should either:

Do the calculation outside of the database, when inserting OR
Create a trigger on the table to populate the value for you OR
Create a view to represent the data in the way you want.


Aggregate an Aggregate In MSSQL I'm trying to return the number of rows where the count of a certain activity exceeds X and has occurred more then y number of times in a month.

This query tells me how many times on a given day for the current month the activity has occurred.

SELECT COUNT(DATEPART(DAY,myDATETIME))as dayCount,DATEPART(DAY,myDATETIME)as theDAY, Activity

FROM ALL_Activities

WHERE DATEPART(YEAR,myDATETIME)= DATEPART(YEAR,GETDATE())

AND DATEPART(MONTH,myDATETIME)= DATEPART(MONTH,GETDATE())

AND Activity = 'The door opened'

GROUP BY (DATEPART(DAY,myDATETIME)), Activity

HAVING COUNT(DATEPART(DAY,myDATETIME)) > 0 AND ???????

dayCount, theDAY, Activity

3   19  The door opened

7   18  The door opened

7   17  The door opened

1   15  The door opened

2   14  The door opened

1   13  The door opened

5   12  The door opened

1   11  The door opened

10  10  The door opened

4   9   The door opened

1   8   The door opened

2   7   The door opened

What I'm trying to end up with is a query that returns the activity when it has happened at least X times a day more then Y times a month such that A single row matching the criteria returned would have the total number of occurrences, the total number of days, and the activity. Is it possible to do this strictly within SQL?

44, 12, The door opened

If not that, then even the rows that meet the criteria would help. Meaning maybe return the rows when the number of days in the given month is >=5 where the times per day >=1. I tried using some combination of sub queries, @@ROWCOUNT, and other things, but couldn't get anything to work.

Using  with common_table_expression() and count() over(): we can get a running total for the count.

query:

with cte as (
  select distinct
      TheDate= convert(varchar(10),convert(date,mydatetime),120)
    , TheDay = datepart(day,mydatetime)
    , DailyActivityCount = count(*) over (
          partition by convert(date,mydatetime), activity
          )
    , runningActivityCount = count(*) over (
          partition by activity
          order by convert(date,mydatetime)
          )
    , Activity
  from all_activities
  where activity = 'The door opened'
    and mydatetime >= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime < dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
select
    *
  from cte
  --where runningActivityCount > 12
  order by TheDay

rextester results: http://rextester.com/WOW38735

+------------+--------+--------------------+----------------------+-----------------+
|  TheDate   | TheDay | DailyActivityCount | runningActivityCount |    Activity     |
+------------+--------+--------------------+----------------------+-----------------+
| 2017-01-07 |      7 |                  2 |                    2 | The door opened |
| 2017-01-08 |      8 |                  1 |                    3 | The door opened |
| 2017-01-09 |      9 |                  4 |                    7 | The door opened |
| 2017-01-10 |     10 |                 10 |                   17 | The door opened |
| 2017-01-11 |     11 |                  1 |                   18 | The door opened |
| 2017-01-12 |     12 |                  5 |                   23 | The door opened |
| 2017-01-13 |     13 |                  1 |                   24 | The door opened |
| 2017-01-14 |     14 |                  2 |                   26 | The door opened |
| 2017-01-15 |     15 |                  1 |                   27 | The door opened |
| 2017-01-17 |     17 |                  7 |                   34 | The door opened |
| 2017-01-18 |     18 |                  7 |                   41 | The door opened |
| 2017-01-19 |     19 |                  3 |                   44 | The door opened |
+------------+--------+--------------------+----------------------+-----------------+

update based on question in comment:

Declare @Activity varchar(64) = 'The door opened';
Declare @DailyThreshold int = 2;
Declare @Month varchar(7) =  '2017-01';
Declare @NthDay int = 5; -- for more then 5 days in a month(Y)

;with countCte as (
  select distinct
      TheMonth= convert(varchar(7),convert(date,mydatetime),120)
    , TheDate= convert(varchar(10),convert(date,mydatetime),120)
    , TheDay = datepart(day,mydatetime)
    , Activity
    , DailyActivityCount = count(*) over (
        partition by convert(date,mydatetime), activity
          )
    , runningActivityCount = count(*) over (
        partition by convert(varchar(7),convert(date,mydatetime),120), activity
        order by convert(date,mydatetime)
          )
    , MonthlyActivityCount = count(*) over (
        partition by convert(varchar(7),convert(date,mydatetime),120), activity
          )

  from all_activities
  where activity = @Activity
    and mydatetime >= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime < dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
, cte as (
    select *
      , rn = row_number() over (
          partition by Activity
          order by TheDate
          )
      --,
      from countCte
      where 1=1
        and DailyActivityCount > @DailyThreshold
        and TheMonth = @Month
)
select
    top 1
    *
  from cte
  where rn > @NthDay
  order by TheDay


Migration of Always On Availability Group from old to new SAN within same data center What are the steps required to move AAGs (version=SQL 2012 and OS=Windows 2012 R2 O) to new SAN within same data center?

It is a virtualized environment.

Instead of building new servers - can the VM and storage be v-motioned to new storage?

Method 4

Depending on the size (and contents) of the data store, if you connect your new SAN to your VMWare infrastructure, you can storage vMotion in addition to moving the guest itself.

If you have large databases in the Availability Group, I'd recommend a modification of Tony Hinkle's answer under Method 1.

Remove secondary replica (call it "B") from the AG
Stop the "B" guest
Storage vMotion "B"'s data store to the new SAN
Bring "B" guest online
Rejoin "B" to Availability Group
Manually failover the Availability Group to "B"
Remove the secondary (former primary, call it "A") from Availability Group
Stop the "A" guest
Storage vMotion "A"'s data store to the new SAN
Bring "A" guest online.
Rejoin "A" to Availability Group
Test with a failover to "A"

Note that depending on how long this takes, you may have to apply any log backups (WITH NORECOVERY, remember) taken during the storage vMotion process on the primary (either time) as part of steps 5 and 11.


whats the use of an autoincrement primary column? I would like to know when is it really needed to use an auto increment primary column, what is the value it adds?

my colleague suggests to have a separate auto increment column for a table as a primary key. It should be named ‘_id’. Primary key should not store any business data.

Another colleague is of different opinion that we can have a non-auto increment column to be a primary key as we plan not to have duplicates.

One approach without auto-increment primary column:

1)  Create Tables in Database with the below fields

Table I
a.  SLOC_ID (Primary Key)
b.  Gerrit
c.  Parent Key
d.  Files Inserted
e.  Files Deleted
f.  Gerrit Last User (Non Service Accounts) update time
g.  Current_Patch_Number

Table II
a.  SLOC_ID (One to Many Relationship)
b.  File Path
c.  File Name

Another approach with auto-increment is below

PK,FK   Column Name
PK(autoincrement)   sloc_id
FK  change_list_id

PK,FK   Column Name
PK  sloc_details_id
FK  sloc_id
    linesmodified
    lines deleted
    filepath
    latestpatch
    modification_type

where can I find more details here?which of the best solution?

There are a few advantages to using an auto-increment column as a primary key.

Unlikely to need to change: If your primary key is business data that's meaningful to human beings, you may be asked to change that at some point. Examples I have seen would include usernames based on a combination of company branch number and person's initials (want to change them if the person moves to a different branch); and company region names (may need to change if company reorganizes regions). Updating a user's username ultimately touched about 20 columns across about a dozen tables, and almost always caused blocking for long enough periods that users noticed.
Less fragmentation on table: Assuming that your primary key is also the clustered index for your table, records will also be inserted at the end of the table. since you won't be inserting a new record in between existing records, you won't be splitting pages in the middle of your data, which leads to fragmentation.
Smaller indexes: In some DBs, at least, the clustered index value is used in any other indexes to locate a row in the table. If your primary key is a varchar value that's 8 characters long on average, that will require twice as much space to store as a 4-byte integer value (4 times as much, if nvarchar).

For me, personally, the first reason was the strongest one, and the one that made me start including an autoincrement primary key in basically every table. Human-meaningful values will always be at risk of being changed by order of the business; putting those into a position where making that sort of changes could take hours and cause millions of other records to get changed is not acceptable, in my opinion.

UPDATE: As joanolo points out there are certain codes that may be meaningful to human beings, but which are part of established standards, and should (at least in theory) not change because of that. Examples would include an ISO 3166 country code, an ISO 4217 currency code, an ISO 639 language code, or a UN ISIC activity code. He notes these are usually a reasonable size (char(4) or less), so they wouldn't cause overly large indexes, and they are generally static, and thus fragmentation wouldn't be a concern. So, it would make sense to use these codes as primary keys in tables based on this sort of information. It's an excellent point, and I agree.


Remote to a remote oracle server and get running instances? I need to make a shell script that takes a list of servers and and check the running instances then run sql script in each instance!

now I do not want it to ask me for a password!

it's redhat and centos servers, I can not install any additional packages like sshpass, ...

any ideas?

Agree with first responder.  The only way to do what you want, and not 100% sure it is possible, would be to have very high level permissions on all servers, or have special permission settings.  Any system admin would be justified in asking for any order to set something like this up in writing.  The same goes for the DBAs for the DBs you want to run "sql script in each instance", without user/password signon.  My advice is you need to find a way that works with the standard server/DB security, and stop finding a way around them.


Performance tuning in Sybase Does Sybase have a tool like AWR in Oracle which can be used to identify high load SQL, tell the important waits that are happening, help in understanding the workload?
What are the main tools to be used to identify high load SQL and tune query performance?

Not specifically like AWR, but there are a number of built-in tools that can be deployed.

The "mda" tables will be your best friends for identifying poorly performing queries.

Rob Verschoor's site has detailed information on how to use them along with some pre-built tools.

http://www.sypron.nl/mda.html

Official documentation can be found here:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc70002.1570/html/title.htm

sp_sysmon, monserver and historical server may be of little use. They're more suited to server configuration.


sql auth vs nt auth in SQL server Ok, so everything I read says best practice is to us NT authentication in SQL server. And I can see some advantages to that but using NT auth how do I force users to access the data using only my application and not some other tool such as SSMS? It would seem to me that it would be better to allow only my application to connect to SQL server using SQL auth and password the user does not know and controlling security through the application. Am I missing something here? Is there a way to block SSMS and other database tools from connecting to my application's database?

Ok, so everything I read says best practice is to us NT authentication in SQL
  server

Read more. Please. Because while there are good reasons to do that in certain scenarios, there are good reasons NOT to do it in other scenarios.

  And I can see some advantages to that but using NT auth how do I force users
  to access the data using only my application and not some other tool such as
  SSMS?

Since maybe 25 years or so a standard approach to program applications is to have a SERVER. The user connects to the SERVER and ONLY the server connects to the database. Problem solved.

  Am I missing something here?

Stating the obvious: A lot more reading than you did so far.

  Is there a way to block SSMS and other database tools from connecting to my
  application's database?

Your reading did not get to application passwords in the documentation (https://msdn.microsoft.com/en-us/library/ms181491.aspx is titled CREATE APPLICATION ROLE) which would allow an application to post-authenticate with a password. Not that this is a good approach - but it sis AN approach that was introduce some versions back. This way rights could be on an application, and unless the user knows the role password of the application - out of luck.

But really, the standard approach is to use an application server and only the app server can connect. And often this does not use NT authentication as the connection password is coded in a server config file.


Many-to-One replication / Transactional Replication I have several PostgreSQL DBs in different geographical locations (local sites).

Each local site DB have the same schema, but unique data. For example, Take a table with columns: Site_ID, Department_ID, Department_Name. Site_ID is unique for each site.
I want to collect all the data from the local site DBs into a centralised DB (PostgreSQL again) which acts as a data warehouse.
The corresponding example table on the centralised DB will have the same columns as above. All local site data will go into this table. Each site data designated by the Site_ID, of course.

Question: How to achieve this with PostgreSQL replication methods (streaming/multi-master UDR/BDR/etc.)
I see this can be done with SQLServer using Transactional Replication. What is the best way to achieve this functionality with PostgreSQL?

Restriction: The local sites can make only outgoing network connections (i.e. no inbound connections due to firewall restrictions)

I see at least two options:

if you don't need near-real-time replication, but just once a day (or maybe every hour) is enough for you, and the amount of data is small, you can quite simply create a script that will run in each remote location:

connect to the local database
do a dump of the local data (pg_dump --data-only should be fine)
connect to the remote database
remove all data for that location from it
push the data from the dump.

if you need near-real-time replication, or the volume of data is large (but the changes are small in comparison), you could look into Slony. Haven't used it in a while, and it's not necessarily its typical use scenario, but I believe it should work as the datasets are distinct.

Slony uses triggers on the tables to store changes in a log, and then daemons that transfer the logs from one server to the other, and apply the changes. It would be worth checking if this would work in the "can't connect from central to remote" scenario, though. If it doesn't, you'll need to consider some form of VPN (which you should anyway).


Is there a way to export Oracle's UNDO? I tried exp utility to dump all database. Looks like this exports only the last version of data skipping undo log. Using flashback queries I see:

01466. 00000 -  "unable to read data - table definition has changed"
*Cause:    Query parsed after tbl (or index) change, and executed
           w/old snapshot

What I'm trying to do is to capture db changes, make backup for later use with the ability to flashback to timestamp.

With rman backup I have similar situation:

ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_2472002983$"  too small
01555. 00000 -  "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause:    rollback records needed by a reader for consistent read are overwritten by other writers`.

Update:
I managed to do what I needed only by increasing undo retention and direct copying of data files and control file modification on cloned instance.

You could insert the results of a flashback query or flashback versions query into a table and export that table or you could do your own versioning.  You could also look at the Change Data Capture feature which may meet your requirements.  The real answer to your specific question though is...

No, you can't do that.


SQL Server: Log Shipping in "restoring" state: Can I temporarily take it out of restoring to check it? I know it is normal for the target db to be in "restoring" state

I also would like to do a few queries as a sanity check that the data is all up to date.

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain?

thanks!

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY.  This will allow you to query the secondary db for any reporting, etc.

When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it currently sits without worrying about any users accessing/changing things.

When you use STANDBY mode, the database restores in NORECOVERY mode, then looks at and rolls any open transactions. Once that is complete, users can access the database and it will be in read-only mode. When the next log is restored, the database disconnects all users and repeats the rollback/forward process and then, again, places the database in read-only mode. Lather, rinse, repeat.

Think of it as a transaction log restore.  When you restore each transaction log, you leave the DB in NORECOVERY so that you can continue to append any additional log files until you have restored to the point you require.  Then, you set it to RECOVERY and the database is able to be read. Make sense?

@DenisT brought up a good point that the 'disconnect all users' option must be checked in order for all users to get booted.  Otherwise, the log restore will fail.

Here's a great article on it
http://askmesql.blogspot.se/2011/01/log-shipping-norecovery-vs-standby-mode.html


Choosing A PostgreSQL Authentication Method For A Large Course I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone.

I would like students to be able to do the following, and (of course) have their accounts reasonably secure from attack:

Use psql on the same machine where the database server runs.
Use pgAdmin on their local (probably Windows-based) machine.
Write JDBC applications that run from their local machine.
Write JDBC webapps that run through apache on the same machine where the database server runs.

There are many authentication methods available (see http://www.postgresql.org/docs/9.2/static/auth-methods.html), but none seem to meet all of my requirements well.

Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the peer mechanism.
Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option A is what I have always used myself, and it would be my preference.  But I believe that it would rule out accessing the database from any client other than psql running on the same machine.

Option B seems to be the most flexible.  But it also seems terribly ugly for students to need to set and maintain passwords in two disparate systems.

Option C would only allow connecting from clients on remote machines, which is not really acceptable.

I am fairly unfamiliar with GSSAPI / Kerberos, but it does not really sound like what I want either.  My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.  Is there some better option for my requirements than B above?

Sounds like no fuss to me:

Create an OS account for each user, preferably with simple ssh key based authentication;
in pg_hba.conf use peer for local connections;
Optionally also use ident for host connections from 127.0.0.1 and run identd on the server. Not strictly required.
Set a password on their role for use with md5 authentication, then use md5 authentication for connections from allowed remote hosts with the hostssl method.

That way they'll be able to connect over SSL directly with a password, and locally (after ssh'ing in) with no password prompt.

What you describe:

  My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.

sounds a bit like remote ident. This exists and works, but it's completely and utterly insecure. It was written for the days when "my personal computer" would elicit gales of laughter and nobody but nobody had root. These days anyone can set up a laptop (or phone) to claim to be any user they want over ident, so it's completely useless except on the local loopback where you control it.

It does sound like Kerberos is closer to what you want, but honestly it's going to be way more messing around than seems worthwhile. I'd just use peer for local connections and md5 for remote ones.


SQL - Select everything after a certain row After the following query:
SELECT
    my_table.ID,
    my_table.DATETIME,
    my_table.VALUE,
    diagnostics_keys.type as type,
    diagnostics_keys.key,
    diagnostics_keys.unit,
    diagnostics_policies.type as policy,
    pair_device_voucher.device_id,
    pair_device_voucher.voucher_id
FROM
    my_table
    inner join diagnostics_keys on my_table.DIAGNOSTIC_TYPE_ID = diagnostics_keys.ID
    inner join diagnostics_policies on my_table.POLICY = diagnostics_policies.ID
    inner join pair_device_voucher on my_table.DVP_ID = pair_device_voucher.ID
WHERE
    (
        (diagnostics_keys.type = 'AVGKMKWH')
        OR (diagnostics_keys.type = 'STATUS')
    )
    AND (
        DATETIME BETWEEN 1612515739000
        AND 1612516445000
    )
ORDER BY
    DATETIME ASC

I am getting the following results as they are in the image:

How can I adapt the query in order to select only the rows/entries that follow after the condition type= status and value = 40 (the rows that are inside on the red rectangles)?
Or to exclude, if the status = 100/20/whatever, do not select the following entries.
These are two commands for the sample data:
CREATE TABLE my_table(
         ID varchar(255),
         Datetime_ varchar(255),
         Value_ varchar(255),
         Type_ varchar(255),
         Key_ varchar(255),
         Unit_ varchar(255),
         Policy_ varchar(255),
         Device_ varchar(255),
         Voucher_ varchar(255) )

INSERT INTO `my_table`(
                      `ID`,
                      `Datetime_`,
                      `Value_`,
                      `Type_`,
                      `Key_`,
                      `Unit_`,
                      `Policy_`,
                      `Device_`,
                      `Voucher_`)
             VALUES ('1','1612515739000','40','STATUS','203_sts','','periodic','12345','12345'),
('2','1612515745000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('4','1612515754000','40','STATUS','203_sts','','periodic','12345','12345'),
('3','1612515760000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('56','1612515775000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('6','1612515775000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('7','1612515790000','100','STATUS','203_sts','','periodic','12345','12345'),
('8','1612515805000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('9','1612515813000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('10','1612515820000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('11','1612515835000','40','STATUS','203_sts','','periodic','12345','12345'),
('12','1612515850000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345')

Thank you in advance.

Perhaps something like this:
SELECT
  ...,
  (
    SELECT value
    FROM
      (SELECT MAX(mtsub.ID) maxid FROM mytable mtsub WHERE mtsub.type_ = 'STATUS' AND mtsub.id < my.ID) maxids
      INNER JOIN my_table mtsub2 ON mtsub2.id = maxes.maxid
  ) recent_status_value
FROM
  my_table mt ...

This should result in a column that has the most recently encountered status row's value - you can then where it for being equal to 40 in an outer query
Not a huge fan of "selects in the select list" coordination but if you don't have window functions available to help spread the most recent value of status over some rows..
Where'ing the mtsub queries to restrict their rows may help performance too; I left it out for clarity of code


Optimizing UDF - Is Using DUAL Expensive I am new to Oracle technology (primarily versed in SQL Server) and I am having trouble with the performance of a UDF.

The purpose of the UDF is to take some inputs for an "event", and look up an "expected time for event b to occur". The function returns a single DATE value.

The first thing that occurs is a SELECT ... INTO [variable] FROM DUAL; a variable. Finally a lookup occurs in a reference table (only ~30k rows), and that is stored in the return variable.

It took 8 hours to process on 800k rows. I can select all of the columns without using the function in less than 40 seconds.

My main question is: Is the use of DUAL costing me?

We are on an Oracle 11g RAC.

No. Oracle knows that DUAL is "in-memory" table. Your problem is somewhere else.

BTW: you can also use call fname(...); statement.


When were there changes in the memory settings of SQL Server Is there a log, query or any other way to see when there was a change made to the memory settings of SQL Server?

You could try reading the sql server error log searching for memory configuration changes.

EXEC xp_readerrorlog 0, 1, "memory"

Example on my test machine:

More info on xp_readerrorlog


Blocked sessions waiting with PAGELATCH_* wait types? Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types?

I previously assumed that SQL server would only report blocking sessions in the blocking_session_Id column. If the blocked sessions were waiting for a logical lock and not anything else such as a PAGELATCH_*.

I previously assumed that SQL Server would only report a blocking session in the blocking_session_id column, if the blocked sessions were waiting for a logical lock and not anything else such as a PAGELATCH_*.

You are using Adam Machanic's sp_WhoIsActive procedure, not a built-in SQL Server facility. Adam's procedure reports all 'interesting' causes of blocking, not just lock blocking. The underlying information comes from various sources, including sysprocesses, sys.dm_exec_requests, and sys.dm_os_waiting_tasks.

A task can wait for other things besides a lock. It may be able, for example, to acquire an exclusive lock on a row on a page, but then still have to wait to acquire an exclusive page latch (because other tasks have latched the same page in an incompatible mode).

In your example, the PAGELATCH_EX exclusive page latches are on regular data or index pages, not PFS, GAM, SGAM, DCM, or BCM pages, as indicated by the (*) at the end of the wait_info.

You can find the documentation (blog entries) for sp_WhoIsActive here.


Proper form in relational meal database I am trying to design a database solution for the following:

A table containing all nutritional information regarding a given ingredient; ingredient_ID is the PK here but ingredient name has a 4 character unique constraint upon it too (so OATS for a record containing nutritional information on oats)

A 'join' table containing a meal name, say BRKFST, ingredient_ID, and the given quantity for that ingredient

Possibly a third table containing just meal name

The first table has already been created so if that could be borne in mind I would appreciate it.
The inclusion of the third table is where I am struggling. I have read through topics on related matters, here, here, and here, but none of these quite seem to answer my query or deal with different designs such that I cannot see how they would adapt to my own. I have also read through the post here and it led me to the current design I have sketched out.
As it stands, this is what I have:

with meal_name and ingredient_id forming a composite PK in the second table. This is due to each record referring to a meal (and potentially an ingredient) multiple times.
So BRKFST might have two records; one for OATS, and one for MILK; similarly, MILK may appear elsewhere, in another meal.
I just wonder then what the use of the third table is in my design and was considering just having the two but was not sure if I might be missing something here. I have no need to include a description and do not want any recipe instructions.

If a meal consists of nothing but a name, and that name never changes, and there are no two meals that can have the same name, you don't need the meal table, because the name is de facto a primary key.
If any of these conditions are not guaranteed, or you might at some later time add other attributes to a meal (like a boolean column vegetarian), you need a separate table for the meals.


Why PostgreSQL volatile function is faster than inline subquery? The following examples have been tested in PostgreSQL versions 11 and 12.
A few of the tables involved have around 2m rows, others not more than 250k.
I have very big and nested queries like this (not the actual query, just an example):
SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM
  (SELECT table2.some_id AS label4,
                       table2.id AS label5,
                       table3.some_date AS label6,
                       CASE
                           WHEN (table2.some_value IS NOT NULL
                                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
                           ELSE table4.table4
                       END AS label6
   FROM table3
   JOIN
     (SELECT coalesce(table5.table5) AS table4,
             coalesce(table5.table5_1) AS table5_1,
             coalesce(table5.table5_2) AS table5_2,
             coalesce(table5.table5_3) AS table54_3
      FROM
        (SELECT table2.some_value * 1 AS table5,
                table2.some_id AS table5_some_id,
                table2.id AS table5_id,
                table3.some_date AS table5_some_date
         FROM table3
         JOIN table2 ON table2.some_other_id IN
           (SELECT unnest(something_nested.ids) AS unnest_1
            FROM
              (SELECT ...

The query above takes around 49s to execute.
Then I decided to convert some of these subqueries into functions. It was literally just a cut/paste of some pieces of the big query, resulting in a final query that reads like this:
SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM some_function()

-- some_function created as "STABLE":
SELECT table2.some_id AS label4,
       table2.id AS label5,
       table3.some_date AS label6,
       CASE
           WHEN (table2.some_value IS NOT NULL
                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
           ELSE table4.table4
       END AS label6
FROM some_other_function()

The query above takes the same 49s to execute.
However, as soon as I changed one function (any of them) to VOLATILE, the query magically executes in less than 250ms.
My questions are:

Why VOLATILE is WAY faster than STABLE in this case? Is it safe to use VOLATILE?
Why declaring subqueries as function executes faster than just inlining them? Is there a flag or some way to optimise this kind of scenario?

EDIT
I tried to add the execution plan here but the text body is limited to 30000 characters and one plan alone is bigger than that.
This is the obfuscated execution plan for the first query (inlined subqueries)
https://explain.depesz.com/s/A3mG
This is the obfuscated execution plan for the query with volatile functions that runs super fast
https://explain.depesz.com/s/Yhs1
EDIT 2
This configuration did the trick, the first query now runs in 230ms.
SET enable_nestloop = off;
I'm interested to know, behind the scenes, what Postgres is doing that is making these queries run so quickly.

Volatility has to do with the expected results and the type of query so the query planner can be more efficient.
I suspect it has something to do with that your query is performing an index scan.
Without an explain it is very difficult to know what the query planner is doing. Please add this.


How to convert a bytea column to text? how to convert a bytea  column to text in PostgreSQL so that I can read the column properly in PGADMIN?
I have the following SQL query in the PGADMIN's query editor:
SELECT event_type, created_at, encode(metadata::bytea, 'escape')
FROM public.events
ORDER BY created_at DESC
LIMIT 100

However, it produces an encoded column with each records more or less ressembling the following output:
\203t\00000some_textd\000some_other_textd\0000

How can I get rid of this encoded, so that I only see the original value of the column, in the text format:
some_text some_data

What I have also tried:
SELECT event_id, event_type, created_at, decode((encode(metadata, 'escape')::text), 'escape')
FROM public.events
ORDER BY created_at DESC
LIMIT 100

But in the above case, the query returns a decode column of type bytea and I only see the field [binary data] for each record of the column.
I have also tried the first two answers mentioned here without success and can't properly translate the last answer to my query.

How can I get rid of this encoded, so that I only see the original
value of the column, in the text format

Bytes have 256 possible values, when there are only about 95 visualizable ASCII characters, (the range [32:126]), so the mapping between ASCII text and binary representation cannot be one-to-one.
The format produced by encode(metadata::bytea, 'escape') is just one possible representation, where some bytes are represented by sequences of characters. Others common representations are hexadecimal (outputs each byte as 2 characters) and base64 (outputs each 3 bytes as 4 characters).
If you don't want these and have a specific idea on how your metadata column should be output as text, then you can always provide a user function instead of  encode. Right now your question does not specify what output you want to see, only that it's not the escape format.


Two queries usually run fast but get slow when joined There are those 2 tables

CREATE TABLE IF NOT EXISTS `media` (
  `mid` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `order_type` int(11) NOT NULL,
  `media_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `performed_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `flag` int(11) NOT NULL,
  PRIMARY KEY (`mid`),
  UNIQUE KEY `mid` (`mid`),
  UNIQUE KEY `media_id` (`user`,`media_id`),
  KEY `user` (`user`,`timestamp`),
  KEY `performed_by` (`performed_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306 ;

CREATE TABLE IF NOT EXISTS `media_resources_used` (
  `muid` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` varchar(50) NOT NULL,
  `resources_used` text NOT NULL,
  PRIMARY KEY (`muid`),
  UNIQUE KEY `media_id` (`media_id`),
  KEY `media_id_2` (`media_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8_unicode_ci AUTO_INCREMENT=1507815;

They're connected by media_id. One row from media_resources_used can have n rows at media.

They both have over 1 million entries.

media_resources_used is used to determine what resources have been used on a media with a media_id and this works like a queue.

First I reserve a media row

UPDATE media SET performed_by = 'worker_id' WHERE performed_by = NULL LIMIT 1

Then I want to fetch the media row with the corresponding media_resources_used info

SELECT * FROM media AS me INNER JOIN media_resources_used AS au ON me.media_id = au.media_id WHERE me.performed_by = 'worker_id'

But this takes around 20 seconds to complete.

EXPLAIN shows

+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys |     key      | key_len |  ref  |  rows   |                      Extra                      |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
|  1 | SIMPLE      | me    | ref  | performed_by  | performed_by | 63      | const |       1 | Using index condition                           |
|  1 | SIMPLE      | au    | ALL  | NULL          | NULL         | NULL    | NULL  | 1439398 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+

And it seems the index at media_id isn't even used.

When I run

SELECT * FROM media WHERE performed_by = 'worker_id'
SELECT * FROM media_resources_used WHERE media_id = '$media_id_from_above'

separated they both execute under 0.001 seconds.

What could be the issue there?

Change the line

UNIQUE KEY media_id (user,media_id)

to

UNIQUE KEY media_id (media_id)

then execute the query it will perform better


How to get the last 10 query run on MongoDB? I am using mongoose driver in my Node.js app and I want to know what queries mongoose run behind the scene when I call available function on mongoose model.

e.g. I am saving a signup information for a user using mongoose create function and as per its definition: it should create the document in the database with the provided information. It returns a document to the caller, so I am curious to know whether it run extra query to get the returned document or it makes it from the provided information. In the first case it will take a network bandwidth for the already available information so I want to avoid this by using any other function but I don't think it is working like this.

Anyway, please suggest me how can I see the last 10 queries that hits MongoDB Database?

On the database side, you can enable Auditing, but at this time, only available on MongoDB Enterprise.


Custom aggregation for non-leaf level nodes to avoid double counting I have a fact table with the following columns:

WorkerName
OrderId
NumberOfPackagesPerOrder

Note that neither WorkerName nor OrderId is unique in this fact table.
Multiple users could work on a single OrderId
Also note that NumberOfPackagesPerOrder only depends on the OrderId, i.e. for every OrderId, the number of NumberOfPackagesPerOrder will be the same.

I am trying to build a cube to report hierarchical report from this data by employee hierarchy:

ManagerLevel1           [Total orders completed] [Total Packages Shipped]
    Managerlevel2
        .....
        ManagerLevelN
                  Worker

Since multiple workers could work on the same order, I need to avoid double counting  PackagesPerOrder for manager levels (non leaf nodes).

How to do this? What MDX Script do I need to properly SUM NumberOfPackagesPerOrder by summing these only after DISTINCTing these by OrderId?

Note1 - the employment hierarchy is defined recursively - the levels are not static.
Note2 - managers, those on a non-leaf nodes, could also potentially ship packages.

The right approach for the problem is to use many to many relationship in SSAS.

More details here https://stackoverflow.com/questions/10971593/custom-aggregation-for-non-leaf-level-nodes-to-avoid-double-counting and here http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf.

Then no MDX is required.


Number of sessions connected to database I need to know how many sessions was connected in a period of time for each snapshot.  Is this possible in Oracle?

Thanks


Is there a UNION ALL equivalent for columns? It's possible to simulate a loop in SQL Server with a self-referencing CTE and a union all, something like the following:

declare @a int = 1;
declare @b int = 5;

;with Tbl as (
    select @a a
    UNION ALL
    select (a + 1) a
    from Tbl
    where (a + 1) < @b
)
select * from Tbl

This is useful when you want to create a list of dates from a start date to an end date and then join on those specific dates.

I could also imagine wanting to do that for columns as well. Is there a means of achieving this in SQL Server without using dynamic SQL? I would probably want to save such a query as a view, and not an SP (though I know that this could be achieved via dynamic SQL)

Is there a UNION ALL equivalent for columns?

You are talking about the usage of a UNION in a CTE to generate dates. Here I show how you can generate dates in columns.

You can use a PIVOT Clause. However, there is a limit: The column names must be known in advance. If, for instance, you want to display the data per month, you can use the day numbers as column names:

SELECT *
FROM
    (SELECT DAY([Date]) AS d,
            MONTH([Date]) AS m,
            YEAR([Date]) AS y,
            Amount
     FROM MyTable) src
PIVOT (
    SUM(Amount)
    FOR d IN ( [1],  [2],  [3],  [4],  [5],  [6],  [7],  [8],  [9], [10],
              [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
              [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) piv;

The result looks like this (I clipped the image at 11 days but in reality, there are columns up to 31):

If you must display more dates, you can use the number of days since the start date as column names instead. If you create a report (using some reporting tool) based on this query, you can add this number to the start date to get real dates again for the columns headers.

Since you must specify the column names explicitly, no CTE is necessary for this part, but you can combine the pivot query with a CTE to generate the rows.


Items and their tags This is my table design:

  CREATE TABLE IF NOT EXISTS `items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL,
      `description` mediumtext NOT NULL,
      `user_id` bigint(100) NOT NULL,
      `to_read` tinyint(1) NOT NULL DEFAULT '0',
      `added_at` datetime NOT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(100) NOT NULL,
  `item_id` int(11) NOT NULL,
  `tag` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My two queries:

SELECT items.*,
    (SELECT GROUP_CONCAT(tags.tag)
       FROM tags
      WHERE tags.item_id = items.id) AS tags
FROM items WHERE items.user_id = 0;

Three-table layout with a tags_items table:

SELECT items.*,
    (SELECT GROUP_CONCAT(tags.tag) FROM tags
       WHERE tags.id = tags_items.tag_id) AS tags
  FROM items
  LEFT JOIN tags_items
         ON tags_items.item_id = items.id
 WHERE items.user_id = 0;

Question:

Is it good to go with 2 table design, or 3 table design?

When I tried out 3 table design, my second query, rewritten for 3-table design doesn't work properly, it simply doesn't concatenate properly like the 2nd table query.

As I hinted in my first comment, you have to move the join in the subquery, so you still have only one row of output per item. Example can be tested at http://sqlfiddle.com/#!9/11b37/3

SELECT items.*,
  (SELECT GROUP_CONCAT(tags.tag)
   FROM tags
   JOIN tags_items ON tags_items.tag_id = tags.id
   WHERE items.id = tags_items.item_id) AS tags
FROM items
WHERE items.user_id = 0;

Using 3 tables is the right design. With only two you would end up with multiple rows for the same tag name.

Your original tables have redundant user_id column in tags table (and that same would apply for tags_items) - as one tag/tags_items row can only be linked with one item the user_id is defined by it.


How to identify a user (Oracle) that is locked due to unsuccessful login attempts How to differentiate in oracle a user deliberately locked by the security administrator and locked due to unsuccessful login attempts

Select the user information from dba_users and look at the ACCOUNT_STATUS. If the status is LOCKED then the user was deliberately locked. If it is LOCKED(TIMED) then it is locked because of multiple wrong passwords. If you have set the PASSWORD_LOCK_TIME then the user will be unlocked after this time is reached.

select   username, account_status
from     dba_users
where    username='A-USERNAME'
/


Unable to connect to Oracle 11g server remotely using SQL developer ORA-12520 Hello everyone I'm unable to connect to my Oracle Server remotely using SQL developer ....while I can connect from SQL developer when I'm in the server directly ....I get the following error when I try to test my TNS listener

ORA-12520: TNS:listener could not find available handler for requested type of server

I followed the Oracle help ...and I found out that My Oracle service ORCL is registered

this is from Oracle support

ORA-12520: TNS:listener could not find available handler for requested type of server

Cause: None of the known and available service handlers for requested type of server (dedicated or shared) are appropriate for the client connection.
Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener and that the appropriate handlers are accepting connections.

this is the output of lsnrctl services

`

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 15-
     SEP-2017 16:49
     :00
     Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: OSAMA-PC, pid: 3240>
         (ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=49212))
    The command completed successfully

Lnsrctl status output
`

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-SEP-2017
18:2
:26

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 -
Prod
ction
Start Date                17-SEP-2017 17:39:16
Uptime                    0 days 0 hr. 48 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\list
ner.ora
Listener Log File         c:\app\mod\diag\tnslsnr\osama-
PC\listener\alert\log.x
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Windows\system32>

tnsping orcl result

C:\Windows\system32>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-
SEP-2
017 18:29:11

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

Used parameter files:
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)
(HOST = osama-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (20 msec)

C:\Windows\system32>

A couple of things can trigger this error. My guess is this the first thing below.
Max Process Limit Reached
You may have have exceeded the maximum number of processes. (see here and here.)
This query will show you the current utilization, max utilization, initial allocation, and limit for Processes:  select * from v$resource_limit where resource_name in ('processes','sessions');. You will need to run the query from the server, since you can't get in remotely. If Max Utilization is at or near your limit value, this may be the problem.
You can see what the process limit is by logging into Oracle as sysdba from the server. show parameter processes will display something that says processes and has an integer value. You can extend this value by issuing this command: alter system set processes=XXX scope=spfile; set XXX to a higher value. Oracle recommends 200 for a "small" system and 400 for a "medium" system. You will have to shutdown and restart Oracle to enact the change.
Oracle bug
Oracle mentions a Bug, bug number 13831723, in Oracle 11.1. Your information seems to indicate you're running 11.2, so this shouldn't apply. But if it does, then you'll need to install a patch from Oracle. Log into Oracle support and search for Oracle DOC ID 1566704.1. They say it is fixed in 11.1.1.7.0 and up.


Mysql sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 with replication I'm looking into sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 to improve write performance. There is this question, but it doesn't address the specific problems with replication.

I have an application that processes incoming log data with dozens of commits per second, causing a lot of flushing to disk, and losing one second of data is not a problem for the increase in speed it gives. However, The docs state:

  For durability and consistency in a replication setup that uses InnoDB
  with transactions [set both options to 1].

But it doesn't specify the issues with consistency and durability. I would like to know what exactly can go wrong. If you have a hardware crash and the slave becomes live, it may be one second behind the original master. Are there subsequent problems in getting that failed master back online (as slave)? Could there be mismatches in ID's, duplicate rows, etc?

As I said, loosing data is OK, but breaking replication is not.

Edit: BTW, by now, the problem is solved another way. Long story, but an comprehensive schema change made it possible run log processors in parallel. Still slow per process, but many at a time makes it fast.

Another way to get past these possible problems is to use a Galera Cluster (eg, MariaDB 10 or PXC).  With Galera, you can (should) be sloppy about such syncs; instead rely on the cluster's ability to recover automatically and efficiently from a dead machine, whether dead from power, OS, or whatever.  One caveat, though:  You need to make sure a single failure (such as power) won't kill all nodes in the cluster simultaneously.

With Galera, you can write and read any node.  3 is the minimal number of nodes to provide automatic recovery from the loss of any one node.  You can configure it to do all writes to a specific node, thereby pretending to have Master+Slaves configuration.  (In some applications, this is actually beneficial.)

Back to your situation.  Set both of those settings to 1 or the Master; set them to 50 and 2 for the Slaves, then...  If a Slave dies, assume it is corrupt and rebuild it; do not try to recover the data, since the necessary syncs may not have been done.  To lighten the load on the Master, don't send standalone reads to it.


Huge mysqldump import I have a 200+GB mysql dump file that I need to import into the local sql server running on my development machine. I attempted a naive dump restore using the mysql < db_backup.sql approach and managed to restore around 85% of the database before hitting a

  mysql server has gone away

error. This took in the region of 5 days.

I'd like to know if there's a way of comparing the partially restored DB to the dump file to understand which tables are still pending or if there's a significantly (at least 4x) quicker method to restore from a huge dump file.

Any advice would be appreciated. Thanks.

Thank You for additional information
If it standard and default settings, it not optimal for huge data, so You can start from:

locate my.cnf file (it could be few different locations, depending
from installation)
in mysql run SHOW VARIABLES LIKE 'innodb_file_per_table'; if it OFF -
good to drop ibdata file and re-create it

adjust for start :

innodb_buffer_pool_size = 4G (or 8G)
innodb_log_file_size = 1G
innodb_file_per_table= ON (if it OFF)
innodb_flush_log_at_trx_commit = 0 (because it development machine)
innodb_log_buffer_size = 64M
max_allowed_packet = 128M

It must increase speed of work

if innodb_file_per_table= OFF it mean all You databases in single files and mysql will not release space when You delete and/or optimise tables

with innodb_file_per_table= ON, effect from TRUNCATE/OPTIMISE available momentary and this is important for dev machine.

All above work always and if You decide re-load all dump, and if will go other way.

For already restored:

check in MySQL "SHOW DATABASES", by default it dump and restore in
alphabetical order, so question will be for last in list
for last database, compare table by table with original (if available), but better re-load it completely

I not sure - what inside Your huge dump, so can not give universal recommendations - how to properly split it.


PostgreSQL "upsert" tuning with unique constraint I have the following table (PostgreSQL 9.6 but could upgrade):

# \d annotation_annotation
                                       Table "public.annotation_annotation"
      Column       |          Type           |                             Modifiers
-------------------+-------------------------+--------------------------------------------------------------------
 id                | bigint                  | not null default nextval('annotation_annotation_id_seq'::regclass)
 release           | character varying(32)   | not null
 chromosome        | character varying(32)   | not null
 position          | integer                 | not null
 reference         | character varying(512)  | not null
 alternative       | character varying(512)  | not null
 database          | character varying(8)    |
 effect            | character varying(64)[] | not null
 gene_id           | character varying(64)   |
 transcript_id     | character varying(64)   |
 transcript_coding | boolean                 |
 hgvs_c            | character varying(512)  |
 hgvs_p            | character varying(512)  |
Indexes:
    "annotation_annotation_pkey" PRIMARY KEY, btree (id)
    "annotation_annotation_release_chromosome_posit_d102b713_uniq" UNIQUE CONSTRAINT, btree (release, chromosome, "position", reference, alternative, transcript_id)
    "annotation__release_3f254e_idx" btree (release, chromosome, "position", reference, alternative, gene_id)

I'm running bulk insertions with ON CONFLICT IGNORE of tens of millions of files into the table. I'm expecting a high number of duplicates (>90%). The table is append-only. Using pg_activity, I'm seeing that PostgreSQL is spending more time reading than writing (as I would expect if the number of duplicates is high).

What would be a good way of tuning "upsert" performance here:

Would partitioning with PostgreSQL 11 help? I could provide an additional integer "chromosome_no" column that could be used for hashed partitioning.
Is it possible to use bloom indices to speedup "upsert" performance with unique constraints? (EDIT I realize that bloom filters will not help because they err into the wrong direction)

Partitioning the table would only help if the data in the files you are trying to insert are also partitioned using the same scheme.

If you sort the data to be inserted into the same order as the unique index is defined on, that should help if the bottleneck is reading from the index.  But if the table is not clustered in the same order as the index, and the bottleneck is reading from the table, this might not help much.

For this type of task, I would usually deduplicate outside the database.  You might also look into using something like file_fdw to map the new data into the database, and then query and insert for new data.


How to index for inequality queries? I have query that is excluding data based on the value of a float column

select *
from My_Table
where my_Float_column != 0 and my_Float_column is not null

I don't want to index a float type if I can help it. Would the execution plan be clever enough to use a filtered index like the below (Where I am only indexing the 0 and null values) to increase performance?

CREATE NONCLUSTERED INDEX IX_My_Table_Float_Filtered
    ON My_Table (my_Float_column)
    WHERE my_Float_column = 0 or my_Float_column is null

Would the execution plan be clever enough to use a filtered index like the below (Where I am only indexing the 0 and null values) to increase performance?

No. There's no direct support in SQL Server for the sort of single-operator row-rejection you seem to have in mind.

Speaking more generally, the filtered index is not directly helpful for the given query, and isn't a valid index filter definition anyway. You could create an indexed view containing those predicates instead, but it still wouldn't be helpful as way to locate rows for the target query. You could rewrite the query to use the indexed view to exclude rows fetched with a separate full scan, but it's tough to see how that would be a good idea in general.

The closest you could get would probably be a scan of the indexed view populating a bitmap filter, with that filter applied as part of a full scan of the target table. It might be challenging to get this query plan shape reliably.

You could also use an indexed persisted computed column using a CASE expression, but again this would require rewriting the original query, and would require storage for every row in the table, plus the index.

From the information given, it seems the best you can do is to define the index as:

CREATE NONCLUSTERED INDEX IX_dbo_My_Table__Float_Filtered
ON dbo.My_Table (my_Float_column)
WHERE
    my_Float_column <> 0
    AND my_Float_column IS NOT NULL;


Understanding SQL Server permissions I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition.  I am trying to understand how the permissions work.

What I can see is (via the properties of many of these entities)

My Server Login can be linked to a Database User
My Database User can have one or more Database roles
One of the Database Roles is db_datawriter which owns schema db_datawriter

However at that point the trail goes cold.  Schema db_datawriter has a permissions page under its properties which is blank.

What defines precisely what schema db_datawriter's permissions are?

db_datawriter has no items in the permissions page because it doesn't have explicit object permissions as such. Rights are implied by the role.

MSDN for db_datawriter says

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

It has INSERT, UPDATE, DELETE on tables as per another MSDB page Permissions of Fixed Database Roles

  Granted: DELETE, INSERT, UPDATE

Finally, what does the DB engine say (SQL Server 2008 R2)?

EXEC sp_dbfixedrolepermission 'db_datawriter'

db_datawriter   DELETE permission on any object
db_datawriter   INSERT permission on any object
db_datawriter   UPDATE permission on any object

The MSDN pages for SQL Server 2008 are here (different page hierarchy)


SQLCMD :connect dynamic parameter In SSMS I have this

:SETVAR DBSrvrInst MyDatabase
:connect  ADMIN:$(DBSrvrInst)

In the Messages tab I get

  Connecting to ADMIN:$(DBSrvrInst)...

The variable isn't being replaced.
But if I do this and remove the constant

:SETVAR DBSrvrInst MyDatabase
:connect  $(DBSrvrInst)

In the Messages tab I get

  Connecting to MyDatabase...

The variable is replaced correctly.
How do I get this to work

:SETVAR DBSrvrInst MyDatabase
:connect  ADMIN:$(DBSrvrInst)

I would even be ok with using the port but that doesn't work either

:SETVAR DBSrvrInst MyDatabase
:connect  $(DBSrvrInst),1434

Any suggestions are greatly appreciated.

How about just placing the ADMIN: text in the variable? eg

:setvar DBSrvrInst ADMIN:.\sql2014

:connect $(DBSrvrInst)
GO


Access Denied error in CREATE DATABASE ... FOR ATTACH I'm attempting to attach a copy of msdb to my workstation SQL Server 2012 Developer Edition on Windows 8.1 in an attempt to mimic the problem detailed in this question where the OP is attempting to recreate missing jobs out of a foreign msdb.

I performed the following actions:

use master;

backup database msdb to disk='C:\sqlserver\test.bak';
go
restore database msdbtest from disk='c:\sqlserver\test.bak'
    with move 'MSDBData' to 'C:\SQLServer\Data\msdbtest_data.mdf'
    , move 'MSDBLog' TO 'C:\SQLServer\Logs\msdbtest_log.ldf';
go

I then stopped the SQL Server Service, made a copy of C:\SQLServer\Data\msdbtest_data.mdf and C:\SQLServer\Logs\msdbtest_log.mdf, and restarted SQL Server.

I then performed the following:

drop database msdbtest;
go
create database msdbtest on (
    name='msdbtest_data'
    ,filename='c:\sqlserver\data\msdbtest_data.mdf'
) log on (
    name='msdbtest_log'
    , filename='c:\sqlserver\log\msdbtest_log.ldf'
) for attach;

This results in:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "c:\sqlserver\data\msdbtest_data.mdf".
Operating system error 5: "5(Access is denied.)".

SQL Server is configured with the default out-of-the box settings for the service account:

Security effective access for the SQL Server service account on c:\sqlserver\data\msdbtest_data.mdf is:

I am at a total loss; I think the error being returned is misleading me.  Perhaps I misunderstand the purpose of CREATE DATABASE ... FOR ATTACH?

As far as I can tell, SQL Server should certainly have access to the file.  The folder C:\SQLServer\Data is used by all the other user databases currently attached to the instance.

SELECT @@VERSION returns:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

When you get this message it can be a few things. Some examples:

1.) Read Only Marked Files

2.) Insufficient permissions to the files for the SQL Server Service account

3.) Insufficient permissions to the folders the files live on.

You indicated in our chat that this was not read only. You also verified permissions looked like full access. That ruled out these three items.

The fact that, from our chat conversation, you were not experiencing this issue when you properly detach a database using sp_detach_db - which actually changes permissions around a bit on the files led me to suggest trying to run SSMS as administrator.

Why? Well this article gets into a lot more nitty gritty about some of the impersonation issues here - but my understanding is:

1.) When you just stop the SQL Server Service and move the file around. The owner of the file is the service account that created it.

2.) When you detach the database - you'll potentially notice a permissions change. The owner has changed basically and the file is not really associated with that SQL Server service account the same way.

3.) So it works when you try to attach because the permissions can get assigned, because the security of the file allows it..

But if you just stop the service the security hasn't changed, and you can't attach that if you have UAC enabled unless you are impersonating administrative access.

I've probably made the file ownership and access more confusing here but the article I shared probably does a better job :-)

But one slight moral is - do a detach next time ;-) Or just be prepared to run SSMS as administrator from time to time when interacting with the OS in various ways.


Use only one Junction Table per Database? I am currently working on a Database-Design for an insurer. Example-Entities are "Customer", "Address", "Vehicle", "Building" etc..
So there are many Relations and every Relation has some Context. e.g. Customer-Vehicle: Customer can be owner, driver, lessee of a vehicle...
Basically I see two options on how to handle that:

(1): A Junction Table for every relation, with the two foreign keys and a context-ID. This would lead to many junction tables, with partially very few data in it. Selects would be a little bit difficult, because of the amount of tables.

(2): One Junction Table for the whole Database. This table would contain two foreign keys, one key as to what relation it is (e.g. customer-vehicle) and a key for the context. This would lead to a huge junction table, but also simpler selects.

Which one is the better solution? Are there any main advantages/disadvantages or even other solutions I didn't think of?

Thank you for your help. If I didn't express myself clear enough or something is not understandable, please ask. (English is not my native language)

The first solution is the best.

The second solution will give you less tables but in case that you need to join 3 tables with 2 junctions you need to use the big junction table twice in your SQL.
In case that you want to create constraints between a table and it's junction (is practically mandatory) then this is not possible in the second solution. Your data integrity will not be checked by the database.
The ER diagram for the second solution will look complicated for somebody that needs to get data out of your database.


Insufficient Privileges on selecting Views I am using Oracle 12.

Given 2 schemas, SCHEMA_01 and SCHEMA_09. I have a View_01 owns by SCHEMA_01 but it is made up of SCHEMA_01 and SCHEMA_09 tables.

Then I have a User_02 that could select both tables in SCHEMA_01 and SCHEMA_09. But I am prompted with insufficient privileges when selecting the VIEW_01.

Before that I have tried granting all SCHEMA_09 tables with grant option to SCHEMA_01. This error is also prompted when I tried granting select for view USER_02's role

  grant option does not exist for SCHEMA_09.TABLE_01

Anything that I am missing or maybe somewhere I can check?

Does USER_02 have permission to see VIEW_01? The grants on the view have nothing to do with the grants on the base tables. Being granted SELECT on the tables is not sufficient to have SELECT access to the view.

On your last paragraph, it's true that SCHEMA_01 needs to have the SCHEMA_09 tables granted to create a view against them, including the WITH GRANT OPTION so that SCHEMA_01 can be allowed to provide access to the SCHEMA_09 tables to other users through the VIEW_01 view. However, it sounds like you already have this if the view was created without errors.


Audit tables in different databases I am studying ways to implement audit tables to my PostgreSQL database and would like to know if it is possible to make a trigger for the data to be written to a table in a database other than the source database.

You can do that with the help of the additional module dblink. Install once per database (the one where the trigger is executed):

CREATE EXTENSION dblink;

You need superuser privileges (in the database cluster where the trigger runs) for some of the involved functionality.

Since you want to write to the external database, you will probably want to use dblink_exec(). The manual has code examples.

Or try a search here for more dblink examples.

Note that you cannot ROLLBACK what you have written with dblink to an external database. A local exception after the external write does not undo what's written in the foreign database.