texts: Prevent auto commit of DROP without SET AUTOCOMMIT OFF

texts: I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?


texts: How to deal with unbounded growth in cassandra shards

texts:
Let's say we have a website like youtube where we have a comment session per e video. Users want to be able to comment and to see the comments made by other users by comment creation time. This is relatively easy to shard when you know there will be just a small amount of comments per video or you have a limit on the amount of comments per video. But how can we shard these comments when there growth is unbounded?

I have found very few information about this problem.
https://medium.com/@foundev/synthetic-sharding-in-cassandra-to-deal-with-large-partitions-2124b2fd788b


texts:
I have a table with a composite foreign key like so:

CREATE TABLE a
(
  a1 INTEGER,
  a2 INTEGER,
  a3 INTEGER,
  a4 INTEGER,
  -- other columns
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES b
);

The foreign key may or may not exists, therefore a1 to a4 may be NULL, however if so, they should be NULL all at the same time. Is there a better way to specify such an "optional composite foreign key" than to add a (rather verbose) constraint like so?

  CHECK ((a1 IS NULL) = (a2 IS NULL) AND
         (a3 IS NULL) = (a4 IS NULL) AND
         (a1 IS NULL) = (a3 IS NULL))


texts: How to configure PG text search dictionary through sql

texts:
I want to add terms to a text search dictionary without access to the file system shares/tsearch_data (specifically for google cloud SQL).

Is there a syntax to do this via SQL?

All I have found is "ALTER TEXT SEARCH DICTIONARY.." which can configure how a dictionary works, but cannot be used to add or remove lines in the dictionary .rules/.syn/.dict file.


texts: SQL PROFILER Replay statistics event interpretation

texts:
I tried capturing a replayable trace on a SQL Server 2012  production environment and then replay it against a SQL 2016 test environment.

After doing so , I got the following results :

Can  anyone help me identify what each of the below highlighted term represents :

Total events = 89
Replayed events = 39
Provider errors = 13
Internal    errors = 26
Total errors = 39
Provider hit ratio = 66.67 %
Internal    hit ratio = 33.33 %
Overall hit ratio = 0.00 %
Replay time = 0 hr 2 min 12 sec 38 ms

Will be of great help as I am not able to understand how the upgrade to 2016 might perform based on the profiler results.


texts: How to construct a set-based query for subset problem?

texts:
I have a DB of the following structure (picture shows simplified version)

How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe requiredQuaintity > availableQuantity?

I tried this:

SELECT r.Name
FROM
   Receipe r
   JOIN RecipeIngredients ri ON ri.RecipeID = r.Id
   JOIN Ingredients i ON i.ID = ri.IngredientsId
WHERE
   ri.RequiredQuantity - i.AvailableQuantity > 0

but am not sure this is correct as I think this will only return available ingredients. How should I modify above query to produce only receipees where each of ingredient is available?
Thank you for help

---- Edit ----

Maybe something like this:

SELECT r.Name
FROM
(
    SELECT   r.Name AS Name
             r.Id AS Id
           , CASE (
                WHEN (ri.RequiredQuantity - i.AvailableQuantity >= 0)
                THEN 1
             ) AS Available
    FROM
       Receipe r
       JOIN RecipeIngredients ri ON ri.RecipeID = r.Id
       JOIN Ingredients i ON i.ID = ri.IngredientsId
    WHERE
           ri.RequiredQuantity - i.AvailableQuantity >= 0
    GROUP BY
       r.Id
) AS results
WHERE
    // count of ingredients with value 1 for each recipe == count of all required ingredients for this recipe


texts: Large query with subquery runs indefinitely after mysql update

texts:
We recently did a big server update, in the process migrating from mysql-5.0 to 5.5.40 Several of the queries, which had been running well on the old server, now grind everything to a screeching halt. After some investigation, I figured out the following minimal example of a query that triggers the problem:

SELECT
    (SELECT date FROM results
        WHERE person_id = people.id
        ORDER BY results.date DESC limit 1
        ) AS date
FROM people
WHERE people.boss_id = 123;

Some base info:

Both people and results are MyISAM tables
There are primary key ids on both tables and no other indexes
results is one of our larger tables, currently clocking in at about 4 million rows
people.boss_id = 123 filters down to ~3000 rows
The above the query runs in about 4 seconds on the older mysql-5.0 server
On the newer, mysql-5.5 server the same type of query on the same dataset has run for up to three hours before I manually killed the process, during which it reported as "sending data" My research suggests that that means that the database is filtering the data, but I'm not 100% sure on that.
Running the above query on the newer server produces the same results: stuck query "sending data" until I manually kill the process.
Both tables work fine on their own
I can actually run a single isolated version of the subquery (ie where person_id = 123 or whatever). It takes a few microseconds on the newer server.

Here's what an EXPLAIN EXTENDED looks like:

select_type         table    type   possible_keys   key         key_len     ref     rows    filtered    Extra
PRIMARY             people   ref    boss_id         boss_id     4           const   4217    100.00      ----
DEPENDENT SUBQUERY  results  index  boss_id         date        32          NULL    1       1700.00     Using where

So, any ideas on what's causing this? I'm more than a little stuck. Have I run into some sort of bug? Could upgrading to mysql-5.6 possibly fix this (it's got a new query planner, after all)? Might changing one or both of the tables to InnoDB help? Would repairing one table or the other help?

Incidentally, one of the annoying side effects of this problem is that it locks up phpmyadmin. It's a little unnerving, actually. The commandline works fine and well, but phpmyadmin will just sit there "[loading]" until I kill the rogue process via command line. Any ideas on why a stuck query might do that?


texts:
I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are:

What are all the possible types of  “Reason for early termination of
statement optimization”. I did searching for this in msdn but didn’t
get a complete list of values.
Is there a DMV or extended event to list all queries for which optimization was terminated due to reasons other than Good Enough Plan Found? I referred following two articles which does not list the complete list of possibilities. [Also, they give me different result in my database].

Finding: Query Compilation
Timeout
Identifying Query Plans that are not good
enough


texts: Is "TABLESAMPLE BERNOULLI(1)" not very random at all?

texts:
Whenever I need to return a random record from my tables and performance matters, instead of:

SELECT column FROM table ORDER BY random() LIMIT 1;

I always do:

SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1;

This is much faster but it seems like it's not very random? It looks like a lot of the same records get returned when using this method repeatedly. Is it just me or is this method much less random (and less useful because of that)?


texts:
--Procedures Exercise:
create or replace procedure Display
is
cursor ABC is
select empno, ename, sal
from emp
where deptno=10;
emp_rec ABC%rowtype;
Begin
Open ABC;
Loop
fetch ABC into emp_rec;
exit when ABC%notfound;
end Loop;
dbms_output.put_line(emp_rec.empno||'   '
||emp_rec.ename||'  '||emp_rec.sal);
end;
/

When I execute the following code there is only one output shown for deptno=10, while deptno=10 contains 3 records.


texts: Don't see the Microsoft.Jet provider in my new SQL Server 2008 R2 instance?

texts:
I moved an Excel linked server from SQL Server 2005 (it was built on Jet 4.0, excel 5?) to a new SQL Server 2008 R2 instance (64 bit environment). I matched the settings for both, but it seems the system tables in the Catalogs\defaults.. didn't transfer..

The new server providers list doesn't have the Jet 4 or the Microsoft ACE 12....

How can I install them? hoping that will fix the issue

Thanks in advance,

Shayma


texts:
I noticed this issue when exporting the Oracle SQL execution result from Intellij.

The database version is: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production.

My SQL is

SELECT TableA.seq||'-'||TableB.id doc_id
FROM schemaA.TableA
JOIN schemaB.TableB
JOIN schemaA.TableC
JOIN schemaA.TableD ON ...

The result of SELECT COUNT(doc_id) is 137272, however, the exported CSV file has less than 137272 (sometimes 137258, not fixed) lines.

I checked the result and to my surprise found that there are duplicated doc_id which was not expected.

But if I add ORDER BY doc_id to the SQL statement and re-export the result, everything becomes fine.

I have also used SQL*PLUS to export to CSV, but encountered a similar situation.

I thought it might be caused by paging, so I wrote a Java program to get the data. No luck, the output file also has less than 137272 lines, and with some duplicated lines.

If I change the FetchSize to 1000, sometimes the result is OK. If the fetchsize is 100, it is always wrong.

try (Connection conn = DriverManager.getConnection(JDBC_CONNECTION,
        JDBC_USER, JDBC_PASSWORD)) {
    try (Statement statement = conn.createStatement(); FileWriter fw = new FileWriter("out.txt")) {
        statement.setFetchSize(100);

        try (ResultSet rs = statement.executeQuery(sql)) {
            while (rs.next()) {
                fw.write(rs.getString(1));
                fw.write("\n");
            }
        } catch (SQLException ex) {
            // not shown in this code snippet
        }
    }
}

I have asked here, and someone suggested to me to ask in stackexchange, so I ask again here.


texts:
I have to apply paging on query as per requirement i need two extra columns other than query result
1. Total Records
2. Total Pages

but i am trying to confirm that this the right way to retrieve total number of pages

CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage

WITH CTE
AS
(
        SELECT COUNT(*) OVER() TotalRec, ROW_Number() over(ORDER BY id) RowNo, ColumnOne, ColumnTwo
        FROM
        (
        SELECT ColumnOne, ColumnTwo
        FROM table1 ct LEFT OUTER JOIN table2 IR ON ct.code=IR.code
        WHERE table1.code=3
        Group By  ColumnOne, ColumnTwo

        )ImmCat
)

    SELECT TotalRec TotalRec,
    CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage
    ,ColumnOne, ColumnTwo
    FROM CTE
    WHERE RowNo BETWEEN (((@PageNo - 1)*@PageSize) + 1)) AND (@PageNo * @PageSize))


texts:
We keep getting the error SqlException (0x80131904): Cannot open database in our environment.

We are sure the connectionstring used is correct. The user has the necessary permissions in the database and we cannot see any signs the database is offline in any way.

Any hints on why this error may occur? What to look for using extended events?

The application where this problem occurs has a few clients that connect to a service. This service runs on the same server as the database. The database has synchronous mirroring set up. There are no signs of failover when the problem occur.

There are no other apps that connect to the database. We have so far not been able to catch the error while it is happening, but the users succeed with their task if they retry a minute later.

The connection string used:
Data Source=localhost; Initial Catalog=xyz; Integrated Security=SSPI; MultipleActiveResultSets=True;Transaction Binding=Explicit Unbind; Connect Timeout=5;

Full exception:
System.Data.SqlClient.SqlException (0x80131904): Cannot open database xyz requested by the login. The login failed.
Login failed for user NT AUTHORITY\XYZUSER.

Server stack trace:
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Adapdev.Data.ConnectionScope.get_Connection()
   at Adapdev.Data.AbstractDAO2.CreateCommand(String command)
   at NAFData.FarmaPro.DAL.DAO.BetalingsBetingelseDAO.GetCommandForCriteria(Criteria1 criteria)
   at NAFData.FarmaPro.BusinessLayer.Kunde.KundeClassFactory.FetchBetalingsBetingelse(Int64 sessionId, Criteria`1 betalingsBetingelseCriteria)

Many thanks for any help.

Best regards,
Vemund Haga


texts: Understanding MAXDOP behaviour for queries going parallel

texts:
For my current server as below

4 Sockets with 10 cores per socket and 20 logical processors per socket.

Total 80 per SQL licensing: 4 NUMA nodes with HT enabled

I've set CTOP 50 and MAXDOP 8 on this DB servers with just one database of approx 15 TB which is Log Shipping stand by database ie Read only state

I recently changed the MAXDOP from 0 to 8 as i see lots of crapy queries running over 4-5 hours [not all but most of them] assuming were all over the CPU threads when checked their DOP was 64 all the time.

From above i assumed there could be excessive work being done by specific CPU cores when those heavy query starts coming, while some might still be idle. Not sure if this is how it can work.

SO now i've changed to MAXDOP 8 and see DOP for those queries showing as 8 fpr each individual queries ,say for 10 different queries running at this time.

  How can i test/check if those queries are actually distributing the
  work across all the cores evenly or is it still the case where all of
  them might be doing all the work on one NUMA node socket with 20 cores
  while remaining 60 are idle?

Yes there are performance issues with reports slow but its one of such case where i want to ensure first from my end if there is something that can be done at my server level while developers or people running the reports tune up their queries. Also this being ready only i cant change indexes or control better update to stats on LS restore occurring every 12 hours .


texts:
I have a MongoDB 4.2.1 replica set, that I am trying to setup x.509 membership as documented here

  https://docs.mongodb.com/manual/tutorial/configure-x509-member-authentication/

In doing so, I get the following error when I start up the mongo process.

  "Error connecting to myserver.subdomain.domain.int:27017
  (10.82.24.29:27017) :: caused by :: internal error"

I have tried to look at the standard log

  /var/log/mongodb/mongod.log

and syslog and the kern.log but no other errors are showing up

I have verified that the certificate, Issuer, Subject, Subject Alternate Name and the CA chain are valid. I have also verified port 27017 and the certificate is being served is correct with the following command:

  openssl s_client -connect
  black-duck-data-mart-member-member-3.mia.ucloud.int:27017 -showcerts |
  openssl x509 -noout -text

and adding grep to verify the SAN

  | grep DNS:

Since the error message isn't very useful, I am looking for a way to troubleshoot this problem.

Here is an example configuration file that I am using

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /data/mongodb
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 0.0.0.0
  tls:
    mode: preferTLS
    certificateKeyFile: "/etc/ssl/private/mongodb.pem"
    CAFile: "/etc/ssl/certs/ca-certificates.crt"
    clusterFile: "/etc/ssl/private/mongodb.pem"

# how the process runs
processManagement:
  timeZoneInfo: /usr/share/zoneinfo

security:
  clusterAuthMode: x509

#operationProfiling:

replication:
  replSetName: "replica01"

On the bindIp I have also set it to the FQDN and the IP but to no avail.

Any assistance in this matter would be greatly appreciated.


texts: how to run db.adminCommand() using pymongo for mongodb 3.6

texts:
I want to run this command and get it's output using pymongo module.

db.adminCommand({getParameter: 1, logLevel: 1})

or say I want to run this command

db.adminCommand( { setParameter: 1, disableJavaScriptJIT: false } )

basically I want to get the configurations settings using mongodb shell only. I don't have access to configuration file.


texts: How to do failover between AG setup for FCI-FCI servers

texts:
Hope I can portrait my question well because i really get confused with AG's :)
We have the setup like below-
Windows cluster WKCLU01 with 4 nodes (Node1,2,3,4) and a file share witness in DC3, total 5 votes so ODD# fits in
Out of those 4 node
In DC1 --> SQL FCI Shared storage between Node1 and Node 2 as SQL1\inst1
In DC2 --> SQL FCI Shared storage between Node3 and Node 4 as SQL2\inst2
Now we have to setup AG between DC1 and DC2. below is my understanding:
AG can be setup between 2 replicas here SQL1\inst1 and SQL2\inst2 in ASYNC mode as per limitation and cant use SYNC mode. Assuming this is correct

Is it true automatic failover will happen between each of the FCIs only just like plain old Always on FCI's?

Now how can we do a planned failover between DC1 and DC2 as per our bi-monthly activity? Do we have TSQL or PS to help us automate this. On some msdn links i am confused doing so will cause data loss and somewhere it wont if we change sync mode?

Please suggest


texts: How to loop through tables dynamically and INSERT INTO a temporary table in MSSQL

texts:
I have an MSSQL (2016) DB with multiple tables for accounts of customer that have similar names like bandwidth_timeseries_ACC_1111111, bandwidth_timeseries_ACC_222222 etc. They are identical to each other in design and data types. In addition I have a control table with the following structure:

AccountID      Report_Name   Appear_In_View
111111       QUARTERLY REPORT   1
222222       QUARTERLY REPORT   0
[...]

And in the loop should be included those tables belonging to those accounts where the bit "Appear_In_View" =1 only. Also in each physical table (like bandwidth_timeseries_ACC_1111111) there is a column named "Most_Recent_Data" which is also a bit (0 or 1) and only lines where it's set to 1 should be included.
All the lines from each table should be inserted into a temp table that unions the physical table.

Up until today I had an SP which performed a similar thing only by creating Views that UNION all tables that match the above criteria, however I was advised of a possible performance problems down the road because the union query is growing rapidly in length and that using small INSERT INTO queries in a loop are a better solution.

I know the implementation should be done using T-SQL but I'm not very proficient in writing in this language and would appreciate any assistance that can be offered. Thanks.


texts: MySQL: IF statement check is a table exists

texts:
I run in the following issue and can't figure out how to resolve it.

delimiter #
create procedure pr1()
    begin

        declare v1 double default 0;

        start transaction;
            IF "table1" in (Select `information_schema`.`TABLES`.`TABLE_NAME` FROM `information_schema`.`TABLES`) THEN
                set v1 = (select count(value) from `table1`); -- error here

            ELSE
                -- create table by calling a script

            END IF;

        END #

    delimiter ;

It doesn't go in that branch of the if statement in case the table doesn't exist. Is there any suggestions how to solve this issue or any potential work-around?


texts: MySQL single large table vs many small tables

texts:
I'm building a website to monitor a bunch of IOT devices. E.g. Online/Offline status of each devices and some device specific information it may report back, IP address, Temperature etc this will vary. FYI These devices report back to my site via a processor/computer that poles these devices and then reports back (a maximum of 255 devices but in most cases between 10 - 100 devices).

To date, my approach had been that for each processor I would create a new table with just that processors devices would reside within. However in discussions with a colleague he suggested this might not be the best way to go, as it isn't particularly efficient and could be problematic later on e.g. if you wanted to add another column later on, having to add this to possible 50+ different processor tables etc.

Instead because all these tables would have the same structure e.g. identical amount of columns etc just the amount of devices e.g. rows would vary, would one big table with all these rows was a better way to go?

I know that in MySQL terms "scanning" is an expensive operation, and with one big table I would argue there would be more scanning as I would have to filter as I would have to take one big data set each time, and filter it down into a view, e.g. Processor or location against 5000+ rows vs lots of smaller tables of 100 rows. Also I would argue the data in this table would be written to allot e.g. each time a device goes offline the offline flag is updated, so I'm not sure if that makes it more suitable to a single table vs one large table.

Appropriate there's many different ways of approaching this, I just don't want to go down one rabbit hole and regret it later on. Front end will be PHP if that counts for anything.

*Originally posted on stack overflow put reposted here as the question warranted “opinion based answers”.


texts:
I'm attempting to migrate a relatively large Oracle DB to MSSQL using SSMA however due to disk space constraints, I had to stop the full migration last night when it was 50% of the way through.

Upon investigating disk space usage, I've found that the transaction log file is nearly 3x the size of the data files.
Is there any way I can get this down?
Would you recommend deleting the new DB and starting again from scratch?
Would the Transaction log file have normalised in size if I let SSMA complete the migration?
I've tried shrinking the txn log to no avail.
Any help here is appreciated.
Cheers
Mark


texts:
I have 2 tables load_data and logs.

I have written a trigger on the load_data table (mysql).
The trigger inserts the data into the logs table on Updates

I would like to insert the data in "load_data" only when the 3 columns has following records

If diIndex=3 && diMode=0 && diStatus=1 then only execute insert statement.

I tried this but throws an error

SELECT 1
   INTO @id
   FROM load_data
   WHERE diIndex=3 && diMode=0 && diStatus=1;

   IF @id != 1
   THEN
       INSERT INTO logs(user_id,button_name,flag,box_name)
values("83","Plant","1","Trigger")
    END IF;


texts: MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

texts:
Can someone tell me how to prevent the following commands

 DROP TABLE <tbl-name>;
 TRUNCATE TABLE <tbl-name>;

from being replicated to slaves?


texts:
I am making a Phonegap based ERP solution for school in which every school can configure their own school individually and can manage the whole school from that single application, in which a main administrator configures the school,i.e., adds all the subjects, teachers, classes, timetables, result, etc.

The application also has roles for students, teachers, parents, and admins in which the admin can define the permissions that all the different roles have plus can add and delete the permissions as well.

The application manages the attendance, time table, results, profile of all the students and staff as well.

I can think of only two ways of doing the same,

To maintain a column of school_id in every table that i create
and in all the queries that i make i specify the school_id with it.
To create a different database for every school that registers in the application so that all the school data is not in the same table and the single table is not over populated.

I just cant make out with what design I shall go forward with.

Thanks in advance.


texts: SQL Server hangs on index rebuild, works well after SQL service restart

texts:
We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2.
SQL Server is limited to 64GB of memory.

We are using Ola Hallengren scripts for index rebuilding and optimizations once per week for all our databases.

Every time script triggers, it sometimes hangs on the same index

ALTER INDEX [SecretId] ON [someDB].[dbo].[Articles] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

This is unique identifier field in the database. Table has approx 1.5 mil records.

When index rebuild hangs, we do a failover to another SQL server, restart service on first one, and failover back to instance one. After that, we re-run scripts for index rebuilding, and it passes well in a matter of minutes.

Why do we need to restart service in order to rebuild index successfully?


texts:
I suddenly start to get the message while performing backup, I know i have taken backup of the certificate and stored it in my password manager which I still have access to.
My question is that do we need to have backup cycle of certificate(s) backup? is there any default limit set from Microsoft that we need to take backup every x days. I know ,I took backup of certificate on '2017-02-03 15:57:53.000' i.e. 164 days ago.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.


texts: Stored Procedure doesn't show result in SQL Server

texts:
I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP doesn't return any result. It should return more than 1 rows

 CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS
    BEGIN

        SELECT  [Capital Project] ,
                [Capital Task] ,
                ResourceName ,
                ProjectName ,
                [Project Home Group] ,
                ActualWork ,
                TimeByDay ,
                ResourceStandardRate ,
                ActualWork * ResourceStandardRate AS Dollars ,
                [Project Hierarchy]
        FROM    [IR.CapOnly]
        WHERE   ( TimeByDay >= @StartDate )
                AND ( [Project Home Group] = ( @ProjHomeGrp ) )
                AND ( TimeByDay <= @EndDate )
                AND ( ActualWork > 0 )
                AND ( [Project Hierarchy] = ( @ProjHier ) )
        ORDER BY ProjectName ,
                ResourceName

    END

You can see that in the where clause, Project Home Group (@ProjHomeGrp) and Project Hierarchy (@ProjHier) are the parameters, whose value when supplied (more than 1) SP returns zero values.

The value that I'm passing are:

Start Date: 1/1/2011
End Date: 12/31/2012
@ProjHomeGrp : PHG1,PHG2,PHG3,PHG4,PHG5,PHG6,PHG7
@ProjHier: PROH1, PROH2, PROH3

Let me know for any questions!


texts:
We have a small office setup with one Windows Server 2012 running SQL Server 2016 Express. The database is hosting files for our accountant software. This is a multi module software that runs projecting, accounting, personnel and so on.

One of the four computers connecting to this server is experiencing slower response time on read/write to the database. Network setup is similar for them all, so  I am wondering if this could be some kind of hardware failure, but I don't really have a clue.

Any thoughts before I tear open the box and start switching out components?


texts:
I am looking for a way to order table entries during insertion rather than fetching.
If this is not possible, is there a way to have an auto-increment respective to the primary key? (i.e. if id is primary, ai will increment starting from 0 for every different id inserted)

example:

id | ai
---+---
 0 | 0
 0 | 1
 0 | 2
 1 | 0
 1 | 1
 1 | 2
 1 | 3


texts: How to connect to root with Oracle Developer VM

texts:
I have the Oracle Database App Development VM (12c) and am trying to create a new container. Specifically:

conn / as sysdba
create pluggable database pdborcl admin user system identified by oracle FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/', '/u01/app/oracle/oradata/cdb1/pdb2'');

However, I cannot connect to the root container. The above conn spits out:

  URL           = jdbc:oracle:oci8:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )
  Error Message = ORA-01017: invalid username/password; logon denied
  USER          =
  URL           = jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )
  Error Message = ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

And sqlplus / as sysdba returns:

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

Logging in with sqlplus system as sysdba logs into the connection ORCL.


texts: Convert query for count and updated date

texts:
I currently run the below query in Snowflake for some of my reconciliations and have never worked in PostgreSQL, which we just adopted.
Select count(1), min (LAST_UPDATED_DATE), max(LAST_UPDATED_DATE)
from "SOURCE"."SCHEMA"."TABLE"

I'm looking to do the same thing in PostgreSQL. It's one that we can run against a table that will give us the last updated date and a count when checking that movements completed.
I know Postgres can be time heavy on counts and some of these tables are massive. That aside I'm not sure how to rewrite this to begin with.


texts: How to store likes to posts in Parse.com database?

texts: In my parse database I have a classed called Post and Like. Should I create a field likes in Post class with Relation<Like> type or there is another better option of storing likes in Parse DB?


texts: migrating tables from Postgres to SQL Server 2008

texts:
(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...)

I have a Postgres 8.4.x database with over 100 tables.  I need to recreate these tables in SQL Server 2008.  I'm not concerned about the data, just the structure.

Are there any slick tools, shortcuts or suggestions to accomplish this?  Heck, I'll even take the find-and-replace values to run on Postgres CREATE TABLE scripts!

Thanks!


texts:
This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist).

I thought I was handling the variables correctly, but for some reason I can't get the typing to function properly:

CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name
TEXT,  column_name TEXT, data_type TEXT) RETURNS BOOLEAN AS $BODY$
DECLARE   _tmp text;
BEGIN

   EXECUTE 'SELECT COLUMN_NAME FROM information_schema.columns WHERE
     table_schema='''+|| schema_name+'''
     AND table_name='''+||table_name+'''
     AND column_name='''+||column_name+''''   INTO _tmp;

   IF _tmp IS NOT NULL THEN
     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' already exists in '+||schema_name+'.'+||table_name;
     RETURN FALSE;   END IF;

   EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name,
 table_name, column_name, data_type);

     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' added to '+||schema_name+'.'+||table_name;

   RETURN TRUE;
END; $BODY$ LANGUAGE 'plpgsql';

Here is the error message I get:

  ERROR: operator does not exist: text + unknown

Does anyone know how I can get this to work in 8.4?


texts: Viewing Type Of Lock Being Used T-SQL - Update Lock Query Issue

texts:
I am looking for a way to determine whether SQL will take a shared lock or an update lock.

Take these three queries. I believe in that in theory:

Query one will take a shared lock on the pk, due to using SELECT TOP 1
Query two will use an update lock due to using MIN
Query three... not sure whether the use of group by and order by will make it do a shared lock?

However how do I prove this theory? Also if anyone can tell me how to format sql in these posts that would be great :)

Query one:

/* Uses Shared Lock? */

CREATE TABLE #TABLE1
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

INSERT INTO #TABLE1
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE1
VALUES (2,'Tim', '2013-01-01')

UPDATE #TABLE1
SET Name = 'Jeff'
WHERE ID = (SELECT TOP 1 ID FROM #TABLE1 ORDER BY DateModified ASC)

Query two:

/* Uses Update Lock? */

CREATE TABLE #TABLE2
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

INSERT INTO #TABLE2
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE2
VALUES (2,'Tim', '2013-01-01')

UPDATE #TABLE2
SET Name = 'Jeff'
WHERE ID = (SELECT MIN(ID) FROM #TABLE2)

Query three:

CREATE TABLE #TABLE3
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

CREATE TABLE #StagingTable
(ID int,
DateModified datetime)

INSERT INTO #TABLE3
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE3
VALUES (2,'Tim', '2013-01-01')

INSERT INTO #StagingTable

SELECT MIN(ID), DateModified FROM #Table3 GROUP BY DateModified ORDER BY DateModified

UPDATE #TABLE3
SET Name = 'Jeff'
WHERE ID = (SELECT MAX(ID) FROM #StagingTable)

You may be wondering what the idea with query three is. However if you take a look at the data the date is not in line with the ID column. So the oldest date may not be the lowest ID number, which means the MIN logic in query two is invalid.

Thanks


texts: Amazon RDS: Replacing file based workflows in Oracle RDS

texts:
Oracle RDS has limitations to how it supports different file workflows

External tables are apparently not supported
It is, to my knowledge not possible to mount a disk and use UTL_FILE to read/write to it

There are documents describing procedures for data dump and migration, but the file transfer to and from the RDS instance seem very cumbersome. We have a process that is best described as semi-interactive, so the users are used to being able to mount the data directory over Samba or NFS, and see the results without significant delays.

The question is, what would be the best way to achieve this in Amazon AWS? We're considering and planning to do some light PoCs with the following:

Write some PL/SQL to sync contents in eg. DATA_PUMP_DIR to some EC2-mounted volume
Using AWS Data Pipeline to read and write files to S3, as described here
Using some smaller Oracle installation on an EC2 instance to run operations over DB links
Write custom programs/scripts to run the operations over JDBC or similar and execute them over Lambda, or on some fixed instance
???

Has anyone else dealt with this issue, and built a similar workflow? What is the best way to achieve the results, or should we take some completely different approach here?


texts:
I have setup a master-slave replication between two MySQL servers using mysql  Version 14.12 Distrib 5.0.51a, for debian-linux-gnu.

Recently the replication errored out with this in the logs:

  111128 17:41:12 [ERROR] Slave: Error 'Cannot add or update a child
  row: a foreign key constraint fails (test/bookmap_item,
  CONSTRAINT bookmapitem_bookmap FOREIGN KEY (map_id) REFERENCES
  bookmap (map_id) ON DELETE CASCADE ON UPDATE CASCADE)' on query.
  Default database: 'test'. Query: 'INSERT INTO bookmap_item
  (map_id, ezcontentobject_id, ezcontentnode_id, section, sub_section)
  VALUES(21514, 20205, 27601, 252, 291)', Error_code: 1452

My question is, when using MySQL's replication, is there anyway to keep the slave from enforcing foreign keys?  Or is there something else I could try?


texts:
(pgadmin4:10053): Gtk-WARNING **: Theme parsing error: gtk.css:8645:2: Junk at end of value for padding-left

(pgadmin4:10053): Gtk-WARNING **: Theme parsing error: gtk.css:13132:11: 'shadowbox' is not a valid property name

Can someone please help figure out why? Also help me with a solution.


texts: Archival strategy for a table with 25 million records in SQL Server 2008 R2 Enterprise

texts:
I have a table that stores some audit information and currently has 25 million records. There is a process that inserts to this table every 30 minutes. This is historical data and is not used for any processing.

Since this table already has huge number of records so these 30 minutes inserts are taking considerable long time to complete.

According to me I should archive this huge table and only have a day or so data depending on the business conditions and move rest of the data to a archival table.

One possible strategy that I thought was renaming the existing table to something else and creating a new table with the same name as the old one. Can somebody comment on this strategy or suggest a better solution?


texts: What is the system view for sys.syscolpars?

texts:
I am checking system base table at https://msdn.microsoft.com/en-us/library/ms179503.aspx and system internal views at https://technet.microsoft.com/en-us/library/ms189600(v=sql.105).aspx

For base table sys.syscolpars, is there a corresponding system view for it so that I can access its data without loginning in DAC mode?

Thanks


texts: Can I replicate to a pre-existing database?

texts:
I am going to test this out, however I'd like to know what the forum would say.

I have 2 SQL servers with replication setup between them.

I am now adding another new SQL Server and I want this one to be the new publisher.

Do I need to recreate the databases on the old server when I setup replication again?

Can I simply stop subscription and then begin replicating from the new server to the same databases after I setup the machine?


texts: Automation approach to executing Shell scripts and performing checks on an Oracle DB

texts:
I am in the process of producing an automation framework that will:

Check Oracle tables for entries, such as IDs and status numbers
Log onto a UNIX box via Putty and switch to a directory where the scripts are located
Execute Shell scripts
Perform further checks on Oracle tables depending on values
Repeat above actions (conditional)
Log/notify users of errors during the process above

Would I be able to achieve this purely using Bash/Shell and SQL, or would I need to possibly add another language into the mix?

I was thinking of building it in a modularized fashion.

Thanks


texts: How can i enforce data integrity in this setup?

texts:
I have these tables:

   |-------------tblSchool -------------|
   |               + ID                 |
   |               + Name               |
   |                                    |
   |                                    |
tblSchoolYear                        tblTerm
 | + SchoolID                        | + SchoolID
 | + Name                            | + Name
 |                                   |
 |                                   |
 |---------- tblSchoolTerm ----------|
               + SchoolYearID
               + TermID
               + StartDate
               + EndDate

As you can see, I have 2 tables tblSchoolYear and tblTerm that both have SchoolIDs. These 2 tables then makesup a SchoolTerm. My problem is I want the to prevent the combination of tblSchoolYear and tblTerm in tblSchoolTerm withe different SchoolIDs.

For example, if I have 2 schools with IDs 100 and 101, I don't want to have a SchoolYear and Term in SchoolTerm in which SchoolYear has a SchoolID of 100 and the Term a SchoolID of 101.

What I can think of is using a trigger to enforce this, but I wanted to ask if maybe there is a better design to solve this problem without resorting to triggers.

Using SQL Server 2012 by the way.

ADDITIONAL INFO:

For those asking why I can't just have 2 tables, tblSchool and tblSchoolTerm, I have other columns in tblSchoolYear and tblTerm that contains data that I don't wnat duplicated. Sorry, I'll add some sample columns more to make it clearer.


texts:
I am trying to alter column in redshift from varchar(30) to Varchar(100) Its not working througing an below error.

Command used:

alter table t_name alter column c1 type varchar(300);

Error:

[Amazon](500310) Invalid operation: ALTER TABLE ALTER COLUMN cannot run inside a transaction block; [SQL State=25001, DB Errorcode=500310]


texts:
I'm basically concerned about the following two settings, these are my default settings :

innodb_log_buffer_size  1048576
innodb_log_file_size    5242880

I haven't changed them for 2 reasons : one, I don't know what they are, and two changing this is risky, according to articales I've read

My application data-structure is write-intensive and I use only innodb tables. Currently I've set innodb_buffer_pool_size to 5GB and my database size is around 7GB and increases at the rate of a 400MB per day. Also note, this 400MB insert is done within a period of only about 30 minutes and some might refer to this as "bulk insert"

As far as i understand, log files are for keeping a record of all the insert/update operations, why do I need this?

What are these log files and why should I increase their size?

And lastly, if I don not want to keep a log, how should I fine tune my database then?


texts: My query fails to segment the results as intended. What am I doing wrong?

texts:
Update:
Re-phrasing the question.

Below are the results and the desired results. What do I need to do to get the desired results?  See http://sqlfiddle.com/#!2/d0e13c/69

Thank you

SELECT CONCAT('Greater than or equal to ',t.val2,':', count(*)) FROM
(
SELECT
  CASE
    WHEN val>=1000 THEN 1000
    WHEN val>=500 THEN 500
    WHEN val>=400 THEN 400
    WHEN val>=300 THEN 300
    WHEN val>=200 THEN 200
    WHEN val>=100 THEN 100
    WHEN val>=10 THEN 10
    WHEN val>=5 THEN 5
    WHEN val>=2 THEN 2
    WHEN val>=1 THEN 1
  END AS 'val2'
FROM e
) as t
GROUP BY t.val2
ORDER BY t.val2 DESC

Results:

Greater than or equal to 1000:1
Greater than or equal to 500:1
Greater than or equal to 400:2
Greater than or equal to 300:1
Greater than or equal to 200:2
Greater than or equal to 100:3
Greater than or equal to 10:1
Greater than or equal to 5:1
Greater than or equal to 1:2

Desired results:

Greater than or equal to 1000:1
Greater than or equal to 500:2
Greater than or equal to 400:4
Greater than or equal to 300:5
Greater than or equal to 200:7
Greater than or equal to 100:10
Greater than or equal to 10:11
Greater than or equal to 5:12
Greater than or equal to 2:12
Greater than or equal to 1:14


texts: How to get average according to last update time?

texts:
There is table - Item below, how can I get the average Value according to its 15 minute value from last record time? I am using SQL Server 2008.

Table -- Item

Table 15min_AVG


texts: SQL Server Express vs. Oracle Express Edition vs DB2 Express-C

texts:
I have been trying to compare the features of the SQL Server Express, Oracle XE and DB2 Express-C, but find it difficult to compile all the differences into one place.  Especially the current differences as this is a constantly shifting battle field.  Anyone have this information on hand or know of someone who tracks this?  To the best of my knowledge:

DB2 Express-C (10.5)

cores: 2
mem: 16G (4G before 10.5)
size: 15 terabytes of user data per database

Oracle Express Edition (11g)

cores: 1
mem: 1G
size: 11G

SQL Server Express (2012)

cores: lesser of 1 socket or 4 cores
mem: 1G
size: 10G

Other than these obvious measures, what am I missing?  I know I could go with open source as well, and MySQL and PostgreSQL are both certainly viable solutions, but for the sake of argument, lets limit it to just these 3 options.  In my view, DB2 is the clear cut winner.

Another article on this subject can be found here.


texts:
I am writing to a csv file in a Foreach loop. For each loop the code is writing the header so it appears repeatedly throughout the file.

How do I ensure the header only appears once?

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


texts: Why can't I set oracle sessions value to 35?

texts:
I want to simulate the error "ORA-00018.Maximum number of sessions exceed", so I want to limit the number of sessions to 35, and open serverl sqlplus to simulate this error, but sessions number didn't change

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     248
shared_server_sessions               integer

change sessions from 248 to 35

SQL> alter system set sessions=35 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

restart oracle instance

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             763363792 bytes
Database Buffers          301989888 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.

Nothing changed

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     248
shared_server_sessions               integer

Can anyone tell me why I can't change this value to 35, and how to simulate the "ORA-00018.Maximum number of sessions exceed" error?


texts:
Let's say I have table name Company with two columns cname, location.

In the table I am having two records with

cname   |location

Asmetric |Dallas

Sophore |Washington

on this table when I perform a  delete operation

delete company where cname='Sophore';

I am getting an error

Error 1064:You have an error in sql syntax check the manual that corresponds to your sql server version

whereas i had expected the complete record to be deleted , is the syntax wrong or it's incomplete.Any suggestions welcome


texts: Migrate all databases from one SQL Server 2005 to another

texts:
We have a production SQL Server 2005 server on which we are hosting 200 databases.

We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready.

I want to know the best and the most convenient way to migrate the whole data includes:

Security and permission
Users and memberships
All of the databases and ..

In one word I want to have exactly the same copy of the current SQL Server 2005 on our new instance on the new server.

I will be thankful if you guide me on this case.

Best regards


texts: Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

texts:
I just need advice on how I am going to optimize my database.

| Date | Time | Area | Block | Data1 | Data2 | Data3 | DataN |

I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.

The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.

Now, I am really confused on how I am going to optimize this. Based on this reference on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.

Please help me if I need to use clustered index here or just non clustered or both.


texts:
Given the following schema:

Staff (sid, sname)
Project (pid, pdesc, pfrom, pto)
Asset (aid, acat, adesc)
Workfor (sid, pid, wfrom, wto)
Assignment (aid, sid, pid, afrom, ato)

Note: The bold attributes are the keys.

Find the names of the staffs who worked or are working for more than one project, in Tuple Relational Calculus.

I was thinking of using count() but it is not allowed in TRC (or according to my Prof). Any answers appreciated.


texts: Postgresql - slow distinct on million records table

texts:
I have a table with a million of records, i need to select the distinct values of log_type.
In other queries i also need to filter log_types result of the distinct and limit the result.
But the query is really slow, also this simple one

select distinct log_type
from app_logs;

log_type has an index but nothing change without index, query always use Seq Scan

This is the explain

Aggregate (cost) 119007.80 - 119008.03 (rows) 29 (time) 1923.251
|- Seq Scan (cost) 0.00 - 115796.84 (rows) 1284384  (time) 1300.025

I read other posts but not helped me to find a solution (if exists).

There is any way to improve the performace?
Maybe the only my solution is cache the complete distinct results in server application.

ps. i can't define custom functions on db server


texts:
I'm trying LiquiBase, so for testing I created new schema/user LB_TEST

create user LB_TEST identified by <password>;

grant create session, connect, resource to LB_TEST;

alter user LB_TEST default tablespace USERS;

alter user LB_TEST quota 10M on USERS;

alter USER LB_TEST TEMPORARY TABLESPACE temp;

but when I executed LiquiBase I'm getting

c:\betlista\progs\\liquibase-3.8.7>liquibase.bat --defaultsFile=lb_test.properties update
Liquibase Community 3.8.7 by Datical
Unexpected error running Liquibase: ORA-01950: no privileges on tablespace 'SYSTEM'
 [Failed SQL: (1950) INSERT INTO LB_TEST.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0)]
For more information, please use the --logLevel flag

I do understand what the error is saying, but I do not understand, why I need privileges on SYSTEM TS.

edit:

lb_test.properties

# SQL change log
#changeLogFile: c:/betlista/Git/LiquiBase-tests/changelog.sql
# XML change log
changeLogFile: c:/betlista/Git/LiquiBase-tests/changelog.xml

driver: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521/MDMDEV
username: lb_test
password: <password>

#defaultSchemaName=lb_test

classpath: c:/betlista/progs/no-install/liquibase-3.8.7/drivers/ojdbc7.jar

I didn't add this as obviously there is no problem connecting to DB...


texts:
Per the SQL Server docs, it looks like if I have one or more varchar/nvarchar columns that cause row length to exceed 8KB, SQL Server will magically handle it.

Scenario:
- Table has 100 cols
- Max current row length is 3.4KB
- But if every varchar/nvarchar field was populated to max, the row length would hit 14kb

From how I interpret the Books Online details linked above, this scenario will work out OK (e.g. nothing will break and everything will work).

Is my assumption true?


texts: ERDs: How do you indicate mutually-exclusive encounters?

texts: If you have a character_instance and character entity in an ERD for a video game, and you want to indicate that something happens when a character entity encounters a character_instance entity, how do you do that?  Here's the catch, though: a character_instance doesn't have the same relationship to its own character.  Is there any special way to indicate this complicated relationship?


texts:
When I try to import my database on the production server, I get:

  SQL запрос:

CREATE TABLE `articles` (
  `id_art` int(11) NOT NULL AUTO_INCREMENT,
  `id_section` int(11) NOT NULL,
  `id_ct` bigint(20) NOT NULL DEFAULT '0',
  `fesid` int(11) NOT NULL DEFAULT '0',
  `date_art` datetime(6) DEFAULT NULL,
  `title_art` varchar(250) NOT NULL,
  `meta_art` longtext,
  `short_art` text NOT NULL,
  `full_art` longtext NOT NULL,
  `foto_art` varchar(100) DEFAULT NULL,
  `foto_art_text` text,
  `coordinates_art` longtext,
  `hot` tinyint(1) NOT NULL,
  PRIMARY KEY (`id_art`),
  KEY `articles_fesid_c163d690_uniq` (`fesid`),
  KEY `articles_id_ct_af67450c_uniq` (`id_ct`),
  KEY `articles_id_section_8f87bacd_uniq` (`id_section`)
) ENGINE=MyISAM AUTO_INCREMENT=4060 DEFAULT CHARSET=cp1251;

  Ответ MySQL: Документация

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to
use near '(6) DEFAULT NULL,
  `title_art` varchar(250) NOT NULL,
  `meta_art` longtext,
  ' at line 6

What causing this and do I fix that?

I tried to import with Phpmyadmin and by mysql -u user -p basename < filename.sql

I made export with mysqldump


texts: MS SQL Server Restore: Methods Without Exclusive Access

texts:
I've got a team recovering an MS SQL Server database. This database is live and currently working with a live website.

An issue occurred (data was deleted) where we needed to restore a backup copy of the data without overriding the current database, in order to retrieve the missing data.

Currently, our website is down with the message:

  Cannot open database "{database_name}" requested by the login. The
  login failed. Login failed for user '{user_name}'

This is because the restoration process requires Exclusive Access the to live database and renders all other access denied.

I haven't worked in database backup/recovery in years and don't remember, and can't find relevant information.

Is this normal, as in, is this how every restore is done?
Is the restore causing this Exclusive Access because of how the backup was taken or setup?
Are there backup/restore methods which could be used which will not required this Exclusive Access, and what are they?

I may have more questions but this is it for now. I need to learn and understand what is possible and compare that with what my DBA Team is doing and why.

Thank you for all your help.


texts:
We have a Windows Server 2008R2 server with 32GB or RAM running SQL Server 2008R2, along with an IIS server. The machine is also used as a backup server.

The problem is, periodically (every week or two) the SQL takes up all the memory, literally all of it: resmon shows 0MB available. Now, I did look the problem up, and I know SQL is supposed to take up the entire memory, but it really hurts the web performance, and in the end the SQL itself starts stuttering; scheduled jobs don't execute (the logs say "insufficient resources") etc.

I tried limiting the max memory size to 4096MB. The memory footprint dropped - and rose again to full in a week.
I tried scheduling a job that would empty the caches every night. Is that likely to help?
If not, what will?

edit Last time it crashed, already after I limited it to 4GB, I wasn't on-site, unfortunately. But the SQL Server logs show the available page file space dropping below 4MB, and the Windows logs report low virtual memory conditions for hours, with sqlservr.exe consuming the most memory (though only about 1,640,919,040 bytes, for some reason).


texts:
I believe my problem is something similar to this but I am not really sure. I am not even sure my title is on topic. Using mysql.
I have the following ACL design for a website:

### tools
id
name

### tasks
id
tool_id references tools.id
name

### roles
id
tool_id references tools.id
name

### access
id
role_id references roles.id
task_id references tasks.id
has_access

Website has several tools, each tool can do several tasks. Each tool has several roles (ex: admin, mod, user) and each role is allowed to perform specific set of tasks based on access table.

Problem: how do I achieve that access.task_id belongs to the correct tool and not some random tool? For example, if we have role "editor" in tool "articles" which has tasks "delete", "edit" and "publish", how do we prevent someone inserting into access table a task id belonging to "execute_shell_script" which is from a totally different tool? So roles.tool_id=tasks.tool_id for each row.

I suspect that my design is flawed in some way so feel free to point me to a better solution.

And a more general question, when db constraints get really complicated is it generally better to do them only programatically?


texts: How to optimize query with having and group

texts:
My Task is to optimize a query, which should check for data inconsistency: Return all leads ids with more than one entries and multiple is_first flag.

| id | lead_id | is_first| (other data) |
|----|---------|---------|-------- ...
|   1|       20|        1|...
|   2|       20|        0|
|   3|       21|        1|
|   4|       21|        0|
|   5|       21|        1|
|   6|       22|        1|

So this dataset should return 21. (20 ok, because is_first is only true/1 once)

Old Query:

SELECT DISTINCT s1.lead_id as lead_id
FROM history as s1
INNER JOIN history as s2 ON
  s1.is_first = 1 AND
  s2.is_first = 1 AND
    s1.id != s2.id AND
    s1.lead_id = s2.lead_id;

New Query: I already increased performance somewhat with this query

SELECT history.lead_id AS lead_id
FROM history
WHERE is_first = 1 GROUP BY lead_id
HAVING count(lead_id) != 1;

But I feel it's not end of the line. Any recommendations?


texts: SQL Server tempdb rogue temporary tables

texts:
I've got some rogue temporary tables being generated which is starting to cause disk space issues.
The database creating these tables is fairly large and I'm unsure what stored procedure/function etc is causing them. At the moment the tempdb is around 50 gig and only restarting the service clears it down (something we don't want to continually do).

Using SQL Server 2008 R2 SP1

The temp tables are all named in a similar fashion and are located in System Databases > tempdb > Temporary Tables:

dbo.#0519C6AF
dbo.#1273C1CD
dbo.#2A4B4B5E

There are about 12 of them at the moment – all created in the 9 days since the last service restart.
So far I’ve been looking for stored procedures that either create temp tables (although I don’t think this is the issue), use cursors, use transactions that could be left uncommitted.

Does anyone know what could cause the creation of temporary tables named similarly to the ones above?


texts: MongoDB covered query needs to fetch and examine documents when exists operator is used

texts:
I need to execute following query on mongo db:
db.testCollection.find({ dataType: { $exists: true } }, { _id: 0, date: 1 })
   .sort({ date: -1 })
   .limit(1)

so basically only I need to do is to find newest date from documents that have dataType field.
I have ~20 milion documents in that collection and this query last something like 4-5 minutes.
I tried to cover all query needs by index, so I created index like this:
db.testCollection.ensureIndex(
   { dataType: 1, date: -1 },
   { partialFilterExpression: { dataType: { $exists: true } } }
)

As far as I know, this index contains all data the query needs:

information that all documents in this index contains dataType field exists
date field value needed to retreived it from query
date field value needed to sort operation

Unfortunately, mongo still have FETCH stage in query plan to check if dataType filed exists:
"winningPlan" : {
            "stage" : "PROJECTION_SIMPLE",
            "transformBy" : {
                "_id" : 0,
                "date" : 1
            },
            "inputStage" : {
                "stage" : "SORT",
                "sortPattern" : {
                    "date" : -1
                },
                "limitAmount" : 1,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "dataType" : {
                                "$exists" : true
                            }
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "dataType" : 1,
                                "date" : -1
                            },
                            "indexName" : "dataType_1_date_-1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "dataType" : [ ],
                                "date" : [ ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : true,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "dataType" : [
                                    "[MinKey, MaxKey]"
                                ],
                                "date" : [
                                    "[MaxKey, MinKey]"
                                ]
                            }
                        }
                    }
                }
            }
        }

So my question is why mongo still needs to fetch document from collections?
EDIT: When I changed $exists operator to $ne: null it doesn't need to execute FETCH stage.


texts: Implementing a Circular Buffer (Sliding window) in MySQL

texts:
I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the last N items from a specific type should be kept.

My general idea is a an INSERT trigger:

DELETE FROM pixels WHERE type="type_of_new_pixel"
                         AND id NOT IN
                         (SELECT id FROM pixels
                          WHERE type="type_of_new_pixel"
                          ORDER BY timestamp DESC LIMIT N);

Is triggger the right way to go? How do I add the trigger to the table?
In order to increase performance, I would like to activate the trigger every M INSERTs. I will have some extra items in my list, but that's OK. How do I implement this? Will a id % 100 == 0 check do?


texts: How to handle the My SQL DB Master to Master replication when there was a disk space outage

texts:
We had master to master replication with two node nodes A & B, both are in virtual environment. Initially there was an outage (diskspace issue) in node A and stopped replication. Application level traffic has been diverted to node B and relocated the node A data store and made available of space.

Node A has started successfully & up. Coming to the replication has started and got an error in node B while syncing with node A and bin log in node B got corrupted. The root cause is still a mystery. But analyzing the logs could found some duplicate entries and below is the error log info :

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

How do we handle the replication in this situation? Can we skip the current bin-log and start the replication with the next available bin-log and its position. Is it a good idea to sync node B with node A:

CHANGE MASTER TO
  MASTER_HOST='XX.XX.XXX.XXX',
  MASTER_USER='replicate',
  MASTER_PASSWORD='slave',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.001025',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

The main reason I am posing this scenario is, that I don't want to restore the DB and build it from scratch, where DB backup is around 80 GB in node B. How can I repair the replication?


texts: show con_name not working in SQLDevelopper

texts:
I typed show con_name; in sqlDeveloper but the result is the following error : SP2-0158: unknown SHOW option "con_name" ... Other commands like Show user;  work perfectly. I tried in sqlplus and no problem there.

Any idea of what's happening here ?

ps : I'm using Oracle 12c on Linux


texts:
I need to create a script that gives the report for the following information:

Backup Job Name
Databases that have been backed up in the last 24 hours
Size of each DB backup
Start time of backup
End time of backup
Duration of backup
Location of the backup files

I need to still include Log backup information, backup size and the location of the backup files stored. Could someone point me in the right direction of how to better my query? Thanks in advance.
What I have tried:
SET @dbname = NULL --set this to be whatever dbname you want
SELECT
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id)
   FROM msdb.dbo.backupset
   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
   AND type = 'D' --only interested in the time of last full backup
   GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name```


texts: Is it possible to join these both tables?

texts:
SELECT u.*,h.*,d.*
FROM User u
LEFT JOIN HealthDec h ON u.ID = h.EmpId
LEFT JOIN Document d On u.ID = d.UserID;

and:
SELECT count(id) FROM OHS
WHERE Category = 'POLICIES'
  AND ID NOT IN
  (
    SELECT OHSID
    FROM User_OHS
    WHERE UserID='1' AND Type='POLICIES'
  );

Both queries are working fine separately as required. But I want to combine both queries to get result for each user.


texts: How to delete records with FK constraint using Delete Trigger

texts:
I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table  if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my Microsoft.
I have two tables DimProductSubcategory and DimProduct. So if I delete a product category in DimProductSubcategory, all related records in DimProduct should also be deleted.
So I have created a trigger:
CREATE trigger on_delete_trigger
    on DimProductSubcategory
    after delete
    as
    begin
        SET NOCOUNT ON;
        DELETE FROM DimProduct WHERE (DimProduct.ProductSubcategoryKey IN (SELECT DimProduct.ProductSubcategoryKey FROM DimProductSubcategory))
    end

But when I try to delete a record in DimProductSubcategory I get:
The DELETE statement conflicted with the REFERENCE constraint "FK_DimProduct_DimProductSubcategory".
The conflict occurred in database "AdventureWorksDW2019", table "dbo.DimProduct", column 'ProductSubcategoryKey'.

I understand the meaning of the error message, but I do not understand the reason for it. I thought the trigger was supposed to delete all child records so that I can delete the parent record without violating data integrity.
Although I'm not 100% sure I got my DELETE statement right.
So how can I implement a trigger to delete child records when a parent record is deleted?


texts: "Permission denied" in Postgres 12 trying to use to_regclass() on table partitions

texts:
I have a partitioned table public.events owned by user web. Running this query:
SELECT to_regclass('public.events_52642b42e4b0889a77dcd6db')

(to confirm that a partition is present for a given partition key) I get:
ERROR:  permission denied for schema public
SQL state: 42501

If I run the same command as the postgres user (superuser) I get the expected result:
public.events_52642b42e4b0889a77dcd6db

I likewise get the same result if I make the web user a superuser, however that's not a viable approach.  This was working under PG 11, not sure what changed and the docs don't provide detail (that I could find anyway) as far as what permissions are required for the "System Catalog Information Functions".  What am I missing?


texts:
We've managed to set up a Cassandra 2.2.6 cluster of two physical nodes (nodetool sees both of them, so I'm quite certain the cluster is indeed active). My steps to create the cluster were (this applies to both machines):

Empty listen_address and rpc_address.
Define a cluster_name.
Define both machines as seeds.
Open ports 9042, 7000 and 7001 for external communication.

Now I want to secure access to the cluster in all forms:

define a different database user with a new password
encrypt communication bet ween clients and the cluster including client verification
encrypt communication between the nodes including verification

What is the best order of steps and correct way to achieve this? I wanted to start with defining a different user, but cqlsh refuses to connect after enforcing user/password authentication:

cqlsh -u cassandra -p cassandra
Connection error: ('Unable to connect to any servers', {'127.0.0.1': error(111, "Tried connecting to [('127.0.0.1', 9042)]. Last error: Connection refused")})

This happens when I run the command on either of the two machines. Any help would be greatly appreciated.


texts:
I executed an incorrect SQL command:

SET GLOBAL init_connect='SET CHARACTER SET=utf-8';

Now, when I try and run a script that connects to this database, I get the following error: _mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away').

How would I reset the global init_connect values?


texts:
Link: Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008.

Say I have the following table:

CREATE TABLE RateTable(
    [Key] [int],
    [Value] varchar(10) NULL,
    [RecordDate] [DateTime] NULL
    )
GO

INSERT INTO [RateTable] VALUES (1,'abc','2012-01-01');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-02');
INSERT INTO [RateTable] VALUES (1,'xyz','2012-01-02');
INSERT INTO [RateTable] VALUES (1,'xyz','2012-01-05');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-05');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-08');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-10');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-11');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-12');
GO

So it contains the rows that follow:

Key Value RecordDate
--- ----- ----------
1   abc   2012-01-01
1   abc   2012-01-02
1   xyz   2012-01-02
1   xyz   2012-01-05
1   abc   2012-01-05
1   abc   2012-01-08
2   abc   2012-01-10
2   abc   2012-01-11
2   abc   2012-01-12

I would like to have output (the result set) for the value 'abc' only, as shown below:

Value StartDate  EndDate
----- ---------- ----------
1     2012-01-01 2012-01-02
1     2012-01-05 2012-01-08
2     2012-01-10 2012-01-12


texts: Changing own user permissions with Oracle SQL not being the DBA

texts:
I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes I'll want, for instance, to copy information from production to a development environment. When doing this kind of stuff, it always scares me to think of what would happen were I to copy or merge or whatever in the wrong direction, then destroying the production database.

I'm currently connecting to the schemas as a regular user with read/write permissions (I'm not the DBA here).

The ideal would be to have some way of being able to decrease in a fine grained way my own permissions (or having some system akin to having to typesudo in Linux. It'd be cool that any kind of write action to DEV would make me have to write my password).

I imagine this is a recurrent situation. How do people generally solve this kind of issues? I could of course ask the DBA to create different users with different kinds of permissions, but that gets tiresome, as their permissions end-up not being fine-grained and there are like 5 or 6 different environments where I'd have to create new users to. Maybe he could grant me rights to create new users (being those users always limited to have in the best case having exactly the same rights as my original user?)

Thanks


texts: How to summate weekly totals from a postgres date range type per user

texts:
I have the following table:

Schedules
+----+---------+------------+-------+--------------------------+
| id | user_id | project_id | hours | date_range               |
+----+---------+------------+-------+--------------------------+
|  1 |       1 |          1 |     4 | [2019-05-01, 2019-05-08) |
+----+---------+------------+-------+--------------------------+
|  2 |       2 |          1 |     8 | [2019-04-20, 2019-05-20) |
+----+---------+------------+-------+--------------------------+
|  3 |       1 |          2 |     2 | [2019-05-05, 2019-05-12) |
+----+---------+------------+-------+--------------------------+
| ...etc       |            |       |                          |
+--------------+------------+-------+--------------------------+

What I was hoping to accomplish with SQL was to get a summation of total hours per week given a specific date range input, for example date_start: 2019-05-05, date_end: 2019-05-11.

For the week of 2019-05-05 to 2019-05-11, any Schedule that has a date_range that has overlapping dates would get summated by project_id and by user_id.

Example: Row 1 from the above table has a date_range of [2019-05-01, 2019-05-08) so it would have 2019-05-05, 2019-05-06, 2019-05-07 (2019-05-08 omitted because exclusive )) dates that match the date_start/date_end criterion.

Since the hours are 4 for that row, the given total would be 4 * n days = 12 hours total

The date_start/date_end can be arbitrarily large, so say it was over a year range, every date_range would be added by week over the given range.

Potential expected output (open to suggestions):

| week_start |  week_end  | project_id | user_id | total_hours |
| 2019-05-05 | 2019-05-11 |          1 |       1 |          12 |
| 2019-05-05 | 2019-05-11 |          1 |       2 |          56 |
| etc ...    |            |            |         |             |

I'm not quite sure how to structure a query to generate matching date_rage days and multiply them out per user.

Can someone point me in the right direction?


texts: sqlite - Distinguish a column with the same value by range of id and count this range, take the higher one

texts:
I have a small sqlite table

CREATE TABLE IF NOT EXISTS intersection (
    cnt NUMERIC NOT NULL,
    sweep NUMERIC)

from what i want to get the maximum value of "cnt" and later on the average of "sweep" depending on the range/amount of distinct "cnt"-values.

Since sqlite only has this rowid option i use it within my query:

SELECT rowid, cnt, sweep FROM intersection
WHERE cnt IN (SELECT MAX(cnt) FROM intersection)

This returns:

+--------+------+-------+
| rowid  | cnt  | sweep |
| 106    | 7    | 21.07 |
| 107    | 7    | 21.17 |
| 108    | 7    | 21.27 |
| 109    | 7    | 21.37 |
| 110    | 7    | 21.47 |
| 111    | 7    | 21.57 |
| 112    | 7    | 21.67 |
| 113    | 7    | 21.78 |---split here
| 152    | 7    | 25.7  |
| 153    | 7    | 25.8  |
| 154    | 7    | 25.9  |
| 155    | 7    | 26    |
| 156    | 7    | 26.1  |
| 157    | 7    | 26.2  |
| 158    | 7    | 26.31 |
| 159    | 7    | 26.41 |
| 160    | 7    | 26.51 |
| 161    | 7    | 26.61 |
+--------+------+-------+

I need both "cnt"-ranges but the higher count of 7s (from id 152 downwards) first. So i would have to split this result according to the gap in id and proceed with my calculations.

I googled a lot and also looked here on stackexchange, this is one partial solution i found so far

SELECT f.rowid, f.cnt, f.sweep FROM intersection f
    WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
    AND NOT EXISTS (
        SELECT * FROM intersection s
        WHERE s.cnt = f.cnt
        AND s.rowid = f.rowid+1)--or -1

It returns, depending on +1 or -1, the lower or upper boundary of id but with that i can't compute the average of sweep for the given range.

  Finally i need a query that looks for the highest "cnt"-value, the highest count of one range of (in this case 7s) split by the gap between the id's of these two groups.

I have no idea how to get there since i'm not that experienced but willing to learn a lot, so a little advice would be greatly appreciated!

Wanted output:

+-------+-----------+
| count | avg_sweep |
| 8     | 21.42     |
| 10    | 26.15     |
+-------+-----------+


texts: SQL multiple select with where conditions performs really poorly

texts:
Data structure is something like this:

Number| ID | DateTime | Status

50000001, 101, 1/1/09 0:10, PO
50000001, 110, 1/1/09 0:11, PO
50000001, 102, 1/1/09 0:15, PO
50000001, 101, 1/1/09 0:10, PCK
50000001, 102, 1/1/09 0:12, PCK
50000001, 110, 1/1/09 0:12, PCK
50000001, 101, 1/1/09 0:15, C
50000001, 101, 1/1/09 0:15, C+
50000001, 110, 1/1/09 0:15, C
50000001, 110, 1/1/09 0:15, C+
50000001, 102, 1/1/09 0:15, C
50000001, 102, 1/1/09 0:15, C+
50000002, 126, 1/1/09 0:13, WO
50000002, 126, 1/1/09 0:14, PCK
50000002, 126, 1/1/09 0:14, C
50000002, 126, 1/1/09 0:14, S

I am trying to select data based on several values in the Status column and return it formatted like:

| Number | OrderOpen | OrderCleared |

My query looks like this:

Select
SetOpen.ID, SetOpen.Num, SetOpen.OrderOpen, SetCleared.OrderCleared
FROM
(
SELECT
    order_status_hist.ID AS 'ID',
    order_status_hist.Number AS 'Num',
    order_status_hist.datetime AS 'OrderOpen'
FROM
    dbo.order_status_hist
WHERE
    order_status_hist.Status='WO' OR
    order_status_hist.Status='PO'
) as SetOpen
inner JOIN
(
SELECT
    order_status_hist.ID AS 'ID',
    order_status_hist.Number AS 'Num',
    order_status_hist.datetime AS 'OrderCleared'
FROM
    order_status_hist
WHERE
(
    order_status_hist.Status='C+' OR
    order_status_hist.Status='S'
)
) AS SetCleared

ON SetOpen.ID = SetCleared.ID

In my production database there are about 50 values in the status column, and my where statements have 10 'cases' in the first one and 5 in the second one. When I run the production version of this, it takes a VERY long time to complete. The further trouble is, this is really just part of a larger query, I would be joining these results on 'Number' to pull data in more tables.


texts: Get row with max date from a joined table

texts:
I have this schema of dossiers having 1 to N pages, and each page has a date:

CREATE TABLE dossiers
(id INT AUTO_INCREMENT,
code VARCHAR(50),
 PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE pages
(id INT AUTO_INCREMENT,
name VARCHAR(100),
date DATE,
hour TIME,
dossiers_id INT,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO dossiers (id, code)
VALUES (1, 'A1'),
(2, 'A2');

INSERT INTO pages (id, name, date, hour, dossiers_id)
VALUES (1, 'page1', '2018-09-01', '17:00:00', 1),
(2, 'page2', '2018-09-05', '17:00:00', 1),
(3, 'page3', '2018-09-10', '17:00:00', 1),
(4, 'page4', '2018-08-20', '17:00:00', 1),
(5, 'page1', '2018-09-01', '17:00:00', 2);

I want to select all the dossiers alongside the page with its maximum date.

This is what I tried (error):

SELECT *
FROM dossiers d1
INNER JOIN pages ON pages.dossiers_id = d1.id
WHERE CONCAT(pages.date, ' ', pages.time) = (SELECT MAX(GROUP_CONCAT(date, ' ', time)) FROM pages WHERE dossiers_id = d1.id GROUP BY dossiers_id)

I need to get the A1 dossier along with its "page3" page, and the A2 dossier with its "page1" page.

I'm sure it's not as twisted as I've done, but I can't figure it out.

Suggestions?

Link to the SQL Fiddle here.


texts:
I'm using PostgreSQL 10.5 on Linux (RHEL).  I recently installed pgAudit and was trying to configure it to capture DLL statements.

1) The first thing I tried was to edit the postgresql.conf file directly.  I didn't see any commented out default entries to edit, so near where I have the entries:
log_destination = 'csvlog'
log_collector = on
I entered my own entry of:
pgaudit.log = 'ddl'
Then I restarted the database.  After doing this I created and dropped a table, but no entry of that in the log file postgresql-2019-11-19-141901.csv.

2) So then tried to create the entry using the ALTER command:
ALTER SYSTEM SET pgaudit.log TO 'ddl';
SELECT pg_reload_conf();
After doing this, I noticed a second file was created (postgresql.auto.conf).  That file had the entry pgaudit.log = 'ddl'.  From what I read, this file get read after the main postgresql.conf file.
However after creating and dropping a table, still no entry in the log file.  I did notice though when I run the command: show pgaudit.log;  It came back with 'Role' (and not ddl).

3) So the final thing I tried was to enter the command: set pgaudit.log = 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'.  This time I tried to drop a table again.  After the table was dropped , my psql client displayed:
NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLE....blah,blah....[not logged].  This looks like a pgaudit entry but was on my screen and not in the log file.  At this point I restarted the database again and now when I run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl).

Can someone please help me see what it is i'm doing incorrectly?

Thanks in advance


texts: Replication from EC2 Master to RDS Slave - Connecting to master

texts:
I am migrating my company's database from an EC2 instance to an RDS instance. I have already migrated a snapshot of data and am now trying to set up replication to get the data that has been added since the snapshot.

I am following the instructions at this link:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

When I call mysql.rds_start_replication , SHOW SLAVE STATUS\G shows the following:

               Slave_IO_State: Connecting to master
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

From my Googling on the subject matter, I'm led to believe this is a network/firewall problem. I've checked that my RDS has a security group allowing all network traffic with the source machine (both private IP and Elastic IP) and the source machine has a security group allowing all network traffic with the RDS instance IP address.

The master server has a user set up with replication privileges.

I'm sure there must be something easy I'm missing here.

I've tried running flush hosts on the master server.

How do I get the replication to proceed successfully?

Some additional information:

The master server is version 5.5.33
The slave server is version 5.6.13
The master server is using old_passwords=1
The slave server is using old_passwords=0

I've tried setting SESSION old_passwords=0 on the master, creating a new replication user with a new, long hash format, but still can't get replication to start.

The error I get is:

        Last_IO_Errno: 2049

which seems to relate to the authentication protocol.

Do I need to completely restart the MySQL master server, with old_passwords=0 in the my.cnf file?


texts: Product table with possible duplicate part numbers from different manufacturers

texts: I am making a database. It has a product(aka component or part) table with a ID, ManufacturerID, and PartNumber. The ID is obviously the primary key and is unique. The ManufacturerID is pulled from the ManufacturerT. The PartNumber will be entered by the user. I am trying to decide whether to make the PartNumber field unique or not. If I do, I won't have the issue with a user entering the same product multiple times, but what if there is a product from a different manufacturer with the same PartNumber? It wouldn't allow the user to enter. What is the solution to this? Does it require some special logic? Is there a way to not allow duplicates if the ManufacturerID and PartNumber fields are identical? I am using Microsoft Access 2013(version 15).


texts: SQL Server, how to log client PID of failed login attempts?

texts:
I'm trying to figure out which process is trying to connect to my SQL Server instance with a wrong password. The log file only contains the IP address from where the connection is initiated. Example:

Date      4/05/2021 9:43:34 AM
Log        SQL Server (Current - 5/05/2021 10:11:00 AM)
Source     Logon
Message
Login failed for user 'SA'. Reason: Password did not match that for the login provided. [CLIENT: 10.120.1.99]

It does not log which PID from the client machine made the attempt.
I know that I can use profiler to find out which PID from which machine is responsible for this. But I do not want to keep a profiler running for this. (especially if this happens rarely, in which case I'll need to keep the profiler running for days before I can catch such an attempt).
Is there a way to log the PID as well as the IP for such failed logons?


texts: Sybase ASE lock table to disallow users insert values

texts: I need to disallow users to insert values in some table, for example for a 5 mins. Seems I should use at isolation clause but can't figure out how to use it. Thanks


texts: Need to find a pattern in SQL Data base and then write a query to delete the duplicate "NULL" Value

texts:
I have read a lots of stuffs regarding this but cannot find solution to this problem. Please some one have a look into it.

I have a unique data-base, which contain Row-number, Year, Practice, office Statistics like details in different columns. Please find the below screen shot of it.

Here is my problem.

I need to search if the practice is unique('A', 'B'), then we don't need to do any change in the SQL Database server. However, if you find a repetitive value like in practice 'C'. Then we need to remove the Whole row from SQL database, if the Q1 , Q2 Q3, Q4 , YTD, and avg value is "NULL".

I want to remove the NULL Value if you have duplicate practice name if Q1 , Q2 Q3, Q4 , YTD, and avg value is "NULL".


texts: Index Maintenance on SQL Server 2008 R2 Web edition

texts:
I've inherited of a couple of databases which indexes have never been maintained (rebuilt, reorganized).

I'm using Ola Hallengren's Index maintenance script, I've used the following script to record the difference between the pre-maintenance and post-maintenance values.

Script :

SELECT  OBJECT_NAME(IPS.object_id) AS [TableName] ,
    SI.name AS [IndexName] ,
    IPS.Index_type_desc ,
    IPS.avg_fragmentation_in_percent ,
    IPS.avg_fragment_size_in_pages ,
    IPS.avg_page_space_used_in_percent ,
    IPS.record_count ,
    IPS.ghost_record_count ,
    IPS.fragment_count
   -- ,IPS.avg_fragment_size_in_pages
FROM
    sys.dm_db_index_physical_stats(DB_ID(N'test_db'), NULL, NULL, NULL, 'DETAILED') IPS
JOIN
    sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id
JOIN
    sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id
                                 AND IPS.index_id = SI.index_id
WHERE
    ST.is_ms_shipped = 0
ORDER BY
    3 Desc, 4 Desc

However, I didn't notice any changes on the various indexes having an avg_fragmentation_in_percent above 30%.

Are index maintenance tasks supported on SQL Server 2008R2 Web Editions?


texts:
Is it possible to have multiple fields as range key?

Say I have a table where each row is uniquely identified by <A,B,C>

 -------------------------------
 A   |   B   |  C  |  D   |  E  |
 -------------------------------

where A is the primary hash key

and I want B and C to be the primary range keys.

  How can I have more than 2 fields as a primary key in DynamoDB?


texts: Adding an Organization schema to existing person/relationship schema

texts:
I'm not a database programmer. I really don't plan on becoming one; I dabble. My skills and work lie elsewhere, and these keep me busy enough. Nevertheless, I find myself needing, and begging, for some quick help at times in an area in which I have no time or need to learn fluently. I have officially attempted some trials at building a couple of new tables for this, but to no avail. I do most of my learning in a hands-on setting, and will know how this particular schema works once I've received some direction (which is all I'm asking for, I think) and implemented it.

That said, the following was suggested by @RolandoMySQLDBA four years ago, and I've found it terrific for a project I'm building.

I'm hoping he will see this and that he, or someone, can help me extend this to include a table of "Organizations" along with a table of Organization rel_types, to be associated with any of the persons...

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE relationship
(
    rel_id INT NOT NULL AUTO_INCREMENT,
    person_id1 INT NOT NULL,
    person_id2 INT NOT NULL,
    reltype_id TINYINT,
    PRIMARY KEY (rel_id),
    UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
    UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
    KEY has_relationship_to (person1_id,reltype_id),
    KEY has_relationship_by (person2_id,reltype_id)
);
CREATE TABLE relation
(
    reltype_id TINYINT NOT NULL AUTO_INCREMENT,
    rel_name VARCHAR(20),
    PRIMARY KEY (reltype_id),
    UNIQUE KEY (rel_name)
);
INSERT INTO relation (relation_name) VALUES
('friend'),('follower'),('foe'),
('forgotabout'),('forsaken'),('fixed');

I will be, and am, grateful to anyone who can assist me with this.


texts: Do more joins equate to slower performance?

texts:
I'm working on a denormalized database dump, reconstructing the model so that we can access the data in our own system. Because the database is denormalized there is quite a bit of redundancy that I would like to 'ignore' in my reconstructed data model - only to make the model easier to read. I have a choice, and while I'm not looking for a strictly performant solution I would like to avoid excessive performance costs.

table A has a column hTableE, which is a denormalized reference to Table E. In my model I have Table D references Table E via FK. Table D also references Table C via FK, which references Table B via FK, which references Table A via FK. I think I have 3 choices:

Create an FK relationship from A to E. And then JOIN on that.
Join without an FK
follow the table FK relationships and use multiple joins to associate Table A with Table E.

My feeling is that No. 2 is the best option. But would not be obvious just from looking at the datamodel. No. 1 makes the datamodel harder to read (over 100 tables). No 3 is the most logically easy to 'rediscover', but would probably come with a performance hit?


texts: Creating an index in both asc and desc directions

texts:
Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table has two indexes; each one with a single segment, one in asc order and one in desc order.

Apart from the wtf'ness of having an index for every field in every table, it got me thinking - is there any advantage for single-segment indexes to having two indexes with the same index segments, but one in desc and one in asc? Is there anything to be gained, or would a modern DBMS simple use the asc index and start from the end and work its way backwards if required?


texts: Difference between ASYNC & SYNC real time apply

texts:
What is the difference between a data guard (Oracle 11g) operating in max performance ASYNC mode and enabling real time apply and a data guard operating in maximum availability SYNC and enabling real time apply?

What is the overhead on performance in both?

Also, does it make sense in enabling real time apply in max performance mode?


texts: Transaction Log Truncation with Copy Only

texts:
I have a database backup job set to use full backup with the Copy only option on once daily. The copy only is on because from what I have read that is the only way to backup a database that is connected to an availability group. I have log backups happening every 20 minutes using the same options on the same AG. What is the best practice for truncating the transaction logs after these backups run. The full backup is not truncating since its using copy only the same for the log backups. They are growing out of control. I know i can use DBCC SHRINKFILE but the more i read about it the more dangerous it seems. Is there some other method or best practice?

Any advice is appreciated.


texts:
I have this SQL statement:

select *
from _my_table1
where 1 = 0
      and (exists (select 1 from _my_table2 where id =7))

The execution plan is:

|--Constant Scan

Now when I change my query to

Declare @i int = 1
SELECT *
FROM dbo._my_table1
WHERE @i = 0 AND (EXISTS (SELECT 1 FROM dbo._my_table2 WHERE id = 7))

I get the following plan:

  |--Nested Loops(Left Semi Join)
       |--Filter(WHERE:(STARTUP EXPR([@i]=(0))))
       |    |--Clustered Index Scan(OBJECT:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table1].[PK___my_tabl__3213E83FEF434214]))
       |--Clustered Index Seek(OBJECT:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table2].[PK___my_tabl__3213E83F4D4FA6E2]), SEEK:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table2].[id]=(7)) ORDERED FORWARD)

The question is why SQL Server checks for second part of WHERE clause, if the first part returns false. Is there any workaround to force the SQL Engine not to check the second part of where, if the first part returns false.


texts: mysql performance tuning innodb_buffer_pool_size and warming the cache (select * vs select count(*))

texts:
I have a couple of questions about tuning a MySQL (5.5) database. It seems the clients database server has never had any performance tuning done. Upon reading around, it seems the one thing is to set innodb_buffer_pool_size to "80% of the available RAM". Now, the physical size of the database is about 5Gig, and the available RAM on the server is 32Gigs. It seems to make no sense to me to make the innodb_buffer_pool_size = 25G.

At the moment I have set

innodb_buffer_pool_size: "8G"
innodb_buffer_pool_instances: "8"
innodb_log_files_in_group: "2"
innodb_log_file_size: "1G"

Which, reading around (and running mysql-tuner script) seems about sane.

My question is: Is it a bad idea to have a buffer_pool_size greater than the size of the database itself. Is it bad to effectively have all the data in the buffer_pool ?

Warming the cache

The other part is warming the cache. Reading other posts on the net, the suggestion is to run a bunch of select count (*) from <table> to warm it. If I do that, I get the amount of RAM at point (1) in the picture below.

If instead I run select * from <table> I get a heap more RAM used. Which, I assume means that I have more data loaded into the cache. Which seems good to me.

Is it better to run select * or select count(*) for warming the cache?


texts:
I am trying to import tables from a production environment, to a test environment.  The production environment has supplemental logs on a number of tables for golden gate replication.  The supplemental logs are being included in the import, which isn't a problem, but they aren't needed so I would like to exclude them.
I am using a par file to do the import currently, and while I feel like I should be able to exclude the supplemental logs, but I don't see an obvious exclusion type
SCHEMAS=PROD_SCHEMA
REMAP_SCHEMA=PROD_SCHEMA:TEST_SCHEMA
REMAP_TABLESPACE=\"TBS_1\":TEST_TBS
REMAP_TABLESPACE=\"TBS_2\":TEST_TBS
REMAP_TABLESPACE=\"TBS_3\":TEST_TBS
parallel=32
cluster=n
DIRECTORY=DPUMP
TABLE_EXISTS_ACTION=REPLACE
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
EXCLUDE=TABLE:"IN ('TABLE1','TABLE2')"
EXCLUDE=TRIGGER
EXCLUDE=CONTRAINT


texts: Get distinct results based on column and satisfy condition

texts:
I have the below select which is selecting from a table with the columns:

id, project_id, keyword_id, position,
Data example:

i, p_id, k_id, p
1,  1,    1,   4
2,  1,    1,   5
3,  1,    1,   6
5,  1,    2,   7
6,  1,    2,   8
7,  1,    3,   5
8,  1,    3,   7

QUERY

SELECT
  pr.project_id,
  COUNT(DISTINCT pr.keyword_id) as total_keywords,
  COUNT(CASE WHEN position BETWEEN 4 AND 10 THEN position ELSE NULL END ) AS pos4,
  date(pr.created_at) AS created_at
FROM
  project_reports pr
GROUP BY pr.project_id, date(created_at)

I only want to get the number of distinct keyword_id in pos 4-10. But using my SELECT I get all of them.

Expected Data example:

p_id, total_keywords, pos4
1,      3,            3

My current Data output

p_id, total_keywords, pos4
1,      3,             7


texts: Oracle symmetrical one-way replication for cheap HA: unfashionable or technically unfeasible?

texts:
We have two smallish unrelated databases on two big Oracle Enterprise servers. Instead of buying more licenses and servers for high-availability replicas, can we replicate database B to server A and database A to server B, as long as either server can handle the load for both databases if necessary?

Seems like there are several underlying questions:

Which replication methods can replicate an active DB to a standby DB in both
directions between two servers with good failover?
How much lighter is a replicate-to-standby workload than a
production workload?
If people aren't doing this, are hidden gotchas stopping them or is
it just not in fashion?


texts: Oracle function based index is not used in query

texts:
I cannot figure out why the following is not working as I expect it :

CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY,
      col1 INT NOT NULL,
      val1 VARCHAR(50));
CREATE INDEX IDX_table1_fnct ON table1(CASE WHEN col1 = 3 THEN val1 ELSE NULL END);

SELECT COUNT(val1) WHERE col1 = 3 ; shows full scan

I'd expect new index to be used for such queries, but it's not even if I try to force it with INDEX hint...
Could anyone clarify why it works this way (I'm using Oracle 10 if that matters)?

Thank you