Precautions when designing database schema if tables are going to have billons of rows I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have further inventory table with reference to dictionary table. I have not worked earlier with such huge database.

InnoDB is good for maintaining relational database. There are category and subcategory references, so i am going to use InnoDB. There will come a situation where I need to show total count based on category or subcategory, or even based on state and city. So on... it may any combination.

I am familiar with creating indexes on most searched columns. I have heard about table partitioning also helps to speed up queries.

My Questions is what points should I consider at this early stage when creating such database tables which are going to have billions of rows, so that later on when tables grow larger, I can keep table performance at high level with select queries and DML quires (insert, update).

Guidance will help me a lot.

Start understanding that the problems you face have nothing to do with the database technology you use. They are caused by physics, and physics does not care about oracle, microsoft or open source. It is the same.

Generally - depending on the queries you may want a HUGH server (albeit "billions of rows" sounds too much for a phone dictionary). Or cluster of servers. Not "I got 8gb memory". I have a sql server here with 48gb memory and guess what - that is small, not to say tiny, when talking big dada.

Also, ad hoc MAY not work. Oracle Exadata is great hardware to make ad hoc work by pure power - but even there are limits.

  There will come a situation where I need to show total count based on category
  or subcategory, or even based on state and city.

There is this concept called a data warehouse and it has a totally different schema than an oltp (transactional) database. I suggest you read up on database concepts and the difference between OLAP and OLTP, also about normalization and - the other side - star schemata (used for reporting.

Generally you may run into a real time problem here, so reglarly updated precalculated tables may be in order for this. The numbers may be off, but who cares if the number by city is 5 minutes late (or whatever update interval you use).

A lot depends on exact business requirements. On what is acceptable, how often certain queries happen, whether they can be offloaded. A data mart / warehouse may be a good idea to isolate reporting / aggregating queries from OLTP queries to lessen burden on servers. Sometimes you can not. But not everything can / should be done ad hoc, especially when mutiple users hit the same (heavy) queries.

Generally - guidance is "read some books or hire someone who knows how to work with databases". Point being - you dont even know enough to ask the right questions. And you focus on Innodb for the totally wrong reason ("good for maintaining relational database" - that is like saying "Big mac makes good food because their hamburgers have meat" - no real sense in the statement at all.


How many tables will be there in Magento 1.8 I just started using Magento with 1500 products and I saw the DB size is about 150MB and when I looked at the tables, the count is 250 tables.

Is it normal?

Should I worry about anything?

I host this on EC2 micro instance.

I wouldn't worry if I were you. Many systems (Oracle's EBS e-business suite) have gazillions (a techical term meaning a large number) of tables, most of which will never be used by many customers. Any major e-commerce/ERP/ERM system will be like this. You'll find that you'll be spending 85% of your time working with just a few modules which are core to your own business - and that's as it should be.

Just as a matter of interest, what prompted you to go with Magento? You business speciality? Are you going commerical or Open Source? Did you consider other solutions Sugar CRM/Adiempere/Apache OfBiz?


Insert result of a parallellized select query into a table, in Postgresql According to https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html,

  "Even when it is in general possible for parallel query plans to be
  generated, the planner will not generate them for a given query if any
  of the following are true:
      The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within
  a CTE, no parallel plans for that query will be generated. This is a
  limitation of the current implementation which could be lifted in a
  future release."

Indeed, when I try to insert result of a parallel SELECT query into a table (either by SELECT.. INTO either by WITH..SELECT..INTO, the query is not executed as parallel query.

My question is: Is there any way to trick the Postgresql so that a SELECT query is executed as parallel query and then its result inserted into a table?

I think the only even worth trying is to use PostgreSQL FDW which may or may not work. Your use case would also have to be super-niche for that to pay off, like a complex GIS aggregation query.

You can see this restriction is still very much noted in the code,

  The most significant restriction imposed by parallel mode is that all operations must be strictly read-only; we allow no writes to the database and no DDL.  We might try to relax these restrictions in the future.

And, again here

 * Don't allow writes in parallel mode.  Supporting UPDATE and DELETE
 * would require (a) storing the combocid hash in shared memory, rather
 * than synchronizing it just once at the start of parallelism, and (b) an
 * alternative to heap_update()'s reliance on xmax for mutual exclusion.
 * INSERT may have no such troubles, but we forbid it to simplify the
 * checks.

I think it's being stopped by a call to ExecCheckXactReadOnly here in ExecCheckXactReadOnly

if (plannedstmt->commandType != CMD_SELECT || plannedstmt->hasModifyingCTE)
    PreventCommandIfParallelMode(CreateCommandTag((Node *) plannedstmt));

Shy of that Maybe in PostgreSQL 11, or 12?


Where can I get this nice phpMyadmin features for mongodb In phpmyadmin

Say I browse a table, or do anything.

I will be told the actual sql command that I should run to execute the query.

Say I want to do a query and then I want to do something else similar. I can just run some query. Copy the SQL command, modify it, and move on. This is obviously less error prone than writing queries from scratch, see that it doesn't work, and not knowing what's wrong.

In rockmongo I don't have that feature.

Say I want to search for certain record. Then I want to modify the search with additional search. I can't do that.

So what other software to access mongodb that give that feature?

Another related feature is a feature to run any sql command I wish in phpmyadmin.

I do not have that feature in mongodb.

Technically you don't have this feature in MySQL either - PHPMyAdmin is a third part tool just like rockmongo, but basically rockmongo does not have the features you want.  I am sure there are features people would like in PHPMyAdmin too that are not there yet.  When that happens you basically have 3 options:

Request the feature from the people running the project
Write it yourself (if the current tool is open source you could fork and extend etc.)
Use another tool that does have the feature you want

The effectiveness of 1 and 2 will depend on the feature you want, how easy it is to implement, what your skills are, how active the project is etc.

For number 3, there are other options for a GUI interface to MongoDB.  MongoVUE seems to have the view option you want, for example - however it is Windows based:

http://www.mongovue.com/features/

There are others too, MongoHub, PHPMoAdmin etc. - you can find them listed here:

http://www.mongodb.org/display/DOCS/Admin+UIs

There is a comparison of several of the UIs (and more in the comments on this blog post also):

http://timgourley.com/2010/03/16/tuesday-night-tech-mongodb-ui-edition.html

Finally, you should also consider MongoHQ and MongoLab as potential solutions, I know that HQ at least will let you add your own databases, and they both provide excellent admin interfaces for MongoDB.


Issue with variable length string between compatibility level 80 and 100 I was getting tons of issues related with a specific scenario on my current enterprise.

We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to 100, but some of our store procedure are having a problem, demonstrated by the code below:

CREATE TABLE #PRUEBA (texto1 char(30), condicion char(30))
INSERT INTO  #PRUEBA VALUES ('PFI','1')
INSERT INTO #PRUEBA VALUES ('CFI','2')
SELECT
    CASE
        WHEN condicion= '1' THEN texto1
        ELSE 'TFI'
    END
FROM  #PRUEBA
DROP TABLE #PRUEBA

Compatibility -> 80

'PFI                           ' -> char of 30 length
'TFI                           ' -> char of 30 length

Compatibility -> 90

'PFI                           ' -> varchar of 30 length
'TFI'                            -> varchar of 3 length

Compatibility -> 100

'PFI                           ' -> varchar of 30 length
'TFI'                            -> varchar of 3 length

Why did this happen?

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?

Is there any solution to get the same result on compatibility 100 like compatibility 80?

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?

No, not that I am aware of. This is an issue of datatype precedence with regards to the CASE expression. The CASE expression needs to return a consistent datatype no matter which branch is selected. Since VARCHAR has a higher precedence than CHAR, the only choice here is to return VARCHAR. It looks like you will need to update the code to possibly convert the result of the CASE expression to CHAR(30). For example:

SELECT
    CONVERT(CHAR(30), CASE
                        WHEN condicion= '1' THEN texto1
                        ELSE 'TFI'
                      END)
FROM  #PRUEBA

Or, I suppose you can simply convert the string literal to match the datatype of the column:

SELECT
    CASE
      WHEN condicion= '1' THEN texto1
      ELSE CONVERT(CHAR(30), 'TFI')
    END
FROM  #PRUEBA

Also, why are you using compatibility level 80? That is for SQL Server 2000. And you are about to move up to SQL Server 2016? By default that would be compatibility level 130. And lucky for you, level 100 is available in SQL Server 2016, though it is the lowest level available. Still, if you are currently relying upon SQL Server 2000 behavior, you will likely have a lot more to change than just this if moving to SQL Server 2016.


Postgresql 9.4 incrementing value issue I am trying to go from the first table: test4 (pid, event, time) and count how many events transition to another (in second table eventtransitions).

For instance, in the final table, there should be a +1 count for prev = A and event = B because the first two rows has A happening before B and the pid for both are 1. The issue I am having right now is that my query only increments once, but should actually increment as many times as the transitions occur. The final table should have prev = A event = B with count = 2.

Test4 table:

 pid | event |        time
-----+-------+---------------------
   1 | A     | 2016-09-23 02:21:13
   1 | B     | 2016-09-23 03:24:13
   1 | A     | 2016-09-23 04:21:13
   1 | B     | 2016-09-23 05:21:13

Evventtransitions table:

 prev | event | count
------+-------+-------
 C    | B     |     0
 C    | A     |     0
 A    | C     |     0
 B    | C     |     0
 A    | B     |     1
 B    | A     |     1

The query I tried is this:

update eventtransitions
    set count = count + 1
from
(select rank() over (order by pid, time) as rank,
    pid, event, time
    from test4) as a
join
(select rank() over (order by pid, time) as rank,
    pid, event, time
    from test4) as b
    on a.rank = b.rank - 1
    where eventtransitions.event = b.event and prev = a.event
    and a.pid = b.pid;

Use the window function lag() in a subquery:

UPDATE eventtransitions e
SET    count = COALESCE(count, 0) + t.count
FROM (
   SELECT prev, event, count(*) AS count
   FROM  (
      SELECT lag(pid)   OVER w AS prev_pid, pid
           , lag(event) OVER w AS prev    , event
      FROM   test4
      WINDOW w AS (ORDER BY time)
      ) t
   WHERE   prev_pid = pid  -- only where pid matches
   GROUP   BY prev, event
   ) t
WHERE  e.prev = t.prev
AND    e.event = t.event;

Seems like you only want to count rows cases where the previous row has the same pid. I eliminated all other.

Aggregate in the next step to get counts.  (That's what you were missing!) Then add the result to the existing count in eventtransitions.

COALESCE to catch possibly existing NULL values in eventtransitions.


Cannot restart mysql I don't know why, all of a sudden I cannot open anymore MySQL. I get the following error:

bomble@ChemAlive:~/ChemAliveCode/SABIC$ mysql -h localhost -u bomble

  ERROR 2002 (HY000): Can't connect to local MySQL server through
  socket '/var/run/mysqld/mysqld.sock' (2)

I tried to restart MySQL doing:

bomble@ChemAlive:~/ChemAliveCode/SABIC$ sudo /etc/init.d/mysql restart
[sudo] password for bomble:

But it failed:

  * Stopping MySQL database server mysqld [ OK ]
  * Starting MySQL database server mysqld [fail]

I had a look at my error.log file. Here's what it says:

  InnoDB: Unable to lock ./ibdata1, error: 11
  InnoDB: Check that you do not already have another mysqld process
  InnoDB: using the same InnoDB data or log files.

Don't know what that means?

Found it:

If I restart using

service mysql restart

instead of

sudo /etc/init.d/mysql restart

It restarts fine, and I can then login to MySQL.


Clarifications on Transparent Data Encryption This is the first time we are applying TDE on production databases that are critical and I have the questions below.

While TDE is in progress:

Can we still access the application that is working on this database with a connection string?
If TDE fails while it is encrypting the database, will it break the .mdf file?  How would I recover the database?
Are the .ldf and .mdf still accessible, can we read and write to these files?
What are the database operations that will be affected while the TDE is in progress?
Will TDE have any effect on disk usage?
Will TDE have any effect on database backups? Are both the .bak, .trn files encrypted automatically if TDE is enabled?
What are the areas that we need to concentrate on while TDE is being applied? Is an outage needed?
Will there be any effect on SQL Server service packs or cumulative updates?

The platform of relevance is SQL Server 2014 SP2 GDR with availability groups.

Can we still access application that is working on this database with a connection string?

Yes.  TDE is implemented only at the storage layer.  Data is decrypted as it is read from disk.  Accordingly, nothing beyond the storage layer is affected.

  If TDE fails while it is encrypting data, will the encryption fail and break the .mdf file. How would I recover the database?

I would highly recommend getting a full backup prior to encrypting just to be safe.  However, it is highly unlikely that a problem will occur that will corrupt the database or leave it unusable.

  Are the .ldf and .mdf, still accessible and we can read and write this file's?

As mentioned previously, the encryption/decryption is done at the storage layer, and the system uses the .mdf and .ldf files in the same way that it uses them for unencrypted databases.  It simply encrypts data before writing, and decrypts data after reading.

  What are the database operations that will be affected while the TDE is in progress?

The system will use significant CPU and disk I/O as it has to read, encrypt, and write every data page in the database.

  Will TDE have affect on disk usage?

No, the size of the database will stay the same. If you're using compression or other technologies it will continue to work with it at TDE is done only when reading and writing pages to disk.

  Will TDE have any impact on database backups? Are both the .bak and .trn files encrypted automatically if TDE is enabled?

The backups may be somewhat slower depending on a varying number of factors and the backup files will be somewhat larger if you were previously using compressed backups.  How much will depend on the type of data and version of SQL Server (latest versions support compression on TDE databases).  Yes, the data in the .bak and .trn files will be encrypted.

  What are the areas that we need to concentrate on while TDE is being applied? Is an outage needed?

I would recommend concentrating on a nice cup of tea or coffee.  While you are enjoying the beverage of your choice, keep an eye on the SQL Server error log.  An outage is not required, but due to the resource utilization required to encrypt the database, having a maintenance window is ideal.

  Will there be any affect on SQL Server service packs or cumulative updates?

In general, the answer is "no."  However, if a defect is found that affects only TDE, then it may be advisable to install the CU that includes the fix.  We can't see into the future, but you will install the same CU and service packs whether TDE is used or not.  It is simply a feature of the database engine, so all updates to the database engine will include all of the TDE functionality.


Why does SESSION_USER return dbo instead of SQL login? I have a trigger on a GP table that records when a critical field is changed and who changed it. A sample of the trigger follows.

CREATE TRIGGER [dbo].[TRACKAPP_UPDATE] ON [dbo].[Table]
AFTER UPDATE
AS
DECLARE @MDFUSRID   char(15)
DECLARE @SOPNUMBE   char(32)
DECLARE @DOCSTATUS  INT

BEGIN
BEGIN
IF UPDATE(DOCSTATUS)
BEGIN
    SELECT @MDFUSRID = SESSION_USER
    SELECT @SOPNUMBE = SOPNUMBE FROM inserted
    SELECT @DOCSTATUS = DOCSTATUS FROM INSERTED

In 99% of the cases this returns the correct name of the user who changed the field. If any of the ERP people change a field (myself included) the user shows a dbo. I have always assumed this is an access issue. However, we have found a couple of users who also return as dbo rather than their name.

Do you have any suggestions as to where I should start looking?

You are confusing Users with Logins. Users are "principals" at the Database-level while Logins are principals at the Server-level (a.k.a. Instance-level).

Each Database has a single User, dbo, that is mapped to a Login. A Login that is mapped to the dbo User will show up as dbo for SESSION_USER when the "current" Database is the DB in question.

AND, Logins in the sysadmin fixed server role (including Windows Logins that connect via a Windows Group that is member of the sysadmin fixed server role) will show up as dbo in SESSION_USER. This will be the case even if the Login is mapped to a User in that Database that has a default Schema other than dbo (since this issue has nothing to do with Schemas). An example of this is shown in "Test 3" in the test code below.

SESSION_USER can return a Login name when there is no User in the DB to map to. For example, if a Login has the CONTROL SERVER server permission such that they can connect to any DB but are not in the sysadmin fixed server role, SESSION_USER will return the Login name for only those DBs that do not have a User mapping. An example of this is shown in "Test 5" in the test code below.

What you want to use is ORIGINAL_LOGIN(). This function will return the actual Login that was used to connect to the instance, even if Impersonation has been used to alter the current security context to that of another Login.

The following tests illustrate and prove the behavior noted above:

SETUP

USE [master];
CREATE LOGIN [GazooLogin] WITH PASSWORD = 'NevrCrack';
CREATE DATABASE [GazooDB] COLLATE Latin1_General_100_CI_AS;
ALTER AUTHORIZATION ON DATABASE::[GazooDB] TO [sa];
GO

CREATE USER [GazooUser1]
  FROM LOGIN [GazooLogin]
  WITH DEFAULT_SCHEMA = [GazooSchema1];
GO

CREATE SCHEMA [GazooSchema1]
  AUTHORIZATION [GazooUser1];
GO

USE [GazooDB];
CREATE USER [GazooUser2]
  FROM LOGIN [GazooLogin]
  WITH DEFAULT_SCHEMA = [GazooSchema2];
GO

CREATE SCHEMA [GazooSchema2]
  AUTHORIZATION [GazooUser2];
GO

TESTS

--------------------------------------------------
-- Test 1:
USE [GazooDB];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser2   Dali\Solomon    GazooLogin  GazooLogin

USE [master];
SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 2:

USE [master];
EXEC sys.sp_addrolemember N'db_owner', N'GazooUser1';

EXECUTE AS LOGIN = 'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- database     CONNECT

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser2   Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 3:

EXEC sys.sp_droprolemember N'db_owner', N'GazooUser1';

EXEC sys.sp_addsrvrolemember N'GazooLogin', N'sysadmin';

EXECUTE AS LOGIN = 'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 4:

CREATE LOGIN [SuperDuperDatabaseOwner] WITH PASSWORD = 'NevaCrack';
ALTER AUTHORIZATION ON DATABASE::[GazooDB] TO [SuperDuperDatabaseOwner];

USE [GazooDB];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    GazooLogin  GazooLogin

REVERT;

SELECT * FROM sys.database_principals;
SELECT * FROM sys.server_principals;

--------------------------------------------------
-- Test 5:

DROP SCHEMA [GazooSchema2];
DROP USER [GazooUser2];

USE [master];

EXEC sp_dropsrvrolemember N'GazooLogin', N'sysadmin';

GRANT CONTROL SERVER TO [GazooLogin];

EXECUTE AS LOGIN = N'GazooLogin';

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooUser1   Dali\Solomon    GazooLogin  GazooLogin

USE [GazooDB];

SELECT * FROM sys.fn_my_permissions(NULL, N'database')
-- lots of stuff :)

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- GazooLogin   Dali\Solomon    GazooLogin  GazooLogin

USE [master]; -- can only revert from DB where EXECUTE AS was run
REVERT;

--------------------------------------------------
-- Test 6:

USE [GazooDB];

EXECUTE AS LOGIN = N'SuperDuperDatabaseOwner';

SELECT SESSION_USER AS [SESSION_USER],
       ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
       SUSER_SNAME() AS [SUSER_SNAME],
       SUSER_NAME() AS [SUSER_NAME];
-- dbo  Dali\Solomon    SuperDuperDatabaseOwner SuperDuperDatabaseOwner

REVERT;

CLEANUP

--------------------------------------------------
-- CLEAN UP:

USE [master];
DROP DATABASE [GazooDB];

DROP SCHEMA [GazooSchema1];
DROP USER [GazooUser1];
DROP LOGIN [GazooLogin];
DROP LOGIN [SuperDuperDatabaseOwner];

!!!  ALSO, when working with Triggers in SQL Server, you need to be very careful when doing things such as the following:

SELECT @SOPNUMBE = SOPNUMBE FROM inserted
SELECT @DOCSTATUS = DOCSTATUS FROM INSERTED

The problem is that the INSERTED pseudo-table can have multiple rows in it, based on how many were affected by the DML operation. If there is more than one row that was updated, the value in those two variables will be that of the last row in INSERTED (and they are not in a specific order). If a column's value is the same across all rows then I suppose it doesn't matter, but it is still a best practice to not structure Trigger code in this manner.


Passing current server name from SQL Server Agent job to PowerShell I have a SQL Server Agent job that calls a PowerShell script in one step. And I want to pass the SQL server name and the current job-id to my script.

With the job-id I have no problems. But with the server name...

My PowerShell call looks like this:

.\StopServices.ps1 -ServerName $(ESCAPE_DQUOTE(A-SVR)) -JobId $(ESCAPE_DQUOTE(JOBID))

The job-id works perfect, but the server name is empty.

How can I pass the server name to my PowerShell script?

Replace A-SVR with SRVR.

A-SVR is for alerts.

SRVR will contain server name and instance name.

Refer to document on MSDN.


Get data from a MDX linked server and use it on a FROM clause on SQL without OPENQUERY i have the following SQL query on SQL Server 2012:

WITH MdxQuery (field1,field2,field3) AS (
    SELECT field1, field2, field3
    FROM OPENQUERY(my_linked_server, 'my_long_mdx_query')
)
SELECT * FROM MdxQuery

The 'my_long_mdx_query' is a MDX query running on a linked server. Everything works fine except when the MDX query is longer than 8000 characters, which is unsupported by OPENQUERY.

I have tried to use variables, but without success:

Declare @TempTable TABLE (
     field1 varchar(max),
     field2 varchar(max),
     field3 varchar(max)
)
Declare @strMDX varchar(max)
set @strMDX = N'my_long_mdx_query'
INSERT INTO @TempTable (
    field1,
    field2,
    field3
) EXEC (@strMDX) at my_linked_server

WITH MdxQuery (field1,field2,field3) AS (
    SELECT field1, field2, field3
    FROM @tempTable
)
SELECT * FROM MdxQuery

It throws this error before reaching the WITH MdxQuery part:

The requested operation could not be performed because OLE DB provider "MSOLAP" for linked server "my_linked_server" does not support the required transaction interface.

Is it any missing configuration? Is there any other approache i can use?
Thanks

If you're familiar with SSIS it can be done this way by saving the query in a file and then using it as a source query.  You'll start by creating an OLE DB connection manager that uses the Microsoft OLE DB Provider for Analysis 13.0 provider.  Set the initial catalog to your cube. Then add an Execute SQL Task with the OLE DB connection.  For the SQLSourceType, use file connection and create a connection manager for the file with the saved MDX query (or select this connection manager if you already created it).  From here, you can output the results to a table.  One option is to store the intermediate results in an SSIS object variable.  To do this you'll need to set the Result Set property to Full, and map the object variable to result name 0 on the result set pane of the Execute SQL Task.  You can then put another Execute SQL Task inside a Foreach Loop (ADO Enumerator type), map variables for each of the columns returned by the query, and then use these variables for an insert into an output table with the Execute SQL Task that's inside the Foreach Loop.


You have two right parenthesis more than left ones and a missing alias:

Select *
from wh_acct a                                 -- alias added
where a.rundate =
      (select max(a2.rundate)
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.ORGA = a.ORGA)
UNION
Select *
from wh_acct a                                 -- alias added
where a.rundate =
      (select max(a2.rundate)
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.PERS = a.PERS) ;

You could also write the query with OR instead of UNION:

Select *
From wh_acct a
Where a.rundate =
      (Select max(a2.rundate)
       From wh_acct a2
       Where a2.ORGA = a.ORGA)
   Or a.rundate =
      (Select max(a2.rundate)
       From wh_acct a2
       Where a2.PERS = a.PERS) ;

Or using window functions:

WITH cte AS
    ( SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE,
             MAX(rundate) OVER (PARTITION BY ORGA) AS max_rundate_orga,
             MAX(rundate) OVER (PARTITION BY PERS) AS max_rundate_pers
      FROM wh_acct
    )
SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE
FROM cte
WHERE rundate = max_rundate_orga
   OR rundate = max_rundate_pers ;

If, as your sample shows, all rows have NULL in orga where there is a value in pers and vice versa, you can change the UNION to UNION ALL to improve efficiency as there would be no need to check the subresults for duplicates (which is what UNION does). The window-cte version could also be rewritten as a union (ALL) of 2 subqueries.

Indexes on (orga, run_date) and (pers, run_date) would help, too.


Access.adp (2010) and SQL Server 2014 We are using Access .adp (Access 2010) frontend in combination with SQL Server 2014 backend. At the moment we have a big performance problem with the SQL function permissions(). We have ca. 400 Users on our application. The logon takes 50+ Seconds for an normal user. Sysadmins logon takes 3 seconds. Do you have a solution for this problem?

Microsoft seem to have a solution for this problem:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use fn_my_permissions and Has_Perms_By_Name instead. Continued use of the PERMISSIONS function may result in slower performance.


error: must be owner of relation USER_ACCOUNT_ID_seq I have granted priviledges to a user for testing in PostGreSql:

GRANT USAGE ON SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL TABLES IN SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL SEQUENCES IN SCHEMA "MySchema" TO "Tester";

Yet, when I run the following statement;

TRUNCATE TABLE "MySchema"."USER_ACCOUNT" RESTART IDENTITY;

I get the following error message:

[error: must be owner of relation USER_ACCOUNT_ID_seq]

What else do I need to grant to my Tester user, and how? Thanks!

From http://www.postgresql.org/docs/9.5/static/sql-altersequence.html

  You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the sequence's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.)

Restarting the sequence on table truncation is essentially the same as altering the sequence in a separate statement

TRUNCATE TABLE "MySchema"."USER_ACCOUNT";
ALTER SEQUENCE my_seq RESTART;

So, the above restriction applies to your original TRUNCATE with RESTART statement. You must be the owner of that sequence or be a super user.

You can GRANT the sequence owner's role to the role that's going to execute your statement by doing

GRANT roleA TO roleB;

assuming that roleA owns the sequence you are trying to restart.

Once you are done with all the TRUNCATE/ALTER SEQUENCE statements you can REVOKE the sequence's owning role from roleB

REVOKE roleA FROM roleB;


How do you delete an aliased user I am trying to delete an aliased user from a sql server 2000 database that was restored to sql server 2008 r2. Originally, I was attempting to convert the aliased user to a regular user, but it does not appear that I can do that, so now I am attempting to delete the user without deleting the login.

You can use sp_dropalias do drop the legacy SQL Server 2000 user alias in SQL Server 2008 R2:

 EXEC sp_dropalias N'<name-of-aliased-login-here>';

This stored procedure was removed in SQL Server 2012 later versions.


How can I execute multiple procedures continuously? Here is my code:
$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$category = $sth->fetchAll(PDO::FETCH_ASSOC);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$tags = $sth->fetchAll(PDO::FETCH_ASSOC);

It throws this error:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in C:\xampp\htdocs\myweb\others\user.php:71 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(71): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 71

Also I've used closeCursor() right after fetchAll(), based on this solution. But sadly it throws a new  error:

Warning: Packets out of order. Expected 1 received 9. Packet size=7 in C:\xampp\htdocs\myweb\others\user.php on line 72
Warning: PDO::prepare(): MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php on line 72
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php:72 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(72): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 72

Any idea how can I fix the problem?

Noted1: Each of queries above work separately. I mean, when I call a single procedure, it works as well.
Noted2: Each procedure returns a result set. I mean there is a SELECT statement in those procedures, which may return multiple rows.

The quick and dirty answer here would be to do the following

prepare each query once
prepare each query in separate statement handles
call your execute and save result sets in separate arrays

LIke this

$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$topcat_sth = $this->dbh->prepare($query);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$toptag_sth = $this->dbh->prepare($query);

$topcat_sth->execute([$user_id]);
$topcat_rs = $topcat_sth->fetchAll(PDO::FETCH_ASSOC);

$toptag_sth->execute([$user_id]);
$toptag_rs = $toptag_sth->fetchAll(PDO::FETCH_ASSOC);

...


Prostrct create causes windows batch file to exit I have a daily job to restore a database from last night's back up before doing other things to it and moving on. I'm trying to automate this with a script, but when I get to the following command the batch file exits - it won't run anything after this command:

prostrct create repldb rdb10.st -blocksize 8192 >> "%logfilename%" 2>&1

So if I put another command on the very next line such as echo boo! then nothing appears.

Can anyone advise on how I can get my batch to continue after prostrct create?

prostrct is a batch file, so calling it from a batch file will terminate the current batch file.

To get around that you need to call it with a "call" command, like so:

call prostrct -list dbname

or whatever command you want to use.


Why "Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable This title is the question. I am curious to know the answer. Someone told

select into is minimally logged in Simple recovery model database ... I didn't get into it at all.

Excerpt from Microsoft:

The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT… INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement

Seeking help

Thanks

A couple of ideas/theories:

SELECT INTO... lets the RDBMS determine sort order based on order of your original table.  If you insert into an existing table, there may be a sort needed to match a clustered or nonclustered index(es).

No Indexes - when you SELECT INTO... the RDBMS knows for certain there are no pre-existing indexes to update.

No Contention - since the table you are inserting into does not exist, SQL Server doesn't need to worry about row-level locking or contention handling.  Nothing else can reference the table you create since it doesn't exist.

All that being said, there are other ways to insert into a table very quickly.

Make sure your clustered index keys match when possible.  This means there is no on-the-fly sorting
Disable all non-clustered indexes.  Self-explanatory.
Set recovery mode to simple and trace flag 610 to ON.  Use the TABLOCK hint on your target table and NOLOCK hint on your source table.

For example, assume tablea and tableb have the same clustered index:

INSERT INTO TableB WITH (TABLOCK)
SELECT <Columns>
FROM TableA WITH (NOLOCK)

In my experience this is faster than using SELECT INTO... and then creating the clustered index afterwards.  Please note this can also work on a table that already has data in it which is a much more useful scenario.

EDIT:

Here's a fantastically detailed whitepaper from MS for data load performance in Sql Server 2008.


Oracle 11.2 Configuring authentication Can the following be setup in Oracle11.2 for password authentication?

A new, non temp password should be atleast 24 hours old before it can be changed. System admin and authorized help desk personnel may change passwords of any age.
Users will change temporary passwords including system administrator issued passwords at the first login.

I don't see these conditions in oracle documentation.

Thanks in advance

I am using the security documentation to answer your questions:

No, a minimum password life cannot be specified. Only a PASSWORD_LIFE_TIME (maximum life time) can be specified. Depending on what you want to accomplish, PASSWORD_LOCK_TIME might be what you're looking for. (Source)
Yes, this can be configured. You can expire a password, the user will then be prompted that he needs to change his password on first login. Look into the PASSWORD EXPIRE mechanism. (Source)


Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag We have an OrderLines table with columns:

Quantity int not null
QtyCancelled int not null
QtyBackorder int not null
QtyPicking int not null
QtyPacking int not null
QtyShiped int not null

with computed and persisted column FulfillmentStatusId as:

dbo.clr_GetFulfillmentStatusByLineQuantities(Quantity, QtyCancelled, QtyBackorder, QtyPicking, QtyPacking, QtyShiped)

"clr_GetFulfillmentStatusByLineQuantities", of course, being mapped through a scalar function to an ASSEMBLY in the database with the associated method (C#) marked with:

[SqlFunction(DataAccess = None, IsDeterministic = true, IsPrecise = true, SystemDataAccess = None)]

With reference to this question, I've run DBCC CHECKTABLE against this table multiple times and it still remains as has_unchecked_assembly_data=1. This was true when the column was first added to the table, but I believe the DBCC CHECKTABLE command succeeded prior to the migration to SQL 2016.

While the table has been in use for a long time with has_unchecked_assembly_data=1, there seems to be no impact on the table's functionality. I am concerned about ALTER ASSEMBLY throwing an exception when not using WITH UNCHECKED DATA, then throwing another exception when I did use that option, but appearing to update the assembly anyway "according to MVID".

Sorry if I ramble, I don't ask too many questions and I mostly lurk.

Thank in advance, however!

Update (20180917T17:34-05:00):
Thanks to Sean for suggesting an initial direction. The result of DBCC CHECKTABLE ('OrderLines') WITH EXTENDED_LOGICAL_CHECKS was two lines similar to:

Msg 2537, Level 16, State 106, Line 1
Table error: object ID 1486732449, index ID 1, partition ID 72057594403946496, alloc unit ID 72057594422755328 (type In-row data), page (1:1430554), row 5. The record check (valid computed column) failed. The values are 38 and 0.

So I executed a REBUILD on the primary key of the table (which is index ID 1 indicated above), which succeeded. However, rerunning DBCC CHECKTABLE('OrderLines') WITH EXTENDED_LOGICAL_CHECKS again returned similar errors, only this time with different partition IDs and allocation unit IDs (as one might expect).

The extreme solution might be to drop, recreate, and repopulate the table with identity insert, "nuke the site from orbit" so to speak, but for obvious reasons, that's extreme.

What is the correct solution to "there appears to be inconsistent computed column data in the table's clustered index"?

OP here, answering my own (and first) question!

With help from Sean Gallardy, using EXTENDED_LOGICAL_CHECKS pointed me to inconsistencies with the persisted computed column's data. I first tried recreating the primary key (clustered index), but this didn't actually fix anything (an extreme and weird solution).

I also tried using DBCC CHECKTABLE ('{table}', REPAIR_ALLOW_DATA_LOSS), but the same errors were returned again. I don't believe anything was repaired.

My first solution was to drop and recreate the persisted computed column. This worked.

But, thinking that was somewhat "extreme" as well (considering the table has 10M+ rows and it takes forever to do this because there is also an index against this column), I was curious about a more direct solution.

Since there were only two rows reported by DBCC CHECKTABLE with their page and row references I looked into DBCC PAGE. Using this command with precision got me the actual row data/row identity and when I executed the CLR method that drives the computed column, there was indeed an inconsistency.

Basic actual and inconsistent table data:

OrderLineId  QtyOrd  QtyCancl  QtyBack  QtyPick  QtyPack  QtyShip  FulfillId
    1234567       4        12        0        0        0        0          0
    4567890       4        12        0        0        0        0          0

FulfillId in this case should be 1 (representing cancelled), but these two table rows indicate 0 (open/unfulfilled) for both rows.

I ran the actual row data through the CLR function using a SELECT statement:

select
  ol.OrderLineId,
  ol.QtyOrd, ol.QtyCancl, ol.QtyBack, ol.QtyPick, ol.QtyPack, ol.QtyShip,
  ol.FulfillId [CurrentFulfillId],
  CLR(ol.QtyOrd, ol.QtyCancl, ol.QtyBack, ol.QtyPick, ol.QtyPack, ol.QtyShip) [ComputedFulfillId],
from
  OrderLines [ol]
where
  ol.OrderLineId in (1234567, 4567890)

This returned essentially the exact same information, with ComputedFulfillId also resolving to 0 (open). So I retested the CLR code directly in a .Net console application, and given the explicit values that should have been passed in the SQL call, the result was the expected value 1 (cancelled).

I revised the SELECT statement to include a call to the CLR method using explicit constant values instead of the values from the dubious rows:

SELECT ..., CLR(4, 12, 0, 0, 0, 0) [ForcedFulfillId] FROM OrderLines [ol] ...

And the ForcedFulfillId column contained the correct value of 1.

Using DBCC PAGE to find the exact row identities and updating the rows in question forced the computed column data to be recomputed. One final DBCC CHECKTABLE returned no errors and everything was good again.

So rather than drop and recreate the whole computed column, using DBCC PAGE with information from DBCC CHECKTABLE({table}) WITH EXTENDED_LOGICAL_CHECKS allowed me to fix the original "has_unchecked_assembly_data=1" issue causing ALTER ASSEMBLY errors.

WHEW

Sorry for the long-winded explanation - wanted to make sure I documented my travels for the next poor soul.


Object-oriented programming CRUD design pattern At first I have posted this question at https://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can not answered by "yes" or "no" or with a single answer.

I don't know if here it's the better place for this question, but here we go. I have shared a common question for DB designers - imagine the following SELECT:

 SELECT
     P.id,
     P.legalName,
     P.type,
     L.tradeName,
     L.foundation,
     L.EIN,
     N.SSN,
     N.gender,
     N.birthDay,
     A.zipCode,
     A.address,
     A.number,
     A.district,
     A.city,
     A.state
 FROM
     Person as P

 LEFT JOIN
     LegalEntity AS L ON L.person = P.id AND P.type = 'L'

 LEFT JOIN
     NaturalPerson as N ON N.person = P.id AND P.type = 'N'

 LEFT JOIN
     Address as A ON A.person = P.id AND E.legalAddress = 1

The above SELECT has a "maintaining" level relatively high by the following factors:

CREATE - When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.
READ - To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)
UPDATE - When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.
DELETE - WITHOUT aditional complexities.

Imagining all this complexity is also necessary. Now think that be need to run in 3 differente SGDBs (MySQL, Oracle and Postgres) - the quoted example are created in MySQL.

What are the best practices that we can adopt in this scenario?
Maintenance of data with the triggers assist for the 3 SGDS?
Create a CRUD with appropriate treatments implemented via application?

Thank you in advance for your time.

CREATE - When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.

Do all inserts for each "person" in a single transaction based on updatable views, not on base tables. Every dbms exposes a function that will return the last inserted ID number. (For PostgreSQL, this SO answer.)

  READ - To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)

In the query you posted, you don't do that at all. Instead, you retrieve all the data for every type.

Instead, use two views (see above), one for legal persons and one for natural persons. Resist the urge to join every possible table in the views.

  UPDATE - When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An
  INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.

In the real world, you don't "upgrade" a natural person to be a corporation. (A person who "is" a corporation is legally two different things. That's why there are corporations.) In a database, if you run into such a situation, you have to take one of three paths.

All the original data is wrong, because you entered data for a person who is not really a person. Delete the bad data, and enter new data for the corporation.
All the original data is right; that person is actually a natural person. Reject all the updates that would make that natural person a corporation.
All the original data is right, and that person is both a natural person and a corporation. Redesign your schema to accommodate this possibility.


How can i update table when counter reach a specific value? (thread safe) I increase a field when somebody click the image. Also i want to update (decrease) customer budget when counter value reach to 1k. It is like that:

UPDATE click SET count = count + 1 WHERE customer_id = 1;
UPDATE budget JOIN click ON budget.customer_id = click.customer_id
 SET budget.total_budget = budget.total_budget - 10, click.count = 0
 WHERE budget.customer_id = 1 AND click.count = 1000;

But it should be thread safe, i don't want to decrease budget 2 times when 2 clicks happen same time. My question is how can do this with mysql?

One update is atomic operation, so when running the second update twice, the second thread has to wait until the first finishes (when using innodb and transactions, entire transaction is atomic) and it should not update anything, because the click.count will be set to 0 before by the other thread.

But I see a potential problem with your code - if other thread updates count to 1001 too fast, your update won't do anything and will never repair it. You should probably do

SET click.count = click.count - 1000 ... WHERE click.count >= 1000

so you can catch such cases. (They are maybe not very probable, but if you have lot of hits, then they can sure happen if you do not prevent them by some other means)


passing --single-transaction in mysqldump command line tool I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup.

I have however, noticed that if I backup using command line mysqldump tool, the backup process speeds up noticeably. I am wondering if is possible to mention --single-transaction in command line mysqldump utility?

Sure enough, --single-transaction is supported from the MySQL command line.

mysqldump --single-transaction databasename

Will use all of the default options from --opt (except for --lock-tables, which is overridden by --single-transaction) as well as --single-transaction.

That really should be all there is to it.

I recommend briefly reading through all of the options of mysqldump - you may find that some of them, like --routines, are important for your database.


User defined function can only have select statements One of the main differences between UDF and SP is that UDF can only have select statements inside it and not insert/update/delete statements. Can someone please explain the reason behind this? The below function:

create function test(..)
...
BEGIN
insert into EMPLOYEE('22',12000,'john');
return 0;
END

is not valid. But why is this so?

This is not about function (UDF) vs procedure. It is the context where you use them.

You can have DML operations in a function:

SQL> create table t1 (c1 number);

Table created.

SQL> create or replace function f1 return number as
  2  begin
  3    insert into t1 values (1);
  4    commit;
  5    return 0;
  6  end;
  7  /

Function created.

SQL> declare
  2    i number;
  3  begin
  4    i := f1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

        C1
----------
         1

But if you use your function as and UDF in a query:

SQL> select f1 from dual;
select f1 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "BP.F1", line 3

You can still work around this with an autonomous transaction:

SQL> create or replace function f2 return number as
  2    pragma autonomous_transaction;
  3  begin
  4    insert into t1 values (1);
  5    commit;
  6    return 0;
  7  end;
  8  /

Function created.

SQL> select f2 from dual;

        F2
----------
         0

SQL> select * from t1;

        C1
----------
         1
         1

But should you do this? I do not think so.


Where can I find a good tutorial or book for various MySQL performance guidelines? Can anyone refer me a good book or a small tutorial for various use cases for database design on MySQL for high load test/high availability scenarios.

It should include the following:

What are the various scenarios/solutions for high availability?
What are the various scenarios for high load and how to cater them
based on reliability, response time, throughput etc.

I am particularly interested if someone knows of a practical experience book for both of these cases.

I read these:

MySQL High Availability
High Performance MySQL

They are good enough.


Problems restoring backup from base backup + WAL with WAL-E I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files.

On the master I have

wal_level = hot_standby
archive_command = 'wal-e --s3-prefix=s3://... wal-push %p'

and I've done a full backup with:

wal-e --s3-prefix s3://... backup-push <pg data>

On the backup node I have done:

PGDATA=.. initdb
wal-e backup-fetch $PGDATA LATEST

which works and I'm able to connect and select data. Nice.

So to get it to consume the latest WAL archives I added a $PGDATA/recovery.conf:

restore_command = 'wal-e --s3-prefix ... wal-fetch "%f" "%p"'
standby_mode = 'off'

When start the server I expected it to go ahead and look for WAL segments to consume before starting in non-recovery mode, but I'm getting this:

LOG:  database system was interrupted; last known up at 2015-03-21 12:20:50 CET
wal_e.operator.backup INFO     MSG: begin wal restore
        STRUCTURED: time=2015-03-21T12:16:38.820461-00 pid=23630 action=wal-fetch key=s3://.../wal_005/00000005.history.lzo prefix=...
/ seg=00000005.history state=begin
lzop: <stdin>: not a lzop file
wal_e.blobstore.s3.s3_util WARNING  MSG: could no longer locate object while performing wal restore
        DETAIL: The absolute URI that could not be located is s3://.../wal_005/00000005.history.lzo.
        HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration.
        STRUCTURED: time=2015-03-21T12:16:39.829223-00 pid=23630
wal_e.operator.backup INFO     MSG: complete wal restore
        STRUCTURED: time=2015-03-21T12:16:39.830287-00 pid=23630 action=wal-fetch key=s3://.../wal_005/00000005.history.lzo prefix=.../ seg=00000005.history state=complete
LOG:  starting archive recovery
wal_e.operator.backup INFO     MSG: begin wal restore
        STRUCTURED: time=2015-03-21T12:16:39.900355-00 pid=23638 action=wal-fetch key=s3://.../wal_005/00000005000000050000009D.lzo prefix=.../ seg=00000005000000050000009D state=begin
lzop: <stdin>: not a lzop file
wal_e.blobstore.s3.s3_util WARNING  MSG: could no longer locate object while performing wal restore
        DETAIL: The absolute URI that could not be located is s3://.../wal_005/00000005000000050000009D.lzo.
        HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration.
        STRUCTURED: time=2015-03-21T12:16:40.896215-00 pid=23638
wal_e.operator.backup INFO     MSG: complete wal restore
        STRUCTURED: time=2015-03-21T12:16:40.899320-00 pid=23638 action=wal-fetch key=s3://.../wal_005/00000005000000050000009D.lzo prefix=.../ seg=00000005000000050000009D state=complete
WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
LOG:  consistent recovery state reached at 5/9D009DA8
LOG:  record with zero length at 5/9D009DA8
LOG:  redo is not required

So the questions are;

is my approach fundamentally wrong? Is there an alternative way to get this to work?
PostgreSQL is claiming I'm having wal_level=minimal, how can I check the level of the produced WAL files? On the main server I get this when checking it's actual settings:

select * from pg_settings where name='wal_level';
-[ RECORD 1 ]------------------------------------------------
name       | wal_level
setting    | hot_standby
unit       |
category   | Write-Ahead Log / Settings
short_desc | Set the level of information written to the WAL.
extra_desc |
context    | postmaster
vartype    | enum
source     | configuration file
min_val    |
max_val    |
enumvals   | {minimal,archive,hot_standby}
boot_val   | minimal
reset_val  | hot_standby
sourcefile | /.../postgresql.conf
sourceline | 155

EDIT: adding details on archive_command on master

wal-e backup push

will NOT push any wal files to s3.

So this backup will be mostly unrecoverable.

Use

archive_command = '... wal-e wal-push %p'

on master to make this backup usable.


Deadlock detected on CREATE TABLE in PostgreSQL I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C.

Scripts

CREATE TABLE "TableA"
(
  "Id" uuid NOT NULL,
  "TableCId" uuid NOT NULL,
  CONSTRAINT "PK_TableA_Id" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_TableA_TableC" FOREIGN KEY ("TableCId")
      REFERENCES "TableC" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE "TableB"
(
  "Id" uuid NOT NULL,
  "TableCId" uuid NOT NULL,
  CONSTRAINT "PK_TableB_Id" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_TableB_TableC" FOREIGN KEY ("TableCId")
      REFERENCES "TableC" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

This table exists in moment of above scripts execution

CREATE TABLE "TableC"
(
  "Id" uuid NOT NULL,
  CONSTRAINT "PK_TableC_Id" PRIMARY KEY ("Id"),
)
WITH (
  OIDS=FALSE
);

So when I take a look at the logs I get the following error, and relation 87066 is the TableC. Note that TableA & TableB are created in separate transaction using the READ COMMITTED isolation.

  Process 464 waits for AccessExclusiveLock on relation 87066 of
  database 86965; blocked by process 1180. Process 1180 waits for
  AccessExclusiveLock on relation 87066 of database 86965; blocked by
  process 464.

When I take a look at PostgreSQL documentation it says that one should execute create tables in same order in order to avoid the deadlocks and they are executed in the same order so no deadlock should occur.

Is there any reason why this ends with deadlock? I think that process 464  should wait for 1180 if 464 was the first one to execute ?

Edit

One other note is that I'm running one insert in the same transaction before create table script.

INSERT INTO public."TableD"("Id", "TableCId", ....)
VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)

PostgreSQL 9.3

Let's assume that the target table of the INSERT ("TableD") has a column with a foreign key to "TableC". An INSERT to this table must acquire a shared lock on "TableC" and will keep that lock until the transaction ends.

Say two concurrent transactions, T1 and T2, start with such an INSERT. Each takes a shared lock on "TableC".

Later on, in order to do a CREATE TABLE involving a foreign key to "TableC", T1 must acquire an access exclusive lock on "TableC".

Since T2 has already a shared lock on "TableC", T1 will be put to wait until T2 commits or rollbacks.

At this point, if T2 tries to create a table also involving a foreign key to "TableC" (which again implies acquiring an access exclusive lock on "TableC"), it must wait for T1 to commit or rollback, because T1 holds a shared lock on "TableC".

So now T1 is waiting for T2 to finish and T2 is waiting for T1 to finish: there's the deadlock.

To avoid this situation, an explicit and exclusive lock may be taken at the beginning of the transaction, or at least at the beginning of the sequence of instructions that can't be run safely in parallel by concurrent transactions.


syspolicy_purge_history job failed at step 3 In one of my SQL Server 2008 sp1 standalone instances, syspolicy_purge_history job is fails at step 3 Erase Phantom System Health Records.

Below is the command -

(Get-Item SQLSERVER:\SQLPolicy\servername\DEFAULT).EraseSystemHealthPhantomRecords()

Error :

  Executed as user: domain\xxxxx. The process could not be created for step 3 of job 0x3183E8EEDE9C3A4DA0F20ABD41B573DC (reason: %1 is not a valid Win32 application). The step

Service account is same for SQL Server and Agent and it is admin on the server. I'm aware that clustered instances usually fail because of virtual name mismatch but I tried searching Google but I don't see correct solution to this problem.

I ran into this error a while back and found this link, hope it resolves your issue. http://www.mssqltips.com/sqlservertip/2041/why-syspolicypurgehistory-job-fails-in-sql-server-2008-failover-cluster-instance/


Aggregating results of two database views in one database view I have a view with name of UserInfo like this:

And another view with name of UserRoleInfo like this:

I want to aggregate all the records of the two views for one user name like H.F into one view.

I tried to join the two views on user name but does not work!

How can I have all records of these two views in one view with the same columns as in picture 3?

You want to use the UNION ALL operator, rather than trying to JOIN on Username.

A UNION ALL returns a combination of all results from Query A and Query B.

Alternatively, if you only wanted DISTINCT records from the set, you could omit the ALL keyword and just do a UNION.

SELECT
    UserID,
    Username,
    OperationFaName,
    OperationEnName,
    ComponentName,
    AccessString,
    ApplicationName,
    ApplicationID
FROM UserInfo

UNION ALL

SELECT
    UserID,
    Username,
    OperationFaName,
    OperationEnName,
    ComponentName,
    AccessString,
    ApplicationName,
    ApplicationID
FROM UserRoleInfo


How to test permissions on an empty database? It's usual in an organization I work in that I need to request a database creation to the DBA group (I am a developer). As such, they will also create a user for me to use and a database dedicated to my application.

When they do so, they ask which types of permissions and I answer.

When the work is done, they request that I test that everything is fine as to give my approval on the configuration. However, at this point, the database is completely empty. I can easily test for connectivity and access permissions, but how should I test my own read/write/execute permissions into a specific database, if it´s just been created and it is completely empty, using a non-admin user?

If the database wasn't empty I would try selecting data from a table and updating it.

The question could use some clarification.  SQL Server security is the same whether or not the table is empty.  You have server roles, database roles, and explicit permissions.  You are asking 'how can I test execute permissions on a database that has no objects to execute'.

Well the answer is you have nothing to test if the DB is empty, that should be obvious. create objects then test it, and then remove the objects.  Test it on a dev box.  If you're worried that you'll forget to remove all objects, backup the DB, then do your tests, and restore it.  If you like, restore the DB as a different name, do all of your testing on there, then drop the newly created test DB.  I'm not sure what you're asking.

Final answer remains that you cannot test permissions on objects if you have no objects to test.

I will provide a script or two at the end to help you with these steps.

Step 1: Look for users with SA rights.

What you would look for first would be SA equivelent users.  Any SA user will have full access to every single object, and if they don't, they can grant it to themselves.

Step 2: Look for users with the DB_Owner role
Users granted the DB_Owner role will be able to perform any task in the database.  You could DENY them access to certain features but they can just GRANT themselves access to it again.

Step 3: Audit what users are in the database
You will want to look at the 'security' settings of each user in the database.  This could get very in depth as you can look for indvl permissions on each object and this would be required if you were taking over a database.  In your case, there are no objects and you get to create them yourself, thus we don't have any object level permissions to go through.  You will want to check what roles each user is in.  DataReader will be able to read anything in the DB unless explicitly blocked.  DataWriter will be able to write anything.  Execute role lets them execute sprocs.

Step 4: Continue to monitor after objects are created
Now comes the hard part.  What happens when you go live and a sysadmin grants themselves rights to the machine/db?  What if you have the option to have start up stored procs execute?  If you're on 2008R2 and below, the NTAUTHORITY user can always be used as a backdoor.  This is where real security auditing and security policies are needed but luckily it's out of scope of this question.

Check out:
http://www.mssqltips.com/sqlservertip/1881/sql-server-security-audit-report/

His script will cover almost everything you're looking for.

Also this one is very good too, but test it out first:
http://gallery.technet.microsoft.com/scriptcenter/SQL-SERVER-SECURITY-AUDIT-eb5b0fd5/view/Discussions

If you want to do some research on your own, search for "SQL Server Auditing" or "Object Level Auditing SQL Server".

Does this info help?  Do you have any follow up questions?

Follow up edit to address permissions under the current user context:

In that case you'll want to use this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

USE DBName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

The first one will tell you your permissions on the server, the 2nd one will tell you your permissions on the database.

Click here for more easy to read info.


SQL Server dbo permissions for specified databases Can I assign SQL Server dbo permissions to a user, so it applies only for specified databases on a server?  Currently using SQL Server 2005.

The code for SQL would be:

USE [DBName] --Insert DBName Here
GO

CREATE USER [SQLServerorWindowsUser]
  FOR LOGIN [SQLServerorWindowsUser] --add the user to the DB
GO

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO

If you already have the user in the database you only need to execute:

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO


Query to get yesterdays data only for a specific time interval Here is my where condition which I use for daily data but I've got to change the date
everyday.

WHERE X.stage = 'Approval'
AND
X.T_ExitTime between '2015-08-06 06:00:00:000' and  '2015-08-06 17:00:00:000'
ORDER BY x.T_ExitTime asc

How can we make this to use the timeframe 0600-1700 for yesterday or for the same day without having to change it manually everyday and so that we can make this a Job and automate it

I'm using SQL Server 2008

DECLARE @yesterday SMALLDATETIME;
SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE())));

SELECT ...
WHERE X.stage = 'Approval'
AND X.T_ExitTime >= DATEADD(HOUR, 6, @yesterday)
AND X.T_ExitTime < DATEADD(HOUR, 17, @yesterday)
ORDER BY x.T_ExitTime;

Don't think you want BETWEEN for datetime ranges, unless you really want to include 5:00 PM and not 5:00:00.0000001 PM.

What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries


How do I ALTER all columns of one type to another type? Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.

In psql, you'd run something like this which will output a bunch of commands.

SELECT format(
  'ALTER TABLE %I.%I.%I ALTER COLUMN %I SET DATA TYPE int;',
  table_catalog,
  table_schema,
  table_name,
  column_name
)
FROM information_schema.columns
WHERE data_type = 'bigint'
  AND table_schema NOT LIKE 'pg_%'
  AND lower(table_schema) <> 'information_schema'
  AND is_updatable = 'YES';

You can spot check them and if you're good to go execute them with \gexec.


Installing service packs with SQL Server services stopped I always installed Service Packs on the servers with running SQL Server Services. This time I am faced with the situation where I have to patch multi-instance SQL Server that has its services stopped for each instance. There is no posiblity of starting the services without breaking one of the applications. It is not part of a cluster, just a regular stand-alone server.

My question: Will stopped services affect instalation of SP?

You can definitely apply Service Packs or Cumulative Updates while the services are stopped. However, if there are steps that may require a reboot, you will be responsible for that.


MySQL master slave replication-troubleshooting Why does the Procedure/function created on the master replicates to slave even after enabling table filters using "replicate-do-table". This create a lot of problems in the production setup as it rewrites the function already in the slave with the one modified in master.
I need to have two different functions in master and slave, only the table data needs to be replicated. Solution please

How to repeat:
Create a table in master

Replicate the table to slave with replicate-do-table option

create a function on master

it rewrites the function which was already there in slave with the same name

Because in MySQL all fuynctions and procedures are stored in the mysql system database rather than in the database where they were defined. So unless you exclude the mysql database from replication, the functions and procedures you define will replicate downstream.


Filter on a window function without writing an outer SELECT statement Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. Here is the sqlfiddle.

SELECT MERCHANTID, WAREHOUSEID, PRODUCTCODE
FROM (
  SELECT 0
  , WMP.MERCHANTID
  , WMP.WAREHOUSEID
  , WMP.PRODUCTCODE
  , RANK() OVER (PARTITION BY ML.MASTERMERCHANTID, WMP.PRODUCTCODE ORDER BY ML.PREFERENCEORDER ASC NULLS LAST) MERCH_RANK
  , RANK() OVER (PARTITION BY WMP.MERCHANTID, WMP.PRODUCTCODE ORDER BY WM.PREFERENCEORDER ASC NULLS LAST) WARE_RANK
  FROM MW_WAREHOUSEMERCHANTPRODUCT WMP
    LEFT OUTER JOIN MW_WAREHOUSEMERCHANT WM ON 0=0
                AND WMP.MERCHANTID  = WM.MERCHANTID
                AND WMP.WAREHOUSEID = WM.WAREHOUSEID
    LEFT OUTER JOIN MW_MERCHANTLINK ML ON 0=0
                AND WMP.MERCHANTID = ML.LINKEDMERCHANTID
    LEFT OUTER JOIN MW_WAREHOUSEMERCHANTPRODUCT MASTER ON 0=0
                AND ML.MASTERMERCHANTID = MASTER.MERCHANTID
                AND WMP.PRODUCTCODE     = MASTER.PRODUCTCODE
  WHERE 0=0
    AND WMP.STOCKLEVEL > 0
    AND NVL(MASTER.STOCKLEVEL, 0) <= 0
)
WHERE 0=0
  AND MERCH_RANK = 1
  AND WARE_RANK  = 1
;

Yes, most analytic functions can be rewritten with semi-join. In your case it would probably not be very efficient:

SELECT <select_list>
  FROM <table_list>
 WHERE <where_conditions>
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (ML.MASTERMERCHANTID, WMP.PRODUCTCODE)
                  = (ml_in.MASTERMERCHANTID,wmp_in.PRODUCTCODE)
          )
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (WMP.MERCHANTID, WMP.PRODUCTCODE)
                  = (wmp_in.MERCHANTID, wmp_in.PRODUCTCODE)
          )


How to prevent SQL Log from being too full I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even though there are a lot of transactions, the actual data is pretty small.

Now the problem I have is that when the log reaches his maximum value, which is pretty big to being with, the database will not accept anymore transactions. I can't really put unrestricted growth since I do have a physical limit to respect.

My initial understanding was that once the log reaches full size , it will simply take the oldest transaction and delete them to make space for the new ones, which would be ok in our case. However, this does not seem to be the case here. I am no expert on SQL so I am at a lost here.

I understand this log is used for backups, but so far we don't have plans to implement backup because we are still on testing phases. Also we won't be the one performing it, so we have to wait for now.

Thanks

A couple of things you can do.

Your database is probably in full recovery mode. This is why the log file does not get re-used. Now if you DON'T need transaction log backups you can set the recovery mode to simple. Your log file will now re-use itself in simple mode. If you DO need transactional log backup i.e. point in time recovery then you will need to actually DO transaction log backups to re-use the log file.

Please be aware that this setting has a MAJOR impact on how you can perform disaster recovery and you need to make sure that the setting matches your disaster recovery needs. If you database is so busy then transactional backups are probably better than simple recovery mode.

Without transaction log backups you will NOT be able to do point in time recovery. Thus you will lose A LOT OF DATA in the event of a disaster.

Go read about transaction logs and recovery modes here: http://msdn.microsoft.com/en-us/library/ms189275.aspx

Now there is something else you need to keep in mind. Virtual Log File fragmentation. It is going to be something very important to understand and master. Kimberly L Tripp has a very very  usefull blog entry about this. You can read all about this very helpful topic here: http://www.sqlskills.com/blogs/kimberly/post/transaction-log-vlfs-too-many-or-too-few.aspx

Keep in mind 50-100 transactions a second is not a lot. Also a BIG transaction log file is not always a bad one. There is more to log files than just logging transactions. Go read about it. It is a topic worth mastering.


Importing different file types to SQL Server I'm looking for direction here.  I need to automate a process that will import several different file types into SQL Server.  They are all text based files (no excel) but the internal format of the files is different.  Some are comma delimited, others are tab delimited.  They all contain the same base information (customer name, address, phone, etc.) but some files contain additional data.  I want to only import the common data from each file.  So, some data will be skipped over in one file but in another file all the data needs to be imported.

I was going to use an SSIS ForEach loop but I'm having trouble figuring out how to handle multiple file formats.  I get stuck on the connection string for setting up how to import the files.  Has anyone come across this before?  If so, can you push me in the right direction?

How many is "several"?  If it's three or four, I'd start by writing a simple .NET script in SSIS which sorts out the files into separate directories.  Use several foreach loops, each working in its own directory, with the files in that directory having a consistent format.

If there are a lot of formats, it'd probably be faster to write a little application which parses files and writes a single clean file for subsequent BCP or SSIS import.


Index issue on upgrade from SQL Server 2000 to 2012 About 6 months ago, we upgraded  a large .NET project from SQL server 2000 to 2012. (i know it's 2 versions behind already, don't get me started!)

One of the stored procedures ran a query in the overnight batch process that took 55 mins since the upgrade, but jumped up to 3 hours last week. Prior to the upgrade however, the query took 1 minute. I added an index on the table as per the execution plan and it brought the run time down to 10 mins. I'm not a DBA, so I'm wondering if someone could explain why this is, as it doesn't make sense to me.

This was the query that caused the problems:

DELETE @ExcludedPersons
FROM   @ExcludedPersons
WHERE  personID NOT IN (SELECT personID
                        FROM   PersonStudy ps
                        WHERE  (ps.StudyTypeID = 1)
                        AND (ps.StudyClosedDate IS NULL))

There are 2 indexes on the PersonStudy table:

A clustered index on StudyID which is the primary key.
A non-clustered index on PersonID which is also unique.

These indexes were just carried forward from SQL 2000.  There are about 60,000 rows in the table.  There are about 500,000 rows in the @ExcludedPerson table variable (but only 1 column).

For the last few months, the sub-query in the where clause returned 1 row and the query was taking ablout 55 mins. Last week, the final person had a date added to StudyClosedDate so the subquery returned 0 rows and that was when the time jumped up to 3 hours.

The Index that execution plan suggested was to add in an index with the Keys of StudyTypeID and StudyClosedDate and include PersonID and it was this that brought processing time to 10 mins.

So, my question is this:

Why is there this much processing time on a subquery that returns 0 rows and why does the index make such a big difference?
And why the jump in processing time between the sub-query returning 1 row and 0 rows?  Is it do do with the different way indexes are handled in sql 2000 and 2012?

The unstated assumption in the question is that the subquery is executed first, then the outer DELETE is processed. This is not how things work. People write queries that express a logical requirement, then the SQL Server query optimizer tries to find an efficient physical implementation.
The optimizer's decisions are driven by cost estimates for the various possible physical options it explores.
Garbage In, Garbage Out
By using a table variable, the current arrangement deprives the optimizer of two important pieces of information: the number of rows in the table (cardinality); and the distribution of those values (statistics).
In most cases, the optimizer is unable to see the cardinality of a table variable, and guesses at one row. The physical execution strategy it chooses on the basis that there is one row in the table variable is very likely suboptimal for half a million rows.
Assuming one row, the optimizer may well decide that scanning the PersonStudy table looking for matches is a good enough strategy:

In practice, this plan results in the PersonStudy table being scanned 500,000 times at runtime (once per row in the table variable). That is potentially 60,000 * 500,000 = 30 billion rows. No wonder it takes a while.
Given incorrect or incomplete information about the data, the chances are pretty high that the optimizer will deliver a poor execution plan.
Advice

Use a temporary table (e.g. #ExcludedPersons) instead of a table variable. This will provide accurate cardinality information, and allow SQL Server to automatically create statistics.
Constrain the personID columns to be NOT NULL. This gives the optimizer useful information and will allow it to avoid a common problem with NOT IN.
Make the personID column in the temporary table (or table variable) the PRIMARY KEY. Again, this provides useful information to the optimizer (uniqueness, ordering, not null).
Provide a useful index on the PersonStudy table. The suggested index is a reasonable choice, but there may be better options. A good index provides the optimizer with a more efficient data access path.

Especially if you are unable to switch to using a temporary table, test the following (but still add the constraints and indexes/keys mentioned above):

Add an OPTION (RECOMPILE) hint to the query. This will allow the optimizer to see the cardinality of the table variable (but not statistical distribution) at runtime.
Or: Use an OPTION (HASH JOIN) hint. Hash join scales better than nested loops with a table scan. The hash join may well spill and reverse roles at runtime, but this should still be very significantly better than what you have right now.
Or: If your workload often uses table variables with a significant number of rows, test the impact of enabling trace flag 2453. This will expose cardinality as above, without the (typically small) overhead of a plan recompilation.


App Engine Standard connection to Cloud SQL Latency Randomly I have a pretty "basic" app that we designed that was originally on a local plesk server and we migrated to GAE/GSQL/GCS. app engine, mysql, cloud storage.

Here's some background info:

App is PHP based, and runs great on the local server. When we migrate to the cloud we notice this random yet extremely latency that happens. It's so bad that the app times out and gives a SPDY timeout error. We utilize cloudflare for SPDY assistance so we started there and they said it's the the server. Then we went to google. We've been going back and forth back and forth and I am looking for other avenues of help.

I am running an app on a F2 standard GAE instance and a G1-small CloudSQL instance (gen 2). All same region/zone. There is also a failover sql instance.

There is really no pattern to it but users on the app notice a bad timeout very frequently and it dies after 60 seconds. (which points to a PHP timeout right? We checked the code and it runs fine on the local server)

I dont have a whole lot of traffic on this app yet (maybe a few users a day) so i dont know if it's traffic load. Here's some basic stats for you:

https://imgur.com/a/U1tk5ak

Some Google Engineers said our app has trouble scaling (QPS never will get about 1)

https://imgur.com/a/XWh44bm

And asked if we are threading. We are not. We do not use memcache yet either.

I also see a ton of these:

https://imgur.com/a/eVSNqc3

Which looks like this bug: https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/126

But I am unsure if this is all related.

We've tried going through Google's tech support, they said we have "manual locks" but our dev team doesn't agree nor know what this really means. Again, the same framework of the app (session handling etc) code is used in many apps with a ton of users on it (non GAE, they're on compute on AWS) so this is our first venture to GAE.

We connect using standard MySQL connection parameters and use the same framework in a lot of applications and it runs fine. We use the required proxy to connect to CloudSQL.

The speed and constant lag shouldn't be there. We don't know what this issue could be. My questions are:

1) Do you see any issues here? All database logs are above and summaries

2) Can you help me understand what may be wrong here?

Thank you!

It's hard to tell where exactly the problem is because there isn't enough information. I'm not sure how your application implements connecting, so it's hard to judge if that's the problem or not. Additionally, it's impossible to separate network latency vs query latency without more information.

Here some advice that might potentially help:

Make sure you are using the /cloudsql/<INSTANCE_CONNECTION_NAME> unix socket to connect. You can follow the instructions here. This will likely provide the lowest latency.
Make sure you are opening/closing connections correctly. You should be opening a connection, performing your queries, then closing the connection. Look up how best to do this in PHP given your stack.
It looks like you are using a db-g1-small instance. This is a shared-core instance, and isn't recommended for production use (this is a particularly strange decision since you are also using a failover). Try upgrading to a machine size that is covered under the SLA.


Search 200M documents of text by keyword in text Data

~5M documents, would love to have ~223M, with the text of ~10-1000 words each. It must be utf8 and support any spoken language. All words in a text are unique and I can also order them or do whatever needed to have better performance. Full text is saved in another location. Data is almost read-only. For performance, I can generate data, add indexes and mark it as read-only.

Data structure:

{
  id: 1,
  reference_id: 1,
  text: 'text of unique. 10-1000 words each ',
}

Requirements

Results must come under 0.1s
Sample queries:

find text with words: x or y or z
find text with words: x and y and z
would be great to find text with part of the word: hous would find house, housing etc.

Questions

Is it possible to make it happen with MySQL/PostgreSQL?
What database solutions you would offer?
What config to look at?
Index type?
Query sample?
Server requirements for RAM/HDD/CPU?
Better more powerful machines or array of simpler?

Just think about your requirements and what you're asking and if an answer can even been given here. Wikipedia has 5.3 M documents. You're talking about a load on that scale. And you want to know whether or not you can query it in 0.1 sec? You're going to have provide the sample text. I'm all about making sample data, but how am I even going to generate sample data for this question/

What you want is enterprise Full-Text Search (FTS): PostgreSQL has that. Whether or not it will perform as well as you intend, I have no idea. I've used it for reasonably large projects, it's worked fantastically.

  would be great to find text with part of the word: hous would find house, housing etc.

It also supports :* which does what you want. so just use hous:*

It is indexable using a GIST index. All of that is in the docs.

{
  id: 1,
  reference_id: 1,
  text: 'text of unique. 10-1000 words each ',
}

So just

CREATE TABLE foo (
  id            int PRIMARY KEY,
  reference_id  int, -- no idea what this is
  document      text,
);
CREATE INDEX ON foo USING GIST (to_tsvector(document));


How do I open local files using heidiSQL? I'm using HeidiSQL to access my remote MySQL db.

Recently I was sent an .sql file.

Is there a way to open this file using HeidiSQL?
Does anyone know of a better way to graphically view and manipulate a local sql file?

I know that in windows Heidi associates with sql files but when double clicked nothing happens.

(This part lifted from my answer here...)

The first thing to note is that an .sql file is not a MySQL database. It is a script file containing SQL statements. You would run such a script against the database platform for which it was written (using the SQL dialect for a specific SQL database engine) to create the actual database.

From comments to other answer:

  I was wondering if there is a way on Heidi or another program to do this without setting up a server.

No, you need a server somewhere. If there was any stand-alone tool that could "graphically manipulate" an .sql file (containing DDL, I presume) then it would have to have its own MySQL server embedded in it.


SSIS Repeated Header In CSV File I am writing to a csv file in a Foreach loop. For each loop the code is writing the header so it appears repeatedly throughout the file.

How do I ensure the header only appears once?

I have tried using RowCount variable and adding an expression to the Flat File Connection Manager but no change.

Since the link that I created in the comments did not contain this particular method, I figure I would throw it in here.   Besides the approaches mentioned here I went ahead and tried this approach as another way that would take very little change to a package that is going through a ForEach Loop (since that is how I did it in mine).   What you would do, is before running the ForEach Loop (and if you are doing the ForEach through an ADO recordset then you would want to do it before that) you could have a file kept in some other location that is nothing more than the file with the headers you want for this file and you would create a task (either Script task or a File System Task) to copy that file that you saved off to the location of the file you are generating via your ForEach Loop.

After you have done that, be certain that within your flat file connection that you have the "Column names in the first data row" unchecked.

If you do that, it will copy the file with the headers and then all of the rows added within your foreach loop will contain data only and no headers.  It is an alternative if those others did not work for you.

Obviously it requires you to keep a file that exists only to be used for headers out on your server or some network drive but it is a solution that will get this done.


Select tables on column entry in schema (with many tables) I have a schema in a PostgreSQL-DB that includes approx. 3000 tables with each having one row/ entry (size, id, geom). I'd like to select all tables which have a size value lower than 20. How can I query over multiple tables without joining them?
Would it be better to merge all these tables into one and execute a simple SELECT query?

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             || ' LIMIT 1';
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;


Composite primary key from multiple tables / multiple foreign keys I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this...

A great answer was given to building a composite key from multiple foreign keys on this question but I want to do this from multiple tables. I am using Oracle 11g.

foreign key (Name, BoughtFrom, TimeBought)
  references the_other_table_name (Name, BoughtFrom, TimeBought)

Imagine that the "references" line actually includes multiple tables. I'm not even sure that the above syntax will work with Oracle.

I have one table "Student" and one table "Parent". It is a many-to-many relationship. My association table is named "Relation" and I would like to make a composite primary key from "PAR_ID" and "STU_ID". Both are foreign keys.

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL,
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id)
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id)
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;


How to create a DB Link to a Database which has failover in TNS Could you please help me to understand how my DB LINK creation sql look like to a database (RAC env, Oracle 12c) which have fail over  option in tns entry?

I read below post from Oracle-(not having access to view below url since I do not have Oracle Support membership)

  Don't create a DBLink using a TNS connect string configured for failover. #Database links don't support TAF or Failover. See the following: Database Link: DBLink Creation using TAF or SCAN TNS Connect String (Doc ID 399453.1)
  https://support.oracle.com/knowledge/Oracle%20Database%20Products/399453_1.html

Does it means that we have to remove fail-over, connect etc options from tns entry and then create db link?
My TNS entry is below(just a sample)

panda.host.com=
(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=OFF)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=10)(RETRY_DELAY=1)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))

I am now trying to create a DB link in another database pointing to this database so based on above fact which of below sql is correct and will be working? In 'using' clause I am providing connection descriptor since I am not allowed to make changes in tnsnames.ora.

1.

   CREATE DATABASE LINK TEST_LINK connect to REMOTE_SCHEMA identified by "MyPass" using
'(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=OFF)
    (CONNECT_TIMEOUT=3)(RETRY_COUNT=10)(RETRY_DELAY=1)
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))';

2.

CREATE DATABASE LINK TEST_LINK connect to REMOTE_SCHEMA identified by "MyPass" using
'(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))';

//failover and connect options are removed.

The referenced MOS just states the following:

Dblinks do not support TAF. This refers to FAILOVER_MODE parameter, not FAILOVER. The first parameter is about TAF, the second is about connect-time failover.
It is not recommended to use the SCAN address for dblinks in case of RAC. Instead of the SCAN address, use the VIP addresses.

Removing FAILOVER parameter will not change anything, the default parameter for it is ON.

FAILOVER

You do not need to remove connect-time failover parameters.


Platform independent select ordered by primary key Is there a reasonably standard way to perform a select on a single table and have the result set ordered by primary key without having to specify the primary key condition explicitly?

Ideally I wish I could perform a query such as the following, invalid one:

select * from my_table order as primary key;

The SQL Standard requires that you specify an explicit column list if you require the result set to be ordered.

Many RDBMS return the results of a single table select in clustered index order. As the primary key again is enforced by the clustered index by default, this gets you close.
however, there is no guarantee of this order. Things that could cause an engine to return the rows in a different order are for example parallelism within the query or other concurrent interactions with that table.

There is a fairly standard way to construct a dynamic select that has your desired order by clause specified. You can use the INFORMATION_SCHEMA management views for that purpose. However this approach falls short when it comes to actually execute the dynamic SQL as there are significant differences between the different vendors.

So in short, the answer is: NO.

There is one, admittedly small exception where you can get away without knowing the column name of a single column surrogate primary key in SQL Server:

CREATE TABLE dbo.A(i INT IDENTITY(1,1) PRIMARY KEY CLUSTERED);
GO

SELECT * FROM dbo.A ORDER BY $IDENTITY;

The key word $IDENTITY always references the one identity column of the table. But this is SQL Server specific.