How can I efficiently downsize some Unicode fields? We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all Unicode and I have calculated a savings of 5G in space converting only 2 such fields averaging 206 characters each to non-Unicode, and almost 10G in space if we convert a few more of them from nchar and nvarchar to char and varchar types. These fields will never have a requirement to hold Unicode characters that cannot be in the SQL_Latin1_General_CP1_CI_AS collation as they come in as plain ASCII originally and will always do so per the protocol standard.

I’m the software architect and chief C# developer though only a DBA hack or I would not have designed our database to have Unicode fields for high volume tables that did not need Unicode for those fields when the database was created 3 years ago. I want to correct this mistake now before we finalize converting to an AlwaysOn environment to help with various performance and backup issues.

After downsizing these two or more fields, we would like to shrink the database one time to take advantage of the space savings for full backups, and for seeding an AlwaysOn environment.

Questions are –

What is the safest and most efficient conversion technique for downsizing columns from nchar/nvarchar to char/varchar types? Esp. when there are multiple fields in the same table to be converted. I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server (4 virtual core, 8G memory) before running out of disk space even though there was 8G left on the disk, and the db has unlimited size set (Could not allocate space for object 'dbo.abc'.'PK_xyz' in database 'xxx' because the 'PRIMARY' filegroup is full). I did delete an old database before it finished after getting a disk warning so maybe it did not count that new space. Regardless it was too slow. And this was on just the two largest of these columns (12.6M rows) and only ran 2 to 3% CPU busy so seemed not very efficient, and indicated unacceptable downtime if we were to convert even these two fields much less any additional fields. Average field size for these two fields was only 206 characters or 412 bytes each. Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table. I have a FK and indexes to contend with on the table.
If I figure out how to do #1 efficiently within an acceptable maint window, what is the safest practice for doing a one-time shrink and end up with organized/rebuilt indexes and updated Statistics? I understand the logic of not doing regular shrinks and that sometime it can actually increase the size.
Is there any third party tool that could take a backup and restore it into a new database with the modified field definitions or otherwise convert certain field types?

Any suggestions and best practices most welcome.

Thanks, Dave

Regarding:

  I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server ... before running out of disk space ... it was too slow.

and

  Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table.

Generally speaking, making a copy of the table with the ideal schema is my preferred approach. But, if you only now have maybe enough space to convert the two columns, are you sure you have enough space to make a copy of the entire table?

Also, the new table would just need to have a different name. It wouldn't need to be in a different schema.

Since you are on Enterprise Edition, have you considered or even looked at enabling Data Compression? Not only would it have the effect you are looking for on the NCHAR / NVARCHAR fields, but it would also save space on other fields with other types as well.

There are two types of Compression: Row and Page. You should read up on them and run the stored procedure that estimates what your savings would be.

Enabling compression can be done as an ONLINE operation, but might require a bit of disk space. If you do not have the available space for this, then you might could consider a hybrid approach where you build a copy of the table as TableNEW, and have the clustered index already created, and created with Compression enabled. Then you should be able to slowly fill TableNEW and the data will compress as it goes in. Of course, you will want to use INSERT INTO...SELECT to do it in batches. And the full benefit of the compression might not be realized until after you drop the original table and do a full rebuild on the Clutered Index of TableNEW.

Main MSDN documentation for Data Compression.
Enable Compression on a Table or Index
sp_estimate_data_compression_savings (mentioned in the "Enable Compression" document listed directly above)

And keep in mind that there are scenarios where you might not save that much space, or saving the space isn't worth the increase in CPU activity. But, that all depends on a lot of factors so it is really something that should be tested on your system.

You could always take the approach of:

Enable compression, either directly to the current table as ONLINE (if there is enough space to support it), or into a separate table that has Compression enabled.
IF you find that CPU actually has increased beyond the benefit of the space savings, then you have the option of building the table again with regular VARCHAR fields and no Compression. Because compression was already enabled, you should definitely have room for this now.

But again, like anything we do, it should be tested. I have heard for years how horrible "XML parsing" is on CPU, and how bad Compression is supposed to be, but in practice, those concerns are quite often overstated. The only way to know is to test on your system. (ps, just in case it is not clear being a text-only medium, these final statements are not attacking what @Kin said in his answer about needing to be cautious of CPU activity increase. He is correct, at least to a degree. I am just reminding everyone to be sure to put everything into perspective of current hardware and software and system setup).


Setting up database structure for Students, Managers, and Administrators I'm trying to figure out how to set up the structure for users.

Users can have multiple Managers and Managers can manage multiple Business Units. Administrators have full control of the system.

I'm trying to figure out if I should separate the Users and Managers from the User table and have a table for Administrators and Managers or separate all three.

The Managers have multiple Business Units which a User can be managed by.

Total amount of users could be estimated about 1,000. What do you think the best approach to this?

I think the best thing to do is this:
1. Have a User table.
2. Have a User_Role table.
3. Have a User_Assigned_Role table.

Since every user of the system is a user. There is no need separating users table-wise, that means if there is a new requirement tomorrow another table will emerge.

You can create roles like Student, Manager, Administrator and assign resources to these roles and afterwards assign the roles to users. So if you want Mr. A to be an Administrator, assign an administrator role to him.

Table Design

User Table
--------------
id
user_name
password
firs_name ...

Role Table
---------------
id
role_name
role_description
role_status (To deactivate or activate a role)

User Assigned Role Table
----------------------------
id
user_id (References User Table)
role_id (Reference Role Table)

Hope this solves your problem. Contact me if you need more clarification.


MySQL refuses UPDATE with (1071, 'Specified key was too long; max key length is 1024 bytes') sometimes I'm using MySQL 5.7.2.23.

I need to copy some columns between two tables (u -> m) and I am using the following update command:

UPDATE m
 JOIN u
  ON u.id=m.user_id
  AND u.package_name=m.package_name
 SET
    m.host_version = u.host_version,
    m.device_model = u.device_model,
    ...
     m.device_name = u.device_name
 WHERE    shard_id = 96

The UPDATE works

Both tables have keys on (id, package_name):

CREATE TABLE u (
  id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  package_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  device_model` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  device_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  host_version` int DEFAULT NULL,
  shard_id` int NOT NULL DEFAULT '0',
  device_group_id` int DEFAULT NULL,
  PRIMARY KEY (shard_id`,`package_name`,`id`),
  UNIQUE KEY by_id` (`id`,`package_name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE m (
  user_id varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  package_name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  device_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  device_model varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  host_version int DEFAULT NULL,
  PRIMARY KEY (package_name,user_id),
  UNIQUE KEY by_id (user_id,package_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Innodb default row format is innodb_default_row_format=dynamic and
Innodb innodb_large_prefix=ON.

And yet for some shardId-s I get the above error message (key too long).

Explain shows the following

id            | 1
select_type   | SIMPLE
table         | u
partitions    | <null>
type          | ref
possible_keys | PRIMARY,by_id
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 5
filtered      | 100.0
Extra         | <null>

id            | 1
select_type   | UPDATE
table         | m
partitions    | <null>
type          | eq_ref
possible_keys | PRIMARY,by_id
key           | PRIMARY
key_len       | 1534
ref           | activitydb.u.package_name,activitydb.u.id
rows          | 1
filtered      | 100.0
Extra         | <null>

I understand that the key is 1534 due to 3-byte UTF-8 encoding and changing the columns to latin1 reduces the key length and solves the problem.

What I don't understand is why MySQL complains only on some shard_id values. The shards that do work seem to have a very small number of rows.

And in general we use the above JOIN for many SELECT queries and MySQL does not complain about key length.

(nimrodm's answer is better, but here are some workarounds)

VARCHAR(255) -- check the data; you probably do not need 255 in most fields.
COLLATE utf8_unicode_ci may be unnecessary for some of your columns.  Consider CHARACTER SET ascii.  utf8 takes 3 bytes toward the limit nimrodm mentioned; ascii takes only 1.
Consider normalizing those long strings.  MEDIUMINT UNSIGNED, for example, is only 3 bytes (1..16M range) -- a lot less that 3*255.


Check whether empty strings are present in character-type columns I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to write a CHECK on every table that has a VARCHAR, TEXT (or similar) column.

Assuming that one can't or does not want to do that is there a way to write a simple, generic, SQL query (obtaining table and column names from the database's metadata) that would check if any textual columns in the database contain empty strings?

The best way to do this is with a CHECK constraint, as you noted, possibly via a DOMAIN, e.g.

CREATE DOMAIN nonempty_string AS text
CONSTRAINT non_empty CHECK (length(VALUE) > 0);

then ALTER existing columns to use the domain.

If that's not possible, you will need to query INFORMATION_SCHEMA to find all columns of the target type across all tables, then for each column, dynamically generate a query to check. You can use PL/PgSQL and the EXECUTE statement for this; there are many examples of its usage this way elsewhere on Stack Overflow.

You cannot write a single query to do what you want. It just won't work. You'll have to use query generation from information_schema.


Cannot connect to Postgres DB I'm new to Postgres and I am trying to implement it through the command line. I just used it previously in a project, but that was by downloading Postgres.app and Postico. I wanted to go through the process of setting up a database through the command line. I installed postgres using Homebrew, and that was fine. I'm trying to follow this tutorial:

https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb

For some reason, none of the wrapper commands work for me. I wasn't able to use createuser, but running CREATE ROLE patrick WITH LOGIN PASSWORD 'quoted password' was fine.

When I run the \du command, I get the following:

     Role name     |                         Attributes                         | Member of
-------------------+------------------------------------------------------------+-----------
 patrick           | Create DB                                                  | {}
 patrickshushereba | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Which I believe is what I want. Because I created an account that isn't a superuser account.

I also ran \list to make sure that the databases that I wanted to use were created correctly.

  List of databases
        Name        |       Owner       | Encoding | Collate | Ctype |            Access privileges
--------------------+-------------------+----------+---------+-------+-----------------------------------------
 bridgeworq         | patrick           | UTF8     | C       | C     | =Tc/patrick                            +
                    |                   |          |         |       | patrick=CTc/patrick
 bridgeworq_testing | patrick           | UTF8     | C       | C     | =Tc/patrick                            +
                    |                   |          |         |       | patrick=CTc/patrick
 postgres           | patrickshushereba | UTF8     | C       | C     |
 template0          | patrickshushereba | UTF8     | C       | C     | =c/patrickshushereba                   +
                    |                   |          |         |       | patrickshushereba=CTc/patrickshushereba
 template1          | patrickshushereba | UTF8     | C       | C     | =c/patrickshushereba                   +

The first two in the list are the ones that I want to connect to. I'm using Knex.js and node.js to build my backend.

Here is the development object inside of my knexfile:

development: {
    client: 'pg',
    connection: {
      filename: 'postgresql://localhost/bridgeworq'
    },
    migrations: {
      directory: './data/migrations'
    },
    seeds: {
      directory: './data/seeds'
    }
  }

My problem is that when I try to run my migrations, I get the following error:

error: database "patrickshushereba" does not exist.

I haven't found any good resources to troubleshoot what is wrong. What else should I be doing in the command line to make sure that the user that I want to use patrick is connected to the database that I want to use? And if anyone is by chance familiar with Knex, is that the right way to connect to the database? Or do I need to be using a different path? Sorry for the long winded amateur question. But I'd really like to learn how to do this, and what the workflow is for setting stuff up, and I wanted to be thorough. Any help or resources would be greatly appreciated.

It turned out that I was creating the database and the user correctly. What I was doing wrong was not connecting to my database correctly through the Knexfile.

I looked at the docs from http://knexjs.org/

Once I adjusted the object in my knexfile to specify the database, I was able to run my migrations successfully.


Amazon RDS CPU Usage keep increasing without any long running process in Show Processlist We are using Amazon RDS(db.m2.2xlarge), CPU usage is suddenly increasing gradually even after  we reboot the DB Server. This is happening just past 2 days and we didn't give any new build recently.

I tried to debug using "SHOW PROCESSLIST" and identified long running process. I killed it but still CPU % is not reduced. Actually we just have 10 process at a time

You should keep an eye on things like your buffer sizes, table cache, query cache and innodb_buffer_pool_size, key_buffer_size as all of these memory allocations can have an affect on query performance which can cause MySQL to eat up CPU.
Enable slow query log and see if there are queries getting logged there.
Observe "mysqladmin status" output for sometime certain values like "Questions, Slow queries, Open tables, Queries per second avg" should help.


SQL Server 2000 DTS package, filter rows using stored procedure returned date I know very little about DTS packages in the first place (I'm a programmer not a dba) and have been stuck with the task of filtering rows based on the results of a stored procedure call that returns a date, where the desired result would be something like this:
" ( WHERE SpecialDate >= SPROC_Returned_Date )".

The stored procedure lives on a local DB server and the rows are all coming from elsewhere using a DB/C FS 3 driver connection. I'm having a very hard time figuring out how to connect the SQL query on the Transform Data Task that does the row retrieval to the date returning stored procedure.

Where should I even start?

Update:
I've been able to create a view that returns one row with the date I'm interested in from the stored procedure. However the DB/C FS 4 connection doesn't seem capable of interpreting anything involving the use of parameters. I'm at a loss here as it seems I'm just about 90% here but can find any useful information about this DB/C FS4 ODBC driver.

In SQL Server 2000, you cannot use the results of a stored proc directly in another query. However, you can insert them into a #temp table and then join to that. But you must create the temp table structure first, you can't use select into.


convert historic local time to UTC taking into account of daylight saving How should I convert historic local time to UTC taking into account of daylight saving?

The main problem is the daylight saving doesn't occur at a fixed period every year and hence I cannot think of a way to do this task.

See https://github.com/mj1856/SqlServerTimeZoneSupport by Matt Johnson for Sql Server 2008R2+.


Assuming:

p_id is unique
'last purchase' is defined by highest p_id

SQL Fiddle

MySQL 5.5.32 Schema Setup:

create table t(p_id integer, item_id integer, user_id integer, cnt integer);

insert into t(p_id, item_id, user_id, cnt) values(1,4,1,22);
insert into t(p_id, item_id, user_id, cnt) values(2,4,2,1);
insert into t(p_id, item_id, user_id, cnt) values(3,1,1,1);
insert into t(p_id, item_id, user_id, cnt) values(4,0,3,1);
insert into t(p_id, item_id, user_id, cnt) values(5,3,1,182);
insert into t(p_id, item_id, user_id, cnt) values(6,0,4,1);
insert into t(p_id, item_id, user_id, cnt) values(7,3,2,7);
insert into t(p_id, item_id, user_id, cnt) values(8,3,2,14);

Query 1:

select *
from( select *
      from t tt
      where user_id=1 and p_id=( select max(p_id)
                                 from t
                                 where user_id=1 and item_id=tt.item_id )
      union all
      select *
      from t tt
      where user_id=2 and p_id=( select max(p_id)
                                 from t
                                 where user_id=2 and item_id=tt.item_id ) ) u
where item_id in(select item_id from t where user_id=1)
      and item_id in(select item_id from t where user_id=2)

Results:

| P_ID | ITEM_ID | USER_ID | CNT |
|------|---------|---------|-----|
|    1 |       4 |       1 |  22 |
|    5 |       3 |       1 | 182 |
|    2 |       4 |       2 |   1 |
|    8 |       3 |       2 |  14 |


ORA-00942: table or view does not exist while creating synonym We are using oracle 11g, the db structure goes like this:

We have users CST and FAS.
CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user.
ST_ROLE is a role which has given SELECT UPDATE DELETE ALTER privileges on ST_CAT2. ST_ROLE is granted to FAS.

Now, while creating a view as FAS,

CREATE OR REPLACE VIEW "FAS"."EXTERNAL_SR" ("PROD_ID", "PRODUCT") AS
SELECT
prod_id,
    product
FROM
st_cat;

we are experiencing the error

ORA-00942: table or view does not exist

what could be the cause?

In the SQL Language Reference you will find under Prerequesites (emphasized by me):

  The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

So you have to grant the privilges directly to the user that creates the view.

The same is true for PL/SQL procedures and is already discussed in this post.

There are two ways to grant a select privilege directly:

grant it by an object privilege: grant select on A_TABLE to A_USER;
grant it by a system privilege: grant select any table to A_USER;

The latter is not recommended, granted such an ANY system privileges to non DBA users is considered as a security risk.

Such ANY privileges exists for a lot of procedures and actions, e.g. INSERT ANY TABLE, EXECUTE ANY PROCEDURE,...


Multi Session Mirroring using 1 witness server, cannot find certificate I am trying to use 1 witness server for 2 mirrored databases that reside on different servers.

For example:

  db1 on server(A) mirrored to server(B) using witness server(1), and

  db2 on server(C) mirrored to server(D) using witness server(1)

However, High Safety using witness server(1) only works with db1 mirrored session.
When trying to set the witness on db2 session, I get error message:

  The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness server(1)'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Mirroring is up and working on the first db1 using witness1, and mirroring also works with db2 between server (C) & (D), however the second db on server(2) is unable to connect to the witness server(1). The witness shows this error: Message

  Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.' Client x.x.x.x

I copied the original witness server cert over to both db2 primary/secondary servers. All 3 certs are on each server.  I am not using AD, just SQL Auth, and Certificates.

Microsoft Documentation suggests that a Witness can be used in multiple sessions here: http://msdn.microsoft.com/en-us/library/ms175191.aspx

Any help would be fantastic! (I'm Using SQL Server 2014 Std on Windows 2012R2 x64 machines in AWS)

The answer is in the error - you will need to make sure that both servers i.e. the new db server and the witness can communicate using the certificate in question.

There will be a certificate for each individual server and must exist on witness as well. So witness cert on the p/m servers and vice-versa


What is the cause for the error 1610 This morning, I had the following event:

  200310  9:54:57 [ERROR] Slave SQL: Could not execute Update_rows event on table Call_Center_Infinit.queue_member_table; Corrupted replication event  was detected, Error_code: 1610; Corrupted replication event was detected, Error_code: 1610; handler error No Error!; the event's master log  log TN-TX-DB03-bin.000972, end_log_pos 551613719, Error_code: 1610.

I ran mysqlbinlog --base64-output=DECODE-ROWS -vv  log TN-TX-DB03-bin.000972 and search "end_log_pos 551613719" using vi, I found the following:

#200310  9:54:57 server id 6  end_log_pos 551611999     Query   thread_id=2180761724    exec_time=0     error_code=0
SET TIMESTAMP=1583855697/*!*/;
BEGIN
/*!*/;
# at 551611999
# at 551612137
# at 551612227
# at 551612741
# at 551613719

#200310  9:54:57 server id 6  end_log_pos 551612137     Table_map: `Call_Center_Infinit`.`Agentes` mapped to number 1089
#200310  9:54:57 server id 6  end_log_pos 551612227     Table_map: `Call_Center_Infinit`.`queue_member_table` mapped to number 1140
#200310  9:54:57 server id 6  end_log_pos 551612741     Update_rows: table id 1089
#200310  9:54:57 server id 6  end_log_pos 551613719     Update_rows: table id 1140

### UPDATE `Call_Center_Infinit`.`queue_member_table`
### WHERE
###   @1=189482 /* INT meta=0 nullable=0 is_null=0 */
###   @2=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @3=52984 /* INT meta=0 nullable=1 is_null=0 */
###   @4=53740 /* INT meta=0 nullable=1 is_null=0 */
###   @5='53740' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @6='52984' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @7='Agent/53740' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @8=NULL /* VARSTRING(128) meta=0 nullable=1 is_null=1 */
###   @9=0 /* INT meta=0 nullable=1 is_null=0 */
###   @10='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### SET
###   @1=189482 /* INT meta=0 nullable=0 is_null=0 */
###   @2=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @3=52984 /* INT meta=0 nullable=1 is_null=0 */
###   @4=53740 /* INT meta=0 nullable=1 is_null=0 */
###   @5='53740' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @6='52984' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @7='Agent/53740' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @8=NULL /* VARSTRING(128) meta=0 nullable=1 is_null=1 */
###   @9=1 /* INT meta=0 nullable=1 is_null=0 */
###   @10='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */

according to me, from here is stopped replica

How was the "rotation" done?  Normally you should never need to "rotate the binlog".  1.1G is normal when max_binlog_size is 1G; it overshoots, then automatically rotates.

You could change that setting to 100M with no harm.  That will cause for frequent rotation, and each file will be about 100MB.


Indexing issues while partitioning with dbms_redefinition I get an error while doing the redefinition. Following are the steps which I have taken so far:

Step 1. Start redefinition

DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT',
 dbms_redefinition.CONS_USE_ROWID);

Successful

Step 2. Creating a interim table

CREATE TABLE "CDS_USER"."T_FDC_TOOLCONTEXT_ONLINE"
(   "SERIAL_ID" NUMBER(15,0),
"CONTEXTID" NUMBER(15,0) NOT NULL ENABLE,
"ENTITYNAME" VARCHAR2(35 BYTE) NOT NULL ENABLE,
"ENTITYCOMPONENT" VARCHAR2(10 BYTE),
"SOLUTIONTYPE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"JOBTIMESTAMP" DATE NOT NULL ENABLE,
"EAJOBNAME" VARCHAR2(35 BYTE),
"PRJOBNAME" VARCHAR2(35 BYTE),
"FAJOBNAME" VARCHAR2(50 BYTE),
"STEPNAME" VARCHAR2(80 BYTE),
"LOTNAME" VARCHAR2(20 BYTE),
"LOTTYPE" VARCHAR2(20 BYTE),
"CARRIERNAME" VARCHAR2(20 BYTE),
"PRODUCTNAME" VARCHAR2(80 BYTE),
"RECIPENAME" VARCHAR2(80 BYTE),
"WAFERID" VARCHAR2(23 BYTE),
"MATERIALNAME" VARCHAR2(20 BYTE),
"MATERIALCOUNT" NUMBER(4,0),
"CAPACITY" NUMBER(4,0),
"TECHNOLOGY" VARCHAR2(128 BYTE),
"RETICLE" VARCHAR2(60 BYTE),
"LAYER" VARCHAR2(20 BYTE),
"SWRLOT" VARCHAR2(5 BYTE),
"PORTNAME" NUMBER(1,0),
"SLOT" NUMBER(3,0),
"RUNNUMBER" NUMBER(2,0),
"MVINTIME" DATE,
"ROUTE" VARCHAR2(80 BYTE),
"EPA" VARCHAR2(64 BYTE),
"PROCTIME" DATE,
"IMAGEID" VARCHAR2(25 BYTE),
"PRODUCTGROUP" VARCHAR2(25 BYTE),
"BASICTYPE" VARCHAR2(25 BYTE),
"PRETOOL" VARCHAR2(25 BYTE),
"REWORK" VARCHAR2(100 BYTE),
"SENDAHEAD" VARCHAR2(100 BYTE),
"SEQUENCE" VARCHAR2(80 BYTE),
"RSN" VARCHAR2(25 BYTE),
"COMPLETED" NUMBER(1,0) DEFAULT 0,
"LAYER2" VARCHAR2(20 BYTE),
"RETICLE2" VARCHAR2(60 BYTE),
"TRUSTED_FLAG" VARCHAR2(2 BYTE),
"VALID_FLAG" VARCHAR2(5 BYTE),
"RECIPE" VARCHAR2(80 BYTE),
"POSITIONTYPE" VARCHAR2(10 BYTE),
"PROCESS_TYPE" VARCHAR2(3 BYTE),
"IN_WORK" NUMBER(1,0) DEFAULT 0,
"D_CARRIERNAME" VARCHAR2(25 BYTE),
"SUMOSTEP" VARCHAR2(10 BYTE),
"INSERTED_TIME" DATE DEFAULT SYSDATE,
 PRIMARY KEY ("SERIAL_ID"))
PARTITION BY RANGE ("INSERTED_TIME")
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))

Successful

Step 3. Starting the redefinition

DBMS_REDEFINITION.START_REDEF_TABLE (uname => v_name,
  orig_table => 'T_FDC_TOOLCONTEXT',
  int_table => 'T_FDC_TOOLCONTEXT_ONLINE',
  col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

Successful

Step 4. Copying the dependents

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(v_name, 'T_FDC_TOOLCONTEXT',
  'T_FDC_TOOLCONTEXT_ONLINE',
  dbms_redefinition.cons_orig_params,
  TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));

Error:

ORA-01408: such column list already indexed.

I have following indexes on my existing column.

CREATE UNIQUE INDEX "CDS_USER"."SYS_C0012094" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("SERIAL_ID")
CREATE INDEX "CDS_USER"."I_FDC_CONTEXTID" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("CONTEXTID")
CREATE INDEX "CDS_USER"."I_FDC_ENTITY" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("ENTITYNAME")
CREATE INDEX "CDS_USER"."I_FDC_TIMESTAMP"ON"CDS_USER"."T_FDC_TOOLCONTEXT"("JOBTIMESTAMP")
CREATE INDEX "CDS_USER"."I_FDC_CTRL" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("EAJOBNAME")
CREATE INDEX "CDS_USER"."I_FDC_PRJ" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("PRJOBNAME")
CREATE INDEX "CDS_USER"."I_FDC_CARRIER" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("CARRIERNAME")
CREATE INDEX "CDS_USER"."I_FDC_MATERIAL" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("MATERIALNAME")
CREATE INDEX "CDS_USER"."I_FDC_PORT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("PORTNAME")
CREATE INDEX "CDS_USER"."I_FDC_SLOT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("SLOT")
CREATE INDEX "CDS_USER"."I_FDC_ENTITY_LOT" ON "CDS_USER"."T_FDC_TOOLCONTEXT" ("ENTITYNAME", "LOTNAME")

What can I do to avoid the error?

Ok, minimal test case that reproduces the error, showing it's the intermediate table PK that is the problem:

SQL> CREATE TABLE SO33070_ORIGINAL
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  );

Table created.

SQL>
SQL> CREATE TABLE SO33070_NEW
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  )
  7  PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  8  (
  9    PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 10  );

Table created.

SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>FALSE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2026
ORA-06512: at line 5

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

OBJECT_TYPE  OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX        PHIL   SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110330" ON "PH
IL"."SO33070_NEW" ("SERIA

SQL>
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>

You have 2 options to fix this.

Option 1 - create the intermediate table without a primary key (best option!!):

SQL> CREATE TABLE SO33070_ORIGINAL
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  );

Table created.

SQL>
SQL> CREATE TABLE SO33070_NEW
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE
  5  )
  6  PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  7  (
  8    PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  9  );

Table created.

SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

no rows selected

SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>

Option 2 - simply tell DBMS_REDEFINITION to ignore the error (ignore_errors=>true).

It looks like there are some errors, but they don't matter as the PK constraint and index was created when the intermediate table was created:

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

OBJECT_TYPE  OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX        PHIL   SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110410" ON "PH
IL"."SO33070_NEW" ("SERIA

CONSTRAINT   PHIL   SO33070_ORIGINAL
ALTER TABLE "PHIL"."SO33070_NEW" ADD CONSTRAINT "TMP$$_
SYS_C00110410" PRIMARY KE

SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>


Database is not reflecting in sh.status() I have a 2 shard (s1 and s0 )servers with repl set. s1 has 2 dbs (db-A and db-B) and one of the db is sharded (db-B).

When i do sh.status() from mongos only (db-B) is displayed. I am wondering what i need to do for identifying of db-A by mongos.

db.getSiblingDB.databases.find()

Those databases what have "partitioned":false are not sharded and you can see from "primary":xxx key which shard those are located.


Don't see it : Integration Services Catalog, and open the [All Executions] report I have SQL Server 2012 Enterprise Edition (64-bit) on Windows NT 6.2.
I am not seeing the option to the "[All Executions] report" in the Integration Services Catalog . How can I solve it?

Update: I have attached another image that shows that I do not have the same menu as in the answer by stacylaray.

Right click on the SSIS Catalogue, highlight Reports, and select All Executions.

The Simple Talk article, SSIS 2012 Projects: Deployment Configurations and Monitoring by Feodor Georgiev, includes the following illustration:

If you are not observing the Reports submenu, try running Setup again and choose Management Tools – Complete. Try executing SSMS as Administrator.


Row of database is unreacheble Here is the problem: I have a table called "flags" with 54 columns:

PVKEY   PEOPLEKEY   ADDRESSKEY  FLAGTYPE    FLG1    FLG2    FLG3    FLG4    FLG5    FLG6    FLG7    FLG8    FLG9    FLG10   FLG11   FLG12   FLG13   FLG14   FLG15   FLG16   FLG17   FLG18   FLG19   FLG20   FLG21   FLG22   FLG23   FLG24   FLG25   FLG26   FLG27   FLG28   FLG29   FLG30   FLG31   FLG32   FLG33   FLG34   FLG35   FLG36   FLG37   FLG38   FLG39   FLG40   FLG41   FLG42   FLG43   FLG44   FLG45   FLG46   FLG47   FLG48   FLG49   FLG50

If I do this:

select * from flags where PVKEY = 902180

it takes forever. If I do for any other PVKEY, the query runs in less then 1 second.

If I do:

select * from flags

It runs fast until reachs that PVKEY.

I've try to UPDATE or DELETE, with no sucess, it never ends the execution.

The only query that works for this row is:

select PVKEY from flags where PVKEY = 902180

It returns me: 902180

I'm running SQL Server 2012.

Any ideas, please?

To see open transactions which may be locking rows, execute:

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Issuing a COMMIT or ROLLBACK for those transactions would address your issue.

If you don't mind dirty reads (depends on your business rule for your particular situation) you can

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

And this would allow you to read the table without the wait. It;s important to understand that the data would be inaccurate if the pending transaction was rolled back.


SQL Server: hierarchy of permissions for schema? We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is explicit permissions like

Alter
Control
Delete
Execute
...

What is the hierarchy of these permissions? The idea is to grant Control for admin AD group, and give developer AD group some rights that allow them to do their work (ddl, read, write). What rights should be given for developers?

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

To see what can be GRANTed: GRANT Schema Permissions
To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

CONTROL implies the rest and is the highest permissions of any securable
SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
EXECUTE to run scalar UDFs and Stored Procedures in that schema
ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy


How do I Delete A Database And Log Files With SQL 2008 Script? While working on making an automated Delete and Restore script for my backups I ran into a snag. I can not delete the database; however, the delete button works exactly how I need it to. Can anyone tell me the script for that delete button?

I have also tried another query to do this task, however it is not working like I would like. Here is the code:

> USE CENTRAL
ALTER DATABASE DATABASE REMOVE FILE DATABASE_1.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE_2.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE.MDF
ALTER DATABASE DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DATABASE SET OFFLINE
EXEC sp_detach_db DATABASE, true;
DROP DATABASE DATABASE
GO

The error that I get when I run the code is:

Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102).  The step failed.

I am not sure what I am doing wrong, can someone please tell me?

If you click on the 'Script' in the execute box that pops up from the delete button, it reveals that the script actually uses a DROP command:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLE]    Script Date: 3/6/2014 9:29:39 AM ******/
DROP TABLE [dbo].[TABLE]
GO


PostgreSQL update and delete property from JSONB column From this article, I tried to update or delete property of a JSONB column:

CREATE TABLE xxx (id BIGSERIAL, data JSONB);
INSERT INTO xxx(data) VALUES( '{"a":1,"b":2}' );
SELECT * FROM data;
 id |       data
----+------------------
  1 | {"a": 1, "b": 2}

create the update function:

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB FROM json_union;
$$ LANGUAGE SQL;

testing:

-- replace
UPDATE xxx SET data = jsonb_merge(data,'{"b":3}') WHERE id = 1;
SELECT * FROM xxx;
 id |       data
----+------------------
  1 | {"a": 1, "b": 3}

-- append
UPDATE xxx SET data = jsonb_merge(data,'{"c":4}') WHERE id = 1;
SELECT * FROM xxx;
 id |           data
----+-------------------------
  1 | {"a": 1, "b": 3, "c": 4}

The question is:

is there any drawback of using JSONB_EACH (jsonb_merge) instead of JSONB_EACH_TEXT (from the article) in this case?
how to modify the jsonb_merge so if the second parameter property value is null (something like {"b":null}) the value would be erased?

.

-- remove
UPDATE xxx SET data = jsonb_merge(data,'{"b":null}') WHERE id = 1;
SELECT * FROM xxx;
 id |       data
----+-----------------
  1 | {"a": 1, "c": 4}

Question 1
There should be no signicant drawbacks. As the value is converted back to jsonb anyhow I would guess it would be more efficient to keep it that way the whole time.

Question 2
Just replace your function with the following (only the part WHERE key NOT IN ... added):

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;


Is there any security risk in having many foreign keys referencing a single table? I have a teacher who told me that having a database with a table that's referenced by around 60% of the other tables was a security no-no. The problem is that he failed to explain why it was a security risk.

This stretches the bounds of credibility.  The only thing I can think of is that by deliberately causing referential integrity errors the attacker is able to harvest the error messages and derive information the underlying schema.

However, if the attacker is able to do that you have bigger worries.


Extract date and time separately from a time with time zone column I have a table in PostgreSQL 9.3 with the following column

....,
  created_at time with time zone NOT NULL DEFAULT now(),
...,

When I do a select created_at from trails; I get this

"02:32:44.62054-05"

How can I get the date in this format dd-mm-yyyy (01-11-2015)? I have tried select created_at::date from trails; but I get this error

ERROR: cannot cast type time with time zone to date

I changed to select date(created_at) from trails; and got this error

ERROR:  function date(time with time zone) does not exist

I want to do one select statement that will get the date and time separately in the format dd-mm-yyyy and hh:nn:ss in two columns i.e. select date(created_at) as date, time(created_at) as time from trails;

From the documentation:

Name                       Description                        Low Value      High Value
time [(p)] with time zone  times of day only, with time zone  00:00:00+1459  24:00:00-1459

It would appear that the data type you have chosen for this column does not hold any "Date" value. What you are trying is not possible.


Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB? Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB?

someone says here that transactions only work in some tables types

The only storage engine ("table type") that supports transactions with the standard MySQL 5.x distributions is InnoDB.  MyISAM and the others do not support transactions.

http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-transactions.html


Is the innodb_lazy_drop_table problem solved? I'm researching database configuration for MySQL/MariaDB. I've been reading the configuration chapter in "High Performance MySQL". In there, it suggests that innodb_lazy_drop_table can be used to avoid server stalls when dropping tables if innodb_file_per_table is set, to stop an immediate scan of the buffer pool to invalidate pages that refer to the dropped table.

However, I also found that innodb_lazy_drop_table has now been removed from XtraDB.

Does that mean the original issue is fixed, and I no longer have to worry about performance issues when dropping InnoDB/XtraDB tables? Or perhaps the lazy invalidation of pages has been enabled by default and the configuration option is all that's been removed?

It seems to be fixed (see percona manual):

  Feature has been removed because the upstream DROP TABLE implementation has been improved.


dynamically create trigger to audit row changes I'm using this approach to create a trigger to catch data changes. However, the system allows schema changes, and I would like to create this trigger dynamically after a new table has been created. I was trying to separate the logic into a stored procedure and call it in the trigger body, but in the stored procedure context, there's no access to inserted / updated. Is there any better approach to achieve the same result?

CREATE PROC X
    @TableNameValue varchar(50)
AS

    declare @bit int ,
        @field int ,
        @maxfield int ,
        @char int ,
        @fieldname varchar(128) ,
        @TableName varchar(128) ,
        @PKCols varchar(1000) ,
        @sql varchar(2000),
        @UpdateDate varchar(21) ,
        @UserName varchar(128) ,
        @Type char(1) ,
        @PKFieldSelect varchar(1000),
        @PKValueSelect varchar(1000)

        select @TableName = @TableNameValue
        -- date and user
        select @UserName = system_user ,
        @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
        -- Action
        if exists (select * from inserted)
        if exists (select * from deleted)
        select @Type = 'U'
            else
        select @Type = 'I'
            else
        select @Type = 'D'

        -- get list of columns
        select * into #ins from inserted
        select * into #del from deleted
        -- Get primary key columns for full outer join
        select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where pk.TABLE_NAME = @TableName
        and CONSTRAINT_TYPE = 'PRIMARY KEY'
        and c.TABLE_NAME = pk.TABLE_NAME
        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
        -- Get primary key fields select for insert
        select @PKFieldSelect = coalesce(@PKFieldSelect+'+','') + '''' + COLUMN_NAME + ''''
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where pk.TABLE_NAME = @TableName
        and CONSTRAINT_TYPE = 'PRIMARY KEY'
        and c.TABLE_NAME = pk.TABLE_NAME
        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
        select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where  pk.TABLE_NAME = @TableName
        and CONSTRAINT_TYPE = 'PRIMARY KEY'
        and c.TABLE_NAME = pk.TABLE_NAME
        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

        if @PKCols is null
        begin
            raiserror('no PK on table %s', 16, -1, @TableName)
            return
        end

AND FOR EVERY SCHEMA CHANGE I WOULD RUN THIS STATEMENT

PS: trigtest is the new table, I've hardcoded just to test it

create trigger trigtest_ChangeTracking on trigtest for insert, update, delete
as
    declare @TableNameValue varchar(50)
    set @TableNameValue = 'trigtest'

    exec X @TableNameValue

SQL Server supports DDL triggers. This would fire when a table is created, for example.

Constructing the table trigger code dynamically in the DDL trigger would, I believe, achieve the desired outcome.


Differences between development and live database setups I was wondering if there are any best practice recommendations on setting up databases in a dev environment versus a live environment with regards to configuration.

Should they be as similar as possible (preferably identical)? Or should the dev environment be purposefully under powered in order to push developers to write optimal queries?

This could be as simple in MySQL as setting query_cache_size and related values to lower when on the dev machine.

I'd say that not having in development/test environments one that matches production will be a bad bet.

I worked for a shop where we didn't have an environment that matched production, so we couldn't do many of the tests as we should. I'm not saying that each developer/tester should have an env identical to production, but at least one environment for all devs/testers. And for usual development any environment will do. Testing performance of the code should be mandatory, if not for all pieces of code, than at least for the most used ones (can use profiling tools to find out).


How to get Size of db tables one month back Recently there has been a drop in our db size and we want to compare the size of the tables back then with that of now. For this purpose we need the exact size of each table one month back.
We can extract license_size for an old date, but how to extract size of each table say a month ago.

Unless you keep a log of it , you cannot. There is no history mechanism to keep track of tbl sizes.

you also can get a lot of scripts and articles on Vertica here www.aodba.com

What i do to keep an eye on this kind of stuff is logging this info in a dba use table using a cron job.

##will run every 30 minutes
*/30 * * * * /home/dbadmin/scripts/dba/database_growth.sh

Content .sh script

#!/bin/bash
#!/bin/bash

. /home/dbadmin/.profile
export VSQLDIR=/opt/vertica/bin
export SQLDIR=/home/dbadmin/scripts/dba
export data=`date`
echo " Execution date -" $data >> /home/dbadmin/scripts/dba/log/database_growth.log
echo "#################################################" >> /home/dbadmin/scripts/dba/log/database_growth.log
$VSQLDIR/vsql  -U $username -w $password -d analytics -f $SQLDIR/database_growth.sql >> /home/dbadmin/scripts/dba/log/database_growth.log
echo "#################################################" >> /home/dbadmin/scripts/dba/log/database_growth.log

Note:/home/dbadmin/.profile -- holds all the parameters values (this is to avoid text passwords - they are all logged as text - you wanna avoid this )
Content database_growth.sql

 insert INTO dba.database_growth(time,schema_name,size_gb) SELECT * from dba.pre_database_growth;

Content view dba.pre_database_growth

CREATE VIEW
    dba.pre_database_growth AS
SELECT
    (statement_timestamp())::TIMESTAMP AS GETDATE,
    p.anchor_table_name,
    s.schema_name,
    ROUND(((((SUM(s.used_bytes) / 1024::NUMERIC(18,0)) / 1024::NUMERIC(18,0)) /
    1024::NUMERIC(18,0)))::FLOAT, 3) AS size_in_bytes_gb
FROM
    v_monitor.storage_containers s join v_catalog.projections p
    on s.projection_id=p.projection_id
GROUP BY
    s.schema_name,p.anchor_table_name;

You can use this then to create growth reports on your objects over time.

I hope this helped.


How to create a summary report that contains other reports? I have 4 reports that I run for 8 different environments. Each report creates a graph from some usage tables. It's very simple SQL that creates a very simple graph.

The guy who used to do these manually took the results of each one of these reports and pasted them into a powerpoint doc.

I have been able to get each one of these reports into report server so you just need need to click on the report and see the graph. I can set up subscriptions to send out these results with no problem either.

The problem is that I am sure the managers don't want 32 graphs coming their way. They want only one doc to see all the graphs.

Is there a way to put all these graphs on one report?

You can do this.  You can create a single report that has multiple data sources (one per environment).  Add the data sets required.  Recreate the graphs (you may be able to leverage some cut-and-paste).  In order to format them nicely, you will need to place the graphs into the cells of a matrix.

You could also build a single report that links to other reports.

I realize this is a high-level description of how to do it - if you need further details, let me know.


How to select last record of each month I have a table:

Table1(DDate date,Deposit_Key Number , Amount Number)

some example data are:

DDate       Deposit_key        Amount
1/1/2019          1              10
1/2/2019          1              20
1/3/2019          1              30
1/4/2019          1              40
  ----           ---             ---
1/31/2019         1              100

I have data for one month and I need to retrieve the Amount for the last day of the month . I mean I need to have 100 . What is the best query possible for this? I'm new to oracle and I want to know whether there are better ways than using row_number() over(partition)?
Thanks in advance

The Oracle built in function called LAST_DAY can help you here.

It can be used with dates or timestamps and an example based on your table description is:

SELECT * FROM TABLE1
WHERE EXTRACT(DAY FROM DDATE) = EXTRACT(DAY FROM LAST_DAY(DDATE))
AND EXTRACT (MONTH FROM DDATE) = 1;

There are many ways to work with dates in Oracle but I like using EXTRACT as it works with dates and timestamps and means you don't have to use the TO_CHAR function.


Creating and querying a database to set hierarchical associations between Groups, Users, Items and Points I am working with three entity types, Group, User and Item. A Group consists of Users, and a User consists of Items.
Now, I have to work with a fourth entity type: Point. I want to be able to distribute these Points on a Group, User and Item level. A Point is an entity that can be assigned on many levels.
Say I have a Point in "Group 1", then all users and items have access to that. So let's say I add a new user and attach it to "Group 1" then it would automatically have access to that point in the application because it's a member of "Group 1".
Assuming that I want to give a point to "Group A" then a point should be associated with the group, all the users and the items of those users.
If I, for example, give a point to "User 2" then the point is associated with that user and all the items associated with that user.
My idea for the three levels is dynamically adding new data and hence inheriting those permissions while having the ability to add the Point permission all the way down to a Item level if you decide that just a single item should be able to see it.
How would I go about making a data scheme for this scenario?
My attempt so far
Entity-relationship diagram
My attempt at a diagram of how the significant entity types would connect in my head:

Data manipulation
Example with assigning GROUP 1 to POINT 1:
INSERT INTO Point_group
   (group_id,point_id)
VALUES
   (1,1);

Example with assigning USER 1 and 2 to POINT 1:
INSERT INTO Point_user
    (user_id,point_id)
VALUES
    (1,1);
INSERT INTO Point_user
    (user_id,point_id)
VALUES
    (2,1);

And then write some logic to determine what entries are marked based on that. I feel a bit lost.

I think your approach - in having 3 joining tables (Point_group, Point_user, Point_item) is sound.

The considerations of this design are:

Adding "permission" is easy enough - if the user selects to add to User, then you insert into Point_user
Starting with a Point, if you want to ask "Which Groups, Users and Items have permission?" then you will need to construct a query against all 3 joining tables. You could have 3 separate queries and aggregate results in your application, or you could have a single query which is the UNION of 3 SELECTs (with a column representing the SELECT that includes a row, with values like user, etc)
If you want to start with a Group, User or Item, and ask "Which points are permitted for this Group/User/Item?" then your query gets more complicated the lower down you go. For example - if you start with an Item, you have to query Point_item for all rows having the ID of the Item. You also have to find all linked User rows - and for each of those rows, check Point_user. Likewise, you then have to find all linked Group rows and then search Point_group for all matches. In straight SQL this will be a large SELECT - but that may be totally fine - it's just something to think about.

To come at this another way - what alternatives do you have?

You could have a single table that somehow maps permissions from a Point to other objects. Its design would have point_id, of course, and then a column to specify what it is that this Point is mapping to - ie. Group, User, or Item. Then, of course, you need an id column - which would link to the relevant table.

I have seen models such as this alternative implemented before, and quite frankly, I think that's a worse design. Such a design means that when you want to join from your Point to the other entity, you need to check this mapping flag column in order to make a decision about which table should be joined. It's messy, ugly and performance intensive.

There is one further alternative, however:

Merge your Group, User and Item tables into a single table. For want of a better name, call it Entity, in this example. You now need a flag to indicate the type of entity on a given row - such as Group, User and Item. You will also want a column to specify parent - so that for an Item row you can specify its User parent.

This last suggestion would mean you only need a single mapping table between Entity and Point. However, run through all of the above scenarios again - how will you query this if you start with, say, a Point, and want to know which Groups, Users and Items have permission to view? Getting a list of all entities would be a simple query, but if you want to list all members of all entities, you would need the entity table to join itself multiple times.

This last design may have its benefits, but I think your original proposal is the easiest to maintain and understand.


SQL Server has encountered occurences of I/O requests taking longer than 15 seconds On Production SQL Server, we have following config:

3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to single Dell SAN storage unit which is a RAID array

From time to time, on PRIMARY we are seeing messages similar to below:

  SQL Server has encountered 11 occurrence(s) of I/O requests taking longer
      than 15 seconds to complete on file [F:\Data\MyDatabase.mdf] in database id 8.
      The OS file handle is 0x0000000000001FBC.
      The offset of the latest long I/O is: 0x000004295d0000.
      The duration of the long I/O is: 37397 ms.

We are novice in performance troubleshooting

What are the most common ways or best practices in troubleshooting this particular issue related to storage ?

What performance counters, tools, monitors, apps, etc. must be used to narrow down to the root cause of such messages ?

Might be there is a Extended Events that can help, or some kind of audit / logging ?

UPDATE: added my own Answer (see below) which explains what we did to fix the issue

This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN?

If you go to your SAN team and start talking about the disks being slow, they're gonna show you a fancy graph with 0 millisecond latency on it and then point a stapler at you.

Instead, ask them about the network path to the SAN. Get speeds, if it's multipathed, etc. Get numbers from them about the speeds you should be seeing. Ask if they have benchmarks from when the servers were set up.

Then you can use Crystal Disk Mark or diskpd to validate those speeds. If they don't line up, again, it's most likely the networking.

You should also search your error log for messages that contain "FlushCache" and "saturation", because those can also be signs of network contention.

One thing you can do to avoid those things as a DBA is make sure that your maintenance and any other data-heavy tasks (like ETL) aren't going on at the same time. That can definitely put a lot of pressure on storage networking.

You may also want to check the answers here for more suggestions: Slow checkpoint and 15 second I/O warnings on flash storage

I blogged about a similar topic here: From The Server To The SAN


How to use language PL/PythonU on a PostgreSQL database via phpPgAdmin? I am used to PgAdmin III where I can simply create python functions like the following on a PostgreSQL database:

CREATE OR REPLACE FUNCTION pyHello (x integer)
RETURNS void AS $$
    if x == 1:
        plpy.notice("Hello World")
$$ LANGUAGE plpythonu VOLATILE;

And that works flawless:

Now, executing exactly the same query on the same database with the same user gives me an error in phpPgAdmin:

ERROR:  syntax error at or near "CREATE"
LINE 1: SELECT COUNT(*) AS total FROM (CREATE OR REPLACE FUNCTION py...
                                       ^

Here is a screenshot:

Now I'm wondering:

How to use language PL/PythonU on a PostgreSQL database via phpPgAdmin?
Is it even possible to use PL/PythonU through phpPgAdmin?
Why is it wrapping my query in a SELECT COUNT(*) FROM (...) AS sub query?

Like this:

SELECT COUNT(*) AS total FROM (CREATE OR REPLACE FUNCTION pyHello (x integer)
RETURNS void AS $$
    if x == 1:
        plpy.notice("Hello World")
$$ LANGUAGE plpythonu VOLATILE) AS sub

This puzzles me a little bit and I couldn't find any information on the internet. As far as I understand the language support is installed into the database, so why is this query working on one interface but not on the other?

The host is ArchLinux with the following package versions installed:

extra/python2         2.7.9-1
extra/postgresql      9.4.1-1
community/phppgadmin  5.1-3
community/pgadmin3    1.20.0-1

Any ideas?

The trick is to uncheck the paginate results checkbox before executing the query. This creates that SELECT COUNT(*) ... wrapper.

Without that checkbox selected, creating the plpythonu query is working as expected.

I created the bug report #449 on sourceforge to see if this is really the desired behaviour.


VBA @ Access on replacing values on a huge list I have a question (that's why I'm here actually :P ) about VBA @ MS Access.

So, I have a huge database (well, for me it is huge, about 23K+ ID) on shoes. It has 5 columns: ID, Article number, size (mm),pairs (avaliable on stock) and EAC13 codes. It is updated daily. Everyday I get the update on it, on CSV (all those ID all over again) and create new tables, although I just need to update stock availability. However there is one column that I have to change in order to get reports: Sizes. I have to get 2 reports, one where I get the size of the shoe in mm and the other where the size is on European measure. For example: 245 mm is equal to a 35. Simple as that. I have a VBA that I thought it could help me to replace all those values (yes, all the 23k on the "size" column), that goes like this

Public Sub Substitui()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT * FROM BS_2704"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
For Each fld In rst.Fields
If fld.Value = "150" Then
rst.Edit
fld.Value = "24"
rst.Update
End If

If fld.Value = "160" Then
rst.Edit
fld.Value = "25"
rst.Update
End If

(...)

If fld.Value = "325" Then
rst.Edit
fld.Value = "50"
rst.Update
End If

Next fld
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Anyway...this only changes 150 entries (more or less). The million dollar question is, is there any kind of line that I am missing here in order to get this VBA to change ALL the 23k entries?

I am a n00b on this fields, so I would appreciate some n00b therms for a start. Thanks for the patience.

I think I can guess why your VBA is only changing around 150 records instead of all of them, but more importantly, you need to know that the specific method you're using--changing all the fields using VBA--can lead to incorrect data in your table. And, you can accomplish what you want much more easily with a SQL query ... even one made using the query designer. But for now I'll stick with plain SQL. There are three possibilities and corresponding approaches:

1 You just have to change a limited number of different sizes from mm to European. In that case use the switch function, e.g.:

update BS_2704
set Sizes = switch(
  Sizes = 150, 24
, Sizes = 160, 25
, ...
, Sizes = 325, 50
, true, Sizes
)

2 You can use a mathematical formula to convert from your mm size to your European measure, e.g. like http://en.wikipedia.org/wiki/Shoe_size#Europe:

update BS_2704
set Sizes = Sizes * 3 / 20

3 You can't use a mathematical formula, and you have such a large number of conversion mappings that putting them all in a single switch function is unwieldy: in this case you create a new table, e.g. named tblSizeMappings, with the mm sizes and the corresponding European sizes:

SizeMM SizeEuropean
   150           24
   160           25
   325           50

Then, do an update query like above but using the tblSizeMappings table to get the European sizes:

update BS_2704
inner join tblSizeMappings
on BS_2704.Sizes = tblSizeMappings.SizeMM
set BS_2704.Sizes = tblSizeMappings.SizeEuropean


Installing and enabling both master and slave semisynchronous plugins on same server I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience any problems having both master and slave plugins installed and enabled (see here)

It looks like after installing the second plugin, both variables rpl_semi_sync_master_enabled and rpl_semi_sync_slave_enabled are reset to off (the non-default). When I include these lines in my.cnf:

loose-rpl_semi_sync_master_enabled = 1
loose-rpl_semi_sync_slave_enabled = 1

...and restart MySQL, both plugins are loaded and enabled.

Is this going to cause any problems for me?

This will not cause any problems. I have always setup all MySQL 5.5. DB servers for clients with both plugins. In fact, I answered a question back on Aug 05, 2011 where I posted an update on Aug 08, 2011 on how to install both plugins at the same time
Please see Is MySQL Replication Affected by a High-Latency Interconnect?
Here is the excerpt from that update
EXCERPT
The configuration of MySQL 5.5 Semisynchronous Replication is straightforward
Step 1) Add these four(4) lines to /etc/my.cnf
[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL
service mysql restart

Step 3) Run these commands in the MySQL client
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option
[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL
service mysql restart

All Done !!! Now just setup MySQL Replication as usual.


What happens in this strange SQL Server Mirroring situation? Let's assume that two SQL Server instances (S1 and S2) are in synchronous mirroring or in a synchronous Availability Group. Now the following happens:

A write happens on S1 and transaction commit is initiated by the client
The write is hardened on S2 (but not yet hardened on S1)
S1 loses power and the commit log record is never written
S2 loses power (the transaction is hardened already)
S1 starts up and undoes the transaction
S2 starts up and keeps the transaction

Now S2 is further along in time! It has a write that S1 does not have. The two replica have diverged.

What happens in such a situation? What happens when more writes arrive at S1 and take S1 into an incompatible history regarding S2? The write histories would look like a fork.

Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario.

In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur on the Mirror (as you have correctly assumed usr) and this does indeed mean that the log has to be written to (those respectively log buffers flushed to disk on commit). Remember that this only means the log file has the transaction hardened to it, but at this stage, the transaction would still be "in the redo queue". This allows for a very fast commit to the Mirror, meaning that the commit on the Primary can also occur very quickly afterwards - this thereby reduces the chances further that there would be a discrepancy in the first place.

Other thing to consider is that if you are using delayed durability, this alters the mechanics further, and could actually result in lost transactions to your Mirrored database as a whole (due to the change in behaviour of the log buffer flushes - which no longer flush on commit), but resolution would between the two would still be able to occur to find the correct lsn point.


Oracle 10g dump file into Oracle 11g database I went through this link for my answer to the same question. I was not sure if i could continue in the same link, so created a new question.

Oracle 10g dump file push into Oracle 11g database

Sorry if I am wrong.

When I used imp  with the following parameters:

file=my.dmp log=my.log fromuser=myuser touser=myuser statistics=none, ignore=y, full=y

I got the following errors:

IMP-00058: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
IMP-00000: Import terminated unsuccessfully

Also this error message was there

  the procedure entry point longjmp could not be located in the dynamic link library ORAUTS.DLL

The database and devsuite home are installed on Windows in two different drives. I found some references saying I should run the datapump import commands from the 11g home, with this:

set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_1
set ORACLE_SID=ORCL11R1

I am new to this Oracle database. Should this command be run in command prompt, since the installation is done on Windows, is it "set ORACLE_HOME"?

Any suggestion on this would be welcomed.

That error is most likely due to incorrect environment settings indeed, probably your PATH is mixed up too.

In a command prompt, assuming that your Oracle 11g home is indeed as you posted above, run:

set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_1
set ORACLE_SID=ORCL11R1
set PATH=%ORACLE_HOME%\bin;%PATH%

Make sure you can connect with sqlplus, then start imp.

(Do consider using the expdp/impdp Data pump tools instead though. imp/exp are essentially deprecated in 11g.)


Optimize Bitmap Heap Scan I am trying to understand why my query takes a really long time even though I have the required columns indexed:

SELECT entity_id,
       id,
       report_date
FROM own_inst_detail
WHERE ( own_inst_detail.id = 'P7M7WC-S' )
  AND ( own_inst_detail.report_date >= '2017-02-01T17:29:49.661Z' )
  AND ( own_inst_detail.report_date <= '2018-08-01T17:29:49.663Z' )

The cached result of EXPLAIN ANALYZE is as follows:

Bitmap Heap Scan on own_inst_detail (cost=20.18..2353.55 rows=597 width=22) (actual time=1.471..6.955 rows=4227 loops=1)
  Recheck Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
  Heap Blocks: exact=4182
  ->  Bitmap Index Scan on own_inst_detail  (cost=0.00..20.03 rows=597 width=0) (actual time=0.901..0.901 rows=4227 loops=1)
        Index Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
Planning time: 0.123 ms
Execution time: 7.801 ms

This part of the query takes 4 out of 5 total seconds that my full query takes.

I have combined index for id and report_date. I also have two standalone indexes for those columns.

I've tried playing around with setting high work_mem as well as lowering the random_page_cost  but nothing really helps.

Any additional suggestions are greatly appreciated.

I found similar question How to index WHERE (start_date >= '2013-12-15') which recommends adding a B-Tree index, but I already do have index for report_date.

Create table script:

CREATE TABLE IF NOT EXISTS public.own_inst_detail (
    entity_id character(8) NOT NULL,
    id character(8) NOT NULL,
    report_date date NOT NULL,
    PRIMARY KEY(report_date)
);

Index:

CREATE INDEX indx_own_inst_detail_report_date_desc ON own_inst_detail (report_date DESC NULLS LAST)

CREATE INDEX indx_own_inst_detail_id_report_date_desc ON own_inst_detail (id, report_date DESC NULLS LAST)

Data types
The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like
report_date >= timestamp '2017-02-01T17:29:49.661Z'

Or:
report_date >= date '2017-02-02'

The manual about constants and type casts.
Index
Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.
If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:
CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id)

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.
Related:

Can Postgres use an index-only scan for this query with joined tables?


Select all customers and number of orders per month as csv This is what I have:

customer
--------
customerId INT(10)
name TINYTEXT
phone VARCHAR(10)
...

order
-----
orderId int(10)
customerId INT(10)
dateCompleted DATETIME
...

This is what I need:

custId  name  thisMo  lastMo  last3Mos  monthsThisYear              ordersPerMonth
------  ----  ------  ------  --------  --------------              --------------
1       John  5       10        28      2019-01,2019-02,2019-03,..  7,11,9..
2       Bill  9       15        33      2019-01,2019-02,2019-03,..  12,10,14..
3       Jack  3       8         19      2019-01,2019-02,2019-03,..  3,7,5..

Trying to generate a table showing customers, number of orders they've placed this month, last month, and last 3 months, along with a spark line chart showing number of orders per month for the entire past year (hence the comma separated lists of months and orders per month)

Currently, they're using a single query to get all customers, then each customer is looped through to generate the table and additional queries are made to get the numbers. The queries to get the csv are as follows:

SELECT GROUP_CONCAT(`count` SEPARATOR ',') AS `countCsv`
FROM (
    SELECT SUM(IF(`customerId` = {$row['customerId']}, 1, 0)) AS `count`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

SELECT GROUP_CONCAT(`month` SEPARATOR ',') AS `monthCsv`
FROM (
    SELECT DATE_FORMAT(`dateCompleted`, '%Y-%m') AS `month`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

...but this is resulting in 5 extra queries per row per page load and it's taking forever. I've been trying all day (8-1/2 hours now) to search and extrapolate from similar questions and I just can't get anything even close to working in a single query.

Edit - added customerId to order table to show customerId as the relationship between the two tables.

Step 1:  Get the data without the complexity:

CREATE TEMPORARY TABLE t
SELECT cus.customerId,
       cus.name,
       CONCAT(cus.LEFT(dateCompleted, 7), '-01') AS month_of,
       o.orderId
    FROM Customer AS cus
    JOIN orders AS o  ON ??? -- how do the table relate??
    ;

(Fox and try that query now.)

Step 2:  Compute the counts:

SELECT customerId, COUNT(*) AS ct
    FROM t
    WHERE dateCompleted
    GROUP BY customerId, month_of
    ;

(Try that query now.)

Step 3:  Use that as a "derived" table to build the ultimate table.

SELECT a.customerId AS custId,
       a.name,
       SUM(a.month_of >= CURDATE() - INTERVAL 1 MONTH) AS thisMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 2 MONTH AND
           a.month_of  < CURDATE() - INTERVAL 2 MONTH) AS lastMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 3 MONTH) AS last3mo,
       GROUP_CONCAT( ??? )  as monthsThisYear,  -- unclear where this comes from
       GROUP_CONCAT(t.ct ORDER BY month_of) AS ordersPerMonth
    FROM ( ..query 1, above.. ) AS a
    JOIN ( ..query 2, above.. ) AS cts  USING(customerId)
    GROUP BY customerId, a.name

There are several holes, but maybe this gives you some useful clues.

If you are using MySQL 8.0 or MariaDB 10.2, use WITH instead of a temp table.


SSRS Matrix Report - Count for NULL data I have a SSRS Matrix Report for data quality in our warehouse. We use this report to display invalid values in specific fields during specific time period.

For example, Missing/Invalid Postcode,
My SP lists all the users with Missing or Invalid postcode in our system. I list this users based on their activities in past 6 months. At Front-end, I am using Matrix report to display the count of Users.

Another Validation field is Missing Birth-date, if in last 6 months there isn't any user without birth-date, my SP returns a row with Validation Field as "Missing Birth-date" but the activity date will be null as there isn't any activity time. (This is the result of my left outer join on Validation Fields Table. Please refer to attached ERD for more details)

If it appears at least once in last 6 months, count becomes 0 for other five months automatically but what I want to do is display 0 as Count for "Missing Birth-date" validation criteria but currently this specific criteria doesn't appear on the report at all.

Does anyone have any idea on How I can achieve this?

Thanks,

Maulin.

I changed the structure of my report and instead of Primary Key I used date-time field to count the records and that started giving me 0 where there were no DQ issues.


New table row autopopulated with data from other rows? I am in the middle of developing a simulated horse game using PHP and databases. Apparently I have just enough knowledge to get into trouble with my grand ideas! I'm looking for help/tips on my current task, creating new horse stats based on currently registered ones in a database.

I have a horse table containing data similar the following:

CREATE TABLE horse(id int, name text, stat1 int);
INSERT INTO horse VALUES
  (1,'sparky',20),
  (2,'spunky',10);

I want to create a new horse using the average of the horse 1 & 2's values contained in stat1. So horse 3 would have a stat1 value of 15 that would be automatically calculated.

Is this possible using PHP? Or can someone point me in the right direction?

If you want the average for all horses:

insert into horse (id, name, stat1)
select nextid, 'name', avg(stat1)
from horse;

If you want the average for a subset of horses you can add a where clause:

insert into horse (id, name, stat1)
select nextid, 'name', avg(stat1)
from horse
where ... ;

If id is autogenerated:

insert into horse (name, stat1)
select 'name', avg(stat1)
from horse
where ... ;


move last three characters of column to a new column I have a column ( cases ) that contains 14 characters xxxxxxxxxx-xxx
I need to remove the "-" and move the last three characters to column ( subcase )

is there a easy way to do this as there are 1000's of entries

Create your new column, then do something like:

UPDATE mytable
SET subcase = RIGHT(cases,3)
   ,cases = LEFT(cases,10)


Table structure for voting app I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on two different days or something), but I'm not sure what the most efficient way is to do this.

A simple solution is to have a table with 3 fields: voter (the user #), winner (the profile # who won), and loser (the profile # who lost). not necessarily gonna use those names, but I do want to use the data on who won/lost.

Is it horribly inefficient and stupid to send a request for every new matchup to search that table to see if the voter/votees have already been processed? If not, what are my options? Is it really that huge of a deal? Or should I instead allow repeat matchups and simply update the table fields accordingly if the voter's choice changes?

Here's how I would model the tables for that requirement (note the table and column names can be changed for whatever is appropriate for your application):

In this model, the idea of a "Profile" and "User" is contained in the same entity, "User". The idea of a single vote for one of two user candidates is contained in the entity "Vote". If you want to restrict a User to one Vote per set of two users, then you can add a unique index in Vote for idUser, idUserCandidate1, and idUserCandidate2.


SSPI error on windows authenticated remote access only We are trying to figure out what is wrong with our server. Right now i have many accounts that are sql accounts and windows authenticated (domain accounts).

When i connect with ANY windows authenticated account i get an error

  Cannot generate SSPI context

The account is good. I have also been trying to connect simply using Management Studio and i get the same error. If i use any SQL accounts it works and all windows authenticated account fails.

That being said, if i do a remote desktop to the SQL server itself and try using Management Studio on the server itself, all Windows authenticated account works without error.

The error only shows for remote computer (same domain / ip range) when using windows authenticated connections.

i have found this but it doesn't fix anything. According to the doc no accounts should work if we are getting this error.

Now what changed, the only thing that change is that the server was a virtual machine on VMWare, there was a conversion done to switch it to hypervisor and the instance has been restarted.

Anyone else would know when SSPI error can show based of where the authentication request was called ? i haven't been able to find anything. Could possibly be a bad error. Our servers have been down for the whole weekend we still haven't found anyone with the same issue.

Important notes :
- PC that remote and connect to SQL are in the same domain, same physical location, plugged in the same rack mount network wise.
- Running using IP or DNS doesn't change anything.
- no error logs show up in SQL and neither in Event Viewer on the server

We finally found how to fix the issue. Unfortunately we do not know why it fixes it as the old VMWare image have the same setting it has on the Hyper-V image. Both suddenly stopped working.

To fix it we had to change the SQL server instance that has been running on the main Domain admin account for the last 10 years to run on LocalSystem and both SPN were registered under a user account. That user was another domain admin account which was the one originally used when SQL was first installed 10 years ago. That account is still active and used. We removed the SPN from the account and put them directly under the SQL dns.

Note that i don't know that interface where SPN are listed but i understood that it list all computer DNS names and all user account of the domain and SPN can be under either. Both IT have no idea why this suddenly stopped working. According to oldest image backup of the servers those settings never changed but that's all we needed to do to make everything work again.


Group only certain rows with GROUP BY Schema

I have the following set-up in MySQL database:

CREATE TABLE items (
  id SERIAL,
  name VARCHAR(100),
  group_id INT,
  price DECIMAL(10,2),
  KEY items_group_id_idx (group_id),
  PRIMARY KEY (id)
);

INSERT INTO items VALUES
(1, 'Item A', NULL, 10),
(2, 'Item B', NULL, 20),
(3, 'Item C', NULL, 30),
(4, 'Item D', 1,    40),
(5, 'Item E', 2,    50),
(6, 'Item F', 2,    60),
(7, 'Item G', 2,    70);

Problem

I need to select:

All items with group_id that has NULL value, and
One item from each group identified by group_id having the lowest price.

Expected results

+----+--------+----------+-------+
| id | name   | group_id | price |
+----+--------+----------+-------+
|  1 | Item A |     NULL | 10.00 |
|  2 | Item B |     NULL | 20.00 |
|  3 | Item C |     NULL | 30.00 |
|  4 | Item D |        1 | 40.00 |
|  5 | Item E |        2 | 50.00 |
+----+--------+----------+-------+

Possible solution 1

Two queries with UNION ALL:

SELECT id, name, group_id, price FROM items
WHERE group_id IS NULL
UNION ALL
SELECT id, name, MIN(price) FROM items
WHERE group_id IS NOT NULL
GROUP BY group_id;

/* EXPLAIN */
+----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
| id | select_type  | table      | type | possible_keys      | key                | key_len | ref   | rows | Extra                                        |
+----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
|  1 | PRIMARY      | items      | ref  | items_group_id_idx | items_group_id_idx | 5       | const |    3 | Using where                                  |
|  2 | UNION        | items      | ALL  | items_group_id_idx | NULL               | NULL    | NULL  |    7 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL               | NULL               | NULL    | NULL  | NULL |                                              |
+----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+

However it is undesirable to have two queries since there will be more complex condition in WHERE clause and I would need to sort the final results.

Possible solution 2

GROUP BY on expression (reference)

SELECT id, name, group_id, MIN(price) FROM items
GROUP BY CASE WHEN group_id IS NOT NULL THEN group_id ELSE RAND() END;

/* EXPLAIN */
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | items | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

Solution 2 seems to be faster and simple to use but I'm wondering whether there is a better approach in terms of performance.

Update: According to MySQL documentation, this query is illegal in SQL92 and earlier and may work in MySQL only.

First, do not use either of your two queries. Both have a group by some column (GROUP BY group_id) and then select other columns, non-aggregated (SELECT id, name). This may give you wrong and unexpected results, despite that it may work in your tests, with some small sized table.

Second, the UNION ALL is not a problem. If the two subqueries perform efficiently, then the final union is ok, too. If you need a sort, the efficiency will depend on how that sort differs from the indexes used.

Now, the problem of "groupwise-max" or "greatest-n-per-group" has many solutions (and even a tag, both at SO and here). There are two sub-problems, depending on whether ties can happen and what the wanted results are in those cases.

If you want all the tied rows, the solution with GROUP BY inside a derived table is usually good. In your case, that you want just one row returned per group, another approach is easier to write and usually performs very well when there is a small number of group overall:

SELECT id, name, price
FROM items
WHERE group_id IS NULL

UNION ALL

SELECT i.id, i.name, i.price
FROM
    ( SELECT DISTINCT group_id
      FROM items
      WHERE group_id IS NOT NULL
    ) AS di
  JOIN
    items AS i
  ON  i.id =
    ( SELECT id
      FROM items
      WHERE group_id = di.group_id
      ORDER BY price, id             -- order for resolving ties
      LIMIT 1
    )
ORDER BY
    <some_columns> ;                 -- final order

An index on (group_id, price, id) will be helpful


Pre-staging data causes execution plan cost to skyrocket I have a troublesome query that we are trying to tune.  One of our first thoughts was to take a portion of a larger execution plan and store those results to an intermediate temp table and then perform the other operations.

What I'm observing is that, when we prestage the data into a temp table, the execution plan cost goes through the roof (22 -> 1.1k).  Now, this has the benefit of allowing the plan to go parallel, which reduced execution time by 20% but that isn't worth the much higher CPU usage per execution in our case.

We are using SQL Server 2016 SP2 with the legacy CE on.

Original plan (Cost ~20):

https://www.brentozar.com/pastetheplan/?id=ry-QGnkCM

Original SQL:

WITH Object1(Column1, Column2, Column3, Column4, Column5, Column6)
AS
(
    SELECT  Object2.Column1,
            Object2.Column2,
            Object3.Column3,
            Object3.Column4,
            Object3.Column5,
            Object3.Column6
    FROM Object4 AS Object5
    INNER JOIN Object6 AS Object2 ON Object2.Column2 = Object5.Column2 AND Object2.Column7 = 0
    INNER JOIN Object7 AS Object8 ON Object8.Column8 = Object2.Column9 AND Object8.Column7 = 0
    INNER JOIN Object9 AS Object3 ON Object3.Column10 = Object8.Column11 AND Object3.Column7 = 0
    INNER JOIN Object10 AS Object11 ON Object2.Column1 = Object11.Column1
    WHERE   Object8.Column12 IS NULL AND
            Object8.Column13 = Object5.Column13 AND
            Object3.Column3 = Object5.Column3 AND
            Object11.Column14 = Variable1
)
insert Object12
SELECT  Object13.Column2,
        Object13.Column3,
        MIN(Object13.Column4) AS Column15,
        MAX(Object13.Column4) AS Column16,
        COUNT(DISTINCT (CASE WHEN Object13.Column5 = 1 THEN Object13.Column1 END)) AS Column17,
        COUNT(DISTINCT (CASE WHEN Object13.Column6 = 0 THEN Object13.Column1 END)) AS Column18,
        COUNT(DISTINCT Object13.Column1) AS Column19
FROM Object1 AS Object13
GROUP BY Object13.Column2, Object13.Column3 OPTION (RECOMPILE)

New plan (with area highlighted in blue above is pre-staged into a temp table - Cost ~1.1k):

https://www.brentozar.com/pastetheplan/?id=rycqG3JRf

New SQL:

SELECT  Object1.Column1,
        Object1.Column2,
        MIN(Object2.Column3) AS Column4,
        MAX(Object2.Column3) AS Column5,
        COUNT(DISTINCT (CASE WHEN Object2.Column6 = 1 THEN Object1.Column7 END)) AS Column8,
        COUNT(DISTINCT (CASE WHEN Object2.Column9 = 0 THEN Object1.Column7 END)) AS Column10,
        COUNT(DISTINCT Object1.Column7) AS Column11
from Object3 Object1
join Object4 Object2 on Object2.Column12 = Object1.Column13 and Object2.Column2 = Object1.Column2
where Object2.Column14 = 0
GROUP BY Object1.Column1, Object1.Column2 OPTION (RECOMPILE)

Can someone help us understand why the new plan would have such a larger cost?  I'll be happy to provide additional information regarding tables/indexes underneath, if needed.

In the case of the original plan, we do realize that it's doing an insert instead of select.  Even so, the select shouldn't (in our minds) be that much more costly.

This is the actual execution plan.  It's a concern because, due to the immensely higher plan cost, it goes parallel.  Therefore using higher CPU.  Also, we're just curious as to why the plan cost goes up that much for something like pre-staging the data, which usually will get you close to, if not better, than the original cost.

The temp table is indexed in the second query as a composite clustered PK on Object1.Column13 and Object1.Column2.  This matches the columns (and order) of Object4.  Adding a MAXDOP hint is an option, but this is also an academic exercise of 'why in the world does cost go up that much'?

Adding OPTION (ORDER GROUP) to the second query results in no change, same operators/costs.

NOTES:

Object9 in the first query is the same object as Object4 in the second.

Costs are based on estimates, even in "actual plans". You cannot compare two query plans side by side and conclude that one of them will require more CPU to execute based on operator or total plan costs alone. I can create a query with a cost in the millions that executes in one second. I can also create a query with a tiny cost that will effectively take forever to execute. For your case, the first query has a cost of only 22 optimizer units because of a poor cardinality estimate after the hash join:

The operators in red execute millions of times, but the query optimizer expects them to only execute a few thousand times. Costs, which are based on estimates, won't reflect that work. The operator in blue is a table spool for which the cardinality estimator expects to insert a single row. It instead inserts a few million. As a result, the operators in black (along with a few others not shown) are inefficient and spill to tempdb.

With the other plan, you put a significant number of rows into tempdb and as a result the cardinality estimate is more reasonable, though it's still not ideal:

The query optimizer expects to need to process many more rows and as a result the query plan has a higher cost. As a very general rule of thumb you may see improved performance with improved estimates, but it doesn't always work out how you'd like. Looking at the plan with a temp table I see a few areas of possible improvement:

Load the full CTE from the original query into the temp table. A query with multiple distinct aggregates can be tough to optimize. Sometimes you'll get a query plan where all of the data is loaded into a spool (into tempdb) and some of the aggregates are applied separately to the spool. All of that work is always done in a serial zone in my experience. If you eliminate all of the joins in the query I believe that you won't get that optimization. The aggregates will just be applied to the temp table. That will save you the work of writing out nearly the same data to tempdb and the entire plan should be eligible for parallelism.
Define the temp table as a heap and write to it with TABLOCK. It looks like right now you have a clustered index which means you aren't eligible for parallel insert.
Consider making the query eligible for batch mode by using one of these tricks. Batch mode aggregates can be significantly more efficient with multiple distinct aggregates.

I would expect some combination of those steps to significantly improve runtime. Please note that I did a quick analysis partially because anonymized plans are difficult to interpret.