texts:
I am distributing several instances of mongoDB between two datacenters (DC). I want to have high availability (HA), which means that three instances must always be active if one of DC goes down.

My decision has been:

DC1:

Primary
Secondary
Arbiter

DC2:

Secondary
Hiden
Arbiter

Can I choose 2 arbiters for HA? Or it is a mistake?


texts:
Sorry for the wonky title if it got you confused.

I want to know if a server can be a slave and master at the same time. Our problem is that we have lots of mobile units that need to be synced to the master but they only need 6 out of the 100s of tables on the master. All the extra tables serve no purpose on the slave except for delaying synchronization and adding data costs.

Desired Configuration:

Server A: Master to Server B.

Server B: Slave to Server A, Master to Server C.

Server C(n): Slave to Server B.

OR

Server A: masterSchema, slaveSchema.

slaveSchema is a sync'd subset of masterSchema.

Server B(n): Slave to Server A.slaveSchema

If the answer is no would anyone have any alternate solutions to propose. We're trying to avoid having to sync the different schemas/databases manually as that can get real ugly real fast. We have a lot of critical live data flying around so both performance and integrity are of concern.


texts: Optimize query execution based on two datetime columns

texts:
I've been struggling all day long against this.

We have a very busy database (still on development) and we have records being inserted very frequently. The record logs have a start time and a end time.

If I want to select something between col1(datetime) and col2(datetime) MySQL can't use indexes properly because it will search the index for col1 but will never look into col2.
The database engine is InnoDB. What happens is for example that MySQL will search 80 thousand rows when the interval requested should only return two rows.

My biggest problem is that I'm trying to do some aggregate functions on this time ranges and its taking a very long time when it should be really fast considering how many rows it is actually counting.

Also note that I can't do dateStart between col1 and col2 nor dateEnd between col1 and col2 because dateStart can be lower than col1 and dateEnd can also be lower than col2.

Lets assume this sample data:

     col1      |     col2
---------------+---------------
date 10:20:00  |date 10:21:00
date 10:21:00  |date 10:22:00
date 10:22:00  |date 10:23:00
date 10:23:00  |date 10:24:00
date 10:24:00  |date 10:25:00
date 10:25:00  |date 10:26:00
date 10:26:00  |date 10:27:00

If I need the rows that range between 10:21:30 and 10:25:30 I need to do something like this:

'10:25:30' <= col1 AND '10:21:30' >= col2.

So how do I index these columns properly? MySQL only picks up one of the date columns in the index.


texts: How to get list of tables, views etc. a users has rights to on Sybase IQ?

texts:
I'm looking for a way to get a list of views, tables, procesdures etc, a user has been granted to. A select onto SYSROLEGRANTS helped me to find basic role of users -- but not more like this.

So e.g. Having this:

create or replace view myFancyView as
SELECT * from mytable;

GRANT SELECT ON myFancyView TO myUser;

I'm hoping for a list like that:

|User  | Right                |
|------|----------------------|
|MyUser| SELECT ON myFancyView|


texts: mongodb wt file corrupt issue, can not query few documents

texts:
I have a mongodb cluster with 3 shards and no replication
The problem is one of the shards has a bad disk and because of which some files show input/output error when trying to read

[root@mongodb2 b0]# md5sum collection-700-3655945817504191327.wt
md5sum: collection-700-3655945817504191327.wt: Input/output error

If I try to use wt to salvage the file that too fails

I know exactly which documents are corrupt , but I am helpless because I can not delete or update these documents. Any find() or remove() command on these documents causes entire mongo shard to get killed.

How can I recover this data


texts: How do I import a dump for a deleted tablespace?

texts:
I'm trying to import a database dump, however I am encountering this error:

ORA-39083: Object type TABLE:"MYSCHEMA"."MINF" failed to create with error:
ORA-00959: tablespace '_$deleted$5$0' does not exist
Failing sql is:
CREATE TABLE "MYSCHEMA"."MINF" (
    "P_MID" VARCHAR2(16 BYTE) NOT NULL ENABLE
    , "XML_ORIG_MSG" "SYS"."XMLTYPE"
    , "XML_MSG" "SYS"."XMLTYPE"
    , "P_OFFICE" CHAR(3 BYTE) NOT NULL ENABLE
    , "P_DEPARTMENT" CHAR(3 BYTE) DEFAULT '...'
    , "P_MSG_TYPE" VARCHAR2(15 BYTE) DEFAULT '...'
    , "P_MSG_SUB_TYPE" VARCHAR2(10 BYTE)
    , "P_CDT_MOP" VARCHAR2(6 BYTE)
    , "P_PREFERRED_CDT_MOP" VARCHAR2(6

It's seems the tablespace for the MINF table has been dropped before getting the dump.

Is there a workaround to still import this table?

Update:

I have tried impdp statement, with the same result:

./impdp system/******
DIRECTORY=GPPDUMP
DUMPFILE=SPOCBC_SCD02.ROCKY.20120919102739.dmp.1
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.2
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.3
,SPOCBC_SCD02.ROCKY.20120919102739.dmp.4
LOGFILE=baru.log
REMAP_SCHEMA=SPOCBC_SCD02:OCBCGPP5
REMAP_TABLESPACE=_$deleted$5$0:P_DATA


texts:
I was navigated over here from Stack Overflow, hopefully you can help me out.

So I've got a dilemma. Our company has bought a volume license for Microsoft SQL Server 2012 Standard Edition. We purchased the per core license scheme. All of that didn't have any issues; we even installed it without any problems.

The issue is coming from Microsoft though. When I configured our Volume License Account they said:

  We will issue a special installation file that will not require a key for usage. In order to install this to the proper processor
  allocation you purchased you'll need to install the file that many
  times.

So from what I've gathered, a Microsoft SQL Server 2012 Standard Edition Per-Core License by default is configured as a dual-core. So I understand if I have a quad-core I have to purchase it twice. What I don't understand, why would I need to install it two times?

I can't find any documentation on this.

That process doesn't seem correct, is there a way to test how many cores SQL is running on? Any assistance would be terrific-

Things I've Tried:

SQL Installation Guide
SQL Getting Started
Calling Microsoft

Some how I feel this issue shouldn't be nearly as complicated as it has become.

Those haven't let me come any closer to my goal, I get the notion that I should only have to install it once. But with what that Microsoft Licensing Technician said I suddenly have a cloud of doubts over my head.

Thanks again.

Update:

So within our Microsoft Volume License Account this is all it provides with our download:

Which I feel is adding to this confusion.  We have it installed on the Server; but I'm not entirely sure if I can check to see that it is running all cores.  The receipt shows we've purchased multiple licenses-

If I understand the answer posted; it should be built into our license file from the installation.  Is there a way to check that?


texts:
I'm using MySQL Workbench 6.1.6, and I have opened a SQL Editor Window and have 14 Query tabs open. If I close the connection, and then open the connection again from the Home page it reopens all 14 tabs. That's ok.

However, if I close the tabs, then close the connection, when I re-open the connection the tabs re-appear. I have tried closing the tabs and exiting workbench. I have tried closing the tabs and opening new ones, but each time the original 14 tabs re-appear.

Does anybody know of what I can do to get rid of these 14 tabs once and for all?


texts:
We have an evil query that reads every row from a table, but should not.  To help the devs find the source of that query, I want to make SQL Server error out when any select against that table has no where clause.

An app is doing an evil select with no where.  The select reads every row of a table having about 6,500 rows.  The select is fast in SSMS (< 1 second), but when run by the app it's slow (about 1/2 hour).  We think that's because the app is slowly iterating over the result set.  It's an active table for updates and inserts, so other queries may be blocked while this read takes place.
The developers can think of no reason that any app should read every row of that table.  They suspect an oversight by a past developer.
The evil query-with-no-where is my top-runner, by far, for having the longest total duration in Query Store.
I know which app is issuing the evil query by using SQL Profiler to identify the app name, host name, and DB user name.  The app's developers have been unable to locate the source of the evil query.  I want to help.
The app is in C# and uses LINQ.  The evil query is exactly the same every time, with the same columns in the same order.  Simple equality could be used to determine whether the query being issued is the evil one.  The query is issued in its own batch without any other queries.
I want to cause any select against that table that has no WHERE clause (i.e. selects every row) to fail immediately.  Our hope is that the app will log the errors, or possibly an end user that triggered the activity will report a 500 error to us, and this will give us another clue to find what code is issuing the evil query-with-no-where.
Our other selects against that table have specific where clauses and only return a subset of the table's rows.  Those selects must be allowed to continue.
The query
SELECT
  [a].[Id],
  [a].[AccountingIntegrationActionTypeId],
  [a].[CreateDate],
  [a].[CreatedBy],
  [a].[FranchiseId],
  [a].[Message],
  [a].[ModelObject],
  [a].[ProcessId],
  [a].[RequestedBy],
  [a].[RequestedFor],
  [a].[Resolved],
  [a].[UpdateBy],
  [a].[UpdateDate]
FROM
  [AccountingIntegrationQueue] AS [a]


texts: Stored Procedure Create Table from Variable with Variable Constraint

texts:
I have managed use a stored procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure.

The problem: the constraint must be a variable as when it makes a copy of a table it cannot name the PK that I have with the same as one used before. I am getting syntax errors in these areas.

I am very new to SQL Server... literally just started learning today!! So please explain in dummy terms.

Code so far below:

    CREATE PROCEDURE procFinancialPeriodTable
        (@TABLENAME as varchar (50))
    AS
        DECLARE @SQL varchar(2000)
        --SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        --drop table [dbo].[@TABLENAME]
    BEGIN
        SELECT @SQL = 'CREATE TABLE ' + @TABLENAME + '('

        SELECT @SQL = @SQL + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,[FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,[FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,[PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,)'
    CONSTRAINT [PK_FinancialPeriod' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
    [FinPeriodNr] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,                 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    EXEC(@SQL)

    SET NOCOUNT ON;
END


texts: Compress files and directories into zip file and put it into blob field

texts:
What is the best way to do the following using SQL Server?

Compress all the files, folders and subfolders from a specific location (specified in a parameter in a stored procedure) - the server side
In the blob return the zipped file as bulkcolumn - client side

Is it even possible?


texts: How can I fix "Cannot perform a differential backup ... a current database backup doesn't exist"?

texts:
We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.

The problem is, the differential backups don't always seem to work. The error message recorded in the job history is

  Executed as user: username. ... 9.00.3042.00 for 32-bit
  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
  Started:  11:20:12 AM  Progress: 2013-01-25 11:20:13.90     Source:
  {450389BA-54C2-4892-9CD0-0126CA9B0ED8}      Executing query "DECLARE
  @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End
  Progress  Error: 2013-01-25 11:20:14.40     Code: 0xC002F210
  Source: Back Up Database (Differential) Execute SQL Task
  Description: Executing the query "BACKUP DATABASE [MyDatabase] TO
  DISK = N'E:\Database Backups\MyDatabase_backup_201301251120.diff'
  WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME =
  N'MyDatabase_backup_20130125112014', SKIP, REWIND, NOUNLOAD,  STATS =
  10  " failed with the following error: "Cannot perform a differential
  backup for database "MyDatabase", because a current database backup
  does not exist. Perform a full database backup by reissuing BACKUP
  DATABASE, omitting the WITH DIFFERENTIAL option.   BA...  The package
  execution fa...  The step failed.

The full backup job completes successfully every time, and I can view it in msdb.dbo.backupset using the query found here, so I know it exists. And it looks like after a reboot, the differential back up does complete successfully up until the next full backup.

Here's a look at the job history I have:

1/16 - Full Backup - Success
1/17 - Differential Backup - Success
1/18 - Differential Backup - Success
1/19 - Differential Backup - Success
1/20 - Full Backup - Success
1/21 - Differential Backup - Failure
1/21 - Differential Backup - Failure
1/22 - Full Backup - Success
1/22 - Differential Backup - Failure
1/22 - Reboot
1/23 - Differential Backup - Success
1/23 - Reboot
1/24 - Differential Backup - Success
1/25 - Full Backup - Success
1/25 - Differential Backup - Failure

The reboots were due to an unrelated error where @@SERVERNAME was null, so I held off posting this question until I found out if fixing the @@SERVERNAME fixed the problem, but it didn't and now I have no idea where to start.

What can cause SQL Server not to recognize a full backup for a database, and how can I fix it?

Also I'm not sure if this is related, but the differential backups seem rather large, and I am wondering if they're running the differential off the full database backup taken in the very beginning (on 1/16) instead of the most recent full backup. The full backups are about 260GB, and the differentials from 1/23-1/24 are at 30GB each.

The jobs were setup using the SQL Server Maintenance Plan Wizard, and I'm using SQL Server 2005

The [View T-SQL] button for the Maintenance Plan returns this code:

Full Backup:

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_backup_2013_01_25_140607_2543780.bak'
WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10,
    NAME = N'MyDatabase_backup_2013_01_25_140607_2523778'

Differential:

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_backup_2013_01_25_140537_4073936.diff'
WITH DIFFERENTIAL, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD,  STATS = 10,
    NAME = N'MyDatabase_backup_2013_01_25_140537_4053934'

This is very closely related to another question I have, however it was suggested in chat that I post a new question instead of editing the other one due to the questions being different and the existing answers on the other question.


texts: Where are some useful SQL puzzles to teach SQL in a workplace?

texts:
I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice.

I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there that you could suggest?


texts: Get size of a mongo database in a clustered enviroment

texts: As far as I can see there is a command to get the size of the database by using the db.stats() command. My question: is there a way to get the total size of all nodes in the cluster for a specific database?


texts:
I am currently maintaining an innodb cluster with three nodes. It runs well but sometimes one node will be MISSING then I have to bring it up online again.

The problem is I inserted a table without primary key. Then a node failed.
When I want to rejoin the failure node to cluster it says it can not join because there is a table without a primary key. I altered the table in cluster to give it primary key and the failure node still complains the same. So I deleted the table in failure node and expected it will rebuild the table.

Now it says

ERROR: Group Replication join failed.
ERROR: Error joining instance to cluster: '192.168.123.45@3306' - Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)

after I run cluster.checkInstanceState('root@192.168.123.45:3306');

The instance '192.168.123.45:3306' is invalid for the cluster.
The instance contains additional transactions in relation to the cluster.

{
    "reason": "diverged",
    "state": "error"
}

I know it is because the inconsistent state in different db. But I searched the internet but there's no documentation about how to resolve conflict in innodb cluster.
Any advice would be helpful!


texts: Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state

texts:
I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files available which were sent to me over the network.

When trying to restore from full backup, after some time (~1 hour maybe, the database is ~270 GB in size), I'm getting an error :

  System.Data.SqlClient.SqlError: An error occurred while processing the
  log for database 'database name'.  If possible, restore from backup.
  If a backup is not available, it might be necessary to rebuild the
  log. (Microsoft.SqlServer.SmoExtended)

After this, the db is in 'Restoring..' state.

I wanted to run something like (got it from this question)

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

against it, but naturally I can't as the database is in 'Restoring.." state.
Restarting the restore process on it leads to the same error message, dropping and restoring again didn't help too.

How do I get the db up and working? Transactional consistency doesn't matter to me.

The SSMS auto-generated restore script :

  USE [master]
  RESTORE DATABASE [database_name] FROM  DISK = N'D:\database_name.bak' WITH  FILE = 1,
  MOVE N'database_name' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name.mdf',
  MOVE N'database_name_index' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_index.ndf',
  MOVE N'database_name_log' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_log.ldf',
  NOUNLOAD,
  STATS = 5

  GO

The result of query suggested by @Craig Efrein

  The log cannot be rebuilt because there were open transactions/users
  when the database was shutdown, no checkpoint occurred to the
  database,  or the database was read-only. This error could occur if
  the transaction log  file was manually deleted or lost due to a
  hardware or environment failure.


texts: Query requires 'Update statistics' very often in SQL Server

texts:
We have a few tables in our database. Since most of our queries are date based, we have constructed clustered index on 'date + primary key (surrogate)' combination and we enforce joins on date as well in our queries. We have enabled 'page compression' on these tables/indexes.

Data is bulk inserted into these tables in the order of ~ 200k rows per day.

We have a query which joins a few of these tables and has date filter for two consecutive days.

Problem :
This query times out(no data returned even after minutes) for the latest dates for which data was bulk inserted, but works perfectly fine for the older dates. Further debugging revealed that the execution plan chosen by the optimizer changes (and is not optimal) when we pass latest two dates in the filter.

Currently, we are able to solve this issue by just running 'Update statistics' on these tables, post which optimizer chooses good old plan for the latest dates as well.

I noticed from the stats that, out of ~5 million rows, only ~300k rows are sampled for stats purpose.

Has anyone faced a similar problem of having to update statistics so often. Is there a way, I can fix this problem or this can be avoided?


texts: When should run Analyze table statement in mysql


texts: Does READ UNCOMMITTED make any performance difference on static data?

texts:
I have some static tables that gets a lot of select queries

I wonder if i add this to each query, does that make any performance difference?

SQL server 2014

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

here my wait times after i have executed the below query

    select *
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;


texts: LOAD_FILE() works on SELECT, but returns NULL on insert

texts:
I'm having troubles using the LOAD_FILE() function in my environment.

I'm trying to add a file to a BLOB field but whenever I try to insert or update this field with LOAD_FILE(), this field is being set as NULL.

I already checked all common problems which includes system variables, file size and max packet Size and permission problems (Both DB and Linux permissions), even some workaround that included moving the file to a Mysql Owned directory, the problem persists.

Do note that when using LOAD_FILE in a statement similar to

SELECT LOAD_FILE('/foo/bar.jpg');

does work and the file raw data gets displayed, either via CLI or using a DBA tool like DBeaver.

Dump of the table:

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string` varchar(3) NOT NULL,
  `blob` blob NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Each file size I want to insert is around 8kb in size.

I'd appreciate any help with this issue.


texts: SQL Replication - Specify Schema on destination Table

texts:
wondering if it's possible to replicat into a different schema using Transactional Replication?

My situation is that I have 3 DB servers, each hosting it's own instance of our software, in three different locations. I would like to grab the data from each database and replicate them to a central server for reporting in Azure. I'm seeing if it's possible to combine them into a single database as opposed to having to pay for 3.

e.g. (hope this makes sense)
Server 1, db=MySoftware, Table=dbo.Invoice -> replciation to db=MySoftware, table=server1.invoice in Azure SQL server
Server 2, db=MySoftware, Table=dbo.invoice -> replication to db=MySoftware, table=server2.invoice in Azure SQL server
Server 3, db=MySoftware, Table=dbo.invoice -> replication to db=MySoftware, table=server3.invoice in Azure SQL server

I'm not worried if it's not possible, more of a curiosity thing.

Cheers


texts: Why performance degradation with query hint FIRST_ROWS(1000)?

texts:
I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.

In case the user supplies very unselective arguments, the query takes a lot of time to complete. In case the query would return more than 1000 rows, the user should be supplied with an error message.

The query does not have an order by clause, but just joins three (large) tables.

The idea now was to use the query hint FIRST_ROWS(1000) to speed up. Tests in SQL developer showed the cost metric is considerably reduced (factor 10).

The query is executed through the Entitymanager using a native query.

However, the behavior within the application is as follows: duration of OraclePreparedStatement.executeQuery is reduced by 60%. However, now a lot more of time is spent in EJBQueryImpl.getResultList which before took less than 100ms creating a high performance degradation compared with leaving out the query hint.

Code is essentially:

Query query = em.createNativeQuery(sqlQuery); // sqlQuery has query hint "FIRST_ROWS(" + (maxResults + 1) + ")"

query.setMaxResults(maxResults + 1);

List<Object[]> resultList = query.getResultList();

if (maxResults != 0 && resultList.size() > maxResults) {
    resultSetTruncated = true;
}

Query is:

SELECT ... FROM
    T1
LEFT OUTER JOIN T2 on T1.pk1 = T2.pk1
        AND T1.pk2 = T2.pk2
        AND T1.colx = T2.pk3
LEFT OUTER JOIN
    T3
        ON T2.pk1 = T3.pk1
        AND T2.coly = T3.pk1
WHERE
    T1.pk1 IN ('081111')
    AND EXISTS (
        SELECT 1 FROM T4 WHERE
            T1.PK1 = T4.PK1
            AND T2.PL2 = T4.PK2
            AND T4.KIND = 'N'
            AND T4.NORMALIZED LIKE 'DA%'
    )

Table T3 is very large and the specified conditions in the case of interest are not very selective.


texts: How to get the oracle dataguard log transfer and applying status?

texts:
Logs are transferred from primary db instances to stand-by db and applied continuously.

Please advise how to get the report in the following format (preferably in browser) at some frequency (say 5sec/10sec) for monitoring purposes at stand-by db (prefer to any built-in function or class in oracle dataguard):

-----------------------------------------------------------------------------------
log_name   instance   created_time   tx_start    tx_end  tx_duration  tx_success   apply_start    apply_end   apply_duration apply_success
-----------------------------------------------------------------------------------
...
...
...
-----------------------------------------------------------------------------------


texts: How to change MSSQL Management Studio theme?

texts:
Different from:
How to apply a theme to SQL Server Management Studio 2008?

What I want is to change the theme not colour scheme of the editor.

I want it to look as close as possible as Dark in 2015.


texts: Inject aggregation function inside a procedure

texts:
Is it possible to inject the name of a function (AVG, MAX...) as a procedure parameter?
CREATE PROCEDURE test(func TEXT)
LANGUAGE PLPGSQL
AS $$
BEGIN
DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo AS SELECT
func(bar.column_a) as func_column_a
FROM bar;
END
$$;


texts: SA account locked out then unlocked somehow

texts:
This morning I noticed that our sa sql server account was locked out, and then somehow unlocked.

9/27/2019 5:28:26 AM - Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: local machine]
...
9/27/2019 5:28:47 AM - Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.  [CLIENT: xx]
...

I replaced the client IP with xx for posting here. Then at 5:45 AM EST the account was unlocked somehow and the errors stopped.
We also receive few of these messages during the time the account was locked

The client was unable to reuse a session with SPID 413, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
and APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
to the user Domain\user SID (S-1-5-21-1482476501-1715567821-725345543-24374) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

It looks like someone was on the server and trying to login as SA. We checked internally and everyone says they were not, we have a consultant at the moment who has his own sql server id, we asked him and he says he was not trying to login as SA during the time frame. It looks like he was logged in at the time this happened because his username is what is shown in that event log message regarding 'application-specific permission'. The consultant does not have the SA credentials.
Is there anyway I can check who was trying to login as SA and how the account ended up getting unlocked? Is there any auditing I can setup for the future in case this happens again? This is the first time we have seen this.


texts: MySQL upgrading from 5.7 to 8 - Throwing strange error and won't start up

texts:
I followed the instructions on the MySQL web site and got MySQL server 8 mostly installed but it won't launch because it fails pretty quickly during the upgrade process. The apt-get portion installed all the files. But when it launches this is what is added to my error.log file and I have no clue what to make of it and Google is not being helpful.

2018-09-07T12:25:54.281373Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 3662
mysqld: /export/home/pb2/build/sb_0-29394547-1530206990.8/release/mysql-8.0.12/sql/dd/upgrade/table.cc:1075: bool dd::upgrade_57::add_triggers_to_table(THD*, TABLE*, const String_type&, const String_type&): Assertion `t->get_event() >= t_type && (t->get_event() > t_type || t->get_action_time() >= t_time)' failed.
12:27:19 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=300
thread_count=1
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 134902 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x55ae33b103a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f082a824da0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char*, unsigned long)+0x3d) [0x55ae31bc258d]
/usr/sbin/mysqld(handle_fatal_signal+0x423) [0x55ae30dfcde3]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890) [0x7f083e593890]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7) [0x7f083c98fe97]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141) [0x7f083c991801]
/lib/x86_64-linux-gnu/libc.so.6(+0x3039a) [0x7f083c98139a]
/lib/x86_64-linux-gnu/libc.so.6(+0x30412) [0x7f083c981412]
/usr/sbin/mysqld(+0x1abc55b) [0x55ae31b8155b]
/usr/sbin/mysqld(dd::upgrade_57::migrate_all_frm_to_dd(THD*, char const*, bool)+0x562) [0x55ae31b82042]
/usr/sbin/mysqld(dd::upgrade_57::fill_dd_and_finalize(THD*)+0xf2) [0x55ae31b554f2]
/usr/sbin/mysqld(+0xde1e96) [0x55ae30ea6e96]
/usr/sbin/mysqld(+0x1f920af) [0x55ae320570af]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f083e5886db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f083ca7288f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2018-09-07T12:27:20.027024Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 4161

I know this is all that is being added because I am adding a few blank lines to the log just before trying to launch MySQL and this code is all that comes up after my injected blank lines.

I am running Ubuntu 18lts server. MySQL 5.7 rand fine and my database had no errors because I checked it before trying the upgrade.


texts: Can someone explain what’s the criteria SQL server (2014) will automatically recompile a stored procedure which has table variables? I know there is criteria for statistics but how about a proc using table variable not the temp table?


texts: How can sp_blitz help investigate what caused TLog to fill up

texts: Non-uncommon scenario:  Something has ran wild and caused the TLog for a patifular Db to fill and everything has stopped.  How can sp_blitz show us some info on what led up to this point? What about sp_whoisactive?


texts: index using btree_gin on smallint/bigint

texts:
I try to use a btree_gin index on smallint column, but the index is never used for queries. According to documentation (http://www.postgresql.org/docs/9.5/static/btree-gin.html) it should work. If I change the column-type to int the index is used, if it is bigint it does not work either.

I would like to use btree_gin as the index is much smaller. In this example the table is 442MB, btree_bin index is 14MB, normal btree index is 224MB in size.

Steps to reproduce:

create table test (year smallint, somedata varchar);
insert into test (year, somedata) select (random()*50) + 1970 as year, 'something' as somedata from generate_series(1,10000000);
create extension btree_gin;
create index on test using gin (year);

The query (type smallint, index is not used):

explain select * from test where year = 2000;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on test  (cost=0.00..179055.00 rows=192333 width=12)
   Filter: (year = 2000)

Change the type to int:

alter table test alter column year type int;

Now the index is used:

explain select * from test where year = 2000;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=467.50..56278.04 rows=50000 width=14)
   Recheck Cond: (year = 2000)
   ->  Bitmap Index Scan on test_year_idx  (cost=0.00..455.00 rows=50000 width=0)
         Index Cond: (year = 2000)

This happens with Postgres 9.5.0 on Debian strech and Postgres 9.4.2 on Centos 6.4.

Could anybody please explain how to use btree_gin on smallint columns? Thank you


texts: Reasons for db2 foreign key error (-667) despite all rows appearing sane?

texts:
I have some csv formatted table dumps and loaded those into a new database with the tables defined by a DDL script from the source database (both acquired by somebody else so I cannot have a look at the original production database). Before loading the rows, I disabled all foreign key constraints (via ALTER TABLE xyz ALTER FOREIGN KEY fkname NOT ENFORCED) to avoid having to care for the load order. Now I want to reenable those constraints with the following statement

ALTER TABLE xyz ALTER FOREIGN KEY fkname ENFORCED

and some of them fail. The error message generated is

SQL0677N: The FOREIGN KEY "fkname" cannot be created because the table
contains rows with foreign key values that cannot be found in the parent key
of the parent table. SQLSTATE=23520

That reads straightforward and clear but I cannot locate the erroneous rows.

An example: T02 is the name of a parent table and T24 the name of a child table. The primary key is (T02.A, T02.B, T02.C) and the corresponding foreign key columns are (T24.A0, T24.B0, T24.C0), the constraint is called F02 and all the named columns are of type CHAR with lengths 2, 4 and 7, respectively.

I tried finding the breaking rows with the following statement:

(SELECT T24.A0, T24.B0, T24.C0 FROM T24)
EXCEPT
(SELECT T24.A0, T24.B0, T24.C0 FROM T24, T02
WHERE T02.A = T24.A0 AND T02.B = T24.B0 AND T02.C = T24.C0)

but it returns an empty result set.

Are there any other circumstances which may cause this error and how do I investigate them?

The database server in question is DB2 10.1.0 for Linux/Unix/Windows (running under Windows Server 2008 R2 64-Bit).


texts:
I am trying to group by and count with mongodb. Whatever I tried I couldn't do it.
This is my query

db.getCollection("requests").find(
    {
        "updatedAt" : {
            "$gt" : ISODate("2020-05-14")
        },
        "status" : "SUGGESTED"
    },
    {
        "user.email" : 1.0,
        "user.name" : 1.0,
        "updatedBy.displayName" : 1.0,
        "requestTerm" : 1.0
    }
)

I would like to group and count based on updatedBy.displayName

So result will be for me distinct displayname ( Column1), count ( number of times )

How can I achieve this?

This is what I tried and it seems it doesn't work

db.requests.aggregate(
    {$match: {         "updatedAt" : {
            "$gt" : ISODate("2020-05-14")
        },
        "status" : "SUGGESTED" }},
    {$group: {updatedBy: "$updatedBy.displayName", number: {$sum: 1}}}
)


texts: Resuming log shipping, do I just restore the db over itself?

texts:
I have log shipping set up.

I then bring all the databases online on the secondary machine, so log shipping can't restore anymore.

A bit later on I want to resume log shipping.

I was told before that to do this I should do a full copy of the database back from primary, overwrite it and put it into no recovery mode. This works.

I am wondering if this is necessary though - doesn't it ship the entire log? If I restored a blank database, wouldn't it just run the restore from primary job and we're back in business?

I basically I have 20 databases to put back into no recovery mode after I bring the secondary server online when I am done with it, and manually backing up and copying them all from live and restoring would be a tough job, so I am trying to automate it.

I should add that I'm not interested in preserving any data on the secondary machine. It's basically an emergency machine that keeps some services running if the primary one fails, it doesn't store anything important.

Any ideas?


texts: Clarification on UNION ALL of JSONB_EACH result

texts:
This function used to merge 2 JSONB and delete certain key when the value is null, refer to this question.

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;

First question, why value = 'null' works, but value IS NULL is not?

Second question, is there any bad reason to use WHERE value <> 'null' instead of WHERE key NOT IN (SELECT key FROM ...) ?

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> 'null';
$$ LANGUAGE SQL;

Third question, why this function also incorrect (always returns NULL)? Is it because UNION ALL change all resulting record into TEXT?

CREATE FUNCTION jsonb_merge(JSONB, JSONB)
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> (NULL::JSONB)
$$ LANGUAGE SQL;

-- check function:
SELECT COALESCE(jsonb_merge('{}','{"b":null}'),'{"x":"its empty"}'::JSONB) FROM xxx;
      coalesce
--------------------
 {"x": "its empty"}


texts: Transfer data from the cloud to a local database

texts:
I have an SQL Server 2016 Azure database in the cloud called XCLOUD, with many tables big and small, and a local SQL Server 2016 database with the same tables and data called XLOCAL.

In all databases there are neither PK, nor FK.

My goal is to keep XLOCAL updated with all the changes in XCLOUD, minimizing network traffic and keeping XLOCAL available all the time. Performances on Azure are not a problem: once XCLOUD is filled, the server does nothing else until the next day.

A daily process on the Azure server truncates all the tables in XCLOUD and fills them again with almost the same data. I don't have control on this process: if I had, I would change it to only INSERT/UPDATE/DELETE and then I would use log shipping, but this is not possible.

One idea I had is to create a new database from XCLOUD1 on the Azure server: when the process filling XCLOUD finishes, I would somehow compare the differences between XCLOUD and XCLOUD1, update XCLOUD1 with MERGE statetements as needed, then use log shipping from XCLOUD1 to XLOCAL. But I would need to code the MERGE statements one by one, unless you can recommend some tool for this task.

What would you do?


texts: SQL Agent jobs in Basic Availability Group setup

texts:
I have the following scenario on SQL Server 2016 Standard Edition 13.0.4411.0 (SP1-CU1):

2 database servers (DBNode1 and DBNode2). I have a database in a Basic Availability Group, with DBNode1 being the Primary and DBNode2 being the Secondary. As expected, if I try connecting to the DB on DBNode2, it fails as expected with the "The target database, ..., is participating in an availability group and is currently not accessible for queries" error

I've created a SQL Server Agent Job on DBNode2 only (the Secondary) to perform a dummy UPDATE on the database every minute as a test and expected this to fail as that DB should not be accessible on that Secondary node.

UPDATE dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

The "Database" option for the job step is set to my DB.
However, it succeeds and has actually performed the operation on the Primary node. (See images 1 and 2 below)

If I change the Job to target the master database, and use the following TSQL command structure, this does then error as I'd expect, being the Secondary node (See images 3 and 4 below).

UPDATE MyDB.dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

It seems like behind the scenes, despite targetting the local server, this is actually working out that the target DB is in an Availability Group and so connects to the Primary node and runs the command there. Is this normal / documented behaviour?

UPDATE:

Added screenshots to show the setup, to avoid confusion.

1 - original job setup showing the unexpected behaviour

2 - original job history output, showing the successful execution

3 - revised job setup showing the expected behaviour

4 - revised job history output, showing the failed execution as expected


texts: What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?

texts:
I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some calculated values, and optionally update two columns on a 3rd table.

My application has a List<T> of records, and I'm trying to decide if I should loop through the list and run the procedure on each record, or combine the id values into a single string and make a single database call. If I make a single database call, then SQL would be responsible for converting the comma-delimited string to a temp table and running the scripts on all records at once.

I'm leaning towards passing a comma-delimited string of Ids to the procedure, but I am not sure if this is an ideal way of doing this kind of mass-update, or if I am missing some key factor that would affect this decision.

What should I consider when deciding if I should pass a comma-delimited string of ids to a stored procedure instead of calling it individually per record? Or is there a more efficient way to do this kind of update from an application?

I am using SQL Server 2005 and Entity Framework


texts: How to add the value of my Sub-query, I want to add the value of Cash,Charge and Other_Charges as Total_payment

texts:
I am using 2 tables, TL_FISCALREPORT and TL_TENDER. What I want to do is to add the total value of my 3 subqueries from TL_TENDER named as cash, charge, tender and show it in my query:

SELECT DISTINCT F.DT_TIME,F.DT_DATE,'DEPT STORE' AS CATEGORY,
      F.STOREID,F.TID,(F.CURRENT_POSITIVE) AS NEW_GT,
      PREV_POSITIVE,TOTAL_SALES_AMT,
      CAST((((F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT)/.80)*.20) AS DECIMAL(16,2)) AS
      SC_DISC,(0) AS PWD_DISC,F.REGULAR_DISCOUNTS_AMOUNT,(0) as NON_PROMO_DISC,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT) AS
      TOTAL_DISC,F.NEW_GRAND_TOTAL_VOIDS,F.DAILY_RETURNS,(0) AS ADJ,
      (TOTAL_SALES_AMT-(F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT)-0) AS GROSS_SALES ,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT) AS  SC_SALES,(0) AS PWD_SALES,(F.SC5_DISC_AMOUNT
      +F.SC20_DISC_AMOUNT+0) AS NON_VAT_SALES,F.ZERO_RATED_SALES_AMOUNT
      ,CAST((F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-
      F.ZERO_RATED_SALES_AMOUNT/1.12)AS DECIMAL(12,1)) AS VATABLE_SALES,CAST(( ((F.DAILY_GROSS_SALES-
       F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT)*.12)/1.12) AS DECIMAL(15,2)) AS
       VAT,CAST((F.DAILY_GROSS_SALES-(F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-
       F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT*.12/1.12)) AS DECIMAL(12,1))
       AS NETSALES,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) CASH,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') CHARGE,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') OTHER_TENDER
,F.FROM_FISCAL_XACT,F.TO_FISCAL_XACT ,F.POS_ITEMS_COUNT,F.PREV_VOID_COUNT,(0) AS NOSALE_TRANSACTION,
F.REPORT_TYPE FROM TL_FISCALREPORT   F
WHERE F.REPORT_TYPE=1 AND F.STOREID=1 AND F.DT_DATE ='2016-06-30'
ORDER BY TID


texts: SQL Server 2008 R2 : LDF file size not increasing

texts:
I am using SQL Server 2008 R2. I have a table statDB of 100 GB in primary filegroup.

I have created a secondary filegroup in same database (Lab1) and created a table copyStatDB.

Now I start copy table data from primary file group to secondary file group.

I have noticed that none of my TempDB size change and not my .LDF file size change.

I am surprised to see that because as per my understanding when we execute a Insert statement it should increase .LDF file size increase first then copy data to my .NDF file.


texts: How can create trigger that will insert username and date to a table,when oracle user account unlocked to keep account lock history


texts:
I'd like to see if there are any changes on the permissions granted for a specific table.

For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices.

If this permission is somehow GRANTed to John, I want to know about it. I'd like to know who has granted him the permission and when.

I'm using SQL Server 2012 Enterprise Edition.


texts: Once daily AWS Postgres CPU spike caused by mysterious "OS processes"

texts:
We've been experiencing huge CPU usage spikes on our Postgres 9.6 AWS RDS so the point we've had to upgrade to a cost prohibitive instance just to handle these once daily spikes. This spike always occurs at 23:30 UTC and lasts for about 30 minutes before tapering off to half the intensity, then are gone completely by 00:30.

After looking thru the enhanced monitoring logs at 1 second granularity, I can see that at 22:30:03 there is suddenly an entry for OS processes in the processList that did not exist before.

I cannot tell any information about what this mystery process is, however, I see that it immediately requires +33% of the overall cpuUsedPc.

No slow queries show up when I watch this output:

SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND query not ilike '%DEALLOCATE%' AND query not ilike '%COMMIT%'
ORDER BY query_start desc;

There are no crons running at that time.

There are no backups running until 02:00.

What could this be or how can I diagnose what is causing it?

Example:

    {
        "vss": 0,
        "name": "OS processes",
        "tgid": 0,
        "vmlimit": "",
        "parentID": 0,
        "memoryUsedPc": 0,
        "cpuUsedPc": 32.22,
        "id": 0,
        "rss": 0
    }


texts: Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?


texts:
What is the damage to the database if it uses high memory storage? Are there consequences for this use? Does it affect performance?
I need to understand a lot about this problem to be able to create solutions to it


texts:
I am moving data and SSIS packages from SQL Server 2014 database server to SQL Server 2016 server.

I began with building the new server and installing SQL Server 2016 Standard edition.  Then I backed up the data on the 2014 server and restored it onto the new server 2016 server.  Including SSISDB.

One of the items that needs to be done is to go into the SQL Server Agent jobs and modify any step that is of the type SQL Server Integration package and change the server parameter in the package to the name of the new server.

However when I try to do that I get the following error message:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  ADDITIONAL INFORMATION: Invalid object name 'SSISDB.catalog.customized_logging_levels'. (Microsoft SQL Server, Error: 208)

I get the same message when I try to look at the SSISDB Integration Catalog

One article I found recommended the following steps:

Backup SSISDB database from SSMS 2014.
Create Integration Service catalog on SSMS 2017 and restore backup.
Change the SSISDB database owner sa.
Right click on SSISDB under Integration Service Catalogs.
Select Database Upgrade-> Select Next -> Select I have taken backup of the SSISDB database. continue with the SSISDB upgrade and Select Upgrade.

The last step (SSISDB upgrade) fails with the following message:

  The SQL Server edition or version do not meet the minimum requirements to create a Master Data Services database. (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

I would really appreciate any advice.

Thanks in advance


texts: Statistics that are neither auto_created nor user_created

texts:
So far, I was under the impression that there are two types of statistics objects in SQL Server:

automatically-created statistics, which are created by SQL Server on index columns or single columns to improve performance, and
user-created statistics, which are created manually with a CREATE STATISTICS statement.

Thus, I was quite surprised to see the following query:

SELECT s.name, s.stats_id, s.auto_created, s.user_created
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
       INNER JOIN sys.tables AS t ON c.object_id = t.object_id
 WHERE t.name = 'mytable'

yield the following result:

name                    stats_id   auto_created   user_created
--------------------------------------------------------------
PK__mytable__7A73B1B8       1          0              0
SomeIndex                   2          0              0
SomeIndexedColumn1          3          0              0
SomeIndexedColumn2          4          0              0
SomeIndexedColumn3          5          0              0
SomeColumn                  6          0              1

What are those statistics, which are neither auto_created nor user_created? I've looked at the documentation of sys.stats, but did not find an answer there.


texts:
I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 roles. I wrote the following script:

DEFINE ROLE_1 = &INPUT_1
DEFINE ROLE_2 = &INPUT_2

SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET LINESIZE 140

COLUMN PRIVILEGE    FORMAT A20    HEADING "PRIVILEGE"
COLUMN TABLE_NAME   FORMAT A30    HEADING "TABLE_NAME"
COLUMN GRANTABLE    FORMAT A5     HEADING "GRANTABLE"

PROMPT
PROMPT OVERLAPPINGS BETWEEN 1st ROLE AND 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

INTERSECT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 1st ROLE BUT NOT IN 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 2nd ROLE BUT NOT IN 1st ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

It works all fine, but for some reason it doesn't display the table head.

The real problem why I'm asking is, is there a more comfortable way to automatically compare all roles to one another? Some neat way to do all this? I'm quite new to PL/SQL and don't really know how to loop this thing up. Is there even maybe a possibly way to display the corresponding column of the tables?


texts: SQL Server 2014 - Strange SP behavior during compilation

texts:
Today I noticed a strange behavior during the creation of a stored procedure in SQL Server 2014.
Below is my test procedure. Please explain why SQL Server disallows compilation with the first SELECT and allows compilation with second one.

CREATE PROCEDURE [dbo].[test]
AS
BEGIN

    SET NOCOUNT ON;

    IF 1=0  -- this will never occur
    BEGIN
        select x from Table -- TABLE EXIST BUT COLUMN NOT - STORED PROCEDURE IS NOT CREATED
        --Msg 207, Level 16, State 1, Procedure test, Line 25
        --Invalid column name 'x'.

        -- But if i change
        select x from TableWhichNotExistInDatabase  -- This table not exist in database, but stored procedure has been created without any problems and I can exec it without errors (    because of 1=0 in if )

    END
    ELSE
    SELECT ''
END

In my real procedure, I'm creating temporary tables with various schema (from XML passed as parameter). Next I'm trying to validate some data in the stored procedure. Validation blocks are controlled by IF statements.

The creation of temporary tables and the stored procedure call is executed by a dynamiclly created procedure.

I think the problem lies somewhere during the creation of the execution plan or something like that. If the table does not exist in the database, SQL Server allows the creation of the stored procedure when a table does not exist.  However, when the table exists, the creation of the stored procedure fails because of invalid schema - no column.

I was looking for a solution which disables schema checking but did not have any success.

Are there any ideas regarding how can I bypass this limitation?


texts: Basic question: Simplest way to share company databases

texts:
My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite limited in its functionality (set reports), and the available data is vast. At the moment we have limited technology resources to develop ways of mining this data, and though I am pretty clued up on basic database construction, this is something totally new to me.

If the data was small enough to be manageable, I could request a CSV file to be sent to an FTP server, pull it into the software we have (Access, Tableau) and query it from there. However, there's going to be loads of it.

So what are my options here?

From the little I know, the simplest thing would be to query source data. Are there any tools that would do that outside them using another API?

Similarly in my head I could foresee a way to send new data to a SQL server somewhere, and then query/link to it.

Is this possible?
Is this how SQL Server works?
How much effort would this take on their part?

I am not looking for a full schematic on how to do this (though I wouldn't turn one down), but just some clear terms and tools I can research on my own would be enough.

Essentially, we are meeting with the company to discuss how best we would do this. They currently have a limited UI for customers, but are interested in expanding. We have very little database infrastructure but are also eager to expand and looking for tools/products that will help us. We don't have SQL Server at the moment - we don't hold much data here, hence why we rely on querying third party data. So without them building a new interface for customers, how would one go about sharing this data considering the volume is considerable?

((Update re data size: Apparently we're looking in the region of 1-2 million rows a day with, probably, about 50 fields (Mostly short number and text < 50)....I don't know exactly what that translates into in terms of Mb/day, but suspect it's going to be too much to handle over email on anything over a daily basis))


texts: Query the Owner of SQL Server 2008 R2 Jobs

texts: Is there a way to query the owner of all jobs in SQL Server 2008 R2? I discovered when maintenance plans are edited the owner gets changed, so I want to make sure they are all owned by sa.


texts: Creating/dropping MySQL users using "IF NOT EXISTS" when row based replication is in place

texts:
I'd like to know the affect of using "IF NOT EXISTS" when creating or dropping users when row based replication is being used to replicate data to a mysql slave.
I know that in the past I have broken replication by creating or dropping users that already existed (or didn't exist) on the slave, even though row based replication was being used.
It seems that the user table isn't replicated in a row based fashion even though the database itself is.
Would adding "if not exists" to my create or drop statements prevent me from breaking replication in the future?


texts:
I backed up a database a few weeks ago, then for comparison downloaded it again. It was about 10,000 kB larger. There should not be anyone writing to it, but there are lots of reads.

Will a static MySQL database become bigger in file size over time, even without anyone writing to it?


texts: How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?

texts:
I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping.  Here's the error I get:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

I have taken the advice (or at least tried to) of the error message and created a new data file. Here's the command I used for that:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ABCDEFG/temp02.dbf'
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 4096M;

Now when I look into DBA_TEMP_FILES I see both the old and new files associated with the TEMP tablespace, yet when I attempt to re-run my query, I get the same error message.

So, my question is why does this error still happen, given that the query I'm running is big but not that big. Did I create the data file correctly? How can I diagnose this further?


texts: Transfer all read request to Slave in Master-Slave MySQL

texts:
I have configured master slave on my system (MYSQL). Now, is there is any script or way available or possible to transfer all read request to SLAVE.

So, let me make my question here more clear.
I have a application written in JAVA-SPRING and use Bone-CP to connect to Mysql db.
So, now I have to implement Master-Slave where all write request goes to MASTER and all read request goes to SLAVE via Master or directly.
I don't want to make any changes in code, so I am looking for some solution which I could implement on MYSQL to transfer all read/Select request to Slave.

I have googled around but no solution or hint found.

There might be two possible solution for it.
1. All request first hit MASTER and then master transfer all request to SLAVE.
2. A layer between APPLICATION and MYSQL which decides where to transfer the request accordingly

But I have no idea how to implement it, So can anyone provide any link, hint or solution to my problem.


texts: Copying MySQL schema, some tables not getting recognized!

texts:
From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are in .frm format.

Some random tables are not getting imported. All the tables that I have are InnoDB with only two MyISAM tables. Many InnoDB tables and both the MyISAM tables are working fine. It's just that some random tables are not working.


texts: Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave

texts:
I am trying to setup multi source replication. In my slave I have defined following two properties in its configuration file.

master-info-repository=table
relay-log-info-repository=table

These entries are needed because I am defining channels in command "CHANGE MASTER TO" as below:

change master to master_host="127.0.0.1", master_port=20000, master_user="replication",master_password="password1" for channel="master1";

Above command is showing error:

  ERROR 1794 (HY000): Slave is not configured or failed to initialize
  properly. Yo u must at least set --server-id to enable either a master
  or a slave. Additional error messages can be found in the MySQL error
  log.

SERVER ID is clearly defined in config file.

If I don't define those two properties (shown on top) in config file and start slave without "for channel" the it works fine. Only when I try to define multi source it is showing this error.

Error log file contain following entries:

  2017-01-01T12:41:54.446764Z 0 [ERROR] Error in checking
  mysql.slave_master_info repository info type of TABLE.
  2017-01-01T12:41:54.446764Z 0 [ERROR] Error creating master info:
  Error checking repositories.  2017-01-01T12:41:54.446764Z 0 [ERROR]
  Failed to create or recover replication info repository.
  2017-01-01T12:41:54.446764Z 0 [ERROR] Failed to create or recover
  replication info repositories.

How do I fix this issue?

One thing I came to know that this is related to 5 tables innodb_index_stats, innodb_table_stats, slave_master_info,  slave_relay_log_info, and slave_worker_info. Even if I delete those tables and then create again then it still shows same error.

I am using multiple instance on a single machine.


texts: Entering Exact Values into a Table Using SQL

texts:
I am needing to insert particular values into a table I am working on with SQL (see function below):
INSERT INTO *Table Name* (...) VALUES(...);

The values that are going to be stored inside this table are values that involve another programming language that have a lot of weird symbols involving a single backslash, *, dollar signs, etc.
How can I insert values precisely as they are inside this table?

For instance, when I type '\2' it needs to return \2 inside the table instead of just 2. I am unable to manually enter the two backslashes to get \2. This is because I am receiving input from another user via HTML and sending it to my table with PHP. The issue I am having in that there are these special characters with SQL which are being entered is not the way I intend them to as values.

Is there a way to type these values in exactly with a certain string function that is called beforehand? Other ways of doing this would be greatly appreciated!
Additional Info for Database:

Server: Localhost via UNIX socket
Server type: MySQL
Server connection: SSL is not being used Documentation
Server version: 5.6.49-cll-lve - MySQL Community Server (GPL)

Potential Answer:
Is $mysqli defined to be as follows? (I call it $db.)
$mysqli=mysqli_connect(...);
So, that literals are added as follows:
$query = "INSERT INTO Table (C1, ... Cn) VALUES('$C1', ... '$Cn')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $C1, ..., $Cn);
$stmt->execute();
mysqli_query($db, $query);


texts: How to speed up a mysql restore from large dump file

texts: I have a 350MB msyql dump file that takes over 3 minutes to load. I'd like to speed it up. I've tried using --opt with mysqldump (shaved off 30 seconds). The best I can achieve is 42 seconds faster. Any other techniques to speed up a mysql restore from a dump file?


texts: PostgreSQL remove numbers before first comma

texts:
I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g.
{100000,125000,150000,175000,200000}

I want to remove the first number and comma of all rows in "scrap_value", so the end result looks like this
{125000,150000,175000,200000}

How would I got about doing this?


texts:
The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.

We also have SSIS on the secondary replica of the HA group, which seems to be pulling data from the primary replica which puts a strain on the application and the end users.

Is there a way to set up a reverse listener where the application or user will be pointed toward the secondary replica instead of the primary replica?

Thanks!


texts: Why can't I script out CLR assemblies with SMO in Powershell?

texts:
I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most part my script works well and has helped identify a few gotchas in some old procs and functions, but I cannot get it to script out CLR functions.

There are only one or two and if I right click on them in SSMS and create script to new query editor window it works fine and I can then run that on the destination DB and it creates the assembly, but when I try and use SMO in Powershell it just does not script the assemblies (it doesn't error either). In essence, this is my script (left out the bits which create the connections/DB objects etc):

Write-Host "Getting DB objects..."
$assemblies     = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema  }

# Set scripter options to ensure only schema is scripted
$scripter.Options.ScriptSchema  = $true;
$scripter.Options.ScriptData    = $false;

#Exclude GOs after every line
$scripter.Options.NoCommandTerminator   = $false;
$scripter.Options.ToFileOnly            = $false
$scripter.Options.AllowSystemObjects    = $false
$scripter.Options.Permissions           = $true
#$scripter.Options.DriAllConstraints     = $true
$scripter.Options.DriForeignKeys        = $false
$scripter.Options.SchemaQualify         = $true
$scripter.Options.AnsiFile              = $true

$scripter.Options.Indexes               = $true
$scripter.Options.DriIndexes            = $true
$scripter.Options.DriClustered          = $true
$scripter.Options.DriNonClustered       = $true
$scripter.Options.NonClusteredIndexes   = $true
$scripter.Options.ClusteredIndexes      = $true
$scripter.Options.FullTextIndexes       = $true

$scripter.Options.EnforceScriptingOptions   = $true

function CopyObjectsToDestination($objects) {

    foreach ($o in $objects) {

        if ($o -ne $null) {
            try {
                Write-Host "Writing " $o.Name
                $script = $scripter.Script($o)

                $destDb.ExecuteNonQuery($script)
            } catch {
                #Make sure any errors are logged by the SQL job.
                $ex = $_.Exception
                $message = $ex.message
                $ex = $ex.InnerException

                while ($ex.InnerException) {
                    $message += "`n$ex.InnerException.message"
                    $ex = $ex.InnerException
                }
                Write-Error $message
            }
        }
    }
}

# Output the scripts
Write-Host "Create assemblies in destination database..."
CopyObjectsToDestination $assemblies

I feel like I'm missing something simple and obvious because I can get tables, procs, functions, views and the like without any problems at all. What have I missed?


texts: Find timestamp for highest number of concurrent users by date

texts:
I have a table including these columns:
Sample data:
concurrent_users  concurrent_timestamp       concurrent_date
3                 2020-06-24 07:20:00 UTC    2020-06-24
7                 2020-06-24 08:20:00 UTC    2020-06-24
8                 2020-07-05 06:20:00 UTC    2020-07-05
2                 2020-07-05 03:20:00 UTC    2020-07-05

I want to find/list the date and timestamp where the concurrent_users is highest for each date.
Expected output:
concurrent_users  concurrent_timestamp       concurrent_date
7                 2020-06-24 08:20:00 UTC    2020-06-24
8                 2020-07-05 06:20:00 UTC    2020-07-05

I tried following approach/query:
Error 1
select concurrent_timestamp, max(concurrent_users)
from sample_data
group by concurrent_date;

But this gives an error as the concurrent_timestamp column is not included in the group by clause.
When I put concurrent_timestamp in group by, I get ...
Error 2
select concurrent_timestamp, max(concurrent_users)
from sample_data
group by concurrent_timestamp ;

It does not give the desired output instead just lists more than a single record for each date.
How to solve this?


texts: Mysql Connector C++ data retrieval question

texts:
So, the result of a query is stored in a ResultSet. And that type has multiple methods for data retrieval such as getString, getInt etc.

I'm curious how the data is stored in that object. Is all the data stored as a string, and then converted to the required type, according to the function called(getInt would convert it to an int for example).

OR the data is already stored as the actual type, and if that is so, is it ok if I call getString on a value that is supposed to be an int? Will the conversion take place without problems?


texts:
I can't find a solution although it should be easy one.

I have some students and all I need is to get the highest mark of each one in Math.

TableName: Students.
Columns:
Name Mark
John 90
John 84
John 92
John 60
Yan  75
Yan  88
Yan  60

I want to get this results:

Name Mark
John 92
Yan  88

I have tried to do top 1, but it gives me only one student. I need to do somehow top 1 on each student, but I don't know how to do that.

Please help me to figure it out.

Thanks in advance.


texts:
I've just installed Oracle 11g and linked it to SQL Developer.

I want to create a database for my django install.

CREATE database 'django';

Error starting at line 1 in command:
create database 'django'
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted
01501. 00000 -  "CREATE DATABASE failed"
*Cause:    An error occurred during create database
*Action:   See accompanying errors.

(I have also tried without the '')

Why isn't this working?

Is there another way I'm meant to create databases?


texts:
If you have a design of a "master table" and several "subtables", is it better* that the master table "link" to each of the sub tables, the subtables link to the master, or for bi-directional links be established? The data present in the subtables COULD be not present (i.e. it might not be relevant to the particular entry, so a linked entry in the subtable won't be created). Basically data will be related in a 1-to-(zero or one) fashion. There will never be a reason to link between the subtables WITHOUT involving the master table.

Specifically I'm concerned about better* with respect to MS SQL Server (2008R2+)

Example Design 1: Master Table links to Sub-tables

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
  ,index_subA int NULL --index created on this value. links to PK_SA of appropriate entry if it exists
  ,index_subB int NULL --index created on this value
  ....
  ,index_subM int NULL --index created on this value
)
CREATE TABLE tSubA
  PK_SA int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
)
CREATE TABLE tSubB
  PK_SB int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
)
CREATE TABLE tSubM
  PK_SM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
)

Example Design 2: Sub-Tables link to Master Table

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
)
CREATE TABLE tSubA
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED -- value of PK_TM of cross-linked entry
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
)
CREATE TABLE tSubB
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
  ,Master_ID int NOT NULL --index created on this value. links to PK_TM of appropriate entry
)
CREATE TABLE tSubM
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
)

Example Design 3: Bidirectional links

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
  ,index_subA int NULL --index created on this value. links to PK_SA of appropriate entry if it exists
  ,index_subB int NULL --index created on this value
  ....
  ,index_subM int NULL --index created on this value
)
CREATE TABLE tSubA
  PK_SA int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
  ,lMaster_ID int NOT NULL
)
CREATE TABLE tSubB
  PK_SB int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
  ,lMaster_ID int NOT NULL
)
CREATE TABLE tSubM
  PK_SM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
  ,lMaster_ID int NOT NULL
)

*better could be

faster
less likely to bork up over time as DB entries are added and/or occasionally deleted
easier for other DBAs to quickly comprehend
more intuitive for the average end-user
more easily extensible by adding additional sub-table types
more data-compact
more easily modified to be a one master to many copies of a subtable
more easily modified to be a many masters to a single copy of a subtable
insert your own definition here

(less emphasis on options 7&8 here)


texts: I am evaluating software systems which configure products that tout constraint based versus rules based engines.  Can someone explain (with maybe an example or two) to a non-expert what the difference is?  My guess is that a constraint based engine is rules defining relationships between objects, and rules based engines are more like the WHERE clause of a SQL language query.


texts:
Unintentionally discovered that following query works in PostgreSQL:

UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1;

I'm passing an array of strings to field and the result is a string of '("string1","string2")', not an array as expected, at least then using php extension pgsql.

How this happens and that does ('string1', 'string2') means in PostgreSQL? I don't think this works in other SQL RDBMSes.


texts: How can I unload Oracle database tables into a csv files?

texts:
I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command line.
What would be the best approach to this?


texts: File Group Association while Snapshop Replication

texts:
I want to know how can I save filegroup structure during snapshop replication in SQL Server? Does this property solve my problem? I want the same filegroups in publishers be created in the subscriber.

Thanks


texts: Help solving a Clustering Issue: [sqsrvres] ODBC sqldriverconnect failed

texts:
I have a 2 node cluster (NODE-A & NODE-B) with 2 SQL instances spread between them. INST1 prefers NODE-A, INST2 prefers NODE-B. INST1 started generating errors then, failed over to NODE-B. Migrating INST1 back to NODE-A generates the connection errors after it logs a "Recovery is complete." message.

Win 2008 R2 Ent.
SQL 2008 R2 Ent.

Errors from the Event Log after first failure:

[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
[sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Query timeout expired
[sqsrvres] OnlineThread: QP is not online.
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]The connection is no longer usable because the server failed to respond to a command cancellation for a previously executed statement in a timely manner. Possible causes include application deadlocks or the server being overloaded. Open a new connection and re-try the operation.
[sqsrvres] ODBC sqldriverconnect failed
[sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]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.


texts: Change oracle port from 1523 to 1521 when something else is on 1521

texts:
I'd like to have oracle running on the default port of 1521 so I don't need to change connection strings in 100 places. My newly installed database is listening on 1523 though:

$tnsping $ORACLE_SID
.... (PORT = 1523)

I want that to be 1521. I confirmed that I can connect to 1523 but not on 1521. However, it appears that oracle is actually listening on port 1521,1522 and 1523:

$netstat -tulpn
...
tcp        0      0 0.0.0.0:1521                0.0.0.0:*                   LISTEN      119935/tnslsnr
tcp        0      0 0.0.0.0:1522                0.0.0.0:*                   LISTEN      17766/tnslsnr
tcp        0      0 0.0.0.0:1523                0.0.0.0:*                   LISTEN      185301/tnslsnr

Why would it be listening on 3 ports, but not working on 1521? Is it normal for oracle to be listening on 3 ports with 3 instances of tnslsnr?


texts: How do you do two date calculations in one SQL Statement?

texts:
In a SQL Stored Procedure, I need to calculate a date by subtracting 1 month and set the day to the 17th of the month.

I can subtract 1 month with:

DATEADD(month,-1, loanFirstPmtDate)

But do not know how to also set the day to 17.

How do I do both calculations in one SQL statement?

Example: Loan First Payment Date = ‘03/01/2019’
Subtract 1 month and set day to 17 = ‘02/17/2019’


texts: What's the difference between master and slave nodes in MySQL Cluster?

texts:
I use MySQL Cluster 7.1.15a.

I have 2 nodes, ndb_mgm -e shows:

[ndbd(NDB)]     2 node(s)
id=3    @X  (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0, Master)
id=4    @Y  (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0)

(ignore the starting state, i just started the cluster)

Node id 3 is Master

What does it mean ?
What is the difference between Master and Slave nodes ?

I connect using mysql node anyhow, so I don't choose to which data node to connect.

Do writes happens on the Master and reads on the Slave?
When I connect to the mysql node, does that happen transparently?


texts: PostgreSQL count() works extremely slow on replication slave

texts:
I'm using postgres replication. I have one master and 2 slaves.
I have table first with 1 500 000 rows. If I perform select count(*) from first on master, it takes less than a second, but on both slaves it takes more than 500 seconds! Sometimes it's executing forever! It's ridiculous!

Query plan on master

Aggregate  (cost=246018.54..246018.55 rows=1 width=0)
  ->  Seq Scan on news_event  (cost=0.00..242254.63 rows=1505563 width=0)

On slave I can't get query plan - query execution works more than 30 minutes, I stop it.

Also, I have table second with 6 500 000 rows, and count for this table works equally fast on master and both slaves.

Problem appeared after adding new column to first table. About 24 hours before I added nullable column, and after that manually inserted default values to all rows. After that slave works extremely slow. All data on slaves is up to date.

I runned vacuum and analyze - nothing changed.


texts:
I am trying to make an except in SQL but it doesnt exclude the data even though they are identical.

SELECT A.payment_id,
                            SUM(A.importe_trx) monto_total
FROM  [dbo].[ABONOS] A
WHERE payment_id = '980190391415704'
GROUP BY A.payment_id

SELECT B.payment_id payment_id,
                            SUM(B.precio_venta) monto_total
FROM [dbo].[SALES_VISANET_USD] B
WHERE payment_id = '980190391415704'
GROUP BY B.payment_id

I used that query to get the data and it's the same, but it doesn't exclude it with the EXCEPT command in the middle

SELECT A.payment_id,
                            SUM(A.importe_trx) monto_total
FROM  [dbo].[ABONOS] A
WHERE payment_id = '980190391415704'
GROUP BY A.payment_id

EXCEPT

SELECT B.payment_id payment_id,
                            SUM(B.precio_venta) monto_total
FROM [dbo].[SALES_VISANET_USD] B
WHERE payment_id = '980190391415704'
GROUP BY B.payment_id

I use "sum" because in one of the tables it has different amounts with the same payment_id, but when adding and grouping them they give the same amount and the same data as the other table.


texts: How to keep the data warehouse synchronised with the source systems?

texts:
I'm a DBA who is responsible for exporting data from an ERP system for Business Intelligence purposes. The data from ERP system is being sent to the data warehouse and they are used in the ETL process.

We used to run the export process at the end of the day. The queries which collect the data used the WHERE clause with a document creation/modification date = yesterday.

This mostly works just fine - there isn't too much data to export from the source system (less resource consuming on both source and destination systems).

But some source tables don't have the modification date column (only the create date).

At the moment I can see only 2 possibilities:

exporting all the records from the source system (very costly and impossible to mainain in a long run)
exporting records created in the last 7 days (less costly and pretty effective because older data is rarely modified)

I am looking for a better solution. I heard that SQL Server Integration Services have a MERGE JOIN and a LOOKUP transformations. I'm not sure if one of these provide the possibilities that I'm looking for.

What tool can you recommend? I'm sure it can be done efficiently in SSIS but I just don't know the right solution.


texts: I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and actually delete the rows from disk when no clients are accessing the table, or is the point delaying removal of visibility?


texts: Is it safe to rely on auto increments even after restoring data?

texts:
I plan of using auto increments as a primary key on one of my table.

I need to store data related to each row in an other storage system and I plan to use each primary key id as the key for the data.

Is it guaranteed that even after the restoration of a pg_dump, each row will still have the same id?


texts: The process cannot access the file because it is being used by another process 2

texts:
I have log shipping enabled on my database. I created a file, with let's say logical name 'AB' of 50 MB at location E:\Databases\AB.ndf. However, I later dropped this file and created another file with logical name 'AB_File' of 10 MB at same location E:\Databases\AB.ndf. Now the server where the backup was moved to is failing with error:

Error:

1) Could not apply log backup file 'some location of a .trn file' to secondary database

2)The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Databases\AB.ndf'.

3) File 'AB_File' cannot be restored to 'E:\Databases\AB.ndf'. Use WITH MOVE to identify a valid location for the file.

I have tried running the following script:

RESTORE LOG [db_name] FROM
DISK =  'E:\Log_Shipping\\db_name_20160817070500.trn'
WITH MOVE 'AB_FILE'
TO 'E:\Databases\AB.ndf',
standby = N'E:\Databases\\db_name_20160818.tuf'

I don't seem to be able to fix this. Please help!

The file location on the backup server is the same as that on primary server. Also, the file-group was dropped and the file names are correct.


texts: NOT NULL if value is true in another column

texts:
I have a similar table to this one

create table my_table
(
id serial,
attribute boolean,
number integer
)

Is there a way to have it force not null in column number IF attribute value is true?

So if a record is saved with attribute value 'true' then number must be given a value.

EDIT:
After some digging I have tried this

alter table my_table
add constraint number_must_have_value CHECK (attribute = 't' and number IS NOT NULL)

It throw constraint is violated by some row, but if I run:

select * from my_table where attribute = 't' and number IS NOT NULL

I retrive 0 rows. So my data seem to be OK?

To force it anyway I try to use

constraint number_must_have_value NOT VALID CHECK (attribute = 't' and number IS NOT NULL)

But can't get the NOT VALID option to work. I get a syntax error. Is it not in the right place?


texts: How do you manually change the 'log_reuse_wait' status in SQL Server?

texts:
I'm in the process of moving several databases to a new server. All the databases have SIMPLE recovery mode, as they are backed up once/day.

It seems that the easiest way to move the databases is to just restore from the .bak files on the new server. I've tested that this works, aside from the fact the logs of the restored databases are locked waiting for a replication that no longer exists.

This query:

USE master;
GO

SELECT
    DB.name [DATABASE],
    DB.recovery_model_desc [RECOVERY MODE],
    DB.log_reuse_wait_desc [LOG WAITING ON],
    CAST(CAST(MF.size AS decimal) * 8 / 1024 / 1024 AS DECIMAL(10,2)) [FILE SIZE GB],
    CASE UPPER(REVERSE(SUBSTRING(REVERSE(MF.physical_name), 0, 4))) WHEN 'MDF' THEN 'DATA' WHEN 'LDF' THEN 'LOG' ELSE NULL END [FILE TYPE],
    UPPER(REVERSE(SUBSTRING(REVERSE(MF.physical_name), 0, 4))) [FILE EXTENSION]
FROM sys.databases DB
INNER JOIN sys.master_files MF ON MF.database_id = DB.database_id
ORDER BY
    [FILE TYPE] DESC,
    [FILE SIZE GB] DESC

produces LOG WAITING ON = REPLICATION for the restored databases.

Since the replication no longer exists, I can't just delete the publications on the new server.

Question. Do I need to delete the snapshot replication on the databases before moving them across? Or can I change this log_reuse_wait status to NOTHING manually?

Some more detail:

Server A has replication enabled. DB A.a is marked for snapshot replication. This rep is run occasionally. Database A.a is in SIMPLE recovery mode, with a full backup taken nightly.

Server B is new and doesn't have replication enabled yet. I have found when I restore A.a to B.a, the restoration works. But the log file is still being held for replication.

I would like the log file on B.a to NOT be marked for replication...


texts:
In a current report, I'm trying to track the migration of a person from department n, to n+1, or to n+k.

My data is split into a row per term (university data).  An example:

dept |  term
------------
   a | 20120
   a | 20121
   a | 20122
   b | 20123 -- <- person moved to new department
   b | 20130
   c | 20131 -- <- person moved to another new department
   a | 20132 -- <- person went back to a department they've already been in

using the query

select
  dept,
  FIRST_VALUE(term) OVER (PARTITION BY dept ORDER BY term) as
from
  table
order by term;

gives me:

dept |  term
------------
   a | 20120
   a | 20120
   a | 20120
   b | 20123
   b | 20123
   c | 20131
   a | 20120 -- <- notice this is the same as the above set of a's

The duplicated a's in the first group and b's in the second group are due to other rows in the data (I'm okay with this, but removing that would be a bonus).  My main problem is I need that final a to be treated as a different group; so basically showing the first each time a cluster appears.


texts: Mandatory and non mandatory attributes for products

texts:
I am trying to come up with a simple way of implementing the following design, but I always end up with something overly complicated.

I am trying to design a database where companies can store products. Each product belongs to exactly one category, and depending on the category, they share some common attributes that I want to be mandatory. They can also have different attributes that are up to the company to define.

As of now, and based on this question I asked last year, my idea is to have the following:

Company table to store everything related to the company (one company can have several products)
Product table to store everything related to the product (one product can be in one category, and can have several features)
Category table just to save the name of the category
Attribute table attributes that can be defined by the company for that product
Product Feature table joint table

With these tables, companies will be able to add their products to a category and add as many attributes as they want.

Now, dependant on the category, I want to make mandatory to define a set of attributes. For example, if we are talking about groceries, and a category would be 'Fruits', I might want the company to always provide the following attributes: color, country of origin. If instead of fruits, the category is 'Cereals' I might want to be always provided with the following: allergens, expire date...

I have thought of two different solutions, which are:

Create a new table for each category that needs these mandatory attributes. This, of course, will be a problem as if a new mandatory attribute is added to an existing category, all previous ones will have no value. Also, every time I add a new category I should define a new table for the attributes.
The other option I have thought of is to simply manage this in the frontend of the application. Once a company adds a product belonging to one of those categories, prompt them to input the mandatory attributes and simply save them as a 'normal' attribute.

I am more inclined towards using the second solution. Using the first one I could end up with a ddbb with dozens of tables, but I would like some input to see if anyone can come up with a better solution.


texts: Getting percentages for each day/category


texts: How to restore a SQL Server 2008 R2 database

texts:
Here's my issue.

I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the SQL Server instance.
If I detach then reattach the database everything works fine, the two windows users are able to connect no problem.
If I detach the database, re-image the system, then re-attach the database the windows users will still be listed as I can see them via Management Studio.
I'd imagine that the attaching and detaching of the database has nothing to do with it since the logins are listed under 'Security' for the SQL Server instance and not the db.
At this point users exist and have rights, but they can not connect unless you delete and re-add the windows users, I believe this is because the SID's are messed up.

Any idea how to 'update' the sid's to reflect the current windows users and not the previous ones?


texts: Unable to install SQL Server Management Studio (SSMS) on server

texts:
We are experiencing persistent errors when attempting to install SSMS on one of our development database servers. We have tried the installers for versions 17.4 and 17.9.1, but the error messages are the same.

Extracts from SSMS-Setup-ENU_20190319131949.log (17.4)

10C0:10B8][2019-03-19T13:28:23]i000: MainViewModel.OnPackageActionProgress: Percent completed: 50, Overall progress: 65
[1E58:1E70][2019-03-19T13:28:24]e000: Error 0x80070643: Process returned error: 0x643
[1E58:1E70][2019-03-19T13:28:24]e000: Error 0x80070643: Failed to execute EXE package.
[10C0:10B8][2019-03-19T13:28:24]e000: Error 0x80070643: Failed to configure per-machine EXE package.
[10C0:10B8][2019-03-19T13:28:24]i000: MainViewModel.OnPackageAction: Install Completed for package Microsoft Visual Studio Tools for Applications 2015 Language Support
...
[10C0:10B8][2019-03-19T13:33:28]e000: MainViewModel.OnBundleAction: Bundle action failed: Fatal error during installation (0x80070643)
[10C0:10B8][2019-03-19T13:33:28]i399: Apply complete, result: 0x80070643, restart: None, ba requested restart:  No
[10C0:1C40][2019-03-19T13:33:42]i000: MainViewModel.OpenUrl: Opening url: C:\Users\******~1\AppData\Local\Temp\SsmsSetup\SSMS-Setup-ENU_20190319131949.log

Extracts from SSMS-Setup-ENU_20190319134134.log (17.9.1)

[16C4:2298][2019-03-19T13:49:35]i000: MainViewModel.OnPackageActionProgress: Percent completed: 50, Overall progress: 67
[2324:1D80][2019-03-19T13:49:35]e000: Error 0x80070643: Process returned error: 0x643
[2324:1D80][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to execute EXE package.
[16C4:2298][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to configure per-machine EXE package.
[16C4:2298][2019-03-19T13:49:35]i000: MainViewModel.OnPackageAction: Install Completed for package Microsoft Visual Studio Tools for Applications 2015 Language Support
[16C4:2298][2019-03-19T13:49:35]i319: Applied execute package: VSTALS2015, result: 0x80070643, restart: None
[16C4:2298][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to execute EXE package.
[2324:1D80][2019-03-19T13:49:35]i351: Removing cached package: VS2015KB3095681Update, from path: C:\ProgramData\Package Cache\02A26E554FBB4232ACD36E70D09F2C7893D399CD\
...
[16C4:2298][2019-03-19T13:55:14]e000: MainViewModel.OnBundleAction: Bundle action failed: Fatal error during installation (0x80070643)
[16C4:2298][2019-03-19T13:55:14]i399: Apply complete, result: 0x80070643, restart: None, ba requested restart:  No
[16C4:1798][2019-03-19T13:56:28]i000: MainViewModel.OpenUrl: Opening url: C:\Users\******~1\AppData\Local\Temp\SsmsSetup\SSMS-Setup-ENU_20190319134134.log

The installer takes many minutes to arrive at this point, but fails with the same result each time.

We have restarted the server in between each attempt, and have run on the latest Windows Updates. We have .NET Framework 4.5.2 installed on the server. All attempts have been made using accounts with full admin permissions on the server.

Can anyone please advise how we can get this installed successfully?


texts: How large should my pool size be? And should I use pgbouncer?

texts:
I have a Rails application with 4 Unicorn workers. How do I determine what postgresql database pool size I should use?

And if I have 6 applications connecting to this postgres database, would that be too many connections? And should I use pgbouncer instead?  If so, why?


texts: Big Amount of  in Buffer Pool's Database Pages

texts:
We were viewing sys.dm_os_buffer_descriptors using below query

select
 d.[name]                                       [Database_Name],
 (count(file_id) * 8) / 1024                    [Buffer_Pool_Size_MB],
 sum(cast(free_space_in_bytes as bigint)) / 1024 / 1024     [Free_Space_MB]
from sys.dm_os_buffer_descriptors b
    join sys.databases d on
        b.database_id = d.database_id
group by d.[name]
order by [Buffer_Pool_Size_MB]

And for one of my databases, it shows [Buffer_Pool_Size_MB] = 77325 Megabytes, and [Free_Space_MB] = 15849 Megabytes

So its about 20% of space in pages in buffer pool is empty. Seems like a waste of resources

Questions:

Is this a problem ?
How can number of free_space_in_bytes be mitigated ?
Any other things to investigate / look at in our situation ?