Oracle - Search a value in all columns of a table Here is my issue.

I have a Oracle table which contains 53 columns. And i would like to do a search for a value for all columns. And i do not want to put all columns in the where conditions.

Is there any optimistic way to accomplish this?

Any help would be appreciated.

As far as I can tell it's not possible to search all columns of a table for a value without referencing all of the column names that you want to search in some way in the query.

If all of your columns have compatible data types and you just want to return any rows that have at least one column that matches your value here is one way to accomplish that:

SELECT *
FROM tablename
WHERE 'value' IN (col1, col2, ... col53);

If you don't want to go through the work of writing out all of the column names or if you need to generate these types of queries for many different tables you can automate the query writing using Oracle's metadata views. For example, you can generate a list of all column names for a table by querying the ALL_TAB_COLUMNS view. Something like this should work:

SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)
from ALL_TAB_COLUMNS
WHERE OWNER = 'TABLE_OWNER' -- replace with the owner
AND TABLE_NAME = 'tablename'; -- replace with the table name


Datawarehouse bad practice? In our Datawarehouse context, we have to update some record in fact tables inside our ETL.

One thing we did is to create nonclustered indexes just before the heaviest queries, and drop them afterwards. This lead us to much less time spent scanning tables in queries, and the time spent in building indexes has very low impact (1-2 minutes).

Is this a bad practice?

Note: We cannot partition tables right now.

If it works for you then it's a good practice.  There are basically no hard rules for databases.


Why is SQL Server 2014 consuming so much RAM with no active data stored? I'm running SQL Server 2014 on my laptop along with a ton of other installed applications, from Visual Studio to video games.

This is obviously not a production server, but for development and debugging only.

I've scripted out a fairly small and simple database, with a few dozen tables and views - and I've inserted less than 100 rows of dummy data for testing.

At one point I accidentally created a 100GB+ index, using some badly formed cross joins - the query creating this index was consuming 100% of the RAM in my laptop (16GB), which slowed everything to a crawl, so I aborted the query rather than waiting for it to finish, then promptly dropped and re-created the entire database with this index removed from the creation scripts.

However, SQL Server 2014 kept consuming all 16GB of my RAM from that point forward, even with the query aborted and the database dropped.

So I rebooted my laptop next, and the RAM usage for SQL Server dropped down to 7GB - where it has remained ever since, regardless whether or not I drop all of the 5MB of existing databases etc.

I'd like to get SQL Server back to a reasonable memory consumption level, but I don't know where to begin looking for possible causes.

I know this is a pretty open-ended question, but I'm willing to do the legwork with a little guidance.

I just don't know where to begin troubleshooting, since I'm not a DBA myself - and apparently Google doesn't know either. :)

First thing is to change the max memory setting on your instance using either the GUI or sp_configure.

EXEC sp_configure 'max server memory (MB)', 1024

Then go into SQL Server Configuration Manager and stop and re-start the instance.  You don't actually have to restart your laptop.

That will fix it so that your instance won't use much more than 1gb.  I'm fairly sure it uses memory that isn't included in this setting but not a huge amount.

SQL Server likes to take memory for what it needs.  It isn't so good at giving it back so it's a good idea to keep the max memory setting on your personal laptop fairly low unless you happen to need more.  I use the 1gb setting for random small queries etc and if I need more I update it.  Of course my laptop only has 6gb of ram total.


Where should I fix the deadlock? We have an application database on Server A that is replicated to Server B using push transactional replication. Reporting is done on the replicated copy on Server B.

We are having issues where the replication process, which obtains a shared with intent exclusive (SIX) lock, deadlocks with SELECT statements, which obtain an Intent shared (IS) lock. The SELECT statement is chosen as the deadlock victim, the query is terminated, and the report developer isn't happy.

Where should I fix this? Should we add NOLOCK hints to the SELECT statement? Change some replication setting? Is there a bigger issue, like poor database design, at work here?

While I've never tested this, I would try turning on snapshot isolation on the reporting database. If all the existing queries run using READ COMMITTED (the default), turning on READ_COMMITTED_SNAPSHOT could fix the problem with a flick of the switch.

Using one of the snapshot isolation levels should eliminate the deadlocks completely as the single writer won't block the reader. As a nice byproduct it should also reduce the possibility of inconsistent data ending up in the reports due to concurrently-applied replication changes.


MongoDB Hashed Shard Keys Selection I have a DB which holds very simple documents called "Item":

{_id:<ObjectId>, playerId:int, isRead:bool, content:string}

containing about 80M documents. Each Item belongs to a certain player, defined by a monotonically incrementing playerId. Each player has up to 1,000 documents in the collection.

I want to shard the collection based on hashed playerId, since the read queries are based on playerId. This way, I believe I will be able to get to the right node in the cluster efficiently, then get the relevant documents (worst case, 1000) and retrieve them.

I was advised not to use hashed playerId because it's not unique, and might retrieve a large amount of documents per query, since isRead is not included in the index. Queries that retrieve by playerID and isRead might impose an efficiency issue.

Is this indeed a problem? Are there any other considerations to think of?

Why not shard on a custom _id value since it's guaranteed to be unique? With the size around 1,000 an index on playerId should allow for good performance as well.

Still do some testing of course because it can still vary based on number of shards and overall size of the collection.

Found a video I was looking for at the time from the Mongo University M102 course: Cardinality & Monotonic Shard Keys, which provides some more insight.


How to find description of error in error log? We have errors like these in our MySQL error log:
[ERROR] [MY-010211] [Server] Got error 203 when reading table 'xxx'
But I can't find anything about either MY-010211 nor error 203 in the documentation.
Where can I find more information about this error, or other errors like this in general?

Errors labelled [MY-XXXXXX] are MySQL 8.0 errors. You can find the definition for this particular error here https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_reading_table_failed.


where clause: if this value from col x make sure col y is this value I feel like this is an easy one for you Experts!  I haven't done any kind of Advanced SQL in years.

Fundamental question:

select
    *
from myTable
where ((if col1 = 'this', make sure col2 = 'that')
else, don't include it in the results)

I would want to include all of this in my result set EXCEPT the first row, because where col1 = 'this'  col2 doesn't = 'that'

I want to only check where col1 = 'this' .. other values I want to include in the results.

How do I do the above logic in SQL Oracle.  I don't want to use PL/SQL  just plain SQL.
I was thinking there was a clever way to do this without some weird if/then/else statement

Thank you,
Mike

Like this:

select * from myTable
where colY = 'that' or colX <> 'this'

The standard implementation of any criteria specified as A => B is as B or not A

Update:

Using this CTE for sample data:

with myTable as (
    select N,colX,colY from ( values
         (1,'this','hey')
        ,(2,'this','that')
        ,(3,'yup', 'yerp')
        ,(4,'yup', 'that')
    ) myTable(N,colX,colY)
)

and this query from above:

select * from myTable
where colY = 'that' or colX <> 'this'

yields (as required):

N           colX colY
----------- ---- ----
2           this that
3           yup  yerp
4           yup  that

(3 row(s) affected)


Easiest Way to Move 100 Databases I need to move about 150 databases from one server to another server.

SQL Server 2008 Web Edition
SQL Server 2012 Preview (in a different datacenter - East Coast Azure)

I was planning on moving them one at a time using RedGate Packager, however this will take a while.

Is there a faster and easier way?

The best way to do this effectively I think is to backup all the databases and transfer via Site to Site VPN (setup between your location and East Coast Azure Datacenter).

To backup all databases, setup a SQL agent job with below step. Remember it's a CmdExec type and not a T-SQL statement.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @Directory = N'C:\SQL Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime =312, @CheckSum = 'Y',@Compress = 'Y'" -b

And simple enough, copy all the .bak files from C:\SQL Backups to the remote Azure location. and use a restore script to restore all databases at once.

I recently found a tool (Teracopy) that does the copy/paste much faster than traditional Windows server copy functionality.

Hope this helps.


Solution for deploying multiple instances of Django Server with Database replication have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple instances of this across a client site. I need to keep all databases consistent across the instances with each instance talking to its own database (essentially a multi-master setup)

I have been looking into various replication solutions (mySQL Cluster, PGCluster) but these all seem to require that I have each instance accessing the database through one location, whereas I want each instance to read /write to its own database and the databases to be kept consistent across all devices. Does anyone have any ideas of a solution that may work for this or somewhere else I could look?

Its also worth noting that the databases are write heavy rather than read heavy

I'm going to highlight one of the products listed in the wiki link provided by Jack Douglas.
Galera is a product I've been hearing a lot about recently, but have not had a cause to implement it yet into a production environment.
Back in July, Percona wrote a brief blog about implementing it, but mentioned there was no 'production' release at that time.
They've since released the first major version, v1.0. And Vladimir has been shedding more light on it this month. And with some benchmarks
Due to your write-heavy environment, I highlight this use-case:

Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

Disclaimer: I'm not affiliate with Galeria or Percona, and have not used it personally...yet.


Optimising file shrink and index reorganization operations I'm archiving my SQL tables. So I am shrinking my files (because I need the free space) and re-organising my indexes(to defragment them). I know shrinking a file causes the indexes to be fragmented. So after shrinking my file, I check the fragmentation % of my indexes and if the degree of fragmentation is greater than 10%, I reorganise them. I am reorganising it partition by partition by employing the following code:

ALTER INDEX <index_name> ON <table_name> REORGANIZE PARTITION = <partition_number>

And for shrinking files, the following code is being employed:

DBCC SHRINKFILE ('<file_name>',<desired_size>)

Now so far, I have been shrinking my files, reorganising my indexes only to find that the file pertinent to the index has again bloated up, requiring to be shrunk up again; the said shrinking again causes fragmentation(as expected) requiring defragmentation again. I have had to perform these operations several times to get rid of index fragmentation and set the file to a desired size.

Is there a way to optimise the vicious cycle of shrinking -> defragmenting -> shrinking -> defragmenting? Is there a way to reduce the number of steps?

Move your indexes into a new pre-sized (but fairly small) filegroup. Moving them will involve a rebuild, and your file will be small. If you do this to all the objects in your current filegroup you will be able to remove it completely, keeping only the new, tight, filegroup, with only unfragmented indexes.

And please stop shrinking your database files. If you need the space on a temporary basis (eg, for loading data prior to transformation) and need to reclaim it after for other uses, then consider using a different database for that temporary stuff, so that you can drop that database once your loading (or whatever) is done.


Why isn't the result of an OpenQuery refreshing? I'm trying to get some data from the Active Directory in SQL. I use the code:

SELECT * FROM OpenQuery
(
ADSI,
'SELECT  objectGUID, objectSID, employeeID, lastLogon, mobile, telephoneNumber, mail,  department, givenName, sn, displayname,  sAmAccountName
FROM ''LDAP://DC=mycompany''
where  objectClass = ''user''
and sAmAccountName = ''testuser'''
)

My problem is that the value i receive for lastLogon isn't refreshing. It is possible that the other values aren't also refreshing, however i can't say this for sure, because in the other data there is no change, but the lastLogon value should be updated when i lock my computer and log in.

Does anyone have any idee why is this, and how can i solve this?

Thanks!

The lastLogon attribute reflects the last interactive logon, not the last network-based logon

More info found here

This is some excellent information from this site

  Another twist: Active Directory does NOT replicate the LastLogon
  attribute across domain controllers. So in order to get an accurate
  value, you need to obtain the LastLogon value for the same user from
  all your domain controllers and accept the value that is highest.


SQL Server cardinality hint Is there a way how to 'inject' a cardinality estimation to a SQL Server optimizer (any version)?

i.e. something similar to Oracle's cardinality hint.

My motivation is driven by the article, How Good Are Query Optimizers, Really?[1], where they test the influence of the cardinality estimator on a selection of a bad plan. Therefore, it would be sufficient if I could force the SQL Server to 'estimate' the cardinalities precisely for complex queries.

[1] Leis, Viktor, et al. "How good are query optimizers, really?"
 Proceedings of the VLDB Endowment 9.3 (2015): 204-215.

You can get something similar to Oracle's CARDINALITY hint by strategically using TOP and a user defined function called MANY() developed by Adam Machanic. Let's work through a few examples. I'm using the freely available AdventureWorks database. Suppose that I really need to control the number of rows returned by the th derived table in the following query:
SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
) th ON p.ProductID = th.ProductID;

As is, I get an estimate of 113443 rows:

If I need to lower the estimate from th I can use TOP along with the OPTIMIZE FOR query hint to set a row goal. Here's one way to do it:
DECLARE @row_goal BIGINT = 9223372036854775807;
SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1));

We can see that the estimate is just 1 row:

I set @row_goal to the largest possible BIGINT value to avoid changing the results. The OPTIMIZE FOR query hint instructs the optimizer to optimizer the query as if @row_goal is equal to 1. I will get the same results but the query will be optimized differently.
Increasing a cardinality estimate is trickier. We can't just increase the value for TOP because the optimizer will realize that it won't return enough rows. However, we can use the MANY() function to add rows to the estimate. Note that the MANY() function will always return 0 rows but the row estimate from it changes with the input parameter. Suppose that you need to increase the row estimate from the derived table by 10X. One way to accomplish that:
SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (9223372036854775807) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
    LEFT OUTER JOIN dbo.Many(10) AS m ON 1=1
) th ON p.ProductID = th.ProductID;

We can see that the estimate is 10X the base table:

The superfluous TOP was added to prevent the optimizer from moving the tables around. Without it the MANY() function can be applied to the wrong place in the plan.
It is possible to combine the two techniques if you want a precise overestimate instead of just multiplying the number of rows by a factor. For example, let's suppose that you really need the estimate of the derived table to be exactly 1000000 rows. One way to accomplish that:
DECLARE @row_goal BIGINT = 9223372036854775807;

SELECT
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory
    LEFT OUTER JOIN dbo.Many(10) AS m ON
        1=1
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1000000));

We can see that the estimate is 1000000 rows:

I need to caution you that these are advanced techniques which often aren't needed for query optimization. If you'd like to learn more I recommend watching Clash of the Row Goals presented by Adam Machanic.

dbo.Many function
-- By Adam Machanic, reproduced with permission
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Many' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo')
    DROP FUNCTION dbo.Many
GO
CREATE FUNCTION dbo.Many(@n INT)
RETURNS TABLE AS
RETURN
(
    WITH
    a(x) AS
    (
        SELECT
            *
        FROM
        (
            VALUES
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
        ) AS x0(x)
    )
    SELECT TOP(@n)
        1 AS x
    FROM
        a AS a1,
        a AS a2
    WHERE
        a1.x % 2 = 0
)
GO


MySQL data precision confusion I have two tables with same column.

First table:

DESC orders;
Field           Type       Null Key      Default    Extra
order_id        int(8)     unsigned      zerofill   NO  PRI (null)auto_increment
customer_id     int(10)    unsigned      YES        MUL (null)

Second table:

DESC items;
Field            Type      Null Key      Default    Extra
item_id          int(11)   unsigned      NO PRI (null)  auto_increment
order_id         int(10)   unsigned      NO PRI (null)

Based on tables above order_id have different data precision.

But upon checking on information_schema database the precision is identical

SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.NUMERIC_PRECISION, t.ENGINE from information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t on t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_SCHEMA = 'database' AND c.COLUMN_NAME = 'order_id' and c.TABLE_NAME in ('orders','items');

The result was.

TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME DATA_TYPE   NUMERIC_PRECISION   ENGINE
database         items      order_id    int         10                  MyISAM
database         orders     order_id    int         10                  MyISAM

Which of them is accurate? The one displayed in DESC table of the result from information schema? Thank you.

NUMBER_PRECISION is useless except for one case:  ZEROFILL.  Otherwise, it is an indication of how many columns of output are needed to display the largest number.

INT UNSIGNED needs 10 digits.  INT SIGNED needs 11 columns -- a sign (if negative) and 10 digits.

Ignore the column there.  Ignore it when you see int(11).

And don't bother declaring a column to be INT(100) or INT(2); it is still a 32-bit (4-byte) signed binary number.


Sharding for slave node in master slave replication Is it possible to configure sharding only for slave nodes in a master slave replication scheme?

"Sharding" is not supported directly in MySQL.  See other products, such as Spider.

If you meant "Partitioning", then elaborate on what you hope to gain and provide the partitioned and unpartitioned versions of SHOW CREATE TABLE.


Why Does Oracle kill a session at the specific time? Oracle kills sessions remotely open at the specific time. It means if I open a session at local machine, it is ok. In my case, at every 58 minute.

For example, at 13:58, 14:58, 15:58 and so on.

It means even if I open a session at 13:57, it will be closed 1 minute after.

My environment is:

I tested with sqlplus and my python application.
DB is placed in Windows Server 2012.
Firewall is turned off.
Network is connected over local router.

I also checked the Oracle alert.log, but there is nothing special.

Any suggestions?

--------------------Edit--------------------

I get a trace logs after setting TRACE_LEVEL_SERVER on sqlnet.ora.

There seems an error but I don't know what it means.

I run two program, one is my app and the other is seqlplus.

They missed connection at the same time 17:36.

Below are the trace log from each.

[07-4월 -2016 17:36:48:829] ntt2err: soc 4160 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
[07-4월 -2016 17:36:48:829] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[07-4월 -2016 17:36:48:829] nioqrc: recv: packet reader error -> translated to IFCR_EOF
[07-4월 -2016 17:36:48:829] nstimarmed: no timer allocated
[07-4월 -2016 17:36:48:829] nsclose: closing transport
[07-4월 -2016 17:36:48:829] nsclose: global context check-out (from slot 0)
complete

.

[07-4월 -2016 17:36:48:829] ntt2err: soc 2216 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
[07-4월 -2016 17:36:48:829] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[07-4월 -2016 17:36:48:829] nioqrc: recv: packet reader error -> translated to IFCR_EOF
[07-4월 -2016 17:36:48:829] nstimarmed: no timer allocated
[07-4월 -2016 17:36:48:829] nsclose: closing transport
[07-4월 -2016 17:36:48:829] nsclose: global context check-out (from slot 0) complete

If the sessions can no longer be reached, this sounds a lot like Oracle Dead Connection Detection.

From that paper:


Limits with IN operator Are there any upper limits on the amount of terms that could be between the parenthesis on an IN operator?

For example, the query would look something like:

SELECT * FROM table WHERE number IN (1,2,3...n)

I'm curious if there's any upper limit for 'n'.

You mean the IN operator. There are limits, but they differ from DB engine to engine.

SQL Server: 65,536 * Network Packet Size - see Batch Size
Oracle seems to allow 1,000.


how to change the default date format in oracle New to Oracle here, i am working on some assigments but i noticed when i was creating the tables using a script, oracle throws the following error:

ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
       incorrect.  The input data did not contain a number where a number was
       required by the format model.
*Action:   Fix the input data or the date format model to make sure the
       elements match in number and type.  Then retry the operation.

knowing that the date format in my queries is something like '01-JUN-2012'
i was wondering how can i change the default format in my oracle installation to meet my system format? I am using SQL Developer with the Free Oracle version 11g.

also i am wondering if this is because my computer is WIN 7 french version and teh dates in my queries are in the English US format?

UPDATE bb_product
   set salestart = '01-JUN-2012', saleend = '15-JUN-2012', SalePrice = 8.00
   WHERE idProduct = 6;

this query is from the Brewer Beans DB that my professor handed out. an which i am assuming was created in a system that uses the EN-US format...any help?

You generally don't want to rely on implicit conversions of strings to dates.  That only leads to pain and suffering since different users will have different date formats.  Either use ANSI date literals or use the to_date function with an explicit format mask

UPDATE bb_product
   SET salestart = date '2012-06-01',
       saleend   = date '2012-06-15',
       saleprice = 8
 WHERE idProduct = 6

or

UPDATE bb_product
   SET salestart = to_date('01-JUN-2012', 'DD-MON-YYYY'),
       saleend   = to_date('15-JUN-2012', 'DD-MON-YYYY'),
       saleprice = 8
 WHERE idProduct = 6


What Microsoft DBA certificate should I acquire? I am  thinking about getting Microsoft DBA certification, but trying to find out which exactly certificate should I study for gets a little confusing.
My company uses on premises SQL Server 2017 but seems like Azure Database becomes pretty popular. So is on premises going away?
Is "MCSA: SQL 2016 Database Administration" would be the one I need?
If I get "MCSA: SQL 2016 Database Administration" will I be able to administer Azure DB? How different is that?
Thank you

The MCSA certifications are being retired very soon.

This certification retires on January 31, 2021. You will no longer be able to earn this certification after this date.

I would not recommend starting to pursue any of them right now.
The "replacement" exam is Microsoft Certified: Azure Database Administrator Associate.
Notice that the exam related to this cert covers on prem and cloud based offerings:

Candidates for this exam are database administrators and data management specialists that manage on-premises and cloud relational databases built on top of Microsoft SQL Server and Microsoft Azure data services.

Acquiring Microsoft certifications won't give you the same benefits as practical experience.  However, they can be useful for exposing you to the breadth of features and offerings available as part of the Microsoft Data Platform.  And once you're aware of these things, you can dig into the ones that are most valuable to you and your current / potential employers.
There are other valid reasons for pursuing certifications as well: for example, if your employer is trying to maintain or upgrade their Microsoft Partner status


innodb cache duplication due to os page cache and buffer pool We have set our innodb buffer pool 80% of total memory. Mysql page allocation gets stalled sometimes when page cache is occupying memory. Now since mysql is caching table rows and indexes and follows a lifecycle to flush them, doesn't OS page cache have the same data in terms of files? Doesn't it cause cache duplicity and increase in memory usage? If data is duplicated can we assume flushing OS page cache we would have no impact on performance of mysql as innodb buffer pool has hot/active data?

Hence the recommendation of using O_DIRECT.


Get monthly sums from several tables with same (date,amount) columns I have records stored in "date,amount" columns from several tables: table1, table2, table3, table4. Amongst tables, field names are same.
Table 1:

date
amount

2018-02-17
10.00

2018-06-25
18.00

2018-07-09
10.00

2018-07-21
5.00

For each month available in any of the table, I want to sum the amounts coming from all four tables, keeping one column per table:

month
amount_table1
amount_table2
amount_table3
amount_table4

2018-02
10.00
null
null
12.00

2018-03
null
null
3.00
8.00

2018-04
null
16.00
3.00
null

2018-05
null
null
3.00
12.00

2018-06
18.00
1.00
null
null

2018-07
15.00
2.00
7.00
1.00

The following code is highly inspired from Roman Pekar's answer to the question "Multiple FULL OUTER JOIN on multiple tables", that uses an "anchor" table. I appreciate this solution as it is easy to read.
I attempted extending it to the case where records are stored for distinct dates and were not already summed by month. It is almost working, but for some reason the monthly sums in the output array are wrong.
SELECT
    X.month,
    SUM(A.montant) AS 'amount_table1',
    SUM(B.montant) AS 'amount_table2',
    SUM(C.montant) AS 'amount_table3',
    SUM(D.montant) AS 'amount_table4'
FROM (
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table1`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table2`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table3`
    UNION
    SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS month FROM `table4`
) AS X
    LEFT OUTER JOIN table1 AS A ON DATE_FORMAT(A.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table2 AS B ON DATE_FORMAT(B.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table3 AS C ON DATE_FORMAT(C.date_comptabilisation,'%Y-%m') = X.month
    LEFT OUTER JOIN table4 AS D ON DATE_FORMAT(D.date_comptabilisation,'%Y-%m') = X.month
WHERE 1
GROUP BY  X.month

I also attempted adding a stricted WHERE clause, with no more success.
WHERE
    DATE_FORMAT(A.date,'%Y-%m') = X.month
 OR DATE_FORMAT(B.date,'%Y-%m') = X.month
 OR DATE_FORMAT(C.date,'%Y-%m') = X.month
 OR DATE_FORMAT(D.date,'%Y-%m') = X.month

Can someone explain what's wrong with my code?
Thanks.

It is hard to explain where you go wrong, because your logic doesn't fit in mysql.
AS you can see in my sample, you have to add a column that describes, from which table you get the data
This you can use to SUM the amount

CREATE TABLE table1
    (`date` varchar(10), `amount` int)
;

INSERT INTO table1
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table2
    (`date` varchar(10), `amount` int)
;

INSERT INTO table2
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table3
    (`date` varchar(10), `amount` int)
;

INSERT INTO table3
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

CREATE TABLE table4
    (`date` varchar(10), `amount` int)
;

INSERT INTO table4
    (`date`, `amount`)
VALUES
    ('2018-02-17', 10.00),
    ('2018-06-25', 18.00),
    ('2018-07-09', 10.00),
    ('2018-07-21', 5.00)
;

SELECT
CONCAT(YEAR(`date`),'-',MONTH(`date`)) AS yearmonth,
SUM(IF(groupby = 'table1' , `amount`,0)) AS 'amount_table1'
,SUM(IF(groupby = 'table2' , `amount`,0)) AS 'amount_table2'
,SUM(IF(groupby = 'table3' , `amount`,0)) AS 'amount_table3'
,SUM(IF(groupby = 'table4' , `amount`,0)) AS 'amount_table4'
FROM
(SELECT `date`, `amount`,'table1'  AS groupby FROM table1
UNION ALL
SELECT `date`, `amount`,'table2' AS groupby FROM table2
UNION ALL
SELECT `date`, `amount`,'table3' AS groupby FROM table3
UNION ALL
SELECT `date`, `amount`,'table4' AS groupby FROM table4) t1
GROUP BY CONCAT(YEAR(`date`),'-',MONTH(`date`))

yearmonth | amount_table1 | amount_table2 | amount_table3 | amount_table4
:-------- | ------------: | ------------: | ------------: | ------------:
2018-2    |            10 |            10 |            10 |            10
2018-6    |            18 |            18 |            18 |            18
2018-7    |            15 |            15 |            15 |            15

db<>fiddle here


How to dive into an ugly database? I'm sure many of you are/were dealing with a ugly database. You know, that database that isn't normalized at all, that database where you have to do a large painfully query to get the most trivial data, that database that is in production and you can't change a bit... you know, "that one".

My question is, how do you deal with it?

Do you try to make a new database?
You give up and leave it alone?
What advice can you give?

The first thing I do is create an Entity-Relationship Diagram (ERD).  Sometimes you can simply describe the metadata with command-line tools but to save time there are some tools that can generate a diagram automatically.
Second, examine each table and column make sure I learn the meaning of what it stores.
Third, examine each relationship and make sure I understand how the tables relate to one another.
Fourth, read any views or triggers to understand custom data integrity enforcement or cascading operations.
Fifth, read any stored procedures.  Also read SQL access privileges if there are such.
Sixth, read through parts of the application code that use the database.  That's where some additional business rules and data integrity rules are enforced.

update:  I just read an interesting article "9 Things to Do When You Inherit a Database" with a good checklist.

Summary:

Backups
Research (the schema documentation steps I mention above)
Talk to the former developers
A bug database
Source code control
Talk to the users and/or business owners
Establish credibility with the users by fixing a few things or making some enhancements
Create a development environment
Drop obsolete objects


How to loop through all table rows and perform subquery using values of same row? I have a table structured as follows:

| reference | valA | valB |    xml      |
     1        NULL   NULL   <xml stuff>
     3        NULL   NULL   <xml stuff>
     4        NULL   NULL   <xml stuff>
     6        NULL   NULL   <xml stuff>

My objective is to parse the xml column and insert the values into columns valA and valB. I have a query which does this but I only know how to do this by specifying the reference number of the row:

UPDATE table
   SET  valA = (SELECT xml.value(_parsing queryA_) FROM table WHERE reference = N),
        valB = (SELECT xml.value(_parsing queryB_) FROM table WHERE reference = N)
   WHERE reference = N;

I need to be able to loop through each row in the table and perform the same query in each row based on values in that row. So the pseudo code for this would be:

UPDATE table
   SET  valA = (SELECT xml.value(_parsing queryA_) FROM current row),
        valB = (SELECT xml.value(_parsing queryB_) FROM current row)
   in current row;

I am currently doing this in an awkward way by implementing this loop in my main program (C++) by first reading in each reference in an array and then looping through the array for the reference number and setting it in the query in place of N in the example query above.

I am very new to SQL so I don't know if I am making this more complicated than it is and if there's an easy way to do it.

I have been running this operation for many hours (about 164,000 rows in table) so I have a partially updated table.

Answer based on comments originally left by Akina

UPDATE table
SET valA = xml.value(_parsing queryA_),
    valB = xml.value(_parsing queryB_)

When you use a column name in the right-hand side of an assignment expression (parsing expression in your case) it is referenced to a column value in the same record that is currently being updated (if you want to think about an update like it is a record-by-record process). It is always the same row field. Referencing a column in another row is always more complex (needs a window function or another reference to the table).

To continue processing a partially-updated you could add WHERE reference > last_ref to the SET clause of the UPDATE. Your WHERE clause will determine whether each record will be updated or not.

A better way may be to check valA and/or valB values for NULL - if not then the record was already processed previously. For example UPDATE ... WHERE valA IS NULL (an index on the tested field will help). If it is possible that some of the columns will remain null after processing, it is a good idea to set it to some non-NULL value (empty string or single space, for example).


Alter table move taking long time I'm currently trying to compress a table in Oracle with the following statements:

ALTER TABLE MYTABLE COMPRESS FOR OLTP;
ALTER TABLE MOVE;

My question is: the MOVE operation is taking forever and unfortunately I forgot to state the ONLINE clause.

If I cancel it, will I lose data? Or is there any way to find out how long it will take?

There's no problem in canceling the MOVE operation. Oracle just rolls back.

Progress information can often be gathered from [g]v$session_longops, looking for sessions with time_remaining > 0 (or the specific session you're interested in when you know that).


ibdata1 back to it's original size after deleting it I have followed the following steps in order to save space but I am surprised after restarting MySQL ibdata1 gets back to it's original size.

  As you want to reclaim the space from ibdata1 you actually have to
  delete the file:

  Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
  Drop all databases except the above 2 databases
  Stop mysql
  Delete ibdata1 and ib_log files
  Start mysql
  Restore from dump

Source

Did you have innodb_file_per_table = ON?

Regardless of the above setting, ibdata1 will have some stuff in it.  Without more specifics (size of that file, the above setting, number of tables, number of .ibd files, etc.), I cannot be more specific.

In my opinion, "tiny" tables may as well be built with file_per_table = OFF.


Failover Cluster Diagnostic Logs I know there must be a simple answer to this question, but I cannot find it anywhere.  Our SQL 2012 FCI have SQLDIAG .XEL files in their MSSQL\LOG folder.  In some instances, these files are often exactly 100MB yet when opened in SSMS appear to be empty (says displaying 0 events).

I need to know how to manage their maximum size and file retention if this is possible.

We have some root drive mount points that were from SQL 2008 FCI (upgraded to SQL 2012) and these mount points were only 1GB.  As you can guess, these mount points are filling up and I need to limit these Failover Cluster Diagnostic logs if possible or I'll have to come up with an alternative solution.

You can change this behavior with the ALTER SERVER CONFIGURATION statement.  The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want.  See the bottom of the referenced link above for samples/examples.  Here is one copied from the source:

ALTER SERVER CONFIGURATION
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

HOWEVER ... these diagnostic logs are great tools for troubleshooting.  Whereas you can limit the amount of retained data, I would much rather get more storage to store the default amount of data (1000 MB, which is almost free in today's day) for the time when you will need as much diagnostic data as you can get.


Script to backup all databases I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a SQL Script. I cannot use the SQL Agent because this is the express edition.

Currently, I am writing a script which backs up each database one by one. However, this is quite high maintenance - every time a database is added, dropped, or renamed, I have to modify this script to reflect these changes. I would instead like to write one single SQL script which backs up all databases at once, without the need to explicitly backup each database one by one.

This is currently the script used to backup a single database:

Print 'Backing up MyDatabase...'
BACKUP DATABASE MyDatabase
TO DISK = 'F:\Backups\SQL Databases\MyDatabase.bak'
   WITH FORMAT,
      NAME = 'Full Backup of MyDatabase';
GO

How can I go about enumerating a list of all databases and performing a loop to do this backup to all databases?

Just some additional notes of the backup process:

Scheduled Task in Windows executes a batch file at 9:00 PM daily
Batch file calls OSQL which executes the SQL script
The OSQL outputs a text file saving the results
There are about 30 databases to be backed up
Some of the databases are over 2GB (so it will take some time)
A third-party backup service obtains the resulting backup files and pushes them off-site at midnight every night

I believe you can refer to ola.hallengren. SQL server Backup scripts.
As stated on how to use for SQL server Express:

SQL Server Express has no SQL Server Agent. Therefore, the execution
of the stored procedures must be scheduled by using cmd files and
Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.
Execute MaintenanceSolution.sql. This script creates the stored    procedures that you need.
Create cmd files to execute the stored procedures; for example:    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType
= 'FULL'" -b -o C:\Log\DatabaseBackup.txt
In Windows Scheduled Tasks, create tasks to call the cmd files.
Schedule the tasks.
Start the tasks and verify that they are completing successfully.

Now you can modify this easily as per you're needs.
Note: In MaintenanceSolution, you can separately only download SQL server Backup scripts individually.


How to format the output of SQL queries on SQLCMD for SQL Server 2017 on RHEL Linux 7.3? I have installed SQL Server 2017 on RHEL Linux 7.3, and using SQLCMD utility for querying the SQL Server.

When I try to query for example: 'SELECT * FROM SYS.DATABASES'. I get output like the following:

How to format the output for SQL commands on SQLCMD for SQL Server on RHEL Linux?

For example, in Oracle we use 'SET LINES 200 PAGES 200' to format the output / width of the output columns.

Note: I have already tried the following workaround (as per other answer on dba.stackexchange):

C:/> sqlcmd -S my_server
:setvar SQLCMDMAXVARTYPEWIDTH 30
:setvar SQLCMDMAXFIXEDTYPEWIDTH 30
SELECT * from my_table
GO

The sqlcmd utility is handy for executing T-SQL scripts via command-line, such as in automation scenarios, but is limited in its output formatting capabilities and interactive query editor.

Consider using mssql-cli instead for interactive terminal queries. This open source tool provides pagination, handles wide results well, includes IntelliSense ,and allows one to use emacs or vi mode. mssql-cli runs on Linux, Windows, and Mac.

I'll add that Azure Data Studio is a cross-platform alternative to SSMS when a full desktop experience is desired. It does not include all features available in SSMS but does include other features and provides greater extensibility.


Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite Our old product supports 4 different database types for storing data:

SQLite
MySql
Sql Server CE
Sql Server

There is also an export feature, which allows to export some data and here is the problem - it actually exports the database itself:

SQLite and Sql Server CE - the database file (*.db3 or *.sdf respectively)
Sql Server - the database backup file
MySql - the whole database directory

Stupid, right? We also came to think so. So, I had to write a tool, which given an old export file converts it to some database independent binary format.

The problem is how to unit test it? At the very least, I need 4 databases with identical data, which brings me to my question:

Is there a tool to reliably convert between the 4 databases?

Note, that I do not need a super duper tool, which knows to do all the pairwise conversions.

Until now I used http://sqlite2ce.codeplex.com/ to convert SQLite to Sql Server CE, but besides having had to fix several bugs in their code, there is a deeper issue with their conversion - all the integral types become 64 bits long after the conversion, which is not good for me.

Thanks.

You may be able to use Kettle, aka Pentaho Data Integration, to do this.

If not supported natively, someone will have written a plug in for SQL Server CE/SQLLite. Kettle supports any JDBC compliant data source. MySQL and SQL Server are supported of course


Update SQL Server Management Studio without restart When I install or update SQL Server Management Studio it always asks to restart the server. Like now when I updated from 17.3 to 17.4, it asks to restart the server when the update is finished. Is it possible to avoid that somehow?

I now I should not have SSMS on the SQL Server itself, but we still do that, so it is annoying that you have to restart the db server just for updating the client.

Reference:

Download SQL Server Management Studio (SSMS)

Minimize Installation Reboots

Take the following actions to reduce the chances of SSMS setup requiring a reboot at the end of installation:

Make sure you are running an up-to-date version of the Visual C++ 2013 Redistributable Package. Version 12.00.40649.5 (or greater) is required. Only the x64 version is needed.
Verify the version of .NET Framework on the computer is 4.6.1 (or
greater).
Close any other instances of Visual Studio that are open on the
computer.
Make sure all the latest OS updates are installed on the computer.
The noted actions are typically required only once. There are few
cases where a reboot is required during additional upgrades to the
same major version of SSMS. For minor upgrades, all the
prerequirements for SSMS are already be installed on the computer.


How to subtract the sum of columns from different tables? I have two tables:

Withdrawals: account_id + amount
Prizes: account_id + amount

I want to calculate a balance per account, probably in a VIEW.

I could have two queries:

SELECT account_id, SUM(amount) FROM prizes GROUP BY account_id

which gives me the total prize per account, and:

SELECT account_id, SUM(amount) FROM withdrawals GROUP BY account_id

which gives me all withdrawals for these accounts.

Now, I'd like to subtract them to return balances for my accounts. What is the most efficient way to create a view like that?

As an alternative to a full join, you could use UNION ALL to combine the two aggregated sets together, only the withdrawals set would be included with the negated results. You would then use the combined set as a derived table and aggregate the amounts per account_id once more:

SELECT
  account_id,
  SUM(amount) AS balance
FROM
  (
    SELECT
      account_id,
      SUM(amount) AS amount
    FROM
      prizes
    GROUP BY
      account_id

    UNION ALL

    SELECT
      account_id,
      -SUM(amount) AS amount
    FROM
      withdrawals
    GROUP BY
      account_id
  ) AS derived
GROUP BY
  account_id
;

I am not sure how this query would compare to the full join solution but it looks simple enough to me to qualify as a good starting point.


How to enforce integrity when joined tables share a foreign key? Consider the following table structure:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'

JoinTable
    UserGroupFK = 'Admin'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App2'

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    PermissionValue = 'WRITE'

User
    ID = 'John'

Where you have a jointable between UserGroup and Permission, with both UserGroup and Permission having foreign keys to Application.

Is there any way to enforce integrity, to avoid the situation above, where the ApplicationFKs don't match? I cannot simply remove one of the Foreign Keys, as it is possible to have a Permission without any UserGroups, and possible to have a UserGroup without any permissions.

Making sure I understand/have correctly elaborated on @ypercubeᵀᴹ's comment/linked answer:

The correct structure should be:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

JoinTable
    UserGroupFK = 'Admin'
    ApplicationFK = 'App1'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'
    [PK: UserGroupFK, ApplicationFK, PermissionFK]
    [FK -> UserGroupFK+ApplicationFK -> UserGroup]
    [FK -> PermissionFK+ApplicationFK -> Permission]

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    ApplicationFK = 'App1'
    PermissionValue = 'WRITE'
    [FK -> PermissionFK+ApplicationFK -> Permission]

User
    ID = 'John'


Is it possible to create a horizontally partitioned table that is located on two separate sites in Oracle? I know that it is not possible to use @dblink syntax to assign tablespaces.

For example, the following would be illegal.

CREATE TABLE hr.admin_emp (
    empno      NUMBER(5) PRIMARY KEY
  )
  partition by range (empno)
  partition p1 values less than (100) tablespace tbspace1,
  partition p5 values less than (MAXVALUE) tablespace tbspace2@dbserver2;

Is there any alternative way to achieve a similar thing with Oracle12?

Starting with 12c, there is a new feature called Oracle Sharding.

Sharded Database Management

For example you can define the following shardspaces in Global Data Services with GDSCTL:

ADD SHARDSPACE –SHARDSPACE shspace1;
ADD SHARDSPACE –SHARDSPACE shspace2;

ADD SHARD –CONNECT shard1 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard2 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard3 –SHARDSPACE shspace1;

ADD SHARD –CONNECT shard4 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard5 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard6 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard7 –SHARDSPACE shspace2;

Where each shard is an independent, seperate database. Then create the tablespace sets as:

CREATE TABLESPACE SET tbs1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;

And create a partitioned table as:

CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup_date DATE
, CONSTRAINT cust_pk PRIMARY KEY(class, cust_id)
)
PARTITIONSET BY LIST (class)
  PARTITION BY CONSISTENT HASH (cust_id)
  PARTITIONS AUTO
(PARTITIONSET gold VALUES (‘gld’) TABLESPACE SET tbs1,
 PARTITIONSET silver VALUES (‘slv’) TABLESPACE SET tbs2)
;


innodb buffer pool size not changing I have Percona fork of MySQL 5.7 installed where I have defined innodb_buffer_pool_size in /etc/percona-server.conf.d/mysqld.cnf as follows

innodb_buffer_pool_size = 10G

But, when I login to MySQL prompt and run command show global variables like 'innodb_buffer_pool_size' it shows as follows

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

and no matter what value I set in /etc/percona-server.conf.d/mysqld.cnf for innodb_buffer_pool_size the value for innodb_buffer_pool_size in MySQL prompt remains same.

Also, when I run show global variables like 'innodb_buffer_pool_%size' in MySQL prompt it shows as follows

mysql> show global variables like 'innodb_buffer_pool_%size' ;
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size       | 134217728 |
+-------------------------------+-----------+
2 rows in set (0.00 sec)

Can someone tell me why innodb_buffer_pool_size value is not changing? while I have enough memory on my server.

Repeating the above comment to provide an "answer" for this question:
The config file /etc/percona-server.conf.d/mysqld.cnf was not being included in the standard "global config" file, commonly known at /etc/my.cnf. @OP added !includedir /etc/percona-server.conf.d to the end of the global config and then the changes were visible upon restart of MySQL.


Is there a way to make an OOo form for PostgreSQL updateable view? I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in fact completely updateable. I connected the database to OpenOffice Base through jdbc so that I could make forms for it in OpenOffice. There is an option to connect a form to the view as though it was a table, but since the view has no primary key (it can't in PostgreSQL), the view is read-only.

Is there any way to make the form active for the view? Or do I have to decompose the form into one primary and seven sub-forms, so that I could use it to insert data? Or is there any other solution? Or is it not possible to make such a complex form in OOo Base at all?

According to this bug report, there is currently no way to insert data into updatable views through SDBC in LibreOffice Base (I assume it's the same about OpenOffice Base). I tested JDBC and ODBC as well, and no luck. There is no way how to point the Base to a (fake) primary key in the Base, and Base won't insert data into tables without primary key. The author of the report promised to solve this issue himself, but this project looks dead now.

However, the post pointed me to a workaround: when you add a serial-like column to the view (like ROW_NUMBER() OVER (ORDER BY table.id ASC) AS oid), you can assing it as the primary key while importing the view to MS Access through ODBC. This works with MS Access 2003 for me - the data can be inserted into database, with only minor bugs. Access is as good at making forms as OpenOffice base, so this is a good workaround for anyone who have the Access.


How to use RETURNS TABLE with an existing table in PostgreSQL? From the PostgreSQL documentation here:

  There is another way to declare a function as returning a set, which
  is to use the syntax RETURNS TABLE(columns). ... This notation is specified in recent versions of the SQL standard, and thus may be more portable than using SETOF.

This sounds as if RETURNS TABLE is a newer, more portable style to return multiple rows. But I am not sure if the two syntaxes are equivalent.

I was wondering if we can actually use RETURNS TABLE to replace RETURNS SETOF?

In particular, one case I haven't figured out is: if we have an existing table foo and its associated composite type, how can we use it in RETURNS TABLE?

Using the example for the above link, can the following be rewritten using RETURNS TABLE:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

So far, I tried  to use RETURNS TABLE (foo.*) and RETURNS TABLE (foo), which didn't work.

PS: sorry for my comments, I was developing a complex code and I have a small error that seems a stupid PostgreSQL restriction on a "returing table" part... I was stupid, instead of concentrating and solving it, I used the internet (search engine put me here). Now, this wiki-answer is to help other readers, called by  search engine and attracted by the title of the question.
Thanks to @dezso (was a correct answer) and, please all readers, you can edit this question to be more didactic, it is a Wiki.

Since PostgreSQL-v8 we can do it! We can RETURNS EXISTING_TABLE_NAME
In its Guide, in all PostgreSQL versions, from since pg v8 to current version, all have a section named  "SQL Functions as Table Sources". Let's reproduce the Guide's example with some simplifications:
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1);

It is running as expected, it is perfect!
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed

The question "How to use RETURNS TABLE with an existing table in PostgreSQL?" have a good answer since pg v8... This is the way we do it in the last 15 years, the syntax is:  RETURNS SETOF <EXISTING_TABLE_NAME>.
Use clause TABLE as instantaneous CREATE TABLE for returning
The @tinlyx's confusion, explainded on his question, is about the use of the clause TABLE instead of SETOF... To think using the "PostgreSQL syntax logic", we must  first remember that RETURN <EXISTING_TABLE_NAME> is also valid, and it has the same behavior   that RETURN <EXISTING_TYPE_NAME>. Is natural to return only one row.
Next step, remember that we declare a tuple with the CREATE TABLE clause (<tuple_description>), therefore, a good syntax to express an "instant-define-tuple table" is RETURN TABLE (<tuple_description>), and it makes sense to return TABLE -type, which is like an array type, will return several instances (TABLE is a set of tuples).
Next step, remember that we declare a tuple with the CREATE TABLE (<tuple_description>) clause, therefore, a good syntax to express an "instantaneous table-definition" is RETURN TABLE (<tuple_description>); and it makes sense to return Table-type, that is like Array-type, they return multiple instances (TABLE is a set of tuples).
The "modern thing" in PostgreSQL (!) is what @ZiggyCrueltyfreeZeitgeister showed, the RETURNS TABLE (LIKE <table_name>) syntax.
CREATE FUNCTION getfoo2(int) RETURNS TABLE (LIKE foo) AS $$ -- working fine!
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo2(1); -- same result as getfoo(1)

Many ways to do the same, a summary:
Expliciting the table name (two ways) or type name:

RETURNS TABLE (LIKE <table_name>) (modern and good)
RETURNS SETOF <table_name> (old but good)
RETURNS SETOF <type_name> (after  CREATE TYPE <type_name> (<tuple_description>))

Implicit/generic ways, by anonymous types:

RETURNS SETOF  RECORD (generic but somethimes a problem)
(exist something as?) RETURNS SETOF  ROW?

Instantaneous table-definition:

RETURNS TABLE (<tuple_description>)
(no RETURNS) using OUT in the parameter list.

For the last case, using our example to illustrate: CREATE FUNCTION getfoo(int, OUT fooid int, OUT foosubid int, OUT fooname text)
For dynamic and/or polymophic input you must check this explanation.
Best practice?
There are many ways to do the same, so, there are a "best one"?
As syntax I prefer the use of RETURNS TABLE (LIKE <table_name>), that is explicit: no confusion with "implicit RECORD", no fear of incompatibilities...
Important for library management, DROP TABLE foo CASCADE will drop also the function: in any syntax (returns table or returns setof) PostgreSQL will do a good job.
drop table foo cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function getfoo(integer)
drop cascades to function getfoo2(integer)


Microsoft SQL Server 2008 R2 backup error I'm trying to do a simple backup to an external disk and getting this error:

  Backup failed for Server 'Server-name'

Additional information:

  System.Data.SqlClient.SqlError: The media loaded on "C:\Program Files\Microsoft SQL.." is formatted to tsupport 1 media families, but 2 media families are expected according to the backup device specification.
  (Microsoft.SqlServer.Smo)`

Backup type: Full

Why am I getting this error and why is the message pointing to C:\Program Files.. if the destination file is an external device?

This error means that you "striped" your backup across multiple files, and you're only providing one of the files when you're trying to restore.

If you're backing up using SQL Server Management Studio, make sure that you click "remove" to remove any existing backup destinations before you "add" the file you want to back up to.


Postgres UPDATE ... LIMIT 1 I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular.

I want a database query to change the status of a standby - JUST ONE - and return the server IP that is to be used. The pick can be arbitrary: since the status of the server changes with the query, it doesn't matter which standby is selected.

Is it possible to limit my query to just one update?

Here is what I have so far:

UPDATE server_info SET status = 'active'
WHERE status = 'standby' [[LIMIT 1???]]
RETURNING server_ip;

Postgres doesn't like this. What could I do differently?

Without concurrent write access
Materialize a selection in a CTE (Common Table Expressions) and join to it in the FROM clause of the UPDATE.
WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active'
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING s.server_ip;

I originally had a plain subquery here, but that can sidestep the LIMIT for certain query plans as Feike pointed out:

The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT, e.g.:

 Update on buganalysis [...] rows=5

   ->  Nested Loop
         ->  Seq Scan on buganalysis
         ->  Subquery Scan on sub [...] loops=11
               ->  Limit [...] rows=2
                     ->  LockRows
                           ->  Sort
                                 ->  Seq Scan on buganalysis

Reproducing test case

The way to fix the above was to wrap the LIMIT subquery in its own CTE, as the CTE is materialized it will not return different results on different iterations of the nested loop.

Or use a lowly correlated subquery for the simple case with LIMIT 1. Simpler, faster:
UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         )
RETURNING server_ip;

With concurrent write access
Assuming default isolation level READ COMMITTED for all of this. Stricter isolation levels (REPEATABLE READ and SERIALIZABLE) may still result in serialization errors. See:

SELECT … FOR UPDATE SKIP LOCKED in REPEATABLE READ transactions

Under concurrent write load, add FOR UPDATE SKIP LOCKED to lock the row to avoid race conditions. SKIP LOCKED was added in Postgres 9.5, for older versions see below. The manual:

With SKIP LOCKED, any selected rows that cannot be immediately locked
are skipped. Skipping locked rows provides an inconsistent view of the
data, so this is not suitable for general purpose work, but can be
used to avoid lock contention with multiple consumers accessing a
queue-like table.

UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING server_ip;
If there is no qualifying, unlocked row left, nothing happens in this query (no row is updated) and you get an empty result. For uncritical operations that means you are done.
However, concurrent transactions may have locked rows, but then don't finish the  update (ROLLBACK or other reasons). To be sure run a final check:
SELECT NOT EXISTS (
   SELECT FROM server_info
   WHERE  status = 'standby'
   );

SELECT also sees locked rows. Wile that doesn't return true, one or more rows are still unfinished and transactions could still be rolled back. (Or new rows have been added meanwhile.) Wait a bit, then loop the two steps: (UPDATE till you get no row back; SELECT ...) until you get true.
Related:

Atomic UPDATE .. SELECT in Postgres

Without SKIP LOCKED in PostgreSQL 9.4 or older
UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;
Concurrent transactions trying to lock the same row are blocked until the first one releases its lock.
If the first was rolled back, the next transaction takes the lock and proceeds normally; others in the queue keep waiting.
If the first committed, the WHERE condition is re-evaluated and if it's not TRUE any more (status has changed) the CTE (somewhat surprisingly) returns no row. Nothing happens. That's the desired behavior when all transactions want to update the same row.
But not when each transaction wants to update the next row. And since we just want to update an arbitrary (or random) row, there is no point in waiting at all.
We can unblock the situation with the help of advisory locks:
UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         AND    pg_try_advisory_xact_lock(id)
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;
This way, the next row not locked yet will be updated. Each transaction gets a fresh row to work with. I had help from Czech Postgres Wiki for this trick.
id being any unique bigint column (or any type with an implicit cast like int4 or int2).
If advisory locks are in use for multiple tables in your database concurrently, disambiguate with pg_try_advisory_xact_lock(tableoid::int, id) - id being a unique integer here.
Since tableoid is a bigint quantity, it can theoretically overflow integer. If you are paranoid enough, use (tableoid::bigint % 2147483648)::int instead - leaving a theoretical "hash collision" for the truly paranoid ...
Also, Postgres is free to test WHERE conditions in any order. It could test  pg_try_advisory_xact_lock() and acquire a lock before status = 'standby', which could result in additional advisory locks on unrelated rows, where status = 'standby' is not true. Related question on SO:

Postgres pg_try_advisory_lock blocks all records

Typically, you can just ignore this. To guarantee that only qualifying rows are locked, you could nest the predicate(s) in a CTE like above or a subquery with the OFFSET 0 hack (prevents inlining). Example:

Put pg_try_advisory_xact_lock() in a nested subquery?

Or (cheaper for sequential scans) nest the conditions in a CASE statement like:
WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

However the CASE trick would also keep Postgres from using an index on status. If such an index is available, you don't need extra nesting to begin with: only qualifying rows will be locked in an index scan.
Since you cannot be sure that an index is used in every call, you could just:
WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

The CASE is logically redundant, but it servers the discussed purpose.
If the command is part of a long transaction, consider session-level locks that can be (and have to be) released manually. So you can unlock as soon as you are done with the locked row: pg_try_advisory_lock() and pg_advisory_unlock(). The manual:

Once acquired at session level, an advisory lock is held until
explicitly released or the session ends.

Related:

Optimizing concurrent updates in Postgres


Disable Oracle substitution parsing I need to update a string (VARCHAR2) value in an Oracle database which contains Javascript code.

The update script contains the following string:

c===l&&p(a,b)

which when executed from "Oracle SQL Developer", triggers (on that line) the following popup:

Is there a way to disable the substitution parsing?

You can use this command to disable parsing for substitution variables for the session.

SET DEFINE OFF;

Enter this at the start of the query or operation.  This cannot be used with PL/SQL code blocks but the standard SELECT, INSERT, UPDATE, DELETE allow it.

This Stack Overflow question from 2010 shows this is a popular question!


Reset all sequences so that they continue after max(id) + 1? It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like:

  django.db.utils.IntegrityError: duplicate key value violates unique
  constraint "django_admin_log_pkey" DETAIL:  Key (id)=(2) already
  exists.

Is there a query/script I could run that would run across all tables in the database, look at any columns tied to a sequence and reset those sequences to max(column) + 1?

Using PostgreSQL v11.

I see you are using Django.
I implemented a command to resolve these issues for all databases in your settings file.
import psycopg2
from django.conf import settings
from django.core.management.base import BaseCommand
from django.db import connections

def dictfetchall(cursor):
    """Return all rows from a cursor as a dict"""
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

class Command(BaseCommand):
    help = "Resets sequencing errors in Postgres which normally occur due to importing/restoring a DB"

    def handle(self, *args, **options):
        # loop over all databases in system to figure out the tables that need to be reset
        for name_to_use_for_connection, connection_settings in settings.DATABASES.items():
            table_name = connection_settings['NAME']
            host = connection_settings['HOST']
            user = connection_settings['USER']
            port = connection_settings['PORT']
            password = connection_settings['PASSWORD']

            # connect to this specific DB
            conn_str = f"host={host} port={port} user={user} password={password}"

            conn = psycopg2.connect(conn_str)
            conn.autocommit = True

            select_all_table_statement = f"""SELECT *
                                    FROM information_schema.tables
                                    WHERE table_schema = 'public'
                                    ORDER BY table_name;
                                """
            # just a visual representation of where we are
            print('-' * 20, table_name)
            try:
                not_reset_tables = list()
                # use the specific name for the DB
                with connections[name_to_use_for_connection].cursor() as cursor:
                    # using the current db as the cursor connection
                    cursor.execute(select_all_table_statement)
                    rows = dictfetchall(cursor)
                    # will loop over table names in the connected DB
                    for row in rows:
                        find_pk_statement = f"""
                            SELECT k.COLUMN_NAME
                            FROM information_schema.table_constraints t
                            LEFT JOIN information_schema.key_column_usage k
                            USING(constraint_name,table_schema,table_name)
                            WHERE t.constraint_type='PRIMARY KEY'
                                AND t.table_name='{row['table_name']}';
                        """
                        cursor.execute(find_pk_statement)
                        pk_column_names = dictfetchall(cursor)
                        for pk_dict in pk_column_names:
                            column_name = pk_dict['column_name']

                        # time to build the reset sequence command for each table
                        # taken from django: https://docs.djangoproject.com/en/3.0/ref/django-admin/#sqlsequencereset
                        # example: SELECT setval(pg_get_serial_sequence('"[TABLE]"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "[TABLE]";
                        try:
                            reset_statement = f"""SELECT setval(pg_get_serial_sequence('"{row['table_name']}"','{column_name}'),
                                                    coalesce(max("{column_name}"), 1), max("{column_name}") IS NOT null) FROM "{row['table_name']}" """
                            cursor.execute(reset_statement)
                            return_values = dictfetchall(cursor)
                            # will be 1 row
                            for value in return_values:
                                print(f"Sequence reset to {value['setval']} for {row['table_name']}")
                        except Exception as ex:
                            # will only fail if PK is not an integer...
                            # currently in my system this is from django.contrib.sessions
                            not_reset_tables.append(f"{row['table_name']} not reset")

            except psycopg2.Error as ex:
                raise SystemExit(f'Error: {ex}')

            conn.close()
            print('-'*5, ' ALL ERRORS ', '-'*5)
            for item_statement in not_reset_tables:
                # shows which tables produced errors, so far I have only
                # seen this with PK's that are not integers because of the MAX() method
                print(item_statement)

            # just a visual representation of where we are
            print('-' * 20, table_name)


Restrict insertion of new row if a row exists with certain field values Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table.
e. g row 1
withdrawal, txn1
I am trying to add a constraint where it restricts to insert new row with txn_type <> 'withdrawal' and txn_id = txn1.
e.g it restricts to insert row with following values
non_withdrawal, txn1
My situation is like if a record exist with transaction_type = withdrawal & txnid=txn1 then no non-withdrawal record with same txn id should be able to be inserted.

Answer left in a comment by matigo:
Two ways to do this would be through a BEFORE INSERT trigger or via a stored procedure. With a trigger, you can be sure that every application that writes directly to the database must follow the prescribed rules. With a stored procedure you can (potentially) simplify the entire process if there are multiple steps that must be followed in succession.


Make query depend on input in PostgreSQL function I have a simple query:

CREATE OR REPLACE FUNCTION companies_all_teams(
  IN  _company_name text = NULL,
  IN  _company_id int = NULL,
  OUT _result text
)
AS
$$
  BEGIN
  _result := (SELECT json_agg(distinct(teams.name))
                     FROM jobs
                     JOIN companies
                       ON companies.id = jobs.company_id
                     JOIN teams
                       ON teams.id = jobs.team_id
                     WHERE lower(companies.name) = lower(_company_name)
                       AND jobs.is_disabled = false
             );
  END;
$$ LANGUAGE plpgsql;

There are two inputs, but only one of those is being used in the query. What is the simplest way to run the query conditionally based on which input we have?

If I understand correctly, you can sometimes provide _company_id directly, while you only have _company_name in other situations. You do not intend to provide both at once.

Since one parameter is of type text while the other is integer the most efficient solution for this case is probably function overloading. I.e., create two functions of the same name, one for text and another one for integer.

While being at it, I suggest simple SQL functions for the simple task. The principal is the same as for plpgsql.

json_agg() returns data type json, not text. Your plpgsql variant silently coerces the type in the assignment, but that's bad style. I made out the functions to return json directly. You can cast to text explicitly if you need to.

1) companies_all_teams(text)

CREATE OR REPLACE FUNCTION companies_all_teams(_company_name text)
  RETURNS json AS
$func$
   SELECT json_agg(DISTINCT(t.name))
   FROM   companies c
   JOIN   jobs      j ON j.company_id = c.id
   JOIN   teams     t ON t.id = j.team_id
   WHERE  lower(c.name) = lower(_company_name)
   AND    NOT j.is_disabled
$func$ LANGUAGE sql;

2) companies_all_teams(int)

Since we have two functions, we can optimize each for their input. Assuming referential integrity in your relational design, we don't need to join to companies at all, we already know the company_id:

CREATE OR REPLACE FUNCTION companies_all_teams(_company_id int)
  RETURNS json AS
$func$
   SELECT json_agg(DISTINCT(t.name))
   FROM   jobs      j
   JOIN   teams     t ON t.id = j.team_id
   WHERE  j.company_id = _company_id
   AND    NOT j.is_disabled
$func$ LANGUAGE sql;

To avoid ambiguity with overloaded function calls you need to provide explicitly typed values. In your simple case, however, it's still enough to use numeric literals (no single quotes) for integer or string literals (single quotes) for text.

Call:

SELECT companies_all_teams(123);           -- numeric literal without quotes
SELECT companies_all_teams('My Company');  -- string literal with quotes

Related:

No function matches the given name and argument types
Function quote_nullable(timestamp without time zone) is not unique
Is there a way to disable function overloading in Postgres


What are the ports and directionality of the TCP connections used in MySQL replication? I wanted to know how replication works in mysql.

Both slave and master have mysql server running say on port 3306.
Slave acts like  a client and master acts like a server.

Does slave always bind to 3306 when it makes a tcp connection to send the request? When the response is sent back by server the dest port will be 3306 and since slave mysql is listening to that port it will process the response?

Not sure if my understanding is correct.

Also I am interested in knowing ingress and egress rules that should be added for replication to work.

For TCP connection from slave will it pick any available TCP port or is it always 3306?

Any help is appreciated!

Thanks!

Your assumptions are incorrect.

The slave does not bind to 3306 (or whatever port it is listening to) when making its outbound connection to the master, and the port the slave is "listening to" for inbound client connections is otherwise irrelevant in replication.

The slave establishes a TCP connection from a high port to the master's port (e.g. 3306), authenticates itself, optionally does a little bit of session setup (for example, telling the master its desired heartbeat configuration), and then requests a binlog stream from the master, beginning at a specific file and byte position.

The master responds by beginning to stream binlog events beginning at that position (assuming it's valid) and continuing to autonomously send new binlog events to the slave over that same TCP connection as those events are generated on the master.  The master never makes a connection back toward the slave unless both machines are slaves to each other in a circular replication configuration where each of them is the other's slave.

The single TCP connection, originated by the slave, is the pipeline for transferring binlog events and the master will continue streaming events as long as it thinks this TCP connection is established.  If the connection is severed for any reason, the slave is responsible for reconnecting to the master, with the same process as before, and requesting a new start point for the binlog stream, based on the last valid event it received before it lost connectivity.

Ever server has a server_id global variable.  If a master accepts a connection from a slave that identifies itself with the same server_id as one that is already connected, the master will forcibly close those already-existing connections, preventing the possibility of the master wasting resources streaming traffic into a black hole because of network issues that make the master think the former connection (which should have been from the same slave) is still established.

So your single access list entry would be "TCP, from slave IP, high random port -- to master IP, port 3306" (assuming the master is listening on 3306).

Additional details:

The replication io_thread is the thread in MySQL that creates the outbound TCP connection to the master, receives the replication events over that connection, and writes them to the "relay log" (file) on the slave.  It doesn't "process" the replication events, it stores them for execution by the sql_thread.

The sql_thread on the slave then reads the relay log (file) written by the io_thread and executes the events found there.  It appears like any other client thread in SHOW PROCESSLIST but it is not created via a connection to port 3306 on the slave, because it's a thread created inside the MySQL process when you START SLAVE.

There is no reason a thread that's running inside the MySQL process would need to make a TCP connection to the same process -- it's already running inside the MySQL server and has access to the server internals to execute the replication events received from the master and stored to the relay log.


How to find number of days since beginning of fiscal month? This relates to my question How to find XXth day of previous month in SQL server?

The fiscal month starts on 26th of each month.

I need to find out from any date:

The date of the start of the fiscal month (Start_Of_Fiscal_Month)
The number of days into the fiscal month (Day_Of_Fiscal_Month)

For example:

CurrentDate - '2014-04-26'

Start_Of_Fiscal_Month = 2014-04-26
Day_Of_Fiscal_Month = 1

CurrentDate - '2014-05-02'

Start_Of_Month = 2014-04-26
Day_Of_Fiscal_Month = 7

This will do it:

DECLARE @d DATE = '20140502', @dm TINYINT = 26;
DECLARE @Start_Of_Fiscal_Month DATE

SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d)));
SELECT @Start_Of_Fiscal_Month
SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1


Unexpected very slow user count in WordPress I have a Wordpress page that returns timeout error every so often. When reviewing the MySQL "slow queries" log, I could notice that there is an extremely slow query coming from Wordpress, but I don't know exactly which module:
SELECT  SQL_CALC_FOUND_ROWS wpe_users.*
    FROM  wpe_users
    WHERE  1=1
      AND  wpe_users.ID IN (
        SELECT  DISTINCT wpe_posts.post_author
            FROM  wpe_posts
            WHERE  wpe_posts.post_status = 'publish'
              AND  wpe_posts.post_type IN ( 'post', 'page', 'attachment',
                        'wp_block', 'products' )
                          )
    ORDER BY  display_name ASC
    LIMIT  0, 10;

The query appears two or more times, with State Copying to tmp table and Time of up to 53815.44 (secs? 15 hours? it's crazy!). And it makes the Mysql process consume 557% of CPU and 8.7% of RAM

The wpe_users table contains almost 3 million entries and the wpe_posts table contains 68,000 entries. The wpe_comments table is also starting to cause problems as it contains 361,000 entries.
I use pagination for comments and posts but not for users. The question is, how can I fix the problem without disabling paging? How can I identify where the user query is coming from if I don't show users anywhere?
What have I tried?

I updated Wordpress to the latest version available.
I tried to identify where these queries come from using the "Query Monitor" plugin but they don't appear.
I verified the only 3 plugins that I have active and none of them generate such queries.
I closed the administration panel and killed said processes to see if the queries were generated by the administration panel but they start again...

I don't have much experience with MySQL configuration. I am using MySQL 5.5.62 with Plesk panel on a VPS 5GB RAM 6 Cores
SHOW CREATE TABLE wpe_users
CREATE TABLE `wpe_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`),
  KEY `user_email` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=3492068 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN wpe_users query
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
| id | select_type        | table     | type  | possible_keys                | key              | key_len | ref  | rows    | Extra                        |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
|  1 | PRIMARY            | wpe_users | ALL   | NULL                         | NULL             | NULL    | NULL | 2780982 | Using where; Using filesort  |
|  2 | DEPENDENT SUBQUERY | wpe_posts | range | type_status_date,post_author | type_status_date | 164     | NULL |    2265 | Using where; Using temporary |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+

Add this composite index to wpe_posts:  INDEX(post_status, post_type, post_author).
The answer to "why":  In 5.5 (and before), IN ( SELECT ... ) was a terribly slow construct.
Amd see if this runs faster:
SELECT  SQL_CALC_FOUND_ROWS wpe_users.*
    FROM  ( SELECT  DISTINCT post_author
            FROM  wpe_posts
            WHERE  post_status = 'publish'
              AND  post_type IN ( 'post', 'page', 'attachment',
                        'wp_block', 'products' )
          ) AS x
    JOIN wpe_users USING wpe_users.ID = x.post_author
    ORDER BY  display_name ASC
    LIMIT  0, 10;

For further discussion, please provide EXPLAIN SELECT ... and SHOW CREATE TABLE.
Be aware that a future version is likely to remove the SQL_CALC_FOUND_ROWS feature, forcing you to make two SELECTs.


SQL Server Query Plans & Offline databases I recently asked a question about exporting query plans (here Export Query Plans) and got some great answers.

However, I did run into one issue, during the process of interrogating SQL Server to obtain the plans, the process fails if there are any offline databases.  Simple question, to which I suspect the answer is complex.  How do I have the interrogation ignore the offline databases?

Thank you in advance for your assistance!

Did you try:

get-sqldatabase -ServerInstance yourserver | Where-Object status -eq 'Normal'


duplicate SAMPLE db in db2 Right now im accessing a SAMPLE db which is in remote server. I found that its default instance db2inst1 with database name "SAMPLE". Since everybody using the same database for all their purposes, i want to create a new DB for my application. i tried connecting to the server through telnet. Since there are lot of data in SAMPLE db, cloning or duplicating same would help me save time. Please can someone post some set of commands or steps to duplicate SAMPLE db through telnet and sqldeveloper. If not commands or steps for creating a new db also is fine.

FYI: DB2 version - v10.5.0.3

The SAMPLE database is usually created with the db2sampl command (documented here for DB2 LUW 10.5). You can tell db2sampl to create another SAMPLE database under a different name with the -name option (note: this command must typically be run by someone with SYSADM privilege in the instance, e.g. by the db2inst1 user). For example, on a Linux box:

someuser@myserver:~$ sudo su - db2inst1
[sudo] password for someuser:
db2inst1@myserver:~$ db2sampl -name SAMPLE2


In Postgresql 12 how to I make the primary server a slave after a deliberate failover I am using PostgreSQL 12 on Debian 10.6.
I have been reading up about replication and I have set up an asynchronous replication between one primary and standby. I wanted to test failover so I stopped the primary server and promoted the standby which was successful. I'm having difficulty finding a tutorial online about how to make the old primary a standby server now that the old standby is the new primary. What follows are the steps that I have taken, it looks like it's working but is there anything I may have missed or not thought of?
On the new primary
Update the pg_hba
echo "host    replication     replicator      <new standby ip>/24        md5" >> /etc/postgresql/12/main/pg_hba.conf

On the new standby
Make a back up of the lib
sudo mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main.bk

Make a backup of the old tablespaces directory, and make a new empty directory
sudo mv /tablespaces /tablespaces.bk
sudo mkdir /tablespaces/

Make a base backup of the new primary
pg_basebackup -D /var/lib/postgresql/12/main -F p -R -c fast -l mffb -P -v -h <primary_ip> -p 5432 -U replicator

If WAL Archiving is set, I'm taking these extra steps:
On new primary:
echo "archive_mode = ON" >> /var/lib/postgresql/12/main/postgresql.auto.conf
echo "archive_command = 'test ! -f  /wal_archive/%f && cp %p /wal_archive/%f'" >> /var/lib/postgresql/12/main/postgresql.auto.conf
echo "restore_command = ''" >> /var/lib/postgresql/12/main/postgresql.auto.conf

On new standby
echo "restore_command = 'cp /wal_archive/%f %p'" >> /var/lib/postgresql/12/main/postgresql.auto.conf

Sounds about right, but I would keep the configuration identical on both servers, so that you don't have to change anything:

recovery settings like restore_command are ignored on the primary

you can set archive_mode and archive_command on the standby, since it doesn't archive WAL anyway (unless you set archive_mode = always)

Before you remove the data directory and re-initialize the standby with pg_basebackup, you could try to run pg_rewind, which does the same, but faster.