Duplicate rows - how to remove one? I have a pretty large table (~114 million rows) containing OS MasterMap data. This is freshly loaded data in a new table. When trying to set the primary key, I get this error:

ERROR:  could not create unique index "tbl_os_mmap_topoarea_pkey"
DETAIL:  Key (toid)=(1000000004081308) is duplicated.

Somehow, I have ended up with an exactly duplicated row. Every field is the same in these two rows. I want to delete one row, but keep the other. As there is no way to distinguish between the two, how can this be done?

I would like to do this as quickly and simply as possible. Creating temporary tables etc. is not really an option as it would take too long on a dataset of this size. Creating a new unique ID column would be quicker I guess, but also probably take some time.

After a bit of research, I have learned that all records in postgres have a hidden unique id, the ctid. Can I use this to delete one of the duplicate rows?

This will work I think:
with d as
  ( select ctid, row_number() over (partition by t.*) as rn
    from tablename as t
  )
delete from tablename as t
using d
where d.rn > 1
  and d.ctid = t.ctid ;

And another variation. Not sure which will be more efficient:
delete from tablename as t
where exists
      ( select *
        from tablename as d
        where d.ctid > t.ctid
          and d.* is not distinct from t.*
      ) ;

But note what the docs say abut ctid:

ctid
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

So, if the table was created WITH OIDS, use that instead.


Improve the performance of ST_Within boundary lookups in PostGIS I'm trying to improve the performance of a georeferencing feature in our application. For every address in our system, we need to store which of several boundary types it is located within (local government area, electoral boundaries and so on).

The lookup process I'm using seems very slow for what it is. In this test dataset of 690 rows, and 150 relevant boundaries, the query takes a bit over 5 seconds on my iMac:

UPDATE testdata
SET ELB = (
    SELECT gc.id
    FROM geo_choice gc
    JOIN geo_choice_list gcl ON gc.geo_choice_list_id = gcl.id
    WHERE gcl.name = 'FEDERAL_ELECTORATE'
      AND gc.geo_choice_list_version_id = gcl.current_version
      AND ST_Within(ST_Point(testdata.lon, testdata.lat), gc.geom)
      LIMIT 1
)

This version takes just over 9 seconds:

UPDATE testdata
SET ELB = gc.id
FROM geo_choice gc
JOIN geo_choice_list gcl ON gc.geo_choice_list_id = gcl.id
WHERE gcl.name = 'FEDERAL_ELECTORATE'
      AND gc.geo_choice_list_version_id = gcl.current_version
      AND ST_Within(ST_Point(testdata.lon, testdata.lat), gc.geom)

And this version, using CROSS LATERAL (inspired by this blog post), is about 5.5:

UPDATE testdata t1
SET ELB = gc.id
FROM testdata t2
CROSS JOIN LATERAL (
    SELECT gc.id
    FROM geo_choice gc
    JOIN geo_choice_list gcl ON gc.geo_choice_list_id = gcl.id
    WHERE gcl.name = 'FEDERAL_ELECTORATE'
      AND gc.geo_choice_list_version_id = gcl.current_version
      AND ST_Within(ST_Point(t2.lon, t2.lat), gc.geom)
    LIMIT 1
) AS gc

There is a spatial index on the geo_choice table:

CREATE INDEX geo_choice_gix ON public.geo_choice USING gist (geom) TABLESPACE pg_default;

I can confirm the index is being used: if I used _ST_Within, to negate it, the query takes over 4 minutes.

Is there a faster way to do this kind of boundary lookup, or something else I'm missing?

I had a similar issue, I find a faster way (30% than using joins an ST_Within in my case) using python with the shapely package, shapely has a method called .contains that works similar to ST_Within. I'm not sure if .contains is faster than ST_Within but using a scripting language allows you to split the processes at different times, and that's the key point from my understanding.
These would be the steps I done:

Store all geometries as a huge .json or several .json files (in my case I have about 15,000 geometries divided into about 500 .json files), you should find fast the geometry or the groups of geometries you want using a key. You could store a geo_json or the hexadecimal, see documentation.
Iterate over all geometries in the .json and keep it in memory as shape, maybe as a list() of  dict() making a relation like [{"id":5, "shape": <shape object ..>}]. In this step you have all relevant geometries in memory and you could access them instantly using a key or keys. The limitation here is the memory available.
Iterate over points (as Point) and inside iterate over precalculated shapes using .contains. Be careful because we are comparing all points to all geometries.
Store the result you want, depending on the implementation. This is one connection and one query (insert, update..).

Step 1 should be run only once or every time there is an update on any geometry.
Step 2 should be executed at the beginning of the script only once outside the loops.
Step 3: Depending on the exact implementation this needs to be improved, for example you should reduce as much as possible the geometries to iterate, maybe at first match you could break the iterations.


Updating rows in tables concurrently using Multithreading in Java I have an excel file with 50000 rows and I'm updating a MySQL table using hibernate by uploading that excel file from a Java Program. It is taking a huge time to update all the rows. Can it be possible if I divide batches of 100 rows in excel file and assign different batches of rows to different threads to concurrently update the rows in table from multiple threads ?

You can split your file  into multiple files.

Try POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files (Apache POI Project)

Here's an example of how to read an Excel file :

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
   }


SQL Management Studio user permissions with multiple databases I'm trying to create SQL Management Studio user permissions for different users and different databases. I want to make sure that every user can only see one database and this user will also have to be able to backup and restore their databases. I have been playing around with the server roles and user mapping, but I cannot seem to find the right settings. With the sysdmin server role, a user is able to perform a restore, but then that user is also able to access other databases.

Privilege to backup the database part is straight forward.

You create 2 database:

CREATE DATABASE dbuser1
GO
CREATE DATABASE dbuser2
GO

Create 2 login:

CREATE LOGIN loginuser1 WITH PASSWORD ='Aa123456789!@#'
GO
CREATE LOGIN loginuser2 WITH PASSWORD ='Aa123456789!@#'
GO

Create 2 user from the login created above:

USE [dbuser1]
GO
CREATE USER [user1] FOR LOGIN [loginuser1]
GO
USE [dbuser2]
GO
CREATE USER [user2] FOR LOGIN [loginuser2]
GO

Make the users member of db_backupoperator in corresponding databases:

USE [dbuser1]
GO
EXEC sp_addrolemember db_backupoperator, [user1]
GO
USE [dbuser2]
GO
EXEC sp_addrolemember db_backupoperator, [user2]
GO

Now user1 can only take backup up of dbuser and user2 for dbuser2. If user2 attempt to backup dbuser1 following error message will be issued.
Msg 916, Level 14, State 1, Line 1

  The server principal "user2" is not able to access the database
  "user1" under the current security context. Msg 3013, Level 16, State
  1, Line 1 BACKUP DATABASE is terminating abnormally.

For restore you have to add the users to dbcreator fixed server role.
Then user2 can restore dbuser1 with REPLACE option.  That is something you do not want.

There is few solution discussed here and hope you can implement one of these for restricting restore.


SSIS append trailer with Script I am trying to add a footer to a flat file output. I have 2 data flows in my control flow. One creates the file from the DB. It goes from OLE DB Source to Flat File Destination. The second control flow uses a second connection manager pointed at the same file path as the first data flow and uses. In the second data flow i am using a script component pointed at the flat file to append the trailer.

The issue is the script is not generating any output. I am a C# novice and have modified code found on the net and would appreciate any help.
Here is the scipt code in C#2012:

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent

 {

     public void Main()
     {
         const string dirPath = @"Q:\General\Operations\Prod_Support\XOOM OPS Procedures\Wells_Testing\";

         var fileBody = AddHeaderAndFooter.GetFileText(dirPath + "WellsPOS.txt");

         var trailerRecord = "9" + DateTime.Today.ToString("ddMMyyyy") + AddHeaderAndFooter.CountRecords(dirPath + "WellsPOS.txt").ToString();

         var outPutData = fileBody + trailerRecord + "\r\n";

         AddHeaderAndFooter.WriteToFile(dirPath + "WellsPOS.txt", outPutData);

     }
 }

 public static class AddHeaderAndFooter
 {
     public static int CountRecords(string filePath)
     {

         return (System.IO.File.ReadAllLines(filePath).Length + 2);

     }

     public static string GetFileText(string filePath)
     {
         var sr = new System.IO.StreamReader(filePath, System.Text.Encoding.Default);

         var recs = sr.ReadToEnd();

         sr.Close();

         return recs;
     }

     public static void WriteToFile(string filePath, string fileText)
     {

         var sw = new System.IO.StreamWriter(filePath, false);

         sw.Write(fileText, System.Text.Encoding.ASCII);

         sw.Close();

     }
 }

I had to use a Script Task at the Control Flow level instead of a Script Component at the Data Flow level. When I switched that around it worked like a charm.                        I also changed the sw.Write(fileText, System.Text.Encoding.ASCII); line to sw.WriteLine(fileText, System.Text.Encoding.ASCII); so it adds the text to the next line.


Why can't I connect to SQL server 2017 LocalDB shared instance? So I've been struggling on this for the past few days.
I cannot connect to a SQL Server 2017 (14.0.1000.169) LocalDB shared instance. I've been searching and reading all over the net why but could not find any answer that worked.
I tried on a laptop that previously had some SQL Server Express and stuff that I all have uninstalled. And also on a fresh Win10 VM.
Both always have the same error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

This error is from SQLCMD but I've also tried with SSMS or visual studio connection string.
I tried connecting with the owner of the instance and could not. Also tried with a SQL Login with sysadmin right and could not.
I can only connect with the pipe name.
I also tried everything from these links
https://stackoverflow.com/questions/10214688/why-cant-i-connect-to-a-sql-server-2012-localdb-shared-instance
and
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1257bf26-6ab0-416d-bf26-34f128f42248/sql-2016-sp1-sqllocaldb-versions-errors-with-quotwindows-api-call-quotreggetvaluewquot?forum=sqlexpress
Why can't I connect?

Seems to be a bug in LocalDb v2017 and higher. I tested all available versions, starting from v2012 SP4. Up to and including v2016 SP2, the friendly share name "(localdb)\.\SHAREDNAME" is accepted in the connection request. When using v2017 or higher, the request always results in an "instance not found" error.

I've also observed that querying all installed LocalDb versions with sqllocaldb.exe v results in an error message for v2016 and higher:

  Windows API call "RegGetValueW" returned error code: 0.

Not sure whether this behavior might be linked to the original issue.

UPDATE

Applying the registry fix linked in the question solves the version query.

After some more digging, I think I may have discovered the cause for the connection failure. When SQLLocalDb up to v2016 is installed on the local machine, the share feature works as follows:

In the registry, a new key is created in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\{SharedName}. This key contains a string value  InstanceName, which stores the pipe name, as visible in the following screenshot:

When the corresponding instance is started, it always picks this pipe name. The name stays persistent, i.e. when the instance is restarted, the pipe name is reused.

This logic is destroyed when LocalDb v2017 or higher is installed: When an instance with a shared name is started, the pipe name in the registry is ignored. Instead, a completely different pipe name appears when querying the instance via sqllocaldb i {instance}.

This is probably also the reason why the connection can't be established, since the pipe names don't match. Editing the registry value to match the actual pipe name solves the issue, but only until the instance is restarted.


ORA-00904 error while querying the Oracle database table I am newbie with oracle DB and I need to List the Number of departments per region in the region table.

This is my SQL statement:

    select r.region_id, count(d.department_id) "Number of Department"
    from region r, department d
    where r.region_id = d.region_id
    group by r.region_id

I am getting this error:

  ORA-00904: "D"."region_id": invalid identifier

How can I correct this error?

When ORA-00904: invalid identifier occurs, we must enter a valid column name as it is either missing or the one entered is invalid. This error most commonly happens when we are referencing an invalid alias in a select statement.  Oracle's voice on ORA-00904 error:

  ORA-00904 string: invalid identifier

  Cause: The column name entered is either missing or invalid.

  Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #.
  If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

To avoid ORA-00904, column names must
begin with a letter.
consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them.
be less than or equal to thirty characters.


Split cases in WHERE clause based on boolean variable I have to split a WHERE clause based on a boolean variable:

If ShowZeroVariable is true, I only what to show rows value 0 in a certain integer column.
If false I want to show all that are greater than 0.

Something like this:

select IntField from Table
where
IntField
case
when ShowZeroVariable  = 'true' then = '0'
else <= '0'
end

I can only get it to kind of work by casting to text
(But this doesn't filter out the zero values when false):

select IntField from Table
where
IntField::text like
case
when ShowZeroVariable  = 'true' then '0'
else '%'
end

Can't seem to get a WHERE clause CASE statement to work with operators < / = / >.

CASE probably isn't the right tool here. But you can use a Boolean expression.

...
WHERE showzerovariable
      AND intfield = 0
       OR NOT showzerovariable
          AND intfield > 0
...


Returning data based on if another column in another table is NULL I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been billed. In a separate table is a list of reports, once a report has been billed a number is populated in the Report_Billing_Num column. How do i get the below code to show non-billed orders?

SELECT
      [Call_Num],
      [Call_Cust_Num],
      [Call_Cust Name],
      [Call_Status]
FROM tableCall

JOIN tableREPORTS
ON tableCALL.Call_Num = tableREPORTS.Report_Call_Num

WHERE tableCALL.Call_Status = 'COMP' AND tableREPORT.Report_Billing_Num IS NULL

ORDER BY Call_Status

This code kind of works, however in the tableREPORTS table several rows can be in the table for the same Call_Num which seems to be showing duplicated when i run this script. How would i have it so duplicated aren't shown? Would it be better to use a NOT IN or NOT EXISTS query?

Would it be better to use a NOT IN or NOT EXISTS query?

Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter.

This should work for what you need to do:

SELECT
      [Call_Num],
      [Call_Cust_Num],
      [Call_Cust Name],
      [Call_Status]
FROM tableCall
JOIN tableREPORTS
ON tableCALL.Call_Num = tableREPORTS.Report_Call_Num
WHERE tableCALL.Call_Status = 'COMP'
  AND tableCALL.Call_Num in(select tableREPORTS.Report_Call_Num from tableREPORTS where tableREPORT.Report_Billing_Num IS NULL)
ORDER BY Call_Status

  How would i have it so duplicated aren't shown?

If there are duplicate values in tableREPORTS.Reportz_Call_Num, then eliminating the join should eliminate duplicate records in the results. However, you may still get duplicate records in the results if the tableCall.Call_Num column isn't unique or a primary key.


Use CASE to select columns in UPDATE query? I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:

SELECT CASE WHEN val = 0 THEN column_x
            WHEN val = 1 THEN column_y
            ELSE 0
       END AS update, ...

Is something similar at all possible when performing an UPDATE query in Postgres (i.e. choose which columns should be updated)? I assume not since I couldn't find anything about this, but maybe someone has a clever alternative (besides using a procedure or updating each column using a CASE to determine if the value of the column should be assigned a new value or simply reassigned the existing value). If there is no easy alternative, I'll of course accept that as an answer as well.

Extra info: In my case I have 14 potential columns that may be updated, with only one being updated per matching row (the table to be updated is joined with another in the query). The amount of rows to update will most likely vary, could be dozens or hundreds. I believe indexes are in place for the joining conditions.

If you specify a column should be updated then it will always be updated, but you can change the value you put in conditionally and put back the original values depending on your conditions. Something like:

UPDATE some_table
SET    column_x = CASE WHEN should_update_x THEN new_value_for_x ELSE column_x END
     , column_y = CASE WHEN should_update_y THEN new_value_for_y ELSE column_y END
     , column_z = CASE WHEN should_update_z THEN new_value_for_z ELSE column_z END
FROM   ...

So if the conditions are not right for an update to a particular column you just feed back it's current value.

Do note that every row matched will see an update (even if all the columns end up getting set to the values they already have) unless you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.


SQL Server cannot obtain a LOCK resource at this time I'm somewhat familiar with blocking processes and locks in SQL Server, however this one has me pretty confused. I understand this error can come from two issues: 1) if SQL Server cannot allocate more memory for locks or 2) if there is a blocking process.

I do not believe #1 is the issue as the machine has 48GB memory and only ~14GB is used. SQL Server is configured to use a max of 2,147,483,647MB of memory.

With #2, when I run sp_lock, I see a row with an ObjID and a TABLE lock, mode IS. This lock is under the same process I am running SSMS under. Therefore when I try to do a KILL on the process, I get the error "Cannot use KILL to kill your own process."

I have restarted the server a couple of times and closed SSMS and still this lock persists. My program can't do work on the relevant table because of the lock; it keeps getting the error message that SQL Server cannot obtain a lock and to try again at a less-busy time. There is no activity on the server, I am the sole user. Therefore I do not believe this is a resource issue, but firmly related to the lock I can't seem to get rid of.

I have run a modified sp_lock procedure that uses sp_who in conjunction to try and determine who is blocking who. In this view, I see my userid has one IS mode lock, but it is not an exclusive lock.

How can I release the table lock? What is happening with the database? How can I avoid this issue in the future?

the machine has 48GB memory and only ~14GB is used. SQL Server is configured to use a max of 2,147,483,647MB of memory.

This does not tell wether the locks memory is exhausted or not. SQL Server will use for the lock manager either the configured sp_configure 'locks' value or, by default, some percent of the available total memory. The lock manager can well exhaust the available locks memory w/o the total memory exceeding the 14GB you see. you need to check SQL Server, Memory Manager Object:

Lock Blocks   Specifies the current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.
Lock Blocks Allocated Specifies the current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.
Lock Memory (KB)  Specifies the total amount of dynamic memory the server is using for locks.
Lock Owner Blocks Specifies the number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.
Lock Owner Blocks Allocated   Specifies the current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.

However, in your case, I do not believe the problem to be lock manager memory exhaustion. It could be a lock held by an orphaned transaction, although is very unclear from your explanation. To begin with, post the exact error message your application is seeing. Normally applications either get the lock, or they wait. An infinity. A 'could not get the lock' message would imply some very specific context (eg. attempt to obtain a lock for ALTER DB). So is very important to know what message you get, and the exact statement being executed when you get the message.
Next step, depending on the result of the previous steps, is to look into sys.dm_tran_locks.


mysql database restore takes long time I am in the process of reinstalling a Ubuntu server to rhel6 server. I have a pretty huge database. When I checked the mysql data directory size, it was around 354 GB. However, I dumped the entire database using the mysqldump command.

After reinstalling the machine, I started to restore the database using the mysql command. I started the restore process 3 days back. When I checked the mysql data directory in this newly installed machine, the size is just 31 GB. I am confused if actually the mysql command is restoring the database. I still see the command getting executed and the process is visibile from the linux top command. Is there something else I can do to speed up the process?

If you're using largely innodb tables increasing the innodb_buffer_pool_size as large as possible (~80-90% system assuming nothing else but mysqld is really consuming ram).  This requires a mysqld restart though

Before the reload you can also set innodb_flush_log_at_trx_commit=2 to speed things up.  This disables flushing to disk every commit (breaking the D in ACID), but should be fine for a restore.  If there's a catastrophic failure during this process you're starting from scratch anyway.  Be sure to set this back to one after the restore.

This can be changed dynamically without a bounce

Ensure innodb_flush_method = O_DIRECT is set in your cnf.  This requires a restart

Is it getting pegged on CPU in top?  If you have a lot of compressed table data that will be inherently slower.  There's not much that can be done about this aside from getting a faster core.  Multi core wouldn't help you here since the load is done serially anyway.

If you're using spinning disks, if possible ensure the .sql source file is being read from a different set of HDDs.  Similarly, during the reload (unless you're trying to let the reload replicate) ensure binary logging and general logging are turned off.

If you're running 5.6 ensure performance schema is not enabled.


SQL restore duration I am trying to determine the duration of backup restore. I executed a few restore command to restore some backups located on network share drive. Here is command and summary,

RESTORE DATABASE [test]
FROM DISK = '\\network\test.bak'
WITH
    MOVE 'test_data' TO 'D:\test_data.mdf',
    MOVE 'test_log' TO 'E:\test_log.ldf',
    CHECKSUM, STATS;

Backup A
1st restore take about 5 min. Ok, after restore complete, I delete the database
2nd restore take about 1.8 min. Hm.. Let's delete the database and try again.
3rd restore take about 1.5 min. Hm..

Backup B (almost same size as backup A, at the same network location)
1st restore take about 1.8 min. I deleted it and try again
2nd restore take about 1.5 min. Ok..

The service account is enabled for instant file initialization.

My question is why the restore duration varied on different restore attempt on the same backup file to the same machine? Is it purely on network throughput (perhaps someone was doing something on the network share and stuff), or something else like cache or something in SQL internal?

Some of it will have to do with caching on the remote machine.  You've also got to account for what else is flying across the network.  Also some of those blocks may be in cache on the SQL Server's disk cache (not SQL's buffer pool) as they are the same exact blocks the disk may use the cached copy not the actual page you are restoring.

Don't forget things like witches, gremlins, boogie men, etc.


Failover scenario with principal "hard down" My company has a failover pair of SQL Server 2005 instances, which provide database availability for a UL-licensed alarm/call center, where uptime and availability are critical for life safety.

Here's an example line from the connectionStrings of one of the business-critical applications (sanitized for our protection):

<add name="MyCompany.MyApp.Properties.Settings.MyDbConnectionString"
 connectionString="Data Source=Principal-DB;Failover Partner=Mirror-DB;
 Initial Catalog=MyDb; Integrated Security=True; Persist Security Info=True;"
 providerName="System.Data.SqlClient" />

About half an hour ago, this was the scenario as we believe it to be:

DBA reports need for hardware maintenance on Principal-DB server to all users at a specified time, and recommends they close and restart client applications if they experience database connection issues.
DBA fails over from Principal-DB to Mirror-DB. Client applications are unaffected.
DBA powers down Principal-DB server.
Some client applications begin to timeout on pooled connections (as would be expected). Users experiencing these errors close and restart as recommended by the DBA.
Those client applications now fail to start altogether, due to a failure to receive a response from the database.
Hilarity ensues.

Our theory was that, because Principal-DB was not in a state to respond to connection requests at all, when it would normally refuse such connections quickly if it were in the restoring state, the client applications ended up waiting for the entire allotted connection timeout (default, 20 seconds) for the primary server to respond, then returning the timeout error without ever attempting to connect to the listed failover partner.

The quick fix was to push an update to the client application containing an App.config that swapped the Data Source and Failover Partner instances, so Mirror-DB was now the server to which the app attempted to connect first. When we fail back to Principal-DB, we will have to undo this change with another application update.

I need a more permanent fix. This was not our expected behavior for a failover pair, and it cannot be allowed to happen again. There has to be a way to configure the client application so that it will correctly try to connect to the failover partner in this circumstance before returning an error.

I found the problem.

SQL Native Client and the .NET SQL Data Provider should seamlessly connect to a failover partner even if the primary is down; however, the default configuration of both providers is to try both TCP/IP and Named Pipes.

This configuration is fine, and works appropriately, when the servers are still both available but the primary has failed over to the backup, because the network-layer connection will succeed, but the application-level connection will be promptly refused causing the client to try the mirror. If the primary is unresponsive, however, the client will waste the entire default 20-second timeout period just waiting for the primary server to respond, first over TCP/IP, then over Named Pipes (which by itself has a default 20- to 30-second timeout period at the network level), and so in this situation it will return an error without ever having tried the backup mirror.

The solution, according to this TechNet article, is to force the client, one way or another, to connect using only TCP/IP, which will fail fast enough that the client has ample time to try both partners. You can manually configure the data providers to do this, or you can override their default settings case-by-case, by specifying the proper protocol to use in the connection string. The parameter to include that forces TCP/IP is Network=dbmssocn;. On top of that, the SQL client has a "three strikes" approach before it gives up and tries the failover mirror, so the Connection Timeout should be set long enough that it will get through this process and try the failover partner at least once. A timeout of 30 seconds should be adequate.

We tried this fix when our primary DB server was powered down again, and it does work. Now we're in the process of updating the config files of all our in-house apps (we have at least a half-dozen desktop apps and intranet sites that target this failover pair; it's a well-loved stack).


Should I create multiple maintenance plans to backup more than 200 User databases I have a SQL Server 2008 with more than 200 User databases. Some are larger than others and every two weeks or so a new db gets created.
Should I create only 1 maintenance plan backing up all 200+ databases or should I break them into 3 separate maintenance plans.
How much load does a backup plan generate on the server. Will it affect performance if I have them in the same Plan.
Some more details regarding the infrastructure.
I am using SSD's. The storage is all SATA, It's not a SAN but I have a network drive where I store the backups. I cannot backup to a tape. My backup strategy is Full once a week Differential every day. I am using SImple backup (Due to storage restrictions, cannot let logs grow and cannot have logs backup). The databases as I mentioned are roughly 10 GB around 10 or 12 DBs are 100 GB+ and I cannot use third party tools. I can create Maintenance plans

I wouldn't use maintenance plans but either Hallengren's backup solution, or, Minion Backup (the latter would be better because you can still have a single job, but write a table into each database which tells it which day it backs up on). In your comment you said you can't use "third party tools". I wouldn't go so far as to call these that, they're scripts, and whatever red tape there is I'm sure you can work around it by asking.

Question: How long do all the backups take?

I would likely go with the single job assuming all backups can complete in a reasonable amount of time. But key to this is using operational validation - writing something to check that all databases have been backed up, and also testing the backups themselves on a cycle.

If you wanted to go the other route - staggering backups - I've also done this on lots of large (at the time) backups to slow storage. I wrote/used a simple formula to run across the databases and determine which day each backup would run.

Pick a measure for the databases (cost in minutes).
Construct a table of databases ordered from most cost to least cost.
Construct a table of the backup days, with slots for database name and cost.
Iterate the database table.
Find the next slot in the table of backup days with the least cumulative cost.
Move the database into that slot.
Repeat until there are no databases left.
You are left with a list backup days, databases, and costs that balance out surprisingly well.

So you'd then use that to set a different backup for each day with those databases (and something to catch anything left over - which is going to be pretty difficult).


Design a db table where rows may have NULL in multiple columns I am trying to design a database for tour packages. Tour packages contains the detailed itinerary.

Tour package duration may be between 3 and 30 days. I need to add the description of each day.
If  I make 30 columns like day_1, day_2, ... day_30 .
While creating the package for 5 days the rest of 25 Columns will be empty/NULL.

There will be constraints that all packages should have less than 30 days.

How can I improve this design ?

You normally would have your data normalized. This means you will have one table for the tour packages, and another one for each days itinerary:

CREATE TABLE tour_packages
(
    tour_package_id INTEGER PRIMARY KEY,
    tour_package_name varchar(150),
    other_data varchar(1000)
) ;

CREATE TABLE package_schedules
(
    tour_package_id INTEGER NOT NULL REFERENCES tour_packages(tour_package_id) /* ON DELETE CASCADE */,
    day INTEGER NOT NULL,  -- Day 1 of itinerary, day 2, ...
    day_description varchar(1000) NOT NULL,

    -- The rows in this table are identified by a combination of tour_package_id and day
    PRIMARY KEY (tour_package_id, day),
    -- We can have the database check that we don't have day 0, or day negative
    CHECK (day >= 1),
    -- If you want to limit the number of days:
    CHECK (day <= 30)
) ;

With the proper CONSTRAINTS defined for your tables (which include in this case the PRIMARY KEYS, the REFERENCES and CHECKS) you can be quite sure that your data follows the rules.

The only thing that an SQL database (as of now) cannot do for you is to make sure that you don't have "missing days" just by declaring constraints.

That is, the situtation produced by the following INSERTs cannot be avoided, and should be taken care of at the application level:

We have one tour package:

INSERT INTO tour_packages
    (tour_package_id, tour_package_name)
VALUES
    (1, 'My first tour') ;

We add now the schedules for days 1 and 3, but we miss day 2:

INSERT INTO package_schedules
    (tour_package_id, day, day_description)
VALUES
    (1, 1, 'First day of first tour: Welcome') ;

-- We are missing day 2

INSERT INTO package_schedules
    (tour_package_id, day, day_description)
VALUES
    (1, 3, 'Third day of first tour: Going to visit Honolulu') ;

This could actually be accomplished on the unnormalized scenario that you provided, with a large collection of CHECK constraints; where each day_n can only be null if day_n+1 is also null. That is, all the NULL values must be on the right side and be consecutive.

CHECK(day_1  IS NOT NULL)
CHECK(day_2  IS NOT NULL OR (day_2 IS NULL AND day_3 IS NULL))
...
CHECK(day_29 IS NOT NULL OR (day_29 IS NULL AND day_30 IS NULL))

I'd say that the advantage that the unnormalized design has because of this is far outweighted by all the inconvenients you may have. The day that you will move from 30 day-max packages to 90 day-max will become absolutely obvious. The day that you want to query: Is there any package where any scheduled day moves through Honolulu? you will find out as well.

The "missing day" is usually dealt with at the application layer, not the database. The database could in theory also handle this (probably) by using triggers, or with a database that allows the definition of functions that can perform complicated checks, and put them into CHECK( no_empty_days(tour_package_id)). In practice, it's something I've seldomly found.

The previous definitions are standard SQL and run on all versions available in DBFiddle.uk (as of today). Take into account, however, that MariaDB (or MySQL) allow the declaration of CHECK constraints, but then do not enforce them.

You can check everything at dbfiddle here


SSRS - select where parameter and fixed value So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement.

So what I'm trying to do is this:

select * from table where id in (Parameter_1, 'total')

Yet when I do that it complains its can't find 'Parameter_1'.

I've gotten another report to work when its just:

select * from table where id = Parameter_2

but this this isn't good enough in this use case.

Ok the problem was actually with the parameter itself.

I had to create a new one in the query called @Parameter_1 and then set it up in the query parameter tab and delete the old one.

Really confusing.


Checking if user owns child records (MySQL) I am having a quite big problem with the following queries. The tables looks like:

A (Parent)

+------------+---------+
| id | user_id | value |
+------------+---------+
|  1 |     20 |      X |
|  2 |     20 |      Y |
|  3 |     12 |      Z |
+------------+---------+

B (Child)

+------------+-----------+
| id | parent_id | value |
+------------+-----------+
|  1 |         1 |     X |
|  2 |         2 |     Y |
|  3 |         2 |     Z |
+------------+-----------+

C (Childs of the Child records)

+------------+-----------+
| id | parent_id | value |
+------------+-----------+
|  1 |         1 |     X |
|  2 |         1 |     Y |
|  3 |         1 |     Z |
+------------+-----------+

I need two (separate) queries. The first for verification if records in the table B belongs to the user id = X and then if records in the table C belongs to the user id = X. So for example I have got IDs (1, 2, 3, 18, 329) of records from the table C and I need to check if these values belong to the user id = 20. The result should be 1, 2, 3. How can I do this? I don't work with SQL a lot so I am completely lost and I appreciate every help. Thanks

You need a Select with 2 joins:

SELECT c.id
FROM c
  INNER JOIN b ON b.id = c.parent_id
  INNER JOIN a ON a.id = b.parent_id
WHERE c.id in (1, 2, 3, 18, 329)
  AND a.user_id = 20 ;


What is the most efficient way to count the number of rows in a table? I am using Postgres with the following query:

select count(*) from image;

The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app often attempts to ingest images that have already been recorded in the database, so the primary key/serial number ensures they are only recorded once.

Now we are wondering if we should have gone with an incrementing primary key instead. We have 1,259,369 images in the database and it takes about 7 minutes for the count query to run.

Our app will never delete images from this table - so an incrementing primary key would allow us to check the value of the last ID which would equal the number of rows in the table.

Generally, if you don't need an exact count, there is a much faster way:

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'image'::regclass;

Fast way to discover the row count of a table

As a matter of fact, in a DB with concurrent write access every count is an estimate, because the number may be outdated the instant you get it.

But, like @a_horse commented, there is something off in your DB. Counting a million should not take more than a few seconds in the worst case.

That your app will never delete images from this table makes this even more suspicious, because there shouldn't be many dead rows then. (Or are you updating a lot?) A huge amount of dead tuples could slow you down - and call for VACUUM. Normally, autovacuum takes care of this. Did you enable it? (It's the default in modern Postgres.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.


Store time taken to run query in a table I am trying to gauge performance across about 300 views with and without some changes. I can write a script easily enough to run every view, but ideally, I wanted to get the time it took for each view to run individually.

I know that you can use SET STATISTICS TIME ON, to get the time, but this would be per-query and would not be easily searchable. Ideally, I wasn't sure if the STATISTICS TIME ON has an output parameter or something, or additionally - if there is a known way to manually time a query's execution so that I can insert it somewhere?

A good start would be to look at the exec_query_stats view.
You can get the execution count and total elapsed time and a lot more, but this is only from queries that have been cached.

SELECT dest.TEXT AS [Query],
       (deqs.total_elapsed_time+0.0)/deqs.execution_count average_elapsed_time,
       deqs.*
  FROM sys.dm_exec_query_stats AS deqs
 CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
 ORDER BY 2 DESC

You might want to issue a CHECKPOINT and DBCC DROPCLEANBUFFERS after you've installed your new views to clear the cache.


Unable to telnet to port 1433 in SQL Server clustered instance? Situation: I believe that multiple port 1433 can be implemented as long as the IP address are all unique.
I’ve created SQL Server 2008 R2 SP1 clustered virtual instance on top of my Windows Server 2008 R2 SP1 Enterprise MSCS, one instance is working fine and I can perform:
C:\telnet SQLClustername-instance1 1433
successfully
However with the other instance I cannot perform the above command and it is not accessible at all ? pinging the SQLClustername-instance2 is fine and reply back, when I do the following command:
C:\netstat –ano | find /I “1433”
I cannot see the SQLClustername-instance2 IP address with port 1433 listening ?
Any assistance would be greatly appreciated.
Thanks.

Does the SQL Server instance installed on the cluster resource named SQLclustername-instance2 listen on port 1433? If you installed a named instance then is very likely using dynamic ports. See Configure a Server to Listen on a Specific TCP Port.


Mysql: Query which orders by default and rank (pin/hold up entries?) I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to "hold up"/"pin" certain products to a place in the returned query collection.
Means, I want to set something like rank_index which contains the number the product should have in the returned query collection.
Example:
id     title                rank_index
1      An awesome product
2      Another product      3
3      Baby car
4      Green carpet         1
5      Toy

Lets assume the default order would be the id, descending. But descending? Because as I pointed out the products table gets permanently filled with new products and I always want to show the newest product on top. However, when e.g. an product has a rank_index of 2 the database always should return this entry as the second no matter how money new products are written to the database!
The result of the table above would be:
id     title                rank_index
4      Green carpet         1
5      Toy
2      Another product      3
3      Baby car
1      An awesome product

Is this somehow possible to do (without recursion)? The rank_index column was just an idea of mine. I mean.. I also could do this on the php side and do a normal query which does only include the products without an rank_index and one which only contains products with an index_rank and order them manually on the php side. Do you would recommend this? Would that be faster?
Furthermore, late on I want to add a perfomance_index which is not an +1 incrementing index but rather an index which show the performance of an product. This should be sorted descending as well if that makes any difference to this problem.
This here is doing a good job but only for ascending tables, I want a descending table with products being added permanently!
SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5)

Btw: I am using Laravel 8 if this makes any difference.
Kind regards

You can add to the ORDER BY a IF ot CASE, depending on the complexity or the new order.
In you case you ned to add the max(id) into the mix

CREATE TABLE table1 (
  `id` INTEGER,
  `title` VARCHAR(18),
  `rank_index` VARCHAR(4)
);

INSERT INTO table1
  (`id`, `title`, `rank_index`)
VALUES
  ('1', 'An awesome product', NULL),
  ('2', 'Another product', '3'),
  ('3', 'Baby car', NULL),
  ('4', 'Green carpet', '1'),
  ('5', 'Toy', NULL);

    SELECT t1.id, t1.`title`,t1.`rank_index`
    FROM table1  t1 CROSS JOIN ( SELECT MAX(id)  maxid FROM table1) t2
    ORDER BY IF(rank_index IS NULL, maxid - id + 1 , rank_index - 0.5) ASC

id | title              | rank_index
-: | :----------------- | :---------
 4 | Green carpet       | 1
 5 | Toy                | null
 2 | Another product    | 3
 3 | Baby car           | null
 1 | An awesome product | null

db<>fiddle here


How to build a table for a private messaging system that supports replies? I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply to and to what was that a reply to and so on and so on.

I'm trying to find a right database structure that would avoid redundancy, so I'm wondering how can I link a single message to all other messages that it is replying to?

I thought about basically having a field called reply_id which would be a serialized array holding the IDs of all messages to be presented as replies

Does anyone have a suggestion on how to do this efficiently? Is my thought a good practice?

Hierarchic Self-Referential Data

Syntax shown using the free and open source database, PostgreSQL

You need a self-referential table, which creates hierarchy in the database. This is how it looks.

CREATE TABLE messages (
  ts_entered  timestamp DEFAULT now(),
  reply_to    int       REFERENCES messages,
  id          int       PRIMARY KEY
                        GENERATED BY DEFAULT AS IDENTITY,
  content     text
);
CREATE INDEX ON messages (reply_to, id);

Notice that reply_id reference an id on the very table. In a more enterprisey example, we'd probably make it EXCLUDE the same id.

Then we enter test data like this.

INSERT INTO messages ( reply_to, id, content )
VALUES
  ( null, 1, 'SYN'),
  ( 1,    2, 'SYN-ACK'),
  ( 2,    3, 'ACK'),
  ( null, 4, 'We should give free advertising to PLAN EXPLORER'),
  ( 4,    5, 'Admins should disclose their affiliations with products'),
  ( 5,    6, 'BANNNN'),
  ( 4,    7, 'Plan Explorer only works on one database'),
  ( 7,    8, 'BANNNN'),
  ( 4,    9, 'Plan Explorer does not support Linux'),
  ( 9,   10, 'BANNNN'),
  ( 4,   11, 'Plan Explorer only does what decent databases already do'),
  ( 11,  12, 'BANNNN')
;

Now in order to query this, we need a RECURSIVE CTE.

WITH RECURSIVE t(reply_to, id, content, root, level)
AS (
  SELECT reply_to, id, content, ARRAY[id], 0
  FROM messages
  WHERE reply_to IS NULL
  UNION ALL
    SELECT messages.reply_to, messages.id, messages.content, root + ARRAY[messages.id], t.level+1
    FROM t
    JOIN messages
      ON (messages.reply_to = t.id)
)
SELECT * FROM t
ORDER BY root;

And you're done..

 reply_to | id |                         content                          |   root    | level
----------+----+----------------------------------------------------------+-----------+-------
          |  1 | SYN                                                      | {1}       |     0
        1 |  2 | SYN-ACK                                                  | {1,2}     |     1
        2 |  3 | ACK                                                      | {1,2,3}   |     2
          |  4 | We should give free advertising to PLAN EXPLORER         | {4}       |     0
        4 |  5 | Admins should disclose their affiliations with products  | {4,5}     |     1
        5 |  6 | BANNNN                                                   | {4,5,6}   |     2
        4 |  7 | Plan Explorer only works on one database                 | {4,7}     |     1
        7 |  8 | BANNNN                                                   | {4,7,8}   |     2
        4 |  9 | Plan Explorer does not support Linux                     | {4,9}     |     1
        9 | 10 | BANNNN                                                   | {4,9,10}  |     2
        4 | 11 | Plan Explorer only does what decent databases already do | {4,11}    |     1
       11 | 12 | BANNNN                                                   | {4,11,12} |     2
(12 rows)


Find minimum number of columns that identify uniqely all rows I'm using SQL Server Management Studio.

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

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

It isn't reasonable to try to do this. I'm going to assume there is no existing primary key / unique constraints existing already. The problems with this approach are:

Data can change at any time. If you found a unique set of values, there is no guaranteeing that they will remain unique
Analyzing the data to find this could be extremely costly from a time/resources perspective and would require extensive customization for each specific table.
Figuring out how many columns actually make up the key by their values is extremely hard (you'd probably have to take a brute force approach) as you can add any x number of columns to a key and still have unique values when looking at them as a set.

In some cases there may be something obvious, like an ID column, which is probably a primary key candidate. However, if no primary keys or unique constraints are defined, it is always best to double check with a data owner / steward.

In most cases, context, the business, and/or requirements will define what unique keys are. It is not something I'd ever advise inferring just from the data unless you are extremely familiar with the data domain and even then you should refer to a subject matter expert to validate.


Select returns users locked or a default value for null rows I'm don't have much knowlegde on Oracle database and used some questions from here to obtain this query:

select case when ((sysdate - timestamp)*24 < 1 and returncode <> 0) then

(username  || '@' || userhost|| ' ON ' || to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')|| ' FAILED AT ' || action_name || ' BECAUSE ' || returncode || decode(returncode,'28000',':Maximum Login Attempt','1004',':Wrong Connection','1005',':NULL Password','1017',':Wrong Password','1045',':Insufficient Priviledge','0',':Login Accepted',':Unkown Code'))

else 'none'

end as "locked users" from sys.dba_audit_session where (sysdate - timestamp)*24 < 1 and returncode <> 0 order by timestamp;

With this query, I want to retrieve users locked on which date and what causes the lock, if there's no users, returns only 'none' instead of "no rows selected". For users locked it works, but the else statement don't. Can someone explain me why? I need this to fix my zabbix database monitoring with ODBC.

Just select the union of the query and the 'none' constant if the original query does not return any row:

with s as (
select case when ((sysdate - timestamp)*24 < 1 and returncode <> 0) then
(username  || '@' || userhost|| ' ON ' || to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')|| ' FAILED AT ' || action_name || ' BECAUSE ' || returncode || decode(returncode,'28000',':Maximum Login Attempt','1004',':Wrong Connection','1005',':NULL Password','1017',':Wrong Password','1045',':Insufficient Priviledge','0',':Login Accepted',':Unkown Code'))
else 'none'
end as "locked users" from sys.dba_audit_session where (sysdate - timestamp)*24 < 1 and returncode <> 0 order by timestamp
)
select 'none' as "locked_users" from dual where not exists (select * from s)
union all
select * from s;


Duplicate Oracle Database from Windows to Linux I have a regular Oracle 12c database already created, named ECLS. I've been given a pile of .BKP files, not from this database, and have been told to replace everything in ECLS with the contents of these files.

Here's how I've attempted to do that so far:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initECLS.ora' nomount;
SQL> exit
$ rman auxiliary /
RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere' NOFILENAMECHECK;

Inside the pfile, I have this line set:

*.enable_pluggable_database=true

It runs for 2.5 hours, then it fails with this error message:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/02/2017 14:05:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/fast_recovery_area/ECLS/archivelog/2017_02_02/o1_mf_1_73358_d970psod_.arc'
ORA-10562: Error occurred while applying redo to data block (file# 66, block# 799698)
ORA-10564: tablespace NCDS_TS
ORA-01110: data file 66: '/u01/app/oracle/product/12.1.0/db_1/dbs/D:ORADATAFUUVZPD152204CC3CA44789537C2BE3F39041EDATAFILEO1_MF_NCDS_TS_CQ476L1O_.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 106975
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [212], [], [], [], [], [], [], [], [], [], []

In the logs at /u01/app/oracle/diag/rdbms/ecls/ECLS/alert/log.xml I see these messages:

<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/ecls/ECLS/trace/ECLS_pr07_5138.trc:
ORA-00308: cannot open archived log &apos;D:\ORADATA\FUUVZP\ONLINELOG\O1_MF_1_CQ39NGLV_.LOG&apos;
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [67], [], [], [], [], [], [], [], [], [], []
 </txt>
</msg>
<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Slave exiting with ORA-10562 exception
 </txt>
</msg>
<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/ecls/ECLS/trace/ECLS_pr07_5138.trc:
ORA-10562: Error occurred while applying redo to data block (file# 66, block# 1200147)
ORA-10564: tablespace NCDS_TS
ORA-01110: data file 66: &apos;/u01/app/oracle/product/12.1.0/db_1/dbs/D:ORADATAFUUVZPD152204CC3CA44789537C2BE3F39041EDATAFILEO1_MF_NCDS_TS_CQ476L1O_.DBF&apos;
ORA-10561: block type &apos;TRANSACTION MANAGED DATA BLOCK&apos;, data object# 106975
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [67], [], [], [], [], [], [], [], [], [], []
 </txt>
</msg>

The fact that I'm seeing Linux errors about files not being found and Windows file paths suggests to me that this is somehow related to the fact that the .BKP files were generated on a Windows machine while the place they're being used is a Linux machine.

How can I account for this? Is there some setting in the pfile that I need to change during startup? Or is there another argument that needs to be passed to RMAN in the duplicate command?

An Oracle employee told me that redo logs are not platform independent. They told me I'd either have to shut down the Windows database and take a cold backup of it without any redo logs, or I'd have to restore the files I already have to another Oracle database running on Windows.

We ended up setting up a new Windows machine to restore the database to.


PGpool Promote to master and Slaves wont replicate on failure Sorry for the copy paste from stackoverflow, but this is probably a better place to post this.

  Version pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64.rpm

I’ve seen some chat online about this from a while back, but I haven’t found a solve.

I have 2 pgpool servers running streaming replication and load balancing. They connect to 3 postgres servers. 2 are virtual machines and 1 Physical machine.

I’d prefer the physical machine to be the master server. I have done test using the pcp_promote_node command, and I see that the show pool_nodes; shows the node I select as primary, however on postgres side the command SELECT pg_is_in_recovery(); show that server still in recovery.

All the pcp_promote_node command does, is detach master node. Which then prevents insert into the DB.

Secondly If the master fails, another server does become master, however the other slave is clueless of this and does not replicate any new data until I recover it from the new master.

Is there a work around for the above 2 problems. Also please let me know if u need more info, such as logs etc etc.

Thanks

Ok so. By design, pgpool does the above.

The follow_master_command in the pgpool.conf file is what you need to use to resolve this issue.

I use the following script (follow_master.sh which i place in the /etc/pgpool-II folder)

#!/bin/sh

################
##
## $1 = node id
## $2 = Old master node id
## $3 = node hostname
##
###############
PGPOOLIP=10.**.**.**
PGUSER=postgres
PGPASS=*************
PGHOME=/var/lib/pgsql/9.3
REMOTE_PGDATA=/var/lib/pgsql/9.3/data

if [ $1 = $2 ]; then
        /usr/bin/pcp_detach_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
else
        sleep 5
        ssh -T postgres@$3 "
        LD_LIBRARY_PATH=$PGHOME/lib:LD_LIBRARH_PATH;
        $PGHOME/bin/pg_ctl -w -D $REMOTE_PGDATA stop"
        /usr/bin/pcp_detach_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
        /usr/bin/pcp_recovery_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
        /usr/bin/pcp_attach_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
fi

I've done testing and it seems to be working fine.

I hope this helps someone in the future.

Thanks

Rob


Is introducing a where condition into the join clause bad practice In the query below, the condition

and r.master_seq=206

is used on the on clause as a join condition.
In the programme code, the value for r.master_seq is set by a parameter.

My concern is that I'm not sure if it's ok to mix in this condition(r=.master_seq=123, that I believe should be a where condition) into the join condition.
I was thinking that I should only use relational information(primary keys and foreign keys) in the join clause, not a where condition ( where id=123, balance>300, etc).

SELECT
  *
FROM
TB_REQUEST_DATA D

left outer join
TB_SPOT_REPORT_REQUEST r
on D.SEQ = r.REQUEST_SEQ
and r.master_seq=206

WHERE
  d.SEQ IN (
  7,12,5,11,4
  )

desc tb_request_data;
Name          Null     Type
------------- -------- -------------
SEQ           NOT NULL NUMBER
REQUEST_NM    NOT NULL VARCHAR2(50)
REG_DT        NOT NULL DATE
REG_ID        NOT NULL VARCHAR2(20)
UP_DT         NOT NULL DATE
UP_ID         NOT NULL VARCHAR2(20)
DISASTER_TYPE          VARCHAR2(500)

desc TB_SPOT_REPORT_REQUEST;
Name         Null     Type
------------ -------- -------------
MASTER_SEQ   NOT NULL NUMBER
SEQ          NOT NULL NUMBER
REQUEST_SEQ  NOT NULL NUMBER
REAL_FILE_NM          VARCHAR2(100)
DOWN_FILE_NM          VARCHAR2(100)

Putting a condition in the WHERE clause and the ON clause are two very different things.  The ON clause is part of the JOIN.  Now with an INNER JOIN you won't see any difference in the output (unless also using GROUP BY ALL).  But an OUTER JOIN is a different story.  If you moved that particular condition into the WHERE clause it would (among other things), in effect, negate the OUTER portion of your query.

I wrote an article with several examples here.

Just remember that the ON clause joins the two tables together.  The WHERE clause excludes rows from the output.  For your specific example lets say you have a row in TB_REQUEST_DATA without a match in TB_SPOT_REPORT_REQUEST.  With r.master_seq=206 in the ON clause of the  OUTER JOIN you will get a row returned with the value of TB_REQUEST_DATA but NULLs in the TB_SPOT_REPORT_REQUEST columns.  If on the other hand you put it in the WHERE clause it's going to eliminate the row entirely.

Let's say however that you want every row of TB_REQUEST_DATA but only the information in TB_SPOT_REPORT_REQUEST where master_seq = 206 AND SEQ = REQUEST_SEQ.  That's when you would put the condition in to the ON clause.


Generate DDL Statement from XSD file I have a database schema exported into XML format.

My goal is to create a DDL statement from the XSD files that I can use to import the data into a different platform without manually having to parse it myself.

There is very little information on XSD files in general and it seems that only Oracle have any support at all for them.

Sometimes you overlook the basic tools; Microsoft Access Imported the file straight up.

Opened a new Blank Database in Access

External Data -> XML File -> Select the file.

Then I pointed my Access database to an SQL Server backend and just used SSMS to generate the DDL Statement to make the schema portable.

Thanks for the help!


Hide databases from SQL Server with AG I have 10 databases on a server. Each database is part of a different AG. Each database has its own listener. Can I hide all databases and show only the one database belonging to a listener? I also do not want to create a separate user for each listener as it will be a lot of users to be created and maintained across many database servers.

Can I hide all databases and show only the one database belonging to a listener?

Yes.  Have each AG's databases owned by a separate login created specifically for that purpose.

  I also do not want to create a separate user for each listener

Then no. There's no way to trim the list of databases a user can see based on the endpoint used to connect to SQL Server.


What versions of SSMS have an Express edition? I know all the SQL Server versions from 2005 to 2014 have an express edition. This question is specifically about SSMS. There was an SSMS Express 2005, 2008, and 2008 R2, but I haven't found any later version.

The SSMSE was different in what it can manage as documented here.

Now you can use SSMS (SQL Server Management Studio) as a seperate download - SSMS or SQL server 2014 express - SSMSE that will manage all editions of sql server.

Aaron has a really good explanation here with screenshots :-)

  Express version of Management Studio, since 2012 Service Pack 1, has been a fully functional product with absolutely no differences


How I convert it to SQL Server please How can I create a SQL Server equivalent of this query?

SELECT m.ItemId
    ,item.NAME
    ,m.Expiratory
    ,m.t AS TotalNumber
FROM item
    ,(
        SELECT ItemId
            ,Expiratory
            ,sum(TotalNumber) t
        FROM inbox
        GROUP BY Expiratory
            ,ItemId
        ) m
WHERE (
        (
            m.ItemId
            ,m.Expiratory
            ) NOT IN (
            SELECT ItemId
                ,Expiratory
            FROM outbox
            GROUP BY Expiratory
                ,ItemId
            )
        )
    AND (item.ItemID = m.ItemId)

SELECT
    inbox.ItemId,
    item.NAME,
    inbox.Expiratory,
    t = SUM(inbox.TotalNumber)
FROM dbo.Item
INNER JOIN dbo.inbox
ON Item.ItemId = inbox.ItemId
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.Outbox
    WHERE Item = inbox.Item
    AND Expiratory = inbox.Expiratory
)
GROUP BY inbox.ItemId, item.NAME, inbox.Expiratory;


Element of an array in a daterange? I have an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.

For example (pseudocode - doesn't work, wrapped for readability):

SELECT ARRAY['2017-01-01'::DATE, '2017-02-03'::DATE] &&
    daterange('2017-01-01', '2017-01-31', '[]');

What this example is trying to show (although it doesn't work), is whether there's an overlap between values between my array of dates and the daterange.

Ideally I could do it with sets, and work out if there's a union between the two, but it doesn't seem possible to cast daterange objects to anything.

I did manage to get this working, but I can't work out how to subsequently reduce the rows:

SELECT daterange('2017-01-01', '2017-01-31', '[]') @>
    unnest(ARRAY ['2017-01-01' :: DATE, '2016-07-30' :: DATE]);

This returns me multiple rows:

?column?
----------
 t
 f
(2 rows)

Does anyone have any pointers for this? Any help is appreciated.

I've got an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.

Depending on the query, you can use

row aggregation with bool_or mentioned by joanolo in his answer.
or, you can use ANY with the contains operator @>

SELECT daterange('2017-01-01', '2017-01-31', '[]')  @>
  ANY(ARRAY['2017-01-01'::date, '2017-02-03'::date]);

SELECT daterange('2017-01-01', '2017-01-31', '[]')  @>
  ANY(ARRAY['2016-01-02'::date, '2016-01-09'::date]);


In a mirror, how can I keep the SQL Server Agent offline, but have it start automatically in the event of a failover on the Principal? In this scenario there are three MS SQL servers. ServerA has a series of jobs that write data to databases on ServerB. I want to set up ServerC as a mirror of ServerA, with all the same jobs available. I want to keep the SQL Server Agent down on ServerC until such a time as ServerA fails over, at which time I would like the SQL Server Agent on ServerC to automatically start up and continue to service those same jobs writing to ServerB until we get ServerA back online.
Can this be accomplished? And what is the best way to go about it?

You can keep the Agent enabled but disable the individual jobs on ServerB and create a dedicated job on ServerB that continuously runs (or schedule it to run every X amount of time) and checks if the current server is the Primary. If it is, then enable the other jobs with sp_update_job.
You can also check which jobs are enabled with msdb.dbo.sysjobs so your dedicated job only tries to enable them once.
Using this same methodology you could have the dedicated job disable itself and have a secondary job check for when the current server becomes a Secondary Replica so that it can re-enable the dedicated job, or you can just let the dedicated job continually check but do nothing when the current server is the Primary and it already enabled all the jobs for the first time.


Does it make sense to run a cluster of database servers in docker containers? I'm currently looking into setting up a database cluster (MySQL Galera or Percona) but I have only 2 machines.

However, according to many sources I've read (This article from the Mariadb KB), the minimal node size in the cluster should be 3, to avoid split brain conditions.

  In order to avoid a split-brain condition, the minimum recommended number of nodes in a cluster is 3. Blocking state transfer is yet another reason to require a minimum of 3 nodes in order to enjoy service availability in case one of the members fails and needs to be restarted. While two of the members will be engaged in state transfer, the remaining member(s) will be able to keep on serving client requests.

So I'm wondering if I can run maybe 3 (or more) docker containers, each running a database server, on both machines. If one of the machine fails, there are still 3 nodes in the cluster, so no split brain problems.

It feels like cheating, but I cannot think of a reason not to do it.

Does this setup make sense ?

Galera (for MySQL, Percona XtraDB Cluster or MariaDB Cluster, they are essentially the same but from different vendors and base mysql version) can work perfectly with 2 machines, and it is a very common setup for substituting standard MySQL replication.

Requiring 3 nodes is not a requirement of Galera, but of any cluster that cares about data consistency over availability. In other words, it is needed in order to provide availability and data integrity in the case of only one node suffering from network partition (causing a "split brain" on the cluster). We need an odd number of nodes and 1 node is not a cluster :-). You probably knew that, but I wanted to clarify this for any people reading this answer.

Given that the only reason to have 3 nodes is for availability in case of a node failure/network failure, the recommended way to setup a 2 node cluster with network split protection is to setup a Galera Arbitrator which is essentially an emulation of a MySQL node, but without storing any local data. You can install that anywhere, although you must be careful because if still may influence the performance of the cluster if it has network latency problems.

Running several instances on the same physical node is useless (docker is not needed, by the way, you can do that by just changing the local configuration), as if you absolutely can only run things on 2 machines, you better disable the automatic shutdown on one of the nodes and manage the failover manually. The 3 limitation is just for data consistency, there is nothing physical or on the protocol requiring a certain number of nodes.

You can read more information about it on the official documentation.


sql server active active configuration with same database names I have a SQL Server 2008 Enterprice in a clustered environment and in active-active mode.

By active-active mode i mean that both instances are active and serving requests to their databases.

Instance 1 has databases Db_A and Db_B and instance 2 has databases Db_C and Db_D.

I would now like to create a new database called Db_E on each instance. They will have different content and structure, but will have the same name.

I tried it and it's possible but i'm not sure what will happen in the case of failover when i shut down Instance 1?

I guess it should be okay, but i'd like a confirmation.

You have two different clusters sharing the nodes, both clusters in having one active and one passive node. The names and resources on each cluster are completely independent from the names and resources on the other cluster. So you can safely add Dnb_E and each cluster. On failover the node running now the active node of both clusters will start OK, since the Db_E on the cluster A is different from Db_E on cluster B. Notice that I intentionally avoided using the name 'instance'.

Using the misleading 'active-active' nomenclature just leads down the thinking that you somehow share resources between the two clusters. Calling this configuration 'active-active' is strongly discouraged.


How to model a relationship with different roles of users I am creating a new project and currently modeling the relevant business domain. I am hesitating to choose one solution over another.

Consider the following case.

There is an order, the order has a customer who made it, an order will have an assigned manager after some state changes and next order will be assigned to a delivery guy.

I don't know how to model this relationship, not strictly in SQL, but in general from the business domain perspective.

I have tried to trace these changes, however figured out that any redux dispatch action causes this issue.

We can have "fields" customer_id, manager_id, courier_id in the order "model" (table).
Use pivot tables with additional information of relationship type

order_id = 31
user_id = 22
rel_type  = "customer"

This will be quite tricky to query such a relationship.

Have multiple "pivot" tables like customer_order, manager_order, courier_order.

Please suggest the best option to choose for such a business domain.

Thanks.

Option 3 is considered the best practice in this case because it lets the RDBMS do what it is designed to do. You have the best options for controlling relationships with declarative referential integrity and your queries will be simple and direct.  If you specifically want to make this option work like Option 1, insofar as the relationships are constrained to many to one (many orders to one customer each for example) then add a unique constraint on the order_id foreign key column in each intersection table. However, in practical terms, you might actually want many-to-many because you might assign different managers or couriers over time and that history may be important. It all depends on your business rules.

Option 2 is considered a "SQL Code Smell" (polymorphic association)

Option 1 might be reasonable as well, depending on your business rules, but some people don't like it because it might result in "sparsely populated columns" (again, depending on your business rules).


Do MySQL (5.6) triggers support 4 byte emojis? I'm using MySQL 5.6.34 with the following settings:

"character_set_client","utf8mb4"
"character_set_connection","utf8mb4"
"character_set_database","utf8mb4"
"character_set_filesystem","utf8mb4"
"character_set_results","utf8mb4"
"character_set_server","utf8mb4"
"character_set_system","utf8"
"collation_connection","utf8mb4_general_ci"
"collation_database","utf8mb4_unicode_ci"
"collation_server","utf8mb4_unicode_ci"

3 byte emojis seem to work correctly within a trigger but 4 byte do not. They just become a '?'. Is there a different setting that should be used or is this just not supported within a trigger?

As in the github issue was recommended to change the settings on the following:

connection_charset: utf8mb4
connection_collation: utf8mb4_unicode_ci
charset: utf8mb4
collation: utf8mb4_unicode_ci

I suppose you could change

"collation_connection","utf8mb4_general_ci"

on

"collation_connection","utf8mb4_unicode_ci"


Not getting the expected result with 'IN' and subquery I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the result of id 1 i didn't get the result of id 3.

SELECT count( id ) AS totalDestination, group_concat( ring_to_number ) AS phoneNumbers
    FROM tbl_destinations
    WHERE id
    IN (

    SELECT destinations
    FROM tbl_rings_to_groups
    WHERE id = '1'
    AND user_id = '1'
    )

Can anyone please tell me why i didn't get the destinations=>1,3 in SubQuery?

tbl_rings_to_groups:

tbl_destinations:

You don't get results because the data type of the two tables' id are different and by that, cannot be compared (as 1 != '1'). Try this instead:

DECLARE @destlist varchar(max)
SET @destlist = SELECT destinations FROM tbl_rings_to_groups WHERE id = '1' AND user_id = '1'

SELECT count( id ) AS totalDestination, group_concat( ring_to_number ) AS phoneNumbers
FROM tbl_destinations
WHERE id IN (CONCAT('SELECT ', REPLACE(@destlist), ',', ' UNION ALL SELECT '))


mysql start and stop error I install a mysql 4.1(Ok it's very old version) on debian.
the service start and stop correctly but show this error.

    root@VM03:~# /etc/init.d/mysql start
/etc/init.d/mysql: 63: /etc/init.d/mysql: source: not found
Starting MySQL
/etc/init.d/mysql: 117: /etc/init.d/mysql: log_success_msg: not found

and this

root@VM03:~# /etc/init.d/mysql stop
/etc/init.d/mysql: 63: /etc/init.d/mysql: source: not found
Shutting down MySQL
../etc/init.d/mysql: 117: /etc/init.d/mysql: log_success_msg: not found

i use the mysql.server default.

Someone to help.
Best,
JGois

These are clearly bugs in particular 4.1 version , refer these bug reports:

http://bugs.mysql.com/bug.php?id=24294

http://bugs.mysql.com/bug.php?id=9852


Mysql Internals on restart We have a huge database and many procedures loaded onto it to cater reports and some of them are called every 5 min throughout the day.

Database is performing pretty well otherwise, but if I do not restart mysql once daily, queries which used to take few milliseconds to execute takes minutes and even hours to finish.
Even mysqldump (selective dumping of data based on timestamp columns) which normally completes in 15 minutes is taking 3-4 hours to complete if mysql restart is not done on daily basis.

Can anyone please help me understand what does mysql do internally on restart.
We use lots of temporary tables in our procedures. Could these be causing contention?

I am quoting from the official MySQL 5.7 documentation in my answer. This question has an evolving state and (hopefully) will contain as much information as possible.

mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.
When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients.

The mysqld program has a lot of options that can be specified at startup. A complete list of options can be retrieved when you run this command:

shell> mysqld --verbose --help

The MySQL documentation recommends that:

When troubleshooting, it is usually best to run the MySQL server from the command prompt, rather than through mysqld_safe or as a Windows service

If you want to see what the mysqld service starts or touches during the start-up procedure, you can start mysqld directly at the command prompt with:
shell> mysqld

You might find information during the startup procedure, which are causing your performance issues.
If you are using the default innoDB configuration you will make use of the InnoDB Temporary Table Undo Logs which are part of the Temporary Tablespace which is initialised during the start-up process:

The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs.

Temporary table undo logs have restrictions:

Temporary table undo logs, introduced in MySQL 5.7.2, are used for temporary tables and related objects. This type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. Temporary table undo logs are, however, used for rollback while the server is running. This special type of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and related objects. Temporary table undo logs reside in the temporary tablespace. The default temporary tablespace file, ibtmp1, is located in the data directory by default and is always recreated on server startup. A user defined location for the temporary tablespace file can be specified by setting innodb_temp_data_file_path.
32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects, which means that the maximum number of rollback segments available for data-modifying transactions that generate undo records is 96. With 96 available rollback segments, the limit on concurrent data-modifying transactions is 96K. For more information see Section 15.3, “InnoDB Multi-Versioning” and Section 15.8.8, “Limits on InnoDB Tables”.

You might be hitting an issue where the number of transactions creating temporary objects is above this 96K level of connections. Or you might be just generating too many temporary objects.
Restarting your MySQL instance will (according to the Temporary Tablespace documentation) clean up your temporary tablespace and all temporary tables locate therein:
The temporary tablespace is removed on normal shutdown or on an aborted initialization
Given the information provided, I would suggest you to check your temporary objects, because you might have a problem in your temporary tablespace.


Oracle supported linux distributions According to this site (http://www.dbspecialists.com/oracle11glinux.html), Oracle is only supported on the following Linux distributions:

Oracle Enterprise Linux
Red Hat
SUSE
Asianux

Is there any technical reason that Oracle would perform poorly on a Debian-based install (Ubuntu)?  Or is this more about dealing with Oracle from an enterprise support standpoint?

There is no reason why Oracle would perform badly on Ubuntu (though Oracles OEL kernel has some Oracle-specific optimisations), it's all about enterprise support.

Certification is an expensive and time-consuming task, as is training support workers in each flavour of Linux - that's why the RDBMS is only officially supported on a select number of releases.


Read after transaction from secondary session not visible We've got a database (MySQL ) that has a client 'per channel'.
When a player changes channel from, say, 1 to 2, we have to save the state of the player to the database.

Saving is done in multiple transactions (per table), which include in most cases a DELETE FROM table WHERE player = X; INSERT INTO table VALUES (...);. Some other tables require only an UPDATE statement.

The problem is as follows:

Player starts moving from channel 1 to channel 2
Channel 1 registers transition in Redis, making sure that player is bounced on Channel 2 until player is saved.
Player disconnects from channel 1
Player connects to channel 2, gets asked to reconnect (bounce)
Channel 1 received disconnection, saves character, disables bouncing
Player connects to channel 2, gets accepted
Channel 2 loads info from database --THIS IS OUTDATED INFO--

I'm about to, instead of deleting the Redis key, to change the TTL of the key to a couple seconds to prevent it from loading old info. But it doesn't guarantee anything, as the database is a system on its own...

For the record, there's a MariaDB instance that acts as a slave for backup purposes.

If you can install Mysql UDF Plugin, you can using lib_mysqludf_redis plugin to synchronous records between Mysql and Redis by Mysql trigger.

Here is a example:

CREATE TABLE `player_channel_log` (
  id       int         AUTO_INCREMENT PRIMARY KEY,
  date     datetime,
  player   int,
  channel  varchar(32)
);

set up a trigger for the table and call redis command by mysql udf

DELIMITER $$
CREATE TRIGGER `after_insert_player_channel_log`
AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
  DO `redis`('redis://@127.0.0.1/0/', 'SET', new.`player`, new.`channel`);
END $$
DELIMITER ;

then you can get value by player id

from mysql:

mysql>  SELECT `redis`('redis://@127.0.0.1/0/', 'GET', <player id>)\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/0/', 'GET', <player id>): {
        "out":  <channel>
}

from redis-cli utility:

$ redis-cli -h 127.0.0.1 -n 0 GET <player id>
"<channel>"


What does Oracle impdp do when a name clashes with a materialized view or synonym? I have a production Oracle database, and I want to export tables, data, views, materialized views, synonyms, etc, to import into a development Oracle database. Actually, this is part of my job, and we have a script in place for doing this, which calls expdp and impdp. However, I'm experiencing some issues now, because there have been a lot of recent structural changes to the production database that seem to be causing problems in conjunction with the parfile being passed to impdp. I should say that I'm quite new to expdp and impdp.
I understand about TABLE_EXISTS_ACTION and the 4 options SKIP | APPEND | TRUNCATE | REPLACE, but how can I deal with a situation where a table name exported from the source database clashes with a materialized view name in the destination database, or vice versa? What about when the name belongs to a table in one database and a synonym in the other - what does impdp do in that case, or how can/should I deal with that? Really I don't care about any existing tables, views, materialized views or synonyms in the development database; if impdp tries to import some object that was exported from the production database, I just want it to be created, and any object whose name clashes can just be dropped basically.
Also, I don't fully understand what actually happens when you export/import a materialized view from one database to another. A materialized view has a definition - so, is the actual data in the source materialized view exported, or just the definition, and the destination materialized view is refreshed according to the definition?

Naming conflicts will result in an error and generally cause the object in the datapump file to be skipped. This can lead to all sorts of other dependency issues. The simplest way to avoid that is to drop the target application schema/account entirely and recreate it, using a script like the one here: https://oracle-base.com/dba/script?category=script_creation&file=user_ddl.sql to construct the right user DDL for the user in the target database, or by including the application schema definition in the datapump file and letting datapump create it for you. So...
Step 1: run the script to get the create user DDL.
Step 2: drop the user using the cascade option to drop all objects.
Step 3: run the user DDL script from step 1 to recreate the schema, assuming the password is different or there are any other differences between production and development that you need to preserve.
Step 4: run impdp to load all of the schema objects. If datapump tries to create the schema and it already exists it will throw an error, but you can ignore it.
For your second question, materialized views have two parts: a table with data, and the materialized view definition, which includes the query. Both - the table with data and the definition of the MV - are replicated with datapump when you copy a schema.


Converting PostgreSQL table to TimescaleDB hypertable I have a PostgreSQL table  which I am trying to convert to a TimescaleDB hypertable.
The table looks as follows:
CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

When I attempt to convert this table to a TimescaleDB hypertable using the following command:
SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

I get the Error: ERROR:  cannot create a unique index without the column "event_time" (used in partitioning)
Question 1: From this post https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi my understanding is that this is because I have specified a unique constraint (con1) which does not contain the column I am partitioning by - event_time. Is that correct?
Question 2: How should I change my table or hypertable to be able to convert this? I have added some data on how I plan to use the data and the structure of the data bellow.
Data Properties and usage:

There can be multiple entries with the same event_time - those entries would have entry_id's which are in sequence

This means that if I have 2 entries (event_time 2021-05-18::10:16, id 105, <some_data>) and (event_time 2021-05-18::10:16, id 107, <some_data>) then the entry with id 106 would also have event_time 2021-05-18::10:16

The entry_id is not generated by me and I use the unique constraint con1 to ensure that I am not inserting duplicate data
I will query the data mainly on event_time e.g. to create plots and perform other analysis
At this point the database contains around 4.6 Billion rows but should contain many more soon
I would like to take advantage of TimescaleDB's speed and good compression
I don't care too much about insert performance

Solutions I have been considering:

Pack all the events which have the same timestamp in to an array somehow and keep them in one row. I think this would have downsides on compression and provide less flexibility on querying the data. Also I would probably end up having to unpack the data on each query.
Remove the unique constraint con1 - then how do I ensure that I don't add the same row twice?
Expand unique constraint con1 to include event_time - would that not somehow decrease performance while at the same time open up for the error where I accidentally insert 2 rows with entry_id and pair_id but different event_time? (I doubt this is a likely thing to happen though)

A couple questions as well as answers here:

it's the unique constraint (con1) that's the problem not the foreign key constraint but that was probably a typo.
Yes, it's a problem because the time partitioning column isn't included, the first question I have is, the entry_id seems like it might be a proxy for time from the other system, can you just use pair_id, time as your unique constraint? Will there be multiple records with different entry_ids same pair_ids and same times? You could probably run a query with a count(*) grouped by pair_id, time to figure this out... Note that a three column unique constraint could work, it's a question of how the time is assigned, is it assigned by you at write time or by the upstream system? If it's the upstream system then I wouldn't worry too much about duplicates.
the array thing is somewhat similar to compression, but the compression does different, more efficient algorithms than simple array packing.
figuring out a unique constraint that works, either on pair_id, time or pair_id, entry_id, time will probably be much more efficient than trying to reduce your data to the unique set at query time. And a three column unique constraint isn't going to have a huge impact on performance. The ordering of those columns should be chosen based on query patterns, what sorts of where clauses do you usually have? ie on pair id or something like that as well as time? or just by time?
finally, given the size of your table, I'd highly recommend creating the hypertable without migrating data, so create a separate table, make it a hypertable, then make a small job to migrate the data. The migrate_data = true is really meant for much smaller tables, having an outside job do it means you could also introduce some parallelism which will make it go much faster.


Postgres - Vacuum doesn't complete on large/busy table We have a decently active PG database hosted on AWS. We recently started getting notifications like the following:

 Transaction ID age reached 750 million. Autovacuum parameter values for [autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay, autovacuum_naptime] are updated to make autovacuum more aggressive.

I also noticed the disk usage for this particular table was increasing fast. Here's the used space:

[
  {
    "oid": "16413",
    "table_schema": "public",
    "table_name": "connections",
    "row_estimate": 1.01476e+07,
    "total_bytes": 518641270784,
    "index_bytes": 478458511360,
    "toast_bytes": 30646272,
    "table_bytes": 40152113152,
    "total": "483 GB",
    "index": "446 GB",
    "toast": "29 MB",
    "table": "37 GB"
  }
]

Then we were doing some analysis on something else and noticed a long-running (5 days ago) vacuum process:

[
  {
    "pid": 14747,
    "duration": "14:11:41.259451",
    "query": "autovacuum: VACUUM ANALYZE public.connections (to prevent wraparound)",
    "state": "active"
  }
]

(This was actually a new one, but looked identical to this as the last one never finished).

To confirm, i see that the connections table hasn't been autovacuumed since the 15th and has a lot of things to cleanup:

[
  {
    "relid": "16413",
    "schemaname": "public",
    "relname": "connections",
    "seq_scan": 19951154,
    "seq_tup_read": 226032655046,
    "idx_scan": 41705151351,
    "idx_tup_fetch": 375484186787,
    "n_tup_ins": 8029742,
    "n_tup_upd": 13217694302,
    "n_tup_del": 542670,
    "n_tup_hot_upd": 96750657,
    "n_live_tup": 10237553,
    "n_dead_tup": 887751401,
    "n_mod_since_analyze": 350036721,
    "last_vacuum": null,
    "last_autovacuum": "2019-06-15 17:05:51.526792+00",
    "last_analyze": null,
    "last_autoanalyze": "2019-06-15 17:06:27.310486+00",
    "vacuum_count": 0,
    "autovacuum_count": 4190,
    "analyze_count": 0,
    "autoanalyze_count": 4165
  }
]

I've read a bunch about configuring autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor differently for very active tables. And that's all good, but it doesn't appear it's going to get through this one right now that's running.

I've also read about optimizations to autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay to let it be more aggressive in the work it needs to do.

I've tried to change a few of those for the table, but it just sits there when I try to write any values for that particular table.

What's the best way to get the table vacuumed?

Also, would effect would a reboot of the database have on all this?

You don't show your settings for "autovacuum_work_mem" and "maintenance_work_mem".  The default settings in 9.4 are very low (64MB, which only allows 11M tuples to be vacuumed per pass over the index), unless RDS (or you) changed them.  You need to set these to the highest value you can given the amount of RAM you have.

"index": "446 GB",
"toast": "29 MB",
"table": "37 GB"

Having the index be 12 times bigger than the table seems demented (Edit: or it would be if there was only index--I didn't think about having many indexes).  Is this a normal btree index, or is it pg_trgm or something?  Is the index corrupted somehow?  Do you know how you got into this situation?

Space in indexes is harder to reuse than space in tables. A given leaf page can only be used for new tuples if the new tuples are near the same range of values as the tuples it already holds, or if the page is completely empty.  So if the key space is alway moving in one direction (like a sequence), and almost-but-not-quite-all of the old tuples eventually get deleted, then you could be left with a bunch of pages which only hold one tuple each, and can't be reused.  Tables don't have this problem as mostly-empty pages can be used to hold any tuple that comes along.  Or, if your table was horribly bloated at some point but then got cleaned up, it is possible the table got shrunk down but the indexes did not.  (Tables can shrink if they happen to have a big chunk of completely empty pages at the end of the table).  It can be hard to tell which of these things is the one that happened.  You can use pg_freespacemap on the indexes to see how many pages are completely empty, but that probably won't be accurate until the vacuum finishes.

The fastest way out of this, although with some down time, might be to start a VACUUM FULL of the table--you don't want to spend a lot of time vacuuming a hopelessly bloated index when you could just throw it away and rebuild it in a non-bloated state. The VACUUM FULL will block, then in another session, kill the autovacuum worker (which holds a lock blocking the VACUUM FULL). You want to have the other command already waiting when you kill the autovac worker, because it will be restarted quickly and so take the lock again, unless something else is already there waiting to grab it.

  I've tried to change a few of those for the table, but it just sits there when I try to write any values for that particular table.

Changing those table-specific settings requires a lock on the table.  The autovac worker is blocking that lock.  Normally an autovac will detect when it is blocking something else and surrender the lock, but (to prevent wraparound) does not do that.  So you would need to kill the autovac worker to be able to make this change (which is a general theme of everything here).

I wouldn't recommend changing that table-specific setting anyway. If you want this to be a one-time thing, just run a manual VACUUM or VACUUM FULL to accomplish that.  If you want it to be permanent, it is hard to justify why this one table needs a different "autovacuum_vacuum_cost_delay" than your other tables, at least based on the info given, so just change it at the system level.  If you change it at the system level, it still won't take effect in the middle of the ongoing autovac, you would need to kill it so the next one picks up the change.

  Also, would effect would a reboot of the database have on all this?

A reboot would cause the autovac to lose part of the work it has already done, and start over again.  It wouldn't accomplish anything, unless you also made meaningful configuration changes which will take effect after the reboot.