Unmount/detach/deactivate PostgreSQL database I manage a server that serves several web applications that use PostgreSQL as a data store.  Each web application has a separate role and database, and each database is stored exclusively on a dedicated tablespace on a different logical file system.

What I would like to be able to do is unmount a web app's file system without affecting postgres - if I do that at the moment CHECKPOINTs fail to run, so CREATE DATABASE, VACUUM, and other commands fail.

Is there a way to "deactivate" a database so that PostgreSQL won't try to checkpoint or autovacuum it?

This is not possible with PostgreSQL at this time.

What you're talking about is more like a "quiesce" or "stop" than a "detach". You don't seem to want to be able to move it from one PostgreSQL instance to another.

The issues are:

Transaction IDs are global. Pg would have to stop all writes to the DB then VACUUM FREEZE it, which is quite expensive, to make sure there would be no txid wrap-around issues when it was re-enabled.
Pg needs to be able to tell if a database contains objects that depend on a global object like a user, so when you DROP USER it can reject the attempt if it'd violate dependencies.
... and probably more

I don't think it's impossible, theoretically, but it's not going to be trivial to impliment. It also won't be very useful - I don't see what you gain from this over simply leaving the DB unused, or at most forcing a VACUUM FREEZE. Checkpoint activity is insignificant to nil if there's no activity in the DB, same with autovacuum.


MariaDB 10.0.36: Table './mysql/servers.MYI' is read only Trying to upgrade MariaDB using Generic Linux package from https://downloads.mariadb.org/mariadb/10.0/#os_group=linux_generic on a Debian 8 server:

kernel version 2.6.32-49-pve
OpenVZ/Virtuozzo container

Have no issue with MariaDB 10.0.35. The SQL server starts, no issue found. Hence it can not be an issue with files/folders permissions. None of master/slave replication is enabled.

But MariaDB 10.0.36 does not start due to the following errors:

180909 17:04:07 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180909 17:04:07 [ERROR] Can't open and lock privilege tables: Table './mysql/servers.MYI' is read only
180909 17:04:07 [Note] Server socket created on IP: '127.0.0.1'.
180909 17:04:07 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/host.MYI' is read only
180909 17:04:07 mysqld_safe mysqld from pid file /usr/local/mysql/data/mysqld.pid ended

That's weird, if I roll binaries back to MariaDB 10.0.35 and starts fine again.

The server have selinux disabled, and AppArmor does not seem to be enabled.

root@hosting:/etc/apparmor.d# service apparmor status
● apparmor.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
root@hosting:/etc/apparmor.d#

A fresh installed MariaDB 5.5.x fails the same way even after:

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/

it installs all the tables and fails to start afterwards.

So we can't start any other MariaDB version, but 10.0.35.

What else can it be? What do I miss?

Regards,
Alex.

Changed datadir= in /etc/my.cnf from /home/mysql/ and moved databases to /var/lib/mysql/:

datadir                        = /var/lib/mysql/

and MariaDB 10.0.36 started fine.

But why? What do I miss?

Regards,
Alex.


Trigger to delete with cascade effects Hi I am new to Oracle triggers. Your help will be much appreciated!
I have the following 2 tables:
Product:

CREATE TABLE Product
  (prodId varchar(10) NOT NULL PRIMARY KEY,
   prodName varchar(30) NOT NULL,
  storeCode varchar(10) NOT NULL,
  CONSTRAINT prodStore FOREIGN KEY (storeCode) REFERENCES Store(stCode)
  );

and the Store table:

CREATE TABLE Store
(stCode varchar(10) NOT NULL PRIMARY KEY,
storeName varchar(20) NOT NULL
);

I want to create an Oracle delete trigger so that if I delete a store, all the products that are related to it will be deleted as well, in a cascade.
note: I don't want to use an ON DELETE CASCADE CONSTRAINT.
Here's my code:

CREATE OR REPLACE TRIGGER trigg_suppression
BEFORE DELETE ON Store
FOR EACH ROW
BEGIN
DELETE FROM Product
WHERE Product.storeCode= :OLD.stCode;
END;
/

I get a mutating table error.

I tried to use a statement level trigger:

CREATE OR REPLACE TRIGGER trigg_suppression
BEFORE DELETE ON Store
BEGIN
DELETE FROM Product
WHERE Product.storeCode= Store.stCode;
END;
/

This won't even compile.

Thank you!

Thanks to @ Justin Cave I got the answer. The problem was that another table (Department) was referencing the Store table as well. so I added in the trigger body:

DELETE FROM Department
WHERE Department.storeCode = :oldRow.stCode;


Why do we have negative length for character maximum length for geography and varchar(max) data types? Why do we have negative length for character maximum length for  geography and varchar(max) data types?

Character_Maximum_length = - 1

The value -1 is a special value that means "maximum currently supported". The maximum length of e.g. max, xml, and clr types could change in a future version of SQL Server.

In system views like INFORMATION_SCHEMA.COLUMNS the CHARACTER_MAXIMUM_LENGTH column already uses NULL for types where a character length is not applicable, for example the numeric types.

If you are currently using INFORMATION_SCHEMA views, you should read this and then consider using SQL Server's own metadata views like sys.columns instead.

As a side note, the underlying type of the max_length column in sys.columns is smallint NOT NULL. It is the maximum length (in bytes, not characters) of the column, or -1 meaning 'maximum supported` as mentioned before.


Invalid column name SIS ID? How do i get this column to execute, it has this exception 'Invalid name [SIS ID]? Meaning there is a space in between and want to find a way to allow it not to happened.
insert into dbo.eNtsaRegPeopleLists(LoginID,Name, [SIS ID],Section,Role,LastActivity,TotalActivity)

values(1, 'gcobani', 'Ggcobs', 'Your course guide to template', 'Developer', '2020-09-22', 05.23);

// Error
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SIS ID'.

I assume you mean how to get the INSERT statement to execute without error? (A column doesn't "execute".)
Apparently, there is no column named "SIS ID" in the dbo.eNtsaRegPeopleLists table. That is what the error message is telling you.
You did right by enclosing the column name in square brackets (double quotes is another option). So double-check what the column name really is. Perhaps the square brackets are part of the column name. Or that the space between SIS and ID isn't a space but some other invisible character (like char(255)). We can't say which it is, so it is for you to determine. One option is to do:
EXEC sp_help 'eNtsaRegPeopleLists'

And see what output you get for the column names.


SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans.  I can take care of all the backup stuff, but the table maintenance baffles me some.  Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?

Check Database Integrity
Rebuild Indexes
Reorganize Indexes
Update Statistics
Shrink Database?

Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful.  Lastly does it matter what order I run these tasks in (when setting up a job)?

Oh and I'm open to any links which might be of help!

Usually you'd do weekly or daily depending on your usage and maintenance windows.

You pretty much never shrink, especially not scheduled.

For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).

We use weekly index/DBCC and daily statistics. And never shrink.

From Simple-talk: Don't Forget to Maintain Your Indexes


MySQL - Log data with Inner Join 12 Tables or Create View? I'm using MySQL (phpmyadmin) with engine InnoDB for my e-commerce website.

Now, I need to get the data as Log but It's need to joining more than 12 tables for logging all data we need to see, only last 30 data submitted (done / reject) will show in pagination (Log page 1) but still have pagination for next last 30.

9 INNER JOIN
3 LEFT JOIN (It's Admin Order Form when customer Ordering via Offline (friend))
WHERE order_status = 1 OR order_status = 2 (done & reject)
ORDER BY order_id DESC
LIMIT 30 (limiting 30 per page with pagination)

The order data is more than 2.000.000 rows for 4 tables in INNER JOIN connected in PK & FK.

Any problem if I'm joining 12 tables OR I should create table view or something ?

Sometime we need to make sure storage is saved with normalization, sometime we need the performance faster. (but still not wanted to use denormalization data for log).

Any solution ? Thank you.

Normalize, but don't over-normalize.  Do not normalize things where the id is not significantly smaller than the value.  Do not normalize 'continuous' datatypes (ints, floats, dates); it severely hampers use of 'range' filtering.

Why do you have LEFT?  (If you need it, use it; if you don't then remove it so the Optimizer has more options.)

Do have INDEX order_id and do not put that table after LEFT.  Then, the Index can be used to find the rows more quickly than via a table scan.

When you still have performance problems, start with a minimal SELECT that finds the 30 ids that you need. Then join to the rest of the tables.  (And possibly self-joins back to the table to get the other columns needed.)  Use this as a "derived table" (... FROM ( SELECT ... ) ...).

A VIEW is just syntactic sugar.  Use it if it simplifies your code, but don't expect any performance benefit.


Why SELECT query is waiting on HADR_SYNC_COMMIT? We have two AlwaysOn nodes in sync commit.
Occasionally SELECT queries are waiting on HADR_SYNC_COMMIT.
What is the underlying process that causes it?

SQL Server 2016 SP1 CU5.
client sessions are using READ COMMITTED isolation level
Allow Snapshot Isolation and Is Read Committed Snapshot On are set to True. But snapshot isolation is not being used because client is enforcing READ COMMITTED.

Query example from primary replica:
select * from dbo.photoFileMetaData where FK_mediaId=@P0
Wait info: (780ms)HADR_SYNC_COMMIT
Status: suspended

Since SELECTs are not updating data, why would they need to wait on a replica sync commit?

Update 2018-08-30:
I was able to capture blocking when it was happening. Here is what is in blocking resource:
metadatalock subresource=QDS_INTERVAL_STABILITY classid=qds_interval dbid=7 lockPartition=0 id=lock1e78c13d580 mode=X

Since the HADR_SYNC_COMMIT wait indicates that the primary is waiting for a log block to be hardened on the secondary, it doesn't really make sense for a plain select query to cause this wait to accumulate - since a select query does not generate any transaction log to be sent.

How are you gathering that wait info?  The most likely reason for that wait is that there are inserts, updates, or deletes being executed, and then the select runs within the same transaction, and however the waits are being accounted are including the previously accrued HADR_SYNC_COMMIT waits alongside that select statement.

If the select statement were really running on it's own outside of a transaction, and it's trying to read data that's part of an active transaction (that's waiting on HADR_SYNC_COMMIT):

without RCSI, I'd expect to see LCK waits for the select query
with RCSI (your scenario), I'd expect the select to just work (since writers shouldn't block readers in this case)

Quick point of clarification: since you have Read Committed Snapshot enabled and turned on, any queries using the READ COMMITTED isolation level are [automatically using RCSI.  This is just how RCSI works:

  The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
  ...
  If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data...

You mentioned that the client sending the queries is enforcing READ COMMITTED.  It could be that it's forcing the use of shared locks vs snapshots via a READCOMMITTEDLOCK table hint.  But I still thought I'd clarify for completeness.


In MySQL why is the first batch executed through client-side prepared statement slower? I have around a million lines to be inserted into a MySQL (InnoDB) database, so to speed things up I turned to batch/multi-line insert. To be specific, I connect to the database (I'm using Java, so it's done through Connector/J) with rewriteBatchedStatements=true appended at the end of URL, then I initialize all the required prepared statements with prepareStatement(). I use addBatch() to add lines into individual prepared statements and call executeBatch() when a certain number of lines is reached. After several calls of executeBatch(), I will do a commit.

In terms of final result everything is good, but I'm very curious about a behavior. Suppose I set the threshold of when to call executeBatch() to 10,000 lines, then during processing, the first call of executeBatch() will be significantly slower than the subsequent calls (in my scenario about 5 sec vs. < 1 sec). It feels as if the MySQL server is still "preparing" something.

From what I know, PostgreSQL has an option like setPrepareThreshold to set the number of queries issued before actually compiling the SQL statement. Is MySQL doing something similar? How can this delay be mitigated or is this behavior native to MySQL implementation?

EDIT (Some more background)

Below is one of the tables I will insert records into (others have similar schema).

'CREATE TABLE `flow_hourly` (
  `datetime` datetime NOT NULL,
  `customer_id` varchar(7) CHARACTER SET utf8mb4 NOT NULL,
  `pages` bigint(14) NOT NULL,
  `hits` bigint(14) NOT NULL,
  `bandwidth` bigint(14) NOT NULL,
  `nvpages` bigint(14) NOT NULL,
  `nvhit` bigint(14) NOT NULL,
  `nvbandwidth` bigint(14) NOT NULL,
  PRIMARY KEY (`datetime`,`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Since this database is for development purposes, the current maximum size is also around a million records, but in production the rate of growth should be relatively fast (n*100K+ inserts per day).

One may guess from the schema above that I'm creating a database to record server traffic information. For each time interval (e.g. hourly, daily, monthly...) there is a dedicated table so that queries on different time intervals can be sped up accordingly. I receive traffic information on an hourly basis, so except for the hourly tables, I do INSERT ... ON DUPLICATE UPDATE to accumulate values.

Probably you have an ordinary "cold cache" case.

When you INSERT 10,000 rows into a table, the rows need to be added to the appropriate place(s) in the "data".  Also, 10,000 entries need to be added to each index's BTree.  (How many indexes do you have?  Please provide SHOW CREATE TABLE.)

If you have AUTO_INCREMENT, then the rows will be "appended" to the table; this is not a caching issue.  If, on the other hand, you have a UUID index, or some other 'random' index, then the "inserts" into the index will be random -- involving read-modify-write.  The read and the write are cached.  But, if the system were "cold" when you did the first 10,000, a lot of reads were probably necessary.  Multiply that by how many indexes you have.

How big is the table?  If it is small enough to fit into innodb_buffer_pool_size (if InnoDB) or small enough for the indexes to fit into key_buffer_size (if MyISAM), then soon all the index blocks will be cached, and the inserts will speed up.

If the table is too big for the cache, then the 'random' indexes will continue to hit the disk (and be slow).  AUTO_INCREMENT will continue to be fast.

5 seconds is about 500 reads from a commodity spinning drive.  So, I would guess that you have a modest sized table.  Since the next 10K rows go in <1sec (<100 disk hits), I will guess that it fits in cache so far.

I often recommend 100-1000 for chunk size.  This is partially because 5 sec can be a problem.  You may as well COMMIT after each chunk, since the benefit of delaying it is minor.  Furthermore, if you wait "too long" to commit, the 'transaction' could overflow the log_file, leading to an inefficiency.

A chunk size of 100 is about 10% slower than the theoretical maximum.  1000 is about 1% slower than max.

If you have replication, keep in mind that the 5 seconds will interfere with anything else being replicated.  (Another argument against 10K.)


How to connect Powershell to the SQL Server named instance? When I run

# Load the SMO assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$ServerName = "[local\s12]";

#Get a server object which corresponds to the **default** instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

I am getting an error

Failed to connect to server local\s12.

$ServerName = "[local\s12]";

Remove the square brackets.

Apart from that, the PowerShell code looks fine (and works on my test machine - the code I used is below, copied from my SQL Server test machine SERVER1).

PS C:\Users\Administrator.ADVENTURE-WORKS> [void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo" )
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName = "server1\sql2012"
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName
server1\sql2012
PS C:\Users\Administrator.ADVENTURE-WORKS> $Srv = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName

Additionally, is the server actually called "local" or is that just anonymised? If you want to connect to the local server then the alias is a period.

$ServerName = ".\SQL2012"

Otherwise, try to connect to the server local\s12 using SQL Server Management Studio using windows authentication from the same machine that you are running the powershell script. if that fails then there is a connectivity issue. Check firewalls (on the SQL Server machine and on any routers in between the client and server). Check that the TCP/IP network library is enabled on both the client and the server. If TLS is enabled then confirm that the certificates are all correct.


What is more appropriate sp_updatestats or UPDATE STATISTICS? In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place.

At the end of this process I would expect  sp_updatestats to run, so that statistics for all this new data is updated.

However, while inspecting the code I noticed the following line instead:

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Can you compare that with running sp_updatestats? That is what is the differences and advantages/disadvantages of one over the other?

Couple of things ..

Stop using the undocumented sp_MSforeachtable
From Erin's blog - Understanding What sp_updatestats Really Updates

  sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows

Note that sp_updatestats in the code sp_helptext sp_updatestats, it runs 'UPDATE STATISTICS '

Recommendation:

Use Ola's Index maintenance solution


Create a job from dbms_scheduler with stream redirection? I want to create a simple job who have to execute this shell command:

echo some_text > some_file

The attributes some_text and some_file can change between two calls to run_job so I need to use set_job_argument_value procedure.

Here is my code:

exec dbms_scheduler.create_job('write_file','executable','/bin/echo',1,auto_drop=>false);
exec dbms_scheduler.set_job_argument_value('write_file',1,'text > /tmp/file');
exec dbms_scheduler.run_job('write_file');

I begin by creating the job who will execute the echo command. This job will take only argument : text > /tmp/file.
When I run my job, the procedure works fine but my file is not created. It's like the procedure runs this command: /bin/echo "text > /tmp/file".

So I tried with multiples arguments:

exec dbms_scheduler.create_job('write_file','executable','/bin/echo',3,auto_drop=>false);
exec dbms_scheduler.set_job_argument_value('write_file',1,'text');
exec dbms_scheduler.set_job_argument_value('write_file',2,'>');
exec dbms_scheduler.set_job_argument_value('write_file',3,'/tmp/file');
exec dbms_scheduler.run_job('write_file');

Same output and no file created.

So, is it possible to specify the standard input? Or to use this kind of job_action: /bin/echo $1 > $2?

I found a patch to this problem. The idea is to create a bash file:

#!/bin/sh
echo $1 > $2

And now, you can create and run your job:

exec dbms_scheduler.create_job('write_file','executable','/usr/bin/oracle/transfer',2,auto_drop=>false);
exec dbms_scheduler.set_job_argument_value('write_file',1,'some_text');
exec dbms_scheduler.set_job_argument_value('write_file',2,'/tmp/some_file');
exec dbms_scheduler.run_job('write_file');

And you can see the result:

$ cat /tmp/some_file
some_text


Innodb mysql 5.7 cluster wont join after reboot I get the following when I try to recover the cluster from a complete outage. Can anyone advise what's going on

cluster = dba.rebootClusterFromCompleteOutage()
Dba.rebootClusterFromCompleteOutage: Invalid value for localAddress,
string value cannot be empty. (ArgumentError)

Thanks

Fixed it by adding "group_replication_local_address" value in my.cnf cluster config file.


Rejoining range seek on nullable composite index? For the following schema and example data

CREATE TABLE T
  (
     A INT NULL,
     B INT NOT NULL IDENTITY,
     C CHAR(8000) NULL,
     UNIQUE CLUSTERED (A, B)
  )

INSERT INTO T
            (A)
SELECT NULLIF(( ( ROW_NUMBER() OVER (ORDER BY @@SPID) - 1 ) / 1003 ), 0)
FROM   master..spt_values

An application is processing the rows from this table in clustered index order in 1,000 row chunks.

The first 1,000 rows are retrieved from the following query.

SELECT TOP 1000 *
FROM   T
ORDER  BY A, B

The final row of that set is below

+------+------+
|  A   |  B   |
+------+------+
| NULL | 1000 |
+------+------+

Is there any way to write a query that just seeks into that composite index key and then follows it along to retrieve the next chunk of 1000 rows?

/*Pseudo Syntax*/
SELECT TOP 1000 *
FROM   T
WHERE (A, B) is_ordered_after (@A, @B)
ORDER  BY A, B

The lowest number of reads I've managed to get so far is 1020 but the query seems far too convoluted. Is there a simpler way of equal or better efficiency? Perhaps one that manages to do it all in one range seek?

DECLARE @A INT = NULL, @B INT = 1000

;WITH UnProcessed
     AS (SELECT *
         FROM   T
         WHERE  ( EXISTS(SELECT A
                         INTERSECT
                         SELECT @A)
                  AND B > @B )
         UNION ALL
         SELECT *
         FROM   T
         WHERE @A IS NULL AND A IS NOT NULL
         UNION ALL
         SELECT *
         FROM   T
         WHERE A > @A
         )
SELECT TOP 1000 *
FROM   UnProcessed
ORDER  BY A,
          B

FWIW: If the column A is made NOT NULL and a sentinel value of -1 is used instead the equivalent execution plan certainly looks simpler

But the single seek operator in the plan still performs two seeks rather than collapsing it into a single contiguous range and the logical reads are much the same so I'm suspecting that maybe this is pretty much as good as it will get?

Is there any way to write a query that just seeks into that composite index key and then follows it along to retrieve the next chunk of 1000 rows?

A favourite solution of mine is to use an API cursor:

SET NOCOUNT ON;
SET STATISTICS IO ON;

DECLARE
    @cur integer,
    -- FAST_FORWARD, AUTO_FETCH, AUTO_CLOSE, CHECK_ACCEPTED_TYPES, FAST_FORWARD_ACCEPTABLE
    @scrollopt integer = 16 | 8192 | 16384 | 32768 | 1048576,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536,
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return (up to) the first 1000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT A, B, C
    FROM T
    ORDER BY A, B;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur,
        2,
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

SET STATISTICS IO OFF;

The overall strategy is a single scan that remembers its position between calls. Using an API cursor means we can return a block of rows rather than one at a time as would be the case with a T-SQL cursor:

The STATISTICS IO output is:

Table 'T'. Scan count 1, logical reads 1011, physical reads 0, read-ahead reads 0
Table 'T'. Scan count 1, logical reads 1001, physical reads 0, read-ahead reads 0
Table 'T'. Scan count 1, logical reads 516, physical reads 0, read-ahead reads 0


How to get the size of the result of a SQL query? How do you get the size (in bytes) of the result of a SQL query, in MySQL 5.6?

EXPLAIN will return the number of fetched rows, but not their size.

My aim is to evaluate common queries to know a lower bound value for query_cache_limit so that they can be served by the Query Cache.

There is no way.

EXPLAIN gives you an estimation; do not count on it being exact.
The number of bytes in a resultset is not directly available.  And attempts to estimate will be fraught with errors.
The structure of the QC is another variable -- there is overhead to take into account.
Since every row for a table in the QC is purged when any write to the table occurs, the QC is rarely useful at all.


Retrieve Data that do not exist in the primary table, exist in the secondary only Problem:
I would like to retrieve the value that do not exist in the list table b but it exist in table a.

I don't know how to do it.

Please remember that the data will change very often.

SELECT [id]
      ,[name]
      ,[email]
      ,[phone]
      ,[cellphone]
      ,[none]
  FROM [test].[dbo].[datatable]

SELECT [id]
      ,[name]
      ,[email]
      ,[phone]
      ,[cellphone]
      ,[none]
  FROM [test].[dbo].[datatable2]

Use EXCEPT:

SELECT [id]
  ,[name]
  ,[email]
  ,[phone]
  ,[cellphone]
  ,[none]
FROM [test].[dbo].[datatable1]
EXCEPT
SELECT [id]
  ,[name]
  ,[email]
  ,[phone]
  ,[cellphone]
  ,[none]
FROM [test].[dbo].[datatable]

This will give you all the rows that exists in datatable1 but do not exist in datatable.

If you want it only for the keys then you should use

select t1.[id]
from   [test].[dbo].[datatable1] AS t1
where  not exists (select t2.[id]
                   from   [test].[dbo].[datatable] AS t2
                   where t2.[id] = t1.[id]);


mysql data types: define as char but work as int I have to create a table including a column named tax. The data values of that column are such as 20k, 15k, and so on. So I used varchar(10) data type for that column. But when I have to do calculation or comparison , (eg) to find the tax more than 15k,   I have to use that as an int (tax>15). Is the correct way? Or is there any better way?
I consider to change the column name as “tax(k)”. But I didn’t because I want to keep the table as the original table. In the original table, the column name is just tax and the data values are 10k, 15k and so on.

If there is no specific requirement for you to store the data in that manner then you SHOULD change the datatype of the column and make it to INT or Decimal as per your requirement.( This would be a suggestible approach as you will need to do a lot of calculations and there is no inbuilt function to calculate 'K' values)

However if there is not way out, you can use a replace function to replace the 'K' with a space or '000' and use INT/DECIMAL function to  convert the same for your  calculations.  Syntax would be something as below

SELECT * FROM TAX_TABLE WHERE CAST(REPLACE(TAX,'K','') AS DECIMAL) > 15

OR

SELECT * FROM TAX_TABLE WHERE CAST(REPLACE(TAX,'K','000') AS DECIMAL) > 15000


Restore InnoDB tables / database using only data files I have MySQL Server 5.1.51 installed on Windows Server 2003 as Operating System and using InnoDB as database engine.

I have run DROP DATABASE dbName statement by mistake. I have no backup of that database and not enabled binary logging on server. But I have used one data file for all database. I have access to that data file (ibdata1) and log files (ib_logfile0, ib_logfile0).

May I restore the database using data files and log files?

If yes, then How can I do that?
What are the steps that I have to follow to restore the database?

Unfortunately, there is really not an easy way to get this table back. Although the data is likely still in the ibdata1 file, only specialized tools can recover it. If this data is business critical, some consulting companies such as Percona can help you to recover it, but that will be quite expensive (thousands of dollars at least), and not all of the data may be recoverable.

If this data is important to you enough to spend the money or time recovering it, save a copy of the ibdata1 file immediately as the disk space will be more likely to be re-used the longer the server is running.


Database after restore process still display Restoring I restored Database from Backup File (in Management Studio) and after restore, Database showed Restoring, then I deleted files from disk (engine's stopped), then I tried Restore again with success, on both cases I leave Restore Process on Default Options

I have no idea why, dump file I created after restart of enviroment, without active Connection(s)

my question: is possible somehow to stop the Restoring status, if is there some tools for that

By default, a DB is not ready to use. It stays "restoring" ready to receive log restores.

Simply run this to bring the DB on-line

RESTORE DATABASE MyDB WITH RECOVERY

Or using the GUI, this is step 12 in the MSDN SSMS "how to" (a different place to what you mention)


Oracle: update table with object type udt I have this Oracle code:

Create type address_ty as object( street varchar2(50),city varchar2(50),prov varchar2(50),cap number);

Create type person_ty as object( name varchar2(50), address address_ty);

Create table client (client_id number,person person_ty);

insert into client
values(1,person_ty('pinco pallo',
         address_ty('Boulevard street' ,'torino','to',10100)));
Commit;

select c.client_id,
     c.person.name,
     c.person.address.street,
     c.person.address.city,
     c.person.address.prov,
     c.person.address.cap
from client c;

Everything works good.

Now i want to update c.person.address.prov

I tried with:

update client
set client.person.address.prov='MI'
where client.client_id=1

But i get an error.

Ora-00904: "client.person.address.prov" invalid identifier.

How can i update the field prov??

Thank you for your answer.

Use an alias. It is mandatory when updating a table with an object type.
UPDATE

  t_alias

  Specify a correlation name (alias) for the table, view, or subquery to
  be referenced elsewhere in the statement. This alias is required if
  the DML_table_expression_clause references any object type attributes
  or object type methods.

Create table client (client_id number,person person_ty);

insert into client
values(1,person_ty('pinco pallo',
         address_ty('Boulevard street' ,'torino','to',10100)));
Commit;

...

update client c
set c.person.address.prov='MI'
where c.client_id=1;

1 row updated.


Count multiple columns grouped by date My current query looks like this

SELECT `task`, COUNT(*) as sum
FROM tasks
where `datetask` between ? and ?
  AND `taskstatus` = ?
GROUP BY DATE(`datetask`)
ORDER BY `datetask` ASC

Parameter status is currently 1. What If I wanted to make multiple status count up? Like 3-4 per date

"query": [
      {
        "datetask": "2015-11-04 00:00:00",
        "status1": "1"
      },
      {
        "datetask": "2015-11-05 00:00:00",
        "status1": "1"
      }
    ]

What I want to accomplish

DATE         | Status 1  | Status 2 | Status 3
---------------------------------------------
2015-11-04   | 3         | 3        | 3
2015-11-05   | 2         | 3        |
2015-11-06   | 5         | 3        | 3

First, some comment on the existing query:

Every expression that is not in the GROUP BY list should not appear in the SELECT list, unless it is inside an aggregate function (SUM(), COUNT(), etc). So that SELECT task should probably be changed to SELECT DATE(datetask) AS date_of_task.
It's not good to use BETWEEN .. AND .. for ranges of datetime and timestamp columns. The condition WHERE datetask BETWEEN '2015-10-01' AND '2015-10-31' will seemingly gather all rows of this October. Except that almost all the rows of the 31st of October will escape!

The reason is that the condition is equivalent to WHERE datetask BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 00:00:00' and all rows with datetime values that are not in midnight exactly (00:00:00) will fail the condition and will not be counted in the query.

Even if you use something like WHERE datetask BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 23:59:59', you have a few drawbacks: It's more complicated to write. It will work but only if the precision of the dtateime/timestamp is 1 second. If the column has millisecond or microsecond precision (this is allowed in 5.6+ versions), you'll have to change the condition to have ... AND '2015-10-31 23:59:59.999999'.

My suggestion is to use the WHERE column >=? AND column < (? + INTERVAL 1 DAY) pattern. It's guaranteed to work on all DATE, DATETIME and TIMESTAMP types of arbitrary precision.

Now for the multiple counts, there are several methods. Perhaps the easiest to write is:

SELECT DATE(datetask) AS date_of_task,
       COUNT(CASE WHEN status = 1 THEN 1 END) AS status_1,
       COUNT(CASE WHEN status = 2 THEN 1 END) AS status_2,
       COUNT(CASE WHEN status = 3 THEN 1 END) AS status_3
FROM tasks
WHERE datetask >= ? AND datetask < (? + INTERVAL 1 DAY)
  AND taskstatus = ?
GROUP BY DATE(datetask)
ORDER BY date_of_task ASC ;

which  can be simplified a bit, to:

SELECT DATE(datetask) AS date_of_task,
       SUM(status = 1) AS status_1,
       SUM(status = 2) AS status_2,
       SUM(status = 3) AS status_3
FROM tasks
WHERE datetask >= ? AND datetask < (? + INTERVAL 1 DAY)
  AND taskstatus = ?
GROUP BY DATE(datetask) ASC ;    -- combine GROUP BY and ORDER BY


ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' I am getting this error as a result of performing Insert and Select operation which involves calling of number of pl/sql procedures and functions. I am not getting exact reason. I tried optimizing queries I reduced fetch count of rows as well as reduces Inserting number of rows. What is the exact reason for this?

UNDO is the area that stores the change vectors required to restore the data to the state that it was in at the beginning of the transaction.

The only cures for this are to reduce the amount of data that you're changing (by changing less data or reducing the transaction size), or to increase the available undo size.

Reducing the transaction size, which you'd do by committing more frequently, is problematic because you still have to deal with rolling back transactions that you've committed, and because it would also promote snapshot too old errors when the database cannot reconstruct from undo the data required for consistency with the start of a query.

So, as commenters say, the answer really is to have the DBA's adjust the size of UNDO available. Strangely, they can be reluctant to do so, or at least to do so adequately. If you currently have 256MB of UNDO and you ask for 2GB, they might see this as some huge amount that might in some way be undesirable and offer you 1GB. Smile indulgently at them, because you already doubled the amount that you really wanted and because there is no such thing as oversizing your UNDO tablespace.


How to solve this complex insertion problem In our database we store policycase records. A policycase has a 1:1 relation to a (requested) policy record. The policy record has a 1:n relationship to policy_map. Where policy_map is a key, value table.

When a policycase is changed from our user application, the complete policycase record and all it's child records are copied and stored again. At least, that was the requirement. Due to a bug only a small number of the policy_map records are copied to the new policycase.

We want to execute an insert query that creates the missing policy_map records for the copied record, when they do not exist.

The old policycase can be recognized from a status HISTORICAL and the copied policycase from a status ARCHIVED. Both policycases have the same uniqueid field.

So the following select query gives me the policy_map records for the historical and the archived (copied) policycase records for a certain uniqueid:

SELECT
    policycase.status,
    policy.id,
    policy.uniqueid,
    policy_map.map_key,
    policy_map.map_value
FROM policycase
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'

The result of this query is something like:

+------------+--------+----------+--------------------------------+-----------+
|   status   |   id   | uniqueid |            map_key             | map_value |
+------------+--------+----------+--------------------------------+-----------+
| HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE      |
| HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J         |
| ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE      |
| ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE      |
| ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED      |
+------------+--------+----------+--------------------------------+-----------+

In the table above, the map records

stateOfMaintenanceRoof
unsecuredGuaranteeHouseContent
unsecuredGuaranteeHouseFire

need to be copied to the policy_map table, related to the ARCHIVED policycase. That needs to be done for every uniqueid in the database.

How would a query or function that accomplishes this look?

We are running Postgres 9.6 on Amazon RDS.

Setup:

create table policycase
(requested_id int
,status       varchar(30));

insert into policycase values (438957,'HISTORICAL');
insert into policycase values (451423,'ARCHIVED');

create table policy
(id           int
,uniqueid     varchar(10));

insert into policy values (438957, '91157235');
insert into policy values (451423, '91157235');

create table policy_map
(policy_id    int
,map_key      varchar(100)
,map_value    varchar(30));

insert into policy_map values (438957, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (438957, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (438957, 'stateOfMaintenance'            , 'GOED');
insert into policy_map values (438957, 'stateOfMaintenanceRoof'        , '0');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseContent', 'J');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseFire'   , 'J');

insert into policy_map values (451423, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (451423, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (451423, 'stateOfMaintenance'            , 'GOED');

Before insert:

SELECT
    policycase.status,
    policy.id,
    policy.uniqueid,
    policy_map.map_key,
    policy_map.map_value
FROM policycase
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;

status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED

The proposed INSERT:

insert into policy_map

select p2.id,
       pm1.map_key,
       pm1.map_value

from   policycase pc1
join   policy p1
on     pc1.requested_id = p1.id

join   policy_map pm1
on     pm1.policy_id = p1.id

-- join with p2/pc2 to obtain id for ARCHIVED rows
join   policy p2
on     p2.uniqueid  = p1.uniqueid

join   policycase pc2
on     pc2.requested_id = p2.id

where  p1.uniqueid = '91157235'
and    pc1.status  = 'HISTORICAL'
and    pc2.status  = 'ARCHIVED'

and    not exists(select 1
                  from   policycase pcx
                  join   policy px
                  on     pcx.requested_id = px.id

                  join   policy_map pmx
                  on     pmx.policy_id    = px.id

                  where  px.uniqueid      = p1.uniqueid
                  and    pcx.status       = 'ARCHIVED'

                  and    pmx.map_key      = pm1.map_key
                  and    pmx.map_value    = pm1.map_value);

NOTE: There may be a way to eliminate some of the joins but I'll leave it like this for now as it's easier (for me) to understand what's going oin.

After insert:

SELECT
    policycase.status,
    policy.id,
    policy.uniqueid,
    policy_map.map_key,
    policy_map.map_value
FROM policycase
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;

status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED
ARCHIVED   | 451423 | 91157235 | stateOfMaintenanceRoof         | 0
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseContent | J
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseFire    | J

And here's a dbfiddle


Visio 2010 and SQL 2012 - Reverse Enginnering We have upgraded to SQL 2012 and Am trying to reverse engineer our Dbs into Visio 2010.  I setup a connecting using SQL 11 driver and Visio complains that that driver is not supported.

Any one run into this?

The Generic OleDB Driver also works for reverse engineering a SQL Server 2012 database using Visio 2010.

(Might not be "supported", but this works).  Select the Generic OLE DB Provider from the list of Installed Visio drivers and click Next.  Then pick the Microsoft OLD DB Provider for SQL Server and then go to the Connection tab.

Enter in your server name,
choose the authentication method,
Select the database name from dropdown (or build from file) and click the Test Connection button.

I didn't need to do anything with the Advanced or All tabs after the connection succeeded, just clicked OK and was able to pick the object types and list of objects and it exported everything to the diagram.


Does mysqldump export indices, by default? I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:

--disable-keys, -K

which suggests, that it actually does export the indices. But I don't want to trust my interpretation and I want to make sure I got it right (or wrong ;-)). Can anyone confirm that?

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs:

DROP TABLE IF EXISTS `tblAccountLinks`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tblAccountLinks` (
  `ID` int(11) NOT NULL auto_increment,
  `FirmNo` varchar(10) NOT NULL,
  `CustomerNo` varchar(20) NOT NULL,
  `AccountNo` varchar(20) NOT NULL,
  `LinkType` smallint(6) NOT NULL,
  `AccessLevel` smallint(6) NOT NULL,
  `Status` smallint(6) NOT NULL,
  `CreatedOn` datetime NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=27023 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `tblAccountLinks`
--

LOCK TABLES `tblAccountLinks` WRITE;
/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;
INSERT INTO `tblAccountLinks` VALUES (1,'F0001','C001','T00000001',1,2,1,'2008-06-30 07:55:43'),(2,'
F0001','C001','T00000002',2,2,1,'2008-06-30 07:55:43'),(3,'F0001','C002','27601012',1,2,1,'2008-06-3 ...

The line after LOCK TABLES is

/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;

This is what the --disable-keys option embeds in the mysqldump.

Also, this is embedded after all the INSERTs are done

/*!40000 ALTER TABLE `tblAccountLinks` ENABLE KEYS */;
UNLOCK TABLES;

CAVEAT #1

  DISABLE KEYS and ENABLE KEYS were
  implemented to disable the reloading
  of nonunique indexes while a table is
  being reloaded. Primary Keys and
  Unique Keys are not disabled. They are
  loaded at the same moment the INSERTs
  are being. Once you ENABLE KEYS, the
  nonunique indexes are rebuilt via
  sorting (or using the MyISAM key cache is there isn't
  enough available memory)

  Unfortunately, DISABLE KEYS and ENABLE
  KEYS only work for MyISAM tables, not
  InnoDB.

CAVEAT #2

You do not have to --disable-keys. You could disable DISABLE KEYS (no pun intended) them with --skip-disable-keys:

  -K, --disable-keys  '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
                      '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
                      in the output.
                      (Defaults to on; use --skip-disable-keys to disable.)

This could result in a slower load and a potential lopsiding of the index pages for nonunique indexes.

CAVEAT #3

You can dump the actual InnoDB tablespaces (MySQL 5.5.12)

  -Y, --all-tablespaces
                      Dump all the tablespaces.
  -y, --no-tablespaces
                      Do not dump any tablespace information.


Limiting database security A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which would affect each aspect.  This is what I have come up with - are they any good?

Availability - DDOS attack Integrity
Secrecy - SQL Injection attack
Integrity - Use of trojans to gain access to objects with higher security roles

SQL Injection would be straightforward if scripting code was not properly sanitizing its inputs.

Here is a good post on SQL Injection Prevention via Stored Procedures : Do stored procedures prevent SQL injection?

Interestingly, even in the book MySQL 5.0 Certification Study Guide, Chapter 33 is all about using Stored Procedures and Triggers for Administration. In it are three bulletpoints on Page 470:

  For the tables in question, disallow direct access to by ordinary
  users for INSERT, UPDATE, and DELETE statements. (You can do this be
  granting the appropriate privileges to administrative users only.)

  Implement a procedural interface for modifying each table. That is,
  using an administrative account that has access to the table, write
  stored procedure that have DEFINER security and that perform the
  required modifications to the table, giving appropriate data values as
  parameters. Grant the EXECUTE privilege for these routines to the
  appropriate users.

  Require users to perform table modifications by calling the stored
  procedures and passing column values for the rows to be modified as
  parameters. Each procedure examines its arguments and verifies that
  they satisfy whatever constraints are deemed necessary. If the
  arguments are suitable, the procedure performs the requested
  modification. If they are not, the procedure aborts the operation.

In another mysql aspect, you have to secure user authentication. The most overlooked aspect is looking for the mysql schema tables. I wrote about this before in terms of how user authentication works and how to clean it up at install time and later afterwards:

What is the mysql.db table used for?
MySQL : Why are there "test" entries in mysql.db?
Is this a normal set of MySQL privileges?
Secure method beside phpMyAdmin for remote mysql


Connect Apache Http server with Oracle I have problem with connect ApacheHttpServer with Oracle. All work on OpenSuse 12.3

At first I install Oracle instal client 11.2. Set environment variables:

ORACLE_HOME=/usr/local/instantclient_11_2/
LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_HOME LD_LIBRARY_PATH PATH TNS_ADMIN

After that I compile apache http :

#apr
./configure --prefix=/usr/local/apache2/apr
make
make install

#apr-util
./configure --prefix=/usr/local/apache2/apr-util --with-apr=/usr/local/apache2/apr/
make
make install

#httpd
CPPFLAGS=-IORACLE_HOME/include LDFLAGS=-LORACLE_HOME ./configure --prefix=/usr/local/apache2/httpd --with-apr=/usr/local/apache2/apr/ --with-apr-util=/usr/local/apache2/apr-util/
make
make install

Unfortunately, the installation was not successful because mod_dbd_oracle.so didn't created. I think I was connected with that:

#./configure output:

checking oci.h usability... yes
checking oci.h presence... yes
checking for oci.h... yes
checking for OCIEnvCreate in -lclntsh... no
   setting LIBS to "-lnnz11"
checking for OCIEnvCreate in -lclntsh... no
   nulling LIBS
   setting LIBS to "-lnnz10"

I could not find a way how to solve. So I decided to use Oracle with ODBC driver. I install unixODBC 2.2.12-219.1.1.

After that I modife to odbc.ini and odbcinst.ini:

#odbc.ini
[xe]
DRIVER = oraclevb
DSN = xe
ServerName = //192.168.56.101:1521/xe
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10`enter code here`
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F

 #odbcinst.ini:
[oraclevb]
Description = Oracle ODBC
Driver = /usr/local/instantclient_11_2/libsqora.so.11.1
Setup =
FileUsage =
CPTimeout =
CPReuse =

I chcek how it work with command:

isql -v xe user passowrd

All was OK. So I again compile Apache Http Source. Modify httpd.conf to use ODBC:

LoadModule authn_dbd_module modules/mod_authn_dbd.so
LoadModule authn_core_module modules/mod_authn_core.so
LoadModule authz_host_module modules/mod_authz_host.so
LoadModule authz_user_module modules/mod_authz_user.so
LoadModule authz_dbd_module modules/mod_authz_dbd.so
LoadModule authz_core_module modules/mod_authz_core.so

LoadModule access_compat_module modules/mod_access_compat.so
LoadModule auth_basic_module modules/mod_auth_basic.so
LoadModule dbd_module modules/mod_dbd.so

DBDriver odbc
DBDParams "datasource=xe"

<Directory "/sample">
     AuthType Basic
     AuthName "private area"
     AuthBasicProvider dbd
     AuthDBDUserPWQuery "SELECT password FROM USERS WHERE name = %s"
     AuthzDBDQuery "SELECT group FROM GROUPS user_name = %s"
     Require dbd-group grup1 grup2
</Directory>

Start Apache HTTTP Server and I found in error logs:

[Sun Jul 14 15:19:07 2013] [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at dbd/apr_dbd_odbc.c:1130 [unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient_11_2/libsqora.so.11.1' : file not found 01000
[Sun Jul 14 15:19:07.332394 2013] [dbd:error] [pid 13374:tid 140250220050240] (20014)Internal error: AH00629: Can't connect to odbc: [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at dbd/apr_dbd_odbc.c:1130 [unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient_11_2/libsqora.so.11.1' : file not found 01000

I don't know what I can do with that. Any suggestions would be very helpful. Maybe there is another way that conduct authorization and authentication using the Oracle database. Or other Apache module?

I would try to go with Oracle without ODBC. ODBC adds one more layer and both Oracle and ODBC usually are not as well tested as MySQL or PostgreSQL. Like others noticed the problem is with checking for OCIEnvCreate in -lclntsh... no. The most probably configure cannot find required libraries. You should try two things:
 1. Check config.log for error messages about OCIEnvCreate.
 2. Shouldn't httpd's configure command line say LDFLAGS=-L$ORACLE_HOME/lib? Of course I install instant client from RPMs but libclntsh.so is in lib/ subdirectory.


Login failed for user 'NT Service\SSISScaleOutMaster140' In times of creating replication by using below query I getting from log

Login failed for user 'NT Service\SSISScaleOutMaster140'

DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = N'ServerName'
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = N'ServerName;
-- Specify the replication working directory.
SET @directory = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\repldata'
-- Specify the publication database.
SET @publicationDB = N'databasename';

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB,
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks2012 for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = N'ServerName;

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher,
    @distribution_db=@distributionDB,
    @security_mode = 1;
GO

Not sure it this is exactly what you need for replication but it is worth a quick try.

SQL Server uses the server SID as part of the password encryption/generation. Often just creating the same user name and password on 2 servers that are in an Availability Group or Mirrored or Replicated, maybe for Linked servers as well. The general term for these cases is 'Orphaned Users'. You will see it often in your career.

So just using the same login name and password will give an error. Usually when something fails over at 3AM because it wasn't tested...

There are two ways to do this. The 'old school' way is to go to this link ( https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server ) and run the included script on the server that works, meaning you can login to that server. The script will create two stored procedures in Master. You execute the sp_help_revlogin SP on the working server, it will generate SQL code that you can copy and paste into the non-working server.

From your explanation I suspect you will get an error about the login already existing, go ahead and drop the user on the non-working server. It will get created from the generated script.

The more 'modern' way is to use PowerShell, specifically the Copy-SqlLogin and Export-SqlLogin found at https://dbatools.io/

If you are comfortable using PowerShell, these should work well and are pretty well documented on the website.

Hopefully this will help or at least point to a better answer.

Norman


Configuring Oracle AWR reports Is it possible to configure Oracle so that AWR reports contain more that the top ten SQL queries by elapsed time and also include the explain plan for those queries?

You can use DBA_HIST_SQL_PLAN view to get the execution plan for SQL statement. In order to include more SQLs in your report you have to change AWR setting as shown below.

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHORT_SETTINGS(
RETENTION => 7200,
INTERVAL =>60 ,
TOPNSQL =>10 , --Here you can define the number of SQL to collect at each AWR snapsho
DBID => 123661118);
END;
/

You can get the SQL_ID from the AWR reports and can get their execution plan as shown below:

SQL>select plan_table_output from table (dbms_xplan.display_awr('&SQLID'));

Another way is to use Oracle supplied script called awrsqrpt.sql which can be found under $ORACLE_HOME/rdbms/admin directory.
During the execution of the script it asks for the SQL_ID for which you want to get the execution plan.

References:

MODIFY_SNAPSHOT_SETTINGS Procedures
DBA_HIST_SQL_PLAN
How to Control the Number of SQL Statements and other information displayed in AWR Report (Doc ID 1357637.1)


Postgresql 10.x installer configures password wrong? (Windows) I am new to PostgreSQL, but I just installed 10.x and was asked to type a root/admin password which I could  not leave empty. I hence entered a password I know very well since i only intend for this to be a localhost database.

But when prompted by 3rd party application which uses PostgresSQL to login I only know password - and I can not login correctly.

Being optimistic I tried to start pgbench.exe from commandline which asks for password - but it says that password authentication failed for my name.

Is here anyway to reset password and setup a proper user/password account or somehow fix the situation? And maybe some kind of frontend tool I can use for testing? I have searched for pg_hba file, but I only have a "sample" file.

I have installed into path D:\databases\Postgresql\10

If you are using the EDB installer, when it asks for a password, that is the password for the PostgreSQL user named postgres, not for the user named tom.

Perhaps there is a way to make our 3rd party app connect as postgres?

Or if you want it to use the database role whose name matches your Windows account name, you should be able to connect with

psql.exe -U postgres postgres

And then:

create user tom with password 'hi tom';


Syncing two databases in a redundant network architecture I've been reading posts on here about database mirroring and it's an almost overwhelming amount of options and information, but as I'm still new to most of the SQL database scene, I was hoping to find a more definitive answer to my situation:

My company has two 2008 R2 SQL Server databases, which exist on the same network and contain the same information. They are both updated simultaneously as new system data comes in from the automated processes they record, so all the records in both databases match perfectly. The concern in this scenario is that when one database goes down, the other is meant to kick in as the backup, and then when the problematic database is back up, it will be missing the records that were created during down time.

The question:

What would be the best method for retroactively syncing data between the two after one of them has crashed and been brought back online?

As many of the other posts have asked more questions back about what the company will / will not allow per the DBA or other permissions that might exist, I'd like to just state that it is safe to work off the assumption that all other concerns or conflicts have been taken care of, and we purely need to just migrate data to keep each database up to date with the other after a crash. In terms of a master / slave relationship, I don't believe one currently exists, as neither database is getting its data from the other (so long as they are both running); rather, it's coming from a tag-based program that monitors the afore-mentioned automated system

EDIT:

Did some looking into using the Publication / Subscription method through the REPLICATION folder you can find in SSMS and this seems plausible. The options appear as if it allows for immediate and continuous data sharing - would this be a viable option?

That's tough. Even 5 minutes of downtime for a reboot would put you in a situation with out of sync data. I really think you should look into other options.

I'd definitely recommend mirroring here instead of what the app is doing. With Synchronous Database Mirroring with a witness, the two databases will be in sync and you'll even get automatic failovers. It's not hard to setup either.

You could use Transaction Replication as it can handle a server being down and pickup where it left off. But I don't consider it to be an HA or DR technology. Mirroring, Log Shipping and Availability Groups are the HADR technologies. Specifically Sync mirroring and Sync AG are HA, async mirroring, LS and async AG would be DR.


Drop/Create MongoDB TTL index vs. collMod in production I'm working with a MongoDB collection containing over 250 million documents that has an existing TTL index and is written to with a pretty high frequency. I'd like to reduce the TTL value for the index from 60 days to 30 days, while minimizing production impact. It seems that I have two options: dropping and recreating the index in the background or running the collMod command to modify the index. The simplicity of the collMod command is appealing, but I'm concerned about the impact it might have, especially since there appears to be no way to run it in the background. Would dropping/recreating the index lead to less lock contention and have less of an impact?

The first you need to do is calculate how many documents the index will delete if you change the 60 days into 30. That is a simple count with {TTL_FIELD: $gt:30days}.

If the number of documents is big (how "big" depends from the server specs and the workload) I recommend to run an incremental Colmod like (pseudocode):

While achieve 30 days {

NUM = 60 days;

NUM = NUM - threshold;

db.runCommand( {"collMod" : "COLLLECTION" , "index" : { "keyPattern" : {TTL_FIELD : 1} , "expireAfterSeconds" : NUM } } )

wait 2 minutes;}

If the number of deleted document is small just do the collMod at once or on 2-3 steps manually.

Your approach to drop and recreate the index takes more time and if you have lot of documents for deletion you will get performance degradation when the index completes.

Hope that helps


How memory allocation happened in SQL Server Suppose I have a 32 GB machine, now I have allocated 12GB memory to the maximum server memory for my database application to perform. Now I am running some load simulation which is going to consume the entire 12GB. Now SQL Server is run out of memory when it required more memory, OS is going to assign more memory:

Now here is my thought, the scheduler will check the priority of the task if the task has got the highest priority then, how the memory is going to allocate? if so how much % of the rest available memory will allocate to SQL Server. Is it entire or it will assign periodically and how much memory will allocate?

If the above condition is false then the alternate concept is (note this is what I think), if the task has got the lowest priority then it will wait for its priority to come till the time application will throw out memory exception.

Is the maximum server memory parameter play a role in memory allocation in the SQL server? How important it is for memory allocation and troubleshooting?

Now SQL Server is run out of memory when it required more memory, OS is going to assign more memory:

By OS I believe you mean Windows OS, if so, no windows OS does not do memory management of SQL Server it is taken care by SQLOS (Specific OS built for SQL Server) primarily which has component memory manager(MM). This MM takes care of memory allocations and interacts with Windows OS.

For the other part if you are running something which is going to consume entire 12 GB yes you may get OOM error, but its rare to see such process.

  Now here is my thought, the scheduler will check the priority of the task if the task has got the highest priority then, how the memory is going to allocate?

AFAIK SQL Server does not allocates memory as per task priority if you boost priority the task would get higher "CPU time" not memory.. The memory allocation would be done normally as it should be, subject to condition how much free memory is available.

  Is the maximum server memory parameter play a role in memory allocation in the SQL server? How important it is for memory allocation and troubleshooting?

Yes very much, Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Basically max server memory control how much maximum memory SQL Server can utilize.

NOTE: Their are some direct memory allocations which are not tracked by clerks and are done directly to windows, if such request comes this can further increase SQL Server memory ( more than specified in max server memory value).

PS: The topic is very deep it may not be completely possible to make you understand with simple answer, so I would suggest you to read SQL Server Memory Architecture Guide which I have added as a link.


Use PL/pgSQL in PostgreSQL outside of a stored procedure or function? I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can I do this in PostgreSQL?

There is the DO command for ad-hoc plpgsql execution.
But you cannot pass parameters or return rows directly to / from a DO command.
BTW, strictly speaking, Postgres does not have "stored procedures" before Postgres 11. Just functions providing very similar functionality. See:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?


Is "TABLESAMPLE BERNOULLI(1)" not very random at all? Whenever I need to return a random record from my tables and performance matters, instead of:

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

I always do:

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

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

The probability of a row to be returned from TABLESAMPLE BERNOULLI(1) is 1/100, that is, 0.01. The presence of LIMIT 1 tells Postgres to stop reading the table after the first match. This means that on average one of the first 100 rows (in whichever order they are scanned by the engine) in the table will be retrieved.

The probability of any particular row to be returned after ORDER BY random() LIMIT 1 is, on the other hand, 1/C, where C is the table cardinality. The entire table will be read and randomly ordered, then one row returned. This means that if your table has more than 100 rows, the ORDER BY random() will return a row from a wider selection (or, as you put it, will be "more random").

Some background info here.


Oracle 11gR2 Installation on Oracle linux 7 - Prerequisite condition failed for OS kernel parameter "semmni" while Installing oracle 11gr2 on oracle Linux 7. The prerequisite condition to test whether the OS kernel parameter "semmni" is failed with below error.

Please find the screenshot of it.

Below is the kernel parameters which are configured in the etc/sysctl.conf file.

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
#semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 256000 100 1024
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Any idea why it is failing?

Having the correct value in sysctl.conf does not mean that is the current setting in effect system-wide.

You can check the current value in effect with:

sysctl -a | grep "kernel.sem"

If it differs from the one in the file configured, you need to actualize it with

sysctl -p

Or it will happen on the next reboot automatically.


Grant select to dbo view without select on non dbo tables SQL Server 2008 R2

We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database.

We then granted it SELECT on a dbo view - the dbo view however selects data from underlying tables that belong to a non-dbo schema - so we get an error selecting from the view saying access was denied to underlying tables.

How does one configure the user to have select access to the dbo view but not the underlying non-dbo tables?

Is this even possible?

Jordon

You have to understand the problem before proceeding with the solution.
When you select from a view the SQL Server system would check permissions twice:
Once when you select from the view, and once when the view makes reference to the underlying table.
Obviously the second check would fail if the user has no permission on the underlying table.

Microsoft have solved this with "Ownership Chaining" (known as OC).
OC will bypass the permission check that would be done when the view is referencing the table,
Only if the owner of the view is the same as the owner of the table.
The thing is, OC is bypassing permission checks completely, which means that it can bypass denies as well...
For example, if "JoeBlogs" has the create view permission, he can create a new view which has access to the entire table.
I suggest you read about ownership chaining before you decide your course of action.

books online
msdn blog
detailed example, using a stored procedure instead of view

Having said that, the first Intuitive solution would be to create the view under the same non-dbo schema as the underlying tables (or a schema owned by the same user as the non-dbo schema).
However, if you find that solution to be too risky there is another (and maybe even better) option:
You can always use a  function (Multistatement Table-valued Function) with an EXECUTE AS clause:
Create function syntax
execute as clause
This method will allow you to select from the function (execute permission on the function) while the function belongs to dbo. The user specified in the execute as clause must have permissions on the underlying tables.


Using a Calculated Field in a Separate Calculated Field I'm trying to avoid creating a temp table for just one single field, but I need to use a calculated field in a separate calculated field.  For example:

,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
        ELSE 0
         END AS RateReset

,CASE WHEN ln.TIME_RATE = 1 AND RateReset = 3 THEN 'Pass'
      ELSE 'FAIL'
        END AS OGIntRate

If you just need to hack this in, you can embed the first expression in the second:

,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
        ELSE 0
         END AS RateReset

,CASE WHEN ln.TIME_RATE = 1
     AND (CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
     ELSE 0 END) = 3 THEN 'Pass'
      ELSE 'FAIL'
        END AS OGIntRate

Although based on this very limited excerpt of the query, you should just be able to check for INT_RATE_CODE = 'C':

,CASE WHEN ln.TIME_RATE = 1 AND ln.INT_RATE_CODE = 'C' THEN 'Pass'
      ELSE 'FAIL'
        END AS OGIntRate

If you're able to change the table definition, you could add a computed column for the first CASE expression, and then reference it directly in the other CASE expression.  Like this:

ALTER TABLE YourTableName
ADD COLUMN RateReset AS CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1 WHEN ln.INT_RATE_CODE = 'B' THEN 2 WHEN ln.INT_RATE_CODE = 'C' THEN 3 ELSE 0 END;

Then OGIntRate could be based off of RateReset directly like you have in your original code.


Automatically updating a record using count() of another collection I have been looking at the docs and searching for videos on my problem, but I find nothing. I have a db called HPDASH where I have 2 collections, scripts and serverList. My problem is that I want to create a field for serverList that has the count() of the collection scripts.

I have tried this:
db.scripts.insert([{name: 'local_script'}, {name: 'qidsloader'}])
db.serverList.insert({script_count: db.scripts.count() })

The problem is that when I insert another record to scripts, the data on scripts_count stays as 2.

Is it possible to achieve this on MongoDB?

As you have noticed, MongoDB doesn't support naturally triggers. So, you need "programming skills" to do that functionality. You can use mongoTriggers, what can installed with mongo-triggers npm package.


Oracle big datafile restore fails using Netbackup (ORA-19511, ORA-27190) I am restoring datafiles about 1000GB in size. I am using Netbackup as a backup server.

After 2 hours reading Netbackup fails with the following error:

7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) socket read failed, An existing connection was forcibly closed by the remote host.  (10054)
7/25/2012 3:27:50 PM - restored image r2n1_1342889172 - (file read failed(13)); restore time 02:01:41
7/25/2012 3:27:54 PM - end Restore; elapsed time: 02:01:50
the restore failed to recover the requested files(5)

RMAN shows the following error:

channel ch1_tape: ORA-27192: skgfcls: sbtclose2 returned error - failed to close file
ORA-19511: Error received from media manager layer, error text:
   VxBSAEndTxn: Failed with error:
   The sequence of calls is incorrect.
ORA-19870: error while restoring backup piece
ORA-19501: read error on file , block number  (block size=512)
ORA-27190: skgfrd: sbtread2 returned error

I looked in  ...\NetBackup\logs\bpbrm and found the following:

15:03:03.132 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:03:11.681 [660.7908] <2> bpbrm read_media_msg: read from media manager: MEDIA NOT READY
15:03:11.681 [660.7908] <2> bpbrm signal_bpbrm_child: sending Media Ready to bpbrm child 1176
15:03:11.681 [660.7908] <2> bpbrm read_media_msg: read from media manager: EXIT r2n1_1342889172 0
15:03:11.681 [660.7908] <2> bpbrm process_media_msg: media manager for backup id r2n1_1342889172 exited with status 0: the requested operation was successfully completed
15:03:11.681 [660.7908] <2> bpbrm signal_bpbrm_child: sending Normal Exit to bpbrm child 1176
15:03:13.787 [1176.2356] <2> bpbrm mm_sig: received not ready signal from media manager
15:08:12.044 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 20

15:08:12.106 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:13:13.061 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 21

15:13:13.186 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:18:14.001 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 22

15:18:14.063 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:23:15.018 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 23

............

bpbrm send_parent_msg: CURRENT POSITION C047L4 8
15:26:58.145 [5412.7124] <2> bpbrm read_parent_msg: read from parent CONTINUE RESTORE
15:26:58.145 [5412.7124] <2> bpbrm tell_mm: sending media manager msg: CONTINUE RESTORE
15:26:59.081 [5412.7124] <2> bpbrm read_media_msg: read from media manager: MEDIA READY
15:26:59.081 [5412.7124] <2> bpbrm signal_bpbrm_child: sending Media Ready to bpbrm child 7468
15:27:01.343 [7468.4308] <2> bpbrm mm_sig: received ready signal from media manager
15:27:06.070 [7468.4308] <2> bpbrm handle_restore: from client r2n1t.justice.gov.ge: change timeout to 10800
15:27:50.234 [1176.2356] <16> bpbrm readline: socket read failed, An existing connection was forcibly closed by the remote host.  (10054)
15:27:50.265 [660.7908] <2> bpbrm brm_child_done: child done, status 13
15:27:50.265 [660.7908] <2> bpbrm brm_child_done: child 1176 exited with status 13: file read failed
15:27:50.265 [660.7908] <2> bpbrm send_status_to_parent: EXIT r2n1_1342889172 13 sent to parent process.
15:27:50.327 [660.7908] <2> bpbrm read_parent_msg: read from parent TERMINATE
15:27:50.327 [660.7908] <2> bpbrm tell_mm: sending media manager msg: TERMINATE

Note: I am able to successfully restore a small datafile size about 1.2GB.

Please if you have such kind of experience help me.
The issue is about restoring big files using Netbackup.

7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) socket read failed, An existing connection was forcibly closed by the remote host.  (10054)

This shows that it is trying to read from a socket that has been closed. It's a networking problem. Ask your network administrator to raise the firewall connection timeout between the database host and the netbackup host.


Adding columns upped my RAM usage significantly I have a Google Cloud Platform MySQL 2nd Gen 5.7 instance with two databases, one for testing and one for production.

There are about 20 or so tables. One of the tables accounts for 99% of the storage.

I added 8 columns to the largest table (it already has ~150 columns) of the testing db. 6 of the new columns are Integer and 2 of them are Boolean (or more accurately, TinyInt). No indexes of any kind added.

Below is a snapshot of the metrics from the migration operation. You can clearly see CPU and Storage peaks for each of the 8 columns.

Weird part is, RAM usage goes up an stays up. Any insight on what I may have forgotten to do?

The columns did not have any data populated to them, all are Null for each row so far

How many GB in the big table?

What was the setting of innodb_file_per_table?  Of innodb_buffer_pool_size?  Please provide the ALTER statement(s).

What probably happened was that the table was copied over to produce the new version with the new columns.  (8.0 can add columns without copying -- in some situations.)

If you had file_per_table OFF, then the copy would be in ibdata1, which won't shrink afterwards.  (The freed up space will be reused, just not returned to the OS, which you are monitoring.)

The buffer_pool is in RAM.  It grows as needed up to the configured limit, which I guess to be 5G.  The ALTER caused the sudden growth.  This is a "cache", so there is no 'permanent' harm.  Again, no shrinkage will happen.


SQL Server Reporting Server Migration I have a separate database server and a separate reporting server. I would like to upgrade and migrate the database server to a separate server and do not to touch the reporting server. Since the reporting server License comes with the SQL server license.
Can I keep the existing reporting server and use the database server on a different license, or should I migrate both, the database and the SQL server reporting server with the same license?

You already are not using the "same license" for both servers. SSRS is part of the SQL Server license (Standard Edition and above), but it's only covered by your SQL Server license on the same server. IOW, if your report server is on Server A and SQL Server is on Server B, you need to license them both.
So yes, if you have acquired a license for Server B to upgrade SQL Server, you can do that without touching Server A (report server). Server A will continue to be covered by its previous license.
Verify with whomever handles your SQL Server license purchases.


How can I add a rowversion column to a large table with minimal downtime Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply

ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL

Then the table is unavailable for updates for too long.

What strategies can I use to reduce this downtime? I'll consider anything. The simpler the better of course, but I'll consider any strategy.

My thinking is that as a last resort, I could maintain a copy staging table maintained by triggers and then sp_rename the staging table into the original table. But I'm hoping for something simpler/easier.

Consider creating a new table with the same schema plus the rowversion column, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some craptacular execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the craptacular execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

In the finish, Michael decided to skip the view (and not delete from the original table) to get more stable plans. The trade off was holding essentially two copies of the table. He turned it into a series of blog posts.


SQL Server - Non-reindexed Database I have a database that has not been reindexed in the last 5 years.

I want a yes or no answer if databases without index maintenance and "Missing Rows" are related to each other.

Database index fragmentation and missing rows are not related. Fragmentation is mismatch in ordering of physical pages and clustering key. A fragmentation can never cause rows to go "missing".
No a database without index is NOT prone to corruption. Index just makes things faster period.


How do I limit Oracle to use specific ports? I've built a new Oracle 9iR2 instance on a Windows 2003 32-bit cluster. Of course, it's version 9i for reasons out of my control.  Several application servers connect to this instance, and each opens several DEDICATED connections that they keep open for their work.

This instance is in a locked-down environment, so my network guys want to seal off as many ports as possible. To do that, I need to tell them precisely what ports or port ranges the instance needs open.

I've skimmed the Listener documentation, and I can see that I can change the default Listener port of 1521. However, every connection these application servers open against the database appears to get its own TCP port.

Is there any way to limit the range of ports these servers will use to connect to the database?

You may want to visit the Connection Manager documenatation  here

I have used it to concentrate connections - we had a client that needed to access our database over a VPN. The firewall was only configured to open 1 port for all their connections. We were able to handle all their traffic over this 1 port using CMAN. Your mileage may vary though, we just used it for a handful of connections, if you are servicing dozens or hundreds of connections CMAN may run out of steam.

Your network guys should also check with the firewall vendor, there may be the ability for the firewall to open ports after the listener hands them off... So the firewall still has control over what gets opened and when after Oracle authenticates the connection. I don't know what this feature called as I am not heavy on the network side, but we had to use CMAN for some of our firewalls that did not handle this.


Where mysql writes its log seqeunce numbers and last checkpoint at? I'm working on developing mysql log collecting system.
To do that, I have to collect log sequence numbers from mysql LSN and last checkpoint location.
But I have no idea where should
Which log file should i use to get them?

Well, that is handled in memory and transactionally stored on disk on the transaction log (by default, the ib_logfile0 and i_blogfile1. But if you just want to know its value, you can check SHOW ENGINE INNODB STATUS or simply:

mysql> SHOW GLOBAL STATUS like '%lsn%';
+----------------------------+-------------+
| Variable_name              | Value       |
+----------------------------+-------------+
| Innodb_lsn_current         | 25808994907 |
| Innodb_lsn_flushed         | 25808994907 |
| Innodb_lsn_last_checkpoint | 25808994907 |
+----------------------------+-------------+
3 rows in set (0.00 sec)

If you want MySQL to output that periodically to its error log, you can enable one of the InnoDB monitors.


Merge two applications using different NoSQL databases This is a very generic and high level set of queries related NoSQL type of datastores
Imagine I have two different applications using NoSQL datastores each hosting its own document collection.
Questions

What are the design considerations I should be aware of in case I wanted to merge these two applications together?

How can I redesign the datastore so that I can support both the applications from a single unified datastore.?

Apologies if this sounds vague, but this is the only clarity I have at the moment.
In the case of merging an RDBMS into NoSQL, I would have followed these steps:

Modelling of the RDBMS structure into NoSQL datastore
Dump the entire data from RDBMS into NoSQL with Select *
Change the application code wherever affected (ex: Insert/Delete/Lookup etc)

The important thing to remember here, is that a commonality NoSQL databases tend to share, is that a query-based approach needs to be taken with respect to table design.  The main driver for that, is going to be around how the data is queried.
Another consideration is around each data store's adherence to the CAP theorem.  NoSQL tends to side with the P of partition tolerance, and then also choose another side.  If the NoSQL products support different sides of CAP (AP vs. CP), you'll need to figure out if the overall application is more dependent on consistency or availability.
I would also consider if search is a part of the application, or rather, if the queries require any pattern matching.  Some data stores handle this better than others, so it would be good to understand how each functions in those use cases.


What are the risks for logging across databases via a trigger? I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here.

On SQL Server 2005, we currently log certain table changes via triggers using the inserted/deleted tables. Currently, our log tables exists in the same database as the primary tables and I was thinking that from a management perspective, moving the log tables to a different database has advantages. In the very least, log tables tend to grow at a factor of 10 for the source table, so managing the different types of tables is along different paths and separating them by db may be helpful.

If we go this route, the trigger has to log across db boundaries (same server though). What problems would this create that I don't have now?

So far, the main one seems to be that someone could take down a db independently of another (rare), and then the logging would be lost and insert would be kept, or inserts would fail b/c the trigger fails, depending on how the trigger was written.

Are there other risks?

Another solution that was offered was to log to the same db and have a job move (delete and copy) the records over to the other db. However, I still don't know why that's a better solution.

There aren't many issues beyond what you have now that I can think of off-hand (I'm sure someone will correct me though!). Since you are proposing to have your audit DB on the same server, and, I presume, instance, then by using three part naming you are all set. You've already noted the unlikely possibility of someone removing the audit DB and likewise there's a slim chance someone might re-name it, move it, leave it in an uncertain state by some failed maintenance and so on, but again these aren't that likely. If all your DBs are in the same file group or all on oen disk/array of disks then there might be a performance implication, depending on the level of traffic you expect so you might want to consider where your audit DB physically resides quite carefully: Same SQL instance doesn't have to mean the same physical disk.

With regards to trigger design, you would most likely be using AFTER INSERT and you could handle exceptions inside the trigger so that they are not raised to the caller and therefore the transaction completes on the parent table. Of course, you wouldn't be getting audit data then and you must decide if that is a problem for you and if so you'll need a suitable strategy for raising an alert of some kind. But you'll need to consider if it is right and acceptable to swallow exceptions in that way: Most developers I know (and I am one!) would look like you'd asked them to do something un-natural with a chicken if you asked them to bury exceptions in that way.

I'm not sure I understand the rationale for a job which copies and deletes records: That's a programming smell to me. Why over-complicate things? You're right to question it.


Extracting an an integer value from an xml column I'm just wondering how I can extract an integer out of an XML column. Currently all the digits are 4 which the below query is able to do. But the number is going to grow to 10,000's soon which will mean 5 digit and this query will not be able to do it. Is there anyway that I can dynamically get any integer out that is next in the middle of <siteID> and </siteID> from the Xml column. Any suggestion will be highly appreciated.

SELECT
SUBSTRING(msg, patindex('%[0-9][0-9][0-9][0-9]</siteID>%',msg),4)  AS DOMAIN
FROM table  a(NOLOCK)
WHERE msg like '<DLR%'
ORDER BY 1 DESC

You should use the value() method of the XML data type.

If your XML structure looks like this:

<DLR>
  <siteID>12345</siteID>
</DLR>

Your query would be

select msg.value('(/DLR/siteID/text())[1]', 'int') as DOMAIN
from dbo.YourTable

But it does however not look like you have an XML column at all since msg like '<DLR%' is invalid against XML columns.

Then you need to either modify your table so the column is XML or you can cast to XML in your query.

select cast(msg as xml).value('(/DLR/siteID/text())[1]', 'int') as DOMAIN
from dbo.YourTable


Postgresql 8.3: Limit resource consumption per query I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy.

This particular version of PostGIS has a bug when calculating  a buffer() on very complex segments, which causes the query to take more and more memory until the entire machine gets stuck.

I'm looking for a PostgreSQL mechanism that can:

Limit the memory consumption (and perhaps other resources) used by a specific query.
Automatically stop queries whose execution time exceeds a certain threshold.

Any ideas?

To limit memory consumption, the main configuration parameter is work_mem.  Since this applies per operation, not per query, you cannot simply set it to N if you want to spend N amount of memory, but you have to tweak and tune it a bit to get the desired result.

This doesn't help, however, in case of bugs and other memory leaks in the server code or extensions.  You could control that with the process-specific resource limits controlled by ulimit.  But if you reach the limit and memory allocation fails, what do you want to have happen?  It probably won't behave too nicely.  Better fix those bugs or use a different version.

The stop queries whose execution time passed a threshold, use the parameter `statement_timeout', e.g.,

SET statement_timeout TO '10min';


How to retrieve or reset the password on postgresql on linux I Have Installed Postgres on my Linux system, I forgot the admin password I want to reset to password how can I do that..

If you want to reset your password you need to go to the pg_hba.conf file. You can do this by giving in the following command into Linux.

sudo subl /etc/postgresql/12/main/pg_hba.conf

After gaining access to the file you must change all local locations from Method md5 to trust so you can enter Postgresql without permission or without being asked for a password.
Then place the following command into Linux:

sudo /etc/init.d/postgresql restart

Subsequently, run the succeeding command to get into PostgreSQL:

sudo -u postgres psql

And last but not least run the common SQL Command to reset the password for User:

ALTER USER postgres with PASSWORD 'anewremembrablepassword';

Of course, you could also use another user than postgres if you considerably created one. Make sure you go back to the pg_hba.conf file to change the methods back to md5 so you always give in a password as for better security or leave it and never give in a password.
I hope I could be helpful in solving this matter.
Thanks!