MySQL UUID() behavior 4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk".
I answered, after trying it,
UPDATE table SET uuidcol = UUID();

It worked for me, but about half (it seems) people report that it doesn't work, saying the ID is identical on all rows.
So today, I tried again, dropping each time the column, and creating a new one with a new type, like
text, char(36), varchar(36)

and it worked again, making a unique UUID per row, each time.
What could make that it does not work for other people?
The engine for instance? ( using InnoDB on MySQL 5.7 )
edit
Did another test by

creating a simple table t of a uu varchar(36)
inserting 10,000,000 lines with uu being null
did the UPDATE t SET uu=UUID();

and all of the 10M lines have a different, unique UUID.

I'm having this exact same problem right now.
I have written a sample java Application to demonstrate the problem:
MariaDBTestUUID.java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MariaDBTestUUID
{
    private Connection connect;
    private Statement statement;
    private ResultSet resultSet;

    public void updateUUID() throws Exception
    {
        try
        {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("org.mariadb.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    // AWS Aurora (MySQL server 5.6.10) - found with SQL "show variables like '%version%';"
                    .getConnection("jdbc:mariadb://<hostname>:3306/<DB name>?user=<user>&password=<password>");
//                     // local MySQL server (5.7.22)
//                    .getConnection("jdbc:mariadb://localhost:3306/licenceserver4?user=root&password=<password>");
//                     // docker container MySQL server (5.7.30)
//                    .getConnection("jdbc:mariadb://localhost:3307/testDB?user=root&password=<password>");

            // PreparedStatements can use variables and are more efficient
            PreparedStatement preparedStatement =
                connect.prepareStatement("UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(uuid(), '-', ''))");
            preparedStatement.executeUpdate();

            queryStrings("select ID, new_ID, new_ID2, hex(new_ID3) as new_ID3 from testUUID");
            queryStrings("select count(distinct(new_ID)) as distinct_rows from testUUID");
            queryStrings("select count(distinct(new_ID2)) as distinct_rows2 from testUUID");
            queryStrings("select count(distinct(new_ID3)) as distinct_rows3 from testUUID");
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            close();
        }
    }

    private void queryStrings(String sql) throws SQLException
    {
        System.out.println("Running \"" + sql + "\"... ");
        statement = connect.createStatement();
        // Result set get the result of the SQL query
        resultSet = statement
            .executeQuery(sql);
        writeResultSet(resultSet);
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException
    {
        // ResultSet is initially before the first data set
        while (resultSet.next())
        {
            // we can only do this because we know they are all strings
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
            {
                String value = resultSet.getString(i);
                System.out.print(resultSet.getMetaData().getColumnName(i) + ": " + value + ", ");
            }
            System.out.println();
        }
    }

    // You need to close the resultSet
    private void close()
    {
        try
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connect != null)
            {
                connect.close();
            }
        }
        catch (Exception e)
        {
            System.out.println("Error closing:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception
    {
        new MariaDBTestUUID().updateUUID();
    }
}

The version of the client library is set here:
build.gradle:
plugins {
    id 'java'
}

sourceCompatibility = 11

repositories {
    mavenCentral()
}

dependencies {
    // DB driver
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.2.4'
}

To run the test application, just:
1 - connect to each of the DBs and run the following queries:
create table testUUID (ID int, new_ID varchar(100), new_ID2 varchar(100), new_ID3 BINARY(16));
insert into testUUID (id) values (1),(2),(3);

2 - replace the connection info masked for security reasons and run it on each of the DBs.
3 - set the version of mariadb client to use in the test in build.gradle
4 - run main()
If the output shows 3 for each of the distinct queries, the update stmt created distinct values in each column.
If it shows 1, the update set the same value to all rows for that column.
The queries should all return 3.
What I saw, for all DBs:

With mariadb client 2.2.3, it returns 3 for all queries.
With mariadb client 2.2.4, it returns 1 for the second and third queries (new_ID2 and new_ID3).

So, my findings so far:

Everything worked without issues (all columns in every row were set with distinct values) until version 2.2.3 of the mariadb client library.
From version 2.2.4, it started setting identical values on every row for columns we were setting to replace(uuid(),'-', '') using a single update statement.
This happens with several further mariadb client versions as well, including the latest (2.7.0).

Either the issue is related to the REPLACE() function, or to the nesting of function calls in the update. It is definitely not caused by the function UUID().

The issue happens equally in MySQL 5.7.22 (locally installed in my env), MySQL 5.7.30 (test docker container), and more importantly, Aurora DB (MySQL 5.6.10).

----------------------------------------------------
EDIT
----------------------------------------------------
For me, this was caused by a conjunction of:

the database charset
a change in MariaDB J/Connector starting at version 2.2.4
the combination of UUID() and String processing functions (like REPLACE())

Adding CONVERT() to the picture solved it:
UPDATE sometable SET uuid=UNHEX(REPLACE(CONVERT(UUID() using utf8mb4), '-', '')) WHERE uuid IS NULL;
For more details (and an upvote): https://stackoverflow.com/a/51393124/5154619


is keeping idle / unused mysql databases bad? Is there any harm like eating up resources etc. Or is mySQL engineered to just work for the used ones and keep others at hand. If so, why is there such thing like taking a db offline? Should I do that?

Thanks

The only aspects to be concerned about is Diskspace and Fragmentation.
MySQL's two main storage engines are MyISAM and InnoDB.
MyISAM
When accessing a MyISAM table, data are always read from the MyISAM table's .MYD file. When an index is being accessing, the index pages are stored in the MyISAM key cache (sized by key_buffer_size). This being the case, if you never access a MyISAM table, index pages are never loaded into memory.
InnoDB
When accessing an InnoDB table, data and index pages that are read are loaded into the InnoDB Buffer Pool (sized by innodb_buffer_pool_size). In this instance, if you never access an InnoDB table, data and index pages are never loaded into memory.
If you have innodb_file_per_table disabled, then all data and index pages would resides inside the system tablespace file ibdata1. Then, have a large ibdata1 with a lot of unused tables could introduce fragmentation that could potentially slow down access to InnoDB tables. If you are using MySQL 5.5/5.6, this should not be a consideration to worry about.
Summary
There are options and structures that can be used to preload index pages (and data pages for InnoDB). Notwithstanding, as long as you do not access those archive tables, you should be OK in all other aspects. To answer your questions, you cannot take a DB Offline and MySQL is engineered to work with what is accessed.


Convert ASCII Bytes from XML to VARCHAR I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried:

DECLARE @testxml XML = N'
<ProgramWzgSignStates>
    <WzgSignStates>
        <Element>
            <id>AQ_A07_1_280_F3~ZA</id>
            <text>
                <Element>50</Element>
                <Element>72</Element>
                <Element>97</Element>
                <Element>108</Element>
                <Element>108</Element>
                <Element>111</Element>
            </text>
        </Element>
    </WzgSignStates>
</ProgramWzgSignStates>'

SELECT
 Tab.Col.value('id[1]', 'varchar(50)') AS Id,
 Tab.Col.value('text[1]', 'nvarchar(MAX)') AS TextAsNVarChar
,Tab.Col.value('text[1]', 'varbinary(MAX)') AS TextAsVarBinary
,Convert(NVARCHAR(255), Tab.Col.value('text[1]', 'varbinary(MAX)')) AS TextUsingConvert
FROM @testxml.nodes('/ProgramWzgSignStates/WzgSignStates/Element') AS Tab(Col);

Which results in the following output:

Id                 TextAsNVarChar  TextAsVarBinary TextUsingConvert
AQ_A07_1_280_F3~ZA 507297108108111 NULL            NULL

Converting to NVARCHAR results in the ascii codes to be concatenated, while using VARBINARY returns NULL.

How can I retrieve the string 2Hallo as VARCHAR/NVARCHAR?

I think you should parse the text/Elements ,so that you can obtain the desired string.

SELECT
 Tab.Col.value('id[1]', 'varchar(50)') AS Id,
 Tab.Col.value('text[1]', 'nvarchar(MAX)') AS TextAsNVarChar
,(SELECT CHAR(t.u.value('.','nvarchar(max)'))
        FROM Tab.Col.nodes('text/Element') AS t(u)
        FOR XML PATH('')
 )TextAsNVarChar_New
FROM @testxml.nodes('/ProgramWzgSignStates/WzgSignStates/Element') AS Tab(Col)

I used FOR XML PATH to obtain 2Hallo

Id                 TextAsNVarChar    TextAsNVarChar_New
AQ_A07_1_280_F3~ZA 507297108108111   2Hallo


Transactional Replication: Cannot continue the execution because the session is in the kill state I have Transactional replication in one environment, I disable existing one and configuring it again, But While initialization in am getting following error

Distributor Subsystem: Cannot continue the execution because the session is in the kill state.

Same was working before with out any issues.

Can some onle help me on this?

Issue is resolved as of now. Just rebooting the server solved the problem.

Note: Raised EXCEPTION_ACCESS_VIOLATION stack dumps


Get old database off external hard drive My hard drive crashed yesterday, luckily I had a back up. I didn't restore from backup though cause I needed a fresh system anyways. However there was a development database I would like back.

Is there a way to grab an old database file from my back up and somehow get that database onto my new localhost?

When you say back up, do you mean a formatted back up as in the kind produced by pg_dump or an actual copy of the files in the pg_data folder?

If pg_dump, then you can restore using pg_restore or the in the UI using PGAdmin.

If pg_data folder, then you can install a new instance of Postgres and drop the pg_data folder from the old version in the same place where the new version is (but first stop the postmaster process and rename the new version of pg_data to pg_data_old first). As long as the major version numbers are the same, when you restart the postmaster process, it will read the data and log files and config files just fine (unless you have some special circumstances that I don't know about).


sp_send_dbmail with attachment SQL Server 2008, connecting via SQL Server Authentication.

I have a stored procedure in DatabaseA which calls sp_send_dbmail in msdb to send an email with a file attachment. The file is on the db server, not on a remote fileshare.

The SQL Server account being used is not sysadmin, but does belong to the DatabaseMailUserRole in msdb.

Sending an email without an attachment is fine, but when an attachment is present I get an error:

  The client connection security context could not be impersonated. Attaching files require an integrated client login

There are a few articles/posts about this out there, but some seem to say conflicting things. I've been looking into impersonation, and one thing that does work is in the stored procedure in DatabaseA, to do the following:

EXECUTE AS LOGIN = 'sa' -- or any account with sysadmin privileges
EXECUTE msdb..sp_send_dbmail ....
REVERT

I wasn't expecting this to work as I thought to send attachments, you needed to use Windows Authentication. However it does work, but it means the lower privileged SQL Server account needs to be granted permission to IMPERSONATE the sa (or other sysadmin account).

Doing my due diligence as a dev before unleashing a DBA's nightmare into the wild...

My question is: what is a good/safe way of allowing a user authenticated via SQL Server (non sysadmin) to send email attachments from the local db server disk without opening up a security hole?

Update:

Re: Credentials

I've created a new Windows Login, created credentials for that account via SSMS, mapped those credentials to my limited privileges SQL Server account. I get the error:

  Msg 22051, Level 16, State 1, Line 0
  The client connection security context could not be impersonated.
  Attaching files require an integrated client login

I must be missing something!

You need to use Credentials:

  A credential is a record that contains the authentication information
  (credentials) required to connect to a resource outside SQL Server.
  This information is used internally by SQL Server. Most credentials
  contain a Windows user name and password.

  The information stored in a credential enables a user who has
  connected to SQL Server by way of SQL Server Authentication to access
  resources outside the server instance. When the external resource is
  Windows, the user is authenticated as the Windows user specified in
  the credential.

This way you can associate your SQL login that sends the mail with an NT credential that has file access to the attachment.


Best practice database design - user rating tables I want to create a table to store user ratings of items for collaborative filtering algorithms. So far my table looks like this:

IID | UID | Rating
i.e.

item ID |user id |rating.

From collaborative algorithm examples the tables rather look like:

Item ID | Rating U1 | Rating U2 | ... | Rating Un

This means i would have to alter (extend) the table every time a new user signs up. Alternatively i have to create a new table for each user. Both does not seem to be very efficient solution. Can anyone point me into the right direction?

Thanks

The first design is normalized, the second design is in 0NF (un-normalized).  You haven't stated any reason to denormalize and you should never denormalize without a good reason.

Here is why the second design is very bad:

Think about what your queries to calculate an average rating would look like in each case.
Think about what you're going to do when your number of users exceeds your DBMS' limit on the number of columns per table.
You stated in a comment that you are concerned about duplication of data because of foreign key values, however, how much space are you wasting by having a sparsely populated table?  If you have 1,000 users and only 10 of them have rated each item on average, then you're wasting 990 bits of null flag per item.
Constantly changing your table width as users enroll can be a big performance hit because of having to move chunks of data around when the new column doesn't fit in the available free space.

You asked for best practice.  Best practice for transactional data is third normal form (or higher, if applicable).


How do I get the row as column and one of the column as rows under the first column data I have table like below:

Tname         Scode
-----         -----
SVC           code1
SVC           code2
SVC           code3
AKV           code1
AKV           code12
AKV           code3
AKS           code3
AKS           code2
AKS           code1

I would want a result like below:

SVC      AKV         AKS
----     -----       ----
code1    code1       code3
code2    code12      code2
code3    code3       code1

Use Inner Join to display the three queries horizontally. Then use Row_number for setting join conditions,

As a demo:-

create table #temp (Tname varchar(10) , Scode varchar(10))
insert into #temp values ('SVC','code1')
insert into #temp values ('SVC','code2')
insert into #temp values ('SVC','code3')
insert into #temp values ('AKV','code1')
insert into #temp values ('AKV','code12')
insert into #temp values ('AKV','code3')
insert into #temp values ('AKS','code3')
insert into #temp values ('AKS','code2')
insert into #temp values ('AKS','code1')

go

SELECT SVC , AKV , AKS
FROM (
    select Scode as SVC , row_number() over(partition by Tname order by (select 1)) rn
    from #temp where Tname = 'SVC')  t1
INNER JOIN (
    select  Scode as AKV , row_number() over(partition by Tname order by (select 1)) rn
    from #temp where Tname = 'AKV' )  t2
    on t1.rn = t2.rn
INNER JOIN (
    select  Scode as AKS , row_number() over(partition by Tname order by (select 1)) rn
    from #temp where Tname = 'AKS' ) t3
    on t2.rn = t3.rn

--drop table #temp

Result:-

| SVC       | AKV       | AKS       |
|-------    |--------   |-------    |
| code2     | code1     | code3     |
| code3     | code12    | code2     |
| code3     | code3     | code1     |


IMPDP Job Failing with ORA-54002 I am trying to import a dump file from an 11g database into a 19c database instance.  I am running the job as SYSTEM as recommended by Oracle. The job starts but quickly devolves into failing SQL errors that all share the same:

ORA-54002: only pure functions can be specified in a virtual column expression.

I have done some research on that error and have found some indications that this was an issue or bug solved after Oracle 12. The following blog addresses the issue:
Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2
...and discusses a workaround that invloves recoding the SQL.
I am creating a DDL file from the dump to give me all the code to work with.
My questions are:

Is there an alternative to re-writing all the code in the dump file and running that against the impdp job?
Is that even a possibility?
Are there other options for creating a new dump file from the 11g instance that can work with the impdp into 19c?

I think this document sums it up nicely:
10 Behavior Changes, Deprecated and Desupported Features for Oracle Database (Oracle | Docs)

Review for information about Oracle Database 19c changes, deprecations, and desupports, as well as deprecations and desupports in Oracle Database releases 18c, 12.2, and 12.1.
...
In addition to new features, Oracle Database releases can modify, deprecate or desupport features, and introduce upgrade behavior changes for your database
...

It doesn't even contain any reference to 11.x versions.
I guess your databases contains some features that may have been desupported.
Recommended Solutions
Pre-Upgrade Information Tool
You might be able to pin-point issues within your database, with information provided in the article Using the Pre-Upgrade Information Tool for Oracle Database. However, based on the information provided in your question, I guess this could take a while.
The Pre-Upgrade Information Tool is also mentioned in Oracle's whitepaper: Upgrade and Migrate to Oracle Database 19c

How to choose and use the best available method to upgrade or migrate your current Oracle database to Oracle Database 19c.

Consultant
If you are in the process of upgrading a database, then you might want to consider reaching out to a consultant who can possibly provide you with a clear concise list of features/functions/... which are no longer supported in the different database versions.
Answering Your Questions

Is there an alternative to re-writing all the code in the dump file and running that against the impdp job?

See above suggestions.

Is that even a possibility?

Maybe not, depending on the features that were used in your 11g database. It might work and it might not work.

Are there other options for creating a new dump file from the 11g instance that can work with the impdp into 19c?

You could create the DDL of all objects from the old database and try to create them in a new 19c database. But even then, you might be creating objects that contain desupported features.
Good luck.


PostgreSQL - using password/md5 authentication with empty password I have the following role in my system:
CREATE ROLE test LOGIN INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

There is no password. Trying to connect to the db from within the same machine using either psql or PgAdmin3 to the above role I'm getting:
psql: fe_sendauth: no password supplied

When the password is set I can connect perfectly.
The pg_hba.conf is default - merely:
host    all             all             127.0.0.1/32            password
host    all             all             ::1/128                 password

Create Role statement help says:

PASSWORD password
Sets the role's password. (A password is only of use for roles having the LOGIN attribute, but you can nonetheless define one for roles without it.) If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user. A null password can optionally be written explicitly as PASSWORD NULL.

Can I deduct from that that having a NULL password password authentication will always fail? Is this also the case for MD5 authentication? Is there anyway to connect using an NULL password role without specifying trust authentication?

Indeed an empty password is equivalent to no password so it's not going to be accepted by the server when pg_hba.conf says that a password is required.

Still, there are various ways to avoid inputting the password:

trust method in pg_hba.conf, possibly constrained to a specific user and database
client-side PGPASSWORD environment variable
client-side .pgpass file
dummy password like "test" stored in the same place than the login name


dropping tables with bigger size We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that state for a long time. Is there a solution to drop these tables without mysql hanging. We have a machine which has a memory of 122GB and swap of 60 GB. We use an InnoDB Engine Hope the information suffices kindly help us with this issue.

You can try if your MySQL server supports and/or behaves better in your case with setting innodb_lazy_drop_table=1 in your my.cnf file.

This option works for older MySQL versions, my understanding is that the option got removed in the newer versions and the innodb_lazy_drop_table behavior was made the default one.


Can you control what tags are used in an XML column? We allow our developers to use XML columns to store data.  We want to provide standard names to XML tags.

As a DBA, can we prevent the users from adding XML tags that are not predefined?

You could define a trigger in a suitable language to examine the data and block storing if validation errors occur. At least Postgres users could take this route.

For DB2 I found an example on how to use XML Schema for validation of XML columns. There should not a big difference to call XMLVALIDATE from within a check constraint or a trigger.
The example basically proposes the following steps:

-- Import the XML schema
register xmlschema 'http://sample' from 'file:///sample.xsd' as sample_db_scheme.sample_xml_schema;
-- Put schema to repository
complete xmlschema sample_db_scheme.sample_xml_schema;

Once the schema was imported, you can use it to validate the XML:

-- Place this call in a trigger or constraint
XMLVALIDATE(? ACCORDING TO XMLSCHEMA ID sample_db_scheme.sample_xml_schema)


determine page size for index I'm in the process of migrating a set of databases from code set ISO8859-1 to UTF-8, STRING_UNITS=CODEUNITS32. I suspect that quite a few indexes will no longer fit in there current tablespace page size (SQL0614N). Is there a formula that can be used to determine the minimum page size for each index?

I've found the following article by Serge Reilau:

https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/getrowsize?lang=en

that I guess I can modify to determine the size of the index, but I'm not entirely sure whether the same formula holds for indexes.

Any pointers?

Not sure whether this is actually correct, but I'll use it as an estimate until I figure out something better:

CREATE OR REPLACE FUNCTION GetInxSize(indschema VARCHAR(128), indname VARCHAR(128))
RETURNS INTEGER
SPECIFIC GETINXSIZE
READS SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
    RETURN
        SELECT SUM(
                    CASE c.TYPENAME
                       WHEN 'SMALLINT'        THEN length
                       WHEN 'INTEGER'         THEN length
                       WHEN 'BIGINT'          THEN length
                       WHEN 'REAL'            THEN length
                       WHEN 'DOUBLE'          THEN length
                       WHEN 'DECFLOAT'        THEN length
                       WHEN 'DECIMAL'         THEN TRUNC(length / 2) + 1
                       WHEN 'CHARACTER'       THEN 4*length
                       WHEN 'VARCHAR'         THEN 4*length + 4
                       WHEN 'GRAPHIC'         THEN length * 2
                       WHEN 'VARGRAPHIC'      THEN length * 2 + 4
                       WHEN 'XML'             THEN 4*c.inline_length + 3
                       WHEN 'DATE'            THEN length
                       WHEN 'TIME'            THEN length
                       WHEN 'TIMESTAMP'       THEN length
                       ELSE raise_error('78000', 'Unknown type')
                    END +
                       CASE WHEN NULLS = 'Y' THEN 1 ELSE 0 END
                 )
        FROM SYSCAT.INDEXES i
        JOIN SYSCAT.INDEXCOLUSE ic
            ON i.indschema = ic.indschema
            AND i.indname = ic.indname
        JOIN SYSCAT.COLUMNS c
            ON i.tabschema = c.tabschema
            AND i.tabname = c.tabname
            AND ic.colname = c.colname
        WHERE i.indschema = GetInxSize.indschema
          AND i.indname = GetInxSize.indname
@

The DB:s in question don't use compression, and I also skipped types that can not be part of an index.

SELECT rtrim(indschema) || '.' || rtrim(indname)
FROM SYSCAT.INDEXES i
JOIN SYSCAT.TABLES t
    ON i.tabschema = t.tabschema
    AND i.tabname = t.tabname
JOIN SYSCAT.TABLESPACES ts
    ON coalesce(t.index_tbspace, t.tbspace)
WHERE t.tabschema not like 'SYS%'
  AND getinxsize(indschema, indname) > ts.pagesize / 4;

alt:

SELECT distinct rtrim(t.tabschema) || '.' || rtrim(t.tabname)
FROM SYSCAT.INDEXES i
JOIN SYSCAT.TABLES t
    ON i.tabschema = t.tabschema
    AND i.tabname = t.tabname
JOIN SYSCAT.TABLESPACES ts
    ON coalesce(t.index_tbspace, t.tbspace) = ts.tbspace
WHERE t.tabschema not like 'SYS%'
  AND getinxsize(indschema, indname) > ts.pagesize / 4;

EDIT: Example of XML column in index

A table like:

CREATE TABLE TESTING_APA.ATOM_FEED
( FEED_XML XML
, FEED_XML_IS_NULL SMALLINT NOT NULL
      GENERATED ALWAYS AS (CASE WHEN FEED_XML IS NULL THEN 1 ELSE 0 END)
) IN USERSPACE1
ORGANIZE BY ROW;

Generates two indexes (not sure why though) where type name of the column is XML:

select tabschema, tabname, indname, colnames
from syscat.indexes
where indname in ('SQL190304085605540','SQL190304085605550')

TESTING_APA  ATOM_FEED  SQL190304085605540  +FEED_XML
TESTING_APA  ATOM_FEED  SQL190304085605550  +FEED_XML

In addition, explicit xml indexes also result in indexes in the catalog whose column type is XML.

Since the source database is not UTF-8, there can't be any JSON columns. For a UTF-8 database, it may be worthwhile to investigate whether indexes for JSON comes into play. The function GetInxSize is however specially designed to handle non-utf -> utf, so it will have to be changed for utf-8 databases.


T-SQL Issues With Defining 'AS' I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently they are showing as [UDF_0] to [UDF_7], i.e. :

SELECT [TABLE].[UDF_0],
       [TABLE].[UDF_1],
       [TABLE].[UDF_2],
       [TABLE].[UDF_3],
       [TABLE].[UDF_4],
       [TABLE].[UDF_5],
       [TABLE].[UDF_6],
       [TABLE].[UDF_7]
FROM [TABLE]

Would obviously display as:

UDF_0 || UDF_1 || etc...

What I would like to be able to do is display them something to this effect, but I can't for the life of me figure it out.

EXECUTE PROCEDURE [dbo].[GetProjectInfo] @Project varchar(100)

AS
BEGIN

SELECT [TABLE].[UDF_0] AS (SELECT [TBL_PROJECT_DESC].[UDF_0]
                           FROM [TBL_PROJECT_DESC]
                           WHERE [TBL_PROJECT_DESC].[PROJECT_NAME]=@Project),
       --etc....
FROM [TABLE]

Desired display would be :

Field Name 0 || Field Name 1 || etc...

You could try something like this.

declare @command varchar(8000)

set @command = 'select udf_0 as ' + (select udf_0
                                     from tbl_project_desc
                                     where project_name = @project) +
               ' from table where column = value'

EXECUTE(@command)


Can't restore Azure bacpac into localdb with SqlPackage I have made a backup of my SQL Azure in a bacpac file and when I try to import the bacpac in localdb it always fail.

Here is the command I use:

PS C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin> .\SqlPackage.exe /Action:Import
/SourceFile:"C:\Users\S\Desktop\MyDb.bacpac"
/TargetConnectionString:"Data Source=(localdb)\v11.0;Initial Catalog=MyDb; Integrated Security=true;"

And here is the error message:

*** Error importing database:Data cannot be imported into target because it
contains one or more user objects. Import should be performed against a new, empty
database.
Error SQL0: Data cannot be imported into target because it contains one or more
user objects. Import should be performed against a new, empty database.

My machine is newly installed, and I have never used localdb at all.

I encountered this problem and, after some frustration, solved it.

What had happened is that you have created, most likely by mistake, a user object in the model Database.  On Management Studio look under Databases\System Databases\model.   This is the template that is used when you create a new database, and is used when importing the bacpac.  If there are objects (such as a table) in the model, they are automatically created at the same time as the new database.  The bacpac import then encounters them in what it expects to be a blank database, and so immediately fails.

You'll also see these same objects created if you manually create a new database.

Deleting the object(s) from the model database solves the problem.


How to prevent Extended Events from tracking system fired events? I have employed Extended Events to track the DML and DDL operations that are being performed on the tables within a particular database. Apart from this I'm also tracking the procedures that a user(for now I'm the sole user) executes within the database which is being tracked. I'm using the event 'sql_statement_completed' for the same. It captures all the sql statements that are successfully completed, post which I filter the statements to store only Insert, Update, Delete and Drop statements (exec statements are also being stored). Below is the code pertaining to it:

CREATE EVENT SESSION <track> ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_hostname,
           sqlserver.sql_text,
           sqlserver.username)
    WHERE ([sqlserver].[database_name]= <database_name>
    AND [sqlserver].[is_system]=(0)
    AND (([statement] like 'INSERT%'
       OR [statement] like 'UPDATE%'
       OR [statement] like 'DELETE%'
       OR [statement] like 'DROP%'
       OR [statement] like 'ALTER%'
       OR [statement] like 'EXEC%'))))
ADD TARGET package0.event_file(
    SET filename=N'C:\ExtendedEvents\track.xel',
    max_file_size=(102400),
    max_rollover_files=(10000),
    metadatafile=N'C:\ExtendedEvents\track.xem')
WITH (MAX_MEMORY=1048576 KB,
      EVENT_RETENTION_MODE=NO_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=5 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=ON)

Now while this has been tracking the DML and DDL statements that are being executed against the tables in the database, it has also been tracking some statements that have not been executed by the user directly(particularly some procedural statements that seem to have been executed by the system itself).

The pic below subsumes the statements(not directly fired the user) that have been tracked:

This is despite the fact that I have explicitly set     [sqlserver].[is_system]=(0) in the where predicate of Extended Events, to disable tracking of system fired statements and only track the user fired statements.

So why is it tracking system fired statements as shown in the pic above?
How to circumvent the same?

These statements look to be activity generated by SQL Server tools, management API, or a third party product.  This activity happens on regular connections, not a system session which the is_system field identifies.

You might be able to add a filter on client_app_name to exclude the unwanted activity unless some of the activity from that application or tool needs to be captured.


Enabling TDE on SQL Server AOAG 2014 Databases - Guidance I am completely new to Transparent Data encryption and please help me on below questions related to enabling the TDE on existing PRODUCTION Databases.

Primary Question:

What are the things which I should keep in mind before enabling the TDE on PRODUCTION servers ?

NOTES

I have SQL Server 2014 AOAG Databases PROD site. The number of DBs is close to 10.
I read about DEK , SMK , Certificate creation process and backup of Key's and certificates.

Questions:

Now the Databases are TDE free ,what will be the new changes or effects which I will be seeing post enablement of TDE in my PROD site.
What is the performance impact on my existing PRODUCTION database accessibility.
Will there be any performance impact at all ? What is the range of impact after enabling TDE on my DBs ?
What will be the effect on the AOAG component Failover part ? Will I face issues while the Databases are with TDE enabled ?
What will be the affect while Backing up or Restoring the Databases to different SQL Server ?
If I enable TDE on Primary Replica and all secondaries will I be able to add the DBs to AOAG 2014 using the Wizard.

Please guide me , I was not completely sure of the consequences of this change handling , please help me know the disadvantages related to existing situation and post enabling TDE and performance related issues and points.

Many thanks.

1 Now the Databases are TDE free ,what will be the new changes or
  effects which I will be seeing post enablement of TDE in my PROD site.

The data at rest is encrypted, the data in memory not.
The encryption might take a while, depending on your database size, you can follow this up with:

SELECT DB_Name(database_id) AS DB, encryption_state
FROM sys.dm_database_encryption_keys;

Your backups will go slower (10-20 %) and use more cpu
If you are going to disk a lot (not enough memory, bad queries , ...) then I would not enable TDE before fixing this.

If any filegroups are read only, then enabling TDE will fail.
There are other restrictions, found here:
https://docs.microsoft.com/nl-nl/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

I would also advise to test TDE on a DEV server first.

  2 What is the performance impact on my existing PRODUCTION database
  accessibility.

  &

  3 Will there be any performance impact at all ? What is the range of
  impact after enabling TDE on my DBs ?

The data at rest is encrypted, the data in memory is not.

Your backups will go slower (10-20 %) and use more cpu.

When modifying data you will see some overhead, since it will have to be encrypted on disk.

If your disk access is low, then the overhead will be lower
and TDE will have a lower impact on performance.

  4 What will be the effect on the AOAG component Failover part ? Will I
  face issues while the Databases are with TDE enabled ?

On the secondary, do the following:

1 Make backups of the master key and the certificates

2) Create a master key

3) Restore the key and Certificate from the primary on the secondary

4) Restore the database on the secondary

5) add the database to the AG

(more info here: https://blogs.msdn.microsoft.com/sqlhenryweng/2017/11/08/configure-tde-encrypted-database-in-sql-server-alwayson-availability-group/)

  5 What will be the affect while Backing up or Restoring the Databases to
  different SQL Server ?

To do restores from the backups you need to have the same certificate that was used for encryption. This certificate is protected by the master key.

To accomplish this:

1 Make backups of the master key and the certificates
2 Restore the key and certificates on the new principal and mirror pairs

Extra checklist to migrate to a new server:

There's a database master key in the master database of the new server.
The certificate used to encrypt the database is restored along with its private key.
Restore the database

  6 If I enable TDE on Primary Replica and all secondaries will I be able
  to add the DBs to AOAG 2014 using the Wizard.

See Question 4 for more info on this.


PostgreSQL JSON field performance with large documents How well is the Postgre JSON type optimized for large JSON documents? I am especially concerned about partial retrieval (e.g. the cost of getting the last N items of a JSON array or looking up one particular item in a JSON dict) in situations where the JSON object is multiple MB in size and too large to be loaded efficiently in full.

Background: I am working with a dataset where each record has 10,000s of annotations. I do not need these annotations fully indexed, but do I need the record to insert quickly, so I am considering storing them in a JSON field instead of creating thousands of additional rows in a mapping table.

This relates to PostgreSQL 9.3.

In PostgreSQL 9.3, performance will be fairly poor for large json documents. The whole document must be parsed in order to access a field, index an array, etc. It's stored on disk as a simple text representation, the json text.

PostgreSQL 9.4 is likely to change this, with support for jsonb storage on disk. It'll still have to read the whole json object from disk to access any part of it, but it won't have the same kinds of parsing overheads you'll see in 9.3. It'll also gain many of the indexing features of hstore.

In 9.3, I strongly suspect that hstore, which is stored in a compact binary representation on disk and supports a variety of indexing features, is much more appropriate for your needs.


Primary Key on a View While answering this question
https://stackoverflow.com/questions/4668146/how-to-add-a-primary-key-on-a-oracle-view/8558346#8558346

A lot of questions popped in my mind.

1. Is it advisable to have primary key in a view.
2. Why anybody be intrested in having a primary key in a view, what are its advantages. I mean one cannot search a view by index.
3. Is it because of hibernate, but again i have used distinct keyword in a view declaration which means i cannot insert via view.

Here is my code for view

CREATE OR REPLACE FORCE VIEW VU_NAME
    (
      PRIMARY_KEY, NAME_ID, ADDRESS_ID
     )
    AS
    SELECT DISTINCT ROWNUM AS PRIMARY_KEY,
        NAME.ID UNIT_ID,
        ADDRESS_ID
    from table1;

Your code creates a column with the name PRIMARY_KEY, but this is not the same as a primary key constraint. (It is not enough to be unique.) As Branko Dimitrijevic pointed out only logical constraints can be defined on views, and a primary key constraint will be enforced by Oracle.

On a materialized view where you store your data physically in the database a index may help you to find your data faster. A primary key constraint may be helpful if you want to use replication. But a materialized view is more of a table than a view.

I hope this helps to answer your questions.


Comparing two rows in PostgreSQL I have an employee table in two different servers with the same structure. I want to shift all the data to main server but there are some records already exists in main server that are duplicate of local one's but are incomplete, now i want to compare these records so i can chose the most completed one and delete the incomplete one from one of the servers. surely i will use dblink to compare but what would be the SQL query for it?

in short some of the records are incomplete in local server while some of them are incomplete in main. is there any way to compare them and chose the completest one?

Thank you in advance.

instead of dblink, I recommend you to use potgres_fdw.
When you map the foreign table, you can use a simple SELECT INSERT to solve your problem. E.g.:
INSERT INTO remote.employee
SELECT local.* FROM public.employee AS local
WHERE NOT EXISTS (
     SELECT 1 FROM remote.employe AS remote
     WHERE local.id = remote.id
)

Remember to fix the WHERE filter to fit your table's primary keys.

EDIT: I make a small sample how to compare the rows of both tables as a gist: https://gist.github.com/sebastianwebber/7b4ab352733e8aab8b03f94d8aa0c7a9

The easy way to compare the rows is:
SELECT
    e1.id as e1_pk,
    e2.id as e2_pk,
    COALESCE(e1.* = e2.*, false) as equal
FROM employee1 as e1
RIGHT JOIN employee2 as e2 USING (id);

Hope it helps.


is there any need to index b again as it is already a under primary Is there any need to index b again as it is already a  under primary-
if primary(a,b)  and in query we have where b=' ';

Is there any need to index(b) again individually because while indexing it will take left column first then thinks of the next.

Thanks in advance!, and please help me out of this.

If you have index on (a,b) and a query with b = ? then that query can't make efficient use of that index. There are DBMS that can make use of the index in a decent way, but mysql is not one of those.


Select a count of record sharing the same value than record X Figure a table with two columns: id, catId
How to select the records having the same catId than the record having id = 3?
Example:
id catId
1   3
2   4
3   4
4   4
5   3

Input: 3

Then catId of {id = 3} is 4

Then count of records having {catId = 4} is 3

Output: 3 records

SELECT COUNT(*)
FROM table t1
JOIN table t2 USING (catId)
WHERE t1.id = 3

If id column is not unique then
SELECT COUNT(DISTINCT t2.id)
FROM table t1
JOIN table t2 USING (catId)
WHERE t1.id = 3


Trouble connecting to Oracle database with sqlplus and OS authentication I am a read-only user to an oracle database, and the DBA gave me two accounts, one with a specific user name and password, and another with OS authentication. The OS authentication is supposed to let me connect by authenticating with my active directory user account that I use to login to my laptop, where I am then running sqlplus.
I can connect and start querying just fine with this command, which shows it is picking up my tnsnames file:
sqlplus username/password@database

However, the version to connect with OS authentication doesn't work:
sqlplus /@database

ERROR: ORA-01017: invalid username/password; logon denied

Am I doing something obviously wrong? I checked in powershell that my user and domain name are the user and domain name that was set up for OS authentication for this database.
I did another test, this time using powershell to do the connection and again with my AD login that should work:
Add-Type -Path "C:\Windows\Microsoft.NET\Oracle.ManagedDataAccess\Oracle.ManagedDataAccess.dll"

#This connection string works for the normal authenticated account
$connectionString = "User Id=$username;Password=$password;Data Source=$datasource"

#But this one gives me ORA-01017 invalid username/password
$connectionString = "User Id=/;Data Source=$datasource"

$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()

I also made sure that SQLNET.AUTHENTICATION_SERVICES= (NTS) is in the sqlnet.ora file
Any thoughts?

For anything looking for an answer: I was connecting the correct way. On the backend, remote_os_authent was set to false, which would not allow this kind of authentication.


Linking Oracle 10g XE with SQL server using ODBC I am connecting Oracle 10 XE with an SQL server 2008 using ODBC in windows 7.

I have successfully done following steps:

Configured ODBC and test it, runs ok!
Configured listener in tnsnames.ora and listener.ora
Started listener from command prompt
Created database link from interface SQL command line

After creating database link, when i issue a query to SQL server an error is returned: ORA-12154: TNS:could not resolve the connect identifier specified.

Below are the contents of *.ora files:

***************************** listener.ora *****************

LISTENERSQLSERVERDSN =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSQLSERVERDSN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=SQLSERVERDSN)
         (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
         (PROGRAM=hsodbc)
       )
      )

************************tnsnames.ora************

SQLSERVERDSN  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
    (CONNECT_DATA=
    (SERVER=DEDICATED)
    (SID_NAME= SQLSERVERDSN))
    (HS=OK)
  )

*********************sqlnet.ora***********

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Please help since i have tried almost every solution found.

thanks,

Use the parameter NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name.

For example, if the default domain is set to us.acme.com, the connect string CONNECT scott/tiger@sales gets searched as sales.us.acme.com. If the connect string includes the domain extension, such as CONNECT scott/tiger@sales.acme.com, the domain is not appended.


Postgres Plus Cloud Database on AWS for write heavy social game? I'm one of four founders of a big social gaming iPhone app in Sweden. We now suffer from severe database server issues so I'm turning to you guys to get some help.
First of all, let me get this out there: I'm not the tech guy in charge of the servers but I assist with research.

We got about 8k querys/5 sec to our PostgreSQL DB and we're growing fast. The DB server is already behaving stressed out. It's a 8 core VPS with 16 gb ram.

We're looking into the possibilities of using a Postgres Plus Cloud Database solution from EnterpriseDB running on AWS. (http://www.enterprisedb.com/cloud-database)

1) Anyone have experience in this matter?

2) We've also been looking into the options to use a dedicated server but we need the possibilities to scale the server up to 20-30 times. Is this relatively easy with EnterpriseDB's solution?

3) If i got it all right: EnterpriseDB offer a cluster solution which theoretically would solve our problems. Am I right?

For high performance systems, I think virtualization usually causes a lot more problems than it solves.  Save yourself the headache.  Build a decent db server inhouse.

It is hard to say what your current problem is, and virtualization makes troubleshooting much harder.  Is the load cpu bound?  Is it I/O bound?  Are you having concurrency issues?  Something else?

Now, if your 8k queries every 5 sec are not very complex queries, then the immediate question is why are you having performance problems.  It may just take some db tuning.  I have seen some indications that you should be able to get several times that on good hardware if things are well tuned.  Again virtualization is probably making things harder to fix rather than easier.

Finally, I would highly recommend looking into Postgres-XC.  It allows you to do sharding for write-extensibility while managing concurrency and consistency a layer above.   I would start tuning what you have and move from there though.


How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL? I'm working on a project that must support two database engines; SQL Server and PostgreSQL.

We are using NHibernate as the ORM.

We are running into performance issues with certain queries. Using SQL Server tools we've come up with several new indexes and statistics that greatly improve performance on SQL Server.  However, I'm not certain how to implement the same indexes and statistics on PostgreSQL.

Two examples are:

    CREATE STATISTICS [perfStat_Answer_02] ON [dbo].[Answer]
    ([InclusionExpressionGroupId], [QuestionId], [AnswerId])

    CREATE NONCLUSTERED INDEX [perf_Answer_01] ON [dbo].[Answer]
    (
        [QuestionId] ASC
    )
    INCLUDE (
            [AnswerId],
            [InclusionExpressionGroupId],
            [AnswerConceptId],
            [Revision],
            [AnswerText],
            [AnswerOrder]
    )
    WITH (
            SORT_IN_TEMPDB = OFF
            , IGNORE_DUP_KEY = OFF
            , DROP_EXISTING = OFF
            , ONLINE = OFF)
    ON [PRIMARY]

What is the syntax for the INCLUDEd fields in PostgreSQL, if such a feature exists?

How do we add statistics?

Reading the PostgreSQL docs, I'm not convinced that either are supported.  However, I would like to know if there is any way to accomplish something similar.

I don't know what CREATE STATISTICS does, but statistics for the optimizer are collected using the ANALZYE command when autovacuum is running - which is turned on by default.

Statistics are always collected for all columns, no need to turn it on specifically.

You can control the level of details collected for the statistics on a per-column basis using ALTER TABLE ... ALTER COLUMN column SET STATISTICS integer.

An index in PostgreSQL is always non-clustered, so I'd assume that the above index maps to a regular index on the QuestionId column.

Not sure about the INCLUDE part. I assume this is to support index only retrievals if that index is chosen by the optimizer.  As PostgreSQL does not yet have an index-only retrieval, there is no equivalent technique there.


migration by cloning a Production SQL Server host into a Dev environment We are doing some SQL Server migrations, various versions, by cloning (snapshot backups) the host servers to the new DC in a different domain. Work is still in planning stage, not yet implemented. For the DEV environment in the destination domain, we need to have Production data in the databases from the source instances.
I am therefore thinking that we could clone the Production server from the source domain and rename the host in the destination Dev environment. The SQL server host name part of the instance name will also be renamed i.e. host_server_name\instance_name.
Are there other considerations that I need to have in going down this route? Things I need to be aware of about moving from Prod to Dev? Logins/users from Prod will be dropped and test logins/users will be scripted in.
Any thoughts will be appreciated please. Thank you.

Ideally you should have an SSIS package that runs at night and copy the new data from your Production environment to your Developer/Test environment.

This is a guide that will help you doing it right
This is a schema to check if you are doing it right
This is a post that will help you avoid troubles to production


Backup MySQL after inactivity I currently backup daily which I think is fine, however, I want to backup more frequently when there's no activity (e.g., after X minutes of no INSERT/UPDATE/DELETE excluding SELECT).

I'm currently thinking of creating a custom script that will check for CHECKSUM TABLE mytable and see if that changes, every X minutes.

Is this a good idea or is there a better way, perhaps an existing tool?

If you set up Replication, you can lose the entire server, yet recover everything except anything that was "in progress" when the disaster hit.

That is, the Slave is always nearly an up-to-the-second copy of the Master.  No backups, no locks, no reload, etc.

Then, if you like, you can "continually" backup the slave.  It won't impact the Master and all the activity there.

If your fear is that something will be deleted, then needs to be recovered, see CHANGE MASTER TO ... MASTER_DELAY ....


SQL Query: Print string (i.e. header) in output before returning table results I’m using the following query to generate a summary of invoices for a particular customer.

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

This SQL query is used as an input in a homegrown application which executes the query against the DB, and outputs the results to an Excel spreadsheet. The output looks something like:

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

Before this spreadsheet can be sent to the customer, text or “header” information must be manually added to the file such as:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

Is there anything I could add to the SQL query that display this text in the output, before returning the table data? Ideally, I would want the SQL query to return:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

I tried using something like:

PRINT N’Frank’s Fish Supply’;
PRINT N’Remit To’;
PRINT N’123 First St., Wonderland, HI 12345’;
PRINT N’’;

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

However, the results of the PRINT commands only show up in the “Messages” tab of SSMS rather than the “Results” tab, and do not appear in the Excel output when run through the homegrown application. Are there any SQL commands I could use to output this static text before the results of SELECT statement?

I stumbled upon this thread which contained a suitable solution. I ended using a query similar to the following to generate my desired results:

select customer_account, store_number, invoice_total
from ((select 'Frank’s Fish Supply' as customer_account, '' as store_number, '' as invoice_total, 1 as which
      ) union all
        (select 'Remit To:' as customer_account, '' as store_number, '' as invoice_total, 2 as which
      ) union all
        (select '123 First St., Wonderland, HI 12345' as customer_account, '' as store_number, '' as invoice_total, 3 as which
      ) union all
        (select '' as customer_account, '' as store_number, '' as invoice_total, 4 as which
      ) union all
        (select 'customer_account' as customer_account, 'store_number' as store_number, 'invoice_total' as invoice_total, 5 as which
      ) union all
      (select customer_account, store_number, invoice_total, 6 as which from arinvc where company LIKE '%Joe’s Bait Shop%'
      )
     ) t
order by which;


Import binary database file into postgres I have a quite newbie question that I do not know if is possible or not:

I had to format my computer, and the only way to keep my postgres database was accessing to data with my folder browser (nautilus)

So now I have the data folder with a lot of binary data inside, from /var/lib/postgresql/9.3/main/base

My question is:

Is there any form to import theese data to my (now) new postgres database?

I think copy paste is not going to work.

Maybe with some import? Or have I permanentely lost all my data?

I run postgres 9.3 in Ubuntu 14.04

Thanks

Assuming you took a copy of the whole data directory including pg_xlog, pg_clog, global, base, etc, then you can simply ensure that PostgreSQL 9.3 is installed and:

postgres -D /path/to/data/directory -c "port=5433"

then in another terminal use pg_dump to dump the database(s) from the database running on port 5433 with (eg) pg_dump "dbname=mydb port=5433".

This process is well-covered elsewhere; it's just starting a new PostgreSQL server and dumping the database. So I won't go into detail and cover all the possible errors, etc.

However, if you only copied the base directory, you're pretty much stuffed. The data is sort-of there, but it's missing all the vital information that tells the system what databases there are, handles crash-safe updates, keeps track of rolled back vs committed transactions, and lots more.

If you only have base, recovery might be possible but would probably involve paying an expert for a fair bit of time to try to extract the data. Even then, it'd likely be significantly corrupted - duplicates in primary key columns, deleted data reappearing, old versions of updated rows reappearing, recently committed transactions only half-complete, etc.

So I hope you kept backups.


Backup compression in SQL server 2008R2 and above Recently we got a new server to take care of and found that there are two data drive:

S: 1 TB where resides 10 databases(mdfs and ldf's, though they should be on different drives but that's how we got the set up) where max size of a DB being 780 GB and rest pretty small.

X: similar scenario  here as well a DB of 720 GB resides rest small and bits, total of 10 DB's

Now, we've been asked to perform the backup strategy and for that i am sure we are short of backup drive as there does not exist any other drives apart from C: and above two.

Since this being sql server 2008R2 enterprise, Compression feature has been enabled and we are good to take compressed backups:

But my question here would be how can i predict or calculate what size of drive would be require to accommodate the backups, means using the compression feature how can i predict close to the amount of disk space required to backup these 20 user databases.

You can't completely predict it, but you can backup smaller databases with compression and if the data sets are similar (based on the factors listed below), you can predict a ratio that you may be able to apply to the larger databases.

It's easiest to just quote the MSDN on compression here:

To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_size

For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space. To query on these columns, you can use the following Transact-SQL statement:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

The compression ratio of a compressed backup depends on the data that has been compressed. A variety of factors can impact the compression ratio obtained. Major factors include:

The type of data.
Character data compresses more than other types of data.
The consistency of the data among rows on a page.
Typically, if a page contains several rows in which a field contains
the same value, significant compression might occur for that value.
In contrast, for a database that contains random data or that
contains only one large row per page, a compressed backup would be
almost as large as an uncompressed backup.
Whether the data is encrypted.
Encrypted data compresses significantly less than equivalent
unencrypted data. If transparent data encryption is used to encrypt
an entire database, compressing backups might not reduce their size
by much, if at all.
Whether the database is compressed.
If the database is compressed, compressing backups might not reduce
their size by much, if at all.


Turning off auto shrink on all SQL Server databases. Why doesn't this work? I figured I could use sp_MSforeachdb to solve this problem, but I get an error message.

sp_MSforeachdb '
BEGIN
USE [?]
DECLARE @dbid INT
SET @dbid = DB_ID()
IF(@dbid > 4)
BEGIN
    --PRINT ''[?]'' + CONVERT(VARCHAR, @dbid)
    --ALTER DATABASE [?] SET AUTO_SHRINK OFF
END
END;
'

If I run the above query with the PRINT line uncommented, I get a list of all databases except the system databases. However, when I uncomment the ALTER DATABASE line, I get these two error messages:

  Msg 5058, Level 16, State 2, Line 9
  Option 'AUTO_SHRINK' cannot be set in database 'master'.
  Msg 5058, Level 16, State 1, Line 9
  Option 'AUTO_SHRINK' cannot be set in database 'tempdb'.

This seems to break the operation at some point, so that only some of the databases get auto shrink disabled.

Any idea how I can disable auto shrink on all databases? Bonus question: why doesn't my approach work?

Unfortunately, the procedure sp_MSforeachdb is and will always be unsupported and thus, kind of unreliable.

There's an article on CodeProject that shows how to filter databases when using this DB. But I have the same problem as yours on my local 2008 R2 installation. Filtering doesn't work.

Our own friend, Aaron, wrote a nice article some time ago, about writing a different and better version of sp_MSforeachdb. See his article here. Use that SP and the filtering parameters will do what you need.


MongoDB failover when master is stuck on IOWait? We host a MongoDB 3.4 replica set in AWS with three nodes:  a primary, secondary failover and an arbiter.  Normally, if the primary instance dies, the failover to the secondary is pretty quick (10-30 seconds).

Today we had a network issue where the MongoDB primary instance lost connectivity with the disk containing the database for about 3 minutes and CPU IOWait went to 100%.  During this time, queries to the primary just hung and went into timeout.  Probably because the primary was still up (though unresponsive), the replica set did not failover or even start a vote.

Is there a configuration which would produce a failover also in such cases?  Or is there some ready tools that could force a failover if simple queries to the primary node start taking too long?

A closely related question is discussed extensively in the comments on SERVER-14139, a bug report against filed against mongodb. To summarize, it is not feasible to build a fully general hang detection system inside a server process.

The commentary in discuss a monitoring approach that kills either the process or shuts down the operating system and can be done using a cron job or the watchdog daemon. Because a mongod process cannot win an election before it has read and written some data to its storage engine, it is safe to immediately attempt to restart mongod after you kill it. The restarted process should not accept connections and certainly will not be able to win an election for primary.

A ticket linked to SERVER-14139 covers an implementation in the enterprise (non-free) version of MongoDB of a storage watchdog timer. Organizations that can instead use the watchdog daemon or an external monitoring process should prefer that approach, because it can protect against more kinds of resource failure.


Issues after schemas migration from mysql 4 to mysql 5.6 Recently I've migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully.

After the import, I went over the log file and i found some issues. Does anyone know what is that and how to fix it?

InnoDB: Error in pars0opt.cc: table cars/cars_ftr_a has prefix_len != 0
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_CONFIG'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_1'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_2'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_3'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_4'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_5'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_6'
InnoDB: in InnoDB data dictionary has unknown flags 40.

Edit (adding create table statement)

Create Table: CREATE TABLE `cars_ftr_a` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `manufacturer` varchar(100) NOT NULL DEFAULT '',
  `model` varchar(255) NOT NULL DEFAULT '',
  `object_type` int(10) unsigned NOT NULL DEFAULT '0',
  `fts_string` text NOT NULL,
  `manufactor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `model_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version` varchar(100) NOT NULL DEFAULT '',
  `group_id` int(10) unsigned NOT NULL DEFAULT '0',
  `group_name` varchar(100) DEFAULT NULL,
  `a` int(10) unsigned DEFAULT NULL,
  `b` varchar(100) NOT NULL DEFAULT '',
  `price` int(10) unsigned NOT NULL DEFAULT '0',
  `c` int(10) unsigned NOT NULL DEFAULT '0',
  `d` varchar(100) NOT NULL DEFAULT '',
  `e` float NOT NULL DEFAULT '0',
  `f` int(10) unsigned NOT NULL DEFAULT '0',
  `g` int(10) unsigned NOT NULL DEFAULT '0',
  `h` int(10) unsigned NOT NULL DEFAULT '0',
  `i` smallint(6) unsigned NOT NULL DEFAULT '0',
  `j` varchar(100) NOT NULL DEFAULT '',
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `l` int(10) unsigned NOT NULL DEFAULT '0',
  `m` int(10) unsigned NOT NULL DEFAULT '0',
  `n` varchar(100) NOT NULL DEFAULT '',
  `o` int(10) unsigned NOT NULL DEFAULT '0',
  `p` text NOT NULL,
  `q` varchar(100) NOT NULL DEFAULT '',
  `r` int(10) unsigned NOT NULL DEFAULT '0',
  `s` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`fts_string`(10)),
  FULLTEXT KEY `fts_string` (`fts_string`)
) ENGINE=InnoDB DEFAULT CHARSET=hebrew

There are two things that came into focus once you posted the table structure

InnoDB Table has Fulltext Index
Your Character Set is Hebrew

Would you believe someone asked 7 years ago Does MySql full text search works reasonably with non-Latin languages (Hebrew, Arabic, Japanese…) ???

You should make sure you have that the Hebrew character set in your MySQL installation

mysql> select * from INFORMATION_SCHEMA.CHARACTER_SETS where CHARACTER_SET_NAME='Hebrew';
+--------------------+----------------------+-------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION       | MAXLEN |
+--------------------+----------------------+-------------------+--------+
| hebrew             | hebrew_general_ci    | ISO 8859-8 Hebrew |      1 |
+--------------------+----------------------+-------------------+--------+
1 row in set (0.00 sec)

along with the necessary collations

mysql> select * from INFORMATION_SCHEMA.COLLATIONS where CHARACTER_SET_NAME='Hebrew';
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| hebrew_general_ci | hebrew             | 16 | Yes        | Yes         |       1 |
| hebrew_bin        | hebrew             | 71 |            | Yes         |       1 |
+-------------------+--------------------+----+------------+-------------+---------+
2 rows in set (0.00 sec)

You could try creating a new table with the same structure and loading it.

METHOD #1

CREATE TABLE cars_ftr_a LIKE cars_ftr_a_new;
INSERT INTO cars_ftr_a_new SELECT * FROM cars_ftr_a;
RENAME TABLE cars_ftr_a TO cars_ftr_a_old,cars_ftr_a_new TO cars_ftr_a;

METHOD #2

ALTER TABLE cars_ftr_a ENGINE=InnoDB;

Then, go look into the error log and see if those same warnings reappear. If they do not, you are good to go. You should also look into tuning the InnoDB Fulltext Options.

UPDATE 2017-08-22 23:17 EDT

METHOD #3

Another thing you can try is to drop the Fulltext Index and create it again

ALTER TABLE cars_ftr_a DROP INDEX `fts_string`;
ALTER TABLE cars_ftr_a ADD FULLTEXT `fts_string` (`fts_string`);

Then, go check the logs to see if the same errors came back or not.

UPDATE 2017-08-23 11:04 EDT

METHOD #4

CREATE TABLE cars_ftr_a LIKE cars_ftr_a_tmp;
ALTER TABLE cars_ftr_a_tmp DROP INDEX `fts_string`;
INSERT INTO cars_ftr_a_tmp SELECT * FROM cars_ftr_a;
RENAME TABLE cars_ftr_a TO cars_ftr_a_xxx,cars_ftr_a_tmp TO cars_ftr_a;
ALTER TABLE cars_ftr_a ADD FULLTEXT `fts_string` (`fts_string`);

Load new table without Fulltext Index. Then create Fulltext Index last.


Ensure order when select two rows using two identifiers on the same column I'm trying to write a query to retrieve exactly two rows from a table using the same column for identification.

SELECT * FROM teams WHERE name = $1 OR name = $2;

Is there any way to ensure that the result for name = $1 is the first result returned and name = $2 is the second?

SELECT *
FROM teams
WHERE name IN ($1, $2)
ORDER BY CASE WHEN name = $1 THEN 0 ELSE 1 END;


Get distinct values through direct query of an index I have a relatively large SQLite database that I'm building. The general access profile of this database is:

Initial load of 300 million rows.
Initial read of each row, and UPDATE on approximately 30 million rows
Ongoing read-only access through a python script with a MyDatabaseReader class.

The data structure is a flat denormalized table and queries are of the form:

WHERE chromosome=TEXT, position=INT, reference_sequence=TEXT, alternate_sequence=TEXT

I would like to validate that an incoming query requests a valid chromosome name, based on the contents of the database. Requesting chromosome='chr13' is valid, but in the case of '13' or 'chr31' I would like my python script to throw an error, instead of silently returning zero rows. To enable this, my during the init of my python class, it makes an initial query to get the set of valid chromosome names:

SELECT DISTINCT chromosome from dbsnp;

This query takes a LONG time, and bogs down the execution. I have tried both a compound index on (chromosome, position, reference_sequence, alternate_sequence), as well as a single index on chromosome, and have verified with EXPLAIN QUERY PLAN that in both cases the index gets used.

sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT chromosome FROM dbsnp;
order|from|detail
0|0|TABLE dbsnp WITH INDEX chromosome ORDER BY

My question: Is there some SQL trick to query directly from the chromosome index. I don't care about anything else in the row, and it seems that the index is a pre-built version of the data that I want to return.

Alternatively, I'm considering constructing a chromosome_names table that I populate with my SELECT DISTINCT query above after database load and update. Because it scares me to construct a static table that can fall out of sync with the main table I'm considering triggers to update the chromosome_names table on change of the main table. However, I'm concerned that this may cause significant churn should I update rows in the main table, and more importantly, that I'm reinventing what is essentially contained in an index.

Is there a good way to get my distinct values query from the index directly, or alternatively, is there a way to have SQLite throw an error should the queried value for chromosome be outside of the set of contained values (note: position, ref, and alt sequences are expected to sometimes query for unexpected values so erroring on zero rows returned will not work).

Thanks

I've never used SQLite so bear with me here. But it seems as if this problem is common among many RDBMS platforms.

When you select distinct values from your column you end up scanning all rows in the index:

This can be a great strategy if there aren't many rows in the table or if the column doesn't have very many duplicate values. But if you have millions of rows for each distinct value then you'll scan millions of rows just to return a single unique value. For data sets like that, it can sometimes be better to get the first distinct value, then skip to the next value, and so on. This can be accomplished via recursion in some platforms. You can also run one query at a time with each getting the next distinct value. For example, you could get the first value with this query:

SELECT MIN(chromosome) FROM dbsnp;

Then get the next value with this query (substituting the filter with the values of the first query):

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;

And the next:

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;

And so on. For these queries I'm getting index seeks:

For a relatively small data set, the single distinct query takes about 320 ms and the series of LIMIT 1 queries only took 4 ms. You'll of course need to write more code to use this solution, but it might be worth a shot.

db fiddle


Estimate amount of downtime from RDS backup I have an Multi-AZ RDS instance. Due to some maintenance I want to create a consistent snapshot of the database during the day, but this means shutting down all our services. I noticed from the AWS console that the time between a database backup start and finish is about 30 minutes.

What I was wondering is if this impacts performance and if so, how long and when? Is it like EBS where I can start everything up again when the snapshot moves from "creating" to "progess" or do I need to wait until it is entirely complete?

Because if possible I'd like to minimize the amount of downtime our services have, meaning that not having to wait for the entire backup would save us a lot of time.

Usually, RDS backups don't cause downtime. Similar to EBS backup, the I/O needs to freeze the DB for a split fo the second to get a consistent LVM snapshot. After that, it is irrelevant how long the backup takes. Alternatively, you may consider taking the snapshot on the Read Replica to avoid the impact on Master.

  During the automatic backup window, storage I/O might be suspended
  briefly while the backup process initializes (typically under a few
  seconds). You might experience elevated latencies for a few minutes
  during backups for Multi-AZ deployments. For MariaDB, MySQL, Oracle,
  and PostgreSQL, I/O activity is not suspended on your primary during
  backup for Multi-AZ deployments, because the backup is taken from the
  standby. For SQL Server, I/O activity is suspended briefly during
  backup for Multi-AZ deployments."
  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html

Since you've mentioned that you need to perform RDS Maintenance after the backup.
you may want to check out how to avoid downtime altogether during the RDS maintenance.
https://workmarket.tech/zero-downtime-maintenances-on-mysql-rds-ba13b51103c2

Disclosure: I work as a Lead DBA for WorkMarket.


SSIS File System Task Editor closes unexpectedly In a package I am developing, I am having an issue using a variable for the destination connection in the File System Task.  I have done the following:

I dragged a new File System Task to the design surface.
I changed the IsDesinationPathVariable parameter to true

When I use the drop down arrow to select a variable, the editor closes.  The same thing happens when I try and paste the variable name.

I have highlighted the drop down that causes the editor to close below:

I am using Visual Studio Ultimate 2012 to develop the package, build 11.0.50727.1. The SQL Server Integration Services Designer is version 11.0.3436.0.

I've been having the same issue. I have managed to get around it by manually entering the variable I'm using in the "Destination" property on the Properties window for the File System Task.  For instance, "User::DestFileName".


You can use row_number() to do this, with something like this:

select [Currency Code], [Starting Date], [Exchange Rate Amount]
from (
  select *,
  row_number() over (
       partition by [Currency Code]
       order by [Starting Date] desc
    ) as RN
  From myTable
) X where RN = 1;

The row number will number the rows per currency (that's what partition by does) starting from the newest, and then the outer select will just take the first for each currency.


Create a partition for a temporal table based on a period To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension.

For my Employee dimension, I created a temporal table. Now I want to split data into partitions based on periods.

I already declared StartTimeV and EndTimeV  (the created columns for my temporal tables) as system period start time and end time periods. I want to create a partition for my temporal table based on the period (StartTimeV and EndTimeV).

I mean you can... but I don't think you necessarily should...

Given a setup of the form...

use [master]
create database ynot;
alter authorization on database ::ynot to sa;
go
use ynot
go
create table dbo.foo (
     id         int identity primary key
    ,info       varchar(100)
    ,StartTimeV datetime2(0) generated always as row start
    ,EndTimeV   datetime2(0) generated always as row end
    ,period for system_time (StartTimeV, EndTimeV)
)
with (system_versioning = on (history_table = dbo.foo_hist));

You can...

Remove the system versioning
Apply partitioning to the underlying history table
Reapply system versioning

...in this way

alter table dbo.foo set (system_versioning = off);
go
create partition function pf_months(datetime2(0))
    as range right for values (
         '20190501'
        ,'20190601'
        ,'20190701'
        ,'20190801'
        ,'20190901'
        ,'20191001'
        ,'20191101'
        ,'20191201'
);
go
create partition scheme ps_months
    as partition pf_months
    all to ([PRIMARY]);
go
alter table dbo.foo set (system_versioning = off);
go
drop table dbo.foo_hist;
go
create table dbo.foo_hist (
    id         int          not null,
    info       varchar(100) null,
    StartTimeV datetime2(0) not null,
    EndTimeV   datetime2(0) not null
)
on ps_months([StartTimeV]);
go
alter table dbo.foo
    set (system_versioning = on (history_table = dbo.foo_hist));
go

It works as-is, although I won't speak to interoperability, scaling, or storage management concerns at this time.

insert foo (info )
values ('stuff')
      ,('things');
go
update foo set
    info = 'objects'
where id = 1;

delete foo where id = 2;

select * from foo for system_time all;
go

Demo cleanup

use [master]
drop database ynot;
go


Reading ASYNC_NETWORK_IO wait stats I was troubleshooting some bad performance for a client yesterday and identified that there was a missing index for a particular set of queries that were heavily used

(this was for an ERP applicaton: Microsoft Dynamics NAV)

After adding these indexes database performance for the areas that were giving the client grief improved massively, however, during the visit I noticed that at times starting up the application took forever and other users were complaining of freezing and slow response (it didn't happen against all users simultaneously).

The infrastructure consists of a DB server, a service tier X 2 (that runs the application logic and caches data/business object metadata) and the client machine.

I checked the server CPU/memory stats on the service tier and it didn't look taxed at any point (CPU barely got above 50% at any one time and memory was barely touched - around 20-30%)

The SQL server was set to use unlimited memory so that needs addressing but there was actually free memory on the SQL box as all their databases combined don't exceed 10 GB and there is 12 in the server (though the page life expectancy perf counter didn't get much above 200-300 so I'm not sure why this would be)

The users mentioned this had only started happening recently (last 3 weeks) and also mentioned that other applications such as Excel were taking a long time to open files from the network so I had a look at some of the performance counters on the server and ran Brent Ozar's sp_Blitz just to check if there were any things that needed addressing immediately

The script gave me this report on the ASYNC_NETWORK_IO stats:

3437.8 hours of waits, 58.6 minutes average wait time per hour, 100.0% of waits, 0.0% signal wait, 20062055 waiting tasks, 616.9 ms average wait time.

The server has 4 virtual cores

I ran the same script on a server on another client's network (running the same solution) and the script came back with no significant wait stats

I'm suggesting to the client that since they don't need the 16 GB allocated to the service tiers (as they barely get near 4-6 GB usage) that they should consider reallocating some of the memory to SQL as their DB will grow (NAV databases tend to grow quickly), but I want to advise them best on if there could be a network issue

How do I correctly interpret these waits? Am I looking at a potential network issue as my gut says yes?

While the ASYNC_NETWORK_IO wait type is usually a consequence of the application design, the network issues can also cause the stockpiling of the ASYNC_NETWORK_IO wait type.

It is advised to check the following, regarding the network issues:

Check network bandwidth between the SQL Server and client
Make sure that all network components between the SQL Server instance and the client, such as routers, switches, cables are properly configured, fully functional and dimensioned according to required bandwidth
Review the Batch requests per second counter values, as this could often indicate the reason for high ASYNC_NETWORK_IO waits
Make sure that Auto Negotiate of the NIC is detecting the network bandwidth properly

Also -checking the NIC bandwidth utilization is prudent, even often overlooked.

Please, take a look at the Reducing SQL Server ASYNC_NETWORK_IO wait type aticle, to get more details on interpreting high ASYNC_NETWORK_IO wait type values.


Create Security Policy without schemabinding Create Security Policy without schemabinding?

Is this a bad idea? I have an indexed view in another database that I need to use in my security predicate for row level security. I can either turn off schemabinding and use a synonym, or I have to start using transactional replication to copy to a table in the main database. I control both dbs and they are on the same server.

The security policy documentation incorrectly states that the inline table value function must have been created using the SCHEMABINDING option. The actual behavior, however, is that the function needs to be schema bound only when the policy is schema bound (default). Since the function with cross-database references cannot be schema bound, the implication is the policy cannot be schema bound either.

Without schema binding, the onus is on you to ensure no breaking changes are made to the function or dependent objects and that the other database is always available. Also, users will need SELECT and EXECUTE permissions on the function as well as SELECT on objects accessed by the function (not necessary with SCHEMABINDING on the function). This may be a security concern depending on the sensitivity of the predicate data.

I think the general best practice is SCHEMABINDING for the above reasons but you'll need to weigh those benefits with the costs of managing replicated data.
Besides transactional replication, you could use an ETL process or Service Broker to replicate the security policy data. Not sure why the data is in a different database to begin with but another option would be to move the objects to the same database.


Stored procedure inserts 2 columns into a table with 3 columns that are not null, with no defaults on the table I struggled with how to ask this question. This is from a piece of software that we have used for well over a decade. For the life of me, I can't figure out why it works. There are 3 objects in question, a table, a view, and a stored procedure. Here is the definition of them:

Table:

CREATE TABLE [dbo].[AgentGroupsHistoryMap]  (
    [AgentGroupFK]  int NOT NULL,
    [UsersFK]       int NOT NULL,
    [Start]         datetime NOT NULL,
    [Stop]          datetime NULL,
    CONSTRAINT [PK_AgentGroupsHistoryMap] PRIMARY KEY CLUSTERED([AgentGroupFK],[UsersFK],[Start])
    WITH FILLFACTOR = 80 ON [PRIMARY])
ON [PRIMARY]
    WITH (
        DATA_COMPRESSION = NONE
    )
GO
CREATE NONCLUSTERED INDEX [IX_AgentGroup]
    ON [dbo].[AgentGroupsHistoryMap]([AgentGroupFK])
    WITH (
        FILLFACTOR = 80,
        DATA_COMPRESSION = NONE
    )
    ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AgentGroupStop]
    ON [dbo].[AgentGroupsHistoryMap]([Stop])
    WITH (
        FILLFACTOR = 80,
        DATA_COMPRESSION = NONE
    )
    ON [PRIMARY]
GO

View:

CREATE VIEW [dbo].[AgentGroupsMap] AS
    SELECT distinct AgentGroupFK, UsersFK from AgentGroupsHistoryMap where Stop is null

GO

Store Procedure:

CREATE PROCEDURE [dbo].[usp_AddAgentToGroup] @GroupKey int, @AgentKey int
AS
    SET NOCOUNT ON
    INSERT INTO dbo.AgentGroupsMap (dbo.AgentGroupsMap.AgentGroupFK, dbo.AgentGroupsMap.UsersFK)
    VALUES(@GroupKey, @AgentKey)
    RETURN
;

GO

This SQL was all written on a SQL 2000 system, but we've been able to keep it all up to date. When I started with it, we were on SQL 2005, and it went through 2008, and then we jumped to SQL 2014.

That stored procedure, which seems like it shouldn't work; does. I just executed and watched the results.

This came up because in out test environment we started seeing an issue. Both production and test are on the same version of SQL. It works fine in production, but in test, we started getting this error:

>[Error] Script lines: 1-1 --------------------------
 Id 4403, Level 16, State 1, Line 4
    Cannot update the view or function 'dbo.AgentGroupsMap' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
 Msg: 4403, Level: 16, State: 1, Procedure: usp_AddAgentToGroup, Line: 4

So I have 2 questions:

Why am I getting that error? I don't see any of the conditions that the error says are the case.
How in the world does this work? From what I can see, the sproc is able to insert into the "Start" field, which is not null and has no default, without specifying a value.

Someone else figured it out. In production, there is a trigger on the view that was not in place in testing.

2 of us didn't think to look for a trigger on a view. Mystery solved.


enable log_bin_use_v1_row_events in mysql 5.6 I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly.
I have  log_bin_use_v1_row_events switched off in MYSQL 5.6 and I am using mixed format replication.

Can anyone give me advise on doing above.

There is one thing that cannot work with MySQL Replication from 5.5 to 5.6 : DATETIME. Why ?

MySQL 5.6 DATETIME has a very different byte format because it accommodates microseconds. When the binlog_format is ROW, or when MIXED switches from STATEMENT to ROW, only a 5.6 Slave can unpack the row change from the relay logs. A slave running MySQL 5.5 cannot handle that.

I have two recent posts where I mentioned this

Jan 02, 2015 : mysql replication master 5.5 slave 5.1 error on create database
Jun 18, 2015 : mysql master slave replication scenario

The workaround I learned from my boss back in January was to switch binlog_format to STATEMENT and restart mysqld.

If you enable log_bin_use_v1_row_events on a MySQL 5.6 Master to let a MySQL 5.5 slave read it, that sounds like it could work. I still worry about the DATETIME representation. If you set the option log_bin_use_v1_row_events to true and it stops MySQL 5.6 from writing microseconds with binlog_format ROW or MIXED, then you I think have something. Based on my two posts, I cannot guarantee other statements would be immune.

I would normally say "GIVE IT A TRY !!!" but I have never tried that log_bin_use_v1_row_events. So, go ahead and try and let us know.


How do I optimize large table so queries that target only recent data perform optimaly? So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved.

I am open to changing design or if there is mechanism in MsSql to solve this. I have limited options to try different solutions as database is in production and its hard to reproduce.

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created, -- index2 NONCLUSTERED INDEX
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active
[Modid] [timestamp] NOT NULL
)

Time series should be clustered by time:

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created,
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active
[Modid] [timestamp] NOT NULL,
CONSTRAINT NONCLUSTERED PRIMARY KEY ([ID])
);

CREATE CLUSTERED INDEX cdxMyDataTime_1 on dbi.mydata (Time_1);

In time series data the time is almost always specified in queries, and usually as a range. With a clustered key based on the time range queries will scan only the relevant portion of the table.

The ID can continue to server the logical primary key role, but there is little benefit from having the table clustered by it since the ID is never used as a range. So off it goes into a non clustered constraint. Singleton lookups based on ID will need two reads, but who cares, is two fast reads.

If you cannot have the Time_1 as clustered key a frequent trick used is to retrieve the ID range for each day, eg. create table of days and min_ID/max_ID. Then use the ID range that covers the time range you're interested in to restrict the range of the scan on the table. This advantage of this approach is that it works for multiple time columns (you cannot cluster by Time_1 and Time_2...) and it is less invasive (can be tried out right away w/o modifying the table). But this approach is very invasive in the application query design, it requires discipline in remembering to use the ID ranges for the days desired. Note that since the IDs usually don't change, they can be cached in the app.

Simple indexes on Time_1 and Time_2 do not work because they hit the index tipping point. Covering index (with INCLUDE columns) on Time_1 and Time_2 explode the data size as often the included column required are ... all columns.


Getting deadlock information easily I know to check for live deadlocks

select * from sys.sysprocesses where blocked>0

However, that is only for deadlocks that are current. How do we check for deadlocks from the say the last 24 hours? Note: I want to be able to do this without having to configure on any trace etc - just get the information from a system table. Is that possible?

Thanks.

Either use Trace Flag 1204 and Trace Flag 1222, or use Sql Server profiler to analyse and save deadlock graph information.

See also here


Normalising a database for monitoring stations I have data about monitoring stations. For now it is stored in Excel tabs. I have decided to try to make a database. It will be my first contact with databases. Here is an example how it looks now in Excel

Typ   Station name   Typ of area     Location     long    lat    Time/date/concent
pm      Kittsee        suburban     background
SO2   Oberschützen      rural        traffic
SO2     Kittsee        suburban     background
....
....

As you can see some stations occur two or more times. So I have decided to put them in another tab as

typ_id  typ
 1       pm
 2       SO2

then put station_id and typ_id together in the next tab

typ_id  station_id
 1         1
 2         1
 2         2

So my question is can I use this approach for location and area? So columns in my main table will look like this:

station_id typ_id station_name area_id location_id long lat time_date_conc

Yes, you can. However, some database implementations allow for enum or enumerable types, so if your type of station can come from just a small list, it may be better using an enum types sir, which has a similar effect to having a lookup table but runs much faster.
Also, you may not need a station I'd. Primary keys can be made of multiple columns and I don't know, but I would guess that there are no stations with the same name and type. If this is the case, you can make the primary key composed of both he'd columns!
Search for primary composite keys in your database implementation for how to do this, but in SQL it is done like so:

name VARCHAR(100) NOT NULL
type VARCHAR(10) NOT NULL
PRIMARY KEY (name, type)

Although bear in mind that your full create statement will have more information, and you may not want those lengths for your text.


SQL Server 2012 Failed to launch local ScenarioEngine.exe I have been trying install SQL Server 2012 Developer Edition for quite some time now..
I always get "stuck" at Install Setup Files, the install wizard just hides,

Now, i reviewed the log file for setup, which is: SqlSetup_1

There,i found these errors

Attempting to launch process (null)SQLServer2012\x86\ScenarioEngine.exe
Error: Failed to launch process
Error: Failed to launch local ScenarioEngine.exe: 0x80070003

The full SqlSetup_1 log:

http://pastebin.com/Ehvi1Nvm

any suggestions ?

(Community Wiki answer from comments provided by Jon Seigel.)

Try putting the install files in a location that is not on your desktop. It seems there's an issue resolving the base path to that executable


During restore restart, an I/O error occurred on checkpoint file Short version: Running a RESTORE VERIFYONLY throws one of these errors:

  During restore restart, an I/O error occurred on checkpoint file '\Server\Backup\SQLDirectBackup\DatabaseName\RestoreCheckpointDB24.CKP' (operating system error 5(Access is denied.)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

Or

  During restore restart, an I/O error occurred on checkpoint file '\Server\Backup\SQLDirectBackup\DatabaseName\RestoreCheckpointDB11.CKP' (operating system error (null)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.

More information:

Using Ola's SQL Server Maintenance Solution
The file share is on a Dell DR4300 device
Has occurred on multiple servers using this device for backup storage
Different servers, service accounts, versions of SQL Server have all thrown this error
Not consistent or reproducible as far as I can tell (happened Monday & Wednesday but not Tuesday)
Subsequent runs of the backup jobs have succeeded without error
Time of day does not seem to be relevant, it's occurred at all hours
The error is not immediate, it occurs minutes into the execution of the command
Default backup directory points to \\Server\Backup\SQLDirectBackup\DatabaseName\
Hasn't happened with an actual restore yet... but I'd like to avoid that. Hence the post.

I've never seen this error before, and all my research is coming up empty. It is possible that this has been happening for a long time but I am new to the company and only recently added Severity 16+ alerting so I cannot be sure.

Anyone have any ideas? Safe to ignore? Potential time bomb? I'm stumped.

Anyone have any ideas? Safe to ignore? Potential time bomb? I'm
  stumped.

It's not a "bomb", this means only that if you want to use RESTORE with RESTART option (to complete interrupted RESTORE ) you'll not be able to do it, i.e. checkpoint file cannot be used and the RESTORE will not be able to skip some passes if they woudl be done prior to interruption.

You can read more about checkpoint file here: Restores using Invalid Backup Default Locations

The cause of this error is that your default backup location becomes temporarily inaccessible.

You can just ignore this error or you can change the default backup location with any local path.