texts: Verification that my SSRS subscription was emailed to a valid address

texts:
I would like to know if it is possible to verify that an SSRS subscription that was set up to send an email sent it to a real address.  That is, if I put in an email address that is not valid, can I find out that an undeliverable message was sent back from the destination server?

I tested this with an email address that does return an undeliverable message when I emailed to it from Outlook.  After the subscription ran, its status simply said Mail sent to abc@abc.com.

OUR SOLUTION: We set up a shared Outlook account with a generic email address.  Those of us who have access to create subscriptions have access to this email account.  We then modified the RSReportServer.config file on the ReportServer to send emails from this new generic address.  Now, any time a subscription is set up to send to an incorrect email address, we can see the delivery failure in the shared Outlook inbox.  Woo hoo!


texts: Sql Server 2008 : What is the cost of accessing an index?

texts:
I've just read this interesting blog post about selectivity notion. In the section 'When is better to not use a database index?', the author states :

  Accessing the index takes time, and consumes resources. If we are
  accessing the index 5,000 times, it is actually faster to just
  directly access the table and do a full table scan.

which ressources the author is speaking about here ? shoul I bother about this ? or it's up to the RDBMS to manage all this for me ?


texts: Does my associated ASM Instance have any play in Re-naming my SID?

texts:
Greetings DBA Experts,

I have changed my Oracle SID in the past by updating the init.ora file, and updating
my environment (vars, tns, lintener, etc..), yet not since we moved to 11g with ASM.
I come from an app development team, and when I ask our system-level DBA's the best way to do this, they just reply with "Why would you want to do that?".
Is there anything extra or different I need to do in renaming a SID with respect to our ASM instance?
Thanks very much!


texts: psql vs jdbc cannot reproduce immutable function "caching" via psql


texts: I have accidently deleted ibdata1 on an ubuntu server

texts:
I have accidently deleted the 'ibdata1' file on the server having innodb, Is there any way I can recover it?

the mysql service has restarted since the deletion.


texts:
I have a problem with this query:

SELECT *
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID--- adventureworks db

  Warnings

  Type conversion in expression
  (CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect
  "CardinalityEstimate" in query plan choice


texts: SQL Server - Add signature to stored procedure by certificate does not work

texts:
I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2

To avoid granting calling login/user permissions to do table updates, I try to use code signing

Created certificate in the master database, and created login from this certificate
Then I've created users for this certificate-mapped login, in Database 1 and Database 2, and granted them permissions to do table updates

When I try to run below t-sql:

use [Database 1]
add signature to MyProcedure
by certificate MyCertificate

It throws:

  Msg 15151, Level 16, State 1, Line 168
  Cannot find the certificate 'MyCertificate', because it does not exist or you do not have permission.

Then I try

use [master]
add signature to [Database1].[dbo].MyProcedure
by certificate MyCertificate

I am in a sysadmin server role, but it throws:

  Msg 15151, Level 16, State 1, Line 168
  Cannot alter the object 'Database1.dbo.MyProcedure', because it does not exist or you do not have permission.

Question:

How can I properly add signature to my stored procedure in Database 1, by certificate that is in the master database ?


texts: SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?

texts:
I encountered a weird SQL Server Service Broker consistency problem, namely I have a queue that holds messages (status=1, Ready to receive) which are lying there for a very long time.

In the meantime there was an upgrade from SQL 2005 to SQL 2012 and now when I want to do something with these messages (e.g. END CONVERSATION, even with CLEANUP) I get an error:

  The conversation handle "......." is not found.

So I tried to look for this handle in sys.conversation_endpoints, but to my surprise it's not there. This shouldn't have happened, as far as I know if there are messages in the queue with associated conversation_group_id and conversation_handle, there should be an endpoint for each one.

I ended up running DBCC CHECKDB and got messages such as:

  Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 9708, State 1:
  The messages in the queue with ID some_number are referencing the
  invalid conversation group 'some_guid'.

  Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 9705, State 1:
  The messages in the queue with ID some_number are referencing the
  invalid conversation handle 'some_guid'.

My question is: how could this happen? Are there any potential DEV/DBA errors or gotchas e.g. while performing SQL upgrade, that might lead to such corruption?


texts: Refreshing Materialized view creates many child cursors

texts:
Doing some tests, I noticed that having REFRESH FAST ON COMMIT materialized view and flashbacks enabled cause Oracle to create many child cursors in some cases. For example,

CREATE TABLE table1 (table1_id INT NOT NULL PRIMARY KEY, val1 VARCHAR2(20));

CREATE TABLE table2 (table2_id INT NOT NULL PRIMARY KEY,
table1_id INT NOT NULL ,
val2 VARCHAR2(20),
CONSTRAINT FK_table2_table1_id FOREIGN KEY (table1_id)
REFERENCES table1(table1_id)
);

--index on FK column omitted for simplicity

CREATE MATERIALIZED VIEW LOG ON table1 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON table2 WITH ROWID;

CREATE MATERIALIZED VIEW mv_test REFRESH FAST ON COMMIT AS
SELECT A.table1_id, b.table2_id, A.val1, b.val2, a.rowid as rid1, b.rowid as rid2
FROM
table1 A,table2 b
WHERE (b.table1_id = a.table1_id);

INSERT INTO table1 (table1_id,val1)
values (1,'test');

INSERT INTO table2 (table2_id,table1_id,val2)
VALUES (1,1,'test2');
COMMIT;

UPDATE table2 SET val2 = 'test3' WHERE table2_id = 1;
COMMIT;
SELECT * FROM v$sqlarea WHERE sql_text like upper('%mv_test%');
-- find sql_id used to populate data into mview,
-- something starting with /* MV_REFRESH (INS) */,
-- version count = 1

UPDATE table2 SET val2 = 'test4' WHERE table2_id = 1;
COMMIT;
-- check version count in v$sqlarea, it got increased by 2, now it's 3
SELECT * FROM v$sql_shared_cursor WHERE sql_id = :sql_id_from_prev_steps
-- it shows 'Y' in FLASHBACK_CURSOR

From now each update(followed by commit) that hits underlying tables (or at least columns that are part of materialized view definition) ends up creating a new child cursor.  As far as I understand many child cursors means among other things high usage of sharable memory.

Can it be an issue in real-world scenario even in case of relatively small number of dml against underlying tables? If yes, what is the right way to deal with such cases ?


texts:
I will have to do this process in EXCEL first before it is made into a database. I will have many files, but there is about 7 - 9 files that work all together as one e.g.

X192AD1-test.docx
X192AD1-test.avi
X192AD1-test.mpg
X192AD1-test-small.jpg
X192AD1-test-preview.jpg
X192AD1-test-pdf

All the above works as ONE file.

I do not want to use a numbering system, e.g. 00001, 00002, 00003 because people can determine the age of the files, 00001 is older than 000032.

Can anyone give me some advice regarding this because of course I cannot repeat the primary key and I am not sure how to approach this.


texts: Is there a way to view the SQL Server errorlogs in Amazon's RDS?  I usually use sp_ReadErrorlog, but in RDS we need to be a member of securityadmin role.


texts: How can you tell if a table in DB2 has a Primary Key?

texts:
We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined.

Is there a way using a select statement on the DB to see if a given table has one?

Thanks.


texts: Using CoreData on a client-side application

texts:
This may be a bit more of an SO question, but...

If I'm writing a client-side application for OS X that's going to sync with an online MySQL database, is there any difference on if I have CD use SQLite or XML?


texts: How to create a trigger in oracle to prevent overlapping date ranges

texts:
I'm learning Oracle and I had a problem.

Chat table:

CREATE TABLE training
(
   id_training      NUMBER,
   id_user          NUMBER,
   start_training   DATE,
   end_training     DATE
);

I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.

I was thinking of using :old and :new in the trigger, but if I use an insert in my trigger the data of :old always will be null.

How can I overcome this problem of changing :old values, if I'm inserting new values inside my trigger?


texts: Optimize PostgreSQL server setting for extremely wide tables

texts:
We do have a dedicated server where only PostgreSQL 9.4.4 server is running, 2 physical cores, 16 logical cores, 128GB RAM.

On this server we have quite atypical data - there are several tables with approx. 500 columns each, ranging from .5 - 2 milion rows. All columns are either of smallint or integer data type. The typical operations we do:

perform some aggregation function on one column, grouped by another column (this may well be also some window function)
read subset of variables (e.g. 20) and all rows
transform entire table into new table (typically there are no joins to other tables, these are mostly simple case when then end statements

There are two concurrent users at maximum. There is a primary key (single column), but this is almost never used in queries. There are no additional table constraints or indexes.

What would be the recommend configuration of PostgreSQL server? Increasing shared_buffers to 60GB, work_mem to 10GB? Anything else?

Note: Using such strange tables is not bad design in this case. We really have that many attributes for one case. We use this data as an input to in-database machine learning (MADlib) and as data pre-processing storage before using other statistical tools.


texts: Postgres Replication From Different Masters?

texts:
I'm getting a Postgres 9.3.3 install sorted out. My application is Drupal (so read/write), but low-traffic, so all application traffic is sent to the primary master. I have two sites (main and secondary) with two machines in each (again, main and secondary). let's say machines 1 and 2 are in the main site, with machines 3 and 4 in the secondary site.

Right now, 1 is the master, and 2,3 and 4 are all hot_standby slaves of it (the master has a DNS CNAME, which is in the recovery.conf files). I've been told that if I wanted to flip between sites, I'd need to promote 3 as the master (by touching the /tmp file), and then can update the DNS to point to machine 3 instead of machine 1. I can then restart each of the slaves and they'll magically start replicating from 3 instead of 1.

This sort of strategy absolutely won't work with MySQL, but can it work with Postgres? If needs be, I can stop Drupal from accessing the database while I do this sort of work, to avoid any writes being made. Could it work then?

Is there another strategy I should be using to make changing sites (or machines in a site) relatively easy? Could cascading replication from 1 -> 2 -> 3 -> 4 maybe make more sense? I could then promote 3 (and have 4 as a slave of it), but then have to rebuild 1 and 2 to become slaves of 4.


texts:
What is the best configuration for pgBouncer in my case?

I have only 1 database, 1 user and the max_connections in postgresql.conf is 1024.

The currently config of my pgbouncer.config is:

max_client_conn = 1024
default_pool_size = 1024
min_pool_size = 32
;max_db_connections = 50
;max_user_connections = 50


texts: Create new user without typing current user password

texts:
On a VPS, I need to create a new user on PostgreSQL 9.3 so I have tried

sudo -u postgres psql -c "create user new_user with password 'secret';"

This works but it asks me for the current user (deploy) password before proceeding and since I'm running it from a Capistrano task the shall is not interactive so I cannot input the password.

Is there a way to create a new PostgreSQL user without inputting the current user password?

Thanks and have a nice day.


texts: AWS performance of RDS with provisioned IOPS vs EC2

texts: Has anyone done a performance comparison of AWS RDS with the new provisioned IOPS vs EC2? I've found plenty of non-high IOPS RDS vs EC2 but nothing with the new high IOPS feature in RDS.


texts: MySQL: can not get rid of “Using filesort” in a simple query


texts: couchbase cbrestore fails to transfer data

texts:
cbrestore runs without error messages, but does not transfer any data.

The problem occurs on debian 10 with couchbase versions 6.0.0, 6.0.1, 6.0.3.
The exactly same command runs without problems on windows 10 with couchbase version 6.0.1 - with the same dump.


texts:
I have a query that is taking too long to run.
I executed set statistics io on and set statistics time on, and I noticed that Scan count and logical reads are very high

Table 'TABLE_01'. Scan count 6497248, logical reads 26065220, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
SQL Server parse and compile time:     CPU time = 20 ms, elapsed time
= 20 ms.
(138 row(s) affected)
SQL Server Execution Times:    CPU time = 28969 ms,  elapsed time =
30559 ms.

Here's the query:
SELECT Object1.Column1 AS Column2,
       Object1.Column3          AS Column4,
       Object1.Column5,
       Object1.Column6,
       Object1.Column7             AS Column8,
       Object2.Column9                      AS Column10,
       Cast(Object2.Column11 AS VARCHAR)  AS Column12,
       Object2.Column13              AS Column14,
       Object2.Column15                     AS Column16,
       Object2.Column17                   AS Column18,
       Object2.Column19                     AS Column20,
       Object3.Column21                                  AS Column22,
       Object3.Column5                                   AS Column23,
       Object2.Column24              AS Column25,
       Object4.Column21                                 AS Column26,
       Object4.Column5                                  AS Column27,
       Object5.Column21                            AS Column28,
       Object5.Column29                        AS
       Column30,
       Object6.Column31                  AS Column32
FROM   Object1
       INNER JOIN (Object2
                   LEFT JOIN Object7 Object3
                          ON Object2.Column19 = Object3.Column19
                   INNER JOIN Object7 Object4
                           ON Object2.Column24 = Object4.Column19
                   INNER JOIN Object5
                           ON Object2.Column13 = Object5.Column15
                   INNER JOIN Object6
                           ON Object2.Column9 = Object6.Column9)
               ON Object1.Column3 = Object2.Column3
WHERE  Object1.Column3 IN (
   43796020, 43795933, 43795931, 43795681,
       43795672, 43793093, 43793090, 43793085,
       43790358, 43790342, 43789167, 43789155,
       43789154, 43788970, 43788928, 43788924,
       43788776, 43788774, 43788712, 43788699,
       43788674, 43788673, 43788626, 43788625,
       43788227, 43788219, 43787557, 43787530,
       43787088, 43787075, 43787063, 43787041,
       43787002, 43786368, 43786351, 43786244,
       43786243, 43786223, 43786222, 43786151,
       43417954, 43417953, 43417929, 43417294,
       43417293, 43417286, 43416500, 43416334,
       43416304, 43416288, 43416089, 43416034,
       43416016, 43416015, 43416014, 43416013,
       43416012, 43416011, 43416010, 43416009,
       43416008, 43416007, 43415753, 43401569,
       43400701, 41524028, 41316604, 41311555,
       41309174, 41308146, 41308136, 41286794,
       41281375, 41281374, 41281294, 41263751,
       41263749, 40960597, 38574969, 38133930,
       38133868, 38133516, 38132482, 37630401,
       37630400, 37630399, 37630398, 37630397,
       37630396, 37630395, 37629851, 37629846,
       37301414, 37298436, 37298433, 37298431,
       37298429, 37298427, 37298424, 37298398
       )
ORDER  BY Object1.Column1 ASC

I would like to know how I can improve this query
The estimated execution plan and the actual execution plan


texts: SQL Server 2008 R2: (OUTER APPLY) Why is SQL scanning all rows instead of the rows I specify?

texts:
I have a reasonably large ETL table (170M rows, 40GB).

The clustered index is the EVENT_DTTM.

Every day a new batch of data is inserted and processed. Part of the processing is updating each row (only the newly inserted ones), based on other rows in the table. (e.g. compare the datetime of each row to the row before it and calculate "event time"; detect row with malformed data and use data from nearby rows to correct it.)

I'm using an OUTER APPLY (top 1) structure to do this. Basically, something of the form:

select *, event_duration = datediff(ss, alias.EVENT_DTTM, TABLE.EVENT_DTTM)
from TABLE
outer apply
  (select top 1 EVENT_DTTM
   from TABLE alias
   where alias.EVENT_DTTM >= @min_date
   and alias.EVENT_DTTM < @max_date
   and alias.ETC = TABLE.ETC) alias
where TABLE.EVENT_DTTM >= @min_date
and TABLE.EVENT_DTTM < @max_date

My problem is that SQL appears to be scanning the entire table for the OUTER APPLY, instead of just the range between @min_date and @max_date.

Here is an estimated query plan for the same query but with different parameters. The query does not run in parallel but the index spool is present.

Does anyone have an idea why this might be, and how to fix it?

I've tried building a CTE limited to the given date range, and then updating that. I've tried building a dynamic SQL string using literals for the dates. Neither of these has worked.

The only thing that I've found that works is to build a filtered index on the date range. SQL will then use the filtered index for the OUTER APPLY. This isn't very elegant and writing out the index costs a lot of time.

Unfortunately, I don't have time to implement and kind of partitioning scheme at this time.

For the sake of completeness, here is an example of my actual code:

update fact_cabinet_event_base
set assoc_event_device_id = assoc_event.device_id,
assoc_event_num = assoc_event.event_num
from fact_cabinet_event_base
outer apply
  (select top 1 device_id, event_num
   from fact_cabinet_event_base assoc_event
   where assoc_event.event_datetime >= dateadd(dd, -3, @populate_min_date)
   and assoc_event.event_datetime < @populate_max_date
   and assoc_event.event_type = 'dispense'
   and assoc_event.facility_applix_number = fact_cabinet_event_base.facility_applix_number
   and assoc_event.idr = fact_cabinet_event_base.idr
   and assoc_event.idri = fact_cabinet_event_base.idri
   and assoc_event.idria = fact_cabinet_event_base.idria
   and assoc_event.event_datetime < fact_cabinet_event_base.event_datetime
   order by assoc_event.event_datetime desc) assoc_event
where fact_cabinet_event_base.event_datetime >= @populate_min_date
and fact_cabinet_event_base.event_datetime < @populate_max_date
and (fact_cabinet_event_base.event_type = 'waste'
     or (fact_cabinet_event_base.event_type = 'return' and fact_cabinet_event_base.event_subtype <> 'return to pharmacy'))
and (fact_cabinet_event_base.assoc_event_num is NULL or fact_cabinet_event_base.assoc_event_num = 0)


texts: How do you select "array"-type columns via the DBLINK function?

texts:
Using PostgreSQL, I can easily do a select statement to return a column that is an array of the datatype point. i.e. colA is the datatype point[]

select colA from tbl

Returns a column that contains an array of points:

+-----------------------------+
|            colA             |
+-----------------------------+
| (180.0,90.0),(-180.0,-90.0) |
| (64.0,38.0),(-26.0,-40.0)   |
| etc                         |
+-----------------------------+

I'm certain the column is of type point[], because select colA[0] returns as expected.

I'm trying to do this query from another server using DBLINK(). This is the query:

select
colA
from dblink(
  'my_conn',
  'select
   colA
   from public.tbl'
) as s (
  colA point[]
)

This query fails with this error message:

  SQL Error [22P02]: ERROR: malformed array literal: "(180,90),(-180,-90)"
    Detail: Array value must start with "{" or dimension information.

I tested that I am able to select the datatype point using DBLINK. I.e. this query succeeds:

select
colA
from dblink(
  'my_conn',
  'select
   colA[0]
   from public.tbl'
) as s (
  colA point
)

So, my question is How do you select an array via the DBLINK function?.


texts:
I am working on tidying up our log back up Jobs (Sql 2008r2 ), which have grown into a bit of a mess!

I am setting up a trn log back up job to a remote folder for each day ( in this example  called Monday).
I  then plan to set a backup device to each folder with a filename of [DAYNAME]logs.trn eg Mondaylogs.trn

My plan is that the maintenance job will back up 5 production databases ( database A B C D E) log files every 15 minutes to the back up device file Mondaylogs.trn

The one thing I just want to confirm is, if I want to restore to a point in time on a Monday for  just one of the database  (let us say database B) I will be to achieve this from the single file Mondaylogs.trn?

If I am incorrect do I have to set up devices MondaylogsA.trn MondaylogsB.trn and so on?


texts: In Postgres, how do I adjust the "pgsql_tmp" setting?

texts:
I"m using Postgres 9.5.4 on Ubuntu 14.04.  I have my Postgres data on a separate disk partiion, which is getting pretty full.

myuser@myproject:~$ df -h /mnt/volume-nyc1-01/
Filesystem Size Used Avail Use% Mounted on
/dev/sda 99G 93G 413M 100% /mnt/volume-nyc1-01

I want to delete some data from my partition, but this has become challenging.  In particular, when I run some queries, i get results like this

myproject_production=> select count(*) FROM my_object_times rt1, my_object_times rt2 where rt1.my_object_id = rt2.my_object_id and rt1.name = rt2.name and rt1.time_in_ms = rt2.time_in_ms and rt1.id > rt2.id;;
ERROR:  could not write block 52782 of temporary file: No space left on device

I want to free up some temp space so I can run queries and identify what data I need to delete.  I have some other free space on another partition. How do I point my pgsql_tmp variable there so that I can run the queries I need?

Edit:

As the symlink option seemed to be the least invasive, I gave it a go, setting up things like this

myuser@myproject:~$ sudo ls -al /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp
lrwxrwxrwx 1 root root 14 Apr 10 18:01 /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp -> /opt/pgsql_tmp
myuser@myproject:~$ cd /opt
myuser@myproject:/opt$ df -h /
Filesystem                                              Size  Used Avail Use% Mounted on
/dev/disk/by-uuid/050e1e34-39e6-4072-a03e-ae0bf90ba13a   40G   24G   15G  62% /

You can see that I have over 16GB available on the partition where I pointed it to, but still I get the errors

ERROR:  could not write block 1862514 of temporary file: No space left on device


texts: master-master replication or master slave replication?

texts:
We have right now one server with a database.
A device and a website are accessing that database causing load

What I want is to create two servers master-slave where master databases are replicated to slave but slave have some databases that must not be in master database.

Is it possible to do it using master-slave replication or using master-master replication?


texts: Can't change authentication mode from Windows to both SQL Server and Windows

texts:
As the title suggests I'm having problems changing the authentication mode from Windows to both SQL Server and Windows mode in version 12.0.2. I have tried right right-clicking the server, go to security and change it there but I'm getting an error when doing this. I installed it originally using both but changed it to windows only mode earlier this evening.

The error message I'm getting is long winded but the end of it is:

  The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229)

Any idea what that means? I'm guessing I don't have the required permission but I'm not familiar enough with it to know what to do next. I have the sa password but can't enter it while I'm stuck in Windows mode.

I've been trying to figure out why I can't seem to write to the database all day and in desperation tried changing the mode to see if that would fix it... but it's only made things worse.

While connected using Windows credentials I tried ALTER LOGIN sa ENABLE also and was given the following error message in response:

  Cannot alter the login 'sa', because it does not exist or you do not have permission.

I did try altering the registry too to change the mode but realised I was reading a post relating to SQL Server 2005

Thanks in advance for your help.


texts:
Can you please help me out with the below query?

I need to extract from column VAL_DT only items with date older than today's date but this rule should not apply to items that contain the following wordings in column MVT_DS: "NOSTRO", "SCREV", "SCRIN", "MAN".

here is my attempt

SELECT * FROM table_name
WHERE VAL_DT < CURRENT DATE  IF MVT_DS <> 'NOSTRO' AND MVT_DS <> 'SCREV' AND MVT_DS <> 'SCRIN' AND MVT_DS <> 'MAN'


texts: Connect tables in a cube based on historical data

texts:
So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me.

Imagine a setup described by the following:

CREATE TABLE [dbo].[Orders] (
    OrderNumber int,
    OrderDate smalldatetime,
    ProductNumber int,
    UnitPrice money,
    Qty int
)
CREATE TABLE [dbo].[Products] (
    ProductNumber int,
    ProductName varchar(30),
    CreatedDate smalldatetime,
    EndDate smalldatetime NULL
)

INSERT INTO Products VALUES (1,'My Product','1/1/2011','5/5/2011')
INSERT INTO Products VALUES (1,'My New Product','5/6/2011',NULL)
INSERT INTO Orders VALUES (1,'1/3/2011',1,1)
INSERT INTO Orders VALUES (2,'6/6/2011',1,1)

SELECT * FROM Orders o INNER JOIN Products p ON o.ProductNumber = p.ProductNumber AND o.OrderDate BETWEEN p.CreatedDate and p.EndDate

As you can see, my product numbers get reused and I need to be able to see what the product was at the time the order was placed. Easy enough to do with SQL, but I'm having trouble trying to build a cube in SSAS that takes it into consideration.

Any help would be appreciated.


texts:
How can I convert database from PostgreSQL to MySQL?

I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error:

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the right
  syntax to use near 'bigserial NOT NULL,
    created timestamp NOT NULL,
    sql0 varchar(250) DEFAUL' at line 2


texts: Restore or reset Oracle database (No Flashback features)

texts:
I am running

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Seem it not Enterprise edition, so I not able to use flashback features

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

SQL> SELECT flashback_on, log_mode FROM v$database;
FLASHBACK_ON |LOG_MODE
-------------|-------------
NO           |NOARCHIVELOG

As you see my database are running with NOARCHIVELOG mode and my filesystem is ext3

I had to do CRUD test with my system + another vendor. and after that I want to revert back  to the state of "restore point" (before testing). In short word, I don't want to keep test data inside my database.

There is discussion here, but this is using flashback features. But how about for my case?

Is there any solution suitable for me?


texts: How to allow creation and deletion of a database or collections with user and password

texts:
I created a user with the role of userAdminAnyDatabase

db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdminAnyDatabase", db: "admin"}]})

and also user with the role of userAdmin for a single db in the mongo shell.

db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdmin", db: "myDB"}]})

But when I create or delete collections in myDB it's not asking me for the user name or password. What is the point of creating a user then?

I want the creation and deletion of databases and collections only when I give in the admin credentials.


texts: Create trigger that executes maximum one time per day

texts:
I have a table big_table with a large number of daily INSERTs, and this table has a column interesting_val. For the first row, and first row only, which is inserted after 00:00 UTC on each date, I want to SELECT interesting_val and INSERT to another table daily_interesting_val. Is this behavior something I can achieve with a combination of procedures and triggers?

Whenever big_table has at least one row created after 00:00 UTC, but daily_interesting_val does not have a corresponding row with the new interesting_val, my application is in an illegal state, and I want to make this time window as small as possible.

One solution I can imagine, but don't if is possible in MySQL:

Create a scheduled procedure that creates a trigger at 00:00 each day. This trigger calls a procedure which (1) checks existence of row, (2) if not exists, insert row, (3) if exists, delete trigger.


texts:
I am tuning some indexes and seeing some issues would like to take your advice

On 1 table there are 3 indexes

dbo.Address.IX_Address_ProfileId
[1 KEY] ProfileId {int 4}
Reads: 0 Writes:10,519

dbo.Address.IX_Address
[2 KEYS] ProfileId {int 4}, InstanceId {int 4}
Reads: 0 Writes:10,523

dbo.Address.IX_Address_profile_instance_addresstype
[3 KEYS] ProfileId {int 4}, InstanceId {int 4}, AddressType {int 4}
Reads: 149677 (53,247 seek) Writes:10,523

1- Do i really need the first 2 indexes, or should i drop them?

2-  there are queries  running that use  condition where profileid = xxxx and other use condition where profileid = xxxx and InstanceID=xxxxxx. Why the
    optimizer  choose the 3rd index not the 1st or 2nd?

Also i am running a query that get the Lock wait on each index. If i am getting these counts, what should i do to tune this index?

Row lock waits: 484; total duration: 59 minutes; avg duration: 7 seconds;
Page lock waits: 5; total duration: 11 seconds; avg duration: 2 seconds;
Lock escalation attempts: 36,949; Actual Escalations: 0.

table structure is

TABLE [dbo].[Address](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressType] [int] NULL,
[isPreferredAddress] [bit] NULL,
[StreetAddress1] [nvarchar](255) NULL,
[StreetAddress2] [nvarchar](255) NULL,
[City] [nvarchar](50) NULL,
[State_Id] [int] NOT NULL,
[Zip] [varchar](20) NULL,
[Country_Id] [int] NOT NULL,
[CurrentUntil] [date] NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[ProfileId] [int] NOT NULL,
[InstanceId] [int] NOT NULL,
[County_id] [int] NULL,
 CONSTRAINT [PK__Address__3214EC075E4BE276] PRIMARY KEY CLUSTERED
(
   [Id] ASC
 )

this is an example (this query created by hibernate so looks strange)

(@P0 bigint)select addresses0_.ProfileId as Profile15_109_1_
, addresses0_.Id as Id1_20_1_
, addresses0_.Id as Id1_20_0_
, addresses0_.AddressType as AddressT2_20_0_
, addresses0_.City as City3_20_0_
, addresses0_.Country_Id as Country_4_20_0_
, addresses0_.County_id as County_i5_20_0_
, addresses0_.CreatedDate as CreatedD6_20_0_
, addresses0_.CurrentUntil as CurrentU7_20_0_
, addresses0_.InstanceId as Instance8_20_0_
, addresses0_.isPreferredAddress as isPrefer9_20_0_
, addresses0_.ProfileId as Profile15_20_0_
, addresses0_.State_Id as State_I10_20_0_
, addresses0_.StreetAddress1 as StreetA11_20_0_
, addresses0_.StreetAddress2 as StreetA12_20_0_
, addresses0_.UpdatedDate as Updated13_20_0_
, addresses0_.Zip as Zip14_20_0_
from dbo.Address addresses0_
where addresses0_.ProfileId=@P0

(@P0 bigint,@P1 bigint)
select addressdmo0_.Id as Id1_20_
, addressdmo0_.AddressType as AddressT2_20_
, addressdmo0_.City as City3_20_
, addressdmo0_.Country_Id as Country_4_20_
, addressdmo0_.County_id as County_i5_20_
, addressdmo0_.CreatedDate as CreatedD6_20_
, addressdmo0_.CurrentUntil as CurrentU7_20_
, addressdmo0_.InstanceId as Instance8_20_
, addressdmo0_.isPreferredAddress as isPrefer9_20_
, addressdmo0_.ProfileId as Profile15_20_
, addressdmo0_.State_Id as State_I10_20_
, addressdmo0_.StreetAddress1 as StreetA11_20_
, addressdmo0_.StreetAddress2 as StreetA12_20_
, addressdmo0_.UpdatedDate as Updated13_20_
, addressdmo0_.Zip as Zip14_20_
from dbo.Address addressdmo0_
left outer join dbo.Profile profiledmo1_
on addressdmo0_.ProfileId=profiledmo1_.Id
where profiledmo1_.Id=@P0 and addressdmo0_.InstanceId=@P1


texts:
I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables.

At the moment I have one filegroup for data (where most of the data resides) and one filegroup for non-clustered Indexes (I have been slowly migrating indexes across to this filegroup)

The data filegroup and non-clustered indexes filegroup are located on different disks.

The individual tables will have primary keys and non-clustered Indexes added to them. Where should I put these? (In terms of filegroups)  Should I create filegroups for each table, for each type of index etc?

Note: I can't use Data Partitioning as we don't have Enterprise edition.


texts: Both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON

texts:
I inherited a SQL Server 2012 instance that hosts Hyperion databases. I believe the Hyperion techs configured the system and they have both READ_COMMITTED_SNAPSHOT ON and ALLOW_SNAPSHOT_ISOLATION ON at the same time. This doesn't make sense to me. Does it make sense?

Also I am seeing 60-70% of the waits are Lock_M_X, which is much higher than i have ever seen. Wondering if this might be related to the above.

And I have been running 1 minute sp_whoisactive snapshots into a table and have not been able to capture a single X type lock. A reasonable number of S type locks show up.

Any suggestions?


texts: SQL Server Distributed Transactions: xp_sqljdbc_xa_commit vs xp_sqljdbc_xa_end

texts:
In SQL Server 2019, can anyone definitively tell me the functional difference between xp_sqljdbc_xa_commit & xp_sqljdbc_xa_end?
My guess is that commit is attempting to commit the transaction (thank you Mr. Obvious ;-) ) and end is telling DTC "hey, all done here. Please clean up."
But, the code appears to live in SQLJDBC_XA.dll (as extended sp) so I can't view it, and I can't find a good reference to confirm my assumptions.
Thanks!


texts:
I have to design and implement a database on the study course. I want to make sure that my scheme is correct. I've created a model company and a system which needs a database.

A little background about the database: The database is designed for the company who test applications for external clients. Let's say that the company offers performing acceptance and compatibility testing services. The workflow of the company:

Seller (Client) requests for testing his application
The ticket is created
Ticket contains information about the application and what should be tested
Test Manager decides which Tester should test the application (ticket)
Tester reports defects to the application
When testing is done, tester set a result

Other businesses requirements:

Seller can requests for testing the application more than one time
Only one tester can test the application (ticket) in one iteration
History of testing should be collected and available

The updated model:

I'm not good into database designing, it's my first bigger project. So forgive me a basic mistakes :) If the diagram is not readable so I'd be glad to hear some advices how to make it pretty. Also, if someone see some weak points, please comment it.


texts: Parsing JSON data from a text column in Postgres

texts:
I have JSON in a file as follows:
[xyz@innolx20122 ~]$ cat cgs_test.json
{"technology":"AAA","vendor":"XXX","name":"RBNI","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}
{"technology":"AAA","vendor":"XXX","name":"RRCADD","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}

and I have loaded this to one temp table:
create table temp_json (values text);
\copy temp_json from '/home/xyz/cgs_test.json';

Now I want to extract this data into separate columns as:
technology
vendor
name
temporal_unit
regional_unit
dataset_metadata
rk

dataset_metadata is a JSON column and other columns are strings.


texts: How to identify database users and their owned schemas before dropping server logins?

texts:
While deleting logins, there might be a possibility that there are users in databases along with chances that they own schemas in those databases.

Is there a way to find this out with a single select?

This is the warning message I get:

  Deleting server logins does not delete the database users associated
  with the logins.  To complete the process, delete the users in each
  database.  It may be necessary to first transfer the ownership of
  schemas to new users.


texts:
Our team has been tasked with a fairly open ended request for a MongoDB with multiple users that can access and submit documents to collections for a graduate class. The team is all new to MongoDB and are unsure how to handle the user storage side of things.

The internal team discussion is for: a) create a new limited role user in the database level as each user signs up to the service or b) creating a user collection and store users and hashed passwords there.

Does anyone have any advice on which method is better and why?


texts: "Unspecified Error" when executing SSIS Package

texts:
I am executing an "Initial Load" SSIS package, which truncates all ETL tables first, then re-populates them by pulling data from a remote server.  I get the dreaded "Unspecified Error" at a different point each time, and different ETL subflow each time.  Other times, but rarely, everything runs fine.  I have no idea how to get more information or where to even look.

The Server running the package is a Intel Xeon E5-2640 @ 2.50 GHz with 144 GB ram. (HP Proliant DL380p Gen8)
It's running Windows 2012 Server Standard.
Software versions:
SQL Server 2012
Microsoft SQL Server Management Studio 11.0.5058.0
.Net Framework 4.0
Operating System: 6.2.9200

Connection to the remote server is done through OLEDB:
Connection String: Data Source=MyServerURL;User ID=MyDWTransferUser;Initial Catalog=DBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

See screenshot of the failure attached.

Previously, I was receiving "Communication Link Failure" error.  The internet connection was upgraded, and also we installed SP2 on SQL 2012 on this DW server.  Now receiving "Unspecified Error" on this package, after various times of execution, on a different sub process each time.  So it's not an SQL issue, but a configuration issue.

Any tips appreciated.


texts: ORACLE RAC Installation : Why there is a need to create a symbolic links for asm disk

texts:
By reading the note 1538626.1 from MOS Meta Link, I know that we should use udev to change the ownership of some device for the further installation of oracle real application cluster:
I met a lot of similar udev rules for the purpose of changing the owner of asm disk  in internet as follow:

ENV{DM_NAME}=="data01", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="data02", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="data03", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="data04", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="crs01", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="crs02", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"
ENV{DM_NAME}=="crs03", OWNER:="oragrid", GROUP:="asmadmin", MODE:="660", SYMLINK+="iscsi/asm-$env{DM_NAME}"

After the rules is defined, we can restart the udev and get the device with ownership changed

#ls -l /dev/dm*
brw-rw---- 1 root    disk     253,  0 Dec 15 14:18 /dev/dm-0
brw-rw---- 1 root    disk     253,  1 Dec 15 14:18 /dev/dm-1
brw-rw---- 1 root    disk     253, 10 Dec 15 14:18 /dev/dm-10
brw-rw---- 1 oragrid asmadmin 253,  2 Dec 15 14:18 /dev/dm-2
brw-rw---- 1 oragrid asmadmin 253,  3 Dec 15 14:18 /dev/dm-3
brw-rw---- 1 oragrid asmadmin 253,  4 Dec 15 14:18 /dev/dm-4
brw-rw---- 1 oragrid asmadmin 253,  5 Dec 15 14:18 /dev/dm-5
brw-rw---- 1 oragrid asmadmin 253,  7 Dec 15 14:18 /dev/dm-7
brw-rw---- 1 oragrid asmadmin 253,  8 Dec 15 14:18 /dev/dm-8
brw-rw---- 1 oragrid asmadmin 253,  9 Dec 15 14:18 /dev/dm-9

At the same time, because we use the key SYMLINK in the udev rules, we can get the symbolic links which act as alternative names for the device node

#  ls -l /dev/iscsi/*
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-crs01 -> ../dm-5
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-crs02 -> ../dm-7
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-crs03 -> ../dm-8
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data01 -> ../dm-9
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data02 -> ../dm-2
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data03 -> ../dm-3
lrwxrwxrwx 1 root root 7 Dec 15 14:18 /dev/asm/asm-data04 -> ../dm-4

It seems that such SYMLINK has no relationship with the rac installation, why is the purpose of adding the sysmlink property in the udev rules?Can I get rid of sysmlink in the udev rules?


texts: Delete _new table from pt-online-schema-change

texts:
I used pt-online-schema-change but the process died in the middle.

The script makes a copy of the original table with the name followed by "_new".

Since the process died in the middle, the table "_new" wasn't deleted and when I try to delete it my application crashes.

It seems whenever I alter the original table this _new table is also changed so if it is not found it crashes.

I would like to know how can I remove this _new table.


texts: Replicate MySQL 5.0 Master to 5.5 Slave?

texts:
We're planning out a series of server/software upgrades and were curious.

We have a RHEL5 install running MySQL 5.0 that we'd like to replicate to a new (virtualized) server running CentOS 6 and MySQL 5.5. Then, in the future, we would bump the RHEL5 machine to CentOS6 and MySQL5.5. We want to get this replication going first if possible.

So the short question is, has anyone tried replicating a MySQL 5.0 master to a 5.5 slave?


texts: How does lock works when delete rows through secondary index in InnoDB?

texts:
step1: InnoDB Transaction Isolation Level
Repeated Read.
step2: Table schema
CREATE TABLE `follows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `followable_type` varchar(255) DEFAULT NULL,
  `followable_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type_first` (`followable_type`,`followable_id`),
  KEY `id_first` (`followable_id`,`followable_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

step3: prepare test data
insert into follows(followable_type, followable_id) values ("Post", 6);
insert into follows(followable_type, followable_id) values ("Post", 22);
insert into follows(followable_type, followable_id) values ("Post", 28);
insert into follows(followable_type, followable_id) values ("Post", 32);
insert into follows(followable_type, followable_id) values ("Post", 34);

step4: begin two transactions
Session1
session1> begin;
session1> delete from follows where followable_id=28;

Session2
session2> begin;
session2> insert into follows(followable_type, followable_id) value ("Post", 22);
Lock wait timeout exceeded; try restarting transaction

session2>insert into follows(followable_type, followable_id) value ("Post", 28);
Lock wait timeout exceeded; try restarting transaction

session2>insert into follows(followable_type, followable_id) value ("Post", 32);
Query OK, 1 row affected (0.01 sec)

Test Result
MySQL manual claim

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

https://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
Actually, session1 locked this range [22, 32), which consists of

record locks (those index records equal 22)
a next key lock(22, 28]
a gap lock (28, 32)

Meaning of() (]

(): Gap lock is a lock on a gap between index records, I use bracket () to stand for.

(] Next key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. I use (] stand for it.

If manual is right, the result should be (22, 28]. So I really confused by conflict between test result and MySQL manual:

How to explain the conflict?
Could you explain the meaning of DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.


texts: Improving MySQL query performance for a large database

texts:
have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values has others 914 794 items in child table. That just for a month period, for all time the child table has more than 35 975 568 items.

A simple join, like that, filtering by a range date is so slow:

select  count(i.BrandId) from projectitem i
inner join project p on i.ProjectId = p.Id
where (p.Date between "2019-07-01 00:00:00" AND "2019-07-30 23:59:59");

That is the struct of tables:

    'CREATE TABLE `project` (
      `Id` char(36) NOT NULL,
      `Url` varchar(10) DEFAULT NULL,
      `Region` varchar(2) NOT NULL,
      `Area` int(11) NOT NULL,
      `Name` varchar(250) DEFAULT NULL,
      `Description` text,
      `AccountId` char(36) DEFAULT NULL,
      `UserId` char(36) DEFAULT NULL,
      `Date` datetime DEFAULT NULL,
      `ModifiedDate` datetime DEFAULT NULL,
      `DeletedDate` datetime DEFAULT NULL,
      `Deleted` tinyint(4) NOT NULL,
      `Likes` int(11) NOT NULL,
      `Views` int(11) NOT NULL,
      `Private` tinyint(4) NOT NULL,
      `OnlyBudget` tinyint(4) NOT NULL,
      PRIMARY KEY (`Id`),
      KEY `dateproject` (`Date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

'CREATE TABLE `projectitem` (
  `Id` char(36) NOT NULL,
  `BrandId` char(36) DEFAULT NULL,
  `SpecificationId` char(36) NOT NULL,
  `ProjectId` char(36) NOT NULL,
  PRIMARY KEY (`Id`,`ProjectId`,`SpecificationId`),
  KEY `project_item_key_idx` (`ProjectId`),
  KEY `brand_idx` (`BrandId`),
  KEY `spec_item_key_idx` (`SpecificationId`),
  CONSTRAINT `project_item_key` FOREIGN KEY (`ProjectId`) REFERENCES `project` (`Id`),
  CONSTRAINT `spec_item_key` FOREIGN KEY (`SpecificationId`) REFERENCES `specification` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

How I can improve that? I thing that one reason for the bad performance is because the lenght of my key in "projectitem" table.

I migrated that data from another database. Than, first a created all tables without keys and after the migration I used that command for create keys and indexes:

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE `projectitemreference`
ADD PRIMARY KEY (`Id`, `ProjectId`, `ProjectItemId`, `SpecificationId`),
ADD INDEX `reference_item_key_idx` (`ProjectId` ASC, `ProjectItemId` ASC, `SpecificationId` ASC) VISIBLE
, LOCK = NONE;

ALTER TABLE `projectitemreference`
ADD CONSTRAINT `reference_item_key`
  FOREIGN KEY (`ProjectId` , `ProjectItemId` , `SpecificationId`)
  REFERENCES .`projectitem` (`ProjectId` , `Id` , `SpecificationId`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `reference_project_key`
  FOREIGN KEY (`ProjectId`)
  REFERENCES .`project` (`Id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, LOCK = NONE;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Can it be a problem?


texts: Truncate and insert: Heap and build index or directly into Clustered table

texts:
In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place.
I know that I can test it and measure it (have not done that yet) but I what to understand the reason, that is what is more expensive: sort and insert into a clustered index or to create an index.
I have kept my index in place and when I insert I see a sort at the end of the execution plan. Also the clustered index insert operator right before the root node is quite expensive (basically all the cost is divided between the sort and the clustered index insert operators).
I use TABLOCK for my insert, recovery model is simple and table is rowstore.


texts: Conditional WHERE Clause in a Table Valued Function

texts:
I have a table valued function

    CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25),
        @Device varchar(25)
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE Date < @EndDate AND
              Date > @StartDate AND
              Location = @Location AND
              Device = @Device

    );

Now I would like to give the user the option to use NULL as location parameter and or device parameter so he/she gets all data independent of the location and device back

What would be the most elegant and efficient way to do this ?


texts: Is it possible to run a trigger on Table Creation in MySQL

texts:
Simple Question. Is it possible to create a Trigger that runs everytime a Table is created.

The goal is to create a metadata table for each table, but I can worry about dynamically passing the table name later.

The MySQL documentation implies that you can only run triggers on Inserts, Updates, and Deletes.See: 13.1.15 CREATE TRIGGER Syntax.


texts: Scheduling large numbers of events in Mysql

texts: Looking at using the Mysql event scheduler to schedule potentially quite a large number of events which could be in the 100,000's of discrete events per day (possibly more).  Does anyone have any experience with scheduling such volumes of events in mysql and if they encountered any pitfalls or issues with implementing things this way.


texts: OpenRowSet - How to configure OLE DB Provider to be used for for distributed queries

texts:
I have an Excel file with data and would like to update a table in database based on the data the Excel file contains.

To do that I want to use OpenRowSet command. But I get the error below when even I want to have a SELECT from the Excel data.

SELECT  exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Dev\ExcelDataImport.xlsx; Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'
,'SELECT * FROM [Sheet1$]') AS exl
GO

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

As error message says, I think I should configure the Microsoft.ACE.OLEDB.12.0 to be able to use it for distributed queries like OpenRowSet. To do that I ran the commond below:

exec sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Output message from the command above:

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

But still I get the same error when try to run the OpenRowSet query.Could you please give me some hints how to resolve this issue and get the OpenRowSet working?

My environments:

SQL Server 2008 R2, Excel Professional Plus 10 (32 bits)

Also the providers I have in Linked sever as as following:

Thanks in advance.


texts: How to tell if SQL is autogenerated by some tool

texts:
I am looking into some stored procedures and the code is more or less non-readable e.g. there are 15 main joins in one statement and the level of nesting of the subqueries is e.g. 7 in order to simply define one of the tables used in the joins... If one counts the joins in the subqueries then there are many more joins... Hope it makes sense...
I do not think a human has written these SPs and I think they have generated the code and copy-pasted it in the SP template... Does this sound right?
What are these tools called? So I am trying to refactor these and I was first annoyed and thought what idiot writes code like this but now like I said I think it is autogenerated... By autogenerated I mean a drag and drop interface.
Is my inference correct based on the description above? If you could comment of performance that would be great. My understanding is that these tools are performance killers...


texts: How do you grant permissions to a MySQL user using a shell script with variables?

texts:
I'm trying to create a provisioning script for Scotch Box (Vagrant) that will populate a database with seed content when I fire up the server.

When I name the actual user, database, password, and host in the lines of code being piped to mysql, it worked perfectly.

But when I added a "config" section with those items defined as variables, everything worked except the GRANT: The user was created, the database was created, and the data was imported, but the user didn't get access to the database.

Here is the script:

#!/usr/bin/env bash
# ...

echo "CREATE USER '$USER'@'$HOST' IDENTIFIED BY '$PASS'" | mysql -uroot -proot
echo "CREATE DATABASE $DB" | mysql -uroot -proot
echo "GRANT ALL ON $DB.* TO '$USER'@'$HOST'" | mysql -uroot -proot
mysql -uroot -proot $DB < /var/www/data/db.sql

Is there a way to pass these items as variables to the GRANT command?


texts: locked from root and can't change privileges on another user

texts:
I was getting error 1698 when I was trying to log into root

orpheus@Roedelius:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I did this to fix it

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('woofwoof')  WHERE  User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

but that didn't work and now I've been getting error 1045 when I try to log into root. The top answer here https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost to my same problem says its because of anonymuos users, but I don't have any.

orpheus@Roedelius:~$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

these are all my users, none are anonymous, I think.

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| orpheus          | localhost |
| root             | localhost |
| tom              | localhost |
+------------------+-----------+

so I tried granting all privileges to root and I get error 1698 again

mysql> grant all privileges on *.* to root@localhost identified by 'woofwoof' with grant option;
ERROR 1698 (28000): Access denied for user 'orpheus'@'localhost'

when I try to see grants for root I get this

mysql> show grants for 'root'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'

but for my other user, orpheus, I get this

mysql> show grants for 'orpheus'@'localhost';
+------------------------------------------------------+
| Grants for orpheus@localhost                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'orpheus'@'localhost' |
+------------------------------------------------------+

I don't know why this is happening, but I just want to access root again. I would appreciate any help with this.


texts: duplicate entry issue with large mysql database table

texts:
I have a very large MySQL database table. The other day, for no apparent reason, I now keep getting:

Duplicate entry '148245' for key 'PRIMARY'

If I remove the entry at with primary of 148245, then I just get the same error but showing a different number. I tried on a backup copy of the table by removing the bad row and trying an insert statement through my ASP.NET webpage using the MySQL connector which has until recently always worked and I get the error but with a different number.

What do I do to remove this error - surely I can't just delete a load of rows until it goes away?

Not an error I have seen before and I am not a massive MySQL expert.

Thanks in advance.


texts:
Help me please clarify a very important question on which I can't find the answer. It only can be proven with tests.

Let's say we have XtraDB Cluster (or Galera Cluster) with three nodes. We have main user' table with the auto-increment primary key.

Ids in this table on the first node goes like 1,4,7,10...
In the second node - 2,5,8,11...
In the third node - 3,6,9,12...

After some time we have that the first node has 10000 inserts, so its auto-increment value is around 30000.
But two other nodes were less loaded, so they have only about 100 inserts and their auto increment value is around 300.

Now we decided to add two more nodes to the cluster. Now the auto-increment offset is equaled five.
Obviously, two lesser loaded nodes can't fill the numbers from 300 to 30000 with their new offset, right?

So what will happen to the table's auto-increment on each node? How will the cluster behave?

Is it gonna start the auto-increment from 30000 for every node and unfilled ids from 300 to 30000 will remain unfilled?

Thank you for your time and experience.


texts:
Is there a tool like phpMyAdmin for RDBMS-MySQL for a Cassandra DB?

I know that there is less "runtime" config possible (column families can not be edited at runtime etc.). Nevertheless, it would be very helpful to have a GUI to inspect the different column families, browse data, and so forth.


texts:
Currently I have a simple schema, a sites table and an items table.

Sites own many items, items has a siteId column to store the reference of the site which owns it.

I'd like to be able to enforce a condition when querying items, meaning returning an error if it is not queried with a siteId - in practice:

select * from items would fail
select * from items where siteId = 1 would succeed, albeit a site record with id 1 exists

This can easily be achieved on an application level.

I recently started investigating further into row level policies, roles, functions, etc… which I found very interesting as they enable lower level setups. That is, I'm still new about it and lack understanding/knowledge. I was wondering if the use case described above could simply be solved within SQL (via a combination of policies, functions, etc… or any other Postgres builtin tools)?


texts:
I reinstalled Windows 10 from scratch.

I'm trying to get SSMS options setup the way I like them. When I r-click a DB, then Tasks->GenerateScripts, the result doesn't contain SCOPED CONFIGURATION lines anymore. I checked the various SSMS scripting options and don't see one specifically for SCOPED CONFIGURATION.

Does anyone have ideas on why GenerateScripts doesn't include SCOPED CONFIGURATION lines?

SSMS 17.9.1 SQL Server 14.0.2002.

Thank you in advance.


texts: Merge replication: some subscribers initializing by downloading changes multiple times

texts:
I have a database in merge replication with about 25 subscribers, all accessing the publisher/distributor over a satellite connection (high latency, low bandwidth).  Recently, as part of a planned system upgrade, each subscriber was taken offline, had its data merged with the publisher, then was deleted.  The publisher was then updated, and each subscriber was to be recreated.

Possibly relevant info:

-Only about 50 of the 130 tables are being replicated.  Of those 50, about 20 are marked as Download Only to Subscriber.

-To deploy the subscribers, I first use SSMS to generate scripts to create the publisher database schema, then run that script on each subscriber.

-I created a parameterized script to create the subscription on each subscriber.  (In other words, the exact same script was run on each subscriber to create the database schema and subscription.)

-The plan is that the subscribers will be populated with data during initialization.

-Snapshot Agent job is run daily and is compressed to a .cab file in an alternate pickup directory.

-All Agents and SQL Server instances are running under the same domain service account.

-SQL Server 2012 Standard on all servers

Now for my issue:

On 20 of the subscribers, the initialization happened without any issues (although a couple took several attempts due to connection issues). Within 6 hours, the 20 subscribers were initialized and synchronizing hourly.  Each sync ends with about 300-500 combined inserts, updates, and deletes.
Each of the subscribers returned a message after initialization similar to: Applied the snapshot and merged 0 data change(s) (0 insert(s), 0 update(s), 0 delete(s), 0 conflict(s)).

5 of the subscribers were offline at the time, so they could not be initialized for about a week.  During that week, the system was in use, and snapshots were being generated nightly.

When the remaining 5 subscribers were brought online and attempted initialization they did not initialize in the same way that the prior 20 had.  Instead, each of them is downloading mass amounts of changes on top of the bulk inserts.  In some cases, the changes are 2 or 3 times the number of bulk inserts that were originally copied to the subscriber.  Instead of the ~6 hours that the original 20 took, these 5 can take upwards of 5-6 days, and given the slow/bad connection, they have failed every attempt to initialize.

The 20 subscribers that worked are still doing fine, so I do not believe the publication setup or method used is faulty, but maybe there is some hidden or non-obvious reason why the remaining subscribers are behaving as they are?


texts:
Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

Background: We are doing microservice development and there is an sole owner schema and many different service accounts (schemas) that have been granted access to the tables created by the owner. At the moment, it's a nuisance to have to log into each service account to create synonyms. Is there a way to run a script to do it from the owner schema? We're trying to automate this process.


texts:
I have data as

id action
1  a
1  b
2  b
2  a
3  c

I wrote code as

select id,group_concat(action SEPARATOR ' ')
from table1;

It ends up as

id action
1  a b
2  b a
3  c

Actually, ba and ab is the same one, how could we sort it?


texts:
Can you anyone tell me if it's possible to restore an 11g Oracle database using media management software like Net Backup or Commvault incrementally?

I need to perform a restore of a DB into a new target database (overwriting the DB) ... this of course is fine - connect to the RMAN catalog and then duplicate the DB. However I would then, perhaps 5 days later, like to apply all the incremental changes that occurred on the source to this new target. Is it possible to restore again but only apply the logs from the previous 5 days and roll forward? The new target can be left mounted during this time. It's not being used until all logs are applied. The idea is to save time restoring a massive database before switching over to it permanently. Many thanks.


texts: SQL USING CAST in a query inside a stored Procedure

texts:
I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error

  Conversion failed when converting date and/or time from character string.

SELECT *
  FROM TEMPTABLE
 WHERE Status = 'A'
   AND technician = 5
   and schid not in (1047)
   and CAST(approxstartdate as datetime)  > =
       ( select CAST(approxstartdate as datetime)
           from trscheduler where Status = 'A'
            AND technician = 5
            and schid = 1059
       )
 order by  CAST(approxstartdate as datetime)

This query is used in a cursor.

Kindly let me know an alternative for this.


texts:
I am running MySql 5.6 on Google cloud as VM instance, one of the tables contains 1500,000K rows with 676G .ibd file.
using mysqldump --opt --add-drop-table  --quick DATABASE BIG-TABLE >DATABASE.TABLE.sql to export the table ends up with poor read performance of around 8-15 MB/s with high iowait.
more details:

Not a destination disk issue, the destination disk is SSD as well with 120.00 MB/s writes (tested)
Not a source disk issue, according to GCP and dd the disk (sdb) is SSD volume with 180.00
MB/s reads.

time dd if=BIG-TABLE.ibd of=/dev/null status=progress
2886054912 bytes (2.9 GB) copied, 17.044179 s, 169 MB/s^C
5709061+0 records in
5709060+0 records out
2923038720 bytes (2.9 GB) copied, 17.237 s, 170 MB/s

iostat:

iostat -m -x 5 -d /dev/sdb
Linux 3.10.0-957.1.3.el7.x86_64 (sql-server)      12/26/2018      _x86_64_        (8 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     4.91  170.00   29.18    19.75     0.73   210.57     3.78   19.00   21.78    2.77   4.92  98.08

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  103.60    0.00     7.33     0.00   144.99     1.68   16.31   16.31    0.00   9.43  97.66

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  145.60    0.00    12.71     0.00   178.81     1.90   13.03   13.03    0.00   6.75  98.22

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  140.00    0.00    12.73     0.00   186.24     2.03   14.52   14.52    0.00   6.98  97.78

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00  172.40    0.00    17.16     0.00   203.84     2.81   16.16   16.16    0.00   5.69  98.14

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00   94.20    0.00     6.56     0.00   142.61     1.37   14.79   14.79    0.00  10.41  98.02

iotop

cat my.cnf:

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table=ON
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=3G
innodb_log_file_size = 2G
innodb_old_blocks_time=1000
innodb_stats_on_metadata=off
innodb_log_buffer_size = 5M
bulk_insert_buffer_size = 1G
skip_name_resolve = ON
thread_cache_size=8
max_allowed_packet=4G
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

general-log   = off
server-id       = 1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

show create table:

| BIG-TABLE | CREATE TABLE `BIG-TABLE` (
  `major` int(11) NOT NULL,
  `minor` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `attributeid` int(11) NOT NULL,
  `value` varchar(128) NOT NULL,
  `longvalue` mediumtext NOT NULL,
  UNIQUE KEY `componentIndex` (`major`,`minor`,`version`,`attributeid`),
  KEY `componentAttrIdx` (`attributeid`),
  KEY `componentValIdx` (`value`,`attributeid`,`major`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


texts:
I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day going back 3 months?

I am using datediff but it would be great if I can have a field with the actual date and a count in the resultset.

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())


texts: Getting Error: wrong pg_constraint entry for trigger

texts:
I have not been able to find any details on this error at all.  Specifically, I'm running a query similar to:

UPDATE "data"."campaigns" SET "name"='New Name' WHERE ("id"='182')

... and I'm getting the following error:

ERROR:  wrong pg_constraint entry for trigger "RI_ConstraintTrigger_54145"
        on table "campaigns"

If it helps, here is the definition for the trigger:

CREATE CONSTRAINT TRIGGER
   "RI_ConstraintTrigger_54145"
AFTER UPDATE ON
   "data"."campaigns"
FROM
   "data"."ethics_wall_exemptions"
NOT DEFERRABLE
FOR EACH ROW
EXECUTE PROCEDURE
   "RI_FKey_noaction_upd"();

Also, the query works fine on the DEV database; the error only throws on the production database.  I've not been able to find any differences, but I'm sure something is different.

Any ideas?


texts: How to spool output from SQL*PLUS to appear in single line with 1 space in between them?

texts:
I have a table with 20 rows. I want to spool the rows into a text file where rows come side by side in one line and have one space in between to keep them separated. I intend to create a tar.gz file by keeping them in one line.

Sample data:

col1
------

file1
file2
file3

expected output:

tar cvzf tarfile.tgz file1 file2 file3


texts:
I have a Redshift table that looks like the following:

+----------------+---------------+------+
| FieldA         | field1        |field2|
+----------------+---------------+------+
| ABC            | 123           |   78 |
| DEF            | 456           |   90 |
+----------------+---------------+------+

I want to run a query to produce the following output. I believe I have to use a

UNION ALL

to get the fields I want split up into different rows, but I'm not sure how to get the column names (field1, field2) as values within a column.

+----------------+---------------+------+
| FieldA         | value         |field |
+----------------+---------------+------+
| ABC            | 123           |field1|
| DEF            | 456           |field1|
| ABC            | 78            |field2|
| DEF            | 90            |field2|
+----------------+---------------+------+

(I don't care about output order at the moment)


texts: SQL Server Transactional Replication over Internet

texts: I have 2 instances with SQL Server 2008R2. I would like to build Transactional replication between those servers. One is in the Local Network (Local_Instance) and the second server is over Internet (Internet_Instance). The Internet Server is running an FTP service. I have read something that I can use replication using FTP but I don't have any idea how to make this. I have built the publication but I need help to configure the suscription. The distribution database is in the Local Network.


texts: Error that role of the previous master user does not exist when restoring a database to a different server with different master username

texts:
I have to move a postgres database to another server. Both servers are Postgres 11.8 on Amazon RDS. I'm dumping the original jira database like this:
pg_dump \
  --dbname=postgresql://postgres:***@oldserver.***.eu-central-1.rds.amazonaws.com:5432/jira \
  --file="$(date +%Y%m%d_%H%M)"_jira \
  --format=d \
  --jobs=4 \
  --no-owner

The original jira database was running with the master user postgres, which has access to every database. I want this database to have it's own user with no access to any other database. Therefore on the new server I'm running this first:
PGPASSWORD=*** psql \
  --command="REVOKE CREATE ON SCHEMA public FROM PUBLIC;" \
  --command="CREATE DATABASE jiradb;" \
  --command="REVOKE ALL ON DATABASE jiradb FROM PUBLIC;" \
  --command="CREATE USER jirauser WITH PASSWORD '***';" \
  --command="GRANT ALL PRIVILEGES ON DATABASE jiradb TO jirauser;" \
  --dbname=postgres \
  --host=newserver.***.eu-central-1.rds.amazonaws.com \
  --port=5432 \
  --username=testuser

This should ensure that the restored database runs with his own user jirauser. I'm restoring the dump like this:
PGPASSWORD=*** pg_restore \
  --dbname=jiradb \
  --host=newserver.***.eu-central-1.rds.amazonaws.com \
  --jobs=4 \
  --no-owner \
  --port=5432 \
  --username=jirauser \
  --verbose \
  20201125_1405_jira

At the end of the restore I'm getting this error:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8422; 0 0 ACL SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  role "postgres" does not exist
    Command was: REVOKE ALL ON SCHEMA public FROM rdsadmin;

Should I be concerned about this? What I don't understand why it tries to revoke rdsadmin rights from the public schema? Shouldn't this just be a dump of my jira database without any postgres role or rdsadmin stuff?
I have tried with the --data-only option, but this just yields more errors.
What I understand is that it has something to do that the master user name being postgres on the old server and testuser on the new server. Should I also name the master user postgres on the new server?
@Laurenz Albe: When running \du rdsadmin I get:
postgres=> \du rdsadmin;
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 rdsadmin  | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
           | Password valid until infinity                              |


texts: script to show all the permissions for a table

texts:
inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update.

when I run this script

select * from sys.objects
where name = 'spGen2_tblIE_SchedProcess_Update'

Inside the same database I have a table called tblIE_Step

when I run the following script

select * from sys.objects
where name = 'tblIE_Step'

then I want to see all the permissions of the objects.

when I check my stored procedure
I run this script:

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc, 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc
    , *
From sys.objects as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where p.object_id = 87671360

and I get the permissions:

But when I run the same for my table, I don't get anything:

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc
    , 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc
    , *
From sys.objects as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where p.object_id = 389576426

I know there are logins and groups that belong to roles that are have db_reader and db_writer permissions.
However, they are not showing here.

How can I change my script so that it would show me all the permissions for this table?


texts:
I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives.

Is there a way of following each tables activity ?
As in follow IOPS, writes, reads per table ?


texts: Postgresql: Difference between pg_dump and backup/restore?

texts: Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore deletes everything and then dumps it in?


texts: Field types database design best practice

texts:
I am creating a cms system where the user can create fields to use in the templates of the website. I want to create different types like 'string', 'html', 'integer' etc. The user must also have the possibility to add these fields in different values, so I will be needing at least two tables.

My question is this: Should I create a table for each field value type and catch this in the code or should I store all field value types in one table?

Option 1:
Table for each type

+---------------+    +-----------------+    +--------------------+
| Fields        |    | field_int_value |    | field_string_value |    etc...
+---------------+    +-----------------+    +--------------------+
| id            |    | field_id        |    | field_id           |
| field_type_id |    | language_id     |    | language_id        |
+---------------+    | value (int)     |    | value (varchar)    |
                     +-----------------+    +--------------------+

Option 2:
Store all values in one table. keep the unused values NULL.

+---------------+    +-----------------+
| Fields        |    | field_value     |
+---------------+    +-----------------+
| id            |    | field_id        |
| field_type_id |    | language_id     |
+---------------+    | value_int       |
                     | value_string    |
                     | etc...          |
                     +-----------------+

Some examples:

A user could for example create a string with his or her phone number. In the code the template would call this field to display the phone number. If the user would ever change this, they can just edit the value in the admin without having to worry about editing the template.

Another example is a boolean to allow the user to show a certain 'call to action' on a page. This boolean would be used to hide something in the template without having to update the template.

My Field model would be the one handling all this. In here I would create functions to get the field of a specific type.

I have been breaking my head over what would be the best option, but I can't seem to find the better one. Does anyone have any ideas?


texts: How to make a query using "where MONTH(datetime)" faster?

texts:
I already tried to add an index on the datetime column, but the result is still unacceptably slow.

SELECT s.id, s.player,
COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 AND dp.colour > 5 then 1 else null end) as totalwin,
COUNT(case when dg.winner = 2 AND dp.colour <= 5 then 1 when dg.winner = 1 AND dp.colour > 5 then 1 else null end) as totallose,
COUNT(dg.winner) as totalgames
       FROM dotaplayers AS dp
       LEFT JOIN gameplayers AS gp ON gp.gameid = dp.gameid and dp.colour = gp.colour
       LEFT JOIN stats AS s ON s.player_lower = gp.name
       LEFT JOIN dotagames AS dg ON dg.gameid = dp.gameid
       LEFT JOIN games AS g ON g.id = dp.gameid
       LEFT JOIN bans as b ON b.name=gp.name
       WHERE MONTH(g.datetime) = 4
       GROUP by gp.name
       ORDER BY totalwin DESC LIMIT 0,10

Showing rows 0 - 9 (10 total, Query took 7.7552 seconds.)

If someone wants to help, I can provide a copy of my database, it is only 120 MB in size.


texts: How many bytes does character takes if I created database with utf8 in mysql

texts:
Suppose if create a database with utf8 character set and created table and inserted data.
Here my doubt is if the character is 1 byte then  it will occupy only 1 byte or it will occupy 3 bytes because of utf8 character set


texts: How to troubleshoot "BCP copy in failed"

texts:
I have a table that looks something like this in a SQL server database in Azure:

CREATE TABLE [dbo].[TableName](
[col01] [int] IDENTITY(1,1) NOT NULL,
[col02] [varchar](255) NOT NULL,
[col03] [varchar](255) NOT NULL,
[col04] [datetime] NULL,
[col05] [datetime] NULL,
[col06] [nvarchar](255) NULL,
[col07] [nvarchar](max) NULL,
[col08] [nvarchar](255) NULL,
[col09] [nvarchar](max) NULL,
[col10] [int] NULL,
[col11] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I have a data file that looks a bit like this:

$ head datafile.csv
2134024,SRC001,SE,NULL,NULL,NULL,NULL,NULL,NULL,1,SRC
2134025,SRC002,SE,NULL,NULL,NULL,NULL,NULL,NULL,2,SRC
2134026,SRC003,SE,NULL,NULL,NULL,NULL,NULL,NULL,3,SRC
2134027,SRC004,SE,NULL,NULL,NULL,NULL,NULL,NULL,4,SRC
2134028,SRC005,SE,NULL,NULL,NULL,NULL,NULL,NULL,5,SRC
2134029,SRC006,SE,NULL,NULL,NULL,NULL,NULL,NULL,6,SRC
2134030,SRC007,SE,NULL,NULL,NULL,NULL,NULL,NULL,7,SRC
2134031,SRC008,SE,NULL,NULL,NULL,NULL,NULL,NULL,8,SRC
2134032,SRC009,SE,NULL,NULL,NULL,NULL,NULL,NULL,9,SRC
2134033,SRC010,SE,NULL,NULL,NULL,NULL,NULL,NULL,10,SRC
$ wc -l
713224
$

I try to import it using bcp running on my linux laptop:

$ bcp TableName in ./datafile.csv -S niceclouddbservername.database.windows.net -U myusername -dmydatabasename -c -t ','
Password: <I paste my password here>

Starting copy... <this takes a few seconds>

BCP copy in failed
$

Two questions, one is interesting right now:

Why doesn't this work?
How can I debug it?

What I have done so far:

Used duckduckgo  (and google for good measure) to look for an explanation
Re-read the docs, found other examples, tried different variations
Tried with just the first 10 records
Verified (tried to at least) that the datatypes matches the input
Testing with a known wrong password to verify that it would produce a different error message
Consulted everything that seemed remotely related from the list of "Similar Questions"


texts: UPDATE-SELECT Consistency Issue In Read Committed

texts:
In Read Committed isolation level, I would expect the following to deadlock since UPDATE statement will mark the row dirty and SELECT should not be able to read the row before transaction is committed or rolled back. But it seems to work without a deadlock. How does this happen?

BEGIN TRAN

UPDATE TableA
SET Col2 = Col2 + 3
WHERE ID = 50

SELECT Col2 from TableA WHERE ID = 50

COMMIT TRAN


texts: SQL Server - global temporary table vs normal table

texts:
I have a stored procedure which I use to load csv files to SQL Server. This will initially bulk insert the file into a global temporary table then at the end it will do:

select cast(column1 as int) [column1], column2, column3... into table from ##temptable

and then proceeds to drop the ##temptable

Before the query select into table from ##temptable I run a series of select queries on the temporary table to determine what type the columns should be (and I'm confident this works properly).

Note that having a table (either normal or temporary) before creating the final output table is essential for the stored procedure.

What I'd like to know is if I'm better off using a global temporary table or if I should use a normal table or does it make no difference?

I've seen several articles about the differences between temporary tables and table variables but I've seen nothing when it comes to comparing global temporary tables to normal tables and it's difficult for me to test this properly in my environment.


texts: Find minimum number of columns that identify uniqely all rows

texts:
I'm using SQL Server Management Studio.

I've a table and I want to find a Primary Key for this table.

Can I write a script in T-SQL that finds which columns (and I want that the number of columns is minimum) identify uniqely all rows?


texts:
This is not a duplicate of "Spool query to file without output on screen"

Imagine I am spooling the below in a query:

SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "Expiring Password" from dba_users where lower(username) in ('gunner','gunnersa','gunnerx3');

Expiring Password
--------------------------------------------------------------------------------
alter user GUNNER account lock password expire;

Now I have spooled off the value and saved it to a file. I will be running it as a separate file for the expiring users. When I do that, I get some basic errors about the SQL command on the dynamic query.

Is there a way that we can get only the - "alter user GUNNER account lock password expire;" from the output from SQLPlus to a file?

I use Solaris (SunOS sintscdldmu004 5.11 11.3 sun4v sparc sun4v)


texts: Insert randomly takes 10+ seconds to complete

texts:
I have an insert statement generated by the .NET Entity Framework.  In most cases, this particular insert will execute in 0ms according SQL Server Profiler.

One out of every 30 or so inserts will jump to as high as 12 seconds duration, causing the .NET client on the other end to show as "Not Responding" while it waits.  Server load should not be an issue as our server is very, very lightly loaded.

Here is the table the insert is being performed against:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ProductEvents](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[KID] [int] NOT NULL,
[EventDescription] [varchar](50) NOT NULL,
[EventDate] [datetime] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Notes] [varchar](max) NOT NULL,
[Version] [timestamp] NOT NULL,
[IsSynchronized] [bit] NOT NULL,
[LastSyncDate] [datetime] NULL,
 CONSTRAINT [PK_ProductEvents] PRIMARY KEY CLUSTERED
([EventID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ProductEvents] ADD  CONSTRAINT [DF_ProductEvents_IsSychronized]
DEFAULT ((0)) FOR [IsSynchronized]
GO

ALTER TABLE [dbo].[ProductEvents]  WITH CHECK ADD  CONSTRAINT
[FK_ProductEvents_Products] FOREIGN KEY([KID])
REFERENCES [dbo].[Products] ([KID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProductEvents] CHECK CONSTRAINT [FK_ProductEvents_Products]
GO

And the query as seen by SQL Server Profiler (actual example):

exec sp_executesql N'insert [dbo].[ProductEvents]([KID], [EventDescription],
[EventDate], [UserName], [Notes], [IsSynchronized], [LastSyncDate])
values (@0, @1, @2, @3, @4, @5, null)
select [EventID], [Version]
from [dbo].[ProductEvents]
where @@ROWCOUNT > 0 and [EventID] = scope_identity()',N'@0 int,@1 varchar(50),@2
datetime2(7),@3 varchar(50),@4 varchar(max) ,@5
bit',@0=1894,@1='Modified',@2='2013-08-12
08:09:25.4766233',@3='KNEXTION\aellison',
@4='Description changed from Mini Awareness Ribbon Cookie Cutter - RM 1698 to Mini
Awareness Ribbon Cookie Cutter - R&M 1698.',@5=0

Here is the execution plan as a screenshot from SSMS:

Any ideas on how to begin to track this down?

The actual and estimated plans seem to be the same.

It is a multi-user application, and this table would be one of the more active tables.

Is there any easy way in the Profiler or any other tool to see if blocking is the issue? Are there any other issues I should be looking for besides blocking?


texts: Identical tables, identical query, completely different execution times

texts:
We have two tables with identical columns and indexing (No indexing at all, basically). We run the same query, which in case of the original table takes 5 seconds to run; in the case of the new table we let it run for 30 minutes and then killed the query.

We updated statistics, but that had no result. We tried a rebuild of the new table to see if defragmenting it would help, but that had no effect either.

On a hunch, we then exported both tables to the same database, in order to see whether or not anything would change there, but we have the exact same results.

I'm kind of baffled how this could be. To make things even more interesting, the original table contains more data than the new table, which should theoretically mean that the new table should finish the query faster.

Does anyone have a possible explanation? I've worked for years as a DBA (though not for a few years now), and frankly, I'm baffled as to why this could possibly happen.

In response to some of the comments, here is the table definition in question:

CREATE TABLE [dbo].[Fact_SubscriptionDetail_Test](
[CustomerSellTo_Key] [int] NULL,
[CustomerContactSellTo_Key] [int] NULL,
[CustomerRefTo_Key] [int] NULL,
[WebAuthUser_Key] [int] NULL,
[Country_Key] [int] NULL,
[Date_Key] [int] NULL,
[SnapshotDate_Key] [int] NULL,
[Product_Key] [int] NULL,
[License_Key] [int] NULL,
[Subscription_Key] [int] NULL,
[SubscriptionTypeLostSeatsType_Key] [int] NULL,
[M_SubscriptionDetail_NewSeats] [int] NULL,
[M_SubscriptionDetail_WinbackSeats] [int] NULL,
[M_SubscriptionDetail_RenewedFlexSeats] [int] NULL,
[M_SubscriptionDetail_RenewedCommitSeats] [int] NULL,
[M_SubscriptionDetail_RenewedSeats] [int] NULL,
[M_SubscriptionDetail_ActiveSeatsEndMonth] [int] NULL,
[M_SubscriptionDetail_LostNonPaymentSeats] [int] NULL,
[M_SubscriptionDetail_LostGraceInactiveSeats] [int] NULL,
[M_SubscriptionDetail_LostOtherSeats] [int] NULL,
[M_SubscriptionDetail_LostSeats] [int] NULL,
[M_SubscriptionDetail_GrossBookings] [numeric](38, 20) NULL,
[M_SubscriptionDetail_ActiveFlexSeats] [int] NULL,
[M_SubscriptionDetail_ActiveCommitSeats] [int] NULL,
[M_SubscriptionDetail_ActiveSeats] [int] NULL,
[DateCreated] [datetime] NULL
    CONSTRAINT [DF__Fact_Subs__DateC__gtfhjCC]  DEFAULT (getdate())
) ON [DATA]

Data in the two tables is not entirely identical, but very similar in nature. The query we run on it ties to some other tables (primarily Data warehouse dimensions).

I defragged (which I agree, doesn't make much sense for a heap, but I figured it wouldn't and couldn't hurt) by simply running

ALTER TABLE Fact_SubscriptionDetail_Test REBUILD.

When we initially checked the query plan, it suggested adding some indexes on the Test table (but not the original, fast table). We also attempted adding a clustered index (PK) on the Test table, but neither that, nor the suggested index by the Execution Plan had any effect.

Here are the execution plans:

http://pastebin.com/XagvSxjj (Original table; fast)
http://pastebin.com/LSgCsvUe (New table; slow)

There is a difference in the execution paths, making me think the cardinality of the data in the original table is somewhat better. The original table contains some 1.4 mio rows (230 MB), of which 400k are processed by the query. The new table contains 400k rows (52 MB).

This is the full query: http://pastebin.com/bYiaGW1d (slightly edited to remove some sensitive stuff).

The value for "cost treshold for parallellism" on the server is 5.


texts: how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time

texts:
how do I change the query below so it only reads and displays 132 characters each line

with w(id, line#, line, rest) as (
  select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'),
         substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
  from foo
  union all
  select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'),
         substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;

Thanks


texts: How to prevent parallel running of SQL Agent jobs

texts:
What would be good ways to design a group of jobs such that they can never run at the same time?
I am looking for a generic concept, like a semaphore, which makes it easier to ensure that some jobs are never running at the same time.
For example, I'm thinking of a "signal table". When a job starts, it sets the signal and at the end, it resets the signal. In addition, every job needs to wait until the signal is set. (How to implement a CPU friendly waiting?) However, when a job crashes, there must then be a solution to automatically reset the signal, otherwise jobs would never start.
We have 4 jobs which are doing various updates, imports, calculations. They all are running by scheduler, and in addition, some of them can also be started manually by super users in case a special run is needed. However, they should not run at the same time, because it often results then in locks or even deadlocks and the database does not properly respond to the users then.
EDIT: It's a data warehouse which is gathering and consolidating data from multiple external data sources. Every job is responsible for another data source, like this:

job #1 is running every 15 minutes
job #2 every 3 hours
job #3 and Job #4 run once per day or have extra runs when started by super users.


texts: Can Aria table be changed back to InnoDb with foreign key constraints enabled?

texts:
Question is, I am aware that Aria Engine is non-transactional and does not support foreign key, but imagine there is a scenario where I copy in data from S3. Copying in two different tables into mariadb and later establishing them the constraints.
Is this behavior possible in MariaDB?
Alter table to use engine=s3, pull data from s3 and then change it back to innoDB. Will this then establish the constraints and create indexes and make it ACID ready?
I am aware that this is a fundamental question and would appreciate further readings and explanations.


texts:
When I create a user account with localhost and password as

create user user1@localhost identified by 'user1'

It is ok to grant.
But when i create user using '%' and without password, it is not ok for grant.

create user user2@'%'
grant select on newdatabase.s to user2

and I have been checked the privileges of user2 using show grants for user2. It shows correctly.
But when i use as user2, user2 can not access 'newdabase'
Why?


texts: copy only tables with data from one database to another database

texts:
I have two database , dbOne(version - 10.50.1600 - locate in office server )  and dbTwo(version - 10.0.1600 - locate in my local server) .
I want to copy dbOne's tables with data to dbTwo .
Is there any way or script to do it ? I don't want to upgrade my local server-version !


texts: Cannot drop database with logical replication

texts: I cannot drop database having logical replication SUBSCRIPTION, when i try to dropdb i get an error saying that the database contains a subscription but when i try to drop the SUBSCRIPTION i get an error saying the replication slot does not exist.


texts:
I want to run a script inside a transaction in postgresql. So I suround the sql with begin , commit statements. But I want to rollback on error. I dont see how to do that.
BEGIN;
UPDATE public.tablename
SET blah = 'xxx'
WHERE thing= '123';

COMMIT;


texts:
I have two tables, created as below,

sqlite> CREATE TABLE Artists(
   ...> ArtistId INTEGER PRIMARY KEY,
   ...> ArtistName TEXT NOT NULL
   ...> );
sqlite> CREATE TABLE Albums(
   ...> AlbumId INTEGER PRIMARY KEY,
   ...> AlbumName TEXT NOT NULL,
   ...> Year TEXT NOT NULL,
   ...> ArtistId INTEGER NOT NULL,
   ...> FOREIGN KEY(ArtistId) REFERENCES Artists(ArtistId)
   ...> );

.table returns the following,

sqlite> .table
Albums   Artists

foreign key pragma is set to ON

sqlite> PRAGMA foreign_keys;
1

However, When I try to insert data as below results in FOREIGN KEY constraint failed.

sqlite> INSERT INTO Albums VALUES (NULL, 'Killers', '1981', 7);
Error: FOREIGN KEY constraint failed
sqlite>

I am a database learner. Could someone point out what is that I am doing wrong?