It turns out there was a firewall issue on both the servers.
After stopping the firewall(systemctl stop firewalld), the cluster is consistent.


SQL server Like pattern match with CONCAT() always fails I am facing problem in a simple query script for SQL server. Please help

The script is

CREATE PROCEDURE [dbo].[Search_By_Author_And_Name]
(
    @Author_name AS [varchar](50)
)
AS
BEGIN
    Select B.*, R.R_number From Book AS B , Rack As R
    where B.Author LIKE CONCAT('%', @Author_name, '%')
    AND B.B_Id=R.B_Id
END
GO

The problem is this returns no columns. Even if I pass a valid argument(@Author_name) to the procedure it returns nothing. There must be a problem in the where clause in the usage of LIKE.

sample data in the table are

the first table is Book table and the other table is Rack table

I am calling the procedure by

EXEC Search_By_Author_And_Name 'Physics'

Please help

Thanks in advance

You are only matching on Author, not B_Name.  Change code to:

CREATE PROCEDURE [dbo].[Search_By_Author_And_Name]
(
    @Author_name AS [varchar](50)
)
AS
BEGIN
    Select B.*, R.R_number From Book AS B , Rack As R
    where (B.Author LIKE CONCAT('%', @Author_name, '%') OR
    B.B_Name LIKE CONCAT('%', @Author_name, '%'))
    AND B.B_Id=R.B_Id
END
GO


MySQL row does not persist, but primary key with auto-increment is incremented I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, the row vanishes (despite no other queries being executed).

This table has an integer ID as a primary key, with auto-increment set, and the primary key is auto-incremented as expected for new rows. This leads me to believe there is not some kind of transactional rollback (and my table is MyISAM, so that shouldn't be possible anyways) or anything else that is somehow reverting the database to a previous state.

What logs should I be checking to see what is going on here? The contents of my '/var/log/mysql/error.log' are below, but I don't see anything unusual.

120815 21:01:01 [Note] Plugin 'FEDERATED' is disabled.
120815 21:01:02  InnoDB: Initializing buffer pool, size = 8.0M
120815 21:01:02  InnoDB: Completed initialization of buffer pool
120815 21:01:03  InnoDB: Started; log sequence number 0 44233
120815 21:01:03 [Note] Event Scheduler: Loaded 0 events
120815 21:01:03 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.63-0ubuntu0.11.10.1'  socket: '/var/run/mysqld/mysqld.sock'      port: 3306  (Ubuntu)
120815 21:01:04 [ERROR] /usr/sbin/mysqld: Table './appname/users' is marked as crashed and should be repaired
120815 21:01:04 [Warning] Checking table:   './appname/users'
120815 21:10:34 [Note] /usr/sbin/mysqld: Normal shutdown

120815 21:10:34 [Note] Event Scheduler: Purging the queue. 0 events
120815 21:10:34  InnoDB: Starting shutdown...
120815 21:10:39  InnoDB: Shutdown completed; log sequence number 0 44233
120815 21:10:39 [Note] /usr/sbin/mysqld: Shutdown complete

I noted the 'crashed' mark on the appname/users table, but mysqlcheck suggests the table is OK.

Any thoughts?

The disappearing rows are a common behavior of mysqlcheck (or myisam check), because it tries to repair the corrupted blocks. Other engines (eg. Oracle) have more advanced repairing algorithms, that can actually preserve your data.

Corruption usually appears when there's a problem with the disk (space, partition headers etc), so start by checking them. Then check the integrity of your data files, and make sure they can expand correctly.

PS: When runing mysqlcheck, you're using the --auto-repair argument?


Can an SQL Server Failover Cluster support a named instance? If I currently have someName\instanceName on a standalone SQL Server and I want to migrate to a failover cluster, http://itknowledgeexchange.techtarget.com/sql-server/moving-a-standalone-instance-to-a-cluster-while-keeping-the-name-and-ip/ hints that I can give the cluster the network name someName but that page doesn't deal with instance names.  On the other hand, Figure 8-10 on https://www.microsoftpressstore.com/articles/article.aspx?p=2224049&seqNum=2 shows an instance name (with the default value) suggesting that you can create named instances.  What isn't clear to me from that page is whether sql-cluster refers to SQL-C\MSSQLSERVER (generically clusterName refers to hostName\instanceName) or if those three fields are independent and sql-cluster\MSSQLSERVER is a valid way to address that named instance on the cluster?  (And, of course, if the "SQL Server instance name" was not the default but, say, myInstance, is sql-cluster\myInstance valid.)  I've found conflicting hints and no conclusive or authoritative answers on the web.

You can use either a default instance or a named instance on a Failover Cluster. I have supported hundreds of clusters, most of them used named instances.

When connecting to it, use networkname\instancename. You can use other ways, but it makes most sense to use networkname since that moves to another node on failover.


Sql Server sys.Traces log Working with traces on production server I found some mysterious entries to me in sys.Traces, When I run below query it shows me status of all traces as ‘Paused’ along with entries in sys.dm_exec_sessions, which scenarios generate these types of records.

SELECT t.id
 ,CASE t.status
 WHEN 0 THEN 'Paused'
 ELSE 'Running'
 END AS status
 ,t.reader_spid
 ,t.start_time
 ,s.HOST_NAME
 ,s.program_name
 ,s.login_name
 ,t.*
FROM sys.traces AS t
 LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = reader_spid
WHERE t.is_rowset = 1
and is_default<>1

If the following command is run by someone with the authority to do so, the trace will be stopped.

exec sp_trace_setstatus @traceid=<trace_id>, @status=0 -- Stop the trace

However it still exists in the stopped state.  This is the state you are reporting as 'Paused'.  If the stopped trace should be ended, then a second command is needed:

exec sp_trace_setstatus @traceid=<trace_id>, @status=2 -- Close the trace

This removes the stopped trace from the server.  Once the trace is stopped and closed you can read the full trace.

Of course, if the trace is to be periodically restarted, then you can leave it in the stopped state until the next activation.


Oracle RAC with single node Data Guard physical standby Best practices for Oracle 11gR2 Data Guard implies that the primary and standby databases should have the same layout, eg. RAC to RAC.

What special considerations would one need to take into account to replicate a RAC database to a single node physical standby?

Any reason why this would be a very bad idea?

There aren't any special considerations that need to be taken into account when setting up a RAC->No RAC Primary->Standby configuration.

In fact, there's a White Paper written by Oracle which explains the setup.

The Oracle documentation is here.


Alternatives to Linked Servers (Allow Inline SQL with Parameters) I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use OPENQUERY. And the only proper way to prevent SQL injection is with parameters.

However, the way OPENQUERY is structured it does not look like parameters are possible. Am I right to assume the only proper way to query with this type of configuration is with stored procedures? Are there any alternatives? Are there any similar database configuration that could be used?

Any thoughts or advice on the subject would be greatly appreciated.

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters:

DECLARE @someParam INT = 5;

EXEC LinkedServerName.master.sys.sp_executesql
  @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;',
  @params = N'@i INT', @i = @someParam;


Enabling IPAII protocol disable remote conections. Why? I have some servers with IPAII protocol enabled with a certain port:

I'm using 1433 because a long story. but its working. We can use the default port 1433 AND IPAII ( for PHP developers ) with 1433 too.
But in production, when I changed, the server lost connection.
There's no way to explain this in a easy way.
Why I had no problems on one servers, but on another, it lost connection?
If the question is to abroad, I can get it better.

After you modify any of these settings it's common to lose connections until you restart the instance (and sometimes Browser too if you're using it). We have a policy not to do it without an outage window.


After I replaced master mdf/ldf files, can no longer log in Today I couldn't start SQL Server process anymore. Reason:

The log scan number (213:18:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Ok, then I searched the internet looking for a solution for this strange problem and I found a blog post suggesting that I should replace master.mdf and mastlog.ldf files with the ones located in C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Template Data and so I did.
Now I can start the process but I can't log in with neither my 2 previous login accounts. I thought that after replacing master I would at least be able to login with my Windows account, but I can't.
These are the login failed event logs:

Login failed for user 'andre-PC\andre'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
Login failed for user 'andrerpena'. Reason: Could not find a login matching the name provided. [CLIENT: ]

How can I work around this?

Login details are stored in master, so it makes sense that they would be gone if you've replaced the master database files.

The files in the template data folder are referred to in User Instances for Non-Administrators as "clean system database". I don't have an Express edition to hand to check but I would assume that if your now using a "clean" master database, the only login that would be present is "sa" with a blank password.


Convert sql_variant to varchar to select left 2 I am trying to populate a column (versionstatus) with if the service pack needs updating. I have created a lookup table which lists the SQL Version, and the latest product version, e.g.
SQLVersion           LatestSP
SQL Server 2016      13.0.5026.0

I then have a second table which contains product versions, and want to compare this value with the LatestSP value, if the value in the first table is less than the second, then SP is not up to date.
I need to specify that where the product version in both tables begins with X (i.e. begins with 12 for SQL 2014, 13 for SQL 2016) then compare the numbers (so that I only compare the correct versions to each other) and if the value in Servers is less than the value in SPLookup, then populate the versionstatus column with 'NEEDS UPDATING'.
 However I am having issues converting sql_variant to varchar to be able to select the first 2 digits.

Here is what I have so far, and would extrapolate this to the other versions.
productversion in Servers table is sql_variant, LatestSP in SPLookup is varchar.

 INSERT INTO Servers
 (versionstatus)
 SELECT CASE WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion < Y.LatestSP THEN 'UPDATE NEEDED'
            WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion >= Y.LatestSP THEN 'UP TO DATE'
        END AS versionstatus
        FROM Servers X INNER JOIN SPLookup Y ON X.productversion = Y.LatestSP

Try explicitly converting the sql_variant column to varchar before applying the LEFT function.  The following simple example worked for me.

Declare @V sql_variant
set @v = '13.0.5026.0'
select left(convert(varchar(max),@v),2)

| (No column name) |
|------------------|
| 13               |


Is using strings as keys for reference to other tables bad in terms of memory usage? So i have to deal with the following scenario:
I have a table , users , which holds all of the users of my application .
Each user has an email which is unique and thus could be the primary key of said table .
Let's now say i have a second table that associates some data with a user . What pops into my mind is to have that second table hold an email column which i can then use to associate a row with a user .Is this approach bad in terms of memory? Will MySQL store said email repeatedly for each extra table that holds an email column to reference users table? Should i instead just use an auto-increment id for each user and use that for the whole reference thing since an integer is way lighter than a string big enough to hold an email?

Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes it needs or the maximal size depending on the rdms.
Integer with up to 8 bytes for a big integer will only use these bytes, and are so faster when referencing.
In terms of speed you use  INTEGER, and consider other like varchar(36) for uuids when the need arises, for example different servers have to save data in the same table.
with emails a unique and so be indexed for referencing, you should take the extra mile and use Integer if you expect you will have big tables.


How to migrate windows login in from one sql server to another sql server on another domain We have a SQL server that is located in domain 1(DMN1). We are migrating databases in domain 1 to our new SQL Server in domain 2(DMN2).Corporate states that there will be no trust established between each domains. So whatever logins the database has in DMN1 cannot be used in DMN2 anymore. it would take a lot of time to sort out a lot of windows logins in DMN1, get their equivalent login in DMN2 and map them to the right databases. What would be an easier way of doing it? Is it possible to just copy the data in each databases without the security part, and just rebuild the security on each database manually? Can anyone share a non time consuming way of accomplishing this?

How to migrate windows login in from one sql server to another sql server on another domain

  So whatever logins the database has in DMN1 cannot be used in DMN2
  anymore. it would take a lot of time to sort out a lot of windows
  logins in DMN1, get their equivalent login in DMN2 and map them to the
  right databases. What would be an easier way of doing it

Take a look at the two below scripts I've used for similar needs in the past. You will need to run these on each DB you need to mirror permissions for the two different domain accounts.

You will need to search the results of script #1 for the principal you need to mirror the access which is changing domains, etc. Once you get the logic out you need, run it with the new value of the domain credential from the new domain and then run that logic on each DB.

Use the script #2 query before and/or after if you wish per each DB to check the records for the security principals.

Script 1

/* Script DB Level Permissions v2.1 Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/

DECLARE
    @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR FOR

/*********************************************/ /*********   DB CONTEXT STATEMENT    *********/ /*********************************************/ SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --] UNION SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/ /*********     DB USER CREATION      *********/ /*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --] UNION SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --] FROM    sys.database_principals AS rm WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/ /*********    DB ROLE PERMISSIONS    *********/ /*********************************************/ SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --] UNION SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --] FROM    sys.database_role_members AS rm WHERE   USER_NAME(rm.member_principal_id) IN (
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/ /*********  OBJECT LEVEL PERMISSIONS *********/ /*********************************************/ SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --] UNION SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
--select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE
                WHEN perm.state <> 'W' THEN SPACE(0)
                ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --] FROM
    sys.database_permissions AS perm
        INNER JOIN
    sys.objects AS obj
            ON perm.major_id = obj.[object_id]
        INNER JOIN
    sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
    sys.columns AS cl
            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/ /*********    DB LEVEL PERMISSIONS   *********/ /*********************************************/ SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --] UNION SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --] FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --] UNION SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --] from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp FETCH NEXT FROM tmp INTO @sql, @sort WHILE @@FETCH_STATUS = 0 BEGIN
        PRINT @sql
        FETCH NEXT FROM tmp INTO @sql, @sort     END

CLOSE tmp DEALLOCATE tmp

source

Script2

CREATE VIEW vwObjectPermissions
AS
SELECT schema_name(o.schema_id) AS [Schema_Name]
    ,o.NAME AS [object_name]
    ,u.NAME AS [principal_name]
    ,u.type_desc AS [principal_type]
    ,r.minor_id
    ,r.permission_name
    ,r.state_desc
    ,o.schema_id
    ,o.principal_id AS [alt_owner]
    ,o.type_desc
FROM sys.database_permissions r
LEFT JOIN sys.database_Principals u ON r.grantee_principal_id = u.principal_id
LEFT JOIN sys.all_objects o ON o.object_id = r.major_id
WHERE class_desc NOT IN ('database')
GO

--1. Check if Public or guest is granted any permission on an object (database role and server role)
SELECT *
FROM vwObjectPermissions
WHERE principal_name IN ('Public','Guest')

--2. Check if any user is granted permissions on an object rather than roles.
SELECT *
FROM vwObjectPermissions
WHERE principal_type NOT LIKE '%ROLE%'

--3. Check if a user has "with grant" previliges on an object
SELECT *
FROM vwObjectPermissions
WHERE state_desc = 'WITH GRANT' --check the spelling on this one

--4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype='X')
SELECT *
FROM vwObjectPermissions
WHERE type_desc LIKE '%X%Proc%'
GO

DROP VIEW vwObjectPermissions;

source


Why is it not a good idea to use MariaDB's extra_port for replication? MariaDB has an extra_port for administrative access that helps in cases the thread pool or main port is locked up.
MariaDB's documentation says it's use case is primarily administrative access and it can be used to make sure monitoring systems always have access.
To me "always have access" sounds like something you want for replication but it's not mentioned and I could not find anyone writing about using the extra port for replication.
The paragraph When to Use the Thread Pool in the documentation says:

Thread pools are most efficient in situations where queries are
relatively short and the load is CPU-bound

Replication runs forever (fingers crossed) and is IO-bound. This sounds like another indication to use the extra port (although replication is not a query).
My Questions:
Can you explain me why it's not a good idea to use the extra port for replication?
If it actually is a good idea, why is it not widespread (as indicated by not finding anything substantial when googling for "mariadb extra_port replication")?
Do replication connections get some special treatment and/or priority?

Replication do not need some special ports and can use the common 3306. An extra  port is a port that can be used by root as back door if there is no access through the common port.
Say if you have max_connections = 100 and your app is already used all that 100 connections, only one extra connection can be used by user with ALL PRIVILEGES granted. If someone is already connected as DBA you have no chance to connect to the server. But you still can connect via extra_port cause it has no max_connections limit. And that is the reason why you should not use it for any other tasks except an emergency access. With no max_connections set to the reasonable value you can easily exhaust your host's RAM that can hang your DB or even a host in a pretty bad way.


How to store multiple vendor data in Google Cloud SQL? I am designing a mobile app in which I need to host my vendor data and give access to that data after analysis to only the specified users. Since I will have multiple vendors with different set of data and users, should I have multiple database 1 of each vendor or 1 database with different schema ? I plan to run the application on Google Cloud Platform and use Google Cloud SQL.

What are advantaged and disadvantages of using single database to host multiple vendor data?

The advantage of using a single database is in having a single schema and a single codebase.  If you make a change, then all vendors get the change.  (That can be a disadvantage, too, if they don't like the change).

Apart from that, the disadvantage is if the vendors have significantly different requirements and need different schemas and/or they interpret the data differently.  But if some of them want to have a schema or code change, then you have to manage all the different versions.

It's easy enough to serve up data for a given vendor to a given user by using the right queries.  Unless you want to be spending most of your time managing versions, you probably want to keep them all synchronized with the same schema and code.


SQL Server Snapshot Replication and SRDF compatability Is there anything that breaks down if you use snapshot replication and then use SRDF for disaster recovery (DR) on the snapshot publisher?

Specifically I am asking is: On the DR system, would the DR snapshot agent understand all the tags added by the original snapshot agent? Is there any information on how to reconfigure the agent?

I'd check with EMC's tech support, however I seriously doubt anything at the SAN level will have an effect on SQL Server snapshots themselves.

Snapshots at the software layer are simply files that are handled just like any other file at the SAN hardware level.

Our Dell SAN has some software that integrates with SQL Server to allow you to create snapshots from SQL Server Management Studio, but again, that is a snapshot of the entire file system - not a specific database file.


What is the valid string representation of tstzrange[]? I am trying to find string representation of tstzrange[] type (array of timestamp ranges):

select '{[2014-01-01,2015-01-01]}'::tstzrange[];
select '{''[2014-01-01,2015-01-01]''}'::tstzrange[];
select '{[''2014-01-01'',''2015-01-01'']}'::tstzrange[];

But each of queries above throws malformed range literal error.

I know that I can use array[tstzrange('2014-01-01','2015-01-01','[]')] but what I need is a string representation.

The thing is that here you have to combine two literals: '{}' for the array and '[...,...]' for the range type.

In all these cases, the easiest is just getting back the output from the system itself:

SELECT array[tstzrange('2014-01-01','2015-01-01','[]')];
                            array
─────────────────────────────────────────────────────────────
 {"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}

Now you simply wrap this into quotes, and there you are.

There is, however, a simpler way, which usually works: just double-quote the inner literal.  See:

select '{"[2014-01-01,2015-01-01]"}'::tstzrange[];
                          tstzrange
─────────────────────────────────────────────────────────────
 {"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}

Note that the output is the same as above.


How to relate a table to other two? I'm trying to design a database that contains, among others, entities "Photo", "Venues" and "Products".
Given both Venues and Products could contain a collection of photos... how could I tell whether the photo is from a Venue or a product?

Photos
    Id PK
    Venue_Id FK
    Product_Id FK

Venues
    ID PK
    Photos_Id FK

Products
    ID PK
    Photos_Id Fk

There is likely something wrong with your model. Mutual dependencies are very rare in relational database designs. This is what I assume you want to do:

create table photos
( photo_id ... not null primary key
, <additional attributes> );

create table venues
( venue_id ... not null primary key
, photo_id ... not null
      references photos (photo_id)
, <additional attributes> );

create table products
( product_id ... not null primary key
, photo_id ... not null
      references photos (photo_id)
, <additional attributes> );

To determine whether a photo belongs to a venue, product or both you could do something like:

select x.photo_id
     , case when y.photo_id is not null then 'Venue' end
     , case when z.photo_id is not null then 'Product' end
from photos x
left join venues y
    on x.photo_id = y.photo_id
left join products z
    on x.photo_id = z.photo_id


How to keep history of MySQL/Aurora stored procedure revisions? We need to maintain the history of the procedures. I think it will be easier to save the history in a table so every time someone either deletes or modifies the content of the procedure it will go and save the changes to a table to maintain an history. May be upto a point a trigger can help, but wanted to check if anyone has any custom script written on this already which can be reviewed?

Here's a good StackOverflow post with various answers that might help you: Put MySQL database under version control?
Specifically, Liquibase seems to withstood the test of time, and it seems to be supported by AWS (seeing as you mentioned Aurora): Deploy, track, and roll back RDS database code changes using open source tools Liquibase and Jenkins
(Note this leverages the Amazon service CodeCommit.)


How to count items in specified ranges in one query? I have this table:

Id | Username | FollowersCount
 1 | user1    | 1500
 2 | user2    | 700000
 3 | user3    | 94000
 4 | user4    | 117000

I want to find the number of users who have less than 50k followers, users who have between 50k and 100k, users who have between 100k and 150k, and so on.

Of course I can write many queries to get the result. But is there a way to somehow mix aggregate functions with between clause?

This will return a row for every range from 0-49999 up to whatever range the highest followers count falls in.

DECLARE @RangeSize int = 50000;
DECLARE @NumberOfRanges int = (SELECT MAX(FollowersCount) FROM #table) / @RangeSize;

;WITH n(n) AS
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n <= @NumberOfRanges
), ranges(s,e) AS
(
  SELECT @RangeSize * (n-1), @RangeSize * n FROM n
)
SELECT r.s, r.e-1, COUNT(t.Id)
FROM ranges AS r
LEFT OUTER JOIN #table AS t
ON t.FollowersCount >= r.s
AND t.FollowersCount < r.e
GROUP BY r.s, r.e
ORDER BY r.s;

If you only want the ranges with a non-zero user count, just change the LEFT OUTER to INNER. And I prefer an open-ended range over BETWEEN for two reasons - one, it's ambiguous, since if someone has exactly 100K, do they fall in the 50k - 100k bucket, or the 100k - 150k bucket, and two, while you can force that either way, it doesn't translate well to other data types, where it's even more troublesome.


Entity-Relationship Diagram for a scenario involving Books, Authors and Countries Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^

I am getting warmed up with MySQL and I try to understand the concepts of creating well structured databases. Currently I am reading about Entity-Relationship Diagrams (ERD) and I asked myself:

If I have three tables which are linked by a forth one, for example: A book written by one ore more authors, and each authors and was published in one ore more countries.

authors          books            countries
id | name        id | book        id | publishedInCountry
 1 | ...          1 | ...          1 | ...
 1 | ...          2 | ...          2 | ...
 1 | ...          2 | ...          3 | ...
 4 | ...          1 | ...          3 | ...

linkTable
idOfAuthor | idOfBook   | idOfCountry
 1         | 1          | 1
...

How would an ERD look like for these ones?

Additional information

I thought of:

[authors] -- < is author of > -- [books] -- < was published in > [countries]

But how do I fit in the linkTable?

as far as I can see,

you need two separate link tables as these are unrelated, one for linking many books to many authors, and one for linking many countries to many books.

linkTable Author-Book
idOfAuthor | idOfBook
 1         | 1

linkTable2 Book-Country
 idOfBook   | idOfCountry
 1          | 1


BCNF when no keys are available I have a problem understanding a particular case of BCNF.
Let's suppose that we have a relation R(B, C, D) with df:{C->D}.
Is my relation in BCNF and if yes why???
Is C considered as superkey?

The only key is {BC}. You have a partial key dependency: C->D. Since you have a partial key dependency, R is not in 2NF. So R is also not in BCNF.

An informal way of expressing BCNF is that every arrow in every FD is an arrow that comes out of a candidate key. In R, the arrow in "C->D" doesn't come out of a candidate key.


Performance issue with composite primary key or not on Transactional Replication with AlwaysOn Subscribers? I configure identity column with composite primary key on Transactional Replication scenario to maintain unique row value.

    CREATE TABLE [dbo].[AD_CLIENT_DETAIL_MST](
    [CLIENT_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SERVER_ID] [char](1) NOT NULL CONSTRAINT   [DF_AD_CLIENT_DETAIL_MST_SERVER_ID]  DEFAULT ('A'),
    [C_NAME] [varchar](300) NOT NULL,
    [CLIENT_TITLE] [varchar](10) NULL,
    [C_FIRST_NAME] [varchar](100) NOT NULL,
    [C_MIDDLE_NAME] [varchar](100) NULL,
    [C_LAST_NAME] [varchar](100) NULL,
    [C_DOB] [date] NULL,
    [RESELLER_ID] [bigint] NOT NULL,
    [RESELLER_NAME] [varchar](300) NULL,
    [CITY] [varchar](50) NULL,
    [STATE] [varchar](50) NULL,
    [COUNTRY] [varchar](50) NULL,
    [COMPANY_NAME] [varchar](500) NULL,
    [COMPANY_LOGO_PATH] [nvarchar](max) NULL,
    [COM_ADD1] [varchar](300) NULL,
    [COM_ADD2] [varchar](300) NULL,
    [BANK_NAME] [varchar](50) NULL,
    [BANK_ACCOUNT_NO] [varchar](50) NULL,
    [PAN_NO] [varchar](20) NULL,
    [TIN_NO] [varchar](20) NULL,
    [BANNER] [varchar](50) NULL,
    [YOCC_NO] [varchar](15) NOT NULL,
    [PRIMARY_NO] [varchar](15) NOT NULL,
    [SECONDARY_NO] [varchar](15) NULL,
    [ALLOWED_CHANNEL] [int] NOT NULL,
    [ACTIVE_OPERATOR] [int] NOT NULL,
    [CALL_ROUTING_TYPE] [bigint] NOT NULL,
    [CALL_ROUTING_PATTERN] [bigint] NOT NULL CONSTRAINT [DF_AD_CLIENT_DETAIL_MST_CALL_ROUTING_PATTERN]  DEFAULT ((0)),
    [LIST_STATUS] [bigint] NOT NULL,
    [SMS_PROVIDER_ID] [bigint] NOT NULL,
    [EMAIL_PROVIDER_ID] [bigint] NULL,
    [IS_BLOCK] [bit] NOT NULL,
    [REG_DATE] [datetime] NOT NULL,
    [TOTAL_USER] [int] NULL,
    [TOTAL_ADMIN] [int] NULL,
    [EXP_DATE] [datetime] NULL,
    [CHANNEL_COUNTER] [int] NULL,
 CONSTRAINT [PK_AD_CLIENT_DETAIL_MST] PRIMARY KEY CLUSTERED
(
    [CLIENT_ID] ASC,
    [SERVER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

So, when i define two primary key then i will give some slow query performance or not ?

Performance not only depends on the query, but also the kind of data you store in the table.

Let's zoom out and talk about how you choose a clustering key for a table. Generally, you want the clustering keys to be:

Static - non-changing
Unique
Narrow - as few fields as possible, with as small datatypes as practical
Ever-increasing - which gives you a hotspot at the end of the table, which would sound bad but is usually good in small (sub-terabyte) database designs

Here, you've already got a single field that satisfies all of those requests: CLIENT_ID, which you defined as an identity field. You could just start by using that field alone - adding SERVER_ID doesn't really buy you anything.

However, I'm a little suspicious of the table design because SERVER_ID is only a CHAR(1). That gives you a limited number of servers to work with per CLIENT_ID. I don't know what you're trying to model, but generally an ID wouldn't be a single character.


SQL Server Express 2014: Network Access I have SQL Server Express running on one (virtual) machine running Windows 8.1, but I cannot connect to it from another.

I have followed the instructions on https://msdn.microsoft.com/en-us/library/ms191294.aspx, which is pretty similar to countless other instructions, restarted the server, restarted the machine, and, for good measure, made another coffee.

I know I can connect to the other machine using another protocol. I ran HFS on the machine (a nifty little HTTP server), and could easily connect from another machine.https://msdn.microsoft.com/en-us/library/cc646023.aspx to allow access through the firewall, but even with the firewall totally turned off, it doesn’t work.

My settings in Configuration Manager are:

Protocols for SQLEXPRESS | TCP/IP
    Protocol: Enabled Yes; Keep Alive: 30000; Listen All: Yes
    IP Addresses | IP2: Active Yes; Enabled Yes; IP Address …;
                        Dynamic Ports 0; TCP Port [empty]

How can I access this server from the network?

Thanks

SQL Express installs by default with a named instance of SQLEXPRESS.  See https://technet.microsoft.com/en-us/library/ms143744(v=sql.90).aspx as it applies to SQL Express 2005.  The settings you give in your question imply your instance uses dynamic ports.  Running SQL Browser and connecting using the named instance as previously suggested should in theory help.  If not, read on...

To restate: you're using a Mac to host a virtual machine where your SQL Express instance lives.  You're connecting to this virtual machine from the Mac host.  HTTP connections are okay, but not SQL Server connections.

There is a possibility that the Mac's network security allows connections over port 80 but not other ports.  That may be worth investigating, especially if you're trying to connect to the VM from an entirely different computer; the Mac's firewall may be preventing the connection in that case. Depending on what you're using to run the VM (virtualbox? vmware?), it's possible that network settings there are interfering with what you want to do as well.

Determining your SQL Server's port

If you're fortunate enough to have SQL Server Configuration Manager on your SQL Server box, you can run that, choose your instance, expand SQL Server Network Configuration, then click Protocols for instancename.  Double-click TCP/IP (it IS enabled, right?) Choose the IP Addresses tab and scroll all the way to the bottom where you'll see IPAll. On your way there, notice if TCP Dynamic ports is zero for the various IPn entries and TCP Port is blank.  Once you get to IPAll, notice the value for TCP Dynamic ports - this is what port SQL Express listens on if you're using dynamic ports.

For each IPn item where TCP Dynamic ports is zero, your instance will listen on the value specified in IPAll.TCP Dynamic Ports.  Otherwise, it listens on the TCP Port value for that particular item (or IPAll if blank).

Assuming you have all the defaults and SQL Browser is running, connecting to 192.168.2.1\sqlexpress should get you to where you need to go.  If not, try 192.168.2.1,dynamicportnumber instead.  Yes, that's a comma separating the IP address and port number.  So if your SQL instance uses port 49390 like the screen capture above, it would be 192.168.2.1,49390.

No SQL Server Configuration Manager?  Open a command prompt as administrator and use netstat -b -a -p TCP to get a list of open ports and attached processes:

The -b switch tells netstat to give you the associated process, -a says list listening ports, and -p TCP limits the list to just TCP otherwise you'd get a lot of extra items to sift through.

Note this particular box has an instance listening on two different ports: 1433 and 49390.

If none of this works (which would be sad because this is a lot of typing) I recommend you run a good port scan against the IP address where your SQL instance lives.  NMAP works on Mac, Linux and Windows:  https://nmap.org/

Also, install SQL Server Management Studio Express (https://technet.microsoft.com/en-us/library/ms365247(v=sql.105).aspx) on the SQL VM and try connecting to the database engine using that.  If you're on the box and it can't connect to itself, the problem is on the virtual machine itself - that will help narrow things down considerably.


can mysql user be blocked on the local server but have remote acces? So at work we have a windows server running mysql, the IT guy got fired but he supposedly left root password.

Try to login with root user:
  - Locally: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

- Remotely: "ERROR 1045 (28000): Access denied for user 'root'@'hp-01' (using password: YES)

Beacause i've been using a .net app which connects to that server I thought I'd give it a try with that user. it went like this:

- Locally: "ERROR 1045 (28000): Access denied for user 'adminpect'@'localhost' (using password: YES)

- Remotely: I stablished connection, but because it's not an admin user I only have limited acces to one database.

All this i tried from the command line and mysql workbench and in both I get the same results.

For the record I have very basic mysql knowledge and I've been searching all over the internet for this but have'nt had much luck.

Thanks in advance.

Yeah its possible that an user only have access from serveral locations. Did you try just google 'reset root password windows server'? Or just contact that guy who installed it. Is that a dumb idea?


SQL Server tuning: Disk queue to OS drive: How to optimize? I expect to see the disk queue move on the data, tempdb, log and backup volumes.

I do not expect to see the OS drive (C:) which has zero SQL files present, show disk queue impact.

Assume it is to the OS paging file, or ?

Suggestions on how to chase?

This is SQL 2014 on Win2012-R2/

Use Microsoft Performance Analyzer or Media Experiance Analyzer with Windows Performance Recorder. Any of these is capable of answering your question in great detail, quickly and foul-proof, using system ETW capabilities.

This article is a good walk through: Analyzing Storage Performance using the Windows Performance Analysis ToolKit.

At the simplest, it will be something like this:

c:\>Xperf -on FileIO+Latency+DISK_IO+DISK_IO_INIT+SPLIT_IO
 # repro your scenario
c:\>Xperf -d repro.etl
c:\>Xperf repro.etl

Use the Hard Faults view to track paging spikes, use Disk Utilization to correlate paging and disk IO, use Disk Utilization per Process to identify disk users.

You can take it one step further with Windows Performance Analyzer, WPA can open the same .etl file Xperf Viewer can, but it has much nicer graphs and intelligence built-in.


Determining if a relation is in third normal form 3NF I have a ralation R(A,B,C) with the functional dependencies as follows

A->B
A->C
B->C

with A being the candidate key, is this relation in 3NF? well because by analyzing the first two dependencies it seems that both non key attributes  B and C are not transitively dependent on A but does the third functional dependency B->C not make C transitively dependent on A?

When looking to see if a relation is in 3NF, two are the questions that you must answer:

Has every functional dependency a left hand side which is a superkey?
If this is not true, are the attributes on the right hand side prime attributes? (i.e. belonging to any key?).

So in your example you can see that both A -> B and A -> C are dependencies in which the left hand side is a key, so the answer to the first question is yes.

In the third dependency, instead, B is not a superkey, (since B+ = BC), so, is C a prime attribute?

To answer to this question, you should know all the keys of the relation, and see if C belongs to one of them. And in this case this is very simple, since A is the unique key of this relation, so you can conclude that the relation is not in 3NF. But note that in general, in more complex cases, it is not so simple to answer to such question, since you can have a very large number of keys, actually a number which is exponential with the number of attributes.


Error updating multiple rows on a table with triggers I have a Table Products with a Trigger to create a record on each update made to Products (something like a Log).

Everything works fine if I update one record at a time but if I try to update multiple records (Update Products SET Name = "BLABLABLA" WHERE Price > 10) I get the following error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. The statement has been terminated."

My Trigger is this one:

CREATE TRIGGER [dbo].[NS_Art_Export_ProdUpdate] ON [dbo].[Produtos] FOR UPDATE
    AS BEGIN

    DECLARE @Key1 NVARCHAR(20), @Key2 NVARCHAR(20)
    DECLARE @Changed BIT

    SET @Key1 = (SELECT Counter FROM INSERTED)
    SET @Key2 = (SELECT Prod_Gen FROM INSERTED)
    SET @Changed = 0

    IF UPDATE(Codigo) SET @Changed = 1
    IF UPDATE(NOME) SET @Changed = 1
    IF UPDATE(Familia) SET @Changed = 1
    IF UPDATE(CodIVA) SET @Changed = 1
    IF UPDATE(qtEmbal) SET @Changed = 1
    IF UPDATE(Volume) SET @Changed = 1
    IF UPDATE(PesoBrt) SET @Changed = 1
    IF UPDATE(PesoLiq) SET @Changed = 1
    IF UPDATE(Status) SET @Changed = 1

    IF @Changed = 1
        INSERT INTO Log_NSArtSoft
            (Tabela,      Tipo,  Key1,   Key2,   Data_Update,
             Data_Export, Flag_Update, Data_Export2)
        VALUES
            ('Produtos',  'U',   @Key1,  @Key2,  GETDATE(),
             0, 0, 0)

END
GO

Can someone help me with this?
Thanks

The issue is that in SQL Server, Triggers fire once per operation, not once per row. So when you do multi-row operations, the subquery in SET @Key1 = (SELECT Counter FROM INSERTED) is returning a Counter value per each row updated. You need to restructure the INSERT as being a set-based operation as follows:

CREATE TRIGGER [dbo].[NS_Art_Export_ProdUpdate]
ON [dbo].[Produtos]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE(Codigo) OR UPDATE(NOME) OR UPDATE(Familia) OR
      UPDATE(CodIVA) OR UPDATE(qtEmbal) OR UPDATE(Volume) OR
     UPDATE(PesoBrt) OR UPDATE(PesoLiq) OR UPDATE([Status]))
  BEGIN
    INSERT INTO dbo.Log_NSArtSoft
            (Tabela, Tipo, Key1, Key2, Data_Update, Data_Export, Flag_Update, Data_Export2)
      SELECT 'Produtos', 'U', ins.[Counter], ins.[Prod_Gen], GETDATE(), 0, 0, 0
      FROM   INSERTED ins;
  END
END;
GO

Please keep in mind that the UPDATE function only indicates whether or not the column is in the SET clause of the UPDATE statement (in terms of an AFTER UPDATE Trigger; the UPDATE function returns true for all columns in the context of an INSERT operation). It does not indicate whether or not the value of that column has changed. Meaning, for the query of UPDATE table SET ColA = ColA, calling UPDATE([ColA]) will return true even though the value is guaranteed to be the same.

Assuming that you do not want to log rows in which no values for the specified columns have changed, you need to JOIN the inserted and deleted tables and test each column between those two pseudo-tables. When testing the columns, you need to wrap NULLable columns in ISNULL(), and when the column is a string type (not XML) then you need to use a binary Collation to make sure that all changes get logged (even if they equate when linguistic rules are applied). Hence, the SELECT statement would change as follows (I have no idea what the datatype of any of the columns are, so the particular usage below for each one is purely for example):

  SELECT 'Produtos', 'U', ins.[Counter], ins.[Prod_Gen], GETDATE(), 0, 0, 0
  FROM   INSERTED ins
  INNER JOIN DELETED del
          ON del.[Key1] = ins.[Key1]
         AND del.[Key2] = ins.[Key2]
  WHERE  ins.Codigo <> del.Codigo -- NOT NULL number column
  OR     ISNULL(ins.[Status], -9999) <> ISNULL(del.[Status], -9999) -- NULLable number column
  OR     ISNULL(ins.PesoBrt, '1/1/1900') <> ISNULL(del.PesoBrt, '1/1/1900') -- NULLable date column
  OR     ins.NOME <> del.NOME COLLATE Latin1_General_100_BIN2 -- NOT NULL string column
  OR     ISNULL(ins.Familia, N'45#$%$%^$59jh35$%^$%~!@~!@#|{}\[]') <>
         ISNULL(del.Familia, N'45#$%$%^$59jh35$%^$%~!@~!@#|{}\[]')
         COLLATE Latin1_General_100_BIN2 -- NULLable string column

P.S. It is also a good idea to include SET NOCOUNT ON; at the beginning of Triggers and Stored Procedures (unless a client application specifically needs that output, which is usually not the case).


Creating oracle database physical files from migrated DB I migrated a database from SQL Server to Oracle 11g and there doesn't seem to be any physical files created after migration.

The client requires that they receive a copy of the database so they can clone it wherever they need. How do I create the files from the existing database? I'm from SQL server so it's pretty easy to get the files with a few clicks. Are there any similar feature for oracle 11g? Any alternatives if there aren't?

You should use export/import. On your side do:

expdp system/systempawwdord SCHEMAS=YOURSCHEMA

This will produce a file. Take it manually to you customer using CD, DVD, Pendrive. On customer site do:

impdp system/systempassword SCHEMAS=YOURSCHEMA

If the customer is using a different Oracle version, this is not a problem. You probably need to manually re-create tablespaces before importing the DB and add to the impdp command:

REMAP_TABLESPACES=OLDTBS:NEWTBS

For testing you can also do it on your actual instance, export, drop schema and import.


Unable to start mysql on ubuntu I have an issue with re-starting my MySQL 8 service on Ubuntu 19 after a force shutdown in normal mode.

Originally I was able to re-start the service in the save mode (had to do

innodb_force_recovery = 3  #in my cnf

However, switching this setting back to 0, in order to start up the db in normal mode, gives me the following error -

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

I tried doing

mysqlcheck -uroot -p on_every_database

on every database, and all tables came back as OK .

I'm in the process of doing auto-repair on all db's, but thats 300gb total.  Is there anything else I can try to fix the DB , without blowing everything away and restoring from mysql dumps?

p.s. stats on pc - 32gb ram total with 8gb for buffer pool size + 2tb ssd.  so more than enough ram/space.

output for systemctl status mysql.service

Mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: failed (Result: exit-code) since Thu 2019-10-03 19:57:34 PDT; 3min 54
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 8810 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=
  Process: 8849 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
 Main PID: 8849 (code=exited, status=2)
   Status: "Server startup in progress"

Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Service RestartSec=100ms expir
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Scheduled restart job, restart
Oct 03 19:57:34 bob123 systemd[1]: Stopped MySQL Community Server.
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Start request repeated too qui
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Failed with result 'exit-code'
Oct 03 19:57:34 bob123 systemd[1]: Failed to start MySQL Community Server.

Trying mysql's built in table check/fix/ etc functions did not help my in restarting mysql server in regular mode.

I ended up:
1) doing mysqldump of all the databases, into one file.
This article was immensely helpful

2) dropping 1 db at a time, and attempting to restart mysql server in regular mode, after each drop (nothing helped).

3) I ended up purging mysql from ubuntu all together, and re-installing it.

4) I did the sql dump restore doing the following command (took 3 hours for 200gb dump, on a fairly fast machine):

mysql -u root -p  <  /home/zzzzz/all_databases.sql

Best of luck, and hope this doesn't happen to you - it was definitely a waste of 2 days of trial and error, with the nuclear option as my last restort.


psql 9.5: gen_random_uuid() not working SELECT gen_random_uuid()

produces output

ERROR:  function gen_random_uuid() does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I ran CREATE EXTENSION pgcrypto; on selected database and SELECT gen_random_bytes(1) works perfectly (gen_random_bytes doesn't work on other databases where pgcrypto extension was not manually created).

% psql --version
psql (PostgreSQL) 9.5.3

Ubuntu version is 16.04.

you can check if the functions are defined using

select pg_get_functiondef(to_regproc('gen_random_bytes'));
select pg_get_functiondef(to_regproc('gen_random_uuid'));

or:

select * from pg_proc where proname like 'gen_random_%';

if both functions are not defined then you probably had an error with the extension creation - just drop it and recreate:

drop extension pgcrypto;
CREATE EXTENSION pgcrypto;


Huge "Network I/O" type resource waits We have an in-house application, developed in WinDev, that uses a Microsoft SQL Server 2012 SP2 (build 11.0.5058) database running on Windows Server 2008R2.

We are experiencing performance issues. At the same time Activity Monitor shows a huge number of waits of type "network I/O", where "huge" in this case is a cumulative wait time of 7 million seconds for a server that is up for less than 6 days:

The network connectivity itself is fine, the server is connected with 10G while the client are either physical desktop connected with 1Gb or Remote Desktop servers with 10G. Network monitoring shows no link saturation, nor any other network issue. CPU, RAM and disk I/O are also fine.

From what I read about Network I/O waits, it relates to records returned by a query that are not consumed by the clients. So I tend to think the issue is in the application but I have a hard time having the developers investigate this (not that they are unwilling to do it, but they are quite busy and it seems they don't have any clue about the root cause and how to solve it)

So the questions:

Am I right thinking the performance issue is related to those Network I/O waits?
what clue could I provide to the dev team to help them identify the cause?
apart from fixing the application, is there some fine tuning I can do on the SQL Server itself to alleviate the issue?

Stop using Activity Monitor
Use sp_WhoIsActive to figure out what's actually going on

It could be backups, in which case there's not a whole heck of a lot you can do that doesn't involve hardware upgrades (maybe that 1 Gb iSCSI wasn't such a great idea...)

It could be client-side code consuming data RBAR (think foreach loops for every row coming in), or asking for a lot of rows all at once (this is where paging queries can help).

It could be something else entirely!

To get an idea what your server is waiting on the most, head over to firstresponderkit.org -- full disclosure, I contribute to this open source project -- and grab sp_BlitzFirst (or, heck, grab'em all).

You can run this command to look at your wait stats as a whole, since startup.

EXEC sp_BlitzFirst @SinceStartup = 1

Or this to get a sample of wait stats during a slowdown.

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

Hope this helps!


need to join Vertical row as per values in Horizontal rows TABLE 1

+----+--------+-----+
| id | userId |state|
+----+--------+-----+
|  1 | 1      |AZ   |
|  2 | 1      |AK   |
|  4 | 1      |AL   |
|  5 | 1      |CO   |
|  6 | 1      |CA   |
|  7 | 2      |AZ   |
|  8 | 2      |AK   |
|  9 | 2      |AL   |
+----+--------+-----+

TABLE 2

+----+---------+--------+
| id | job  | from | to |
+----+------+------+----+
|  1 | job1 |   AZ |AK  |
|  2 | job2 |   AL |CO  |
+----+---------+--------+

I want list of Job as per their to & from both state allowed by user in mysql, For above it will return

+------+--------+
| userId | job  |
+--------+------+
|  1     | job1 | // As User 1 will have state AZ & AK
|  1     | job2 | // As User 1 will have state AL & CO
|  2     | job1 | // As User 2 will have state AZ & AK
+----+----------+

used query:

SELECT hs.userId, j.job FROM `table2` j

JOIN (
    SELECT userId,GROUP_CONCAT(CONCAT('\'', `state`, '\'' )) as stateList FROM `table1` GROUP BY userId
) hs ON  j.`to` IN (stateList) AND j.`from` IN (stateList)

If I get it right

SELECT t1.`userId`, j.`job`
FROM `table2` j
JOIN `table1` t1 ON t1.`state` IN (j.`to`, j.`from`)
GROUP BY t1.`userId`, j.`job`
HAVING count(t1.'state') = 2


cli connection to oracle on linux I am trying to connect to a local oracle on both windows and linux machines.
I am running the following command

echo Select count(*) FROM all_tables where owner='schema_name'; | sqlplus USER/PASS@localhost:1521

It works on windows but I get a syntax error on Linux. Can someone please fine tune my command to run on Linux?

This is the error I get:

  -bash: syntax error near unexpected token

You just need to wrap the query in quotes:

echo "Select count(*) FROM all_tables where owner='schema_name';" | sqlplus USER/PASS@localhost:1521


How do I use the `COPY` command with JDBC So I've been working with postgre databases and I noticed that it throws an error sometimes when I try to read a csv file and copy to a table remotely. It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

Error occurs in this line:

st.execute("COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ',';");

Here's where I connect to database:

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");

I read online that maybe STDIN can possibly help me but I haven't been able to get it to work and most examples online are using pqsl. Is there a way I can get tthis to work or do I need to go back to buffered readers?

UPDATE:

So I've tried using the CopyManager API but it has an error when trying to make a connection using DBUtil as it is not recognized even though I downloaded the jar file from this website: http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

Code:

    Connection connection = DBUtil.getConnection("POSTGRES");
    String sql = "COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ','";
    CopyManager copyManager = new CopyManager((BaseConnection)connection);
    copyManager.copyIn(sql);

I get the error in the first line with DBUtil...is importing the jar file the only way to make jdbc recognize this variable?

UPDATE2:

Thanks again guys I ended up getting the copyin to work by following the given link and a few adjustments.

public static void readInputData(String inputDataFile, Statement st) throws FileNotFoundException, IOException, SQLException {
    Connection connection = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");
    String sql = "COPY INPUTDATA FROM stdin CSV HEADER DELIMITER ','";
    BaseConnection pgcon = (BaseConnection)connection;
    CopyManager mgr = new CopyManager(pgcon);
    try {
        Reader in = new BufferedReader(new FileReader(new File(inputDataFile)));
        long rowsaffected  = mgr.copyIn(sql, in);
    } catch (SQLException ex) {
        System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
        System.exit(0);
    }
}

It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

No you have to use a BufferedReader, or InputStream

COPY opens a file handle on the server. It just needs a path, because it can open and manage its own file handle, and that's more efficient.
For client-side COPY, you have to use libpq PQputCopyData. Internally, client-side COPY is COPY FROM STDIN. The client has to send the data. This is what psql's \COPY does internally. In Java, the function that calls PQputCopyData is copyIn which requires a BufferedReader, or InputStream.


Importing external data with different field format I have the source file in Excel as follows:

  Item-------Reference---Description
  -400020--20045-------Ruler 60cms
  8900032--20012-------Eraser 45gr

Notes:
Because I can add many spaces here, the dash"-" denotes a space on the original file
The Item 400020 comes with a space before the 4 from the source.

The following file is the expected with the proper format to be exported to our DB

  Item--------Reference---Description
  0400020--0020045-----Ruler
  60cms    8900032--0020012-----Eraser 45gr

It seems that you need to convert numbers in excel table to characters first, and adjust the data (spaces). Export to CSV, and then import into database. I suppose you have varchar datatype for Item and Reference columns in the table.


Extracting files from partitioned database The company I work for performs services for a couple of hundred customers, all have their own database with identical schemas.

We are planning to merge all of these databases into one, partitioned, database as it will make reporting across all customers easier.

Each table in the database has a customer_id column, so this seems to be the best data item to partition on.

As I understand it, following the merge each partition will have its own mdf and ldf files and these will contain all the data for one customer.

One reason that we have kept the databases separate up to now is that it makes copying a database to our own development environments for debugging or testing new functionality quick as each database is relatively small (100GB typically).

My question is, given that I can get the files for a single customer from the database server is it possible to copy them to my development SQL Server and create a database for that single customer?

As I understand it, following the merge each partition will have its
own mdf and ldf files and these will contain all the data for one
customer.
...
My question is, given that I can get the files for a single customer
from the database server is it possible to copy them to my development
SQL Server and create a database for that single customer?

That is wrong.
There are no "partitioned databases" in SQL Server, there are partitioned tables and partitioned views. I suppose you tell us about partitioned tables.
Normally, every database has ONLY ONE log file, and even if for some reason some database has more than 1 log file, it has nothing to do with partitioning. So one more time, you misunderstand partitioning.
Partitions are NOT independent databases within your database, you cannot get out one partition by just copying the corrsponding .ndf file. You never can attach a single ndf file, it will never succeed.
And even if you'll try to make partial backup/restore you cannot restore database from one ndf backup without restoring PRIMARY filegroup


SQL Concurrent Inserts and Automatically Find Active Flag Record What is the proper database design to Instantaneously find Active Flag for an OLTP table: with many rows being inserted into a parallel, multithreading environment while allowing optimization, and prevent racing conditions, locking, deadlocks, etc?
Note: This table only has  inserts, no deletes or updates.

The active flag is determined by shops sending order files ( max(LastModified) column). With customer modifications and technical issues, sometimes shops can send old files later. So last file being sent may not always be current and have an earlier [LastModified] date.

Should we use :

(1) One table design with active flag,

(2) Or Two table design (transaction table and a current pointer table) in high insert environment?

We receive around 5000 inserts/sec and 100 million inserts in week time span. Consumes around 200GB of data, SQL2016 in 50 Cores, 200GB of RAM.

Method 1: One Table Method with Current Flag

create table dbo.CustomerOrder
(
    CustomerOrderId bigint primary key identity(1,1),
    CustomerId int,
    CurrentFlag bit,
    FileLastModified datetime,  -- actual shop submittal date
    Productsku varchar(25),
    Quantity int,
)

Method 2: Two Table Method, Pointer Rows to Original Table

create table dbo.CustomerOrderCurrent
(
    CustomerOrderCurrentKey bigint primary key identity(1,1),
    CustomerId int,
    CustomerOrderId bigint foreign key references  CustomerOrder(CustomerOrderId) -- refers to historical table
)

Based on the question 'What is the proper database design':

Having a 'CurrentFlag' column 'de-normalises' the schema design a little, for the sake of identifying the current order for a customer quickly.

I've worked on databases that use this method, but it is a performance trade-off that requires 'code' (e.g. triggers) to maintain the column.
This is where concurrency issues can creep in because of the extra work being done upon insert.

However, the same result can be achieved without changing the schema, by adding a descending order covering index which 'includes' the 'CustomerOrderID' in the index itself.

Testing is required for this index-only solution to ensure it is performant under the '100 million inserts in week time span' load, especially when querying the data (see end of the code sample).

Covering index example code:

--Create a test table for the example based on given schema
USE [testDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerOrder](
    [CustomerOrderId] [bigint] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [FileLastModified] [datetime] NOT NULL,
    [Productsku] [varchar](25) NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_CustomerOrder] PRIMARY KEY CLUSTERED
(
    [CustomerOrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Create the covering index
CREATE NONCLUSTERED INDEX IX_ActiveCustomerOrder
    ON dbo.CustomerOrder (CustomerID ASC, FileLastModified DESC) INCLUDE (CustomerOrderID);

-- Insert dummy data (with dates out of order)
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '17-Oct-2017', 'Prod1',20);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '15-Oct-2017', 'Prod1',30);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '18-Oct-2017', 'Prod1',41);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '14-Oct-2017', 'Prod1',42);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '13-Oct-2017', 'Prod1',43);

-- Fetch the active order for a single customer using an index hint
SELECT TOP(1) CustomerID, CustomerOrderId, [FileLastModified]
FROM dbo.CustomerOrder WITH (INDEX=IX_ActiveCustomerOrder)
    WHERE CustomerID = 100;

-- Fetch active orders for all customers
SELECT x.CustomerID, x.CustomerOrderId, x.[FileLastModified]
FROM (
SELECT CustomerID,
       CustomerOrderId,
       FileLastModified,
      (ROW_NUMBER() OVER(PARTITION BY CustomerID
                    ORDER BY FileLastModified DESC)) AS RowID
  FROM dbo.CustomerOrder WITH (INDEX=IX_ActiveCustomerOrder)
) x
WHERE x.rowid = 1;


pg_dump - table dependencies when using --table The documentation says this when using -t table:

  Note When -t is specified, pg_dump makes no attempt to dump any other
  database objects that the selected table(s) might depend upon.
  Therefore, there is no guarantee that the results of a specific-table
  dump can be successfully restored by themselves into a clean database.

However, when the table has a sequence, and the owned by for the sequence is set to the table, it is included in the dump.

Shouldn't it be excluded according to this part of the documentation? (If so, why are sequences exceptions?)

What kind of database objects are going to be excluded in -t table mode then?

pg_dump -t will dump the definition of foreign key constraints.  But will not dump the definition (or data) of the table which that constraint points to.

Similarly, if you have a check constraint which uses a function, the definition of the function will not get dumped.

I don't know the thought process behind dumping sequences.  That does seem like a documentation bug.


How often should I run integrity and consistency checks on SQL Server 2008? Before updates of our application, I run some checks on the database like CheckAllocations and CheckCatalog, so I know the database is in good shape before the update scripts get executed.

What is recommended for checks like that? How often should they be executed?

Do we need more checks besides these two?

We used to run the CheckIdentityValues as well, but I removed that, because that method will be removed (according to MSDN).

For me, it depends on your maintenance window and usage. It takes time and resources to run the DBCC checks so you want to minimise imnpact on a running app

If you can run it every night, do so. Otherwise weekly.

Or monthly if you really are 24/7.. but then I'd be running DBCC on a restored database as an extra check.

And you have to consider other maintenance too: indexes and statistics in your window


Oracle Enterprise Manager Jobs Failing Initialization The Enterprise Manager jobs on our RAC system recently started intermittently failing initialization with the following error:

  Error evaluating parameter sources/security checks/locks ORA-06508:
  PL/SQL: could not find program unit being called

We have tried bouncing DBConsole, re-submitting the job from the library and even recompiling the package being referenced.  This is happening to multiple jobs with about one execution in every ten scheduled succeeding.  Any ideas on what can be tried or checked?

Update: I've opened a SR with Oracle.

Oracle support has been no help.  After about a week of the jobs sometimes running they stopped running completely.  Every single job got an initialization error every time it ran and every time it was retried.  We are in the process of re-creating the repository and migrating to dbms_scheduler jobs.


where clause return empty row in view mysql i have two tables with the schema below:

INCOME TABLE:
INCOME_ID, INCOME_NAME, INCOME_DATE

OUTCOME TABLE:
OUTCOME_ID, OUTCOME_NAME, OUTCOME_DATE

EXAMPLE DATA INCOME
INCOME_ID | INCOME_NAME | INCOME_DATE
1         | FIRST EXMPL | 24-07-2016
2         | SEC EXAMPLE | 25-07-2016
ETC
EXAMPLE DATA OUTCOME
OUTCOME_ID | OUTCOME_NAME | OUTCOME_DATE
1          | FIRST        | 24-07-2016
2          | SEC          | 03-08-2016
ETC

so i use union all to combine these table

SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME FROM INCOME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME NAME FROM OUTCOME

and i use query above to create view

CREATE VIEW V_BALANCE AS SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME_NAME FROM OUTCOME

the view was succesfully created but when i want to add where clause in view, it returns empty row

SELECT * FROM V_BALANCE WHERE DATE BETWEEN = '24-07-2016' AND '03-08-2016'

Assuming that the two date columns are indeed DATE columns, the problem is that you are not using proper format in the date literals. Use ANSI format:

SELECT *
FROM V_BALANCE
WHERE DATE BETWEEN '2016-07-24'
               AND '2016-08-03' ;

See the documentation for other options: Date and time literals.

If the two date columns are not DATE but some CHAR or VARCHAR, then the issue is there. Convert the columns to the proper date type, otherwise the comparisons will work incorrectly.


Is MongoDB Connections getting too high? I currently have my MongoDB running in Production Environment. It has 1 Primary, 1 secondary and an Arbiter. Configuration of the ubuntu machines are pretty decent, it's got 56GB of RAM and about 100GB of HardDisk space. It's been around a week's time since MongoDb has been running continuously. I did some reading about the uLimit and it is set as

httpd soft nofile 10240
httpd hard nofile 10240
* soft nofile 65000
* hard nofile 65000

Our server-side is Java and we have implemented connection pooling. Today when I did a  db.serverStatus(), under connections I got the following

"connections" : {
            "current" : 19314,
            "available" : 32686,
            "totalCreated" : NumberLong(53770)
    },

Is this something which I should be concerned with, because I could not find any limit or number which tells me I've reached a danger limit before mongoDB crashes. Can anyone let me know if there is anything I should be doing?

RAM, mainly. Every connection gets a stack allocated, at the size of 1MB. The more connections you have, the more RAM is needed for them and the less RAM is available for keeping indices or the working set of data in RAM.

So with your 19314 connections, roughly 19GB of RAM is used for connections. That's roughly a third of your available RAM – which is too much, from my point of view. What is acceptable has very much to do with your use cases, performance needs and whatnot. Finding out an acceptable RAM utilization is out of scope of an answer and can take many hours of analysis and optimization.


Do I need to use -f to fix insufficient max server memory settings? SQL Server Version:

SELECT @@VERSION;

/*
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)
*/

Issue:

So, I was playing around with max memory and I accidentally set max memory to 128MB and now, I cannot open a connection to SQL Server. When I tried to open a session and fix the mistake, I got this wonderful pop-up.

In order to save myself, I decided to add the -f parameter to SQL Server and bounce the service.

See more about that here:

Server Memory Server Configuration Options

and here:

Database Engine Service Startup Options

Once the service was re-started, I tried once again to connect. I got the following message:

This was expected according to the information I had about the -f flag. I then was able to open a connection using the Dedicated Admin Connection (DAC). I was then able to run sp_configure and alter the max memory setting. Here is what I saw in sp_configure before I made a change. It appears that the running value is again infinity (or close enough).

I ran this update to increase the max memory to 3GB:

USE master;
GO
sp_configure
      'max server memory (MB)'
    , 3072;
GO
RECONFIGURE;
GO

And then I removed the -f parameter and re-started SQL Server. At that point, the server became accessible in multi-user mode and I had a max memory value equal to 3GB. This is not the most elegant solution I have ever encountered; but, alas it worked; so I decided to share it here.

Does someone have a quicker and cleaner solution?

I was able to reset the memory by establishing a DAC connection and without ever resorting to the -f startup parameter. However, I had to be persistent:

At 128 MB, you will get all kinds of errors in the error log:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
(repeat for other caches)
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Verbose DBCC MEMORYSTATUS dumps
errors about audit / extended events data potentially lost
There is insufficient system memory in resource pool 'internal/default' to run this query.

In some cases, the errors will be persistent enough to simply shut SQL Server down (and nothing will be recorded in the log). This happened twice during my experiments, and may also be influenced by the size of the databases attempting to be recovered on subsequent restarts. You should check whether any of the errors you receive while attempting to establish a DAC connection actually stem from the service not running (or perhaps in the process of shutting down).
In each case, I had to go to Configuration Manager and restart the service. On the second try, I think the service shut down (or was in the process) before I established the DAC connection. On the third try, there was a lot less recovery action on startup, and I was able to establish a DAC query window and adjust max server memory, then when I restarted, everything went back to normal.
Note that I was using 11.0.2376, and I recommend you perform this kind of edge case testing on builds you are likely to run in production. (Things like this may have been fixed post-RTM, and not all fixes are public.) If you're running RTM in production, then I kind of take that back, and will suggest that you reconsider that approach.

If you find that in your case you are also experiencing this "oops, SQL Server is no longer running" symptom, you could probably attach a debugger and determine exactly how SQL Server is shutting down.
So, can you do it without -f? Yes. Is it any cleaner? Only if you're really lucky. If your databases are large you are more likely to want to have a predictable number of restarts (2) because the recovery process could be a lot more involved than mine
Of course, an even cleaner thing would be to not mess around with max server memory using the GUI or ad hoc scripts that are prone to fudges - in production you want use a second set of eyes and have proper, validated, source controlled scripts that are much more predictable than "playing around." :-)


Allow self-referential foreign keys to be null from an alter-table I am trying to establish a hierarchical relationship between rows in an existing table.  There are two fields of interest: a not-nullable primary key field id, and a second nullable field parentId.

Both these fields are pre-existing, and the normal case is that parentId will be null. This seems to be a normal use case, and in fact we have some old tables using the same pattern.

I am trying to use fluentMigrator to add a foreign key constraint after the fact on these rows:

Create.ForeignKey("C", "ParentId", "C", "CId").OnUpdate(Rule.None);

It is failing with the error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_C_ParentId". The conflict occurred in database "R", table "dbo.C", column 'CId'.

This is not a problem with illegal values, as I did a bulk update on the test system, and set all C.ParentId = null, and am still getting the same error.

Thoughts?

If there are no illegal values in C.ParentID, then I am not clear what the problem might be.  Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior.  Or fluentMigrator may have a problem.


Where to place non clustered index Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and which columns should i use in incluse list.
I am using SQL Server 2008/R2

In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:

create table SomeTable
(
    id int identity(1, 1) not null
        primary key clustered,
    AnotherInt int null,
    SomeData nvarchar(1024) null
)
go

insert into SomeTable(AnotherInt, SomeData)
values(null, null)
go 1000

update SomeTable
set
    AnotherInt = id * 3 + 152,
    SomeData = 'My ID value is ' + cast(id as nvarchar(16))

So we have a test table, SomeTable, with a clustered index.  Execute the below query:

select SomeData
from SomeTable
where AnotherInt < 200

This causes a Clustered Index Scan, like you are seeing:

So analyzing the query, we have the column AnotherInt that is in the WHERE clause and being searched on.  We are also retrieving the SomeData column, so to prevent a key lookup in the clustered index (or the optimizer may even just use a Clustered Index Scan again), we'll have SomeData as an INCLUDE column:

create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt)
include (SomeData)
go

Now, executing the same query above:

select SomeData
from SomeTable
where AnotherInt < 200

SQL Server will utilize that non-clustered index to return the data.  It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:

Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.


Will dropping text column on SQL Server 2014 lock the table? I have been tasked to drop a text column from a production SQL Server 2014 table.

The table contains 61,683,821 records.

Question: will dropping the column lock the table (or cause similar disruptions)?

Bonus question: one procedure references the column (the column does not allow nulls - so, on insert we are using an empty string). Can I wrap the ALTER TABLE and ALTER PROCEDURE in a single transaction without issues? (that is, alter the table and remove the reference from the procedure).

Thanks...  I'm the developer, not the dba... why they want me to do this, I don't know :(

Update  I can answer the first question.  The DROP was instant and caused no problems.  I chose not to use a transaction.

I performed the DROP command and it was instantaneous.

I did not attempt to wrap the DROP and ALTER PROC in a transaction however.


Database Install Issue I was navigated over here from Stack Overflow, hopefully you can help me out.

So I've got a dilemma. Our company has bought a volume license for Microsoft SQL Server 2012 Standard Edition. We purchased the per core license scheme. All of that didn't have any issues; we even installed it without any problems.

The issue is coming from Microsoft though. When I configured our Volume License Account they said:

  We will issue a special installation file that will not require a key for usage. In order to install this to the proper processor
  allocation you purchased you'll need to install the file that many
  times.

So from what I've gathered, a Microsoft SQL Server 2012 Standard Edition Per-Core License by default is configured as a dual-core. So I understand if I have a quad-core I have to purchase it twice. What I don't understand, why would I need to install it two times?

I can't find any documentation on this.

That process doesn't seem correct, is there a way to test how many cores SQL is running on? Any assistance would be terrific-

Things I've Tried:

SQL Installation Guide
SQL Getting Started
Calling Microsoft

Some how I feel this issue shouldn't be nearly as complicated as it has become.

Those haven't let me come any closer to my goal, I get the notion that I should only have to install it once. But with what that Microsoft Licensing Technician said I suddenly have a cloud of doubts over my head.

Thanks again.

Update:

So within our Microsoft Volume License Account this is all it provides with our download:

Which I feel is adding to this confusion.  We have it installed on the Server; but I'm not entirely sure if I can check to see that it is running all cores.  The receipt shows we've purchased multiple licenses-

If I understand the answer posted; it should be built into our license file from the installation.  Is there a way to check that?

Have never encountered anything remotely like this and suspect something has been lost in translation. I will of course apply the usual caveat for licensing questions, "don't take anything you read here as gospel, check it with your licensing partner, and Microsoft, and call both back to speak to a different individual. There's a good chance you'll get a different answer the second time around".

You get a key, you install the software. Any CPU limitation is a product of the physical environment or version of SQL Server. For standard edition that is:

  Limited to lesser of 4 Sockets or 16 cores [source]

A volume license agreement is essentially a discount pricing arrangement, it does not involve a different installation, key distribution policy or CPU utilisation limitation:

  If you plan to buy five or more software licenses, Microsoft offers
  price advantages for volume purchasing. Discover how Microsoft Volume
  Licensing can help your organisation realise its goals and find out
  what customers and analysts have to say about it.
  [source]

Licensing is always complex. Microsoft licensing is at least as complex as other vendors. Unfortunately, my experience has been that they have not yet mastered the art of training their own, or their resellers employees about license models.

There is much fun to be had by locking a Microsoft licensing specialist in a room with a reseller specialist and watching them argue about the finer points of SQL Server or MSDN license arrangements. If you think SQL Server is a minefield, try MSDN.

NB: The "helpful" diagram at the top of the article explaining edition compute capacity limits neatly demonstrates why folk lose their way.


Routines backup in Percona xtrabackup I am using Percona xtrabackup for creating backup of mysql server. Does backup file includes all the procedures, functions and triggers ? Does it get restored when i restore it ?

Procedures, functions are stored in mysql database, triggers in  respective database directories. XtraBackup makes a copy of all of that.

It however doesn't make a copy of MySQL config file (/etc/my.cnf) you should take care of it.


Start Sql Server agent when sql server starts Is it possible to automatically start the agent when sql server starts?

When you boot the server than both windows services get started.

But when you stop the main sql server service agent also gets stopped. The problem is that the reverse is not true.  When you start the main sql server windows service again than agent wont be started automatically.
This tends to be forgotten a lot here.

Is there a way to start agent as a consequence of starting sql server?

There are couple of steps that you can do to address the problem that you faced :

Have a startup stored procedure using sp_procoption that runs and checks and alerts you that SQL Agent is not running.

IF EXISTS (  SELECT 1
       FROM MASTER.dbo.sysprocesses
       WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
 SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'yourDBMailProfile',
@recipients = 'dbateam@somecompany.com',
@body = 'Please check the status of SQL Agent. It is not running !',
@query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
@subject = 'SQL Agent is not running',
@attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
END

Set SQL Agent service to restart


How to install pgcrypto? While trying to answer Create integer id columns from existing string columns (integer coding?) it turned out that I needed my own version of postgres to play with. I already had a decent postgres version installed:

stack=# select version();
                                                version

--------------------------------------------------------------------------------
------------------------
 PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (R
ed Hat 9.1.1-1), 64-bit

but it did not have the pgcrypto module:

stack=# CREATE EXTENSION pgcrypto;
ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/pgcrypto.control": No such file or directory

so I installed:

sudo dnf install postgresql-contrib

but I still get the same error. If I look in:

]$ ls /usr/pgsql-11/share/extension/
plpgsql--1.0.sql  plpgsql.control  plpgsql--unpackaged--1.0.sql

Is there any other package I need to install, or am I missing something else?

UPDATE:

rpm -qa postgresql\*
postgresql-contrib-11.3-1.fc30.x86_64
postgresql11-11.4-1PGDG.f30.x86_64
postgresql-11.3-1.fc30.x86_64
postgresql11-server-11.4-1PGDG.f30.x86_64
postgresql11-libs-11.4-1PGDG.f30.x86_64

I get an error for dnf install postgresql11-contrib:

[...]
   Failed to synchronize cache for repo 'fedora-modular'
   Error: Failed to synchronize cache for repo 'fedora-modular'

pdgd seems to be the latest availible:

rpm -qa | grep pgdg-fedora
pgdg-fedora-repo-42.0-4.noarch

UPDATE2:

Apparently something where temporarily down, it now worked to:

sudo dnf install postgresql11-contrib
[...]
Downloading Packages:
postgresql11-contrib-11.4-1PGDG.f30.x86_64.rpm  285 kB/s | 602 kB     00:02

rpm -qa postgresql\*
postgresql-contrib-11.3-1.fc30.x86_64
postgresql11-11.4-1PGDG.f30.x86_64
postgresql11-contrib-11.4-1PGDG.f30.x86_64
postgresql-11.3-1.fc30.x86_64
postgresql11-server-11.4-1PGDG.f30.x86_64
postgresql11-libs-11.4-1PGDG.f30.x86_64

stack=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

contrib is correct, but you must have installed it for a different PostgreSQL than the one you are using.

Find out which packages are installed:

rpm -qa postgresql\*

Odds are that you need to run

dnf install postgresql11-contrib


Current Active Session on a Oracle Table I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.

Could someone please advise how I can examine that active query/session in DB?

I'm using DB visualizer tool.

There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.

You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.

Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.


How to stop a db2 instance properly? I have a db2 instance, and I need to stop it properly, clean, without any db2stop force. I need this in order to restart it fast in a batch process.

I am completly sure there are not connections or active databases; however, the instance does not stop. Attached, there is an image that shows this behaviour.

What can I do? what is using the instance that produces the SQL1025 error?

You need to deactivate all active databases first, in your case

db2 deactivate db

before stopping the instance. db2stop force does that for you, so I'm not sure what's wrong with simply using the force option.


Database design for products with multiple units I am designing a database for retail business. There are some products that can be sold in multiple units, for example, pencils can be sold in ea and dozen, paper can be sold in sheet, ream, and canton. Basically, each product can be sold in more than one unit.

The software needs to supports

Can receive products from suppliers in many unit. Sometime we might order 1 pencil and the next time we order 2 boxes of pencil.
Can sell products in multiple unit, for example, we must be able to sell 1 box and 2 pencils in the same bill.
Can check the actual items in stock.

The following are my initial design.

Table Products
ProductId | Barcode | Name   | CurrentPriceId
1         | XXXX    | Pencil | 1

Table Prices
Id | Amount
1  | 0.49

Table Units
UnitId | Name
1      | Ea
2      | Box

Table UnitConverter
ProductId | FromUnitId | Multiplier | ToUnitId |
1         | 1          | 24         | 2        | // 24 pencils equals 1 box

Table Inventories
Id | ProductId | UnitId | Quantity | PurchasePrice
1  | 1         | 1      | 48       | 0.23

Table Invoices
Id | ProductId | UnitId | Quantity | PriceId
1  | 1         | 1      | 27       | 1

Is there any flaws in my design? Is there anything that I miss?

Removed the pricing from this model, just to keep this example simple and precise! It is important to use Base Unit, that means you must maintain each SKU (Barcode) with base unit. Ofcourse, it is possible to do the purchases, store and sell with Base Unit or any other applicable unit based on Unit Conversion configuration.

Here is the design I would like to propose, please review and let me know your thoughts!

Table: Products
ProductId | Barcode | Name   | BaseUnitId
1         | XXXX    | Pencil | 1

Table: Units
UnitId | Name
1      | Each / Pieces
2      | Box

Table: UnitConversion
ProductId | BaseUnitId | Multiplier | ToUnitId |
1         | 1          | 24         | 2        | // 24 pencils in a box

//Better to store the Inventories in Base Unit. You can perform conversion on the fly only if the requirement arise.
Table: Inventories
Id | ProductId | UnitId | Quantity
1  | 1         | 1          | 48                //In pieces

Table Invoices
Id | ProductId | UnitId | Quantity
1  | 1         | 2      | 1.5                   //Sold/Purchased 1.5 boxes that means 36=(1.5*24) pieces

//You can perform transaction against the products in any legitimate Unit (validate against UnitConversion table)


Need Script to find SP Invoked in SQL I'm unable to track a stored procedure that is executing every morning 7 AM. Is there is any script to check that which job invokes that particular SP?

The SQL Server Agent job definitions are stored in the msdb database, in the sysjobs and sysjobsteps tables. You could do a plaintext search on those tables with the following query:

SELECT *
FROM msdb.dbo.sysjobsteps
WHERE subsystem='TSQL' AND
      command LIKE '%nameofmyprocedure%'

Note that there are still cases when your stored procedure still won't show up.

If there's another stored procedure run by the job that, in turn, launches the stored procedure you're looking for
If an SSIS package launches the stored procedure
If the procedure isn't launched by SQL Server Agent, but by some other scheduler task


How to run Mongo database db.currentOp(true) command using API Using the Mongo Java API I can run the currentOp() command like this:

MongoClient mongoClient = null;
mongoClient = new MongoClient( "127.0.0.1", 27017);
db = mongoClient.getDB("admin");
db.command("currentOp");

But I only get details of current operations. I need to get details of idle connections too.

With reference with this
https://docs.mongodb.com/v3.0/reference/method/db.currentOp/#currentop-examples

Behavior

If you pass in true to db.currentOp(), the method returns information on all operations, including operations on idle connections and system operations.

db.currentOp(true)
Passing in true is equivalent to passing in a query document of { '$all': true }.

If you pass a query document to db.currentOp(), the output returns information only for the current operations that match the query. You can query on the Output Fields. See Examples.

You can also specify { '$all': true } query document to return information on all in-progress operations, including operations on idle connections and system operations. If the query document includes '$all': true as well as other query conditions, only the '$all': true applies.

While using this command db.command("currentOp(true)");, I get an exception like this:

  "ok" : 0.0 , "errmsg" : "no such command: 'currentOp(true)', bad cmd: '{ currentOp(true): true }'" , "code" : 59}

Try this instead:

db.command("currentOp({$all: true})");

I've tested the JS version of this (don't have Java set up to test atm) and it worked. If you are looking for an example of how to do this, you can see the actual methods (rather than helpers) in Java in this example, compare the default on line 43 with "all" on line 49