texts: What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

texts:
I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this fake table:

CREATE TABLE "tbl" (
    "id" serial,
    "name" varchar(40),
    "num" int,
    "time" timestamp
    PRIMARY KEY ("id"),
    UNIQUE ("id")
);

Also name, num, time are not explicitly stated as NOT NULL, in reality they are, because the enforcement happens on the application side.

My feeling is that it should be changed, but the counterpoint is that the application level makes sure that null values can't appear here and no one else manually modifies the table.

My question is: What are the benefits (performance, storage, consistency, something else) and drawbacks (assuming that I already verified that there are no nulls present at the moment, and from the business logic there should be no nulls) by setting an explicit NOT NULL constraint?

We have a good code review process and a reasonably good documentation, so the possibility that some new person would commit something that breaks this constraint is not really enough to justify the change.

This is not my decision, so this is exactly why I am looking for other justifications. In my opinion, if something can't be null and a database allows you to specify that something is not null - then just do it. Especially if the change is super simple.


texts: Why/how did the behavior of extract(epoch...) from "timestamp without time zone" fields change between PostgreSQL 8.4 and 9.2?

texts:
Context:

We currently run PostgreSQL 8.4 in production, and are testing 9.2 internally for a future upgrade. Some issues have emerged regarding dates. The servers that run 8.4 versus 9.2 are identical in every way other than the PostgreSQL versions and configs. The same exact data is stored on both sets of servers; it is transferred using pg_dump and pg_restore.

In one part of our database, we store dates in a field of type timestamp without time zone.

Problem:

When accessing dates from that field, an extract(epoch..) request returns very different results depending on the Postgres version, like so:

#On Postgres 8.4. entered_timestamp is a "timestamp without time zone" type column:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351774899.69961

But...

#On Postgres 9.2. All tables, schema, and data are identical, the SELECT statement is identical to the previous one:
SELECT entered_timestamp, extract(epoch from entered_timestamp) AS entered_timestamp from <table row>

 entered_timestamp      | entered_timestamp
 ----------------------------+-------------------
 2012-11-01 06:01:39.699612 |  1351749699.69961

As you can see, the returned timestamps are identical, but the returned epoch numbers are not; in fact, they are off by several hours. The server dates and Linux time zones on both servers are identical (within a second of each other). The Postgres time zones are also identical: running SELECT current_setting('timezone') on both servers returns the same data as well. Running SELECT now() on both servers returns near-identical values.

Question:

How has the behavior of extract(epoch...) on fields of type
timestamp without time zone changed between PostgreSQL 8.4 and
PostgreSQL 9.2?
Why did this change occur?
Is there any way to prevent this change without altering my schema
or changing past data (i.e. a configuration-based fix)?


texts:
Background/Context

We are in the process of moving our SQL Server 2000 databases to a mirrored SQL Server 2008 R2 server with a witness and automatic failover.  We have both .Net and Access applications running off of these databases, and are testing client redirection on a server failover event.

The .Net apps respond correctly after I added a "Failover Partner" to the connection string. On a failover the app will fail on the next DB request, then move to the mirror server when we restart the app.

Question

For the Access applications going through ODBC we are trying to use the SQL Server Native Client 10.0 driver (it looks like the standard driver doesn't support mirroring?) and supplying a mirror server in the settings.

I'm specifying the database and the failover server name here.  On a failover event the Access application does not seem to be redirected to the mirror server even after an application restart.  Is there anything else I should be doing here to force ODBC to switch to the mirror server, or am I not specifying the mirror server name correctly?


texts: Use of table compression in SQL Server 2014

texts:
I am going through articles about table compression in SQL Server. To me it seems quite easy to setup compressed tables and the benefits make the decision no brainer.

I wanted to consult with this community and see if there is any gotcha that I need to know. I am concern that the table compression feature might not be as simple as it looks. Could there any unwanted side effect when I migrate my tables to compressed table?


texts: High memory usage in Masters with MariaDB and Vitess

texts:
I'm running a Kubernetes Cluster with MariaDB 10.3.20 and Vitess 6. Everything is running fine, but the memory usage in the Master nodes is always around 90%. I don't have OOM right now, but I was keen to understand this high memory usage.
Currently, each node (master and replica) has the same configuration of 10GB RAM and 10 CPU cores. The MariaDB configurations, for now, are the default ones (innodb_buffer_pool_size has 128MB, I will try to update to 70% of the node memory), except for the number of max_connections which is set to 15758.
The data inside database is bellow 1GB and 5 tables. SELECT peak is around 700 queries per seconds, INSERT peak is around 200 QPS and UPDATE 100 QPS.
Masters are receiving only writes (INSERT, UPDATE, DELETE) and replicas are receiving only reads (SELECT).
Below are two charts for memory usage (the top ones are masters, the ones at the bottom are replicas).
The master's memory does an interesting "pattern" every week, not sure why.
I've tried to understand this behavior, reading MariaDB Memory Allocation, but couldn't anything that could explain this.

The chart below has a span of 15 days.


texts:
We have a SQL Server 2008 R2 which has, among others, a 12K RPM HDD and a 7.2K RPM HDD. The original intention was to store the data files on the 12K drive and the log files on the 7.2K drive, as is our usual practice. However, a DBA has advised that this may not be the ideal configuration and that we may see better performance storing both the data and log files on the 12K HDD.

Not being a DBA, I'm quite happy to defer to an expert, but I'm not fully convinced that this configuration is the best approach. I would have thought that there would have been advantages in separating the data from the logs in terms of parallelism regardless of the specs of the HDD's.

What are your thoughts?


texts: How should I migrate a large MySQL database to RDS?

texts:
I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here:

http://aws.amazon.com/articles/2933

My concerns are the following:

Amazon recommends using mysqldump only for "small amounts of data", which they define as less than 1GB. The database I intend to migrate is over 20GB.

One thing that's nice about mysqldump, however, is that it has the --single-transaction flag, which allows me to ensure a DB state that is consistent with a single point in time.

For larger amounts of data, Amazon's recommendation is to export the database into flat (e.g., CSV) files and then use mysqlimport to import those to RDS. The best way I know how to do this, however, is through the SELECT ... INTO OUTFILE command, which only operates one table at a time. The downside to this, of course, is that it doesn't provide the consistency guarantee of --single-transaction.

I suppose I could ensure consistency by taking the entire DB down temporarily; but I'd like to avoid that if at all possible.

What's the best way to get my large (> 20GB) database into flat files so that I can then use mysqlimport?
If it is indeed the SELECT ... INTO OUTFILE command, how do I export all of the tables in the database (preferably without having to do one at a time)?
Is there any good way to ensure consistency throughout all this?


texts: Simplest filesystem backup of Postgres database

texts:
Situation: A backup system performs filesystem-level daily backups on a server containing a large but fairly static PostgreSQL 9.2 database. The filesystem backups are incremental and generally take <30 minutes.

My initial idea was to use the backup system hook scripts to put the database cluster into backup mode:

start-backup hook runs: psql -c "SELECT pg_start_backup('daily');"
backup copies postgres data files somewhere else, ignoring pg_xlog/
backup-finished hook runs: psql -c "SELECT pg_stop_backup();"

My understanding was that (1) would force a database checkpoint, meaning that you have a consistent filesystem in base/ & friends. Further WAL segments would be written during (2) to pg_xlog/ but would leave the base/ files alone. Then when pg_stop_backup() is called in (3) it applies the WAL logs from during the backup to base/ & friends. And if you restore the backup you'd end up with the database state at (1).

But the Postgresql docs describe something different:

  Standalone Hot Backups

  It is possible to use PostgreSQL's backup facilities to produce standalone hot backups. These are backups that cannot be used for point-in-time recovery, yet are typically much faster to backup and restore than pg_dump dumps.

  If more flexibility in copying the backup files is needed, a lower level process can be used for standalone hot backups as well. To prepare for low level standalone hot backups, set wal_level to archive, archive_mode to on, and set up an archive_command that performs archiving only when a switch file exists. For example:

archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'

  This command will perform archiving when /var/lib/pgsql/backup_in_progress exists, and otherwise silently return zero exit status (allowing PostgreSQL to recycle the unwanted WAL file).

  With this preparation, a backup can be taken using a script like the following:

touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/`

  The switch file /var/lib/pgsql/backup_in_progress is created first, enabling archiving of completed WAL files to occur. After the backup the switch file is removed. Archived WAL files are then added to the backup so that both base backup and all required WAL files are part of the same tar file. Please remember to add error handling to your backup scripts.

That's fine - I need to enable wal_level=archive to get pg_start_backup() to work anyway, and can easily create & remove a switch file. But getting the backup system to revisit the filesystem (for the final step above) isn't possible...

From what I can tell, the above approach from the Postgres documentation would get me a backup of the database state as at pg_stop_backup() (the latter part via replaying the archived WAL segments during startup).

But it's quite possible my understanding is wrong, and base/ does change during the backups, which means you need the archived WAL segments as well.

So, do I need to copy the WAL segments from the time between pg_start_backup() & pg_stop_backup() calls in order to get a usable backup?


texts:
Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks.
There is a high accordance between the tables (and views) affected by the deadlocks and the tables being diagnosed as

"Aggressive Indexes: Total lock wait time > 5 minutes (row + page)"
(Explanation).

There it is adviced to check missing indexes on those tables. Ok, there are indeed misssing indexes with high potential suggested by Sql Server for those tables...
But I am actually trying to understand the exact meaning of the information given by sp_BlitzIndex and the reasons for them appearing.
Here are some samples popping up in deadlock chains as well as "Aggressive Indexes":

dbo.O.IX_O1 (48): Page lock waits: 133; total duration: 5 minutes; avg duration: 2 seconds; Lock escalation attempts: 98; Actual Escalations: 19.
dbo.O.IX_O2 (57): Row lock waits: 468; total duration: 5 minutes; avg duration: 0 seconds;

dbo.O is a heap.

dbo.P.IX_P1 (18): Row lock waits: 6; total duration: 19 seconds; avg duration: 3 seconds; Page lock waits: 52; total duration: 4 minutes; avg duration: 5 seconds; Lock escalation attempts: 196; Actual Escalations: 19.
dbo.D.IX_D1 (14): Row lock waits: 587; total duration: 30 minutes; avg duration: 3 seconds; Lock escalation attempts: 30; Actual Escalations: 0.
dbo.S.PK_S (2): Row lock waits: 697; total duration: 44 minutes; avg duration: 3 seconds; Lock escalation attempts: 709; Actual Escalations: 1.
dbo.E.IX_E1 (18): Row lock waits: 108; total duration: 5 minutes; avg duration: 3 seconds; Lock escalation attempts: 4; Actual Escalations: 0.

Questions:

Brent Ozar unfortunately does not really explain their meaning and ways to workaround, besides re-engeneer indexes. So what does it mean and why is it there? Is it basically because READS and/or WRITES on those tables are simply too slow and the resulting locks take too much time?

In case I go ahead and add the missing indexes having the most impact - won't this just increase the time required to perform insert / update statements and maybe this way increase the chance to trap into deadlocks - or the other way round, will removal of unused indexes for a table reduce the chance of deadlocks because I/U statements will be faster?


texts: Transactional Replication failing between SQL Server 2012 and SQL Server 2016 versions

texts:
As per the Microsoft documentation - A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example:

  a SQL Server 2012 (11.x) Publisher can have SQL Server 2014 (12.x) and
  SQL Server 2016 (13.x) Subscribers;

and

  a SQL Server 2016 (13.x) Publisher can have SQL Server 2014 (12.x) and
  SQL Server 2012 (11.x) Subscribers.

But the subscription I am trying to create from

  Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) to
  Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)

is failing and here is the error message I receive -

  The selected subscriber does not satisfy the minimum version
  compatibility level of the selected publication.

Are these versions not compatible?


texts: MySql Switching Masters During Failover with log-slave-updates

texts:
I am still pretty newbie to mysql. This question is regarding promoting slave with option --log-slave-updates to become a master.

The typical schema of promoting slave to master was described in 15.3.6 Switching Masters During Failover. Here you can find very strict steps how to do it :

  Run your slaves with the --log-bin option and without --log-slave-updates. The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master.

Let's say I have the schema like the following (the image was taken from 15.3.5. Improving Replication Performance).

Where Master 2 is actually a slave with --log-slave-updates and bin-log enabled. I need to enable --log-slave-update in order to save bin-log from the Master 1 to bin-log of Master 2 and then transfer it to all slave. In case of failover I need promote Master 2 to the role of Master 1. But there is a contradiction to the standard MySql failover schema I have --log-slave-updates enabled which can cause a problem.

In your opinion. What the action I should take in order to do this?


texts: Azure Readable Secondary - How to: Different Access Policy to Primary?

texts:
We have primary databases. They are in a mix of elastic pool, and standalone.
We have successfully created active geo replicated secondaries. (Our use for these is solely workload isolation.)
We want want to have different access policies. e.g A reader (by whatever principal - AAD group user etc) can only access the secondary.
This Microsoft Docs page implies it is possible to use different credentials for the secondary:

An application can access a secondary database for read-only
operations using the same or different security principals used for
accessing the primary database"

There are no further instructions. When attempting to use T/SQL grants on the secondary, an error is given that the DB is read only. This is expected behaviour, however the MS documentation quoted above suggest a different mechanism is possible.
How can we manage access at the secondary level, other than granting at the primary? Or granting excessive rights at the secondary, such as Active Directory Admin?
(We are aware of intent read only, but that does not meet our need, which is to deny certain principals access to the primary, regardless of intent)


texts: SSMS: Limit a login to be able to view and work only with some databases, but not able to make any changes on the server level

texts:
On a shared cloud we would like to work with one instance of MS SQL Server. Different clients have various databases. They can be owner of their databases and with DENY VIEW ANY DATABASE TO PUBLIC in SSMS they can only see their databases. That works fine. To connect to SSMS they need the CONTROL SERVER permission. But even after a DENY of all the server level permissions they are able to do many things in SSMS, like restart the sql server services. Of course this should not be possible for such users.
How can a user be configured not to be able to do any actions to the server itself?


texts: Select rows from table 1 only if all corresponding rows have certain status in the join table

texts:
These are the tables

Orders

id
status

Order_lines

id
order_id

Invoice_lines

id
invoice_id
order_line_id

Invoices

id
status

I would like to retrieve only orders whose invoices are in approved or closed status. If any invoices are in open status, that order should not be selected

I tried this

 SELECT o.id FROM orders o INNER JOIN order_lines ol ON ol.orders_id = o.id
                     INNER JOIN invoice_lines il ON il.order_line_id = ol.id
                     INNER JOIN invoices i ON i.id = il.invoice_id
                     AND i.status IN ('approved', 'closed')

I keep getting orders which have invoices in open state. This is not the result I want. I want orders for whose invoices are only in approved and closed status


texts: Should a privileged port (<1024) be used to deploy Postgresql in production?

texts: I've never found a recommendation to run Postgresql on a privileged port in production. What type of port should be used in production regarding security and best practices?


texts: utl_raw.bit_and attaches extra 0 after and operation in oracle sql

texts:
For a given binary number, say length longer than 128, I want to check whether a particular bit is on or off (say 30th bit) I am using utl_raw.bit_and function to do and between 2 binary numbers. I expect the input binary number to be longer than 128 and I can't use oracle bitand function then. When I do a bit_and, I see extra 0 appended to the result. I am not sure how it got in there when the length of both the input are the same. Here is my sample sql

SELECT
A,
LENGTH(A) LENGTH_A,
B,
LENGTH(B) LENGTH_B,
UTL_RAW.BIT_AND(TRIM(A),TRIM(B)) AND_AB,
LENGTH(UTL_RAW.BIT_AND(TRIM(A), TRIM(B))) LENGTH_AND_AB FROM (
SELECT
    TRIM(RPAD('0', LENGTH('1111111111111100000000000000011') - 30    ,'0')) || '1' ||TRIM(RPAD('0', 29, '0')) A,
'1111111111111100000000000000011' B FROM DUAL);

This is the result of the operation

A                                 LENGTH_A  B                               LENGTH_B  AND_AB                           LENGTH_AND_B
0100000000000000000000000000000   31        1111111111111100000000000000011 31        00100000000000000000000000000000 32


texts: Return only rows under certain constraints in join

texts:
I am using PostgreSQL 11.6 and I have two tables: a parent and a child table.  The child table is associated to the parent in that it has a parent_id column and a parent can have many child rows associated.  The child also has a status column that I'd like to filter on.

Here are my requirements.  I need to return all rows from the parent table where either:

there is NO associated child row present
there are child rows present but none of those rows have a value in their status column of at_home

E.x.

parent table
+----+
| id |
+----+
|  1 |
|  2 |
+----+

child table
+------------------------+
| id  parent_id   status |
+------------------------+
| 1   1       at_school  |
| 2   1       at_shop    |
| 3   1       at_home    |
+------------------------+

My query should return only the parent with an id of 2 since that parent doesn't have an associated child record.  The parent with an id of 1 should only been returned if none if it's present child records had an at_home status.  With my query below, it is currently still being returned (which is incorrect).

I'm trying a query like this right now but it's not working because I'm filtering on child where the status is != to at_home, but it still returns a parent record because rows 1 and 2 of the child records above match that condition:

SELECT parent.* FROM parent LEFT OUTER JOIN child ON child.parent_id = parent.id WHERE (child.id is NULL OR child.status != 'at_home')

Any help would be appreciated.  Thanks!


texts: Oracle 12.2 : Is there a way to copy pdb from one server to another in production env

texts:
From time to time, customers need to send their database to us for analysis. We like the PDB feature of Oracle 12.2, and we are willing to use it for shipping our customer's database for analysis

Can a customer create the db file while the database is up and running? We need them to keep the database up while they create the back up file.


texts: Get missing index statistics in SQL Server 2014

texts:
For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and missing index problem.

Is there any way to get missing index statistics for all SP and queries?

thanks


texts: What is the algorithm used for Index Recommendation in SQL Databases?

texts:
I have used tuning adviser tool in SQL Server which provide index recommendation based on a given workload.

I want to create a similar tool for PostgreSQL. I went through some research papers. But I am unable to find the exact algorithm used by SQL Server Tuning Adviser to provide index recommendations.

If anyone is aware of the algorithm or any source from where I can get the algorithm please guide me. Please note that I want to provide recommendation based on a workload, not for a single query. Thanks in advance.


texts: Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

texts:
Is there any different how mysql treats

UNIQUE (column1, column2),
INDEX (column1)

and

UNIQUE INDEX (column1, column2)

and

UNIQUE (column1, column2)

In the latter, I think mysql will automatically creates index for UNIQUE to avoid duplicate records. Am I right?


texts: showing locks from all postgres relations

texts:
I'm trying to show all locks (DBMS-wide) in Postgres.

I initially tried adapting the documented example to:

SELECT pg_database.datname AS database, relation::regclass, pg_locks.database AS db_oid, pg_locks.relation AS rel_oid,
        locktype, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted
    FROM pg_locks
    LEFT OUTER JOIN pg_database ON (pg_locks.database = pg_database.oid)
    ORDER BY database, relation;

but Postgres seems to only be able to resolve the names of relations in the current catalog:

    database    |                relation                 | db_oid  | rel_oid |   locktype    | page | tuple | virtualxid  | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |           mode           | granted
----------------+-----------------------------------------+---------+---------+---------------+------+-------+-------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------
 franknfurter   | pg_locks                                | 2881378 |   11000 | relation      |      |       |             |               |         |       |          | 40/1709521         |  9684 | AccessShareLock          | t
 janet          |                                         | 3820378 | 3820698 | relation      |      |       |             |               |         |       |          | 97/3461972         | 19937 | AccessShareLock          | t
 janet          |                                         | 3820378 | 3820698 | relation      |      |       |             |               |         |       |          | 97/3461972         | 19937 | RowExclusiveLock         | t
                | pg_database_datname_index               |       0 |    2671 | relation      |      |       |             |               |         |       |          | 40/1709521         |  9684 | AccessShareLock          | t
 brad           |                                         |  772666 |  772946 | relation      |      |       |             |               |         |       |          | 28/1711346         |  9317 | ShareUpdateExclusiveLock | t
 franknfurter   | idx_posts_user_id_deleted_at            | 2881378 | 2883250 | relation      |      |       |             |               |         |       |          | 21/2032902         |  9602 | RowExclusiveLock         | t
 franknfurter   | pg_class                                | 2881378 |    1259 | relation      |      |       |             |               |         |       |          | 40/1709521         |  9684 | AccessShareLock          | t
 franknfurter   | posts_pkey                              | 2881378 | 2881707 | relation      |      |       |             |               |         |       |          | 21/2032902         |  9602 | RowExclusiveLock         | t
                |                                         |         |         | virtualxid    |      |       | 97/3461972  |               |         |       |          | 97/3461972         | 19937 | ExclusiveLock            | t

is this just a limitation of postgres or am I doing something differently?


texts:
May I know what's the problem with this? I keep on getting this "SP2-0003 Ill formed" on the timestamp. How can I solve this?

ACCEPT v_cheindatetime timestamp FORMAT 'DD-MON-YY HH:MI:SS'
  PROMPT 'Enter Checkin Date & Time : '
ACCEPT v_cheoutdatetime timestamp FORMAT 'DD-MON-YY HH:MI:SS'
  PROMPT 'Enter Checkout Date & Time : '

--execute the INSERT statement
INSERT INTO booking VALUES
('&v_cheindatetime','&v_cheoutdatetime');


texts:
I'm essentially trying to find a better way to determine if my job has completed successfully before moving on to another call of the same job. I have multiple jobs which run through a parsing process before calling this one job in particular, but I don't want all the job calls to clash and ruin everything. What I have in place now to account for this looks like:

This basically just checks to see if the job_ID persists in the sys_jobs_view and sysjobactivity. If so, wait. If not, break and execute job again.

WHILE EXISTS (SELECT
            job.Name, job.job_ID
            ,job.Originating_Server
            ,activity.run_requested_Date
            ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
        FROM
            msdb.dbo.sysjobs_view job
                INNER JOIN msdb.dbo.sysjobactivity activity
                ON (job.job_id = activity.job_id)
        WHERE
            run_Requested_date is not null
            AND stop_execution_date is null
            AND job.name like 'myJob%')
        BEGIN
            WAITFOR DELAY '00:00:05.000'
        END
EXEC msdb.dbo.sp_start_job 'myJob'

Is there a way to just capture the exit code and have something that looks like:

pesudo-code:

WHILE (*Capture Exit code for 'myJob'* = In_Progress)
BEGIN
    WAITFOR DELAY '00:00:05.000'

    IF (*Capture Exit code for 'myJob'* = Fail)
    BEGIN
        BREAK
    END
END

IF (*Capture Exit code for 'myJob'* = Success)
BEGIN
    EXEC msdb.dbo.sp_start_job 'myJob'
END


texts: Exporting Very Large Database from mysql 5.5

texts:
Before I proceed, I would like to confirm can I export a very large table of 3.6GB size using the MySQL Admin Export feature. I have exported tables of small size like 28 MB but just wondering exporting such a large database would break anything?

Say for example, in the image below, I want to export category table which is of 3.6GB size.

Also, I have more tables whose size is 11.6 GB for which I would like to do the export in the future.

I want to use this technique rather than using mysqldump.


texts: MySQL trigger if new value is not equal to varchar

texts:
StackExchange, I'm trying to create a trigger in MySQL (before insert) that gives an error if the new value of "sifra" is not S, P, K, A or Z, but it I'm getting an error even if the said criteria is met.

BEGIN
DECLARE msg VARCHAR(40);
DECLARE sifra VARCHAR(1);
SET sifra = NEW.sifra;
IF (sifra <> 'S' OR sifra <> 'P' OR sifra <> 'K' OR sifra <> 'A' OR sifra <> 'Z' ) THEN
    set msg = "Sifra mora biti S, P, K, A ili Z.";
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
END

Any ideas?


texts: SQL Server Configuration Manager shows no services

texts:
I have installed Microsoft SQL Server Management Studio 2008 on my system. When I tried to open it for the first time, it asked me for the "Server Name" when I surfed I came to know that I have to check the instance name in Configuration manager-->Sql Services, but I couldn't find any services running in Configuration Manager, it is showing "There are no items to show"

I tried to find out but couldn't.

What am I doing wrong?


texts:
I'm utilising Azure SQL Database 2019.
Let's say I have columns A-F and the following indexes.

Primary Key (clustered): A
Index 1: B (ASC),C (ASC)
Index 2: B (ASC)

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?
SELECT A,B,C,D,E FROM table WHERE B = 'X'


texts: Transaction basics. What is the result of 2 transactions running concurrently?

texts:
I have a rather basic question on transactions.
Assume a table Account with a column amount. Now we have the following code:

BEGIN
$amount = SELECT amount FROM ACCOUNT WHERE id = 123;
$amount = $amount - 100;
UPDATE ACCOUNT SET amount = $amount WHERE id = 123;
COMMIT

Now if this snippet runs concurrently by 2 transactions, T1 and T2 what will happen?
I know that in MySQL by default the isolation level is REPEATABLE READ.
So let's say that amount initially is 500.
So when the 2 transactions finish will the final amount be 300 or 100?
I mean when they start both read from table which I guess is a shared lock.
When one updates the table it gets an exclusive lock right? So what happens to the other transaction? Will it proceed with the amount it "view" when it started i.e. 500?


texts: Why does LEN() function badly underestimate cardinality in SQL Server 2014?

texts:
I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is yielding very poor plans because there are actually thousands or even millions of rows and SQL Server is choosing to put this table on the outer side of a nested loop.

Is there an explanation for the cardinality estimate of 1.0003 for SQL Server 2014 while SQL Server 2012 estimates 31,622 rows? Is there a good workaround?

Here is a short reproduction of the issue:

-- Create a table with 1MM rows of dummy data
CREATE TABLE #customers (cust_nbr VARCHAR(10) NOT NULL)
GO

INSERT INTO #customers WITH (TABLOCK) (cust_nbr)
    SELECT TOP 1000000
        CONVERT(VARCHAR(10),
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
GO

-- Looking for string of a certain length.
-- While both CEs yield fairly poor estimates, the 2012 CE is much
-- more conservative (higher estimate) and therefore much more likely
-- to yield an okay plan rather than a drastically understimated loop join.
-- 2012: 31,622 rows estimated, 900K rows actual
-- 2014: 1 row estimated, 900K rows actual
SELECT COUNT(*)
FROM #customers
WHERE LEN(cust_nbr) = 6
OPTION (QUERYTRACEON 9481) -- Optionally, use 2012 CE
GO

Here is a more complete script showing additional tests

I have also read the whitepaper on the SQL Server 2014 Cardinality Estimator, but didn't find anything there that clarified the situation.


texts: get previous 12 month data and if not exist then add zero in mysql


texts: Command fails as CmdExec job but succeeds from console

texts:
I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command:

powershell -ExecutionPolicy Bypass -Command "$objServiceManager = New-Object -ComObject 'Microsoft.Update.ServiceManager'; $objService = $objServiceManager.AddService2('7971f918-a847-4430-9279-4a52d1efe18d',2,''); $objService.PSTypeNames.Add('PSWindowsUpdate.WUServiceManager');"

The service account that SQL Server Agent is running under has sysadmin privileges to the instance, but does NOT have Admin access to the server.

If I RDP into the server as the Agent's service account, I can execute the command successfully from a command prompt:

But, if I execute the SQL Server Agent Job, it fails with an "Access Denied" error:

Exception calling "AddService2" with "3" argument(s): "Access is denied.
(Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"
At line:1 char:79
+ $objServiceManager = New-Object -ComObject
'Microsoft.Update.ServiceManager'; $o ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

You cannot call a method on a null-valued expression.
At line:1 char:170
+ ... efe18d',2,'');
$objService.PSTypeNames.Add('PSWindowsUpdate.WUServiceManager');
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

I have found that if I set the "Run as" for the job to a proxy account that has sysadmin privileges on the instance AND Admin access to the server, it succeeds, but that's not really relevant to my question.

EDIT: I'm executing everything as CmdExec, and not Powershell, because I need to execute an unsigned Powershell script (not included in my code, since it's not causing a problem).  I get the same error if I change the Type to Powershell.

My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the same server?


texts: Microsoft Access is giving me network interrupted errors

texts:
We have just upgraded to window 10 and are using Microsoft Access 2016 (16.0.8431.2110) 32 bit.

As a result of this upgrade we now have the issue of intermittent network connectivity issues. My Access database session work will just lose the network connection, tables will be corrupted and the following error message will be displayed:

  Your network access was interrupted. To continue, close the database, and then reopen it again.

Needless to say this is problematic as we are using 2016 AutoPLANT SS8 v8i with AutoCAD 2016 Service Pack 1 that is constantly reading/writing to the database. The result is a file crash and a reload is necessary.

Any one out there in the world have an idea of what is happening?


texts:
Well, the problem is well known, but I'm looking for a smarter solution if there's one.

For some reason the system doesn't recognize some characters and I can't compare the columns

Here is an example of the text:

Right

  ASPIRADOR ULTRASSONICO-LOCAÇAO (NOTA FISCAL SERVIÇO)

Wrong

  ASPIRADOR ULTRASSONICO-LOCA€AO (NOTA FISCAL SERVI€O)

Actually I'm fixing this through this function

create function fixcollation(@ps_Texto VARCHAR(4000)) returns VARCHAR(4000)

as

begin

    declare @vlgsv1itu INT declare @nxn68ezzi INT declare @dw17rsyva  VARCHAR(50) declare @iw8a2z01i VARCHAR(50) declare @t64e98xq6 VARCHAR(50) declare @zwjs2imy3 INT declare @jsyt85sy8 VARCHAR(4000)

    ----------------------------------------------------

    set @dw17rsyva = ' …Æƒ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãåâ™£—–éëêš‡€§'
    set @iw8a2z01i = 'áàãâäÁÀÃÂÄéèêëÈÉÊËíìïÍÌÏóòõôöÓÒÕÔÖúùûüÚÙÛÜçÇºØ'
    set @jsyt85sy8 = @ps_Texto set @zwjs2imy3 = IsNull(datalength(@ps_Texto), 0)
    set @nxn68ezzi = 1
    while(@nxn68ezzi <= IsNull(datalength( @ps_Texto), 0))

    begin

        set @vlgsv1itu = 1

        while(@vlgsv1itu <= IsNull(datalength(@dw17rsyva), 0))
        begin

            IF(ASCII(SUBSTRING(@ps_Texto, @nxn68ezzi, 1) COLLATE LATIN1_GENERAL_CS_AS) = ASCII(SUBSTRING(@dw17rsyva, @vlgsv1itu, 1) COLLATE LATIN1_GENERAL_CS_AS))
            BEGIN
                set @t64e98xq6 = SUBSTRING( @iw8a2z01i, @vlgsv1itu, 1) set @jsyt85sy8 = SUBSTRING(@jsyt85sy8, 1, @nxn68ezzi -1) + @t64e98xq6 + SUBSTRING(@jsyt85sy8, @nxn68ezzi + 1, @zwjs2imy3 -  @nxn68ezzi)
                break
            end
            set @vlgsv1itu = @vlgsv1itu + 1
        end
        set @nxn68ezzi = @nxn68ezzi + 1
    end
    return @jsyt85sy8
end

So, my question is: is this the best way or have I missed something here?

EDIT

Just a complementary test

select dbo.fixcollation(' …Æƒ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãåâ™£—–éëêš‡€§')
select dbo.FixCodePage850toCodePage1252(' …Æƒ„µ·Ç¶Ž‚Šˆ‰ÔÒÓ¡‹ÖÞØ¢•ä“”àãåâ™£—–éëêš‡€§')

And this is the result in my production environment

fixcollation

FixCodePage850toCodePage1252

My personal thanks to Solomon Rutzky


texts: Dependency preserving and lossless join decompositions

texts:
I have a relation:

R = {A, B, C, D, E, F, G, H, I, J}

And a set of functional dependencies:

G = {AB -> C, A -> DE, B -> F, F -> GH, and D -> IJ}

I ended up decomposing into two decompositions.

D1 = {R1, R2, R3, R4, R5}

      R1 = {A, B, C}
      R2 = {A, D, E}
      R3 = {B, F}
      R4 = {F, G, H}
      R5 = {D, I, J}

D2 = {R1, R2, R3, R4, R5}

      R1 = {A, B, C, D}
      R2 = {D, E}
      R3 = {B, F}
      R4 = {F, G, H}
      R5 = {D, I, J}

How can I prove that D1, D2 are dependency preserving and lossless join decompositions?

Related question that got me as far as the decompositions but I am lost.
2NF decomposition + database normalization


texts: What can become a reason of database failure?

texts:
I'm the new one with SQL Server, so, please don't beat me.

It's known that one of the best ways to protect your database from the failure is to make regularly scheduled backups.

But my question is: what can become a reason of database failure?

I know that this is a quite wide question, but I would like to know something from your experience.


texts:
Consider this CTE:
WITH division_by_zero AS (
  SELECT 1/0
)
SELECT 42

It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?


texts: How to Determine the input and output values of stored procedures?

texts:
I have hundreds of SPs and I would like to find out:

input parameters with type
output fields with type (not output parameters)

Of course I could manually go through each one and write it down but where is the fun in that...No, literally where IS the fun in that :)

Can this be done or does Sql Management Studio 2008 R2 have this capability already?

I do not even know where to start so any answer is acceptable.

Edit for enhancment of question:
If we look at it as 2 different tasks, could we accomplish this easier. Even using reflection on the client side. (A quick and dirty console app would suffice.)


texts: Want to change a timestamp date to an integer YYYYMMDD

texts:
I have this input "2017-07-21 15:31:04.853453" and I want it to return "20170721" as output.  How can I do it using cast or convert function?

My column input is the current time (CURRENT_TIMESTAMP).

From cast(CURRENT_TIMESTAMP as date) I have the output "2017-07-24", then I can do several concatenates with substrings. It should work but I wonder if there isn't any other way to get "integer YYYYMMDD".


texts: After upgrade to SQL Server 2019 some stored procedures fail

texts:
We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150).
We have two different stored procedures that started failing after the upgrade, with error messages like this:
Msg 8632, Level 17, State 2, Procedure BuildSelfSaleStats, Line 14 [Batch Start Line 4]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Whats really strange is that this particular stored procedure doesnt take any arguments, and when we simply execute the body of the SQL code in SSMS, it works fine(!?).
What might cause some SQL code that works fine when executed in SSMS, to suddenly start failing when its wrapped in a stored procedure?
We managed to isolate the error to a date formatting function we had:
This works when run in SSMS:
select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
output:
(No column name)
2019-12

But when called in testproc, it fails:
CREATE PROCEDURE testproc
AS
BEGIN
    SET NOCOUNT ON;
    select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
END

exec testproc

Msg 8632, Level 17, State 2, Procedure testproc, Line 9 [Batch Start Line 11]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.


texts:
At a client site with a development Oracle 11.2.0.3/linux DB

power outage and crash - no archivelog mode - no backups - Don't know how this was even running

select file#,substr(name,1,70) name, recover, status, fuzzy, checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time from v$datafile_header;

FILE# NAME                                               REC STATUS  FUZ
CHECKPOINT_CHANGE# CHECKPOINT_TIME      RESETLOGS_CHANGE# RESETLOGS_TIME
1 /oracle/oradata/orcl/system01.dbf                 NO  ONLINE             YES         4663440487 08-MAR-2018 02:57:17            945184 04-APR-2012 12:07:59
2 /oracle/oradata/orcl/sysaux01.dbf                 YES ONLINE             YES         3759618229 21-DEC-2016 22:19:41            945184 04-APR-2012 12:07:59
11 /oracle/oradata/orcl/rmdata01.dbf                 YES ONLINE             YES         3759618229 21-DEC-2016 22:19:41            945184 04-APR-2012 12:07:59

Basic plan:

alter database datafile 2 offline drop;
alter database datafile 11 offline drop;
recover database ;
export/import
rebuild DB

Don't care about sysaux, however, I hope to keep whatever is in datafile 11.

I was hoping to force the DB open even with datafile 11 in a bad state.

Oracle says any hidden parameters will not help due to very old SCNs.

Have you faced this situation and successfully recovered the data?


texts: Why do I see sp_OACreate, sp_OASetProperty in sp_WhoIsActive?

texts: I've seen this happen a few times: sp_WhoIsActive shows various sp_OA% sprocs, with my user name, but I'm certainly not running them directly.  Does this reflect some kind of query rewrite process?  It seems to be correlated with queries across remote servers.


texts: Make MySQL database accessed by just one user

texts:
I have MySQL server, and it have a lot of databases and users, and i need to create a user and a database for it, and only this user can access this database, even the root user can't access this database.
how can i do it


texts:
So, let me give you some context.

I started to work for this company just a few weeks ago, and I'm a jr sysadmin, not a database administrator. Actually, my boss started the same time. The previous boss did not knew what he was doing, so the company hired us to fix a lot of things.

We have a partner that developed the main system we use here and the database(postgres) backup routine was automated by then (old boss had no clue how to do it). My boss asked this partner if the backup/recovery routine was working, but the response was not confident, so he asked me if I could performe a test.

I've done backup/recoveries in databases before, so no problem, but when I  got the backup file, to my surprise, it was not a pg_dump file, but a bunch of folders named like 'base', 'pg_clog', 'pg_logical', etc. So, their backup was just a shell script that copy all database folders. That base folder alone is more than 32Gb.

My question is: is this a standard or acceptable way of doing a database backup? I ask because I was wondering if, in case a fatal hardware failure happens in the server, how would I recover this database in another server from this folders?


texts: SQL 2005 SP2 Database on SQL 2012 Instance

texts:
I've an SQL Server 2005 SP2 instance that contain old databases that are not compatible with other SP of SQL Server 2005.
Those databases are on physical server that don't have any space to backup them so i planned to put them on a new virtual machine on a SQL Server 2012 instance with compatibility level at 90.

But i want to know if the compatibility level of the 2012 instance is equivalent of an SQL Server 2005 SP4 because if is this i can't put the database on it.


texts: Couchbase Ghost entry - Have it in view, not in data itself

texts:
I have a 4 server setup.

In them I have a view like this:

function (doc, meta) {
    if(meta.type == "json") {
        if(doc.type && doc.type == "saveDocument") {
            emit(meta.id, null);
        }
    }
}

The problem is that right now I have as first reply from it record with key ""cache::1433576113.9028" but when I try to Remove it from the system all I am getting is "Key not present" error.

Please advice.


texts: Cannot restore database with transaction log

texts:
I make every day at 0300 AM a Full Backup (NOT a COPY-ONLY) of a database (11 GB) and every minute (from 03:05AM till 02:55AM) a TRANSACTION LOG Backup.
When I tried to Restore to a point in Time eg 03:07AM there is an error that the backupsets are not inline.

When I took a look at the backupset table in msdb database I see all the backups (FULL en LOGS), but the first_lsn of the First TRNLOG after a FULL Backup is not the same as the last_lsn of the FULL backup, but it is the same the last_lsn of Prev TRNLOG (which is before the FULL backup).

So it is not possible to restore the FULL backup and eg the First TRNLOG backup.

What is going wrong in my backup process?

See image below


texts: A trigger that show the user that has made an insertion

texts:
I have the following code

create table o_c(
op_id NUMBER(10,0) PRIMARY KEY NOT NULL,
n_c VARCHAR2(50) NOT NULL,
op VARCHAR2(500) NOT NULL,
amount NUMBER(9,2)
);

create table a_o(
a_id NUMBER(10,0) PRIMARY KEY NOT NULL,
details VARCHAR2(500) NOT NULL
);

create sequence a_o_seq
START WITH     1
INCREMENT BY   1
NOCACHE
NOCYCLE;

And I need a trigger that writes in "details" of each record this message:

User username performed an insert operation. The data added were saved with op_id = value, the operation was performed on the date DATE


texts: Do user database settings affect maintenance job times for system databases?

texts:
I have an SQL Server 2008 and for maintenance solutions I use Ola Hallengren scripts. Recently, out of 15 databases I have on this server, I made two changes in three of them (not system databases, but user databases). I removed mirroring and changed recovery model from full to simple. This done running sql commands on SQL Server Management Studio.

From the day I made these changes, I noticed strange behavior on Ola Hallengren's sql job for integrity check and full backup on system databases. To understand, while 2 minutes were enough to run and finish the job, now it needs approximately 10 minutes for the same job. As a result, at this time I/O increases on SQL server and it gives timeouts.

I don't know and I didn't find anywhere if running the following commands could possible influence system databases.

ALTER DATABASE <database_name> SET PARTNER OFF
and
USE master ;
ALTER DATABASE <database_name> SET RECOVERY SIMPLE ;

where database_name is the users database I made the change, not the systems.

Is it possible made those changes affect anything on system databases?


texts:
I am running log shipping with SQL server 2008 R2.

I have a situation where the secondary database drive ran out of space and was not applying log shipping transaction logs.

The way I want to fix this is delete the databases at the secondary and configure log shipping from scratch.

The problem I have now is that my secondary databases are in the restoring state, and I cannot delete them. How can I proceed?

For instance if I try to take them offline I get the error,

ALTER DATABASE is not permitted while the database is in the Restoring state.


texts: Create a MySQL database with charset UTF-8

texts:
I'm new to MySQL and I would like to know:

How can I create a database with charset utf-8 like I did in navicat?

create mydatabase;

...seems to be using some kind of default charset.


texts: How do I know if an index has been successfully created in MySQL?

texts:
I use Capistrano to deploy a Rails app. Capistrano handles running database migrations via essentially a shell script that runs from my local computer.

About 24 hours ago I started a migration that added a compound index to my table (on 3 columns, to be specific, and 9 million records). I returned to my office this morning and the script still hadn't completed.

However, at this point, I have no idea if it's just that my local connection to the database timed out, even though it continued processing the index.

My question is, how do I know if the indexing process has completed?

When I view the table via a MySQL client (Sequel Pro), I do see the indexes listed (with cardinality equal to the correct number of rows). But I'm not sure if these would be listed anyway as part of the initial creation of the indexes.


texts: Failover stragtegy for replicant db via 3rd party

texts:
We use a Multi-tenant SaaS product hosted in the providers network. They provide us a 3 replicate db for our data, updated approximately every 30 min. We used this replicant database for our read heavy web application that does reporting etc.

The problem we are seeing frequently is their replication fails. Depending on which replicant database  crashes, this can cause the loss of whole tables and  our front end system.

What strategy can improve our fault tolerance from 3rd parties replication problems?


texts: Schema for lists of relationship differences

texts:
Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item <- InheritedItem <-> *AddedProperties, *RemovedProperties, *ChangedProperties. How would such a model be best represented in a relational database?

Having thought about possible schemas, I've come up for two general options for storing the list of changes:

Two or three different tables for added, removed, changed properties.
A single table that has a change type flag and all the necessary columns for all three change types.

Regarding change tracking, there are again two options I've come up with so far:

Storing changed properties as two foreign keys, one to the old property, one to the new property.
Giving the Property table a nullable foreign key to the original property and recording the change as as an added and a removed property.

Clarification: This question relates to additions & deletions in the sense of version control (i.e. an object of this kind was replaced with a new object that has these changes relative to the parent object), not in the sense of object inheritance.


texts: storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?

texts:
I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for Thunderbird.
It is now becoming impossible to do so and I have been thinking about moving the emails to a DB as they come in and process them there and then, the processor needs to access the body and header. What database engine is best for this task mysql,postgre,etc?


texts: How to set up from scratch distributed transaction between SQL Server and Oracle?

texts:
For one of our client, I have to solve a problem regarding intermittent transactions failures between an SQL Server 2012 DB engine and an Oracle 12c DB engine.

(OLE DB provider "OraOLEDB.Oracle" for linked server "xxxx" returned message "Impossible de s’enrôler dans la transaction.")

The equivalent in english is "cannot enlist in the  transaction"

Before looking toward the problem itself, and in order to understand this architecture/behavior, I would like to set up a test environnement (db engines are already installed).

What are the correct steps to do this ? I know Internet is full of ressources, and I know there are severals components here in use (dblink, MSDTC, OLE provider) but I do not kwow how to assembly themselves correctly, and where.

A detailed answer witch each steps from an expert would be gratfully welcome !


texts: Drop multiple columns at the same time from a SQL Server table

texts:
I am currently looking some way to delete multiple columns at the same time from a given table in sql server.

One way of doing that is to use ALTER statement as shown below:

Alter Table [USER_DB].[dbo].my_Table
DROP COLUMN "ID",
            "Name",
            "Relationship"  ;

I am using above statement in some stored procedure to drop the columns and it's working fine. However, it creates problem when I re-ran this statement and columns are already dropped.

I found some nice solution on SO which doesn't fails even when columns are dropped.

IF EXISTS(SELECT 1
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME='my_Table'
          and column_name= 'ID'
          and Table_SChema='DBO')
  BEGIN
    ALTER TABLE my_Table
    DROP COLUMN
     ID
  END

I tried above statement to drop multiple columns at the same time but it's not working.

I was looking something like this.

IF EXISTS(SELECT 1
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME='my_Table'
          and column_name= 'ID'
          and column_name= 'Name'
          and column_name ='Relationship'
          and Table_SChema='DBO')
  BEGIN
    ALTER TABLE my_Table
    DROP COLUMN
     ID
    ,Name
    ,Relationship
  END


texts: Unable to restore database backup because it is accessed by other connection

texts:
--first command

use master

Go
-- second command
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Go
-- Third command
RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\AcctDB.bak'
WITH  FILE = 1,
MOVE N' AcctDB '
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct.mdf',
MOVE N' AcctDB _log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10

GO

--forth command

ALTER DATABASE MyDB SET MULTI_USER

Go

I run the above commands, one by one to restore database in different server. But sometime I cannot restore the database after changed to single user mode, because it is accessed by other connection. If I run the whole script except the last part together would that block other connection so I will be the only user?


texts: What can cause DBCC CheckDB to take longer than usual?

texts:
I have a maintenance task, which do a DBCC CHECKDB WITH PHYSICAL_ONLY on the Databases on my server (SQL Server 2016 Standard Edition).
This usually takes about 7h30 for about 120 databases and it is an acceptable time. But once in a while (like yesterday), it took 10 hours to do this operation on the same amount of databases.
From the logs, I know that every DBCC CHECKDB took longer than usual to run. It's not only one DBCC CHECKDB that took 3 hours more. It's all individual CHECKDB that took a few minutes more.
The CHECKDB are running during the night, when clients are still using the Database, but where there's less activity.The databases have increased in size,
 but not that much that it would cause a 3 hours increase.
Other than client's activity on the Database, which can cause some delays, what can cause DBCC CHECKDB to take a much longer time than usual? I need to at least have an explanation to give to my boss as to why once in a while it does this and then reverts back to a normal time.
Thanks


texts: SQL Server TDE - Database backups and post restore behaviour

texts:
Below is my SQL Server details:

SQL Server 2014 Enterprise SP2 GDR with Availability Group configured.
List item

NOTE: The Databases are FileStream Enabled.

Below are the series of actions I did recently and need your help to understand and correct myself:

Enabled TDE (Structured Data .mdf & .ldf)
Enabled Encryption File System (EFS) for Unstructured data
After both the configurations , now the Database is on TDE Enabled and EFS Enabled in AOAG .
I took a backup of the Database (From what I have learnt  post TDE & EFS the backup of the Database is encrypted as well using Database Encryption Key Algorith . Is this point correct ?)

I removed the Database from AOAG. Later deleted the database from all AG Replicas and restored it using encrypted file taken above and joined them to AG again.

My Actual question is :

Post taking a backup of the database by using Encrypted file above and restoring it on the same server , the database is now freed from both TDE & EFS , Can some explain me what happened here ?
Why the Database is not encrypted when I used a encrypted Database Backup file !
Does the backup file post restoration gives decrypted files automatically by default behavior ?

Current the Is_encrypted value for the database is now "1" earlier before retore it was "3".

How can I make sure (or) is there way at all when I restore a encrypted backup I can see the database is encrypted as well ? Is my thinking wrong , kindly share some insights on this point?


texts: I am designing a MySQL db for a forms program we use for tracking the steps our techs take when working on a machine. Currently I use integers to store the answers to the questions. We have the question and then they hit complete or hit see notes and enter notes below. The issue is that the forms change constantly. Sometimes they add tasks, and take them away. Which messes with the order because the column headers are like Question 1 through question however many. What is the best way to approach designing this especially if we need to be able to see and create reports based off of these entries.


texts: SQL Server datetime column datatype stores offset

texts:
Does the SQL Server datetime column have the capability to store the offset of the time?

I'd like to store a value like

YYYY-MM-DDThh:mm:ss±hh

e.g.

2015-07-14T10:08:15+01

But when I insert the above value in the datetime column, the error that SQL Server returns is as follows:

insert into TestTable(testdatetimeoffset) values ('2015-07-14T10:08:15+01')

  Msg 241, Level 16, State 1, Line 1 Conversion failed when converting
  date and/or time from character string.

I also tried

insert into TestTable(testdatetimeoffset) values ('2015-07-14 10:08:15+01')

>

  Msg 241, Level 16, State 1, Line 1 Conversion failed when converting
  date and/or time from character string.

and

insert into TestTable(testdatetimeoffset) values ('2015-07-14 10:08:15 01')

  Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value. The
  statement has been terminated.

If datetime is unable to support the offset, what are the best ways to do this?


texts: I Want To insert data in transaction table and at the same time update customer table in codeigniter

texts:
(codeigniter) I Want To insert data in transaction table and at the same time update customer table .
I want to Add value of ChangeDue in transation table To value now_debt in Customer Table

Function in transaction Model

public function add($_iN, $_iC, $desc, $q, $qs, $_up, $_tp, $change_due,
$cn) {
$data = ['itemName' => $_iN, 'itemCode' => $_iC, 'description' => $desc,
'quantity' => $q, 'quantitys' => $qs, 'unitPrice' => $_up,
'totalPrice' => $_tp,'changeDue' => $change_due,'cust_id'=>$cn];
     $this->db->insert('transactions', $data);
     $this->db->set('customer.now_debt', 'customer.now_debt' + $change_due);
     $this->db->where('customer_id', $cn);
     $this->db->join('transactions', 'transactions.cust_id =
     customer.customer_id');
    $this->db->update('customer');
    if ($this->db->affected_rows()) {
        return $this->db->insert_id();
    }
    else {
        return FALSE;
    }
}


texts:
I have followed the mongodb documentation to create a sharded cluster. I have created a config server replica set and shard replica set with 3 members each.Following is the procedure I followed as per the docs:

Step 1: Created config server replica set with 3 members:

mongod --configsvr --replSet "myrepl" --dbpath <path1> --port 27019
 mongod --configsvr --replSet "myrepl" --dbpath <path2> --port 27020
 mongod --configsvr --replSet "myrepl" --dbpath <path3> --port 27021

Step 2: Connected a mongo shell to one of the mongod instances:

mongo --host localhost --port 27019

Step 3: Initiated the replica set in the mongo shell created above:

rs.initiate(
  {
    _id: "myrepl",
    configsvr: true,
    members: [
      { _id : 0, host : "localhost:27019" },
      { _id : 1, host : "localhost:27020" },
      { _id : 2, host : "localhost:270" }
    ]
  }
)

Step 4: Created shard replica sets:

mongod --shardsvr --replSet "shardrepl"  --dbpath <path1> --port 37019
 mongod --shardsvr --replSet "shardrepl"  --dbpath <path2> --port 37020
 mongod --shardsvr --replSet "shardrepl"  --dbpath <path3> --port 37021

Step 5: Connected to one member of the replica set and initiated the replica set:

mongo --host localhost --port 37019
 rs.initiate(
  {
    _id : "shardrepl",
    members: [
      { _id : 0, host : "localhost:37019" },
      { _id : 1, host : "localhost:37020" },
      { _id : 2, host : "localhost:37021" }
    ]
  }
)

Everything is fine till here, the problem is in the next step:

Step 6: Connect mongos to sharded cluster:

mongos --configdb "myrepl"/localhost:27019,localhost:27020

Further it is stated to connect a mongo shell to this this mongos instance using
mongo --host <hostname> --port <port> But it's not explicitly stated which host or port to use here. What host or port should I use here? And where should I execute the further commands to add shards?

Although I had a strong feeling this won't work (and it didn't :p), I tried executing mongo --host localhost --port 27019 and executed sh.addShard("localhost:37019") ,it gave an error- "no such command addShard"

Kindly help me out here,thanks!


texts:
1) I have table

CREATE TABLE tor
(
  events_date timestamp without time zone NOT NULL,
  message text
  CONSTRAINT tor_pkey PRIMARY KEY (events_date, message)
)PARTITION BY RANGE (events_date);

2) There is a default partition where the data gets, if there is no partition where you can put it

CREATE TABLE tor_part_default partition OF tor default;

3) There is a trigger on an INSERT to tor_part_default:

CREATE OR REPLACE FUNCTION create_next_part()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    partname VARCHAR(11);
    startdate VARCHAR(10);
    enddate VARCHAR(10);
BEGIN
    partname = 'tor_'||to_char(NEW.events_date, 'YYYYMM');
    startdate = to_char(NEW.events_date, 'YYYY-MM-01');
    enddate = to_char(TO_DATE(startdate,'YYYY-MM-DD') + interval '1 month','YYYY-MM-DD');
    RAISE NOTICE '    Start date: %',startdate;
    RAISE NOTICE '      End date: %',enddate;
    IF NOT EXISTS (SELECT 1 FROM   information_schema.tables WHERE  table_name = partname)
    THEN
        RAISE NOTICE 'Partition created: %',partname;
        EXECUTE 'CREATE TABLE '||partname||' partition OF tor FOR VALUES FROM ('''||startdate||''') TO ('''||enddate||''')';
        EXECUTE 'INSERT INTO '||partname||' VALUES(NEW.*)';
    END IF;
    RETURN NEW;
    END;
    $BODY$;

CREATE TRIGGER tor_create_next_part
    BEFORE INSERT
    ON tor_part_default
    FOR EACH ROW
    EXECUTE PROCEDURE create_next_part();

When I try to insert data, as a result I get an error:

ERROR:  cannot CREATE TABLE .. PARTITION OF "tor" because it is being used by active queries in this session
CONTEXT:  SQL statement "CREATE TABLE tor_201606 partition OF tor FOR VALUES FROM ('2016-06-01') TO ('2016-07-01')"
PL/pgSQL function create_next_part() line 16 at EXECUTE

What am I doing wrong or is there another way to create partitions automatically?


texts: Why query plan changes by number of rows

texts:
I execute following two queries on an oracle database:

select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48507

which returns result in less than a second and with following execution plan

and

select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48508

which takes more than 10 seconds to execute and uses following execution plan

Can anyone help me understand the reason why this strange behavior happens?


texts:
I have two MySQL 5.7 Servers (5.7.29 and 5.7.31)
I have created a set of SSL certificates and copied them to both the source and replica DB's so they both have the same certificates in place.
On my Source DB, I have set up a user:
CREATE USER 'ssltest'@'%' IDENTIFIED BY 'ssltest';
GRANT ALL ON *.* TO 'ssltest'@'%' REQUIRE ISSUER '/C=UK/ST=. . . . ';

On my replica DB, I then connect to the source DB using:
mysql -h1.2.3.4 -P3306 -ussltest -pssltest --ssl-ca=ca-cert.pem ssl-cert=client-cert.pem  --ssl-key=client-key.pem

This connects fine, and I verify the connection with:
mysql> show session status like 'ssl_cipher';
SSL_Cipher | ECDHE-RSA-AES128-GCM-SHA256

However if I try and connect replication using:
CHANGE MASTER TO
MASTER_HOST =  '1.2.3.4',
MASTER_PORT = 3306,
MASTER_USER = 'ssltest',
MASTER_PASSWORD = 'ssltest',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154,
MASTER_SSL=1,
MASTER_SSL_CA='ca-cert.pem',
MASTER_SSL_CERT='client-cert.pem',
MASTER_SSL_KEY='client-key.pem';`

I just get an "error connecting to master" on the LAST_IO_ERROR.
What is missing? Why does the user work for a regular connection, but not replication?


texts: SQL statement to hide all system databases in Server from a server login

texts:
I want to create a Windows authentication server login and make it cannot see any system databases e.g.master, model.

I know that we can control view definition in database level e.g. database user, database role but I don't want to configure them to all system databases.

Therefore, I started by:

Create Windows authentication login
Create custom server role
Deny view definition to a custom server role
Add an login to a role

I hope the login shouldn't see all system databases but it still can see master and tempdb under System Databases node.

This is the screenshot of the result.

and these are my SQL statements.

-- Create login from Windows User
USE MASTER
GO

CREATE LOGIN [domain\web_user] FROM WINDOWS WITH
DEFAULT_DATABASE = MyTestDB
GO

USE MyTestDB
GO

CREATE USER web_user FOR LOGIN [domain\web_user]
WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember @rolename = 'DB_DataReader',
@membername = 'web_user'
GO

--Create custom server role, deny view definition and add login to role
USE master
GO

CREATE SERVER ROLE junior
GO

DENY VIEW ANY DEFINITION TO [junior]
GO

ALTER SERVER ROLE junior ADD MEMBER [domain\web_user]
GO


texts: Applying Multiple Window Functions in 1 SELECT using different partition

texts:
I was wondering if there is anything wrong with using this method.

I have a working demo of what i'm trying to do below

In the SELECT statement I have used 2 OVER clauses.

Is there any risk that the data could get out of sequence?

CREATE TABLE basic_pays (
    employee_id int,
    fiscal_year INT,
    salary DECIMAL(10 , 2 ),
    PRIMARY KEY (employee_id, fiscal_year)
);

INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2017,24000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2017,17000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2017,18000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2017,9000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2018,25920);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2018,18190);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2018,18360);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2020,26179.2);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2020,19463.3);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2020,19278);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2020,10206);

SELECT
    employee_id,
    fiscal_year,
    salary,
    LAG(salary)
        OVER (PARTITION BY employee_id ORDER BY fiscal_year) previous_salary,
    SUM(salary) /SUM(SUM(salary))
        OVER (PARTITION BY fiscal_year ORDER BY fiscal_year) salary_percentage
FROM
    basic_pays
GROUP BY
    employee_id, fiscal_year, salary
ORDER BY fiscal_year, employee_id

DROP TABLE basic_pays


texts: How do I deal with items that I need to repeat monthly?

texts:
I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every month. How should I store this recurring item?

We thought just add in 12 new entries so it atleast covers the year (The users will want to look a few months into the future) but then the problem is that if the cost changes, we have to update all following entries

Our other option was to create another table which would store the key of the original with the dates or day of the month that it needs to repeat on?

Is there a better solution than these?


texts: Performance improvements for query (maybe faulty query)

texts:
I hope you can help me.

I have three tables:

Table 1 with 6155 data sets (user information)
Table 2 with 15 933 data sets (answers of a questionnaire)
Table 3 with 595 120 data sets (pages visit by user)

I want to join all three tables into one to see which user visited which page and what answers did he submit on the questionnaire
with the following query:

SELECT user.email, user.expdata, tp.tstamp as tp_tstamp, mq.tstamp AS mq_tstamp, mq.question1, mq.question2, mq.question3, mq.question4, mq.question5, mq.question6, mq.question7, mq.question8, mq.question9
FROM table1 AS user
INNER JOIN table2 AS mq ON user.id = mq.userid
INNER JOIN table3 AS tp ON user.id = tp.userid
WHERE user.email = "test@test.de"

When I submit this query, the browser loads and loads until no results will be shown. I tried to submit these queries one by one:

SELECT user.id, user.email, user.expdata
FROM table1 AS user
WHERE user.email = "test@test.de"

==> results in 1 data set

SELECT tp.tstamp AS tp_tstamp
FROM table3 AS tp
WHERE tp.userid = "1"

==> results in 172 data set

SELECT mq.tstamp AS mq_tstamp
FROM table2 AS mq
WHERE mq.userid = "1"

==> results in 4 data set

I don't have much experience with sql so I don't know if my query is exactly what I want. I hope you can advice me to a nice solution.

Just ask, if you need some more information, thanks!

Edit: ids & tables where not the same...

DDL:
Table1

CREATE TABLE `table1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                        `email` varchar(255) NOT NULL DEFAULT '',
                        PRIMARY KEY (`id`),
                        KEY `email` (`email`)
                      ) ENGINE=MyISAM AUTO_INCREMENT=6862 DEFAULT CHARSET=utf8

Table2

CREATE TABLE `table2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                        `tstamp`int(10) unsigned NOT NULL,
                        `question1` int(10) unsigned NOT NULL,
                        `question2` int(10) unsigned NOT NULL,
                        `question3` int(10) unsigned NOT NULL,
                        `question4` int(10) unsigned NOT NULL,
                        `question5` int(10) unsigned NOT NULL,
                        `question6` int(10) unsigned NOT NULL,
                        `question7` int(10) unsigned NOT NULL,
                        `question8` int(10) unsigned NOT NULL,
                        `question9` int(10) unsigned NOT NULL,
                        `userid` int(10) unsigned NOT NULL DEFAULT '0',
                        PRIMARY KEY (`id`)
                      ) ENGINE=MyISAM AUTO_INCREMENT=16370 DEFAULT CHARSET=utf8

Table3

CREATE TABLE `table3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                        `tstamp` int(10) unsigned NOT NULL,
                        `userid` int(10) unsigned NOT NULL DEFAULT '0',
                        `pageid` int(10) unsigned NOT NULL,
                        PRIMARY KEY (`id`)
                      ) ENGINE=MyISAM AUTO_INCREMENT=618813 DEFAULT CHARSET=utf8

MySQL Version: 5.6..


texts:
Is there a way to get resource usage statistics by login in SQL Server?

Ideally, I'd like to have a version of sys.dm_exec_sessions that tracks usage for all sessions, rather than just the currently-connected ones. Then I could just run something like this:

select login_name, sum(reads) as reads,
       sum(writes) as writes,
       sum(cpu_time) as cpu_time,
       count (login_name) as sessions
from sys.dm_Exec_Sessions
where login_name is not null
group by login_name

Obviously this runs currently, but only shows statistics for currently-connected sessions.


texts:
I have a table of millions of records (user contribution). sometimes, our content team find duplicate content (as it is almost the same context, this represents about 0.1%) and they asked to make redirect to the original content/article.
Now:

I can add a column to the same table to point to the original article to be redirected to

create table article (id int primary key auto_increment, body text,
title varchar(128), ....., redirect_to int,
FOREIGN KEY (`redirect_to`) REFERENCES article (id));

Or to have this 1-M relationship in separate table as below

create table article (id int primary key auto_increment, body text,
title varchar(128), .....,);

create table article_redirect (article_id int, redirect_to int,
FOREIGN KEY (`article_id`) REFERENCES article (id),
FOREIGN KEY (`redirect_to`) REFERENCES article (id));

From Database Administration concepts and standards, which option is better and why?


texts: Problems installing Postgis extension on Ubuntu 14.04

texts:
I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple:

sudo apt-get install postgresql-contrib postgis postgresql-9.3-postgis-2.1

However, this throws an "unmet dependencies" error:

Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:
The following packages have unmet dependencies.

postgis : Depends: libgdal1h (>= 1.9.0) but it is not installable
    Depends: libgeos-c1 (>= 3.4.2) but it is not going to be installed
    Depends: liblwgeom-2.1.3 (>= 2.0.0) but it is not going to be installed

postgresql-9.3-postgis-2.1 : Depends: libgdal1h (>= 1.9.0) but it is not installable
    Depends: libgeos-c1 (>= 3.4.2) but it is not going to be installed
    Depends: liblwgeom-2.1.3 (>= 2.1.2) but it is not going to be installed
    Depends: libproj0 (>= 4.8.0-1) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

I've seen quite a few people having this issue, but haven't found a good solution yet.

The closest question/answer to this that I found so far is this one, however it deals with Ubuntu 12.04, so I feel my question is not a duplicate.


texts:
I have a table with following definition:

create table json_test (
    filter_data jsonb);

and i insert to it values like this:

'{"task_packets": [
    {
        "state": "PROCEEDING",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'

Now I want to update this jsonb column to:

'{"task_packets": [
    {
        "state": "DONE",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'

i.e. I want to change state for value with specified task_id inside array of task_packets. I suggest somehow to use jsonb_set() function in combination with #- operator (first delete value from array, then append to it with updated state).
How can I do it?


texts: How to Bulk Insert sqlalchemy subclasses

texts:
I am trying to bulk insert SQL-Alchemy Subclasses into the parent table and their respective tables ie fruits tables -> Apple Table and so I insert a table of APPLE and it will insert both the row into the fruits table then give me the id of the row in fruits table and put it Apple
This works when inserting one row at a time, but I need it to work with bulk insertion due to performance

I have tried to bulk insert which failed and I tried single row insertion it works with single row insertion but the thing is this data is not really unique except for the id of the row which is auto-generated so its going to be really hard to do a bulk insert to the parent table then do a bulk insert into the subclass table where the data matches and use the id by a mapping function

            for data in apple_list:
                db.session.add(Apple(
                    brand=data["brand"],
                    picked_date=data["picked_date"],
                    type=data["type"],
                    color=data["color"],
                    sub_type=data["sub_type"],
                ))

what I want is something more like bulk insertion

          db.session.bulk_insert_mappings(model_classes['Apple'], apple_list)

Actual results are that when it tries to insert it errors out on the insertion due to it not having the foreign primary key that tells the row for the fruits table to the apple table

Expect to insert without any errors and to populate both tables like when inserting both rows


texts: Sybase Replication rs_subcmp can ignore length?

texts:
I want to use rs_subcmp utility to sync one table.
I've created a config file but I got the following error:

Length mismatch for column #9 between the primary (50) and the replicate (100). [c/subcmp.c(3398)]
Found 1 inconsistencies between the primary and the replicate - exiting ... [c/subcmp.c(3483)]

Is it possible to make rs_subcmp ignore this?
I want the column table bigger on the replicate side but this shouldn't be a problem.


texts: Extended event for lock count does not show locks

texts:
I recently came across the article Locking and Performance. It has following statements

If we manually monitor locking activity while this query executes, we see that without the page locking granularity hint, SQL Server acquires and releases over half a million row level locks while seeking the clustered index.
The performance of this query can be improved to 320 ms simply by adding a page granularity locking hint to the Sales table

I am using SQL Server 2012. For identifying the locks, I referred MSDN: How to: Find the Objects That Have the Most Locks Taken on Them. And I used a workload query that joins two tables and selects   1001000 rows. Still it is not showing any locks in the output. It is hard to believe that there is no lock happening.

Why is it not showing any locks? How to correct this?
Is there a better way to identify the type and count of locks?

References - Updated:

Which Locks Count Toward Lock Escalation? - Kendra Little

Use Extended Events to capture Deadlocks in SQL Server

Tracking SQL Server Database Usage - Jonathan Kehayias

Locking and Blocking (2) – Shared Locks and Lock Resource

View locks acquired during query execution (SQL Server) and  beta_lockinfo

Using Extended Events to Find Blocks in SQL Server 2012

Determine Which Queries Are Holding Locks

Extended Event with my Workload
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
DROP EVENT session LockCounts ON SERVER
GO
DECLARE @dbid int

SELECT @dbid = db_id('My_DW')

DECLARE @sql nvarchar(1024)
SET @sql = '
CREATE event session LockCounts ON SERVER
ADD EVENT sqlserver.lock_acquired (WHERE database_id =' + CAST(@dbid AS nvarchar) +')
ADD TARGET package0.synchronous_bucketizer (
SET filtering_event_name=''sqlserver.lock_acquired'', source_type=0, source=''resource_0'')'

EXEC (@sql)
GO
ALTER EVENT session LockCounts ON SERVER
STATE=start
GO

----------------------------- Create a simple workload that takes locks.------------------------------------------------
-- ---------------------------------------------------------------------------------------------------------------------
USE My_DW
GO

SELECT TOP 1001000 *
FROM Fact_Appointment F
INNER JOIN Dim_AppointmentType D
    ON  F.AppointmentType_Key = D.AppointmentType_Key

GO

-------------------------------------------------------------------------------------------------------------------------
SELECT name, object_id, lock_count FROM
(SELECT objstats.value('.','bigint') AS lobject_id,
objstats.value('@count', 'bigint') AS lock_count
FROM (
SELECT CAST(xest.target_data AS XML)
LockData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//BucketizerTarget/Slot') AS T(objstats)
 ) LockedObjects
INNER JOIN sys.objects o
ON LockedObjects.lobject_id = o.object_id
--WHERE o.type != 'S' AND o.type = 'U'
ORDER BY lock_count desc
GO
--
-- Stop the event session.
--
ALTER EVENT SESSION LockCounts ON SERVER
state=stop
GO


texts: for archive_command use an script rather than a command

texts:
I want to create a configuration to get a PostgreSQL HA. In my Master I want to configure a postgresql.conf file with this:

archive_command = 'local_backup_script.sh "%p" "%f"'

instead of:

archive_command = 'cp "%p" /path/to/archive/"%f"'

Then I have a doubt: Where do I have to put the script?


texts: Performance Inserting and Updating Millions of rows into a table

texts:
We have a CSV the user imports into the Application.

After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a table(Subscribers)

We got around 1 millions of inserts and 1 million of updates operations.

Which will be the most effective way to execute them?

1) For the inserts we planned to put them in a CSV file and execute them as a BULK INSERT.

2) What should we do for the updates? Is there something similar to BULK INSERT?

3) Is there any other optimization we should look?

3a) Like for example disabling the indexes while doing this? We should consider the DB should be kept available as normal for the other users of the application...

3b) Suggestions ????


texts: What's the anatomy of a columnstore index?

texts:
One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.

I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between the leaf level and b-tree pages, affects of included fields, optimizing to use them, order of keys, etc.

I'm having difficulty getting any good info on the internals of a columnstore index.

How is it structured?
Is there a b-tree?  Some other structure in place?
How is data organized?
What sorts of specific operators are best suited to use it?
Any other anti-patterns to be avoided when using them?

A lot of what I can find out about them are basically the exact opposite of a "normal" index, i.e. no ordering of keys, no included fields, nonclustered ONLY.

Any insights are appreciated.


texts:
Postgres version: PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Open psql and run query. Takes 80ms.
Run same query again. Takes 11ms
Close psql and open again.
Running same query again results in 80ms first time and 11ms second time.

We are using pgbouncer with Transaction pooling mode.
Same behavior is observed from Java application code.

Only difference is seen in bitmap index scan times.

Bitmap Heap Scan on TABLE_NAME_MASKED  (cost=5.09..304.74 rows=79 width=638) (actual time=5.070..83.190 rows=105 loops=1)

Bitmap Heap Scan on TABLE_NAME_MASKED  (cost=5.09..304.74 rows=79 width=638) (actual time=2.161..12.129 rows=105 loops=1)

Will changing pooling mode of pgbouncer help here?
We usually see 8 TCP connections established between JVM and pgbouncer, so we hope application is not closing and opening psql session.

Any pointers to bring down execution times to 10ms level for most of the queries in production?

Thanks,
Sameer


texts:
I have database for multi store application. So, I have 3 type of user thats Admin, Customer, Merchant and I have 3 table for users. User table, Customer table, Merchant table.

User Table;

id
username
name
password
email
status
city_id
state_id
role_id
...

Customer Table;

id
taxNumber
cart_id
user_id

Merchant Table;

id
taxNumber
commission
user_id

So, there is a 1:0 relationship between merchant/customer tables and user table. I want a 1:1 relationship but if I do this, I have to create columns like customer_id and merchant_id and if customer_id is not null then merchant_id will be null or merchant_id is not null then customer_id will be null in user table. Is it bad for normalization?


texts: How to copy MongoDB database from server to local machine?

texts:
Or just may be dump database on to the folder on the server and then scp it to local machine? I'm trying to use mongodump from /usr/bin, but nothing good comes out of it, for instance:
mongodump --ssl -u root -p password --authenticationDatabase name -d databaseName -o /database
-->Failed: error connecting to db server: no reachable servers
if:
mongodump -u root -p password --authenticationDatabase name -d databaseName -o /database
-->Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed.
tried to do --host=ip.ad.re.ss:27017
tried to do --host=host-name:27017
--host=127.0.0.1:27017
--host=web-address:27017
tried to switch off mongodb...
Tried to copy files manually, but end up with the empty database on the local machine...
tried to use: mongo --> db.copydatabase(from, to, name)
Nothing is happening positive... How to use mongodump? Possible to use any other way. Only through terminal, ubuntu setup doesn't have any gnu.


texts: PostgreSQL: Having many instances of one application should I go with database per instance or schema per instance?

texts:
I am not asking for an opinion here. I need an answer supported by arguments.

We have an web-application that will be installed multiple times on one server (one instance per customer). How should I organize the database for that case? Should I go with database per instance or schema per instance?

Option 1:

DB: appname_customername1, SCHEMA: appname
DB: appname_customername2, SCHEMA: appname
DB: appname_customername3, SCHEMA: appname

Option 2:

DB: appname, SCHEMA: appname_customername1
DB: appname, SCHEMA: appname_customername2
DB: appname, SCHEMA: appname_customername3

How the two approaches compare in sense of performance and memory usage? Is there a difference in administrative tasks/costs?

NOTES:

NO data is shared between tenants and never will be
There is no shared master data and it will never exist


texts: Why does the fill factor in SQL Server default to 0 (100%)?

texts:
I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?

Is there something I'm missing?


texts: Is it a good practice to create tables dynamically in a site?

texts:
A friend asked me to build a site with a few "static" and "dynamic" tables.

In fact he wants to have a few tables which can't be deleted, and some "dynamic" tables which can be created directly from site users, according to their needs.

I.E. if the user needs some "optional" that in the current db doesn't exist, he creates a new table for his specific need.

I think this is not a good way to do it, i think it is better to have a list of all possible optionals in a table and then flag them for each user, i.e.:

Table garden(id, camellia, californa_poppy, azalea) contains a generic garden with 3 flower species that the garden can contain

Table users(id, username, some_user_infos, garden_id) contains a generic user and it's garden id.

If garden_id value from table users is 1, and line with id 1 from table garden is (1, 1, 0, 1) It means that user with garden_id=1 has every allowed flower but California Puppy.

This is my idea.

My friend says it would be better to dinamically create tables and connect them to the user to dinamically define new flowers.

Wouldn't it be better to have every possible flower as a field of the garden table and flag it as i want to do?


texts: A query submitted from different applications has differing DOP

texts:
I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 seconds. However, when executing it from SpotFire (a product from Tibco) it uses only 1 CPU and can take hours to complete.

I am able to replicate the issue in Management Studio if I using the query hint OPTION (MAXDOP 1).

I was thinking that it may be a SpotFire problem, but it uses the Microsoft JDBC driver for connection and I see no connection properties regarding parallelism. I have confirmed using Profiler that the queries from SpotFire and from Management Studio look exactly. the. same. The only difference appears to be parallel execution.

Can anyone offer insight as to why this might occur?

!SOLVED! [kind of]

I reran the SQL Profiler trace and found that the queries are indeed the same, but the way that they are executed is different.

From SpotFire:
declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT a, b, c, d FROM MyView',16,8193,@p7 output
select @p1, @p2, @p7

From Management Studio:
SELECT a, b, c, d FROM MyView

I then added two new events to SQL Profiler and reran the queries:
 - Performance / Degree of Parallelism
 - Performance / Showplan All

This showed the the query from SpotFire was using DOP 0 (BinaryData = 0x00000000) and from Management Studio was DOP 12 (BinaryData = 0x1200000). Next, the SHOWPLAN showed me that the SpotFire query was inserting the results from the view into a temporary table before returning the data to the client.

So why does it do this? Possibly because of the sp_cursorprepexec statement. But why should that cause the DOP to drop to 0? I don't know.

I think that the "solution" is going to be working with SpotFire and possibly tuning the JDBC connection string.

Here is an article from MSDN regarding all connection parameters for JDBC.

Here is another article from someone who had the same exact problem I have, also with no resolution.


texts: Decision criteria on when to use a non-dbo schema vs a new Database

texts:
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out whether to divide my state using seperate Databases or using seperate Schema's in the same database. I've done a little reading on SQL Server Schema's and it seems like a natural way to seperate object domains (which I like), but I'm not sure if there may be hidden costs to this pattern.

What are the more practical things I should consider when selecting between these two approachs? If I avoid the dbo.mytable in favor of myschema.mytable will I be creating other challenges (or problems) for my architecture?

As a side note... At some point this will be handed over to a real DBA to maintain/support so I'm trying to make sure I don't make their lives harder.


texts: How many parallel workers can I have on Postgres?

texts:
I working on a server with Ubuntu, 320 GB SSD, 6 cores and 16GB Ram, but Postgres is having issues some queries that take very long time to run, and with parallel queries, that are increasing the server load a lot.
Some Postgres conf:
max_connections = 150
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 109MB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
effective_io_concurrency = 200

seq_page_cost = 1
random_page_cost = 1.1
cpu_index_tuple_cost = 0.030
cpu_operator_cost = 0.0150
cpu_tuple_cost = 0.06

#parallel_tuple_cost = 0.1      # same scale as above
#parallel_setup_cost = 1000.0   # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

The server is on Linode and i get some info:

#Larger bs
$ dd bs=16k count=10240 iflag=direct if=./test_file of=/dev/null;
167772160 bytes (168 MB, 160 MiB) copied, 1,01442 s, 165 MB/s

#Smaller bs
$ dd bs=2048 count=80000 iflag=direct if=./arquivo_teste of=/dev/null;
163840000 bytes (164 MB, 156 MiB) copied, 9,91975 s, 16,5 MB/s

lsblk -o NAME,MOUNTPOINT,MODEL,ROTA
sda  /          QEMU HARDDISK       1

Linux myClientName 5.7.6-x86_64-linode136 #1 SMP PREEMPT Wed Jun 24 15:41:07 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux

Not sure if the dd test will help, but i find interesting how large is the difference between the small BS and the big BS tests.
If i not wrong QEMU is vitualized system, and the HD may still be and SSD despite the ROTA = 1

Is there any way to be sure how many parallel workers can I have on a Postgres server ?


texts: DB2: What's the fast way to drop a big partitioned table?

texts:
We got a partitioned table with 100000000+ rows, when I drop it it takes 10+ hours and finally failed.

how to speed drop command up ?


texts: TSQL to get the list of associated users from all databases for SQl server Login

texts:
I am looking for tsql query to get all the associated users in all dbs for a login.
Tried below SQL, but this is not giving right result set.
If I execute this query under the Database context where the user is present, it gives output as if user is present in all DBs.
Same query executed under the Database context where the user is absent, gives 0 rows as if user is not present in any of the DBs.
exec sp_msforeachdb 'select  ''?'' AS DBName,dp.name as username from sys.database_principals dp join sys.server_principals
srp on dp.sid=srp.sid where srp.name=''domain\account'''

Thanks


texts: postgresql - Importing CSV Data with Custom Type

texts:
I'm mostly a newcomer to databases and am building a postgres database of student achievement in a series of courses. My database uses a type called "class" that consists of the course, the academic term (year and quarter), and the section number (1,2,3,...), e.g. ('Bio 101', (17, 'F'), 1). I'm trying to copy a CSV containing such data into a table and am getting an error message saying ERROR:  extra data after last expected column. I've tried the pgAdmin III import wizard, COPY and \copy, all with the same results. However, manually copying a line from the file and IMPORTing it (using the appropriate parentheses) works fine. What's going on and what can I do?

Here are the table and some data:

CREATE TABLE public."GradedItems"
(
  class class NOT NULL,
  category text NOT NULL,
  "number" smallint NOT NULL DEFAULT 1,
  topic text[],
  points integer,
  weight double precision, -- Weight of item within category
  CONSTRAINT primary_key PRIMARY KEY (class, category, number)
)

'class','category','number','points'
('LS 30A', (18, 'W'), 2),'Homework',1,60
('LS 30A', (18, 'W'), 2),'Homework',2,36


texts: Master mysq8 slave Mariadb 10.3 Error 22

texts:
Master (mysql  Ver 8.0.16)
Slave (mysql  Ver 15.1 Distrib 10.3.15-MariaDB)

I have this error

  Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file' on query. Default database: 'test.database'. Query: 'BEGIN'

    MASTER my.cnf
    [mysqld]
    collation-server = utf8_unicode_ci
    character-set-server = utf8
    [client]
    default-character-set=utf8

    SLAVE my.cnf
    [client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    character-set-server = utf8mb4


texts: What's the point in mapping users in SQL Server Data Collection?

texts:
In SQL Server 2012 R2 Enterprise, whenever I check Management\Data Collection, right-click --> Configure Management Data  Warehouse , I see whatever users-accounts-logins except the one I am logged in

I cannot understand what's the point in "Map Logins and Users" in Management\DataCollectiion of SQL Server?


texts:
I have manually setup phpMyAdmin (downloaded from official site) on my site.
However, when I enter my User&Pass in login page, i get warning:

When i try same page with HTTPS, it logins. Any idea why this could happen?
tried $cfg['ForceSSL'] = false; but same happens.

I've tried same package on my localhost (where i dont have https) and it logins.