grant all privileges of all tables in database to a user I use oracle 12c. I try to grant all privileges of all tables to a user. And in future I don't need to add any privileges to that user. I need to do this. I don't want to grant system tables to that user. I wrote pl/sql command. but it gives error.

BEGIN
  2  FOR t IN (select * from dba_tables where owner not like '%SYS%' AND owner not like '%ADMIN%' AND iot_type IS NULL)
  3  LOOP
  4  EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO GGSTEST';
  5  END LOOP;
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 4

What is the mistake? thanks.

I use dbms_print

GRANT ALL ON OUTLN.OL$ TO GGSTEST
GRANT ALL ON OUTLN.OL$HINTS TO GGSTEST
GRANT ALL ON OUTLN.OL$NODES TO GGSTEST
GRANT ALL ON DBSNMP.BSLN_BASELINES TO GGSTEST
GRANT ALL ON APEX_040200.WWV_FLOW_LOG_HISTORY TO GGSTEST
GRANT ALL ON DBSNMP.BSLN_METRIC_DEFAULTS TO GGSTEST
GRANT ALL ON XDB.XDB$ROOT_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$XDB_READY TO GGSTEST
GRANT ALL ON XDB.XDB$CDBPORTS TO GGSTEST
GRANT ALL ON XDB.XDB$IMPORT_TT_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$TTSET TO GGSTEST
GRANT ALL ON XDB.XDB$H_INDEX TO GGSTEST
GRANT ALL ON XDB.XDB$D_LINK TO GGSTEST
GRANT ALL ON XDB.XDB$COLUMN_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$PATH_INDEX_PARAMS TO GGSTEST
GRANT ALL ON XDB.X$NM7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.X$QN7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.X$PT7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.APP_USERS_AND_ROLES TO GGSTEST
GRANT ALL ON XDB.APP_ROLE_MEMBERSHIP TO GGSTEST
GRANT ALL ON XDB.XDB$DBFS_VIRTUAL_FOLDER TO GGSTEST
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 4

GRANT ALL PRIVILEGES ON .... is MySQL privs syntax.

You need to do:

GRANT ALL ON SCHEMA.TABLE TO USER

See the documentation.


Unable to connect SQL Server 2012 I am facing an issue in connecting to SQL Server 2012 with a .net application. SQL Server 2012 database and code are deployed on the same server.

Connection string:

<add key="umbracoDbDSN"
     value="server=test.test.com;database=testumbraco;user id=testumbraco;password=testumbraco" />

Localhost:

<add key="umbracoDbDSN"
     value="server=localhost;database=testumbraco;user id=testumbraco;password=testumbraco" />

I tried same credentials with SQL Server Management Studio 2012/2014 and these are working fine locally and from remote machine.

Error:

  The network path was not found

Create a second test virtual directory:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Please advise

If you haven't verified that Named Pipes is enabled on the SQL instance, do this:
Open SQL Configuration Manager, usually found in Start > Microsoft SQL Server 2012 > Configuration Tools (in Server 2008/R2. If using Server 2012, just search for it with WinKey+S)
Go to SQL Server Network Configuration on the left. Open Protocols for .
Right-click Named Pipes if it's disabled (will be red) and choose Enable.

.Net seems to require named pipes (and your error above mentions it). There are probably other ways to connect that would not require this.


PostgreSQL/Psycopg2 upsert syntax to update columns I want to have Psycopg2 update col1, col2 and col3 when there is a conflict of id.

In my Python code I have the insert SQL currently as:

insert_sql = '''INSERT INTO {t} (id,col1,col2,col3)
        VALUES (%s,%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO NOTHING;'''

Basically instead of DO NOTHING I want to set:

(col1,col2,col3) = (%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))

Which ignores inserting ID and updates col1, col2, and col3.
The problem is using %s to pass in tuple variables in Python using Psycopg2:

cur.execute(insert_sql.format(t='my_table'),(int(id),new_col1,new_col2,new_col3))

What is the syntax used to refer to the %s corresponding to col1, col2, and col3 to update ON CONFLICT?

You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:

insert_sql = '''
   INSERT INTO {t} (id,col1, col2, col3)
        VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO UPDATE SET
            (col1, col2, col3)
            = (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
'''

See examples of use in Postgres documentation about ON CONFLICT.


Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008 Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008?

The case here is to set the row and page locks to false on the table. The lock is in effect during a series of insert, update and delete actions on the table which are run in a batch.

If I understand you correctly, a better way is to use TABLOCK hint to force a table level lock in the query that needs it (example: delete from a heap)

UPDATE SomeTable WITH (TABLOCK)
SET ...

INSERT SomeTable WITH (TABLOCK)
...

Personally, I wouldn't set it globally in the CREATE INDEX statement...

Also, the MSDN page for sp_indexoptions states

  This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use ALTER INDEX (Transact-SQL).


Replace/upgrade mirrored server hardware We are deploying new VMs for a mirrored database that is in high safety mode with automatic failover and also going from SQL Server 2008 to SQL Server 2008R2.

Most of the documentation I've come across covers a rolling upgrade, but that only covers upgrading the SQL version, not an OS version change let alone changing servers out.

The steps as I see it are: I have to break the mirror and then recreate with the new secondary server, failover, break it again, and then recreate it with the new primary and witness.

How have you managed migrating servers and databases to new OS and new version with an existing mirror?

I think I would first outline something like this before testing it while recording time with a stopwatch.

D-X

get the newProd and newMirror ready (OS + SQL Server + User Account, job, ...)
build scripts for every step (back, restore, mirror, ...)

D-3

add a new IP to oldProd
update DNS with new IP and TTL = 300s (Time To Live)

D, H-2 or H-1 (I assume backup and restore both take 30min)

full backup of oldProd
restore newProd and newMirror

D, H (Downtime)

Stop applications and DB access
Backup logs on oldProd
Restore logs on newProd
Remove new IP from oldProd
Add new IP to newProd
Start applications and DB access

D,  H+something

Build new mirror between newProd and newMirror

Another option for 4. (downtime should be lower)

Break mirror with oldMirror
Backup/Restore logs to newProd
Create new mirror between oldProd and newMirror
Manual failover to newProd
Break new mirror
Create new mirror between newProd and newMirror

I may have forgotten a couple things.


What is the real difference between vacuum and vacuum analyze on Postgresql? What is the difference between:

vacuum;

and

vacuum analyze;

I read the postgresql manual, but this is still not clear 100% for me.

Do I need to run both, or one of them is sufficient?

tl;dr running vacuum analyze is sufficient

Because vacuum analyze is complete superset of vacuum. If you run vacuum analyze you don't need to run vacuum separately. See the discussion on the mailing list archive.

Analyze is an additional maintenance operation next to vacuum. It is supposed to keep the statistics up to date on the table.

  Vacuuming isn't the only periodic maintenance your database needs. You
  also need to analyze the database so that the query planner has table
  statistics it can use when deciding how to execute a query. Simply
  put: Make sure you're running ANALYZE frequently enough, preferably
  via autovacuum. And increase the default_statistics_target (in
  postgresql.conf) to 100.

More info: https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT


Problem Reaching VM SQL Server Instance Here's what I'm working with right now.  I have a host OS which includes a SQL Server 2008 Enterprise instance.  I have a virtual machine running on this host which has a SQL Server 2008 R2 Express instance.  In the virtual machine, I can connect to the host instance in SSMS.  But in the host OS I cannot reach the virtual machine's Express instance.

What could be causing this?  The host can ping the virtual machine, so I don't think it's a network issue.  And in the SSCM on the virtual machine I have set the appropriate network protocols enabled.

Any other things I need to look out for?

In order of what to try:

1. Enable TCP/IP

Go to: SQL Server Configuration Manager on your SQL Server VM, under SQL Server Network Configuration choose your instance (should be Protocols for SQL2008), enable your preferred protocol (I'd guess TCP/IP) and give it another test.

As to why it's usually disabled:

From MSDN article:

  For security reasons, networking protocols are disabled by default in
  SQL Server Express. Individual protocols can be enabled manually or
  during installation of SQL Server Express.

2. Start SQL Server Browser

Go to: SQL Server Configuration Manager on your SQL Server VM, under SQL Server Services make sure SQL Server Browser is running.

from MSDN article:

  SQL Server Browser is used by some networking protocols to associate named instances with  TCP/IP port numbers. However, if SQL Server Express is installed on the same computer as a pre-SP3 version of MSDE or SQL Server 2000, there might be conflicts. Also, the SQL Server Browser service does not start automatically after installation

3. Check your instance name

If you install SQL 2008 Express (or later versions), the Server instance will be named as SQLExpress, so to connect you will need to specify ServerName and InstanceName, ie:

[VMServer\SQLExpress]

4. Check Connection on SQL Server

In SSMS on Server right click Server, go to Connections and make sure that Allow remote connections to this server is checked

5. Basic network checks

Create a folder on your server, share it and make sure you can see it from the other machine.
Try to telnet to it
netstat -ab

Security
You might run into authentication issues, but that should be a straight forward matter of adding your user onto the SQL Server instance.

Note: If none of the above work for you, and you find the solution, please add your solution to the list above (trying to help future users as well)


How to recover deleted records with minimal downtime? In an OLTP environment that cannot tolerate downtime, suppose if 100 records are deleted from a table, how do we recover them?

One way to recover the deleted records is to restore the database and/or tablespace then rollforward to point-in-time.

I believe DB2 Recovery Expert is the best option.

In the case of a dropped object, or recovery to a specific point in time, you should use the "Recovery" option.  The "undo SQL" option performs Log Analysis, which only affects data, not structure.

Please check IBM's official webpage : DB2 Recovery Expert


DAC connection error I'm having a problem connecting using my dedicated admin connection in SQL Server 2014. I just wanted to test it out.

I have DAC enabled in sp_configure. I have performed RECONFIGURE.

I have prefixed my connection string with ADMIN: in SSMS Connect To Server box.

I have not put a space between ADMIN: and the server name. The server name works just fine so long as I don't prefix it with ADMIN:

I am not trying to connect to object Explorer. I am attempting connection when I open a new query window.

I am using Windows Authentication, which works fine when not prefixing ADMIN: to the server name.

Its not a firewall issue, as I've tried it with the firewall turned off.

Having checked the SQL Server error log it says "dedicated admin connection support was established for listening remotely on port 1434". Does that mean it is connected? If so, why did I get the error below?

I've verified TCP port connectivity with the Powershell script mentioned by Dan Guzman, and it works fine. Powershell returns the message "server listening on TCP port 1434"

I am getting the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
(Microsoft SQL Server, Error: 11001)

No such host is known

Your help would be appreciated.

Check the SQL Server error log to determine the remote DAC port number.  It will be contained in the message Dedicated admin connection support was established for listening remotely on port nnnn..  Verify that port is allowed through the firewall.

You can verify TCP port connectivity with TELNET or the Powershell one-liner below, specifying the port number as the argument piped to the echo command.

nnnn | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }


NOWAIT equivalent in MySQL 5.7 MySQL 8.0 has a new feature to allow a transaction to timeout immediately by throwing an error if it tried to access records that are locked by another transaction instead of waiting for that transaction to commit, or waiting for the time-out.

Is there a way to achieve the same effect in MySQL 5.7?

Server-side SELECT statement timeouts allows:

SET SESSION MAX_EXECUTION_TIME=2000;

Not exactly the same, but close.


Does Oracle support table-level & column-level permissions? If there is a customer table, and some extra-private/secure information like SSN is there, should I create another table to store such information and lock that table so only certain people can view it even though it's strictly one-to-one? or can columns be locked too from unauthorized viewing based on rights?

Even if both types of security mechanism do exist, is one preferred over the another? I might have said something very vague but I've used SQL Server more often.

Create a view which simply selects all the other columns form the table in question excluding the columns that you don't want the people to have access to.  Give them rights to query the view and not the table.


Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE? We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for more than half of the table space. We found some indexes are redundant (thanks to Percona Toolkit). Could we simply drop the redundant indexes to release the disk space used by the index without OPTIMIZE TABLE?

I tested this:
CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t` text,
  PRIMARY KEY (`id`),
  KEY `t` (`t`(768))
) ENGINE=InnoDB

I filled it with some thousands of rows of random data.
mysql> show table status like 'mytable'\G
*************************** 1. row ***************************
           Name: mytable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 95030
 Avg_row_length: 5660
    Data_length: 537903104
Max_data_length: 0
   Index_length: 190742528
      Data_free: 5242880

Dropped the index on the text column:
mysql> alter table mytable drop index t;

Refreshed information_schema statistics:
mysql> analyze table mytable;

And checked the table status:
mysql> show table status like 'mytable'\G
*************************** 1. row ***************************
           Name: mytable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 95030
 Avg_row_length: 5660
    Data_length: 537903104
Max_data_length: 0
   Index_length: 0
      Data_free: 195035136

The space that used to be taken by the index is now in data_free. The file size has not shrunk.
This is consistent with other behavior of InnoDB, that a tablespace file never shrinks, the only thing that gives you back more disk space is if you copy the data to a new tablespace file. That requires enough free disk space to hold both the old and the new tablespace, temporarily.
So if you don't have enough free disk space to do this, your only recourse is to make the whole storage volume larger using a virtual disk, or else move the whole MySQL datadir to another server that already has a larger disk.


How do I guarantee inventory numbers are unique across all tables? I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords to be inventoried and 'butters' for different butters. Obviously, these two objects have practically nothing in common as far as attributes other than the fact that they both need to be accounted for in the inventory. This is why they each will have their own table.

I need every inventoried object to have a unique, integer ID assigned to it. So, inventory number '5' might be a sword and '6' might be a butter.

I believe what I need is a separate table which just keeps track of inventory numbers, 'inv_nums'. Then I'd have a foreign key constraint referencing that table in each of the 'swords' and 'butters' tables. The relationship would be 1:1 between an object table and the 'inv_nums' table.

How do I guarantee that an inventory number doesn't get used, inadvertently or otherwise, by multiple object tables?

I'm thinking maybe I need to use a trigger on inserts which calls a function or procedure (not sure which) to either create and return the next sequential inventory number or ensure that the one being provided in the insert statement isn't already in use (it doesn't already exist in the 'inv_num' table).

Am I on the right track? I'm sure there must be a design pattern for this somewhere but my googling hasn't been very successful thus far. I could certainly do all this at the application layer but I'd rather have it enforced in the DB itself.

I'm implementing this in MySQL/MariaDB but I think the concept behind this should be the same no matter the (relational) DB.

First insert a row in a table of "objects" that includes an AUTO_INCREMENT PRIMARY KEY.  Get the id by using LAST_INSERT_ID().

Then insert whatever rows you need into other tables.  Use the id from the first step as the unique id.

This works for MySQL and MariaDB.  Other databases have the concept of a SEQUENCE.  Such can be simulated in MySQL/MariaDB, essentially by using the 'first' step I gave you.  So, if you really need to be somewhat db-independent, start with a SEQUENCE emulation.

(My opinion:  There are so many differences between RDBMS vendors that it is folly to try to have one code for all.)


Referring alias name in subselect returns "Unknown column in 'where clause'" I have following table:

+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| Field      | Type      | Null      | Key      | Default      | Extra      |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
| id         | int(11)   | NO        | PRI      |              | auto_increment |
| date       | datetime  | NO        |          |              |            |
| a          | int(11)   | NO        |          |              |            |
| b          | int(11)   | NO        |          |              |            |
| c          | int(11)   | NO        |          |              |            |
| d          | int(11)   | NO        |          |              |            |
| e          | int(11)   | NO        |          |              |            |
+ ---------- + --------- + --------- + -------- + ------------ + ---------- +
7 rows

I need to get number of rows, number of rows where a is '1', number of rows where a is '0' and frequency of '1's as percentage, in each hour of day. So, I ran this query:

select day,hour,c,100.0*(a1/c) as percentage,a1 from
(
    select day(date) as day,hour(date) as hour,count(*) as c, (select count(*) from t where a=1 and hour(date)=hour) as a1 from t group by hour(date)
) as T;

Which I have Debian 8.0 - MySQL server version (5.5.?), and it returned correct results. Though it fails at home on Windows 7 MySQL 5.7.9 with following error:

Error Code: 1054. Unknown column 'hour' in 'where clause'

My question is if SQL is evaluated from inside out, so how the first query even worked? And what is the standard query to get this results?

Note
Just installed 5.5.13 and above query is working.

For the alternative way to count these:

select
    day(date) as day,
    hour(date) as hour,
    count(1) as c,
    sum(a=1) as a1,
    sum(a=0) as a0,
    (sum(a=1) / count(1)) as pct
from t
group by hour, date(date)

I added the date to the group by so the same hour of different days do not get counted together - change that the way you need.


How can i update my database from another database using temporary tables? I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it?

This my temporary table :

use data_pro;
DROP TABLE IF EXISTS datasll.table1;
CREATE TEMPORARY TABLE IF NOT EXISTS datasll.table1
SELECT
 cs.id,
 cs.fk_c,
 cs.so,
 cs.pr,
 cs.date,
 cs.o_p,
 cs.sh_c,
 cs.x,
 LEFT(cs.x,2),
 cs.doc,
 cs.fk_op,
 cc.id_c_c,
 cc.name,
 cc.descrip,
 left(cc.n_o,3) as bon,
 cc.fk_c_br,
 cc.de,
 cc.m_mat,
 cc.color,
 cc.fk_c_s
 FROM
   data_pro.catas cs

 LEFT OUTER JOIN
data_pro.c_co cc ON  data_pro.cs.fk_c_c = data_pro.cc._id_cc
WHERE
data_pro.cc.pap = 1 AND   data_pro.cc.stati = 'b';

Many thanks for any help.

You can indeed use stored procedures to refresh the data in your database.  It just means coding the INSERTs and UPDATEs appropriate to your data.

If you are replacing the existing data with new data, you could use:

INSERT INTO ... SELECT FROM ... syntax, after deleting the existing data, to pull the data from your query and insert it into the now empty table. Of course, you could run individual steps to update many tables, one at a time.

Syntax documentation on INSERT INTO ... SELECT FROM is here: https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

If you want to UPDATE ... SELECT FROM for only some values in a table, you could follow the different examples here:

https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query

Also general UPDATE syntax is described here: https://dev.mysql.com/doc/refman/5.7/en/update.html


Check all enabled trace events Recently  on a database I enabled trace for event 12842 using the below statement in order to trace Oracle error ORA-12842 : Invalidated cursor.

alter system set events '12848 trace name ERRORSTACK level 3';

After sometime the error did occur but we could not find any trace file for that event.

I am suspecting the tracing got disabled after instance restart.

Is there any way to list out all the events for which tracing is enabled ?

I got my answer from the below link:
generate/analyze trace for error 1461

One may know by using the following statements in sqlplus:

oradebug setmypid
oradebug eventdump system

This did the magic :)


Execute sql file in Exception statement Hi I'm trying to execute an SQL File in a Exception Statement when I'm executing an Spool file.

I have something like this:

column dt new_value _dt
select to_char(sysdate,'yyyymmdd_hh24mi') dt from dual;
set line 10000;
set pagesize 50000;
set serveroutput on;

spool .\backup\backup.sql

select dbms_metadata.get_ddl(object_type, object_name)
from user_objects
where object_type in ('FUNCTION')
and object_name = 'TEST_TABLE';

Spool Off

spool .\!Run_&_dt..txt

BEGIN
    raise_application_error( -20001, 'This is a custom error' );
EXCEPTION
    WHEN OTHERS THEN
        Prompt ./backup/backup.sql
        @ ./backup/backup.sql
END;

Spool Off

And I get the next error:

>> BEGIN
     raise_application_error( -20001, 'This is a custom error' );
EXCEPTION
    WHEN OTHERS THEN
        Prompt ./backup/backup.sql
        @ ./backup/backup.sql
END;

Spool Off
Error at line 18
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "/" when expecting one of the following:

    <an identifier> <a double-quoted delimited-identifier>
    current delete exists prior
ORA-06550: line 6, column 49:
PLS-00103: Encountered the symbol "SQL" when expecting one of the following:

    <an identifier> <a double-quoted delimited-identifier>
    current delete exists prior

I don't have any idea, thanks in advance.

Best regards.

So it's a bit confusing - you're writing a SQL*Plus script, which accepts both SQL*Plus commands as well as SQL commands and PL/SQL blocks.

SQL*Plus commands are executed locally on your client machine, so they have access to other scripts on your local filesystem.

Everything inside a BEGIN..END block is PL/SQL code, and it'll behave differently than the rest of your script because it's a different language. The entire block of code is sent to the server and executed there, and the database doesn't know how to interpret sqlplus commands like prompt and @.

There are ways to have a PL/SQL block execute scripts on the database server - see this answer for more details - but I don't think that's really what you want.

It's hard to tell what you want from your example, but I'd suggest looking into handling sqlplus errors using a shell script - split this into two sqlplus scripts, then run the first script, check the return code, and if you got an error, run your second script. Sqlplus itself doesn't really have much in the way of built-in exception handling.


Clustered index data access I am trying to understand how SQL Server access data from the clustered index. My understanding is when the table has a clustered index SQL should be able to seek into the single page that holds the record using the seek predicate.
However, my test showing me when the query is executed it loads a few more data pages.
Setup
CREATE TABLE t2(id INT IDENTITY PRIMARY KEY CLUSTERED,col2 VARCHAR(500),col3 VARCHAR(500));

INSERT INTO [dbo].[t2]([col2],[col3])
SELECT TOP 10010 REPLICATE('z',490),REPLICATE('*',490)
FROM sys.all_columns c1,
     sys.all_columns c2

The following query
select *
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.t2'),DEFAULT,null,'DETAILED');

shows the output as

I then cleared the cache with
CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

And ran the following SELECT seeking into a single row
SELECT  [fplc].*,[t2].[col3] FROM [dbo].[t2] AS [t2]
CROSS APPLY sys.[fn_PhysLocCracker](%%physloc%%) AS [fplc]
WHERE id=4582

The above query tells me the record is located on the page 1061
I use the code below check how many pages have been loaded into the buffer to get the result of my SELECT
SELECT buffers.* FROM sys.dm_os_buffer_descriptors  buffers
INNER JOIN sys.allocation_units AS au
    ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
    ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100
and [database_id] = DB_ID () AND i.[object_id]=OBJECT_ID('t2')
 ORDER BY [page_level] desc

Why SQL is loading all the pages marked in green? Are the pages loaded by READ AHEAD ?

This is explained in The Read Ahead that doesn’t count as Read Ahead

  what I wanted to talk about in this post, was another prefetching
  mechanism that can be triggered by any query when SQL is running any
  of the editions considered Enterprise (i.e. Developer, Evaluation, and
  Enterprise itself). The aim of this optimization is to warm up the
  cache as quickly as possible. To do so, the buffer pool converts any
  request to read a single page from disk into a request that will read
  the whole extent containing the page initially requested.

In your case you ran this after clearing the buffer cache with DBCC DROPCLEANBUFFERS. So SQL Server is in the state that it would try and warm up the cache quickly.

You needed to read three pages to perform the seek (one at each level of the index). These were pages 1111, 1382 and 1061.

So you ended up bringing in three entire extents with page numbers 1056 - 1063, 1104 - 1111 and 1376 - 1383.

If you do the same experiment against some other SKU than mentioned in the quote (and traceflag 840 is not enabled) you should see your expected three pages (below was against Express LocalDB)


Question about the UNION operator and its implicit data type conversions Is there a limit to the number of UNION operators to combine query results?

I have an odd situation where I have a table called TransmittalSheet TS that have a double (1 to Many) relationships with each of the 2 tables: (DocumentInfo and EquipmentJobPlan tables), they both have almost the same exact column names and data types.
Each primary key (TransmittalID)  that’s coming from the parent table TS above, have two corresponding foreign keys in each of the children tables above. FK pair columns are called: (Docs_Implementing, Docs_Obsolescing) and (JobPlan_TS_Implementing and JobPlan_TS_Obsolescing).

I need to query if each Transmittal have any documents or equipmentJobPlans are associated with it (either Imp or Obs for each table association), so I believe I need to have 3 UNION operators like this:

Select A, B, C , ‘Imp Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS
ON Di.DocumentTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS
ON Di.DocumentTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Imp JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’

Is there a better way to do this?  Using SQL Server 2008R2.

Also, it seems that Union is doing some data conversion for column C for example: both columns are capturing document versions. It has a data type of varchar(75) in the DocumentInfo table, but it has a smallDateTime in the JobPlan table, , should I have an explicit conversion in place or the implicit Coversion that the Union is doing will be OK? Thanks

For one, you can certainly simplify your query, for example (I've added schema prefixes, which you should always use):

Select A, B, C
  From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001'
UNION
Select A, B, C
  From dbo.EquipmentJobPlan As EQ dbo.INNER JOIN TransmittalSheet TS
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001';

Or perhaps even:

SELECT A, B, C
  FROM dbo.TransmittalSheet TS
  LEFT OUTER JOIN dbo.DocumentInfo As Di
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  LEFT OUTER JOIN dbo.EquipmentJobPlan As EQ
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
WHERE TS.TransmittalNumber = 'TS- 0001'
AND COALESCE(Di.KeyColumn, EQ.KeyColumn) IS NOT NULL
GROUP BY A, B, C;

(I don't know if the GROUP BY is necessary, because I don't know if you are using UNION on purpose for the intention of removing duplicates. Though I do recommend putting table aliases on all column references, so readers have a clue which columns come from which tables. You may need to use COALESCE here and choose which table takes precedence for any of the output columns if A, B or C don't come from TransmittalSheet.)

Now, you say that when you use a UNION you get a conversion error, because in one of the tables column C is a varchar and in another it is a smalldatetime. Your options are:

To fix that. If you are considering those columns to be the same and compatible, they should be of the same data type and contain like data. Which means changing the data type after you remove any data that can't be directly converted to smalldatetime.
If the varchar column needs to have different data that is not compatible with smalldatetime, then perhaps it is best to present that as a different output column in your UNION query.
After that, you can apply an explicit cast on the smalldatetime type so that it can be converted to varchar, instead of the way SQL Server will do it (which is to try to convert the string to a date). If you want to only present valid smalldatetime data, then you can CAST the other way, but use a CASE expression so that the conversion is only attempted on valid data. The implicit conversion can follow the rules of data type precedence, but if the varchar column contains any value that can't be converted to smalldatetime, it fails with Msg 295, as you've discovered. This can happen even if those rows don't appear in the output - the conversion can occur before the rows are filtered out (I've written about this here, here, here and here and it was reported as a bug on Connect (RIP). And because you're using UNION instead of UNION ALL, you may be more likely to hit invalid values because the additional DISTINCT SORT operations this (usually) adds, which can change the way SQL Server will optimize the overall plan.

So, if you can't benefit from the re-writes above (or if they produce different results for some reason), and you can't do 1. or 2., then I would re-write the UNION version either like this:

-- against the table where C is smalldatetime:
SELECT A, B, CONVERT(CHAR(10), C, 120)
FROM ...

-- or against the table where C is varchar:
SELECT A, B, CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM ...

A more localized example you can play with:

CREATE TABLE dbo.foo(A INT, B INT, C VARCHAR(75));
CREATE TABLE dbo.bar(A INT, B INT, C SMALLDATETIME);
INSERT dbo.foo(A,B,C) VALUES(1,2,'three'),(2,4,'20150101');
INSERT dbo.bar(A,B,C) VALUES(1,2,'20050101'),(2,4,'20050101');
GO

-- these both work of course:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1;
SELECT A,B,CONVERT(SMALLDATETIME,C) FROM dbo.foo WHERE ISDATE(C) = 1;
GO

-- fails:
SELECT A,B,C FROM dbo.foo
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds, but may not always:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds:
SELECT A, B, C = CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM dbo.foo
UNION
SELECT A, B, C FROM dbo.bar;

-- succeeds:
SELECT A, B, C FROM dbo.foo
UNION
SELECT A, B, C = CONVERT(VARCHAR(10), C, 120) FROM dbo.bar;
GO

DROP TABLE dbo.foo, dbo.bar;

Finally, please don't CAST( AS Char) with no length, or any other variable type.


Error: 9245, Severity: 16, State: 1. / "During the last time interval X query notification errors were suppressed" We are receiving this notification several hundred times a day and while we're regularly checking notifications from Severity 16 errors, this isn't something we're really concerned about.

I've read the article and replies at Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed and while that's very informative, I'd like to know how can we suppress it?

We have an automated alert set up for severity 016 errors and we don't exact categorize this one as an "action items" yet are receiving hundreds of them daily.

Is there a way to suppress it?

It's the simple built-in alerts that you can configure in SQL Server i.e. (EXEC msdb.dbo.sp_add_alert @params) - it emails the DBA team when that error is captured.

Since this particular "cause"  is not exactly threatening the health of the server I was wondering "other than a mailbox rule" if there was a way to "omit" these particular ones containing "notification errors were suppressed" - so basically that they're not sent at all.


Using a Linked Server to import data from Excel I have read a lot about linked servers being bad when there are multiple hops across servers and distributed joins.

What if I am just using a linked server to import data into SQL Server from an Excel spreadsheet?

What if that's all I am doing and the rest is being all done in SQL Server itself on a single server?

I want to avoid SSIS owing to headaches with multiple transactions and chaining them to each other (which is failing for some reason).

You can use the Import wizard to load your data.  The Import/Export wizard actually uses SSIS to do the work.  But if your goal is to avoid learning SSIS, then just do not save the package.

Simple way to import data into SQL Server

That MSSQLtips article walks you through setting up the import and then run the data into your SQL Server destination.

If you want to reuse the package (which is probably a good idea) there is a save option that allows you to save the SSIS package that was internally generated and use it again and again.

However, if the Linked Server approach is the way you want to go, there is a tutorial here:

Excel Import to SQL Server using Linked Servers

This is somewhat more complex to set up, but should work OK.

UPDATE: based on your comment about coordinating two transactions you might find it simpler to import the data into two staging tables.  (You would most likely truncate those tables before each load.) The table Stage01 receives one load and the table Stage02 receives the other load.

Then, now that both data sets are in their own tables, you can join between Stage01 and Stage02 data sets to insert the resolved data into your operational database tables. This would likely simplify the processing of the two data sets.


Error with pgAdmin III and pg_hba.conf I have a problem with my Pgadmin3.. I used to use the last year, but this year I can't connect with the localhost server with pgadmin

This is the message error:

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL on
FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

My pg_hba.conf file have this content:

TYPE       DATABASE USER                ADDRESS                 METHOD
local      all                          all                     trust

I can connect without problems with psql. My user is postgres and my pass is postgres too.

I have information in the database, i don't have idea what's happen.

My OS is Linux Lubuntu 14.04

Look at the first column TYPE, from the docs

local This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are disallowed.
host This record matches connection attempts made using TCP/IP. host records match either SSL or non-SSL connection attempts.

Your type is local, but your error message indicates you're trying to connect to 127.0.0.1 which is an IP address. You either need to connect using unix sockets without an ip-address (no -h in psql) or change the type to host.
From the docs on PgAdminIII

The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.


Finding road segements which are not split at intersection.(Query Optimization ) I have a query in which I am trying to find the road segments which intersect but are not splitted at intersection,I have also created gist index on geom column. Here is postgerSQL srcipt

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
 -- The "vertical" start node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
 ) OR (
 -- The "vertical" end node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
 )

Logic is working fine but it takes a lot of time to complete this task, can someone let me know how can I optimize this query.

I just added st_intersects(a.geom,b.geom) at the start of second and third queries, as st_intersects is fast so it only calculates st_intersection where st_intersects returns 'True' hence performance of query is enhanced. Below is the query after modification.

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
-- The "vertical" start node touches, but not on either of the "horizonal" --nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom),
ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
) OR (
-- The "vertical" end node touches, but not on either of the "horizonal"
--nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
)

But I think there is still some room for improvement.


Get inserted row count when master table isn't modified I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables.
Now, I need to create a PL/pgSQL procedure that SELECTs some data from the (master) table and re-inserts it with some changed values.
Normally I'd use the ROW_COUNT diagnostics variable and be done with it.
Trouble is that ROW_COUNT is always 0, because nothing gets inserted into the master table (the trigger catches and redirects inserts en route).
My next idea was to do something like
WITH inserted AS (
  INSERT INTO master (...) SELECT ... FROM master WHERE ... RETURNING master.field
) SELECT count(*) FROM inserted INTO rowcount;

but, again, rowcount always ends up being 0, because nothing was ACTUALLY inserted into the master table.
If I do
WITH insertable AS (
  SELECT ... FROM master WHERE ...
), blah AS (
  SELECT count(*) INTO rowcount FROM insertable
) INSERT INTO master SELECT * FROM insertable;

then Postgresql complains that:

ERROR: INTO used with a command that cannot return data
SQL state: 42601

Etc...
In case of my last code extract, how would I get the row count of inserted into a rowcount variable in my procedure that I can then do stuff™ with? Or is there any other way of retrieving the number of rows that were inserted into the (tables inheriting from) master table?

If this is still desirable for you (although not THAT vital), this is a solution to it:

CREATE TABLE rows_affected_in_session (
  session_pid int NOT NULL,
  session_time timestamptz NOT NULL,
  table_name text NOT NULL,
  rows_affected int NOT NULL,
  PRIMARY KEY (session_pid, session_time, table_name));

CREATE OR REPLACE FUNCTION f_update_rows_affected_in_session (p_table regclass, p_rows_affected int) RETURNS void AS $BODY$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM rows_affected_in_session WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text) THEN
    INSERT INTO rows_affected_in_session (session_pid, session_time, table_name, rows_affected)
    VALUES (pg_backend_pid(), current_timestamp, p_table::text, p_rows_affected);
  ELSE
    UPDATE rows_affected_in_session SET rows_affected = rows_affected + p_rows_affected
    WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text;
  END IF;
END;
$BODY$ LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE FUNCTION f_rows_affected_in_session() RETURNS TABLE (table_name text, rows_affected int) AS $BODY$
  SELECT table_name, rows_affected
  FROM rows_affected_in_session
  WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp
  ORDER BY 1
$BODY$ LANGUAGE SQL SECURITY DEFINER;

You use it by calling the function f_update_rows_affected_in_session(<table>, <rows_affected>) after every successful insert within triggers (which you can use ROW_COUNT diagnostics), and then use the function f_rows_affected_in_session() to retrieve the rows affected within the session (you can determine in it exactly which child tables were affected, or sum the results for a grand total). Example:

SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT * FROM f_rows_affected_in_session();


Script for listing tables having no primary key oracle 9i I'm looking for a script to find tables within my database that have no primary key.

select table_name from user_tables
minus
select table_name
from all_constraints
 where (constraint_type ='P' ) and owner='dbSID_here'


Filter table by multiple pivot values I have the following tables:
fabrics
| id| title   | code|
|-- |---------| ----|
| 1 | Fabric 1| FBR1|
| 2 | Fabric 2| FBR2|
| 3 | Fabric 3| FBR3|
| 4 | Fabric 4| FBR4|

attributes
| id| title      | category|
|---|------------| -----   |
| 1 | Type       | printed |
| 2 | Color      | printed |
| 3 | Composition| printed |

attribute_values
| id| attribute_id| text  |
|---|-----------  | ----- |
| 1 | 1           | soft  |
| 2 | 2           | yellow|
| 3 | 2           | blue  |
| 4 | 2           | black |
| 5 | 1           | dry   |
| 6 | 2           | green |
| 7 | 3           | cotton|
| 8 | 3           | wool  |

fabric_attribute_value
| id| attribute_value_id| fabric| attribute_id|
|---|-------------------| ------| ------------|
| 1 | 6                 | 1     | 2           |
| 2 | 7                 | 1     | 3           |
| 3 | 2                 | 2     | 2           |
| 4 | 7                 | 2     | 3           |
| 5 | 6                 | 3     | 2           |
| 6 | 8                 | 3     | 3           |
| 7 | 2                 | 4     | 2           |
| 8 | 8                 | 4     | 3           |

My problem is that I want to filter for example Fabrics with Color attribute: yellow OR green AND with the Composition attribute: cotton.
I should be getting Fabric 1 and Fabric 2 as results back.
How do I accomplish this query?
When there are multiple filters for the same attribute like Color it should be an OR inside it, but if it binds with condition with another attribute like Composition it should be AND. So for example in pseudo-code:
(Yellow(Color) OR Green(Color) AND Cotton(Composition))

every further filter you want to add simply increase the number in the IN Clauses
If you have a new attrubute you have to add another AND in the WHERE clause
As i said in the comment attribute_id in attribute_values is redundant

CREATE TABLE fabric_attribute_value
    (`id` varchar(3), `attribute_value_id` varchar(19), `fabric` varchar(6), `attribute_id` varchar(12))
;

INSERT INTO fabric_attribute_value
    (`id`, `attribute_value_id`, `fabric`, `attribute_id`)
VALUES

    ('1', '6', '1', '2'),
    ('2', '7', '1', '3'),
    ('3', '2', '2', '2'),
    ('4', '7', '2', '3'),
    ('5', '6', '3', '2'),
    ('6', '8', '3', '3'),
    ('7', '2', '4', '2'),
    ('8', '8', '4', '3')
;

CREATE TABLE fabrics
    (`id` int, `title` varchar(8), `code` varchar(4))
;

INSERT INTO fabrics
    (`id`, `title`, `code`)
VALUES
    (1, 'Fabric 1', 'FBR1'),
    (2, 'Fabric 2', 'FBR2'),
    (3, 'Fabric 3', 'FBR3'),
    (4, 'Fabric 4', 'FBR4')
;

SELECT `title`, `code`
FROM fabric_attribute_value av
INNER JOIN fabrics f ON av.`fabric` = f.`id`
WHERE (`attribute_id` = 2 AND (`attribute_value_id` IN ( 2 , 6)))
AND `fabric` IN (SELECT `fabric`
                 FROM fabric_attribute_value
                 WHERE (`attribute_id` = 3 AND (`attribute_value_id` IN (7))))

title    | code
:------- | :---
Fabric 1 | FBR1
Fabric 2 | FBR2

SELECT `title`, `code`
FROM fabric_attribute_value av
INNER JOIN fabrics f ON av.`fabric` = f.`id`
WHERE (`attribute_id` = 2 AND (`attribute_value_id` IN ( 2 , 6)))
AND `fabric` IN (SELECT `fabric`
                 FROM fabric_attribute_value
                 WHERE (`attribute_id` = 3 AND (`attribute_value_id` IN (7, 8))))

title    | code
:------- | :---
Fabric 1 | FBR1
Fabric 2 | FBR2
Fabric 3 | FBR3
Fabric 4 | FBR4

db<>fiddle here


MySQL InnoDB .ibd file growing extreme We have a database with a few larger tables were one (yes only one) table grows extremely. It contains only 50 gigabytes of data while the .ibd file (we use innodb_file_per_table) takes up about 1.2 terabytes of space after only one week.
There are many many update querys run against the table which this file belongs to so the problem is probably InnoDB saving all the undo states (or whatever this is called).

The server runs on Percona Server 5.7.18

The server settings that we use:

innodb_log_files_in_group = 2
innodb_log_file_size = 10G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

binlog_format = MIXED
gtid-mode=ON
enforce_gtid_consistency=ON

log_slave_updates = 1
expire_logs_days = 7
sync_binlog = 1
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 10000
innodb_thread_concurrency = 0
transaction-isolation = READ-COMMITTED
thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000
open_files_limit = 1024000
innodb_lock_wait_timeout = 30
innodb_buffer_pool_size =  650GB
max_heap_table_size = 32M
tmp_table_size = 32M
innodb_log_buffer_size = 64M
query_cache_type = off

I know that we could use OPTIMIZE TABLE or pt_online_schema_change to get the used space back. But doing this once a week is not really an option on a production database which is essential for the company.

Are there any settings that I don't know of or missed that will prevent the .ibd file from blowing up this fast?

Study your queries.  It could be that you accidentally have a "cross join" because you left off the ON clause.  This could lead to a huge temp table that, in certain circumstances, could bloat the .ibd file.

What version of Percona?

How much RAM do you have?? innodb_buffer_pool_size =  650GB implies that you have at least 700GB!  Otherwise, you will swap to death.  (This won't explain the .ibd issue, but it is a serious one.)

These are also rather high unless you have a lot of RAM:

thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000

Please provide SHOW CREATE TABLE for that one table, and SHOW TABLE STATUS for it.  BLOBs and TEXTs are of interest; other things may be of note.

A query would take a long time to bloat the table.  Glance at SHOW FULL PROCESSLIST; you might spot it.  And/or, turn on the slowlog, and check it later.

A query (added from comment)

select  table0_.id as id,
                table0_.created as created,
                table0_.modified as modified,
                table0_.chunk_index as chunk,
                table0_.content as content,
                table0_.other_table_id as export
    from  table table0_
    where  table0_.other_table_id=8305
    order by  table0_.chunk_index ASC

To improve the performance of that query, you need INDEX(other_table_id, chunk_index).


how to give access to view only one database to a particular users in sql server 2008 I need to give 5 different users to access one database, hiding other databases.

I have tired to give access, i am able to give only one user not for all.

Method 1:

If you want users to View only,

USE <database name here>
GO
GRANT VIEW Definition TO User1

USE <database name here>
GO
GRANT VIEW Definition TO User2

and so on for all 5 of them

Method 2:

If you meant to give them read only access

Step 1:

In case for domain account users, create a login from windows account with below( for the users you want to give access to say Database A):

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS;
GO

Else (in case not windows account)

 CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';
GO

Step 2: Perform below for all 5 users to grant login permission to access the database in question

Say for user 1:

 USE (your database name)
 CREATE USER (username1) FOR LOGIN (login name)

Similarly for user 2

USE (your database name)
 CREATE USER (username2) FOR LOGIN (login name)

and so on for all 5..

Step 3: Once you have users added in your database, you can give them any rights you want, e.g. you could assign it the db_datareader database role to read all tables.

USE (your database)
EXEC sp_addrolemember 'db_datareader', '(username1)'

.... for all 5


As a minimum, you should use OR instead of AND, because it is impossible for a date to be simultaneously in February AND January.

SELECT sum(sell) AS sell
  FROM stat
  LEFT JOIN stat_key ON stat.stat_key = stat_key.id
 WHERE stat.date BETWEEN '2017-02-16' AND '2017-02-26'
    OR stat.date BETWEEN '2017-01-16' AND '2017-01-26'
    OR stat.date BETWEEN '2016-12-16' AND '2017-12-26';

Now that I see your expected output, I know that you want something like this:

WITH ct(d) AS (
  SELECT d::date
  FROM generate_series('2016-11-26', '2017-02-26', INTERVAL '1 month') AS gs(d)
)
SELECT COALESCE(SUM(s.sell), 0) AS sell,
       ct.d AS dt
    FROM  ct
    LEFT JOIN stat s ON s.date BETWEEN ct.d + '-10 days' AND ct.d
    GROUP BY ct.d;

Unfortunately, your expected output cannot be generated by the sample input, so I had to test it with my data.

Notes:

generate_series works with timestamps: you can convert them to dates in the main SELECT or the CTE. Other approaches are possible.
It is possible to avoid the CTE (i.e. the WITH at the beginning) and join directly with SELECT generate_series(.... However, I find it more readable this way, given that in this way the report dates are near the top and that is what you will change most often.
LEFT JOIN and COALESCE() let you display results (0) for ranges that have no values at all in the data table.


Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode I am laying out a plan where there will be 3 Node on site with automatic fail over with synchronous mode and 1 remote replica with asynchronous mode. My question is if the primary goes down which node among the remaining 2 replica will be failed over. I will have a file share for node majority with file share quorum.

My question is if the primary goes down which node among the remaining 2 replica will be failed over.

The node that is set to Synchronous with Automatic failover will be the chosen one to failover when the primary goes down.  You can only have one node set as the automatic failover target, see the docs for 2014  (it was the same for 2012):

  To configure an availability group for automatic failover, you need to set both the current primary replica and one secondary replica to synchronous-commit mode with automatic failover.

On SQL Server 2016 and newer, you can have a second automatic failover target (it's 3 including the primary):

  To configure an availability group for automatic failover, you need to set both the current primary replica and at least one secondary replica to synchronous-commit mode with automatic failover. You can have up to three automatic failover replicas.

Remember that an Async replica might have data loss if you try to do a force failover.


The pgAdmin 4 server could not be contacted Whenever I install PostgreSQL, it stops working after about 6 months.  This is the second time this happened!  Last time this happened, I uninstalled and then installed version 4.29.  How can I debug the PostgreSQL client or server and get a stack trace?

Splash screen opens and never closes unless I click on it.

I can't show more of the top section of the log because the error dialog pops up.  When closing the error, the system tray icon for PostgreSQL crashes and closes everything.

After further troubleshooting, I found 8 "PostgreSQL Server" tasks, 1 pg_ctl task, 1 pgAdmin 4 Desktop Runtime in the Task Manager.  Killed them all and restarted this and restarted the service in Admin Tools > Services.  Same behavior.
C:\Program Files\PostgreSQL\12\bin\postgres.exe

Found these.  We'll give it a shot.
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

Windows debugging.
Open Environment Variables (WINDOWS key + BREAK)
Set environment variable "_NT_SYMBOL_PATH" for 4.2.
C:\Program Files\PostgreSQL\12\debug_symbols;SRV*c:\localsymbols*http://msdl.microsoft.com/download/symbols

open pgAdmin 4 > look for process in Task Manager and get process id
open Microsoft Visual Studio (run as administrator) > Debug > Attach to Process > check "Show processes from all users" > sort by process id > Attach
now go back in pgAdmin

Got a new popup I've never seen before from Visual Studio!

Created new virtual environment based on Python 3.8.

Created a new Visual Studio Project using Python, not C++.  I might need a newer version of Python based on this fix.
https://github.com/microsoft/ptvs/issues/5853
https://bugs.python.org/issue37633
Since this new version does not show in the Base Interpreter, I created a new environment based on the zip file.

None of that worked.  Then I stumbled upon the registry configuration for pgAdmin.  I read that pgAdmin uses Python version 2.7.  And the PythonPath variable was blank, so I set it to C:\Python\Python27.  So it must be using a value from somewhere else to get the path.  It's looking in sys.path in Python whatever that is.

Then I set ApplicationPath to C:\Program Files\pgAdmin 4\v4\web, to the folder where pgadmin4.py is located.
Here is the icon I'm clicking on each time.
"C:\Program Files\pgAdmin 4\v4\runtime\pgAdmin4.exe"
"C:\Program Files\PostgreSQL\12\pgAdmin 4\bin\pgAdmin4.exe"

ERROR flask.app: Error starting the app server: (<class 'OSError'>,
OSError(10013, 'An attempt was made to access a socket in a way
forbidden by its access permissions', None, 10013, None),

There are two pgAdmin folders.  There are traces left over from the other pgAdmin uninstall.
C:\Program Files (x86)\pgAdmin 4\files.txt
C:\Program Files (x86)\pgAdmin 4\v4
C:\Program Files (x86)\pgAdmin 4\v4\venv
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\collections
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\site-packages
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\collections\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\collections\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\aliases.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\cp1252.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\latin_1.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\utf_8.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\abc.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\machinery.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\util.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\decoder.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\encoder.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\scanner.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\site-packages\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\site-packages\__pycache__\_virtualenv.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\abc.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\codecs.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\contextlib.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\copyreg.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\datetime.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\enum.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\functools.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\genericpath.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\heapq.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\io.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\keyword.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\ntpath.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\operator.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\os.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\re.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\reprlib.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\signal.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\site.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\sre_compile.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\sre_constants.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\sre_parse.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\stat.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\subprocess.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\threading.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\types.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\warnings.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_bootlocale.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_collections_abc.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_sitebuiltins.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_weakrefset.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\__future__.cpython-38.pyc

FINALLY SOLVED IT!  Flask within the Qt framework is NOT getting the port!  I put some troubleshooting code into the source code.

C:\Program Files\pgAdmin 4\v4\web\pgAdmin4.py

# Initialize Flask service only once
# If `WERKZEUG_RUN_MAIN` is None, i.e: app is initializing for first time
# so set `use_reloader` = False, thus reload won't call.
# Reference:
# https://github.com/pallets/werkzeug/issues/220#issuecomment-11176538
with open("myfile.txt", "w") as file1:
    file1.write("Testing")
    file1.write(config.DEFAULT_SERVER)
    file1.write(config.EFFECTIVE_SERVER_PORT)
try:
    app.run(
        host=config.DEFAULT_SERVER,
        port=config.EFFECTIVE_SERVER_PORT,
        use_reloader=(
            (not app.PGADMIN_RUNTIME) and app.debug and
            os.environ.get("WERKZEUG_RUN_MAIN") is not None
        ),
        threaded=config.THREADED_MODE
    )

except IOError:
    app.logger.error("Error starting the app server: %s", sys.exc_info())


Query with "Logical operator (AND)" that does not return the desired result set I have a table:
create table test_table
(customer_num  NUMBER,
 dep_group     VARCHAR2(50),
 dep_type      VARCHAR2(50),
 balance       NUMBER)

the table has two records:
                  " test_table "
 --------------------------------------------------
 customer_num   dep_group   dep_type    balance
     1            x           x1          10
     1            x           x2          10

The query below must return two records of the table but it does not, and it's weird!!
select *
from test_table
where ((dep_group = 'x') AND (dep_type = 'x1') AND ((balance) > 1)

           AND

     ((dep_group = 'x') AND (dep_type = 'x2') AND ((balance) > 1)))

when I run each part of the query separately , it gives me the desired record
Q1:
 select *
 from test_tablet t
 where (dep_group = 'x') AND (dep_type = 'x1') AND ((balance) > 1)

Q2:
 select *
 from test_tablet t
 where (dep_group = 'x') AND (dep_type = 'x2') AND ((balance) > 1)

I mean customer = 1 matches both conditions and the original query must give me the two records of the table. Is there something wrong with my query or my understanding of the logical operators?
Thanks in advance.

As Mustaccio mentioned in the comments, you want rows where dev_type is either 'x1' or 'x2', not where it's 'x1' and 'x2'.
Also you only need the brackets around each of the alternative sets of conditions. The others aren't doing anything and they are just confusing.
with test_table (customer_num, dep_group, dep_type, balance) as
     ( select 1, 'x', 'x1', 10 from dual union all
       select 1, 'x', 'x2', 10 from dual
     )
select *
from  test_table
where (dep_group = 'x' and dep_type = 'x1' and balance > 1)
or    (dep_group = 'x' and dep_type = 'x2' and balance > 1)


Pros and cons of using many schemas in PostgreSQL as opposed to just one? For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the data in one schema and using foreign keys to partition it in the queries?

I know in the past pg_dump was painfully slow when working with many schemas but not sure if that is the case today. I'm also aware any change in the database structure will have to be done on all the schemas. And I know that on the plus side, moving a schema from one physical server to another is easy, as well as restoring a schema from backup, not to mention it makes sense to partition data that way.

So what are the pros and cons I'm missing?

Obviously, you are dealing with the same tables in each user schema. Have you considered inheritance for this? It can give you the best of both worlds for some use cases. There are also some limitations. You can have a separate schema for each user and still search all user tables at once very conveniently.

Related:

Select rows from table where each row refers to a different schema
Find out which schema based on table values

Other than that, at least granting / revoking privileges has to be mentioned, which is much simpler with separate schemas.


Term for non transaction save actions If you only look at the database everything is fine. You have transactions and if somethings goes wrong everything gets rolled back. That's nice - I like this.

BUT: I want to send mails. Now I am in trouble because I can't rollback.

example:

transaction starts
Mail gets send
Other stuff gets done (inside DB)
Something goes wrong.
Rollback.

How to solve this is a different question, not this.

This question how to call this in general. In this example is about sending mails. But the same problem as soon as you do modify something in systems which are outside the transaction boundary.

Is there a name for this problem?

Roughly the same problem arises if you want to import files from a directory. If you delete the file inside the transaction, then the transaction might fail and the file was deleted but never imported. Or you delete the file after the transaction. Then the delete of the file might fail and the file gets imported a second time.

I don't want to reinvent a solution for this. That's why I need the matching term for this problem. Then I can read some papers and learn what's "state of the art" in the year 2018.

Oracle PL/SQL keyword AUTONOMOUS_TRANSACTION will cause a procedure to create another session, do a transaction, commit/rollback just that private transaction, and return flow control back to the parent.

Oh..never send email on uncommitted data.

EDIT: (due to edit of original post)

  Roughly the same problem arises if you want to import files from a
  directory. If you delete the file inside the transaction, then the
  transaction might fail and the file was deleted but never imported. Or
  you delete the file after the transaction. Then the delete of the file
  might fail and the file gets imported a second time.

This type of problem is called a bug.

The solution is:

Define each step as its own TRANSACTION

You'll want to create them in a way that you can rerun (or skip) the step(s) as needed

Run each step in the appropriate order.

do not send email prior to COMMIT.
do not delete a file prior to successful loading of the data

You'll need to keep track of "where you are at" and if that step has passed/failed.

EMAIL example

You should have a procedure to sendEmail that should be called after commit.

If you want to call the procedure prior to commit, you'll need to add a row to a queue that will rollback with the main transaction.  For Oracle, this will be either Advance Queuing or the package APEX_MAIL

By putting it in a separate procedure, you can sendEmail a 2nd time upon [end-user's] request.

Process File

You have an algorithm that contains a few steps where each step can fail.  this is actually different than your sendEmail problem.

You need to record what you are processing, where you are at within your algorithm, and if that step have succeeded or failed.

In order to recover from an error at any step, each step of the process needs to be defined as a discrete TRANSACTION.

In Oracle, I would have these procedures (1 procedure per TRANSACTION):

create or replace
package file_processing_package
as
  procedure update_file_processing_status(
                                p_id       IN files_to_process.id%TYPE
                              , p_status   IN process_states.id%TYPE);

  function add_a_file_to_be_processed( p_filename IN files_to_process.file_name%TYPE )
                               return files_to_process.id%TYPE;

  procedure load_data_from_file( p_id in files_to_process.id%TYPE );

  procedure process_already_loaded_data( p_id in files_to_process.id%TYPE );

  procedure delete_file_from_os( p_id in files_to_process.id%TYPE );
end;
/

This is based on the following tables:

CREATE TABLE PROCESS_STATES (
  id   int generate by default on null as identity, -- 12c+
  state_desc  varchar2(25) not null,
  constraint process_states_pk primary key (id),
  constraint process_states_uq1 unique (state_desc)
);

insert into process_states( state_desc ) values ( 'file to be processed' );
insert into process_states( state_desc ) values ( 'file loaded' );
insert into process_states( state_desc ) values ( 'processing' );
insert into process_states( state_desc ) values ( 'processing failed' );
insert into process_states( state_desc ) values ( 'processing succeeded' );
insert into process_states( state_desc ) values ( 'delete failed' );
insert into process_states( state_desc ) values ( 'OK' ); -- delete succeeded
commit;

CREATE TABLE FILES_TO_PROCESS (
  id               int generate by default on null as identity, -- 12c+
  file_name        varchar2(50) not null,
  process_state_id int not null,
  constraint file_to_process_pk  primary key (id),
  constraint file_to_process_uq1 unique (file_name),
  constraint file_to_process_fk1 foreign key (process_state_id)
                            references (process_states.id)
);

The UNIQUE constraint on FILE_NAME prevents the same file being processed twice.


How does lock works when delete rows through secondary index in InnoDB? step1: InnoDB Transaction Isolation Level
Repeated Read.
step2: Table schema
CREATE TABLE `follows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `followable_type` varchar(255) DEFAULT NULL,
  `followable_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type_first` (`followable_type`,`followable_id`),
  KEY `id_first` (`followable_id`,`followable_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

step3: prepare test data
insert into follows(followable_type, followable_id) values ("Post", 6);
insert into follows(followable_type, followable_id) values ("Post", 22);
insert into follows(followable_type, followable_id) values ("Post", 28);
insert into follows(followable_type, followable_id) values ("Post", 32);
insert into follows(followable_type, followable_id) values ("Post", 34);

step4: begin two transactions
Session1
session1> begin;
session1> delete from follows where followable_id=28;

Session2
session2> begin;
session2> insert into follows(followable_type, followable_id) value ("Post", 22);
Lock wait timeout exceeded; try restarting transaction

session2>insert into follows(followable_type, followable_id) value ("Post", 28);
Lock wait timeout exceeded; try restarting transaction

session2>insert into follows(followable_type, followable_id) value ("Post", 32);
Query OK, 1 row affected (0.01 sec)

Test Result
MySQL manual claim

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

https://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
Actually, session1 locked this range [22, 32), which consists of

record locks (those index records equal 22)
a next key lock(22, 28]
a gap lock (28, 32)

Meaning of() (]

(): Gap lock is a lock on a gap between index records, I use bracket () to stand for.

(] Next key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. I use (] stand for it.

If manual is right, the result should be (22, 28]. So I really confused by conflict between test result and MySQL manual:

How to explain the conflict?
Could you explain the meaning of DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

(Semi-related comments...)

Assuming this is a standard many-to-many mapping table, get rid of id entirely.  Then make one of the other KEYs (either one) the PRIMARY KEY.  More tips here

If the DELETE is typical, then I would make the KEY starting with followable_id the PRIMARY KEY.

This will speed up the inserts and the deletes, thereby tending to avoid deadlocks and "lock waits".


SQL select question for hourly data I need help with MySQL statement . I need to know how many calls come in every hour. This is what we have so far.

    SELECT
sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 09:00:00' and '2015-12-02 10:00:00' THEN 1 else 0 END) as Hour2,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour3,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour4,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour5,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour6,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour7,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour8,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour9,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour10,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour11,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour12,
    sum(CASE WHEN session.creationtimestamp BETWEEN '2015-12-02 00:00:00' and '2015-12-02 23:00:00' THEN 1 else 0 END) as Hour13
    FROM session
    Inner Join mxuser
    ON session.extensionid2=mxuser.extensionid
    WHERE
    mxuser.userprofilename = 'Team1'
    And session.legtype1 = 2
    AND session.Legtype2 = 1
    or session.Legtype2= 2

I will call creationtimestamp simply ts...

SELECT  FLOOR(UNIX_TIMESTAMP(ts)/3600) AS start_of_hour,
        COUNT(*)
    FROM session ...
    GROUP BY FLOOR(UNIX_TIMESTAMP(ts)/3600);

If you need to "pivot" the output into 24 columns, then generate the code from http://mysql.rjweb.org/doc.php/pivot .


Conflict resolution for hash-backed unique constraint of large text bodies So we've just run into
ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un"
  Hint: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

(foo is table, bar is column and un for "unique")

Consider a function index of an MD5 hash of the value

Alright, let's.
This is simple enough to do:
ALTER TABLE foo DROP CONSTRAINT foo__bar__un;
CREATE UNIQUE INDEX foo__bar__un ON foo(md5(bar));

So far, so good, this is the solution both proposed in the error hint and found online in numerous places.
What I haven't found yet, though, is how to deal with collisions.
In my case, I have a database with results for biochemical experiments. I can't just go to the scientists and say "can you please change your result a tiny bit so we can actually store it in our database? yes, I understand this may cost a couple of your customers' lives and you personally a potentially prohibitive amount of money, but I can't allow you to store it otherwise".
(Realistically, we'd just drop the constraint way before that, but you get my point.)
I do understand that collisions are almost negligibly rare, but I'd rather know I can deal with them if against all odds one should happen. And given that this isn't a niche issue only we are having, I'm sure I'm not the only one to have ever wondered about this.
So what is the way to deal with collisions, in this case? I'd rather not have to replace the unique index with a DIY trigger-based workaround, if possible.
Can I somehow shoehorn in an additional "equals" check to be executed for entries with the same hash, for example, and only throw an exception if they actually are?

I don't know why the results of a biochemical experiment would be enforced to be unique in the first place, but you can use an exclusion constraint that tests for equality.  It will automatically resolve hash collisions by doing the character-by-character comparison.
alter table foo add constraint foo__bar__un exclude using hash (bar with =);

But you should upgrade to something newer than 9.6.


Oracle static lookup table What is the best approach (or what options am I not aware of) to creating a static table or view where the data rarely, if ever, changes?  An example use-case is a list of US states, where my structure would look something like:

state(
  id NUMBER(15) PRIMARY KEY,
  name VARCHAR2(35),
  abbreviation VARCHAR2(35)
)

The two approaches I am currently evaluating are:

Create a table and insert the desired data using a one time data import script

CREATE TABLE state
  (
    id           NUMBER(15) PRIMARY KEY,
    name         VARCHAR2(35),
    abbreviation VARCHAR2(2)
  );
INSERT INTO state (id, name, abbreviation) VALUES (1, 'California', 'CA');
...

Concern: data can be manipulated easily and modifying these records would break other parts of the software

Create a view using unions of hard-coded values

CREATE OR REPLACE VIEW state
AS
  SELECT 1 AS id, 'California' AS name, 'CA' AS abbreviation FROM dual
  UNION
  SELECT 2 AS id, 'New York' AS name, 'NY' AS abbreviation FROM dual
  UNION
  ...

Concern: maintenance, potential for errors when inputting (as opposed to using a sequence and trigger to populate id, for example), and it just doesn't feel right

Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role.

CREATE ROLE myApp_Admin;
CREATE ROLE myApp_User;

Assign rights to the roles:

GRANT SELECT, INSERT, UPDATE, DELETE ON state TO myApp_Admin;
GRANT SELECT                         ON state TO myApp_User;
-- ... grant rights to other tables and views here ...

Then, assign these roles to users

GRANT myApp_Admin TO invertigo;
GRANT myApp_User TO someoneElse;

  Concern: data can be manipulated easily and modifying these records would break other parts of the software.

This should not be the case, if the database has been designed carefully. If you are using referential integrity, the users should not able manipulate the data in a way that corrupts the application. If you have a table employee that refers to state, then define a foreign key constraint on this column:

ALTER TABLE employee ADD CONSTRAINT FK_emp_sta_myApp
FOREIGN KEY (state_id)
REFERENCES state.id;

With this constraint it is not possible to delete a state or change its id as long as it is referenced in employee.

A completely different way of solving this problem, is to not have a lookup table (or view) at all in your db, but to keep the lookup information within your application (e.g. as embedded xml resource).

Yet another solution:

Create the table as another oracle owner and grant the select right to your usual application owner. Here you can then create a view.

CONNECT otherOwner;
CREATE TABLE state (...);
GRANT SELECT ON state TO appOwner WITH GRANT OPTION;
-- GRANT OPTION allows appOwner to grant this SELECT right to your users or roles.

CONNECT appOwner;
CREATE VIEW state AS
    SELECT * FROM otherOwner.state
    WITH READ ONLY;

or you could just create a synonym (as appOwner):

CREATE SYNONYM state FOR otherOwner.state;

In both cases your users must not have access to otherOwner.


Drop and create stored procedure Yesterday we have seen some production issue in my project. While many users accessing the reports in SQL Server, it got timed out and CPU went up to 100%. When we checked in the backend, some 60 sessions were open. We just decided to drop and create the stored procedure. We have dropped and recreate the stored procedure and CPU became 40% and the issue has been resolved.

Can you please let me know what might be the reason for the same. This has happened for the different stored procedure today but we have dropped and recreated the stored procedure immediately to resolve the issue.

The phenomenon is generally known as parameter sniffing. You got a bad execution plan due to an unusual set of parameters, and by dropping and recreating the stored proc, you've just cleared the cached execution plan for that stored procedure.

Related reading material:

Slow in the Application, Fast in SSMS by Erland Sommarskog
Why Is This Query Sometimes Fast and Sometimes Slow by Brent Ozar (uh, me)
How to Start Troubleshooting Parameter Sniffing Issues by Brent Ozar (dang, that guy is good)


MySQL: bind-address 0.0.0.0 in my.cnf does not work? I'm new to mysql.
I want to connect to mysql server using whatever ip I want.
I read that all I have to do is to add to my.cnf file the line bind-address = 0.0.0.0.
That's what I did.
I restarted mysql server and then I tested it from command line.

mysql -uroot -p'*password*' -h 127.0.0.1 --> Works
mysql -uroot -p'*password*' -h 192.168.2.4 (local ip address) --->
ERROR 1045 (28000): Access denied for user 'root'@'mguru.lnx.gr' (using password: YES)

You can see below part of the section mysqld in my.cnf file

[mysqld]
user = mysql
port=3306
socket          = /opt/lampp/var/mysql/mysql.sock
skip-external-locking
key_buffer = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

plugin_dir = /opt/lampp/lib/mysql/plugin/
#skip-networking
bind-address=0.0.0.0

Thank you in advance,
Nikos

The configuration option bind-address tells MySQL what interfaces to listen on.  By receiving the error Access denied for user 'root'@'mguru.lnx.gr' we can see that is actually working fine.

What you will need to do is modify MySQL's privilege system so that you are able to connect from 'mguru.lnx.gr'.  The potentially confusing part of privileges in MySQL is that it is the combination of a username + the host.

This is covered in the MySQL manual here.


Differential backup to a specific date in history Is that possible to make backup to any specific date in the past in SQL Server?

For example I have full db backup for 15:30, then at 17:30 there were unintended actions on db: is there a way to backup changes made from 15:30 to 16:30?

If your database is configured for SIMPLE recover model, you're pretty much out of luck.

If your database is configured for FULL recovery model, you might be in luck.  This query will tell you what the recovery model is:

select recovery_model_desc from sys.databases where name='<YourDatabase>'

If your database is configured for FULL recovery, you could attempt to take a transaction log backup (example here). Then, you could attempt to restore your full backup WITH NORECOVERY and then restore your transaction log backup to a point-in-time using the STOPAT option (example here)

Assuming you can attempt a restore to point-in-time, you should probably restore to a test database to prevent overlaying the current one and then retrieve the missing data from the test database.


Make Edition Upgrade in a SQL cluster scenario I have a question in regards to Enterprise Edition to Standard Edition Upgrade. I need to move a SQL Cluster based on couple of SQL Evaluation Enterprise Edition to a licensed Standard Edition. How do I use the new license key?

OS: Windows Server 2012 Std Edition
SQL Server: SQL Server 2012

You aren't able to "downgrade" SQL Server Editions from Enterprise (including Evaluation) to Standard Edition. The Supported Version & Edition Upgrade article details supported upgrade paths.

In the upgrade matrix in the above link, you will notice that the section for Evaluation Edition includes the following note:

 Upgrading from Evaluation Enterprise (a free edition) to any of the
 paid editions is supported for stand-alone installations,
 but is not supported for clustered installations.

On a Failover Cluster Instance, in order to change from Evaluation Edition to Standard Edition, you will need to uninstall SQL Server, and re-install using Standard Edition. The same is true in all cases (clustered or standalone) if you are using Enterprise Edition, or Evaluation Edition with any Enterprise-only features.


How do I access a old saved tablespace after reinstalling postgres? So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to migrate my tablespace to an external harddrive ( I got this to work ). Then I basically changed my default tablespace to access the external drive. Then... couple weeks later I tried to run psql shell and it was throwing some error that it could not access subdirectories and something like this:

Macbooks-MacBook-Pro:~ macbookpro$ psql
psql: FATAL:  database "macbookpro" does not exist
DETAIL:  The database subdirectory "pg_tblspc/24748/PG_9.3_201306121/16384" is missing.

So, I looked around and found no answer so decided to reinstall postgres. After I reinstalled everything, I tried to reaccess my tablespace but it would not let me! I can see that the folders include over 130 GB of data so there must be something in there.

Please help! How do I access the data?

Thank you!

Recovering a tablespace if you've deleted the main data directory is difficult, because all the metadata about what's in the tablespace is gone. (Nor can you generally recover the main DB if you lose a tablespace.).

I really, really hope you had decent backups, because recovering your data will be difficult and time consuming. You'll need to learn a lot about PostgreSQL's innards.

Identifying relations (lost pg_class)

Pg's on-disk layout is minimalist, with almost all information about tables stored in the system catalogs, in tables like pg_class. That's necessary to properly support transactional DDL. The system catalogs are part of the main data directory.

There is a metapage for each table, but it won't tell you much.

The file layout of the tablespace is a tree of database-oids with relation-relfileid nodes within it. The mapping of relations relfileids to actual tables is stored ... in pg_class, in the main database.

So you have a collection of loose relation forks, with none of the metadata that tells you what they are. And it gets worse.

TOAST tables

As if that wasn't bad enough, lots of data is stored out-of-line in TOAST tables.

The only thing that connects TOAST tables to the main tables is the system catalogs.

Transaction visibility, lost pg_clog and pg_controldata

PostgreSQL records the status of transactions - which tx's are in progress, which ones committed, which rolled back, etc - in pg_clog. You guessed it, in the main database. The control file also resides in the main DB, and keeps track of things like the transaction ID wrap-around point.

Every tuple in a table has xmin and xmax fields, which together tell readers whether they should "see" a tuple when scanning the table. You've lost the records that give the transaction IDs in xmin and xmax meaning. So when you read your tables, you may get:

Duplicate rows where an UPDATE has marked the old row's xmax and created a new row.
Deleted rows
The results of rolled back transactions

Essentially, if you can read the table at all, it'll be a dirty read.

Unwritten changes and lost pg_xlog

PostgreSQL records changes to pg_xlog, then lazily applies them, possibly out of order, to the main relation forks. On crash it will replay these changes.

It doesn't matter if the on-disk status is inconsistent so long as the transaction logs are OK and the in-memory state in the buffer cache is fine.

You've lost the transaction logs, so your tables might be in a partially-replayed state with inconsistent/invalid data.

OK, so your data is a mess, what can you recover from it anyway?

Before you attempt to recover anything, make a complete copy of the original data. Do not change this. Any recovery attempts might make things worse, so you need to keep the originals.

Also, do not attempt any of this on a PostgreSQL install that contains anything you care about. initdb a throw-away PostgreSQL instance for the work. Also, you must be using exactly the same PostgreSQL version.

The first thing I'd do would be CREATE TABLE the original table structure. If you ever dropped columns or changed column types, you must repeat what you did exactly when you recreate the table. Once you're done, check the relfilenode that backs the table:

SELECT relname, relfilenode FROM pg_class WHERE relname = 'mytable';

You also need to find the associated TOAST table's relfileid.

Then I'd disconnect and stop PostgreSQL cleanly (pg_ctl stop -m smart) and try copying the lost table over to the file with the relfilenode of the newly recreated table. If the original had lots of forks (1234.1, 1234.2, etc) you need to copy and rename all of them. You might be able to match up which table is which based on file size, or by looking at strings for it.

Do the same for the TOAST table associated with the relation.

Then start PostgreSQL in standalone backend mode and attempt to read the table.

If you're really lucky it won't crash or ERROR, and will just give you an empty table or bogus results.

At this point, you then need to use pg_dirtyread to attempt to read the table data while ignoring transaction visibility. See the github repo.

It'd be quite nice to have a tool to dump a raw PostgreSQL database table standalone, but I don't think there's such a thing.

Others may have better suggestions about how to attempt recovery. It isn't something I've done, and I don't have time to test it out right now.

I suggest doing some more reading and research, see if anyone else has written about this already. Also post on pgsql-general@postgresql.org for further advice.


AG Cluster db upgrade by vendor Vendor wants to put db into simple mode prior to upgrade then back into full recovery mode after upgrade. I do not want to do this because I am not sure what impact it will have on the db cluster. Won't have to drop the db from the AG then put it back in after this process?

As seen in the availability group prerequisites the database must be in full recovery mode.

To change the recovery model, the database must be removed from the Availability Group, and it must be changed back to FULL before adding back into the Availability Group.

Obviously be very careful here - changing the database to single mode will break your log backup chains so you will be without that recovery mechanism if it goes wrong.


Tack database growth rate modifications Usually I use below query to get list of DBs which are with Percent growth or not having growth rate of 100 MB. I want to automate this process; How do I get alert when ever any developer change database growth rate value to any from our default configurations ?

SELECT DISTINCT
            db.name,
            growth,
            is_percent_growth
  FROM      sys.master_files mfile
            INNER JOIN sys.databases db ON mfile.database_id = db.database_id
  WHERE     ( growth <> 12800
              OR is_percent_growth = 1
            )
            AND db.state_desc = 'ONLINE'
            AND db.is_read_only = 0
            AND ( db.database_id > 4
                  OR db.name = 'Model'
                )
  ORDER BY  name

Probably depends on how quickly you want to be alerted.

If you just want to able to report on it then I'd probably suggest setting up a policy in Policy Based Management. You should be able to evaluate your instance on demand or on a schedule. You can generate an alert based off the SQL job it creates as well so you can be notified.

You could also look to just create a job that looked much like your query but either raise an error if there are databases that breach your policy (and append it to the alert)

The two options that would almost immediately let you know if someone had changed an autogrowth setting would be either via

DDL Server Scoped Trigger
SQL Alert using WMI to query ALTER_DATABASE Events

However, the latter would probably need to use tokens and would definitely require Service Broker to be enabled on all databases to work.


Can Oracle SE2 RAC works with only a single instance? Is it recommended? I've got a client with an Oracle DB 12 SE2 RAC with two instances.

One of the instances is giving too much problems. He wants just to remove it, and therefore leaving Oracle RAC running in only with one node.

Is the client forced to convert its DB to RAC ONE NODE or to Single Instance?

Regards!

You can just simply shutdown and disable one of the instances.

RAC One Node is optional in this case. If you want to use features like online database instance migration or rolling patching, you may use it, this is a simple configuration change, there is nothing to be converted.

Keep in mind, while RAC and RAC One Node are available without extra cost with SE2 in 12c, starting with 19c, these options are not available anymore in Standard Edition (SE2).


Oracle uses a lot of memory OS: Windows Server 2012
DB: Oracle11g-r2

When you restart or turn on the server, everything works perfectly, the memory is normal, the number of sessions, too. But after 2-3 days of continuous work, the consumed memory increases, and the number of sessions increases significantly to 450+. As far as I know, the database session should be thrown out by itself and there should be no problems with it. But what happens to memory? At the moment, it is 5.4GB passed 3 days. Tomorrow will be even more.
In the end, the database will stop accepting new traffic connections, even if everything is unloaded.
Only restart helps. Help me pls

But after 2-3 days of continuous work, the consumed memory increases ...

Databases don't generally consume memory by themselves.  Even Oracle.  :-)

They do so on behalf of client Applications.

  ... the number of sessions increases significantly to 450+.

Sounds like those client Applications are opening connections and not closing them.

Have those Application(s) changed recently?

  As far as I know, the database session should be thrown out by itself

[By default] The database has no knowledge of which sessions are eligible to be killed off and so will leave them connected for as long as they "want" to be.  Whilst it is possible to configure Oracle to kill off idle sessions after a period of time, this would only be a "Band-Aid"; you have a underlying [Application] problem that needs to be resolved.

  I do not know how the database is configured. I did not install.

Then ask yourself (very honestly): are you the Right Person to be dealing with this problem?  Do you have enough information to even look at it?

I get the impression (apologies if I'm completely wrong) that you are an "Accidental DBA".  If that's the case, then I would suggest you need help from a real one, preferably the one that installed this database in the first place.

If you can, go back and ask them for assistance.

If you can't, then escalate this issue to your Management.  Switching your database "off and on again" every other day is not a viable, long-term solution to your problem - and it's not just your problem, either; it's a problem for your entire organisation and not one that you can "solve" in isolation.  You need input from the developers of those client Applications.