How can I create a readable/writable clone of a log shipped standby/read only database? I need to take adhoc copies of a development database (on SQL Server 2005) to another SQL Server and be able to read/write to that cloned database. As the SQL Servers are in different data centres, copying the 20GB database I'm working with and then restoring is pretty slow. I have therefore opted to use log shipping to get an up to date copy of the data on the destination server. I now need a method to clone the log shipped read only database to a database I can read/write.

How can I copy a log shipped database to writeable database and not break the log shipping chain?

Using various sources (see list at the bottom) I have created two stored procedures that allow me to do what I need using something similar to:

EXEC sp_copy_ls_database
  @restore_job='LSRestore_server1_LOG_SHIPPED_DB',
  @database='LOG_SHIPPED_DB',
  @dest_database='LOG_SHIPPED_DB_COPY'

Where:

@restore_job - the name of the SQL agent job that is responsible for applying shipped logs on the log shipped database (the stored procedures disables this job while the clone is made and then enables it again).

@database - the name of the log shipped database to clone.

@dest_database - the name to give the cloned database.

Note: I don't think this is 100% fool proof as I believe SQL Server starts processing an agent job before updating it's internal tables and therefore sp_wait_for_running_job may miss the start of the job. Depending on the amount of data change and log ship frequency, this should still be fine for non-production databases.

Here's the SQL to create the stored procedures:

USE master
--SP to determine if a named SQL Agent job is running and wait for it to complete if it is
CREATE PROCEDURE sp_wait_for_running_job
   @JobName as varchar(100) = Null
AS
CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
                    last_run_date         INT              NOT NULL,
                    last_run_time         INT              NOT NULL,
                    next_run_date         INT              NOT NULL,
                    next_run_time         INT              NOT NULL,
                    next_run_schedule_id  INT              NOT NULL,
                    requested_to_run      INT              NOT NULL, -- BOOL
                    request_source        INT              NOT NULL,
                    request_source_id     sysname          COLLATE database_default NULL,
                    running               INT     NOT NULL, -- BOOL
                    current_step          INT              NOT NULL,
                    current_retry_attempt INT              NOT NULL,
                    job_state             INT              NOT NULL)

-- Am I sysadm and who am I
DECLARE @is_sysadmin AS INT
DECLARE @job_owner AS NVARCHAR
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

-- Loop until the <job> stops
WHILE 1=1
BEGIN
 -- Clear out temporary table on each pass
 TRUNCATE TABLE #xp_results

 -- Populate the temporary table with job details from Extended SP xp_sqlagent_enum_jobs
 INSERT INTO #xp_results
     EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

 -- If the <job> is running then break out of the loop, otherwise wait for 10 secs
 IF (SELECT COUNT(*)
     FROM #xp_results xpr
     LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
     msdb.dbo.sysjobs_view sjv
     WHERE (sjv.job_id = xpr.job_id)
     AND step_name = @JobName and job_state = 1) = 0
  BREAK
 ELSE
  WAITFOR DELAY '00:00:10'
END

--SP to clone a log shipped database to a new read/write database without impacting log shipping
CREATE PROCEDURE sp_copy_ls_database
    @restore_job AS NVARCHAR(128),
    @database AS NVARCHAR(128),
    @dest_database AS NVARCHAR(128)
AS
DECLARE @mdf_location AS NVARCHAR(max)
DECLARE @log_location AS NVARCHAR(max)
DECLARE @dest_mdf_location AS NVARCHAR(max)
DECLARE @dest_log_location AS NVARCHAR(max)

--Get log shipped database MDF file location
SELECT @mdf_location=physical_name FROM sys.master_files
WHERE database_id=(SELECT DB_ID(@database)) AND type=0

--Get log shipped database LOG file location
SELECT @log_location=physical_name FROM sys.master_files
WHERE database_id=(SELECT DB_ID(@database)) AND type=1

--Determine destination database MDF file
SET @dest_mdf_location=LEFT(@mdf_location,LEN(@mdf_location) - charindex('\',reverse(@mdf_location),1) + 1) + @dest_database + '.mdf'

--Determine destination database LOG file
SET @dest_log_location=LEFT(@log_location,LEN(@log_location) - charindex('\',reverse(@log_location),1) + 1) + @dest_database + '_log.ldf'

DECLARE @q AS NVARCHAR(max)
DECLARE @cmd AS VARCHAR(255)

--Disable the LS restore job
RAISERROR('Disabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=0
--Wait for the job to finish running if is running
RAISERROR('Waiting for log shipping restore job to complete: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC sp_wait_for_running_job @restore_job
--Set log shipped DB offline
RAISERROR('Setting log shipped database %s to offline mode', 0, 1, @database ) WITH NOWAIT
SET @q = 'ALTER DATABASE [' + @database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
EXEC(@q)

--Drop placeholder DB if it already exists
IF EXISTS(SELECT name FROM sys.databases WHERE name = @dest_database)
BEGIN
    RAISERROR('Dropping existing database: %s', 0, 1, @dest_database) WITH NOWAIT
    SET @q = 'DROP DATABASE ' + @dest_database
    EXEC(@q)
END

--Create new placeholder DB
RAISERROR('Creating placeholder database: %s' , 0, 1, @dest_database) WITH NOWAIT
SET @q = 'CREATE DATABASE ' + @dest_database + ' ON (NAME = ' + @dest_database + ', FILENAME = ''' + @dest_mdf_location + ''') LOG ON (NAME = ' + @dest_database + '_log, FILENAME = ''' + @dest_log_location + ''')'
EXEC(@q)

--Set placeholder DB offline
RAISERROR('Setting placeholder database %s to offline mode', 0, 1, @database) WITH NOWAIT
SET @q = 'ALTER DATABASE [' + @dest_database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
EXEC(@q)

--Delete placeholder database files
SET @cmd = 'DEL "' + @dest_mdf_location + '"'
RAISERROR('Deleting placeholder database MDF file: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;
SET @cmd = 'DEL "' + @dest_log_location + '"';
RAISERROR('Deleting placeholder database LOG file: %s', 0, 1, @dest_log_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;

--Copy the log shipped DB's MDF file
SET @cmd = 'COPY "' + @mdf_location + '" "' + @dest_mdf_location + '"';
RAISERROR('Copying the log shipped database MDF file to: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;

--Copy the log shipped DB's LOG file
SET @cmd = 'COPY "' + @log_location + '" "' + @dest_log_location + '"';
RAISERROR('Copying the log shipped database LOG file to: %s', 0, 1, @dest_log_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;

--Put the copied database online
SET @q = 'ALTER DATABASE [' + @dest_database + '] SET ONLINE'
RAISERROR('Setting copied database %s to online mode', 0, 1, @dest_database) WITH NOWAIT
EXEC(@q)

--Set log shipped DB online
SET @q = 'ALTER DATABASE [' + @database + '] SET ONLINE'
RAISERROR('Setting log shipped database %s to online mode', 0, 1, @database) WITH NOWAIT
EXEC(@q)

--Enable the LS restore job
RAISERROR('Enabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=1

Sources:

General overview: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/backup-and-copy-warm-standby-log-shipped/
Path from file name:
https://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path
Wait for a job to complete:
http://www.sqlservercentral.com/Forums/Topic99401-8-1.aspx#top
Set DB offline immediately
https://stackoverflow.com/questions/808232/extreme-wait-time-when-taking-a-sql-server-database-offline


Getting VLF info with least privileged to run across multiple servers of different version Per our monitoring scripts i am thinking to add monitor the status of VLF for each database across different SQL servers in our organization:
We have mix of servers from 2012 to 2019: I've found a really good script here https://www.sqlserverscience.com/tools/detect-databases-high-vlf-count/ but there is a problem with which i need some inputs on how to achieve above
The login which executes any scripts across those 100's of SQL server is not a sys-admin. I know there is  a way here https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate?view=sql-server-ver15 -- but this seems tedious as i think it would need a change across the servers where i would like to run the script
Is there a way i can execute the script with non sys admin privilege to get the VLF info from SQL servers 2012+?
Thanks

I don't think you'll find a way prior to 2016.
As of 2016, you can use the DMV sys.dm_db_log_info instead. In fact, your script does just that, checks for the version and uses one or the other command (the other of course being DBCC LOGINFO, which requires sysadmin).
For sys.dm_db_log_info you can just GRANT VIEW SERVER STATE permission.


how innodb_ft_result_cache_limit work? Quote from MariaDB Documents

Description: Limit in bytes of the InnoDB FULLTEXT index query result cache per fulltext query. The latter stages of the full-text search are handled in memory, and limiting this prevents excess memory usage. If the limit is exceeded, the query returns an error.
Default: 2000000000

I want to know if for example both queries run FTS query in parallel, MySQL Allocate about 1907MB to per query (if query wants) ?
Because we have to many FTS query and all are complex query which lead to growing use of RAM.
Update 1
This is one our query:
select id,title,content,time,c_count,agency_name
    from news join agency use(id)
    where time >= '2016-10-01 00:00:00'
      and time <  '2016-11-01 00:00:00'
      and match("content") against('+("Hello" "Hi" "Hey")+("World" "All" "guys")'
            in boolean mode)
    order by c_count desc

I wrote a bug report to complain about the default value of that setting.


What is a proper way to handle a "sequence number" like field? The fields of a trimmed-down version of a database I'm working with looks like this:

Model Number, Model Name, Customer, Date, SeqNum

The customer can purchase the exact same item on the same date so we're going to use a sequence number to distinguish the rows. So entries might look like:

AS-23423, Box, John Doe, 01/22/2013, 00
AS-23423, Box, John Doe, 01/22/2013, 01
AS-23423, Box, John Doe, 01/22/2013, 02
AS-23445, Pen, John Doe, 01/22/2013, 00
AS-23454, Case, John Doe, 01/22/2013, 00
AS-23423, Box, Mary Lyn, 01/22/2013, 00
AS-23423, Box, Mary Lyn, 01/22/2013, 01

How should you handle the sequence number if an application has to be CRUD compliant? I've considered doing MAX(SeqNum) + 1 upon inserting but this seems off especially when handling update and delete, so I figured there might be a better way.

In Access, this is more about code than design, unless you decide instead on an autonumber, which provides the required unique field and is much easier and safer. Otherwise, in https://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1/11950647#11950647, you will find the following notes:

This requires a reference to the Microsoft ActiveX Data Objects x.x Library

Sequential Numbers

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String

    lngResult = 0  'assume fail

    strCon = "Provider=" ''Connection to back-end
    cn.Open strCon

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseServer

    ''Where BEInfo is a single line table
    strSQL = "SELECT ASeqNumber FROM BEInfo"

    rs.Open strSQL, cn, , , adCmdText

    'Note this is ADO, so no rs.Edit
    rs!ASeqNumber = rs!ASeqNumber + 1
    rs.Update

    lngResult = rs!ASeqNumber

    ''This should not happen, but just to be sure
    If DCount("ASeqNumber", "Table", "ASeqNumber=" & lngResult) > 0 Then
        lngResult = 0
    End If


Analyse and report disk usage growth historically Is it possible to use historic information to show disk usage growth?

I haven't keep much in the way of database setup and disk usage information. So I can't say how much disk growth has occurred in the last 6/12/18 months.

Can I reconstruct that picture from info stored somewhere in SQL Server?

If your DBs reside on a SAN or other shared storage that platform may have logs you can reference.  For local storage I know of nothing held by default.

A couple of years ago we started tracking DB size and disk free/used space. It has proved very valuable.  A couple of Powershell scripts, a scheduled task and a small table has helped us avoid several incidents.  Eventually we had enough data to make Monte Carlo prediction workable.


Need help in designing a database with a need of adding new columns or tables dynamically I want to design a vaccination chart for children.

Each child will have his own schedule and history of administered dates.

I am planning to store the administered dates of each vaccine right in the table of children, with each column storing the storing the administered dates of each vaccine. ( like child_chart(child id, vaccine1_administered_date, vaccine2_administered_date, ...)

Now if a new vaccine is found and to be added to chart, I need to make a new column dynamically.

Is it the only way or can I do it in any other way without dynamically creating a column.

This is my first project using databases. Please help me. Thank you

You have the wrong table structure planned.

Tip: Naming a series of columns on a table with a sequential number (foobar1, foobar2, foobar3) is often a sign of incorrect database design.

Many-To-Many

You have children and you have vaccines as entities. Each should be represented by a table.

Furthermore, you have a relationship between those two entities. Each child can get a dose of zero, one, or more vaccines. Each vaccine may be given to zero, one, or more children. That means these two entities have a Many-To-Many relationship.

A Many-To-Many relationship is always a problem. The solution is always a third table that bridges the other two tables, representing the relationship between them. The bridging table always has at least two fields; as a child table, it has a foreign key to each of the parent tables.

In your case, the bridging table would be named something like dose, tracking which kid got a dose of which vaccine. And in your case, the table would have a third field, the date when the dose was given.

To present this data together, you perform a database join to extract various columns from the various tables where rows match up the foreign key on the child table (dosage) to the primary key of each the parent tables (patient & vaccine).

For more discussion of Many-To-Many, search for the canonical example: Books, Authors, and a bridging table named something like Authorship.  Another example is employees being assigned to teams/projects. Yet another example is music albums where an album is categorized one or more genres, and each genre can have many albums assigned to it, as discussed in this Question.

Also search to learn more about how to separate such data into tables, a process known as database normalization.

  Each child will have his own schedule and history of administered dates.

This Answer addresses only the “history of administered dates” portion of your problem, not the scheduling.

  This is my first project using databases.

Your problem of vaccination records is not simple. As a beginner, I suggest working on a simpler problem.


Defrag a HEAP by creating clustered index and immediately dropping it I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it.  (It's someone else's app and I don't want to make any permanent changes - they did okay this, though.)

ALTER TABLE my_heap ADD dummy BIGINT IDENTITY;
GO
CREATE CLUSTERED INDEX ix_dummy ON my_heap (dummy);
GO
-- need to wait here until index completes
DROP INDEX ix_dummy ON my_heap;
ALTER TABLE my_heap DROP COLUMN dummy;
GO

When I run individual steps of the script, it works fine.  When I run it all at once, nothing seems to happen (frag levels don't go down).  I believe it's because the DROP happens before the CREATE CLUSTERED has finished - so the CREATE CLUSTERED doesn't have time to do its stuff.

How do I make the DROP wait for the CREATE CLUSTERED to finish? I'm doing this for about 30 tables, and so I'd rather not put in a simple WAITFOR DELAY 15 minutes for each one.

PS Using SQL Server 2005

How do I make the DROP wait for the CREATE CLUSTERED to finish?

You don't, it already does. The statements are sequential, i.e. synchronous, one after the other.

What is heap storage? Paul Randal explains

So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom right corner, collates the pages in sequential order, binds them, and then... unbinds and throws them back onto the desk again in a heap.  The total achievement is near zero.  Sure you will regain some usage from tidying up and removing forwarded pages, the data pages may be a tad bit more compact.  Other than that, it's just as messy as you started.


Tracking changes on a replication slave I suspect the answer to this is "you're boned", but I'd like to see if there are any good options that I haven't explored.

We're building a system that will integrate with a 3rd-party POS system and display tickets on boards at various stations in a kitchen. We don't have direct access to the POS system's database; rather, the database is replicated to another server that we can read data from.

In order to send new tickets to the clients, or change/remove modified tickets, we need to watch this database for changes. If we were running it all on a single database server, I'd add a few change logging triggers to the tables in question (which total a couple GB), then poll that log table every 5-10 seconds to see which tickets need to be rebroadcast.

Unfortunately, we're dealing with this replicated system. I know that I can create triggers on the slave, but they'll only run for operations that are logged in statement format rather than row format. The master server is configured for mixed binlog format, and looking through the list of operations that are unsafe to log in statement format, I'd be surprised if none of them are being used on the POS tables. We absolutely can't have tickets get missed due to the trigger not being run on the slave.

These POS tables also don't appear to have any update timestamp columns on them, so I can't simply query rows that have been updated since the last polling time (not to mention the possible race conditions we'd have to address there). Further, these tables are much too big to snapshot and diff them (or a relevant subset of their columns) at every polling.

Oh, and I'm guessing it would be way too much work to connect to the master directly pretending to be a slave and reading the binlog content directly. So, anybody have any clever tricks I haven't thought of before we tell the client we can't do it with what we have? (They would probably be comfortable letting us access the master server, but it's the application vendor that's cagey about it. The client owns the server and data, and we could certainly force our way in, but I don't want to void any contracts and leave them really screwed.)

If you can modify the configuration of the slave, then the solution would be to fairly straightforward: turn on log_slave_updates on the slave, which will result in the writing everything changed on the slave via replication events into the slave's own binlog, which you could then "sniff" and decode every event as it occurs, by connecting to the slave as "your" master and reading from the replication stream that it generates.

Further, changing the slave's binlog_format to ROW will cause the slave to translate any statement-based events from the master so that they will be written to the slave's binlog (and replicated to you) as row-based events, which are dramatically simpler to process from the replication stream.

Once you have that, there are numerous projects in various languages that can spoof a slave connection and decode a replication stream, although in my case, I ended up writing my own parser because I wanted to genuinely understand the low-level nuts and bolts of the implementation well enough that I could thoroughly troubleshoot it as well as extend it... because with each major version iteration from MySQL Server 5.1 → 5.5 → 5.6 (I don't have anything older in my networks), new capabilities in replication have been introduced that will break older code that fails to understand the new structures.  This, incidentally, is why you always upgrade the slave first, when doing version upgrades, since new slaves understand everything an old master can send, but not the other way around.  Some of the open source projects that accomplish this will not work with newer versions of MySQL or will require non-default configurations, because of these changes.

I also wanted support for the compressed client/server protocol, which can significantly reduce the number of bytes sent over the wire in replication events, but which didn't often seem to be as much of a priority to some of the other projects.

Sniffing the replication stream has been very useful in my operation, since I really hate polling.  The one caveat I would point out that you might not expect is the fact that foreign key cascade events are not explicitly replicated, ever.  MySQL itself is apparently completely unaware when InnoDB performs foreign key operations on the child tables as a result of updates or deletes on the parent table.  I never thought about precisely which "layer" was doing this before, but it makes sense... if foreign keys weren't happening strictly internal to InnoDB, then there's no reason why MyISAM wouldn't have them by now... and there would also be no reason why foreign keys cascades couldn't activate triggers, but that too makes sense in this light, since triggers happen at the MySQL layer, not the storage engine layer... and this also explains why cascade actions are not reflected in the "rows affected" count.  The rows changing on the parent table are replicated, and on a normal slave, the foreign key cascades are handled internally by InnoDB magic... but that's the only unexpected thing I've encountered in the last couple of months of using this in live (back-end, not end-user-facing) systems.


Are their any default privilege in Oracle for users? I want to grant new users in Oracle the normal rules, for example, create a table, delete table update, in general, the default CRUD operations.
Every time I do this manually by running a grant script. Does Oracle come with default privilege group, for instance, create a user/schema with developer rules,
create a user with DBA rules, without using the grant statement?

You can create a role and grant privileges to that role and in turn grant that role to a user.

To grant dba privileges to a user grant dba to someuser

You can explore default/current roles that gives you an idea.

select grantee role,privilege
from dba_sys_privs
where grantee not in (select username from dba_users)
-- and grantee in('DBA','CONNECT','RESOURCE')
group by grantee,privilege
order by 1

or

 -- dba_roles data dictionay lists all roles existing/created in Oracle
    select grantee role,privilege
    from dba_sys_privs
    where grantee in (select role from dba_roles)
    group by grantee,privilege
    order by 1

Keep in mind you have to be very careful before giving blanket privileges.


Installing MS SQL Server on Centos Has anyone been able to install SQL Server on Centos 6? I am running Centos 6.8, and I can only find information on Centos 7.

I know that SQL Server on Linux is still in its early stages. I’m curious to see how it runs.

I installed developer edition on centos 7(Although, it was for RHEL), by referring : https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017. Didn't get any difficulty in installation. I can use it now. documentation is good.


"Log file for database is full" running an INSERT statement I'm getting the following error message

  The log file for database 'scratchdb' is full. Back up the transaction log for the     database to free up some log space.

when I'm trying to insert new records into my temp table (there is ~1.3 mil rows) to check that it does not exist.

INSERT INTO SCRATCHDB..members_temp
SELECT M.*
FROM SCRATCHDB..members_temp D (NOLOCK) RIGHT OUTER JOIN
(SELECT * FROM PROD..Member (NOLOCK)
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()) AS M
ON M.MemberNo <> D.MemberNo

Is there a better/efficient way to do this?

Did you try to run this select? I guess it outputs a LOT of records. For example if M table contains 1000 records it outputs 1000 * 1.3M = 1.3 BILLION records (excepts a few with M.MemberNo=D.MemberNo). So in this case increasing a LOG file has no sense. Try to run this query:

INSERT INTO SCRATCHDB..members_temp
SELECT * FROM PROD..Member (NOLOCK)
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()
AND MemberNo NOT IN (select MemberNo from SCRATCHDB..members_temp (NOLOCK));


how drivers work in sql server We have different drivers like ODBC, OLE DB, JDBC. I understood when we want to work with Excel in SQL Server, we need to load different driver. But my question is why we need different drivers, what work they do? Will drivers be at both client and server end? Suppose I am running SQL query from my local machine, will there be drivers on my machine? So far my search ended here, any info would be greatly appreciated.

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-jdbc.html

Drivers enable specific communication methods between applications.  Some communication methods are very generic (such as OLEDB) and work with a variety of applications.  Others are specific and provide interaction only between one application type.

SQL Server ships with a few drivers which are usually enough to enable it to communicate with other databases as required.  Additional drivers give additional capabilities.  For example, in my environment I have installed Oracle drivers so that some of our SQL Servers can communicate directly with an Oracle database server.

For your second question about where the drivers need to be installed, that depends on where the work is taking place.  If you are writing an application to run on your local client machine to work with Excel spreadsheets then you need the Excel driver on your local client, because that is where the work is taking place.

But I do not recommend having SQL Server work with Excel documents.  You should write something that will extract the data from excel (using the excel drivers) and then push that data to SQL Server (using ODBC or OLEDB drivers).  The same process could work in reverse.


mongodb: drop all collections How to remove existing content before entering a replicaset the first time?

mongodb worries about:

  ... has data already, cannot initiate set.  All members except initiator must be empty.

db.dropDatabase() will drop the database, which will also drop all of the collections within a database.

If you need to see what databases you have, you can do show dbs.

Update: Here's a script to delete everything, make sure you really want to do this:

var dbs = db.getSisterDB('admin').adminCommand("listDatabases").databases
for(d in dbs) db.getSisterDB(dbs[d].name).dropDatabase();


Is there a better way than this to split a string and get the first substring? I'm trying to cut off everything after a certain character (in this case '&') and if there is no occurence of that character, get the entire string.

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

This seems to be a rather needlessly complicated way of doing it.   Is there a better/shorter way?

Here is a full test case to indicate what I'm doing...

declare @str as varchar(255)
set @str = 'abc&def&ghi'

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

-- Returns 'abc'

set @str = 'abc'

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

-- Returns 'abc'

Well - here is 'another' way...

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&def&ghi'

SELECT CASE
        WHEN charindex('&', @str) > 0
            THEN substring(@str, 1, charindex('&', @str) - 1)
        ELSE @str
        END AS SelectedString
-------------------------------
SET @str = 'abc'

SELECT CASE
        WHEN charindex('&', @str) > 0
            THEN substring(@str, 1, charindex('&', @str) - 1)
        ELSE @str
        END AS SelectedString

OR - you could simply ALWAYS concatenate '&' at the end of the string like this:

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&def&ghi'

SELECT substring(@str, 1, charindex('&', @str + '&') - 1)
-------------------------------
SET @str = 'abc'

SELECT substring(@str, 1, charindex('&', @str + '&') - 1)


NT SERVICE\MSSQLSERVER cannot be found to add as user for file permissions I need to give the NT SERVICE\MSSQLSERVER account permissions on a folder to be able to move and rename some files. I'm getting an access denied error. From the SQL Configuration tool, the services is set up like the below image. What else has to be done to make this appear as a user account?

What else has to be done to make this appear as a user account?

Nothing.  And it doesn't matter what your service account is.  The SQL Server service always has privileges assigned to the per-Service SID "NT Service\MSSQLSERVER".
If the files are on the SQL Server, just add permissions for this account:

And if the files are on a remote share, give the permissions to the machine account instead, eg <YourDomain>\,<YourServer>$.


Best way to defrag/compact a database for archival purposes We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this quarterly in the past, but we're looking to do it monthly now. The amount of data being archived is about 15 - 20 GB per month, and the bulk of the data resides in only a handful of tables (usually 2 - 4).

Once we roll over to a new database, the old one becomes used on a strictly read-only basis. What I'd like to do is optimize it into a nice, tight data file, with all the tables/indexes contiguous and having a very high fill factor, and not much empty space at the end of the data file. Also, we're using Standard Edition on this server, with all the limitations that implies (otherwise I'd be using data compression already).

A few possibilities I can think of:

REBUILD/REORGANIZE indexes, DBCC SHRINKFILE (Okay, this isn't a sensible option, since DBCC SHRINKFILE will fragment the piss out of anything it touches, but I'm including it for completeness.)
Create a new database with auto-stats off. Script and recreate all tables from the source database. Use bcp to export/import the data into the new database, in cluster-key order. Script and recreate all indexes. Recalculate all statistics with full scan.
Create a new database with auto-stats off. Script and recreate all tables from the source database. Use SSIS or T-SQL to transfer data to the new database. Script and recreate all indexes. Recalculate all statistics with full scan.

The final step in every case would be setting the database to read-only mode.

What other good/better options are there for doing this? My concern is moving the data over in such a way to preserve a high fill factor, and in a logically contiguous fashion.

Edit:

I should mention that about 75% of the data seems to be stored in image (LOB) columns.

To eliminate the physical fragmentation in the files you could as move the clustered index with drop existing to a new filegroup. As they are going to be RO make them all fillfactor 100% as no space needed for inserts, page splits caused by updates.

This would also allow you to perform a piecemeal restore and bring the database online very quickly if you decided to ever go to Enterprise. Enterprise also allows columnstore indexes in addition to massively reduce query time for this Read Only data, which is massive fillet.

You can use the shrinkfile option once before switching to read only without any serious issues with fragmentation to remove the space at the end of the file as you desired.

On a side note, just checking that you are using the latest Datatypes for your LOBS.
  i.e. nvarchar(max) or varchar(max) instead of ntext or text, varbinary(max) instead of image?


Is there any way to disable MongoDB's cache? I'm not getting recent data when querying, and I believe it has to do with cache.
Probably, MongoDB's cache invalidation fails somewhere. I'd like to disable it completely and I don't care about performance.
Getting recent data using a query like this:
await Sessions.find({ ip: ip}).sort({ receive_time: -1 }).lean().limit(1).exec();

Mongo database service doesn't search for answers to your query in only the cache. It searches in the memory and if it doesn't find your answers, it has to go into the storage.
You can try the query below, and you will see the last data on your table.
db.zips.find().sort({ "_id": -1 }).limit(10)

Check out your data and your query, probably your condition is wrong.


Two array_agg at once I'm trying to get all likes/dislikes of multiple post.
The problem can be summarized as:

posts table: id, content, user_id (fk)
users table: id, name
likes table: post_id (fk), user_id (fk), is_liked, is_disliked

To fit my use case, I need to get the list of all likes/dislikes so I've first tried this:

SELECT
    posts.id, posts.content, users.name as author
    ARRAY(SELECT user_id FROM likes WHERE is_liked and post_id = posts.id) as likes,
    ARRAY(SELECT user_id FROM likes WHERE is_disliked and post_id = posts.id) as dislikes
FROM
   posts
   INNER JOIN users ON posts.user_id = users.id

This is clearly not optimized. That's why I've tried something with another join on likes table and the array_agg function but I can't figure out how to get two different arrays for likes and dislikes.

That should be simple with array_agg and additional FILTER clauses:

SELECT
    posts.id, posts.content, users.name as author
    array_agg(likes.user_id) FILTER (WHERE likes.is_liked) as likes,
    array_agg(likes.user_id) FILTER (WHERE likes.is_disliked) as dislikes
FROM posts
   INNER JOIN users ON posts.user_id = users.id
   INNER JOIN likes ON likes.post_id = posts.id
GROUP BY posts.id, posts.content, users.name;


Execute job every time database changes - execute job on each change-event I have a transactional-replication running in my database, and I need to execute a service on my website that refreshes the cache every time the database changes.

So i guess i have two options to make it work.

Execute job when transactions occur
Execute a job on every database change

But i have no idea how to do either of those.
Is it even possible? I have been looking around and it seems that the "on-transaction" event, is not really possible because the replication agent is not starting and stopping, it's just running the whole time.

And executing a job on every database change should be possible, but again, never tried it before, but i really would bet money on it being possible.

Anyone know how this can be done?
Thanks in advance :)

EDIT:

I have two sets of servers, two webservers and two database servers.
The db-server has transactional replication to the other db.

Each webserver has it's own website running with some pretty deep caching.
I need to trigger a cacherefresh webservice on the webserver so that the replicated changes will be visible to users.

Yesterday after writing this question, i set up a scheduled job to refresh the cache, and if this works wothout any hicks, i would rather keep that solution going, but if not i will need to trigger the refresh some other way.

Thank you :)

In sql server  database level triggers comes in handy like this situation ,  you can call job to start and leave   inside the trigger like

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    EXEC dbo.sp_start_job N'jobname'
END


Why does pg_last_xact_replay_timestamp() sometimes return extremely old timestamps? In PostgreSQL 9.5, to monitor streaming replication lag, I use a combination of:

pg_current_xlog_location()
pg_last_xlog_replay_location()
pg_last_xact_replay_timestamp()

The first two functions are used to determine if there is any lag between the master and standby. If there is some lag, then I determine it with:

select now() - pg_last_xact_replay_timestamp();

Mostly it returns reasonable amounts -- seconds to minutes of lag.
However occasionally, that statement will return unlikely, large values. Several days of lag.

We don't have any transactions in our system that run for that long, not even close.

Can anyone suggest what is going on here?

Answering this from the future - when I have seen this, it means there have not been any writes to the primary (upstream) database in a while.
If there are no recent transactions on the primary, there are no recent transactions to replay on the follower, so pg_last_xact_replay_timestamp() will just report the time of the last write to primary.


Select another row if NULL In a MySQL database I have these tables:

Table 1: News

news_id int (PK)
category int (FK)
rating tinyint

Table 2: News Detail

news_detail_id int (PK)
news_id int (FK)
language_code char
news_title varchar
news_detail text

Sample data;

News

news_id  |  category  |  rating
--------------------------------
   1     |     1      |    3
   2     |     3      |    4
   3     |     2      |    5
   4     |     1      |    1
   5     |     2      |    5

News Detail

news_detail_id  |  news_id  |  lang_code | news_title | news_details
--------------------------------------------------------------------
      1         |     1     |    EN      | Title1 Eng | Detail 1 Eng
      2         |     1     |    GER     | Ger Titel  | Deutsch detail 1
      3         |     2     |    EN      | Title2 Eng | Detail 2 Eng
      4         |     3     |    EN      | Title3 Eng | Detail 3 Eng
      5         |     4     |    GER     | Ger Titel4 | Deutsch detail 4
      6         |     5     |    GER     | Ger Titel5 | Deutsch detail 5

All news have an English translation. In German language some of the translations are missing. Now when I query all news in German language, I want to get English translated row if German translation is missing.

Expected Output

news_id  |  rating  |  lang_code | news_title | news_details
-------------------------------------------------------------
   1     |    3     |     GER    | Ger Titel  | Deutsch detail 1
   2     |    4     |     EN     | Title2 Eng | Detail 2 Eng
   3     |    5     |     EN     | Title3 Eng | Detail 3 Eng
   4     |    1     |     GER    | Ger Titel4 | Deutsch detail 4
   5     |    5     |     GER    | Ger Titel5 | Deutsch detail 5

Is there a way to accomplish this?

The left-join variant:

select n.news_id, rating,
  coalesce(dg.lang_code, de.lang_code),
  coalesce(dg.news_title, de.news_title),
  coalesce(dg.news_details, de.news_details)
from news n
join news_detail de on (n.news_id = de.news_id and de.lang_code = 'EN')
left join news_detail dg on (n.news_id = dg.news_id and dg.lang_code = 'GER')

http://sqlfiddle.com/#!9/01d421/7 - returns only 3 rows because your sample data does not contain the EN translation for all news as your real data are supposed to.

For completeness, there is a version which picks first available translation (in order 'GER', 'EN') by a dependent subquery - http://sqlfiddle.com/#!9/01d421/9 (in this case it picks the German translation where there is no English one in the sample data, so results differ)


Hardware specs for MySQL database with millions of records For 35 million records I have this table

CREATE TABLE IF NOT EXISTS `records` (
  `code` varchar(12) NOT NULL,
  `type` varchar(255) NOT NULL,
  `used` set('y','n') NOT NULL default 'n',
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The code is alphanumerical and unique. I just have to check if a code exists and whether or not it has been used.

What server specs would you recommend? Any other suggestions on arquitechture? We will probably be using Rackspace so if you have any experience with them that would be great.

Given the above table description, my first order of business would be to get rid of the duplicate index for three reasons:

it would slow down inserts
needlessly bloat the table to about triple the amount on index info
would render either the PRIMARY KEY or the code index useless in EXPLAIN plans

NOTE : A duplicate index is an index that has the same leading columns as another index. You would remove the index if the leading columns has  no additional columns needed by certain queries.

Please run this to discard the duplicate index

ALTER TABLE records DROP INDEX code;

You need to find out how much memory and disk will be need for a given dataset.

Please see my other posts on memory, threading, and InnoDB configurations

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Sep 12, 2011 : Possible to make MySQL use more than one core?


How to completely hide a function from all other users and roles? Database : Postgres 9.6

Use case : I have a function that needs to be hidden, only a particular role/user can see it. I don't even  want to expose even the name or body of function to other users/roles.

For example if I have a user named test1 then he should not have any knowledge of existence of function called top_secret_function_1 (not via PgAdmin not via any other command.)

Only the the users/roles that I want to see this function should see this function.

Importance : This function executes some important logic which I don't want to expose but still I need to give some other database access rights to some other users.

Update 1

Klin suggested use of pg_temp but its does not seems effective but his suggestion triggered a idea of using different schema and then restrict the users/roles to schema.

when i reconnect the database the secret function is no longer valid in pg_temp !
I cannot use pg_temp as suggested because then I will have to define function in each users pg_temp schema when he connects

You can revoke and grant access privileges from/to specific Postgres roles but the name and source of the function will be still visible.

To keep the function in top secret create it in the session in which it is needed, in the schema pg_temp. It is a schema associated with the session for keeping temporary resources. It is automatically created when needed and automatically dropped at the end of the session.

create or replace function pg_temp.top_secret_function()
returns text language sql as $$
    select 'Top secret!'::text;
$$;

select pg_temp.top_secret_function();

CREATE FUNCTION
 top_secret_function
---------------------
 Top secret!
(1 row)

There is no need to drop the function, it will be removed when the session terminates. Nobody (even you as the same user in another concurrent session) can see the function.


SQL Always on availability with failover and mixed authentication I have 2 boxes running SQL Server 2016, and set up using Always On Availability. I have a listener set up to choose which server to talk to in case of a failover, which seems to work fine, except for the fact that I'm using mixed authentication, and my connection string is using a SQL Server authentication user rather than a windows one.

For example, the connection string in my application is:

Database=MySQLServer;Server=AGListener;User Id=sqluser;Password=XXXXXXX;

when I'm working against the primary, this works fine. when I failover to the secondary, then I get an error that 'sqluser doesn't have any permissions to the database'. If i run the below script on the secondary server:

EXEC sp_change_users_login 'Update_One', 'sqluser', 'sqluser'

then it works fine again, but when I failover back to the original primary, I get the error again. it looks like on every failover, I have to run the update user script for the connection string to work.

Is using SQL Server authentication for a connection string in an AG environment just not a good idea? Or is there a step I'm missing? I would like my failover to be automatic, rather than needing to remember to run a script every time.

Either create the login on instances hosting the replicas specifying the same SID as on the instance hosting the primary:

  To Resolve an Orphaned User

  In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login,
  providing the SID of the database user obtained in the previous
  section:

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

From: Troubleshoot Orphaned Users (SQL Server)

Or use a Contained Database User:

CREATE USER user_name WITH PASSWORD = 'strong_password';


PostgreSQL and storing configs The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact.
Lets take currency as an example. We store currency as numeric, but when displayed we want it to be of a certain format, say to_char(_numeric_value, 'FM999G999G999G999G990D00').

The obvious solution is to create a table that stores this format and uses is whenever needed. But since this is very commonly called function we don't want it to be slow.
The solution I came up with is to re-compile format function each time config is changed. I use on-update trigger for that and also store function source-code. This way performance should be the same as if the format was hardcoded, but it doesn't seem to perform as expected.

What are down sides of this solution, am I missing something? Maybe there's an even better solution?

sqlfiddle is down for me currently, so I will post all necessary DDL/DML here.

CREATE TABLE test_config(money_format text);
CREATE TABLE test_config_fncs(fnc_name text, fnc_source TEXT);

--This is function for performance comparison.
CREATE OR REPLACE FUNCTION test_fnc_common_format_money_no_precompile(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, money_format)FROM test_config;
END;
$BODY$
    LANGUAGE plpgsql STRICT STABLE;

CREATE OR REPLACE FUNCTION test_tfnc_config_fnc_precompiler()
    RETURNS trigger AS
$BODY$
BEGIN
    IF (NOT (NEW.money_format = OLD.money_format)) THEN
        EXECUTE
        (
            SELECT
                    replace(fnc_source, '>money_mask<', NEW.money_format)
                FROM test_config_fncs
                WHERE fnc_name='test_fnc_common_format_money'
        );
    END IF;
    RETURN NEW;
END;
$BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER test_tg_config_aiu_fnc_precompile
    AFTER UPDATE
    ON test_config
    FOR EACH ROW
    EXECUTE PROCEDURE test_tfnc_config_fnc_precompiler();
-- DDL DONE
-- INITIAL INSERTS:
INSERT INTO test_config(money_format, date_format) values (null);

INSERT INTO test_config_fncs(fnc_name, fnc_source) VALUES ('test_fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION test_fnc_common_format_money(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, '>money_mask<');
END;
$BODY$
    LANGUAGE plpgsql STRICT IMMUTABLE;
$fnc$);

--TEST QUERIES:
UPDATE test_config SET money_format='FM999G999G999G999G990D00';

SELECT * FROM test_fnc_common_format_money(11111.2222); -- runtime 1700ms per 1 million queries
SELECT * FROM test_fnc_common_format_money_no_precompile(11111.2222); -- 13100ms per 1 million (!)

Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements.

General advice

For simple functions as displayed:

Use simple SQL function (LANGUAGE sql) which can be inlined in the context of an outer query. SQL is preferable for simple functions that are typically nested.

PostgreSQL Stored Procedure Performance

Remove the STRICT modifier. Contrary to what you might expect, this is probably not going to help with simple SQL functions, because it can prevent said inlining. (STRICT may help with expensive functions, though.)

Function executes faster without STRICT modifier?

Multiple users

To accommodate multiple users with different but more or less stable preferences, you could improve your design building on the typical default search_path setting  serach_path = "$user",public.
Create a separate version for each user with the same function name in their respective private schemas. Unless schema-qualified, each user will see his own version of the function fnc_common_format_money(). And they can all use the same queries.

Switch schema

Or, if we are dealing with changing requirements for a single user, you could switch the search_path manually (and an arsenal of functions with it):

SET search_path = "$user", verbose_format, public

verbose_format being one name of a schema with specialized functions. Could also come before "$user".
Related:

How can I fake inet_client_addr() for unit tests in PostgreSQL?

You may not need a regime for dynamic settings any more. If you still do, extend your table test_config with a column username (or schemaname) to store a separate row for each user (schema) and adapt your code accordingly. Make it a bit simpler and safer while being at it:

Code suggestions

For the "multiple users" scenario.

CREATE OR REPLACE FUNCTION fnc_test_config_precompiler()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE (
      SELECT format(fnc_source, NEW.username, NEW.money_format)
      FROM   test_config_fncs
      WHERE  fnc_name = 'test_fnc_common_format_money'
      );

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER test_config_precompile
AFTER INSERT OR UPDATE OF username, money_format      -- !!
ON test_config
FOR EACH ROW EXECUTE PROCEDURE fnc_test_config_precompiler();

...

INSERT INTO test_config_fncs(fnc_name, fnc_source)
VALUES ('fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION %$1I.fnc_common_format_money(_value numeric)
  RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT to_char(_value, %$2L)';
$fnc$);

Major points

Build one function per user setting. (Optional, but the rest of the advice applies in any case.)
Build conditions into the trigger directly and call trigger function only after relevant columns have been changed or on INSERT (this way you do not have a reference to OLD in the trigger function, which would not work with the additional call ON INSERT.
The simple trigger only creates required functions and does not take care of orphaned versions.
Parameter name inside the body of an SQL function requires pg 9.2+.
Use format() to inject (sanitized!) parameters into the CREATE FUNCTION statement. Closing a possible security issue with SQL injection.


SQL Server Availability Group - Listener configuration problem Platforms:

Windows Server 2016
SQL Server 2016
SSMS 2018

Problem:

  Create failed for Availability Group listener 'lsn'.

  None of the IP addresses configured for the availability group
  listener can be hosted by the server 'MACHINE1'. Either configure a
  public cluster network on which one of the specified IP addresses can
  be hosted, or add another listener IP address which can be hosted on a
  public cluster network for this server.

I have three machines:

the first machine runs Active Directory
the second machine is named MACHINE1
the third machine is named MACHINE2.

The machines are nodes for SQL Server.

My machines, user and cluster are added to Active directory in a OU and they have necessary permissions. IPv4 configuration is complete as well.

My Active Directory looks like this:

cluster1 - Failover cluster virtual network name account
MACHINE1
MACHINE2

I couldn't find any clear solution for this problem.

In order for the IP address to be brought online, three requirements must be met (among others, but these are the most relevant to this error):

At least on of the listener IP addresses must be in the same subnet as one of the cluster networks.
The cluster network that is in the same subnet as the listener IP address must be configured as a public network (will add more details in a few minutes).
The IP address must not be in use on the network.

You can view and configure the cluster networks in Failover Cluster Manager.  Expand the cluster and select the Networks node to see the networks.  Right-click on a network and select properties.  Ensure that "Allow clients to connect through this network" is enabled on all networks that are used by listener IP addresses.

If you need further help, please add more detail to the question regarding the network and IP configuration of the nodes and the listener.  The only useful information in your question at this point is the error message, so that's all I have to go on.


Masking or substring data while replicating data I  have a table that has  some  unencrypted data  that needs to be replicated to another server. I want to do :

Mask the data with an x except the last 5 characters e.g.  xxxx12345
just replicate the last 5 characters of the data in the column (substr the last 5 characters)

Is there a way I can do that other than writing a trigger on the subscriber ?

Thanks

R

Yes, you can use custom replication stored procedures to do this.  Custom replication stored procedures are especially useful when an application requires custom logic such as this and can allow the data to be transformed in-flight to Subscribers.

Have a look at the sections Default and custom stored procedures and Considerations for Using Custom Stored Procedures in Specify How Changes Are Propagated for Transactional Articles, and the section To generate and use custom stored procedures in Set the Propagation Method for Data Changes to Transactional Articles.


Keywords - column or separate table I am building a website on  MySQL with php, and I have 400+ products in a table. I have three columns with comma separated values, each has 1-3 values separated by commas in each row.
I was wondering if my db would be faster if I make separate tables for those three columns?

It may or may not be faster, depending on your data distribution and the queries you execute.  What it will be is better.

Normalisation isn't a goal in itself.  It's a series of processes one applies to remove potential errors while changing data.  The output is a schema which is resilient to changing data.  Without normalisation you leave yourself open to inconsistent data.  It really doesn't matter how fast your site runs; if it's wrong it's wrong and it's no less wrong by being wrong quickly.

Separated values could potentially be faster if your current queries retrieve too much information because it is all bundled up together in CSV columns.  It will definitely be faster if, down the line, you discover you have to join to another table on one value currently embedded in a CSV.  At that point your performance will fall off a cliff.


UPSERT with ON CONFLICT using values from source table in the UPDATE part Given:

CREATE TABLE A (
PK_A INT8 NOT NULL,
A INT8,
PRIMARY KEY (PK_A)
);

CREATE TABLE B (
PK_B INT8 NOT NULL,
B INT8,
PRIMARY KEY (PK_B)
);

This query:

insert into table_b (pk_b, b)
select pk_a,a from table_a
on conflict (b) do update set b=a;

causes the following error:

ERROR:  column "a" does not exist
LINE 1: ...elect pk_a,a from table_a on conflict (b) do update set b=a;
                                                                 ^
HINT:  There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query.

How to do the update while referring to content of table_a?

Multiple problems.
Your setup, extended:
CREATE TABLE a (
  pk_a int PRIMARY KEY
, a int
, comment text  -- added column to make effect clear
);

CREATE TABLE b (
  pk_b int PRIMARY KEY
, b int
, comment text
);

INSERT INTO a VALUES (1, 11, 'comment from a')
                   , (2, 22, 'comment from a');

INSERT INTO b VALUES (1, 77, 'comment from b');

This works:
INSERT INTO b (pk_b, b, comment)
SELECT pk_a, a, comment
FROM   a
ON     CONFLICT (pk_b) DO UPDATE  -- conflict is on the unique column
SET    b = excluded.b;            -- key word "excluded", refer to target column

Result:
TABLE b;

 pk_b | b  |    comment
------+----+----------------
    1 | 11 | comment from b   -- updated
    2 | 22 | comment from a   -- inserted

The problems

You are confusing table_a and A in your demo (like @Abelisto commented).
Using legal, lower-case, unquoted identifiers helps to avoid confusion.

Like @Ziggy mentioned, ON CONFLICT only works for actual unique or exclusion constraint violations. The manual:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

Consequently, ON CONFLICT (b) cannot work, no constraint there. ON CONFLICT (pk_b) works.

Like @Ziggy also mentioned, source table names are not visible in the UPDATE part. The manual:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access
to the existing row using the table's name (or an alias), and to
rows proposed for insertion using the special excluded table.

Bold emphasis mine.

You also cannot use column names of the source table in the UPDATE part. It must be column names of the target row. So you really want:
 SET    b = excluded.b

The manual once more:

Note that the effects of all per-row BEFORE INSERT triggers are
reflected in excluded values, since those effects may have contributed
to the row being excluded from insertion.


JDBC connection error for SQL Server Database I have a SQL server names as MS8AT09\AT09. I am able to login to the server through Microsoft SQL server Management studio. While trying to login to same server through JDBC I am facing the error as

  "The TCP/IP connection to host XXXXXX, Port 1433 has failed.  Verify
  the connection properties.  Make sure that an instance of SQL Server
  is running on the host and accepting TCP/IP connections at the port.
  Make sure that TCP connections to the port are not blocked by a
  firewall".

Please help.

It is unlikely that a named instance is running on port 1433, unless you explicitly set it as such. I also suspect that SSMS is using shared memory (if local) or named pipes (if remote), unlike JDBC which is using TCP/IP (and I don't know how to set JDBC to use a different protocol).

From this documentation, it seems your connection string should be:

jdbc:sqlserver://MS8AT09;instanceName=AT09;...

With no port designation. You could also try using the IP address instead of the name, in case name resolution is hampered for some reason.

Please check what port the instance is running on (look at SQL Server Configuration Manager)...

...and confirm your connection string does not hard-code a port that is different (JDBC might ). Do not hard-code the dynamic port, because it may not be the port used next time SQL Server starts. From Microsoft's documentation:

  We recommend that the port number always be specified, as this is more secure than using sqlbrowser.

So, you may want to set your named instance to run on a specific port number, instead of using a dynamic port (the default), and then hard-coding that port number in the JDBC connection string. You can use the one that the instance was using dynamically, since you know it isn't in use by anything else. Also see KB #823938. So your connection string would become (assuming you used port 56737):

jdbc:sqlserver://192.168.0.65;instanceName=AT09;port=56737;...


Replicating only significant records to specific database I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can identify the data using a column id.

i.e if my id starts with dba23 then this data should be replicated to database with the dba23 in this case collection_db.

Am using dblink currently, but I want a real-time solution.

suggestions are highly welcome

You should be able to do this with slony. Slony is an asynchronous (but near real-time) trigger based single-master/multi-slave replication solution for PostgreSQL.

Take a look at the "Retail Store Problem" in this presentation (OpenOffice). This example actually deals with simulating a multi-master replication but the trick used can also be applied in the other direction for your use case:

On the master use table inheritance to route your data (according to the id) from the parent table to a slave specific child table. This can be done with insert triggers or rules.
Replicate the slave specific child table(s) to the slave database.
On the slave use table inheritance again to have the same table name in all databases (parent table only wrapper for slave specific child table).

Instead of table inheritance you could also use INSTEAD OF triggers on views with PostgreSQL 9.1. The little ascii art below should give you an idea of the basic structure of this solution.

db_master                          db_slave_dba23       db_slave_dba17

tbl_parent                         tbl_parent           tbl_parent
|                                  ^                    ^
v                                  |                    |
tbl_child_dba23 --- replicate ---> tbl_child_dba23      |
|                                                       |
v                                                       |
tbl_child_dba17 --- replicate ------------------------> tbl_child_dba17
|
v
...

Be aware that while (under certain pre-conditions) slony can well be used as a production grade replication system, such a complex replication solution can get very cumbersome to maintain and monitor. Especially if DDL statements are involved in your daily database use cases this solution may not be suitable at all.

CAVEATS

The replicated tables are read-only on the slaves
Replication is not transactional (but neither is dblink)
Slony can be a real PITA


Error while installing MySQL on RedHat Linux I am new to RedHat Linux and am referring to this link for the installation :https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html I finished the first two steps and when I try doing Step 3, I get the following error:

Loaded plugins: refresh-packagekit, rhnplugin, security
This system is receiving updates from RHN Classic or RHN Satellite.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.10-2.el6 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.10-2.el6 for package: mysql-community-server-5.7.10-2.el6.x86_64
--> Processing Dependency: mysql-community-client(x86-64) = 5.7.10-2.el6 for package: mysql-community-server-5.7.10-2.el6.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.10-2.el6 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) = 5.7.10-2.el6 for package: mysql-community-client-5.7.10-2.el6.x86_64
---> Package mysql-community-common.x86_64 0:5.7.10-2.el6 will be installed
--> Running transaction check
---> Package mysql-community-libs.x86_64 0:5.7.10-2.el6 will be obsoleting
---> Package mysql-libs.x86_64 0:5.5.47-1.el6.remi will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: php-mysql-5.4.45-3.el6.remi.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_16)(64bit) for package: php-mysql-5.4.45-3.el6.remi.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: php-mysql-5.4.45-3.el6.remi.x86_64
--> Finished Dependency Resolution
Error: Package: php-mysql-5.4.45-3.el6.remi.x86_64 (@remi)
           Requires: libmysqlclient.so.18()(64bit)
           Removing: mysql-libs-5.5.47-1.el6.remi.x86_64 (@remi)
               libmysqlclient.so.18()(64bit)
           Obsoleted By: mysql-community-libs-5.7.10-2.el6.x86_64 (mysql57-community)
               Not found
Error: Package: php-mysql-5.4.45-3.el6.remi.x86_64 (@remi)
           Requires: libmysqlclient.so.18(libmysqlclient_18)(64bit)
           Removing: mysql-libs-5.5.47-1.el6.remi.x86_64 (@remi)
               libmysqlclient.so.18(libmysqlclient_18)(64bit)
           Obsoleted By: mysql-community-libs-5.7.10-2.el6.x86_64 (mysql57-community)
               Not found
Error: Package: php-mysql-5.4.45-3.el6.remi.x86_64 (@remi)
           Requires: libmysqlclient.so.18(libmysqlclient_16)(64bit)
           Removing: mysql-libs-5.5.47-1.el6.remi.x86_64 (@remi)
               libmysqlclient.so.18(libmysqlclient_16)(64bit)
           Obsoleted By: mysql-community-libs-5.7.10-2.el6.x86_64 (mysql57-community)
               Not foundenter code here
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

I will recommend to move from php-mysql to php-mysqlnd (which provides the same set of extensions: mysql, mysqli and pdo_mysql), and thus, get rid of dependency on libmysqlclient:

# yum shell --enablerepo=remi
> remove php-mysql
> install php-mysqlnd
> run

Notice: PHP 5.4 is now EOL, I also recommend to upgrade to 5.5 or 5.6. And 5.4 is also the last version to provides php-mysql, newer RPM versions only have php-mysqlnd.


Efficient matching rows across two tables with huge data set I have two tables with about 300+ columns and 20k+ rows each. Each row is characterized by a primary key which both tables have.I have to find the identical rows, rows with changes,and rows without a corresponding match in the second table(i.e there is a row with primary id=x in table 1 and no row has primary id=x in table 2). Attributing to this huge data set, can somebody suggest an "efficient" method to solve this.

Without making any schema change, your solution requires a table scan on both sides - that's the most efficient you'll be able to do.  You simply have to LEFT JOIN A to B and compare every field.

However, if you expect to do this in the future, you can do a couple things to make life easier: you can add a calculated hash, which will almost always detect a change.  Compare this field, and you can identify changes.  Add an index to each table with the PK field(s) and this hash field, and you can very quickly find new and changed records:

SELECT
    A.PK,
    CASE
        WHEN B.PK IS NULL THEN 'New'
        WHEN A.Hash <> B.Hash THEN 'Changed'
        ELSE 'Unchanged' END AS Status
FROM
    A
    LEFT JOIN B ON A.PK = B.PK

Microsoft SQL offers a ROWVERSION construct which makes this type of operation simple; it looks like the equivalent in Oracle is system change numbers: https://stackoverflow.com/a/20488598/565869.

If for any reason that doesn't work for you, you could always roll your own using triggers or by modifying the sprocs which update these tables (you are wrapping your UPDATEs in sprocs, right?)


New login is created but 'sa' cant assign grant/deny permissions to user in SQL Server 2008 R2? I created users with login option in security tab in SQL Server 2008 R2 running on windows 7 successfully and grant permissions to :

Use dummydb
Grant REFERENCES  ON OBJECT::vuUser TO nvp
GO

Use dummydb
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dummyDB.userTb to nvp
GO

nvp user can login successfully and restriction work great but when I create user and grant permissions as above on SQL Server 2008 R2 running on windows server 2008.

User can login successfully but when I try to grant permission using above code, I get this message:

  Msg 15151, Level 16, State 1, Line 2
  Cannot find the object 'usertb', because it does not exist or you do not have permission.

Is their is something I am doing wrong or I need to try something else ?????

Database and table name are correct...

I login in using user 'sa' credentials

Need urgent help n thanks in advance

Syntax is OBJECT::Schema.Table not OBJECT::Database.Table

e.g.

use test
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dbo.T1 to public


mysql: using comma-separated values I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this:

+----------+----------------+---------------------+---------------------+
| server   | user           | start               | end                 |
+----------+----------------+---------------------+---------------------+
| server01 | Alice,Bob      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server02 | Carlos         | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server03 | Dan,Erin,Frank | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
+----------+----------------+---------------------+---------------------+

To check whether user Carlos has a booking for server02, I use the following SQL query:

SELECT 0, id FROM booking WHERE user = 'Carlos'
AND (( start < now() and end > now() ) AND server = 'server02') ;

But this query does not work if there are multiple (comma-separated) users. How can I check if Alice has booking for server01 ?


Ola Hallengren MaintenanceSolution - Differential - the value for @ModificationLevel is not supported I just installed this great tool, mostly it's working fine, just that if I include for a differential backup the parameter @ModificationLevel = 50 (or anything else) the action fails:

  Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2268
  The value for the parameter @ModificationLevel is not supported.

Example:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES', @CleanupTime = 2160, @Compress = 'Y',
@Directory = '\\cem01nas2\sql_backups',
@BackupType = 'DIFF', @ChangeBackupType = 'Y', @ModificationLevel = 50
GO

Gives the error, but if I remove the @ModificationLevel parameter it works. Any ideas?

This indicates that you are hitting the validation (taken from Ola Hallengren's site here)

  IF @ModificationLevel IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 1
  END

  IF @ModificationLevel IS NOT NULL AND @ChangeBackupType = 'N'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 2
  END

  IF @ModificationLevel IS NOT NULL AND @BackupType <> 'DIFF'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 3
  END

Please check the conditions and also keep in mind the @ModificationLevel is the difference needed for a Full backup to occur. If there is not enough difference it won't do the Full backup.

IF @ChangeBackupType = 'Y'
    BEGIN
      IF @CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
      BEGIN
        SET @CurrentBackupType = 'DIFF'
      END
      IF @CurrentBackupType = 'DIFF' AND (@CurrentDatabaseName = 'master' OR @CurrentDifferentialBaseLSN IS NULL OR (@CurrentModifiedExtentPageCount * 1. / @CurrentAllocatedExtentPageCount * 100 >= @ModificationLevel))
      BEGIN
        SET @CurrentBackupType = 'FULL'
      END
    END


Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema? Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

Background: We are doing microservice development and there is an sole owner schema and many different service accounts (schemas) that have been granted access to the tables created by the owner. At the moment, it's a nuisance to have to log into each service account to create synonyms. Is there a way to run a script to do it from the owner schema? We're trying to automate this process.

It can be done as SYSTEM (or a user with DBA privileges) or if 'create any synonym' is granted to the user.

SQL> create user test1 identified by test1;
User created.

SQL> create user test2 identified by test2;
User created.

SQL> grant create session to test2;
Grant succeeded.

SQL> create table test1.xtable (x number);
Table created.

SQL> create synonym test2.xtable for test1.xtable;
Synonym created.

SQL> drop synonym test2.xtable;
Synonym dropped.

SQL> conn test2/test2
Connected.
SQL> create synonym xtable for test1.xtable;
create synonym xtable for test1.xtable
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>  conn system/manager
Connected.

SQL> grant create any synonym to test2;
Grant succeeded.

SQL>  conn test2/test2
Connected.
SQL> create synonym xtable for test1.xtable;
Synonym created.


Is it OK for the database to be completely overwritten for many times? A simple and maybe too amateur question, but: is it good for a table on the database to be consistently (for example, every day) completely overwritten?

I get regularly (every day) the data for the whole year from the beginning of the year until now. I could just append the new days to the table, but I fear that there are some new data (adjustions or so) for the past days in the file. So I want to overwrite my table every time I get a new file. But is it ok for the table? I mean can it somehow corrupt it?

No corruption, no loss, no...  (Well, there is always the remote chance of a hardware failure, software bug, typo on your part, etc.)

Here is a 'better' way to completely reload a table:

CREATE TABLE new LIKE real;
load `new` by whatever means
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

That way, the table, real, is always available except for the brief moment when the RENAME is running.


Replace part of a string in MYSQL, when only the substring's starting characters, ending characters & a unique string in the middle is known? I'm trying to find a way to replace part of a string in a database without knowing the exact substring content other than how it starts, ends and has a specific ID string somewhere in between that.

To specify, I have Vimeo iframes that are within post content inside a column in a MySQL database table. I need to replace all vimeo iframes strings in this column over to another video platform script. I have a list to use containing the Vimeo IDs and the new code to go in place of the existing Vimeo iframe, so this gives me a unique identifier to use when searching.

The problem is, some of the Vimeo urls have additional GET queries added to the URL so I can't do a 1 for 1 match of the Vimeo iframe.

I do know for certain that each iframe instance I need to replace will begin with <iframe, end with </iframe> and contain a specific Vimeo ID (ie 1234567). Is there a way to do this with Regex and concat or something similar so that I can replace the entire iframe code with a new string?

Test:

UPDATE source_table
SET field = CONCAT( SUBSTRING_INDEX(field, @start, 1),
                    @replacement,
                    SUBSTRING_INDEX(field, @end, -1)
                  )
WHERE LOCATE(@middle, SUBSTRING_INDEX(SUBSTRING_INDEX(field, @start, -1), @end, 1))


Multi-insert batch executed remotely 5x slower than locally I am inserting 30.000 rows into a table in one batch using INSERT .. VALUES statement for each row.
See the testing environment:
Table creation:
CREATE TABLE [dbo].[TestInsert](
    [Col1] [int] NOT NULL,
    [Col2] [varchar](16) NOT NULL,
    [Col3] [varchar](15) NOT NULL,
    [Col4] [int] NULL,
    [Col5] [datetime] NOT NULL,
    [Col6] [nvarchar](128) NOT NULL
)

Batch inserting rows:
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491073,N'D0058A79AE',N'OCIP',51849,'20100823 10:02:04.683',N'TUVWXYZabcdefgh')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491074,N'D00559B4C4',N'OCIP',62488,'20100823 10:02:04.710',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491075,N'D005AB75B6',N'OCIP',52836,'20100823 10:05:17.070',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491076,N'D0070B9F25',N'OCIP',62554,'20100825 08:03:08.260',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491077,N'D00753F2D7',N'OCIP',62554,'20100825 08:03:58.733',N'UVWXYZabcdefghi')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491078,N'D0070B979A',N'OCIP',62554,'20100825 08:04:09.917',N'STUVWXYZabcdefg')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491079,N'D0070B6F37',N'OCIP',62554,'20100825 08:04:21.043',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491080,N'D0070B86F3',N'OCIP',62554,'20100825 08:05:28.460',N'GHIJKLMNOPQRSTU')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491081,N'D00708D1E1',N'OCIP',62554,'20100825 08:06:50.030',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491082,N'D0070B7DFA',N'OCIP',62554,'20100825 08:11:13.507',N'VWXYZabcdefghij')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491083,N'D0070B7FCE',N'PRON_OCIP',62555,'20100825 09:13:26.563',N'XYZabcdefghijka')
...
more (30.000 rows)

You can download the whole batch here: https://filebin.net/zg499h4iv1m44z6v
The table has no indexes, constraints, foreign keys, triggers…nothing.
When I run the batch in SSMS locally, it takes 20 seconds and produces these wait stats:

But when I run it in SSMS from a remote computer over LAN, it takes 100 seconds (5x slower) with almost the same wait stats:

Please, mention there is no ASYNC_NETWORK_IO wait type.
SET NOCOUNT ON is not set intentionally!
Testing in other environment it works just fine with no big differences in time.
What could be the real source of the slowness? Why it's not captured by wait statistics?
Please be more specific than just saying „network infrastructure“.

So there's only one batch (SqlBatchCompleted) involved here, as I understand. Assuming that:
My guess is that the difference is because of (the lack of) the network stack.
The client network library default to try Shared Memory first. This doesn't "dip down" in the network stack. This is likely the one you end up with from the local SSMS.
But Shared Memory will of course not work from the remote machine. The next tried is TCP, which is likely the one you end up with from the remote SSMS (unless you either re-configured that client or re-configured SQL server - both pretty unlikely).
So you compare using a netlib which uses a shared memory area to one that travels the code path of the network stack in both directions and also has bandwidth and latency because of the network in between.
Things you can play with includes:

Force the TCP netlib when running locally (option in SSMS client connect dialog).
Play with various network packet sizes (also available in SSMS connection dialog).
Talk to your network people and try with jumbo frames and such things.


Is it possible to calculate the SQL Server sql_handle for a given SQL statement? The following site mentions the sql_handle is a MD5 hash of the entire batch text
https://techcommunity.microsoft.com/t5/sql-server/2-0-sql-handle-and-plan-handle-explained/ba-p/383204
However, I'm unable to calculate it manually. From dm_exec_query_stats we determine the following statement has the following sql_handle.
SQL: (@P1 bigint)SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1
SQL_HANDLE: 0x02000000CA17931BEF7F24A1787BF580EA365A56408697B30000000000000000000000000000000000000000

The following Ruby script calculates the MD5.
require 'digest'

sql1 = 'SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1'
sql2 = '(@P1 bigint)SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1'

puts Digest::MD5.hexdigest sql1
puts Digest::MD5.hexdigest sql2

Output:
81707e73438befe82957c766dca86799
2652dc1d05e927ac7c5bfc985a3d1483

There must be more to the algorithm.
We can calculate Oracle's SQL_ID, I'm hoping to be able to also calculate SQL Server's SQL_HANDLE.

The 44-byte sql_handle has the following components (in order):

4 bytes: Byte-reversed integer code for the target cache e.g.

0x02000000 (2) for SQL Plans (CACHESTORE_SQLCP)
0x03000000 (3) for Object Plans (CACHESTORE_OBJCP)

4 bytes: Byte-reversed integer object id

For adhoc and prepared SQL this is an internal hash of the batch text as returned by @@PROCID or objectid in sys.dm_exec_plan_attributes.

16 bytes: MD5 hash of the batch text including (parameter definitions) prefix, if present.
20 bytes 0000000000000000000000000000000000000000


The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server) I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which point I get an error like this on the SQL Server machine, and the query utterly fails:

> The XA Transaction Manager attempted to load the XA resource manager
> DLL. The call to LOADLIBRARY for the XA resource manager DLL failed:
> DLL=C:\Program Files\psqlODBC\0905\bin\pgxalib.dll, HR=%3, File=%2
> Line=%3.%0

The DLL is in fact that location, so the registry seems to be pointing to the right file. The ODBC driver is 64bit and so is my OS.  "File=%2" is pointing to something on the d drive, which doesn't make sense to me, since d drive is a DVD. MSDTC is running... what am I missing?

I have toggled Linked Server Properties "Enable Promotion of Distributed Transactions for RPC" to both "True" and "False" and this doesn't change the issue and does not produce a different error.

Otherwise, scouring the Internet has brought me nothing.

Last thing to point out, my query isn't actually doing any updating - it is just pulling data. So I'm not sure why MSDTC get's invoked in the first place...

I fixed the problem by changing folder permissions on the Postgresql ODBC driver folder and giving read/execute access to Network Service. Because MS DTC runs under network service, which I confirmed by looking at the service properties.

That got rid of the error!


insufficient privileges while executing oracle stored procedure? I'm getting an insufficient privileges error while executing the following oracle stored procedure. I'm using Oracle Database 10g Express Edition.

CREATE OR REPLACE
PROCEDURE sp_update_acounts(
    accounts_file_dir  IN VARCHAR2,
    accounts_file_name IN VARCHAR2)
IS
BEGIN

EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_accounts_dir AS '''||accounts_file_dir||'''';

EXECUTE IMMEDIATE 'grant read, write on directory ext_accounts_dir to myuser';

EXECUTE IMMEDIATE 'drop table crm_account_stage';

EXECUTE IMMEDIATE 'CREATE TABLE crm_account_stage (entity_account_id NUMBER(19,0), crm_id VARCHAR2(255 CHAR)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY
ext_accounts_dir ACCESS PARAMETERS (FIELDS TERMINATED BY '','' ( entity_account_id CHAR(225), crm_id CHAR(225))) LOCATION ('''||accounts_file_name||''''||') )';

MERGE INTO ua_crm_accounts acc
USING (
  SELECT entity_account_id,
         crm_id
  FROM crm_account_stage) acc_stage
ON (acc_stage.entity_account_id = acc.pkey)
WHEN MATCHED THEN
  UPDATE SET acc.crm_id = acc_stage.crm_id;

END;

I'm using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure successfully. I have all the rights for the oracle user because I'm the admin. I have given all possible rights.

But when I execute the SP I'm getting error like:

Error starting at line 13 in command:
execute sp_update_acounts('C:\Users\surenr\Desktop\UA\Intitial-Conversion','acc_data.csv')
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "myuser.SP_UPDATE_ACOUNTS", line 7
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

Update:
I'm not trying to update user or password. But the error message says as i'm trying to modify user details. When I try the same code step by step outside the stored procedure, it's executing without any problem.

What could be the reason for this? How can I resolve the issue?

The pl/sql execution context does not include Role. So your risght have been delegated by a role. The Pl/SQL doesn't see it.
So, first, you can try to give the right to alter the user directly not via a role.
2nd, i would try this option on the package definition (auhtid) which is explained here


Getting the first 3 children(items) for each owner in a list of primary keys I have two tables Item an Owner, an a Foreign Key from Item to Owner, owner_id. I'm using PostgreSQL 10.

For each owner I want to get the first 3 items if exist.

select  "items_item"."name"
FROM "items_item"
WHERE ("items_item"."owner_id" IN (64, 65, 130, 99, 43, 140, 108, 143, 115, 154))
ORDER BY LIMIT 3

Clearly is not ok, because is selecting just 3 items from all owners, not a maximum 3 items per owner .

You can use window functions to solve this kind of issues, something like:

select item_name
from (
    select name as item_name
         , row_number() over (partition by owner_id
                              order by id) as rn
    from items_item
    where owner_id in (64, 65, 130, 99, 43, 140, 108, 143, 115, 154)
) as t
where rn <= 3

I updated to order by id in window clause, according to comment


Is there a way to merge two databases, same schema, with a lot of foreign key costraints and incremental id into one single database? DATABASE A
users
id name profile_id
1  jhon          1
2  smith         2

profile_id:
id email user_id
 1 a@g.c       1
 2 c@g.c       2

DATABASE B
users:
id name  profile_id
 1 elvis          1
 2 black          2

profile_id:
id email user_id
 1 b@g.c       1
 2 d@g.c       2

I want to merge
users:
id name profile_id
 1 jhon          1
 2 smith         2
 3 elvis         3
 4 black         4

profile_id:
id email user_id
 1 a@g.c       1
 2 c@g.c       2
 3 b@g.c       3
 4 d@g.c       4

Assuming one the same database server:

Step 1:

Take a record of the maximum ids for users and profile_id and assume that greater than this is the value copied (they are auto_increment right?).

SELECT MAX(id) INTO @maxuserid FROM A.user;
SELECT MAX(id) INTO @maxprofileid FROM A.profile_id;

Step 2:

copy the names, and we temporary use the profile_id as the reference to the B.id:

INSERT INTO A.users ( name, profile_id)
SELECT users.name, users.id FROM B.users

Step 3:

Same for profile_id, using user_id as the temporary old id.

INSERT INTO A.profile_id (email, user_id)
SELECT profile_id.email, profile_id.id FROM B.profile_id

Step 4:

Now correct the A.user.profile_id and A.profile_id.user_id base on the mappings that exist in table B:

UPDATE A.user AS auser
       JOIN B.user AS buser,
            ON auser.profile_id = buser.id
       JOIN B.profile_id AS bprofile
            ON bprofile.user_id = buser.id
       JOIN A.profile_id AS aprofile,
            ON aprofile.user_id = bprofile.id
SET
   auser.profile_id = buser.id,
   aprofile.user_id = auser.id
WHERE
   auser.id > @maxuserid
   AND aprofile > @maxprofileid

tip: write the above UPDATE as SELECT to make sure I've got the join criteria correct.


Select fields from sub query , but I face field could not be bounded SELECT
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,
    a.AcctBranchName,
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM
     v_Account AS a
LEFT join v_Customer AS c
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE
    a.AccountNum IN (
        SELECT  aa.AccountNum FROM v_Account AS aa WHERE
         aa.Category = 'Deposit' AND aa.ProductCode IN ('6003', '6253')
        AND aa.Businessdate='2018-12-06' AND aa.ClosedDate is not NULL
    )
    AND a.Category = 'Deposit'
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'

How can I select aa.ClosedDate from my sub query or any alternate solution which I can get the ClosedDate of 2018-12-06.

Example. Account number: 123456 is closed at date 2018-12-06 (closed mean no balance) So in order to get the balance of this account number I need to look up this at 2018-12-05 (before 1 day this account is closed) ,then I can get the balance for my calculation purpose for example.

Based on your comment to the question of whether multiple rows could come out of your subquery for a given account number, you indicated that was not possible.  If that's true, I don't see why you can't simply LEFT JOIN by account number against a derived result of your subquery.

Not tested, but basically like this:

SELECT
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,
    a.AcctBranchName,
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM
     v_Account AS a
LEFT join v_Customer AS c
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate
LEFT JOIN
(
    SELECT AccountNum, ClosedDate FROM v_Account WHERE
        Category = 'Deposit' AND ProductCode IN ('6003', '6253')
    AND Businessdate='2018-12-06' AND ClosedDate is not NULL
) aa on aa.AccountNum = a.AccountNum
WHERE
        aa.AccountNum = a.AccountNum
    AND a.Category = 'Deposit'
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'


Large long-running reporting queries are causing very slow new mysql connections Usually, mysql's connecton time is in the 0.001 second range. However, when I run a large reporting query (that spans multiple tables with aggregates), I get tons of warnings saying that new mysql connections are taking between .5 and 2 seconds just to connect! Why would a single large query affect connection times so much?

Doing research online, I can't seem to find which mysql settings would need to be tweaked to mitigate the issue.

I'm on MySQL 5.6, using all InnoDB tables.

It's look like your reporting query lock for a while the tables needed by general queries.

There is no general recipe for such cases and you have to refactor your reporting to avoid locking. Sometimes you can achieve this with splitting one big query into the series of smaller ones. Sometimes the good approach is to copy some tables for exclusive use by report generator. Sometimes it is something else. All depends on the your DB scheme.


Do I need to add a new single column index to a table if a multi-column index on that field already exists? I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__.

Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that column?

(at 100 million rows it could take a while.This is why I'm asking instead of just doing)

No, not at all !!! The MySQL Query Optimizer will do the right thing if the main column(s) needed is leftmost in the index. If you did make such an index, the MySQL Query Optimizer may opt never to use that index if you always perform GROUP BY job_id,keyword_id. MySQL Query Optimizer may or may not use the index if you gather records by job_id only, but then you have a redundant index wasting space anyway.

If the table is MyISAM, making such an index would just bloat the MYI file.

If the table is InnoDB and innodb_file_per_table is 0, making such an index would just bloat ibdata1.

If the table is InnoDB and innodb_file_per_table is 1, making such an index would just bloat the .ibd file of the table.

In summary, you do not need to make that additional index !!!


Arithmetic overflow error converting float to data type numeric I am trying to move data from a csv file into a SQL server database. Some of my values are in the scientific notation. I figured out on how to get most of them converted but for one value I get the Arithmetic overflow error.

The value that is causing the error is 4.56621E-6. If I change the part before the E by removing the 1 so it reads 4.5662E-6 the import works fine. All the other values I need to import work fine.

I use a format file to import the data. Below the line for the column that is giving me grief:

88  SQLFLT8 0   0   "," 89  PPL_2_BL    ""

The format in the database is decimal(18,9). Any suggestions on how to avoid this error without manually changing values in the source file?

To put it into perspective. The CSV file contains more than 2.2 million rows with 154 columns each. Which results in a CSV file size of more than 2GB. Currently I am working with a test file. When the final go live comes. I need to switch over fast. Which means I can not analyze and edit the file for several days.

Update

I played around with the values a little bit.

4.56621E-6   -> fails
6.5789474E-6 -> works
4.5662E-6    -> works
4.56622E-6   -> fails
4.566210E-6  -> works (surprisingly)
4.66621E-6   -> fails

I'm no expert in this, but it looks to me as if you're trying to use FLOAT. Have you tried using NUMERIC? Something like this maybe? Just throwing ideas out.

88  SQLNUMERIC 0   0   "," 89  PPL_2_BL    ""


Restore the data from Snapshot Backup when the data spans over multiple directories? Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF.

We have done some testing work on one of the slave say server A.and it takes a huge amount of disk space due to out test work on that slave. (Approx 300GB increased due to our test work) as dropping the database would not help us due to innodb_file_per_table OFF.

ibdata is on LVM.
datadir is on /EBSroot
innodb logs are on some other location

The total ibdata size is approx 600GB due to this test work it increased to 900GB.

Now i want to reclaim the space.I am thinking as

delete the ibdata from server A.and take the snapshot backup from the one of the slave and restore on this slave A.

but i am not getting the correct steps.For How to take the backup from one of the slave and restore on this slave A.

i have studied alot on how to take snapshot backup of MySQL
i found some Caveats as : Handling data spread on multiple volumes (DB logs on separate LV or DBs spread across multiple LVs).

So how can do this without any issues.because my ibdata/logfiles/datadir are all on the different locations.I have to also  restart the replication as well.

I need the best possible way with minimum downtime.

please help..

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

MetaData Issues : I wrote about this as well

Why does InnoDB store all databases in one file? (See WARNING)
How to Recover an InnoDB table whose files were moved around
Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

Performance Issues : I wrote about that back in August 2011

MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

Step 01) mysqldump all databases
Step 02) shutdown mysql
Step 03) delete ibdata1
Step 04) startup mysql
Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.